Compare two tables amount with respect to priortization of FundType

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

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

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

Sample data reproduced from your SQL Fiddle:

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

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

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

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

Usage:

EXECUTE [dbo].[MatchLedgerRecords];

Output:

Source code:

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

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

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

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

            cmd.ExecuteNonQuery();

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

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

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

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

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

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

            SqlDataRecord sdr = new SqlDataRecord(smd);

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

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

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

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

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

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

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

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

                                    SqlContext.Pipe.SendResultsRow(sdr);

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

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

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

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

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


It appears that *.ibd files (like the file ibdata1) by default never shrink, they can only grow.  They are created if you have
innodb_file_per_table
configuration set (which is the default on ubuntu).  If that is not set then you'll see the same behavior from the ibdata file which is basically all tables rolled into one.  Apparently if you have the "all rolled into one" option then there's no easy workaround besides dumping all tables and reloading them from disk.  But you don't so you're good there.
With the .ibd files if you want to reclaim them apparently there's an option optimize table tablename which basically locks the table and rewrites it, and reclaims all the disk space.
Another command that is mentioned (and I assume does the same thing as optimize is ALTER TABLE ... ENGINE=InnoDB;)
See also here.
Somewhat related, even with innodb_file_per_table turned on, the idbdata1 file can grow "a bit" (and never shrinks) because it still includes rollbacks for "your largest transaction to date". Go figure.


Resolving a performance issue with BETWEEN join - eager spool

Ensure you have an index on T1 like:

CREATE INDEX i ON T1 (dtstart, dtend) INCLUDE (col1);

You might reverse the key columns depending on which is more selective with your data set.

The optimizer tends to introduce an eager spool for one of the inequalities and leave the second one in a Filter. Repro:

CREATE TABLE #T1 (col1 integer, dtstart datetime, dtend datetime);
CREATE TABLE #T2 (col2 integer, col3 integer, col4 integer, col5 integer, dtcol datetime);

UPDATE STATISTICS #T1 WITH ROWCOUNT = 5000;
UPDATE STATISTICS #T2 WITH ROWCOUNT = 100000;

SELECT
    T1.col1,
    T2.col2,
    T2.col3,
    T2.col4
FROM #T1 AS T1
JOIN #T2 AS T2 ON T2.dtcol BETWEEN T1.dtstart AND T1.dtend
WHERE T2.col5 IN (1, 2)
OPTION (QUERYTRACEON 8690); -- No perf spool

With the index:

Naturally, the seek is only for the first index column. The second will be a residual predicate.

Generally, interval queries can be hard to optimize. Dejan Sarka and Itzik Ben-Gan have some good coverage on the topic:

Interval Queries in SQL Server Part 4 - Dejan
Interval Queries in SQL Server - Itzik


Compute new records based on existing ones (e.g., sum of 2 records)?

I don't like the design, but sometimes we just have to deal with the what we find even if we don't like it.  Here is a query that gets you the 'Variation' part you are looking for:

select fruit, 'Variation' as recordtype, act_qty-exp_qty as qty from
   (select fruit, qty as exp_qty from foobar where recordtype ='Expected') as ex
join
   (select fruit,qty as act_qty from foobar where recordtype='Actual') as ac
using (fruit);

You can either insert those results into the table (after clearing out the old results) or you can create a view with UNION ALL so that the Variation rows appear in the view, without ever physically existing.


How do I order the columns of a SQL statement (not the rows)?

I came up with this solution but would definitely welcome improvements and other answers.

CREATE SEQUENCE column_sort_seq
   START WITH 0
   MINVALUE 0
   MAXVALUE 9999
   INCREMENT BY 1
   NOCACHE
   CYCLE;

CREATE OR REPLACE PACKAGE column_sort_api IS
   FUNCTION sort_query_columns(
      sql_ IN VARCHAR2) RETURN VARCHAR2;
   PROCEDURE drop_all_temporary_views;
END column_sort_api;
/

CREATE OR REPLACE PACKAGE BODY column_sort_api IS
   temporary_view_prefix_ CONSTANT VARCHAR2(30) := 'COLUMN_SORT_TEMP_VIEW_';

   FUNCTION get_next_temporary_view_name_ RETURN VARCHAR2;

   FUNCTION sort_query_columns(
      sql_ IN VARCHAR2) RETURN VARCHAR2
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      temporary_view_name_ VARCHAR2(30) := get_next_temporary_view_name_;
      modified_sql_ VARCHAR2(32767);
      ddl_ VARCHAR2(32767);
      column_concatenation_ VARCHAR2(32767);
   BEGIN
      modified_sql_ := RTRIM(TRIM(sql_), ';');
      ddl_ := 'CREATE OR REPLACE VIEW ' || temporary_view_name_ || ' AS '
           || 'SELECT * FROM (' || modified_sql_ || ')';
      EXECUTE IMMEDIATE ddl_;
      SELECT LISTAGG(LOWER(column_name), ', ') WITHIN GROUP (ORDER BY column_name ASC)
        INTO column_concatenation_
        FROM user_tab_cols
       WHERE table_name = temporary_view_name_;
      ddl_ := 'DROP VIEW ' || temporary_view_name_;
      EXECUTE IMMEDIATE ddl_;
      RETURN 'SELECT ' || column_concatenation_ || ' FROM (' || modified_sql_ || ')';
      COMMIT;
   END sort_query_columns;

   FUNCTION get_next_temporary_view_name_ RETURN VARCHAR2
   IS
      view_name_index_ NUMBER;
      record_count_ NUMBER;
      view_name_ VARCHAR2(30);
      attempts_ NUMBER := 0;
   BEGIN
      LOOP
         IF attempts_ > 10000 THEN
            drop_all_temporary_views;
         END IF;
         view_name_index_ := column_sort_seq.NEXTVAL;
         view_name_ := temporary_view_prefix_ || TO_CHAR(view_name_index_, 'FM0000');
         SELECT COUNT(*)
           INTO record_count_
           FROM user_views
          WHERE view_name = view_name_;
         attempts_ := attempts_ + 1;
         EXIT WHEN record_count_ = 0;
      END LOOP;
      RETURN view_name_;
   END get_next_temporary_view_name_;

   PROCEDURE drop_all_temporary_views
   IS
   BEGIN
      FOR rec_ IN (
         SELECT view_name
           FROM user_views
          WHERE view_name LIKE temporary_view_prefix_ || '%'
      ) LOOP
         EXECUTE IMMEDIATE 'DROP VIEW ' || rec_.view_name;
      END LOOP;
   END drop_all_temporary_views;

END column_sort_api;
/

It creates a view, then examines the metadata of that view to build a new query. To support a limited amount of concurrency, it works with a set of 10,000 possible view names. In case the system crashes, it checks to make sure the view doesn't exist before using that name. To clear any possible lint, there's a feature to remove all the views.

Here it is in action:

https://livesql.oracle.com/apex/livesql/s/hvqxaqn9pwi8yk276bg4zt5gn

SELECT column_sort_api.sort_query_columns('SELECT * FROM hr.employees WHERE rownum <= 5;')
  FROM DUAL;

Output:

SELECT commission_pct, department_id, email, employee_id, first_name, hire_date, job_id, last_name, manager_id, phone_number, salary FROM (SELECT * FROM hr.employees WHERE rownum <= 5)

Using that output is a matter of copying the result and pasting it back into the editor.

If you can improve this answer, I would like to know if it's possible to bypass the final copy-and-paste step and produce the data via a pipelined function.


Keep a history of transactions for easy rollback

SQL Server with database in FULL recovery model permits you to do a point-in-time restore.

There is no need in "Taking a checkpoint after every transaction" because of write ahead logging.

To know more about it you can read the followin article: SQL Server point in time restore

  Overview

  The RESTORE ... WITH STOPAT option allows you to restore your
  database to a point in time.  This gives you the ability to restore a
  database prior to an event that occurred that was detrimental to your
  database.  In order for this option to work, the database needs to be
  either in the FULL or Bulk-Logged recovery model and you need to be
  doing transaction log backups.

And here is the official documentation:
Restore a SQL Server Database to a Point in Time (Full Recovery Model)


DB_RECOVERY_FILE_DEST_SIZE automatically decrease size after shutdown

This is normal. If you set a parameter without specifying sid, then it will create/modify the entry in spfile with sid=*. But if you have another value set with the actual sid (instance name), that takes precedence over *, and the value remains unchanged.

Demonstration:

SQL> show parameter db_reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /fra
db_recovery_file_dest_size           big integer 20G

SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        db_recovery_file_dest_size    big integer 20G

SQL> alter system set db_recovery_file_dest_size =10G sid='MIN19' scope=spfile;

System altered.

SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
MIN19    db_recovery_file_dest_size    big integer 10G
*        db_recovery_file_dest_size    big integer 20G

My instance is called MIN19, so even though 20G is set for *, it will start with 10G:

SQL> startup force nomount
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             339738624 bytes
Database Buffers          180355072 bytes
Redo Buffers                7876608 bytes

SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 10G
SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
MIN19    db_recovery_file_dest_size    big integer 10G
*        db_recovery_file_dest_size    big integer 20G

I may change it to whatever I want, it will not have effect:

SQL> alter system set db_recovery_file_dest_size = 30G scope=spfile;

System altered.

SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
MIN19    db_recovery_file_dest_size    big integer 10G
*        db_recovery_file_dest_size    big integer 30G
SQL> startup force nomount
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             339738624 bytes
Database Buffers          180355072 bytes
Redo Buffers                7876608 bytes
SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 10G

Make sure to clean up overlapping parameters and use only 1 of them to avoid confusion. Example:

SQL> alter system reset db_recovery_file_dest_size sid = 'MIN19';

System altered.

SQL> show spparameter db_recovery_file_dest_size

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ----------------------------
*        db_recovery_file_dest_size    big integer 30G
SQL> startup force nomount
ORACLE instance started.

Total System Global Area  536868920 bytes
Fixed Size                  8898616 bytes
Variable Size             339738624 bytes
Database Buffers          180355072 bytes
Redo Buffers                7876608 bytes
SQL> show parameter db_recovery_file_dest_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size           big integer 30G
SQL>


How to debug huge binary log file increases in MySQL replication setup?

I think your replication uses ROW as binlog_format. To verify, please run this on the master:

SHOW VARIABLES LIKE 'binlog_format'

The result would be one of the following values: statement, row, or mixed. More info.

In the case of ROW format, every changed row will add info to the bin log. If there are statements that affect a table of 1000000 rows, all these changes will be written to the binlog.

For example, suppose you have a huge table, with 1M rows, and you run a statement like this:

UPDATE big_table SET hashed_email=md5(email)

This statement will add 1M entries to the binlog when it is row based, while it adds only one entry to the binlog when it is statement based.

Suggested solution:

Enable the general query log around the time when this this issue usually happens, and analyze all the statements to see if there are many that does update operation on huge number of rows.


Installation of SQL Server 2014 and SQL Server 2012 on the same system

Yes you can! Your first SQL Sever (2012) will most likely be the default instance, any subsequent installation of SQL Server (any version) must be a named instance. As long as your machine has the resources, you can install multiple instances of SQL Server.


java.sql.SQLException: Login failed for user 'sa'

Make sure your jdts jar version is 1.2x. If it is not ,download correct version. And the second option is using JDBC jar for connecting MSSQL database.


Change default collation when provisioning a SQL Server VM in the Azure portal

If you are going to utilize the images from Azure marketplace for building your SQL Server instances, I'm not aware of any options to change the server collation. Since the image has SQL Server installed already your only option will probably be to just follow the process to change the default server collation after the VM is provisioned. Which requires rebuilding the system databases. You could add this into your build so it executes the needed commands, but you would have to have the installation media available for that version of SQL Server.


It doesn't look like the one-click installer does this directly (see http://www.enterprisedb.com/resources-community/pginst-guide#noninteractive) but if you are calling this from another installer, you could start up the db afterwards and call createdb on it.

I assume you are trying to install from an installer for another program.  if so that's how I would do it.


The MySQL Documentation refers to the SUM() function as an aggregate or group function. In light of this, I would call it an aggregated column since the MySQL Documentation starts with:

  This section describes group (aggregate) functions that operate on sets of values. Unless otherwise stated, group functions ignore NULL values.

As for the CASE statement, the book

page 82 paragraph 1 and pages 88-92 refers to CASE as a conditional control statement. Personally, I would call it a conditional control column or, simply, a conditional column.


How can I restore a backup without overwriting ALL of the tables?

Restore the backup to a database with a new name.  You get to keep the old and new and can move tables between them with a little work


This is the minimum:

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl

Or use oraenv. Add the below in /etc/oratab if it is not already there:

orcl:/u01/app/oracle/product/12.1.0/db_1:N

Then interactively:

. oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base has been set to /u01/app/oracle

Or from a script:

export PATH=/usr/local/bin:$PATH # default path for oraenv, in case your PATH does not include it
export ORAENV_ASK=NO
export ORACLE_SID=orcl
. oraenv

It is not mandatory to set these in the profile, users can set themselves it when needed.


How can a key lookup with zero executions and zero actual rows take time?

The percentages on each operator that are shown in an "actual" plan are still estimates. The additions to an "actual" plan over "estimated" are the actual row counts and operator executes.

In the example you've given the key lookup was estimated to be executed 3341 times, which is why it has a high estimated percentage cost.

One of the other operators is really consuming the time. Get a (free) copy of Plan Explorer from Sentry One, it'll make it easier to identify where.

If nothing stands out, start a new question and post the execution plan xml.


MySQLdump leads to exceeding max connections. Skip-Quick as a solution?

mysqldump takes only 1 connection.
There is 1 extra connection for root.  (Note the 151 vs 152.)  I hope you are not using root for your app; change that if you are.  Then allow mysqldump to be the only root process running when it is doing a dump.
Limite the number of connections requested -- by adjusting the web server (or whatever is doing connections).  Web servers tend to allow far more children than is efficient.
Disconnect (in the app) from MySQL when finished processing.
Lower wait_timeout -- this will lead to disconnecting idle threads or threads that are held unnecessarily long by a "connection pool".
Running mysqldump takes resources away from other queries; be sure to run it at off times.
Use the slowlog to find out what queries are the most impacted by the dump; then let's discuss improving them.
If you have an LVM snapshot, why bother with mysqldump?  LVM is so much faster and less invasive.


Creating Database Tables for Reviews functionality that incorporates Tags/Categories

The tables can be queried in a few different ways depending on what you want to do. If you wish to (Example 2) extract all the Reviews with a particular Category you can create a procedure like this:

SELECT review_id, professional_rating, efficiency_rating, referral_rating...
FROM Review r
INNER JOIN Review_Category rc ON r.review_id = rc.review_id
INNER JOIN Category c ON rc.category_id = c.category_id
WHERE c.review_category_name = 'foo'

For Example 1, if you want to insert a new review with 2 categories, you generally do this in 2 steps, handled by the application calling the database procedures. First insert the review with something like:

CREATE PROCEDURE Review_Insert (<review table values>, OUT NewReviewID bigint)
BEGIN
    INSERT INTO Review (<column list>)
    VALUES (<value list>)

    SELECT NewReviewID = LAST_INSERT_ID()
END

You should then be storing the output of the NewReviewID in your application, which you then pass into the next procedure for associating the Review with the Category, something like:

CREATE PROCEDURE Review_Category_Insert (ReviewID BIGINT, CategoryID BIGINT)
BEGIN
    INSERT INTO Review_Category (review_id, category_id)
    VALUES (ReviewID, CategoryID)
END

This last one needs to be called for each Category that is associated with a Review.

You can do only one trip to the database too, if that is what you need, however it appears you could have 1 category associated with a review or 100 in one trip, which makes defining the parameters in your procedure difficult (not impossible, just difficult).


Mysql performance on varchar with ranges

Assumptions:

the query can only return 1 row (or none) and never 2 or more, i.e. the (number_start, number_end) ranges are not overlapping for the same numbertype_id.
the number_start and number_end values  are all left-padded with 0s to the same length.

Under the above assumptions, we can use a trick to use an index to fetch exactly 1 row (if there is one) that matches the criteria (not all, checking only the low end of the range) - and don't search for more rows. The check on the high end is done before the row is returned to avoid any false positive result:

SELECT n.*
FROM
    ( SELECT id, number_end
      FROM numberdata
      WHERE numbertype_id = 7
        AND number_start <= '00112233'
      ORDER BY number_start DESC
      LIMIT 1
    ) AS ni
  JOIN
    numberdata AS n
  ON  ni.number_end >= '00112233'
  AND ni.id = n.id ;

The query will only need an index on (numbertype_id, number_start) - or keep the one you have on (numbertype_id, number_start, number_end).


Standard Edition of SQL Server and cores

I am thinking we have two choices:

  Update VM and remove the 3 unused sockets, so we have 4 sockets/8
  cores.

This is probably a better option. As Kin suggested in the comment on your question, NUMA imbalance would be a factor by trying to balance CPU affinity across physical cores.

  Update VM and remove the 3 unused sockets, update number of
  cores per socket from 2 to 4, so we have 16 processors. I need to
  check if this is even possible.

Even if it is, go with fewer cores. On a virtual environment, this is preferred, especially with Standard Edition.

Final note: if you do share the host with other VMs, please be wary of something called CPU lag. This can happen when VMs with different numbers of vCPUs are running on the same host.


SQL Server user cannot select from a table it just created?

Granting schema modification is not, and should not be, the same as granting DML rights.  Grant the user the exact rights that user needs, including 'SELECT', etc, as necessary.

See https://msdn.microsoft.com/en-us/library/ms178569.aspx for details.


Warning Console code page (437) differs from Windows code page (1252). 8-bit characters might not work correctly in PostgreSQL

When you use cmd.exe /c chcp 1252 in the batch file, you start a new command line, change the codepage for that and then that command line is immediately terminated, but the one that runs psql has not bee changed.

Just put:

chcp 1252

(without calling cmd.exe) into the batch file.


Should frequently accessed tables containing large blobs with one-to-one relationships be normalized and columns split into two tables?

Using InnoDB and the Barracuda file format permits you to deploy the Dynamic row_format. This configuration will aid you due to the way it will store the BLOBs off page. Queries not using the columns with the BLOBs shouldn't need to touch their pages;

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-row-format-dynamic.html


How does lock works when delete rows through secondary index in InnoDB?

(Semi-related comments...)

Assuming this is a standard many-to-many mapping table, get rid of id entirely.  Then make one of the other KEYs (either one) the PRIMARY KEY.  More tips here

If the DELETE is typical, then I would make the KEY starting with followable_id the PRIMARY KEY.

This will speed up the inserts and the deletes, thereby tending to avoid deadlocks and "lock waits".


Mysql date_add returns wrong row in python, right row in phpmyadmin

First, a crucial mistake of the queries:

LIMIT without ORDER BY does not guarantee consistent results.
If you get correct results using LIMIT without ORDER BY, it's only by accident.

The results you got from running the queries in PHPMyAdmin was only accidental. There is no guarantee that you'll get the same results if you run the same queries tomorrow.

Adding ORDER BY (either ASC or DESC) will solve the issue.

There is also another problem with the 1st query. Adding 25 hours instead of 24 might be a nice hack - if it worked. It might work with the current data but it will stop, if there are ever rows that are not limited to full hours or there are no rows for the first hour of a month. You need to use 24 hours (or 1 day) and timestamp >= ... and ORDER BY .. ASC to get the first row from a period.

Queries rewritten (I used date arithmetic instead of your DATE_ADD(), this is entirely personal preference, the DATE_ADD() is fine in your queries):

-- First row in previous month (or later)
SELECT *
FROM mcs
WHERE time_stamp >= LAST_DAY(NOW()) + INTERVAL 1 DAY + INTERVAL -2 MONTH
  AND wot_id = 1010965979
ORDER BY time_stamp ASC
LIMIT 1 ;

-- Last row in previous month (or earlier)
SELECT *
FROM mcs
WHERE time_stamp <  LAST_DAY(NOW()) + INTERVAL 1 DAY + INTERVAL -1 MONTH
  AND wot_id = 1010965979
ORDER BY time_stamp DESC
LIMIT 1 ;


Facing Error in "Execute Sql task" component (The type of the value (DBNull) being assigned to variable)

Problem Solved .
See, I wanted to retrieve only one column on my table so I set the result set of the general tab to "single row"
as you can see here :

as you can see here

And in result set tab , I configured the resultName to my column name


A direct replacement of the code to PL/pgSQL would be something like this.
The following is (obviously not) tested and may contain typos, so you need to work out the details from here.  But it should get you started.
DECLARE
  l_oid_array int[];
  l_subscription_id_array int[];
  l_config_oid_array int[];

  l_rating_scheme_oid int;
  l_cnt bigint;
BEGIN
  SELECT array_agg(oid order by oid),
         array_agg(subscription_id order by oid),
         array_agg(configuration_oid order by oid)
     into l_oid_array, l_subscription_id_array, l_config_oid_array
  FROM scope
  WHERE subscription_id <> 1
    and subclass_type = 'W'
    and configuration_oid is not null;

  FOR i IN 1 .. cardinality(l_oid_array) LOOP

    select count(*) into l_cnt
    from workspace_config
    where workspace_oid = workspace_oid = l_oid_array[i]
      and OBJECTIVE_STATE_SCHEME_OID is null;

    IF l_cnt = 1 THEN
    THEN

      l_rating_scheme_oid := nextval('oid_seq');

      INSERT INTO RATING_SCHEME (OID, SUBSCRIPTION_ID, VERSION, NAME, CREATION_DATE, WORKSPACE_OID, UUID)
      VALUES (rating_scheme_oid, l_subscription_id_array[i], 1, null, current_date, l_oid_array[i], uuid_generate_v4());

      UPDATE WORKSPACE_CONFIG set OBJECTIVE_STATE_SCHEME_OID = rating_scheme_oid where oid = l_config_oid_array[i];

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Defined', 0, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Committed', 1, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'In-Progress', 2, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Measuring', 3, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Achieved', 4, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Closed', 5, uuid_generate_v4());
    END IF;

  END LOOP;

END;

To get the uuid_generate_v4() function you need to install the uuid-ossp extension
Doing things in loops like that is usually the least efficient way (in Oracle and Postgres). More often than not, a simple insert into select ... is a lot more efficient


The next to last major step was to find out that it is, unfortunately for MySQL users, only MySQL that joins slowly, so these write intensive tables were all given unique integer identifiers to join upon.  That took off 1/3 of the remaining average time consumption reduction.

The last step was taking CTE optimization fencing into account which took off the last 2/3 of reduction.

In the case of the above set of queries, the average time consumed is now between 1 and 2ms on an i7 laptop tuned for SSDs even though I'm using the included hard drive.  On a server with a single SSD, the time consumed averages less than 1ms.

To get the last performance boost, almost all queries were condensed, so for example if a table needed to be updated, it's best to do it in one query no matter how strange or performance reducing it might appear.

Reading to assemble relevant primary key values required more testing.  If it was a combination of RETURNINGs, it was best to allow each sub-statement to recalculate it rather than depend upon another sub-statement to do the calculation.  If the read had to go to disk, even if it was small, it was best to do that once and reference it across the multiple writing queries.

Copying was another grey area.  If a table required inserts, updates, and deletes based upon data from another table, it was slower to pre-select the data.  It was faster to simply reference the primary key data.

In general, aside from reads that go to disk to assemble primary key values to reference, it is best to compress a CTE as much as possible though it may appear strange to keep the chains as short and narrow as possible.

Multi-prepared statement transactions that previously consumed 10s of ms in my application now consume max 5ms.

This approach should probably be limited to small amounts of redundant data.


What db permissions are needed for pgAgent user?

Had this same issue!

Looking in the postgres log it appeared pgagent was failing to invoke one of the sequences in order to add entries into the job log.  Once I did the below (in postgres 9.3) and restarted my pgagents, all worked well:

GRANT ALL ON ALL SEQUENCES IN SCHEMA pgagent TO pgagent_user;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA pgagent TO pgagent_user;


Insert data in relation table and into data table with less query cost

Use the OUTPUT clause of the INSERT statement to capture the identity values created in data.  Then use that to write to relation.

create table #data ( id int  primary key
                  , value int not null);

if not exists (select id from data where value = 1337)
    insert into data (value)
    OUTPUT INSERTED.id, INSERTED.value
        INTO #data
    values (1337);

select * from #data;

insert into relation (someid, dataid)
select
    42, id
from #data;

select * from relation;


The error is saying that ...it is not a valid identifier.
When you pass EXEC @SQL , it is expecting that @sql to represent the name of a stored procedure (or scalar function). Hence the error message: ...it is not a valid identifier.

If you want to execute a text, to pass dynamically , you should encapsulate that text inside ( ), like EXEC (@SQL).


dropping a table does not delete the directories on the disk

Did you clear the snapshot of all clusters?

Clear it with

nodetool -h <ip address> -p <port> clearsnapshot


Trying to make sense of the InnoDB log file stats

37000 tables, itself, is really bad.  Each table is implemented as 1 to 3 files on disk.  This leads to a burden on the OS.  Also, there are various caches (controlled by multiple settings, such as table_open_cache) that could overflow, and probably run inefficiently if made large.

0.56 log i/o's/second sounds trivial.  A spinning drive can (Rule of Thumb) do 10 IOPs.  So the log I/O is only 5% of the available bandwidth.  (Better on SSDs.)

It is very rare that a performance problem is traced to a poorly sized log file (either too large or too small).  (Or maybe I am too dumb in this area?)

Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size
I recommend 0.2 to 2.5.  It means "Minutes between InnoDB log rotations".
Innodb_log_waits / Innodb_log_writes
I recommend 0 to 0.1%.  If out of range, Increase innodb_log_buffer_size.
innodb_log_files_in_group should be 2.  Larger values are possible but they have been tentatively linked to bad performance.

If you would like further tuning and performance critique, see this .

Analysis of VARIABLES and GLOBAL STATUS

The Important Issues

Apropros the original question:  ( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 144 minutes.  This is reasonably close to the optimal of 1 hour so that I suggest that your log settings are fine.
(One must stray quite far from the optimal to get into trouble.)

innodb_buffer_pool_size = 87G is probably OK for 126G of RAM, despite what the analysis below says.

innodb_log_buffer_size -- increase from 8M to, say, 100M.
This may be one of the more important recommendations, but I can't say how important.

Your code seems to be deleting almost as fast as it inserts; what is going on?
In particular, using MySQL as a "queue" has various problems.

Innodb_row_lock_time_avg = 23s -- This is extremely high, and it worries me, but I don't know what to recommend.

Max_used_connections = 158 while max_connections = 1000.
I recommend lowering `max_connections to 500 as a way of preventing a burst of connections from blowing out RAM.

What framework are you using?  It is doing some things, such as SHOW CREATE TABLE and SHOW VARIABLES, that unnecessarily burden the processing.

Suggest increasing bulk_insert_buffer_size from 8M to 32M since you have so much RAM.

Here comes another "more important" recommendation:
161 tmp tables created per second!  Let's look at some of the slower queries.
By adding composite indexes and/or reformulating the queries, performance
may be significantly improved.
See my comments on using the Slowlog to identify such queries in http://mysql.rjweb.org/doc.php/mysql_analysis .
(If you like, start a new Q&A to discuss one of the queries.)

Currently max_tmp_tables = 32 and tmp_table_size = 1G.  It might be better to have max_tmp_tables = 100 and tmp_table_size = 100M.  (Even better would be to work on avoiding some of the tmp tables.)

Of those tmp tables, 1.1/sec are on disk.  This is in spite of the huge values for tmp_table_size and max_heap_table_size of 1GB each.  (Becuase you have so much RAM, I do not have a good argument for lowering those values.)

8.8% of SELECTs are "full joins".  And 146 full table scans/sec.  This may or may not be significant.  I'll wait until such shows up in the slowlog.  When a table has just a few rows; the alarming metrics don't matter.  When a table has lots of rows, they beg for better indexing, etc.

Do you use Cursors in your Stored Routines?

(Some STATUS values confirm that table_open_cache = 20000 is probably OK.)

Be aware that init_connect = SET NAMES utf8 is ignore when connecting as root (or other SUPER user).

innodb_large_prefix is removed (as obsolete) in 5.7.7.  You should remove it when you upgrade.

Why are you running with tx_isolation = READ-COMMITTED?

Details and other observations

mysqld has been running 15 days.  Percona 5.6.32.  126GB of RAM.

( table_open_cache ) = 20,000 -- Number of table descriptors to cache
-- Several hundred is usually good.  (We have alredy discussed this.)

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 87040M / 16 = 5440MB -- Size of each buffer_pool instance.
-- An instance should be at least 1GB. In very large RAM, have 16 instances.

( innodb_max_dirty_pages_pct ) = 60 -- When buffer_pool starts flushing to disk
-- Are you experimenting?

( Innodb_os_log_written ) = 324,963,533,824 / 1314928 = 247134 /sec -- This is an indicator of how busy InnoDB is.
-- Very busy InnoDB.

( Innodb_log_waits / Innodb_log_writes ) = 15,857 / 216746 = 7.3% -- Frequency of needing to wait to write to log
-- Increase innodb_log_buffer_size.

( Innodb_rows_deleted / Innodb_rows_inserted ) = 116,716,394 / 143158563 = 0.815 -- Churn
-- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( Innodb_row_lock_time_avg ) = 23,465 -- Avg time to lock a row (millisec)

( max_tmp_tables * tmp_table_size / _ram ) = 32 * 1024M / 129024M = 25.4% -- Pct of RAM potentially consumed by tmp tables
-- Swapping is bad; decrease max_tmp_tables and/or tmp_table_size. Or lower innodb_buffer_pool_size.

( (Com_show_create_table + Com_show_fields) / Questions ) = (468502 + 509251) / 40574407 = 2.4% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.

( bulk_insert_buffer_size / _ram ) = 8M / 129024M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA
-- Too big could threaten RAM size. Too small could hinder such operations.

( (Queries-Questions)/Queries ) = (348646859-40574407)/348646859 = 88.4% -- Fraction of queries that are inside Stored Routines.
-- (Not bad if high; but it impacts the validity of some other conclusions.)

( Created_tmp_tables ) = 212,256,483 / 1314928 = 161 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 1,461,983 / 1314928 = 1.1 /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.

( tmp_table_size ) = 1024M -- 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.

( Select_full_join ) = 1,396,307 / 1314928 = 1.1 /sec -- joins without index
-- Add suitable index(es) to tables used in JOINs.

( Select_full_join / Com_select ) = 1,396,307 / 15893105 = 8.8% -- % of selects that are indexless join
-- Add suitable index(es) to tables used in JOINs.

( Select_scan ) = 192,567,904 / 1314928 = 146 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 192,567,904 / 15893105 = 1211.6% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 374362 - 360454 ) / ( 374362 + 360454 ) = 1.9% -- Are you closing your prepared statements?
-- Add Closes.

( Connections ) = 7,761,574 / 1314928 = 5.9 /sec -- Connections
-- Increase wait_timeout; use pooling?

Extremes The following are noted, mostly without comment:

Abnormally small:

Com_commit = 0.95 /HR
Com_kill = 0.0027 /HR
Com_show_collations = 0.0082 /HR
Com_show_events = 0.0082 /HR
Com_show_processlist = 0.025 /HR
Innodb_log_writes / Innodb_log_write_requests = 0.02%
Key_blocks_unused = 3,946
Threads_connected = 0.0055 /HR
innodb_lru_scan_depth / innodb_io_capacity = 0.0341
key_buffer_size / _ram = 0.01%
query_cache_limit = 131,072

Abnormally large:

Com_create_procedure = 0.84 /HR
Com_drop_procedure = 0.84 /HR
Com_show_create_table = 0.36 /sec
Com_show_table_status = 0.35 /sec
Com_show_tables = 0.38 /sec
Com_unlock_tables = 0.35 /sec
Handler_delete = 88 /sec
Handler_read_first = 156 /sec
Innodb_buffer_pool_pages_data = 5.12e+6
Innodb_buffer_pool_pages_misc = 438,469
Innodb_buffer_pool_pages_total = 5.57e+6
Innodb_dblwr_pages_written / Innodb_dblwr_writes = 93.1
Innodb_ibuf_merged_delete_marks = 243 /sec
Innodb_ibuf_merges = 2.5 /sec
Innodb_log_write_requests = 673 /sec
Innodb_rows_deleted = 89 /sec
Open_table_definitions = 20,252
Open_tables = 20,000
binlog_cache_size = 1.05e+6
group_concat_max_len = 3.36e+7
innodb_io_capacity = 30,000
innodb_io_capacity_max = 60,000
innodb_open_files = 20,000
max_heap_table_size = 1024MB
min(max_heap_table_size, tmp_table_size) = 1024MB
performance_schema_max_cond_instances = 44,100
performance_schema_max_statement_classes = 181
table_definition_cache = 20,000

Abnormal strings:

Compression = ON
block_encryption_mode = aes-256-cbc
init_connect = SET NAMES utf8
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_force_load_corrupted = OFF
innodb_large_prefix = ON
innodb_numa_interleave = ON
locked_in_memory = ON
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
secure_file_priv = /tmp/
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
tx_isolation = READ-COMMITTED


Can I select keys stored in a postgres jsonb column as values dynamically

Like @a_horse told you, SQL is strictly typed and demands to know the resulting row type at call time at the latest.
If key names follow a predictable pattern, and there can only be a trivial number, you might "overprovision" in your views:
SELECT dynamic_data->'dynamicField1' AS dynamicField1
     , dynamic_data->'dynamicField2' AS dynamicField2
     , dynamic_data->'dynamicField3' AS dynamicField3  -- overprovisioned columns
     , ...
     , dynamic_data->'dynamicField3' AS dynamicField9
FROM  (SELECT json '{"dynamicField1": "value1"
                   , "dynamicField2": "value2"}') tbl(dynamic_data);

Missing keys get a NULL value. dynamicField3 - dynamicField9 are NULL in the example. The workaround has obvious weaknesses.


What does autogrow run in the background (Is it a DDL statement?)

As documented by By Parikshit Savjani in
Insert bulk failed due to a schema change of the target table:

This error says that there have been changes in the schema that require the re-compilation of the plan and Insert bulk cannot support recompiles.
Any Operation on the Target tables which would require the recompilation of the query will cause this error messages.
In a normal scenario, the Schema Change could be addressed by a re-compile, however it turns out that for BCP re-compiles are not possible because at compile time, we are reading metadata from the input stream and we set up our statement based on that, and then basically discard that metadata. If we recompile, that metadata is completely lost, and compilation is no longer possible.

There is no reason that auto growth of a file would cause a plan recompile.
I would suspect auto statistics update as the most likely option but the link does mention some other possibilities:

Parallel Bulk Inserts happening on the same table, and foreign keys are enabled on the table.

Parallel Online rebuild index operation running.

It also mentions that bulk inserting with the option to ignore constraints will bump the internal metadata version, which can lead to this error. The workaround is to drop the constraints before the bulk operation, then recreate them afterward.
You could turn off autostats for the table using norecompute for the duration of the operation and then update them afterwards.


How to transfer 70GB MySQL with no downtime?

You are going to have to bite the bullet somewhere in this process.

You should determine which databases need to be dumped first (smallest to largest):

SELECT db FROM
(
    SELECT table_schema db,SUM(data_length) DataSize
    FROM information_schema.tables WHERE table_schema NOT IN
    ('information_schema','performance_schema','mysql')
    GROUP BY table_schema
) A ORDER BY DataSize;

Take this list of databases and numerically generate conversion scripts

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DBLIST=`/tmp/ListOfDatabasesOrderedBySize`
SQL="SELECT db FROM (SELECT table_schema db,SUM(data_length) DataSize"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
SQL="${SQL} AND engine='MyISAM'"
SQL="${SQL} GROUP BY table_schema) A ORDER BY DataSize"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLIST}
DBNUM=1000000
SQL="SELECT CONCAT('ALTER TABLE ',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema=DATABASE()"
SQL="${SQL} AND engine='MyISAM'"
SQL="${SQL} ORDER BY data_length+index_length"
for DB in `cat ${DBLIST}`
do
    (( DBNUM++ ))
    CONVSCRIPT=DB${DBNUM}_${DB}.sql
    mysql ${MYSQL_CONN} -ANe"${SQL}" > ${CONVSCRIPT}
done

You can now run each conversion script by name in alphanumeric order.

As you get to the bigger databases, you should run them during off hours.

Once everything is converted to InnoDB, enable binary logging on the current server.

Next, mysqldump all of it as a point-in-time dump and gzip it

DUMP_OPTIONS="--routines --triggers --single-transaction --master-data=1"
mysqldump ${MYSQL_CONN} ${DUMP_OPTIONS} | gzip > MySQLData.sql.gz

Transport MySQLData.sql.gz to the new machine and setup replication.

I'll leave the rest to your imagination


How can I instantly update a table in a database with the columns values from another database?

You can probably use a trigger to accomplish your goal.

Consider the following simplified example:

CREATE TABLE t1
(
    c1 int
    , c2 int
);

CREATE TABLE t2
(
    c1 int
    , c2 int
);

CREATE TRIGGER t1_trigger AFTER INSERT ON t1
FOR EACH ROW
BEGIN
    INSERT INTO t2 (c1, c2)
    VALUES (New.c1, New.c2);
END;

INSERT INTO t1 (c1, c2)
VALUES(1, 2);

Check the results:

SELECT *
FROM t2;

╔═════╦════╗
║ c1  ║ c2 ║
╠═════╬════╣
║  1  ║  2 ║
╚═════╩════╝

I've created a simple fiddle that shows the working code, and allows you to play with it.

The above creates two tables, the first with a trigger that copies rows inserted into t1 into t2.  In the above example, both tables are in the same database... since you are attempting to copy from one database to another, the following syntax might work in the trigger if both databases are on the same MySQL Server:

CREATE TRIGGER t1_trigger AFTER INSERT ON t1
FOR EACH ROW
BEGIN
    INSERT INTO other_database.t2 (c1, c2)
    VALUES (New.c1, New.c2);
END;


Creating Multiple Tables with WITH statements

You try to use table1 and table2 before their creation... reorder tables in WITH clause.

WITH table1(c1, loc1) AS (SELECT character1, location
                          FROM lotrfirstencounters),
     table2(c2, loc2) AS (SELECT character2, location
                          FROM lotrfirstencounters),
     table3 AS (SELECT c1 as c3
                FROM table1
                union all
                SELECT c2
                FROM table2)

SELECT DISTINCT *
FROM table3
ORDER BY c3;


Postgres - Return default value if regex match fails

While your answer solves the problem with regexp_matches(), the much better solution is to avoid the problem with the more appropriate function substring() to begin with:

WITH test_data(txt) AS (
   VALUES
      (text 'abc 123')
    , ('abc 456')
    , ('blah')
   )
SELECT txt, substring(txt FROM '\d+') AS first_num
FROM   test_data;

Does exactly what you need: returns the first match in the string or NULL for no match.

substring(string FROM pattern) is standard SQL syntax.
Don't confuse it with substring(string [FROM int] [FOR int]) or substring(string FROM pattern FOR escape) doing something else.

You can also use the shorter, equivalent, currently undocumented Postgres implementation substring(text, text) (with comma (,) instead of FROM).

Use regexp_matches() to return all matches as set. (Note that a single regexp can result in multiple matches, hence a set of text arrays). You still only get the first row unless you add the flag 'g' to get all rows (additional matches - if any - for the same regexp). Related:

Reverse Byte-Order of a postgres bytea field

To avoid complications move set-returning functions to the FROM list in versions before Postgres 10:

What is the expected behaviour for multiple set-returning functions in select clause?

Or use regexp_match() in Postgres 10 or later to only return the first row. This would also not exhibit the original problem:

WITH test_data(txt) AS (
   VALUES
      (text 'abc 123')
    , ('abc 456')
    , ('blah')
   )
SELECT txt, (regexp_match(txt, '\d+'))[1] AS first_num
from   test_data;

But just use substring() for your purpose.

db<>fiddle here


Oracle 11gR2, duplicating and refreshing cloned database

It will transfer all datafiles again.

The previous duplicate already changed all your datafiles. You have a new database incarnation, with a new name, a new dbid, which was written in the datafile headers.

RMAN duplicate skips existing datafiles in case of re-running a failed duplicate so it does not perform the same work again, but that is a different use case.


Getting around Primary Key requiring non-nullable columns

There is no physical difference between Primary Key and a Unique Clustered Index.  So unless you are using some tool that requires a Primary Key, you can do

CREATE TABLE Test2
(
    TestId1 INT NOT NULL,
    TestId2 INT NULL,
    UNIQUE CLUSTERED (TestId1, TestId2),
    FOREIGN KEY (TestId1, TestId2) REFERENCES Test (Id1, Id2)
)


Query plan cache bloated by ad-hoc queries, even with "Optimize for Ad-hoc Workloads"

According to this, the second run of an ad hoc batch removes the stub (which was used only once) and creates and caches the plan (using it for the first time).

I also haven't seen many references to refcounts other than it being a count of references by cache objects. Adhoc Compiled Plan objects can still have a refcount of 1, so it's not exclusively caused by the persistence of the plan.


"Connecting to Integration Service failed..." SQL Server 2016

Solved.

I was using SSMS 17.1 against SQL Server 2016.

SSMS 17 works great w SQL Server 2016.... except it cannot connect to SQL 2016 SSIS. To connect to SQL 2016 SSIS, you must use SSMS 16.x

Installed SSMS 2016, (v16.x), and it connects fine to Integration Services.


Does a Key-Value type table still needs a auto increment key?

Always start by identifying your natural keys, in this case key. If these are too complex (too many columns) or not stable enough (changes to often), consider adding a surrogate key (such as the ID column you mention). The criteria I use for keys are:

- unique
- stable
- irreducible
- complexity
- familiarity

In many situations there will be a conflict between these criteria, and one have to compromise. There is nothing wrong with adding a surrogate key under certain circumstances, but sadly many people design by auto-pilot and blindly add surrogate keys no matter what.


Storing floating point values for various units of measurement (kg, ml, mm, pl)

If you need precise storage for numbers with yet unknown precision, the data type numeric is the way to go. Like @dezso already commented, it stores what you pass exactly without rounding, up to an insane amount of digits. Per documentation:

  up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

Be aware, however, that even numeric cannot store the result of calculations like 1::numeric/3 exactly, since that is impossible on principal. Postgres seems to truncate the result at a scale of 20 unless instructed otherwise, while JDBC only displays a scale of 12:
SQL Fiddle.

You pay a price for extreme exactness. Per documentation:

  However, arithmetic on numeric values is very slow compared to the
  integer types, or to the floating-point types described in the next section.

Won't matter much as long as you don't do heavy computations with your numbers.

As for units, I would not use multiple competing units for the same unit of measurement. Go with just one. So just m for meter, not mm or km etc.

I would stick to SI units if that's at all practical for your use case.

You already mentioned the alternative for better performance: combine integer (or bigint?) numbers with the smallest units of measure.


You could use SQL Server's "processor affinity" to limit processing to a single NUMA node.  See the <process affinity> section of the ALTER SERVER CONFIGURATION command on this MSDN page for more details.

By default, SQL Server attempts to keep query processing contained inside a given NUMA node if the query goes parallel where MAXDOP <= number of cores per NUMA node.  The indication here is to configure MAXDOP to a reasonable value; certainly not more than the number of cores per NUMA node.  There are a load of resources to help with calculating a reasonable MAXDOP; including the answers on my question.


Disadvantage of Changing database schema - Insert or delete new columns/tables in middle

The foundation of your system is your database.  If you change your database schema then you also have to change your code.  That means that keeping a stable data model helps to reduce the amount of rework you need to do in your code.

Having said that, in the real world requirements change.  It's a good idea to invest some time up front to get your data model as good as it can be.  After that, the real world will intrude and you'll have to modify your database schema.

With careful planning, you can save yourself a lot of time reworking your code when easily anticipated changes happen.

In terms of Null values, there is nothing wrong with them in and of themselves.  However, if you find yourself with a table that has a lot of Null values, it could be a sign that your design is not properly normalized.  In a transactional processing system like an e-commerce site, you should start with a database design in third normal form (3NF) and only denormalize with great care and deliberation, and only when necessary.  This will help to keep your data quality from degrading.

EAV should be avoided as a general rule, although there are situations where it is a sane choice.  I've argued here that online product catalogs is one of those situations.


Explaining the problem with a column of values from a non-mutually exclusive domain

The best argument against the existing design is that there's really no good primary key on the table (except perhaps an identity column, which I don't consider a good primary key). That makes it hard to join to other tables.

An advantage with your design is that a query can easily identify parcels where you (for instance) have mineral rights but not surface rights using a simple WHERE clause. With the existing design, this is still possible, but it's a lot messier (and not really feasible using graphical tools or entry-level SQL). This is particularly important if the table in question is a dimension table in a datawarehouse.


Get ids only where one id is null and other isn't

WITH cte AS ( SELECT id,
                     num,
                     COUNT(*) OVER (PARTITION BY id) total,
                     COUNT(num) OVER (PARTITION BY id) notnull
              FROM mytable )
SELECT id, num
FROM cte
WHERE notnull * (total - notnull) > 0

OR

WITH cte AS ( SELECT id,
                     num,
                     FIRST_VALUE(num) OVER (PARTITION BY id ORDER BY num) mini,
                     FIRST_VALUE(num) OVER (PARTITION BY id ORDER BY num DESC) maxi
              FROM mytable )
SELECT id, num
FROM cte
WHERE mini IS NULL
  AND maxi IS NOT NULL


meaning of ## in SQL Server Profiler trace data

First, if you're using SQL Server 2008 or higher, the Profiler is deprecated - look into using Extended Events instead.

The ## marks serve as a placeholder for the value of the variables being used in the query.


PostgreSQL pg_upgrade --link mode performance issue after upgrade

After a pg_upgrade all your optimizer statistics are gone. You need to ANALYZE all of your tables.  This is true regardless of whether or not you use --link.


When are privileges listed in \l and when not?

The backslash commands in psql are shortcuts for a query or queries that look through the system catalogs. The \l command looks at information in pg_catalog.pg_database, specifically, this query:

SELECT d.datname as "Name",
   pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
   pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
   d.datcollate as "Collate",
   d.datctype as "Ctype",
   pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;

You can make psql show what it is using for the backslash commands by passing the -E flag to it when you invoke it on the command line.

If the permissions on a database or other object are the defaults that PostgreSQL creates them with, the *acl column will be NULL. If you change the defaults, as you have, the ACL column will be populated with information related to the GRANT and/or REVOKE statements you have ran.

You can see the permissions/ACLs specifically via either \z or \dp

If you read further here:

http://www.postgresql.org/docs/9.4/static/sql-grant.html

If you scroll down, (or search for the word psql), you can look at the table that shows you how to interpret the ACLs that you see with \l or in an ACL column.

For example:

=Tc/vagrant

means that PUBLIC (the implicit role that contains all roles) has permissions to create temporary tables T and connect c, because the ACL line =xxxxx denotes permissions applied to PUBLIC, while rolname=xxxx applies to that specific role.

This presentation from Dalibo should also help clarify this further: Managing Rights in PostgreSQL

Hope that helps. =)


This code should get your sorted.  Note: the top portion is just to set up sample data.

This will include a row if there is a vendor, but no vendee (VE will be null).  It will not include a record if there is a vendee, but no vendor.

-- You Sample Data
WITH YourTableName AS (
    SELECT * FROM (
        VALUES ('JAKE' , 'VO'  , 1234)
                ,('JOSH' , 'VE'  , 1234)
                ,('ANNA' , 'VE'  , 1212)
                ,('FAYE' , 'VE'  , 1212)
                ,('MATT' , 'VO'  , 1212)
    ) AS V(NAME, REL, ID)
)
-- Your View Text, replace 'YourTableName' with... wait for it... your table name:
SELECT VO, VE, Vendor.ID, COUNT(*) OVER(PARTITION BY Vendor.ID) AS CNT
FROM
    (SELECT Name AS VO, ID FROM YourTableName WHERE Rel = 'VO') Vendor
        LEFT JOIN (SELECT Name AS VE, ID FROM YourTableName WHERE Rel = 'VE') Vendee
        ON Vendor.ID = Vendee.ID
ORDER BY Vendor.ID DESC, VE  -- Can be included, really need to be specified when querying the view


mysql - Can a inner join statement limit query results?

You need to perform a left outer join or a full outer join. An inner join takes the value that matches both tables and gives you back the results of both tables.

A left outer join (sometimes referred to as a left join) matches the value between both tables. When a value exists in the primary table you are selecting from but does not exist on the table you are joining to, the results from the primary table will be returned as well as any results that were matched from the joining table. If the values do not exist in the column from the table you are joining to, you will not get those results back.

This can be reversed with a right outer join (also referred to as a right join). I try to remember it as the left join refers to the from table, the right join refers to the joined table. It is generally seen as better practice to keep it to a left join so your code is consistent to read. Sometimes it could be hard to refactor and I'd look at right joins then.

A full outer join is combining all rows in both tables and matching them if possible. If the results don't match the from table, those results will be populated with NULLS. The same is true for the other table if it has no matches.

https://www.w3schools.com/sql/sql_join.asp

Try this:

SELECT
    businesses_old.id, businesses_old.name, businesses_old.email,
    business_sub_category.sub_category_id, sub_categories.id,
    sub_categories.category_id, categories.id, categories.name
FROM businesses_old
LEFT JOIN business_sub_category
ON businesses_old.id = business_sub_category.id
LEFT JOIN sub_categories
ON business_sub_category.id = sub_categories.id
LEFT JOIN categories
ON sub_categories.category_id = categories.id
WHERE email IS NOT NULL AND email <> '';

I would also try to alias each column you refer to with the table before it, this keeps your records straight for anyone else trying to read your SQL.


Postgresql simple query takes too much time like five minutes to execute

When you use the LIMIT clause, it is applied after all the data is loaded to filter down the data, which is evident by your EXPLAIN as it is the last operation (top of the operation chain). This is because LIMIT isn't a predicate filter, rather it just takes the top N number of rows from the total result set. While 8,455 rows is tiny, the actual Table size is 62 GB on disk which is a decent amount of data to SELECT all at once and then filter and return to the client.
When you use the WHERE clause, the filtering is applied upfront, and can be done efficiently because your id field is indexed, so very few rows need to actually be analyzed to serve the data (since the logical data structure is a B-Tree) and therefore much less data (in file size) to load and process.
If you want a more efficient way to get only 50 records back, you'll need to find a way to use predicate filtering with your WHERE clause to reduce the data first, and then apply your TOP if needed. E.g. if you know you which range of ids to filter on (though I'm assuming since you're using a GUID that'll be hard to do). If there are other fields you can use to help filter down the data with predicate filtering, then you should create indexes on those fields as well, but that will depend on your table definition.


query for showing columns from various tables, grouping and showing calculated field in each row of the result

Basic is that you have all Tables set and also defined the relationship.
Then you add a new query.
Then you pull in all Table that you need, if your relationships are correctly set, all tables should ha a connection.
Now you pull all needed columns into the part below
and add a column that has the difference betwenn qty ordered and inqty

Now you have a corect query to run.
As SQL it looks like
SELECT ITEM.item_name, STOCK_IN.description, ORDER.date_order, ORDER_ITEM.[qty_ordered]
, STOCK_IN.in_qty, STOCK_IN.in_date, [qty_ordered]-[in_qty] AS qty_left
FROM ([ORDER]
      INNER JOIN (ITEM
       INNER JOIN ORDER_ITEM ON ITEM.id_item = ORDER_ITEM.id_item) ON ORDER.id_order = ORDER_ITEM.id_order)
       INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item;

For your query to have the complete quantity for all orders
SELECT ITEM.item_name, STOCK_IN.description, ORDER.date_order, ORDER_ITEM.[qty_ordered], STOCK_IN.in_qty, STOCK_IN.in_date, [Sum1] AS qty_left
FROM
    ([ORDER] INNER JOIN (
                                           ( ITEM INNER JOIN (SELECT ORDER_ITEM.id_item
                                                                       ,SUM( [qty_ordered]-[in_qty]) AS Sum1
                                                                       FROM ([ORDER] INNER JOIN ORDER_ITEM ON ORDER.id_order = ORDER_ITEM.id_order) INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item
                                                                       GROUP BY ORDER_ITEM.id_item) qtysum ON qtysum.id_item = ITEM.id_item
                                           )
INNER JOIN ORDER_ITEM ON ITEM.id_item = ORDER_ITEM.id_item
) ON ORDER.id_order = ORDER_ITEM.id_order)
INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item;

Result:


Migrating SQL database to a new SQL server

You can use CNAME in DNS. When you're done with GARDB1, rename it to something else (GARDB1_OLD). Create a CNAME in the DNS server called GARDB1 which should point to GARDB2008. It should solve your problem because GARDB1 will resolve to GARDB2008 now. Ideally what you need to have is a generic CNAME that you can just re-point to a new DB server each time you are migrating. Perhaps next time you plan to migrate, you can implement it. Or you could go with what @SeanGallardy suggested.


First thing is you will need to stop performing shorthand math operations like + against date/time values. This breaks when you switch to the newer types, which you should do.

For this particular problem, you can simply use a CASE expression to either (a) return the datetime as is, or (b) convert to date and then add 11 hours.

CASE WHEN DATEPART(HOUR, col) < 11
  THEN col
  ELSE DATEADD(HOUR, 11, CONVERT(datetime, CONVERT(date, col)))
END


Postgres 9.2 - postgres - FATAL: XX000: relation mapping file "global/pg_filenode.map" contains incorrect checksum

Before I did anything, I would make a copy of the cluster as is and keep it safe, following the directions here:

https://wiki.postgresql.org/wiki/Corruption

Once you have your master copy safe, you might try a fresh initdb in another directory, and copy over the pg_filenode.map file, as suggested here, and see if that helps start your database:

http://tapoueh.org/blog/2013/09/16-PostgreSQL-data-recovery

If that doesn't work, or you encounter more errors, I would try pgsql-general@postgresql.org for more suggestions, or engaging a PostgreSQL consultancy to try and recover your data.

If it does work, I would do a pg_dump immediately, then initdb a new cluster on the new machine, and reload the pg_dump, just to make sure there isn't anything else lurking that could cause problems.

My sympathies on your issue, and best of luck getting it resolved.


Try adding the ^ character to your regex, which means to match the position at the beginning of the string, like this

^[A-Z0-9._%-]{1,10}@[a-z0-9._%-]{1,5}\.[net|com]


Comparing duplicates and choosing the duplicate with most recent date in a join

You could use a LATERAL join or CROSS APPLY (both added in version 12c) or window ranking functions in a subquery which should work in older versions as well:

WITH cte AS
(
    SELECT
        b.id, b.name, b.preference,
        a.timestamp,         -- keep only columns you need
        ROW_NUMBER() OVER (PARTITION BY b.id
                           ORDER BY a.timestamp DESC)
          AS rn
    FROM b
         LEFT JOIN a
         ON b.id = a.id
)
SELECT cte.*           -- keep only columns you need
FROM cte
WHERE rn = 1
-- ORDER BY b.id
 ;


MySQL EXPLAIN gives more rows then the COUNT with WHERE on the indexed col

The difference between what EXPLAIN output shows and the actual number of rows returned by the query means your table statistics are out of date. The explain plan displays the estimated number of rows it thinks the engine will need to read. As the manual says,

  MySQL explains how it would process the statement

(emphasis mine). Strictly speaking, the database engine cannot possibly know how many rows it needs to process until it actually processes all of them.

You can run the ANALYZE TABLE command to update statistics; EXPLAIN might be able to better estimate how many rows would be read.


I don't think it ever is (unless you are stuck in SQL Server 2000 and are looking for pity - don't worry, you'll have it). I would opt for statement-level OPTION (RECOMPILE) every time. I'll quote the relevant bit from Paul White's blog post, Parameter Sniffing, Embedding, and the RECOMPILE Options:

  When a parameter-sensitivity problem is encountered, a common piece of advice on forums and Q&A sites is to "use recompile" (assuming the other tuning options presented earlier are unsuitable). Unfortunately, that advice is often misinterpreted to mean adding WITH RECOMPILE option to the stored procedure.

  Using WITH RECOMPILE effectively returns us to SQL Server 2000 behaviour, where the entire stored procedure is recompiled on every execution. A better alternative, on SQL Server 2005 and later, is to use the OPTION (RECOMPILE) query hint on just the statement that suffers from the parameter-sniffing problem. This query hint results in a recompilation of the problematic statement only;  execution plans for other statements within the stored procedure are cached and reused as normal.

  Using WITH RECOMPILE also means the compiled plan for the stored procedure is not cached. As a result, no performance information is maintained in DMVs such as sys.dm_exec_query_stats. Using the query hint instead means that a compiled plan can be cached, and performance information is available in the DMVs (though it is limited to the most recent execution, for the affected statement only).

  For instances running at least SQL Server 2008 build 2746 (Service Pack 1 with Cumulative Update 5), using OPTION (RECOMPILE) has another significant advantage over WITH RECOMPILE: only OPTION (RECOMPILE) enables the Parameter Embedding Optimization.

You can read on in the post to learn about this optimization, or read the whole thing to learn more about parameter sniffing and the different ways you can change SQL Server's behavior.


Error from master error log while doing a replication to slave

Try to raise the slave_net_timeout. Prior to version 5.7.7, it was set to 3600, now to 60. Therefore, if you have any trouble on the network, the slave will think it has lost connection the server but when a Slave tries to [re]connect, it creates a dump thread on Master. Before Master creates a dump thread, it checks if there is already a dump thread running for that Slave (using the UUID value). If yes, it considers that old dump thread as "Zombie" dump thread and kills it. Creates a new one and prints this "Note" in the error log file. (This is cited from the official MySQL site )

Practically speaking: Enter MySQL on the slave machine and

mysql> set global slave_net_timeout=3600;

Restart your MySQL server and it should solve the problem.

Once this is OK, you can add this parameter in the configuration file my.conf to make the changes definitive.


How do I get access logs from SQL Server database

At least, you can create table with fields for username, date and time and information about transaction. Secondly, you should create trigger functions for your tables, function should write logs into you log table, this allow you to access your logs quite simply.


One table with a changing variable or different tables?

You have a common problem, which is how to implement table inheritance into a relational database. There is not perfect solution, as it will be fully dependent on how you are going to use your database afterwards and what you want to optimize for.

Typical way to implement your solution is one of 3 ways: create 4 separate tables, create a single table, or create 5 tables, one for the "parent" relationship and 4 for the "children". You can find more discussion on the advantages or disadvantages of each solution on:

SQL Antipatterns Book
SQL Alchemy
Many other Q&A on StackExchange such as: How to model inheritance of two tables mysql

Because the question is too general, only a general answer can be answered -optimize for the most common kind of operations you are going to be performed later; or for the restrictions you may have (simplicity of the code, minimize data size on disk, minimize CPU usage, etc.), some of which will depend on the specific dataset you have and the database storage engine you are using.


Postgresql csv to db translate foreign keys

I don't see where you are referencing the content of your CSV anywhere in what you posted.. (at least before any future edits). You should be using the COPY command.

http://www.postgresql.org/docs/current/static/sql-copy.html

EDIT

Since Topic and Format tables appear to be lookup tables for referential integrity, the correct course of action would be to insert the values into those tables FIRST .. and then insert into the other table using a SELECT.. Answer below:

INSERT INTO topics (topic) SELECT DISTINCT topic FROM tmp;
INSERT INTO formats (format) SELECT DISTINCT format FROM tmp;
INSERT INTO texts (topic_id, format_id, text)
  SELECT b.topic_id, c.format_id, a.text
  FROM tmp a
  JOIN topics b ON a.topic = b.topic
  JOIN formats c ON a.format = c.format;

Don't forget to clean up when you're done.

DROP TABLE tmp;


Functional Index LOWER PostgreSQL not working

Using citext

My suggestion is to move to the citext module.

CREATE EXTENSION citext;
BEGIN;
  --out with the old
  DROP INDEX fb_name_lower_alias;
  DROP INDEX fb_name_mid_lower_alias_index;
  DROP INDEX fb_name_alias;
  DROP INDEX fb_name_mid_alias_index;

  --in with the new
  ALTER TABLE fb_name
    ALTER COLUMN alias
    SET DATA TYPE citext;

  CREATE INDEX ON fb_name(mid,alias);
  CREATE INDEX ON fb_name(alias);
  ANALYZE fb_name;
COMMIT;

Then simply stop using lower() everywhere. Your query should like this,

SELECT *
FROM fb_name
INNER JOIN fb_kg ON mid = subject_mid
WHERE alias = 'Polaski'
  AND fb_kg.relation = 'location/location/containedby';

Everything else should work the same. Paste the EXPLAIN ANALYZE of the new query.

Update on indexes, and case-sensitive searching on citext

If  you need a case-sensitive query on a citext column, you just have to cast the other side to ::text, and in fact using the citext index is usually good enough. For example you can do a case-sensitive index only scan with WHERE col::citext = 'fOo'::citext AND col::text = 'fOo'; or, you could create two indexes on the citext column:

one that's case sensitive ON table(myCITEXT)
one that's case insensitive (I find that wasteful though) ON table(myCITEXT::text)


jsonb_populate_record can't be used like a table as it only returns a scalar values.

I think you want

select id, (jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger')).*
from my_table mt

If the json value doesn't contain the key schrodinger then this will automatically return null

Online example


what is the consequence of setting enableQueryTimeouts to FALSE?

Consequences: if there is a slow query(s) your system will be too slow, hang, or even go out of memory.

if QueryTimeout is disabled  from MySQL or the connector it will stay until its finished (how to enable it from MySQL SET GLOBAL MAX_STATEMENT_TIME=XXX;  -- where XXX=number


How to limit databases number that a MySQL user can create?

A Limit to the number of MySQL Databases a User Account can Create

At the MySQL level as per the Limits on Number of Databases and Tables there is no limit on the number of databases MySQL can contain at this level.

If you give a user account the global CREATE permission to create new databases, then you give them just that and you cannot restrict the number of databases it can create at this level.

Limiting the number of Database a MySQL User Account can Create

To control the number of database you allow a user account to create you could just create the databases per an "approved" request and not grant them permission to CREATE databases themselves. You'd grant the user account Database Privileges at this level once created.

Additionally, as some third party hosting services utilize, if you only allow access to manage MySQL instances and databases via an application, it is possible to have rules at this level keeping track of the number of databases an account creates, and enforcing rules to set such restrictions.

Related Posts

Hosting: why does the number of MySQL databases matter?
Global Privileges
Database Privileges


SQL Server 2014 SP2 upgrade on Alwayson + FCI

The answer can be found here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances
I've used this method several times on 2 and 3 node AlwaysOn Availability Group implementations, and it works very well, and is explained in great detail in the above link.


Setting DB to Simple Recovery then back to Full Recovery

Merely setting the recovery back to FULL is not enough - you'd need to take at least a differential after setting back to FULL to bridge the gap between the time you changed to SIMPLE and the time you changed back to FULL - then your T-logs should be ok at 6am

Keep in mind that you will not be able to restore to a point in time after the change to SIMPLE and before the differential is taken.

Also, you say that you are taking T-log backups at the top of every hour.  So, you are basically saying that it's ok to lose up to 1 hour of data should a disaster occur.  Does this actually adhere to your company Recovery Point Objective (RPO)?  Please take a look at The 9 Letters That Get DBAs Fired.

And then take a look at Back Up Transaction Logs Every Minute. Yes, Really


How can the concatenatation of a text and a NULL value return a non-null result?

That's because the system of CASTing array types in PostgreSQL is a bit weird (on a first sight) :-)

text || text[] makes both sides coerced to arrays.

CREATE TABLE tbl (col text ARRAY);

INSERT INTO tbl SELECT NULL;

SELECT col IS NULL AS col_is_null,
  (text 'foo' || col) IS NULL AS result_is_null
  FROM tbl;

 col_is_null | result_is_null
-------------+----------------
 t           | f
(1 row)

Another example which may shed more light:

create temp table x as select 'foo' test, null::text[] col;
SELECT test, col, test || col from x;
 test | col  | ?column?
------+------+----------
 foo  | NULL | {foo}
(1 row)


This:

psql -U postgres -d utrechtTest > backupTest2.sql

runs psql and writes its stdout to backupTest2.sql, which will be overwritten.

You meant:

psql -U postgres -d utrechtTest < backupTest2.sql

which is better written as:

psql -U postgres -d utrechtTest -f backupTest2.sql

because if you use -f then psql can show line numbers.


Vehicle database design - Type, Make, Model, Vehicle

Why does 'type' have to be a separate table, and not just be an enum on 'model'? Then, when a user is doing a search of any type (via a dropdown or other method) it's just a filter on model (... where type = ANY( VALUES ('BOAT'), ('MOTORBIKE')) ...).


Well, did you try:

create database test1 on primary
  ( filename='C:\test.mdf'),
  ( filename='C:\FG1_lt_4000.ndf'),
  ( filename='C:\FG2_4000_8000.ndf'),
  ( filename='C:\FG3_8000_inf.ndf'),
  ( filename='C:\test_log.ldf')
for attach;

And yes, you do need to copy the ndf files as well. These are critical parts of your database, not garbage.

Otherwise, go back to your original server, take a backup, and then do a restore with move on the new server.


Slow MySQL searching from millions record

hash is unique?  If so, change it from KEY to PRIMARY KEY.  That will save some space, but not change the performance much.

It sounds like your innodb_buffer_pool_size is small.  It should be set to about 70% of available RAM.

Let's analyze what is happening.  You have about 100 hashes and a KEY (of any type) on hash.  It will reach into the key about 100 times in 100 different locations.  ("MRR" is involved here, but those details are not very relevant.)  Each lookup of a hash value in the BTree-organized index probably does not find the desired block in the buffer_pool, so it has to hit the disk to get it.  This means about 100 disk hits.  Assuming you have a cheap disk, these hits will take a total of about 1 second.  (Sorry, I can't explain 4 seconds, unless the cache was "cold" and index blocks needed to be fetched, too.)

So, what will a bigger buffer_pool do?  Eventually (as your queries cache more and more of the table), those 100 will be "cache hits" and not have to go to disk.  And the speed will be sub-second.

Your table is probably under 400MB (regardless of whether you take someone's advice on PRIMARY KEY).  You should be able to afford a buffer_pool bigger than that.


It's pretty easy to create a slave either from the running master or slave.
Here Percona explains how to do it with XtraBackup http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html#adding-more-slaves-to-the-master

You can use this procedure with slight variation to add new slaves to a master. We will use Percona XtraBackup to clone an already configured slave. We will continue using the previous scenario for convenience but we will add TheNewSlave to the plot.

At TheSlave, do a full backup:

TheSlave$ innobackupex --user=yourDBuser --password=MaGiCiGaM /
      --slave-info /path/to/backupdir

By using the --slave-info Percona XtraBackup creates additional file called xtrabackup_slave_info.

Apply the logs:

TheSlave$ innobackupex --apply-log --use-memory=2G /path/to/backupdir/$TIMESTAMP/

Copy the directory from the TheSlave to TheNewSlave (NOTE: Make sure mysqld is shut down on TheNewSlave before you copy the contents the snapshot into its datadir.):

rsync -avprP -e ssh /path/to/backupdir/$TIMESTAMP TheNewSlave:/path/to/mysql/datadir
Add additional grant on the master:

TheMaster|mysql> GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$newslaveip'
         IDENTIFIED BY '$slavepass';

Copy the configuration file from TheSlave:

TheNEWSlave$ scp user@TheSlave:/etc/mysql/my.cnf /etc/mysql/my.cnf

Make sure you change the server-id variable in /etc/mysql/my.cnf to 3 and disable the replication on start:

skip-slave-start
server-id=3

After setting server_id, start mysqld.

Fetch the master_log_file and master_log_pos from the file xtrabackup_slave_info, execute the statement for setting up the master and the log file for The NEW Slave:

TheNEWSlave|mysql> CHANGE MASTER TO
               MASTER_HOST='$masterip',
               MASTER_USER='repl',
               MASTER_PASSWORD='$slavepass',
               MASTER_LOG_FILE='TheMaster-bin.000001',
               MASTER_LOG_POS=481;

and start the slave:

TheSlave|mysql> START SLAVE;

If both IO and SQL threads are running when you check the TheNewSlave, server is replicating TheMaster.


Dropping unused indexes - Assessing the unexpected dangers

Your DBA's concerns are both valid.

  Regarding #1, it seems to me that SQL Server would actually do a seek on the index to determine uniqueness before an insert / update is done, and therefore, the index wouldn't show as being not used.

The uniqueness guarantee may be used by the optimizer in deciding which logical transformations or physical operations can be used to obtain correct results. The fact that the optimizer relies on a uniqueness guarantee to, for example, transform an aggregation or choose a one-to-many merge join, will not be reflected in index usage statistics, unless the index is also physically accessed in the final execution plan. One should therefore be very careful about removing (or disabling) any unique index or constraint.

  Regarding #2, is this really possible?

Yes, it is possible for the optimizer to use statistics associated with an index without the final execution plan featuring any access using that index. The processes of loading 'interesting' statistics, calculating cardinality estimates, and producing a finished execution plan are quite independent activities.

Dropping the index would also remove the associated index statistics, which may affect plan quality next time the statement is recompiled. The index statistics may be used in a cardinality estimation calculation that the final plan relies on, even where the index is not physically present in the final plan.

Your DBA knows his/her stuff.

None of this should be taken to mean that apparently-unused indexes should never be removed. I'm simply saying that your DBA's concerns are valid ones, and you should plan the change with them accordingly, with appropriate testing, and a recovery plan. In my experience, point #1 is more likely to be problematic than #2, but I have no way of knowing whether that applies to your situation.


Save SSMS tabs with timestamp on a daily basis?

Actually I think that you simply nedd statement logging. Try ssmstoolspack.

In meantime, my add-in SSMSBoost does a lot of logging as well: editor contents history, tabs history, execution statements history...


If we assume that login and logout always comes in pairs, and that login and logot are always on the same date, it's just a matter of finding the smallest time for a logout after each login. If no logout is found it means that the employee is still working so now() (if the report is run at a later date, use  23:59:59 for the date of login) is used instead:

select empid, date(time) as work_dt, time as login
    , coalesce(
          (select min(time)
           from emplog as b
           where a.empid = b.empid
             and date(a.time) = date(b.time)
             and b.time >= a.time
             and b.state = 1
          ), now()) as logout
from emplog as a
where a.state = 0

;

Now all that remains is to sum the difference between logout and login for each employee and work_dt:

select empid, work_dt,
        SEC_TO_TIME(sum(TIMESTAMPDIFF(SECOND,login,logout))) as time_worked
from (
    select empid, date(time) as work_dt, time as login
        , coalesce(
              (select min(time)
               from emplog as b
               where a.empid = b.empid
                 and date(a.time) = date(b.time)
                 and b.time >= a.time
                 and b.state = 1
              ), now()) as logout
    from emplog as a
    where a.state = 0
) as t
group by empid, work_dt
;


OS error 13 when running mariabackup as non-mysql user

I was able to use the strace command to figure out what was going on. There were two problems. The first is that there was no group write permission on the output directory, so it failed to create the xtrabackup_backupfiles directory. The second is that there were two database directories in /var/lib/mysql that lacked group r-x permission. By fixing these two issues, I was able to get it to work.

Still, it would be nice if the error message actually indicated what specifically it didn't have permission on, rather than simply reporting that it lacked permission for something.


How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement?

I believe that this is because @@IDENTITY and SCOPE_IDENTITY() do not work when used in combination with an UPDATE statement. While I understand that the natural response to this would be "Well why doesn't it return the ID of the outer INSERT?" the answer is: because of Scope.

The UPDATE statement, and the INSERT statement (using the results of the OUTPUT) are executed as one statement and are inherently within the same scope of one another. When the engine executes the query, it realises this and so it is unable to track the identity generated by the outer INSERT. You can prove this by running the following query which splits the UPDATE and the INSERT into two individual statements:

DECLARE @UpdateTable table
(
    UpdateTableID int IDENTITY,
    UpdateTableValue int
);
DECLARE @InsertTable table
(
    InsertTableID int IDENTITY,
    UpdateTableValue1 int,
    UpdateTableValue2 int
);

DECLARE @TestValue int = 5;

INSERT INTO @UpdateTable (UpdateTableValue)
VALUES (1),(2),(3);

SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

DECLARE @tmp TABLE
(
    UpdateTableValue1 int,
    UpdateTableValue2 int
);

UPDATE      @UpdateTable
SET         UpdateTableValue = UpdateTableValue + @TestValue
OUTPUT      deleted.UpdateTableValue, inserted.UpdateTableValue
INTO        @tmp
WHERE       UpdateTableID = 2;

INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
SELECT
    UpdateTableValue1, UpdateTableValue2
FROM        @tmp;

SELECT [@@IDENTITY] = @@IDENTITY, [SCOPE_IDENTITY()] = SCOPE_IDENTITY();

Which produces the correct results:

If you need to get the identity from the table you have 3 options as far as I see:

Use the technique I use above to obtain the value.
Add an OUTPUT clause to the final INSERT to capture the generated ID.
Use the IDENT_CURRENT() function to obtain the current value.

If you are using temp tables (not global temp tables) then IDENT_CURRENT() would be perfectly fine as you can guarantee that it will only return the value generated within your session (as the objects are not available via other sessions). However, if the example you gave is just that then I would go with option 2 and use another OUTPUT clause to capture the identity value that is generated:

INSERT INTO @InsertTable (UpdateTableValue1, UpdateTableValue2)
OUTPUT inserted.InsertTableID
SELECT
    UpdateTableValue1, UpdateTableValue2
FROM
(
    UPDATE      @UpdateTable
    SET         UpdateTableValue = UpdateTableValue + @TestValue
    OUTPUT      deleted.UpdateTableValue, inserted.UpdateTableValue
    WHERE       UpdateTableID = 2
) AS UpdateResults (UpdateTableValue1, UpdateTableValue2);

This then outputs the correct value:


mysqli importer file - disable foreign keys

SET foreign_key_checks = 0;
    //import data
    SET foreign_key_checks = 1;


Copy last modified files from network path into SQL server box and rename them using Agent job

Found a work around :

I have created ps1 file using the script below....
.
.
.
$source = ''
$destination = ''

@(Get-ChildItem $source -Filter *.bak | Sort LastWriteTime -Descending)[0] | % { Copy-Item -path $_.FullName -destination $("$destination\CMSOPEN_backup.bak") -force}
.
.
.

and then, created bat file which calls this ps1 file, went to sql agent job, created step to execute the bat file later executed the t sql for restore.


Better way to get months contained in a period for querying sales tables

I noticed that you have already decided on your solution, but wanted to offer another suggestion which utilizes Aaron Bertrand's Date Dimension table.

There are just so many situations where a Date Dimension table comes in handy.

Assuming you had created and loaded his Date Dimension table, it would be a simple as:

DECLARE @year INT = 2017
    ,@quarter INT = 3

SELECT DISTINCT convert(VARCHAR(4), [Year]) + right(REPLICATE('0', 2) + convert(VARCHAR(2), [Month]), 2) as Period
FROM DateDimension
WHERE [Year] = @year
    AND [Quarter] = @quarter

--OR

SELECT DISTINCT substring(MMYYYY,3,4) + substring(MMYYYY,1,2) as Period
FROM DateDimension
WHERE [Year] = @year
    AND [Quarter] = @quarter

giving

| Period |
|--------|
| 201707 |
| 201708 |
| 201709 |