Same table alias for two different tables (Oracle) This example has the same table alias used for two different tables.  I don't understand why this is allowed by Oracle, and if allowed, how the results make any sense.

create table Table_A (x number);
create table Table_B (x number);
insert into Table_A values (1);
insert into Table_A values (2);
insert into Table_B values (2);
insert into Table_B values (3);

select * from Table_A ;
         X
----------
         1
         2
2 rows selected.

select * from Table_B ;
         X
----------
         2
         3
2 rows selected.

select *
  from Table_A  T
  join Table_B  T
    on T.x = T.x;
         X          X
---------- ----------
         2          2
         2          2
         3          3
         3          3
4 rows selected.

Bug 25342699 : WRONG RESULTS WITH ANSI JOIN USING AND IDENTICAL TABLE ALIASES

The bug is "still being worked on".

The workaround is obvious: use different aliases.

Non-ANSI join throws an error as expected.

Seems to be fixed in 18.1 and 18.2 as well.


SQL Server AG - Is there any case for un-clustering prior to certain operations? I have a software company asking me to remove a database from a synchronous availability group prior to them running their upgrade scripts.

They are convinced that this will increase the execution time. Are their any DDL or DML queries that would take significantly longer when clustered?

What I want to know is: If I do an operation that takes 5 minutes on a non clustered database, will the same take 10 minutes in a cluster, or the same amount + some minimal overhead? I'm guessing it's not going to double or even get that close, but I was hoping for some experienced insight.

Yes they are correct it will increase the execution time. As the comments state though there is no measurable way to determine this unless you have a matched test/stage environment and try it both ways (which would also be a best guess since you cannot know latency and bandwidth of your network and the load on the nodes).

I see this as the company asking you to trade HA and your time (adding the DB back to the AG) to save some indeterminate, likely small, overhead for their scripts to run faster. They better have a pretty good reason to trade that.


MySQL Replication: Seconds Behind Master keep growing We have set up a new slave server for our production database. Since than the Seconds_Behind_Master keep growing slowly.

This is the slave output:

           Slave_IO_State: Waiting for master to send event
              Master_Host: notimportant
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.003790
      Read_Master_Log_Pos: 42585179
           Relay_Log_File: mysqld-relay-bin.002798
            Relay_Log_Pos: 32374374
    Relay_Master_Log_File: mysql-bin.003492
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 32374215
          Relay_Log_Space: 31350956440
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 1448477
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
         Master_Server_Id: 6524
              Master_UUID:
         Master_Info_File: /var/lib/mysql/master.info
                SQL_Delay: 0
      SQL_Remaining_Delay: NULL
  Slave_SQL_Running_State: Reading event from the relay log
       Master_Retry_Count: 86400
              Master_Bind:
  Last_IO_Error_Timestamp:
 Last_SQL_Error_Timestamp:
           Master_SSL_Crl:
       Master_SSL_Crlpath:
       Retrieved_Gtid_Set:
        Executed_Gtid_Set:
            Auto_Position: 0

Show processlist on SLAVE:

|    1 | system user |           | NULL | Connect | 2143813 | Waiting for master to send event | NULL             |
|    2 | system user |           | NULL | Connect | 1448477 | Reading event from the relay log | NULL             |
| 1628 | root        | localhost | NULL | Query   |       0 | init                             | SHOW PROCESSLIST |

On MASTER side:

|  7947837 | repl       | host-vm:59420     | NULL          | Binlog Dump | 2143817 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |

On MASTER the show slave hosts:

+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
|      3410 |      | 3410 |      6524 |
| 347643210 |      | 3306 |      6524 |
+-----------+------+------+-----------+

Does anyone has an idea what's going on and how can I fix it?

The solution was to add the following to my.cnf:

innodb_flush_log_at_trx_commit = 2

Our server was slow in I/O and the row above reduced the disk usage.

It is not recommended for bank databases, but recommended to all others.

Phrased differently:  =1 flushes at then end of each transaction, thereby making it ACID-compliant.  =2 flushes to disk every second, thereby being much less I/O-intensive.


MySQL - Delete older Records from a huge table I have some huge tables which have around 10years data, and each table has approx 10000000+ rows.

Basically, I want to remove older than 2 years data and make a copy of deleted data in a separate table(I'll export the separate table to CSV and drop it later)

I found this great article: Archive huge record

I want the same thing, but I don't have an ID column in my tables.

So the process should like this,

Insert a single row in my separate table, then delete the row from the main table; commit.

Then, repeat the same step.

Its my main production table and its always busy, so no locks, blocks, and deadlock.

Can anyone suggest a better approach for this?

I have already asked a question about archival, but here I don't want to partition the table.

Since you want to archive most of the table, here's what I recommend.  And it will facilitate subsequent archivings.

This assumes

The table has a DATETIME or TIMESTAMP; I'll call it dt in the code below.
New rows will be INSERTed in chronological order or nearly so.  (This assumption may not matter.)
You can take some downtime, roughly equivalent to twice the time it takes to scan the entire table.  If you cannot handle this much downtime, then other (messier) techniques may be possible.

Setup:

SELECT @cutoff = CURDATE() - INTERVAL 2 YEAR;  -- need same cutoff twice
CREATE TABLE new LIKE real;   -- this will become `real` later.
ALTER TABLE new ADD some partitioning ENGINE=InnoDB

The partitioning will be BY RANGE(TODAYS(dt)) with 26 monthly partitions.  See http://mysql.rjweb.org/doc.php/partitionmaint for discussion of what they will look like.  See below for future archivings.

Shrink real to the last 2 years:

-- Stop writes to the table `real`
-- Note:  This will take a long time
INSERT INTO new SELECT * FROM real WHERE dt >= @cutoff;  -- 2 of 10 years = 20% of table?
RENAME TABLE real TO old, new TO real;
-- Allow writes to the table `real`

Archive older stuff:

SELECT ... INTO OUTFILE '...csv' FROM old WHERE dt < @cutoff;

Future archivings:

Now that you have the table partitioned, you need to do some monthly maintenance:

Use "transportable tablespaces" to remove the oldest partition from real and turn it into a csv.  (Perhaps ALTER TABLE foo ENGINE=CSV;?)
REORGANIZE PARTITION future INTO next-month, future to slide the timescale forward.

These steps will be very fast, and have no impact on inserts into real (unlike the original split).


Is there a problem creating only differential backups every hour? I have three databases which are not big. Every differential backup is roughly 50MB, then we have a full backup at midnight.

Is there any problem doing this?

I could take the full backup then logs, then diffs like 4 or 5 times a day, but storage is not a problem with this instance. I can afford to lose 1 hour of data, that's no problem, that's why I'm creating all of the diffs (I'm using full recovery mode). Then I can restore only the full + diff , instead of full + log + log + log + log + log + log + log
.

Storage is not why you take log backups.  You take log backups when the database is in full recovery model, and you need point-in-time recovery between full or incremental backups.

If your business can afford only 1 hour of lost data, then I'd typically setup nightly full backups for smaller databases, with log backups every 30 minutes during business hours (or even 24-hours per day).

If you had the database in simple recovery model, and each database was only a couple of hundred megabytes, you could simple take full backups every hour, or half hour.

Essentially, the decision comes down to answering these questions:

What is my recovery point objective?
What is my recovery time objective?

See Wikipedia for great details about what those two things actually mean.

If you're storing high-value, business-critical data in those databases, you should understand log backups, and the transaction log and recovery in general (link to my blog), to avoid being in a situation where you're unexpectedly missing data, or down for an extended period of time.

In your case, I would also do a log backup after every diff backup just to ensure the log doesn't grow out of control.


Postgres Case-Insensitive search for Jsonb column tag keys I have a requirement to obtain the rows which match given tags key and value case-insensitively.
Here: Key search should be case insensitive and values may be String or Array of String.
Right Now, I am using following Query :
Database : Postgres
select * from my_table_name where jsonb_contains(lower(to_jsonb(jsonb_extract_path(tags,'key1'))::TEXT)::jsonb, to_jsonb('"value1"'::jsonb))
But it is searching key as case sensitive manner.
For Example:
above query should return all records having key is (key1,Key1,KEY1) and value is 'Value1'
Can some one help me on this?

You need to iterate over all key/value pairs in order to compare them using lower()
Something along the lines:
select mt.*
from my_table_name mt
where exists (select *
              from jsonb_each_text(mt.tags) as t(tag, value)
              where lower(t.tag) = 'key1'
                and lower(t.value) = 'value1');

This assumes your column tags is defined as jsonb (which it should be). If it's a json you need to use json_each_text() instead.


Get time format in PostgreSQL If I want get Ubuntu system time, I have two options:

$ cat /etc/timezone
US/Eastern

$ date
Sun Sep 15 14:45:02 EDT 2013

How can I find out if PostgreSQL is using utc or a utc offset, such as US/Eastern?

You can use current_setting() function to get the TIMEZONE setting.

SELECT current_setting('TIMEZONE') TZ;

Sample output:

# SELECT  current_setting('TIMEZONE') TZ;
     tz
------------
 US/Eastern
(1 row)

Here is SQLFiddle demo


Changing storage engine to InnoDb from MyIsam is dropping records from table Currently our Database all tables are using MyIsam as a storage engine. We tried changing storage engine of our mysql tables from MyISAM to InnoDB from PHPMyAdmin operations tab. However during the change table records are dropped. So what can be the possible factor causing this problem.

This is what we tried:

We converted test_user table which was having storage engine MyIsam and was having total records 11k. When we converted it into InnoDB the records droped to 9k. This happened for many tables. Not sure what we are doing wrong.

Are you are using SHOW TABLE STATUS to determine that there is a loss?  Don't trust it.  The "Rows" is an estimate in the case of InnoDB.  Use SELECT COUNT(*) FROM tbl to get the exact row count.

More tips on the conversion:  http://mysql.rjweb.org/doc.php/myisam2innodb


Insert from another table, then update using Identity I am changing the database schema and migrating data that was originally stored in one table to two tables.

Old schema:

CREATE TABLE [dbo].[X](
    [Id] TINYINT NOT NULL PRIMARY KEY,
    [SomeData] VARBINARY(MAX) NULL,
)

New schema:

CREATE TABLE [dbo].[X](
  [Id] TINYINT NOT NULL PRIMARY KEY,
  [SomeDataId] BIGINT,
)
CREATE TABLE [dbo].[SomeData](
  [Id] BIGINT NOT NULL PRIMARY KEY IDENTITY,
  [Binary] VARBINARY(MAX) NOT NULL
)

I know the steps are:

Create the new column [SomeDataId] on [X]
Copy data from [X].[SomeData] to [SomeData].[Binary]
Remove column [SomeData] on [X]

I am having issue on how to complete step 2 in T-SQL only. I know I can:

Use SCOPE_IDENTITY() to get the inserted identity seed
Use OUTPUT INSERTED.Id to get the inserted identity seed
Use INSERT INTO ... SELECT ... FROM to copy data from one table to another

However I cannot figure out how to update the relevant row on [X] with the identity seed just inserted, without using some kind of for-loop logic which sounds silly.

While creating column SomeDataId add identity

alter table dbo.x
 add  SomeDataId BIGINT Identity(1, 1)
Then enable identity_insert on table SomeData and insert rows

SET IDENTITY_INSERT dbo.SomeData ON
insert into SomeData(id, binary)
select SomeDataid, SomeData
from x


Which mechanism is best for previous data archived in oracle? Our company has a big data server in which large data is stored, like images, video, audio, etc. Our company wants a process running with the new year, such that in 2020 the previous year's 2019 data will be automatically archived and removed tables the live tables.  In the future, we'll take a backup of the archived data.

I am new in this field. Which Mechanism should I use for this purpose?

If you used the optional feature table partitioning. You could easily drop the previous years partitions without affecting the current year. If it were me I would partition by month and sub partition by hour. You might want to keep more than the current year online. Hence partition by month would allow you to keep a rolling 11 months if you wanted and drop the oldest partition every month.


How to correctly use PostgreSQL to limit multiple and/or concurrent executions of a task Let's assume you have a task FOO that can be queued once every minute, and a pool of 50 workers that can be paused. The queue is paused for 10 minutes, and 10 FOO tasks are queued. When the queue is resumed, the 10 FOO tasks will be executed almost concurrently (because there are more workers than tasks).

In this case, I need to ensure that no more than 1 FOO task per minute (time can vary) is performed.

One solution, using Redis, is to take advantage of Redis atomic and the TTL of a key. When a FOO task starts, it checks if the key worker:FOO exists. If does, then it exists, if it does not it sets the value and a TTL to the maximum frequency. This is easy to achieve using SETNX worker:FOO whatever and then using TTL worker:FOO if the previous command returned 1.

Because SETNX is atomic, I won't fall into the case where two FOO tasks are executed because of the race condition between the GET and the SET.

Now the question is: what is the correct way to achieve the same result using PostgreSQL? I can have a table with a key and a executed_on timestamp value, but how can I ensure that there is no case where two FOO tasks are both executed because of the delay between FOO 1 checks the record and writes a lock?

Since you're trying to serialize work, I'd update a record in a table.

CREATE TABLE task_keys (
  task varchar(10) primary key,
  last_executed timestamp with time zone not null,
  by_worker_id integer
);

INSERT INTO task_keys(task, last_executed)
VALUES ('FOO', '-infinity');

then to see if you can run a task yet:

UPDATE task_keys SET
  last_executed = current_timestamp,
  by_worker = $1
WHERE task = 'FOO'
  AND last_executed < (current_timestamp - INTERVAL '1' MINUTE)
RETURNING *;

The isolation rules of READ COMMITTED guarantee that if this query successfully updates the table and returns a row, no other query can have concurrently done so. A row lock is taken on the relevant task_keys row. If another UPDATE tries to affect the same row, it'll wait until the row lock is released by a commit or rollback of the holding transaction... then it will re-check the WHERE clause. If the other tx committed then the WHERE clause will no longer match, so it'll affect zero rows.

See the documentation on transaction isolation.

If you need concurrency this gets a little tricker. What you really want is a token pool that refills on a timer, where workers can grab tokens from the pool to do work. That's effectively what we're doing here, with one token - so one option is to add more rows for the same task and grab the first task where the last_executed timestamp is old enough.

There are two flaws with this whole approach though:

It doesn't know when a task finishes, so long running tasks could overlap; and
It doesn't care if a task succeeds or fails

To solve those you need to use a proper work queue implementation. Those are currently very difficult to implement correctly in the database, so I suggest you look at using an external message queue / work queue system to manage them. In PostgreSQL 9.5 the new FOR UPDATE SKIP LOCKED feature will make implementing work queues like this in the database quite simple, though.

BTW, advisory locking is often a good choice for this sort of thing, but it won't help you with the need to expire the lock automatically after a certain amount of time.


Restore backup fails - not enough disk space I have a backup that's about 6Gb. It's a "light" backup of the original (with the log tables purged), which is about 14Gb.

I try to restore the backup on my SQL Express local server. It fails with a message like : System.Data.SqlClient.Error: insufficient disk space. It asks for 227,891,019,776 bytes, which is absolutely crazy, and almost as big as my whole hard drive.

As found on other sites, I tried RESTORE FILELISTONLY FROM DISK = 'backupfile.bak'.

The data file (column Size) is 6,888,226,816 in size, but the log file is 221,006,987,264. The column BackupSizeInBytes returns 6,259,736,576 and 0.

So, if I understand correctly, restore checks that I have enough space to restore the "theorical" size of the log file before proceeding, disregarding the actual log file size ?

How can I bypass that ? It's a bit difficult to get a backup, so if I can resolve my problem without having to return on the production server, it would be great.

Thank you !

Oh BTW, I am on SQL Server 2008 R2 Express.

Note that the backup size does not include empty pages, but when you actually perform the restore, the data and log files will be over 200 GB, because it has to restore exactly what the source system had (including a 200+ GB log file, regardless of how full it was).

If you don't want to risk data loss, you need to correct that at the source (e.g. shrink the log file to something reasonable), take another full backup, and restore that. I'm not sure I understand why it's difficult to get a backup - this is a pretty standard operation, and should be a service provided by anyone you're paying to host SQL Server.

You should also fix the source database to either (a) be in the right recovery model or (b) take log backups more frequently. Your log file is ludicrous because you are in full recovery and never take log backups. If you need point in time recovery, start backing up your log. If you don't, switch to simple. The log file should manage itself if you have it configured correctly. If you don't, then when it gets like this, shrinking should be a one-time operation, and then you should fix the configuration so you're not doing this again next week...


How to Backup/Restore in PostgreSQL pgAdmin 4? So I feel like this is something that should be very basic and easy to do, maybe I'm just not doing things correctly.

I want to do the equivalent of SQL Server backups. I want to create the equivalent of a full .bak file and be able to restore a database to its original state.

So far I've tried many different ways in PgAdmin4 to create a backup file from a freshly created database. I migrate my schema which creates a bunch of tables, then perform a restore on the previously created file. What I expect after the restore is for all those tables I created to be gone, I expect the database to be in the exact state that it was when I took the backup.

I've tried many different options like including the Create/Drop Database commands preserving/not saving data on backup/restore but have had no luck.

Anyone able to help me out here?

I am using commands pg_dump mydb -Fc > db and pg_restore db. Maybe there's some magic parameters I'm missing for it to do what I'm asking above?

Tried pg_restore --clean db, same results.

The problem is I create my database, fresh with nothing in it, take a backup of it, create some tables, restore the backup, and the tables I created are still there. I want a restore method that puts the db in the same state as it was when it was backed up.

You may find the docs useful
It sounds to me like pg_dump is "The Backup Dialog" and pg_dumpall is "The Backup Server Dialog".

The Backup Dialog

Using the pg_dump utility, pgAdmin provides an easy way to create a backup in a plain-text or archived format. You can then use a client application (like psql or the Query Tool) to restore a plain-text backup file, or use the Postgres pg_restore utility to restore an archived backup. The pg_dump utility must have read access to all database objects that you want to back up.

The Backup Server Dialog

Use the Backup Server dialog to create a plain-text script that will recreate the selected server. You can use the pgAdmin Query Tool to play back a plain-text script, and recreate the server.


Cannot rebuild index, but there's no reason why not? I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one particular index and I see no reason why I should.
It fails with the following message:

Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index 'I_520CUSTVENDRELIDX' because the index contains column 'MEMO' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

However, when I run the query based on a suggestion by this chap, shown below, I get no results:
SELECT *
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
AND ((c.system_type_id IN (34,35,99,241)) -- image, text, ntext, xml
 OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary
     AND max_length = -1))
INNER JOIN sys.indexes as si
on si.object_id = ic.object_id
AND ic.index_id = si.index_id
inner join sys.tables t
on t.object_id = ic.object_id
where t.name = 'CONTACTPERSON'
and si.name = 'I_520CUSTVENDRELIDX'

On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX).  Could there be something I'm missing here?
For the record, this is a clustered index.

I skipped the code example, and jumped to what seems to be the real question here

  On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX). Could there be something I'm missing here?

  For the record, this is a clustered index.

You certainly are missing something, and the answer is in the body of the error message:

  For a clustered index, the column could be any column of the table

For Online Index Operations there is a difference between a Clustered and a Non-Clustered index.
both may not contain a BLOB column in order to rebuild online,
but while a Non-Clustered Index "contains" the columns in it's definition (and included columns),
a Clustered Index "contains" the entire table with all of the columns.

in your case there is a column named MEMO which is of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type.
this column is part of the table, and thus part of the Clustered index.

Your options are:

Perform the REBUILD operation offline
Upgrade to SQL Server 2012
Change the column's data type

Guidelines for Performing Online Index Operations


Change Linux Family for Postgres Database I have Postgres 9.5 running on "old" Debian server and about to move it on "new" CentOS, so here is the question.
Is it OK to just install binaries of the same 9.5 version and just copy database cluster (PGDATA) to a new server?

The immediate problem you are going to hit is that there will be a different version of the C library, and your collations will change. So, unless you are using the C collation, either move the database with pg_dumpall/restore or , if you copy the data directory, REINDEX all indexes that are defined on columns with a string data type.
Also make sure that both systems are either 32-bit or 64-bit when copying the data directory.
And upgrading PostgreSQL is not a "tricky political enterprise-world question". After all, PostgreSQL doesn't get any money if you upgrade. If you are not running a supported release, you won't get updates, so you will be exposed to data corruption bugs.
Of course you want to have the same release as the client. Your task is to drive the danger home to the customer.


MySQL Workbench run SQL script file Character Set encoding issue Upon running a .sql file for creating a schema in MySQL Workbench 6.1, it prompts a window saying it cannot decide the right character set

What's the proper thing to do?

If it is the "world" database downloaded from here, it is in latin1 encoding. Use that for importing.

UTF-8 (utf8 or utf8mb4 in MySQL) has become the de facto standard nowadays for operating systems and global communications (except in some countries and specific areas of knowledge), and utf8 is now the default encoding in MySQL (since 5.5). See also http://utf8everywhere.org

latin1 was the 1 byte representation of characters for the western languages. MySQL supports several encodings, you can see them by executing:

SHOW CHARACTER SET;

You asked in a comment:

  Given a *.sql file, how do I unambiguously find out its encoding?

If you used mysqldump to generate it, it usually presets the encoding used at the beginning like this (check the "set names" execution), so that it is imported correctly, without needing to specify it.

In general, it is impossible to know the encoding used of any arbitrary text file, but you can try to auto-detect it with tools like the standard GNU file tool on Amazon Linux file -i <filename>. There should be tons of tools like that, even more specialised.


What does MySQL mean by "Major Version", and is MySQL replication supported between 5.1 and 5.6? I am migrating from some MySQL 5.1 servers to MySQL 5.6 servers. The MySQL docs are a bit unclear if replication is supported between a 5.1 master and a 5.6 slave, and MySQL's documentation is unclear if this is supported.

In terms of version numbers, MySQL seems to follow the norms for semantic version numbers

5.1 has a "Major version" of 5, and a "release level" of 1.
5.6 has a "Major version" of 5, and a "release level" of 6.
MySQL 4, MySQL 5 and MySQL 6 would all be considered "Major versions".

This is documented at https://dev.mysql.com/doc/refman/5.6/en/which-version.html :

  The naming scheme in MySQL 5.6 uses release names that consist of
  three numbers and a suffix; for example, mysql-5.6.1-m1. The numbers
  within the release name are interpreted as follows:

  The first number (5) is the major version and describes the file format. All MySQL 5 releases have the same file format.
  The second number (6) is the release level. Taken together, the major version and release level constitute the release series number.

https://dev.mysql.com/doc/refman/5.6/en/replication-compatibility.html says replication is supported between one major version and the next highest major version. This wording is pretty ambiguous if replication is supported between 5.1 and 5.6.

  MySQL supports replication from one major version to the next higher
  major version. For example, you can replicate from a master running
  MySQL 4.1 to a slave running MySQL 5.0, from a master running MySQL
  5.0 to a slave running MySQL 5.1, and so on.

The updated 5.7 beta docs sort of simply that replication is only supported between release levels, although the manual uses the term "major version". What do they really mean here?

  MySQL supports replication from one major version to the next higher
  major version. For example, you can replicate from a master running
  MySQL 5.0 to a slave running MySQL 5.1, from a master running MySQL
  5.1 to a slave running MySQL 5.5, and so on.

Is MySQL replication supported between MySQL 5.1 and MySQL 5.6? I understand that MySQL 5.1 is EOL on most platforms, but it's still shipped with RHEL6.

I've confirmed this is a bug with MySQL/Oracle Support. They will update the public tickets when the internal fixes are ready.

To summarize: MySQL replication is only supported between one release level (the second digit) and the next; e.g. between MySQL 5.1 and 5.5; 5.5 and 5.6 but not between MySQL 5.1 and 5.6. The documentation is mistaken when it says "MySQL supports replication from one major version to the next higher major version."* They mean release level or release series number, not major version. That is, unless they change the terminology in the future.

Replication between 5.1 and 5.6 might work, but it's not supported and not well tested. It's best to for replication servers to stay within one release level of each other, and to use the latest version of your 5.x release.

The bug reports are:

"Section should be more clear about 5.5 and 5.6 versions". Now, the documentation is more clear:

  MySQL supports replication from one release series to the next higher
  release series. For example, you can replicate from a master running
  MySQL 5.5 to a slave running MySQL 5.6, from a master running MySQL
  5.6 to a slave running MySQL 5.7, and so on.

A second private bug which I'll add once the bug is made public.


Run a script for all databases I need help  what statement or function i can use to run a query in all database using one script , i want to run the below script at the same time to retrieve output in all databases

   select
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id),
      OBJECT_NAME (id) as StoredProc,
      Text
    from syscomments
    where text like '%Janice\SQL%DTS%'.

The solution heavily depends on how much work you are willing to put in your initial query and what is going to consume the data (and how).

-- Option 1 --

If this is a one-off requirement, you can use variations of sp_MSForEachDB to retrieve a separate data set for each database.

Personally, I don't like sp_MsForEachDB for a number of reasons, mainly because it uses a wrongly configured cursor to enumerate the databases, which can make it skip databases at times. I coded my own alternative to it some years ago and blogged about it here

The main issue with this solution is that it doesn't return a single data set, which I suppose is what you want. In this case, you should create a temporary table upfront and use it to store data returned from your query. All executions against different databases will populate the temporary table with the data you need.

Here's an example:

DECLARE @sql nvarchar(max);

SET @sql = '
select
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id),
      OBJECT_NAME (id) as StoredProc,
      Text
    from syscomments
    where text like ''%Janice\SQL%DTS%''
';

CREATE TABLE #results (
    [ServerName] [nvarchar](128) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Object_ID] [int] NOT NULL,
    [StoredProc] [nvarchar](128) NULL,
    [Text] [nvarchar](4000) NULL
);

DECLARE @statement nvarchar(max);

SET @statement = (
SELECT 'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @sql; '
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)');

INSERT #results
EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql;

SELECT *
FROM #results;

-- Option 2 --

This solution requires that you put your sql statement in a string variable and you know the output of the query. If this is not acceptable for you, some time ago I coded a stored procedure that can take the SELECT statement inside a view and use it to execute against all databases (or a subset of your databases). In this case, you just need to create a view with the statement you want to run and pass the name to that stored procedure.
You can find the code here.

-- Option 3 --

You could also UNION ALL the results that come from your query, something like this:

DECLARE @sql nvarchar(max)

SET @sql = '
select
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id),
      OBJECT_NAME (id) as StoredProc,
      Text
    from <dbname>.dbo.syscomments
    where text like ''%Janice\SQL%DTS%''
';

DECLARE @statement nvarchar(max);

SET @statement = STUFF((
SELECT 'UNION ALL ' + REPLACE(@sql,'<dbname>',QUOTENAME(name))
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)'),1,9,SPACE(0));

EXEC sp_executesql @statement

If you use this approach, you also have to take care of everything that could go wrong in the UNION ALL. Collation conflicts between databases, for instance, have to be resolved in the query.

Hope this helps.

BTW, syscomments is deprecated, so if you're using SQL Server 2005 and above, use sys.sql_modules instead.


Can duplicate pg_hba entries result in a connection error? I have received the following error when attempting to connect to an Arch Linux server:

Sequel::DatabaseConnectionErrorreplication#monitor
PG::Error: could not connect to server: Connection refused Is the server running on host "45.56.86.24" and accepting TCP/IP connections on port 5432?

but I have an entry in my pg_hba.conf file on that server:

hostssl replication replication 45.56.86.24/32 md5

Actually... there are two lines like this. Is presence of the second line be expected to result in this error?

No. Duplicate entries are basically ignored.

The documentation of pg_hba.conf describes how the rules work pretty well:

  The first record with a matching connection type, client
  address, requested database, and user name is used to perform
  authentication. There is no "fall-through" or "backup": if one record
  is chosen and the authentication fails, subsequent records are not
  considered. If no record matches, access is denied.

So the second and any subsequent duplicates will never be considered.
So duplicate entires can not be the cause of your error.


Does my table violate normalization rules? I am developing an emulator for a game.  My item table looks like this:

-- ----------------------------
-- Table structure for `items`
-- ----------------------------
DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
  `guid` int(11) NOT NULL,
  `template` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `stats` text NOT NULL,
  UNIQUE KEY `guid` (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

so some rows look like this (row is wrapped for readability):

'110', '6797', '1', '6',
     'b6#1#0#0#0d0+1,3ca#2412#0#0#0d0+9234,3cb#1#0#0#0d0+1,3cc#1#0#0#0d0+1,
      3cd#a#0#0#0d0+10,3ce#1#0#0#0d0+1'

As you can see the long mess at the end is only one column - they are stats.  I think this field could be divided into individual stats in more columns.  Would that violate normalization rules? Should columns hold only indivisible attributes?

don't add more stat columns unless you are absolutely sure the required # of columns wont grow, tables tend to get unmanageable that way.

if you need to work with the individual stats in your comma separated text field, you probably should break them off into their own table where each row has 1 stat, make  guid+statid your primary key and then you can make the stat column something more reasonable like a varchar (20). This might take up a little more diskspace but it should be faster working with individual stats...


Why would an index covering only half of primary key be useful? We have a primitive junction table to store a many-to-many relation between two other tables, their primary keys being bigint:

CREATE TABLE [dbo].[JunctionTable] (
    [ID-1] [bigint] NOT NULL,
    [ID-2] [bigint] NOT NULL,
    CONSTRAINT [PK_JunctionTable] PRIMARY KEY CLUSTERED ([ID-1] ASC, [ID-2] ASC)
)

When the Tuning Advisor was run on the database, it suggested (among many others, obviously) the following index to be added:

CREATE NONCLUSTERED INDEX [_dta_index_JunctionTable_8_296569782__K1] ON [dbo].[JunctionTable]
(
    [ID-1] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

And I wonder: is this index good for anything? AFAICT it is the left half of a clustered primary key and the space reduction coming from omitting a single bigint cannot be that important, I guess. Or, is there any scenario when this index would help? And if not, why would the DTA suggest it? (The Advisor suggested many new indexes which I consider silly, but this one seemed the silliest, so I thought I ask…)

The Database Tuning Advisor does not always make sensible suggestions. It is always a good idea to review any suggestions made (by any tool more that matter).

In general, the cost of an index seek is directly related to the depth of the B+Tree that makes up that index. Seeking in an index that is 8 levels deep is 4 times as expensive as seeking in an index that is only 2 levels deep. The depth of the index is strongly correlated with the length of the index key(s). Other important factors are uniqueness of the index, the length of the clustered index key and wether there are any included columns.

If you create an index on one column you generally get a index less deep than if you create an index on that same column and an additional column. However, in your case, as the index on ID-1 is not declared unique (it can't be), the clustered index key automatically becomes part of the key. Assuming there are no other columns, that additional index will be an exact copy of the clustered index. If you however have additional columns in the table, you might end up with a small difference in index depth. But even then it is usually not worth creating the index.

The difference becomes bigger if you have a lot of additional columns and you are looking at queries that would scan the new index like this one:

SELECT [ID-1],COUNT(1) AS Cnt
  FROM [dbo].[JunctionTable]
 GROUP BY [ID-1];

As all those additional column are not part of the index the scan has to go through a lot less pages. If you for example had two additional DATETIME columns in that table, the index scan speed would probably double with that new index. But you would have to run this query very often for the index to be worth the additional maintenance overhead.


Efficiently bulk upsert unrelated rows As part of a manual replication process between databases with different but related schemas, for each table, we identify new and modified records in the source database and feed them via a table valued parameter to a stored procedure in the SQL Server destination database. The source database is not SQL Server and is in a different data center.

For each row to be upserted, the stored procedure queries for the primary keys of the related rows already in the destination database.

Currently, we do a single SELECT to get the primary keys, followed by a single MERGE to perform the upsert. There are two aspects of this approach that I believe may not be as efficient as possible.

An implicit transaction unnecessarily wraps the MERGE. The database would remain consistent even with each row being upserted one at a time. If an row's upsert fails, we want the remaining rows to proceed.
MERGE interleaves inserts and sets as it goes through the rows, which is fine, but we don't need this. It would be acceptable to set all the modified rows and subsequently insert all the new rows.

Based on the flexibility we have, the MERGE performance tip to use UPDATE and INSERT seems to apply to our case:

  When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements.

Do I understand that right? Am I better off with a separate UPDATE and INSERT? And what about the implicit transaction? Is performing a single SELECT, UPDATE, and INSERT over a large batch of rows most efficient, or is it better to take advantage of the ability to do one row at time by using a FOR loop? Or something else?

In general, what is the most efficient way to upsert a large batch or rows to a SQL Server table in which the rows are not transactionally related and sets and inserts need not be interleaved?

To elaborate on my comment, I would use a set based approach for better performance with error handling, here is how I would do it. I mixed the two common methods, using JOIN and NOT EXISTS / EXISTS.

Note, I don't use a temp table or table variable here. I've seen performance gains when the databases are on different servers but pulling the data into a temp table before doing an upsert across a linked server or whatever, but I'm not sure if your databases are on the same instance or not.

set xact_abort on;
begin try
    begin tran

    update d
    set d.column = s.column
    from destinationTable d
    inner join sourceTable s on
    s.id = d.id

    insert into destinationTable (...column list...)
    select s.1, s.2, ...
    from sourceTable s
    where not exists(select id from destinationTable d where d.id = s.id)

    commit
end try
begin catch
    if @@trancount > 0 rollback tran
    declare @msg nvarchar(2048) = error_message()
    raiserror (@msg, 16, 1)
    return 55555
end catch

Some good reads

Erlan Sommarskog's Error and Transaction Handling
Aaron Bertrand's Use Caution w/ SQL Server MERGE
Martin Smith's break down of Table Variable vs Temp Tables and his suggestions on when you should use one over the other
Using LEFT JOIN or NOT EXISTS

In the case that the data is in on a different server a temp table could increase the performance of the insert (or may be the only option if it's not SQL Server). The above code would be the same, except the sourceTable would be replaced with #sourceTable.

If the table is large, then you may want to consider staging tables instead of #tempTables so that you don't beat up tempdb too bad. BULK INSERT into the staging tables (sourceTable) and then do your upsert from here.


Compare two tables amount with respect to priortization of FundType I have two tables one is PaymentDetail and another is Ledger

Here i want to prioritize paymodetype in manner that cash should be considered first for
R,A and then I FundType, then Credit Card, then DD and then Cheque.

I want that amount received in cash should first be considered for R then A and lastly for I FundType.Another example

Here is sqlfiddle as well. Can it be solved by SQL only. I don't want to use Cursor.
Edit:
I have updated the sqlfiddle with what i have tried. I am trying to use join due to which multiple rows are obtained in my result with respect to Payment Detail. I want to span amount of one row of payment detail over Ledger then second row should be used.
How can i achieve that?
Result of current sqlfiddle is

I cannot think of a reasonable way to meet the requirement with a T-SQL non-cursor solution, so the following is a SQLCLR procedure that returns a result set.  The solution performs a single scan of each table (at most).  In a test with 500,000 Payment rows and 1,000,000 Ledger rows, it completed in 4.8 seconds with a warm buffer pool and 5.2 seconds from a cold start - including the time to display the 120,000 rows of results in SSMS.

The procedure uses the EXTERNAL_ACCESS permission set, so ALTER DATABASE [name] SET TRUSTWORTHY ON; is the easiest way for testing (the database must also have a valid owner).  For deployment, you might prefer to sign it and grant the permission explicitly.  I was unable to upload this to SQL Fiddle as it has a limit of 8000 characters for DDL statements.

CREATE ASSEMBLY [dbase]
FROM [encoded data, 14338 characters]
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
CREATE PROCEDURE [dbo].[MatchLedgerRecords]
WITH EXECUTE AS CALLER AS
EXTERNAL NAME [dbase].[StoredProcedures].[MatchLedgerRecords];

Sample data reproduced from your SQL Fiddle:

CREATE TABLE dbo.PaymentDetail
(
    PaymentId   integer PRIMARY KEY,
    RecptNo     integer NOT NULL,
    Amount      float NOT NULL,
    PayModeType varchar(20) NOT NULL
);

CREATE TABLE dbo.Ledger
(
    LedgerId    integer PRIMARY KEY,
    Recptno     integer NOT NULL,
    FundType    char(1) NOT NULL,
    amount      float NOT NULL
);

INSERT INTO dbo.PaymentDetail
    (PaymentId, RecptNo, Amount, PayModeType)
VALUES
    (1, 1, 1000, 'Cash'),
    (2, 1, 3000, 'Cheque'),
    (3, 2, 4000, 'Cheque'),
    (4, 2, 1000, 'Cheque');

INSERT INTO dbo.Ledger
    (LedgerId, Recptno, FundType, amount)
VALUES
    (1, 1, 'R', 500),
    (2, 1, 'A', 500),
    (3, 1, 'I', 1000),
    (4, 1, 'I', 1000),
    (5, 1, 'I', 1000),
    (6, 2, 'R', 2000),
    (7, 2, 'A', 1000),
    (8, 2, 'I', 2000);

Usage:

EXECUTE [dbo].[MatchLedgerRecords];

Output:

Source code:

using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void MatchLedgerRecords()
    {
        SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder();

        // Use a context connection to find the name of the server and current database
        using (SqlConnection conn = new SqlConnection("context connection = true"))
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand(@"SELECT @ServerName = @@SERVERNAME, @DatabaseName = DB_NAME()", conn);
            cmd.Parameters.Add("@ServerName", SqlDbType.NVarChar, 128).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@DatabaseName", SqlDbType.NVarChar, 128).Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();

            // Build a connection string
            scb.Enlist = false;
            scb.IntegratedSecurity = true;
            scb.DataSource = (string)cmd.Parameters[0].Value;
            scb.InitialCatalog = (string)cmd.Parameters[1].Value;
        }

        // Non-context-connections
        using (SqlConnection conn1 = new SqlConnection(scb.ConnectionString), conn2 = new SqlConnection(scb.ConnectionString))
        {
            conn1.Open();
            conn2.Open();

            // Commands to read the two tables in the required order
            SqlCommand paymentCommand = new SqlCommand(
                @"
                SELECT
                    PaymentID,
                    RecptNo,
                    Amount,
                    PayModeType
                FROM dbo.PaymentDetail
                ORDER BY
                    PaymentID;", conn1);

            SqlCommand ledgerCommand = new SqlCommand(
                @"
                SELECT
                    LedgerID,
                    RecptNo,
                    FundType,
                    Amount
                FROM dbo.Ledger
                ORDER BY
                    LedgerID;", conn2);

            // Avoid default 30 second timeout
            paymentCommand.CommandTimeout = 300;
            ledgerCommand.CommandTimeout = 300;

            // Shape of the results
            SqlMetaData[] smd = new SqlMetaData[]
            {
                    new SqlMetaData("PaymentID", SqlDbType.Int),
                    new SqlMetaData("LedgerID", SqlDbType.Int),
                    new SqlMetaData("FundType", SqlDbType.Char, 1),
                    new SqlMetaData("Amount", SqlDbType.Float),
                    new SqlMetaData("PayModeType", SqlDbType.VarChar, 20)
            };

            SqlDataRecord sdr = new SqlDataRecord(smd);

            // Indicate start-of-results to SQL Server
            SqlContext.Pipe.SendResultsStart(sdr);

            // Main algorithm
            using (SqlDataReader paymentReader = paymentCommand.ExecuteReader(), ledgerReader = ledgerCommand.ExecuteReader())
            {
                if (ledgerReader.Read() && paymentReader.Read())
                {
                    // Read the first ledger row column values
                    // (ordinal numbers are the column position from the original SELECT)
                    int LedgerID = ledgerReader.GetInt32(0);
                    int LedgerReceiptNo = ledgerReader.GetInt32(1);
                    string FundType = ledgerReader.GetString(2);
                    double LedgerAmount = ledgerReader.GetDouble(3);

                    // Read the first payment row column values
                    int PaymentID = paymentReader.GetInt32(0);
                    int PaymentReceiptNo = paymentReader.GetInt32(1);
                    double PaymentAmount = paymentReader.GetDouble(2);
                    string PayModeType = paymentReader.GetString(3);

                    // A modified merge join
                    while (true)
                    {
                        // Join on receipt number
                        if (LedgerReceiptNo == PaymentReceiptNo)
                        {
                            // If the current ledger amount can be allocated from the current payment
                            if (LedgerAmount <= PaymentAmount)
                            {
                                // Set result column values
                                sdr.SetInt32(0, PaymentID);
                                sdr.SetInt32(1, LedgerID);
                                sdr.SetString(2, FundType);
                                sdr.SetDouble(3, LedgerAmount);
                                sdr.SetString(4, PayModeType);

                                // Send a result row
                                SqlContext.Pipe.SendResultsRow(sdr);

                                // Subtract the allocated amount from the payment amount available
                                PaymentAmount -= LedgerAmount;

                                // Next ledger record
                                if (!ledgerReader.Read()) break;

                                // Read the ledger record's columns
                                LedgerID = ledgerReader.GetInt32(0);
                                LedgerReceiptNo = ledgerReader.GetInt32(1);
                                FundType = ledgerReader.GetString(2);
                                LedgerAmount = ledgerReader.GetDouble(3);
                            }
                            // Ledger amount is greater than the payment amount remaining
                            else
                            {
                                // Return a row for the remaining payment amount (if non-zero)
                                if (PaymentAmount > 0)
                                {
                                    sdr.SetInt32(0, PaymentID);
                                    sdr.SetInt32(1, LedgerID);
                                    sdr.SetString(2, FundType);
                                    sdr.SetDouble(3, PaymentAmount);
                                    sdr.SetString(4, PayModeType);

                                    SqlContext.Pipe.SendResultsRow(sdr);

                                    // Adjust the ledger amount
                                    LedgerAmount -= PaymentAmount;
                                }

                                // Read the next payment record
                                if (!paymentReader.Read()) break;

                                // Read the payment record columns
                                PaymentID = paymentReader.GetInt32(0);
                                PaymentReceiptNo = paymentReader.GetInt32(1);
                                PaymentAmount = paymentReader.GetDouble(2);
                                PayModeType = paymentReader.GetString(3);
                            }
                        }
                        // Receipt numbers do not currently match, payment behind ledger
                        else if (PaymentReceiptNo < LedgerReceiptNo)
                        {
                            // Read next payment record
                            if (!paymentReader.Read()) break;

                            // Read column values
                            PaymentID = paymentReader.GetInt32(0);
                            PaymentReceiptNo = paymentReader.GetInt32(1);
                            PaymentAmount = paymentReader.GetDouble(2);
                            PayModeType = paymentReader.GetString(3);
                        }
                        // Receipt numbers do not currently match, ledger behind payment
                        else
                        {
                            // Read next ledger record
                            if (!ledgerReader.Read()) break;

                            // Read column values
                            LedgerID = ledgerReader.GetInt32(0);
                            LedgerReceiptNo = ledgerReader.GetInt32(1);
                            FundType = ledgerReader.GetString(2);
                            LedgerAmount = ledgerReader.GetDouble(3);
                        }
                    // end while: Repeat until ledger or payment runs out of rows
                    }
                }
                // End of result set
                SqlContext.Pipe.SendResultsEnd();
            }
        }
    }
}


How many DBMS's can be installed on a single server? The software in our organization uses various databases, many on different database platforms.

Can we put all the various database platforms on a single machine?  What are the limitations to this?

Should we have a single machine for each DBMS type?  What are the limits to the number of database per DBMS in this example?

Separate.

SQL Server, Oracle, DB2 et al typically do not play nicely alongside other "server" software. They tend to monopolise system resources, particularly memory, so while you could install them side-by-side, you won't be getting the best from them. It would also be challenging to diagnose performance issues with two different DBMS's competing for resources.

Given a collection of ageing or little used applications that required a mix of database systems, virtualisation would be a tempting solution to consolidate.


Network Partitioning My current understanding of the network-partitioning scenario, also mentioned in the CAP theorem, is that it's a scenario where the clients are not able to connect to some DB nodes even though they are up and running. For e.g. If a cluster consisted of say, 10 nodes, then it might happen due to some network issues that client A is able to connect to only 5 nodes, client B is able to connect to the other 5 nodes. In such scenarios, data inconsistency scenarios might arise and we need to plan for them.

As per my current understanding of partitioning, I don't see any such scenario arising when all the nodes are on the same rack/subnet. So this means that I don't have to plan for network-partitioning events(if I'm correct) as they can't arise.

Am I correct?

I think you are correct. We need at least two distinct subnet to talk about network partitioning.

But if your 10 nodes are in the same subnet, what is the point ? If your network is down, you loose your cluster !
Best practice would be to have 5 nodes in one datacenter, 5 nodes in another data center.


Backup error: Failed to flush the commit table to disk When I try to backup a SQL Server 2008 database, I receive an error:

Backup failed for Server 'SQL2008TEST'.  (Microsoft.SqlServer.SmoExtended)
Failed to flush the commit table to disk in dbid 32 due to error 2601.

Does anybody know the cause of this error?

This usually happens when you have enabled change tracking on the database. A fix was released some time back:

FIX: Backup fails in SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012 if you enable change tracking on the database (Microsoft Support)

As it says there:

  The fix [...] was first released in Cumulative Update 7 for SQL Server 2008 Service Pack 2

The latest (and final) Service Pack 4 for SQL Server 2008 would also contain this fix.


Best approch to replicate data from one db to other db on same server I am facing issue of time out and slow data response so I did search on Google and found that I could create a server which replicates main database data to secondary database and both on same server instance; main database used for real time read and write and the Report server will get data from the secondary   database so when someone runs a large report then it will not affect the main database and the application will run with out any issue.

What replication solution I have to use for situation? I am using SQL Server 2012 Standard Edition.

If the data in the reporting database does not have to be real-time, the I suggest you do not implement replication but instead go with log shipping.


Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4? Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - one as part of the PRIMARY KEY and the other a GIST index on a POINT PostGIS geometry column.

To make the process go a little faster the job drops the index on the geometry column, before deleting the records in the table A and inserting the records from table B, then the index is recreated. This all being done the autovacuum daemon gets to work when it feels like it (after ten minutes or so from comparing the job stats and table stats for the job completion time and autovacuum run time).

Upon checking on the table this morning after all this had happened the table stats told me the table size was 272MB, the TOAST table size was 8192bytes, and the index size was 23MB. This seemed quite large so I issued a REINDEX command on the table and the index size came down to 9832kB.

My question(s) is this:

Why does the REINDEX apparently reduce the size of the indexes so much when the indexes (or at least the geometry column index) have been built anew from scratch? Should I make sure that the table has been vacuumed/analyzed before the indexes are built? Is not dropping the index on the primary key a factor in this? What am I missing?

If the CREATE INDEX statement sees that another session holds an active snapshot that might still be interested in the deleted records, then it includes those deleted records into the new index.

Similarly, if a REINDEX sees that another session holds an active snapshot that might still be interested in the deleted records, then it includes those deleted records into the new index.

If a VACUUM sees that another session holds an active snapshot that might still be interested in the deleted records, then it keeps those records in the table. And then the REINDEX or CREATE INDEX also need to carry them into the new index, as long a the snapshot still exists.

Once there or no longer any snapshots that could possible see the deleted rows, then the VACUUM may remove them from the table.  But a CREATE INDEX or REINDEX could also just not carry them over into the new index, whether VACUUM had gotten around to removing them from the able or not.

So in your scenario, the role of the VACUUM between the initial CREATE INDEX and REINDEX is probably just to take up time, during which time your long-running transaction hopefully goes away on its own and drops the interfering snapshot.


SQL Server 2016 Availability Groups Named Pipes? We have a SQL Server 2016 cluster configured (two VM's) with our databases spread across multiple Availability Groups (AG).

Our Sharepoint admin is reporting to me that some functionality is failing as it requires Named Pipes. Now, named pipes are enabled but from some very old articles (pre-2013) I can see that Named Pipes were not supported on multiple IP addresses, which would cause issues for the AG listeners.

Can anyone confirm if this is the case? Or point me in the right direction?

From the documentation on Listeners, Client Connectivity, Application Failover

  An availability group listener consists of a Domain Name System (DNS)
  listener name, listener port designation, and one or more IP
  addresses. Only the TCP protocol is supported by availability group
  listener. The DNS name of the listener must also be unique in the
  domain and in NetBIOS.

So only TCP/IP is supported.


How to disable IO timeouts On my dev box I sometimes need to run very IO intensive queries such as index builds and CHECKDB. This can put so much load on the disk that it hardly can process anything else. This causes enormous lagging in other programs. For that reason I sometimes need to suspend sqlservr.exe using Process Explorer.

If I do that for longer periods of time I sometimes get IO timeouts such as

  Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT', id 0000000160673C40, type 4, Task 0x000000013BBD5C28 : 22, waittime 300 seconds, flags 0x1a, owning task 0x000000010F91F088. Continuing to wait.

  SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [....ndf] in database id 5.  The OS file handle is 0x0000000000000FDC.  The offset of the latest long I/O is: 0x00000e07c60000

  A time-out occurred while waiting for buffer latch -- type 2, bp 0000000200E9FD80, page 1:30481, stat 0x2040d, database id: 20, allocation unit Id: 72057594068533248, task 0x00000001CCE93088 : 13, waittime 300 seconds, flags 0x1a, owning task 0x00000001CC0FBC28. Not continuing to wait.

This sometimes causes the query to abort. How can I disable IO timeouts on my dev box?

Also, in case the query does not abort I sometimes get dump files. How can I disable those?

Since you're using SQL Server 2014 I would use resource governor to limit the amount of IO. Obviously this will make the processes take MUCH longer and you still may have timeouts.

There is no way to disable the IO messages and potential issues except to get a better IO subsystem or do less IO intensive items.


High PageIOLatch_SH Waits with High Drive Idle times We are experiencing high volume of PageIOLatch_SH waits on our database (row counts in the Billions).  However it seems that our drive Idle time Percentage hovers around 50-60 percent.  CPU usage is nill.  The Database Tuning Advisor gives no suggestions for optimization.  The query plan (actual) from the single stored procedure used on the database puts the majority of the expense on index seek (yeah I know these should be optimial) operations.

Anyone have suggestions of how to increase throughput?

Is there memory pressure on your server? The buffer pool may be filling up causing a lot of paging out to disk.

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Buffer Manager'
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

-- Is this a NUMA system?
SELECT
  CASE COUNT(DISTINCT parent_node_id)
     WHEN 1
         THEN 'NUMA disabled'
         ELSE 'NUMA enabled'
  END
FROM sys.dm_os_schedulers
WHERE parent_node_id NOT IN (32, 64);

Also, check the number of rows returned and the hierarchy in your where statements. You may be returning more rows than you require.


Copying Tables (partly replicating) databases remotely I am having hard time figuring out a possible solution copying data from a tables to another.

I have 2 databases
(DB1 and DB2).

DB1 and DB2 has the following tables (eg):

Product
Contacts
Sales

Copying Part,

When a data is inserted, updated to DB1 product tables, it has to be copied to DB2 also.

However,
if DB2  product table is inserted / updated, it should not reflect to DB1 (i assume this might not be a issue).

Further Complication:

If in DB2 Sales table, if new data is inserted/updated, it has to be copied to DB1 and DB1 can make changes and update the table which should reflect to DB2.

I have very less experience with database, although i am using it for quite frequently but have not attempted to do this sort of work, i am finding this a bit complicated. I attempted to look few of the Tutorials, videos. There are techniques using replications, hot standby, which is good but not suitable for my case.

Is it possible to do this thing, i am sure there are tools but i am not sure which tools is very useful for my case? Any suggestions or help is much appreciated.

AFAIK, if we can sync between 2 different database we need to use GUID / UUID (i have used this for one way replication). For this case i use a python server calculate and do the transaction. to retrieve about 10000 records it takes around 5-10 minutes which is tediously slow and useless for my new case. My new case might need to retrieve more than 100 thousan records at a time.

Any suggestions i much appreciated. I am just looking forward for some sign of hope as i could not google any solution.

Create 2 DBLinks between the 2 databases(DB1 to DB2 and vice versa), and use these links to insert/update data by creating triggers for insert/update for the tables.


Error while taking backup using SSMS I am using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login (not windows authentication). In user roles it has all permission for almost all databases. For some database it has only datareader.
Now when I try to take backup of database using SSMS and when try to select backup destination by clicking button I get the following error:

"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup
Cannot access the specified path or file on the server. Verify that
you have the necessary security privileges and that the path or file
exists.
If you know that the service account can access a specific file, type
in the full path for the file in the File Name control in the Locate
dialog box."

Even though I have db_owner and all permissions I am getting this error. But if I select the path manually in the file name field then it allows to take backup without any error.
So why does it happen? I want this user to take backup without these error messages. (But I can't give sysadmin for the user). How can I solve it?
I already some article stating that use SQL query and sqlcmd to take backup. But I want it to work using SSMS.

This is not a SQL Login permission issue. Rather that SQL Server Service Account can't access the location for the backup. To fix this you need to grant access in windows to that path to the account that the SQL Server Service executes under.


PostgreSQL: Running SELECT DISTINCT ON ~700m rows - how to optimize? I have a table that, if we look at just the relevant parts, has two columns: id and raw_data. id is an integer, and raw_data is a text blob. At this point, the table has no constraints or indexes except for an index on id.

My goal is to deduplicate (by id) this data and dump it all to plaintext files (on Amazon S3).

Note that any row with the same id can be assumed to be an exact duplicate (so I only need one, random row's data per id).

The table is on an Amazon EC2 RDS database with 2TB of space, 15GB of RAM. I can expand settings if needed, but want this to run over a reasonable time (i.e. max 24-48 hours, preferably faster).

The queries I'm trying to run (but are too slow) are:

SELECT DISTINCT ON (id) id, data
FROM table
OFFSET <0 through end of table>
LIMIT 250000

The first few offsets run within a reasonable time, but quickly becomes unmanageable (at least minutes to return) when the offset hits 10m+.

Since starting, I've created that id index, removed all other constraints and indexes (there's other columns than I described, but not relevant), set maintenance_work_mem to 4GB (for creating the id index), and most recently tried making the id index a clustered index. But this happened:

cluster id using idx_0;
ERROR:  could not extend file "base/16390/46741.294": wrote only 4096 of
8192 bytes at block 38558630
HINT:  Check free disk space.

Key questions:

1) Is SELECT DISTINCT ON with an OFFSET the right way to do this? Is there a more efficient query for pulling the data?

2) Is there anything else I can do to the DB/table to optimize? Would the clustered index solve my problem? Why is it taking over 1.1TB of extra space to deal with ~800GB of data?

Thanks for any advice!

Thanks to ypercube's tip in the comments above, I was able to keep the time per chunk of the query constant, and so good enough for my one-off purposes. I'm now running:

SELECT DISTINCT ON (id) id, data
FROM table
WHERE id > @last_max_id
ORDER BY id
LIMIT 250000

Takes about 1 minute per query. My whole process will take about 48 hours, but that's good enough.

Could definitely optimize further with multithreading if better performance were needed.

I also noticed that using a cursor would have probably worked similarly well:

BEGIN TRANSACTION;
DECLARE my_cursor CURSOR
FOR SELECT DISTINCT ON (id)
      id, raw_data
FROM table
ORDER BY id;

FETCH 100000 FROM my_cursor;
FETCH 100000 FROM my_cursor;
FETCH 100000 FROM my_cursor;

The FETCH's ran for about 100s, 110s, and 90s respectively so I imagine that scales linearly as well.


Make a boolean false after every n number of minutes in MySQL I have a table with a boolean value, if that value is not explicitly set to anything in the database for a few minutes by the back-end (django) then it must automatically be set to false. How do I do that in MySQL efficiently?

You should use the MySQL event scheduler to run a a query to update all rows where the age of the row is greater than your threshold and have it execute the update at a suitable interval.


Unclear update conflict I have two questions:
1. Why do I get update conflict in this situation instead of just blocking:
-- prepare
drop database if exists [TestSI];
go
create database [TestSI];
go
alter database [TestSI] set READ_COMMITTED_SNAPSHOT ON;
alter database [TestSI] set ALLOW_SNAPSHOT_ISOLATION ON;
go
use [TestSI];
go
drop table if exists dbo.call_test;
create table dbo.call_test ( Id bigint CONSTRAINT [PK_Call] PRIMARY KEY CLUSTERED ( [Id] ASC ), additional int, incl int );
create index ix_Call on dbo.call_test ( additional ) include( incl );
insert into dbo.call_test select 1, 2, 3;
go

First session:
use [TestSI];
go
set transaction isolation level snapshot
begin tran

   UPDATE dbo.call_test SET additional = 22 WHERE [Id] = 1

And second session:
use [TestSI];
go
set transaction isolation level snapshot

   UPDATE dbo.call_test SET additional = 222 WHERE [Id] = 1

In the second session I get immediately:

Msg 3960, Level 16, State 3, Line 3
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.call_test' directly or indirectly in database 'TestSI' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

This behavior I have as well if I update include column incl instead of nonclustered index key.
What impact does a nonclustered index have on update conflict in this situation? Why are locks not used in this situation?
2. And the second theoretical question:
How does SQL Server handle include columns update?
I mean how does SQL Server update all nonclustered index which have an include columns when we update this value? I don't see anything related in the query plan.
select @@version

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
Mar 18 2018 09:11:49
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 18363: ) (Hypervisor)

I checked this example on SQL Server 2019, and behavior on that server is as I expected: second session is locked. Is it a bug or I did something wrong?

Why do I get update conflict in this situation instead of just blocking

It is a product defect, which is fixed in SQL Server 2019.
A snapshot write conflict occurs when a snapshot transaction attempts to modify a row that has been modified by another transaction that committed after the snapshot transaction began.
The reason for the incorrect behaviour in your example is somewhat esoteric. The update plan uses something called Rowset Sharing. This means the Clustered Index Seek and Clustered Index Update share a common rowset.
This is an optimization so the Clustered Index Update does not need to locate the row to update via a normal seeking operation. The common rowset is already correctly positioned by the Clustered Index Seek. The update operator performs its work on the "current row" in the rowset.
This causes the erroneous message because the version of the row seen by the seek (the row before the uncommitted change) is shared with the update operator. The update sees that the row it is trying to update has changed and concludes (incorrectly) that an update conflict has occurred.
The correct behaviour can be obtained in many ways. One way to rewrite the update so that rowset sharing is not possible is to force the seek to use a different index. With different access methods, there is no common rowset to share:
UPDATE CT
SET CT.additional = 222
FROM dbo.call_test AS CT WITH (INDEX(ix_Call))
WHERE CT.Id = 1;

A more direct way is to use an undocumented and unsupported trace flag to disable the Rowset Sharing optimization (this is for demo purposes only, do not use it on a real database):
UPDATE dbo.call_test
SET additional = 222
WHERE [Id] = 1
OPTION (QUERYTRACEON 8746);

The plan looks the same as the original (the rowset sharing property is not exposed by default) but it will correctly block instead of throwing an update conflict error.
You can also avoid the error (and retain rowset sharing for the Clustered Index Update) by forcing a wide (per-index) update plan:
UPDATE dbo.call_test
SET additional = 222
WHERE [Id] = 1
OPTION (QUERYTRACEON 8790);

Encountering the bug requires rowset sharing and a base table update that also maintains secondary indexes (narrow, or per-row update).
If this behaviour is causing you real-world problems, you should open a support case with Microsoft.

Josh has correctly answered your second question. I will just add that you can see the nonclustered index maintenance on the Clustered Index Update operator in SSMS -- you need to look in the Properties window and expand the Object node:


How to prevent SQL Server service account from registering / deregistering SPN? The service account is a domain account. It is not a domain admin (nor is it a member of any group that is a domain admin). It has neither "write servicePrincipalName" nor "Write public information" permission (nor a member of a group with these permissions). Yet it is still able to register / deregister it's SPN upon startup and shutdown.

What permission am I missing?

This doesn't make sense, as you are running the service under a domain account with no "write ServicePrincipalName" permissions. If you were running under a localSystem account or a domain account with "write ServicePrincipalName" permissions, it would be able to create the spn. Is it possible that someone/something manually created the spn?

Here's an excerpt from Degremont Michel (SQL Server Core Engineer at Microsoft) explaining how this works:

If you run SQL Server under the LocalSystem account, the SPN is automatically registered as SQL registering with the machine account that has the right to create an SPN default. So Kerberos interacts successfully with the server running SQL Server.

However, if you run SQL Server under a domain account or a local account, the attempt to create the SPN may fail. When creating the service principal name fails, this means that no SPN is set for the service that is running SQL Server.

http://blogs.technet.com/b/mdegre/archive/2009/11/20/the-sql-network-interface-library-was-unable-to-register-spn.aspx

Here's another excerpt from msdn:

When the Database Engine service starts, it attempts to register the Service Principal Name (SPN). If the account starting SQL Server doesn’t have permission to register a SPN in Active Directory Domain Services, this call will fail and a warning message will be logged in the Application event log as well as the SQL Server error log. To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended) or NETWORK SERVICE, or an account that has permission to register an SPN, such as a domain administrator account. If SQL Server is not running under one of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually.

http://msdn.microsoft.com/en-us/library/ms191153%28v=sql.105%29.aspx

Sounds like the account "somehow" has permissions that you don't know about. Maybe group policy or something similar...


Bad estimates when sorting, good estimates without sort I'm struggling with the tuning of one of the queries on our production SQL Server 2016 SP2.
I needed to anonymize the query due to security policies.
The query is like this:
SELECT TOP 1000 Object1.Column1,Object1.Column2,Object1.Column3,Object1.Column4,Object1.Column5,Object1.Column6,Object1.Column7,Object1.Column8,Object1.Column9,Object1.Column10,Object1.Column11,Object1.Column12,Object1.Column13,Object1.Column14,Object1.Column15,Object1.Column16,Object1.Column17,Object1.Column18,Object1.Column19,Object1.Column20,Object1.Column21,Object1.Column22,Object1.Column23,Object1.Column24,Object1.Column25,Object1.Column26,Object1.Column27,Object1.Column28,Object1.Column29,Object1.Column30,Object1.Column31,Object1.Column32,Object1.Column33,Object1.Column34,Object1.Column35,Object1.Column36,Object1.Column37,Object1.Column38,Object1.Column39,Object1.Column40,Object1.Column41,Object1.Column42,Object1.Column43,Object1.Column44,Object1.Column45,Object1.Column46,Object1.Column47,Object1.Column48,Object1.Column49,Object1.Column50,Object1.Column51,Object1.Column52
FROM Schema1.Object2 Object1
LEFT OUTER JOIN Schema1.Object3 Object4 ON Object4.Column3 = Object1.Column3
WHERE ((Object1.Column43 IS NULL OR Object1.Column43='') AND (Object4.Column54 = 0 OR Object4.Column54 IS NULL) AND Object1.Column3 = N'SomeValue')
ORDER BY Object1.Column3 ASC,Object1.Column8 ASC,Object1.Column7 ASC,Object1.Column12 ASC,Object1.Column13 ASC,Object1.Column1 ASC

The actual query plan can be found here
As you can see an index seek is done on Object2.Index1.Object1 but the estimates are terrible. SQL Server estimated that 194907 rows would come back but only 710 rows are returned.
When I remove the ORDER BY clause the estimates are much better, SQL Server expected only 1181 rows to come back. The plan can be found here.
What I do not understand is why an ORDER BY can influence the estimates of an index seek. Both queries are run with the same value for column3.
Can somebody explain this to me?

why an ORDER BY can influence the estimates of an index seek.
Can somebody explain this to me?

This is a top values query.  The ORDER BY specifies which 1000 rows the query will return.  Without the ORDER BY SQL Server is free to return any 1000.  So once SQL Server has 1000 rows from Schema1.Object2 that qualify in the WHERE clause, it can stop.  The LEFT JOIN can't reduce the number of rows.
Specifically, the no-ORDER BY plan has a row goal when it reads Object2:
    <RelOp AvgRowSize="698" EstimateCPU="0.814711" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"
EstimateRows="1181.27" EstimateRowsWithoutRowGoal="194907"
LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="3.89162">

The ORDER BY plan does not.  It will need to sort all filtered rows before figuring out which 1000 to return.
<RelOp AvgRowSize="51" EstimateCPU="0.214555" EstimateIO="1.3572" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"
EstimateRows="194907"
EstimatedRowsRead="194907" LogicalOp="Index Seek" NodeId="17" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="1.57175" TableCardinality="42781400">


PostgreSQL partition hierarchy design for centralized logging I am looking into setting up logging from all of my servers into a centralized postgresql database. It makes sense to be able to drop logs by date or by host, so I'd like to set it up with partitioning, but a multi-layer partition:host-service inherits service, host-service-yyyymm inherits host-service.

I've worked out an example scheme for a specific service (php errors in this case) and am looking for some PostgreSQL experts to critique it for obvious performance bottlenecks.

// SET UP MASTER PHP LOG TABLE //
CREATE TABLE php (
 log_id     int not null,
 host       char(5),
 logdate     date not null,
 message        text
);

// SET UP HOST-SPECIFIC 'PARTITIONS' //
CREATE TABLE host1_php (
 CHECK ( host = 'host1' )
) INHERITS (php);

CREATE TABLE host2_php (
 CHECK ( host = 'host2' )
) INHERITS (php);

// SET UP HOST-SPECIFIC TIME 'PARTITIONS' //
CREATE TABLE host1_php_2011m12 (
 CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' )
) INHERITS (host1_php);

CREATE TABLE host1_php_2012m01 (
 CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' )
) INHERITS (host1_php);

CREATE TABLE host2_php_2011m12 (
 CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' )
) INHERITS (host2_php);

CREATE TABLE host2_php_2012m01 (
 CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' )
) INHERITS (host2_php);

CREATE INDEX host1_php_2011m12_logdate ON host1_php_2011m12 (logdate);
CREATE INDEX host1_php_2012m01_logdate ON host1_php_2012m01 (logdate);
CREATE INDEX host2_php_2011m12_logdate ON host2_php_2011m12 (logdate);
CREATE INDEX host2_php_2012m01_logdate ON host2_php_2012m01 (logdate);

I'll also be adding services such as apache access/errors.

I think I have to trigger to insert into the host_service (to utilize the host check constraint), and then each host_service must trigger to insert into the host_service_yyyymm table.

What can I expect performance-wise of such a trigger/partition scheme?

Some extra info discussed in chat:

PostgreSQL version 9.1.2
Queries on data won't be very often, and mostly on the current month of data for multiple hosts.
PHP is only about 1 per minute insert, but total apache is probably going to be around 300-500 per second across all hosts.

What can I expect performance-wise of such a trigger/partition scheme?

Context switches mean using triggers is always going to use a lot more CPU than a simple insert. The script below can be used to quantify how much impact that will have - and also demonstrates auto-creating partitions using triggers and compares the performance either way.

Please note I haven't included any indexing, or any consideration of update statements.

begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
--******** the above creates a nice clean schema as a test area
--
set client_min_messages to warning; --******** or you get a lot of "NOTICE:  merging column "xyz" with inherited definition" notices
--
create table phpheap(log_id serial not null, host text not null, logdate date not null, message text not null); --******** This table is used to compare 'insert' performance with that on the partitioned version
create table php(log_id serial not null, host text not null, logdate date not null, message text not null);
--
create function php_host_insert() returns trigger language plpgsql security definer as $$
begin
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
exception when undefined_table then
  execute 'create table php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id int not null, host text not null check(host='''||new.host||'''), logdate date not null check(to_char(logdate, ''YYYYmMM'')='''||to_char(new.logdate, 'YYYYmMM')||'''), message text not null) inherits (php_'||new.host||')';
  execute 'insert into php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
create function php_insert() returns trigger language plpgsql security definer as $$
begin
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
exception when undefined_table then
  execute 'create table php_'||new.host||'(log_id int not null, host text not null check(host='''||new.host||'''), logdate date not null, message text not null) inherits(php)';
  execute 'create trigger trig_insert_php_'||new.host||' before insert on php_'||new.host||' for each row execute procedure php_host_insert()';
  execute 'insert into php_'||new.host||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
create trigger trig_insert_php before insert on php for each row execute procedure php_insert();
--
\timing on
insert into phpheap(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 100000
--Time: 1102.140 ms
insert into php(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 0
--Time: 35615.498 ms
insert into php(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 0
--Time: 34074.579 ms
\timing off
--
--******** Now we replace the trigger functions with the 'normal' kind that don't auto-create partitions
--
create or replace function php_host_insert() returns trigger language plpgsql security definer as $$
begin
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
create or replace function php_insert() returns trigger language plpgsql security definer as $$
begin
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
\timing on
insert into php(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 0
--Time: 28457.146 ms
\timing off
--
rollback;


APEX 5: Page item (Select List) with LOV must have values from a another table which return PK (FK of current table) I have a page form where the user can make a new account. Every user has a type. The user must choose which type of user it is.
I have two tables (Users and UserTypes). What I want to do is user must choose the type from a select list which contains UserTypes.usertype in a table which has UserType_id a PK and FK in USERS.

How to make every value of that select list represent it's PK? Thanks.

You have to change your Select List type. Choose SQL query in List of Values section, as a source of a list write following:

select usertype, usertype_id from usertypes


Partition by year and sub-partition by month mysql I have a database with a date column having datatype as date. I am using mysql 5.6

The table is expected to have 10k rows each day. I want to create partition on the table on year range and then sub partitions on months of that particular year.

I have tried is,

ALTER TABLE nav_master
    PARTITION BY RANGE( YEAR(nav_date) )
    SUBPARTITION BY HASH( MONTH(nav_date) )
    SUBPARTITIONS 12 (
        PARTITION january VALUES LESS THAN (2),
        PARTITION february VALUES LESS THAN (3),
        PARTITION march VALUES LESS THAN (4),
        PARTITION april VALUES LESS THAN (5),
        PARTITION may VALUES LESS THAN (6),
        PARTITION june VALUES LESS THAN (7),
        PARTITION july VALUES LESS THAN (8),
        PARTITION august VALUES LESS THAN (9),
        PARTITION september VALUES LESS THAN (10),
        PARTITION october VALUES LESS THAN (11),
        PARTITION november VALUES LESS THAN (12),
        PARTITION december VALUES LESS THAN (13)
    );

I am able to execute the above query on empty table. But when I insert or when I execute the above query in same table with some data I get the below error.

#1526 - Table has no partition for value 2014

Not able to understand what the exact error is.

Also let me know if sub partitions help in better optimizing the tables or I should just go with partitions?

Update: I was indeed able to create the partition. The issue now is that there is a NULL partition created. When I had ensured there is no null dates and also all the date conditions were handled while making partitions.

Question is how can I see what data has gone to NULL partition? So that I can get an idea on what the problem is.

I am getting a null partition name using this query.

SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'nav_master' limit 0,180

Using this query to get all rows in partition, question is what to pass as partition name for null?

SELECT count(*) FROM nav_master PARTITION(??);

I had to do the same thing and solved slightly differently. Basically as far as I understand by reading the docs MySQL subpartitioning does not support partition types besides HASH and KEY.

  In MySQL 5.6, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning. This is also known as composite partitioning.

This means that we can't determine in what subpartition a record will end up. It's up to MySQL. So I don't think it's wise giving such names to your subpartitions (i.e. january) because no way you're going to know if stuff created on that month will end up there. This because you can't subpartition by MONTH(nav_date) but only by HASH(MONTH(nav_date)) or KEY(MONTH(nav_date)).

So to solve the problem I decided to create a new month column in my table and then I added an index to it. Then I subpartioned by KEY(month) without caring about the subpartition names. This way in MySQL 5.6 I could select the main partition from the FROM clause and the subpartition by specifying the month in the WHERE clause.

Follows the full example:

CREATE TABLE `my_example` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `value` double DEFAULT NULL,
    `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
    `timestamp` datetime NOT NULL,
    `last_modified` datetime NOT NULL,
    `month` tinyint(1) NOT NULL,
    PRIMARY KEY (`id`,`timestamp`, `month`),
    KEY `in_is_deleted` (`is_deleted`),
    KEY `in_last_modified` (`last_modified`),
    KEY `in_timestamp` (`timestamp`),
    KEY `in_month` (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE (YEAR(`timestamp`))
SUBPARTITION BY KEY (`month`)
SUBPARTITIONS 12 (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015); -- will go through all twelve subpartitions
EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015) WHERE month = 9; -- will look only into one subpartition


Securely deleting data from SQL Server logs Is there a good way to purge data from a particular DB's *.LDF?

A little background... I'm a SysAd, not a DBA. I'm running SQL Server 2008 R2 for one of my applications and I'm not particularly concerned about the size of the logs. However, in order to meet certain DSS compliance, I occasionally have to purge certain data from the database and ensure that it is in no way recoverable.

I'm under the impression that if I go in and DELETE the particular entries I should be fine as far as the *.MDF is concerned. But I still need to make sure there is no way to recover the data from logs.

The SQL Server log contains all the data that was written to the database (in some form). So, you are right to be concerned about it.

The log is a ring buffer made up of virtual log files (VLF). Once a VLF is not needed anymore, it can be reused. However it will not be reused until the "write head" comes around to that VLF again.

The only difference between simple and full (with a few small exceptions) recovery model is how quickly a VLF is "not needed anymore". If you use full recovery mode, a VLF is "needed" at least until a log backup has been taken. (A full backup never clears the "needed" flag of a VLF.)

The only way to force a VLF to be overwritten is to first make sure that it is not needed anymore and then pump enough transactions into the database that the VLF gets reused. That however is probably not acceptable to you.

Another way to force the data out is to shrink the log file. If you can take downtime, follow this process:

Set the database to READ_ONLY
Run the CHECKPOINT command.
take a log backup (if in full recovery)
make sure other log-readers (replication, CDC, ...) have caught up
set the database to READ_WRITE
create a new log file
empty the original file migrating the data to the new (DBCC SHRINKFILE, there should not be any data left to migrate at this point, but you still need to execute this step.)
drop the old log file
take another log backup (if in full recovery)

You still need to test this, but this should get you to where you need to be.

If you can't take downtime, follow the same process without setting the database to READ_ONLY. However, you run the risk not to be able to have a complete clean cutover, but if you do this regularly you still should be OK.

Note, as some of the commenters have mentioned, a delete does not actually remove the data from the MDF files either. However, that can be dealt with in a similar fashion:

rebuild all indexes on the offending table(s) (or better all tables)
create a new file
empty the old file, migrating the data to the new file using DBCC SHRINKFILE
drop the old file

Step 1 is necessary as the file migration does not change page contents, so single deleted rows might survive the move. Rebuilding the indexes should use new pages.

Additional notes:

The log cleanup has to happen after the data file cleanup as there is a chance that the data file cleanup moves some of the data into the log file.

Index rebuild operations and also a lot of data access operations utilize tempdb, so you have to shred that too. Tempdb is recreated on server restart, so you can stop the SQL Server service, delete the tempdb files and then start the service again.

Finally, this all causes a lot of blocks (~ database pages) to be unlinked from existing database files. This unlinking (due to file shrinkage or the deletion of files) does not force those blocks to be overwritten. So it is recommended to create the new files in the above processes on a separate disk and afterwards securely wipe the original disk.