To enforce unique email addresses, I would remove all competing email columns and store them in one central email table for all active emails. And another table for deleted emails:

CREATE TABLE users (
  user_id  serial PRIMARY KEY
, username text UNIQUE NOT NULL
, email    text UNIQUE -- FK added below  -- can also be NOT NULL
);

CREATE TABLE email (
  email    text PRIMARY KEY
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE
, UNIQUE (user_id, email)  -- seems redundant, but required for FK
);

ALTER TABLE users ADD CONSTRAINT users_primary_email_fkey
FOREIGN KEY (user_id, email) REFERENCES email (user_id, email);

CREATE TABLE email_deleted (
  email_id serial PRIMARY KEY
, email    text NOT NULL  -- not necessarily unique
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE
);

This way:

Active emails are unique, enforced by the PK constraint of email.
Each user can have any number of active and deleted emails, but ...
Each user can only have one primary email.
Every email is always owned by one user and is deleted with the user.
To soft-delete an email (without losing it and its affiliation to its user, move the row from email to email_deleted.

The primary email of a user cannot be deleted this way, because the primary email must not be deleted.

I designed the FK constraint users_primary_email_fkey to span (user_id, email), which seems redundant at first. But this way the primary email can only be an email that is actually owned by the same user.
Due to the default MATCH SIMPLE behavior of FK constraints, you can still enter a user without primary email, because the FK constraint is not enforced if any of the columns is null.
Details:

Two-column foreign key constraint only when third column is NOT NULL

The UNIQUE constraint on users.email is redundant for this solution, but it may be useful for other reasons. The automatically created index should come in handy (for instance for the last query in this answer).

The only thing that's not enforced this way is that every user has a primary email. You can do this, too. Add NOT NULL constraint to users.email

UNIQUE (user_id, email) is required for the FK constraint:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

You have doubtless spotted the circular reference in the above model. Contrary to what one might expect, this just works.

As long as users.email can be NULL, it's trivial:

INSERT user without email.
INSERT email referencing the owning user_id.
UPDATE user to set it's primary email if applicable.

It even works with users.email set to NOT NULL. You have to insert user and email at the same time though:

WITH u AS (
   INSERT INTO users(username, email)
   VALUES ('user_foo', 'foo@mail.com')
   RETURNING email, user_id
   )
INSERT INTO email (email, user_id)
SELECT email, user_id
FROM   u;

IMMEDIATE FK constraints (the default) are checked at the end of each statement. The above is one statement. That's why it works where two separate statements would fail. Detailed explanation:

How to deal with mutually recursive inserts

To get all emails of a user as array, with the primary email first:

SELECT u.*, e.emails
FROM   users u
     , LATERAL (
      SELECT ARRAY (
      SELECT email
      FROM   email
      WHERE  user_id = u.user_id
      ORDER  BY (email <> u.email)  -- sort primary email first
      ) AS emails
   ) e
WHERE  user_id = 1;

You could create a VIEW with this for ease of use.
LATERAL requires Postgres 9.3. use a correlated subquery in pg 9.2:

SELECT *, ARRAY (
             SELECT email
             FROM   email
             WHERE  user_id = u.user_id
             ORDER  BY (email <> u.email)  -- sort primary email first
             ) AS emails
FROM   users u
WHERE  user_id = 1;

To soft-delete an email:

WITH del AS (
   DELETE FROM email
   WHERE  email = 'spam@mail.com'
   RETURNING email, user_id
   )
INSERT INTO email_deleted (email, user_id)
SELECT email, user_id FROM del;

To soft-delete the primary email of a given user:

WITH upd AS (
   UPDATE users u
   SET    email = NULL
   FROM   (SELECT user_id, email FROM users WHERE user_id = 123 FOR UPDATE) old
   WHERE  old.user_id = u.user_id
   AND    u.user_id = 1
   RETURNING old.*
   )
,    del AS (
   DELETE FROM email
   USING  upd
   WHERE  email.email = upd.email
   )
INSERT INTO email_deleted (email, user_id)
SELECT email, user_id FROM upd;

Details:

Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

Quick test for all of the above: SQL Fiddle.


How to lower HEAP Fragmentation in SQL Server?

What does Fragmentation Means in a Heap

The fragmentation value in Heap which you get from column avg_fragmentation_in_percent by querying sys.dm_db_index_physical_stats DMV states that

  Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

Further the same BOL says that

  This is the percentage of out-of-order extents in the leaf pages of a heap. An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

So you can see it is not the free space present in pages allocated to Heap but the varying sequence of pages that creates the fragmentation.

This can be demonstrated by small test. Let us create a Heap Table and insert some records in it and then check the fragmentation.

create table dbo.HeapTest
(
Id INT not NULL Default (1),
Col1   char(5000) Not null Default ('Heaps Are Cool')
)

SET NOCOUNT ON

Insert into dbo.Heaptest default values
go 50

select index_type_desc,avg_fragmentation_in_percent,fragment_count,
avg_page_space_used_in_percent,record_count
from sys.dm_db_index_physical_stats(db_id(),object_id('dbo.HeapTest','U'),0,default,'detailed')

So Heap table is created with 50 records in it. Below is what fragmentation looks like after query DMV sys.dm_db_index_physical stats

You can see avg_fragmentation_in_percent column value is 33 %. Now let us see how are pages arranged. This can be done by using undocumented query %%lockres%%. The query would be

SELECT  %%lockres%%, * FROM dbo.HeapTest;

And below is what output looks like. Attaching only relevant portion of it. The query produced 50 rows since we inserted 50 rows in our dbo.HeapTest table.

What it says is the first page has ID 197 the next page has ID 242 subsequent pages has continuous ID till we reach page ID 264 because after that we get page ID 280. So this jump in page ID numbers is what actually causing fragmentation.

Now lest rebuild the heap and run The command again to see the fragmentation and how pages are arranged. We get fragmentation like

You can see fragmentation is now 14%.

Let us see page numbers allocated

We only have one jump rest all pages are allocated page ID serially. Since just one jump fragmentation decreased considerably.

I rebuild the Heap again and now when I checked fragmentation it was completely gone. And page ID allocation is like

Why Fragmentation Increased

Now regarding what could have caused fragmentation to rise we can corroborate it to fact that when pages were getting allocated to the heap they would not be continuous, as you saw above what caused fragmentation value to increase was jump in the PAGE ID's allocated to pages.

At the back of head you should also keep in mind that the word fragmentation for HEAP does not have any meaning, how would you define fragmentation for bunch of un-ordered pages.

Really Worried about Fragmentation

If you really face a scenario where heap table is fragmented and slowing queries it would be better creating a clustered index on table than rebuilding it. The reason is when you rebuild heap all underlying Non Clustered indexes are also rebuilt causing the rebuild process to take much longer time, utilizing lot of resources and bloating transaction log. On a production system one would always try to avoid this. Paul covered this in his Myth Section about heap.

PS: Please don't use undocumented command on production system. This was just for demonstration.


Commit and rollback in Oracle stored procedure

You can do this by using a save point.
CREATE OR REPLACE PROCEDURE SPTest
AS
BEGIN
   -- We create a savepoint here.
   SAVEPOINT sp_sptest;

   INSERT INTO emptest(empid, empname, deptno)
   VALUES(1, 'ravi', 10);

   INSERT INTO test1(id, name, sal)
   VALUES(1, 'raju', 4444);

   UPDATE emptest
   SET empname = 'hari'
   WHERE empid = 1;

-- If any exception occurs
EXCEPTION
   WHEN OTHERS THEN
      -- We roll back to the savepoint.
      ROLLBACK TO sp_sptest;

      -- And of course we raise again,
      -- since we don't want to hide the error.
      -- Not raising here is an error!
      RAISE;
END;

The commit will typically be left to the caller. This construct just guarantees that either all of the inserts and the update are done, or none of them is.
On the most outer level, Oracle will do a rollback by itself. In other words, if you do EXEC sptest(); on the SQL+ command line, it will be expanded automatically to
DECLARE
BEGIN
   sptest();
EXCEPTION
   ROLLBACK;
   RAISE;
END;

But if a calling procedure has exception handling, this doesn't happen, since the exception may not get to that outer level. So you may end up with the inserts done, the update throwing an exception which may be caught and handled by the caller, so that the update will have failed but the inserts succeeded.
By doing a rollback in the stored procedure to the beginning of the procedure, you ensure that all of them succeed, or none of them do.


Why is `max_allowed_packet` shows 2048 even though it's set as 16M in mysqld.cnf

Settings in my.cnf (etc) are seen only when mysqld starts up.

Settings changed by SET GLOBAL... are not seen until someone logs in.  But, such settings go away when mysqld is restarted.  (This item probably explains what you encountered.)

Settings changed for the "session" last only for that one connection, and only until disconnecting.

(MySQL 8.0 has some more situations, due to being about the "persist" changes across restarts.)

(Not all settings have a GLOBAL/SESSION split as implied above.)


Tempdb Splitting File/Filegroups among different Databases

No.

Resource Governor is for throttling CPU, query workspace memory, and storage throughput, but there is no feature in SQL Server that restricts which files/filegroups a database can access in TempDB.

Plus, think about cross-database queries - if you had a cross-database query, how would that even work?


/usr/libexec/mysqld: Normal shutdown, but my team doesn't do that?

Linux's Viewpoint
You should run this Linux command
history | grep mysqladmin

This will let you see if anyone ran a shutdown from within the server. Note that this will not let you see remote mysqladmin shutdowns. Perhaps running tcpdump and locating the word mysqladmin or shutdown might help.
MySQL's Viewpoint
Shutdown commands do not exist from command line utilities. Therefore, binary logs will not have shutdowns recorded. You are on the right track when you said;

One possibility I'm thinking is someone (who has SUPER privilege) turned off the binary log then run SHUTDOWN and turned it on again.

Look over the users with that privilege. Run this query:
SELECT user,host FROM mysql.user WHERE shutdown_priv='Y';

You may see something like this:
mysql> select user,host from mysql.user where shutdown_priv='Y';
+-------+--------------+
| user  | host         |
+-------+--------------+
| root  | localhost    |
| root  | 127.0.0.1    |
| root  | ::1          |
| root  | 10.240.163.% |
| root  | 10.48.19.%   |
| user1 | %            |
+-------+--------------+
6 rows in set (0.00 sec)

mysql>

You will see all the users that have shutdown privilege. SUPER privilege does not have the shutdown privilege. There is a separate privilege called SHUTDOWN.
You can quickly revoke that privilege. For example, note the user user1@'%'. It has remote shutdown privileges. You can yank it with
REVOKE SHUTDOWN ON *.* FROM user1.'%';

or
UPDATE mysql.user SET shutdown_priv='N'
WHERE user='user1' AND host='%';
FLUSH PRIVILEGES;

That takes care of the remote shutdown privileges.
You should also address securing user connections by removing SHUTDOWN privilege from every user that does not need to have it. Perhaps you can do this:
UPDATE mysql.user SET shutdown_priv='N'
WHERE CONCAT(user,'@',host) NOT IN
('root@localhost','root@127.0.0.1');
FLUSH PRIVILEGES;

That way, only root@localhost and root@127.0.0.1 can do mysqladmin shutdown within the localhost. I would definitely have the root@127.0.0.1 on hand because there are times when mysqld loses connection to the mysql.sock file and prevents root@localhost from connecting to issue a shutdown. With the root@127.0.0.1, you can issue this:
mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp

to issue a mysqladmin shutdown without needed the socket file.
Give it a Try !!!


One approach would be to use optimistic locking.

Before displaying the update form, you retrieve all the original values from the database. Or, at a minimum, a column that is guaranteed to change on update, such as last_update_date or similar.

When updating you check that all values are still the same in your where clause. If the update fails to update any rows you know that the values have been changed in between and inform the user that this has happened.


Dropping a column generates an error: SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'

Insert profanity of choice.

The problem was caused by a database scoped trigger. Disabling / rewriting the trigger resolved the issue.


Find continuous records with equal values in each group

Assuming your duplicate Dates for MachineId = 1 was an accident (as Scott pointed out) something like this should be what you're after:
WITH CTE_GoodDates_Sorted AS
(
    SELECT MachineId, [Date], ROW_NUMBER() OVER (PARTITION BY MachineId ORDER BY [Date] DESC) AS SortId -- Generates a unique ID for each row within each MachineId, then resets on the next MachineId
    FROM MachineHealth
    WHERE IsBad = 0 -- Only want good Dates so we can get the last good Date per MachineId later
)

SELECT MH.MachineId, SUM(MH.IsBad) AS ContinuousBadDays
FROM MachineHealth AS MH
INNER JOIN CTE_GoodDates_Sorted AS GD
    ON MH.MachineId = GD.MachineId
    AND MH.[Date] > GD.[Date] -- Ensures we only get rows that came after the good Date
WHERE GD.SortId = 1 -- Using the auto-generate unique ID (within each MachineId), ensures we only have the latest good Date for each MachineId to compare to in the previous INNER JOIN
GROUP BY MH.MachineId

This uses the ROW_NUMBER() window function to get the latest Date no accidents occurred on per each MachineId in a CTE then uses that CTE to join to filter down the results to only the most recent continuous bad Dates. The final query then does a GROUP BY on MachineId and sums the IsBad field to get the total days (though you probably can even substitute that for a COUNT(1) instead if your IsBad field at most has a value of 1).


How can Update table column with the virtual column?

I solved it this way :

INSERT INTO game_log (game_detail,user_id,game_id,day_number)
select  sum(game_detail) As game_datail,user_id,game_id,0   from game_log group by user_id,game_id

--

DELETE FROM game_log where day_number != 0


SQL Server Performance Counters Relating to Disk Access and Log Files That Appear Concerning

SQLServer:Databases\Log Flush Wait Time counter, it is staying fairly constant between 600 ms and 900 ms with an average of 750ms

750ms wait time per flush is huge. It means every transaction statement must wait in average 750ms to commit. If you don't use explicit transactions for writes it means each write statement (INSERT/UPDATE/DELETE) must wait 750ms to complete. However this does not corroborate with the other sttaement 'PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms.'. One explanation is that you have log growths (you do, the counters say so) which skew the log flush wait times but that would only explain averages over long periods.

  SQLServer:Databases\Log Growths, I'm at 325

Figure out which database is growing and resize the log appropriately. You should not occur growth events in production, they are extremely expensive. the counter has instances per database.

  PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms. Is it normal for the Log Files to have consistent writes with no break?

Yes. A transaction cannot commit unless the log was written to disk. As long as you have transactions, you will have log disk writes.

  PhysicalDisk\Avg. Disk sec/Write counters: For the TempDB volume, I get dramatic spikes every few minutes that are upwards of 100 ms.

It would be good to know why. Can you correlate it with a specific activity that causes the spike? Can you correlated it with database checkpoints occurring?

  Memory\Pages Input/sec counter, the values fluctuate dramatically and have an average of 6 but have many spikes well upwards of 100 (some as high as 400).

So you're paging. Why? Insufficient memory? Any other offending process on the server host? Virtualized overcommitted environment? Only you can tell. Does swapping IO overlap with any of the IOs above (same physical path, ie. same disks)? Then some of the SQL timers may be influenced by this paging spikes.


How do you install a TFS plugin for SQL Server 2016 Management Studio?

Source control integration is being deprecated in Management Studio according to Deprecated Database Engine Features in SQL Server 2016, but that doesn't explain why it's not working in 2016. If you want it, I suggest you vote for this Connect item.

This can now be enabled by modifying the packages SSMS 2016 (Visual Studio 2015 shell) will load. See Source Control in SQL Server Management Studio (SSMS) by Ken Van Hyning (Engineering Manager, SQL Server Client Tools).

You will need to install Visual Studio 2015 (the free Community edition will do) in order to have the packages installed on your computer. You then modify C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\ssms.pkgundef to remove (or comment out) the lines relating to TFS SCC Configuration.

Note that on every SSMS update (which as you probably know are pretty frequent), you'll need to re-apply those changes.

I have put this process in a PowerShell script file. Trying to get it published to the PowerShell Gallery, but in the meantime you can get it from GitHub.

The same issue exists with SSMS 17, with the same solution ($SsmsUndefPath will need to be updated from 130 to 140 in the path).

Update Azure Data Studio offers an extension for source control. What is Azure Data Studio


Is it a good idea to split MySQL DB into two servers

OPTION #1 : Use Master/Master

You could use Master/Master only under one condition: If you write to a DB on ServerA, DO NOT ISSUE WRITES TO ServerB, and vice versa. In this way, you split writes cleanly. Splitting writes to the same DB in Master/Master can be a little clumsy if you depend on looking up rows by IDs that have the AUTO_INCREMENT property. If you look up rows by unique keys that never change from server to server, such as Social Security Number, Driver License, a HashKey and so forth, the splitting writes to the same DB between two Masters in Master/Master is fine.

OPTION #2 : Use Slave Servers

@DTest already described this, so I will add nothing additional to his suggestion (He get's a +1 for it).

OPTION #3 : Use MyISAM in the Slave Servers

When using read-only slaves that are not being used as a master for other servers, you should do two things to the data in that MySQL Instance

Convert all tables to MyISAM
Run ALTER TABLE tblname ROW_FORMAT=Fixed on all MyISAM tables

This should add 20% increase in speed for reads.

OPTION #4 : Use MySQL 5.5 for Semisynchronous Replication

Before MySQL 5.5, when a Master has Multiple Slaves, this is how an SQL statament is processed among the slaves (Example will be a Master with two slaves):

SQL Statement is

Executed in Master
Recorded in Master's Binary Logs
Passed to Slave1 Relay Logs
Executed in Master in Slave1
Passed to Slave2 Relay Logs
Executed in Master in Slave2

In MySQL 5.5, when a Master has Multiple Slaves, this is how an SQL statament is processed among the slaves (Example will be a Master with two slaves) using Semisynchronous Replication:

SQL Statement is

Executed in Master
Recorded in Master's Binary Logs
Passed to Slave1 Relay Logs
Acknowledged to Master of Receipt By Slave1
Passed to Slave2 Relay Logs
Acknowledged to Master of Receipt By Slave2

Replication is a little more robust and at least 1 Slave is More Closely Sync'd to the Master.


Altering partition functions dynamically by utilizing triggers

The cryptic error in the AFTER trigger is due to performing a DDL against the trigger target table. With the INSTEAD OF trigger, you would need to perform the INSERT to get the assigned IDENTITY value and then split the partition function. However, you probably don't want to use IDENTITY here anyway since those may have gaps that are sometimes large and result in an untidy partition boundary list.

Below is an example that ditches the IDENTITY and uses a RANGE RIGHT function, which I believe is more natural for incremental partition boundaries. This version validates exactly one row is inserted but could be extended to handle multi-row inserts if needed. Your use case as I understand it suggests only rare singleton inserts.

--start with no partition boundaries
CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int])
    AS RANGE RIGHT FOR VALUES ();
GO

CREATE PARTITION SCHEME [PS_Tenant_Isolation]
    AS PARTITION [PF_Tenant_Isolation]
    ALL TO ([Auth]);
GO

CREATE TABLE [Auth].[Tenant] (
     [TenantId] [int] NOT NULL
    ,[TenantActive] [bit] NOT NULL CONSTRAINT [DF_Tenant_TenantActive] DEFAULT 1
    ,[TenantName] [varchar](256) NOT NULL
    ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC)
) ON [PS_Tenant_Isolation]([TenantId]);
GO

CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant]
INSTEAD OF INSERT
AS
DECLARE @TenantId int;
BEGIN TRY

    --Get next TenantId and exclusively lock table to prevent deadlocking during DDL.
    --If other tables are partitoned via this function, add code to get exclusive locks on those too.
    SELECT TOP(1) @TenantId = COALESCE(MAX(TenantId),0) + 1 FROM [Auth].[Tenant] WITH(TABLOCKX);

    INSERT INTO [Auth].[Tenant] ([TenantId], [TenantActive], [TenantName])
        SELECT @TenantId, [TenantActive], [TenantName]
        FROM inserted;

    IF @@ROWCOUNT <> 1
    BEGIN
        RAISERROR('Exactly one row must be inserted into Auth.Tenant at a time',16,1);
    END;

    ALTER PARTITION SCHEME [PS_Tenant_Isolation]
        NEXT USED [Auth];

    ALTER PARTITION FUNCTION [PF_Tenant_Isolation]()
        SPLIT RANGE (@TenantId);

END TRY
BEGIN CATCH;
    THROW;
END CATCH;
GO

INSERT INTO [Auth].[Tenant]([TenantActive], [TenantName])
VALUES (1,'Partition Trigger Test A');
GO

EDIT:

  I see your notation, but given that queries will read from [Tenant],
  wouldn't the opposite happen where this would actually cause
  deadlocks?

The course-grained X lock on the Tenant table will wait for (be blocked by) other concurrent activity against the table to complete and, once granted, block other activity against the table. This blocking will prevent deadlocks on the Tenant table during the DDL operation within the trigger transaction. The duration of the SPLIT itself will be fast since rows are not moved between partitions. The duration of blocking before the initial block X lock is granted will depend on how long the other queries run.

In the case of multiple tables (i.e. related tables partitioned by schemes based on the same function), deadlocks can still occur if the locking order in the trigger is different than that of other activity. An exclusive lock on those tables too in the trigger can only mitigate the likelihood of deadlocks in that case. For example, if you have a SELECT query that joins Tenant and TenantDetails, both partitioned similarly, a deadlock may occur if the query acquires locks those tables in the reverse order as the trigger.

  Also, I understand with partition schemes you typically want to leave
  partitions on the left and right boundaries that are "empty" for
  proper switching.

Empty partitions are a consideration for SPLIT and MERGE but not SWITCH. With the SPLIT in the trigger, the split partition is always empty so no expensive data movement is needed to conform to the new boundary specification.

The general best practice is to MERGE boundaries when both adjacent partitions are empty. That said, you can sill MERGE without row movement as long as the partition containing the boundary (one on the right with a RANGE RIGHT function) is empty.


We've been doing this for almost five years, and we think that explicitly testing modifications is definitely doable, but it is quite slow.
Besides, we cannot easily run such tests concurrently from several connections, unless we use separate databases. Instead, we should test modfications implicitly - we use them to build up at least some of the test data, and verify that our selects return expected results.

I've written an article entitled Close Those Loopholes: Lessons learned from Unit Testing T-SQL, as well as some blog posts

Regarding your question "Is there a treshold in complexity where it gets completely hopeless?", complex modules need tests much more than simple ones.

To simplify maintenance, we generate expected results, and we store them in separate files - that makes a huge difference.


Mongodb deployment best practice for dual nodes?

The first part is the easiest answer - don't use master-slave, it has been deprecated for a couple of years at this point and will be removed in the future (once a few long standing limitations on replica sets are removed).

The second part, how to run with 2 nodes in a replica set is a bit more complicated.  The simplest answer is don't, running one host with a data bearing node and an arbiter to get to 3 nodes is actually preferable.  Consider these scenarios for a 2 node set:

Host 1 down, Host 2 up. Result = no primary
Host 1 up, Host 2 down. Result = no primary
Host 1 up, Host 2 up. Result = healthy

Now, lets add an arbiter on Host 1 and see how that impacts things:

Host 1 down, Host 2 up. Result = no primary
Host 1 up, Host 2 down. Result = healthy
Host 1 up, Host 2 up. Result = healthy

In fact, if you make the second node non-voting it's actually better than having it as a regular replica set node and gives you the same list of scenarios as having an arbiter on Host 1.

Strictly speaking, the answer to your question is that best practices suggest not to run with two nodes at all, since it actually makes the set more fragile from an operational perspective (though at least you have a copy of your data on another host for disaster recovery).

If you have to stick with 2 data bearing nodes, the best practice recommendation is to run an arbiter on one of the hosts, with the non-voting option as your alternative.


I think check your network connection.
In between your network connection interrupted.
So In your sqlnet.ora and also on sqlnet.ora on your server try to change inbound connection or outbound connection and also try to set connection timeout parameter in sqlnet.ora file.


The PASSWORD_VERIFY_FUNCTION is used, even when a privileged user changes another user's password, see below.

SQL> @?/rdbms/admin/utlpwdmg.sql
SQL> create user u1 identified by Oracle123#;

User created.

SQL> select profile from dba_users where username = 'U1';

PROFILE
------------------------------
DEFAULT

SQL> select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';

LIMIT
----------------------------------------
VERIFY_FUNCTION_11G

SQL> show user
USER is "SYS"

SQL> alter user u1 identified by 1;
alter user u1 identified by 1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8


How can I query on-premise SQL Server database from Azure SQL database using stored procedure?

Azure SQL database does not support linked servers or Polybase so you cannot directly virtualize the data from your on prem to your cloud db.
If the requirement is to access the data in the on-prem instance from the Azure SQL database and some delay on the latest data is acceptable, your options are:

Replicate the data from the on prem instance to the azure db. This is most "automated" way to do it. See https://docs.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database

Use DataFactory, SSIS or other ingestion method to copy the data yourself.

Hope this helps.


No COALESCE is not sargable.

Your own test demonstrates this well.

An exception would be if you created a computed column with the COALESCE expression and indexed that.

CREATE TABLE Test
(
    ID int primary key clustered,
    Mod6 int null,
    Foo AS COALESCE(Mod6, 0),
    INDEX IX_Mod6 NONCLUSTERED (Mod6),
    INDEX IX2_Mod6 NONCLUSTERED (Foo),
);

You could end up with a seek on that

SELECT Mod6
FROM Test WITH (INDEX = IX2_Mod6, FORCESEEK)
WHERE COALESCE(Mod6, 0) = 0;

ISNULL is marginally more sargable in that if it is totally redundant it can be optimised out and not prevent a seek.

i.e. if the column Mod6 is defined as NOT NULL then the following can produce a seek.

SELECT Mod6
FROM Test
WHERE ISNULL(Mod6, 0) = 0;

But this of course doesn't supply any benefit over just doing

WHERE Mod6 = 0


The Problem

This problem normally  happens because on master, you allow a poll of connections(threads) which run commands at same time (max_connections), but on the slave (on versions before 5.6) you have just 1 thread executing commands from binlog, lets say you have a big table, and you run an optimize or alter table and this command took 3 minutes on master, after execute it on master, it will be logged on binary log and relay to slave, when the slave pick this command from his log, it will start to run this command and any other query from master will need to wait until this command.

Solution 1

when you start to see the seconds behind master increase, try to look in the SHOW PROCESSLIST on the slave serve to see which query is been executed at that time

Solution 2

when you start to see the seconds behind master increase, try to look in the SHOW SLAVE STATUS which position of the binary log is been executed, and use the mysqlbinlog to find the command.

Solution 3

Update to MySQL 5.6 and start to make use of multi-threaded slave
a good start is to read this article: http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html


You can do that using a filtered aggregate, no need for subselects at all

to_char(100 * (count(*) filter (where ipam_addresses.host_id = 0)) / count(*)::numeric, '999D99%') v

The cast ::numeric is needed to avoid integer division


First, let me explain why your code doesn't work when A or B is NULL by formatting your code to show that you have 2 CASE expressions, one inside the other:

CF1 = (case
           when [A]>[B] then (CF1=3)
           else case
                    when [A]<[B] then (CF1=0)
                    else (CF1=1)
                end
       end)

If A > B is true, the result is (CF1=3). If that isn't true, and if A < B is true, the result is (CF1=0). Otherwise, if neither A>B nor A<B is true, the result is (CF1=1). This last case (the ELSE - ELSE part) covers any remaining case which means when A = B or A is null or B is null.

So to solve the issue, you need to explicitly compare A = B or compare A and B with NULL, to differentiate. As a result the CASE expression should have 4 parts, not 3. And (irrelevent to the issue) we don't need two nested expressions, we can have the result we want with one expression.

And I assume that you want the result to be 3, 1, etc and the (CF1=3) is just pseudo-code.

CF1 = case
          when A > B then  3
          when A < B then  0
          when A = B then  1
          else             null
      end

Note that the ELSE NULL is not needed as it is the default for CASE expressions. If you want, you can have even more strict and clear with your intentions code:

CF1 = case
          when (A > B) then  3
          when (A < B) then  0
          when (A = B) then  1
          when (A is null or b is null) then
                             null
          else               null     --  'ERROR: This should never happen'
      end


Query without precision brings different result

It's called "integer math" - when you divide two integers, any remainder is discarded. Compare:

SELECT integers = (10320238-2092189)/2092189,
       decimals = (10320238.0-2092189.0)/2092189.0;

Results:

integers    decimals
--------    ------------
3           3.9327465157

From the division topic in Books Online:

  If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.


Improve sql server lookup table performance

I assume you mean a SQL Server instances hosted in an Azure VM (as opposed to a SQL Azure instance).

Use BINARY(16) for MD5 hash. Is half the size of CHAR(32)
Must have a clustered index on the hash
Choose an appropriate hosting plan. Read Premium Storage: High-Performance Storage for Azure Virtual Machine Workloads
Follow best practices. Read Performance Guidance for SQL Server in Azure Virtual Machines and Performance Best Practices for SQL Server in Azure Virtual Machine

Other than that, approach this as any other performance investigation. Measure. Read How to analyse SQL Server performance. The solution will depend a lot on what your measurements find.


How to check when a record is replicated in SQL Server

yes,you can get the statements executed by distributor agent which are applied on subscriber by sp_browsereplcmds procedure.
There is a nice article to get familiar with distributor databse system tables and other objects.

http://www.sqlservercentral.com/blogs/vinaythakur/2009/10/23/some-useful-commands-and-sp-for-transaction-replication/

all replication information stored in distribution database.Further it depends how you get.  Kindly go through the article and get the desired information.


best strategy to update from invalid date formats vs allow_invalid_dates

Quick Solution as this is a very small Table(3078). I suggest to create a table with the correct default values and copy the records from the existing table and then rename the table. Hope this helps.


Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared

I checked the impdb log and saw that this object was not imported due to an OID conflict.

At the beginning of the log I found this message.

ORA-39083: Object type TYPE:"VERZANI"."DAYS_INTERVAL" failed to create with error:
ORA-02304: invalid object identifier literal

    Failing sql is:
    CREATE EDITIONABLE TYPE "VERZANI"."DAYS_INTERVAL"   OID 'C01480BB3D223837E0430100007FED58' AS OBJECT
           (DATE_           DATE,
            DAY_OF_WEEK     number(1,0));
    ORA-39083: Object type TYPE:"VERZANI"."DAYS_INTERVAL_TABLE" failed to create with error:
    ORA-02304: invalid object identifier literal

    Failing sql is:
    CREATE EDITIONABLE TYPE "VERZANI"."DAYS_INTERVAL_TABLE"   OID 'C01480BB3D273837E0430100007FED58' AS TABLE OF DAYS_INTERVAL;

So I dropped the schema and rerun the impdp again with the parameter TRANSFORM=oid:n. Now the database went up without any problems.


MASSIVE Identity column value jump in SQL Server 2014

Some possible culprits:

the testing process creates many rows in a transaction, and then rolls back the transaction

This seems like the most likely reason, as you mentioned you're currently doing testing.  Perhaps there are some automated tests being run that make changes to those tables, verify the results, and then roll back the changes.
Identity values are not reused after a transaction has been rolled back, which is what causes you to see large gaps in this scenario.

Someone used the RESEED command

You can manually change the current "next identity" value with this command:
DBCC CHECKIDENT ('dbo.Transaction_Payment_NO', RESEED, 1712149313);

This one seems less likely, because you or one of the admins would have to go out of their way to do it.  Similarly...

Someone inserted those values manually

You can insert whatever you like into an IDENTITY column by first running this statement:
SET IDENTITY_INSERT dbo.Transaction_Payment_NO ON;

Failovers and restarts

You mentioned this in the question, but just for completeness - SQL Server caches identity values in order to improve performance.  But those pre-allocated identity values can be lost if the service restarts or an AG failover occurs.  This leads to more predictable gaps though (10,000 on modern versions of SQL Server).


An ORA-01722 ("invalid number") error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number.
Nls_date_format parameter helps to implicitly convert the varchar to a date type.
Example:

SQL>INSERT INTO tbl values ('01-02-16');
SQL>SELECT ADD_MONTHS('01-02-16',2)

In the above example implicit type conversion happens since we have set NLS_DATE_FORMAT . In your case it was trying to extract number(Or you could tell explicitly) from SYSDATE but found string not date.
Example:

SQL>SELECT SYSDATE-'01' FROM dual;

Here this works perfectly and returns the date subtracting 1 day from the SYSDATE because it was able to convert the string to number.
The best way is to explicitly convert the varchar to a date type.
You can use the following query to subtract date from sysdate(assuming you have set NLS_DATE_FORMAT=DD-MM-RR):

SQL>SELECT SYSDATE-TO_DATE ('01-01-01');

Oracle recommends to use explicit type conversion.


Give this query a shot. I suspect that the "WHERE" clause is effectively turning your full outer join into a right join. When a record doesn't exist in ZT1, your where clauses are obviously going to evaluate false. By enclosing the AND clause in parenthesis for sanity's sake, and adding some sort of OR clause (I assumed that you should have a field named SaleDate in @TTBL, and that you only want to fetch records where the SaleDate is in that range one way or another), you should hopefully see this problem go away. Let me know how it goes.

SELECT
    Employee = COALESCE(ZT1.Employee,Z.Name),
    COUNT(ZT1.sales) AS Salecount,
    SUM(ISNULL(ZT1.[SaleTotal],0)) AS SaleTotal,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)/NULLIF(ZT1.[SaleTotal],0)) AS ActualSale,
    SUM(ISNULL(ZT1.[SaleTotal],0) - ISNULL(ZT1.[TotalB4Discount],0)) AS ProfitMargin,
    ISNULL(Z.SaleTotal0,0) AS SaleTotal0,
    ISNULL(Z.SaleTotal1,0) AS SaleTotal1
FROM dbo.holdings AS ZT1
FULL OUTER JOIN @TTBL AS Z ON Z.Name = ZT1.Employee
WHERE (ZT1.[SaleDate] >= '20170601'
AND ZT1.[SaleDate] <= '20170625')
OR (Z.[SaleDate] >= '20170601'
AND Z.[SaleDate] <= '20170625')
GROUP BY ZT1.Employee,Z.SaleTotal0,Z.SaleTotal1,Z.Name


'ERROR: text search dictionary "unaccent" does not exist' during CREATE INDEX?

I recommend this alternative approach to get an IMMUTABLE unaccent() function:
CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$  LANGUAGE sql IMMUTABLE;

Use that function for the expression index and in all queries. Detailed explanation:

Does PostgreSQL support “accent insensitive” collations?

And do not alter the original function:
ALTER FUNCTION unaccent(text) IMMUTABLE;
.. which also solves any possible issues with the search_path: The fact that you had to schema-qualify public.unaccent() in your index definition suggests that you might not have public in your search_path.
Then, your index:
CREATE INDEX author_label_hun_gin_trgm ON address
USING gin (f_unaccent(label_hun) gin_trgm_ops);

I did not include lower(). You can, but trigram indexes support case insensitive patterns out of the box:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Troubleshooting
If you are running all of this in the same session with the same search_path and one call works while the next reports function ... does not exist, then something is seriously broken in your database.
First make sure you are testing with a valid search_path setting:
SHOW search_path

Details in this related answer

How to create table inside specific schema by default in Postgres?

If you diagnose breakage, switch into alarm mode and take all the necessary steps to avoid data loss (like drawing a backup before you do anything else).
A broken index for the system table pg_proc might be the cause. To reindex all system tables from the shell:
reindexdb [connection-option...] --system mydb


Are "out of the row" fields read when a clustered index is used?

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row...

Actually, that depends on the setting of the large value types out of row option, which can be set using sp_tableoption. From the documentation:

The default is for MAX values to be stored in-row, up to 8000 bytes, if they fit. Unless you have used sp_tableoption to change the default, your MAX data will most likely be stored in-row.

That said, it is poor practice to use MAX data types for values that will never exceed 8000 bytes - use a non-MAX type instead. Aside from anything else, performance is often significantly poorer when dealing with MAX types, because SQL Server must be prepared to cope with data that might be up to 2GB in size.

  Is each field stored out of the row or only the max ones?

Only the MAX ones. In addition, if a previously in-row MAX column is moved off-row, only that column in that row is affected. It is replaced in-row by a pointer to the off-row LOB structure. There are also circumstances where non-MAX columns may be moved off-row.

  If you are using the clustered index of the table to read the whole record, are fields that are stored out of the row read, too?

Scanning the clustered index traverses only in-row data. If off-row data is needed for the query, it is looked up using the in-row pointer.


"Pausing" log shipping to allow normal DB use. Is this process valid?

Once you bring the log shipped database online you would have to restore a new full backup to restart log shipping, regardless of how long it has been. This is because you've recovered the database and cannot then restore log backups to that database.

There is no way to incrementally refresh the Dev/QA database from production with performing a full backup restore, except possibly there are third-party tools that can do this for you. You're better off working on a solution to automate the refresh of your Dev/QA environment and have this process run outside normal operating hours to reduce the impact on your team.


There are five types of keys in the heirarchy of any SQL Server column encryption. They are service master key, database master key, asymmetric key, certificate and symmetric key. SQL Server contains commands to back up the service master key, database master key and the certificate with the private key to separate password protected files. If you want to have something to preserve an asymmetric key, you can create one from a strong named key file (.snk) that already contains the key pair and save that. For the symmetric key, you will need to create it from a command, specify the key_source and identity_value and save those two values in your Vault. IF you create symmetric and asymmetric keys without saving the inputs, then you will not be able to export them. If you want to learn more about another method of securing the keys, I suggest reading about Extensible Key Management systems, which is a SQL Server enterprise edition feature. An EKM is a separate piece of hardware designed to hold the keys and perform the encryption and decryption functions.


In which timezone does psql display "timestamp without timezone"?

timestamp without time zone is time zone agnostic, so it is not adjusted to any time zone on display. This data type just doesn't have a time zone.
You can convert it to a certain time zone with AT TIME ZONE:
SHOW timezone;

   TimeZone
---------------
 Europe/Vienna
(1 row)

SELECT TIMESTAMP WITHOUT TIME ZONE '2020-08-01 12:00:00' AT TIME ZONE 'UTC';

        timezone
------------------------
 2020-08-01 14:00:00+02
(1 row)

Then the timestamp is interpreted as Viennese time, converted to UTC and displayed in my local Viennese time zone, which is offset two hours.


How can I enforce data integrity in this model with a M:2 relationship?

You can add a "model" attribute to Transplantation and use it (in combination with the Donor and Receiver IDs) to create the 2 foreign keys.

If the "model" attribute is not part of Experimental Animal but of Used In, make the foreign keys target that relation.

In SQL terms that would be something like:

CREATE TABLE Transplantation
( DonorID
, ReceiverID
, ModelID
-- other attributes

-- primary key and other constraints

, CONSTRAINT Donor_FK
    FOREIGN KEY (ModelID, DonorID)
    REFERENCES UsedIn                        -- UsedIn or ExperimentalAnimal
       (ModelID, ExperimentalAnimalID)       -- depending where the model
, CONSTRAINT Receiver_FK                     -- attribute resides
    FOREIGN KEY (ModelID, ReceiverID)
    REFERENCES UsedIn                        -- same as above
      (ModelID, ExperimentalAnimalID)
, CONSTRAINT CommonSense_CK
    CHECK (DonorID <> ReceiverID)
) ;

The above FKs to be created, a UNIQUE constraint on UsedIn(ModelID, ExperimentalAnimalID) is required.


Mysql Innodb as Key value store, performance

Joel, there is a lot of unknowns. If you post your my.cnf, queries, table definitions and queries, EXPLAIN plan output, then you might get more precise answers.

You say "disabled query caching and other caching" what 'other caching' are you referring to? What steps did you take to disable QC? Did you restart your MySQL server between tests to flatten any buffers? If no and the KVP table was smaller and fit into RAM then InnoDB might have been able to serve queries fitting a certain criteria from data in the innodb buffer pool.

You also refer to memcache. With MySQL 5.6 there is a memcache api. this means you can use memcache calls to access data stored in innodb. This will not be incidentally speeding up your queries. There is also another method of bypassing the SQL layer within MySQL called HandlerSocket. These are a couple of subjects that I would point you at should you continue to desire this type of access to your data with the added bonus of having MySQL persist the data to disk and also be crash safe through InnoDB's crash recovery process.


ORA-14400: inserted partition key does not map to any partition

It is an older article and it's assuming the year is 2015.
Change the script so partitions look like this:

PARTITION BY RANGE (created_Date)
(PARTITION tab1_part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION tab1_part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')) TABLESPACE users);

The whole script should look like this:

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id                NUMBER,
  code              VARCHAR2(20),
  description       VARCHAR2(50),
  created_date      DATE,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_Date)
(PARTITION tab1_part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION tab1_part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')) TABLESPACE users);

CREATE INDEX tab1_code_idx ON tab1(code) LOCAL;

INSERT INTO tab1
SELECT level,
       CASE
         WHEN MOD(level,2)=0 THEN 'CODE1'
         ELSE 'CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN 'Description for CODE1'
         ELSE 'Description for CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN SYSDATE
         ELSE ADD_MONTHS(SYSDATE, 12)
       END
FROM   dual
CONNECT BY level <= 100000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

This is because of this:

         WHEN MOD(level,2)=0 THEN SYSDATE
         ELSE ADD_MONTHS(SYSDATE, 12)

SYSDATE will be inserted into tab1_part_2017 and ADD_MONTHS(SYSDATE, 12) will be inserted into tab1_part_2018 and you will get equal number of rows in both partitions (50000 in each to be exact).


Why doesn't running a full sample *always* improve query performance

A perfect query optimizer should make the best plan if provided with a full sample of statistics. But even a perfect optimizer does not need the information provided by a full sample of statistics to make the best plan. A simple example:

select count(*)
from tab1
where col1=3
and col2=4

Assume that for this table tab1 there exists a nonclustered index on col1. The optimizer has to possible plans:

1) scan the whole table and check for each row if col1=3 and col2=4
2) consult the index to find the position of all rows in the table with col1=3. Lookup each of these row in the table and check if col2=4

now assume the table has a size of 123,456 GB and 1 123 456 789 rows and 567 890 123 of them (about half of them) have col1=3. What should the optimizer propose? It schould propose a full table scan. The other possibility, looking up 567 890 123 rows one by one is too expensive.

Now assume the optimizer only has a rough estimate of the table data: the table size is about 100GB, there are about 1,000,000,000 rows and about 600,000,000 of them have col1=3. Based on this information the optimizer will choose the same good plan as in the first case.

The decision is based on the size of the ratio 100*(# of col1=3-rows)/#(all rows) and if this value is larger  10% (I don't know which value is really used) the full table is scanned. The decision is not based of the size of the 5th place after the decimal point of the ration.

In a special case where the exact ratio is 10,00001% and the ratio based on the rough estimate is 9,99999% the optimizer will choose different plans but maybe for these different plans the execution time may be the same for both plans or may be better for the plan based on the rough estimate

Administrative jobs like calculationg statitistics should be done only with least resource consumption that is possible. Therefore only the precision that is necessary should be tried to be reached.


What is the best way to automate a migration of multiple SQL Databases to a different hard disk?

Something as simple as the below can construct your script

SELECT
'ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILE (name=N'''+[f].[name]+ ''', filename = N''' + REPLACE(f.[physical_name],'G:','E:') + ''')'

FROM sys.databases d
    INNER JOIN sys.[master_files] f ON d.[database_id] = f.[database_id]
WHERE d.[database_id] > 4

You can then take the output and save it, or you could potentially wrap it further to turn it into dynamic SQL to actually run it

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'';

SELECT @SQL +=
'ALTER DATABASE ' + QUOTENAME(d.name) + ' MODIFY FILE (name=N'''+[f].[name]+ ''', filename = N''' + REPLACE(f.[physical_name],'G:','E:') + ''')'

FROM sys.databases d
    INNER JOIN sys.[master_files] f ON d.[database_id] = f.[database_id]
WHERE d.[database_id] > 4

PRINT @SQL
--EXEC (@SQL)

Note the use of REPLACE to deal with the change of file location

For additional parameters and commands the above script can be edited easily to include things such as setting the database to single_user or offline.

Following this the SQL Server should be brought offline, the files moved in the file system, and the server brought back online.


Double quotes denote identifiers that contain special characters, so set search_path to "blip_schema,public" will set the search path to a single schema named blip_schema,public.

The names you supply to set search_path have to follow regular naming rules, so no quotes at all, unless you have one schema that requires quoting:

set search_path to blip_schema,public;

or if you do want to use quotes, you need them for each identifier:

set search_path to "blip_schema","public";


How can I swap two values from particular column in a table in Postgres?

update the_table
   set name = case id
                 when 1 then (select name from the_table where id = 2)
                 when 2 then (select name from the_table where id = 1)
              end
where id in (1,2);

This assumes that id is unique (e.g. the primary key)


Difficulty removing files from filegroup

Try running DBCC UPDATEUSAGE against the database as SQL may be reporting incorrect data on the space used in the database.

See: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017

If this alters any space usage metadata then you could retry the EMPTYFILE again, then remove the files if it is successful.


I have a bunch of SQL databases. How should I put these into Azure?

the first obvious solution could be the adoption of a SQL Server VM hosted on azure.
You can move there databases and ssis packages in any traditional ways.
This is an Azure Iaas solution and usually is the most expensive. You can reduce cost moving your on prem sql license wiht the "Bring Your own license" solution coming from software assurance license mobility option.
But you can also adopt Azure Paas solution using:

Azure SQL databases (https://docs.microsoft.com/en-us/azure/azure-sql/database/)
Azure SQL managed instances (https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/sql-managed-instance-paas-overview)

I suggest you the second one because it is almost equal to the a on prem sql server instance. But both laks of ssis solution.
You pointed right to Azure Data Factory to relace dtsx packages but you can also run the same dtsx packages inside azure data factory:
https://docs.microsoft.com/it-it/azure/data-factory/create-azure-ssis-integration-runtime
In the meantime you redisign the ssis packages in ADF you can eventually run them temporarly in a Azure VM.
For moving database to an Azure SQL Server Managed instance i suggest you to use DMA:
https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15
You can do a online or offline migration. Look at this:
https://azure.microsoft.com/en-us/resources/videos/data-migration-demo-using-azure-migrate-dma-and-dms/
Here you can find a free lab to test the procedure:
https://github.com/microsoft/MCW-Migrating-SQL-databases-to-Azure


Running MS Access queries for WAN hosted mySQL database

You could run Windows server on AWS EC2 or a Windows Desktop using Amazon Workspaces and try running Access there in AWS's network environment.  That may significantly improve performance.  Another thing to consider is that network performance can be limited on micro instances.

There might be other advantages of hosting your whole application in the cloud in terms of uptime.


Is there any performance advantage to marking a SQL function as LEAKPROOF?

Seems like there is no difference in the most simple cases between thee two performance profiles,

CREATE TABLE t1 AS
  SELECT x::int FROM generate_series(1,1e6) AS gs(x);

CREATE VIEW v1 AS TABLE t1;

CREATE VIEW v2 WITH (security_barrier)
  AS TABLE t1;

-- BASE TABLE
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM t1;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM t1;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM t1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM t1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM t1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM t1;

-- VIEW
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v1;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v1;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v1;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v1;

-- VIEW with SECURITY_BARRIER
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v2;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v2;
EXPLAIN ANALYZE SELECT foo_leaky(x) FROM v2;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v2;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v2;
EXPLAIN ANALYZE SELECT foo_leakproof(x) FROM v2;

These were all 100-110ms. No difference in performance.

However changing the functions to use

CREATE FUNCTION foo_leakproof(bar int)
RETURNS int AS $$
  SELECT bar+1;
$$
IMMUTABLE LEAKPROOF
LANGUAGE sql;

CREATE FUNCTION foo_leaky(bar int)
RETURNS int AS $$
  SELECT bar+1;
$$
IMMUTABLE
LANGUAGE sql;

Will make all the calls slower, but the calls on v2 (our view with a security_barrier) massively slower (2x slower) on the version with and without LEAKPROOF. This is also in agreement with the docs on security_barrier,

  Views created with the security_barrier may perform far worse than views created without this option. In general, there is no way to avoid this: the fastest possible plan must be rejected if it may compromise security. For this reason, this option is not enabled by default.

;tldr LEAKPROOF seems to never make queries slower. However, it's not making them faster either -- in my tests. Perhaps it gives the ability to greater-optimize a query on a view with a security_barrier, but I haven't created that environment. security_barrier massively slows down the view, and should only be used when needed.

Benchmarks done with PostgreSQL 10.4.


How to convert UPDATE operation into INSERT using triggers

This is not very complicated to do. If a BEFORE trigger returns a NULL value, this prevents the UPDATE from taking place.

So in the trigger function you can check the conditions if an UPDATE is allowed. If not, Run an UPDATE statement that marks the row as inactive and do an INSERT.

Something along the lines (not tested!):

create or replace function prevent_update()
  returns trigger
as
$$
begin
   -- adjust this check to whatever you want to prevent
   -- but make sure to only enter this part if the status is not inactive.
   -- you might want to check pg_trigger_depth() as well to make sure you never create an endless loop
   if old is distinct from new and new.status <> 'inactive' then

     -- The row was changed, but status column is the same, so we want to mark
     -- the row as inactive

     -- Because the new status is 'inactive' the trigger that is now fired for this UPDATE statement
     -- will not do anything because we only do this for status other than 'inactive'
     UPDATE the_table
       SET status = 'inactive'
     WHERE id = new.id; --<< the primary key column

     INSERT INTO the_table (id, some_column, status)
     VALUES (default, new.some_column, 'active');

     -- do NOT proceed with original UPDATE
     return null;
   end if;

   -- all OK, proceed with the update
   return new;
end;
$$
language plpgsql;

And the trigger definition:

create trigger prevent_update_trigger
   before update on the_table
   for each row execute procedure prevent_update();

I would be very careful to deploy something like this. This kind of "magic" in the background might be the cause of a lot of confusion and trouble, so use with care!


Postgres Combine Summed Values from 2 Queries / Tables into Single Row

Looks like I can use CROSS JOIN
SELECT recorded_minutes, recorded_hours, budgeted_minutes, budgeted_hours
FROM
    (SELECT
        SUM(minutes) as recorded_minutes,
        SUM(hours) as recorded_hours
    FROM recorded_time
    WHERE
        project_id = 1) rt
CROSS JOIN
    (SELECT
        SUM(minutes) as budgeted_minutes,
        SUM(hours) as budgeted_hours
    FROM budgeted_time
    WHERE
        project_id = 1) bt

http://www.sqlfiddle.com/#!17/e2388/4/0


Statistics Update Time is missing in Actual execution plan

It is indeed in the execution plan, for those stats that were used.
There are two possible reasons why you don't see it:

You use an older version of the database engine, where this info wasn't present in the XML.

You use an older version of SSMS, which doesn't expose the info in the XML.

The answer to which of the above you have is by looking in the XML and see if the info is there. I'm on SQL Server 2019 and SSMS 18.5.
And you need to look at the right place, as well. You probably have to select the SELECT (outermost) operator, perhaps twice with something else in between, and view the Properties window:


According to the mongo manual on $group, the command should be like this.

db.requests.aggregate(
    [
        {$match: {
                "updatedAt" : {"$gt" : ISODate("2020-05-14")},
                "status" : "SUGGESTED"
        }},
        {$group: {
            _id: {updatedBy: "$updatedBy.displayName"},
            number: {$sum: 1}
        }}
    ]
)


SQL Server Maintenance Plan Fails to Rebuild the Indexes

Well, I  have found the answer to this.

I removed the tick from "Compact Large Objects" option and just included the re-organize and re-build indexes tasks and it succeeded!!! Also I will log to text file for future failure details.


MySQL database stops working after log enabled

You certainly have a problem but that does not look related to enabling general logging.  Are you sure it's not that those errors have been filling your error log previously and you only just noticed them after an investigation stemming from enabling general_logging?

Enabling general_logging can and cause your server seemingly hang.   General logging will log every single query thrown over the fence to mysql (even one resulting in syntax errors).

This can cause the file size to grow a lot more rapidly than expected.  Run

show variables like 'general_log_file';

Then check the disk usage for that path from the shell command line

df -h /var/log/mysql/mysql.log

If that reports 0 available and 100% use then it is very much expected mysql would hang.

It's the defined behavior of mysql when any log (general, binary, error) cannot be further written to because space has been exhausted.   The idea is it will just hang out until space is made available.

To remedy you want to either more the log to a filesystem with a filesystem with enough space, or just delete it if you don't care.  Then either restart w/ the general_log variable pointing to a file system with enough space for your traffic loads or add more space to the existing file system.


Assuming you have the process down tight (as in scripted, tested, re-tested, and tested with the newest and most junior persons on the team) the only other area that I seen people slip on is accessibility of the key. Most people get that they should not store it in the same place as the backup files or even transport them together.

What is sometimes missed is whether the keys/certs are as well protected and accessible in a disaster. I've encountered users asking for some built-in backdoor from Microsoft (doesn't exist) or seek help to hack their own DBs because the key was lost along with a major incident at their primary data center (key/cert backups were in a secure administrative server that was not replicated to the DR site).

As for keeping them secure and fresh, the former is no different from storing other valuable data. Place in a secure host, highly restrict access (JIT if possible), all access logged in a secure tamper evident location (non-repudiation). Rotation process and mechanism must be automated to include the backup and DR sites. Everything validated by a different party from the one that executed on the tasks. Really no rocket science here. Things fail when humans think the tasks are trivial and they can do it in their sleep. Automation that has logs, monitors and alerts built-in will significantly reduce your risks.


Update non key ID column for multiple rows at once

You can use the maximum id value and add to each id. This will probably produce a lot of gaps. That can be avoided if the DBMS has window functions - like ROW_NUMBER() - implemented:

INSERT INTO tableX
    (id, name, year)
SELECT
    m.maxid + t.id,       -- m.maxid + ROW_NUMBER() OVER (ORDER BY t.id),
    t.name, 2017
FROM
    ( SELECT MAX(id) AS maxid
      FROM tableX
    ) AS m
  CROSS JOIN
    mytableX AS t
WHERE
    t.year = 2016 ;


It almost certainly doesn't really matter.

The only significant I/O going to files in the database path would be to the recovery history file (and even that isn't much).

Considering that DB2 uses the first storage path by default if you do not specify DBPATH ON, I would put it on /data if that was the only choice.

My SOP is to keep DBPATH in the instance owner home directory.  This is obviously another important location and perhaps makes more sense than the other two locations.


Encapsulating the queries into Views or not, is your choice. For every one of the below queries, you can create a view:

CREATE VIEW category_url AS
  ( Query
  ) ;

If you want only the IDs of the categories and URLs, the simplest (joining only 2 tables) is this - no need for joining the tag table, as long as the foreign key constraints are defined:

    SELECT DISTINCT
        ct.category_id,
        ut.url_id
    FROM
        category_tag AS ct
      JOIN
        url_tag AS ut
          ON ut.tag_id = ct.tag_id

or (the equivalent) which can be easily extended to include the number of tags relating a category with a url:

    SELECT
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags         --- optional
    FROM
        category_tag AS ct
      JOIN
        url_tag AS ut
          ON ut.tag_id = ct.tag_id
    GROUP BY
        ct.category_id,
        ut.url_id

If you also want columns from the category and url tables as well (which is very probable), you need to join those tables, too:

    SELECT
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags,        --- optional

        c.*,                                       --- the columns from `category`
        u.*                                        --- and `url` that you need
    FROM
        category_tag AS ct
      JOIN
        url_tag AS ut
          ON ut.tag_id = ct.tag_id

      JOIN
        category AS c
          ON c.id = ct.category_id
      JOIN
        url AS u
          ON u.id = ut.url_id

    GROUP BY
        ct.category_id,
        ut.url_id


Software need db owner permission to master database (sql2016)

I have seen software that requires a sysadmin server role membership, at least during installation. It might be that the software creates dbo-owned objects in master and db_owner is the easiest way to allow that without resorting to sysadmin role membership. Although it might be possible to provide more granular permissions, application code changes might be needed and one cannot the grant the minimal permissions necessary without knowing specifically what the software does and the vendor would need to support such changes.

A separate instance/machine (including in Azure) can help mitigate security concerns. SQL Server Express might be an option to reduce costs if you can work within the resource limitations but be aware your HA options will be limited.


Is there a way to set variable in UPDATE statement in PostgreSQL (plpgsql)?

Assuming the update only affects one row:

DECLARE
c INTEGER;
UPDATE table SET col = col + 1 WHERE id = 1
  RETURNING col INTO c;

Reference.


Do repeated columns affect normalization?

Repeated data violates first normal form itself.

For an example, to be in the first normal form, you cannot have columns like phone1,phone2 in the employee table.

However in your scenario, having the same columns for two tables does not violate first normal form as long as you have a single column for a particular property.

Your approach to have a common table should be based on your application requirements.

External Source

Hope this helps :)


UPDATE dbo.Bit
  SET Filer = 'D'
  WHERE CustomerID = '3512242'
  AND Filer = 'P';

Documentation for UPDATE with many examples.


Why am I getting an error in execute immediate command in oracle 11g?

Actually i sort of got the problem here. In the Insert into statement I was not giving proper quotes for the Values which are VARCHAR2. Got sorted now.


You have to add the repo list

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10
echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list
sudo apt-get update

Then re-run  sudo apt-get install mongodb=2.2.7

That should work. The repo list file was the differentiating step which you've missed


GROUP BY aggregates the result values of the SELECT statement based on its arguments.
Removing the GROUP BY clause and arguments from your SQL will allow duplicates to be presented.
For MariaDB recommend sql_mode=ONLY_FULL_GROUP_BY (not default, ref: manual) to ensure that SQL statements are correctly formed with respect to GROUP BY.


AlwaysOn Active Secondaries should provide the capabilities you're looking for, including virtually realtime replication of data, and reporting capability on the secondary.

  The Always On availability groups active secondary capabilities include support for read-only access to one or more secondary replicas (readable secondary replicas). A readable secondary replica can be in either synchronous-commit availability mode, or asynchronous-commit availability mode. A readable secondary replica allows read-only access to all its secondary databases. However, readable secondary databases are not set to read-only. They are dynamic. A given secondary database changes as changes on the corresponding primary database are applied to the secondary database. For a typical secondary replica, the data, including durable memory optimized tables, in the secondary databases is in near real time. Furthermore, full-text indexes are synchronized with the secondary databases. In many circumstances, data latency between a primary database and the corresponding secondary database is only a few seconds.

This technology also provides the ability to failover to the secondary in the event of a hardware or software failure on the primary, with minimal or no data loss.

Transactional replication won't prevent access to the data at the subscriber.  Transactional Replication offers a huge amount of customization and requires quite a lot of configuration and management, whereas AlwaysOn provides a simplified way of making an entire database or set of databases highly available.


Discover the original OS of a datafile from a oracle backup

Mount the database and make a level 3 datafile header dump:

alter session set tracefile_identifier = 'header_dump';
alter session set events 'immediate trace name file_hdrs level 3';

In the trace file, you will find this section:

Platform Information:    Creation Platform ID: 13
Current Platform ID: 13 Last Platform ID: 13

The platform ID identifies the platform of the datafile, and that is all what matters.
You can match the platform ID to platform name by using the information in the V$TRANSPORTABLE_PLATFORM view. For example platform 13 is x86 Linux 64-bit:

SQL> select platform_name from v$transportable_platform where platform_id = 13;

PLATFORM_NAME
--------------------------------------------------------------------------------
Linux x86 64-bit


Database mail depends on what server, profile is configured in Database Mail
Ref: Configure Database Mail

as for using sp_send_dbmail, see sp_send_dbmail (Transact-SQL)

This db mail functionality is available from SQL Server 2005.

See also these example database mail configuration scripts by Dan Guzman.


mongodump: fact.bson: input/output error

Use the --out/-o parameter to indicate a directory on a filesystem where you have enough room.


I found my mistake. I realized that spider_link_mon_servers table must contain spider nodes, not backend nodes. And my spider nodes are not running on the same instance as backends. Spider tables run on port 3307.

So I need to configure monitoring servers like this (let's call them "proxy"):

CREATE SERVER server_main_proxy
 FOREIGN DATA WRAPPER mysql
OPTIONS(
 HOST '10.2.0.1',
 PORT 3307,
 DATABASE 'db01',
 USER 'spider',
 PASSWORD '123456'
);

CREATE SERVER server_backup_proxy
 FOREIGN DATA WRAPPER mysql
OPTIONS(
 HOST '10.2.0.2',
 PORT 3307,
 DATABASE 'db01',
 USER 'spider',
 PASSWORD '123456'
);

TRUNCATE mysql.spider_link_mon_servers;
INSERT INTO mysql.spider_link_mon_servers
(db_name, table_name, link_id, sid, server)
VALUES
('%', '%', '%', 100, 'server_main_proxy'),
('%', '%', '%', 101, 'server_backup_proxy');

SELECT spider_flush_table_mon_cache();

Now, if I stop "main" instance (3306 one), spider detects it and access only backup node.

There is still one feature I have not been able to operate: use only main backend if it is alive, and use backup backend only if the main backend dies...

EDIT 11/02/2014 :

The parameter "alc" (active_link_count) set to 1 is what I need in order to use second backend as a failover backend only. So something like this did the trick:

ALTER TABLE np ENGINE=SPIDER COMMENT='wrapper "mysql", srv "server_main server_backup", database "db01", table "np", mbk "2", mkd "2", alc "1", msi "100 101", link_status "0 0"';


Specifying columns in LEFT JOIN through subquery beneficial performance wise?

For most simple cases, like your, there will not be any difference. SQL Server is lazy and will figure out the most easiest way of completing your query and it will do a very good job at it (most of the time).

The best way of analyzing the difference yourself is to look at the query plan of both queries.

Given this example of two queries similar to yours:

SELECT
    c.CustomerId,
    o.OrderId,
    o.ArticleId,
    o.Customerid
FROM
    dbo.Customer c
INNER JOIN
    dbo.orders o
    ON o.CustomerId = c.CustomerId

SELECT
    c.CustomerId,
    o.OrderId,
    o.ArticleId,
    o.Customerid
FROM
    dbo.Customer c
INNER JOIN
(
    SELECT
        o.OrderId,
        o.ArticleId,
        o.Customerid
    from    dbo.orders o
) AS o
    ON o.CustomerId = c.CustomerId

The query plans for both will look exactly the same.

If you hover your mouse over the Index Scan operator you will see the Output List at the bottom of the popup.
The Output List will show exactly which columns that will be "extracted" from the table. Both queries produce the same Output Lists which means that there is no benefit of "pre-selecting" the columns because SQL Server is already doing this for you.

This is true in your example since you are specifically specifying columns from both tables in your select clause, if you would do SELECT * then there could be a difference.


Can a Wallet be used with SQL Developer? How?

One way to do this is to specify an Advanced Connection Type and enter a custom JDBC URL as follows:

jdbc:oracle:oci:/@myDatabase

In addition the "OS Authentication" check box needs to be checked. This would seem to cause it to pass the OS username and password, but apparently (perhaps because I don't have NTS set as an authentication service in my sqlnet.ora) it does not and instead uses the wallet.


I got a 2 core Mysql Server with 25 GB of RAM, 1 TB of Hard drive running on windows server. What is the proper configuration?

Set innodb_buffer_pool_size = 14G; don't bother with any other tuning until you encounter issues.

If, by 1000 tickets/day, you mean a few thousand queries per day, well, that is 'trivial'.  Even a few thousand queries per minute is not a lot.

Further analysis...

Observations:

Version: 5.6.32-log
25 GB of RAM
Uptime = 22:16:52; some GLOBAL STATUS values may not be meaningful yet.
You are running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

1700 qps, not queries-per-day!  So the VARIABLES and STATUS are worth studying.

innodb_buffer_pool_size = 14G since is currently considerably bigger than the dataset.)  Also
innodb_buffer_pool_instances = 14

Com_rollback is quite high (3.7/sec); are you deliberately running things that need to be undone?  Could they check sooner, thereby avoiding a potentially costly rollback?  Com_rollback and Com_commit are surprisingly close in value.  Sounds fishy.

The next upgrade may spit at you about innodb_additional_mem_pool_size, which is unused and deprecated.  Remove it from my.cnf.

myisam_sort_buffer_size = 500M  (The current 3G threatens to cause you to run out of RAM.)

tmp_table_size and max_heap_table_size work together.  The former is dangerously high; the latter is possibly too small.  Set both to 200M.

(Normally I advise against using the Query Cache.  However, your application seems to make good use of it.)

Created_tmp_disk_tables is quite high.  That, and some other clues, indicate inefficient queries.  See this for details on finding slow queries and presenting them for critique.

Since you are running on Windows, I think thread_cache_size should be set to 0.

Details and other observations:

( Key_blocks_used * 1024 / key_buffer_size ) = 1,192 * 1024 / 8M = 14.6% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.

( innodb_buffer_pool_size / _ram ) = 20480M / 25600M = 80.0% -- % of RAM used for InnoDB buffer_pool

( innodb_buffer_pool_instances ) = 20 -- For large RAM, consider using 1-16 buffer pool instances, not allowing less than 1GB each

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 1,107,476 * 16384 / 20480M = 84.5% -- buffer pool free
-- buffer_pool_size is bigger than working set; could decrease it

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 1,107,476 / 1310720 = 84.5% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size is bigger than necessary?

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 3,195,551,744 / 20480M = 14.9% -- Percent of buffer pool taken up by data
-- A small percent may indicate that the buffer_pool is unnecessarily big.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 1,081,532,416 / (80212 / 3600) / 2 / 2048M = 0.0113 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 80,212 / 60 * 2048M / 1081532416 = 2,654 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Com_rollback ) = 295,694 / 80212 = 3.7 /sec -- ROLLBACKs in InnoDB.
-- An excessive frequency of rollbacks may indicate inefficient app logic.

( innodb_additional_mem_pool_size ) = 32M -- (deprecated in 5.6.3, removed in 5.7.4.)
-- A high value is unnecessary.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( join_buffer_size / _ram ) = 262,144 / 25600M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( myisam_sort_buffer_size / _ram ) = 3072M / 25600M = 12.0% -- Used for ALTER, CREATE INDEX, OPTIMIZE, LOAD DATA; set when you need it. Also for MyISAM's REPAIR TABLE.
-- Decrease myisam_sort_buffer_size to keep from blowing out RAM.

( query_prealloc_size / _ram ) = 8,192 / 25600M = 0.00% -- For parsing. Pct of RAM

( query_alloc_block_size / _ram ) = 8,192 / 25600M = 0.00% -- For parsing. Pct of RAM

( net_buffer_length / max_allowed_packet ) = 16,384 / 256M = 0.01%

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Qcache_inserts - Qcache_queries_in_cache ) = (9257553 - 3004) / 80212 = 115 /sec -- Invalidations/sec.

( Created_tmp_disk_tables ) = 597,469 / 80212 = 7.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Created_tmp_tables ) = 597,469 / 614226 = 97.3% -- Percent of temp tables that spilled to disk
-- Maybe increase tmp_table_size and max_heap_table_size; improve indexes; avoid blobs, etc.

( tmp_table_size ) = 853M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( Com_rollback / Com_commit ) = 295,694 / 298826 = 99.0% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic

( Select_scan ) = 98,730 / 80212 = 1.2 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( innodb_autoinc_lock_mode ) = 1 -- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional".

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( Threads_created / Connections ) = 322 / 1160 = 27.8% -- Rapidity of process creation
-- Increase thread_cache_size (non-Windows)

Abnormally large:

Com_show_keys = 0.063 /sec
Com_show_plugins = 0.27 /HR
Innodb_buffer_pool_pages_free = 1.11e+6
Qcache_hits = 1529 /sec
Select_range = 35 /sec
Select_range / Com_select = 23.2%
Sort_range = 36 /sec
myisam_sort_buffer_size = 3072MB


The problem is that I forgot to migrate a template database.
Thereafter pg_upgrade upgrades successfully.


Insert into table select - Replication lag - Percona Server 5.6

The semantics of that statement is to do it all in a single transaction.  This may well exceed innodb_lock_wait_timeout and/or cause other operations to exceed it.

With RBR, each of the 200M rows will be separately fed through the replication stream; this is a huge amount of data (more than the table size, itself).

The Slave cannot handle any other replication during the execution of the 200M individual inserts.

What to do?  First, explain more about the situation.

Does table2 change during the operation?
Does table2 exist on the slave?
Let's see SHOW CREATE TABLE table.  (Need to see indexes, FKs, etc)
Is the schema for table1 identical to table2?
Can you switch to SBR for this one statement?  That will solve the replication stream bloat, but not the other comments.
Will it be OK to copy the rows over in chunks of, say, 1000 rows?  Each chunk being one transaction?  (That is, if there is a crash, the table may be partially copied.)  If so, adapt the tips in http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks .


Changing Data Directory on MySQL DB Server

Open Windows CLI as Administrator and Shutdown MySQL

net stop mysql

Copy the Data to D:\ProgramData\Data

xcopy /s C:\ProgramData\MySQL\MySQL Server 5.6\data D:\ProgramData\Data

Create or edit C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

Add this to the my.ini

[mysqld]
datadir = D:/ProgramData/Data

Startup MySQL

net start mysql

If you can not execute net start mysql, try the Windows control panel.
Login to MySQL and verify everything is good

When you login to MySQL, run this

mysql> SHOW GLOBAL VARIABLES LIKE 'datadir';

Remember to give your new data directory the same full permissions for users Network Service and Administrator that the default data dir was using, otherwise it'll cause the dreaded "started and then stopped" error.  Don't delete this dir until you're sure your new one is working.

If this does not work, here is the rollback plan:

net stop mysql
del "C:\ProgramData\MySQL\MySQL Server 5.6\my.ini"
net start mysql


Filtered index only used when the filtered part is in the JOIN not the WHERE

For the optimizer to match a predicate to an index (filtered or otherwise), the predicate must appear adjacent to the Get operation in the logical query tree. To facilitate this, predicates are generally pushed as close as possible to the leaves of the logical tree before optimization begins.

To greatly simplify, the physical index strategy implementation does this:

Predicate + Logical Get -> Physical Get (using Index)

The query you are interested in starts with the predicate above an outer join:

Predicate on T2 --+-- LOJ -- Get (T1)
                       |
                       +---- Get (T2)

This shape does not match the index strategy rule because the predicate is not adjacent to the Get. So, the first part of the answer is that filtered index matching will fail unless the predicate can be pushed past the outer join.

The second part is simply that the optimizer does not contain the necessary exploration rule to move a predicate past an outer join on the preserved side, because the transformation is so rarely valid. It is a general feature of the optimizer that only the most frequently useful rules are implemented.

As a result, matching the filtered index fails in this case. To be clear, the rewrite would be valid in the very specific case you cite (second query).

For the first query form (with different semantics), the predicate is associated with the join from the start, and the predicate push-down logic can move this the short distance to the Get because it does not have to move past an outer join as explained above.

Background and further information:

Query Optimizer Deep Dive (in four parts)


Equivalent of SQL’s "INNER JOIN USING" in relational algebra

Relational natural join is more or less SQL natural join. SQL natural join is defined as SQL inner join using all common columns. (And it is only defined when each argument has exactly one column with each of those names.) If you find what the course actually says about relational vs SQL natural join & inner join with using then it will be that. If the course says that there is an "equivalence" for that expression then it will be because the set of common columns for those tables will be those columns in a particular example.

(To say that relational algebra natural join "is the equivalent of" something in SQL is vague & needs defining. There are many versions of relational algebra that differ in operators available, in how operators with a given name work & even in what a relation is. Meanwhile SQL tables can have NULLs, multiple columns with the same names & multiple rows with the same contents--all typically non-relational.)


Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

The two queries are functionally identical so should perform the same and as you can see from the query plans SQL server has indeed spotted that there is no difference between the two.

Of course as soon as you need to perform and aggregate operations you need to use grouping instead, otherwise you'll end up having to use the "distinct" version as a derived table and joining for the other detail which is not going to be the efficient way around.


Combine rows to remove nulls while using latest value based on time

SELECT id
     , SUBSTRING(MAX(time || name) FROM 11) "name"
     , SUBSTRING(MAX(time || title) FROM 11) title
     , MAX(time) "time"
FROM test_table
GROUP BY id

The FROM 11 part extracts the data (name or title) from the concatenated aggregate values. First 10 characters is the date (time column).


You can combine a GROUP BY to find the max() value and a window function:

select date_trunc('hour', "time") as "time",
       sum(max(value)) over (order by date_trunc('hour', "time"))
from the_table
group by date_trunc('hour', "time")
order by date_trunc('hour', "time")

This works, because the window function is applied after the GROUP BY. Using an order by in the window definition turns a "regular" sum into a running total.

To round the timestamp to other intervals, see these two questions on Stackoverflow

What is the fastest way to truncate timestamps to 5 minutes in Postgres?
postgresql date_trunc to arbitrary precision?


In mySQL, I shouldn't use semicolon when runing a source command. Why?

Semilcolon is use to signalize to MySQL that the command has ended.
But in your case it is interpreted as part of the file name, and there it is forbidden to use semicolons. see https://en.wikipedia.org/wiki/Filename


Multi-Tenant shared Users Tables: Need an advice

So this is the approach I came up with. Store All user information and details to the Oauth2 Server/Login API and use it to login. Then in each of the service tables just store user ID, role picked up from login into Oauth2 server/Login API and save them into "users" table of your a specific service. That way you can preserve service data integrity while preventing duplicates.

As to Oauth2/Login service, you can use Party-Roles model (also known as Super-Type/Sub-Type). This video well illustrates it but in case you cannot watch it, just make People table and then Customers and Staffs table relating with People table with 1-to-1 relationship with a type.

Thanks to @Sahap Asci for pointing that out in the comment.


Optimizing table for timeseries Postgres data table

If you were seriously considering switching database systems, I could tell you Microsoft SQL Server already has an out of the box feature for sparse columns that fits your use case we'll. But my recommendation would be to not look to change database systems only just to optimize data storage. PostgreSQL is a very capable database system itself.
To answer your question, NULL values only take up 1 bit of data regardless of how many columns there are, and therefore is very lightweight so it reflects your sparseness accurately.


SQL Server 2008 R2 Dirty reads - how non-atomic?

EDITED after reading the MSDN forum link from the comment, very interesting.

Regardless of isolation level, two users cannot update a single page simultaneously, nor can any user read a partially updated page.  Just imagine how SQL Server would deal with a page where the header says Col3 starts at byte 17.  But it really starts at byte 25, because that part of the row hasn't been updated yet.  There's no way a database could handle that.

But for rows larger than 8k, multiple pages are used, and that makes a half-updated column possible.  Copied from the MSDN link (in case the link breaks), start this query in one window:

if object_id('TestTable') is not null
    drop table TestTable
create table TestTable (txt nvarchar(max) not null)
go
insert into TestTable select replicate(convert(varchar(max),
    char(65+abs(checksum(newid()))%26)),100000)
go 10
update TestTable set txt=replicate(convert(varchar(max),
    char(65+abs(checksum(newid()))%26)),100000)
go 100000

This creates a table and then updates it with a string of 100.000x the same character. While the first query is running, start this query in another window:

while 1=1 begin
 if exists (select * from TestTable (nolock) where left(Txt,1) <> right(Txt,1))
    break
end

The second query stops when it reads a column that is half updated.  That is, when the first character is different from the last.  It will finish quickly, proving that it is possible to read half-updated columns.  If you remove the nolock hint, the second query will never finish.

Surprising result!  A half-updated XML column might break a (nolock) report, because the XML would be malformed.


Using max result as a value for a predicate in SQL

Yes this is possible. Your query has several syntax errors however.

a dash in an identifier is invalid. I used an underscore instead in the below example. If you did create the columns that way, you will need to quote them, e.g. "person-name"
the equality operator in SQL is =, not ==
String constants need to be put in single quotes (') not double quotes

It is possible to use the result of the max subquery as a single values because it is a single value. To be accurate it is a subquery that because of the max() returns a single column and a single row.

select person_name
from Works
where salary > (select max(salary)
                from Works
                where company_name = 'Google');

You could use an alternative way with > ALL instead of >, with a subquery that doesn't have an aggregate function and returns many values (many rows but still a single column):

select person_name
from Works
where salary > ALL (select salary
                    from Works
                    where company_name = 'Google'
                      and salary is not null );

Note the salary is not null condition in this second query. Nulls must be taken out of the comparison because each of them would evaluate the > operation to unknown rather than true or false, and as a result the whole > ALL condition result would be unknown and you would get no rows in the output.


Yes

Service_account_name = DomaninServiceAccount = Domain user

A service account is actually a domain user, just with some extra settings like "no interactive login" and "password does not expire".
Dunno why they would use "DomaninServiceAccount" too. It's just Service_account_name

Is it certain? Just have to test, but it looks usual for non-Windows applications (Atlassian does the same, and I have other Java apps that have AD authentication too)


Is it possible to Clone an Existing MYSQL Slave Database into Amazon RDS with the RDS clone being a slave to an external Master?

This is completely possible. The only type of import you can use with RDS is a logical restore, so mysqldump. You will want to use the --dump-slave=2 option :

mysqldump --dump-slave=2 (other options) > dump.sql

This will add the CHANGE MASTER statement as a SQL comment to the dump.sql file. Take the binary log file and log position from that statement and set the RDS up accordingly:

CALL mysql.rds_set_external_master (
  host_name
  , host_port
  , replication_user_name
  , replication_user_password
  , <binary log file>
  , <binary log position>
);

[src]

I would caution you that your replication events are going to be sent as plain text, since ssl_encryption is not implemented for RDS MySQL. If your existing master is not in AWS, to be secure you would need to have your RDS instance in a VPC and set up to communicate with a VPN in your existing infrastructure. Read more about this in the docs: https://aws.amazon.com/vpc/


database structure - complicated requirements

Apart from the other tables you should have (like equipment, farmers ...), you can use this approach:

equipment_price
---------------
equipment_id
equipment_name
equipment_default_price

equipment_special_price
-----------------------
equipment_id
special_price_interval_start
special_price_interval_end
equipment_special_price

Now imagine that you have a concrete mixer, with a default price of 100$ per day, but for the first 2 weeks of August the equipment has different (special) price.

Your query for finding the correct price (as of TODAY) will look like:

SELECT
   a.farmer_name,
   b.equipment_name
   CASE
      WHEN d.equipment_special_price IS NULL THEN c.equipment_default_price
      WHEN d.equipment_special_price > c.equipment_default_price THEN c.quipment_default_price
      ELSE c.equipment_default_price
   END AS equipment_price
FROM
   farmers a,
   LEFT JOIN equipments b ON b.equipment_farmer_id = a.farmer_id
   LEFT JOIN equipment_price c ON c.equipment_id = b.equipment_id
   LEFT OUTER JOIN equipment_special_price d ON d.equipment_id = b.equipment_id
WHERE
   d.equipment_special_price = (   -- Usefull if you have multiple special prices, for the same equipment, in overlapping intervals
       SELECT MAX(equipment_special_price)
       FROM equipment_special_price
       WHERE
           special_price_interval_start >= NOW()
           AND
           special_price_interval_end <= NOW()
       )


Master-master database replication on 5 servers

I'm just throwing this out there but have you thought of using a MQ for writes?

We're using RabbitMQ for writes and federating it out to multiple data centers where each one has a flexihash cluster of ~20 Redis servers. So far it's working well for us and you can increase or decrease the size of each cluster independently as long as your rebalancing them appropriately. Hope this helps.


Topics and posts represented by one table or two?

Generally...

A a rule of thumb: don't pre-optimize for performance.  I think a lot of developers assume that joins are inefficient and they don't trust DBMS to do what it's built to do.

Start with a properly normalized design.  Make sure your indexes and queries are optimized for your particular balance of reads and writes.

If and when you start to find that performance can't keep up with the best hardware you can afford, then start to think about denormalizing.

If you denormalize early you are just setting yourself up for maintenance headaches down the road.

More Specifically...

Looking at your suggested table layouts, I would suggest that you're trying to make TOPIC do too much.  Anything which could appear in POST (e.g. poster_id) almost certainly doesn't belong in TOPIC.  I suggest you adjust your thinking a little bit.  I get the impression you are thinking very much about what topics and posts are going to look like on the page.  This may be leading you to thinking of topics as a small super-set of posts, whereas they are probably rather more like subject headings.  The fact that you plan on displaying the first post under each subject heading along with the heading isn't a good reason to co-mingle posts and headings.

I think you may want to rethink some of your cumulative total columns too.  Thinks like up and down votes may need to be tracked in their own tables.  You may need to do this to keep people from up or down voting repeatedly and to allow people to rescind their votes.  Similarly, you may want to know all editors, not just the last editor.


TLS between pgbouncer and server is not enabled through the connect string, but with server_tls_sslmode, which is disabled by default.

It should be set to at least prefer, and also some of the other server_tls_* parameters might be needed to, depending on the TLS configuration at the other end.

See https://pgbouncer.github.io/config.html#tls-settings for all the details.