Does the order of a mysql multi-column MySQL index matter - if you're always using all columns in the query? I'm aware that when you define a MySQL index that the order of the fields matters if the query is only using part of the index, as described here. But if your query is always going to use all columns defined in the index, then does index column order still not matter?

For example, consider a table with these columns:

name
gender
age

Any my query of:

SELECT name WHERE gender='M' and age<18

Will there be a difference in performance if I define the index as (gender, age) vs (age, gender) ? Especially considering there will be one field with more unique values than the other.

I've read some tips on Percona's blog here that indicate you should order your index columns on the most selective columns being first, but that's the only place I've heard that.

Anyone know? I'm using the innodb table type.

BONUS: Same question if your index is the primary key. Does it affect the answer?

Thanks so much for your input!

In your case it's a choice between bad and worse. gender is low selective (read MySQL has to read at least a half of index), if age comes first, gender part won't be used at all (because of < ). (gender, age) seems to be a bit better anyway.

Check with SHOW STATUS LIKE 'Handler%' which index makes MySQL to read less rows.

mysql> FLUSH STATUS;
mysql> SELECT name WHERE gender='M' and age<18;
mysql> mysql> show status like 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 38    |
| Handler_delete             | 8     |
| Handler_discover           | 0     |
| Handler_external_lock      | 154   |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 35    |
| Handler_read_key           | 62    |
| Handler_read_last          | 0     |
| Handler_read_next          | 153   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 5947  |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 1     |
| Handler_write              | 617   |
+----------------------------+-------+


MySQL database backup every day using java code We are developing a Java web application using Spring, Hibernate and MySQL.

I want our application to automatically backup the MySQL database daily; is there any way to accomplish that with Java code?

This is easy to do.

Write a script to create the backup using the "mysqldump" tool available at http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html - Google "mysql backup unix/windows script" to find some examples.
Use a task scheduler to run this script. On UNIX this could be "cron", Windows as a "task scheduler". Best to do so at times when the app is under low-usage.

It is worth while putting some backup file cycling into the script so that you keep, say, every backup from the last week and delete the rest.

Also remember to backup to a physically different disk from the database itself so you have a backup if the disk dies.


Select date based on the daylight saving periods Imagine that I have this table:

   Id   |      Name       |       Timestamp
________________________________________________
    1   | Bill Gates      | 2017-01-10 12:00:00
________________________________________________
    2   | Mark Zuckerberg | 2017-07-23 17:00:00

If I use a simple query during the winter time like this:
SELECT Timestamp FROM TableName WHERE name="Bill Gates" and try to print it using PHP or any other language I will get this output "2017-01-10 12:00:00".
If I use the exact same query but during the summer time (DST) I get this output "2017-01-10 13:00:00"

The Question: Is there any way to select the timestamp using the correct DST for each row based on the timestamp?
For example, if I select both rows from the table above, I will get the timestamp from line 1 as winter time and the timestamp from the second line as summer time. Can this be done with SQL only?

Thanks.

MySQL documentation states:

  MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored.

If you want some other behavior use DATETIME as indicated.


How to design tables for airbnb style booking system all. My English is not very good, but I will try to describe my question as clear as possible.

I want to design a airbnb(https://www.airbnb.com) style booking system, this kind of booking system allows somebody books idle resources of the others.

Here is my system needs:

Resource provider can set resources' available time of booking. The minimum time slot is 1 hour between 9am-12pm.
The default setting can be set as repeat periodically. For example: 5pm-8pm every Saturday available.
Also resource provider can change the time table whenever necessary. For example: stop provide the resource this Saturday because it's not available temporary.
Other users can book single or multiple time slot of any resource provided by many resource provider as long as it's available
Booking is open for the next 28 days.

Now I have difficulty in designing tables for the resource provider to set the available time.

Can your guys give me some advice?

Use timestamp ranges for booking availability. There is good documentation in official PostgreSQL docs.

You may find this lecture helpful:
Range Types and Temporal: Past, Present, and Future

And my blog post about generating timestamp ranges could also be helpful:
Generating ranges in PostgreSQL


SQL Server Replication - Only weeks worth of data I have a need for a test server that hosts a small subset of data from our production systems. Is it possible to setup a SQL Server Replication Job that only keeps a week's worth of data so developers can develop reports?

Keep running 7 days of data, keeping the storage need small is the goal.

For Merge replication:

Static row filters, which are available with all types of replication. I have not done any time based filtering :)

I don't know that either of these is really going to meet your "running 7 days" criteria, which sounds to me like "last 7 days".


What is the best approach to index varchar and date range? There is an order table which stores customer information and location. I need to filter the customer's last five locations within the last six months based on order status.

Query as follows,

SELECT *
FROM orders
WHERE (    status = 4
       AND receiver_phone = '91xxxxxxx'
       AND created_date BETWEEN '2017-08-06' AND '2017-03-01'
       AND NOT (    receiver_latitude = '10.9315615'
                AND receiver_latitude IS NOT NULL)
      )
ORDER BY created_date DESC
limit 5;

EXPLAIN ANALYZE:

    Limit  (cost=374121.96..374121.97 rows=1 width=449) (actual time=2151.298..2151.298 rows=0 loops=1)
   ->  Sort  (cost=374121.96..374121.97 rows=1 width=449) (actual time=2151.297..2151.297 rows=0 loops=1)
         Sort Key: created_date DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on orders  (cost=0.00..374121.95 rows=1 width=449) (actual time=2151.262..2151.262 rows=0 loops=1)
               Filter: ((created_date >= '2017-08-06 00:00:00+00'::timestamp with time zone) AND (created_date <= '2017-03-01 00:00:00+00'::timestamp with time zone) AND ((receiver_latitude <> 10.9315615) OR (receiver_latitude IS NULL)) AND (status = 4) AND ((receiver_phone)::text = '91xxxxxx'::text))
               Rows Removed by Filter: 4376895
 Planning time: 0.267 ms
 Execution time: 2151.917 ms

what are best approaches to optimize this query?

Seq Scan means sequential Scanning, PostgreSQL enum have to enum all records for filtering result.

You should use Btree index.
https://www.postgresql.org/docs/current/static/indexes-types.html

  B-trees can handle equality and range queries on data that can be sorted into some ordering


Can I install sql server on Win XP machine with user profile on D drive? Is it possible to install sql server on a windows xp machine where the user profiles are created on a non system root directory, that is, on D drive rather than C drive?

I am trying to install SQL server 2008 R2.

This Microsoft support document might be of some help in regards to that particular error:
http://support.microsoft.com/kb/2387754

  The Network Service account does not have permissions on the following folder:

  C:\Documents and Settings\NetworkService\Application Data\Microsoft\Protect

It might need permission to that particular directory on your D Drive


60 minutes to shut down sql server service? We recently merged with a new company and they have a server that has 265 DBs on it (6.6tb of mdfs). Shortly after we merged the other DBA said it takes 60 minutes to shut down the services which I thought was incredibly high. Last night they stopped and started the services and sure enough it took 60 minutes. I looked at the log this morning and found the following error messages in the sql log file while it was in the process of trying to stop the service.

I know SQL takes a longer time to start back up if you have a lot of VLFs, but it only took 1 minute from the time it started up until the Recovery Complete message appeared in the log. Any idea if 60 mins is normal? Or if not, what can I do to investigate speeding this up. Thanks!

 Error: 17142, Severity: 14, State: 0.
 SQL Server service has been paused. No new connections will be allowed. To resume the
 service, use SQL Computer Manager or the Services application in Control Panel.

 Error: 18456, Severity: 14, State: 13.
 Login failed for user ''. Reason: SQL Server service is paused.
 No new connections can be accepted at this time. [CLIENT: xx.xxx.xx.xx]

It shouldn't take 60 minutes to shutdown the server. As you mentioned, the DBA is performing a pause before shutting down - this is probably the major contributor to the slow shutdown.

Unless he's planning to resume the database, there isn't normally reason to pause. Removing this step from the procedure should cut down a significant portion of the 60 minutes.

Here is an example of a log that shows a pause followed by a shutdown.

The pause begins at:

SQL Server IS NOT allowing NEW connections because the Service Control Manager
requested a pause. TO resume the service<c/> USE SQL Computer Manager OR the Services
application IN Control Panel.

And the shutdown sequence begins with:

SQL Server IS terminating IN response TO a 'stop' request FROM Service Control
Manager. This IS an informational message ONLY. No USER action IS required.

Check with the DBA first though, to make sure there isn't a specialized case that necessitates the pause.


Can I restore a MySQL table from a file (non-sql) backup? I am using XAMPP 5.6.23-0 on a Mac.

I have experienced many problems with MySQL, where the server simply refuses to restart after something has happened (not sure which events make it crash). In the end, the only working solution was to re-install XAMPP. The problem is that the new installation does not automatically pick up the databases that were created in the old installation, even if they are in the same physical location on my disk.

The obvious restore method for a MySQL table or database is of course running the restore by importing an earlier SQL backup from phpMyAdmin, but you need to have that backup, and that is impossible to create after the MySQL server dies and refuses to restart. Running a backup after every single database change is not feasible and should not be required IMHO.

I have tried to completely remove the remaining xamppfiles folder before a reinstall of XAMPP, and that works, but of course the databases I have created are not available anymore. I dug out the files from a Time Machine backup, manually added them to the xamppfiles/var/mysql directory and changed permissions to match those of the databases included with the XAMPP package (chmod 770 on the custom database directories in xamppfiles/var/mysql).

Running phpMyAdmin shows that the custom databases and the tables are visible, but when I try to access any of them, there is an error that the table does not exist in the engine. I did add the user account in the new XAMPP installation that has the same name, password and privileges as used in the old installation, so it is not an issue with access privileges. Simply the message that the table does not exist in the engine.

There must be some file(s) in the XAMPP installation that the MySQL server uses to find the various databases and tables. If anyone can point me to that file I can probably dig it out of the Time Machine backup as well and try to make the new installation (same XAMPP version) run with that configuration file to regain access to my custom databases.

Any pointers are welcome. If this cannot be done, also please let me know so I can stop wasting my time on XAMPP and get a stable MySQL server installation instead.

Your best solution is going to be to install MySQL, stop the service, then completely replace the entire "datadir" with the one from the backup, then cross your fingers before restarting the MySQL service.

The datadir is probably the folder you found in xamppfiles/var/mysql; that sounds right but I don't use XAMPP to know for sure. You can open up the command line client and issue the command SHOW VARIABLES LIKE "%datadir%"; to confirm the proper path.

You can't simply restore the parts of what are missing alongside the existing structure, because you also need the information about which users have access to the tables and other metadata that isn't stored directly in the individual database folder. Restore the entire folder so you get all the correct metadata, database and table structure and data, and permissions.

This is a bit beyond the scope of what MySQL is meant to do, so success is not guaranteed (proper backups usually involve making a .sql file export, which you can do from phpMyAdmin or the command line; I use the 'mysqldump' command line client).


Is possible to restore BD SQL Server without key encryption from 2008 R2? I have a database that has an encryption. The encryption key was lost.
This database is on a SQL Server 2008 R2 server, and my mandate is to migrate this database on a more recent server.
Is there a means to perform the restoration if I do not know the encryption key?

Is there a means to perform the restoration if I do not know the encryption key?

No.  Currently if you lose the old server you lose the database.  So immediately decrypt the database and take a backup.
The SQL Server service account has access to the Server Master Key, which grants access to the Database Master Key in the Master database, which in turn grants access to the Database Encryption Key of the TDE database.
So assuming that you have the old server still running,  you can log into that server and remove database encryption from the database, then wait for the database files to be decrypted (it's a background process), and take a backup, which will be unencrypted.
See eg https://www.sqlservercentral.com/steps/stairway-to-tde-removing-tde-from-a-database and https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15


SQL Server 2008R2 convert collation I have an existing database that uses the Arabic_CI_AS collation, but I need to convert it to SQL_Latin1_General_CP1_CI_AS the default collation for the server, but the problem is when I use the following command:

USE master;
GO

ALTER DATABASE MyDB
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

It give me the following error message:

  Msg 5075, Level 16, State 1, Line 1
  The object 'GetAllSuppliersInCompliance' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it.  Remove the dependencies on the database collation and then retry the operation.

  Msg 5075, Level 16, State 1, Line 1
  The object 'GetTier1SupplierComplianceStatus' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
  .....

  Msg 5072, Level 16, State 1, Line 1
  ALTER DATABASE failed. The default collation of database 'MyDB' cannot be set to SQL_Latin1_General_CP1_CI_AS.

So I tried the following to disable the constraints temporary using the following command:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

I then executed the first command, however the same error appeared.

Assuming those objects are stored procedures or views (and not tables), you'll need to:

script the objects in question
drop them from the database (or just remove the schema-binding)
change the database collation
recreate the objects by running the scripts you created in the first step.

As an aside, you didn't need to sp_msforeachtable "ALTER TABLE..."


Get all of the rows involved between tables relationships to delete them Continuing with the question Get all of the rows involved between tables relationships, now I have to delete all the rows for those codes. I explain:
I have a SQL Server 2016 with the following tables:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            VARCHAR(20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC),
    CONSTRAINT [FK_Code_AggregationLevelConfiguration]
           FOREIGN KEY ([AggregationLevelId])
            REFERENCES [dbo].[AggregationLevelConfiguration] ([AggregationLevelConfigurationId])
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

Aggregation and AggregationChildren represent relations between rows in table Code.

An aggregation will have 1 or more aggregation children. And also, a code in aggregation children could have 0 or more aggregation children.

It is something like this:

Code1
|___________________
|                   |
Code2               Code3
|________           |________________
|        |          |        |       |
Code4    Code5      Code6    Code7   Code8

All of the above are Code.Serial values.

All of the Code.CodeId values for those serials are in Aggregation table for Code1, Code2 and Code3.

And, for those serials, Code2, Code3, Code4, Code5, Code6, Code7, and Code8 are in AggregationChildren table.

I need to delete the rows for those serials from Aggregation and AggregationChildren tables.

Using the answer from the previous question, I have tried to do this:

DECLARE @Serial VARCHAR(20) = 'Code1';

;WITH rc AS
(
    SELECT AggregationId Id
    FROM   dbo.Aggregation
    WHERE  AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial)
    UNION ALL
    SELECT ac.AggregationChildrenId Id
    FROM   dbo.AggregationChildren ac
    JOIN   rc
    ON     ac.AggregationId = rc.Id
)
UPDATE c
SET    CommissioningFlag = 130
FROM   dbo.Code c
JOIN   rc
ON     c.CodeId = rc.Id;
GO

Delete ag from dbo.Aggregation ag
inner join rc
on ag.AggregationId = rc.Id;

But I don't know how to use the DELETE FROM with the rc statement. But I think I only need the Codes that are in Aggregation and AggregationChildren. Maybe I need to modify the With statement to get first the rows in Aggregation table because I have an ON DELETE CASCADE in AggregationChildren table.

How can I get all the codes to delete from Aggregation table?

According to your table schema where there is a rule ON DELETE CASCADE in FK_AggregationChildren_Aggregation foreign key, you can delete only affected records of 'Aggregation' table.

Use the same recursive query to fetch rows but adding AggregationID of childrens, then delete distinct AggregationId codes.

DECLARE @Serial VARCHAR(20) = 'Code1';

;WITH rc AS
(
    SELECT AggregationId Id, null as Code
    FROM   dbo.Aggregation
    WHERE  AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial)
    UNION ALL
    SELECT ac.AggregationChildrenId Id, AggregationID as Code
    FROM   dbo.AggregationChildren ac
    JOIN   rc
    ON     ac.AggregationId = rc.Id
)
DELETE FROM dbo.Aggregation
WHERE AggregationID IN (SELECT DISTINCT Code FROM rc);

This is the result:

SELECT * FROM dbo.Aggregation;
GO

| AggregationId |
| ------------: |
|             9 |

SELECT * FROM dbo.AggregationChildren;
GO

AggregationChildrenId | AggregationId | Position
--------------------: | ------------: | -------:
                   10 |             9 |        1
                   11 |             9 |        2

db<>fiddle here


Distribution database compatibility level Using SQL Server transactional replication with a distributor on 2008R2, and publishers/subscribers all on SQL 2005.  Does the compatibility level of the distribution database matter?  Should it be set to 90 to match the other servers, or is 100 fine?

It seems that compatibility level can have an affect on the publishing and subscribing databases, with respect to object ownership and the existence (or not) of a particular schema. If you aren't seeing that, it's probably not a problem for you.

As far as the distribution database goes, there doesn't seem to be any effect. I would leave the compatibility level of any of the SQL Server system databases (master, msdb, tempdb, the distribution database and even the model database) to whatever MS set it to unless Microsoft or a third-party vendor tells me otherwise, gives me a reason and says that I'll be unsupported if I don't change it.


high writelog wait on OLTP system and fast SSD storage, log flush is slow we have recently upgraded from SQL server 2008 to new servers and upgraded to sql server 2016 (SP1 CU3)

new servers hardware hp proliant 580G8 , 4 socket intel xeon E7 processor, total 60 cores, 640GB ram, 8 intel SSD S3700 Raid 10, local

the environment is a busy OLTP system with around 3K batch request/second mostly insert and to a lesser degree updates. as an OLTP system there is mostly 1 insert/transaction so there are alot of transaction commits. there are some reporting queries for system purposes(queue readers, monitoring, etc)

the problem is log flush performance is slow and we get writelog waits.
i have tested I/O and it can manage over 100K iops/sec in 4k random write test, sequential writes up to 2500MB/sec

databases
compatibility level = 130(sql server 2016)

indirect checkpoint = on,

VLF count = 80-120

processor
cpu usage of 3%-8% , average of 5%, maxed under 10% when log backups runs(compressed backups)
total signal time/total wait time = 6% - 94% resource wait time

memory
PLE is about to tick every second, currently stands for 584000 secs
there is very little page swap observable (pages/sec)

this is the real problem i found

"log flushes/sec" = 1000-2000 /sec

"log flush waits time" = 1000-4000 ms/sec

"log flush write time" = 0-1 ms/sec with spikes of 10-15 ms/sec

"AVG disk sec/write" = 0

"current disk queue" = 0 (with spikes of 2 every few minutes)

"Disk idle time" = 90%-100%

so it seems there's something wrong as sql server is unable to use ultra low latency that SSD array provides...

i tried to disable log writer threads from cpu affinity mask , chris adkin wrote in his blog with no success, also used extended events to monitor log flushes, and saw multiple waits happens for a single log flush.

i don't know if this issue exists on previous servers because on that platform I/O was kinda problem and "log flush write time" was alot higher...

so did you ever see this issue in the field and what is the solution from now on
thanks

This is not an answer for 'what is the solution from now on' but more of how to identify the problem.

Transaction log is not like data pages in terms of how it is hardened to disk. Transaction log is first written to log buffer and then asynchronously written to disk.  In 2016 there is a limit of 112 concurrent log-flush I/Os at any one time for each database’s transaction log.

There are 3 things that cause SQL Server to need to flush a log buffer to disk (to an adjoining log block in the transaction log).

A transaction commits and that transaction has an active log record
in a log buffer.  This will cause all log records in those buffer
(again, up to 60K worth of transaction log record for each log
buffer) to be flushed to the log file.
We hit the 60K size limit for the log buffer and must flush it to
disk.
We have data pages that we must write to disk and there are log
records related to those pages.  We must write those log records to
the log file BEFORE we write the data pages to the data files (this
supports our Write Ahead Logging (WAL) protocol).

The smallest log flush possible is a single 512-byte log block. If all transactions in a workload are very small (e.g. inserting a single, small table row) then there will be lots of minimally-sized log flushes occurring. Log flushes are performed asynchronously, to allow decent transaction log throughput, but there is a fixed limit of 112 concurrent log-flush I/Os at any one time.

As you described you have good io subsystem in conjunction with a large number of short transactions/sec your problem may be following.

On a high-performing I/O subsystem, the writes may complete extremely quickly, but the limit of 112 concurrent log-flush I/Os creates a bottleneck when trying to make the log records durable on disk. This situation can be identified by low write latencies and a near-constant number of outstanding transaction log writes near to 112 in the aggregated output of sys.dm_io_pending_io_requests.

Most of what I mentioned above are from following resources and there are few solutions described.  Also Paul Randal has a short video with demo scripts to confirm if you are having the issue I mentioned above.

Transaction Log Monitoring by Paul Randal
Trimming the Transaction Log Fat part 1  by Paul
Trimming More Transaction Log Fat part 2 by Paul Randal
WHAT IS SQL SERVER’S IO BLOCK SIZE? by Argenis Fernandez
Video by Paul Randal
Demo code to use with the video by Paul Randal


How to access NEW or OLD field given only the field's name? I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create multiple triggers, each with a different set of fields to check.

For example, I have the following schema:

CREATE TABLE daily_reports(
     start_on date
   , show_id uuid
   , primary key(start_on, show_id)

     -- _graph are hourly values, while _count is total for the report
   , impressions_count bigint not null
   , impressions_graph bigint[] not null

   -- interactions_count, interactions_graph
   -- twitter_interactions_count, twitter_interactions_graph
);

The validation must confirm that impressions_count = sum(impressions_graph).

I'm stuck because I don't know how to dynamically access a field from NEW from within plpgsql:

CREATE FUNCTION validate_sum_of_array_equals_other() RETURNS TRIGGER AS $$
DECLARE
  total bigint;
  array_sum bigint;
BEGIN
  -- TG_NARGS = 2
  -- TG_ARGV[0] = 'impressions_count'
  -- TG_ARGV[1] = 'impressions_graph'

  -- How to access impressions_count and impressions_graph from NEW?
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_daily_reports_impressions
ON daily_reports BEFORE INSERT OR UPDATE
FOR EACH ROW EXECUTE
  validate_sum_of_array_equals_other('impressions_count', 'impressions_graph');

I tried Executing Dynamic Commands by doing EXECUTE 'SELECT $1 FROM NEW' INTO total USING TG_ARGV[0], but PL/PGsql complains that NEW is an unknown relation.

I am specifically targeting PostgreSQL 9.1.

Actually, since NEW is a well defined composite type, you can just access any column with plain and simple attribute notation. SQL itself does not allow dynamic identifiers (table or column names etc.). But you can use dynamic SQL with EXECUTE in a PL/pgSQL function.
Demo
CREATE OR REPLACE FUNCTION trg_demo1()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col_value text;
   _col_name  text := quote_ident(TG_ARGV[0]);  -- escape identifier
BEGIN
   EXECUTE format('SELECT ($1).%s::text', _col_name)
   USING NEW
   INTO  _col_value;

   -- do something with _col_value ...

   RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', _col_name, _col_value;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

The cast to text is optional. Using it, because it works universally. If you know the type, you can work without casting ...
Using format() with %s, because the identifier is already escaped at that point.
Else, use format() with %I to safeguard against SQL injection.
Alternatively, in Postgres 9.3 or later, you can convert NEW to JSON with to_json() and access columns as keys:
CREATE OR REPLACE FUNCTION trg_demo2()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col_value text := to_json(NEW) ->> TG_ARGV[0];  -- no need to escape identifier
BEGIN
   RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', TG_ARGV[0], _col_value;
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Since the column name is not concatenated into an SQL string, SQL injection is not possible, and the name does not need to be escaped.
db<>fiddle here (with EXCEPTION instead of NOTICE to make the effect visible).
Related:

How to set value of composite variable field using dynamic SQL


Sensible data management tool for importing and exporting of large data files I am looking for some sensible suggestions on data management tools for large data files.

I have been experimenting aith excel and access only to find that access has limitations on import files size which caught me off guard.

I've been looking at dbase and some other out of the box solutions, but just hoping to find something that best fits my needs.

My typical data processing procedure is as follows:

Import large .txt files in common, pipe or other common delimited formats.
Run queries on the data once imported such as merging cells, and
Export my final queries into MySQL format...

The tables I am dealing with are millions of records large and as I said Access can no longer handle them..

Thanks for any ideas or suggestions on the best software for the job.

It sounds like you should check out MySQL's load data infile ....  The docs claim that it is a very fast way to load rows. (I personally have never loaded millions of rows at once).

Once the data is loaded, you may need/want to add appropriate indexes to your tables to help speed up query performance.

For getting data out of MySQL, check out select ... into outfile.


How to secure SQL Agent Job that INSERTS to a linked server What is the proper way secure a T-SQL agent job that moves data between linked servers?

Setup:

Microsoft SQL Server 2012 (SP3) Enterprise Edition
SQL Server Agent running as NT Service\SQLSERVERAGENT
Microsoft SQL Server 2008 R2 (SP3-GDR) Express Edition
Servers are linked to each other
Link security context set to "Be made using the login's current security context."

My goal is to select data from the Enterprise server and insert it into a table on the Express server. The Express server is running vendor software that needs integration with our main database.

I'm trying to use a dedicated account with minimal permissions for this job because the Enterprise server is our core server with a variety of workloads, and I don't really trust the Express server hosting the vendor software will always be secure.

I tried creating an Active Directory user with the necessary database and table permissions on each server, but then I found that Agent Job steps of type T-SQL cannot use proxies to run as another user.

What's the correct way to do this? The solutions I'm finding look silly or risky, like lowing the security context of the server link, making a PowerShell wrapper, or putting credentials in the T-SQL itself. Surely there's an elegant, secure way to move data between linked servers on a schedule? Or is SQL Server lacking in this area?

SQL Express doesn't have SQL Agent, so I'm assuming you're running the job on the EE Server.

In that case, you should be able to simply use a TSQL job step on the SQL EE box
The SQL Server Agent Account on the EE server already has rights to the local SQL Server.  You can grant it access to the remote SQL Express server.  If running under Network Service or a local virtual account, you would grant the rights to the computer account of the EE server.  EG

CREATE LOGIN [MyDomain\SQLEEComputerName$] FROM WINDOWS

Pushing data over a linked server is not very fast, however.  Pulling is faster.  So you'll need to test the performance.


Delete multiple rows in one MySQL statement I have been looking at a lot of solutions, but I simply do not get it.
What I want to do is remove 2 rows, each in a different table:

DELETE FROM adv_equip, adv_players
WHERE session = '9746qbcinll2edfbmkfn316lu0'

There is a session field in both tables.

A lot of examples include t1, t2 and whatnot, and I do not know how to use it. I tried for example:

DELETE FROM adv_equip t1, adv_players t2
USING t1, t2
WHERE t1.session = t2.session
AND t1.session = '9746qbcinll2edfbmkfn316lu0'

What should I do? It's been days and I kind find a logical easy solution anywhere.

The multi-table delete syntax goes like this:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

or like this:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Source: MySQL documentation.

The documentation also points out in the Multi-Table Deletes section:

  Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement.

That is what is wrong with your second example in particular. Instead of

DELETE FROM adv_equip t1, adv_players t2
USING t1, t2
...

it should be

DELETE FROM t1, t2
USING adv_equip t1, adv_players t2
...

Ypercubeᵀᴹ has raised a few good points in the comments. Since one of the conditions in your WHERE clause is effectively a joining condition, you might want to express the join more explicitly in your statement:

DELETE FROM
  t1, t2
USING
  adv_equip AS t1 INNER JOIN adv_players AS t2
  ON t1.session = t2.session
WHERE
  t1.session = '9746qbcinll2edfbmkfn316lu0'
;

A join, however, comes more naturally as part of a FROM clause, so you might find the first variation of MySQL's multi-table DELETE extension more consistent with an equivalent SELECT. This is how it would look in your case:

DELETE
  t1, t2
FROM
  adv_equip AS t1 INNER JOIN adv_players AS t2
  ON t1.session = t2.session
WHERE
  t1.session = '9746qbcinll2edfbmkfn316lu0'
;

I would probably also prefer specifying .* after the target names in the DELETE clause with that syntax:

DELETE
  t1.*, t2.*
...

That, in my opinion, would more clearly be conveying the intention: delete rows rather than the tables themselves.

And last, but not least, point from the comments is that if only one of the tables has the matching rows, your DELETE statement will fail to delete any rows at all. That is very unlike two separate statements each deleting from one table at a time and making sure that in the end neither has the specified rows. Depending on the intended outcome, therefore, you might prefer two DELETE statements to one.


PostgreSQL: After restoring data, unable to use Views (permission denied) I'm attempting to get a PostgreSQL environment running locally.  I'm using 9.2.8, and have restored data from a backup made by the DBA of the production environment.

The restoration worked fine with 0 errors; however, in a couple of the Views there is a missing field that is in the production schema from which we restored our data.

As well, when attempting to do a simple select from the Views mentioned using the postgres role, I always get the following error:

ERROR:  permission denied for relation Map
********** Error **********
ERROR: permission denied for relation Map
SQL state: 42501

After doing some research (read: Googling), I tried GRANTing priveleges on the Schema, Views, and associated tables (in this case, the table 'Map') to avail.

Obviously, I'm a neophyte PostgreSQL user.  I'm sure there's something simple I'm missing.  But what could it be?

To troubleshoot, I would start by looking at the output of SELECT current_user; and SHOW search_path;. Also, the output of \dp termin in psql.

My suspicion here is that you are stumbling over mixed case identifiers. The error message mentions a table

Map

Notice the capital letter M. Are you sure there is no mix-up with another table named map (lower case)?
Are PostgreSQL column names case-sensitive?

(Or, in another schema coming earlier in the search_path?)


Oracle SQL Developer: Set the  value when exporting to CSV When exporting to CSV in Oracle SQL Developer, null values are replaced with "".  I want them to be exported as the string null.  Is this configurable anywhere?

Example of what is wished:

"STARTDATE", "ENDDATE"
"2017-03-16 00:00:00", null

instead of:

"STARTDATE", "ENDDATE"
"2017-03-16 00:00:00", ""

Exporting is performed by running a query, right-click on the data shown in the grid and select "Export..."

Just set the below in your session, as you would do in SQL*Plus:

set null "null"

Note: this will not affect how NULLs are displayed in the data grid (you can set that from the menu: Tools - Preferences - Database - Advanced - Display Null Value As), but it affects the exported CSV.


SQL Server Autonomous transaction logging Our developer who is familiar with Oracle, has asked for the SQL Server equivalent of autonomous transaction logging. We run SQL Server 2014 and 2017.

After extensive searching, nothing really seems like the obvious choice and almost every post is for SQL Server 2008 and earlier. We would appreciate any type of help.

I am also an Oracle developer. Savepoints are for partial rollback. Oracle autonomous transactions are for partial commits.

Oracle autonomous transactions work like below.

Start Transaction1
/*DMLs*/
    Start Transaction2
    /*DMLs*/
    COMMIT only Transaction2 and end Transaction2
ROLLBACK only Transaction1 retaining changes committed by Transaction2 and end Transaction1

It seems that SQL server does not allow partial commits. Please check NestedTransactions. When an outer transaction is rolled back, the committed inner / nested transactions are also rolled back.


How should I handle unknown primary keys? I'm working as a database-administrator at a school. Recently the school upgraded its pupil administrative software into a web-based solution. This works great, but this web-based solution is missing some features which we used to have. Thankfully the new administrative software enables the possibility to synchronize local databases with their online database through an API.

In my local database each pupil used to have a primary key like 2015XXXX where XXXX is some number. The online application uses a different system. As far as I see the identification number currently ranges from XXXX (number in the thousands) to XXXXX (ten thousands). I have no idea if there exists a logic in these numbers.

I prefer to use the new identification numbers, since it's a lot easier to keep pupils in my local database identical to the online system. However I wish to keep the previous pupils which don't have a new identification number. What kind of primary key should I give those, preventing a collision of primary key's?

I see some options:

(what I have done so far) I've changed previous pupils id's as a huge number. Pupil 20050123 becomes 1020050123. The school will not reach this number of pupils any time soon. But I have nog guarantee the online system won't construct big numbers?
Using negative numbers? Pupil 20050123 becomes -20050123
Other?

As you don't know if there's any logic in the keys you can't tell if a huge or negative number will lead to a key violation.
Without knowledge of the application I'd suggest importing the previous pupils in the new software to get a regular id is the way to go.


Get incremental counts of an aggregated value in a joined table I have two tables in a MySQL 5.7.22 database: posts and reasons. Each post row has and belongs to many reason rows. Each reason has a weight associated with it, and each post therefore has a total aggregated weight associated with it.

For each increment of 10 points of weight (i.e. for 0, 10, 20, 30, etc), I want to get a count of posts that have a total weight less than or equal to that increment. I'd expect the results for that to look something like this:

 weight | post_count
--------+------------
      0 | 0
     10 | 5
     20 | 12
     30 | 18
    ... | ...
    280 | 20918
    290 | 21102
    ... | ...
   1250 | 118005
   1260 | 118039
   1270 | 118040

The total weights are approximately normally distributed, with a few very low values and a few very high values (maximum is currently 1277), but the majority in the middle. There are just under 120,000 rows in posts, and around 120 in reasons. Each post has on average 5 or 6 reasons.

The relevant parts of the tables look like this:

CREATE TABLE `posts` (
  id BIGINT PRIMARY KEY
);

CREATE TABLE `reasons` (
  id BIGINT PRIMARY KEY,
  weight INT(11) NOT NULL
);

CREATE TABLE `posts_reasons` (
  post_id BIGINT NOT NULL,
  reason_id BIGINT NOT NULL,
  CONSTRAINT fk_posts_reasons_posts (post_id) REFERENCES posts(id),
  CONSTRAINT fk_posts_reasons_reasons (reason_id) REFERENCES reasons(id)
);

So far, I've tried dropping the post ID and total weight into a view, then joining that view to itself to get an aggregated count:

CREATE VIEW `post_weights` AS (
    SELECT
        posts.id,
        SUM(reasons.weight) AS reason_weight
    FROM posts
    INNER JOIN posts_reasons ON posts.id = posts_reasons.post_id
    INNER JOIN reasons ON posts_reasons.reason_id = reasons.id
    GROUP BY posts.id
);

SELECT
    FLOOR(p1.reason_weight / 10) AS weight,
    COUNT(DISTINCT p2.id) AS cumulative
FROM post_weights AS p1
INNER JOIN post_weights AS p2 ON FLOOR(p2.reason_weight / 10) <= FLOOR(p1.reason_weight / 10)
GROUP BY FLOOR(p1.reason_weight / 10)
ORDER BY FLOOR(p1.reason_weight / 10) ASC;

That is, however, unusably slow - I let it run for 15 minutes without terminating, which I can't do in production.

Is there a more efficient way to do this?

In case you're interested in testing the entire dataset, it's downloadable here.  The file is around 60MB, it expands to around 250MB.  Alternately, there are 12,000 rows in a GitHub gist here.

Using functions or expressions in JOIN conditions is usually a bad idea, I say usually because some optimisers can handle it fairly well and utilize indexes anyhow. I would suggest creating a table for the weights. Something like:

CREATE TABLE weights
( weight int not null primary key
);

INSERT INTO weights (weight) VALUES (0),(10),(20),...(1270);

Make sure you have indexes on posts_reasons:

CREATE UNIQUE INDEX ... ON posts_reasons (reason_id, post_id);

A query like:

SELECT w.weight
     , COUNT(1) as post_count
FROM weights w
JOIN ( SELECT pr.post_id, SUM(r.weight) as sum_weight
       FROM reasons r
       JOIN posts_reasons pr
             ON r.id = pr.reason_id
       GROUP BY pr.post_id
     ) as x
    ON w.weight > x.sum_weight
GROUP BY w.weight;

My machine at home is probably 5-6 years old, it has an Intel(R) Core(TM) i5-3470 CPU @ 3.20GHz and 8Gb of ram.

uname -a
Linux dustbite 4.16.6-302.fc28.x86_64 #1 SMP Wed May 2 00:07:06 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

I tested against:

https://drive.google.com/open?id=1q3HZXW_qIZ01gU-Krms7qMJW3GCsOUP5

MariaDB [test3]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.2.14-MariaDB |
+-----------------+
1 row in set (0.00 sec)

SELECT w.weight
     , COUNT(1) as post_count
FROM weights w
JOIN ( SELECT pr.post_id, SUM(r.weight) as sum_weight
       FROM reasons r
       JOIN posts_reasons pr
             ON r.id = pr.reason_id
       GROUP BY pr.post_id
     ) as x
    ON w.weight > x.sum_weight
GROUP BY w.weight;

+--------+------------+
| weight | post_count |
+--------+------------+
|      0 |          1 |
|     10 |       2591 |
|     20 |       4264 |
|     30 |       4386 |
|     40 |       5415 |
|     50 |       7499 |
[...]
|   1270 |     119283 |
|   1320 |     119286 |
|   1330 |     119286 |
[...]
|   2590 |     119286 |
+--------+------------+
256 rows in set (9.89 sec)

If performance is critical and nothing else helps you could create a summary table for:

SELECT pr.post_id, SUM(r.weight) as sum_weight
FROM reasons r
JOIN posts_reasons pr
    ON r.id = pr.reason_id
GROUP BY pr.post_id

You can maintain this table via triggers

Since there is a certain amount of work that needs to be done for each weight in weights, it may be beneficial to limit this table.

    ON w.weight > x.sum_weight
WHERE w.weight <= (select MAX(sum_weights)
                   from (SELECT SUM(weight) as sum_weights
                   FROM reasons r
                   JOIN posts_reasons pr
                       ON r.id = pr.reason_id
                   GROUP BY pr.post_id) a
                  )
GROUP BY w.weight

Since I had a lot of unnecesary rows in my weights table (max 2590), the restriction above cut the execution time from 9 down to 4 seconds.


SQL Server Frozen Ghost Cleanup workaround needed I have several tables with amount of rows between 5M and 1.5G

Each table has its BLOB field, which size varies from 100 bytes to 30 MBytes and which is stored as 'large value types out of row' = ON

Tables are stored in different filegroups with 3-4 files each on different disk @ different LUNs @ very fast SAN

Every day these tables grow for 5-100 Gb in size and with 600k - 1.5M rows

After certain amount of time, which varies from 2 weeks to 6 months some of the rows are deleted or moved to archive DB, so - there is no any rows in worktables that older than 6 months.

Current configuration of server:

SQL server engine is 2008 R2 SP1 Enterprise @ 24 cores, @ 64Gb RAM
SQL Server runs with extra startup flags:

  -T 3640; (Eliminates sending DONE_IN_PROC messages to client for each statement in stored procedure. This is similar to the session setting
  of SET NOCOUNT ON, but when set as a trace flag, every client session
  is handled this way)

  -T 1118;(Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent.)

  -T 2301;(Enables advanced optimizations that are specific to decision support queries. This option applies to decision support processing of
  large data sets)

  -T 1117;(Grows all data files at once, else it goes in turns.)

  -E; (Increases the number of extents that are allocated for each file in a filegroup. This option may be helpful for data warehouse
  applications that have a limited number of users running index or data
  scans)

  -T 834; (Causes SQL Server to use Windows large-page allocations for the memory that is allocated for the buffer pool,
  http://msdn2.microsoft.com/en-us/library/aa366720.aspx,
  http://support.microsoft.com/kb/920093)

SQL Server uses Large Page Extensions
SQL Server utilizes fast file initialization option
AUTOSHRINK is OFF for all the databases

The problem is - that starting from some point of server's uptime (from couple of days to months) GHOST CLEANUP process refuses to work out forced cleanups and simply do its usual job - cleans up several pages in several seconds (which is seen thru Extended Events), which is not suitable, because it is not able to clean up all the deleted rows

The problem persists from the times of SQL Server 2005 RTM Enterprise

How I was tried to solve the issue:

Tried to force SCAN operations on clustered indexes of the tables
Tried to force SCAN operations, which involving all the contents of BLOB column on clustered indexes of the tables
system sp_clean_db_free_space & sp_clean_db_file_free_space
manually dbcc cleanpage(@dbid , @fileid, @page) for all the files and pages in DB
clustered index rebuilds and reorganizing
recreating database
DBCC FORCEGHOSTCLEANUP
When I run the query:

select *
from sys.dm_db_index_physical_stats(db_id(), object_id('ProblemTable'), 1, 0, 'detailed')

I see millions and tens of millions ghost records, but only for
allocation unit type of LOB_DATA

The only things, that help:

stopping the server with SHUTDOWN command or restarting the whole
host - it helps, after restart GHOST CLEANUP process runs some hours
and actually cleans all the ghosted records
DBCC SHRINKFILE with EMPTYFILE option - moving all the data from one file to other or newly created files cleans up ghost records in this file only - the problem is that I really hate shrink operations. And this takes 3-4 days for ONE file

the question - is there exists any programmatic (preferable) or maintenance way to force GHOST CLEANUP without server downtime at all, because server downtime costs too much, even unacceptable - its from thousands to tens of thousands $ per hour

Problems were noticed alike mine are here:

http://support.microsoft.com/kb/932115
http://www.sqlservercentral.com/Forums/Topic496244-149-1.aspx

And just the same is here:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/c023e51c-92ad-471b-89a6-8c93732ee6cb/

Finally, MS has recognized the issue as a bug: http://support.microsoft.com/kb/2622823

Briefly: It is fixed in

Sql Server 2008 SP3 CU4
Sql Server 2008 R2 CU10
Sql Server 2008 R2 SP1 CU4

In Sql Server 2012 SP1 I'm not experiencing the issue for more than year of runtime.


Size of a MyISAM table doubles after OPTIMIZE I have this table:

CREATE TABLE `foo` (
  `CalculatedResultsId` int NOT NULL,
  `Md5Hash` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `SectionData` json NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

ALTER TABLE `foo`
  ADD UNIQUE KEY `CalculatedResultsId` (`CalculatedResultsId`),
  ADD UNIQUE KEY `Md5Hash` (`Md5Hash`);

which contains ~400k rows and is ~800MB big. This is the output from PMA:

Then I delete 2'454 rows [< 0.6%], which produces an overhead of around 5MB which is also documented in PMA:

Running OPTIMIZE Table suddenly doubles the size of this table. I checked the real size of the table file in the directory and this is correct. It really doubled!

My question:

Why, what is going on? What happened, what is the reason, that the table size doubled?

The only way I found how to reduce the size of the table as it was before is to create a new table and re-fill it. Another very strange fact:

Running this command in order to get the copy of the big table CREATE TABLE foo_new AS SELECT * FROM foo; runs for 27! minutes for these 400k rows.

Creating an empty table and then copying with INSERT INTO foo_new SELECT * FROM foo needs 24! seconds.

Why such a difference? I don't know, but somehow it looks to me like a very serious design problem in MySQL, or a bug.

MySQL 8.0.20 running on SLES 15.1

It's a 4 month ago filed bug and it is fixed in MySQL 8.0.22.


How should I build a DB to hold data about another database I know that this is a strange question. I need ideas and/or advice, not necessarily "how-to"s.

I work with data from a 3rd party tool/database. I do not have permissions to modify this production database. Any changes or additional information that is needed that cannot go into this production database has to be housed somewhere else.

I have tables whose data has some sort of relationship to each other. For example: Let's say I have a "Caller" table that contains the caller's name and the company that they work for, and I have a "Company" table. Normally, I would use the PK on the Company table in the Caller table as an FK. In this case, that is not possible. On top of that, the Company Names may not be EXACTLY the same. For example: Kim Smith might have "ABC Distributing" as her company in the Caller table, while the Company table has the name as "ABC Dist".

I need to create a database to "support" this other database. In addition to the problem listed above, sometimes companies change names. The powers-that-be want the historical records attached to the company's name at that period in time. So if "ABC Dist" changed their name to "ABC Distribution and Manufacturing" in January 2016, the powers-that-be want the records for that company before Jan 1, 2016 to have the "ABC Dist" name, while records AFTER Jan 1, 2016 to have the new name. We now have TWO records for the same company, with two PKs. Again, this is not something that I can change. If I could rewrite this 3rd party tool, I would.

So, I need a way to keep callers associated with the correct company in the company table and a way to associate multiple lines in the same company table together (company name changes).

I know that I'll have to write an ETL to keep these two databases synchronized. What I need is ideas on how to design the support DB. Do I copy company names over or just use the PK for everything?

I hope that I've made the issues clear and understandable. I'm really frustrated with this mess, and need a way to tame it a bit.

OH! Also, I need suggestions on how to keep track of when changes are made to the production database. No one bothers to tell me changes have occurred until something downstream breaks. (Using MS SQL Server 2008 R2)

Should I have submitted this under the developers' StackOverflow?

Strongly recommend you don't do it. There clearly are either technical deficiencies in the current database/app or you have contractual deficiencies that prevent your company from getting the 3rd party to fix/enhance their app/DB. Building a helper DB quickly becomes unmaintainable and you end up with errors that can be hard to troubleshoot.

For example, consider your scenario above where the company changes its name. If you can't modify the primary database to create triggers on that table or modify the app/SP so that changes to company name in the primary DB always ensure propagation to the helper DB, you're almost guaranteed to get out of sync over time. Regardless of whatever procedures you put in place, humans fail. On top of that, you've also tightly coupled another database to the primary so if the helper is not online and operational, there needs to be some mechanism in place to "hold changes" and make sure they get applied later. You also need to know what operations should not be permitted on the primary because they depend on the helper.

Those are just some top level things to deal with, there are more.

If you still want/have to do it, design the table logically the way you would if it was in the same DB. Using your customer name scenario, you'd have the column you want to track changes/variations of so for your example it would be company_name. You'll also need the column that authoritatively identifies the customer. This might be companyID or some other identifier the app uses. If you are on SQL Server 2016, enable temporal table for this and you'll have system managed versioning. Else, you'll need to do your own versioning. You'll need a column to track versions. Date is probably most natural but not necessarily the best for performance if you have a very large table. I suspect it won't matter for your case (if the table becomes very large, this won't be your biggest problem). You'll also need to consider creating a composite primary key or some surrogate key. Again, assuming the table won't be huge, I'd go with composite PK but you know your system best so evaluate options and recommendations carefully - this is a contentious subject.

Once you have that, create your database + tables and make sure you have the right permissions setup and whatever you do to protect the primary database, do the same for this helper DB. Make sure your backup plans will get you the right level of logical consistency and you have a scripted way to reconcile when they get out of sync.

Populating the first time is easy, keeping it in sync is a bit trickier. Since you're not really just doing a 1-1 copy, things like replication won't work without some customization. Writing an ETL job to kick in regularly will do the trick and likely easier but what is the latency you can tolerate? Is it ok to have the helper DB 10 minutes behind the primary? 20? 30? Careful with the selection. Setting high frequency can cause interesting problems if a job gets blocked/delayed for whatever reason. Change tracking or change data capture are helpful here. They are not the same thing and picking the right technology really depends on your environment. At a very high level, if changes to the same row are very infrequent and if they occur, the same row would not be changed multiple times over a short period, change tracking might be a better solution - lighter weight. If the data may be changed repeatedly over a short period, consider change tracking - more overhead but you get full history to move to the helper.

A quick and dirty solution would be to write a trigger on the table so every time a value is changed, you would push that to the helper db but triggers are easy to mess up. It's easy to make a mistake and flood the database. Also, failures are often hard to troubleshoot. I wouldn't go this route.

Whatever you finally implement, be sure you test to ensure it works correctly for deleted companies. If you "blindly" propagate a delete to the helper DB, will that delete the latest or all occurrences? What is the right behavior for the business and are there legal requirements? How do you recover from an accidental delete?


Unable to describe table in oracle I'm trying to get the description of a table in oracle because I need to know what foreign keys does it have and which tables they refer to.
I'm using desc
but all I get is "ORA-00900: invalid SQL statement"
Any idea why?
Thanks.

I suspect that you did not execute this command with sqlplus. Not every sql program can handle the desc command.

You can either use sqlplus or get the table definition with the following SQL command:

SELECT dbms_metadata.get_ddl (object_type, object_name, owner)
FROM   all_objects
WHERE  owner = '<owner>'
  AND  object_name LIKE '<whatever_table>%';

This will give you the complete table definition with all column definitions.


SQL SERVER 2008 R2 Identity Range The current table I am having issues with has an Publisher Range Size of 10,000, but when I look at the constraint I only see 1000 for the range size at the publisher. I have tried running sp_adjustpublisheridentityrange and changing the Publisher Range Size to 12000 to kick start it, but it still will not change.

I manually changed the identity fields with DBCC CHECKIDENT.  No automated way or configuration to fix it.


Count where any 3 columns have values (not null) I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 columns are filled (Not Null). Can we do this through a query?

In the example shown, the query should return count as 4 rows.

You can mark any NULL as 0 and not NULL as 1 and calculate the sum,
it will give you the number of not NULL values in a row.

If you want to count only the rows where there are exactly 3 not NULL values use this code (you should write a sum of cases for all 15 columns, in my example they are only 6):

declare @ItemProperties table (col1 int, col2 int, col3 int, col4 int, col5 int, col6 int);
insert into @ItemProperties
values
(1, 1, 1, null, null, 1),
(1, null, null, null, null, 1),
(null, 1, 1, 1, null, null),
(null, null, 1, null, null, 1),
(null, 1, 1, 1, 1, 1);

with cte as
(
select *,
       case when col1 is null then 0 else 1 end +
       case when col2 is null then 0 else 1 end +
       case when col3 is null then 0 else 1 end +
       case when col4 is null then 0 else 1 end +
       case when col5 is null then 0 else 1 end +
       case when col6 is null then 0 else 1 end as Num_of_not_NULL_columns
from  @ItemProperties
)

--select *
--from cte
--where Num_of_not_NULL_columns = 3

select count(*) as cnt
from cte
where Num_of_not_NULL_columns = 3;

If instead you want to count the rows with at least 3 not NULL values change the condition as where Num_of_not_NULL_columns >= 3;


Equivalent functional dependencies I cannot understand why F1 is not equivalent to the other sets of dependencies below given the relation R(A,B,C):

F1 = {A → B, B → C}
F2 = {A → B, A → C}
F3 = {A → B, AB → C}

This is question no. 6 in this open exercise from stanford.edu, and the answer provided at the bottom of that page states that F1 is  not equivalent. But I do  not understand why because I was thinking that it is equivalent to the other sets by virtue of transitivity. I thought B → C in F1 and A → C in F2 are the same since A → B.

the answer provided at the bottom of that page states that F1 is not equivalent.

That is correct. F2 and F3 are equivalent but F1 is not.

F2 is equivalent to F3 because from A → C we can infer that AB → C (therefore F2 => F3)
and from A → B, AB → C we can infer: A → AB and then A → C (therefore F3 => F2).

You could also prove that F1 => F2 (and F1 => F3) but not F2 => F1.

A counterexample should convince that there are cases where F2 (A → B, A → C) holds but not F1 (B → C):

 A   B        C
----------------------
 1   Bill     Clinton
 2   Hilary   Clinton
 3   Bill     Murray


XML dump of MySQL database don't include foreign key constraints. What can be the reason? sqldump(.sql) file list it I am accessing schema information of databases in XML format. I am taking dump of the database in XML format using the command below.

mysqldump --no-data --xml -u root -p bakerydb > bakerydb.xml

these dump don't list the foreign key constraints. Below part of the output is listed.

   <table_structure name="class_details">
        <field Field="id" Type="int(10)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
        <field Field="course_id" Type="int(10)" Null="NO" Key="MUL" Extra="" Comment="" />
        <field Field="start_date" Type="date" Null="NO" Key="" Extra="" Comment="" />
        <field Field="end_date" Type="date" Null="NO" Key="" Extra="" Comment="" />
        <field Field="tutor_id" Type="int(10)" Null="NO" Key="MUL" Extra="" Comment="" />
        <key Table="class_details" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" />
        <key Table="class_details" Non_unique="1" Key_name="fk_cources_offered_class_details" Seq_in_index="1" Column_name="course_id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" />
        <key Table="class_details" Non_unique="1" Key_name="fk_employee_details_class_details" Seq_in_index="1" Column_name="tutor_id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" />
        <options Name="class_details" Engine="InnoDB" Version="10" Row_format="Compact" Rows="0" Avg_row_length="0" Data_length="16384" Max_data_length="0" Index_length="32768" Data_free="7340032" Auto_increment="1" Create_time="2014-02-25 07:08:56" Collation="utf8_general_ci" Create_options="" Comment="" />
    </table_structure>

Same database lists the detailed foreign key constraint in sqldump. (.sql file).
below that's mentioned.

DROP TABLE IF EXISTS `class_details`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class_details` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `course_id` int(10) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `tutor_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cources_offered_class_details` (`course_id`),
  KEY `fk_employee_details_class_details` (`tutor_id`),
  CONSTRAINT `fk_employee_details_class_details` FOREIGN KEY (`tutor_id`) REFERENCES `employee_details` (`emp_id`),
  CONSTRAINT `fk_courses_offered_class_details` FOREIGN KEY (`course_id`) REFERENCES `courses_offered` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Anyone know why they don't list parent table information? and the way, How we can get the details of foreign key constraints?

P.S. : storage engine is InnoDB.

This is a bug in mysqldump that Oracle has characterized as a "feature request."

http://bugs.mysql.com/bug.php?id=66821

In normal mode (not XML), mysqldump just uses the server's output from SHOW CREATE TABLE tablename.

if (!opt_xml ...
    ...
    my_snprintf(buff, sizeof(buff), "show create table %s", result_table);

When running in --xml mode, mysqldump uses the output from SHOW KEYS FROM 'tablename', which doesn't include the foreign key constraints.

my_snprintf(buff, sizeof(buff), "show keys from %s", result_table);

Since the two different operating modes of mysqldump fetch the data from the server in such different ways, so the addition of this information to an XML dump is not exactly a trivial change.

There is not a simple workaround, although as noted in the bug report, the information is available in information_schema and you can access this from mysqldump if you supply the --skip-lock-tables option (locking information_schema isn't allowed).  You'll have to take the data you retrieve, parse and extract the relevant bits, and reformat it as needed before it would be all that useful.


Does it make sense to use full recovery if the data and log files are on the same HD? We have a database that was set up to use full recovery and my guess is that the reasoning was only to prevent data loss if a failure happened between full backups.

We make daily full backups of the database and we have no need to recover to any point in time previous to our last full backup.

Our data files and log files are in the same hard drive. From my experience (as programmer, I'm not a DBA), most database failures I've seen were related to disk failures and so I wonder if this setup makes any sense. I imagine that if the hard drive fails, we wouldn't be able to recover using the transaction logs.

So my question is twofold:

Is the most likely cause of a database failure, a hard drive failure, or are there other common reasons that would justify this setup ?
Would it make more sense to switch to the simple recovery model and convince the business that in the worst case scenario they would have to re-input data for the day ?

I can't speak to 1 but for 2, I don't know that your position should be pushing the business toward a specific RPO (recovery point objective). They may not be aware that they'd have to re-enter all the data for a day if things go belly up. Talk to them, find out how much data loss they're willing to tolerate. If they say 24 hours is too much, great, then that indicates the current approach is insufficient for their needs. If that requires hardware purchase to meet the RPO, then they will need to provide funding or accept their current max data loss. Finally, document the outcome in some public place and then test on your restores a recurring basis to ensure you are able to meet that RPO.

That said, there are plenty of other reasons to have data and log (and temp) on separate drives. Some of them documented on this question https://serverfault.com/questions/38511/ms-sql-layout-for-best-performance


Cannot create MySQL database with name '5e370227_db' Why am I getting error when I try to create database with name '5e370227_db'?

I am getting the following error:

mysql> create database 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '5e370227_db' at line 1

When I try to create a database with name '5a370227_db' (e -> a) it is created successfully:

mysql> create database 5a370227_db;
Query OK, 1 row affected (0.00 sec)

Details:

MYSQL: mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Linux Centos 2.6.32-358.el6.x86_64
logged as root
database admin

Interesting problem and I think that I've figured it out.

For some reason, MySQL interprets 1e_a_number_ as an exponent - i.e. 10 to the power of something. 1a_a_number has no mathematical meaning, therefore it's not parsed as being a number and accepted as valid. If you notice at the beginning of my experiments below - pure numbers aren't allowed as database/schema names.

Congratulations - it looks like you've spotted a MySQL bug.

Something like this (underscore following number and e) works.

MariaDB [(none)]> create schema 1_e34343_db;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>

which appears to confirm my conclusion.

This does also - i.e. 1ex works, but 1e1 fails.

MariaDB [(none)]> create schema 1ex;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create schema 1e1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e1' at line 1
MariaDB [(none)]>

=== Experiments leading me to my conclusion ============

MariaDB [(none)]> create schema 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create database 5e370227_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '5e370227_db' at line 1
MariaDB [(none)]> create schema  5a370227_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> drop schema  5a370227_db;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> create database  5a370227_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> drop database  5a370227_db;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database 100;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '100' at line 1
MariaDB [(none)]> create database 100_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 100000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 10000000000000000000000000000000000_db;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create database 1e0000000000000000000000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000000000000000000000000000_db' at line 1
MariaDB [(none)]> create database 1e000000000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e000000000000000_db' at line 1
MariaDB [(none)]> create database 1e0000000000_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e0000000000_db' at line 1
MariaDB [(none)]> create database 1e4_db;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_db' at line 1
MariaDB [(none)]> create database 1e4_zx;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1e4_zx' at line 1
MariaDB [(none)]>
MariaDB [(none)]> create database 1a4_zx;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]>


Changing an nvarchar column to datetime I am new to SQL Server and I have a problem.

I created a database to store dates in an nvarchar field but now I need to convert that field to datetime. I searched the internet and couldn't find a proper solution.

One thing I found was to create a new datetime column, copy string value to that, then rename the new column to original one.

My string value format is 21/11/2014. I need to keep the same format. If anyone could give me a SQL Server query that can do that, I'd highly appreciate. I'm using SQL Server Management Studio 2014.

As you mention you string value format is in 21/11/2105 then you can try following one

Alter table table name alter column column name datetime NULL

( null is optional if you want to allow null value to your field).

Note that if the column contain  string value like "abc" (other than date format value) then it will show error as conversion fail.


What are the default settings for accessing MySQL remotely? What is default settings in MYSQL for Remote access?

In CentOS the file /etc/my.cnf does not have skip-networking or bind-address. MySQL Version is 5.5, does it mean the server is enabled for remote access?

MySQL restart failed after adding skip-networking and bind-address=127.0.0.1 in my my.cnf file, so how can I disable remote access?

my.cnf

[mysqld]
skip-networking
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_strict_mode=1
default-storage-engine=MyISAM
long_query_time=5
log_slow_queries=ON
thread_cache_size=32
key_buffer_size=64M
table_open_cache=1000
max_connections=300
query_cache_type=2
query_cache_limit=32M
query_cache_size=160M
max_heap_table_size=256M
tmp_table_size=256M
wait_timeout=360
interactive_timeout=360
max_allowed_packet=16M
sort_buffer_size=8M
innodb_buffer_pool_size=256M
innodb_log_file_size=64M

You will have to resort to removing remote access from the MySQL users themselves.
To find out who has remote access, run this
SELECT user,host from mysql.user
WHERE host NOT IN ('localhost','127.0.0.1');

This will show you everyone that can login to MySQL from specific access points as well as broader network specifications.
You could run the utility mysql_secure_installation which do the following:

You can set a password for root accounts.
You can remove root accounts that are accessible from outside the local host.
You can remove anonymous-user accounts.
You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_. (I posted a question and answer about this weird aspect).

UPDATE 2013-07-02 13:11 EDT
I see you have skip-networking in the my.cnf and you removed bind-address=127.0.0.1. I don't think the two options belong together. Why?
The option skip-networking disables TCP/IP. Using 127.0.0.1 as a bind-address implies TCP/IP. The two can never really mix. They are meant to be mutually exclusive. Use one or the other, not both at the same time. This makes sense because when you read the MySQL Documentation for skip-networking and bind-address, one does not mention the other.


EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan The following EXCEPT query produces a logical plan with a seemingly purposeless projection. This also occurs for INTERSECT.

What is the purpose of the projection? e.g. Is there a different EXCEPT query where the outer projection would specify something?

Query:

use AdventureWorks2017

select p.ProductId
from Production.Product as p
except
select pinv.ProductID
from Production.ProductInventory as pinv
option (recompile, querytraceon 8605, querytraceon 3604)

Converted Tree:

    LogOp_Select

        LogOp_GbAgg OUT(QCOL: [p].ProductID,) BY(QCOL: [p].ProductID,)

            LogOp_Project -- << ?? PASSIVE PROJECTION ??

                LogOp_Project

                    LogOp_Get TBL: Production.Product(alias TBL: p) Production.Product TableID=482100758 TableReferenceID=0 IsRow: COL: IsBaseRow1000

                    AncOp_PrjList

                AncOp_PrjList

            AncOp_PrjList

        ScaOp_Exists

            LogOp_Select

                LogOp_Project

                    LogOp_Get TBL: Production.ProductInventory(alias TBL: pinv) Production.ProductInventory TableID=914102297 TableReferenceID=0 IsRow: COL: IsBaseRow1001

                    AncOp_PrjList

                ScaOp_Comp x_cmpEq

                    ScaOp_Identifier QCOL: [p].ProductID

                    ScaOp_Identifier QCOL: [pinv].ProductID

There are various things we do inside the query optimizer that don't really have a reason that we can explain externally.  The projects you see in the optimizer eventually get rewritten at the end of the optimizer to "flatten" the expressions.  So, the optimizer has things in the search that are part of how the code is implemented that may introduce extra projects that don't really do anything functionally but do allow us to stitch things together where one subtree was created in one part of the optimizer but is used later in a place where it was not originally intended.  There are some physical implementation details that can cause this to happen.  None of them matter for users in terms of plan quality (in almost any case - we have done work to make these not matter in the search).

So, I'd just wave my hand and say "you don't need to see his identification.  He can go about his business.  Move along" ;).


Is there a way to make the subscriber database read-only when using Transaction Replication? I have set up Transaction Replication and its working well.

I have a few different web apps that connect to the publisher and subscriber database and I am concerned that if they use the subscriber database for inserting/updating/deleting intentionally, or accidentally data will be lost. (it should essentially be used as a read-only database because I have not setup updatable subscriptions).

I tried changing the database properties to be read-only, but this caused the replication to fail.

I was thinking maybe a before and after script during replication to add/remove the database read-only flag?

Is there a better way to do this?

Instead of altering the database to become read only, create a new login and add it to the db_datareader role of the subscriber database.


Database architecture for interactions between users (8B rows, 1TB of data) I'm saving an interactions table between two users. I have two interaction types, and each interaction can happen more than once. The easiest reference would be Facebook Likes and comments on posts: User1 did two likes, and 1 comment on User2.

I would also like basic query abilities, like all users that interacted with User1, and users User1 has interacted with sorted by how many interactions.

I'm currently saving this in MySQL, and things have been going well, but as the table is getting big (7.5B rows, 1TB total disk space divided equally between data and indexes), INSERTS have become very slow. Can take up to a minute for 1k rows.

My Current Table Structure:

 CREATE TABLE `interactions` (
  `user_id_to` bigint(11) unsigned NOT NULL,
  `user_id_from` bigint(11) unsigned NOT NULL,
  `type2counter` tinyint(11) unsigned NOT NULL DEFAULT '0',
  `counter` smallint(11) unsigned NOT NULL,
  `updated` date NOT NULL,
  PRIMARY KEY (`user_id_to`,`user_id_from`),
  KEY `uid_from` (`user_id_from`),
  KEY `uid_to_counter` (`user_id_to`,`counter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Edit: Insert look like this:

INSERT INTO interactions
     (user_id_to, user_id_from, counter, updated)
   VALUES
     ('2266931634','3180832729','3',NOW() ),
     ('2266931634','3133897242','1',NOW() ),
     ('2266931634','3207247957','1',NOW() ),
     ('2266931634','3520390476','1',NOW() ),
     ('2266931634','3631954079','1',NOW() ),
     ('2266931634','3687475949','1',NOW() ),
     ('2266931634','3627629761','1',NOW() ),
     ('2266931634','3779990751','1',NOW() ),
     ('2266931634','4071753134','1',NOW() ),
     ('2266931634','4290900946','3',NOW() )
    ON DUPLICATE KEY UPDATE
             counter=counter+VALUES(counter),
             updated=NOW()

MySQL version: 5.6.19

Something wrong with that!  There is no column counter??

But, assuming you meant one of the existing counters...

Having the counter in an index, and updating the counter frequently --> double the work.  Once to update the table, once to update the index.
Since the first part of the PRIMARY KEY is user_id_to, you have a very good way of locating the counter given the user_id_to.  So, DROP that index.  This will speed things up considerably.

Something else to check...  innodb_buffer_pool_size should be about 70% of RAM.  What are the value of those?

PARTITIONing is very unlikely to help.  Given a good index, partitioning is no faster because it is essentially turns a 1-step process (look up in the index) into a 2-step process: first find the partition ("pruning"), then use the index in that partition.

If possible, use either LOAD DATA or "batch" INSERTs.

What do the INSERTs look like?  Perhaps INSERT ... ON DUPLICATE KEY UPDATE?  For batching, that might be best done by creating a tmp table, then using IODKU with a SELECT on the tmp table.  More on high speed ingestion.

Some things look fishy, please provide specifics on the inserts, updates, and selects.


Read access to a login in MS SQL schemas We have a MS SQL Server login and created users belonging to that login in several databases. We want to grant read access to all tables in the databases to these users. What query can we run to grant the read access?

You should probably grant db_datareader access to that user.
Members of the db_datareader fixed database role can read all data from all user tables.

USE master
GO

DECLARE @DatabaseName VARCHAR(32)
DECLARE @SQL NVARCHAR(max)
DECLARE @User VARCHAR(64)
SET @User = '[MyUser]' --Your User

DECLARE Grant_Permission CURSOR LOCAL FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN Grant_Permission
FETCH NEXT FROM Grant_Permission INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; EXEC sp_addrolemember N''db_datareader'', ' + @User + ''

    --EXEC sp_executesql @SQL
    PRINT @SQL

FETCH NEXT FROM Grant_Permission INTO @DatabaseName
END
CLOSE Grant_Permission
DEALLOCATE Grant_Permission

With this permission in-place, you won't need to grant read access every time you create a new table.


MySQL - Order of Table Load From Backup I need to load a database backup from some months ago.There are 29 tables backed up as individual.sql files. Is the order I load them important? For instance the ClientSupplementary table has a FK into the Client Table so that if the Client is deleted the Supplementary data is as well. So does the Client have to be loaded before their Supplementary information?

Usually yes, when foreign keys are involved, you have to create them in the right order.

When you are sure, however, that your data is consistent, you can do it like this:

mysql> USE your_schema;
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> SOURCE /path/to/your/dump/file1.sql
mysql> SOURCE /path/to/your/dump/file2.sql
mysql> # ...
mysql> SET FOREIGN_KEY_CHECKS=1;

Or on the command line:

shell> mysql -uuser your_schema -e "SET GLOBAL FOREIGN_KEY_CHECKS=0;"
shell> cat file1.sql file2.sql | mysql -uuser your_schema
shell> mysql -uuser your_schema -e "SET GLOBAL FOREIGN_KEY_CHECKS=1;"


MongoDB Primary Member of Replica Set High Memory Usage I currently have a sharded cluster up and running, and I have noticed that the memory usage of the primary member of each replica set is very high, it is 8GB although I started each member with the following:

mongod --auth --bind_ip_all --shardsvr --replSet a --smallfiles --oplogSize 1024 --keyFile /file/somewhere/

I thought (possibly naively) that the oplogSize would limit the amount of memory used.

Any guidance in how to solve this or highlighting the error of my ways is much appreciated.

Introduction

The oplog has nothing very little to do with memory consumption. It is a capped collection used as sort of a write-ahead log for operations to be replicated to other replica set members.

In general, MongoDB uses up to around 85% (give and take) of memory unless told otherwise. This memory is used to keep the indices and the "working set" (copies of the most recently used documents) in RAM to ensure optimal performance. While it is technically possible to limit the RAM used by MongoDB, it is a Very Bad Idea™ to do so, as you severely limit MongoDB's performance and make it basically impossible to detect when to scale out or up because of insufficient RAM.

TL;DR: If you have to ask how to limit the RAM utilised by MongoDB, you probably should not limit it, as you are unable to judge the side effects this step will introduce.

Limiting MongoDBs memory consumption

You basically have three options: Limit the cache size for the WiredTigers storage engine, use cgroups to limit the memory mongod can request from the OS or use Docker to do so (which makes it a bit easier, but under the hood Docker uses cgroups as well, iirc).

Option 1: Limit WiredTigers cache size

Add the following option to your configuration file (I assume it is in YAML format):

storage:
 wiredTiger:
  engineConfig:
     cacheSizeGB: <number>

where <number> is the maximum amount of RAM MongoDB is allowed to use for WiredTiger's cache. Note that fiddling with this parameter can severly impact performance (on the other hand, limiting MongoDB's memory consumption always will). Please also note that this does not limit the memory used by mongod itself (for example, each connection gets a small stack assigned).

Option2: Using cgroups to limit the overall memory consumption of mongod

As a root user, first ensure that cgroups are enabled:

$ lscgroup
cpuset:/
cpu:/
cpuacct:/
memory:/
devices:/
freezer:/
net_cls:/
blkio:/

Assuming cgroups are available, you can now configure a control group for MongoDB's memory consumption in /etc/cgconfig.conf:

group mongodb{
    memory {
        memory.limit_in_bytes = 512m;
    }
}

After you have done so, you need to restart the cgconfig service. Do not simply copy and paste the config above: With 512 MB, MongoDB will bearly run (if at all). Adjust the memory limit to your needs, with at least 2GB of RAM.

Next, you need to assign mongod to the control group you just created. To do so, you need to edit /etc/cgrules.conf:

*:mongod memory mongodb/

where * denotes that this rule applies regardless who started mongod, the limit will be applied to RAM according to the rules of the control group mongod/. As a last step, you now need to restart the cgred and MongoDB services. The mongod now should use only the specified amount of RAM, for the better or worse.

Option 3: Use Docker to limit mongod's overall memory consumption:

Identify which version of MongoDB you are running currently

$ mongod -version
db version v3.4.10
git version: 078f28920cb24de0dd479b5ea6c66c644f6326e9
OpenSSL version: OpenSSL 1.0.2n  7 Dec 2017
allocator: system
modules: none
build environment:
   distarch: x86_64
   target_arch: x86_64

Your output may be different, but we only need the db version, namely the minor version. In this example, it is "3.4".
Pull a suitable docker image

$ docker pull mongo:3.4

You should pull the docker image for the version you determined earlier and use the pulled image in the next step.
Run the docker image with the appropriate parameters

 $ docker run -d --name mongod --memory=512m \
 > --mount type=bind,src=/path/to/your/datafiles,dst=/data/db \
 > --mount type=bind,src=/file/somewhere/,dst=/key.file,readonly
 > mongod <yourOptions>

A few things to note here: The first mount makes your existing datafiles accessible from inside the container, while the second mount does the same for your keyfile. You need to adjust your mongod options accordingly, namely the keyFile option to point to the destination you mounted your keyfile to.
See the docker documentation and the README of the mongo docker image for details.

Conclusion

You have a sharded cluster and you want to limit the memory consumption of the individual shard members.

In case we are talking of a production system, this is a Very Bad Idea™: Either you have other services running on the machine running your mongods (which would make the two services compete for resources in case of heavy load) or artificially limit the performance MongoDB will provide (by using the methods described above). This is bad systems design. Why did you shard the first place? Sharding is MongoDB's method of load balancing and scaling out (in case a limiting factor, say RAM, can not be scaled up any more because the bang you get for the buck is insufficient). I have a mantra which I repeat to customers (and to be honest occasionally to myself):

  MongoDB instances bearing production data should run on dedicated machines. No exceptions!

Depending on the reasons you sharded the first place and how many shards you have, it may well be that you didn't even need to shard if you ran your cluster on dedicated machines. Do the math.

And even if it was a good idea to have your cluster nodes running other services, they are obviously under provisioned. Given the price of RAM compared with reduced performance, it is basically a no-brainer to scale your machines up with a decent amount of RAM rather than limiting it artificially to enforce a system design which is bad in the first place.

My advice for you is to not follow any of the above approaches. Instead, run your data bearing MongoDB instances on dedicated machines. Scale them up as long as you get an according bang for the buck RAM and IO-wise (CPU is rarely an issue) before you shard. As of the time of this writing that would be between 128 and 256GB RAM and a RAID 0 (in case you have a replica set, which you do have, don't you?) or RAID 10 ( in case your shards are not replica sets - shame on you ;) ) with SSDs. Shard only if

you have too many IOPS for a single machine to handle
you need more RAM than you could fit into your replica set members with a good bang for the buck
you have more data than a single machine can persist.

hth

PS Do not blame it on me or MongoDB if your performance goes south after you limited the RAM for the mongods.


Why does MYSQL use blocking locks instead of optimistic attempts that fail immediately? Why isn't optimistic concurrency control more commonly supported at the database layer? It is a valid alternative and can completely do away with any blocking locks. For certain usage patterns, this will offer complete safety with better performance.
The primary argument for OCC is:
When a SELECT or UPDATE blocks on a lock, there is no guarantee that it will return successfully and failure/success has to be handled at the application layer. Instead of forcing the application to wait for the timeout period before giving the result, we provide the result immediately and let the application figure out how it wants to handle the failure.
There are use cases where this approach will primarily cause issues like starvation (Primarily with large writes, or highly contentious changes). However, in a lot of standard cases (such as building a blog/forum/maybe even a bank), contentions will be rare and seem like they'll be better dealt with by the optimistic pattern.
What am I missing?

I have attempted to rephrase my question with better terminology to clarify the question a little bit more. Below is the question as it was originally asked. Feel free to remove it since it is there in the history as well.
Essentially, I am trying to understand the reasoning for why conflicting changes are blocked in MySQL instead of failing them immediately. At first glance, blocking doesn't seem to provide much benefit over failing immediately:

The client can always retry if it is important to commit the data
The client has to wait till any conflicts are resolved in both cases

Overall, it seems to me like a system that involves no blocking locks, purely relying on atomically committing changes in order, while failing those that conflict with other committed transactions would fare better in terms of performance. The client will have to implement retries and this could be non-ideal for large transactions, but that could be a special case in which we switch to blocking mode.
Any thoughts and explanations as to what I'm missing here?

"The client can always retry" - yes it can. However, the transaction could have performed an arbitrarily large amount of work before the read conflict was detected, which would all have to be re-done on a re-try with no guarantee of success the second time. It may even face starvation if other transactions again get to the locked resource before it does. By blocking instead of failing the transaction goes into a queue for the shared resource, guaranteeing it will eventually get to make progress.
"The client has to wait .. in both cases" Let's consider two scenarios where the other transaction that is locking the desired resource is either very short-lived or very long-lived. In the former the lock is removed quickly and if our transaction is blocked we can quickly progress once more. Had we failed we would have had to re-do all our existing work (which may have been little or lots, who knows) before we could progress. With a long-lived blocker we may get back to requesting the lock only to find it is still held. Then we fail again and repeat. Now we're in a busy-wait loop, burning system resources but making no progress. Or we may get back to requesting the resource to find it is free. Then we can make progress, but are no better off than had we blocked and queued.
"committing changes in order" - what order? When they're submitted? Then my really fast one-row write has to wait behind your 4TB bulk load even though there are no rows in common? That's not good for latency.
There are a class of protocols called multi-valued concurrency control dealing with these scenarios. They excel at some workloads and suffer under others. I enjoyed the Carngie Mellon series on this, lectures 3-5 specifically.


How to check innodb history list length? The output for show engine innodb status has the history list length as a number, but that output is hard to parse, and there's just too much data when all you want is a single value. Is the value available somewhere else that can be fetched as a row?

I can't find documentation confirming it anywhere, but I think it's:

select count from information_schema.innodb_metrics where name = 'trx_rseg_history_len';

The closest thing to a confirmation is a bug report referring to this counter as the history list length. Also, the current, min and max values match the logs I have recorded. It would be nice if someone familiar with the MySQL source code could confirm it.