Differences Between Two Different Create Index Commands

It boils down to looking what the default values are.  Lets break this down:

CREATE UNIQUE NONCLUSTERED INDEX [DEID_MAP_IDX1] ON [dbo].[DEID_MAP]

nonclustered is specified here.  The default (i.e. nothing specified) is nonclustered.  So unless clustered is specified it will default to nonclustered.  So that's the same in both scripts.

[dbo] is specified here explicitly.  As for the second un-specified CREATE INDEX then it all depends on what the current user's default schema is.  Only you can answer that at the moment, so that may or may not default to dbo.

WITH (
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY  = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

PAD_INDEX: the default is OFF, so unspecified will be the same in the second script as it is in the first.

STATISTICS_NORECOMPUTE: the default is OFF, so the second script unspecified has the same value.

IGNORE_DUP_KEY: the default is OFF, so the second CREATE INDEX is identical with this parameter.

ALLOW_ROW_LOCKS: the default is ON, so the second CREATE script has the same behavior.

ALLOW_PAGE_LOCKS: the default is ON...the second script has identical behavior.

... ON [PRIMARY]: just like the default schema one, this all depends on what your default filegroup is.  If PRIMARY is the default filegroup, your second CREATE INDEX script will also create the index on PRIMARY.  If PRIMARY is not the default filegroup, then it will be a different filegroup, as an unspecified filegroup will go to the default filegroup.

All of this information and default values can be found on this BOL reference here.


How can we have IIS talk to SQL Server with TLS1.0 disabled?

I've resolved my problem. Not sure if all of the hurdles are things DBAs would normally encounter, but since people are often interested in resolving the whole problem, and this seemed like a series of issues others might encounter (given the various sources of the problems), here goes:

Put IIS on a separate box. This allows IIS to have TLS1.0 disabled while SQL Server has it enabled.
Disable TLS1.0 on the IIS box using John Louros' PowerShell script (not NARTAC's IISCrypto tool).
At this stage I started getting System.InvalidOperationException: This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms..  FIPS-compliance is on by default for .NET apps, so others may well encounter this.  Since I didn't need FIPS compliance, I tried adding <enforceFIPSPolicy enabled="false"/> to web.config as per this post, which didn't fix the problem.  I then followed the accepted answer from this post.  I later ran into difficulties, and undid both these changes but the FIPS error didn't recur.  Sorry, that's not very enlightening.
Then I got System.Security.Cryptography.CryptographicException, which I fixed by changing web.config as suggested here. I replaced <machineKey validationKey="AutoGenerate,IsolateApps" decryptionKey="AutoGenerate,IsolateApps" validation="3DES" decryption="3DES"/> (which was suggested in this old Microsoft KB article) with <machineKey validationKey="AutoGenerate,IsolateApps" decryptionKey="AutoGenerate,IsolateApps" validation="HMACSHA256" decryption="AES" />.

Then - hurrah! - everything was working again.

Edit

I read Aaron Bertrand's post which refers to this answer.  For anyone coming here from there, I should point out that my testing was before Microsoft's late-January-2016 announcement of TLS1.2 support. I'll try to get around to re-testing with any updates I didn't previously have from that announcement and with IIS and SQL on the same box again, and post my findings here.

(I'm not especially optimistic about being able to co-locate IIS and SQL because from what I had read, I already had TLS1.1 and TLS1.2 support in SQL Server 2014 due to having SP1 CU1, so these latest updates aren't adding anything new in theory. Also, I have an un-tested hunch that since IIS and SQL are on separate boxes, I could have any old version of SQL Server, without needing TLS1.1 support.  If anyone has any thoughts on that I'd be glad to hear them.)


Database user permissions: GRANT VIEW DEFINITION but DENY on some object types

You can create a role and then grant / revoke permissions to it. Any user that is a part of the role will inherit the permissions.

Below is an example to get you started :

 -- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
 ---- Grant access rights to a specific schema in the databas
GRANT
      SELECT

ON SCHEMA::dbo
      TO TableSelector
GO

-- Add an existing user to the new role created
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO

-- Revoke access rights on a schema from a role
DENY ALTER  -- you can customize here ...
ON SCHEMA::dbo
      TO TableSelector


How to set auto growth in SQL Server 2016?

I see the option on my UI? but I had to scroll, failing that have you tried running the TSQL

USE [master]
GO
ALTER DATABASE [YOURDB] MODIFY FILE ( NAME = N'TheFile', FILEGROWTH = 10240KB )
GO


Entity-Relationship Diagram for a scenario involving Books, Authors and Countries

as far as I can see,

you need two separate link tables as these are unrelated, one for linking many books to many authors, and one for linking many countries to many books.

linkTable Author-Book
idOfAuthor | idOfBook
 1         | 1

linkTable2 Book-Country
 idOfBook   | idOfCountry
 1          | 1


sys.objects column [Type] weird value 'ST'

Well, I don't know what ST stands for, but based on the naming convention I would have to guess it's some kind of system-generated statistic.

You could check to see if there is, by chance, some kind of text associated with the object if it is a module, e.g.:

SELECT OBJECT_DEFINITION(613577224);

SELECT definition FROM sys.all_sql_modules
  WHERE object_id = 613577224;

You could also try:

EXEC sp_help N'dbo._ST_OEA33...';

Finally, you could try to brute force discover this object in any of the catalog views. This script tries to find any view that contains a row where the object_id column is equal to that value.

CREATE TABLE #v(v sysname);

DECLARE @sql nvarchar(max) = N'', @obj int = 613577224;

SELECT @sql += N'INSERT #v
    SELECT DISTINCT ''sys.' + name + N'''
      FROM sys.' + QUOTENAME(name) + N'
      WHERE [object_id] = @obj;'
  FROM sys.all_views AS v
  WHERE EXISTS
  (
    SELECT 1
      FROM sys.all_columns AS c
      WHERE c.name = N'object_id'
      AND c.[object_id] = v.[object_id]
  );

EXEC sys.sp_executesql @sql, N'@obj int', @obj;

SELECT v FROM #v;

DROP TABLE #v;

If this yields nothing, you could expand it to include all int-based columns across all catalog views, since sometimes object_id values are stored in columns with different names, like referenced_major_id or parent_object_id, and any results here might yield clues as well.

SET NOCOUNT ON;

CREATE TABLE #v(v sysname);

DECLARE @sql nvarchar(max) = N'', @obj int = 613577224;

SELECT @sql += N'INSERT #v
    SELECT DISTINCT ''sys.' + v.name + N'''
      FROM sys.' + QUOTENAME(v.name) + N'
      WHERE ' + QUOTENAME(c.name) + N' = @obj;'
  FROM sys.all_views AS v
  INNER JOIN sys.all_columns AS c
  ON v.[object_id] = c.[object_id]
  WHERE v.[schema_id] = 4
    AND c.[system_type_id] IN (56,127)
    AND v.name NOT IN (N'syscolumns')
    AND v.name NOT LIKE N'dm_fts_%';

EXEC sys.sp_executesql @sql, N'@obj int', @obj;

SELECT v FROM #v;

GO
DROP TABLE #v;


Thanks to the insight of Akina, who suggested to use HEX() to check the field, I found an extra '0A' byte at the end of one of the values.

After removing the primary key constraing (to avoid the temporary duplicate id), I used:
 UPDATE table1 SET id = TRIM(TRAILING UNHEX('0A') FROM id);
And was able to solve it.

PS: For future googlers, using SELECT id FROM table1 WHERE id like 'nm0410331%' could make me note my silly problem too...


Set variable inside cursor for to use in select statement

DECLARE statement must posess at the beginning of BEGIN-END block, before any other statements. Use nested BEGIN-END:

DELIMITER $$

drop procedure if exists `getAllBalanceRowsUpdate`$$

CREATE PROCEDURE `getAllBalanceRowsUpdate`(
IN `p_ind` INT(20),
IN `p_scode` VARCHAR(200)
)

BEGIN

    DECLARE finished INT DEFAULT 0;
    DECLARE balanced DECIMAL(20,3) DEFAULT 0;
    DECLARE orderId INT DEFAULT 0;

    select @redeem_unit = (SELECT sum(allotted_unit) from orders where investor_id=p_ind and scheme_code=p_scode and type='R' and status in ('approved', 'awaiting'));
    select @balance=0;
    select @redeem_unit = round( ifnull( @redeem_unit, @redeem_unit:=0), 3 );

    BEGIN  -- add nested BEGIN-END block

        DECLARE curBalRows CURSOR FOR
        select if( allotted_unit >= @redeem_unit, @balance:= allotted_unit - @redeem_unit, @balance:= 0 ) as balanced, if( allotted_unit >= @redeem_unit, @redeem_unit:=0, @redeem_unit:= @redeem_unit-allotted_unit ) as leftunit, order_id, allotted_unit, allotted_nav, allotted_nav*@balance as balanceAmount, datetime from orders where investor_id = p_ind and orders.scheme_code =p_scode and type in ('L', 'S') and status = 'APPROVED';

        DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

        OPEN curBalRows;

        getRows: LOOP
            FETCH curBalRows INTO balanced, orderId;
            IF finished = 1 THEN
            LEAVE getRows;
            END IF;

            UPDATE orders SET balance=balanced where order_id=orderId;

        END LOOP getRows;
        CLOSE curBalRows;

    END;  -- finalize nested BEGIN-END block

END $$

DELIMITER ;

PS. Remember - declared construction exists only within the block where it is defined. So whole code between cursor declaration and cursor closing must be included into the block - finalizing END cannot be placed earlier.


If your table is InnoDB format, then try pt-online-schema-change (from Percona Toolkit), for example:

pt-online-schema-change
    --charset utf8 \
    --alter 'ADD COLUMN baz INT UNSIGNED' \
    --execute -h db-test -u username -p password D=foo,t=bar


Troubleshooting high disk read - buffer cache query

So this query isn't going to be using an index, so will do a full table scan

Yes it will definitely do table scan if there is no index.

  Will this table then be kept in the buffer cache after this query is executed? Or will it continually read from disk?

No whole table might or might not be kept into buffer cache depending on size and whether SQL Server finds all such pages during read ahead reads. SQL Server would try to bring as much page as possible which it can do with read ahead reads. The amount of read ahead pages SQL Server can read in enterprise edition is much more and efficient than standard edition. So its quite possible that SQL Server would read not only page that holds row but more such pages so it might not do any Physical reads but since most pages which belong to table are bought into memory (by read ahead) and since index is not present SQL Server will do some good amount of logical reads to find the row

Example:

I run a query like below on Developer edition which is same like Enterprise edition. Table T1 has no index

set statistics IO ON
set statistics TIME ON
select * from T1 where col1 =2

you would see following in message section of output

  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time =
  0 ms.

  (1 row(s) affected) Table 't1'. Scan count 1, logical reads 164,
  physical reads 0, read-ahead reads 170, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:    CPU time = 15 ms,  elapsed time = 134
  ms.

The table structure is almost same as your table and query as well. So you can see SQL Server did 170 read aheads and 164 logical reads. 170 read ahead brought all the pages that were associated with table now since all pages were in memory SQL Server scanned all pages to locate the row doing 164 logical reads.

Remember every read is logical read, physical reads are just there to show that page was brought into memory using physical I/O and read ahead reads do not count under physical reads.


Maintaining multiple build versions of asp .net / SQL Server applications

This would be an unusual deployment pattern: normally you'd want to have 1-to-1 correspondence between an application and a database. This mainly is because you'd want your development environment to mirror your future production environment as much as possible.

If you cannot create databases on the server you could still look at pre-creating a set of deployment slots (e.g. QA1-QA5) and re-using them.

One thing that might help would be something like DbUp - it allows you to easily deploy just the delta scripts that are needed.
Alternatively you could look at DB change management software like DBGhost or RedGate SQL CI.


Encrypt and decrypt text using the same key

Do not attempt to copy&paste the encrypted value.  Do not try to put the encrypted value in any kind of TEXT field, only BLOB (or BINARY).


Correlated Subqueries: resolving ambiguous names

It resolves the column names in scope order. Inside the sub query columns will be resolved against those from the artists table if there is such ambiguity.

This can sometimes be quite useful though it can also be quite dangerous.

I usually use table aliases and two part names to avoid any unexpected issues and make it clearer to the reader which table each column is from.

SELECT p.id,
       p.title,
       (SELECT a.name
        FROM   artists a
        WHERE  p.artistid = a.id) AS artist
FROM   paintings p;


How to show how many times the server was restarted on SQL Server?

Well, I think this can help:

you create a table, insert errorLog data inside this tabel and just select it:

create table ErrorLog (
LogDate datetime,
ProcessInfo varchar(100),
Text varchar(max))

insert into errorlog  EXEC sp_readerrorlog

select * from errorlog
    where Year(logdate)='2018'
    and text like '%Something%'

Or with a count:

select count(*) from errorlog
        where Year(logdate)='2018'
            and text like '%backup%'

We just need to use a string that is used during a server restart. Maybe '%Server name is%'. I can see this string on every restart.


refreshing 500k rows per day, what primary key to use?

I'd stick with BIGINT.

An INT takes up 4 bytes of space. On 500,000 rows, that works out to 2 million bytes, or just under 2MB per day of overhead (which is practically nothing these days).

Double that for BIGINT, which is 8 bytes in size, so 500,000 rows will need under 4MB of space.

We generally recommend BIGINT if you're going to add (or change) over 2 billion rows over the lifetime of the table or index.

While INT supports a range of 4 billion values from roughly -2 billion to 2 billion, people generally start their IDENTITY value at 0 or 1, cutting out half of the available range.

In a case where you're doing 500,000 a day, a BIGINT might be better for you. It's a good trade-off between storage and data churn in the medium to long term.


How to get row count from all tables of a schema ( without using information schema )

This is actually a very good question since MyISAM stores the count in its header, whereas InnoDB requires a full count (See my answer to the post Why doesn't InnoDB store the row count?)

SOLUTION

Use INFORMATION_SCHEMA.TABLES to create the SQL for counting each table

ALL DATABASES

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema NOT IN ('information_schema','performance_schema','mysql')) A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;

CURRENT DATABASE

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema = DATABASE()) A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;

SPECIFIC DATABASE (like mydata)

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('SELECT ',QUOTE(db),' table_schema,',QUOTE(tb),
' table_name,COUNT(1) table_rows FROM `',db,'`.`',tb,'`') SEPARATOR ' UNION ')
INTO @CountSQL
FROM (SELECT table_schema db,table_name tb FROM information_schema.tables WHERE
table_schema = 'mydata') A;
SELECT @CountSQL\G
PREPARE s FROM @CountSQL; EXECUTE s; DEALLOCATE PREPARE s;

To see the SQL that is generated run this

SELECT @CountSQL\G

GIVE IT A TRY !!!

See docs at https://dev.mysql.com/doc/refman/8.0/en/tables-table.html


What version of PostgreSQL first implemented autovacuum?

Prior to PostgreSQL 8.1 it was an extension; 8.1 moved autovacuum into the distribution and away from pg_autovacuum, from the release notes

Move /contrib/pg_autovacuum into the main server (Alvaro)
Integrating autovacuum into the server allows it to be automatically started and stopped in sync with the database server, and allows autovacuum to be configured from postgresql.conf.

But concurrent vacuuming and enabled-by-default autovacuum was introduced in 8.3,

Autovacuum is now enabled by default (Alvaro)
Several changes were made to eliminate disadvantages of having autovacuum enabled, thereby justifying the change in default. Several other autovacuum parameter defaults were also modified.


Yes, plenty :)

You will have to setup encryption from the application server to the database server as the password is passed in clear text.
All login requests from the internet will result in an expensive authentication request to the database server.
You are not able to use connection pooling which will result in having a single connection for each user accessing the database.


Alan, the format you are showing does not seem to correspond with one of the compatible ones expected by pt-query-digest.

This one is the one expected:

$ cat slow.log
/usr/sbin/mysqld, Version: 5.6.20-68.0-log (Percona Server (GPL), Release 68.0, Revision 656). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 140912 16:10:36
# User@Host: root[root] @ localhost []  Id: 116621
# Schema:   Last_errno: 0  Killed: 0
# Query_time: 11.000250  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 64
SET timestamp=1410538236;
SELECT sleep(11);

Yours looks like the CSV export of the slow query log as written into table (not file) -log_output = TABLE:

mysql> desc slow_log;
+----------------+---------------------+------+-----+-------------------+-----------------------------+
| Field          | Type                | Null | Key | Default           | Extra                       |
+----------------+---------------------+------+-----+-------------------+-----------------------------+
| start_time     | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host      | mediumtext          | NO   |     | NULL              |                             |
| query_time     | time                | NO   |     | NULL              |                             |
| lock_time      | time                | NO   |     | NULL              |                             |
| rows_sent      | int(11)             | NO   |     | NULL              |                             |
| rows_examined  | int(11)             | NO   |     | NULL              |                             |
| db             | varchar(512)        | NO   |     | NULL              |                             |
| last_insert_id | int(11)             | NO   |     | NULL              |                             |
| insert_id      | int(11)             | NO   |     | NULL              |                             |
| server_id      | int(10) unsigned    | NO   |     | NULL              |                             |
| sql_text       | mediumtext          | NO   |     | NULL              |                             |
| thread_id      | bigint(21) unsigned | NO   |     | NULL              |                             |
+----------------+---------------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.00 sec)

I may be wrong, but as far as I know, that format is not available to be parsed (the only formats available are: slowlog, binlog, genlog, rawlog and tcpdump).

However, it is feasible to transform your format to the slow log format, as all the info is there.

In the future, You may want to export the table doing the conversion indicated here:

mysql -u user -p -h host.rds.amazonaws.com -D mysql -s -r -e "SELECT CONCAT( '# Time: ', DATE_FORMAT(start_time, '%y%m%d %H%i%s'), '\n', '# User@Host: ', user_host, '\n', '# Query_time: ', TIME_TO_SEC(query_time),  '  Lock_time: ', TIME_TO_SEC(lock_time), '  Rows_sent: ', rows_sent, '  Rows_examined: ', rows_examined, '\n', sql_text, ';' ) FROM mysql.slow_log" > /tmp/mysql.slow_log.log

(This is because I assume that you are logging into a table because you are using RDS. if not, just use the log_output = FILE option)


SQL Server Simplify Racing condition query

This is not valid syntax:

IF NOT EXISTS
(
    SELECT @CustomerIdVar = CustomerId  --- this was causing compilation error

You could try:

DECLARE @CustomerIdVar uniqueidentifier;

BEGIN TRANSACTION;

    SELECT @CustomerIdVar = @CustomerId
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE [Name] = @Name

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT dbo.Customer(CustomerId, [Name])
        OUTPUT @CustomerId AS CustomerId
        VALUES (@CustomerId, @Name);
    END;
    ELSE
    BEGIN
        SELECT @CustomerIdVar AS CustomerId;
    END;

COMMIT TRANSACTION;

Or try the merge option I posted in answer to your previous question SQL Server Concurrent Updlock Serializable vs Try Catch.

But these sorts of micro-optimizations are not always as useful as you might think. Test carefully to make sure you actually get better performance.

And avoid nonsense like:

IF NOT EXISTS
(
    SELECT top 1 CustomerId
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE Name = @Name
)

The EXISTS already implies finding at most one row. The top 1 and CustomerId are entirely pointless, and the same as:

IF NOT EXISTS
(
    SELECT *
    FROM dbo.Customer WITH (UPDLOCK, SERIALIZABLE)
    WHERE Name = @Name
)


MySQL upgrade 5.0.88 to the latest version

Why should I upgrade ?

For MySQL 5.1.37 and prior, InnoDB does not take advantage of multiple CPUs/multiple cores. No amount of scaling up of hardware will ever make InnoDB go any faster. MySQL 5.1.38 first introduced the InnoDB Plugin which has many aspects of tuning now available for multiple CPU/multiple core engagement. This is now fully available as native InnoDB in MySQL 5.5./5.6. Here are my past posts on this subject

Sep 12, 2011 : Possible to make MySQL use more than one core?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

  if so, which version should i consider?

Although it was released 2 months ago, I trust MySQL 5.6.10 thus far. I already gave a client that version from MySQL 5.0.51a. Check out this post from Jan 19, 2012 :
Which version of MySQL should I use? . The client's production server is replicating to the new MySQL 5.6 Cluster and waiting for the Client to cutover.

  What are all the known risks

As with any release of MySQL, you should always read the Release Notes. The only thing to worry about is not doing an upgrade correctly. Another risk is forgetting to run mysql_upgrade script to fix the mysql.user table's column layout.

  upgrading step by step is suggested? or binary replacement suggested?

Personally, I am uncomfortable with a binary replacement. I have a method to upgrade from MySQL 5.0.88 straight to MySQL 5.6.10.

STEP 01 : mysqldump all databases except the mysql schema

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > Data.sql

STEP 02 : dump the grants as pure SQL commands

MYSQL_CONN="-uroot -ppassword"
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sql

I have recommended this technique before : importing myisam 5.0 database into a 5.5 innodb server

STEP 03 load each file into a new MySQL 5.6 Instance

Before you can do anything, you have so set a password for root@localhost in MySQL 5.6

The temporary password is located in ~/.mysql_secret. Login as root@localhost using that password. Then, change the password with

SET PASSWORD=PASSWORD('whateverIwantasthenewpassword');

While in the MySQL Client, simply run

source Data.sql
source Grants.sql

That's all that's needed.

Give it a Try !!!

UPDATE 2019-06-24 13:30 EDT

For the benefit of Linux users and in response to the comment from @SherylHohman, I tested the above statements from STEP 01 and STEP 02 without MYSQL_CONN defined (left blank on purpose) in a Vagrant environment:

root@vt-mysql:~# mkdir DBAStackExchange
root@vt-mysql:~# cd DBAStackExchange
root@vt-mysql:~/DBAStackExchange# ls -l
total 0
root@vt-mysql:~/DBAStackExchange# SQLSTMT="SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
root@vt-mysql:~/DBAStackExchange# SQLSTMT="${SQLSTMT} FROM information_schema.schemata WHERE schema_name"
root@vt-mysql:~/DBAStackExchange# SQLSTMT="${SQLSTMT} NOT IN ('information_schema','mysql')"
root@vt-mysql:~/DBAStackExchange# DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}"`
root@vt-mysql:~/DBAStackExchange# MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers --databases ${DBLIST}"
root@vt-mysql:~/DBAStackExchange# mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > Data.sql
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange# SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sqlroot@vt-mysql:~/DBAStackExchange# SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
root@vt-mysql:~/DBAStackExchange# mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | mysql ${MYSQL_CONN} -AN | sed 's/$/;/g' > Grants.sql
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange#
root@vt-mysql:~/DBAStackExchange# ls -l
total 4056
-rw-r--r-- 1 root root 4146556 Jun 24 10:22 Data.sql
-rw-r--r-- 1 root root    1326 Jun 24 10:23 Grants.sql
root@vt-mysql:~/DBAStackExchange# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.6.33-0ubuntu0.14.04.1 (Ubuntu)

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

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

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

mysql>

The statements I posted here 6 years ago still work in Linux. I ran these in MySQL 5.6.33


Postgres: How can I force index usage for view with latest rates?

Your view gives the correct answer, while your CTE gives the wrong answer, using the oldest date, not the newest.

If you want to use an index scan (although in my hands it doesn't actually make a difference in performance), specify DESC for both ORDER BY columns, or create an index for (currency, date DESC).


Does size of one particular column in a database affect query performance?

A short answer would be: Yes, it affects performance.

A database stores it's data inside formatted database blocks. These blocks are the basis of all caching mechanism, so caches will store database blocks. Querying the database will move some read blocks into the query cache.

You can imagine, the smaller a block is, the more blocks can stay safely inside the caches without having to drop them for new blocks from new queries.

However, I'd say that in many scenarios that store data this way, performance differences of the kind I described might not be the most prominent factor for the underlying system. A benchmark might find differences, but for a system to react noticeably faster you need at least 10% faster responses (the number differs from study to study), and I'm not certain excluding the text column to it's own table will give you that.

On the other hand, if the column would be indexed, that might make matters different. But as I see no index other than the primary key, I see no reason to refactor here - other than that such a column is considered bad practice.


Storing Routes and Locations in Graph using SQL Server 2017

SQL Server's graph capabilities are -- how can I put this nicely -- limited. At the moment it is really just a little syntactic sugar in the new MATCH clause. There is no support for any serious graph-oriented algorithm.  To achieve your goals you will end up using recursive CTEs, which do not play nicely with MATCH. While a solution is possible it will not be elegant. Sorry to be the bearer of bad news.

To learn about graph design & algorithms I would suggest starting with a specific graph DBMS. Neo4j springs to mind. Otherwise you will be spending all your time in work-arounds and never see the big picture.


The error message says right syntax to use near 'BINARY LOGS TO 'mysql-bin.xxx' but you mentioned use of PURGE MASTER LOGS TO ...?

Try using PURGE MASTER LOGS TO mysql-bin.xxx instead of using 'PURGE BINARY ....' as the latter was added to MySQL v4.1.1 and if your version is any lower, PURGE BINARY ... won't work.


I'm not exactly sure what you are asking, but I would suggest that you use aliases and ANSI joins for your queries:
SELECT mc1.member_id AS member_id
      ,c1.title AS title
      ,c1.type AS type
      ,c1.sort_order AS sort_order
      ,MaxTable.effective_date AS effective_date
FROM members_certifications mc1
JOIN certifications c1
    ON mc1.certification_id = c1.id
JOIN (SELECT mc2.member_id AS member_id
           , c2.type AS type
           , MAX(mc2.effective_date) AS effective_date
      FROM members_certifications mc2
         , certifications c2
      WHERE mc2.certification_id = c2.id
      GROUP BY c2.type, mc2.member_id
     ) MaxTable
    ON c1.type = MaxTable.type
   AND mc1.member_id = MaxTable.member_id
   AND mc1.effective_date = MaxTable.effective_date
ORDER BY  MaxTable.member_id, c1.sort_order, MaxTable.type, c1.title;

Regarding your second query in the fiddle, there is a slight difference between:
1000059 and 100059

So if you change that to:
SELECT mc1.member_id AS member_id
      ,c1.title AS title
      ,c1.type AS type
      ,c1.sort_order AS sort_order
      ,MaxTable.effective_date AS effective_date
FROM members_certifications mc1
JOIN certifications c1
    ON mc1.certification_id = c1.id
JOIN (SELECT mc2.member_id AS member_id
           , c2.type AS type
           , MAX(mc2.effective_date) AS effective_date
      FROM members_certifications mc2
         , certifications c2
      WHERE mc2.certification_id = c2.id
      GROUP BY c2.type, mc2.member_id
     ) MaxTable
    ON c1.type = MaxTable.type
   AND mc1.member_id = MaxTable.member_id
   AND mc1.effective_date = MaxTable.effective_date
WHERE mc1.member_id = 1000059
ORDER BY  MaxTable.member_id, c1.sort_order, MaxTable.type, c1.title;

does that give you the expected answer?
MySQL 8+ supports window functions which are far more efficient. Instead of doing a join against a sub-select you can use a window function to determine max_effective_date, and then filter with that:
SELECT member_id, title, type, sort_order, effective_date
FROM (
    SELECT mc1.member_id AS member_id
          ,c1.title AS title
          ,c1.type AS type
          ,c1.sort_order AS sort_order
          ,mc1.effective_date
          ,Max(effective_date) over (partition by member_id, c2.type) AS max_effective_date
   FROM members_certifications mc1
   JOIN certifications c1
        ON mc1.certification_id = c1.id
) AS T
WHERE max_effective_date = effective_date
ORDER BY member_id, sort_order, type, title;


Copy data from SQL Server 2000 to SQL Server 2014 Express

Copying ONLY data does not copy the keys and indices, since these are metadata describing how to treat the data.

The following article suggests that you create a SQL Server 2008 instance, backup the SQL Server 2000 database and restore to the SQL Server 2008 database. Then backup the 2008 database and restore it to SQL Server 2014.  (Be sure to check Service Pack requirements).

Upgrade path: SQL Server 2000 SP4 to SQL Server 2008
Upgrade path: SQL Server 2008 SP3 or later to SQL Server 2014

http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx

Then you should have the SQL Server 2000 database upgraded to SQL Server 2014.

Since you want Keys and Indices as well, you need the metadata.  Another approach is to script out all the objects (tables, views, procedures, etc) from your SQL Server 2000 database.  Then execute the create script on your 2014 server. (Fixing any problems that you find.)  After that transfer the data via linked server (per Mark Sinkinson), SSIS packages, BCP, etc.


Does sqlite preserve COLLATE on tables in a view?

In sqlite3 VIEWs keep the column collation from the underlying table. Proof: http://sqlfiddle.com/#!5/52669/3


How to link a customer to a country when the city/state isn't known

Use a variation of number 2 - a location table with a involuted (self-referencing)relationship, but add something to make the hierarchy management easier.  For example, use nested sets/visitation numbers (see here) or an adjacency list.

The advantages of this approach are that:

You have a single foreign key from your transaction to your location table.  This keeps your queries simple insofar as you don't need multiple joins and either/or logic.
The foreign key can point to as vague or as specific a location as you need.
You can easily traverse the hierarchy (since you'd be using a technique like nested
sets) to get the level of geography of interest.
You can accommodate countries that have more or less than one
geographical division between the country and the city, without
having to create dummy entries or duplicating data.

I don't know what your ORM is, but it should be able to handle a self-referencing relationship and a partitioning attribute (i.e. GeographyLevel = { country, state, city })


Is there any difference between Rem and -- comments for SQL*Plus?

The difference is that -- and /* */ can be used in a PL/SQL block, while REM[ARK] cannot.  The following will work in SQL*Plus:

REM comment
-- comment
/* comment */
begin
   DBMS_OUTPUT.PUT_LINE('Test'); --comment
   DBMS_OUTPUT.PUT_LINE('Test'); /* comment */
end;
/

These will not:

begin
   DBMS_OUTPUT.PUT_LINE('Test'); REM comment
end;
/

begin
   REM comment
   DBMS_OUTPUT.PUT_LINE('Test');
end;
/

The 11.2 documentation on all comment types has more comment information.  The basics are...

  You can enter comments in a script in
  three ways:

  using the SQL*Plus REMARK command for single line comments.
  using the SQL comment delimiters /*... */ for single or multi-line
  comments.
  using ANSI/ISO (American National Standards Institute/International
  Standards Organization) comments - -
  for single line comments.

The documentation also includes notes on four places that comments should not be used, but these do not include any further differences.


SQL Server EXECUTE AS impersonation as RBAC method

As a DBA by night, software developer by day, my preference is managing user security in the database (regardless if that's in addition to managing it elsewhere). SQL Server hooks directly into Windows Authentication and therefore supports both Active Directory User and Active Directory Group objects for security.
A lot of times its logical to map a specific AD Group to a specific database, schema, or subset of entities. E.g. you might use schemas to define related entities by application, and then grant read access to a certain AD Group and write access to another AD Group (with the respective SQL permissions) for one schema and visa versa for another schema as a way to separate users' data access across applications.
There's a lot of ways to splice how you can use security in SQL Server and again, coupled with AD Groups (or if AD groups aren't being used, then SQL roles can take their place) it becomes a lot more manageable than keeping track of individual permissions on each user.
Also from a security perspective, by having only one account that can access anything on the SQL Server you put yourself at higher risk because one account has more access than is needed and if became compromised would potentially be more damaging. One account being used in just as many instances as if you were using native Windows Authentication passed through to SQL Server is the same size of attack surface, and in fact even more risky assuming you're storing the dedicated account's password somewhere in the application. Whereas with Windows Authentication passed through to SQL Server, you don't need to store any credentials in the application.
Additional Info (from extended discussion):
Impersonation
Regarding Impersonation in ASP.NET Core, it is possible to still use legacy impersonation per Microsoft's docs (especially for connecting to a database): Configure Windows Authentication in ASP.NET Core (see the Impersonation section at the end)
Doing so is not really recommended for a few reasons, one being legacy security concerns, and another being it may limit your options in how you write your apps and the implementation is cumbersome in itself.
Alternative solutions for Impersonation are:

Disabling "Windows Authentication" and re-enabling "Anonymous Authentication" in IIS, and then scaling out your own login page for authentication so you can get a handle on the user (using modern encryption standards on the password.)
Implementing Token based authentication.
Adding the Identity role for Entity Framework Core as seen in this Microsoft example: Create an ASP.NET Core web app with user data protected by authorization

One Dedicated User vs Multiple Users / Groups Database Permissions Security Analogy
Premise: A house that has 200 doors and 1 skeleton key under the mat that opens them all is less secure than 200 different keys which each open only their own subset of door(s) that are kept on the key ring of each user's pocket.
One Dedicated User: The 200 door house has one main door you have to go through first. 200 people each have their own key to get through that door. Once you get through that door, the skeleton key for all 200 other doors in the house is under the mat on the inside of that door.
Individual User / Group Permissions: If you get rid of that skeleton key (individual user) and give all 200 users their own unique key that only opens that front door and specific doors in the house, now you've limited the scope of access each user has to the house and theoretically reduced your attack vector. Basically, all you've done is map each user's key to a limited set of doors.
(You're no longer hiding a skeleton key under the mat.)
Think of Active Directory in the same way. Just because Active Directory stores a mapping of users to groups, doesn't mean there's an additional backdoor in your applications. The point of security threat is where your users actually authenticate which is at the application level. (Mapping permissions in SQL is almost exactly like mapping them in AD, except SQL can leverage the existing Groups in AD.)
Takeaway Point
While permission management can be a pain, it isn't any more complex to do it in the database if you're already doing it at the application level. This assumes the users have some sort of AD (or equivalent) permissions already implemented. If their permissions are already configured in AD then the heavy lifting is already done, it's just in the DBA's hands to map each relevant AD Group or set of Users to correlating SQL permissions, but it's really quite simple to do on the database side.


To get this working:

Change the ROWTERMINATOR to '\n'. I'm not sure why '\r\n' isn't
working, but '\n' works with your example data.
Remove KEEPNULLS. NULL values in the CSV for a nullable column will still be brought through as NULL to the database, but this
prevents the NULL for IMPORT_DATE coming through.
Change IMPORT_DATE to have a DEFAULT value of GETDATE() but leave it as nullable (otherwise the import fails)
Add IMPORT_DATE as a column in the CSV
Add a new line after your last data row to ensure the final field is terminated in a way that SQL can detect when importing.

After these changes, the data imports correctly. NOTE: If you want IMPORT_DATE to come through as NULL instead of using the current date, skip step 3.


Associating large numbers of Items with large numbers of Users in Mongo

Consider two collections (or entities) users and items. And, they are in a one-to-many relationship - users is the 'one' side and the items is the 'many' side.

items: [ 1.. millions ]
  itemId: <id value>,
  name: <some name>,
  description: <...>,
  other_fields:  <...>

users: [ 1.. millions ]
  userId: <id value>,
  name: <some name>,
  email: <some value>,
  items: [ 1.. 10000 ]

  Embed the item id's in each User, but then i'd be often pulling out
  more than I need, and inserting/deleting ID's would also be
  inefficient.

This is a often used approach - embedding the items ids within the users document. This is done by storing the items ids within the users document as an array. For example:

items: [ itemId-1, itemId-2, ..., itemId-5000 ]

With this approach:

You can retrieve all items associated with a user.
For more details of specific item, lookup the items collection (this can be an aggregation lookup operation which queries both collections as a "join").
All the related information for a user is within one document.
For efficient querying, create indexes on the most used query filter fields for the users (e.g., userId, name) and the items array field.
Indexes on array fields are called as Multikey Indexes.

Another Approach:

You can also consider storing more information (as sub or embedded documents) about the items in the items array. For example,

items: [ { itemId: <id-1>, itemName: <some name> }, { itemId: <id-2>, itemName: <some name 2> }, ... ]

In this use case, the itemName is often used when you query a user and related items. This way you can avoid accessing the items collection for the item's name. This approach works when the field like itemName does not change and mostly stable (fields like item price and quantity can change often, and description might not change often).

  ... but then i'd be often pulling out more than I need, and
  inserting/deleting ID's would also be inefficient.

When you query for a user (users collection), you have the option to restrict the fields (user related and also items array) to get as result of a query. MongoDB's projection allows this restricting the fields to retrieve within a single document. This also applies to the items array field - only specific item(s), specific number of items, specific fields for an item, etc.

Regarding the inserting / deleting / updating operations - MongoDB has methods to update a single or multiple fields (including array fields) for a document as atomic operation.

There are specific operators (referred as array operators) to work with arrays (with scalar or composite values) - for inserting, updating, deleting and querying (CRUD operations) on the array fields. Creating indexes on array fields will make these operations efficient.


Is it possible to change collation for an existing database with PostgreSQL?

No, that is not possible.
The reason is that there may be data structures in the database that depend on the database's collation: indexes over string data types. If you'd be able to change the collation on the fly, all these indexes would implicitly become corrupted.
You might say that is OK, let's just rebuild all these indexes. Well, if you are ready to do that, there is a way: pg_dump the database, create a new database with the desired collation and restore the dump into the new database. Sure, that will take longer than just rebuilding the string indexes, but the expense is in the same order of magnitude, and this is a safe way.
You may be tempted to meddle with the pg_database system catalog manually, but I can only discourage that: as the saying is, if you do that and things break, you get to keep both pieces.


query cache hit value is not changing in my database

You have two things running into each other

Issue #1 : Differing Queries
Issue #2 : InnoDB Storage Engine

Issue #1 : Differing Queries
Look at your three queries
select * from drugs_info limit 1000;
select * from drugs_info limit 10000;
select * from drugs_info limit 15000;

Each query stores a unique result (if the results are stored). If the results are too big (as in the case of the second and third queries), the result sets must be retrieved again.
You could potentially solve this with a bigger query cache, which must be big enough to hold each query's result set. This leads us into ...
Issue #2 : InnoDB Storage Engine
Unfortunately, the InnoDB Storage Engine likes to play traffic cop with the query cache. I had discussed this before in my answer to Is the overhead of frequent query cache invalidation ever worth it? where I learned this pages 213-215 in the book (Second Edition)

If you really want the query cache, you have to meticulously set the query cache size to suit your needs. In almost all cases, it is best to set query_cache_size to 0 when dealing with InnoDB.
First, add this to my.cnf
[mysqld]
query_cache_size=0

Then, run this
mysql> SET GLOBAL query_cache_size = 0;

and you should be all set.
Give it a Try !!!


postgresql insert from select query, plus static values

You can put static values into SELECT clause.

INSERT INTO new_tbl (column1, column2, column3)
    SELECT DISTINCT id, 'a string', 0 FROM -- long where clause --;


SLAVE off when loading data into the MASTER from another database

There are several things going on here, and you will need to look into your configuration on both master and slave to establish where there are any of the following options set on the master:

binlog_do_db
binlog_ignore_db

and whether any of the following are set on the slave:

replicate_do_db
replicate_ignore_db

The thing that you should be aware of is that what matters is the current database on the connection where you are performing the database/table creation because DDL is still logged as statements even in row-based binlog.

So if you do:

mariadb> use replicated_database
mariadb> CREATE TABLE ignored_database.table_name ...

this will not get replicated across.

The solution you are looking for is:

1) On the master, set your binlog_ignore_db = ignored_import_database

2) Make sure the data import process connects to the ignored_import_database when it is performing the data loading so that any DDL doesn't make it across, and make sure that there is no database switching ("use database some_unignored_db") inside the import payload.


How can I increase performance for select in select statement

SQL Fiddle

with items as (
    select registered, category_id
    from items
    where
        category_id is not null
        and
        registered < '2013-05-02'
), items_restricted as (
    select registered, category_id
    from items
    where registered >= '2013-05-01'
), category_id as (
    select distinct category_id
    from items_restricted
)
select distinct ir.registered, ir.category_id, "value"
from
    items_restricted ir
    inner join
    (
        select
            category_id, count(*) as value
        from items
        where exists (
            select category_id
            from category_id
            where category_id = items.category_id
        )
        group by category_id
    ) s using(category_id)
order by registered, category_id


Select one row from n number table; Join / Union table with looping

Using the following test data, you may find the procedure underneath useful.  It will need some adjustments.  However, it may give you a starting point (MySQL 5.7).

Test data:

CREATE TABLE `loc_list` (
  `id` int(11) NOT NULL,
  `location_name` varchar(100) NOT NULL,
  `location_table_name` varchar(100) NOT NULL
);

CREATE TABLE `location_1` (
  `delivered_time` timestamp NOT NULL,
  `item` varchar(100) NOT NULL
);

CREATE TABLE `location_2` (
  `delivered_time` timestamp NOT NULL,
  `item` varchar(100) NOT NULL
);

INSERT INTO `location_1` (`delivered_time`, `item`) VALUES
('2017-06-08 14:07:57', 'Bag 1'), ('2017-06-08 14:08:57', 'Bag 2');

INSERT INTO `location_2` (`delivered_time`, `item`) VALUES
('2017-06-08 14:06:57', 'Bag 3'), ('2017-06-08 14:09:57', 'Bag 4');

INSERT INTO `loc_list` (`id`, `location_name`,`location_table_name`) VALUES
(1, 'Location 1', 'location_1'), (2, 'Location 2', 'location_2');

Table for storing the procedure's output:

create table output_(
  id_ integer
, location_ varchar(100)
, table_ varchar(100)
, delivered_time timestamp
, item_name varchar(100)
);

Draft version of the procedure:

--drop procedure findloc;

delimiter //

create procedure findloc()
begin
  declare t_name varchar(100) default '';
  DECLARE done TINYINT DEFAULT 0;
  declare cur_ cursor for select location_table_name from loc_list;
  declare continue handler for not found set done = 1 ;

  open cur_;
  read_loop: LOOP
    fetch from cur_ into t_name;
    if done then
      leave read_loop;
    end if;
    select @t := t_name ;
    set @qry = concat ('
      insert into output_
      (id_, location_, table_, delivered_time, item_name)
      select
        id, location_name, location_table_name,
        delivered_time, item
      from loc_list L, (select * from ', t_name, ') T
      where L.location_table_name = @t
    ') ;
    prepare stmt from @qry ;
    execute stmt ;
    deallocate prepare stmt ;
  end loop;
  close cur_;
end//

delimiter ;

-- clear the table - just in case
delete from output_;
-- execute the procedure
call findloc();

Output (just select from the output_ table):

mysql> select * from output_;
+------+------------+------------+---------------------+-----------+
| id_  | location_  | table_     | delivered_time      | item_name |
+------+------------+------------+---------------------+-----------+
|    1 | Location 1 | location_1 | 2017-06-08 14:07:57 | Bag 1     |
|    1 | Location 1 | location_1 | 2017-06-08 14:08:57 | Bag 2     |
|    2 | Location 2 | location_2 | 2017-06-08 14:06:57 | Bag 3     |
|    2 | Location 2 | location_2 | 2017-06-08 14:09:57 | Bag 4     |
+------+------------+------------+---------------------+-----------+

This may look a bit different from what you'd expect.  However, in your dbfiddle, you had (in the loc_list): location_table_name 'silo_1' and 'silo_2', whereas the timestamps and bags were INSERTed into the tables called 'location_1' and 'location_2', as @RDFozz has pointed out.


How to handle billions of rows in MySQL for biometric project?

Why you are hosed

So, you need to map a 10-byte hash to a 3-byte id, correct?  And there are 2B+ of them?  Nothing else in the table?  (Adding to the table increases the numbers below.)

CREATE TABLE HashToAnimal (
    hash BINARY(10) NOT NULL,
    animal_id MEDIUMINT UNSIGNED NOT NULL,
    PRIMARY KEY (hash),
    INDEX(animal_id)  -- might you need this for maintenance?
) ENGINE=InnoDB.

The hash is very random.  So there are two extreme situations (and not much in between):

The table lives in the buffer_pool.  Accesses would not incur a disk hit (very fast).  2B rows would occupy about 100GB.  This implies more than 100GB of RAM.  (No it won't fit in 50GB; there are several flavors of overhead.)
The buffer_pool is much too small to hold the table.  Now, each SELECT will need a disk hit because caching is 'impossible'.  How many IOPS can you afford on Amazon?  Pessimistically divide that by 3 to get how fast you might be able to fetch rows.

You quoted 1000 fetches took 10 seconds.  That sounds like a cold cache and conventional drives.  100 disk hits per second is a good Rule of Thumb for conventional disks.  (I thought Amazon was all SSD?)  That 10 seconds will get faster as more of the table is cached -- if the buffer_pool is big.  Eventually there won't be any disk hits and my "very fast" will come true.

Another note:  INSERTing into this table has similar caching considerations.  Each insert into the BTree starting with hash (whether PRIMARY KEY or secondary) is very random.  It will be a disk hit if the table is much bigger than the buffer_pool; otherwise it will eventually be cached and fast.

Note:  If Amazon does certain kinds of maintenance, the cache may suddenly become 'cold'.

Hashes, MD5s, GUIDs, UUIDs, etc. are all terrible to use.  You have to pay for either lots of RAM or lots of IOPs.

Thinking out of the box

You usually expect not to get any animals, correct?  So, the preferred test is one of 'existence', not 'fetch'?  So a bit string of 2**80 bits would say which hashes have been seen.  Well, this is really impractical.

Here's a schema that might take 5GB for a table that lets you test the existence of 2B different hashes:

Let x be the first 27 bits of the hash.  Let y be the next 8 bits.

CREATE TABLE CheckHash (
        x BINARY(3) NOT NULL,
        y BLOB NOT NULL,
        PRIMARY KEY(x)
    ) ENGINE=InnoDB;

INSERTing is a bit complicated.  Fetch the row for x, see if y is already in the y column; if not add it.  (If no x row, add one.)

Checking is SELECTing WHERE x=? and looking through y.

There will be some "collisions", but that is OK.

There will be a small percentage of false hits (15%?); you then use the full hash to look them up in the HashToAnimal.  Assuming the percentage is small enough, these disk hits should be small enough to be acceptable.

The goal is to be able to have the smaller CheckHash fully cached, but not worry that HashToAnimal is not well cached.

This table will grow gradually; 4B hashes may take only 8GB.

(If you get beyond, say, 5B hashes, you should rebuild the table with 28 instead of 27.  It's a tradeoff between space overhead versus more CPU time for scanning the blob.)

(Surely there are other techniques.)


Error Code: 1205 Lock wait timeout exceeded in MySQL

innodb_lock_wait_timeout

The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (Until MySQL 5.0.13 InnoDB rolled back the entire transaction if a lock wait timeout happened. You can restore this behavior by starting the server with the --innodb_rollback_on_timeout  option

By default, the transaction will not be rolled back. It is the responsibility of your application code to decide how to handle this error, whether that's trying again, or rolling back.

Also You can see more details of the event by issuing a "SHOW ENGINE INNODB STATUS" on server after getting this error.


MySQL migration physical server VMware virtual server

You would almost certainly be better off doing a fresh install, taking a backup using xtrabackup, and restoring the config files and the backup into the install inside the VM. That way you could even do it with only seconds of downtime by using replication up to the moment you switch over.


Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu)

Have a look at at (the order of the items implies my personal choices)

DbSchema (not free, but not expensive: http://dbschema.com/)
SchemaSpy (http://schemaspy.sourceforge.net/)
Power*Architect (http://code.google.com/p/power-architect/) although development seems to have stopped)

All of them work with PostgreSQL (and other DBMS)

(Note: I have nothing to do with DbSchema. I just think it's a pretty good tool)


You will need to study the BSON specs to create your own functions, or use one of the available libraries that others made already: http://bsonspec.org/implementations.html


When you run a backup with copy_only, it does not interfere with the full/differential backup sequence.

You can think of a with copy_only backup as it never happened.


SQL Server Management Studio 2014 Backup - Set Property Devices Error

Here is my Answer for Error message "To accomplish this action, set property Devices. (Microsoft.SqlServer.SmoExtended)"

Make sure the User Account you are logged in as has DBO rights on both, the MASTER Db and the DB you want to backup.
Using the SQL Express Manager, Expand the SQL Server Objects in the left pane and find "Server Objects" then click on "Backup Devices"

Right Click and create a "New BackUp Device.." and call it the same as the DB to be backed up... pointing to a directory on your server (e.g.: C:\MySQLBackups...)

This will create an "empty file" with the .bak extention. (e.g. C:\MySQLBackups\DatabaseName.bak)

Then right-click on this new device created and select "Backup database"...

The rest is straight forward...

This worked for me... Good luck!"


A index cannot be deferred - doesn't matter if it is UNIQUE or not, partial or not, only a UNIQUE constraint. Other types of constraints (FOREIGN KEY, PRIMARY KEY, EXCLUDE) are also deferrable - but not CHECK constraints.

So the unique partial index (and the implicit constraint it implements) will be checked at every statement (and in fact after every row insert/update in current implementation), not at the end of transaction.

What you could do, if you want to implement this constraint as deferrable, is to add one more table in the design. Something like this:

CREATE TABLE public.booking_status
  ( booking_id int NOT NULL,               -- same types
    check_in timestamp NOT NULL,           -- as in
    check_out timestamp NOT NULL,          -- booking
    CONSTRAINT unique_booking
        UNIQUE (check_in, check_out)
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT unique_booking_fk
        FOREIGN KEY (booking_id, check_in, check_out)
        REFERENCES public.booking (booking_id, check_in, check_out)
        DEFERRABLE INITIALLY DEFERRED
  ) ;

With this design and assuming that booking_status has only 2 possible options (0 and 1), you could remove it entirely from booking (if there is a row at booking_status, it's 1, if not is 0).

Another way would be to (ab)use an EXCLUDE constraint:

ALTER TABLE booking
    ADD CONSTRAINT unique_booking
        EXCLUDE
          ( check_in  WITH =,
            check_out WITH =,
            (CASE WHEN booking_status = 1 THEN TRUE END) WITH =
          )
        DEFERRABLE INITIALLY DEFERRED ;

Tested at dbfiddle.

What the above does:

The CASE expression becomes NULL when booking_status is null or different than 1. We could write (CASE WHEN booking_status = 1 THEN TRUE END) as (booking_status = 1 OR NULL) if that makes it any more clear.
Unique and exclude constraints accept rows where one or more of the expressions is NULL. So it acts as a filtered index with WHERE booking_status = 1.
All the WITH operators are = so it acts as a UNIQUE constraint.
These two combined make the constraint act as a filtered unique index.
But it's a constraint and EXCLUDE constraints can be deferred.


Oracle linked server shows all tables from all schemas in sql server management studio

Just ran this on SQL Server.  As long as you have the correct USER as the credentials to the linked server, it will give you tables that the User Owns, as well as Tables that the user has SELECT access on.

To Prove this, revoke select on one of the tables to that user (On the Oracle side), and it will no longer show in the query you provided.

REVOKE SELECT ON TABLE FROM LINKUSER;

I just did so and got one less table.  As soon as I added the Grant back, the table returned again in the query.


I would avoid msforeachdb it has been known to miss databases, which I have experienced first person.

Either create your own cursor(opportunity to learn something new!), or use Aaron Bertrand's reliable version. sp_ineachdb


Where can I find a drawing of the physical structure of a MySQL Server?

Below screenshot shows MySQL architecture at detailed level.


Is it possible to create Dynamic SQL statements purely in SQLite?

No.
SQLite is designed as a small, embedded database to be accessed from a 'real' programming language. Dynamic SQL is not needed for its intended purposes.


mongoDB won't stop with shutdown or kill

Try the below commands:

kill <process id>
kill -2 <process id>
kill -SIGHUP <process id>
kill -SIGQUIT <process id>
kill -SIGTERM <process id>
kill -SIGKILL <process id>

or

Log on to your database
Switch to admin database
Execute the command db.shutdownServer() to gracefully shutdown mongodb which is the safest way to shut down the DB

Thank you!

-Anban


Powershell: Does System.Data.SqlClient.SqlDataAdapter usange in powershell need some ADO.Net installation?

Put this at the top of your script:

Add-Type -AssemblyName System.Data

Yes it's part of the OS, and no this is not normally required, but there must be some issue from the combination of your OS version + PowerShell version + SQL 2008 R2.


'Logical file name "MyDB" is already in use' Even though the database doesn't exist

If i am able to figure out correctly from below query

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'DDAS_log', FILENAME = N'G:\DDAS_log.ldf')

I believe tempdb to be [DDAS] here..

Also, in the CREATE DATABASE Query

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DDAS')
BEGIN
    CREATE DATABASE [DDAS]
    CONTAINMENT = NONE
    ON  PRIMARY
        ( NAME = N'DDAS', FILENAME = N'E:\DDAS.mdf' , SIZE = 1024000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024000KB  )
    LOG ON
        ( NAME = N'DDAS', FILENAME = N'G:\DDAS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
    END
GO

Above the logical file name of log file seems wrong, instead of LOG ON
            ( NAME = N'DDAS'....) it should have been NAME = N'DDAS_log'..

For more info please refer CREATE DATABASE


Send Email when sql server agent job fails

For 2005 and 2008 you need to configure Database Mail then enable the Agent to use it (SQL Server Agent --> Properties --> Alert System). If problem persists, you can query the system tables related to Database Mail (check BOL for names of tables) for possible more information. If problems still persists you may need to work with your Exchange admin.

For 2000 you need Outlook installed.


Unable to update SQL Server via Access, Reserved error (-7776)

Access (I guess) does not seem too fond of SysStartTime and SysEndTime columns. If I mask them in a view and link the view in Access, both update and insert works. Example:
DROP VIEW dbo.PHYTOCLASS_DIVISION;
CREATE VIEW dbo.PHYTOCLASS_DIVISION as
select  d.DivisionName
        , c.ClassID
        , c.ClassName
        , c.DivisionID as classDivisionId
        , c.ModifiedBy
        , c.ModifiedDate
        , c.Avslutad
from dBotnia20200929.dbo.PHYTODIVISION d
join dBotnia20200929.dbo.PHYTOCLASS c
        on d.DivisionID = c.DivisionID;

For a trivial join like this, SQL Server figures out how to handle insert/update so instead of triggers are not necessary.
The answer in is-is-possible-to-update-sql-server-2016-temporal-tables-from-access indicates that the problem has to do with the precision of the timestamps in Sys... columns. I have not verified that lowering the precision would help, but it may be worthwhile for someone to read that answer


Using Mirroring for incremental backups?

None of the SQL Server backups require downtime. Full, differential, log backup are all online.

So just do a normal run-of-the-mill backup scheme (eg. full weekly, diferential daily, log hourly etc) and go with it. Is incremental and is online.


Access denied error in trying to connect to a MySQL db in Python

You seem to be trying to make an SSH tunnel, but I don't think you're actually using that tunnel for the mysql connection?

I also suspect the mysql error you receive is from a local mysql server, not the remote one you're trying to connect to.

I think you need to do port forwarding and use that port for the mysql connection. See e.g. Enable Python to Connect to MySQL via SSH Tunnelling (stackoverflow.com) or research making SSH tunnels in Python for MySQL connections.


Sudden High CPU Usage On SQL Server 2008R2

Answer converted from comment:

sp_whoisactive shows the following output:

Restarting the SQL Server Service resolved the issue. I think I know why thanks to sp_whoisactive. My theory is we have a query that was running in a loop (the top 8 rows) and that got stuck during the DBCC DBREINDEX that occurred at 12:30AM. This query was using up all of the CPU since it was stuck in that query... Once the server was rebooted, there are hardly any records in the sp_whoisactive. MUCH BETTER!!


SQL SERVER dynamic Query with Like Statement for null values

Try This

DECLARE @articleNo VARCHAR(100)
,@query VARCHAR(8000)

SET @articleNo = NULL
SET @articleNo = COALESCE(@articleNo, '') -- SET to a blank value if NULL, you can't concatenate a NULL value into the string like you were doing.
SET @query =
'SELECT AT.Id AS ArticleId, AT.[Description] AS ArticleName, AT.ArticleNo,
AG.Id AS ArticleGroupId, AG.Name AS ArticleGroup, St.Quantity AS Stock, AB.[Key] AS Attribute, AB.[Value] AS AttributeVal
FROM [dbo].[Articles] AS AT
INNER JOIN [dbo].[ArticleGroups] AS AG ON AT.ArticleGroupId = AG.Id
LEFT OUTER JOIN [dbo].[ArticleAttributes] AS AB ON AT.Id = AB.ArticleId
LEFT OUTER JOIN (SELECT ArticleId, SUM(Quantity) AS Quantity FROM [dbo].
[StockEvent] GROUP BY ArticleId) AS ST ON AT.Id = ST.ArticleId
WHERE
       (@articleNo = ''''   OR AT.ArticleNo LIKE ''%@articleNo%'') '
--DON't use EXEC (@query), you want to make sure dynamic sql is parameterized

EXEC sp_executesql @query
,@params = N'@articleNo VARCHAR(100)'
,@articleNo = @articleNo


Query runs a long time in some newer MySQL versions

Just off the bat, newer versions of MySQL actually improve innodb performance (especially 5.5). I would highly recommend updating to this version if you're going to run InnoDB.

One method you could use to hunt down why it's taking so much longer is using MySQL Profiles

mysql> SET PROFILING=1;
mysql> SHOW TABLES;
mysql> SELECT * FROM foo;
mysql> SET PROFILING=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PROFILES;
+----------+------------+-------------------+
| Query_ID | Duration   | Query             |
+----------+------------+-------------------+
|        1 | 0.09270400 | SHOW TABLES       |
|        2 | 0.00026400 | SELECT * FROM foo |
+----------+------------+-------------------+
2 rows in set (0.05 sec)

mysql> SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000053 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000032 |
| System lock          | 0.000010 |
| init                 | 0.000028 |
| optimizing           | 0.000003 |
| statistics           | 0.000012 |
| preparing            | 0.000008 |
| executing            | 0.000003 |
| Sending data         | 0.000068 |
| end                  | 0.000004 |
| query end            | 0.000007 |
| closing tables       | 0.000008 |
| freeing items        | 0.000013 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000003 |
+----------------------+----------+
16 rows in set (0.04 sec)

This should give you an indication of where it's hanging. From your explain output, you should try to get some indexing on the second and third tables instead of doing full table scans. But without DDL or the actual join columns, I can't suggest anything better than to research indexing strategies.


What we do in LedgerSMB is to have a system of business reporting dimensions (two tables, one of which establishes the dimensions and the second establishes the hierarchies of each dimension).  In your case you could probably just have one table of departments, with a self join to generate the hierarchy (WITH RECURSIVE CTE in a select statement would generate it).

Then you can have a view which generates your hierarchy for it, and you can link your accounts to groups.

This way they can be arbitrarily nested.  You aren't limited to three levels.  You could have 5 or 10 levels with some performance cost and you don't have to change your data.


Assuming you have corrected the slave drift, you can just "start slave" again, but

You may need to skip some transactions, if they have been manually applied to the slave- you can do that with SET GLOBAL sql_slave_skip_counter
You probably have other differences between the master and the slave, because of the operations you mentioned or because of the skipping of transactions. In that case, using pt-table-checksum and pt-table-sync can help you resync the slave without having to reload the full contents.


SSAS ErrorCode="3239575554" Either the user does not have permissions to alter the 'SERVER' object

this was really nonsense.
I right click on ssms and run as administrator, and added the permission there without a problem.


When is a full table scan better than index scan?

When the

table is small enough there is no practical difference
statistically, you'd return most rows anyway

The 2nd case needs qualifying

An index scan will replace an index seek if an index is covering
An index seek or scan with many rows that requires key/bookmark lookups will be expensive and a table scan could be better

Finally

An index scan and a table scan are pretty much the same for clustered indexes


Move log file without taking database offline

There is no way to do this with an online database.

When you move a database file (ALTER DATABASE ... MODIFY FILE), you even get the following message:

  The file "YourFile" has been modified in the system catalog. The new path will be used the next time the database is started.

  The normal way of doing this would be to detach the DB, move the log file then reattach the db.

That's not the "normal" or accepted way I would do it.  To move database files, I do the following:

Run an ALTER DATABASE command to change the location of the file(s)
Take the database offline
Physically move the file(s) to the new location specified in step #1
Bring database online

See this reference on TechNet: Move User Databases


Postgres FOREACH array as composite type

An array always contains a single data type. ['2018-01-01',2] specifies an array with two different types: a varchar and an integer.

To initialize an my_type value you need a row() constructor:

row('2018-01-01',2)::my_item

Elements of that type can then be put into an array:

array[row('2018-01-01',2)::my_item,row('2018-01-02',5)::my_item

or you can simplify that by casting the whole array, which saves you from repeating the ::my_type cast for each element:

array[row('2018-01-01',2),row('2018-01-02',5)]::my_item[]

If you have such an array, there is no need for your own function. An unnest on it will return the result you want:

SELECT x.*
FROM unnest(array[row('2018-01-01',2),row('2018-01-02',5)]::my_item[]) as x;

returns:

field_1             | field_2
--------------------+--------
2018-01-01 00:00:00 |       2
2018-01-02 00:00:00 |       5


There's no one correct way of doing it. What I would do, is to introduce another table on top of what you already have. A table that I would call ReservationHeader, this is the table that would hold a header information and then beneath it I would add another table called RoomReservation which will hold multiple bookings related to ReservationHeader. FInally, I will create another table called Room which holds all data about rooms;

Customer {id (pk), Customer_Name, etc, etc, ....}
ReservationHeader {id (pk), customer_id(fk), reservation_date, etc, etc, ...}
RoomReservation {id (pk), reservation_header_id (fk), room_id (fk), check_in_date, check_out_date, etc }
Room {id (pk), room_number, room_type, etc, etc, ... }

See the diagram below for clarity. You can then add other fields and build on top of that.

You might also might try to think ahead and consider how you are going to query the tables to check for room availability. There are already plenty of good answers covering that stackexchange.


Restricted logins with automatic full permission in databases

You can create a user that has certain non-db_owner fixed database roles assigned, and then you can create a user-defined role which contains additional permissions. (See https://msdn.microsoft.com/en-GB/library/ms189121.aspx)

So in effect, you can create a user that has all the rights of db_owner except say, managing users and dropping database. But I don't think there's a way to directly deny such privileges, you have to instead add everything but them.

Your database creation SP can configure new databases in this way, and you’d have to do it for each existing database.


How could sequence.nextval be null in Oracle?

I'm pretty certain this will end up being an artifact of your code, or the .net driver you are using. I've knocked up a quick demo for you using pure SQL - PL/SQL and never get a lost sequence value. Incidentally the ref cursor you are using is probably unnecessary and likely impacts performance and readability of the code - my demo includes an insert_record2 procedure that consistently performs over 10% faster -in about 26s on my laptop vs 36 for the ref cursor version. I at least also think is easier to understand. You could obviously run a modified version against your test database complete with audit trigger.

/*
demo for dbse
assumes a user with create table, create sequence, create procedure pivs and quota.

*/

drop table dbse13142 purge;

create table dbse13142(
    the_id number not null
,   name   varchar2(20)
,   userid number)
;

drop sequence x_seq;
CREATE SEQUENCE  X_SEQ NOCACHE  NOORDER  NOCYCLE ;

create or replace PROCEDURE Insert_Record
                (p_name    IN  VARCHAR2,
                 p_userid  IN  INTEGER,
                 cur_out   OUT sys_refcursor)
    IS
        v_id NUMBER := 0;
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO v_id
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO dbse13142
            (the_id,
             name,
             userid)
          VALUES
            (v_id,
             p_name,
             p_userid);

        -- Return new id
        OPEN cur_out FOR
            SELECT v_id the_id
              FROM dual;
    END;
/

create or replace PROCEDURE Insert_Record2
                (p_name    IN  VARCHAR2,
                 p_userid  IN  INTEGER,
                 p_theid   OUT dbse13142.the_id%type)
    IS
    BEGIN
        -- Get id value from sequence
        SELECT x_seq.nextval
          INTO p_theid
          FROM dual;

        -- Line below is X_PKG line 40
        INSERT INTO dbse13142
            (the_id,
             name,
             userid)
          VALUES
            (p_theid,
             p_name,
             p_userid);
    END;
/

set timing on

declare
   c sys_refcursor;
begin
for i in 1..100000 loop
   insert_record('User '||i,i,c);
   close c;
end loop;
commit;
end;
/

select count(*) from dbse13142;
truncate table dbse13142;

declare
  x number;
begin
for i in 1..100000 loop
   insert_record2('User '||i,i,x);
end loop;
commit;
end;
/

select count(*) from dbse13142;
truncate table dbse13142;


Should be something like this:

DELETE A
FROM
    table_example1 AS A
    INNER JOIN table_example2 AS B
    ON A.COLUMN1 =B.COLUMN1
    AND A.COLUMN2 = B.COLUMN2
WHERE
    COLUMN_DATETIME > @Period;

Alternatively:

DELETE FROM A
FROM dbo.table_example1 AS A
WHERE EXISTS
(
    SELECT *
    FROM dbo.table_example2 AS B
    WHERE
        B.COLUMN1 = A.COLUMN1
        AND B.COLUMN2 = A.COLUMN2
        AND B.COLUMN_DATETIME > @Period
);


Oracle: spool data to different files depending on where condition (loop)

SQL> create table t1 (c1) as select mod(rownum, 4) from dual connect by level <= 12;

Just to have some sample data.

$ cat run.sql
set heading off feedback off echo off termout off pagesize 0
spool select.sql
select distinct 'spool c1_' || c1 || '.txt ' || chr(10)|| 'select c1 from t1 where c1 = ' || c1 || ';' || chr(10) || 'spool off' from t1 order by 1;
spool off
@select.sql
!rm select.sql
exit

$ sqlplus -s bp/bp @run.sql

And the result:

$ grep "" *txt
c1_0.txt:         0
c1_0.txt:         0
c1_0.txt:         0
c1_1.txt:         1
c1_1.txt:         1
c1_1.txt:         1
c1_2.txt:         2
c1_2.txt:         2
c1_2.txt:         2
c1_3.txt:         3
c1_3.txt:         3
c1_3.txt:         3


Accidentally imported data tables into Master database — Any cleanup needed beyond deleting?

If you have already deleted the Imported Tables then you are fine. You will not be required to do anything else.

This can happen and to get rid of this Use the Import/Export Wizard or Properly use the databasemame.Schema.Table.


Switching between queries with "date  now" slows down the performance a lot

You can't get 10 microseconds -- there is just too much overhead in performing a query.  You can get about 1 millisecond.  But you will need INDEX(datetime), at which point, the LIMIT 1 queries will probably run in about a millisecond.  The first query will take an amount of time proportional to the number of rows returned.  If it is a million rows, it may take seconds.

Or perhaps you have the "Query cache" turned on.  It should probably be turned off.  You are probably updating the table a lot; the QC gets flushed for every write.  So 10us will not be consistent, only if no writes happen since you last performed exactly the same query.

You "want the first two..." -- But the answer to the second one is included the first.  And if you add ORDER BY datetime, then it will be the first row.

MySQL "never" needs the index to be rebuilt.

Do not use MyISAM; switch to InnoDB.

One thing that may be happening.  With MyISAM there is only "table locking".  If a lot of writes are coming in, your SELECT could be held up waiting for them.  (Not a problem with InnoDB.)

How many rows in that one day plus one second?

Are those the actual queries?  Or are you limiting to a specific user.  (That would radically change everything I have said.)

If you want to discuss any more, please provide SHOW CREATE TABLE and tell us approximately how many rows in the table.


what will cause a sql server AG readonly replica to stop serving readonly clients?

But suppose the primary node goes down? Or a database is removed from the AG on the primary? Will the secondary replicas keep doing their thing in those scenarios?

I believe you are taking about unexpected shutdown or similar scenario where primary server not available in this case first WSFC will see if it is in position to update cluster registry to set the status like not synchronizing if the server shutdown is immediate Registry may not get updated with the state. When primary is down it would not send log blocks to secondary and if secondary is also done redoing and undoing all the log records it will also not do any work but wait for log records to be pulled when primary comes online. But let us say when the primary went down secondary was behind it, primary it will continue its process of replaying the log records to come alike primary.

Or a database is removed from the AG on the primary

Database will NOT be removed from AG unless user does that, so my above statement does not apply to this.


How to delete control file in oracle 11g

RMAN is a oracle utility to backup, restore & recovery of database.

The following Steps will be demonstrated the configuration of oracle RMAN backup (for first time configuration)

Lets assume the database is in NOARCHIVELOG mode, by default the database is in NOARCHIVELOG mode, we need to change it to ARCHIVELOG mode for RMAN backup configuration.
We can configure RMAN backup with catalog/repository database as well as control file. It is strongly recommended & very good practice to configure RMAN backup with catalog/repository database.

Note: Be sure you have installed oracle 11g successfully in your environment.
I had asked to you about O.S environment. I didn't get any clue from you. So as a example i am writing the all RMAN backup steps in Windows O.S environment.

First open the command prompt and try to connect sql server as per your environment credential. for example

Step # 1: Connect to Target database(Target DB: The database on which Backup & Recovery to be performed) as sysdba.

C:\> set Oracle_sid=orcl
c:\> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 26 10:17:22 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

Step # 2: Ensure the database has been configured with ARCHIVELOG mode or not?

SQL> select log_mode from v$database;

LOG_MODE

NOARCHIVELOG
Database is in NOARCHIVELOG mode.

Step # 3: If the database has been configured with ARCHIVELOG mode then skip the Step number 3 to 6, If not then Shutdown the database.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step # 4: Startup the database in mount state.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 163580896 bytes
Database Buffers 138412032 bytes
Redo Buffers 4775936 bytes
Database mounted.

Step # 5: Configure database in ARCHIVELOG mode.

SQL> alter database archivelog;
Database altered.

Step # 6: Alter database to open state.

SQL> alter database open;
Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE

Step # 7: Ensure ARCHIVELOG destination.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     330
Next log sequence to archive   332
Current log sequence           332

Step # 8: Ensure the flash/fast recovery area location.

SQL> show parameter db_recovery_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\app\xyz\flash_recove
                                                 ry_area
db_recovery_file_dest_size           big integer 3852M

Note : Here i did 'xyz' in C:\app\xxx\flash_recovery_area. due to security reason.

Step # 9: Connect to RMAN prompt with target database.

C:\>rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Jul 30 12:32:57 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1464491423)

Step # 10 : To show the all configuration of your RMAN through this command.

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backup/rman/full_%u_%s_%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\xyz\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORCL.ORA'; # default

Note : -  As per this statement  'CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backup/rman/full_%u_%s_%p';' I hope you have created the folder '/backup/rman' in your c: drive.

Typically we’ll use “BACKUP AS BACKUPSET” to backup a database. So, to take a full backup of the database without the archive logs, do the following.

RMAN> BACKUP AS BACKUPSET DATABASE

RMAN> backup as backupset database;

Starting backup at 30-JUL-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\APP\xyz\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00001 name=C:\APP\xyz\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00003 name=C:\APP\xyz\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00005 name=C:\APP\xyz\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00004 name=C:\APP\xyz\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 30-JUL-17
channel ORA_DISK_1: finished piece 1 at 30-JUL-17
piece handle=C:\BACKUP\RMAN\FULL_11SAL7BV_33_1 tag=TAG20170730T125212 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:16
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 30-JUL-17
channel ORA_DISK_1: finished piece 1 at 30-JUL-17
piece handle=C:\BACKUP\RMAN\FULL_12SAL7G7_34_1 tag=TAG20170730T125212 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 30-JUL-17

For further your ref Here and Here


Encounter deadlock even when there is no relation between tables

I could not understand the cause of the deadlock exactly because two
  DIFFERENT tables are involved and what I have read so far was when
  only one table is involved !!

Deadlocks occur when 2 or more sessions are waiting on each other. It doesn't matter if the deadlocked resources are rows in the same table, different tables, entire objects, etc.

I ran your scripts to reproduce the deadlock, extracted the xml_deadlock_report column from the system_health XE trace, and saved the deadlock report XML to a file with an .xdl extension. Below is the graphical view from SSMS.

As you can see, the SPID 58 victim was waiting on a Purchasing.PurchaseOrderDetail update keylock, blocked by the SPID 53 exclusive keylock on the same key. SPID 53 was waiting on an update keylock on Purchasing.PurchaseOrderHeader, blocked by the exclusive lock SPID 58 held on the same key.

Below is the raw xml_deadlock_report XML.

<deadlock>
  <victim-list>
    <victimProcess id="process1a668c9eca8"/>
  </victim-list>
  <process-list>
    <process id="process1a668c9eca8" taskpriority="0" logused="400" waitresource="KEY: 6:72057594048348160 (4ab5f0d47ad5)" waittime="1316" ownerId="2500187" transactionname="user_transaction" lasttranstarted="2018-07-24T06:02:13.303" XDES="0x1a85b00c490" lockMode="U" schedulerid="3" kpid="2932" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-24T06:02:31.437" lastbatchcompleted="2018-07-24T06:02:31.433" lastattention="2018-07-24T06:00:22.400" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQLSERVER" hostpid="15904" loginname="DOMAINNAME\USERNAME" isolationlevel="read committed (2)" xactid="2500187" currentdb="6" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack>
        <frame procname="adhoc" line="1" stmtstart="64" stmtend="278" sqlhandle="0x02000000d0c7f31a30fb1ad425c34357fe8ef6326793e7aa0000000000000000000000000000000000000000">  unknown    </frame>
        <frame procname="adhoc" line="1" stmtstart="26" stmtend="326" sqlhandle="0x02000000b66e4532b6686980baf466a9597258f2464dd4f50000000000000000000000000000000000000000">  unknown    </frame>
      </executionStack>
      <inputbuf>               Update Purchasing.PurchaseOrderDetail               Set OrderQty = 2               where ProductID = 448 and                     PurchaseOrderID = 1255   </inputbuf>
    </process>
    <process id="process1a66883d848" taskpriority="0" logused="9484" waitresource="KEY: 6:72057594048413696 (4bc08edebc6b)" waittime="16176" ownerId="2500193" transactionname="user_transaction" lasttranstarted="2018-07-24T06:02:16.577" XDES="0x1a85e004490" lockMode="U" schedulerid="5" kpid="20416" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-07-24T06:02:16.577" lastbatchcompleted="2018-07-24T06:02:16.570" lastattention="2018-07-24T06:01:28.820" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SQLSERVER" hostpid="15904" loginname="DOMAINNAME\USERNAME" isolationlevel="read committed (2)" xactid="2500193" currentdb="6" lockTimeout="4294967295" clientoption1="673319008" clientoption2="390200">
      <executionStack>
        <frame procname="AdventureWorks2014.Purchasing.uPurchaseOrderDetail" line="39" stmtstart="2732" stmtend="3830" sqlhandle="0x0300060004cc856a0ee00a016ba3000000000000000000000000000000000000000000000000000000000000">  UPDATE [Purchasing].[PurchaseOrderHeader]              SET [Purchasing].[PurchaseOrderHeader].[SubTotal] =                   (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])                      FROM [Purchasing].[PurchaseOrderDetail]                      WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]                           = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])              WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]                   IN (SELECT inserted.[PurchaseOrderID] FROM inserted    </frame>
        <frame procname="adhoc" line="3" stmtstart="64" stmtend="278" sqlhandle="0x02000000d0c7f31a30fb1ad425c34357fe8ef6326793e7aa0000000000000000000000000000000000000000">  unknown    </frame>
        <frame procname="adhoc" line="3" stmtstart="28" stmtend="250" sqlhandle="0x02000000cac3a70ebbbc20e8360adb397f4986f2ced020140000000000000000000000000000000000000000">  unknown    </frame>
      </executionStack>
      <inputbuf>  Begin Tran    update Purchasing.PurchaseOrderDetail  Set OrderQty = 4  Where ProductID = 448 and        PurchaseOrderID = 1255   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594048348160" dbid="6" objectname="AdventureWorks2014.Purchasing.PurchaseOrderDetail" indexname="PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID" id="lock1a7fea46a00" mode="X" associatedObjectId="72057594048348160">
      <owner-list>
        <owner id="process1a66883d848" mode="X"/>
      </owner-list>
      <waiter-list>
        <waiter id="process1a668c9eca8" mode="U" requestType="wait"/>
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594048413696" dbid="6" objectname="AdventureWorks2014.Purchasing.PurchaseOrderHeader" indexname="PK_PurchaseOrderHeader_PurchaseOrderID" id="lock1a815dda900" mode="X" associatedObjectId="72057594048413696">
      <owner-list>
        <owner id="process1a668c9eca8" mode="X"/>
      </owner-list>
      <waiter-list>
        <waiter id="process1a66883d848" mode="U" requestType="wait"/>
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

The sample AdventureWorks database includes an UPDATE trigger on Purchasing.PurchaseOrderDetail table that contributed to this deadlock. The trigger inserts into Production.TransactionHistory and also updates both Purchasing.PurchaseOrderHeader and Purchasing.PurchaseOrderDetail whenever ProductID, OrderQty, or UnitPrice columns are updated.

In summary, the sequence leading to this deadlock was:

1) connection 1 updated a Purchasing.PurchaseOrderHeader but did not commit

2) connection 2 updated the Purchasing.PurchaseDetail table and the fired trigger was blocked when trying to update Purchasing.PurchaseOrderHeader

3) connection 1 tried to update Purchasing.PurchaseDetail but was blocked by connection

4) SQL Server chose connection 1 as the deadlock victim, allowing connection 2 to autocommit


GUID as reason for MySQL import slowness

The old cliche SIZE MATTERS applies here without a doubt.

Smaller keys would load into a table faster that with biggers (This becomes even more evident with InnoDB). The bigger the columns in the index, the fewer the keys would be residing in BTREE pages. Thus, you get taller BTREE trees. Even a BTREE height of 3 or more, which is bad for a small table, would be the result of having large columns. The same would apply to the name (up to 150 characters) and category (up to 100 characters) fields. Making all columns CHAR instead of VARCHAR would increase SELECT performance, but the significant increase of diskspace + BTREE node management would throw SELECT performance under the bus as the table grows.

Looking at all of your keys I can see this table being extermely lopsided in index size verses table size.

Please run this query

SELECT data_lenth,index_length
FROM information_schema.tables
WHERE table_name = 'acl_action';

You will see the combined sum of all index pages being as large or larger than the table. From a physical standpoint, the file acl_actions.MYI will be bigger that acl_actions.MYD.

Here is where random I/O would come into play: Notice your index idx_aclaction_id_del

Notice that you have it defined as

KEY `idx_aclaction_id_del` (`id`,`deleted`),

If you ever decide to query for deleted records, you will get an full index scan because of the order of trhe columns in this index.

KEY `idx_aclaction_del_id` (`deleted`,`id`),

That way all deleted keys are grouped together. Likewise, all non-deleted rows are grouped together.

Try altering the table design like this

CREATE TABLE `acl_actions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `guid` char(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `name` varchar(150) default NULL,
  ........
  `category` varchar(100) default NULL,
  `deleted` tinyint(1) default '0',
  PRIMARY KEY  (`id`),
  UNQIUE KEY  (`guid`),
  KEY `idx_aclaction_id_del` (`deleted`,`id`),
  KEY `idx_category_name` (`category`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You should run the following query

SELECT name,categrory FROM acl_actions PROCEDURE ANALYSE();

This will recommend the correct sizes for those columns with the given dataset.


MySQL: IF statement check is a table exists

You probably want to use this syntax:

CREATE TABLE IF NOT EXISTS `table1` ( -- create table );
-- returns only a warning if the table already exists

Unless you want to check that the table name does not exist instance-wide, in which the way you "program with SQL" should be a bit different, for several reasons:

SET @check_table_name := false;

SELECT true INTO @check_table_name
FROM information_schema.TABLES
WHERE TABLE_NAME = 'table1'
LIMIT 1;


I don't see any way as for future inserts you will need to have the index. Maybe consider adding another column to your table ensuring the uniqueness. This could be filled with a trigger function on new inserts.


Is there way to get transaction commit timestamp in Postgres?

This problem could be easily solved by assigning in step 2 timestamp
when update becomes visible for other transactions (transaction commit
timestamp in other words) instead of CURRENT_TIMESTAMP or
clock_timestamp().

This is logically impossible. Postgres writes new row versions before it finally commits to make them visible. It would require prophetic capabilities to write a future timestamp yet unknown at the time of writing.
However, you can get commit timestamps from a different source: since Postgres 9.5, there is a GUC setting track_commit_timestamp to start logging commit timestamps globally.
Then you can get commit timestamps with the utility function pg_xact_commit_timestamp(xid). Your query could look like:
SELECT * FROM my_table t
WHERE   pg_xact_commit_timestamp(t.xmin) > _some_persisted_timestamp;

Be aware that commit timestamps are not kept around forever. After
two billion transactions transactions (2^31), transaction IDs are "frozen". That does not delete it right away, but after 4 billion transactions, the information is gone for certain. That's a big number of transactions, and only very busy databases burn that much over a lifetime. But there can be programming errors burning through transaction numbers more quickly than expected ...
Your step 2 and step 3 trade positions, and you record the commit timestamp instead of CURRENT_TIMESTAMP - or xmin from any freshly updated row to derive the commit timestamp with pg_xact_commit_timestamp() once more.
More:

How do I write a Postgres SQL command based on metadata of the tables themselves?
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#Commit_timestamp_tracking

About xmin:

How to view tuples changed in a PostgreSQL transaction?

But I am not completely sure I understand your task. Maybe you need a queuing tool or process rows one by one like discussed here:

Postgres UPDATE ... LIMIT 1


performance impact when renaming a column in 100M rows table

The size of the table does not matter. Changing the column name just changes metadata in the table. However, there's no concept of a column metadata lock in SQL Server. Renaming a column requires a schema modification lock on the table. Here's what the lock request can look like:

<Database name="TEST">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="rename_col_test" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

Under the default isolation level, after you issue the rename it will be blocked by any queries that were already running with any kind of lock on that object. Any queries kicked off after your rename request that require a lock on the table will be blocked by the rename.

Here's an example of how a blocking chain can form:

Session 52 is a long running SELECT that holds a lock on the table. Session 53 tries to rename the table. Session 54 is another SELECT query that is compatible with the locks taken by session 52, but it is blocked by the rename.