What is wrong with my RESTORE procedure? I'm trying to use Toad Freeware (my SSMS is totally broken) for the following restore command:

RESTORE DATABASE AdventureWorks2016
FROM DISK = 'C:\Database\MSSQL\Samples\AdventureWorks2016CTP3.bak'

The response is:

Lookup Error - SQL Server Database Error: Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL13.DSMAIN\MSSQL\Data\AdventureWorks2016CTP3_Data.mdf" failed with the operating system error 21(The device is not ready.).

I don't even have a D drive. I suspect the original database files were stored on a D drive, but what do I do now?

I had the same issue. I ended up partitioning a D: drive, then it worked. Funny thing is that after it restored, D: drive was empty. :o


Why would SQL Server remain unresponsive after a slow query completes? I've just finished debugging a problematic SQL Server 2012 table-valued function.  It was declared to use a unique column in the table:

CREATE FUNCTION [dbo].[myApp_getUserIdsFromXml]
    (
    @searchXml xml
    )
RETURNS @tbl_found TABLE
    (
    userId int UNIQUE
    )
AS ...

The function returns some ID values based on XML data.  It was being OUTER APPLIED by various procedures without issue, until in one situation it returned about 60,000 rows, in which case the procedure execution time took 18 minutes instead of a fraction of a second.

The function was debugged and redeclared as:

RETURNS @tbl_found TABLE
    (
    userId int PRIMARY KEY /* <-- PK fixed the issue */
    )
AS ...

The execution time then dropped to 0.68 seconds.

Before debugging and altering it, even after the query had completed (18 minutes' worth), SQL Server remained extremely unresponsive.  Simple queries took minutes rather than milliseconds, and the SSMS GUI continuously froze and showed Windows' not responding message.  This lasted for about an hour each time before SQL seemed to assume normal speeds.

Can anyone suggest a reason why this might happen (e.g. memory not being freed, or something).  I've been using SQL for over ten years and never seen behaviour like this (the behaviour was replicated on two servers).

Or, can anyone suggest TSQL code to help investigate different areas where the problem might lie?  I've found code to test for locks, but there was nothing 'locked' here, the transaction had completed and nothing was being executed (by my apps) at the time.

It's obviously fixed now, but I've lost too much hair in the process...

Multi statement table valued functions are well known performance killers. I think the change you introduced had the beneficial side effect of turning your table variable from heap to clustered index. Other than that, I see no other difference between the two implementations.

I have no precise explanation of what could have "freezed" SQL Server, but that unresponsiveness is usually associated with high CPU. Did you take figures of what the CPU looked like during that period?

Another thing maybe worth investigating is the number of cache objects created in tempdb. Paul White has a great post on the subject on his blog. I would expect the numbers to be unchanged between heaps and CIs to be honest.

Another thing worth looking into is the preminent wait class during the "freeze". That could tell a lot about what's going on behind the covers. If you want to collect it over time you can use the Data Collector or a custom script.

BTW, if you managed to turn that function into an Inline Table Valued Function, that would probably help with performance.


Change column value status to be interrupted if no new record return after 10 mins I have three tables ,scadainfo_online, scadainfo_limit and scadainfo_status, data populated just like their name, self-described.

My purpose is changing automatically "status" column's values in scadainfo_status if no new record get in scadainfo_online values in 10 minutes. there is a one-to-one relationship using column item_id.

So, how can I do it? Google told me about a job with about 30 seconds interval, is there another answer? Thanks.

Depending on your response to my comment above this may be what you want. Or it may at least show you the principle of what you want.

UPDATE scadainfo_status ss
SET ss.alarm_status = 'NO UPDATE'
WHERE ss.item_id IN (WITH updates
                       AS ( SELECT so.item_id AS item_id,
                                   COUNT(*)
                                      OVER (PARTITION BY item_id
                                            ORDER BY date_time
                                            RANGE BETWEEN CURRENT ROW AND NUMTODSINTERVAL(10,'MINUTE')FOLLOWING)-1 AS later_records
                              FROM scadainfo_online so)
                       SELECT item_id
                         FROM scadainfo_online
                        WHERE later_records <1)

The 'IN' sub-query is counting records for the id that have occurred between this row and any that are in the following 10 minutes. A row (the current row) is returned if there are no further rows.
Sorry about the description of how it works; I find analytic queries tricky and explaining them even more so.


Does spreading table data across multiple files (in the filegroup) improve performance? From the product documentation on filegroups:

For example: Data1.ndf, Data2.ndf, and Data3.ndf, can be created on
three disk drives, respectively, and assigned to the filegroup
fgroup1. A table can then be created specifically on the filegroup
fgroup1. Queries for data from the table will be spread across the
three disks; it will improve performance.

I understand that if there is a very large table, and if I place it in a file group that has a single file and if this is on a separate disk, then it will improve performance because all queries to this large table will go to the separate disk.
I also know about table partitioning using multiple filegroups; each filegroup with one or more files. However this question is specifically about file group in a scenario of an unpartitioned large table.
In the above example, the author says that the performance is improved even if the file group contains multiple files. I am confused about this because if a table is spread across multiple files, then will this not cause slow performance (because the rows will be spread across the files and so to go from one row to the next row which maybe on another file, wont it take a hit on performance)?

As noted by the other users this doesn't improve performance.  It does provide two important capabilities.

You can add space and IO capacity to a database without moving it by adding a new disk and adding new files to the filegroup and placing them on the new disk, or by moving some of the existing files to the new disk.

You can restore the database on to a server that doesn't have a volume large enough for the entire database by spreading out the files across multiple disks.

And

because the rows will be spread across the files and so to go from one row to the next row which maybe on another file, wont it take a hit on performance?

No.  SQL Server always reads at least an 8KB page; it never reads a single row from disk.  Whether the "next" page is in the same file or not matters little.  And SQL Server will allocate space to an object using 8-page Extents, so when reading sequentially, you'll typically read at least 64KB from each file before reading from the next.
To support old-fashoned data warehouse configurations built on simple arrays of spinning disks there is a startup parmeter -E that "Increases the number of extents that are allocated for each file in a filegroup.", but on a modern storage solution, it's rarely used even in DW configurations.


What's the easiest way to move tables with identity key columns while keeping the referential integrity between them? I have four tables in database A that have identity key columns and have foreign key references to each other on those columns (without actual foreign key objects created, just the columns themselves).

I need to move all the data from these tables to correlating (almost) identical tables in database B. (There's already data in the tables in database B too, so I'm trying to append this data.)

How can I move the data to database B which will generate new key values for each table's records but without disrupting the referential integrity.

As you're going to have new id values, it's not going to to be easy to do a straight copy.

I might suggest copying the data to a set of staging tables with no foreign keys defined. Then, in the staging tables, increase the existing ID values for all rows in a given table by a fixed amount that will guarantee they are higher than the max value in the destination tables (or you could change them to negative values if you haven't already used those) - you'll need to apply the same update to the foreign key fields in any child table at the same time.

Then you can use SET IDENTITY_INSERT {TableName} ON; to allow you to insert the updated records to the destination table. You should turn that off again once you are done.

SQL Server will automatically take account of the new rows you have inserted to re-seed the Identity values for new records moving forward so once you've inserted your records all is good.


Automatic slave reconfiguration after updating master I have development mysql master and slave servers and a prod server. I need to set up regular (once a week) syncronization of dev and prod databases. It would be easy for me to use prod# mysqldump > dump.sql and dev-master# mysql < dump.sql as it won't break replication, but having a 170Gb base it is not very good idea.
So the question is if there's a proper way to re-enable replication after re-writing database on master. Will it work if I just re-write database on both master and slave and hope no bin-log errors occure?

Upd: percona-server-server-5.6, InnoDB/Barracuda are used.

The solution I found for myself is:
stop both master and slave
remove logs
replace ib* files and /var/lib/mysql/mybase/* with files from prod backup
start mysql on master
start mysql on slave with --skip-slave-start, then SLAVE RESET; SLAVE START;

Not sure if this is correct, but it works for now.


Begin and GO in TSQL i have a TSQL code to move DATA from ORACLE to SQL SERVER. It is scheduled to run daily. But, for some reason it is not working as expected like taking a long time to finish execution and getting error like ERROR 3902 COMMIT TRANSACTION request has no Corresponding BEGIN TRANSACTION. The code was tested on the local server and was working properly.
I have some doubts regarding certain parts of the code.

SOME CODE REGARDING MOVEMENT OF DATE FROM ORACLE TO SQL SERVER
GO
    begin transaction
    commit;
    go

My doubts are as follows:

I have read that GO is not a TSQL command. So, does it work in a scheduler?
Begin TRANSACTION should start a transaction explicitly. But there is no code after BEGIN TRANSACTION. So how does it understand the above code should be executed?

I have solved the issue. Issue was with commit statement. After, every insert statement i added a commit(similar to pl/sql). I added begin transaction before every commit and it worked. I think commit works differently in pl/sql and TSQL.

GO is not a Transact-SQL statement, instead it is a command
recognized by the Sql Server Management Studio (i.e. SSMS), SQLCMD
and OSQL utilities. These utilities send all statements after the
previous GO statement and before the current GO statement as one
Batch to the Sql Server engine for execution. It should work in a
scheduler
It looks like your transaction failed, got rolled back and there is nothing to commit.

This will NOT run

CREATE TABLE Test
(
    id INT PRIMARY KEY NOT NULL
);
GO

BEGIN TRAN;
INSERT Test
VALUES
('a');
GO
COMMIT TRAN;

  Msg 245, Level 16, State 1, Line 6 Conversion failed when converting
  the varchar value 'a' to data type int. Msg 3902, Level 16, State 1,
  Line 9 The COMMIT TRANSACTION request has no corresponding BEGIN
  TRANSACTION.

This will run without a problem

CREATE TABLE Test
(
    id INT PRIMARY KEY NOT NULL
);
GO

BEGIN TRAN;
INSERT Test
VALUES
(5);
GO

COMMIT TRAN;

A good article on transactions  http://www.sommarskog.se/error_handling/Part1.html


Could this ActiveRecord/SQL statement in my code base be susceptible to SQL injection? I was reading up on SQL injection attacks from the Rails Guides and was wondering if the below line could be susceptible to a SQL Injection attack:

accounts.where("lower(name) LIKE '%#{params[:query].downcase}%'")

If I read and understand the guide correctly, could a user do something like

 `'\''; DROP TABLE users;`

Would that drop my table?

------EDIT------

Same for these examples:

Account.where(id: account_id).first
current_user.actions.where(id: params[:clear_action_reminder]).first
edits.where.not(account_id: non_human_ids)
Rating.where(project_id: @project.id, account_id: current_user.id).first_or_initialize

These are of course application specific queries but because there is no ? in the query could these all potentially be disastrous?

It's probably open to a generic SQL injection, according to this discussion in the pgsql-general  mailing-list:

http://www.postgresql.org/message-id/20140626102204.36ced4dd@red.yakaraplc.local

The poster looked into ActiveRecord internals and reports that it uses libpq's PQsendQuery, which does accept several queries separated by semi-colons. This is from 2014, so presumably current.

So, assuming that your params[:query].downcase has not already gone through a layer of quoting, a DROP TABLE could be injected into it.

Even if ActiveRecord used PQexecParams or a similar call that doesn't accept semi-colon seperated queries, there would still be the possibility of injecting a function call, as in ...LIKE '%whatever' AND do_something_bad(...) AND 'whatever'='%'. This possibility is opened by lack of proper literal strings quoting, or lack of handling the parameters as recommended by the API.

And even when using prepared statements or injection with proper escaping, LIKE with user-supplied contents require an additional special quoting anyway, since it has its own syntax with % and _ being wildcards and its own escape mechanism with \ configurable with the ESCAPE clause. Otherwise the users can't search a literal % or _


How to revoke DBADM and clean up any related objects from DB2 LUW This question has started because of us taking copies of production backups and restoring them into lower environments (with scrambled data of course) for developers to practice and/or debug against.

We have IDs that are assigned DBADM,SECADM,DATAACCESS, and ACCESSCTRL (mainly the instance owner). When we restore into a lower environment, we end up needing to log on as the original instance owner and grant the same above authorities (DBADM, SECADM, DATAACCESS, and ACCESSCTRL) to the new target instance owner.

I figured it was not a good idea to leave the original ID in the database, so I attempted to revoke its privileges. Not long after my package rebinds started failing, as there are apparently packages tied to that original ID. Not knowing what they contained and if I could/should delete them or not (even though I'm thinking they are harmless to remove???) I ended up restoring the privileges to the original instance owner and just leaving it there.

That has always bothered me. An ID with the powers of DBADM,SECADM,DATAACCESS, and ACCESSCTRL shouldn't just be lying around. To me that is a security hole. To me the ID should be revoked and any other cleanup that needs to be performed should be executed to keep the database safe. The same would be true if say I would quit the company or my fellow DBAs would. We would want to revoke IDs and clean up and/or transfer ownership of objects to remove any security holes.

The problem is, I don't know what all needs to be cleaned up. I have done a bit of poking around on Google and IBM's documentation, and I can find nothing to suggest the steps that should normally be taken when removing such a high user from the system. Or even a user with BINDADD authority?

What do you all remove/revoke and in what order? Is there a way this can be scripted/automated? How do you know which packages you can remove? Are there other things that need to be moved/transferred?

Anyone else encounter this? Thoughts? Experiences?

There are several aspects that you might want to consider.

Firstly, you can assign proper authorities to the target instance owner during the restore automatically by using the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable. If it's set to ON prior to the restore, the user performing the restore will get those authorities.

Secondly, most of the binding/compiling problems come from the fact that only the object owner, that is, the authorisation ID that created the object in the first place, is given permissions to perform certain operations on it. If you run DDL scripts in production as the instance owner, then this would be the owner ID of all objects after the restore, no matter the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES setting. You could, of course, use the TRANSFER OWNERSHIP statement to change that, but in real life, with tens of thousands of objects in the database, this may not be practical.

To avoid the "secondly" part, in many organisations folks use a special "builder" authorisation ID, separate from the instance owner, to run DDL scripts. This ID will not be given any unnecessary privileges, can be tightly controlled, and, when existing in all environments, should be able to perform all necessary tasks without adding security exposure.


Total Server Memory and Target Server memory Value same Can anyone help me what does this indicate:

From the MSDN forums thread Total Server Memory VS Target Server Memory made me crazy answer by Jonathan Kehayias:

  If Total Server Memory is less than Target Server Memory it can be a
  sign of memory pressure

But in my case the values are same:

But in my case the values are same :

You cannot and should not come to conclusion that SQL Server is facing memory pressure by just looking at one perfmon counter, this is just not correct. You have to rely on other perfmon counters which I am sure you are aware.

Now for your question, since both are same this means the SQL Server memory requirement is balanced as of now and its quite likely that SQL Server is not facing memory pressure. In most cases I have seen that on steady system target and total server memory are equal and I have always found out that when they are same it means currently there is no memory pressure.


Listing the existing SQL Server Logins and Users I know we can check the logins and the users that are defined, using GUI in SQL Server, but am wondering how we can do this check using script.

I ran the query below but it shows Principal_id which I'm not sure how to map to get the permission level.

SELECT * FROM Sys.login_token

So is there any built-in stored proc that can list the logins and the users with their permission level?

Thank you.

Not sure if there's a built-in way, but give this query a try for server principals:

;with ServerPermsAndRoles as
(
    select
        spr.name as principal_name,
        spr.type_desc as principal_type,
        spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,
        'permission' as security_type,
        spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.type in ('s', 'u')

    union all

    select
        sp.name as principal_name,
        sp.type_desc as principal_type,
        spr.name as security_entity,
        'role membership' as security_type,
        null as state_desc
    from sys.server_principals sp
    inner join sys.server_role_members srm
    on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals spr
    on srm.role_principal_id = spr.principal_id
    where sp.type in ('s', 'u')
)
select *
from ServerPermsAndRoles
order by principal_name

Basically what it does it gets the permissions granted and denied, and unions it with the role membership.  It should give you a brief look on the security for server logins.  Let me know if that's what you're looking for.


Postgres full text search with multiple columns, why concat in index and not at runtime? I've come across full text search in postgres in the last few days, and I am a little confused about indexing when searching across multiple columns.

The postgres docs talk about creating a ts_vector index on concatenated columns, like so:

CREATE INDEX pgweb_idx ON pgweb
    USING gin(to_tsvector('english', title || ' ' || body));

which I can search like so:

... WHERE
      (to_tsvector('english', title||' '||body) @@ to_tsquery('english', 'foo'))

However, if I wanted to sometimes search just the title, sometimes just the body, and sometimes both, I would need 3 separate indexes. And if I added in a third column, that could potentially be 6 indexes, and so on.

An alternative which I haven't seen in the docs is just to index the two columns seperately, and then just use a normal WHERE...OR query:

... WHERE
      (to_tsvector('english', title) @@ to_tsquery('english','foo'))
    OR
      (to_tsvector('english', body) @@ to_tsquery('english','foo'))

Benchmarking the two on ~1million rows seems to have basically no difference in performance.

So my question is:

Why would I want to concatenate indexes like this, rather than just indexing columns individually? What are the advantages/disadvantages of both?

My best guess is that if I knew in advance I would only want to ever search both columns (never one at a time) I would only ever need one index by concatenating which use less memory.

No you don't need separate indexes. Use the weights feature. They are just a label your can query against. You can have up to four labels to query against (A-D).

--search any "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true

--search B "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:B'::tsquery; --false

--search B or C "fields" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:BC'::tsquery; --true

You might want to concatenate tsvectors, so that you can separately apply weights to them and then put them together:

select
  setweight( name_column::tsvector, 'A') || setweight( phone_column::tsvector, 'B');


Database backup taking longer than usual to a networkshare The SQL server does backups with this command to a network share \BackupServer\weekeklybackups"

BACKUP DATABASE DBNAME TO DISK = '\\BackupServer\weekeklybackups\DBNAME.BAK'

There is a 1 gig network between the SQL Databse server and the BackupServer. but it uses maximum 25% of the network, so the backup is taking for ever.
If I make the database locally and copy it over to the same network share, it is faster using 99% of the network bandwidh.

So, the weekly automated backups are taking a long time, which is an issues

When I check the monitor it says it is waiting with "ASYNC_IO_...". When I did some google search it says, this is an issue on tape backup. But this is on iSCSI hard

disks. and manual copy over works fine. So, what could be the issue? It was working fine for a year until last week. Not sure what happened. but where should I be

looking in to?

Thanks for your time

It was an issues on the hard drive. It never showed any errors in event viewer or the controller. When we rebooted the server, the errors showed up and we replaced the drives.


What are the drawbacks of using DOMAINS to store metadata about the column? I came across a question about a data type for MD5 values. The recommendation in that question states using uuid as type for that field.

The argumentation is solid. But I find that this might be confusing to someone unaware of the points revealed in the above question. The decision to use uuid as MD5 type is something that I would like to see in some form of documentation in any project that does this.

As a "helper" to whoever looks into the DB Schema, one could argue to create a md5 domain inheriting from uuid. That way, the column types would be much more explicit and the intention would be much clearer.

But it would use a domain for no other reason than to "rename" an existing type.

It's still something that would make sense to document properly. But that documentation could be centralised to a section explaining the domains in the DB. So you wouldn't gain anything in terms of documentation. The advantage I see is, as mentioned, that the intention becomes clear when looking at the table DDL.

Are there any downsides to this?

While it's not a bad idea to store the meta-data as the type name, I would not. That's a slippery slope that will lead to bizzare place where ints become more specific types that provide nothing (insofar as constraints, what DOMAINs are for). This meta-data about what is in the column belongs in the column name, not the type.

Further you can explain it with a PostgreSQL COMMENT

COMMENT ON COLUMN mycatalog.myschema.mytable IS
  $$ Not really a UUID, just suing it to store an MD5 $$;

Inability to change underlying types

From a_horse_with_no_name,

You can no longer change the definition of a domain as long as it is used by at least one column. E.g. a domain defined as varchar(10) can not be changed to varchar(20) if it's used.

Other problems

There are some oddities regarding ranges over domains
For any functions that use the domain, you will want to ensure that the types from literals are in the domain type.

CREATE DOMAIN foo AS text;
CREATE FUNCTION f(foo)
RETURNS bool
AS $$
  SELECT false
$$ LANGUAGE SQL;

SELECT f('foo'); -- returns false

CREATE FUNCTION f(text)
RETURNS bool
AS $$
  SELECT true;
$$ LANGUAGE SQL;

SELECT f('foo'); -- returns true

I use domains liberally, but only to add constraints so that they're centrally managed and not in a hodgepodge of CHECK statements.


SQL Server Developer Edition Downgrading to Older Developer Edition I have seen answers for questions regarding downgrading from different editions (e.g. - Enterprise to Standard), but is anyone familiar with downgrade policies for SQL Server Developer editions to OLDER developer editions?

We currently have a need for 2008 R2 developer editions, which are no longer available unless purchasing via reseller.

We found a reseller, but our company's planned upgrade path includes 2012 in the next 6 to 12 months. It does not make sense for us to purchase 2008 R2 dev edition if we can purchase 2012 or 2014 at retail prices, downgrade for the short term, then upgrade once our enterprise does.

Has anyone done this before? I'd prefer an answer here versus the reseller as their answer is going to be sales-centric (to be polite) versus a more straight forward answer from someone who may have done this.

Microsoft gives Downgrade Rights to Volume Licensing customers and, to some extent, even to OEM and retail customers.

However, in general, if you don't have a Volume License, you can't have the bits and the product key, which are necessary to excercise your downgrade rights.

Long story short, if you want 2008 R2, you'll have to buy it.

Here's a blog entry of the licensing team on this subject


Query with ROWNUM returns differents rows on every execution I have a query that is used to search for a list of sub divisions in our network. When the query is executed without any filter, it returns a different amount of rows on every execution

SELECT DISTINCT
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
WHERE ROWNUM <= 200  -- normally a filter goes here
ORDER BY t1.SUBDIV_NAME

Execution 1

ZRESUBDIV    SUBDIV_NAME
------------ ------------------------------------------------------------
262          ALEXANDRIA
400          ALLANWATER
671          ASHCROFT
372          BALA
277          BEACHBURG
200          BRIDGE
374          CARAMAT
543          CENTRAL BUTTE
208          CHAPAIS
324          CHATHAM
340          CHICAGO
...

Execution 2

ZRESUBDIV    SUBDIV_NAME
------------ ------------------------------------------------------------
670          ALBREDA
262          ALEXANDRIA
400          ALLANWATER
671          ASHCROFT
372          BALA
277          BEACHBURG
604          BLACKFOOT
200          BRIDGE
607          CAMROSE
354          CASO
543          CENTRAL BUTTE
...

There is also missing data: the subdivision Baton Rouge never appears although it is in the data.

If I remove the ROWNUM, all data is correctly returned. I need to leave ROWNUM since the query support a WHERE clause with a filter on the subdivision name.

What is the cause of this?

ROWNUM is evaluated before ORDER BY, that is not how ROWNUM should be used. To use ROWNUM properly, use a query like this:

SELECT * FROM
(
  SELECT DISTINCT
      t1.ZRESUBDIV,
      t1.SUBDIV_NAME
  FROM M_STAGE_WP_PFUNCT_LOC t1
  ORDER BY t1.SUBDIV_NAME
)
WHERE ROWNUM <= 200;

But this is old. Since you are on 12c, use this:

SELECT DISTINCT
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
ORDER BY t1.SUBDIV_NAME
FETCH FIRST 200 ROWS ONLY;


How can I disable utf8mb4 entirely on MySQL 5.5? I know you're not supposed to do this, but I have a MySQL 5.5 master and a MySQL 5.1 replication slave. That's supposed to be okay, unless I do something stupid like use utf8mb4 that isn't supported by 5.1. Well, everything has been going well for a while and today I got a replication slave failure because character_set_client #45 isn't supported by MySQL 5.1.

I spent a lot of time trying to find out what #45 was... you see, it doesn't appear in any of MySQL's configuration files -- even on 5.5. I finally determined it was utf8mb4 (which I had suspected) but I'm not sure why it's being used. I performed a static query on the master ... something simple like UPDATE table SET field='value' and it replicated to the slave by setting the character set to utf8mb4. I executed hundreds of similar UPDATE statements at the same time, but some of them triggered the use of utf8mb4.

So... I'm going to be upgrading my slave to 5.5, but in the mean time is there a way to tell MySQL to never use utf8mb4?

POLITICALLY CORRECT ANSWER
You cannot do MySQL Replication from a New Master to an Old Slave
I have written many posts about this:

Nov 26, 2012 : Replicate MySQL 5.0 Master to 5.5 Slave?
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Dec 22, 2011 : What does the base64 BINLOG statements in mysqlbinlog output mean?
Feb 04, 2011 : MySQL master binlog corruption

In addition, there is the likelihood of dealing with the internal BINLOG magic number being in the wrong place and replication going senile. Here are my posts on the BINLOG magic number:

Oct 28, 2012 : Binlog has bad magic number
Dec 26, 2011 : mysql emits BINLOG rows even though binlog_format=STATEMENT

Since there is nothing in the meantime you can do, please upgrade your Slave to MySQL 5.5
ALTERNATE PERSPECTIVE
In all fairness, @ChristopherSchultz is correct in that you can implement it. I just yell and scream "RISK !!!" to be more cautious because of the BINLOG magic number's place in the MySQL Replication Universe.


Returning correct timezone offset from join Meeting table

|MeetingID | Start               | Timezone             | LocalStartTime
|13        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |
|23        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |
|3         | 2018-01-31 19:00:00 | (UTC+10:00)Hobart    |

Timezone Offset

|Timezone ID| StartDateTime    | EndDateTime      | Timezone              | OffsetMin
|1          | 2017-09-30 16:00 | 2018-03-31 16:00 | (UTC+10:00) Melbourne | 660
|2          | 2018-03-31 16:00 | 2018-10-06 16:00 | (UTC+10:00) Melbourne | 600

Hi, I have joined the above two tables in order to retrieve offsetmin, however the only foreign key i have in the meeting table is Timezone. Joining on this returns two rows for each meetingID. This is because each timezone is listed twice inside the timezone offset table, with two seperate offsetmin values based on whether it is day light savings or not.

select m.meetingid, m.start, m.timezone, m.localstarttime, t.offsetmin
from meetingtable as m
join timezone as t

Meeting Table With Offset

|MeetingID | Start               | Timezone             | LocalStartTime| OffsetMin
|13        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |               |660
|13        | 2018-01-31 19:00:00 | (UTC+10:00)Melbourne |               |600

I am trying to return only the correct timezone offset based off its date, whether or not it falls into daylight savings or not (Timezone ID 1 or 2)

I am using MSSQL

If you are not able to modify or generate a new Timezones table without daylights, you could use more than one solution.

Using CROSS APPLY

select m.meetingid,
       m.start,
       m.timezone,
       m.localstarttime,
       t.offsetmin
from   meetingtable as m
cross apply (select top 1 offsetmin
             from timezone
             where timezone = m.timezone) t;

Using sub-query

select m.meetingid,
       m.start,
       m.timezone,
       m.localstarttime,
       (select top 1 offsetmin
        from timezone
        where timezone = m.timezone) offsetmin
from   meetingtable as m;

Using GROUP BY

select    m.meetingid,
          m.start,
          m.timezone,
          m.localstarttime,
          t.offsetmin
from      meetingtable as m
left join (select timezone,  max(offsetmin) offsetmin
           from   timezone
           group by timezone) t
on         t.timezone = m.timezone;


Why does SQL Server estimate less rows will be emitted from a join after inserting some rows? The below is a simplified version of something I came across in production  (where the plan got catastrophically worse on a day where an unusually high number of batches were processed).

The repro has been tested against 2014 and 2016 with the new cardinality estimator.

CREATE TABLE T1 (FromDate  DATE, ToDate DATE, SomeId INT, BatchNumber INT);

INSERT INTO T1
SELECT TOP 1000 FromDate = '2017-01-01',
                ToDate = '2017-01-01',
                SomeId = ROW_NUMBER() OVER (ORDER BY @@SPID) -1,
                BatchNumber = 1
FROM   master..spt_values v1

CREATE TABLE T2 (SomeDateTime DATETIME, SomeId INT, INDEX IX(SomeDateTime));

INSERT INTO T2
SELECT TOP 1000000 '2017-01-01',
                   ROW_NUMBER() OVER (ORDER BY @@SPID) %1000
FROM   master..spt_values v1,
       master..spt_values v2

T1 contains 1,000 rows.

The FromDate, ToDate, and BatchNumber are identical in all of them. The only value that differs is SomeId with values between 0 and 999

+------------+------------+--------+-----------+
|  FromDate  |   ToDate   | SomeId | BatchNumber |
+------------+------------+--------+-----------+
| 2017-01-01 | 2017-01-01 |      0 |         1 |
| 2017-01-01 | 2017-01-01 |      1 |         1 |
....
| 2017-01-01 | 2017-01-01 |    998 |         1 |
| 2017-01-01 | 2017-01-01 |    999 |         1 |
+------------+------------+--------+-----------+

T2 contains 1 million rows

but only 1,000 distinct ones. Each repeated 1,000 times as below.

+-------------------------+--------+-------+
|      SomeDateTime       | SomeId | Count |
+-------------------------+--------+-------+
| 2017-01-01 00:00:00.000 |      0 |  1000 |
| 2017-01-01 00:00:00.000 |      1 |  1000 |
...
| 2017-01-01 00:00:00.000 |    998 |  1000 |
| 2017-01-01 00:00:00.000 |    999 |  1000 |
+-------------------------+--------+-------+

Executing the following

SELECT *
FROM   T1
       INNER JOIN T2
               ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
                  AND T1.SomeId = T2.SomeId
WHERE  T1.BatchNumber = 1

Takes about 7 seconds on my machine. The actual and estimated rows are perfect for all operators in the plan.

Now add 3,000 additional batches to T1 (with batch numbers 2 to 3001). These each clone the existing thousand rows for batch number 1

INSERT INTO T1
SELECT T1.FromDate,
       T1.ToDate,
       T1.SomeId,
       Nums.NewBatchNumber
FROM   T1
       CROSS JOIN (SELECT TOP (3000) 1 + ROW_NUMBER() OVER (ORDER BY @@SPID) AS NewBatchNumber
                   FROM   master..spt_values v1, master..spt_values v2) Nums

and update the statistics for luck

 UPDATE STATISTICS T1 WITH FULLSCAN

And run the original query again.

SELECT *
FROM   T1
       INNER JOIN T2
               ON CAST(t2.SomeDateTime AS DATE) BETWEEN T1.FromDate AND T1.ToDate
                  AND T1.SomeId = T2.SomeId
WHERE  T1.BatchNumber = 1

I let it run for a minute before killing it. By that time it had output 40,380 rows so I guess it would take 25 mins to output the full million.

The only thing that changed is that I added some additional rows not matching the T1.BatchNumber = 1 predicate.

However the plan has now changed. It uses nested loops instead and whilst the number of rows coming out from t1 is still correctly estimated at 1,000 (①) the estimate of the number of joined rows has now dropped from 1 million to a thousand (②).

So the question is...

Why does adding additional rows with BatchNumber <> 1 somehow affect the estimates for rows joined when BatchNumber = 1?

It certainly seems counter intuitive that adding rows to a table should end up reducing the estimated number of rows from the query as a whole.

It's important to remember that you aren't guaranteed consistency as you change queries or the data within the tables. The query optimizer may switch to using a different method of cardinality estimate (such as using density as opposed to histograms) which can make two queries appear to be inconsistent with each other. With that said, it certainly seems like the query optimizer is making an unreasonable choice in your case, so let's dig in.

Your demo is too complicated so I'm going to work off of a simpler example which I believe shows the same behavior. Starting data prep and table definitions:

DROP TABLE dbo.T1 IF EXISTS;
CREATE TABLE dbo.T1 (FromDate DATE, ToDate DATE, SomeId INT);

INSERT INTO dbo.T1 WITH (TABLOCK)
SELECT TOP 1000 NULL, NULL, 1
FROM master..spt_values v1;

DROP TABLE dbo.T2 IF EXISTS;
CREATE TABLE dbo.T2 (SomeDateTime DATETIME, INDEX IX(SomeDateTime));

INSERT INTO dbo.T2 WITH (TABLOCK)
SELECT TOP 2 NULL
FROM master..spt_values v1
CROSS JOIN master..spt_values v2;

Here's the SELECT query to investigate:

SELECT *
FROM T1
INNER JOIN T2 ON t2.SomeDateTime BETWEEN T1.FromDate AND T1.ToDate
WHERE T1.SomeId = 1;

This query is simple enough so that we can work out the formula for the cardinality estimate without any trace flags. However, I'm going to try to use TF 2363 as I go to better illustrate what's going on within the optimizer. It's not clear if I'll be successful.

Define the following variables:

C1 = number of rows in table T1

C2 = number of rows in table T2

S1 = the selectivity of the T1.SomeId filter

My claim is that the cardinality estimate for the above query is as follows:

When C2 >= S1 * C1:

  C2 * S1
  with a lower bound of S1 *
  C1

When C2 < S1 * C1:

  164.317 * C2 *
  S1 with an upper bound of
  S1 * C1

Let's go through some examples, although I'm not going to go through every single one that I tested. For the initial data prep we have:

C1 = 1000

C2 = 2

S1 = 1.0

Therefore, the cardinality estimate should be:

  2 * 164.317 = 328.634

The impossible-to-fake screenshot below proves this:

Using the undocumented trace flag 2363 we can get a few clues about what's going on:

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB2].[dbo].[T1].SomeId

Loaded histogram for column QCOL: [SE_DB2].[dbo].[T1].SomeId from stats with id 2

Selectivity: 1

Stats collection generated:

  CStCollFilter(ID=3, CARD=1000)

      CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

End selectivity computation

Begin selectivity computation

Input tree:

...

Plan for computation:

  CSelCalcSimpleJoinWithUpperBound (Using base cardinality)

      CSelCalcOneSided (RIGHT)

          CSelCalcCombineFilters_ExponentialBackoff (AND)

              CSelCalcFixedFilter (0.3)

              CSelCalcFixedFilter (0.3)

Selectivity: 0.164317

Stats collection generated:

  CStCollJoin(ID=4, CARD=328.634 x_jtInner)

      CStCollFilter(ID=3, CARD=1000)

          CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

      CStCollBaseTable(ID=2, CARD=2 TBL: T2)

End selectivity computation

With the new CE we get the usual 16% estimate for a BETWEEN. This is due to exponential backoff with the new 2014 CE. Each inequality has a cardinality estimate of 0.3 so BETWEEN is calculated as 0.3 * sqrt(0.3) = 0.164317. Multiply the 16% selectivity by the number of rows in T2 and T1 and we get our estimate. Seems reasonable enough. Let's bump up the number of rows in T2 to 7. Now we have the following:

C1 = 1000

C2 = 7

S1 = 1.0

Therefore, the cardinality estimate should be 1000 because:

  7 * 164.317  = 1150 > 1000

Query plan confirms it:

We can take another peek with TF 2363 but it looks like the selectivity was adjusted behind the scenes to respect the upper bound. I suspect that CSelCalcSimpleJoinWithUpperBound prevents the cardinality estimate from going above 1000.

Loaded histogram for column QCOL: [SE_DB2].[dbo].[T1].SomeId from stats with id 2

Selectivity: 1

Stats collection generated:

  CStCollFilter(ID=3, CARD=1000)

      CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

End selectivity computation

Begin selectivity computation

Input tree:

...

Plan for computation:

  CSelCalcSimpleJoinWithUpperBound (Using base cardinality)

      CSelCalcOneSided (RIGHT)

          CSelCalcCombineFilters_ExponentialBackoff (AND)

              CSelCalcFixedFilter (0.3)

              CSelCalcFixedFilter (0.3)

Selectivity: 0.142857

Stats collection generated:

  CStCollJoin(ID=4, CARD=1000 x_jtInner)

      CStCollFilter(ID=3, CARD=1000)

          CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

      CStCollBaseTable(ID=2, CARD=7 TBL: T2)

Let's bump T2 to 50000 rows. Now we have:

C1 = 1000

C2 = 50000

S1 = 1.0

Therefore, the cardinality estimate should be:

  50000 * 1.0 = 50000

The query plan again confirms it. It's much easier to guess at the estimate after you've already figured out the formula:

TF output:

Loaded histogram for column QCOL: [SE_DB2].[dbo].[T1].SomeId from stats with id 2

Selectivity: 1

Stats collection generated:

  CStCollFilter(ID=3, CARD=1000)

      CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

...

Plan for computation:

  CSelCalcSimpleJoinWithUpperBound (Using base cardinality)

      CSelCalcOneSided (RIGHT)

          CSelCalcCombineFilters_ExponentialBackoff (AND)

              CSelCalcFixedFilter (0.3)

              CSelCalcFixedFilter (0.3)

Selectivity: 0.001

Stats collection generated:

  CStCollJoin(ID=4, CARD=50000 x_jtInner)

      CStCollFilter(ID=3, CARD=1000)

          CStCollBaseTable(ID=1, CARD=1000 TBL: T1)

      CStCollBaseTable(ID=2, CARD=50000 TBL: T2)

For this example the exponential backoff appears to be irrelevant:

  5000 * 1000 * 0.001 = 50000.

Now let's add 3k rows to T1 with a SomeId value of 0. Code to do so:

INSERT INTO T1 WITH (TABLOCK)
SELECT TOP 3000 NULL, NULL, 0
FROM   master..spt_values v1,
       master..spt_values v2;

UPDATE STATISTICS T1 WITH FULLSCAN;

Now we have:

C1 = 4000

C2 = 50000

S1 = 0.25

Therefore, the cardinality estimate should be:

  50000 * 0.25 = 12500

Query plan confirms it:

This is the same behavior that you called out in the question. I added irrelevant rows to a table and the cardinality estimate decreased. Why did that happen? Pay attention to the bold lines:

Loaded histogram for column QCOL: [SE_DB2].[dbo].[T1].SomeId from stats with id 2

Selectivity: 0.25

Stats collection generated:

  CStCollFilter(ID=3, CARD=1000)

      CStCollBaseTable(ID=1, CARD=4000 TBL: T1)

End selectivity computation

Begin selectivity computation

Input tree:

...

Plan for computation:

  CSelCalcSimpleJoinWithUpperBound (Using base cardinality)

      CSelCalcOneSided (RIGHT)

          CSelCalcCombineFilters_ExponentialBackoff (AND)

              CSelCalcFixedFilter (0.3)

              CSelCalcFixedFilter (0.3)

Selectivity: 0.00025

Stats collection generated:

  CStCollJoin(ID=4, CARD=12500 x_jtInner)

      CStCollFilter(ID=3, CARD=1000)

          CStCollBaseTable(ID=1, CARD=4000 TBL: T1)

      CStCollBaseTable(ID=2, CARD=50000 TBL: T2)

End selectivity computation

It seems as if the cardinality estimate for this case was calculated as:

C1 * S1 * C2 * S1 / ( S1 * C1)

Or for this particular example:

  4000 * 0.25 * 50000 * 0.25 / (0.25 * 4000) = 12500

The general formula can of course can be simplified to:

C2 * S1

Which is the formula that I claimed above. It seems like there's some cancellation going on that shouldn't be. I would expect the total number of rows in T1 to be relevant to the estimate.

If we insert more rows into T1 we can see the lower bound in action:

INSERT INTO T1 WITH (TABLOCK)
SELECT TOP 997000 NULL, NULL, 0
FROM   master..spt_values v1,
       master..spt_values v2;

UPDATE STATISTICS T1 WITH FULLSCAN;

The cardinality estimate in this case is 1000 rows. I will omit the query plan and the TF 2363 output.

In closing, this behavior is pretty suspicious but I don't know enough to declare if it's a bug or not. My example doesn't match your repro exactly but I believe that I observed the same general behavior. Also I would say that you get a bit lucky with how you chose your initial data. There seems to be a fair amount of guessing going on by the optimizer so I wouldn't get too hung up on the fact that the original query returned 1 million rows which matched the estimate exactly.


How can I move LOB_DATA ( varbinary/image/text) to different Filegroups LOB_DATA : varbinary(max),varchar(max), image , text columns.

I have 4 tables with large amount of LOB_DATA in it. I would like to move LOB_DATA in these tables to different File group in a different hard disk.

I have created a new filegroup and file in this filegroup. I also used  sql command from this question. But varbinary column storage remains where it was before.

-- To move table data to new filegroup
CREATE UNIQUE CLUSTERED INDEX PK_YourTableName
ON dbo.YourTableName(YourPKFields)
WITH (DROP_EXISTING = ON) ON [NewFilegroup]

I use following sql to see filegroup of lob_data.

-- To see lob_data filegroups
SELECT OBJECT_NAME(object_id) as OBJECT_NAME, FILEGROUP_NAME(data_space_id) as
FILE_GROUP_NAME, type_desc
FROM sys.partitions p
JOIN sys.allocation_units a
on p.partition_id = a.container_id
WHERE
type_desc = 'LOB_DATA'

I am using SQL Server 2008.

I have found following solution how-to-move-the-lob-data-from-one-file-group-to-other. Is there exists another approach?

Kimberly Tripp & Paul Randal has a couple of excellent posts on this very subject at sqlmag.com:

http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server-2008-r2/moving-lob-data-142636
http://www.sqlmag.com/blog/sql-server-questions-answered-28/database-administration/moving-lob-data-keeping-table-online-142703


The login already has an account under a different user name When I execute this SQL:

USE ASPState
GO
IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE NAME = 'R2Server\AAOUser')
CREATE USER [R2Server\AAOUser] FOR LOGIN [R2Server\AAOUser];
GO

I get the following error:

  The login already has an account under a different user name.

How do I know what this different user name is for my login account?

This means that the login [R2Server\AAOUser] is already mapped to a user in that database. Or, in other words, another database user is using this login. You can see what database user is using your login with the following query:

use YourDB
go
SELECT su.name as DatabaseUser
FROM sys.sysusers su
join sys.syslogins sl on sl.sid = su.sid
where sl.name = 'test' -- login

PS: a version of the script that doesn't use the compatibility views:

Select sp.name as LoginName, sp.type_desc as LoginType,
    dp.name as DBUser, dp.type_desc as UserType
from sys.server_principals sp
join sys.database_principals dp on dp.sid = sp.sid
where sp.name = 'test' -- your login


The database principal owns a schema in the database, and cannot be dropped message I am trying to delete a principal from the database but can't because it owns a schema. When I go to edit the user, however, the box to uncheck schemae is blue and unremovable.  How can I remove the principal from these schemas?

Try the T-SQL to do this:

alter authorization
on schema::YourSchemaName
to dbo
go

drop user TheUserYouWantToDelete
go

You can't drop a principal that is a schema owner, so the ALTER AUTHORZATION changes the owned schema (I used YourSchemaName, but obviously substitute that with the owned schema in your database) to dbo (likewise, you can change ownership to whatever principal you need in your environment).  That will allow you to drop the previously-schema-owning user (for example purposes I used TheUserYouWantToDelete, but that'll be the now non-owner that you want to drop).


How to Upgrade Master Slave MySQL on AWS RDS? I already have Master Slave configuration on AWS RDS. We want to upgrade 5.5 to 5.6.23. ..What should be correct sequence to Upgrade ?

Upgrade Master ( Using Modify Instance ) --> Upgrade Slave
Upgrade Master  ( Using Modify Instance ) --> Create a new Read Replica ?

Look for Amazon instructions.  With RDS, the instructions are roughly:

Upgrade the Slave first.  Replication works when the Master is 'older'.
Failover, but do not turn on the Slave.
Upgrade the Slave (was Master)
Turn on replication (START SLAVE)
Wait for replication to catch up before using the Slave.


How to connect via config file in mongodb In config file I have:

   systemLog:
        destination: file
        logAppend: true
        path: c:\data\log\mongod.log
    storage:
        dbPath: c:\data\db
        journal:
            enabled: true
    replication:
       replSetName: "rs0"
    net:
       bindIp: 127.0.0.1
       port: 27017
    security:
         authorization: enabled

I'm trying to connect using mongod process like in documentation:

C:\Program Files\MongoDB\Server\3.6\bin> mongod --auth --dbpath /data/db --config C:\Program Files\MongoDB\Server\3.6\mongod.cfg

And getting:

  Error reading config file: No such file or directory
  try 'C:\Program Files\MongoDB\Server\3.6\bin\mongod.exe --help' for more information

Then trying with quotes

C:\Program Files\MongoDB\Server\3.6\bin> mongod --auth --dbpath /data/db --config "C:\Program Files\MongoDB\Server\3.6\mongod.cfg"

and getting:

  2018-07-02T02:49:21.272+0300 I CONTROL  [main] log file "c:\data\log\mongod.log" exists; moved to "c:\data\log\mongod.log.2018-07-01T23-49-21".

Starting mongo, then show dbs and see:

If I'm writting this snippet (without config), everything is fine:

mongod --auth --dbpath /data/db --bind_ip 127.0.0.1

With mongo:

What I'm doing wrong? I appreciate any help.

Solution

To avoid the error like: not master and slaveOk = false need always using the command: rs.slaveOk().
To avoid typing rs.slaveOk() every time need to add rs.slaveOk() in .mongorc.js file check here and here
More detail information.


Removing duplicate entries for each hour I recently asked the same question about MySQL, which has since been migrated to Postgres.
Old question.

To sum it up:

I have a table with an id (type UUID), a timestamp, and some text (row3). How do I retain only one entry per hour for each distinct value of row3?

What I tried is this:

INSERT INTO log_table
SELECT * FROM table1
WHERE id IN (
    SELECT DISTINCT ON(id) id
    FROM table1
    GROUP BY row3, EXTRACT(HOUR FROM "time"), id
);
TRUNCATE table1;

But this, unfortunately, inserts every row from table1.

Generic case

Say you want to retain the values belonging the max(id) for every hour and for every distinct row3 value (but see the UUID section below).  The IDs of these you get with a query like

SELECT max(id)
FROM table1
GROUP BY row3, EXTRACT(hour FROM "time")
;

(This will omit the date from your data, which is what you may want—or not.  In the latter case you may want to use date_trunc('hour', "time") instead.)

Then you can use the above query to populate a table just as you did in your question, or delete everything else from table1 like

DELETE FROM table1
WHERE id NOT IN (
    [the query above comes here]
)
;

UUID

There is no max() for UUID.  Therefore another solution is needed in your case, like this one:

SELECT DISTINCT first_value(id) OVER w
FROM table1
WINDOW w AS (PARTITION BY row3, EXTRACT(hour FROM "time")
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
;

This one applies a window function, and the grouping is achieved by the PARTITION BY clause and DISTINCT.

(Acknowledgment: thanks for the idea for AndriyM!)


PostgreSQL - VACUUM speed (Heroku) My DB have a main table that is has lots of commits on a daily basis, with about 15 indexes on this table.
The table has 25M rows and the dead row count seem to exceed the number of live rows at the moment.

The auto-vacuum seems to run for over a day on this table, and doesn't seem to catch up with the new generated dead rows.

I diverted some actions that I used the DB for filtering to be done in memory, as in, instead of inserting rows and later clean them up using DB queries, I now do it in-memory in-advance.

I'm thinking of removing some indexes since they seem to get scanned to and add a significant overhead.

Any tips on how to improve the speed?

Additional details:

The DB has 8GB RAM, the table is around 30GB, but the indexes are big, each is around 5GB.
The dead row count is now around 40M while the rowcount seems to be stuck exactly the same number for a while 25M (and some change).
I use automatic vacuum tuned up to get triggered frequently; this is the "nightly vacuum" I mention above.

Update:
It seems what I tried is working, previously I was committing lots of rows to the DB, and delete shortly after by launching a cleanup run.
I changed it to filtering the data in advance and not commit data I will cleanup later, causing much less work on the DB and less dead rows.

It seems the auto-vacuum which is starting to catch up slowly, but it takes significant time since each vacuum on this table runs for almost two days.

(unfortunately since I'm on Heroku, I can't control many parameters)

This means you need to tune your autovacuum settings.

Auto vacuum is not the enemy

The default settings for autovacuum will trigger when the 20% rows are dead. This is not a good choice for a large OLTP system. So tune it on Number basis not the percentage.

Like, set auto vacuum when the dead tuple count reaches 10000.

autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 10000
autovacuum_max_workers = 4

OR

you can set this threshold on a particular table.

ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0);
ALTER TABLE t SET (autovacuum_vacuum_threshold = 10000);

In some worst cases auto vaccum will not work. Read why?
The above parameters for Autovacuum for Ad-Hoc vacuum set something like,

vacuum_cost_delay = 20ms
vacuum_cost_limit = 200

vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20

maintenance_work_mem  is also a contributing for this


How to resolve duplicate constraint names in Postgres? On Postgres 9.6, I have a table where a foreign key constraint and a unique constraint have the same name (ultimately due to long distinct names being silently truncated to 63 chars). I'm trying to resolve this name conflict through ALTER TABLE RENAME CONSTRAINT, but that yields an error:

table x has multiple constraints named "y"

Which sounds like in order to rename the constraint I first need to… rename the constraint.
So, how can I do that?
(Side note: the release notes for PostgreSQL 11 have "Fully enforce uniqueness of table and domain constraint names", alluding to this very situation, I suppose, as I discovered while preparing for an upgrade.)
Minimal steps to reproduce:
create table t1 (id uuid primary key);
create table t2 (id uuid primary key);
alter table t1 add constraint oops foreign key (id) references t2 (id);
alter table t1 add constraint oops unique (id);
alter table t1 rename constraint oops to oopsie;

My solution was to drop and recreate the constraints, with one of them given a different name. Postgres will let me drop one constraint by name, but I don't know how it chooses which one or whether it's even deterministic, so the safe route from scripting was to drop both and recreate from there. In the above case, that would go like:
alter table t1
    drop constraint oops,
    drop constraint oops,
    add constraint oops foreign key (id) references t2 (id),
    add constraint oops_unique unique (id);

Fortunately, my table was small enough for this to be a practical solution. If it weren't, I'd try updating pg_constraint directly, as Laurenz suggested.


~15 data sets w/ 25k rows & ~20 columns + quick  one time reports = Access VS SQL? I am working with around 10-15 datasets each containing around 25,000 rows and around 20 columns. Data is text in name/adress/desc and the rest numbers.

I need to be able to calculate certain fields, pull out data on demand for certain list of customers (10, 20, 30) and generally extract data based on certain criteria.

Am I better off using MS Access or using SQL?

Please ask if You need any additional info..

Access is a perfectly fine database system for small scale individual-user apps. Here are some criteria for shifting:

Multi-user applications. Access doesn't do concurrency very well, so a multi-user database - particularly one where multiple users are making modifications to data - will bring Access to it's knees.
Requirement to support advanced SQL functions (windowing, partitioning, MERGE, triggers etc).
Large datasets. Access used to have hard limits on .mdb files at 2GB (IIRC), and I found the likelihood of corruption increases rapidly when the total DB size gets over a gigabyte. (Note that I haven't used Access 2010, so I don't know if things are better now in this regard.)

Note that if you have a front-end built in Access and just want to shift the database away from JET (Access's internal DB engine) onto a "proper" RDBMS, you can do so by migrating the data across and setting up linked tables inside Access to the new data source.


Oracle migration unable to create indexes I want to move the contents of a database instance to another using the expdp and impdp utilities. The source instance is version 12.1.0.1.0 and the destination is 11.2.0.1.0 so I modified the expdp command to include the version:

expdp system@db1/pass schemas=main directory=dmpdir dumpfile=backup-26-11-2015.dmp logfile=backup-26-11-2015.log version=11.2.0.1.0

However when I try to import this dmp file into the 11.2 instance it copies everything except for indexes with this error:

ORA-39083: Object type INDEX failed to create with error:
ORA-06550: line 2, column 1:
PLS-00306: wrong number or types of arguments in call to 'CREATE_INDEX'
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
Failing sql is:
BEGIN
ctxsys.driimp.create_index('ACTCI_T1DX','ACTCI',USER,
'','1','DESCRIPTION','1',
'100','0','8130','1',
'LANGCODE',NULL, NULL, 0,'Y',NULL, NULL, NULL, NULL, NULL);

ctxsys.driimp.set_object('DATASTORE','DIRECT_DATASTORE',0);

ctxsys.driimp.set_object('DATATYPE','CHAR_DATATY

I've noticed that the ending line is missing some code and I'm seeing this occur for every error thrown. Why is the statement cutting off like that?

This is a known bug, and it will be fixed in a future version.

Use CTX_REPORT.CREATE_INDEX_SCRIPT to generate the index scripts, and create them manually.


How to ensure the quick switch from Log shipping to Mirror in SQL Server 2012 Need some Guidance/steps or any reference to link on how safety and quickly can I switch from Log-shipping to Mirroring as we have very small downtime to complete the activity [5-10 mins].

Also how would one ensure that the secondary LSN matches the one with primary so that mismatch might not cause a problem while setting up the mirroring as required above. Because if it does, then all the process of applying log backups will take additional time and goal above would not meet to change LS to mirror quickly.

Please suggest.

Below would be my steps :

disable all backup and copy jobs on primary and secondary.
manually run the copy and restore jobs couple of time to ensure that all the log backups are applied to secondary.
remove logshipping using GUI
restore database on secondary using NORECOVERY - if db is readonly or standby mode.
Enable mirroring using scripts or wizard

this is your logshipping to mirroring conversion.

Make sure you take regular log backups on the principal else your log file will grow.


Error 23 on transaction log backup, full backup works fine I have a database with the database files on the E: drive, and logs files on the D: drive.

I'm getting the following error when trying to run a transaction log backup:

  Read on "D:\LOG\AOA_Log.ldf" failed: 23(Data error (cyclic redundancy check).)

I'm also getting a bad block message in the Windows event log.

A Full database backup works fine.

Can I change the database property to simple restore and shrink the log file?

Can I run chkdsk on the D: drive (need to dismount) without messing up the database?

The server is a VMWare guest with the data residing in a disk array.

To answer the two specific questions in your post:

  Can I change the database property to simple restore and shrink the log file?

You can switch to the simple recovery model and shrink the log.  This might workaround the problem for a little while if it moves the data off of the faulty area.  Or it might cause more problems (if there are more "bad" areas in this drive / array that get written to).

  Can I run chkdsk on the D: drive (need to dismount) without messing up the database?

Again you can do that, but there are some caveats.  Tony mentioned one in a comment:

  You will need to stop SQL Server or detach all databases that have files on D: before you dismount it.

So there will be downtime involved for this.  Tony also mentioned that, before disconnecting everything, you should take a full backup and restore it somewhere else, to make sure the backup was good.  That way, once you have finished chkdsk and resolved any issues there, you can confidently restore from that backup.


How to prevent a login from 'listing' a table or view definition in PostgreSQL? Is there a way to prevent a login from listing the tables and columns in a schema?

I have to give access to a remote login to query on a single view; however, I also must make sure that such login cannot list every object on that schema.

Is there something like MS SQL SERVER's DENY VIEW ANY DEFINITION TO public; on PostgreSQL?

The documentation to the rescue:

UPDATE
[...]
For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables. Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access.

This means, the following would satisfy your requirements, unless alice tries hard enough:
CREATE SCHEMA invisible; -- no privileges granted on this schema to alice or public

CREATE VIEW invisible.iamnothere AS SELECT 'something not very interesting'::text AS data;

GRANT SELECT ON TABLE invisible.iamnothere TO alice;

Now alice will see the following (using psql, and hopefully it is the same in other clients, too):
SELECT * FROM invisible.iamnothere;
              data
────────────────────────────────
 something not very interesting

\dt invisible.*
No matching relations found.

If you are serious
...about preventing your user from seeing other objects, it will be slightly more complicated.  You have to handle the notice in the docs about system tables access.
First, do
REVOKE ALL ON ALL TABLES IN SCHEMA  pg_catalog FROM public, alice;

This, as it suggests, can be disruptive to anyone who is able to browse the schemas through implicitly being a member of public (all roles are members of the latter).  To restore the status quo for them (but not alice), you can do:
CREATE ROLE object_browser;

GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO object_browser;

GRANT object_browser TO bob;

After this, bob will be able to list stuff, while poor alice is confined to querying that single view.


Oracle RAC Failover functionality clarification we are running two node active/active Oracle11g R2 RAC cluster i need to replace public network cables on each node. I am planning to do as given below:

Setup:

OS: oracle Solaris 11.1 Sparc
Database : oracle11g R2 RAC dual node cluster using ASM
Middle tier : Microsoft Dot net apps servers
Client tier :  Internet explorer ( BUI )

Public NW Cable replacement Action Plan:

Prepared and ensured the 2 new Public network cables one for each node
Check and verify the Database status using srvctl
first from node 1 i will remove the cable from both (switch port & server port)
simultaneously and replace it with the new cable with in few seconds.
Check and verify the instance status on the new cable (node1) and wait for 5 minutes
Repeat the steps 2-3 for the node2.
Check and verify the instance status and wait for some time to ensure the both the
instacnes are up and running
Test the application

Note:
Initially check and ensure the Apps servers tnsnames.ora  configuration ( SCAN Names / VIP/node's local ip  ) Using SCAN-Names recommended if not observe carefully the TAF behaviour on the node side and the application side as well )

Kindly review the above steps and guide me.

MShafi - Senior System admin
Abu Dhabi - UAE

Just from Oracle side there is nothing to worry about. You could even do this operation during normal business hours.

BUT! most Applications are not cluster ready. They cannot deal with this situation and will crash. All services are still available since they are migrated to the second node. But the session does not get migrated that's why the client will loose the connection. An application which supports RAC would then reopen the connection and just continue to work.

I would plan a downtime for this operation.

For the future you could bond two network ports for the public interface in failover mode. With that setup you could do the operation without downtime. Note that bonding in load balance mode does not work so well -- at least this is my experience.


MySQL - Is it possible to assign permission to count the number of tables in a database without permissions on individual tables? As part of a script I am trying to count the number of tables that exist in a particular MySql schema.

Using my own user I am using the query

SELECT COUNT(*) FROM information_schema.tables
WHERE table_schema = dbName

However I'd like the user running this script to have as few permissions as possible, specifically I don't want them to have any select privileges on any of the tables belonging to the schema

Is it possible to assign a grant which would allow a user to count the number of tables without having any access to the tables themselves?

One option would be to give the user access to a single non-NULL column that doesn't provide very much information, but will allow COUNT(*) to succeed.

For example:

GRANT SELECT(TABLE_TYPE) ON INFORMATION_SCHEMA.TABLES TO blind_user@localhost;

Now, blind_user@localhost can execute SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES but can't get any private data out of the table itself.


Use jsonb array inside where IN or ANY in PostgreSQL I want to select all products that have active shipments (those have one or more shipment_ids in shipments table).

My product table has a jsonb column called data like this:

{
    "name": "foo",
    "shipments": [1,2,5,7]
}

I thought it would be as simple as something like to this:

... where shipment.id IN (products.data->'shipments')

or

... where shipment.id = any(products.data->'shipments')

but it is not.

After some digging I have managed to get it working, but I do not know if that is the simplest and most efficient solution for this:

select "products".* from "products"
   cross join LATERAL (
  select array_agg(ps.shipment::int) as ids
  from jsonb_array_elements(products.data -> 'shipments') as ps(shipment)
  ) ps
  where exists(select from shipments as "sh" where sh.id = any (ps.ids))
order by id

So do I actually need that cross join? Or there is a way to simply cast the JSON array to a Postgres integer array?

You can do it like this:

SELECT *
FROM products
WHERE EXISTS (
         SELECT 1 FROM shipment
         WHERE products.data @>
               jsonb_build_object('shipments', ARRAY[shipment.id])
      );

This query is still complicated and not very efficient.

The reason for that is the terrible data model. JSON in the database can be useful, but just stuffing all your data into a JSON column is a certain recipe for complicated and slow queries.

You should implement this with a junction table that implements the m-to-n relationship between the tables.

My rule of thumb is that you should use JSON in the database only for columns that are not used a lot inside the database.


How can I get hours worked totaled for a column? I get good results until I try to perform calculations on an alias column. I am aware that that is not possible. However I need a breakthrough.  Can someone lend a hand please :) I have to finish this report and I know the answer to this will help me with many different scenarios as far as MYSQL Queries go.  HELP

SELECT
    `ohrm_user`.`emp_number`,
    `ohrm_attendance_record`.`punch_in_user_time`,
     `ohrm_attendance_record`.`punch_out_user_time`,
     `ohrm_user`.`user_name`,
     time_to_sec(TIMEDIFF (punch_out_user_time,punch_in_user_time)) /60 AS time_to_sec_TIMEDIFF_punc,
     SEC_TO_TIME(SUM(TIME_TO_SEC(ohrm_attendance_record.punch_out_user_time) - TIME_TO_SEC(ohrm_attendance_record.punch_in_user_time))) AS SEC_TO_TIME_SUM_TIME_TO_S,
     TIMEDIFF(MIN(ohrm_attendance_record.punch_in_user_time),
     MAX(ohrm_attendance_record.punch_out_user_time)) AS TIMEDIFF_MIN_ohrm_attenda
FROM
     `ohrm_attendance_record` `ohrm_attendance_record`
      INNER JOIN `ohrm_user` `ohrm_user` ON
     (`ohrm_user`.`emp_number` = `ohrm_attendance_record`.`employee_id`)
WHERE ( `ohrm_user`.`user_name` LIKE 'mel%' )
AND ( `ohrm_attendance_record`.`punch_in_user_time` > '2015-06-29 00:00:00' )
AND ( `ohrm_attendance_record`.`punch_out_user_time` < '2015-07-01 00:00:00' )
GROUP BY `ohrm_user`.`emp_number`,
         `ohrm_attendance_record`.`punch_in_user_time`,
         `ohrm_attendance_record`.`punch_out_user_time`,
         `ohrm_user`.`user_name`
ORDER BY `ohrm_user`.`user_name`,
         `ohrm_attendance_record`.`punch_in_user_time`

I cannot figure out how to calculate total hours for the user and date scope that is specified. The way it currently is the calculations only seem to total up each punch in and out in pairs.  I need a total for the hours worked in the searched period.  I am still learning about aggregates, sub queries and advanced normalization.

If you group by the punch in and out times you will ensure your aggregates do not take place at the user level. Take that out and add a sum around your timediff to aggregate the time worked. Also, Username, unless it is unique and corresponds to Emp Number may cause grouping issues.

SELECT
    `ohrm_user`.`emp_number`,
    `ohrm_attendance_record`.`punch_in_user_time`,
     `ohrm_attendance_record`.`punch_out_user_time`,
     `ohrm_user`.`user_name`,
     SUM(time_to_sec(TIMEDIFF (punch_out_user_time,punch_in_user_time)) /60) AS time_to_sec_TIMEDIFF_punc,
     SEC_TO_TIME(SUM(TIME_TO_SEC(ohrm_attendance_record.punch_out_user_time) - TIME_TO_SEC(ohrm_attendance_record.punch_in_user_time))) AS SEC_TO_TIME_SUM_TIME_TO_S,
     TIMEDIFF(MIN(ohrm_attendance_record.punch_in_user_time),
     MAX(ohrm_attendance_record.punch_out_user_time)) AS TIMEDIFF_MIN_ohrm_attenda
FROM
     `ohrm_attendance_record` `ohrm_attendance_record`
      INNER JOIN `ohrm_user` `ohrm_user` ON
     (`ohrm_user`.`emp_number` = `ohrm_attendance_record`.`employee_id`)
WHERE ( `ohrm_user`.`user_name` LIKE 'mel%' )
AND ( `ohrm_attendance_record`.`punch_in_user_time` > '2015-06-29 00:00:00' )
AND ( `ohrm_attendance_record`.`punch_out_user_time` < '2015-07-01 00:00:00' )
GROUP BY `ohrm_user`.`emp_number`,
         --REMOVE -`ohrm_attendance_record`.`punch_in_user_time`,
         --REMOVE -`ohrm_attendance_record`.`punch_out_user_time`,
         `ohrm_user`.`user_name`
ORDER BY `ohrm_user`.`user_name`,
         `ohrm_attendance_record`.`punch_in_user_time`


update records that are there in .csv file which will be given as input I have .csv file with list of Ids, using those IDs in file- giving them as input, I need to update the record in table,

I have around 4000 Ids so 4000 update statements are not possible, need a procedure to automate this.

can you please tell me if there is any procedure to make this happen.

You can create an external table that references the data in your CSV.
You'll need a directory object creating that points to the location on the file system; in this case MY_DIR.

CREATE TABLE update_ids_ext
(
  rec_id               NUMBER,
  update_value         VARCHAR2(100)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY my_dir
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY '\r\n'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY '"'
      MISSING FIELD VALUES ARE NULL
      (  rec_id ,
         update_value
      )
        )
     LOCATION (MY_DIR:'update_ids.csv')
  )

You can then just refer to your CSV file as if it were a table and perform an update however you like.
I've done an example using a merge and another using an update but the choice is yours.

MERGE INTO target_table tgt
 USING (SELECT rec_id, update_value
          FROM update_ids_ext ) src
 ON (tgt.rec_id = src.rec_id)
WHEN MATCHED THEN UPDATE SET tgt.column_value = src.update_value;

UPDATE target_table tgt
   SET ( tgt.column_value ) = (SELECT src.update_value
                                 FROM update_ids_ext src
                                WHERE tgt.rec_id = src.rec_id)
 WHERE EXISTS (SELECT 1
                 FROM update_ids_ext src
                WHERE tgt.rec_id = src.rec_id)

You probably need to look at the documentation for directory objects and external tables and possibly merge and update.


How do I select distinct on one column and return all other columns? I am using vb.net/access/Microsoft.Jet.OLEDB.4.0/DbaseIV to do this. The syntax varies between vb.net and SQl Server so I am having trouble.

I need to select one of each version in the data (using the code field) while keeping all the columns in the final output. I am selecting distinct on a code field but I cannot figure how to return the rest of the columns.

The issue is with this line

Dim sql2 = "SELECT DISTINCT " & Field1 & " INTO NewTable " & " from " & TableName

Here is all of my code:

**Dim ofd As New OpenFileDialog
    With ofd
        .Filter = "DBASE File (*.dbf)|*.dbf"
        .Multiselect = False
        .CheckFileExists = True
    End With

    If ofd.ShowDialog() = DialogResult.OK Then
        Dim fi As New IO.FileInfo(ofd.FileName)
        Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBase IV;Data Source='" _
                                            & fi.DirectoryName & "'")
        Dim TableName As String = fi.Name.Substring(0, fi.Name.Length - fi.Extension.Length)
        Dim cmd As New OleDb.OleDbCommand(TableName, cn)
        cmd.CommandType = CommandType.TableDirect

        cn.Open()
        Dim rdr As OleDb.OleDbDataReader = cmd.ExecuteReader
        dt.Load(rdr)

        SelectField.ShowDialog()

        Dim dBaseConnection As New System.Data.OleDb.OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & path & “;” & “Extended Properties=dBase IV”)

        dBaseConnection.Open()

        Dim SQLCreateCommand As String

        Dim sql2 = "SELECT DISTINCT " & Field1 & " INTO NewTable " & " from " & TableName

        Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(sql2, dBaseConnection)

        dBaseCommand.ExecuteNonQuery()
        dBaseConnection.Close()

        cn.Close()
        cn.Dispose()
    End If**

This is just an example of my output i need to select all columns using an asterisk or something....

Some SQL databases let you do something like:

SELECT
  DISTINCT ON(Version)
  FirstName, LastName
  /* INTO NewTable */
FROM
  Table ;

This "DISTINCT ON(x)" is implemented only by a limited number of databases. I don't think it is implemented by "Access", and I would think that most probably is not implemented by the "dBase IV" interface.

The SQL used by Access (don't know if the dBase IV variant will work) might let you do something equivalent:

SELECT
    Version, first(FirstName) AS FirstName, first(LastName) AS LastName
FROM
    (SELECT
       *
    FROM
         TableName
    ORDER BY
          Version  /* and whichever columns make sense for your purpose */
    ) AS OrderedTable
GROUP BY
    Version ;

First is an aggregate function, that, together with GROUP BY, will make sure that you get one value for each group. This value will be the "first one found for the group", so you need to order first your table in a sensible way.

If none of these forms work... My guess is that you will have to solve the problem outside of SQL, getting a RecordSet into your program, looping checking, and inserting into NewTable.


Trying to Count Multiple Columns SQL I'm trying to count multiple columns in a select statement for a class I'm in.

Everything is coming from the same table. I want to know how many times that person paid something and how many times they received something.

There is an ID for each time something is given, but that same ID would be for when someone receives something. I was trying to do the following, but I keep getting Scalar Subquery produced more than one element.

Select Distinct A, Receiverid, Giver, C, D, sum (received amount)
(Select Count(paidid) FROM Table WHERE A ='12345' GROUP BY Giver) as NumberGiven
(Select Count(Receiverid) FROM Table WHERE A ='12345' GROUP BY Receiverid) as NumberReceived
FROM Table
WHERE A = '12345'
Group By A, ReceiverID, Giver, C, D

You are getting the error because you are grouping by Giver in NumberGiven (the same error occurs for NumberReceived). A simple example:

x   y
1   1
1   2
2   3

select count(1) from T group by x

will result in:

2  -- the number of rows for group x=1
1  -- the number of rows for group x=2

Your subqueries probably contain multiple groups, and you, therefore, get more than one count, which leads to your error.

Since I don't know what your table looks like, I can only guess what the query should look like:

Select A, C, D  -- distinct not necessary because of group by
      , sum(received amount) as received_amount
      , count(case when receiver = A then 1 end) as #received
      , count(case when Giver = A then 1 end) as #given
NumberReceived
FROM Table
WHERE A = '12345'
Group By A, C, D


db2_install do not have write permission on the directory or file This is the error I encountered (I ran the db2_install and everything else as root,):

DBI1288E  The execution of the program /home/DB_SERVER/ibm/db2/v10.5 failed. This
      program failed because you do not have write permission on the
      directory or file .

I tried to change the access permission of the path:

chmod -R a+rw /home/DB_SERVER/ibm/db2/v10.5

And then I could create a new file in that folder:

vi test

Result of ll in that directory:

total 1
-rw-rw-rw- 1 root root 12 Nov 23 00:24 test

But when I ran the db2_install again, it failed for the same reason.

PS: I tried to change permission of the setup file too

chmod -R a+rwx db2Setup/

Result of ll

total 72
drwxrwxrwx  6 root root 4096 Nov 23 00:10 db2
-rwxrwxrwx  1 root root 5349 Nov 23 00:10 db2ckupgrade
-rwxrwxrwx  1 root root 5302 Nov 23 00:10 db2_deinstall
-rwxrwxrwx  1 root root 5172 Nov 23 00:10 db2_install
-rwxrwxrwx  1 root root 5136 Nov 23 00:10 db2ls
-rwxrwxrwx  1 root root 5154 Nov 23 00:10 db2prereqcheck
-rwxrwxrwx  1 root root 5154 Nov 23 00:10 db2setup
drwxrwxrwx 10 root root 4096 Nov 23 00:10 ibm_im
-rwxrwxrwx  1 root root 5190 Nov 23 00:10 installFixPack
drwxrwxrwx  4 root root 4096 Nov 23 00:10 nlpack
-rw-r--r--  1 root root    8 Nov 23 01:00 test

So I have no idea what's wrong? How to fix this?

if you install on mounted fs ensure it's not mounted with the nosuid and noexec parameters.
db2 binaries require suid file permissions.
remove v10.5 directory if you created it manually
and set chmod 755 on parent directory


SQL Server transaction log backups: test whether tail log follows last known log backup We are using SQL Server with full recovery mode.  Given a full backup and a series of log backups, we would like to be able to check whether the log chain is complete from the last full backup to the current tail log.  (Without actually restoring these backups; the purpose here is to test the consistency of the backups.)

I already know how to do this for the existing backups: using RESTORE HEADERONLY I get the FirstLSN and LastLSN of every file, which can be compared for consecutive files, in order to determine whether they are compatible.

However, I don't know how to check whether the tail log follows the last log backup.

If I had the FirstLSN of the tail log, I could compare it to the LastLSN of the last log backup.  But how can I obtain the FirstLSN of the tail log?

I need a solution that works from SQL Server 2005 upwards (ideally using t-sql). So far, I have searched Google to no avail.  Btw. I first posted this on stackoverflow; but migrated it here since it was flagged off-topic there.

EDIT

I tried the two provided solutions on a small example (SQL Server 2005, 9.0.5057):

BACKUP DATABASE TestDb TO DISK = 'C:\temp\backup test\Full.bak'

-- fire some update queries

BACKUP LOG TestDb TO DISK =  'C:\temp\backup test\Log1.bak'

-- fire both queries from the provided answers:
-- Martin Smith's answer yields: 838886656088920652852608
-- Shawn Melton's answer yields: 46000000267600001

RESTORE HEADERONLY FROM DISK = 'C:\temp\backup test\Log1.bak'
-- yields: 46000000267600001

So it appears the first one is off by several orders of magnitude.

I then did the same test on SQL 2008 SP1 (10.00.2531), where both queries yielded the correct answer.

I turned to my copy of SQL Server 2008 Internals and the DMV sys.database_recovery_status was pointed out to find the first LSN of the next log backup. Which going by BOL the column last_log_backup_lsn provides you with:

  Log sequence number of the most recent log backup. This is the end LSN of the previous log backup and the starting LSN of the next log backup.
  NULL= No log backup exists. The database is offline or the database will not start.

Just to mention as well that Kalen also brings up the point that you will get a NULL value if the database is in SIMPLE recovery mode (autotruncate mode) or if no log backup exist.

  But how can I obtain the FirstLSN of the tail log?

Without actually backing up the tail log of a database (don't have an test instance to try this on) you could logically conclude that the value returned in the column mentioned would be the first LSN of the next log backup, in your case the tail.

So executing the following will return the value I believe you are looking for:

SELECT
   last_log_backup_lsn
FROM
   sys.database_recovery_status
WHERE
   databse_id = DB_ID('MyDb')

This DMV is available starting in SQL 2005.

EDIT
Unless you read the BOL link, please note that this DMV will only return values to databases that are online, or opened as BOL references it. If a failure occurs that requires you to take a tail log backup of a database you will not be able to verify this value through the above code unless the database is accessible; which in a failure it probably would not be.


Unable create partition by trigger 1) I have table

CREATE TABLE tor
(
  events_date timestamp without time zone NOT NULL,
  message text
  CONSTRAINT tor_pkey PRIMARY KEY (events_date, message)
)PARTITION BY RANGE (events_date);

2) There is a default partition where the data gets, if there is no partition where you can put it

CREATE TABLE tor_part_default partition OF tor default;

3) There is a trigger on an INSERT to tor_part_default:

CREATE OR REPLACE FUNCTION create_next_part()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    partname VARCHAR(11);
    startdate VARCHAR(10);
    enddate VARCHAR(10);
BEGIN
    partname = 'tor_'||to_char(NEW.events_date, 'YYYYMM');
    startdate = to_char(NEW.events_date, 'YYYY-MM-01');
    enddate = to_char(TO_DATE(startdate,'YYYY-MM-DD') + interval '1 month','YYYY-MM-DD');
    RAISE NOTICE '    Start date: %',startdate;
    RAISE NOTICE '      End date: %',enddate;
    IF NOT EXISTS (SELECT 1 FROM   information_schema.tables WHERE  table_name = partname)
    THEN
        RAISE NOTICE 'Partition created: %',partname;
        EXECUTE 'CREATE TABLE '||partname||' partition OF tor FOR VALUES FROM ('''||startdate||''') TO ('''||enddate||''')';
        EXECUTE 'INSERT INTO '||partname||' VALUES(NEW.*)';
    END IF;
    RETURN NEW;
    END;
    $BODY$;

CREATE TRIGGER tor_create_next_part
    BEFORE INSERT
    ON tor_part_default
    FOR EACH ROW
    EXECUTE PROCEDURE create_next_part();

When I try to insert data, as a result I get an error:

ERROR:  cannot CREATE TABLE .. PARTITION OF "tor" because it is being used by active queries in this session
CONTEXT:  SQL statement "CREATE TABLE tor_201606 partition OF tor FOR VALUES FROM ('2016-06-01') TO ('2016-07-01')"
PL/pgSQL function create_next_part() line 16 at EXECUTE

What am I doing wrong or is there another way to create partitions automatically?

It's not possible to modify table via trigger.

So I have 2 options:

Add partition via cron.
Add partition via code (check default partition).


What happens when I change a table column's data type to increase its data length in SQL Server? I believe SQL Server would create a new column under the hood and copy the original data to the newly created column. And the original space for the old column cannot be reclaimed before the table is rebuilt.

But I can't come up with a general picture in my head about what exactly is going on.

Say I have a simple table defintion:

CREATE TABLE ExampleTbl
(
 ID INT
 ,Bool BIT
)

And a certain page is completely filled with ExampleTbl's data rows. What happens if I change Bool column data type to int?

Since the page is full, does it mean the the newly created column will have to be stored in some other data page? I originally thought it should be in the same page because SQL Server will always try to use in-row allocation when the data row fits into a page(less then 8000 bytes).

But then it may involve a lot of moving around of the data rows to allocate space so the new column can be inserted into the data page, and some rows will have to be popped out the page and stored in another one, if SQL Server needs to stick to in-row allocation. But this behavior is obviously detrimental to system performance.

So exactly what happens behind the scenes when I change a column data type to one with a wider domain of values?

Thanks!

Changing a column data type to a wider type will require rows to be moved to other pages once a page is full. This behavior is similar to DML updates of variable length columns to a wider value. This can be observed with this script that uses the undocumented sys.dm_db_database_page_allocations TVF available in SQL Server 2012 and later:

CREATE TABLE ExampleTbl
(
  ID INT
 ,Bool BIT
);
WITH
    t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1000 AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
INSERT INTO ExampleTbl WITH(TABLOCKX)
SELECT num, 1
FROM t1000
WHERE num <= 578;

--single page
DECLARE
      @database_id int = DB_ID()
    , @object_id int = OBJECT_ID(N'dbo.ExampleTbl')
    , @allocated_page_file_id int
    , @allocated_page_page_id int;
SELECT *
FROM sys.dm_db_database_page_allocations(@database_id, @object_id, 0, 1, 'DETAILED')
WHERE
    page_type_desc = 'DATA_PAGE';
GO

ALTER TABLE dbo.ExampleTbl
    ALTER COLUMN Bool INT;
GO

--3 pages, with 2 100% full and last 50% full
DECLARE
      @database_id int = DB_ID()
    , @object_id int = OBJECT_ID(N'dbo.ExampleTbl')
    , @allocated_page_file_id int
    , @allocated_page_page_id int;
SELECT *
FROM sys.dm_db_database_page_allocations(@database_id, @object_id, 0, 1, 'DETAILED')
WHERE
    page_type_desc = 'DATA_PAGE';
GO

Heaps are particularly nasty when DML/DDL increases row length because the original row is retained with a forwarding pointer. Consequently, more space will be required that the equivalent change against a table with clustered index.


Postgres 9.4+: JSONB instead of EAV I'm figuring out a way to structure a new PostgreSQL database, and I'm curious if I could use a JSONB column instead of the EAV model for storing properties of an entity. This seems like a flexible way to store these kinds of things.

In the EAV case, I would have 3 tables:

materials (mat_id, mat_name, mat_descr)
material_properties (prop_id, prop_name, prop_desc)
material_property_values (mat_id, prop_id, val)

Each material can have a different set of properties.

Are there any reasons why I shouldn't just add a column mat_props JSONB to my materials table, and store all properties there as {"name":"value"}? This way, querying properties as columns would be as easy as:

SELECT mat_name, mat_props->'name' as propval FROM materials

instead of joins or subselects.

Also, could I add this column in the materials table or would it be much more performant to keep it in a separate table as:

material_properties (mat_id, properties JSONB)

JSONB may be easy to read, but it's complicated and inefficient to write into.

See for example this question: PostgreSQL update and delete property from JSONB column, on how it looks like. It's an order of magnitude harder than an update/delete with classic EAV tables.

Possibly when you'll have written the parts to append/merge/delete key/value pairs, the elegance or simplicity of that JSON-based design are going to seem much less obvious. As for the performance, I'm betting on it being much worse.

At the storage level, any write into one property in a JSON structure will require a rewrite of the entire column (and in fact of the containing row), with the same cost as if all properties changed.
This is not optimal in terms of I/O size and  pressure on vacuum.

This problem is mentioned on S.O in another question with some more links and references:

How to perform update operations on columns of type JSONB in Postgres 9.4

There's ongoing work to ease JSONB updates for the programmer. jsonbx provides functions and operators that can help with 9.4; presumably these will be integrated into PostgreSQL core in future versions. But the large I/O cost of a small update inside a large JSON object will remain.

jsonbx was demonstrated in a recent PG conference (youtube links):

Update and Delete operations for jsonb (part 1 of 2)
Update and Delete operations for jsonb (part 2 of 2)


Does T-SQL have a Schema search path? In T-Sql, a user has a single default schema. Is there a concept of schema search path similar to a postgres search_path?

The question behind the question is, if I use schemas as namespaces for objects, will that mean using qualified names in all code?

No. SQL Server will first check the user's default schema for unqualified object references, and then the dbo schema. If not found at this stage, then you get an error.
I've been searching high and low now, but I can't find a reference for this in the product documentation.  And it is even hard to find anybody written about this over the whole interweb. I did, however, find this from Bob.
Max Vernon wrote a post showing how this works here.
Aaron Bertrand has some interesting advice about always specifying the schema, here


Log Shipping Standby mode I have setup log shipping with standby mode to ship every 15 minutes, but when the log is restored on the secondary server it kills any currently executing queries.

Is it possible to setup the transaction log restore to start only after any currently running queries finish and to not allow any new connections to the secondary while the log is restoring?

But it possible to setup to restore transaction log after finish of
  current running and this interval time no other user connect secondary
  database.

Yes it's possible, you can read more about it here: Using Secondary Servers for Query Processing


Unique relationship attribute for every pair of entities I have entities "Student" and "Course" and they are connected with relationship many-to-many called "Exam". The relationship "Exam" has an atribute "Grade". For this ER model, I have made a relational schema:
Student(StudentID, StudentName, DoB)
Course(CourseID, CourseName)
Exam(StudentID, CourseID, Grade)

Can someone explain how would you put in a relational schema the fact that a student can't get multiple grades for a single course? For example, we can't have records like (Student1, Course1, Grade1) and (Student1, Course1, Grade2).

You would create a unique constraint on exam (StudentId, CourseId) this would usually be achieved with a unique index on that table that covered the two columns.
Here's a quick demo in MySql


Automate COPY command - Postgres 9.2 I've a backup table with several distinct IDS... I wanna do a BACKUP of those rows, by ID.

The function has to be executed by a limit of account_id ( select dump(21); - Where 21 = number of account_id to be touched by the function )
Each account_id must have a different file name. Examples below:

Example:

COPY
(
SELECT * FROM backup_table WHERE id = 1112 AND status = 1
)
TO '/var/lib/pgsql/1112.sql';

COPY
(
SELECT * FROM backup_table WHERE id = 1113 AND status = 1
)
TO '/var/lib/pgsql/1113.sql';

COPY
(
SELECT * FROM backup_table WHERE id = 1114 AND status = 1
)
TO '/var/lib/pgsql/1114.sql';

Question:

Can I create maybe a function to do that automatically? There are thousands of those IDS and would take ages by doing manually.

UPDATE 1:

Tried to do:

CREATE or REPLACE FUNCTION dump(integer)
RETURNS integer AS $$

declare
   crtRow record;
begin
      FOR crtRow in execute 'select account_id from backup_table WHERE migrated = 1 AND account_id = '|| $1

      LOOP
    COPY
        (SELECT * FROM backup_table WHERE migrated = 1 AND account_id = crtRow.account_id)
    TO '/var/lib/pgsql/backup/%s.sql';
    end loop;
    return integer;
end

$$ language 'plpgsql';

Each account_Id would have a file with its ID
When calling the function, I want to specify the numbers of account_Ids I wanna do the dump

However it isn't working...

UPDATE 2:

CREATE or REPLACE FUNCTION function_1(rows integer)
RETURNS void AS $$

declare
   crtRow record;
begin
      FOR crtRow in EXECUTE 'select DISTINCT(account_id) from backup_table WHERE migrated = 1 AND account_id IN '|| $1

      LOOP
    COPY
        (SELECT * FROM backup_table WHERE migrated = 1 AND account_id = crtRow.account_id)
    TO '/var/lib/pgsql/' || crtrow.account_id || '.csv';
    end loop;
end

$$ language 'plpgsql';

Error:

  ERROR:  syntax error at or near "||" LINE 12:     TO '/var/lib/pgsql/'
  || crtrow.account_id |...

UPDATE 3: amacvar's answer

CREATE or REPLACE FUNCTION function_1(rows integer)
RETURNS void AS $$

begin
execute 'COPY
(
SELECT * FROM backup_table WHERE id = ' || rows || 'AND status = 1
)
TO ''/var/lib/pgsql/'||rows||'.sql''';

end

$$ language 'plpgsql';

The above function based on the amacvar's answer works. The problem is that I have to specify the id number...

Example:

`select function_1(25)` - Where 25 = id

That isn't good for me, as I have thousands of ids and can't specify them manually.

  What I need is:

  The same function above, but with a LIMIT of IDS to be specified when
  the function is called:

EXAMPLE:

select function_1(100) - WHERE 100 = numbers of id to be copied

How can I do that?

The scripting answers above will obviously work.

But, assuming you have to, have to, have it executed from within the DB (let us know why :) ), the code below (minus the loop, etc.) should get you on your way.

You were very close btw.

CREATE or REPLACE FUNCTION function_1(rows text)
RETURNS void AS $$
begin
  execute 'COPY (
    SELECT *
      FROM backup_table
     WHERE id = ' || rows || '
       AND status = 1
  ) TO ''/var/lib/pgsql/'||rows||'.dat''';
end
$$ language 'plpgsql';

EDIT 1: function loop example and formatting

CREATE or REPLACE FUNCTION function_loop(rows_arr text[])
RETURNS void AS $$
declare
  crtRow text;
begin
  ForEach crtRow in array rows_arr
  LOOP
     perform function_1(crtRow);
  end loop;
end
$$ language 'plpgsql';

Executed as

select function_loop('{1,2,3}');

Please note there are no checks being performed here.

E.g. This will create a zero byte 3.dat even if there is no id=3 in the table

EDIT 2: A more extensible option for thousands of id would be

CREATE or REPLACE FUNCTION function_loop1(startId int, endId int)
RETURNS void AS $$
declare
  allIds text[];
  crtRow text;
begin
  select array_agg(id::text) into allIds
    from backup_table
   where status=1
     and id between startId and endId;
  ForEach crtRow in array allIds
  LOOP
    perform function_1(crtRow);
  end loop;
end
$$ language 'plpgsql';