Master - Master replication Duplicate entry error I've got a Master - Master replication setup and it worked fine for a couple of days but I keep getting: Duplicate entry errors after a few days.

My setup is as follows: One server in The Netherlands and one server in Washington D.C. both servers are running Windows Server 2012 R2 and MySQL 5.6.15 X64.

I've followed this tutorial.

My my.ini file contains the following:

Netherlands server

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=joomlatest
binlog_do_db=sapp
binlog_do_db=tcadmin
binlog_do_db=whmcs

Washington D.C. server

server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=joomlatest
binlog_do_db=sapp
binlog_do_db=tcadmin
binlog_do_db=whmcs

Both servers keep stopping the slave and both report the same: Duplicate entry error. For example:

  Error 'Duplicate entry '4353' for key 'PRIMARY''
  on query.

How can I prevent this from happening?

The tutorial you linked to is, I'm afraid, very optimistic. It forgets to note that it only covers the case of an auto generated AUTO_INCREMENT PRIMARY KEY.

The methods it suggests do not cover the general case for a UNIQUE KEY, including, of course, a general PRIMARY KEY.

The article does mention that "The most common problem with replication is primary key collision". It then completely fails to mention all the other problems...

So, anyway, any UNIQUE KEY collision will render your replication broken, possibly in both directions.

Which table was it that failed your replication? Was that a table with an AUTO_INCREMENT PRIMARY KEY or a "general" PRIMARY KEY (ie on non-AUTO_ICNREMENT columns)?

To solve the general case of unique keys, your application must be very aware of its location (Netherlands or Washington DC), and must refrain from writing data that may cause collision. Alternatively it might want to use some synchronizing/locking mechanism; that would of course induce latency as data must cross the Atlantic.

Might I also suggest that the use of binlog_do_db is also naive and dangerous. Are you sure you don't want to replicate the mysql schema? What happens when you add new schemas? The general correct solution is to have full replication without filtering, or otherwise have a very good explanation why not to do so.

Finally, I highly discourage the use of active-active master-master replication. I had customers using it in the past. The amount of trouble they went into because of that setup, and in spite of my counter-recommendations, led to many ruined weekends and holidays.

You might want to look at Galera replication. It's a synchronized master replication solution, and I hear it works over WAN. This will allow your application to remain ignorant. Watch out for network failures, as one part of your cluster will turn immutable.


Why does my SQL Server Express (2014) connection break after restarting my computer? For some reason when I install SQL Server Express (2014), it works fine for a while (usually a day or two), and then quits allowing me to sign in.

I'm not just talking about remotely here, either. Even through the Management Studio, I get:

I sign in using Windows Authentication, and as I said, it DOES WORK fine for a little bit, and then, even though I haven't made any changes, of any kind to the system, the program, or even the database it's linked to, it just decides to quit working until I uninstall/reinstall SQL Server Express (2014), and then it works fine again for a day or two.

I've done this 3 times now, and this is the third time it's decided to spontaneously quit working.

I've tried all the tricks on this website (accepts remote connections, firewall, connection string, starting SQL Browser, ): http://www.sswug.org/articlesection/default.aspx?TargetID=44331 but everything seems in order, and again, it works for a while after install each time.

What could possibly cause this to happen? I don't know where else to look. Should I just go with the (seemingly) more stable SQL Server CE (despite its limitations). Should I go with SQL Server Express 2012, instead? Is 2014 just buggy?

The problem is very likely related to your machine; not SQL Server 2014 itself.

A few things I would start checking when the same issue happens:

SQL Server Error Log
Windows Event Log
SQL Server Service Account state and password (is the account disabled or locked?)
Try pinging the IP of SQL Server
Try connecting by forcing TCP, using tcp:192.168.0.1,1433 (replace 192.168.0.1 with the actual IP address)
Try running SysInternal's Process Monitor to see what is happening exactly while attempting the login process.
Is the Kerberos SPN set correctly?

I very much doubt the SQL Server team would release buggy products which even starts failing at login.  The logs will definitely give a clue about what is happening, perhaps the installation logs also show something relevant.


Can we set LOCK_TIMEOUT instance- or database-wide? It is possible to change the SQL Server's default (which is -1 or indefinite period of time) LOCK_TIMEOUT value session-wide.

Is it possible to set LOCK_TIMEOUT default value server-, instance- and/or database-wide? If not, why not?

No it isn't possible to set this as a default and change it from -1 (no timeout).

If it needs to be changed, you have to be explicit with a call to SET LOCK_TIMEOUT <number of ms for timeout>.

As for why there is no way to alter the default, we can only speculate.  But my guess would be that the lack of a timeout would satisfy the majority of requirements for queries, and need to have a lock wait timeout is a pretty corner case scenario, requiring a simple SET statement.

Regardless, you're talking about client-side handling.  Not server-side.  In fact, most providers that I work with by default enforce a command timeout (i.e. System.Data.SqlClient.SqlCommand.CommandTimeout is set to a default of 30 seconds).  Although a bit of a nested requirement with lock requests, it's the same idea.


MongoDB: is order guaranteed deep into the aggregation pipeline? I'm trying to get the "top" 2 items for any number of foreign keys, sorted by some key. What I have is the following, which appears to work:

db.getCollection('col1').aggregate([
    {$match: {fk: {$in: [1, 2]}}}
    {$sort: {name: -1}},
    {$group: {_id: "$fk", items: {$push: "$$ROOT"} }},
    {$project: {items: {$slice: ["$items", 2]} }}
])

My question is: does MongoDB guarantee that the sorting I apply in the second step in my aggregation via $sort will be maintained when I get down to the $slice inside my $project in the last step of my aggregation?

The data I'm seeing indicates that MongoDB is currently behaving this way, but I'm wondering if this is spec'd or if MongoDB is free to return the items array in a different order if the optimizer ever decides it would be advantageous to do so.

Your conclusion is correct: internal optimisations or implementation details may alter the order of results depending on the stage.

The $group stage is intentionally not order preserving, so you cannot rely on observational behaviour.

As noted in discussion on SERVER-24799: $group aggregation command should maintain document order in the MongoDB issue tracker:

  The reason the order is not preserved is that we keep track of all groups with a map from the _id to the accumulated value, so that we can quickly look up which group an incoming document belongs to.

As per Aggregation Pipeline Optimizations, some pipeline stages may also be coalesced or reordered to equivalent pipelines with improved performance. You can use the explain:true option to return information about how a pipeline will be executed.


How to fix MySQL anonymous account security issue? How to fix this security issue on MySQL?

[!!] User '@localhost' is an anonymous account.

Do you want to remove the anon users? You can just use DROP USER to remove them. They shouldn't have any special privileges, as stated in the MySQL Docs and should not be a major problem as they're only in the localhost.

If, even removing, you still can login or something, check out this other question.


Microsoft SQL Server AG Synchronous Mirroring is not synchronous We were upgrading to SQL Server 2012 and decided we wanted to divorce ourselves from replication we were using for Reporting services on Node 2.  AG and mirroring looked like a good solution -- and we went with synchronous.  It was also decided that app's would write to node 1, and read from node 2 to minimize contention on the data.

One app was written to insert the data, and then they check immediately on node 2 whether the data they inserted is present.

Now from my understanding, synchronous mirroring on AG should receive the request, send the data to node 2 -- node 2 commits, then tells node 1 its done and then node 1 returns to the user..

What occurred was not that at all -- they sent the data, did a retrieval from node 2 -- data did not match. Error.

I checked and double checked settings, and had another DBA who had been using AG's and synchronous mirroring look the stuff over -- and our best conclusion is that from what we are seeing Microsoft's 'synchronous' mirroring is NOT synchronous.  Has anybody else seen this?

This is completely expected. I've talked about this before but I'll recap it for you.

There could be multiples reason you are seeing this behavior. The two that most people are confused or inaccurate of how it works are how availability groups synchronize data and how queries on readable secondary replicas work.

Please note the below is ONLY pertinent for SQL 2012 and 2014, and to some extent 2016.

How Data Synchronization Works (brief overview)

There are two types of replica synchronization, synchronous and asynchronous. The way the data synchronization happens in both is exactly the same. The way that SQL Server behaves, though, is different. When synchronous is used we wait for the data to be HARDENED on the secondary replicas. This means it only needs to be acknowledged that it was successfully written to the log, not that the log block shave been successfully redone. Asynchronous does not wait for the status message and just continues.

Thus it is entirely working properly, however there seems to be a misunderstanding as to how it works. AGs ship log blocks, not transactions, thus the entire transaction may not be shipped together and may not even be redone yet.

Querying Readable Secondary Replicas

When you run a query on a secondary replica, the read committed (default) isolation level is silently mapped to snapshot isolation under the covers... whether or not you have SI or RCSI enabled for the database.

Since snapshot works by row versioning and is consistent from the beginning of the transaction, you may not be able to see new data as it comes in per how snapshot isolation works. This is entirely working as intended.

The other point to keep in mind is that just because you have the acknowledgement that the data was hardened to the log on the synchronous secondary replica does not mean that the REDO thread has redone those log blocks yet. Thus, just because you have an acknowledge on the primary doesn't mean REDO has completed on it, only that it has been hardened to the log. Additionally, if your redo thread is blocked it may take a long time (or never) to not be blocked and thus your redo queue size will grow.

In the end I can totally expect the behavior you are witnessing, however it doesn't mean that it isn't synchronous. It just means it doesn't necessarily work the way it was believe or thought to. Hopefully this clears the confusion.


Reason for using hexadecimal in NCHAR()? I found this in some source code today:

        SELECT
            @Error = ERROR_NUMBER (),
            @ErrorMsg = N'An Error occured while populating the TABLE ' + @DestinationTableName +
                        N' with data. Error Code: ' + CAST ( @Error AS nvarchar(20) ) +
                        N', Error Description: "' + ERROR_MESSAGE () + N'".'
                        + NCHAR(0X0D) + NCHAR(0X0A) + NCHAR(0X0D) + NCHAR(0X0A),
            @ErrorSeverity = ERROR_SEVERITY (),
            @ErrorState = ERROR_STATE () ;

It looks like the error message string is adding two line feeds after the description. 0X0D is 13 and 0X0A is 10.

Is there a reason to use hexadecimal instead of just integers?

Normally what I've done is NCHAR(13) + NCHAR(10)...

Yes, there is the obvious readability reason. For programmers, decimal numbers are meaningless. What the heck is 3221225477? But 0xc0000005 you recognize it immediately. 2147500037? Rings no bell, but 0x80004005 uberwellknown. These are value that carry meaning, you recognize them at a glance after seeing them every day in the debugger... It becomes second nature.

And no, I'm not kidding nor being sarcastic. I'm very serious. I would had also use 0x0D and 0x0A. If I see 13 I would have to think what value that is.


Consistent InnoDB dumps with mysqldump The MySQL documentation recommends that when dumping an InnoDB with mysqldump to use --single-transaction to get a transactionally consistent snapshot of the database with minimal blocking of other transactions. Unfortunately, this only works if you can somehow prevent any other connection from executing a schema change during the dump:
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

While a --single-transaction dump is in process, to ensure a valid
dump file (correct table contents and binary log coordinates), no
other connection should use the following statements: ALTER TABLE,
CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent
read is not isolated from those statements, so use of them on a table
to be dumped can cause the SELECT that is performed by mysqldump to
retrieve the table contents to obtain incorrect contents or fail.

What is a good way of ensuring that no schema changes happen during a dump? My use case is that I periodically (cronjob) back up my database every hour using the mysqldump utility.
I thought that maybe using mysqldump's default of --lock-tables instead would give me transactional consistency, but:

The --single-transaction option and the --lock-tables option are
mutually exclusive because LOCK TABLES causes any pending transactions
to be committed implicitly.

Is that saying that if someone runs a mysqldump --lock-tables against an InnoDB that it can actually cause an ongoing transaction in another session to no longer be transactional? I hope not -- that seems insane. Elsewhere:
https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#lock-tables-and-transactions

LOCK TABLES and UNLOCK TABLES interact with the use of transactions as
follows:
LOCK TABLES is not transaction-safe and implicitly commits any active
transaction before attempting to lock the tables.

This makes sense within the context of a single DB session. If you in the middle of a transaction on your session and do this, then it implicitly commits your open transaction first. It isn't clear what it does, if anything, to transactions on other sessions. I'm hoping that it wouldn't do anything, or rather that it would be consistently ordered with respect to any ongoing transactions on other sessions.
Am I just reading too much into the mysqldump docs and using --lock-tables achieves transactional consistency?
Are the mysqldump docs simply explaining that doing --single-transaction together with --lock-tables is nonsensical because --lock-tables would immediately and implicitly close the single transaction? (Couldn't the reverse order work? But maybe it would be pointless?)
Is mysqldump --lock-tables otherwise transactionally consistent?
Thanks!

DDL changes implicitly commit only if they are in the same connection. As they are in a different connection from the mysqldump they lock wait until the dump is complete and then they will complete.
As such hopefully DDL changes aren't as time critical and --single-transaction will suit your needs without any addition fiddling.
Alternate would be to create a replica server there and backup from that. An DDL in the replication stream will wait however it won't impact the user or the backup.


Please explain why deadlock is detected in my example I think Deadlock will happen when T1 lock tabA and T2 lock tabB. Then querying T1 to tabB (locked by T2) and T2 querying to tabA (locked by T1). So T1 and T2 will wait and wait till end of life!
But in my example: Running T2 after executing T1, T2 will wait till T1 is committed. Then running T3 (maybe T1's lock had been released by some special reasons that I don't know because T2 executed complete). So, why deadlock happen at T3? I can't understand. T3 should wait till T2 is committed (like T2 waiting T1 is committed) or T1's lock is not released yet or another reason...?

drop table TabA;
create table TabA (a int, b int);
insert into TabA values (1,11);
insert into TabA values (2,22);
insert into TabA values (3,333);
/
select * from TabA;
/
--T1. Run T1 first.
begin tran t1
insert into TabA values (5,55);
update TabA set b = 44 where a=4;
select * from TabA where a=4;
waitfor delay '00:00:10';

--T2. Run T2 after T1 (run continuously, it must after cooldowning 10s).
--T2 is still waiting T1 is committed.
begin tran t2
insert into TabA values (4,444);
update TabA set b = 111 where a=1;

--T3. Run T3 after T1 is completed.
--Deadlock is detected at there and T2 is executed complete.
select * from TabA where a=1;

I use 2 MS-SQL 2008 R2 client (test with my friend using 2014 is okay). T1 and T3 is belong to client #1 and T2 is belong to client #2:

Client #1:
--T1
--T3
Client #2:
--T2

Ordinarily query by: T1>T2>T3.
//I will find and try with another PC using MS-SQL 2008 R2 later.

Simplified version

drop table TabA;
create table TabA ( a int
,                   b int );
insert into TabA
values ( 1, 11 ),
    ( 2, 22 ),
    ( 3, 333 );
select * from TabA;

--T1. Run T1 first.
begin tran t1
insert into TabA values (5, 55 );

waitfor delay '00:00:10';
--Run T2 in the other session within 10 sec.

-- After delay expires deadlock is detected
select * from TabA where a = 2;

commit tran t1;

Second script

--T2.
begin tran t2
update TabA set b = 111 where a=1;
commit tran t2;

Looking at deadlock graph (MS SQL 2008 Profiler), you'll see

1) T1 has inserted (5,555) row and holds X lock on this RID untill commit.

2) T2 tries to get U lock on all rows, check predicate a=1 and update the rows where the predicate evaluates to true.
2.1. It successfully updates one a=1 row and holds X lock on this RID untill commit.
2.2. When T2 tries to get U lock on (5,555) row it can't do that due to T1's lock. So T2 waits for T1 to commit.

3) After delay T1 tries to get S lock on all rows when executing SELECT * FROM TabA ... and naturally it can't obtain the lock on the row held by T2 (see 2.1). T1 must wait T2 which waits T1. Deadlock.

Now add PK to TabA

create table TabA ( a int PRIMARY KEY,
                   b int );

Sql sever is now deadly sure it needn't check any more rows for a=1 at step(2.1). T2 commits immediately and no dead lock detected.


SQL Server 2014 Latest CU / Service Pack Where can you find the latest SQL Server 2014 Cumulative Update (CU) and/or Service Pack?

Note: I added this question and answered my own question to make this easier to find for others in the future.

At the time of this question, the first several pages of Google results (when searching for "SQL Server 2014 CU") only mention CU 1-4, with no mention of the latest CU 5.  http://lmgtfy.com/?q=sql+server+2014+cu

For the latest CU and builds (this article will also include service packs when released):
SQL Server 2014 build versions

As of 2015-01-13, SQL Server 2014 is currently on CU5, which was released Dec. 17, 2014.
Service pack 1 has not yet been released.

Another similar article, specific to Service Packs:
How to obtain the latest service pack for SQL Server 2014


Why does ENCODE on BYTEA not seem to have any performance impact? I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64).

Initial thought was to enforce a valid hex string storing it as BYTEA will be good idea, but impact I evaluated was the use of ENCODE on select queries.

I did some performance benchmarks, considering both tables have few million rows;

# A file query_with_char.sql
SELECT "key" FROM table_varchar;;

# A file query_with_binary.sql
SELECT ENCODE("key", 'hex') FROM table_binary;

pgbench -c 30 -T 120 -n -f ./query_with_binary.sql -f ./query_with_char.sql -P 5 -S my_db

SQL script 1: ./query_with_binary.sql
 - weight: 1 (targets 33.3% of total)
 - 236 transactions (34.6% of total, tps = 1.876072)
 - latency average = 8896.888 ms
 - latency stddev = 2548.701 ms
SQL script 2: ./query_with_varchar.sql
 - weight: 1 (targets 33.3% of total)
 - 225 transactions (33.0% of total, tps = 1.788628)
 - latency average = 7164.604 ms
 - latency stddev = 2209.866 ms

I am unable to understand why the performance of query ENCODE is faster compared to normal string. How is PostgreSQL able to do encoding on million rows faster than just fetching the string columns?

Can someone explain what might be wrong in the above test?

encode() is a very cheap function. I would not expect any measurable impact at all  in your test.

The difference is almost certainly due to the much smaller storage size of bytea as compared to char(64). Consider:

SELECT pg_column_size('90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271'::char(64)) AS size_char64
     , pg_column_size(decode(text '90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271', 'hex')) AS size_bytea;

 size_char64 | size_bytea
-------------+------------
          68 |         36

The dominant factor for performance of simple SELECT queries is the number of data pages that have be read.

"Best" data type?

  Obviously the choices are BYTEA and CHAR(64).

If your goal is to optimize performance, consider a third option:
2 uuid columns

To understand, first read:

What is the optimal data type for an MD5 field?

And:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

Then consider this demo (executed on pg 11, but true for all modern versions):

RAM size:

SELECT pg_column_size(t64)                   AS c_text
     , pg_column_size(t64::char(64))         AS c_char64
     , pg_column_size(decode(t64, 'hex'))    AS c_bytea
     , pg_column_size( left(t64, 32)::uuid)
     + pg_column_size(right(t64, 32)::uuid)  AS c_2x_uuid
FROM  (SELECT text '90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271') t(t64);

 c_text | c_char64 | c_bytea | c_2x_uuid
--------+----------+---------+-----------
     68 |       68 |      36 |        32

Disk size (compressed format):

CREATE TEMP TABLE c64 AS
SELECT t64                    AS c_text
     , t64::char(64)          AS c_char64
     , decode(t64, 'hex')     AS c_bytea
     , left (t64, 32)::uuid   AS c_uuid1
     , right(t64, 32)::uuid   AS c_uuid2
FROM  (SELECT text '90b7525e84f64850c2efb407fae3f27190b7525e84f64850c2efb407fae3f271') t(t64);

SELECT pg_column_size(c_text)    AS c_text
     , pg_column_size(c_char64)  AS c_char64
     , pg_column_size(c_bytea)   AS c_bytea
     , pg_column_size(c_uuid1)
     + pg_column_size(c_uuid2)   AS c_2x_uuid
FROM   c64;

 c_text | c_char64 | c_bytea | c_2x_uuid
--------+----------+---------+-----------
     65 |       65 |      33 |        32

db<>fiddle here

The seemingly minor difference between 33 and 32 bits can actually make a difference of 8 bytes, because several storage mechanisms require padding at multiples of 8 bytes.

Calculating and saving space in PostgreSQL

Repeat your test with 2 UUIDs. I am confident it will come out on top.


SQL Server -- More efficient alternatives to repeating an (almost) identical subquery I need to improve the efficiency of multiple subqueries within a WHERE clause.  The context is a report generator on an existing product.  The only input I can provide is the WHERE clause.  I have 3 almost identical subqueries with only 1 difference between them.  I need to verify that there is at least 1 record that matches each condition.

Here is the gist of the query

Where
  Exists (Select 1 From t1  ...etc... where ...etc... and fld1='X')
  AND
  Exists (Select 1 From t1  ...etc... where ...etc... and fld1='Y')
  AND
  Exists (Select 1 From t1  ...etc... where ...etc... and fld1='Z')

The "Select" in each of the 3 subqueries is identical. It does Join and has other fields in it's Where clause including a reference to the PK from the outer query : I'm only showing what's different.

There are about half a million records in t1.  The query above has been running now for over 30 minutes.  I need to get it down to 30 seconds.

My options are extremely limited -- No DECLARE, no temporary tables, no "OVER".

Hard to say for sure without seeing the full schema and queries, to work on the execution plans. But you could try:

Where
  (Select Count(Distinct fld1) From t1  ...etc... where ...etc... and fld1 In ('X', 'Y', 'Z')) = 3


MySQL master slave replication I had MySQL master/slave replication working properly but suddenly replication stopped and accidentally someone has changed mysql.binxxx file in salve so I am getting this error

  ERROR: 'Got fatal error 1236'.

So I checked latest mysql.bin and log poss from master and changed in slave so replication started but now again new mysql.binxxx created in server so again I am getting fatal error. Is there any fix solution for this without any data loss.

If your instance is not that big, your best bet would be to recreate your slave from scratch.

You won't be losing data since from what I understand, your master is fine.


MySQL Performance Schema My company hosts a mysql database on google and we do not have complete control over the customization involved. Recently we attempted to turn performance schema on to help debug some common complaints about our software. During the restart that was required after adjusting the flag the database was continually failing to start which caused massive problems with our customers as you can imagine.

At this point it looks like performance schema and everything that falls under its umbrella is essentially out of the question. Are there any other tools or ways to analyze a mysql database if the performance schema features are inaccessible? Aside from looking at the execution plan of every query manually.

The slowlog is my favorite means of quickly identifying performance problems.  (I don't care for the performance_schema.)  Set long_query_time=1 globally, turn on the slowlog, wait a day, then look at the log

The slowlog can be sent to a file.  If you don't have access to the filesystem, what version of MySQL you are using?  In newer versions, the slowlog can be sent to a table, thereby avoiding the need for access to the disk.

If possible, use pt-query-digest to summary the slowlog file.  The first few queries will be the "worst".  We can help you speed them up, possibly by adding a "composite" index, possibly by modifications to the query, possibly by schema redesign, etc.


sys.dm_hadr_database_replica_states, understanding the different *_time columns I am going nuts trying to understand the meaning of the different time columns in the sys.dm_hadr_database_replica_states system table.

We have a three node Always On Availability Group, where two of the nodes are sync and one async.  We want to use the async node for reporting and want to find an accurate way to calculate the delay between the primary and async replica.

Below is a snapshot of a query:

select synchronization_state_desc,
       is_primary_replica,
       last_sent_time,
       last_received_time,
       last_hardened_time,
       last_redone_time,
       last_commit_time
from sys.dm_hadr_database_replica_states DRS
where database_id = db_id('DBname')

Edited the post to narrow down the question

If I need to find the actual delay it takes for the data to appear on async node, which columns do I use?

For a starter, here's the Microsoft Docs Page

As an aside, I suggest you do some reading into the mechanics of an availability group, purely so you can understand my answer. (Microsoft Docs Page)

As for the answer to your question, as normally in the case with questions around an RDBMS, it depends.

In this case is depends on what you mean by "...for data to appear"

Let's have a look at the columns in your question:

last_sent_time,
last_received_time,
last_hardened_time,
last_redone_time,
last_commit_time

It is also worth noting that the System DMV you are referring to is at the database level

last_sent_time

This time indicates the last time that the PRIMARY sent a Log Block to the available secondaries. This is the start of the data synchronisation process.

last_received_time

This indicates the last time that the secondary received a log block.

last_hardened_time

This indicates the last time that the secondary cached the recieved log block data to disk.

last_redone_time

This is the time that the last LSN was redone on the target database.

last_commit_time

This is the time of the last commit record was redone and reported back to the primary.

Summary

Of the above, there are various entry-points of the data into the secondary systems.

The data first enters the server into memory at last_received_time

The data first enters the server on disk at last_hardened_time

The data first enters the database data files at last_redone_time

The data first becomes committed and available for reading by queries (outside of strange NOLOCK situations) at last_commit_time

I suspect that the answer to your question is the latter of the 4 concepts. There is a small overhead to the time in this column however, due to the transmission time of the data between the SECONDARY and PRIMARY. This is likely to be unimportant in calculations for determining the speed of data throughput though.


Optimal design for a large database Brand new to databases, using postgresql on linux. I've done a decent amount of reading on the issue and looked through similar questions on here, but just want to confirm the most efficient way of organizing everything.

So say for example we have entries for a million people, who each have a name, phone number, address, and between 20 to 50 "skills". Would the correct way be to have one table with the basic person properties along with a unique identifier, and then create a second table with two columns containing the person's ID and skill name?

Seems a little redundant to me, but the other option of having a variable number of skill columns in the person table seems even worse.

Any tips to maximize speed and minimize memory usage?

This is a classic situation where you use a "joining table".

You will need three tables.

Person
Skill
Person_Skill

(note that table names are singular - this is a preference of
 mine, not a rule).

Person
ID integer PRIMARY KEY
<other person details - name, address, phone &c.>
KEY name (and possibly phone?)

Skill
ID integer PRIMARY KEY
<other skill details - name, description, necessary qualification &c.>
KEY name (maybe not even necessary if there are only 50 skills?)

Person_Skill
Person_ID integer FOREIGN KEY REFERENCES Person(ID)
Skill_ID integer FOREIGN KEY REFERENCES Skill(ID)
Unique Index on (Person_ID, Skill_ID) (to avoid duplicates)
KEY Skill_ID

This way you don't store the skill name, description &c. for every person who has that skill, but just once and then refer to it in the Person_Skill table by its ID - this reduces space and memory requirements and is less error prone (you only store the Skill datum once - one of the core features of an RDBMS under Codd's rules).

That is the way I would do this.


How to do per-tenant backups for multi-tenant databases? Are there any best practices, experiences or guidelines when it comes to do per-tenant backups/restores in a microservice multi-tenant application?

In my case I have multiple Postgres servers which are regularly backed up as a whole. Each of them contains a single database which is migrated using Flyway.
The single database contains one schema with all the tables with the data of all tenants. Tenants are identified by UUID and all the data has foreign key relations so that e.g. one tenant can easily be deleted.

Now there's the requirement to be able to do restores for an individual tenant. I have found very little about the entire topic so far, but the only process I have come up with so far is this:

Restore all the backups to a different set of databases or different database servers
Spin up a new instance of each microservice so that it can start in isolation and is able to perform the necessary database migrations, if required
Delete the tenant from each of the production databases
Execute script or code in each service that identifies the data in the respective restored database and copy exactly these rows over to the production database

However doing or automating this seems like a disproportionate effort to me. I'm specifically looking for alternative (simpler) approaches, tools or databases that can simplify this process or actual experience how other people approach this problem.

Postgres has no feature to ferret out semantically a subset of the rows in your tables. Nor does any other database I’ve heard of.

You will need to write your own code to retrieve just the one tenant’s rows from each of the relevant tables. This code might be in your app, or might be done on the server within Postgres.

Also, you’ll need to decide if any other tables not split by tenant should also be copied into each tenant’s backup.

I have heard about some database vendors considering features to support multi-tenancy explicitly. The database would understand that the data for each tenant should be considered separate and kept in logical and/or physical silos. But I don’t know of any such products  ready for production yet.


Safely quoting type names to protect against SQL-injection How would I quote the type name to protect against SQL Injection. For example, take this

SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int');
                                                 ^ Quote this

That works fine, but if 'int' is instead ''int); DROP DATABASE foo;SELECT (' you'll have some problems,

SELECT FORMAT('SELECT CAST(%L AS %s);', '5.42', 'int); DROP DATABASE foo;SELECT (');
                          format
----------------------------------------------------------
 SELECT CAST('5.42' AS int); DROP DATABASE foo;SELECT ();
(1 row)

How would I go about casting to a type provided in run time safely with Dynamic SQL? If I use %I instead of %s I get an identifier (safe-quoting with ", but type-names are not identifiers and do not work with double-quotes).

Inspired by this answer to "PostgreSQL alternative to SQL Server’s try_cast function" provided by Jasen

How would I quote the type name to protect against SQL Injection.

You do not need format_type() for the purpose. You may have misunderstood Andrew there. All you need is the cast to regtype:

SELECT format('SELECT cast(%L AS %s);', '5.42', 'int'::regtype);

The text representation of the regtype value is the standard SQL name and quoted automatically where needed - and the value is cast to text implicitly for the concatenation. Demo:

SELECT 'varchar(20)'::regtype, 'int'::regtype, '"char"'::regtype;

regtype           | regtype | regtype
------------------+---------+---------
character varying | integer | "char"

format_type() would only serve to add type modifiers (which are lost in the cast), an orthogonal matter. Related:

PL/pgSQL regclass quoting of table named like keyword
How to introspect materialized views
PostgreSQL syntax error in parameterized query on “date $1”
Table name as a PostgreSQL function parameter


Recompiling dependent objects in Oracle I wonder what is the best practice to recompile Oracle 10g programmable objects that become invalid due to changes in one of them.

To be more specific, I have a few packages, that use schema level types. I also have triggers in the system that call functions/procedures from packages.
From what I understand, changes in package body don't require any further actions (except recompiling the body). However, if I change package , I have to recompile bunch of triggers that use this package and other packages that have dependencies on it.

So far I just grabbed list of invalid objects and recompiled them or used DBMS_UTILITY.compile_schema.

Thank you.

The trick is get the packages and dependent code recompiled in one go. If you are refreshing a development database with new data you will have an enormous list of invalid procedures. As well, with dependent code you find yourself recompiling over and over.  You  can use this command located in  $ORACLE_HOME/rdbms/admin/utlrcmp.sql:

BEGIN
 UTL_RECOMP.RECOMP_SERIAL(schema => ‘MY_SCHEMA_NAME’);
END;

(available from 9i but improved for 10g and 11)

If you have packages that are not valid and will not be valid, for whatever reason, you can use this package from Martin Mares which will recursively compile all invalid objects except the ones you specify.

As far as best practice goes I try to limit the dependency chain. It's always a toss up between "Don't repeat yourself" which encourages dependencies and having stand alone packages.

My rule of thumb is that if the package/trigger/object interacts with another database try to reduce dependencies so if there is a issue you have less places to look for sources of the problem.


When using underscores, should they be reserved to junction tables? Say you have the following tables

company (contains companies)
industry (contains industries)
company_type (contains company types, e.g., Public, Private)
company_status (contains company statuses, e.g., Acquired, Operating)
company_industry (junction table between company and industry)

Using an underscore in the company_type and company_status tables makes it a bit difficult to easily glance at the database and tell which tables are junction tables.

Should underscores be reserved for junction tables? If so, how do you deal with a table like company_type? Simply naming it 'type' is non-descriptive, and is a SQL reserved keyword. As well, what if you have school types and you want to keep them separate from company types? I.e., school_type table and company_type table.

As an aside: I'm looking for a good resource on naming in database design. It's the one thing troubling me more than anything else.

If you want to use underscore in that way, try using CamelCase the rest of the time.

Then you'd have CompanyType , and if a company could have many types, you could have a junction table called Company_CompanyType


MS SQL explicit rowversion column overhead? For MS SQL servers, when I declare something like rv rowversion in the table definition, I have access to the rowversion value of the record. Does it have any impact to the size of the table?
If I understand it correctly, the rowversion value is stored for the record independently whether I make the column explicit, or whether it is hidden. So, making the column explicit should not lead to the increase of the size of the record (of the table). Is that assumption correct?

The "hidden" and "explict" row versions are entirely different beasts. The explict rowversion (a.k.a. timestamp misnomer) is part of the table schema with an 8-byte fixed size for all rows. The rowversion column value may be used to determine if a row has changed since it was last retrieved as part of an optimistic concurrency check in either T-SQL or application code.
The hidden rowversion is a 14-byte value in the internal row structure the database engine uses internally to support consistency when either the ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT database options are turned on. Unlike rowversion, this internal value is not part of the schema and not exposed directly. The field (sic) may or may not exist in a given record since it is added and maintained by SQL Server as needed to support row-versioning instead of locking for consistency.
The internal row version field and rowversion column values are independent of one another. Both may exist in the same record.


What subquery do I need to calculate this report? I have two tables: tickets and comments. I need to run a report that shows me which ticket is "neglected" meaning that there has not been a comment in X amount of time.

If I was to do this in PHP, I would load ticket, then do a separate query for each one, see what the most recent timestamp was (max(comment_ts)), and keep the values of the "old tickets" in an array, then display them from there. But, it seems I should be able to do this as a single query via MySQL and it would be much faster, so I am trying to create a view for this.

Assume this is the basic table structure:

-- MySQL Script generated by MySQL Workbench
-- 07/18/14 17:29:39
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`tickets`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`tickets` (
  `ticket_id` INT NOT NULL,
  `ticket_description` TEXT NULL,
  PRIMARY KEY (`ticket_id`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`comments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`comments` (
  `comments_id` INT NOT NULL,
  `comments_comment` VARCHAR(45) NULL,
  `comments_timestamp` INT NULL,
  PRIMARY KEY (`comments_id`),
  CONSTRAINT `comments_bug`
    FOREIGN KEY (`comments_id`)
    REFERENCES `mydb`.`tickets` (`ticket_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

I've got this far (DDL and DML below)

SELECT ticket_id, c_max_date FROM
(
  SELECT ticket_id, MAX(FROM_UNIXTIME(comment.comments_timestamp)) AS c_max_date
  FROM comment
  WHERE comment.comments_timestamp < UNIX_TIMESTAMP(NOW() - INTERVAL 2 YEAR)
  GROUP BY ticket_id
) AS mytab;

+-----------+---------------------+
| ticket_id | c_max_date          |
+-----------+---------------------+
|         3 | 2004-07-09 15:15:30 |
|         4 | 2006-06-30 15:15:30 |
+-----------+---------------------+

These two comments are indeed the ticket number of tickets whose comments haven't been updated in 2 years (arbitrary choice). The dates are the dates of the last update to the ticket comments.

Can you (anyone) put the finishing touch to this and join mytab to tickets? My brain is fried!

CREATE TABLE IF NOT EXISTS ticket
(
  ticket_id INT NOT NULL,
  ticket_description TEXT NULL,
  PRIMARY KEY (ticket_id)
);

CREATE TABLE IF NOT EXISTS comment
(
  comment_id INT NOT NULL,
  ticket_id INT NOT NULL,
  comments_comment VARCHAR(45) NULL,
  comments_timestamp INT NULL,
  PRIMARY KEY (comment_id),
  INDEX ticket_index (ticket_id),
  FOREIGN KEY (ticket_id) REFERENCES ticket (ticket_id)
);

INSERT INTO ticket VALUES(1, 'ticket 1');
INSERT INTO ticket VALUES(2, 'ticket 2');
INSERT INTO ticket VALUES(3, 'ticket 3');
INSERT INTO ticket VALUES(4, 'ticket 4');
INSERT INTO ticket VALUES(5, 'ticket 5');

INSERT INTO comment VALUES(1, 1, 'comment 1 on ticket 1', UNIX_TIMESTAMP('2013-11-30 15:15:30'));
INSERT INTO comment VALUES(2, 1, 'comment 2 on ticket 1', UNIX_TIMESTAMP('2014-02-13 15:15:30'));
INSERT INTO comment VALUES(3, 1, 'comment 3 on ticket 1', UNIX_TIMESTAMP('2014-06-30 15:15:30'));
INSERT INTO comment VALUES(4, 1, 'comment 4 on ticket 1', UNIX_TIMESTAMP('2014-07-09 15:15:30'));
INSERT INTO comment VALUES(5, 2, 'comment 1 on ticket 2', UNIX_TIMESTAMP('2013-11-30 15:15:30'));
INSERT INTO comment VALUES(6, 2, 'comment 2 on ticket 2', UNIX_TIMESTAMP('2014-02-13 15:15:30'));
INSERT INTO comment VALUES(7, 2, 'comment 3 on ticket 2', UNIX_TIMESTAMP('2014-07-30 15:15:30'));
INSERT INTO comment VALUES(8, 2, 'comment 4 on ticket 2', UNIX_TIMESTAMP('2014-08-09 15:15:30'));
INSERT INTO comment VALUES(9, 3, 'comment 1 on ticket 3', UNIX_TIMESTAMP('2003-11-30 15:15:30'));
INSERT INTO comment VALUES(10, 3, 'comment 2 on ticket 3', UNIX_TIMESTAMP('2002-02-13 15:15:30'));
INSERT INTO comment VALUES(11, 3, 'comment 3 on ticket 3', UNIX_TIMESTAMP('2004-06-30 15:15:30'));
INSERT INTO comment VALUES(12, 3, 'comment 4 on ticket 3', UNIX_TIMESTAMP('2004-07-09 15:15:30'));
INSERT INTO comment VALUES(13, 4, 'comment 1 on ticket 4', UNIX_TIMESTAMP('2003-11-30 15:15:30'));
INSERT INTO comment VALUES(14, 4, 'comment 2 on ticket 4', UNIX_TIMESTAMP('2004-02-13 15:15:30'));
INSERT INTO comment VALUES(15, 4, 'comment 3 on ticket 4', UNIX_TIMESTAMP('2006-06-30 15:15:30'));
INSERT INTO comment VALUES(16, 4, 'comment 4 on ticket 4', UNIX_TIMESTAMP('2004-07-09 15:15:30'));
INSERT INTO comment VALUES(17, 5, 'comment 1 on ticket 1', UNIX_TIMESTAMP('2013-11-30 15:15:30'));
INSERT INTO comment VALUES(18, 5, 'comment 2 on ticket 2', UNIX_TIMESTAMP('2014-02-13 15:15:30'));
INSERT INTO comment VALUES(19, 5, 'comment 3 on ticket 3', UNIX_TIMESTAMP('2014-06-30 15:15:30'));
INSERT INTO comment VALUES(20, 5, 'comment 4 on ticket 4', UNIX_TIMESTAMP('2014-07-09 15:15:30'));


mysql 8.0 in-memory table slow insert Please help with following strange issue.I defined in memory table but insert of 600.000 rows takes 13 minutes and causes heavy write to disk. The CPU is underutilized and I have enough free memory. What's wrong and why I see the I/O?
1. I defined max_heap_table_size as 1GB and restarted the DB.
2. I created the table as:

CREATE TABLE `data`
     (
       `id`         bigint(20)       DEFAULT NULL,
       `datetime`   timestamp        DEFAULT CURRENT_TIMESTAMP,
       `channel`    int(11)          DEFAULT NULL,
       `value`      bigint(20)       DEFAULT NULL
     ) ENGINE = MEMORY;

I created the procedure to insert data.

DELIMITER $$
CREATE PROCEDURE generate_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 600001 DO
    INSERT INTO `data` (`id`, `datetime`,`value`,`channel`) VALUES (
      i,
      FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
      ROUND(RAND()*100,2),
      i
    );
    SET i = i + 1;
  END WHILE;
END$$

Look SHOW PROCESSLIST - the most time the process state is 'waiting for handler commit'.

If your server version is 8 use recursive CTE instead:

INSERT INTO `data` (`id`, `datetime`,`value`,`channel`)
WITH recursive
cte AS (select 1 i
        union all
        select i+1 from cte where i <= 600000)
select i,
       FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
       ROUND(RAND()*100,2),
       i
FROM cte;

If you have 5+ server, use synthetic number tables:

INSERT INTO `data` (`id`, `datetime`,`value`,`channel`)
select i,
       FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
       ROUND(RAND()*100,2),
       i
FROM (  SELECT 1+d1.num+d2.num*10+d3.num*100+d4.num*1000+d5.num*10000+d6.num*100000 i
        FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d1,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d2,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d3,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d4,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d5,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) d6
      ) d0;

At the same system (server version 8.0.18):

your procedure - over 20 min. (killed after 5 min, 122k records were inserted)
insert with CTE - 3.10 sec.
insert with synth. tables - 4.08 sec.


Build replication from backup not publication source I have an interesting scenario I am trying to find a solution to.

We currently have the current setup.

ProductionDatabase (publisher) is the publication for transnational replication to the following:
  WarehouseDatabase (subscriber)
  NewWarehouseDatabase (subscriber)

My issue is that WarehouseDatabase has had triggers and other SP's added to it.  When I build replication for NewWarehouseDatabase I built it from a backup of ProductionDatabase.  So NewWarehouseDatabase does not have the triggers or SP's that had been added to WarehouseDatabase.

Doing a SQL Compare generated a list that had a lot more things displayed than I really want to manage.

So I thought I can build replication by backing up WarehouseDatabase and restoring to NewWarehouseDatabase and then enabling replication from ProductionDatabase.  Then I thought that will not work as the LSN and other internals that might not sync up for replication to work.

I am wondering if anyone can suggest how I can accomplish this, if you can understand my babbling...

Regards,
GarryB

If I understand you question correctly you could use a schema compare tool to generate a script that would be used to insert the extra SPs and triggers into the new datawarehouse DB.

Redgate have a tool like this in their DBA bundle and I think data and schema compare are now functionalities of visual studio.


Mysql select ends with 'killed' Okay, I'm sure this will turn out to be something dumb, but then I'm a dumb n00b DBA.

I've got a largish table, about 163 million rows and something like 44 GiB.  After some experiments with mysqldump for backups, I attempted to build a tab-separated output using select in batch mode.

I tried

echo 'select * from node;' | mysql -u xx -p -B database > file

and it terminates with the message "Killed".

so I tried logging into mysql in batch more, same query, same result.

I tried logging in in the normal fashion, and doing select * from node;

Still the same result: "Killed".   HOWEVER

create table temp_node (select * from node);

works fine.

So I'm clearly missing something basic, but googling hasn't helped.  Have pity on a n00b and tell me what's happening?

Please note the question isn't about running the command per se, it's that when I run the command, or execute select * from node; from the mysql> prompt, mysql terminates with the message "Killed."

Oh, and how I can export that damned table for loading later?

Okay, I thought that was a familiar message.  That's from LINUX saying "process ran out of memory."

Well, there's not a lot more to say. The mysql process itself runs out of memory and is terminated by the OS. I dn't have a good feel for the implementation, but it appears that it's trying something like pulling the whole table into memory to do the select *.


Query to view failure detail for a given sql agent job step? I have a sql agent job step that failed for a given date / time.  Is there an MSDB - TSQL query I can run to view the step failure reason given that :

I know the job name.
I know when the job failed.
I know the step number

You should be able to adjust this query to your criteria - taken from What is the Query to display the failed sql jobs
select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
   ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
   ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE jh.run_status = 0


Mysql - MyISAM Default order We are using Mysql V5.6.
We have a MyISAM table. when we open the table, the records will be displayed in some order instead of primary key order. Even when selecting the records will not be retrieved in primary key order. I am not sure on what basis mysql is displaying the records.

Is there any default order in Mysql, instead of primary key order?

There is no "default" order in the RDBMS. Just because tables are not lists but sets. Imagine records as a cards in a big bag, not in a stack. There is no guarantee that cards will be fetched from the bag in some specific order.

Yes, cards are enumerated by primary key, but you have to request the fetching in that specific order. It is not the default order when order is not specified.


PostgreSQL cannot start after replacing contents inside its Data folder When recovering the Postgresql database from a hacked Ubuntu 12.04 server, I copied the data directory to another location, reinstalled the OS and PostgreSQL, stopped PostgreSQL service, removed the contents in the data directory and copied back the contents from the previous installation.

Problem: PostgreSQL will not start now, showing the errors below. Why is this happening, and how can we fix the problem?

service postgresql restart
* Restarting PostgreSQL 9.1 database server

* Error: could not exec /usr/lib/postgresql/9.1/bin/pg_ctl /usr/lib/postgresql/9.1/bin/pg_ctl start -D /var/lib/postgresql/9.1/main -l /var/log/postgresql/postgresql-9.1-main.log -s -o  -c config_file="/etc/postgresql/9.1/main/postgresql.conf" :

Update

Restarting PostgreSQL service now gives the error:

* Restarting PostgreSQL 9.1 database server
* The PostgreSQL server failed to start. Please check the log output:
2013-04-16 01:52:10 EDT PANIC:  could not open control file "global/pg_control": Permission denied

I ran into the same problem.  To clarify what Nyxynyx hints towards in his comment, the resolution was to give permissions to access the db cluster dir (the path that follows -D in the error):

chown -R postgres:postgres /var/lib/postgresql/9.1/main


MSSQL Mirroring equivalent to Oracle Data Guard Snapshot Standby Does MSSQL support an operating mode on the failover partner / mirror that is functionally equivalent to Oracle database's Snapshot Standby mode?

ie A temporary active copy of the replicated database that can be open for read/write but can be reverted back to a replication target without rebuild?

Mirroring does NOT have this capability.  If you are using the Enterprise Edition of SQL Server then an Availability Group (MSSQL's successor to mirroring) with a readable secondary would do this, but you would be required to license both instances with EE licenses.


Will a composite Primary Key use the 15/16 page fill factor or the 1/2? I understand that if a PK is a consecutive number, it will fill a page as its maximum which is a factor of 15/16. If it is a random PK the page fill factor will be of 1/2.

I was wondering if my PK was a composite key.

For example, for a blog post table my PK would be (author_id, post_id). What fill factor would that use ?

Making a PK of this form lets me retrieve all the posts of a given author faster. But I/O wise I was wondering if this was a good shot.

Question is for MySQL DB using InnoDB storage engine.

A composite key like that counts as non-sequential ordering and will be splitting around the 1/2 fill factor, although this depends on the actual usage.

If author 1 makes the first 3 blog posts followed by author 2 making the next 3, you'd get keys like:
11
12
13
24
25
26

and innodb will pick a fill rate closer to 15/16 then 1/2, but if they post intermixed:
11
22
13
24
15
26

innodb will pick a fill rate closer to 1/2 rather then 15/16.

The actual fill rate will be entirely dependent on your data.

For your I/O thoughts, I would use the composite key over an auto-increment as it will be less I/O overhead for a single primary composite key vs a auto-increment key and a composite key together. Given it's a key you are not updating, your effective fill rate shouldn't be overly important and if you run an optimize table, it will compact your primary index to 15/16 anyway, so if you actually notice an performance improvement from a higher packed index, you can manually make it so.


high traffic mysql hosts I'm wondering if any of you sql buffs out there have any suggestions for a high speed mySQL server. I need something that can handle roughly 33 updates and 33 queries per second, per user.

and for now I really only need to handle say, 10 users.

I know it's hard to say because it depends on the statements so maybe if I supply them it will be of help.

each user is calling :

33x per second

SELECT Online, Xloc, Yloc FROM accounts WHERE AccountID = " + getlocI

and polling:

33x per second

UPDATE `accounts` SET `Online`='"+InitialStat.Onlineint+"', `Xloc`='"+Game.currentx+"',
`Yloc`='"+Game.currenty+"' WHERE (`AccountID`='"+UserVerify.clientID+"') LIMIT 1

with a larger UPDATE only once per minute:

UPDATE `accounts` SET `Online`='"+InitialStat.Onlineint+"',`Zone`='"+Game.currentzone+"',`Xloc`='"+Game.currentx+"', `Yloc`='"+Game.currenty+"', `Level`='"+InitialStat.PlayerLevel+""
                        + "', `XP`='"+InitialStat.XP+"', `Health`='"+InitialStat.health+"', `Magic`='"+InitialStat.magic+"', `Stamina`='"+InitialStat.stamina+""
                        + "', `Strength`='"+InitialStat.strength+"', `Defense`='"+InitialStat.defense+"', `Vitality`='"+InitialStat.vitality+""
                        + "', `Agility`='"+InitialStat.agility+"', `Intelligence`='"+InitialStat.intelligence+"', `Dexterity`='"+InitialStat.dexterity+""
                        + "', `Luck`='"+InitialStat.luck+"', `Gold`='"+InitialStat.gold+"' WHERE (`AccountID`='"+UserVerify.clientID+"') LIMIT 1

the larger UPDATE statement isn't too big of a deal since it happens far less frequently.

But the fastest I can get my other ones to go is somewhere around once per second.

Is there any way I can find out a ballpark of what I would need in a server to get those to function as fast as I'd like them to? I'm currently using a VH, I'd rather not go the dedicated server route yet because this is still in development, so spending a few hundred a month of the server is a bit much.

Any help would be greatly appreciated. If you need more info please let me know.

While MySQL is in-memory database (tables you are querying should be in memory to get quite nice performance) so you have to have enough of RAM to hold accounts table in buffer pool (innodb_buffer_pool_size). Can you calculate the size of your table (query information_schema.tables for this information)? For example, if it's 10G and you can get 10G at least (it would be nice to have at least +20-30%) so you can go with virtual server. If you cant afford 10G RAM (for example you need 700G RAM) you should think about sharding (moving data to different servers by accountId).

It's a good option to use partition by accountId it will reduce amount of blocking inserts/updates.

MySQL 5.5/5.6 has a nice feature to load/dump buffer_pool to you do need to do warm up after each server restart (look at innodb_buffer_pool_load_at_startup, innodb_buffer_pool_dump_at_shutdown).

That is where I would start. General advice: change settings in my.cnf and benchmark them.

Hope it helps


CREATE DATABASE permission denied in database 'master' I've developed a desktop application using WPF and Entity Framework code-first.

In my application the user can create multiple databases at run-time.

But Entity Framework can't create database if the user isn't admin on his machine.

I need to find a solution that could be done with coding. Management Studio is not installed on the user's machine.

What do you suggest?

You need to execute this T-SQL command using an account that does have administrative rights, such as sa:

USE master;
EXECUTE sp_addsrvrolemember @loginame = N'YourAppUserLogin', @rolename = N'dbcreator';

Replace YourAppUserLogin with the login the application user uses to connect to the database.


When are statistics created? I know that when we create an index then statistics are automatically created. My question is: Is it the only case when statistics are created? I remember that once I found on the internet that we have column statistics along with index statistics. Could you please enlighten me regarding this topic? When I execute the below script I get the following result:

use movies
go
EXEC sp_helpstats
@objname = '[dbo].[actors]',
@results = 'ALL';

This object does not have any statistics or indexes.

Column statistics are created automatically as needed by SQL Server when the AUTO_CREATE_STATISTICS database option is ON and also by an explict CREATE STATISTICS statement.

With the AUTO_CREATE_STATISTICS database option, the query optimizer will create single column stats during query compilation as needed on columns specified in query predicates when a stats histogram on the leading column doesn't already exist and that information is useful in plan selection (i.e. non-trivial plan).

Statistics may also be created beforehand using CREATE STATISTICS. Unlike auto-created statistics, CREATE STATISTICS allows one to create statistics on multiple columns to provide better estimates for compound predicates (e.g. FirstName = 'John' and LastName = 'Doh').


Postgres Transaction ID Wraparound and Autovacuum I have the postgres (9.3) logs showing

  database xxx must be vacuumed within 3017422 transactions

My database age is as shown below

 datname   |    age
 ----------+------------
 template1 | 1642961202
 template0 | 1642946792
 postgres  | 1692961164
 xxx       | 2144467989

Note :

We know the exact table which has aged, it is an audit log table.
We also do not mind losing all the data in that table, if deleting or dropping that table could help

The Problem:

Autovacuum is always running (as soon as we start postgres autovacuum starts off). Therefore, we cannot run Vacuum Freeze on the table - This is blocked on autovacuum.

datid            | 16384
datname          | xxx
pid              | 11587
usesysid         | 10
usename          | postgres
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    | 2018-01-27 11:28:27.288297+05:30
xact_start       | 2018-01-27 11:28:27.313791+05:30
query_start      | 2018-01-27 11:28:27.313791+05:30
state_change     | 2018-01-27 11:28:27.313795+05:30
waiting          | f
state            | active
query            | autovacuum: VACUUM myschema.mytable (to prevent wraparound)

Also, the size of the table in question and its index is unusually high compared to another table which has same structure and content.

What is the best way to bail out before the transaction wraparound
limit runs out?
Is it OK to drop the table - will it cause any inconsistency with
respect to transaction ID?
Is it OK delete records from the table (2.5 million) with only 3 million
transactions left before the shutdown? Will it just consume all
remaining transactions?

You can either drop or truncate the table.  They will initially block on the autovac job, so you will need to identify that process and then kill it using your system tools, or pg_terminate_backend.  At that point, the waiting drop or truncate should be able to acquire the lock faster than that autovac can relaunch.


How to view the latest entries in all tables in an Oracle DB? I wanted to examine a running Oracle instance on one of our servers, but nobody knows, if there's still any data of interest stored in the database.

Unfortunately our last Oracle admin has left our small company and so I try to figure out, if there's still any valuable data left.

But I can't really distinguish between system and user content, i.e. what is an example table/database and which tables where created by our ex employee?

I searched for log files but I wasn't really successful, so I thought about using a select about the last 100 (or more) updated that where made in the tables, so that I maybe get a clue what went on in the database.

It's running on Windows2000 and I have the system account.

Thanks for any hints.

You can identify most recently created users/schemas from dba_users view

select *
from dba_users
order by created desc;

When you know what schemas to look out for, you can list the most recently created tables with

select *
from dba_objects
where owner in ('SCHEMA1','SCHEMA2')
  and object_type = 'TABLE'
order by created desc;

For a single table, you can find a rough date when the data was last modified

select scn_to_timestamp(max(ora_rowscn))
from schema.table;

Or get a list of rows that were (potentially) modified last

select *
from schema.table
where ora_rowscn = (
  select max(ora_rowscn)
  from schema.table
);

Note that ora_rowscn is stored by default per database block, not per row. There is a limit how old scn values can be mapped back to timestamp.

You should also check the audit trail

select *
from dba_audit_trail
order by timestamp desc;


MySQL - Number format - Separated with Comma I have a query of MySQL like this-

SELECT avg('total_cost') FROM 'employee'

And then I am getting output like this-

1254589687.26587952

But I like to have a output like this-

1,25,45,89,687.26

So, a comma separator should be added and number should be rounded.

Is there any way in MySQL?

Thanks for helping.


SQL PIVOT multiple columns so this:

so I need:

any suggestions?

Use a Matrix control in SSRS.

But in T-SQL:

You want a row per ID so you'll need GROUP BY ID

In the first column you want a time value where the activity is A, so use: MAX(CASE WHEN Activity = 'Activity A' THEN time END)

And then apply the pattern for all the others.


Taking load off the Master server by using a slave My gameserver utilizes a Master and a slave on one server. The reason I am wanting to use a slave is because I want to beable to run select queries (read only) to get data without affecting the Master database.

For example, a table on the Master gameserver that is being replicated is called rpg_user_items. It stores hundreds of thousands of items, and I want to select some of this data to show to the player (without being in-game); think of a web inventory browser.

My dilemma is: Would selecting the data from the slave be the more appropriate thing to do? And would it technically take load off the Master server? (Maybe, the Master server can even perform better since select queries are not being used on it all the time?)

Edit: My MYSQL version is 5.6.24, using InnoDB. The frequency of insertions are around 20+ req/s. (Inserting 1 row into rpg_user_items) with 15 row fields being a mix smallint and tinyint data types.

Yes.  A slave can (and should) offload SELECTs from the Master.

If you outgrow one slave, you can have many.  This gives you unlimited read scalability.

Keep in mind that every write that happens on the Master must be replayed on every Slave.  Using Row Based Replication helps make this less of a burden on the Slaves.  But, eventually, you may become write bound.  (That's a separate topic; at 20/sec, you are probably nowhere near that.)

A "critical read" is one that depends on something that was just written.  Regular replication is "asynchronous", hence a write can take arbitrarily long to get to the Slave.  Hence, if, say, the user posts a comment, he will want to see it immediately.  The post must happen on the Master.  The "see" is a SELECT that would be nice to do on the Slave, but cannot be safely done ("critical read").  Other reads -- sure, do them on the Slave.


updating same column as in the select query When I try to do this, I get an error saying I cannot use the same table in where select as the column that I am updating.

UPDATE table_name
   SET quantity = 19
 WHERE  productID = 148357
               AND productCost IS NOT NULL
               AND exampleDate1 >=
                      (SELECT min(exampleDate1)
                         FROM table_name
                        WHERE exampleDate1 >=
                                 (SELECT min(exampleDate)
                                    FROM table_name2
                                   WHERE description LIKE "We are true"))

ROOT CAUSE

Look at the following parts of your query

UPDATE table_name (<<-- table_name)
   SET quantity = 19
 WHERE  productID = 148357
               AND productCost IS NOT NULL
               AND exampleDate1 >=
                      (SELECT min(exampleDate1)
                         FROM table_name (<< -- same table_name)
                        WHERE exampleDate1 >=
                                 (SELECT min(exampleDate)
                                    FROM table_name2
                                   WHERE description LIKE "We are true"))

You cannot execute an UPDATE or DELETE query against a table that uses a subquery against that same table. I have discussed this before :

Feb 22, 2011 : Problem with MySQL subquery
Jan 23, 2013 : Problem with nested UPDATE queries
Apr 30, 2013 : UPDATE table based on the same table

SUGGESTION

Rewrite it as two queries

#
# Retrieve Minimum Date into a Session Variable
#
SELECT min(exampleDate1) INTO @MininumDate
FROM table_name
WHERE exampleDate1 >=
(
    SELECT min(exampleDate)
    FROM table_name2
    WHERE description LIKE "We are true"
);
#
# Use Session Variable in the WHERE clause of the UPDATE
#
UPDATE table_name
    SET quantity = 19
    WHERE  productID = 148357
    AND productCost IS NOT NULL
    AND exampleDate1 >= @MininumDate
;

GIVE IT A TRY !!!


ORA-02070: database does not support antijoin in this context I don't think this is even a question, but in case someone has seen something like this before or has any thoughts besides 'file a bug with Oracle' (which I'm doing), I'd love to hear it.

create table pat (patientid VARCHAR2(32), pcpid VARCHAR2(32), bval1 NUMBER(1), bval2 NUMBER(1));
create table tab (setmemberid VARCHAR2(32), uuid VARCHAR2(32));

SELECT COUNT(patientId) n FROM (
SELECT DISTINCT pat.patientId, pat.bval1, pat.bval2
FROM pat
 LEFT OUTER JOIN
 (SELECT /*+ CARDINALITY(tab 10000) */ setmemberid FROM tab WHERE uuid = '7C06216F-EFC8-418A-A6D9-77C7F6FE7AB1') fsq1
 ON pat.pcpid = fsq1.setmemberid
WHERE fsq1.setmemberid IS NULL
) pat
GROUP BY GROUPING SETS ((pat.bval1), (pat.bval2), ())
;

If I remove the CARDINALITY hint query works fine.

Got a workaround from Oracle:

/*+ opt_param('_simple_view_merging', 'false') */

or

alter session set "_simple_view_merging"=false;

So the following query works:

SELECT COUNT(patientId) n FROM (
SELECT /*+ opt_param('_simple_view_merging', 'false') */ DISTINCT pat.patientId, pat.bval1, pat.bval2
FROM pat
 LEFT OUTER JOIN
 (SELECT /*+ CARDINALITY(tab 10000) */ setmemberid FROM tab WHERE uuid = '7C06216F-EFC8-418A-A6D9-77C7F6FE7AB1') fsq1
 ON pat.pcpid = fsq1.setmemberid
WHERE fsq1.setmemberid IS NULL
) pat
GROUP BY GROUPING SETS ((pat.bval1), (pat.bval2), ())
;


How to set-up failover in Postgres 9.6? I have two postgres server with a master-slave architecture, the slave is replicating the DB from the master.

In case the master goes down, the slave takes over and becomes the master.

Now, the slave has some updated data, which I want to replicate to the master, once it boots back up. How do I achieve that?

Be careful with your terminology. Your former slave is now the master.

If you have identical hardware, then there's no need to "switch back".

Regardless, you will need a new slave. You can use the former master for this; either reinitialise it in the same way you created the slave in the first place (probably using pg_basebackup), or use a tool such as pg_rewind or rsync to synchronise them. And then enable streaming replication.

If you want to "switch back", you will need to go through all the above again. That's why I run my slaves on identical hardware so I have no need to "switch back" -- I just setup a new slave (usually reusing the former master).


ORA 01031 Insufficient privileges on GRANT SELECT on ALL_CATALOG to a user as system user Command:
GRANT SELECT ON ALL_CATALOG TO appdbuser
                *

Error:
ERROR at line 1:
ORA-01031: insufficient privileges

PROBLEM:

I want my Oracle XE system user (<-important) to be able to GRANT SELECT ON
ALL_CATALOG, ALL_CONSTRAINTS, ALL_INDEXES, ALL_OBJECTS, ALL_TABLES,
ALL_TAB_COLUMNS and ALL_VIEWS.

DMBS: Oracle XE 18c (Express Edition), Version 18.4
Client(s): SQL Developer 20.2.0.175 AND also with SQL Plus 12.2.0.0
Operating System: Windows 10 Pro Build 18363
User(s) & Privileges:

The Windows user that I am logged in with is a domain user account which is part of Windows Administrators group on my machine.

I have installed this DB on my own machine with this same user, that I am currently logged in with.

I have also verified that this user is included in ORA_DBA Windows user group. The image below shows a list of other groups on my Windows machine that start with ORA.

I am connected as system user (tried both via sqlplus and sql developer) to Oracle XE DB (pluggable DB not CDB).

I connected to the DB with sys/password as sysdba and executed the following command with success:
GRANT ALL PRIVILEGES TO system;
but the GRANT SELECT ON commands still gives the Insufficient privileges error.

If it matters, I have checked that NTS is included as Authentication service in my sqlnet.ora file like this:
SQLNET.AUTHENTICATION_SERVICES= (NTS)

(EDIT1) As per this answer I have now also tried
GRANT ALL PRIVILEGES TO system WITH GRANT OPTION;
which gives me following error report
Error report -
ORA-01939: only the ADMIN OPTION can be specified
01939. 00000 -  "only the ADMIN OPTION can be specified"
*Cause:    System privileges and roles can only be granted with the admin
option. The grant option cannot be used with system privileges and
roles. The delegate option cannot be used with system privileges.
*Action:   Specify the admin option.

After 7, I tried
GRANT ALL PRIVILEGES TO system WITH ADMIN OPTION;

with success, as in Grant succeeded, but my problem is still there.
More Context

This command is part of a SQL script generated by a batch script, neither of which I want to or can change, as that is used by a larger team and based on given parameters these scripts already work for MS SQL Server and Oracle 12.2.0.1.0 on a Linux machine (however the IT has set it up), which is maintained by our IT and we have limited (2) licenses for it only. Hence, I am setting up Oracle XE on my machine and want something to be repeatable so other devs can just do it from my experience.
When I executed this script by logging in with system/password as sysdba, these GRANT commands inside the SQL script worked, but even this logging in is coded in script, which I don't want and most likely shouldn't change. Its ok if I provide the devs with an additional script for them to execute before they use the standard scripts.
This script in question is also creating the users, to whom the script is now GRANTing rights. There were errors in those CREATE USER commands too, because the CREATE USER command specifies a DEFAULT TABLESPACE user_data and a TEMPORARY TABLESPACE temporary_data. The DB complained about these not existing in database (xepdb1), so I created these tablespaces with system user using following commands:

   CREATE TABLESPACE user_data
       DATAFILE '<ORA_DATA>\XE\XEPDB1\user_data.dbf' SIZE 1M
       AUTOEXTEND ON NEXT 1M;

   CREATE TEMPORARY TABLESPACE temporary_data
       TEMPFILE '<ORA_DATA>\XE\XEPDB1\temporary_data.dbf' SIZE 2M
       AUTOEXTEND ON NEXT 1M;

To allow the user system to grant privileges on objects it doesn't own, use
GRANT ALL PRIVILEGES TO system WITH GRANT OPTION


Ola Hallengren - DatabaseBackup - LOG Unable to open Step output file. The step failed DatabaseBackup - USER_DATABASES - LOG: This job fails saying "Executed as user: Domain\XXXX-SVC. Unable to open Step output file.  The step failed."

The error is only with LOG backup job.
The other DatabaseBackup jobs (FULL, DIFF) works just fine with same SVC account. So the service account have appropriate permissions.

The Output File(Job Step properties-->Advanced) is F:\SQLAgentLog\ which is same for all jobs. Only problem is with LOG backup job. Has anyone else experienced this and is there any solution?

Current environment:
SQL Server: 2012 SP3 CU8
OS: Windows Server 2012

Note: This was working all good on a Windows Server 2008!!

It turns out that the person migrated the jobs from old server to new server edited the jobs manually.

One of the edit was to "Output File: (Job Step properties-->Advanced)"
this was set to default log location C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log on old server.

On the migrated server it was edited to "F:\SQLAgentLog\Log\" where the directory Log did not exist. (that person missed deleting the Log text in the path)

On the other jobs (FULL and DIFF) the text Log had been removed so it was set like this "F:\SQLAgentLog\" and these both were working fine!


How can I change SQL Agent Log file to use unicode format? I've created a database with tables that heavily leverages nvarchar for most of the columns.  One of the challenges I'm running into is that when I go to view the SQL Server Agent Log files, I get the following error noted below.  I've been scouring Google and haven't been able to find anything that closely resembles the scenario I'm experiencing.  Short of re-installing the entire server (which is really isn't an option at this point), how can I flip some sort of switch to make the log files readable again?

(Sorry don't have enough rep here to post an image.  Here's the link to the image.)

http://i.stack.imgur.com/VW7hw.png

Here's the details behind it:

  ===================================

  The log file is not using Unicode format. (.Net SqlClient Data Provider)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3000&EvtSrc=MSSQLServer&EvtID=22004&LinkId=20476

  Server Name:
  Error Number: 22004
  Severity: 16
  State: 1

  Program Location:

  at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at Microsoft.SqlServer.Management.SqlManagerUI.LogSourceSqlAgent.InitializeInternal()
    at Microsoft.SqlServer.Management.SqlManagerUI.LogSourceSqlAgent.Microsoft.SqlServer.Management.SqlManagerUI.ILogSource.Initialize()
    at Microsoft.SqlServer.Management.SqlManagerUI.LogSourceAggregation.Microsoft.SqlServer.Management.SqlManagerUI.ILogSource.Initialize()

@spaghettidba's solution worked for this matter.  By unchecking the 'Write OEM error log' check box (cycled the server as well) by right clicking 'Error Logs' under (servername) -> SQL Server Agent -> Error Logs (in the SQL Server Management Studio) the logs started writing and were readable again.


Moving large database to another drive, how to measure performance before, and setup some type of replication for fast fail back? Sorry if this is kind of more than question at a time, but here goes.

Running SQL Server 2008 on windows server 2008 as well. We have a large database just over 200gb right now (it holds images of documents that are scanned in, the 3rd party app is using an image column. it also has some other data that is accessed, including licensing info for the program)

The data files for it are still on the c drive because someone decided to set it up that way. It's been growing bit by bit and the drive is getting dangerously low on space. I have an iScsi drive I want to move it to before anything bad happens (there are log files on this drive right now, but it has a ton of free space and I don't really have any other options right now), but I need to be sure of a few things first.

I want to find a good way to measure the i/o rates on the files as it currently stands, but even after searching I'm not sure of the best way to do this over the course of a day or two to have a good idea.
I also want to test the throughput of the new drive I plan on using to make sure it is suitable. I was thinking of using SQLIO for this, but haven't looked into it much yet.
Lastly, just in case there is some serious performance issues, I was thinking it might be a good idea to have some kind of replication setup, probably asynchronous to avoid performance issues, so that we can quickly revert back to the original files without having to lose any data or wait for the new file to copy back over again.

Thanks

You can use perfmon to measure your disk performance and contention.  I would measure the following counters for the appropriate drives (all under physical disk):

Avg. Disk sec/Transfer - Otherwise known as IOPs, this is the total number of actions (Read and Write) that occur on your disk.
Avg. Disk sec/Read - This is the average amount of time it takes an action to read from your drive.  For DB operations, it's recommended (generally) this be under 20ms.
Avg. Disk sec/Write - Same as for sec/Read, but for writes.  Again, should be (generally) under 20ms.

You want to monitor these values over a period of time to see how they trend.  If you capture them at regular intervals (default is every 15 seconds), put them into Excel and calculate your Min, Max, and Average over a reasonable period of time for your standard work period to establish your current baseline.

Next up, for testing the drive, SQLIO is a good tool.  I'd probably combine it with the perfmon counters to measure how the disk performs in comparison to your current setup.  This will give you a general idea of how the disk will perform.

For replication, you can replicate databases and individual objects, but not files.  So to accomplish your goal, you would need to basically create a full copy of the database to replicate to and handle the different naming/connection strings.  That being said, I believe that's WAY overthinking the issue.  If you do enough baselining and testing up front, you will know how it will perform before you even make the move.


What is the standard time delay taken for the data to get reflected in standby database when a new data is inserted in primary database I've configured Data guard setup using data broker. When I'm inserting new data to the tables in primary database which is suppose to reflect in standby database. When I checked those data's in standby database it was not reflected there. So, I've restarted the database and opened it in read only. I've used the below command to avoid the delay:

SQL> alter database recover managed standby database nodelay disconnect from session;

But still a lot of time is taken to get reflected there in standby database.

What will be the time delay taken to get the data's from primary to standby?

Is this the standard time delay taken for a data to get reflected in standby database. Or do I have missed anything there in configuration.

Is there any way to sort it out?

I think what you want is real time apply, but for that, you'll have to create standby redo logs on your standby database first.  As a side note, to make role transitions easier, it is considered best practice to create standby redo logs on your primary database as well:

The standby redo logs should be the same size as your redo logs and you need at least 1 more group of standby logs than redo logs:

I use the following script on the primary database, which builds that create statements for the standby redo logs that I then run on the standby database:

select 'alter database add standby logfile group ' ||
to_number((select count(*) from v$log) + rownum) || '(''T:\ORACLE\STANDBYREDO\REDO0' ||
to_number((select count(*) from v$log) + rownum) || '.LOG'') SIZE ' ||
(select max( bytes/1024/1024) from v$log) || 'M;' as standbyredo
from v$log
union all
select 'alter database add standby logfile group ' ||
((select max(group#) from v$log) + (select count(*) from v$log) + 1) || '(''T:\ORACLE\STANDBYREDO\REDO0' ||
((select max(group#) from v$log) + (select count(*) from v$log) + 1) || '.LOG'') SIZE ' ||
(select max( bytes/1024/1024) from v$log) || 'M;'
from dual;

After the standby logs are present on the standby database, to start real time apply, you need to include the using current logfile phrase in your recover command:

SQL> alter database recover managed standby database using current logfile disconnect from session;

During shipping and apply, you can check to verify that the standby logs are being used by querying v$standby_log on your standby database:

select * from v$standby_log;

I found Tim Hall's guide on Standby Databases invaluable when I was learning how to setup a physical standby.


SQL Server SSIS quandary I have an SSIS job that generates a list of servers from a table, then executes a stored procedure located in an administrative database on each server.

When I try to add one specific server to the list table, it fails trying to set the connection manager for the new server, with the message below.

There is nothing to be found in the SQL Server Error Log, nor in the Event Viewer on either the the server running the job or the one I'm attempting to add.

  Executed as user: CORPORATE\SqlagentPRD. Microsoft (R) SQL Server Execute Package Utility
    Version 12.0.5000.0 for 32-bit
    Copyright (C) Microsoft Corporation. All rights reserved.

  Started:
    2:40:56 PM
    Error: 2018-09-26 14:40:58.24

  Code: 0xC0209302

  Source: SQL Server Log Consolidation Connection manager "SourceQuery"

  Description: The requested OLE DB provider SQLCNLI11.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000.
    An OLE DB record is available.
    Source: "Microsoft OLE DB Service Components"
    Hresult: 0x80040154
    Description: "Class not registered".
    End Error
    Error: 2018-09-26 14:40:58.24

  Code: 0xC020801C

  Source: Data Flow Task OLE DB Source [30]

  Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
    The AcquireConnection method call to the connection manager "SourceQuery" failed with error code 0xC0209302.
    There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    End Error
    Error: 2018-09-26 14:40:58.24

  Code: 0xC0047017

  Source: Data Flow Task SSIS.Pipeline

  Description: OLE DB Source failed validation and returned error code 0xC020801C.
    End Error
    Error: 2018-09-26 14:40:58.24

  Code: 0xC004700C

  Source: Data Flow Task SSIS.Pipeline

  Description: One or more component failed validation.
    End Error
    Error: 2018-09-26 14:40:58.24

  Code: 0xC0024107

  Source: Data Flow Task

  Description: There were errors during task validation.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).
    Started:
    2:40:56 PM
    Finished: 2:40:58 PM
    Elapsed:
    1.657 seconds.
    The package execution failed.
    The step failed.

This is probably a typo issue. The provider is SQLNCLI, not SQLCNLI


How to simulate production workloads on a dummy database? We are planning to enable Multi AZ on our production database which uses Amazon RDS as a managed service. Our Database is using SQL Server 2012 standard version.

Before enabling that, I wanted to simulate our production workload on a dummy database to check how the read write latency are getting affected after making the dummy database as Multi AZ.

Whats the best way to do the above ?

The fastest, easiest way is to rebuild an index on a large table.

That pushes a lot of write activity through, and it’s consistent, easily repeatable, and easily timed.

It’s not your workload (or anything near it) but helps you get a fast idea of the overhead right away.


Troubles with creating initial PostgreSQL base I'm trying to create a new base /usr/pgsql/bin/initdb -D /var/lib/pgsql/data --locale=en_USand got the following:

creating template1 database in /var/lib/pgsql/data/base/1 ... FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=1, size=35233792, 03600).
... To reduce the request size (currently 35233792 bytes), reduce PostgreSQL's shared_buffers parameter (currently 3584) and/or its max_connections parameter (currently 104).
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for

How to fix this? Where can I change these SHMMAX and SHMIN? Do I really need to recompile the linux kernel as PostgreSQL advises me?

As the root user, edit /etc/sysctl.conf and add the following lines:

kernel.shmmax=2147483648
kernel.shmall=2097152

Then run:

sysctl -p

.. to reload the kernel settings.

That'll set the maximum shared memory segment size to 2Gb & should solve your issue.


Oracle Warning in Alert Log Regarding PGA_AGGREGATE_LIMIT Introduction

During the startup process of our Oracle instances, we are encountering the following error message in the alert log:

WARNING: pga_aggregate_limit value is too high for the
amount of physical memory on the system
  PGA_AGGREGATE_LIMIT is 2048 MB
  PGA_AGGREGATE_TARGET is 256 MB.
  physical memory size is 0 MB
  limit based on physical memory and SGA usage is 512 MB
  SGA_TARGET is 6144 MB
Using default pga_aggregate_limit of 2048 MB

Physical RAM

What is strange is the fact that the physical memory size in the WARNING is being displayed as 0 MB, because our physical server has 768 GB of physical RAM. (Yes, the server isn't a virtual server; physical only for Oracle.)

Spfile and Pfile Configuration

The SGA_TARGET and SGA_MAX_TARGET parameters in the pfile_SID.ora and spfile_SID.ora have been configured at 6144M. Here a list of the interesting parameters from the PFILE:

...
processes              = 500
...
memory_max_target      = 0
memory_target          = 0
sga_max_size           = 6144M
sga_target             = 6144M
...
pga_aggregate_target   = 256M
...

Documentation for PGA_AGGREGATE_LIMIT

According to the Oracle documentation for PGA_AGGREGATE_LIMIT the value will be set according to the following rules:

  By default, PGA_AGGREGATE_LIMIT is set to the greater of...

  2 GB,
  200% of PGA_AGGREGATE_TARGET,
  and 3 MB times the PROCESSES parameter.

  It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.

(Important bit at the end regarding restrictions)

Oracle Patch Level

The current patch level of the server is:

Patch Id: 26758841
Patch Description: WINDOWS DB BUNDLE PATCH 12.2.0.1.171017(64bit):26758841

Applying Documentation to Oracle Configuration

Because we haven't manually configured the PGA_AGGREGATE_LIMIT parameter Oracle is going to be so nice and do that for us. This means Oracle is going to set the value to the greater of the following values:

2 GB
200% of PGA_AGGREGATE_TARGET = 200% * 256 MB = 512 MB
3 MB times PROCESSES = 3 MB * 500 = 1.5 GB

...but there would be the restriction cited above, which in my case would mean:

90% * Physical Memory - 6144 MB = 90% * 0 MB - 6144 MB = <0 MB

Ok. Because Oracle is listing the server's physical RAM as 0 MB we are going to hit the restriction and Oracle should throttle the PGA_AGGREGATE_LIMIT value to 256 MB.

But Oracle is setting the value to 2048 MB!

Questions

Why is the physical RAM being listed as 0 MB?
Why is Oracle setting the PGA_AGGREGATE_LIMIT to 2048 MB instead of 256 MB?
Can I safely ignore the warning?

According to our database admins, this is a known ORACLE error since version 12. We have been advised to set pga_aggregate_limit to 0 in order to work around this problem. I haven't been able to test this thoroughly, but we haven't seen errors since changing this setting (using alter system, we haven't set the parameter in our database's [s]pfile).


Always On adding a database outside of group I have a 2 node AG with a synchronous replica. I want to know whether I can safely add another database to the synchronous replica node, without adding it to the Availability Group.

Does doing this carry any caveats?

That should not cause any issues at all. However, when connecting to a database outside of the AG, you will need to reference the server name instead of the AG Listener since the listener will always redirect you to the primary replica and your non-AG database will not always be on the primary replica.

Edit: Also keep in mind that actively using a secondary replica will warrant a need to license it.


Does the Binary Data column shown in SQL Profiler contain useful information? Many of the events that SQL Profiler captures include a Binary Data column. The documentation for these event classes merely repeats the fact that this column contains "Binary value dependent on the event class captured in the trace."

I'm particularly interested in the RPC:Starting and RPC:Completed events; I notice that the binary data for each event appears the same whenever the same query is executed, although the Starting and Completed events have different binary data. But other events also display information here that I would be interested in decoding. This assumes, of course, that the data would be even relevant or meaningful.

Is the content of these fields documented anywhere, and is it ever meaningful to a developer/administrator?

I know the BinaryData column is useful for a few event classes.  However, if the event class describes the column as "Binary value dependent on the event class captured in the trace" I haven't found it to be particularly useful.  I'm not aware of any meaningful use for the RPC:Starting and RPC:Completed events and I wasn't able to find any.

There are a few event classes where they column is useful though.  For example,  the degree of parallelism event class uses the BinaryData column to show how a plan is being executed (http://technet.microsoft.com/en-us/library/ms187943(v=sql.105).aspx).

The CursorImplicitConversion class uses it to tell you the resulting cursor type (http://technet.microsoft.com/en-us/library/ms180764(v=sql.105).aspx).

The ShowPlan event classes use the BinaryData column to store the XML data.  There are a few others but on the whole it's not very useful.


Using Row_Number to find consecutive row count I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row

If my data looks like this

724
727
728
733
735
737
743
747
749

the resulting data with a consecutive row count column would look like this

724 1
727 1
728 2
729 3
735 1
737 1
743 1
744 2
748 1

I've done it using a looping function but I'm trying to figure out using a cte. Here is a sample of my latest attempt

DECLARE @d TABLE ( signal INT )
INSERT  INTO @d
        SELECT  724
        UNION
        SELECT  727
        UNION
        SELECT  728
        UNION
        SELECT  729
        UNION
        SELECT  735
        UNION
        SELECT  737
        UNION
        SELECT  743
        UNION
        SELECT  744
        UNION
        SELECT  748 ;
WITH    a AS ( SELECT   signal,
                        ROW_NUMBER() OVER ( ORDER BY signal ) AS marker
               FROM     @d
             ) ,
        b AS ( SELECT   a1.signal,
                        CASE ( a1.signal - a2.signal )
                          WHEN 1 THEN 1
                          ELSE 0
                        END consecutiveMarker
               FROM     a a1
                        INNER JOIN a a2 ON a2.marker = a1.marker - 1
             )
    SELECT  *
    FROM    b

Produces these  results

signal  consecutiveMarker
727 0
728 1
729 1
735 0
737 0
743 0
744 1
748 0

The first obvious issue is missing the first signal in a series. Barring that, I thought I could then pass this to another cte with a row_number partitioning on the consecutiveMarker. That didn't work because it partitioned it as one  partition. I couldn't find a way to indicate to the partitioning method that one series is separate from the next

Any help is appreciated.

The general name for this type of query is "gaps and islands". One approach below. If you can have duplicates in the source data you might need dense_rank rather than row_number

WITH DATA(C) AS
(
SELECT 724 UNION ALL
SELECT 727 UNION ALL
SELECT 728 UNION ALL
SELECT 729 UNION ALL
SELECT 735 UNION ALL
SELECT 737 UNION ALL
SELECT 743 UNION ALL
SELECT 744 UNION ALL
SELECT 747 UNION ALL
SELECT 749
), T1 AS
(
SELECT C,
       C - ROW_NUMBER() OVER (ORDER BY C) AS Grp
FROM DATA)
SELECT C,
       ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY C) AS Consecutive
FROM T1

Returns

C           Consecutive
----------- --------------------
724         1
727         1
728         2
729         3
735         1
737         1
743         1
744         2
747         1
749         1