I implemented what you are trying to do this way:  the transformation to a user defined object happens in a packaged procedure that is called by triggers.

---all comments and debugging removed to keep it simple----
CREATE OR REPLACE TRIGGER when_close_ini
   AFTER UPDATE OF end_date
   ON YOUR_TABLE
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE

BEGIN
   IF :NEW.end_date IS NULL AND :OLD.end_date IS NOT NULL
   THEN
      my_schema.queue_util.add_file_to_queue ('REOPEN', :NEW.ID, NULL);

   END IF;

END when_close_ini;

----------------an excerpt from the package with debugging removed----
 PROCEDURE add_file_to_queue   (action_in      IN VARCHAR2,
                                d_case_id_in   IN NUMBER,
                                d_other_in     IN VARCHAR2 := NULL)
   IS

      queue_options        SYS.DBMS_AQ.ENQUEUE_OPTIONS_T;
      message_properties   SYS.DBMS_AQ.MESSAGE_PROPERTIES_T;
      message_id           RAW (16);
      my_message           MY_SCHEMA.file_action;
      err_text             VARCHAR2 (2000);
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN

      my_message := my_schema.file_action (action_in, d_case_id_in, d_other_in);
      DBMS_AQ.enqueue (queue_name           => 'MY_SCHEMA.MY_QUEUE',
                       enqueue_options      => queue_options,
                       message_properties   => message_properties,
                       payload              => my_message,
                       msgid                => message_id);
     COMMIT;
   END add_file_to_queue;


In general what you want here is an explicit transaction around your code.

BEGIN TRANSACTION
... your code ....
COMMIT

However there are down sides to concurrency here as well.  While your transaction is open any locks needed to perform the tasks will be held until the end of the transaction.  That means that the next set of code that needs to run will have to wait until this one is completed.

Even worse you increase your odds of creating a deadlock.  A deadlock would occur when session 1 is holding a set of locks X.  Session 2 is holding a set of locks Y then needs to get a hold of some of the resources held by X.  If session 1 then needs resources in Y you get into a situation where neither session can ever complete.  This causes a deadlock and one of the sessions will be rolled back.

The solution to this is to make sure that your transactions take as little time as possible.  What worries me is the whole bunch of JOINs comments.  That tends to mean the commands will take a fair amount of time increasing your odds of blocking and deadlocking.  If I'm wrong and the whole piece of code is fast then you shouldn't have major issues (there is always a chance of deadlocking, speed just reduces it).

Because your example is so simplified it's going to be hard to go beyond such a general piece of advice.  If you'd like to post a better idea of the actual code we might be able to help you in other ways.

Additional information on blocking & deadlocking here.


/etc/my.cnf is not the only place to get such things.  It sounds as if you have a .my.cnf / my.ini file in your 'home' directory on Windows.

Which version of 5.7 are you using?  (There were several changes to sql_mode.)

What client are you using?  Perhaps it is injecting changes to sql_mode to mess with your head.


You can get overall (instance wide) hit info from the db.serverStatus() command, and in particular:

http://docs.mongodb.org/manual/reference/command/serverStatus/#serverStatus.indexCounters.hits
http://docs.mongodb.org/manual/reference/command/serverStatus/#serverStatus.indexCounters.misses

These numbers are totals seen for the lifetime of the process, so if you want to get a rate or see the trend over time, then you will need to record them periodically and diff the values accordingly.  Thankfully, if you install MMS Monitoring, it will do that for you and graph the results.

However, there are currently no per-index statistics available.  The relevant feature request can be found here for tracking and voting purposes:

https://jira.mongodb.org/browse/SERVER-2227

Update: January 2016

The stats referenced above have been removed from the output of the server status command, so are no longer available.  However, the referenced feature request is now complete and is available in version 3.2 in the form of the $indexStats aggregation operator (the linked docs also contain sample output). For completeness, here's an example I put together:

Prior to any queries I have just 2 indexes, the default _id and indexme, both with 0 ops:

> db.foo.aggregate( [ { $indexStats: { } } ] ).pretty()
{
    "name" : "indexme_1",
    "key" : {
        "indexme" : 1
    },
    "host" : "localhost:27017",
    "accesses" : {
        "ops" : NumberLong(0),
        "since" : ISODate("2016-01-12T19:03:01.358Z")
    }
}
{
    "name" : "_id_",
    "key" : {
        "_id" : 1
    },
    "host" : "localhost:27017",
    "accesses" : {
        "ops" : NumberLong(0),
        "since" : ISODate("2016-01-12T18:59:24.292Z")
    }
}

Then run a couple of finds to bump the ops on indexme and check again:

> db.timecheck.find({indexme: 33})
> db.timecheck.find({indexme: 55})

> db.timecheck.aggregate( [ { $indexStats: { } } ] ).pretty()
{
    "name" : "indexme_1",
    "key" : {
        "indexme" : 1
    },
    "host" : "localhost:27017",
    "accesses" : {
        "ops" : NumberLong(2),
        "since" : ISODate("2016-01-12T19:03:01.358Z")
    }
}
{
    "name" : "_id_",
    "key" : {
        "_id" : 1
    },
    "host" : "localhost:27017",
    "accesses" : {
        "ops" : NumberLong(0),
        "since" : ISODate("2016-01-12T18:59:24.292Z")
    }
}


Change MAMP to use a lower Mysql 5.1 version

Use a binary distribution (or compile from source) and then install using your system specific my.cnf (see below for mine).

pol@Pol-laptop:~/Downloads/mysql/src/inst5.6$ ./scripts/mysql_install_db --defaults-file=/home/pol/Downloads/mysql/src/my.cnf

Then, when you kick off your 5.1 instance, specify the my.cnf in the --defaults-file option to the mysqld daemon. I've never done this with 5.1 (why are you using 5.1?) but if you have problems, it could be due to the fact that older versions sometimes  appear to require that the --socket, --pid-file and --port options be specified separately. Experiment and post a new question if you're stuck.

pol@Pol-laptop:~/Downloads/mysql/src/inst5.6$ more my.cnf
[mysqld]
#
# * Basic Settings
#
user        = pol
pid-file    = /home/pol/Downloads/mysql/src/inst5.6/mysqld.pid
socket      = /home/pol/Downloads/mysql/src/inst5.6/mysql.sock
port        = 3306
basedir     = /home/pol/Downloads/mysql/src/inst5.6
datadir     = /home/pol/Downloads/mysql/src/inst5.6/data
tmpdir      = /tmp
pid-file    = /home/pol/Downloads/mysql/src/inst5.6/mysql.pid
lc-messages-dir = /home/pol/Downloads/mysql/src/inst5.6/share
lc-messages = en_US

general_log     = on
general_log_file= /home/pol/Downloads/mysql/src/inst5.6/logfile.txt

log_error   = /home/pol/Downloads/mysql/src/inst5.6/error.log
log-error   = /home/pol/Downloads/mysql/src/inst5.6/error.log

slow_query_log_file = /home/pol/Downloads/mysql/src/inst5.6/slow_query.log
slow_query_log  = 0

explicit-defaults-for-timestamp = TRUE

#language   = /home/pol/Downloads/mysql/src/inst5.6/share/english

# bind-address  = 0.0.0.0 - may not need c.f. host...

[client]
port        = 3306
socket      = /home/pol/Downloads/mysql/src/inst5.6/mysqld.sock
host            = 127.0.0.1
# or maybe localhost... cf. bind-address above...

# added from http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html
# to allow for abrowse to load data!
#loose-local-infile =   1

local-infile    = 1

[mysqld_safe]
#user       = linehanp
#socket     = /home/pol/Downloads/mysql/src/inst5.6/mysql.sock
#err-log        = /home/pol/Downloads/mysql/src/inst5.6/error.log
#pid-file   = /home/pol/Downloads/mysql/src/inst5.6/mysql.pid
#log_error  = /home/pol/Downloads/mysql/src/inst5.6/error.log
#log-error  = /home/pol/Downloads/mysql/src/inst5.6/error.log

pol@Pol-laptop:~/Downloads/mysql/src/inst5.6$


How can I speed up "show columns" on MySQL?

MySQL recalculates table statistics for certain operations that access INFORMATION_SCHEMA tables (SHOW COLUMNS is just a convenient alias for querying INFORMATION_SCHEMA.COLUMNS).  Set innodb_stats_on_metadata to be false, which will prevent this recalculation from occurring when you ask for metadata from the table.

SET GLOBAL innodb_stats_on_metadata=0;

and add the following to my.cnf

[mysqld]
innodb_stats_on_metadata = 0


If you are unsure anything is happening during the restore here are a few ways to check for activity:

Using Top

From the command line run top. It will show you what's happening right now on the system.  You can then filter the list to look for MySQL by pressing o and typing COMMAND=mysql

If RAM and CPU usage looks fairly low it may indicate the bottleneck being your system's IO being the slowest part while you finish the restore. In the top header you'll see the wa value, which is the time the CPU spends waiting for I/O to complete.

You could interrogate the IO subsystem more by using the linux iostat command. You may be hitting IO bottlenecks but, unless you are planning on doing this regularly, this import task isn't going to be typical system usage so don't worry too much about IO performance just from this. You're importing a 157GB file - that's a lot of data to read, insert and build indexes for!

Mysql client

Log into the server using mysql. Run the command show processlist to see a list of currently running queries. You should see the inserts taking place and changing on each execution of the query.

Filesystem

take a look in the /home/ssd/mysql_datadir directory to see if the physical database files are growing as the data is being inserted. This will show you the file size every two seconds. They'll be growing nicely as the data imports.

watch -n 2 ls -la /home/ssd/mysql_datadir/

ibdata1 will be the data for your database. ib_logfile0 and ib_logfile0 is used by MySQL\MariaDB to help ensure data consistency after an unclean shutdown.

It's a bit too late to restart your import but in future you can use a small utility called pipe viewer to see where in the backup file the import has got to by piping the import through it.

pv database_dump.sql | mysql -u root myDB

It will give you a fancy progress bar and show you the read rate of the file and give an estimated completion time. This is only measuring the reading of the file though, not how long MariaDB is taking to process things.

I hope that helps!


Your approach is broken because of the atomic transactions.

Any change to the table is not visible to other transactions until the change is committed.

So if three transactions are started at the same time, the max() value will be the same for all three of them resulting in the same generated identifier. The only way to avoid this, is to exclusively lock the entire table before you insert a row. Which means you can not have more than one transaction inserting rows at one given time.

In general using this approach is a really bad idea because implementations are either broken (as you have discovered) or they won't scale because of the locking needed.

The only scalable and safe way to generate unique numbers is to use a sequence.

There is also no need to store the prefix twice, just store the prefix e.g.:   'SOMEPREFIX\F717\PT' in one column and store the unique identifier generated by a sequence in another column. If you need to display them as one, do that in the application.


Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?

The direct answer to your question's title is No.
SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.
Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Reasons for occasionally slow queries?
Trouble deciphering a deadlock in an innodb status log
Will these two queries result in a deadlock if executed in sequence?

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that SELECT queries can lock data.
You also have special cases of SELECT where you can lock specific rows on demand.
UPDATE 2011-08-08 16:49 EDT
You asked the variation question : "Are InnoDB deadlock exceptions possibly going to be thrown by SELECT" The answer to that can be Yes under a certain condition. What is that condition ? If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.
Based on that statement, the sequences of event to cause this could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)
UPDATE 2015-09-22 18:40 EST
Earlier in the year, I learned that Percona has a cool Nagios check to find these pesky locks hiding behind sleeping connections. All you have to do now is run the code from that link:
SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS b ON  b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS r ON  r.trx_id = w.requesting_trx_id
LEFT JOIN  INFORMATION_SCHEMA.PROCESSLIST       AS p ON  p.id     = b.trx_mysql_thread_id;

This will only works for MySQL 5.5+. If you have MySQL 5.1 or prior, you must kill all the sleeping connections to release the locks.


DB2 (LUW) equivalent for Oracle "drop user cascade"

It might be helpful to review DB2 in general first.

When you install DB2 it creates an instance. Think of this as a "server". An instance can have zero to many databases within it (can't say I know the limit). When you created DB2 locally it created an instance for you named DB2. Usually when you install DB2 on say a Unix server, you name your instances. You can have more than one instance on the same physical server. You dsinst1 id is the "owner" of the instance (usually the instance is named after the ID that owns it). You never want to delete this ID without scrapping the instance or you can end up with some very strange behaviors. This ID (since it is instance ower) has the power to create databases, but does not have to. This blog entry by fellow DB2 user Ember Crooks helps explain the concept of an instance to those more familiar with Oracle.

Instances can have databases created within them. We'll come back to this later.

The DAS (or DB2 Administration Server) is really more like a service. It is what allows some of the GUI tools like DB2 Control Center to interface with DB2. From what I understand, this service isn't necessary for DB2 to run, but is often necessary for end-users to interface with DB2 from a non-command line perspective. (Mustaccio am I correct here?). No matter how many instances you have on a physical box, there is only one installation of the DAS. This one installation can service all the instances. There is usually a separate ID that is needed to execute the DAS service. This is your dasusr1. Again, you don't want to delete this ID. This ID usually has no authority to create any objects within your database.

Now for the fence ID (ie, db2fenc1). Having one of these is a good idea. When you create an instance you can specify a fence ID. If you do, it becomes that ID. If you don't, then the instance owner ID becomes the fence ID. The fence ID is used for running certain stored procedures within DB2. In DB2 there are two ways to run stored procedures: fenced and unfenced. Unfenced means the stored procedure runs within the DB2 engine's memory. It will run faster, but if the stored procedure is more than SQL (ie, contains C++ or Java code for example) and it crashes, it can threaten the engine and actually crash it and cause harm. Fenced stored procedures run outside of the DB2 engine memory. They are thus slower, but if they crash they do not threaten the DB2 engine because they ran in their own protected memory area. An example of how this is used by DB2: if you run the db2 export utility from the ADMIN_CMD stored procedure, DB2 will switch the user on you of who actually executes the export. This is because you are doing file I/O and DB2 wishes to run this fenced to protect the engine. So even if you are running this stored procedure call as the instance owner, DB2 will switch the ID to the fenced ID (with no chance of you altering this behavior). If you look at the permissions of who owns the export file, you will notice it is the fenced ID, rather than the instance ID (or other ID you used to fire off the stored procedure). Of course if your instance ID is your fenced ID, then it will look one and the same to you.

(Big breath).

OK. So I said all that to introduce you to DB2 in a nutshell. Now we get to what you are probably worrying about -- who owns what? Which ever ID creates the database owns the database. There are "groups" that DB2 accepts that generally have the DBADM (ie, database administrator) authority to create databases. So if you created a database using your instance owner ID, then it would be the owner of the database and any objects it creates within the database. If a different ID connects to the database (as long as it has the correct permissions) it can create objects and own them.

Here is the interesting thing about groups and IDs within DB2. They aren't really objects from what I can see. So you can't drop them. You can't create them either (at least from what I can see). When you grant or revoke a permission to an ID, DB2 then stores mention of that group or ID. But DB2 does not really "create" them. Most often I have seen DB2 pointed to the OS or to an LDAP or both to authenticate the IDs. If the IDs are removed, you have to manually remove the permissions from the IDs and/or transfer ownership of the objects. There is nothing magical about this here. I asked a similar question having to do with doing a backup/restore to a different system and how that affects permissions.

And lastly for your comment about schemas, generally each ID maps to its own schema. Whether or not the schema exists (created by the DBADM ID explicitly or by the end user through the IMPLICIT_SCHEMA permission) is another question. Generally whatever ID connects to a database, DB2 assumes the schema is the same as the user id and you have to switch schemas (SET SCHEMA MYSCHEMA) or explicitly mention your objects (SELECT * FROM MY_OTHER_SCHEMA.MY_TABLE) to work with them.

Hope this helps.


Crossjoin-like operation except A x {} = A?

Will this work for you?

A FULL OUTER JOIN B ON TRUE


How to run multiple ST_DWITHINs/ ST_INTERSECTS within a single WHERE clause

Your FROM clause creates an unwanted cross join between the tables. This is not related to PostGIS and geometric functions.

FROM schema2.tablea a, schema3.tableb b

Indeed as suggested, use two separate UPDATE queries, one for each table. When you compare the EXPLAIN commands, you should see 2 nested loops for the big query instead of one for the alternative solution with two small queries.


What exactly is written to transaction log?

but I still don't understand why the log is so big, especially in Simple recovery mode - this operation should be minimally logged...

No, in simple recovery mode, leaving Operations That Can be minimally Logged, all other operations are fully logged. In simple recovery model what is different is when the checkpoints are going to happen. Checkpoints are fired after transaction commits and when log file grows 70% of its size. For all other scenarios logging in simple recovery is same as full recovery.

Minimal Logging also requires Target Table to meet certain requirements

  Roughly 14 GB of data (~80 million rows),

Were you performing insert into select * from type of query ? In that case you were doing lot of data reading and movement so transaction log file will record each page allocated to new table and depending on query. Now transaction log also keeps space for rollback of all the changes done. Keeping all this in mind I guess the space required would be genuine. I am not sure what query you ran and your SQL Server configuration, depending on that, answer can change.

There was a similar question I answered about what events are recorded in transaction log. There is NO documented command to read it but you can use undocumented command to read transaction log, I must tell you it requires level of expertise to interpret the output. Please read Analyzing Transaction Log By Remus Rusanu.

There are 3rd party tools available by RedGate and Apex which helps you in reading transaction log. You can search about them online.


Start the SQL Server service in single-user mode (by using the -m startup parameter).  When you do this, all members of the local administrators group will have sysadmin rights.  Provided you are a member of that group, you should be able to do whatever sysadmin actions you need.

Take a look at Database Engine Service Startup Options for documentation.

Edit:  To start SQL Server in Single-User Mode, please see this documentation.


Select Unique Fields with Max Values in Each?

This type of queries are sometimes called "greatest n per group" (there is even a tag in StackOverflow and DBA.SE). In this question, you want one row per group.

A common solution is to use window functions (but these ar enot available in Access) and another is to use GROUP BY in a derived table and then join back to the original table. Like this:

SELECT t.*
FROM
    TableName AS t
  INNER JOIN
    ( SELECT Addy, MAX(UDate) AS UDate
      FROM TableName
      GROUP BY Addy
    ) AS grp
  ON  grp.Addy = t.Addy
  AND grp.UDate = t.UDate ;


Sql: Count Null and Non-Null values for set of tables and fields dynamically

The best way to do this would be to construct the query in one shot using STRING_AGG and sys.columns (you should avoid INFORMATION_SCHEMA, it's for compatibility only).
We start off with a basic query for say: Table1 (Col1, Col2), remember that COUNT(something) only counts up non-null values
SELECT
  TableName = 'Table1',
  TotalRows = COUNT(*),
  Col1 = COUNT(Col1),
  Col2 = COUNT(Col2)
FROM Table1

We then unpivot that:
SELECT
  TableName,
  TotalRows,
  NonNullsRows,
  NullsRows = TotalRows - NonNullsRows
FROM (
    SELECT
      TableName = 'Table1',
      TotalRows = COUNT(*),
      Col1 = COUNT(Col1),
      Col2 = COUNT(Col2)
    FROM Table1
) t
UNPIVOT (NonNullsRows FOR Col IN (
    Col1, Col2
)) p

We can then dynamically construct the above for all tables and columns
DECLARE @sql nvarchar(max) =
(
    SELECT STRING_AGG(TableQuery, N'
UNION ALL
')
    FROM (
        SELECT N'
SELECT
  TableName,
  TotalRows,
  NonNullsRows,
  NullsRows = TotalRows - NonNullsRows
FROM (
    SELECT
      TableName = ' + QUOTENAME(t.name, '''') + N',
      TotalRows = COUNT(*),
      ' + STRING_AGG(CAST(QUOTENAME(c.name) + ' = COUNT(' + QUOTENAME(c.name) + ')' AS nvarchar(max)), ',') + N'
    FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + N'
) t
UNPIVOT (NonNullRows FOR Col IN
    ' + STRING_AGG(CAST(QUOTENAME(c.name) AS nvarchar(max)), ',') + N'
) p'

        FROM sys.tables t
        JOIN sys.columns c ON c.object_id = t.object_id
        WHERE c.name LIKE 'new_'   -- do you want this filter
           -- you may also want AND c.is_nullable = 1
        GROUP BY t.object_id, t.name, t.schema_id

    ) AS t(TableQuery)
);

PRINT @sql;  -- for testing

EXEC sp_executesql @sql;


Why is there an additional row with an empty ga and a count of zero?

Because there are NULL values in the ga column.

  Why the count is zero?

While COUNT(*) can never give 0 as a result (as it counts rows), COUNT(column) or COUNT(expression) can give 0 because it counts not null values in the column/expression.

Thus the only logical explanantion is that the ga column has some nulls.

  How many?

You can find out by running:

SELECT ga,
       count(ga) AS count_non_nulls,
       count(*)  AS count_all
FROM gb
GROUP BY ga
ORDER BY ga ;

which will give you something like:

ga | count_non_nulls | count_all
---+-----------------+-----------
?  |  200            |  200
A  |  100            |  100
B  |   50            |   50
   |    0            |   17  -- some number >= 1
(4 rows)


Inplace upgrade from SQL Server 2008 Standard to SQL Server 2014 evaluation

You cannot upgrade from SQL Server 2008 SP4 to SQL Server 2014 evaluation edition. Its not supported and wont allow you please see Supported Version and edition upgarde

You should not worry much about upgrading from 2008 Sp4 to 2014. The upgrade would not be a much of problem but how would application behave is something on which you should focus. You can download SQL Server 2014 evaluation edition , which is equivalent to enterprise edition for 180 days. Restore database from 2008 to 2014 and start testing. Don't use any enterprise features. When you are satisfied with application performance do an inplace upgrade to SQL Server 2014 standard

Before doing in place upgrade make sure you run upgrade advisor and mitigate all issues(if any) pointed by UG advisor.


How Large Table before Using Clustered Columnstore Index

The article you link to gives this guidance:

  Consider using a clustered columnstore index when:

  Each partition has at least a million rows. Columnstore indexes have
  rowgroups within each partition. If the table is too small to fill a
  rowgroup within each partition, you won't get the benefits of
  columnstore compression and query performance.

The "minimum size" for columnstore isn't really any size on disk, but number of rows. Each table (or partition if the table is partitioned) should be at least 1 million rows before considering it for columnstore.

A very narrow multi-million row table might be only a few megabytes and still be an excellent candidate for columnstore. A wider table with fewer rows may take up more space on disk, and not be a good columnstore candidate.

I'd recommend revisiting the sections in that article titled "Consider using a clustered columnstore index when..." & "Don't use a clustered columnstore index when..."


To get the size of TEMP, in bytes (obviously divide by a few 1024s to get Gigabytes):

select sum(bytes)
from dba_temp_files
where tablespace_name = 'TEMP';

Doc link for the view here.


Query to get the number of errors reported by PostgreSQL

This is not tracked inside the database.

You could mine the log file for ERROR entries — log_destination = 'csvlog' will make it easier.

One option to process csvlogs (thanks, a_horse_with_no_name!) is to use them with a file_fdw foreign table to access them with SQL from PostgreSQL.


Have you configured MSDTC on both the source and target machine using dcomcnfg?

The distributed transaction coordinator, by default, does not allow distributed transactions.

You need to configure it by running dcomcnfg as an administrator, and configure the options applicable to your situation:


MongoDB not using wildcard nested array index

When MongoDB indexes an array, or a field in an array of subdocuments, the index is essentially a list of value -> recordId pairs.
The value part is the actual field value, and does not include any information about the index of the value within the array.
The query executor's index scan matches the queried value with the indexed values and returns a list of recordIds that then need to be fetched.
If you have additional requirements, such as an elemMatch clause or require the value to be in a specific position in the array, the documents will have to be examined after the index scan.
This means that if you are using an index on {"quizzes.done":1} to match "quizzes.0.done": true, the executory would need to retrieve all documents that contain at least 1 quiz that is done, and examine each to determine if it is the first index.  In that case, a collection scan is quite likely more efficient.
You noted that the index on {"quizzes.0.done":1} works for that query, but that is because the query executor can guarantee the location of the match within the array with that index, and be certain that no other array element will match.
I'm not certain how the { quizzes.[$**].done: 1 } index might be handled, but I suspect it won't look much different than { quizzes.done: 1 }


Transactional Replication - table does not have the identity property

There is no need to add an identity column for no reason.

My guess is someone is issuing a SET IDENTITY_INSERT ON when there is no identity column in this table.

You need to check the code to make sure it is correct.


MySQL import csv file ERROR 13 (HY000): Can't get stat of /path/file.csv (Errcode: 2)

I hope you are using LOAD DATA INFILE.

Try to use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE.

Other issue might be this, please visit the following links : MySQL LOAD DATA.

When you login in MySQL do like below,

abdul@xmpp3:~/Desktop/Jiva$ mysql -uroot -p --local-infile
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 257
Server version: 5.5.29-0ubuntu0.12.04.1-log (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mydb;

mysql> LOAD DATA LOCAL INFILE '/home/abdul/Desktop/STATISTIC_T.csv' INTO TABLE STATISTIC_T  FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n'  IGNORE 1 LINES;

Now load your CSV using LOAD DATA LOCAL INFILE ,we need to use --local-infile before we load CSV in in new MySQL vesrsions,  due to security reasons.


I am presuming that refers to the existing SQLOLEDB

That is not the case.

The installer is not trying to upgrade SQLOLEDB.  The old driver is part of Windows and won’t be affected by the installation or upgrade of MSOLEDBSQL.


restore mongodb sharded cluster to another server

Restore Config Server using mongorestore.
Restore data backup for each shards.
Change Config settings. use config and see db.shards.find(), you will see setting as related to production server, which you need to update it based on new server settings.


You can declare a reference cursor type and a cursor variable based on that type.
Then you define a select statement that the ref cursor will represent using a bind variable in the place of the dynamic table name you get from the outer cursor.
Open the ref cursor for the sql and populate the bind variable as you go with the 'USING' clause.
I've put the code below together but I haven't run or checked it in anyway so I suggest taking it as a guide to what you want rather than the line for line solution.
On top of that I can't really tell what exactly you are trying to achieve.

DECLARE
    CURSOR c_outer IS
       SELECT 'A'||c_outer.active||'JOB' AS active
         FROM comm;

    TYPE innerCurTyp IS REF CURSOR;
    v_innerCur innerCurTyp
    v_sql      VARCHAR2(1000);
    n_hours     NUMBER;

BEGIN
    FOR r_outer IN c_outer LOOP
       v_sql := q'[SELECT (sysdate-to_date(START_TIME,'YYYYMMDDHH24MISS'))*24 HOURS FROM ]'|| r_outer.active || q'[  WHERE  UPPER(STATUS) = 'EXECUTING']';
       OPEN  v_innerCur FOR v_sql
       LOOP
          FETCH v_innerCur INTO n_hours;
                IF n_hours > 24 THEN
                    DBMS_OUTPUT.PUT_LINE('MAXIMMO');
                END IF;
          EXIT WHEN v_innerCur%NOTFOUND;
       END LOOP;
       CLOSE v_innerCur;

    END LOOP;

END;


Database 'MyDb' is being recovered. Waiting until recovery is finished

Crash recovery is running on 'MyDb'. Possible causes:

The SQL Server service was restarted manually, due to a crash or a server stop/start.
A severe error in the database caused the database to be shut down and recovered.
Recovery was initiated deliberately by someone executing a RESTORE WITH RECOVERY.
The Auto-Close option is set, causing the database to shut down when the last user exits and run recovery to restart when the next connects.

If recovery is taking a long time to complete and there doesn't appear to be any activity you may need to restore from backups. If you're feeling brave you should start reading everything you can find about repairing/recovering suspect databases.

More information on what happened before the database ended up in this state would make for better answers e.g. what actions you carried out, what errors are in the SQL error log etc.


Creating SQL Server triggers to copy a record from one table and populate another

OK, Here's my update based on your newly added info. The merge statement in the trigger will insure that newly added records will be inserted into hydrants. Otherwise, records that exist between inspections and hydrants will be updated in hydrants--when inspections is updated.

You can see when you run the insert and update (after the table and trigger creation below) that the row insert in the inspections table is inserted into hydrants whereas the row update to inspections is also updated in hydrants.

create table Inspections
(
    PK int identity(1,1),
    MyDate date
)
go

create table Hydrants
(
    PK int,
    MyDate date
)
Go
CREATE TRIGGER MyTrigger
   ON  Inspections
   AFTER  INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    MERGE INTO Hydrants as Target
    USING inserted as Source
    ON (TARGET.PK = SOURCE.PK)
    WHEN MATCHED  THEN
    UPDATE SET TARGET.MyDate = SOURCE.MyDate
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (PK, MyDate)
    VALUES (SOURCE.PK,SOURCE.MyDate);

END
GO
insert into Inspections(Mydate) values('01/10/2016')
select * from Hydrants
update Inspections set Mydate = '02/10/2016' where PK =1
select * from Hydrants


Is it faster to load several small files, or one big file?

It's easier for the storage engine to load in smaller chunks. The reason is that a big data load fills up the InnoDB rollback segment, and that takes time to purge.

For this reason, tools like pt-fifo-split exist. This helps to process a huge file in modestly-sized pieces, without copying the pieces to disk.


If you do a full import, the import will attempt to create all the tablespaces and data files exactly as they existed in the source system.  If you want exactly the same data files on exactly the same locations, you're all set.  If you want the data files to be in different locations, you would need to create the tablespaces before doing the import.

If you do a full import, the import will create all the users.


How to solve SIDs mistmatching in SQL or orphaned users?

There are a couple of possibilities.

First if the orphaned user is a windows login/group (type U or G) then no problem.

-- If the login doesn't currently exist on the server
CREATE LOGIN [Windows Account] FROM WINDOWS;
-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];

If it's a "SQL Login" (type S) and the login exists then again no problem.

-- Fix the user
USE dbname
ALTER USER [user name] WITH LOGIN = [Windows Account];

If the login does not exist for a "SQL Login" then you have to create the login and that will require knowing the password.

-- Create the login
CREATE LOGIN [login name] WITH PASSWORD = '<strong password>';

Don't forget to make sure your password meets your windows policy requirements.
If you still have the old server you can copy the login pretty easily from the other server including the password by getting the HASH of the password from the other server. (There may be some issues depending on if the two servers are different versions).

I did a post on this here: http://sqlstudies.com/2013/03/25/how-do-i-move-a-sql-login-from-one-server-to-another-without-the-password/


Is there any way to force at least one column NOT NULL

If you use version 8.0.16 or above, use CHECK constraint.

Something like

CHECK COALESCE(`day`, `date`) IS NOT NULL

for "at least one is not null", or

CHECK `day` IS NULL + `date` IS NULL = 1

for "strongly one is not null".


What must be in place to validate a XMLTYPE against a schema?

My test case ran fine on multiple other databases and multiple sources (including oracle support) asked if the ANONYMOUS user had been dropped.  Although it had not, there were some XML DB Repository changes that were made without a full understanding.  To try short cut the problem I re-installed XDB using note 1292089.1.  This allowed my test case to work correctly and after dropping the created types and tables and re-registering our XML schema, everything worked correctly.


Cannot run a job to update table on a linked server

The SQL Server 2012 builds that were released after SQL Server 2012 was released. You must apply a SQL Server 2012 hotfix to an installation of SQL Server 2012. The fix for this issue was released in Cumulative Update 1 for SQL Server 2012.  For more detail please read:
https://support.microsoft.com/en-in/help/2652582/fix-invalid-authorization-specification-error-message-when-you-run-the


Use sqlldr control file to automatically create several tables in Oracle

The Utilities manual that describes SQL*Loader syntax (which no sane person can recall anyway!) has a number of case studies (doc link here). Case study 5 covers loading into multiple tables. The actual case studies are installed into $ORACLE_HOME/rdbms/demo if you choose to install the companion CD. I seem to recall that in earlier versions they were also described in full in the Utilities manual.


sys.dm_exec_sql_text returns NULL in dbid, objectid, and number columns, but not text

This is by design as of now. There is a connect item about this problem which is closed as of now.

sys.dm_exec_query_stats DBID column NULL for dynamic SQL - by Theo Ekelmans

But at the end there is a comment:

  Thank you for the feedback! We will consider it for the next release
  of SQL Server.

There is also some workaround in the comment section and also as mentioned by @sp_BiltzErik in the comment above.


SQL Server: Are there any performance benefits from indexing a table <8kb in size?

Yes.

One potential benefit is that the slot array is ordered by index key order so matching row(s) on the page can be found by performing a binary search (or sometimes linear interpolation) rather than reading all rows on the page.

SQL Server, Seeks, and Binary Search contains much more details about this.


What are some likely causes of high Client:ClientRead AAS in AWS RDS?

Posting an answer in the hopes that it helps anyone else seeing this issue. Client:ClientRead is best summarized as the time waiting on the client to read the data, and can include not just time transmitting but also time waiting for the client to be ready to transmit. Important to note for NodeJS users.
NodeJS, for better or worse, has that single-threaded event loop. So when we opened up our new app to a few hundred users and were still only using a single NodeJS replica of the service, that one service was queueing hundreds to thousands of events to loop through. So it would end up queuing all of these async calls to the DB and while it was working like crazy, it started taking seconds to get around to the callbacks, and that time was stacking up under this Client:ClientRead statistic.
As soon as we started firing up replicas of that service, that statistic dropped drastically, and we started seeing DB CPU waits be our problem. That's outside the scope of this question so I won't go in to it. But it's good to know the Client:ClientRead, when not dealing with transmitting large datasets back to the client, is likely explained by the calling service taking it's time to get back around to reading the data. If you're using Node in a K8s environment, it's likely time to scale up your replicas.


Inserting sorted results to another table not working

Short answer:  Use ORDER BY on a SELECT if you want a particular order.

Long answer:

Without an ORDER BY clause on SELECT, the engine is free to order the results in any way it feels like.  This is what you need:

select * from ksm_mutasi_inv_benang
     ORDER BY value_date

But digging a little deeper...  You are copying id from one table to another.  Presumably id is/was AUTO_INCREMENT and the PRIMARY KEY in the source table.  Did you really need to copy it over?  If not, some other ordering may have occurred.

Further...  Using ORDER BY in the INSERT..SELECT was wasted effort since the target table might have its own concept of ordering.  In this particular (and uncommon) case, you have UNIQUE KEY idx_id (id), which was treated as the PRIMARY KEY.  Then, in InnoDB, the data is ordered by the PK.  So, the order of the data in the table is by id.  And the SELECT without an ORDER BY (or WHERE) will simply read the data in PK order.


What is the correct way to define and query a 3NF Associative entity

In order to JOIN contacts to networks you need to first JOIN to networkContacts.

select *
from contacts a
    inner join networkContacts b
        on a.cid=b.cid
    inner join networks c
        on c.nid=b.nid


when to put a business rule in the business application layer or into a database constraint

My 2 cents, although I am not nearly as experienced as many of the others on this site.

If it is related to data integrity (either from a relationship standpoint OR a standardized formatting standpoint), then I firmly believe that it should be handled by the database. If it's simply for a nice presentation, then I think that should be handled by the presentation layer.

In the example you site where the value caused a division by 0 error in a calculation .. if that calculation happened outside the database, then I do not believe a constraint on the data in the database is valid at that point. The database shouldn't need to know what the code does with its values and the code shouldn't need to know what the database does with its values...

The short answer (again, in my opinion) - the database should be responsible for delivering clean and reliable data in a standardized format ... what happens WITH that data is up to the person/code that is requesting it.


Performance improvement after table analyze

Oracle's optimiser is clever, it makes decisions about how best to do certain things (e.g. joins) based on what the contents of the tables are.  At least, it can do if it knows what the contents of the tables are.

The optimiser uses table statistics to achieve this.  If it knows that there is a particular type of data, or a particular distribution of values, or any number of different things going on in the table, it can change the way it executes the query that's being asked of it.

If there are no statistics, the optimiser has to guess or use defaults, and while the optimiser's defaults may be suitable or not matter that much at small volumes of data, when the volume of data in the table increases or the skew changes, this can make a huge difference to how quickly a query runs.

By analysing the tables, the statistics for the tables are rebuilt, and the optimiser has up-to-date information on what the contents of the table are, and can make more informed decisions.

Have a look here (http://docs.oracle.com/cd/E25054_01/server.1111/e16638/stats.htm) for Oracle's documentation on optimiser statistics.


Run SQL Job monthly at day 16. If it's a weekend, run it in the next business day ( dont care if is a holiday)

It's not beautiful, but you could wrap all of your conditions into a CASE like so:
DECLARE @IsGood bit;
SET @IsGood = SELECT CASE WHEN DAY(GETDATE()) = 16 AND DATEPART(WEEKDAY, GETDATE()) NOT IN (1, 7) THEN 1
                          WHEN DAY(GETDATE()) IN (17, 18) AND DATEPART(WEEKDAY, GETDATE()) = 2 THEN 1
                          ELSE 0 END;

IF @IsGood <> 1
    RAISERROR ('Nope. Not today.', 16, 1);

Throw that into a scheduled job that runs on the 16th, 17th, and 18th of every month, and you'll be good.


SELECT in 2 merged 3-Tables relation with IN() clause and a COUNT for each relation

You're multiplying the joins, and you get what you ask for.  Scalar selects may be what you want:

select X.id, X.sda + X.sdb sumsummerdesum from (
SELECT d.`id`  id,
 (select COUNT(da.`doc_id`)
  from `docs_scod_a` da,  `scod_a` a
  where da.`doc_id` = d.`id`
    and a.id = da.`doc_id`
    and a.ver_a in ( 'AA', 'AC')
 ) sda,
 (select COUNT(db.`doc_id`)
   from `docs_scod_b` db, `scod_b` b
   where db.`doc_id` = d.`id`
     and b.`id` = db.`doc_id`
     and b.ver_b in ('BA','BB')
 ) sdb
FROM `docs` d
) X

http://sqlfiddle.com/#!9/fee4c6/39

id  sumsummerdesum
1   6
2   1
3   2
4   0


Lines starting with colons in SQL Server

It's SQLCMD
What you have is a sqlcmd script.
The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt.
And sqlcmd has some sqlcmd specfic commands that start with a colon.
Some general info on sqlcmd below.

You can write SQL statements that are executed after you type GO. In the example, first the database AdventureWorks2012 is selected and after that a SELECT is executed.
-i to include scripts
However, you also have the possibility of executing a prepared script using the option -i, for example:

-v to pass in a value
You can also create scripting varibales with the notation $(name) and passing a value from the command prompt using the option -v

Apart from the command prompt, code including scripting variables can be executed from SQL Server Management Studio, by selecting SQLCMD mode in a query window.

:SETVAR
In this environment the way you can set the values of the scripting variables is using :SETVAR command

:ON ERROR
The :ON ERROR [ EXIT | IGNORE] sets the action to be performed when an error occurs during script or batch execution.
Ignore

When the IGNOREoption is used, the error is ignored and the batch or script continues executing. However, the error message is printed.
Exit

When the EXIT option is used, the execution is stopped and exists.


If you're changing the SQL Server Agent Service, make sure you observe this orientation about SQL Server Role Membership from the doc:

  The account that the SQL Server Agent service runs as must be a member
  of the following SQL Server roles:

  The account must be a member of the sysadmin fixed server role.
  To use multiserver job processing, the account must be a member of    the msdb database role TargetServersRole on the master server.

Also, the Configure Windows Service Accounts and Permissions doc should give you information about each SQL Server Service.

You already mentioned you're going to use SSCM to do the change, but since it could be useful to others, I believe it's worth mentioning that it's recommended to use the SQL Server Configuration Manager to Change the Service Startup Account instead of services.msc. Pinal Dave's article explain a few reasons for Why to Use SQL Server Configuration Manager Over Services applet (services.msc)?


SELECT gives different results with/without FOR UPDATE in same transaction

A guess:

"Repeatable Read" means that the identical SELECT will produce identical results throughout the transaction.  But, apparently, the FOR UPDATE clause is enough to make the two selects not 'identical'.

Under the covers...  There is a clock (sequence number) ticking away.  Whenever a row is modified, it is copied over.  Now there are two copies of that row, one with the old clock, one with the new.  (See "History list".)  Your first SELECT is associated with the older clock value; the second SELECT, not being identical to the first, has a newer clock value, hence linked to a different copy of the row.

After the transaction if finished (COMMIT or ROLLBACK), the extra copy of the row is tossed.  (I think this is a background task for COMMIT, but a "do it now" task for ROLLBACK.)  Meanwhile, other connections pick the appropriate row from the history list for each row based on their isolation_mode.  Roughly speaking, READ_COMMITTED goes to the old end of the list; READ_UNCOMMITTED ("dirty read") goes to the new end.

Things are optimized on the "optimistic" assumption that COMMITs are more likely than ROLLBACKs.  Hence, the comments in the previous paragraph, and the slowness of ROLLBACK.


how to cache a subset for cascading select queries in mysql

If filter 110M is exactly a subset of filter 126M, then appending more AND with WHERE would have done the job.

$sql1 = "SELECT ..... WHERE ...";
$sql2 =  $sql1 . "AND column-name  = ....";
$sql3 =  $sql2 . "AND column-name  = ....";

If that is complex to accomplish try to CREATE VIEW of the previous SELECT statement and the next SELECT statement should query from the view.


Query rows with common value over result set partition

It seems that your desired result do not use all foo values, there is no ('c',2) and ('d',2), then you can remove it from the equation and use only foobar table.

select
    barid
from
    foobar
group by
    barid
having
    count(fooid) = (select count(distinct fooid) from foobar);

| barid |
| ----: |
|     2 |

db<>fiddle here


Replace "-" with NULL with Derived Column in SSIS

First of all, there is no need to cast NULL() function (DT_R8)NULL(DT_R8) you can use it without casting. Only you will need to cast when working with DT_STR. Read more at:

https://docs.microsoft.com/en-us/sql/integration-services/expressions/null-ssis-expression
https://stackoverflow.com/questions/43455146/why-does-this-ssis-conversion-expression-syntax-the-way-it-is

The problem is that you are using a Case statement that returns 2 datatypes

[SideDoorType] == "-" ? NULL(DT_R8) : [SideDoorType]

You are comparing [SideDoorType] column to a string value "-",
if it is equal, you are trying to return a double-precision floats NULL , else you are returning [SideDoorType] column.

you can try using the following expression it might be working

if [SideDoorType] type is DT_WSTR or DT_STR, you have to cast the column in the false part expression

[SideDoorType] == "-" ? NULL(DT_R8) : (DT_R8)[SideDoorType]

if  [SideDoorType] type is DT_R8, you have to cast the column in the comparaison expression

 (DT_WSTR,50,1256)[SideDoorType] == "-" ? NULL(DT_R8) : [SideDoorType]

And if these two expressions are not working, you have to use a string dataType like the first expression you provided, because it looks like your column contains values that cannot be converted to DT_R8, or use a Script Component instead of Derived Column and write a script to clean your data before casting it to float


Reviewing Stored Procedure template for handling transaction?

It might be worth looking at XACT_STATE instead of @@TRANCOUNT.

From BOL:

  Both the XACT_STATE and @@TRANCOUNT functions can be used to detect
  whether the current request has an active user transaction.
  @@TRANCOUNT cannot be used to determine whether that transaction has
  been classified as an uncommittable transaction.

So there may be some cases where, even though there was an error, the transaction should still be committed to the database.

IF (XACT_STATE()) = -1
BEGIN
    PRINT 'The transaction is in an uncommittable state.' +
          ' Rolling back transaction.'
    ROLLBACK TRANSACTION;
END;

-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
    PRINT 'The transaction is committable.' +
          ' Committing transaction.'
    COMMIT TRANSACTION;
END;

Of course, it's entirely up to you whether you want to commit any transaction where an error was raised, even if it is committable... Just because you can, doesn't mean it's always a good idea!


I would probably go with triggers. You will be warned about performance implications and all that, but truthfully there isn't a "free" way to audit, and any option you try will have some impact on your workload. You will need to be aware that if someone updates the whole table, you will be logging that too, so be prepared for your auditing data growth to potentially outpace your actual data. Might want a separate database on isolated storage for that.

Auditing, CDC and Change Tracking would be great if they were combined, but they're not. As you've discovered, auditing shows parameter placeholders, so it tells you who changed it but not what they changed, and the other two tell you what has changed, but not who changed it or when.

I've asked for the ability to include username and date/time in CDC data, but it was declined:

http://connect.microsoft.com/SQLServer/feedback/details/283707/cdc-options-to-capture-more-data-username-date-time-etc

I also asked for date/time information to be included in Change Tracking, but similarly it was declined:

http://connect.microsoft.com/SQLServer/feedback/details/319049/change-tracking-provide-datetime-column

I wasn't aware of the auditing limitation. If I had known about it, I would have asked for it, too. And it would have been declined like the others.


MySQL column that auto generates data, longer than UUID

You can create the pair of triggers ON INSERT and ON UPDATE that perform any desired transformations 'on the fly'

CREATE TRIGGER `myuuid_autoinserter`
BEFORE INSERT ON `table` -- or BEFORE UPDATE
FOR EACH ROW
BEGIN
  SET loooonguuid = CONCAT(OLD.uuid, OLD.uuid, OLD.uuid, OLD.uuid)
  SET NEW.uuid = loooonguuid;
END

When you run some INSERT or UPDATE query on the table trigger will replace submitted value of the uuid by string stored in the loooonguuid variable.

Sure you can do as complex and randomized transformation as you want.


Abstracting away filtering behavior from Oracle SQL Function

Callbacks are totally against the idea behind SQL. It's designed as declarative language which queries describe the task and query optimizer builds composition of sorts, index seeks, merges, etc to meet the query. No any row-by-row processing by callbacks.
Oracle supports overloading, with restrictions http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1738 What do you men it doesn't work? You must place overloaded subprogramms into packages, but it's very reasonable way of bulding Oracle-side soft in any case regardless to overloading. But I agree, it hardly can help with countless filter options.

Yes, application is the best place to build the SQL query depending on multiple user choises (filter by price and color, or by year and brand, ...).


sync MS SQL Server 2014 and Express 2014 Database using Stored Procedure

I fully expect down-votes on this, but deprecated doesn't mean discontinued.  When a feature is deprecated it generally means that said feature won't be expanded and will likely be removed in a future release.  According to MS documentation RMO doesn't show up under features that were discontinued in either SQL 2014 or SQL 2016, but again I wouldn't count on it being available going forward.

The point here is if you've already got something that works using RMO with SQL 2012, see if it works in 2014 and continue using it.  No point to reinvent the wheel if you don't have to.  Who knows, in 5-years-time you may no longer use this application, this back-end, or even this approach.  It's also entirely possible that RMO could turn into something similar to the sysindexes view that was deprecated way back in SQL 2005 but is still in SQL 2017 by the looks of it.

In terms of an official "supported" approach, Merge Replication, as stated by Bartosz X in the comments is your best bet, based on what it sounds like you want to accomplish.  Some additional searching on DBA.SE or Google should get you going.


UPDATE Blocking SELECT Of Unrelated Rows

I might have to modify this answer once you provide more information based on my comment.

For now follow this example.  I am using SQL 2016, SP1.

--Creating table
    DROP TABLE IF EXISTS TableA
    CREATE TABLE [dbo].[TableA](
      [col1] [int] NULL,
      [col2] [nchar](10) NULL,
      [col3] [int] NULL
    ) ON [PRIMARY]

   GO

Inserting data:

INSERT INTO [dbo].[TableA]
           VALUES (10, 'AAA', 11),
                  (12, 'BBB', 13),
                  (14, 'CCC', 15)

Session ID 65 running this.

BEGIN TRANSACTION
UPDATE TableA
SET Col3 = 0
WHERE Col2 = N'AAA'

Session ID 66 running this:

SELECT *
FROM TableA
WHERE Col2 = N'BBB'

Looking at the locks held by both session. Session 65 is holding an exclusive lock on file id:1, page id:488 and slot id=0, shared lock is requested on the same resource by session 66 and is waiting.

Running the 2nd query and looking at the actual execution plan it is doing a table scan and because of your isolation level reading requires a shared lock to prevent any update or delete on the same row.

Creating an index on col2 does not change the behavior in my case because of number of rows.

CREATE NONCLUSTERED INDEX [NCI_TableA_col2] ON [dbo].[TableA]
(
    [col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON,
DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

After I inserted 3000+ rows I can get a index seek and session 66 will not wait for the session 65 anymore.

Why did your 3rd query ran without waiting: I can answer that once you provide more information.


Is it possible to use a temp table in a data flow source?

These steps helped me:

Write the final result set into a table.
Script that table as CREATE into a new New Query Editor Window.
Remove everything except the open and close brackets that define the columns.
Wrap that into another pair of brackets.
Recompose the calling of your SP from

exec p_MySPWithTempTables ?, ?

into

exec p_MySPWithTempTables ?, ? with result sets
(
    (
        ColumnA int,
        ColumnB varchar(10),
        ColumnC datetime
    )
)


Reducing column size and data type in mysql

Yes, it will. When there's no load on the table, I would suggest following procedure though. Not only can you double-check if everything is okay, it probably is also faster.

CREATE TABLE new_adv LIKE adv;

ALTER TABLE new_adv
  MODIFY COLUMN id VARCHAR(80),
  MODIFY COLUMN index INT(11);

INSERT INTO new_adv SELECT * FROM adv;

RENAME adv TO old_adv, new_adv TO adv;
/*when everything is okay...*/
DROP TABLE old_adv;

And by the way, it's a good idea not to use keywords like index for column names. You might want to change that, too. And if it's just for the benefit of not having to use backticks all the time when you use this column.


How to avoid warning with file system cache in mongodb?

Any ideas how to avoid warning?

This warning was added for some older implementations of MongoDB's WiredTiger storage engine, but is longer relevant in MongoDB 3.6. The warning has been removed in MongoDB 4.0 and the removal has been backported for the upcoming MongoDB 3.6.6 server release. See SERVER-33706 for more background.

You can ignore this warning until you upgrade to a version of MongoDB newer than 3.6.5.

  Then getting the error Unrecognized option: storage.inMemory.engineConfig.inMemorySizeGB

This configuration option is for the In-Memory Storage Engine which is included in MongoDB Enterprise, and not relevant to the filesystem cache warning. The option will not be recognized unless you are running MongoDB Enterprise server.


How can I calculate the loan balance based on following data set

You can use SUM() function  with OVER clause (see here for more details)

Key point here is the window frame:
ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW
This is saying to include into calculation , the rows from start (UNBOUNDED PRECEDING) till the current row.

  need to calculate till maturity date
  for this you can add a Case expression

;with cteSource AS
(
     SELECT 12345 AS Account_Number ,'12/09/2016' as Maturity_Date, '12/05/2016' as Interest_Charged_Date,0 as  Transaction_Amount , 100 As Balance UNION ALL
     SELECT 12345  ,'12/09/2016' , '12/06/2016' ,28.77  , 0 UNION ALL
     SELECT 12345  ,'12/09/2016' , '12/07/2016' ,-28.83  , 0  UNION ALL
     SELECT 12345  ,'12/09/2016' , '12/08/2016' ,28.77  , 0  UNION ALL
     SELECT 12345  ,'12/09/2016' , '12/09/2016' ,28.77  , 0 UNION ALL
     SELECT 54321  ,'28/08/2016' , '28/06/2016' ,0  , 250  UNION ALL
     SELECT 54321  ,'28/08/2016' , '28/07/2016' ,-17.25  , 0  UNION ALL
     SELECT 54321  ,'28/08/2016' , '28/08/2016' ,17.18  , 0
)

SELECT
    *
    ,SUM(case when Interest_Charged_Date < = Maturity_Date then Balance + Transaction_Amount else 0 end) OVER (PARTITION BY Account_Number
                       ORDER BY Interest_Charged_Date ASC
                       ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW) AS NEW_Column
    ,SUM(Balance + Transaction_Amount) OVER (PARTITION BY Account_Number
                           ORDER BY Interest_Charged_Date ASC
                           ROWS BETWEEN UNBOUNDED PRECEDING  AND CURRENT ROW) AS NEW_Column2
FROM
    cteSource;

output :

 Account_Number Maturity_Date Interest_Charged_Date Transaction_Amount Balance     NEW_Column   NEW_Column2
12345          12/09/2016    12/05/2016            0.00               100         100.00       100.00
12345          12/09/2016    12/06/2016            28.77              0           128.77       128.77
12345          12/09/2016    12/07/2016            -28.83             0           99.94        99.94
12345          12/09/2016    12/08/2016            28.77              0           128.71       128.71
12345          12/09/2016    12/09/2016            28.77              0           157.48       157.48
54321          28/08/2016    28/06/2016            0.00               250         250.00       250.00
54321          28/08/2016    28/07/2016            -17.25             0           232.75       232.75
54321          28/08/2016    28/08/2016            17.18              0           249.93       249.93

dbfiddle here


Fulltext searching on shared hosting - 2000 Entires with around 500 words each

I often query a 170K-row table using InnoDB FULLTEXT.  (Avg: 300 words.)  I typically get the answer of a few dozen rows in under 200ms.  That 200ms drops to below 20ms if the cache is warm.

I don't think the number of documents in the table matters as much as the number of documents returned.  If you need to fetch the text for 100 documents (after searching the index), that could take a full second to randomly fetch from a cheap disk.

Beware of benchmarks; they may be delivering only document ids, not the text; and they may be assuming everything is cached in RAM.  Since you have only a few MB, the caching is a valid assumption for you.


Is an X lock on an "application" a bad thing?

These locks are created by a call to sp_getapplock.

It will only cause blocking for other processes that also call sp_getapplock and request the same lock resource.

You could poll sys.dm_os_waiting_tasks to see if anything is ever blocked waiting for this but any such blocking would probably be desirable anyway as presumably the whole point of taking the lock is precisely to ensure this mutual exclusion.


The SQL Server optimizer cannot produce the execution plan you are after with the guarantee you need, because the Hash Match Flow Distinct operator is not order-preserving.

Though, I'm not sure (and suspect not) if this truly guarantees ordering.

You may observe order preservation in many cases, but this is an implementation detail; there is no guarantee, so you cannot rely on it. As always, presentation order can only be guaranteed by a top-level ORDER BY clause.
Example
The script below shows that Hash Match Flow Distinct does not preserve order. It sets up the table in question with matching numbers 1-50,000 in both columns:
IF OBJECT_ID(N'dbo.Updates', N'U') IS NOT NULL
    DROP TABLE dbo.Updates;
GO
CREATE TABLE Updates
(
    UpdateId INT NOT NULL IDENTITY(1,1),
    ObjectId INT NOT NULL,

    CONSTRAINT PK_Updates_UpdateId PRIMARY KEY (UpdateId)
);
GO
INSERT dbo.Updates (ObjectId)
SELECT TOP (50000)
    ObjectId =
        ROW_NUMBER() OVER (
            ORDER BY C1.[object_id])
FROM sys.columns AS C1
CROSS JOIN sys.columns AS C2
ORDER BY
    ObjectId;

The test query is:
DECLARE @Rows bigint = 50000;

-- Optimized for 1 row, but will be 50,000 when executed
SELECT DISTINCT TOP (@Rows)
    U.ObjectId
FROM dbo.Updates AS U
WHERE
    U.UpdateId > 0
OPTION (OPTIMIZE FOR (@Rows = 1));

The estimated plan shows an index seek and flow distinct:

The output certainly seems ordered to start with:

...but further down values start to go 'missing':

...and eventually:

The explanation in this particular case, is that the hash operator spills:

Once a partition spills, all rows that hash to the same partition also spill. Spilled partitions are processed later, breaking the expectation that distinct values encountered will be emitted immediately in the sequence they are received.

There are many ways to write an efficient query to produce the ordered result you want, such as recursion or using a cursor. However, it cannot be done using Hash Match Flow Distinct.


Best way to store information about a list of materials and their quantity

No you would have a intermediate table, so you can have a many to many realtionship.
And a material can be also a product of other materials
Building            Building_Material         Material
---------------------------------------------------------
Building_id         Building_id               Material_id
Name                Material_id               Name
                    Quantity                  Price


Query to select distinct name based on several factors

Assuming the data is constrained to avoid duplicates, how about something like this?  This is assuming Microsoft SQL Server syntax.

SELECT United.PolicyName FROM
(SELECT PolicyName FROM dbo.Policies
WHERE Setting = 'DiskUsage'
  AND State = 'Enabled'
  AND Value = 1
UNION ALL
SELECT PolicyName FROM dbo.Policies
WHERE Setting = 'Memory'
  AND State = 'Enabled'
  AND Value = 1
) AS United
GROUP BY United.PolicyName
HAVING COUNT(*) = 2

RESULTS: Default

This specifically queries for each of the states that you care about.  Since there are two requirements, then the UNION will produce two rows when both selects are find a row.


Is it possible to import a single mysql database directory?

OK, spurred on by RolandoMySQLDBA's comment (I didn't know what to look for earlier), I was able to solve this.

Ultimately, I used this blog post to recover the table data from the .frm files.

Then, I used the technique here to import the ibd data.


SQL Server: Determine backup location from physical_device_name field

Does this StackOverflow answer point you in the right direction? Specifically if you look at the device_type column from the backupmediafamily table, what do you see? You can find the list of types in the API doc:

2 = Disk
5 = Tape
7 = Virtual device
9 = Azure Storage
105 = A permanent backup device.

I'm almost wondering if that's a CNAME or some sort of name reference to the actual path, but it may be dependent on the above.
This is likely the relevant part from the above answer to what you're seeing:

You should notice that these backups have value 7 in the column "device_type" (7 means Virtual device). These rows are actually very useful, for example it help to know that the Virtual Machine backups are running full database backups on a SQL Server instance.

There's no trace of these backup files since they are above the scope of the virtual machine (above the level of your machine) - these are triggered by the host (Hyper-V or VMware for example).


I think first you can load infile to MEMORY temporary table. And then just:

INSERT INTO tname (id, status) SELECT id, status FROM tname_temp
  ON DUPLICATE KEY UPDATE tname.status=tname_temp.status;
TRUNCATE TABLE tname_temp;

INSERT ... SELECT syntax reference.

Of course id field has to have unique key and your csv files has to fit in memory.

MEMORY engine tables are very fast so you should have just tiny overhead to load data in two steps. As one of drawbacks they have just table level locks so their use is limited. But that does not impact this data load scenario.


Update: I found that connecting to AWS failed too which led me to yet another Google search which led me here: https://www.sqlservercentral.com/Forums/1891503/Unable-to-connect-to-SQL-2008-Servers-from-SSMS-2017

Following the instructions from the last post in the referenced site, I downloaded IISCrypto and enabled all of the Ciphers. Bingo.

https://www.nartac.com/Products/IISCrypto/Download

I'll leave my previous answer below in case it brings comfort to anyone who went through sleepless nights and a couple of keyboards.

Old Update - After installing SSDT 15.8.2 the solution below stopped working for me :(

After days of:

MS Azure SQL Support - not helpful (they just referred me to this post. The one I made.)
Uninstalling SQL Server Express/Developer and three different versions of SSMS - all with the same results
Creating a VM on my computer with Win 10 Pro - SSMS worked on it no problem
Wiping the computer and starting from absolute nothing (using the MSI recovery) - still same issue
Inspecting TLS packets to ensure version 1.2 - it was 1.2
Trying to connect with SqlPackage.exe - never worked for me, same error as SSMS
Trying to connect with SQLCMD - it worked!
Trying to connect with SQL Native Client - also worked!

... I did finally get it to work. Good news is that it works. Bad news is that I don't know exactly why.

I currently have a fresh Win 10 Pro install with SSMS 17.9.1. I do not have any SQL Server instance installed. My default Windows Firewall is enabled and Norton AntiVirus is managing it (default with the computer). All Windows Updates have been applied as of this writing. I also downloaded and installed the SQL Server 2012 Native Client https://www.microsoft.com/en-us/download/confirmation.aspx?id=50402

When it started working, I had just used SQLCMD and connected to the Azure SQL Server master database and was querying the sys.event_log table. Immediately before that, I had used SQLCMD to connect to a different database on that same server. When I didn't see anything in the sys.event_log table I went back to SSMS and it worked for all of my servers (we have three).

TL;DR Connect with SQLCMD for a while and see if that helps??


You can query the default trace to get information about recent growth events.

DECLARE @path nvarchar(260) = (
    SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) +'log.trc'
    FROM    sys.traces
    WHERE   is_default = 1)

SELECT gt.DatabaseID,
       gt.FileName,
       COUNT(*) AS NumberOfEvents,
       CASE WHEN te.name LIKE'%Grow' THEN 1 ELSE 0 END AS is_growth_event
FROM  sys.fn_trace_gettable(@path, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE   te.name in ('Data File Auto Grow','Log File Auto Grow','Data File Auto Shrink','Log File Auto Shrink')
GROUP BY gt.DatabaseID,
       gt.FileName,
       te.name

You can also use Event Notifications to be notified on DATA_FILE_AUTO_GROW,LOG_FILE_AUTO_GROW without having to poll the trace.


Is using indexed view with no expand on standard edition a must?

Why the behavior is not the same in all editions as we have free
  workaround?

You have no workaround in case when the poorly performing query does not use any wiev at all.

You can have a situation when some application does some aggregations and with the large volumes of data it became slowly. In case of Enterprise Edition you can create an indexed view that query optimizer could be able to use for this query even if a query does not access any view at all. And you cannot use this approach in other editions as you have no control on the application source code.

This case is described in Paul White's article that you mentioned about:

  Enterprise Edition and View Matching

  On an Enterprise Edition instance, the query optimizer may be able
  to use an indexed view even if the query does not mention the view
  explicitly. If the optimizer is able to match part of the query tree
  to an indexed view, it can choose to do so, based on its estimation
  of the costs of using the view or not. The view-matching logic is
  reasonably clever, but it does have limits that are pretty easy to hit
  in practice. Even where view matching is successful, the optimizer can
  still be misled by inaccurate cost estimations.


MySQL multiple rows with same id meeting two different conditions at same time

This isn't going to be a query that scales very well, no matter which approach you take, since text matching anywhere in a column except at the left edge of an indexed column will require a full table scan.

I'm not familiar with OTRS ... I downloaded it and looked at the source scripts that create the database schema and it looks like the column you're querying is the result of denormalization and they're using the %% symbols to do language localization... so querying the way you're doing it is not likely to be, as some people like to say, very "performant."

Bear in mind also that % is a wildcard character for the MySQL LIKE operator, so what you're actually matching on, as written, could lead to false positives, because your expression is evaluated by MySQL as trying to match *raw*[space]*new* and *close.

What you appear to be actually searching for is %\%Raw\%% %\%new\%% where % means anything and \% means a literal %, not a wildcard.  Also, you want %\%Close, or possibly %\%Close% if the '%Close' can be found anywhere other than at the end of a line.

Importantly, I suspect there is a better way to get the data you are looking for from OTRS, so you should review the schema and consider whether this is a possibility.  For example, if you are looking for tickets that have been closed, it looks like to me that this might be tickets WHERE ticket.ticket_state_id = 2 or possibly tickets WHERE ticket_history.state_id = 2?  Similarly, wouldn't a ticket with ticket_history.queue_id = 2 mean "Raw"?

Those literal "2" values may not be correct, depending on your version and precisely how OTRS uses its database, but understanding the schema and querying the database in a more optimizer-friendly fashion will get you a substantially better-performing query than searching every row of the table with LIKE.

As your query is written, and also in the example below, every row of the ticket_history table will have to be scanned at least once, no matter what you do.

So I would not begin to suggest that the following is "the right way to do it" but it adds in the logic your query is missing.  If a row contains one pattern, we verify whether the ticket_history contains a row for the same ticket that matches the other pattern, and if so, we keep that row ... and if not, we discard it.

SELECT `ticket`.`id`,
       `th`.`ticket_id`,
       `th`.`id`,
       `th`.`name`,
       `th`.`create_time`
  FROM `ticket_history` th
  JOIN `ticket` ON  `th`.`ticket_id` =  `ticket`.`id`
 WHERE (
        `th`.`name` LIKE  '%\%Raw\%% %\%new\%%'
        AND EXISTS (SELECT * FROM ticket_history th1 WHERE th1.ticket_id = th.id AND th1.name LIKE '%\%Close')
       )
    OR (
        `th`.`name` LIKE  '%\%Close'
        AND EXISTS (SELECT * FROM ticket_history th2 WHERE th2.ticket_id = th.id AND th2.name LIKE '%\%Raw\%% %\%new\%%')
       )
 ORDER BY `ticket_history`.`ticket_id`, `ticket_history`.`id` ASC;


You may need to check out the configuration of InnoDB.

You can start by running

SHOW VARIABLES LIKE 'have_innodb';
SHOW ENGINES;

These will indicate whether or not InnoDB is running properly.

If InnoDB is disabled, it is more than likely that the log files need to be recreated.

As long as you keep ibdata, delete ib_logfile0, delete ib_logfile1, and set innodb_log_file_size to 170M in my.ini, you should be able to start mysql and have mysql rebuild new ib_logfile0 and ib_logfile0 to 170M each as suggested by the article you mentioned in the chat room.


as you know the PK from child tables, the joins are pointles.

even with joins, the difference between #tmpA and TableA is zero, may even be maybe in favor of TableA, depending. Your first select does the physical read if needed into memory, then its only logical reads and those would have to be done with temp table as well, except for temp table you need to crete it and insert the data


Scaling and Reducing Reads On A Similar Query Filter

As Aaron pointed out a CTE is a tool for developers to reuse code. It will not encourage the SQL Server query optimizer to spool the results to disk so they can be reused.

You're talking about trading space for time. It's very difficult for anyone to issue a statement that will be a best practice for all scenarios. The benefits of using a temp table here depend on the table size, which indexes are defined on the table, the workload, the workload on tempdb, etc.

For an example of when using a temp table is clearly better, consider a billion row table with no indexes. Out of those billion rows none of them match the initial filter condition. You don't use any space but avoid four full table scans of the large table.

For an example of when using a temp table is clearly worse, suppose that there is a heavy workload on the server and space in tempdb is limited. Your query could fail or cause other queries to fail if it loads too much data to a temp table.

My advice would be to start with the simplest code or the code that makes the most sense to you and to measure the performance of it. Depending on the results consider adding indexes, using a temp table, or other tricks to speed up the performance of the queries.

In the systems that I work on its very common for different users to run similar or sometimes even identical queries. I'm sure that if we took all of the queries after the fact it would have been possible to create temp tables that would have decreased overall server resource utilization. However, SQL Server is designed to deal with this kind of workload. Pages from tables that are needed for queries are loaded into the buffer cache. Subsequent queries on the same tables will often run faster because data is already in memory.


Yes, influx provides continuous queries(CQ).

Continuous queries (CQ) are InfluxQL queries that run automatically and periodically on realtime data and store query results in a specified measurement.

Syntax

CREATE CONTINUOUS QUERY <cq_name> ON <database_name>
BEGIN
  <cq_query>
END

where cq_query is

SELECT <function[s]> INTO <destination_measurement> FROM <measurement> [WHERE <stuff>] GROUP BY time(<interval>)[,<tag_key[s]>]

Example:

CREATE CONTINUOUS QUERY "cq_basic" ON "transportation"
BEGIN
  SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
END

P.S One thing needs to be noted here is that, the fields created into the new measurement are prefixed with the function name. In above case, mean_passengers would be inserted as column name. To overcome this you can use 'AS'.

Refer this link for detailed https://docs.influxdata.com/influxdb/v1.7/query_language/continuous_queries/


How can I create rows without an underlying table in IBM DB2 SQL?

Platform and version are important when asking about Db2...

In this case it appears you're running Db2 for IBM i (iSeries, AS/400)

What you are looking for is known in the SQL standards as table value constructor which looks like VALUES (<row1>),(<row2>),(...)

On Db2 for i (and LUW I believe) the following are a couple of ways...

-- Using a Common Table Expression (CTE)
WITH X(foo, bar, baz) AS (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) SELECT * FROM X;

-- Using a Nested Table Expression (NTE)
SELECT * FROM TABLE (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) X(foo, bar, baz);

However, you appear to be using an older version of Db2 for i (v5?)  I don't recall if that version supports table value constructors...


SQL-Server - Grant user DBO permission on database

User principals must exist in a database before you can grant them permissions.

use Restored_Prod
GO

CREATE USER [chris] FROM LOGIN [chris];
exec sp_addrolemember 'db_owner', 'chris';
GO

http://technet.microsoft.com/en-us/library/ms173463.aspx


SQL Server remote connection to 2nd instance impossible

The solution was to restart the SQL Server Browser. Looks like my configuration was correct but has to be made before starting this service. After that I could access the 2nd Sql Server without problems, just using the name\instance.


Changing startup accounts on alwayson setup

If you have local accounts and an AG then you must be using mirroring certificates - is this correct? If so then your switchover plan is likely going to be fine because nothing is going to change from the AG's point of view.

I have done something similar just recently to replace local accounts and expiring AG mirroring certificates with service accounts and using that Windows authentication. After the local accounts were in place I ran Alter Endpoint Hadr_Endpoint For Database_Mirroring (Authentication = Negotiate) on both sides, followed by Alter Endpoint Hadr_Endpoint State = Stopped on each, and then started them again. I monitored the endpoint status from their DMVs to make sure they started and checking the ERRORLOG for issues, and confirmed that the AG dashboard was healthy. The final test to confirm it is a failover.

When you say using agent proxies didn't help though - are you sure you used one properly? What happened? I really would have thought it would work.


Possibly, you could catalog the databases under a unique alias for each node:

db2 catalog db DBASE as DBASE1 at node DB2HOST1
db2 catalog db DBASE as DBASE2 at node DB2HOST2


tmp_table_size with mostly InnoDB tables

Because your mysql is still creating disk tables:
[!!] Temporary tables created on disk: 81% (2K on disk / 2K total)

Looking at the source of tuner-script

$mycalc{'pct_temp_disk'} = int(($mystat{'Created_tmp_disk_tables'} / $mystat{'Created_tmp_tables'}) * 100);

*snip*

if ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} < 256*1024*1024) {
    badprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}% (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_tables'})." total)\n";
    push(@adjvars,"tmp_table_size (> ".hr_bytes_rnd($myvar{'tmp_table_size'}).")");
    push(@adjvars,"max_heap_table_size (> ".hr_bytes_rnd($myvar{'max_heap_table_size'}).")");
    push(@generalrec,"When making adjustments, make tmp_table_size/max_heap_table_size equal");
    push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");
} elsif ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} >= 256*1024*1024) {
    badprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}% (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_tables'})." total)\n";
    push(@generalrec,"Temporary table size is already large - reduce result set size");
    push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");

So there's tuner's logic, if the (Created_tmp_disk_tables/Created_tmp_tables)*100 is more than 25(%) then increase your tmp-table size (upto max 256M).

Again, mysqltuner is for reference, if you don't see  performance issues you don't need to blindly follow the suggestions.

Also note that tmp table has nothing to do with InnoDB vs MyISAM (if you mean that in your last line). You might want to read about internal temporary tables.


Will increased network latency cause table locks in MS SQL Server?

If client takes long time to receive data and in turn send
  acknowledgement to SQL Server that it has received the data SQL Server
  has to wait, due to this wait SQL Server will not release the locks
  held by the query unless acknowledgement is received from client.

This is not accurate, it is dependent on the isolation level.

At the default READ COMMITTED locks are not held for the duration of the statements execution. READ COMMITTED does not provide statement level read consistency, the only guarentee is that you cannot read uncommitted data. A shared lock is acquired and held to read the row and then released.

Unless you have LOB types.

LOB types, being potentially very large, cannot be buffered. A shared lock must be acquired and held until the statement completes, essentially giving you REPEATABLE READ behavior at READ COMMITTED.

  If I'm making a single call to an MSSQL database over a high-latency
  network, will table locks occur due to that latency?

The latency isn't causing the table lock, no. However, if a table lock has been acquired the latency is going to prolong it.

To quote someone that knows the mechanics of this better than I (@RemusRusanu):

  Results are returned back to the client program as the execution
  proceeds. As rows ‘bubble’ up the execution tree, the top operator is
  usually tasked with writing these rows into network buffers and
  sending them to back to the client. The result is not created first
  into some intermediate storage (memory or disk) and then sent back to
  the client, instead it is sent back as is being created (as the query
  executes). Sending the result back to the client is, of course,
  subject to the network flow control protocol. If the client is not
  actively consuming the result (eg. by calling SqlDataReader.Read())
  then eventually the flow control will have to block the sending side
  (the query that is being executed) and this in turn will suspend the
  execution of the query. The query resumes and produces more results
  (continue iterating the execution plan) as soon as the network flow
  control relieves the required network resources. [source]

Where results aren't consumed as quickly as SQL Server can deliver them, be it due to the client or the network, we see ASYNC_NETWORK_IO waits accumulating. To reiterate, this will not influence the locks that are acquired, just the duration they are held.


Warning about memory "Excessive Grant" in the query plan - how to find out what is causing it?

The most common two types of operators that consume memory are:

Sorts
Hashes

If a plan is parallel, memory requirements will go up some amount to compensate for exchanges for threads to pass rows through. Parallel plans do not require the entire serial memory grant * DOP (though there may be a relationship between serial required memory and DOP). The full grant is split and (hopefully) used evenly across all threads in the plan.
In some cases, Nested Loops Join may ask for memory as well.
Memory grants are calculated by the optimizer based on the number of rows, and the size of the data that will pass through memory consuming operators. As above, if the plan is parallel, it may ask for more. Another factor is that memory consuming operators can share memory.
For instance, some memory consumed by a sort can be passed to upstream operators after data is sorted, and hashes may pass memory upstream after the initial build phase is complete, and probed rows start moving upstream. This can be observed via the Memory Fraction information.
In your plan, you have three Sort operators.

Which, combined, the optimizer thinks it'll need 2 MB of memory to run without spilling to disk. It ends up only needing 24 KB, hence the warning.
Memory grant mis-estimates can come from many places. In your query, you have a single variable: @arrivalDate.
It's not clear if this parameter is within a stored procedure, or if you're calling it locally. In either case, you might try a recompile hint to see if that removes the warning by getting a different cardinality estimate.
If that doesn't work, you may want to try adjusting indexes so that separate sort operations aren't necessary, but that may be more complicated than is worthwhile for such a small amount of memory.
For reference:

Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)

Troubleshooting Parameter Sniffing Issues the Right Way: Part 1 (links to other parts are provided in the first post)

Yet Another Post About Local Variables


How do you use COLUMN_JSON with COLUMN_GET on dynamic columns in MariaDB?

So I figured out how to do this.

In the first part of the SELECT, is where you would do the COLUMN_JSON portion and the COLUMN_GET is done in the WHERE section.

Example;

SELECT COLUMN_JSON(attr) as 'attribute' FROM items WHERE COLUMN_GET(attr, 'name' AS CHAR) like 'name%' ;


Simply add or subtract a number of days, e.g.:

update fauactivity
set timeout = timeout - interval 1 day,
    timein = timein - interval 1 day
where ...


Restore all applicable files in a backup device in T-SQL

This procedure should do what you want:

create procedure dbo.dbrestore
@dbname nvarchar(50)
as
declare @fileno integer
declare @dumpdevice nvarchar(50)
declare @mediasetid integer
declare @position integer

select top(1) @dumpdevice=a.logical_device_name, @mediasetid=b.media_set_id, @position=b.position from backupmediafamily a inner join backupset b on a.media_set_id=b.media_set_id
where b.database_name='backuptest' and b.type='D' order by a.media_set_id desc, b. position desc

declare restore_cursor cursor for
select position from msdb.dbo.backupset where database_name=@dbname and type='D' and media_set_id=@mediasetid and position=@position
union
select  max(position) as position from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position
union
select position from msdb.dbo.backupset where database_name=@dbname and type='L' and media_set_id=@mediasetid and position>@position
and last_lsn>(select max(last_lsn)from msdb.dbo.backupset where database_name=@dbname and type='I' and media_set_id=@mediasetid and position>@position)
order by position asc;

open restore_cursor
fetch next from restore_cursor into @fileno
while @@FETCH_STATUS = 0
begin
--comment the print statement and uncomment the exec statement to run
--exec('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+@fileno+';')
  print ('restore database ['+@dbname+'] from '+@dumpdevice+' with norecovery, replace, FILE='+convert(varchar(4),@fileno)+';')
fetch next from restore_cursor into @fileno
end
close restore_cursor
deallocate restore_cursor
--exec('restore database ['+@dbname+'] with recovery;')
go

execution goes:

get the name of the dumpdevice based on the supplied database name. I'm assuming that they are all on the same device. The top is to work around having multiple media sets per database if using 'with format' for the initial DB, and to cope with the possibility of multiple full backups in the device

Then build up a union query consisting of:

The last full database backup (type=D) in the device

the last differential backup (type=I and has the greatest LSN (Log Sequence Number)) taken after the last full db backup.

the transaction logs (type=L) taken after the above diff.

returning just the position value, which maps to the FILE value in this case (http://msdn.microsoft.com/en-us/library/ms186299.aspx)

Loop through the result restoring them individually, and then a final restore with recovery to finish it off.

Call it as:

exec dbrestore 'db2restore'

I've tried it on a test db and it appears to work happily, and comparing it to the files SSMS wants to restore for the same operation it looks the same.

Provided as is, there's probably typos, I've probably missed a possible boundary condition, and there's no error checking.


NULL means that there is no value for that column of the record and it does not equal anything. So in both of your queries nothing can equal NULL.

Wiki Entry on Null

  The Greek lowercase omega (ω) character is used to represent Null in database theory.
  Null (or NULL) is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. In SQL, NULL is a reserved word used to identify this marker.


SQL Error [1242] [21000]: Subquery returns more than 1 row (AMATEUR)

You should be joining between each table on the fields you're trying to compare in the WHERE clause. Then your  statement can be on top of that.


Does MySQL/InnoDB use table caches when dealing with foreign tables?

Let's see if you have issues with table_open_cache:

SHOW VARIABLES LIKE 'table%cache';
SHOW GLOBAL STATUS LIKE '%open%';
SHOW GLOBAL STATUS LIKE 'Up%';

How many tables do you have in the system (not just the one database)?

FOREIGN_KEYs need to reach into an index of the 'other' table.  This is usually the cost penalty for FKs.  However all such accesses are cached in the innodb_buffer_pool, so it is often not a disk hit.


How to compare (in MySQL) a DATETIME value to a TIMESTAMP value?

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.


Stock price database optimization in MySQL

Storing the data in one table is not necessarily faster.  If you move company name and industry out of the table, each row will be smaller.  Smaller rows means more rows per data page.  And that means fewer data pages.

You can have a situation where the rows with the company name would be larger than memory.  But the pages without the company name would fit in memory.  Clearly, the latter situation will result in faster queries.

However, to even begin answering questions about performance, one needs information about the types of queries being run.  This will inform not only the table structure, but also the indexes and data types that might be necessary.


phpmyadmin - Login issue, takes me back to the login page after logging in

Turns out the issue was memcache. I'd added it for other reasons and set PHP to use it for sessions. Nothing broke (or so I thought) and all seemed well. Turns out it's this very reason phpMyAdmin was not working. phpMyAdmin does not support memcache being used for sessions in PHP, it requires files.


Conversion fails when converting date average to date in sql

This query:

gets the number of days between each of your dates and 2000/01/01.
calculate the average number of days
add the average back to 2000/01/01

SELECT
    DATEADD(DAY
        , AVG(
            DATEDIFF(day, '2000/01/01', OnMarkDate)
        )
        , '2000/01/01'
    )
FROM @tbl_XMLResult;

Output:

2002-07-30 00:00:00.000

Return type of DATEADD is datetime when a string is used. You can either:

cast the string to date
use a variable with a date type
cast the output back to date (ie. remove the time part).

Your data:

DECLARE @tbl_XMLResult TABLE(OnMarkDate date);
INSERT INTO @tbl_XMLResult(OnMarkDate) VALUES
    ('2001-10-11')
   , ('2001-10-03')
   , ('2001-09-24')
   , ('2000-05-31')
   , ('2001-10-15')
   , ('2008-01-01');


PITR restores forward, not backward.  You must use a base backup which was taken before the point in time to which you want to recover.


Handling exceptions from inside a function

In a PL/pgSQL function (which you are using, but the language declaration is missing), use an EXCEPTION clause in your block.

I also consolidated your two SQL statement with variable declaration and assignment into a single, equivalent statement, which is typically much faster and less susceptible to race conditions in a multi-user environment. Details in the manual about UPDATE.

CREATE OR REPLACE FUNCTION prod_profiles_check_grouped_item()
    RETURNS trigger AS
$func$
BEGIN

   UPDATE public.prod_profiles_steps_items i
   SET    item_id = m.group_id
   FROM   public.inv_items_group_members m
   WHERE  m.item_id = NEW.item_id
   AND    i.item_id = NEW.item_id;

   RETURN NEW;

EXCEPTION WHEN UNIQUE_VIOLATION THEN
-- This catches *any* unique violation in the function. Use a nested block,
-- if you have more candidates in the same function and want to narrow it down.
   DELETE FROM public.prod_profiles_steps_items
   WHERE  item_id = NEW.item_id;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;  -- language declaration required!

Related answers with more code examples:

PostgreSQL stored procedure to return rows or empty set
Issue with composite type in an UPSERT function


Access .sql script files on other partition or hdd

Turns out this was much easier to fix than I though. Being completely new to both SQL and postgres I foolishly assumed that psql had something to do with the problem. As it turns out the directory /media/user in which Storage is mounted did not have Read & Write permissions set for other users, so even though I would have been allowed to access the file inside Storage I could not get these.

Changing the permission in /media/user solved the problem. In the future I'll make sure that all directories in a path are accessible to me.

Thanks again for the help and advice.