Difference between SQL Server Azure and SQL Server 200x

Using logins are discouraged. Logins that will be used by your applications should be contained users and not server logins. Create contained users for your applications and users, place one or more database users into a database role and then assign permissions to the database role.

One common reason to create a database user based on a SQL Database server login is for users that need access to multiple databases. Since contained database users are individual entities, each database maintains its own user and its own password. This can cause overhead as the user must then remember each password for each database, and it can become untenable when having to change multiple passwords for many databases. However, when using SQL Server Logins and high availability (active geo-replication and failover groups), the SQL Server logins must be set manually at each server. Otherwise, the database user will no longer be mapped to the server login after a failover occurs, and will not be able to access the database post failover.

As the Sysadmin fixed role does not exist in SQL Azure, if you want to create a "sysadmin" on SQL Azure, in addition to the sysadmin created on the portal, you  need to add that sysadmin login to the dbmanager database role in the master database.

CREATE LOGIN YourSysadminLogin WITH PASSWORD = 'hSUj4Grd' -- Dec 13, 2018

CREATE USER YourSysadminLogin FROM LOGIN YourSysadminLogin;

ALTER ROLE dbmanager ADD MEMBER YourSysadminLogin;

YourSysadminLogin, as it belongs to dbmanager, it now can perform privileged tasks like been able to create and drop databases.

If you want YourSysadminLogin to manage logins also (create/alter/drop logins) add it to the loginmanager database role in the master database.

ALTER ROLE loginmanager ADD MEMBER YourSysadminLogin;

Create your YourSysadminLogin as a user on user databases and assign it db_owner.

CREATE USER YourSysadminLogin FROM LOGIN YourSysadminLogin;
GO
ALTER ROLE db_owner ADD MEMBER YourSysadminLogin;
GO

Now you have created a sysadmin.


The script is blocked because it cannot obtain exclusive access to the database (technically it is not executing, it is waiting). I think it's in Microsoft's best interests to not allow you to drop a database that is currently being used, so I wouldn't really blame them for making this hard. What you need to do is not only ensure you run your drop from master but also that none of your other query windows, Object Explorer, etc. still have a lingering connection to the database. The easiest way to do that is to set the database to SINGLE_USER:

USE [master];
GO
ALTER DATABASE [emcb_schema_v1_0_7] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [emcb_schema_v1_0_7];


Count movies with specific score, per actor

You can group by actor_id to get the desired results. You might want to join groups in as well if you want to filter by a column in there. (sqlfiddle)

SELECT
    actor_id,
    COUNT(CASE WHEN score >= 0 and score <= 1 THEN 1 END) count0,
    COUNT(CASE WHEN score > 1 and score  <= 2 THEN 1 END) count1,
    COUNT(CASE WHEN score > 2 and score  <= 3 THEN 1 END) count2,
    COUNT(CASE WHEN score > 3 and score  <= 4 THEN 1 END) count3,
    COUNT(CASE WHEN score > 4 and score  <= 5 THEN 1 END) count4,
    COUNT(CASE WHEN score > 5 and score  <= 6 THEN 1 END) count5,
    COUNT(CASE WHEN score > 6 and score  <= 7 THEN 1 END) count6,
    COUNT(CASE WHEN score > 7 and score  <= 8 THEN 1 END) count7,
    COUNT(CASE WHEN score > 8 and score  <= 9 THEN 1 END) count8,
    COUNT(CASE WHEN score > 9 and score  <= 10 THEN 1 END) count9
FROM movies
 INNER JOIN movies_actors ON movies_actors.movie_id = movies.id
 INNER JOIN actors ON movies_actors.actor_id = actors.id
WHERE actors.group_id = 1
GROUP BY actor_id


It is not advisable to use RAM for temporary directory. At time files can grow large enough to outflow RAM and this would be true even with plain MySQL. RAM is best managed by InnoDB buffer pool. You can setup a tmpfs that should be good enough with decent performance.


Is there a magic ratio where you can safely say it's not worth it to maintain such an index ?

There is not a magic number that exist. You might have an index that is used rarely (compare to number of updates) but supporting that query is important for your business/users.

It is a call that only you can make based on your business/user requirement.

If you want to test, do not drop the index. You can disable the index that way your metadata about the index is preserved. Here is a good post about that.

Disabling vs. Dropping Indexes by Jes Borland.


SQL Server transactional replication process and error

Possible cause might be :

This error has been observed on a server that had been renamed after the original installation of SQL Server, and where the SQL Server configuration function ‘@@SERVERNAME’ still returned the original name of the server. This can be confirmed by:

select @@SERVERNAME
 go

This should return the name of the server. If it does not then follow the procedure below to correct it.

Please check these links below :

https://stackoverflow.com/questions/8212032/sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-t

http://www.cryer.co.uk/brian/sqlserver/replication_requires_actual_server_name.htm

http://blog.sqlauthority.com/2015/09/18/sql-server-fix-replication-error-sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-the-server/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a4d1d008-05f5-46f2-a145-347cbf4d9e5c/sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-the-server-specify?forum=sqlreplication


CURRENT_USER refers to the user role your current login has on the specific database.
For instance i have a user called PlebUser, who has db_owner rights on a specific database A, has no rights (not even viewing) on database B and has generic guest rights (server role public) for my master db (C).
Running Select CURRENT_USER for each of these databases returns me:

A: PlebUser (defined role on my database)
B: Nothing, can't even run it because i'm denied on this DB
C: Guest

You need to check whether you are logged in as your defined user (and not as DBO/SA, whatever admin account you are using to create the new users), and that they are properly mapped to specific databases.
You can do this by checking the server wide security logins, properties on the specific login and check user mapping.


Suppress bogus warning for query using triggers

Just switch binary logging from statement based to mixed.

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

Source:
What is the safest way to switch the binlog format at runtime?


Japanese characters in VARCHAR column using Japanese_Unicode_CI_AS

As Tibor mentioned, the Japanese_Unicode_CI_AS collation (all Japanese_* collations, actually) can store Japanese characters in VARCHAR columns due to Windows code page 932 being a Double-Byte character set (DBCS). I believe there are just under 7800 Japanese characters mapped to the Windows-932 code page. However, Unicode contains more than 7800 Japanese characters.
It would be very helpful (maybe even necessary) to know some things in order to narrow down the problem:

Exactly how you are importing the CSV file. Are you using BCP or BULK INSERT / OPENROWSET(BULK...)?
An example of at least one Japanese name that is getting converted to ??
You say both that the "Japanese names from CSV file that are changing into question marks" and "it is changing some of the characters to ??", so:

Are all Japanese characters changing into question marks, or only some?
Are the Japanese characters changing into one question mark ( ? ), two ( ?? ), or some of each?

Without knowing the answers to those questions, I can say that there are two main possibilities:

(all Japanese characters imported as ?):   You aren't telling the import tool what the encoding of the CSV file is. Is it encoded as Windows-932 (or possibly Windows-31J)? Or is it a Unicode encoding such as UTF-8 or UTF-16 (which might be listed as "UCS-2" or "Unicode" depending on the tool)? If you are using BCP, you would need to use the command-line options of -c -C 932 for Windows-932, or -c -C 65001 for UTF-8. This problem should be fixable simply by setting the tool to use the correct code page.

(some Japanese characters imported as ? or ??):   If you are telling the import tool the correct encoding of the file, there are still Japanese characters that are not encoded in the Windows-932 code page. For example:
-- DROP TABLE ##BCP;
CREATE TABLE ##BCP ([Value] VARCHAR(50) COLLATE Japanese_Unicode_CI_AS);
INSERT INTO ##BCP ([Value]) VALUES (N'ヤ :: ㋾ :: ');
SELECT * FROM ##BCP;

returns:
ヤ :: ? :: ??

This problem can only be fixed by doing one of the following:

Change the datatype of the column to be NVARCHAR (though you said that you can't change the schema)
Change the collation of the column to use a UTF-8 collation (i.e. collation names ending in _UTF8; introduced in SQL Server 2019)

For info on working with collations / Unicode / encodings, please visit: Collations Info


How to import blanks as nulls instead of zeros while importing txt using wizard

Unfortunately that's not possible if you insist on using the wizard, you would need to edit the package using Visual Studio.

There is an option 'keep null's' when editing the package in Visual Studio

Given this .csv file:

StringField,IntField
a,1
b,5
c,
d,6
e,
f,8
g,
h,
i,10
j,

and this table definition

CREATE TABLE [dbo].[NullTest](
    [StringField] [varchar](50) NULL,
    [IntField] [int] NULL
) ON [PRIMARY]

GO

When you import the data using the wizard the nulls are converted to 0's

However, if at the end of the wizard you select "save package as" instead of "run immediately" as in this screen

The file can be saved somewhere on the file system.

If you then create a new Integration Services project, you can add an existing package like this

If you browse to the file you just saved and view it you will see one data flow task (you will see an execute SQL task too if you selected to create the table instead of appending to an existing one)

Double click the data flow to edit it, and double click the data source to check the "retain nulls" option

Then double click the destination to view the properties, then change the "Keep nulls" property to true.

If you then click the green arrow in the toolbar or select debug > start debugging from the menu to execute your package the end result is this

In the end the wizard is only a wizard. It generates an SSIS package for you and chooses some defaults, unfortunately not the defaults you want.
If you want to keep nulls there isn't really any other way than editing the package with Visual Studio.

Then again, it might be easier to just create the package yourself in Visual Studio or use @MaxVernon's T-SQL solution.


Which characters are allowed in MySQL/MariaDB user account names?

User names are stored on the mysql.user table. On my system (mariadb 10.3), that table is created as a CHAR(80) with the utf8 character set. You can check yours by running:

MariaDB [(none)]> SHOW CREATE TABLE mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',

That means, for my version, usernames can be between 0 and 80 characters that are valid utf8 strings but not outside the basic multilingual plane (only 3-byte characters).

So control and wildcard characters are valid, but for example, not emojis:

MariaDB [(none)]> CREATE USER 'a*b';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> CREATE USER 'a
    '> b';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> CREATE USER '';
ERROR 1470 (HY000): String '????' is too long for user name (should be no longer than 80)

MariaDB [(none)]> CREATE USER 'Robert\'); DROP TABLE Students;-- ';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT user FROM mysql.user;
+----------------------------------+
| user                             |
+----------------------------------+
| Robert'); DROP TABLE Students;-- |
| a
b                              |
| a*b                              |
| root                             |
| root                             |
| root                             |
| root                             |
+----------------------------------+
7 rows in set (0.000 sec)

Check the type in your system, I guess it could change depending on the version/vendor or be different in the future/past.


DB2 LOAD data into table - Interpreting the error messages

According to IBM SQL3227W states:

  "Record token token1 refers to user record number token2.
  Explanation

  An error or warning was encountered during LOAD, IMPORT or EXPORT of a
  table. CPU parallelism was greater than 1 at the time the problem was
  encountered, and an SQL message was written which identified the user
  record with a special unique token. This message serves to map the
  unique record token to the record number of the source user data.
  User response

  Refer to the original SQL message returned for appropriate action."

So this refers you back to the previous error. And the next one SQL3185W says:

  "The previous error occurred while processing data from row row-number
  of the input file.
  Explanation

  This message provides the identification of the row where the error
  occurred for the previous message listed in the message file (for
  example, SQL3306).
  User response

  No action is required."

So...Phil nailed it on the head, we really need to see what comes before these messages. Do you have a log of the output that you could post to your question? We could better help you with interpreting results then.

That being said, I would highly encourage you to poke around Information Center like I did as it can best help you find what those pesky "SQL" messages mean. The links I found were for 9.5. Just switch to the version of DB2 that you are using to make sure you have the most accurate information. I use Information Center for just about everything.

  EDIT: Your updated helps a lot. Here is the error:

SQL3116W  The field value in row "F12-33856" and column "9" is missing, but
the target column is not nullable.
SQL3185W  The previous error occurred while processing data from row
"F12-33856" of the input file.
SQL3227W  Record token "F12-33856" refers to user record number "440439".**

  In short, it looks like a null was written to "column 9" of our table
  and that you have a not null constraint on that column. Now the trick
  is locating which record had the issue. It is marked with two tokens
  it looks like. (F12-33856 and 440439). I guess I'd start with 440439
  first. That seems to me like it could be the primary key. Just a
  guess. But at least you know your issue is a null value for a non-null
  field.


Slow MySql Server Performance - What and How to check?

Log slow queries- If your system has a ton of queries, it gets tougher to find out which queries are slowing your system. MySQL provides a tool to log slow queries for further analysis
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
EXPLAIN Extended command shows details about your queries when your have no idea what is happening http://dev.mysql.com/doc/refman/5.0/en/explain-extended.html
To speed up your queries use Index - A good practice is add index by seeing which fields are in the WHERE clause add index for them.
Also if you are retrieving all fields from a table, the query gets slower by fetching all data from disk. In SELECT query you should specify which fields you need to bring instead to bring them all with *
Make use of Query Cache http://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html
Make sure that your MySQL server configuration file options are optimized according to your hardware
http://dev.mysql.com/doc/refman/5.5/en/option-files.html
Make sure that you are using optimized data types while creating a table structure
For example "Comments" fields has size of 256 characters, reply it to MYSQL with a field with type VARCHAR(256) instead of using TEXT. The query will be much faster.

Procedure_Analyse() can help you in finding optimal data types:

http://www.mysqlperformanceblog.com/2009/03/23/procedure-analyse/

http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

There are open source tools from Percona - http://www.percona.com/software/percona-toolkit which can be helpful for MySQL DBA's and specially those who are new to MySQL these tools will provide good assistance to them.


Basics of creating a table and refreshing it via a mysql event

Wrap both statements in BEGIN and END, like:

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END

Taken from a quick perusal of the MySQL Create Event page.

So, your code might become:

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'blah'
    DO
      BEGIN
          DROP TABLE IF EXISTS compdist1.comp_CL1;
          CREATE TABLE compdist1.comp_CL1
          AS
          SELECT *
          FROM compdist1.prices_CL_analysis;
      END

Also redefine the MySQL delimiter temporarily before the CREATE EVENT … to a different symbol and use the declared symbol at the end of the definition so that MySQL can pass the entire CREATE EVENT statement to the server. Redefine it back to ; after the definition, like this:

DELIMITER $$

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'blah'
    DO
      BEGIN
          …
      END$$

DELIMITER ;

This is needed when passing compound statements. Since the event body contains more than one statement, you event definition now qualifies as a compound statement. More information at the MySQL Defining Stored Programs page.


How to list all constraints of a table in PostgreSQL?

Constraints can be retrieved via pg_catalog.pg_constraint.

SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = '<schema name>'
             AND rel.relname = '<table name>';

Replace <schema name> with the name of your schema and <table name> with the name of your table.


SQL has a single category NULL and boolean algebra is extended to the ternary case with all the boolean operators AND, OR, NOT and XOR are modified with respect to the third logical state "Unknown" or "Unavailable". All the other subcategories can be implemented as reference table of arbitrary numbers of states. That is for relational databases.


Without concurrent write access
Materialize a selection in a CTE (Common Table Expressions) and join to it in the FROM clause of the UPDATE.
WITH cte AS (
   SELECT server_ip          -- pk column or any (set of) unique column(s)
   FROM   server_info
   WHERE  status = 'standby'
   LIMIT  1                  -- arbitrary pick (cheapest)
   )
UPDATE server_info s
SET    status = 'active'
FROM   cte
WHERE  s.server_ip = cte.server_ip
RETURNING s.server_ip;

I originally had a plain subquery here, but that can sidestep the LIMIT for certain query plans as Feike pointed out:

The planner may choose to generate a plan that executes a nested loop over the LIMITing subquery, causing more UPDATEs than LIMIT, e.g.:

 Update on buganalysis [...] rows=5

   ->  Nested Loop
         ->  Seq Scan on buganalysis
         ->  Subquery Scan on sub [...] loops=11
               ->  Limit [...] rows=2
                     ->  LockRows
                           ->  Sort
                                 ->  Seq Scan on buganalysis

Reproducing test case

The way to fix the above was to wrap the LIMIT subquery in its own CTE, as the CTE is materialized it will not return different results on different iterations of the nested loop.

Or use a lowly correlated subquery for the simple case with LIMIT 1. Simpler, faster:
UPDATE server_info
SET    status = 'active'
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         )
RETURNING server_ip;

With concurrent write access
Assuming default isolation level READ COMMITTED for all of this. Stricter isolation levels (REPEATABLE READ and SERIALIZABLE) may still result in serialization errors. See:

SELECT … FOR UPDATE SKIP LOCKED in REPEATABLE READ transactions

Under concurrent write load, add FOR UPDATE SKIP LOCKED to lock the row to avoid race conditions. SKIP LOCKED was added in Postgres 9.5, for older versions see below. The manual:

With SKIP LOCKED, any selected rows that cannot be immediately locked
are skipped. Skipping locked rows provides an inconsistent view of the
data, so this is not suitable for general purpose work, but can be
used to avoid lock contention with multiple consumers accessing a
queue-like table.

UPDATE server_info
SET    status = 'active'
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR    UPDATE SKIP LOCKED
         )
RETURNING server_ip;
If there is no qualifying, unlocked row left, nothing happens in this query (no row is updated) and you get an empty result. For uncritical operations that means you are done.
However, concurrent transactions may have locked rows, but then don't finish the  update (ROLLBACK or other reasons). To be sure run a final check:
SELECT NOT EXISTS (
   SELECT FROM server_info
   WHERE  status = 'standby'
   );

SELECT also sees locked rows. Wile that doesn't return true, one or more rows are still unfinished and transactions could still be rolled back. (Or new rows have been added meanwhile.) Wait a bit, then loop the two steps: (UPDATE till you get no row back; SELECT ...) until you get true.
Related:

Atomic UPDATE .. SELECT in Postgres

Without SKIP LOCKED in PostgreSQL 9.4 or older
UPDATE server_info
SET    status = 'active'
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;
Concurrent transactions trying to lock the same row are blocked until the first one releases its lock.
If the first was rolled back, the next transaction takes the lock and proceeds normally; others in the queue keep waiting.
If the first committed, the WHERE condition is re-evaluated and if it's not TRUE any more (status has changed) the CTE (somewhat surprisingly) returns no row. Nothing happens. That's the desired behavior when all transactions want to update the same row.
But not when each transaction wants to update the next row. And since we just want to update an arbitrary (or random) row, there is no point in waiting at all.
We can unblock the situation with the help of advisory locks:
UPDATE server_info
SET    status = 'active'
WHERE  server_ip = (
         SELECT server_ip
         FROM   server_info
         WHERE  status = 'standby'
         AND    pg_try_advisory_xact_lock(id)
         LIMIT  1
         FOR    UPDATE
         )
RETURNING server_ip;
This way, the next row not locked yet will be updated. Each transaction gets a fresh row to work with. I had help from Czech Postgres Wiki for this trick.
id being any unique bigint column (or any type with an implicit cast like int4 or int2).
If advisory locks are in use for multiple tables in your database concurrently, disambiguate with pg_try_advisory_xact_lock(tableoid::int, id) - id being a unique integer here.
Since tableoid is a bigint quantity, it can theoretically overflow integer. If you are paranoid enough, use (tableoid::bigint % 2147483648)::int instead - leaving a theoretical "hash collision" for the truly paranoid ...
Also, Postgres is free to test WHERE conditions in any order. It could test  pg_try_advisory_xact_lock() and acquire a lock before status = 'standby', which could result in additional advisory locks on unrelated rows, where status = 'standby' is not true. Related question on SO:

Postgres pg_try_advisory_lock blocks all records

Typically, you can just ignore this. To guarantee that only qualifying rows are locked, you could nest the predicate(s) in a CTE like above or a subquery with the OFFSET 0 hack (prevents inlining). Example:

Put pg_try_advisory_xact_lock() in a nested subquery?

Or (cheaper for sequential scans) nest the conditions in a CASE statement like:
WHERE  CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

However the CASE trick would also keep Postgres from using an index on status. If such an index is available, you don't need extra nesting to begin with: only qualifying rows will be locked in an index scan.
Since you cannot be sure that an index is used in every call, you could just:
WHERE  status = 'standby'
AND    CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

The CASE is logically redundant, but it servers the discussed purpose.
If the command is part of a long transaction, consider session-level locks that can be (and have to be) released manually. So you can unlock as soon as you are done with the locked row: pg_try_advisory_lock() and pg_advisory_unlock(). The manual:

Once acquired at session level, an advisory lock is held until
explicitly released or the session ends.

Related:

Optimizing concurrent updates in Postgres


You need to add that new IP in your listener.ora file and modify tnsnames.ora file to load balance the connection requests to two different IPs.

Let's say I have database server and one listener is serving at 192.168.30.133/Port 1522. Next, I want to add new interface with IP 192.168.30.144.

Then I need to add the second IP address to my listener.ora file as shown below.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.133)(PORT = 1522))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.144)(PORT = 1524))
    )
  )

In order to balance the load of client connections between these two IP address you need to modify tnsnames.ora file at client site as shown below.

 ORAPDB1 =
  (DESCRIPTION =
   (ADDRESS_LIST =
    (LOAD_BALANCE = on)
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.133)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.144)(PORT = 1524))
   )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orapdb1)
    )
  )

Let's try to connect to the database and see whether the connection requests is distributed to these IPs or not.

[oracle@ora12c trace]$ lsnrctl status listener1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 22-FEB-2017 10:46:07

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.133)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                22-FEB-2017 10:44:36
Uptime                    0 days 0 hr. 1 min. 31 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.133)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.144)(PORT=1524)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c.dba.com)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/db_1/admin/oracdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora12c.dba.com)(PORT=5501))(Security=(my_wallet_directory=/u01/app/oracle/admin/oracdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "oracdb" has 1 instance(s).
  Instance "oracdb", status READY, has 1 handler(s) for this service...
Service "oracdbXDB" has 1 instance(s).
  Instance "oracdb", status READY, has 1 handler(s) for this service...
Service "orapdb1" has 1 instance(s).
  Instance "oracdb", status READY, has 1 handler(s) for this service...
The command completed successfully

Lets try to connect to the database.

[oracle@ora12c trace]$ sqlplus jay/jay@orapdb1

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 22 10:47:14 2017

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

Last Successful login time: Wed Feb 22 2017 10:25:31 +05:45

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>

Now lets check which IP address was chosen to server the request.

Go to $ORACLE_BASE/diag/tnslsnr/DB_NAME/listener1/trace.

[oracle@ora12c]$ tail -f listener.log
22-FEB-2017 10:47:14 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orapdb1)(CID=(PROGRAM=sqlplus)(HOST=ora12c.dba.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.144)(PORT=21140)) * establish * orapdb1 * 0

We can see that 192.168.30.144 was used to serve the request.

Let's log out and try again to connect to the database.

[oracle@ora12c trace]$ sqlplus jay/jay@orapdb1

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 22 10:47:57 2017

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

Last Successful login time: Wed Feb 22 2017 10:47:14 +05:45

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Listener.log

[oracle@ora12c]$ tail -f listener.log
22-FEB-2017 10:47:57 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orapdb1)(CID=(PROGRAM=sqlplus)(HOST=ora12c.dba.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.133)(PORT=37185)) * establish * orapdb1 * 0

Now we can see 192.168.30.133 is used.

Note: As @Colin 'T Hart said. You can use same port number for both IPs.


MYSQL cluster issue: shutdown data node correctly breaks replication

In MySQL Cluster the MySQL server that handles the replication slave
will write into the NDB data nodes. Since the NDB Data nodes are
down it isn't possible to write into them and you get the above
mentioned errors. Replication will start up again when the data
nodes are up again and the slave SQL thread is restarted.


Nice question. My objection to your design is: you choose to consider user input and predefined input as the same thing (the only difference being the value of show). But they are not, in my opinion. Because predefined input are different options, but different user inputs could indicate the same idea written with synonyms, wrong spellings, etc, or could be just jokes or junk characters.

You say that this saves you from having empty strings or NULLs. But I believe that the absence of a value (custom text) is a valid use for an empty string.


As Ypercube said in his comment you seem to have an uncorrelated subquery.  This means that for each way you are trying to build a line containing 1,000,000 odd nodes.

Also it it would be best to put a Geography constructor around the result of the subquery.

The tutorial's update statement that you referenced has got a correlated subquery, because the WHERE clause is wn.wayid = ways.wayid
Changing your update query to this should fix the issue.

UPDATE ways
SET geog4326 = Geography::STGeomFromText('LINESTRING(' + STUFF((
    SELECT ',' +
        CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' +
        CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
    FROM ways w
        JOIN waynodes wn ON w.wayid = wn.wayid
        JOIN nodes n ON wn.nodeid = n.nodeid
    WHERE wn.wayid = ways.wayid -- Changed to ways to correlate
    ORDER BY w.wayid, orderid
    FOR XML PATH(''), TYPE
    ).value('/', 'NVARCHAR(MAX)'),1,1,'') +')',4326)
WHERE EXISTS (SELECT 1 FROM waynodes wg WHERE ways.wayid = wg.wayid GROUP BY wg.wayid HAVING COUNT(*) > 1)

Personally I prefer to construct my update statements similar to the following.

UPDATE z
SET geog4326 = Geography::STGeomFromText('LINESTRING(' + STUFF(CAST(x.linestring AS VARCHAR(MAX)),1,1,'') +')',4326)
FROM ways z
    CROSS APPLY (
        SELECT ',' +
            CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' +
            CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
        FROM ways w
            JOIN waynodes wn ON w.wayid = wn.wayid
            JOIN nodes n ON wn.nodeid = n.nodeid
        WHERE z.wayid = w.wayid
        ORDER BY w.wayid, orderid
        FOR XML PATH('')
    ) x (linestring)
WHERE EXISTS (SELECT 1 FROM waynodes wg WHERE z.wayid = wg.wayid GROUP BY wg.wayid HAVING COUNT(*) > 1)

I've tested a SELECT version of my update on some made up data.
SQLFiddle

 WITH ways AS (
 SELECT *
 FROM (VALUES
    (1),(2),(3)
    ) W(wayid)
 ),
 waynodes AS (
 SELECT *
 FROM (VALUES
    (1,1,1),
    (1,2,2),
    (1,3,3),
    (2,1,4),
    (2,2,5),
    (2,3,6),
    (3,1,7),
    (3,2,8)
    ) WN(wayid, orderid, nodeid)
 ),
 nodes AS (
 SELECT *
 FROM (VALUES
    (1, 0, 0),
    (2, .1, 0),
    (3, .1, .1),
    (4, .1, .1),
    (5, .2, 0),
    (6, .3, 0),
    (7, 0, 0),
    (8, 0, .2)
    ) N(nodeid, longitude, latitude)
 )
SELECT Geography::STGeomFromText('LINESTRING(' + STUFF(x.linestring,1,1,'') +')',4326)
FROM ways z
    CROSS APPLY (
        SELECT ',' + CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' + CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
        FROM ways w
            JOIN waynodes wn ON w.wayid = wn.wayid
            JOIN nodes n ON wn.nodeid = n.nodeid
        WHERE z.wayid = w.wayid
        ORDER BY w.wayid, orderid
        FOR XML PATH('')
        ) x(linestring)


Without a lot more info, etc. couldn't you just create an EngageTrips table, and create an CreatedTrips table and hook a foreign key into those and then join the data once the function is run to bring that data for view to the end-user?

Also meaning control how the data is entered into each table at the data entry level based on criteria when the data is imported, etc.

Just a quick thought.


It seems that your server are down, you should start your mysql server first.


Manage billions lines of sensors data in mariaDB

Since you are likely to have a billion rows in this one table, I will focus on it:
CREATE TABLE `mesure` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Machine` varchar(100) NOT NULL,
  `Date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `Valeur` decimal(18,5) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `machine_timestamp` (`Machine`,`Date`) USING BTREE,
  KEY `Date` (`Date`),
  CONSTRAINT `FK_valeur_machine` FOREIGN KEY (`machine`) REFERENCES `machine` (`Name`)
) ENGINE=InnoDB;

Each row is taking about 70 bytes (including overhead); multiply that by billions of rows over 5 years, and... Get the picture?  We need to shrink this table.
decimal(18,5) -- That seems much too big.  It takes 9 bytes.  Consider using FLOAT (no, not FLOAT(18,5)).  That takes 4 bytes and has about 7 significant digits.  That is more than adequate for any sensor I know of.
Machine varchar(100) -- Shrink that down to an "id" that joins to the machine table.  How many machines might you eventually have?  Probably between 256 and 64K?  Use a 2-byte SMALLINT UNSIGNED both here and in the machine table.
To allow for the unlikely duplicate, do INSERT IGNORE instead of simply INSERT.
Can you get 2 readings for the same machine in the same second?  Probably not.  Or even if you did, you can (should?) throw away the redundant reading?  So, I recommend  PRIMARY KEY(machine_id, date) (and eliminate the existing UNIQUE with the same columns).
The change to the PK eliminates the 4 byte ID that you currently have.  INT is a disaster that will occur in less than a year -- when it hits the limit of about 2 billion.
Revised schema
CREATE TABLE `measure` (
  `MachineId` SMALLINT UNSIGNED NOT NULL,
  `Date` datetime NOT NULL,
  `Valeur` FLOAT NOT NULL,
  PRIMARY KEY(`Machine`,`Date`),
  KEY `Date` (`Date`),
  CONSTRAINT `FK_valeur_machine`
        FOREIGN KEY (`machineId`) REFERENCES `machine` (`ID`)
) ENGINE=InnoDB;

Those changes might cut the table's disk footprint in half.  You say the queries are fast enough now, but they will get slower over time.  This schema will help keep them "fast enough".
Summary table(s)
You will probably want "reports" or graphs or other queries that do a big SELECT ... WHERE date between... GROUP BY machineId.  And you will find that this is slower than you like.  See http://mysql.rjweb.org/doc.php/summarytables ; we can discuss that in another Question.
Done properly, you can consider not saving the raw data for 5 years, while keeping the summarized data "forever".  The summary should be one-tenth the size and 10 times as fast to query.  (YMMV - Your Mileage May Vary.)
Deleting old data
If you keep the data for only 5 years, how will you delete the "old" data?  You will find that a big DELETE is grossly inefficient.  Plan ahead by PARTITION BY RANGE(TO_DAYS(date)) now.  Have monthly partitions.  More:  http://mysql.rjweb.org/doc.php/partitionmaint .  DROP PARTITION is much faster.


Upgrading to SSMS with SQL Web Edition turned off Code Outlining : How do I get it back?

Apply the latest service packs was the answer. Applied SP1 and cumulative updates from MS. Should have known, Windows, the service pack wonder.


You should tell them the way you generated the dump, since it is somewhat unusual. One obvious problem may be that they tried a decompression program that does not understand GNU zip.
I recommend to use the custom format:
pg_dump -F c -U postgres -f outfile.dmp $x

That can be restored with pg_restore, and it is automatically compressed.


Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query

Don't know if you're still looking for a solution to this but I'd change the Powershell for the insert command just a little.  Add this after the line setting $size = $_.Size

   $sql = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('$Name', $FreeSpace, $Size)"
   $sql | Out-File "C:\tmp\debug.sql" –Append # to wherever is appropriate for your system
   $cmd.CommandText = $sql

To me using the extra variable but getting an easier to read insert command is a good tradeoff and the debug.sql file makes finding sql errors much easier.
HTH


22839608 is the prerequisite patch for 22839614. If you try to replace it with a newer one (23530402), the prerequisite will not be fullfiled for 22839614 -> conflict.

I would try to rollback the conflicting patch (160419 JavaVM) and install the newer version of it:

opatch rollback -id 22839614

sqlplus / as sysdba
startup upgrade
@?/sqlpatch/22839614/postdeinstall.sql
shutdown immediate
startup

Now try to apply 23530402, and after that, apply 23515277 (160719 JavaVM, you did not mention this, but that is the newer version of 22839614).


slave server is down,master server query halts (postgresql)?

In synchronous mode, your primary needs to wait for the result to be acknowledged by the secondary to answer.

From the documentation (links below):

  When requesting synchronous replication, each commit of a write
  transaction will wait until confirmation is received that the commit
  has been written to the write-ahead log on disk of both the primary
  and standby server. [...] Waiting for confirmation increases the
  user's confidence that the changes will not be lost in the event of
  server crashes but it also necessarily increases the response time for
  the requesting transaction. The minimum wait time is the round-trip
  time between primary to standby.

That's why, except for someone who can afford downtime (it happens and it's not so rare), we add other asynchronous secondaries that can become synchronous secondaries if needed.

  The best solution for high availability is to ensure you keep as many
  synchronous standbys as requested. This can be achieved by naming
  multiple potential synchronous standbys using
  synchronous_standby_names.

  In a priority-based synchronous replication, the standbys whose names
  appear earlier in the list will be used as synchronous standbys.
  Standbys listed after these will take over the role of synchronous
  standby if one of current ones should fail.

  In a quorum-based synchronous replication, all the standbys appearing
  in the list will be used as candidates for synchronous standbys. Even
  if one of them should fail, the other standbys will keep performing
  the role of candidates of synchronous standby.

You'll may find useful to read that documentation page :
https://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION


Check if your pg_dump does not add contents to the existing file instead of replacing it with a new one. That was my mistake.


What is the difference between MySQL and PostgreSQL in terms of writing SQL queries?

(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated):

Lots more features.

CHECK constraints
True SERIALIZABLE isolation
Arrays (including index support for arrays)
Window functions (lead, lag, row_number, etc)
Common table expressions (WITH queries) including recursive CTEs and writeable CTEs
The hstore hash type and its index support
SQL/XML support
json support
A rich set of functions and operators for many types
Partial and function/expression indexes, including partial unique indexes and unique expression indexes
Composite types
User defined aggregates and operators
Set-returning user defined functions
User defined types
Transactional DDL (this is wonderful)
GIN and GiST indexes for complex indexing needs, including K-nearest-neighbour indexing
PostGIS
UNLOGGED tables (though MyISAM meets this need in MySQL)

See the comprehensive user manual for details.

However:

There are no session variables in PostgreSQL, unlike MySQL. (You can abuse GUCs for this, but not to do the same sort of things you do with MySQL session variables).
PostgreSQL has no INSERT ... ON DUPLICATE KEY UPDATE ("upsert")
At the moment, PostgreSQL has different replication options than MySQL. MySQL has no point-in-time recovery, but PostgreSQL has no logical replication (yet, we're working on it).
GROUP BY ... ROLLUP for summaries of groupings. Doing the same thing in PostgreSQL requires a union, usually over two queries over a CTE.
PostgreSQL doesn't have a built-in event scheduler. You generally just use cron or the Windows Task Scheduler; there's also PgAgent from PgAdmin-III.


Determining what objects SHRINKDATABASE will free space from

A few thoughts:

1. Never use SHRINKDATABASE, always use SHRINKFILE.

You want to be in control of which file it is taking space from, and exactly how much so you leave some space in reserve. Run a query like this to see the actual used/free space in each file:

SELECT DB_NAME() as dbname, type_desc, name as logical_name,
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
FROM sys.database_files WITH (NOLOCK)
ORDER BY type, file_id;

Let's say your primary data file is 100GB, but only 20GB is actually used, you might have a final target size of 25GB or 30GB to leave some internal free space. So you'd do a shrink something like:

DBCC SHRINKFILE (name='logical_name', size=30000)

2. Internal SHRINKFILE Behavior

Internally, shrinking a data file simply moves used pages one at a time from the end of the file to an open spot near the beginning until it has enough space at the end of the file to meet your target size.

It's a fairly simply brute-force method and pays no attention to the contents of the pages, which is why it frequently results in table fragmentation. From the Paul Randal article you linked:

  A data file shrink operation works on a single file at a time, and uses the GAM bitmaps to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on.

If the page it wants to move is locked by another process, it will be blocked.

3. SHRINKS keep their progress

If you start a shrink, and then have to stop it because of blocking or other conflicting activity, it will retain its progress. All the pages it successfully moved will stay moved. You can resume later to finish the work.

4. Finding out what tables will be impacted by a shrink

There might actually be some ways to do this, using queries on sys.allocation_units or sys.partitions or maybe using DBCC PAGE or something, but my recommendation is: don't bother, large databases will have the scattered remains of thousands of objects across the data file, most of which you don't care about.

If you're insistent, here are some advanced articles I found via search:

Stack Overflow: See what data is in what SQL Server data file?
Using DBCC PAGE to Examine SQL Server Table and Index Data by Armando Prato
Inside the Storage Engine: GAM, SGAM, PFS and other allocation maps by Paul Randal
Analyzing SQL Server Data File Anatomy by Murilo Miranda

You can already find out what tables/indexes are at play when it starts blocking, everything else is noise.

If that lock never clears to give you a chance to move the page, then you might have to schedule some downtime with no other activity.

5. When you are done, do a reindex to fix fragmentation

The data file might grow a bit again, but unless your database is one massive table, you'll still likely be left with a smaller end result than when you started.


SQL Server : Job not reconnecting to restarted Linked Server

Querying the linked server seems to get it to reconnect. Calling sp_testlinkedserver doesn't always work, but a select statement will.

Sometimes there is a two minute lag from the start of the downed server to the next successful run of the job. In any case, it's simply a matter of waiting.


So assuming that node(n) is a physical box, and the host(n) is a mongod process...If the link between the nodes fails, nothing cool will happen :).

node1 will continue operating as normal, and node2 will just sit there asking wtf.  The regular secondary will only have 2/4 votes which is not the strict majority.

If node1 fails, then you will have to manually configure the secondary on node2 as the primary.

When node1 comes back online, the previous primary will come back online as a secondary and it will detect any unfinished writes, and do a rollback which will require manual intervention.

Keeping in mind that you need a strict majority to elect a new primary, I would try putting the arbiters on their own system that is independent of the two nodes.


Find data size of table for several identical databases on a instance

DECLARE @table  SYSNAME = N'Personal Information',
        @schema SYSNAME = N'dbo';

CREATE TABLE #sz
(
  dbname     NVARCHAR(255),
  fullname   NVARCHAR(768), [rows] SYSNAME,
  reserved   SYSNAME,       [data] SYSNAME,
  index_size SYSNAME,       unused SYSNAME
);

DECLARE @sql NVARCHAR(MAX) = N'DECLARE @t NVARCHAR(512);';

SELECT @sql += N'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
    + N'.sys.tables AS t INNER JOIN ' + QUOTENAME(name)
    + N'.sys.schemas AS s ON t.[schema_id] = s.[schema_id]
    WHERE t.name = @table AND s.name = @schema)
    BEGIN
      SET @t = N''' + QUOTENAME(name) + N'.'' + QUOTENAME(@schema)
        + N''.'' + QUOTENAME(@table); INSERT #sz(fullname, [rows],
          reserved, [data], index_size, unused) EXEC '
          + QUOTENAME(name) + N'.sys.sp_spaceused @t; UPDATE #sz
            SET dbname = N''' + name + N''' WHERE dbname IS NULL;
    END '
FROM sys.databases
WHERE database_id > 4 AND [state] = 0 AND is_read_only = 0;

EXEC sys.sp_executesql @sql, N'@table SYSNAME, @schema SYSNAME', @table, @schema;

SELECT [database] = dbname,
  [schema] = @schema, [table] = @table,
  [rows], reserved, [data], index_size, unused
FROM #sz;

DROP TABLE #sz;


Update and select concurrently running on same set of rows but different indexes are referred?

What you are looking at is more of a blocking scenario. Transactions usually happen quickly and you would not notice a difference when an update and select are issued at the same time.

As per your scenario, the update will happen first. Then depending on your concurrency level, the select will be blocked till the end of the update transaction. In a good case scenario, this happens at the micro second level, and the end user does not notice it.

One way a deadlock can occur: the update transaction, while blocking the select transaction, should request for a resource that is held by the select transaction.


SQL Server 2012 Express high Commit (KB) (low virtual memory condition)

Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory: sqlservr.exe (2792) consumed 28103749632 bytes

This is general message saying their is low virtual memory condition.

  Why would sqlservr.exe be using so much virtual memory?

Their can be quite a few reasons for such messages.

Windows is facing low memory condition
Page file is not configured properly
Some application installed is taking too much memory or leaking memory

As per the error message the SQL Server  was the one which required huge amount of memory and was paged out to disk but that is not the only component. You need to find out why SQl Server required huge amount of memory. Knowing the limitation of express edition you should be aware to not run memory intensive queries.

In the error message I can see w3wp.exe which means application is also running on same machine in such case its quite possible windows would face low memory condition. What is total RAM present on the system ?. Since you have multiple appplications running make sure you have enough RAM on system

  I thought SQL Server 2012 Express was limited to 1GB of memory - is that just a physical memory limit?

Aaron already pointed out and I am going to emphasize on that, starting from SQL Server 2012 the SQL Server express database engine can consume more than 1G of memory. The BOL information is not updated one it still says memory is limited to 1 G which is incorrect. As per this support article

  Starting with SQL Server 2012, these memory limits are enforced only
  for the database cache (buffer pool). The rest of the caches in the
  SQL Server memory manager can use much more memory than is specified
  by these edition limits. For example, a SQL Server 2012 Express
  edition can use only a maximum size of 1.4 GB for its database cache.
  Other caches (such as the procedure cache, the metadata cache, and so
  on) can consume memory up to the size specified by the "max server
  memory" configuration.

EDIT:
From the output of DMV sys.dm_os_process_memory.

Physical_memmory_in_use_KB=445300 KB which is 445 MB

This is physical memory used by SQL Server instance. This is memory backed by physical RAM

Virtual_address_space_committed_kb= 29116348 KB which is 27 G
This is total SQL Server memory utilized (RAM+Pagefile) by SQL Server. So you can see now amount of SQL Server memory curently paged out to disk is 27G -0.4 G.
You need to find out why and what has caused SQl Server to request so much memory. I strongly suggest you to appply SP3 ASAP.

I am not ruling out possibility of McAfee not causing the issue but with statistics posted it seems SQl Server is one which is requesting more RAM


Postgresql - multi-tenant database - backup/restore single database

Restoring the database will take as long as it takes.  There is no way to know with the information you provide.  If each individual database is quite small, step 2 could be acceptably fast with dozens or maybe even hundreds of databases in the cluster.

Having many database clusters will incur more overhead.  There is a certain minimal size of a cluster (around 21MB for the usual initdb run) which can be amortized over all databases in the cluster. You will have to manage many wal streams, and if you most archiving is driven by an archive_timeout that means you will have many more files in total by dividing them.  You will likely run out of available semaphores, but you can recover some of them by lower the max_connections setting.  On modern OS, you could probably configure the system
to allow a lot of semaphores.  You should be able to test some of this stuff on your own infrastructure with some relatively simple scripting.

But if you do run one cluster for each customer and have 100s of customers, there is no reason you couldn't spread them over multiple machines.


mysql: How do you determine the ideal 'open_files_limit' setting value?

You may find this surprising, but mysqld actually determines the correct value for you upon startup.

The MySQL Documentation says the default value for open_files_limit is 0. Yet, when you run the command SHOW VARIABLES LIKE 'open_files_limit';, it does not come back with 0. It returns what mysqld would comfortably run with.

The MySQL Documentation says the maximum value for open_files_limit is 65535. I have seen some systems with the setting above 100000 upon startup (132332 to be exact). That being the case, there are some instances where you cannot set open_files_limit any higher.

Just run the command I mentioned

SHOW VARIABLES LIKE 'open_files_limit';

If the number comes back less that 65535, you can experiment with higher values up to 65535.

Keep in mind two more things

If you are using InnoDB with innodb_file_per_table enabled, you have to set innodb_open_files (Default is set to 300).
Keep your eye on the error log. It will post a warning if your setting for open_files_limit is too high.


Just use MySQL:

select password('foo');

PASSWORD('FOO')

*F3A2A51A9B0F2BE2468926B4132313728C250DBF


Query Efficiency - Filter people when name and surname are different columns, and string may appear in any of them

If it is an innodb table you could just use 'match':
(see https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html)

SELECT * FROM users WHERE MATCH (name, surname1, surname2) AGAINST ('Mark Paul' IN NATURAL LANGUAGE MODE);

But honestly i have no idea how it performs with millions of entries...

Cheers
Olaf


The accepted answer doesn't show the 2016 row, which I would expect to be 0 based on the initial SQL in the question.

Here's an alternative that would give the additional 2016 row:

 SELECT i.region,
        DATEPART(YEAR, i.d1) AS YearNumber,
        SUM(rd.Calc) / ISNULL(SUM(ISNULL(necombs, 0)), 0) Answer
 FROM @info i
 CROSS APPLY (VALUES(CASE WHEN DATEPART(YEAR, d1) = '2015' THEN ISNULL(p1, 0) + ISNULL(p2, 0) + ISNULL(p3, 0) + ISNULL(p4, 0) + ISNULL(p5, 0) ELSE 0 END)) AS rd(Calc)
 GROUP BY Region, DATEPART(YEAR, i.d1);


WITH Sales_CTE ([50001 Sales], [50002 Sales], [50001 margin],[50002 margin],[50001 profit],[50002 profit],[50001 cost],[50002 cost])
AS
(
select * from
    (
    select rtrim(cast(csstor as char))+' Sales' as s
            ,rtrim(cast(csstor as char))+' margin' as m
            ,rtrim(cast(csstor as char))+' profit' as p
            ,rtrim(cast(csstor as char))+' cost' as c
            ,[TOTAL_PRICE]
            ,[Margin]
            ,[TOTAL_Profit]
            ,[Total_COST]
    from sales

    ) as result_1
pivot (sum(total_price)     for s in ([50001 Sales],[50002 Sales])) as pv_tb
pivot (sum(Margin)          for m in ([50001 margin],[50002 margin])) as pv_tb1
pivot (sum(total_profit)    for p in ([50001 profit],[50002 profit])) as pv_tb2
pivot (sum(total_cost)      for c in ([50001 cost],[50002 cost])) as pv_tb3)
SELECT
MAX([50001 Sales]) as [50001 Sales]
, MAX([50002 Sales]) AS [50002 Sales]
, MAX([50001 margin]) AS [50001 margin]
,MAX([50002 margin]) AS [50002 margin]
,MAX([50001 profit]) AS [50002 margin]
,MAX([50002 profit]) AS [50002 profit]
,MAX([50001 cost]) AS [50001 cost]
,MAX([50002 cost]) AS [50002 cost]
FROM Sales_CTE

Result is
    50001 Sales     50002 Sales 50001 margin    50002 margin    50002 margin    50002 profit    50001 cost  50002 cost
1   39498,300       31647,150   12,562300       11,159300      4961,932         3531,624        34536,3675  28115,5264


"where does MYSQL log start and stop entry with timings."

In the error log, it's formatted with a timestamp.

1st, locate your error log:

mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| log_error     | /usr/local/mysql/data/mysql-error.log |
+---------------+---------------------------------------+
1 row in set (0.00 sec)

Then just tail out the contents:

Fri May 02 $ tail -n 100 /usr/local/mysql/data/mysql-error.log
2014-05-02 08:36:35 1024 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
... irrelevant lines ommitted ...
2014-05-02 08:36:45 6473 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Furthermore, you can query the server status variable uptime to display the number of seconds the server have been up.

mysql> SHOW STATUS LIKE 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 13833 |
+---------------+-------+
1 row in set (0.00 sec)


How to select from a table without including repeated column values?

SQLfiddle

SELECT H.*
FROM History AS H
OUTER APPLY
(
    SELECT TOP (1)
        H2.Price
    FROM History AS H2
    WHERE
        H2.Product = H.Product
        AND H2.Kind = H.Kind
        AND H2.Effective < H.Effective
    ORDER BY
        H2.Effective DESC
) AS X
WHERE
    NOT EXISTS (SELECT X.Price INTERSECT SELECT H.Price);

This is a good execution plan for the small number of rows given in the question. For a larger table, the ideal index for this query is:

CREATE UNIQUE INDEX [dbo.History Product, Kind, Effective]
ON dbo.History (Product, Kind, Effective DESC)
INCLUDE (Price);

That index is essentially the clustered index keys in a more helpful order. Depending on how the table is used for other queries, it might be better to replace the clustered index instead of creating this new index.

Do not use batch estimated cost percentages to compare different queries. This is not a valid comparison in general. The costs are always optimizer estimates, and not intended to be used this way. Check actual performance metrics (elapsed time, I/Os, CPU usage, memory usage) by all means, but do not put your faith in the percentages.

Whether this query or the one based on ROW_NUMBER is more efficient depends on the distribution of the data, and other factors. Each has their advantages and disadvantages. This query has advantages when there are many rows for each (product, kind) combination.


(1) Consider designing the schema so that you join on users.id, not users.email.  That will lead to a better way to formulate the query -- by computing the aggregates before joining to users.

(2) Don't hide indexed columns inside functions; the index cannot be used.  Instead of

WHERE DATE(rounds.dateTime) BETWEEN CURDATE()-INTERVAL 1 WEEK
                                AND CURDATE()

use

WHERE dateTime >= CURDATE() - INTERVAL 1 WEEK
  AND dateTime  < CURDATE()

(3) Composite indexes:

INDEX(email, dateTime)   -- after doing (2)
INDEX(dateTime)  -- after doing both (1) and (2)


Is it bad practice to use a data-warehouse for providing reorganized non-BI data?

The first (top) example is essentially the most common ways to design the ETL process for a BI datawarehouse and datamarts. You don't want the lower example, as you'll need to build, deploy/version and troubleshoot multiple instances of the same code.

As for "misuse" or not, it all comes down to how you design the datamart. Typically, star schemas are designed for analytics, but there's nothing to say it won't work for OLTP-type work. I would consider the amount of work required to remodel the data when loading - is it worth the extra complexity, or would you be better off querying the source database directly from your app?
Maybe a database view in the source database(s) could do the same, with a lot less complexity?

Be really careful with "real-time" BI implementations: The more "real-time" you build the solution, the more complex it inevitably gets.

On the one hand, a process that does a truncate-and-load every night is relatively simple to build and run.

And incremental loads that run every hour, and you need to start identifying new, changed and perhaps even deleted (!) rows in the source data. This may be hard or even impossible, depending on how the source data is structured or shipped to you.

A full-on "real-time" solution (within quotes, because unless you have just views on the source database, it's not really realtime) will probably need some kind of change capture, trigger or similar complex logic for every dependent table. All of those things can potentially affect the source databases adversely with regards to performance and stability.

It gets even more complex and costly when those tables interact, say for example, if you need to join snowflake schemas into a star schema.

To answer your third question, pretty much any ETL tool (SSIS, Pentaho, etc) will support basic datawarehouse ETL. Once you add near-realtime logic, the choice of platform ETL tool becomes less relevant (you'll probably need to script most of this yourself anyway), and the solution becomes very dependent on the database platforms of the source data as well the infrastructure and platform of your datawarehouse.


Probably the simplest would be to run a server-side or client-side trace using SQL Server Profiler.

Another option might be to use a protocol analyzer, such as WireShark, or one of the many mentioned in this search result: https://duckduckgo.com/?q=tcp+protocol+analyzer&t=ffsb


Performance Recommendations for Large Volume of Data

It's important to know what sort of queries you are running.  Are they mostly based on the time of the record, or something other field?  That will inform what sort of approach you should take here.  Maybe your indexes should actually be multi-indexes for example.

What's your EXPLAIN statement say for your queries?  That's really one of the best ways to see where exactly things are slowing down and attack it.

Edit: Sorry, I just saw the SUBSTR query, I believe you can make indexes based on substrings of a column.  Have you done that?  That IS different from just having an index on the column itself vs. having a index on the substring(1,8) of said column.


Changing primary key from varchar to int

You do not have to make another table if you are changing the primary key.

The steps I would carry out are:

Add the new integer column that will become the primary key.
Populate it with unique values and make sure you have a unique constraint on it. (It must be unique to be certain it can become primary key)
In each child table add an integer column for the foreign key.
Populate the new foreign key by looking up the old foreign key.

e.g.

tblUser (
 oldID varchar(10),
 newID int, -- The new column added, I'm assuming at this point you have the column populated
 ...
)

tblUserLogin (
 keyUser_oldID varchar(10),
 keyNewID int NULL,
 ...
)

Run the following... I'm work mostly with SQL Server these days, so hopefully this pseudocode gives the right idea. The principle applies to any major table-based database system.

UPDATE tblUserLogin
SET keyNewID = (SELECT newID FROM tblUser WHERE oldID = keyUser_oldID

Add the foreign key between the two new columns.
Make sure that the new primary key is set to auto increment.
Once oldID and keyUser_oldID are no longer referenced anywhere you can drop those columns. But only after you are completely certain!

A few things to consider

That's the general principle, a few things to consider.

This works in simple cases and assumes you can stop people using the database for a while and that any code that uses these tables is ready to be switched to use the new column names.
Subqueries are not the most efficient way, but this is a one-off task and unless you have millions of rows and limited time to make the switch it should be fine.
You didn't say what you are storing in the varchar field? If it is a number (but in varchar format) you could always do UPDATE tblSomeTable SET keyNewID = CAST(keyOldID as int) which would be faster and easier.

Most importantly though...

..you don't mention if the database is used for anything important. Even if it isn't:

Back it up beforehand if you haven't already. If you make a mistake in an update statement you can always reference a backup copy.
Think the steps through, these are structural changes, 30 minutes spent planning could save hours of time later on.
Have a backup plan in case something goes wrong!


How to generate a random character string of length 8 in Netezza?

If you're happy with a hex string, you could do:

select to_hex(round(random() * 2^32 - 1)::BIGINT) AS randchars;

You could also write a user-defined function if you want a very specific type of string. For this, you would need SSH access on the Netezza appliance and some basic knowledge of C++. It may be simpler for you to write these functions in Lua if you're not comfortable with C++ or if you don't have an account on the appliance, but this requires Netezza Analytics to be installed.


Effective backup and archive strategy for database and linked files

If you store your files by hash to and link with the hash to the database row, you can simplify maintaining consistency.

In that case you can either:

keep on incrementally backing up the filesystem (eg with rsync) and never delete

or
if you need to free up space, keep an audit of link count and when the last link was deleted - you can then periodically purge the filesystem of files not linked to in your recovery window. Something like rsnapshot might help you archive these long-term in a space efficient way


Boot page flag for cleanly shut down database

It's part of the dbi_status field, which as Sean Gallardy said aren't documented and no-one's blogged about them (yet - so I don't want to get into details here). You can experiment with open transactions, crashing the server and moving the log file to force a database into the RECOVERY_PENDING state to watch which bits get set (again, no blog post about that yet).


Social network comments and posts model in PostgreSQL 9.6

First the author table, that's easy.

CREATE TABLE author (
    authorid   serial  PRIMARY KEY,
    username   text    UNIQUE
);

Now we use a self-referential post table to create the hierarchical structure.

CREATE TABLE posts (
    id           serial  PRIMARY KEY,
    parent_post  int     REFERENCES posts,
    authorid     int     NOT NULL REFERENCES author,
    body         text    NOT NULL
);

Test data...

INSERT INTO author (authorid,username) VALUES
  (0, 'ecarroll'),
  (1, 'jbob');
INSERT INTO posts (id,parent_post,authorid,body) VALUES
  (0, null, 0, 'HALLO WORLD'),
  (1 ,0,    1, 'HALLO EVAN' );

Recursive query to get it working.

WITH RECURSIVE t(chain,author_un,text,id) AS (
   SELECT ARRAY[id], username, body, id
   FROM posts
   INNER JOIN author USING (authorid)
   WHERE parent_post IS NULL
   UNION ALL
     SELECT t.chain||p.id, username, p.body, p.id
     FROM t
     INNER JOIN posts AS p
       ON (t.id = p.parent_post)
     INNER JOIN author AS a
       USING (authorid)
)
SELECT *
FROM t;

 chain | author_un |    text     | id
-------+-----------+-------------+----
 {0}   | ecarroll  | HALLO WORLD |  0
 {0,1} | jbob      | HALLO EVAN  |  1
(2 rows)

For more information on this method, see my post here

How to build a table for a private messaging system that supports replies?


Constraint many to many table between two child tables

Why must the employees working on a project strictly belong to the same division that owns the project?  That sounds more like a business rule that you can leave up to your application to enforce rather than something that requires DRI.

But let's say you need to enforce this using DRI.  You said you have a many-to-many table associating employees with projects.  Assuming you are using SQL Server, you could expand this table as follows:

CREATE TABLE dbo.Employee_Project (
     ProjectID            INT
   , ProjectDivisionID    INT

   , EmployeeID           INT
   , EmployeeDivisionID   INT

   , Hours                INT

   , CONSTRAINT FK_Project FOREIGN KEY (ProjectID, ProjectDivisionID)
        REFRENCES dbo.Project(ProjectID, DivisionID)

   , CONSTRAINT FK_Employee FOREIGN KEY (EmployeeID, EmployeeDivisionID)
        REFERENCES dbo.Employee(EmployeeID, DivisionID)

   , CONSTRAINT CK_EmployeeProjectSameDivision CHECK (ProjectDivisionID = EmployeeDivisionID)
);

This is what's going on here:

We have two composite foreign keys here, one for Employee and one for Project, so we can have the DivisionID for both entities in the same table and guaranteed by DRI to be correct.
These composite foreign keys require a UNIQUE index on the referenced tables. So in addition to your primary keys on EmployeeID and ProjectID in those tables, you'll need unique keys on (EmployeeID, DivisionID) and (ProjectID, DivisionID).
The CHECK constraint guarantees that an employee can only be assigned to a project owned by the same division.

If you want to see another example of this design pattern, here's the answer I gave to a design problem that had similar requirements.


How do I know what indexes to create for a table?

Short rules of thumb. (Some of these are created automatically, but can possibly be manually dropped later, depending on your dbms. Don't assume you will always work on PostgreSQL.)

Index every primary key.
Index every foreign key.
Index every column used in a JOIN clause.
Index every column used in a WHERE clause.
Study your documentation to learn the "esoteric" indexing options your dbms supports.

Every primary key means that multi-column primary keys should have a single index covering all columns. PostgreSQL will create this index automatically if you declare a multi-column primary key.

There are many cases in which a single multi-column index gives you better performance than several single-column indexes. Monitor slow queries and do testing to figure out which is which.

Assume that any change to indexing will improve some database activities and degrade others. I find it helpful to have a set of SQL statements that I can profile before and after making changes to indexes. This set includes SELECT, INSERT, UPDATE, and DELETE statements.

There's no substitute for studying the docs for your particular dbms.

CREATE INDEX
Indexes (Note especially the sections on indexing expressions, on partial indexes, and on examining index usage)


why sp_ExecuteSql output parameter returns null when procedure shows correct value in results pane

You never actually set the value of the output parameter in your query. In order to return the value as an output parameter you need to assign the result of the query to the parameter itself.

Try this:

set @sql =  'SET @number_out = (SELECT ' +  @NumberFieldName
                              + ' FROM ' + @table
                             + ' WHERE ' + @IdFieldName +' = '+ @recid + ')';

Or you could do:

set @sql =  'SELECT @number_out = ' +  @NumberFieldName
          + ' FROM ' + @table
          + ' WHERE ' + @IdFieldName +' = '+ @recid;


Error 5123 when creating a database with my SQL Server on Linux

The problem isn't solved, but I just donwloaded the software 'Azure Data Studio' developed for linux and I have been able to see that the tables were being created in master. It's seems this app works and is more convenient to me for learn msslq by now. So maybe we can close the post.


Is my theory on INSERT deadlocks correct?

Process 7508 waits for AccessExclusiveLock on relation 939490

Nothing in your shown code would take an AccessExclusiveLock on a relation.  The 7508 member of your deadlocked pair is doing something different.  Look in the log file to see if there is a clue about what that is. (The log file will show what statement it was running when the deadlock occured, but that might not be the same statement as what took out the lock, but at least it will be more information than you currently have)

maybe, the transaction block causes the third INSERT to not "see" the previous INSERTs because they have not yet been committed? Is that it?

No, a statement in a transaction can see INSERTs done by earlier statements in the same transaction.


There are many encryption methods available in mySQL.

If you need two way encryption you could use aes_encrypt which has the accompanying aes_decrypt

If if you only need a secure hash then you could use sha2

The following statement could get you a similar result to openssl_random_pseudo_bytes

SELECT HEX(SHA2(CONCAT(NOW(), RAND(), UUID()), 512));

The statement above takes NOW() and concatenates it with RAND() and a UUID(), then performs a 512 bit SHA2() encryption on the result, and then converts that to HEX()


How are block nested loop joins optimised for disk reads?

To answer your first question, it depends on if the RDBMS has stored the data as rowstore or columnstore. For a rowstore table (much more common), pages are organized into rows. You would read all columns for a row into memory. For a columnstore table, pages are organized into columns. You could just read only the columns relevant to the query into memory.

To answer your second question, why must "recording" be the outer table? What if the "uses" table has zero rows? Wouldn't it be more efficient to pick that as the outer table? Putting that aside, it's a very general question. You haven't listed a specific RDBMS platform. If there are no sufficient indexes the "recording" table then I would expect the optimizer to generally filter out pages using the filter on the "made" column before performing the join. I can't think of an advantage in doing it another way.

Why does the query planner need to calculate the number of disk page reads done? I don't follow that part.


Need explanation SELECT query on many to many relationship

Your version uses a very old JOIN syntax from the ANSI-89 standards - as in 1989. In 1992, the standard was updated to adopt the JOIN...ON syntax. You can read about the differences and their adoption in this SO question.

The problem with listing tables in a FROM and then listing the conditions in the WHERE is that it's very easy to forget a condition somewhere. How easy would it be to accidentally write:

SELECT email
FROM
    user,
    alias,
    alias_member
WHERE
    user.user_id = alias_member.user_id
    AND alias.address = 'foo@domain.tld';

Now, your friend's version also uses a weird convention (the "nested" JOIN). Here, it's trying to help with the fact that you have alias_member as the last table in the JOIN, but is needed to connect the other 2 tables. If alias_member is the first table, the syntax gets a bit cleaner:

SELECT
    email
FROM
    alias_member
INNER JOIN
    user
  ON alias_member.user_id = user.user_id
INNER JOIN
    alias
  ON alias_member.alias_id = alias.alias_id
WHERE address='foo@domain.tld';


How to take backups of MySQL databases, explicitly based on PyMySQL

Google searching turns up a few good posts on Python script wrappers for mysqldump

Oct 08, 2014 : StackOverflow : How to copy a database with mysqldump and mysql in Python?
May 05, 2014 : GitHub Gist : A configurable python script that wraps around mysqldump with some additional configuration.
Dec 03, 2013 : TecAdmin.net : Python Script for MySQL Database Backup
Aug 30, 2010 : StackOverflow : Python subprocess, mysqldump and pipes

The first one looks like the easiest way. The code from it says

subprocess.Popen('mysqldump -h localhost -P 3306 -u -root mydb | mysql -h localhost -P 3306 -u root mydb2', shell=True)

Although I am no expert in Python, if you trust your Python skills and instincts, then ...

Give it a Try !!!


procedure for postgres to create table if not exist

Just use CREATE TABLE [IF NOT EXISTS]

Looks like this,

CREATE TABLE IF NOT EXISTS test (
  the_id  int   PRIMARY KEY,
  name    text
);

If you must wrap it in a function (don't though, there is no point),

CREATE FUNCTION myCreateTable() RETURNS void AS $$
  CREATE TABLE IF NOT EXISTS test (
    the_id  int   PRIMARY KEY,
    name    text
  );
$$ LANGUAGE sql
VOLATILE;

If you want that function to accept a name (still wouldn't suggest this),

CREATE OR REPLACE FUNCTION myCreateTable(myIdent text) RETURNS void AS $$
  BEGIN
    EXECUTE format(
      '
        CREATE TABLE IF NOT EXISTS %I (
          the_id  int   PRIMARY KEY,
          name    text
        );
      ',
      myIdent
    );
  END;
$$ LANGUAGE plpgsql
VOLATILE;

[ IF NOT EXISTS ] has been in PostgreSQL since 9.1


Normally you want to throw away as many rows as you can, as quickly as you can.  That doesn't seem likely here, unfortunately.

All you have is 'deleted = 0' for each table, so MySQL will pick a table to start with.  In your explain, it picked user_details, resulting in 1.5M rows being used.

Do the other tables have fewer active rows?  If so, you'd want to start with that table instead.

Once it picks a table, it will join across.  If we're starting with user_details, you're off to user_accounts_user_details, so you'll want an index on user_details(deleted, id) so the ids can be found via the index.

Since you're coming into user_accounts_user_details with the contact_id, you'll want to start your index with that.  We know that deleted=0 there, too, so add that.  And, once again, you'll want the id for the next table in your index.  I think that means user_account_user_details(contact_id, deleted, account_id).

We'll take that account_id over to user_accounts, so we'll need an index there.  We still have deleted=0, and we're not continuing on to other tables, so try user_accounts(id, deleted).

Depending on the quantity and size of the columns you're SELECTing from each table, you can add them at the end of the indexes, which can keep MySQL from having to read the underlying row from disk.  In your case, it looks like you're SELECTing a good number of long columns, which I wouldn't add to the composite indexes described above.  I might be tempted to include user_accounts.name, since you use that in the ORDER BY clause.

I hope that helps.

If possible find some other way to reduce the number of rows more quickly.  Can you limit by date, or something else?  Starting with 10 records in the first table would be so much better than starting with 1M.

Good luck.


Replicating different set of tables to subscribers

Yes, you just need to have a different publication created for a specific Subscriber with the required articles. This will help you in customizing your subscriber as per the required articles.


Grouping numbers based on closest sum to a specific value with Postgres

A bit of research reveals that this question is known as the subset sum problem  in computer science.

On stackoverflow, Lukas Eder provides an Oracle solution to a similar question, and a longer analysis on jooq's blog.

Here's a postgres version derived from his work:

CREATE TABLE tab(v text, c int);

-- populate table
INSERT INTO tab(v,c) VALUES('Z', 217);
etc...

-- show 10 better results

WITH RECURSIVE sums (subset_sum, max_v, a) AS (
        SELECT
            c,v,array[v] as a
        FROM
            tab

        UNION ALL

        SELECT
            c + subset_sum,
            v,
            array_append(a, v)
        FROM
            sums
        JOIN
            tab
        ON sums.max_v < tab.v
    )
SELECT subset_sum, a FROM sums WHERE subset_sum <= 60000
ORDER BY 60000-subset_sum
LIMIT 10;

The result with the sample data is:

 subset_sum |      a
------------+-------------
      59985 | {A,K,N,O,U}
      59985 | {I,K,R,T,U}
      59985 | {A,G,J,N}
      59985 | {A,F,G,P,Y}
      59985 | {C,K,O,Z}
      59985 | {A,C,F,J,O}
      59985 | {F,H,L}
      59985 | {D,E,M}
      59985 | {A,C,K,U}
      59985 | {E,J,K,M,R}
(10 rows)

Note that the execution time will grow exponentially with the row count in the base table.


Disable foreign key checks during import

The command

SET FOREIGN_KEY_CHECKS=0;

sets the value of a variable in a session scope. So it affects on the current session only, and do not affect on another sessions, both already running and those that will be launched later.

If you specify new value as a global-scope one using the command

SET GLOBAL FOREIGN_KEY_CHECKS=0;

then the global setting will be set. It affects on the current session and sessions that will be launched later, and do not affects on another sessions, which are already running.

You can see it in practice.

Start 2 CLI (mysql.exe) sessions.
Check the variable value in all windows.
Alter it in the 1st window.
Start 3rd window.
Check the variable value in all windows.
Alter it globally in the 1st window.
Start 4th window.
Check the variable value in all windows.


Is it more performant to use a JOIN or a when using LIMIT 1?

The ORs are deadly for performance.  For starters, decide on 0 or NULL so you won't have to test for both.  (That won't actually help any since there would still be other ORs.)

I'm dubious about the formulation of the LEFT JOIN.  (BTW, OUTER is optional.)  Normally when using LEFT, the ON only says how the tables are related (p.id=qd.product_id); the WHERE provides the filtering.

I don't know which is better, but since you seem to be limiting to one id (via WHERE p.id=?), then the second formulation will look into quantity_discounts only once, and (hopefully) won't have too many rows to check.  That is, the OR may not be as bad as I started out with.

A slight help on quantity_discounts will be

INDEX(product_id, price)  -- in this order

Now I am worried.  One formulation has AND qd.min_amount <= ?, the other does not.


Copy Database option not found in SQL Server Management Studio 2008 R2

That's because you are clicking on the wrong thing.  Don't right-click Databases, but you need to actually right-click the database that you want to copy, and under Tasks there is an option for Copy Database....  In other words, right-click on Database1 and then you'll see this option under Tasks.

As per @AaronBertrand's comment, though, it is highly recommended not to use the Copy Database Wizard.  Just go with a backup and restore.  It'll be less of a headache.

Edit: It seems as though this option is not available for SQL Server Express edition.  When connecting to a SQL Server Express instance, you cannot run the Copy Database wizard. This is due to the fact that the two primary means of executing this task are unavailable in SQL Server Express Edition. First, the Detach/Reattach method relies on SQL Agent (presumably to copy the data and log files from source to destination), and SMO has been removed from 2008 R2, so that method is also unavailable in Express 2008 R2.


In-place up-gradation of SQL server on Azure with pre-image installed.

Once the VMs are deployed, you can do anything you want to them. Apply service packs, cumulative updates, or go full blown in place upgrade. You are responsible for anything that happens inside of the VM once you deploy it.  However, if you have to redeploy the VM, you may be reverted back to the previous version of SQL as it is built off of that image.

Additionally, it is a best practice to migrate to the new version of SQL vs doing in an in place upgrade. It is less risky to migrate and provides you more ways to roll back in the event of a problem than to do in place. You also can test your application on the new version before you upgrade so you know how your application will behave on the new version.

I can say with certainty, you will run into behavior differences as the query engine and cardinality estimator of SQL Server 2016 was completely rewritten to be much more efficient. In most cases, your existing queries will run faster, but in some cases, your queries will run slower. You will want to know that ahead of time so you can mitigate those issues before the users experience them and are soured on the platform.


SQL Server Transaction Commits but results are missing

You state that:

  each transaction commits without error

I believe there is a nuance here that is being overlooked. The DML statement can cause an error and the COMMIT still happens (hence "nothing" is Committed) because this is not being done in a TRY...CATCH construct (and I assume that you are not setting XACT_ABORT ON at the beginning of the scripts). I believe that the DML statements are getting an error (possibly a truncation one?) but the Commit then runs and succeeds.

AND / OR:

Another possibility is that you have an unmatched BEGIN TRAN in one of the scripts (meaning, no matching COMMIT). Or how exactly are you running these scripts? If there is an open Transaction that is not committed by the time the scripts end, then the Transaction will be automatically rolled-back.

Individual DML / DLL statements, when executed outside of an explicit BEGIN TRAN...COMMIT/ROLLBACK are already their own Transaction and are automatically committed. Hence, you do not need BEGIN TRAN...COMMIT for single statements, unless you have some additional logic after the DML / DDL statement that conditionally decides on COMMIT or ROLLBACK. Also, unless you need the ability to discard portions of work that covers many statements within a single Transaction without rolling back the ALL statements, then you don't need to name them. It looks like you should start by removing the BEGIN TRAN / COMMIT / ROLLBACK statements, but still keep the TRY...CATCH statements that you are adding.


Oracle DB hanging after upgrade to 11.2.0.3

select privilege#
from sysauth$
where (grantee#=:1 or grantee#=1) and privilege#>0

That SQL is executed as a user authenticates to the database upon login. This is mentioned in support doc 730066.1

Is there a storm of logins happening periodically?


Scheduling an event every day at a given time

Use the first one if you want to execute your event everyday.

The second syntax will execute once.

Option 1: Execute the event every night at 00:20

Option 2: Execute the event once the 2014-05-01 at 00:20

Max.


How to insert into a table daily automatically in postgresql?

PostgreSQL does not have a built in job scheduler.  There are many options to run jobs on a schedule.  If you already have a generic job scheduler in use in your IT environment, I'd recommend making use of that, rather than installing and configuring and learning a new one just for PostgreSQL.  For example, I use "cron" for all my job scheduling outside of PostgreSQL, so I use it for PostgreSQL as well, using something like psql -f job.sql as the command to run.  You will have to arrange for the job to be able to connect to the database without a password--there are several ways to do that depending on your security environment.

For the query itself, you can't expect the job to run at exactly midnight down to the nanosecond. So run the job several minutes after midnight (to make sure clock skew doesn't cause it to accidentally run before midnight), and put WHERE clauses in the query that uses a timestamp in the table to pull out the data exactly aligned to date boundaries.


My bad.. didn't think to exclude NaT from the query.

MATCH (n:Event)
WHERE n.date <> "NaT" AND datetime(n.date) = datetime("2019-01-01")
RETURN n


Are there security vunerabilites/implications with SQL Mail, currently using SQL Server 2008 R2

Please use Database Mail instead of SQL Mail.  SQL Mail had security issues on several layers and in SQL 2005 SP2 had 'inbound emails' removed.  They then further removed support for it and it is now in the depreciation list.

Database Mail is the solution you are looking for.  It passes audits easily, has lots of documentation, and is fully supported by MS.

Don't use this one:

EXEC sp_configure 'SQL Mail XPs', 1
GO
RECONFIGURE
GO

Do use this one:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

Configuration Wizard Link To Help You Configure It.

To start the Database Mail Configuration Wizard
From Object Explorer, connect to an instance of SQL Server.

Expand Management,

right-click Database Mail,

and select Configure Database Mail.

Choose the Set up Database Mail option to set up Database Mail for the first time.

Choose one of the other options for specific maintenance tasks:

Manage Database Mail accounts and profiles.

Manage profile security.

View or change system parameters.


Good Natural Key For a Physical Mailing Address

As mentioned in the comments, free text fields make it tough to create a primary key on. And for something with as much variability as an address field, it's just not reasonably possible by itself.
This is the type of problem where if you must create a primary key for it, then you will need to use strong validation and/or an address standardization API like this one from the USPS, in your application layer before it hits the database. Even doing that, I don't think you'll have a 100% perfect solution, but it does make it more reasonably possible to then create a primary key on an address field.


Copy DB from SQL Server 2008 to SQL Server 2012

DB Copy has a number of limitations as describe at: http://msdn.microsoft.com/en-us/library/ms188664.aspx

This includes not supporting the copying of System databases, Databases marked for replication, or any database not in a normal status.

However, it does support moving logins, jobs, and so forth.

The typically best way to copy a database with the fewest issues is to do a BACKUP from the source server and a RESTORE on the target server.  This is mentioned here in the discussion of strategies for copying databases:

http://msdn.microsoft.com/en-us/library/ms189624.aspx

If you also need to copy logins from the old to the new server, there are a couple of scripts documented at:  http://support.microsoft.com/kb/246133

SQL Agent Jobs can be scripted and recreated on the target server as well.


SELECT t1.sessionId, t1.dt StartTime, TIMEDIFF(t2.dt, t1.dt) duration
FROM src t1
JOIN src t2 ON t1.event = 'start'
           AND t2.event = 'end'
           AND t1.sessionId = t2.sessionId
           AND t1.dt < t2.dt
           AND NOT EXISTS ( SELECT NULL
                            FROM src t3
                            WHERE t3.sessionId = t1.sessionId
                              AND t1.dt < t3.dt
                              AND t3.dt < t2.dt );

fiddle


Efficient merging (removing duplicates) of arrays

Correct results?
First off: correctness. You want to produce an array of unique elements? Your current query does not do that. The function uniq() from the intarray module only promises to:

remove adjacent duplicates

Like instructed in the manual, you would need:
SELECT l.d + r.d, uniq(sort(array_agg_mult(r.arr)))
FROM   ...
Also gives you sorted arrays - assuming you want that, you did not clarify.
I see you have sort() in your fiddle, so this may just be a typo in your question.
Postgres 9.5 or later
Either way,since Postgres 9.5 array_agg() has the capabilities of my array_agg_mult() built-in out of the box, and much faster, too:

Selecting data into a Postgres array
Is there something like a zip() function in PostgreSQL that combines two arrays?

There have also been other performance improvements for array handling.
Query
The main purpose of array_agg_mult() is to aggregate multi-dimensional arrays, but you only produce 1-dimensional arrays anyway. So I would at least try this alternative query:
SELECT l.d + r.d AS d_sum, array_agg(DISTINCT elem) AS result_arr
FROM   left2  l
JOIN   right2 r USING (t1)
     , unnest(r.arr) elem
GROUP  BY 1
ORDER  BY 1;

Which also addresses your question:

Can the aggregate function remove duplicates directly?

Yes, it can, with DISTINCT. But that's not faster than uniq() for integer arrays, which has been optimized for integer arrays, while DISTINCT is generic for all qualifying data types.
Doesn't require the intarray module. However, the result is not necessarily sorted. Postgres uses varying algorithms for DISTINCT. Big sets are typically hashed, which leaves the result unsorted unless you add explicit ORDER BY. If you need sorted arrays, you could add ORDER BY to the aggregate function directly:
array_agg(DISTINCT elem ORDER BY elem)

But that's typically slower than feeding pre-sorted data to array_agg() (one big sort versus many small sorts). So I would sort in a subquery and then aggregate:
SELECT d_sum, uniq(array_agg(elem)) AS result_arr
FROM  (
   SELECT l.d + r.d AS d_sum, elem
   FROM   left2  l
   JOIN   right2 r USING (t1)
        , unnest(r.arr) elem
   ORDER  BY 1, 2
   ) sub
GROUP  BY 1
ORDER  BY 1;

This was the fastest variant in my cursory test on Postgres 9.4.
SQL Fiddle based on the one you provided.
Index
I don't see much potential for any index here. The only option would be:
CREATE INDEX ON right2 (t1, arr);

Only makes sense if you get index-only scans out of this - which will happen if the underlying table right2 is substantially wider than just these two columns and your setup qualifies for index-only scans. Details in the Postgres Wiki.


Query plan for a dynamically built `query_int` using `format()`

First of all, you can simplify the query you have. format() is not needed, among other things:

SELECT *
FROM   media
WHERE  tag_ids @@ array_to_string(ARRAY (
         WITH  tag_ids AS (SELECT id FROM tags WHERE tag IN ('kittens', 'puppies'))
         TABLE tag_ids
         UNION ALL
         SELECT 0 WHERE NOT EXISTS (TABLE tag_ids)
         ), '&')::query_int
ORDER  BY rating DESC
LIMIT  20;

While your query_int value only uses &, further simplify to:

SELECT *
FROM   media
WHERE  tag_ids @> COALESCE(NULLIF(ARRAY(SELECT id FROM tags WHERE tag IN ('kittens', 'puppies')), '{}'), '{0}')
ORDER  BY rating DESC
LIMIT  20;

Should be faster already. But the core problem remains - which is very similar to the one discussed in detail here:

Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

Basically this: Postgres (necessarily) chooses the query plan based on incomplete information. Column statistics cannot contain all frequencies for many rare values. So it's very hard to estimate which plan will be faster:

plan A: traverse the index media_rating_index and filter
plan B: bitmap index scan on media_tag_ids_gin__int_ops_index, sort by ratings, limit

Your case adds complication with a convoluted subquery, which makes it even harder to estimate what might come out of it.

Whatever else you do, make sure you run the most recent version of Postgres available to you, as your particular problem may well profit from a number of recent improvements around this.

Depending on undisclosed information, one of these may work:

1.

Rewrite your query with LEFT JOIN LATERAL as demonstrated in the referenced answer.

2.

Completely disable plan A by obfuscating the ORDER BY. Assuming it's a numerical column:

SELECT ...
ORDER  BY rating + 0 DESC
LIMIT  20;

Like Laurenz suggested:

Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

3.

Even more radical, delete the index media_rating_index - unless it's needed for other queries!

4.

Jeff suggested a manual setting for n_distinct in the related case, but I am not sure how to achieve the same for array elements ...

Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

5.

Here is a related case from the pgsql-performance list. Your case is simpler, you may improve your situation by just upping the statistics target for tag_ids:

ALTER TABLE media ALTER COLUMN tag_ids SET STATISTICS 1000;

And then run ANALYZE media.
This may be useful in addition to other options, especially the following.

6.

In a related case I had success with a fake-IMMUTABLE function:

CREATE FUNCTION f_tag_id_array(VARIADIC text[])
  RETURNS int[] IMMUTABLE PARALLEL SAFE LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN COALESCE(NULLIF(ARRAY(SELECT id FROM tags WHERE tag = ANY($1)), '{}'), '{0}');
END
$func$

Then use it in your query:

SELECT *
FROM   media
WHERE  tag_ids @> f_tag_id_array('kittens', 'puppies')
ORDER  BY rating DESC
LIMIT  20;

Since Postgres now assumes that f_tag_id_array() is immutable it may choose to evaluate it early and continue with the returned constant - thereby achieving the different query plan for known rare IDs you were after.

Note that we are lying about IMMUTABLE - it is not. So don't build an index on it. And it's not safe to use with concurrent write access on table tags.

And since we are lying there, the function cannot be inlined, so using a simpler SQL function won't buy anything in this case. Rather have the saved query plan feature of plpgsql.

The same worked with STABLE on a Postgres 11 instance, while a Postgres 10 instance required the IMMUTABLE hack ...


SQL Anywhere 17 - Database server connection limit exceeded

You need to use the dblic.exe utility to apply your licence key to the dbsrv17.lic file. See the docs for details.
Max connections is nothing to do with how your server is licenced. You could in theory set up thousands of connections from one network location, and that would use one licence - you want connections from lots of different network seats, so you need more licences. Alternatively you can buy a core based licence.


Load-balanced MySQL cluster without load balancer

Please read my other answer to this question before actually using a MySQL proxy of any kind. If You have 2 master-master servers that a CMS is writing to, and 10 httpd that only read from it, You'll be fine, but (as pointed out in the other answer) that's not always the case. You've been warned.

  MySQL Proxy is a simple program
  that sits between your client and
  MySQL server(s) that can monitor,
  analyze or transform their
  communication. Its flexibility allows
  for unlimited uses; common ones
  include: load balancing; failover;
  query analysis; query filtering and
  modification; and many more.

.

  HAProxy is a free, very fast and
  reliable solution offering high
  availability, load balancing, and
  proxying for TCP and HTTP-based
  applications

If You would run it in TCP mode, it could be even better than Wackamole. If I had to choose between them, I would use HAProxy. Also HAProxy can have a lot of backends, Waclamole can have only 2. Note that HAProxy is "dumb", it connects sockets without any looking on what's inside the stream - dedicated MySQL Proxy might f.e. have an option to point various requests to specified servers.