INSERT of 1 wrong value -> Error; INSERT of 2 wrong values -> Warning!

It is way MySQL is designed. As per the documentation:

  If you try to store NULL into a column that doesn't take NULL values, an error occurs for single-row INSERT statements. For multiple-row INSERT statements or for INSERT INTO ... SELECT statements, MySQL Server stores the implicit default value for the column data type. In general, this is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. ....

I did a test to see the contents of the table (Notice that I got the warnings!):

mysql> CREATE TABLE `dummy` (
    ->     `a` TINYINT(4) NOT NULL
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `dummy` (`a`) VALUES (NULL);
ERROR 1048 (23000): Column 'a' cannot be null

mysql> INSERT INTO `dummy` (`a`) VALUES (NULL), (NULL);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'a' cannot be null |
| Warning | 1048 | Column 'a' cannot be null |
+---------+------+---------------------------+
2 rows in set (0.00 sec)

mysql> select * from dummy;
+---+
| a |
+---+
| 0 |
| 0 |
+---+
2 rows in set (0.00 sec)


ORDER BY clause is allowed over column that is not in SELECT list?

It's not always possible:

  If DISTINCT is specified, the expression in the ORDER BY clause have access only to the virtual table returned by the previous step (VT5)

(VT5 is virtual table returned generated by SELECT)

  If DISTINCT is not specified expressions in the ORDER BY clause can access both the input and the output virtual tables of the SELECT phase.

Explanation is taken from "Inside SQLServer 2008 T-SQL Querying" - I'm not sure if this particular chapter is available online (Step 6: The Presentation ORDER BY Phase, p.16.)

Thus,

SELECT distinct field2 FROM table1 order by field1

gives an error:

  Msg 145, Level 15, State 1, Line 1
  ORDER BY items must appear in the select list if SELECT DISTINCT is specified.


SELECT FIRST 1 value FROM table WHERE id <= 2003 ORDER BY id desc

Get the greatest value not greater than.


Percona / MySQL audit log plugin - limiting the content?

OK, from what I can tell, Percona Audit Log Plugin, has very little in the way of configuration.

I looked at McAfee Audit Plugin, but that doesn't seem to work on Debian MySQL.

I also looked at MariaDB Audit Plugin, and that seems to have some level of filtering (DDL or DML).
Unfortunately it requires 5.5.42 , and most of my DBs are 5.5.40. Still, on my test system it seems to work.


How to set CHECK_POLICY OFF in the sql script

You have to do

Alter login login_name with CHECK_POLICY = { ON | OFF }
Alter login login_name with  CHECK_EXPIRATION = { ON | OFF }

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


Best Design for this "Sweepstakes" Project?

Assuming my comment about Archiving vs. Backup is accurate, go with System 2.

You don't mention it in your post, but System 1 would require some sort of count operation on each insert into the sweepstakes_entries and sweepstakes tables.  Initially the performance associated with the count will not be a problem.  However, as your tables grow the BadgeEarned? query will get slower and slower.

What's worse, your best approach to reduce that burden will be to archive data about closed sweepstakes into a less active area in your database, but doing that will be difficult which you point out in System 1 cons.

So best to store the user_stats somehow and update them as necessary.


You're probably not still looking for an answer, but in case anyone else stumbles across this question.

This is an issue in WordPress development all the time. There's a tool built specifically for it by Interconnectit - https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

I've never tried it for anything other than WordPress, but it connects directly to the database using the standard user/pw info, so you should be able to connect it to any database. I think the only thing WordPress specfic is it auto-populates the database info.

It's quick and easy to use, and it deals with serialized strings so they don't become a hassle.


Why do case/iif return nullable datatypes?

First off, the column is_nullable returned from sys.dm_exec_describe_first_result_set_for_object has the following definition as per docs.Microsoft.com:

  Contains the value 1 if the column allows NULLs, 0 if the column does
  not allow NULLs, and 1 if it cannot be determined that the column
  allows NULLs.

This means that the engine could also be looking for a situation where it can't predict the nullability of the return column. (This is more for your information than anything else - in this case it doesn't appear to be happening)

Now when we simplify the stored procedure and eliminate the variables as such:

CREATE PROCEDURE TestProc
AS

SELECT IIF('hello' = 'hello','true','false')
GO

We can see the nullability goes away.

This is because the variables you are utilising in your procedure (@Zerobit and @Onebit) are NULLABLE by definition. The variables themselves are getting set at run-time, but the underlying metadata for the data type of the variable is still nullable.

There is also a possibility that the query optimiser needs to account for all scenarios, and it can't use the value of the variable at runtime for an all-use plan.

If we rewrite the query and do not set the value of the variables, we will see the same results (The column metadata for the return of the procedure is still NULLABLE):

CREATE PROCEDURE TestProc
AS

DECLARE @trueoutput VARCHAR(10)
DECLARE @falseoutput VARCHAR(10)

SELECT IIF('hello' = 'hello',@trueoutput,@falseoutput)
GO

My suggestion for the above case would be (as Mikael Eriksson says) to use constants - this is unless it's a cut down example and this isn't possible. The issue then would be the returned data type would always be nullable without being wrapped with ISNULL, CASE etc


Can I use `on delete set null` on a compound foreign key which contains a primary key column?

You can't use on delete set null if any of the foreign key columns don't accept null:

create schema stack;
set search_path=stack;
--
create table t1( foo integer
               , bar integer
               , primary key(foo,bar) );
--
create table t2( foo integer
               , baz integer
               , bar integer
               , primary key (foo,baz)
               , foreign key (foo,bar) references t1 on delete set null );
--
insert into t1 values (1,1);
insert into t2 values (1,1,1);
delete from t1;
/*
ERROR:  null value in column "foo" violates not-null constraint
DETAIL:  Failing row contains (null, 1, null).
CONTEXT:  SQL statement "UPDATE ONLY "postgres"."t2" SET "foo" = NULL, "bar" = NULL WHERE $1 OPERATOR(pg_catalog.=) "foo" AND $2 OPERATOR(pg_catalog.=) "bar""
*/
--
drop schema stack cascade;

And the same is true for on delete set default. I don't know of any simple way round this, and I'm not sure I agree that it is the most sensible behaviour, I think a better choice for on delete set null would be to set the nullable columns to null if there are both.


How to get latest 2 records of each group

With MySQL 8

SELECT sn, color, value
FROM (
  SELECT
    sn,
    color,
    value,
    DENSE_RANK() OVER (PARTITION BY color ORDER BY sn) AS r
  FROM table
) AS t
WHERE t.r <= 2;

Using MySQL < 8

You need to use their special variables.. something like this


When creating remote BLOB store is "RBSFilestreamFile" always the name of the file to be added to the FILEGROUP?

The filename and filegroup name are not fixed.

SharePoint will use the same name when creating the databases.  However there's nothing that would prevent someone from changing those names to something else if they desired.  I'd query for them from the database backup using RESTORE FILELISTONLY then create the RESTORE DATABASE Command based off that information.


Python MySQL prepared statements slower than regular queries

Much faster is to build a multi-row insert:

INSERT INTO t (a,b)
    VALUES
    (1,2),
    (11,23),
    (111,234), ...

On the MySQL side, this runs about 10 times as fast (for 100-1000 rows) than single-row inserts.


Is there a sql database engine that support "specific value duplicate restriction"?

Some DBMSes support indexes with a WHERE-condition

CREATE unique INDEX OnlyOneUnkownBrand
ON product (brand)
WHERE brand = 'unknown';


How to design a parent-child relationship that includes child priority/order

If I understand your question correctly, your existing "parent" table has a column that contains a string that represents the ID values from a "child" table, and those ID values are in a meaningful order. The question, then, is how to move those parent-child relationship representations out of a string with meaningful order, and into a more normalised database design.

If this is the goal, then I suggest your second design (DB#2), with a minor change, might work best for you.

In summary, a single new table can represent the relationships between child and parent records, with one column for each of those foreign keys. (You already have this in the design). The order of those relationships can be represented in an additional column. (You already have this in the design). The only change I would make is to remove the ID (first) column - there is no need for it.

If a child may only be represented once in relationship with its parent, then make your primary key across (idParent, idChild). If a child can be represented many times against its parent (ie. because it can occupy multiple places in the ordering of the relationships), then make the primary key across all three columns (idParent, idChild, priority).

Per your design, the parent table can drop its obj#3 column (ie. the one that currently contains all the strings).

I see no reason to introduce an extra table to represent the ordering / priorities.

This design allows for two levels in your hierarchy: parent and child. If you have a requirement for multiple levels (such as a person belongs to a department which belongs to an organisation), the question becomes more complex and there are some interesting articles out there on how to model such open-ended hierarchies. Otherwise, with the information presented, I recommend database #2 and, as mentioned, the first column is not required.


Stored Procedure Performance using Temporary Tables

To answer a question that arises from the comments, temporary tables in MySQL are owned by and only available to the client connection (session, thread) that created them, so there's not an isolation issue, but there is the potential issue of your connection having left something there with that name before, either with stale data or a similarly-named table with a different structure, either of which would be less than ideal.

The CREATE TEMPORARY TABLE IF NOT EXISTS statement creates the temporary table if there isn't one of the same name, but is a non-blocking no-op if the table is already there, which, as noted, is still bad.  The better alternative is probably to drop the temporary table at the top of the proc, if it's there, before creating it, so you always begin with a clean environment.  This is similarly a no-op if the temporary table isn't there:

   DROP TEMPORARY TABLE IF EXISTS BATCH_ITEMS_UOW;

Important: if you do not have an index on the STATUS column of the BATCH_ITEMS table, this will be one of the two major reasons this procedure is slower than it needs to be.  That's going to be essential for performance, either with this code or the existing code.

It would probably also be best to index the temporary table, to make life as easy as possible for the optimizer.  Note that the column declared must be the same as the name or alias of the column in the SELECT, otherwise MySQL assumes the SELECT is supposed to generate additional columns for the temporary table using the column names in the SELECT:

   CREATE TEMPORARY TABLE BATCH_ITEMS_UOW (
     ID INT NOT NULL PRIMARY KEY
   ) ENGINE=MEMORY AS (
    SELECT ID FROM BATCH_ITEMS
     WHERE STATUS = 'UNPROCESSED'
       AND BATCH_JOB_ID = batchId
     LIMIT size
   );

Then instead of an update that use a subquery, this should be a join, since those are generally optimized better in any version of MySQL.  This is the other likely reason this procedure may be slower than you expect.

UPDATE BATCH_ITEMS BI
  JOIN BATCH_ITEMS_UOW UOW ON UOW.ID = BI.ID
   SET BI.STATUS = 'PROCESSING';

Then return the result-set...

SELECT * FROM BATCH_ITEMS_UOW;

Optionally, you can drop the temporary table... it will be dropped automatically when your session disconnects, or the next time you run this procedure, since we're now explicitly dropping it at the start if you run this again in the same session.

DROP TEMPORARY TABLE BATCH_ITEMS_UOW;


How to set IN values on an update (SQL Server)?

Update apotable
   set price1 = 0,
       price2 = 0,
       price3 = 0
where po_number IN ('x', 'y', 'z');

Note that numbers should not be enclosed in single quotes 0 is a number, '0.00' is a character literal (aka "string").

There is no need to search the web for the syntax of a SQL statement.
That is all documented in the manual:
https://msdn.microsoft.com/en-us/library/ms177523.aspx


Index selection in MongoDB 3.0 for $or query

I asked this question on the Google group for Mongodb. See Dwight's explanation and work around.

https://groups.google.com/forum/?__hstc=21977188.535555756f91d668db0d32d8604f6233.1443211966594.1443211966594.1443211966594.1&__hssc=21977188.11.1443211966594&__hsfp=469890013#!topic/mongodb-user/iKtaqA5KrKY

Thanks!


Update using the value from the closest row

The following approach will only work as expected if we can safely assume that rows with the same housename will have the same houseno where houseno is populated.

As the first step, you need to get the list of all distinct housename/houseno pairs where houseno has a value. If my assumption above is correct, you will get a result set where all housename values are unique, and each will have a corresponding houseno – a kind of reference table:

SELECT DISTINCT
  housename,
  houseno
FROM
  YourTable
WHERE
  houseno <> ''

Use the above as a derived table to join it back to the original table in the UPDATE statement, so that you can populate the missing values from the derived table:

UPDATE
  YourTable AS t
  INNER JOIN
  (
    SELECT DISTINCT
      housename,
      houseno
    FROM
      YourTable
    WHERE
      houseno <> ''
  ) AS ref ON t.housename = ref.housename
SET
  t.houseno = ref.houseno
WHERE
  t.houseno = '' OR t.houseno IS NULL
;

Note that rather than "fixing" your table like this, it would be better to normalise it by creating an actual reference table with columns housename and houseno that you would then join back to your data table whenever you need to retrieve houseno. Consequently, the houseno column in the original table would no longer be needed.


Using sp_BlitzIndex - Diagnosed as "Index Hoarder: Addicted to nulls"

I think the script in question is sp_BlitzIndex.

The script lists tables that have more than 3 columns, and the number of non-nullable columns is 1 or 0. It doesn't mean that those tables are bad-- but if you see a lot of rows for this, it's just there to raise the question:

Is it valid for those columns to all really contain nulls?
When tables are created, are people being careful to set the right
properties?

If the answer to both of those questions is yes, then it's totally fine. The check is just there because it's worth thinking about. It's in there mostly just for starting conversations about data integrity and validity, that's all.

Thanks for using the script!


I didn't know a good answer to this myself, but thought it was a good question and that it would be a good feature to have. So I asked for ideas in Codership's Google group (here). You're right that there aren't any obvious status variables for this, but two methods were suggested:

Assuming you access the cluster through a DB proxy, then use a monitoring tool (Nagios, Zabbix, maybe PMM, maybe MONyog, ...) and log the availability of the cluster. Uptime is then the time since the last failure.
grep the error log on each of the cluster nodes to find the timestamp of the most recent bootstrap. This coincides with the timestamp for when the status variable wsrep_cluster_conf_id is equal to 1, and can be found in the error log with something like:

grep -e conf_id -e 'Quorum results' node1.err | grep '= 1,' -B 1

I suppose these two methods give a different perspective of 'uptime': The first method is more from the applications' point of view, and the second method is from the cluster's point of view. They may not always give the same answer since the cluster could be "up", but not necessarily always in a state that makes it accessible. Also, there could be network issues between the application and the DB proxy, and between the DB proxy and the cluster, which would make the cluster seem "down" from the applications' point of view, but in reality it is still "up".


mysql_select_db() very slow on a busy server

As you are saying that most of the tables are MyISAM, I would check IOs and locking.

You can verify you disk throughput with a tool like iotop.

http://www.manpagez.com/man/1/iotop/

MyISAM relies on the file system cache to store data blocks, so MySQL has to compete with all the other processes running on your OS for access to that cache.
The key_buffer only applies to index blocks.

http://dev.mysql.com/doc/refman/5.0/en/myisam-key-cache.html

If you notice that MySQL is generating a lot of IO activity, that may mean that your OS is starved for RAM in spite of the 16GB you say is available.

I ran into a similar problem with a Wordpress install lately that relied heavily on two 2GB MySIAM tables.

If your SELECT statements are trying to access data from these same tables that are being modified at the same time by (DML) INSERT, UPDATE or DELETE queries, that might explain why table_locks_waited is so high on your system. Those DML queries could be coming from a website, a cron job or some other process. If locking contention is the problem, you may want to consider converting the table engine to InnoDB for those tables that are causing the most locking. Tools like Jet Profiler and Innotop can help you to get that specific information.


Can I use xtrabackup to import a database without altering others?

What you need is partial backups.
innodb_file_per_table=ON is a prerequisite to make it work.

In your case procedure would be like following:

Take a partial backup from the production server

innobackupex --databases="mydatabase" /path/to/backup

On the destination server prepare the backup copy for export:

innobackupex --apply-log --export /path/to/backup

For each table repeat these steps:

create a new table with the same structure (get it from the production server with mysqldump -t production or SHOW CREATE TABLE t)

demo_1874> CREATE TABLE t (...) ENGINE=InnoDB;

Discard tablespace that InnoDB has

demo_1874> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE; created.

Copy t.* files from partial backup to /var/lib/mysql/demo_1874, chown mysql:mysql them, and import the tablespace from the partial backup

demo_1874> ALTER TABLE t IMPORT TABLESPACE;

Consult http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/partial_backups_innobackupex.html for more details.


Unfortunately the maintenance plans in SQL Server are quite limited in functionality and the option that you are looking for doesn't exist as far as I know.

As Shanky has mentioned, you would need to look at some other tool for this such as the excellent open source backup and maintenance solution from (Ola Hallengren).

This has the specific backup parameter that you are looking for @ChangeBackupType = 'Y'.  From the documentation

  DatabaseBackup checks differential_base_lsn in sys.master_files to determine whether a differential backup can be performed. If a differential backup is not possible, then the database is skipped by default. Alternatively, you can set ChangeBackupType to Y to have a full backup performed instead.


Can a database restore fail after it has begun?

Are you restoring to an existing instance of the DB? If so then space issues shouldn't be particularly relevant. At our place we run the following as job steps in restore jobs:

USE [master]
GO

ALTER DATABASE nameofDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

DECLARE @Kill nvarchar(1024) = ''

SELECT @Kill = @Kill+'KILL '+CAST(spid AS varchar(4))+'; '
FROM sys.sysprocesses s
JOIN sys.databases d ON s.dbid = d.database_id
WHERE name = 'nameofDB'

EXEC(@Kill)
GO

ALTER DATABASE nameofDB SET MULTI_USER;
GO

This will kill all system processes (spids) against your DB. As someone else mentioned ensure there is an agreement with users that this will occur, or that other system processes require it etc.

Hopefully these steps will add some resiliance.

NB: Assumes you run the DB in multi_user !


Every connection has a -h 127.0.0.1 or as you have a --protocol=TCP" on local computers to use round-Robin.

The problem is on linux computers mysql tries to open a socket connection, where you can' use Roud-Robin.

The same is true for Windows computers, but there mysql uses named pipes

so you have to force the connection to use tcp/ip, that works with an ipadress in -h or like you used forcing the protocol


How to check if Oracle DB process is running in Unix and where Oracle DB is installed

This depends on the flavor of *nix where you run on and whether the installation was customized or not. If something of Oracle was installed on your system, the directory /etc/oracle or /var/opt/oracle normally would have to exist. Normally they contain a file /etc/oraInst.loc or /var/opt/oracle/oraInst.log that points to the inventory that contains the central registry of the installations done on the host, if it has been done in a standard way.

If you found the oraInstloc you know at least that some installation has been taken place. The inventory contains the details about what was installed and where. Normally this directory is protected.

If the /etc/oratab or /var/opt/oracle/oratab file has been maintained, it contains a list of all instances running on your system, including the software locations. This is the file that is used by the oraenv utility that sets the minimal environment variables you need to be able to use that software for the specified ORACLE_SID.

The oratab, if maintained shows all defined ORACLE_SID's, also when they are not running.

But again, asking your dba might save a lot of time.


Getting PL/SQL: ORA-00942: table or view does not exist in plsql function but not from the command view

from sys user -->
grant select any dictionary to 'desired schema';


When is it beneficial to move duplicate data to a reference table?

When the referenced data exists independently from the referencing data. In this example, is it useful in the problem domain to know what the possible life states are even when there are no rows in persons.
When the values can only be drawn from a known, finite set. This is often referred to as "reference data". A foreign key constraint can enforce this.
When referential integrity must be maintained between two (or more) normalized entities. This will be for transactional data where the values will not be known in advance. For example where an application allows the addition of  new users at run-time, and has last_updated_userid on each table. Again a foreign key will enforce this.
When additional domain values are discovered that depend sole on the life_state. This is the usual key dependency normalization.
When meta data is required e.g. is_active for "soft deletes" instead of removing the value from the system entirely.

Although it is customary to create a surrogate key for the reference table it is not required. For the example given, the reference table could have a single seven-character column with three rows. The human-readable values would then appear in the referencing tables. This would obviate the need to join to the referenced table.


SQL db query: how to add a new field and assign values

Not the most elegant of solutions, but to answer your question, you could do this:

SELECT T1_ID AS ID, T1_NAME AS NAME, T1_ADD AS ADDRESS, 'ANIMAL' AS TYPE
FROM Table1
UNION
SELECT T2_ID AS ID, T2_NAME AS NAME, T2_ADD AS ADDRESS, 'HUMAN' AS TYPE
FROM Table2
UNION
SELECT T3_ID AS ID, T3_NAME AS NAME, T3_ADD AS ADDRESS, 'OBJECT' AS TYPE
FROM Table3


Delete and Recreate Indexes in MySQL to reduce table size for copying the db to a new server?

You asked

  Can I delete the index-file and recreate the indexes on the new server to reduce the copy and tar-time by 50%?

Yes, you can. You copy just the .MYD into the tar file. You will need a blank .MYI file.

SUGGESTION

Suppose you are moving mydata.mytable. You will have

/var/lib/mysql/mydata/mytable.frm
/var/lib/mysql/mydata/mytable.MYD
/var/lib/mysql/mydata/mytable.MYI

STEP 01 : Make Copy of Table Structure

CREATE DATABASE IF NOT EXISTS myjunk;
CREATE TABLE myjunk.mytable LIKE mydata.mytable;

STEP 02 : Copy Real Data and Blank Index File

COPY S1:/var/lib/mysql/mydata/mytable.frm ->  S2:/var/lib/mysql/mydata/mytable.frm
COPY S1:/var/lib/mysql/mydata/mytable.MYD ->  S2:/var/lib/mysql/mydata/mytable.MYD
COPY S1:/var/lib/mysql/myjunk/mytable.MYI ->  S2:/var/lib/mysql/mydata/mytable.MYI

STEP 03 : Rebuild Index File on Target Server

REPAIR TABLE mydata.mytable;

GIVE IT A TRY !!!


How do I configure PostgreSQL 8.3 to log bind parameters?

I'm not sure how to make those visible in 8.3 except with this:

log_statement = all

That's going to put a lot of data in to your logs though, so you may not want to keep it turned on for long.  Also make sure you have the process ID included in your log_line_prefix if you want to match everything up easily.


When I run mssql-cli, I get "No module named mssqlcli"

I tried to fix this by submitting a patch #234 (approved but not yet committed). You'll have to edit the file ~/.local/bin/mssql-cli and replace

python -m mssqlcli.main "$@"

With this line

( command -v python3 && python3 -m mssqlcli.main "$@" ) || python -m mssqlcli.main "$@"


A non-CDB is anything that isn't a CDB; that is, any database before 12c, or a 12c database created without the enable pluggable database clause. If you create a non-CDB it isn't Multitentant and is a single-instance standalone like a pre-12c database.

  Is non-CDB one single Database process with several databases or Is non-CDB several Database processes?

Neither, it's a single instance (set of processes) with a single database.

The definition you quoted refers to an aspect of non-Mulitenant architecture:

  stacked single-instance databases

So you have one or more Oracle homes (software installations), each with one or more independent databases running from that home. This was the only way to run multiple databases on the same server before 12c's container/pluggable model.

The problem with doing that is the database instances are completely independent and have no visibility of each other, so they are all competing for resources (CPU, memory, network) at operating system level, and you have to have enough resources for them all to be running at peak to avoid degraded performance, even if that is unlikely to happen often. Or don't have enough resources and one or more end up starved at some point. You don't want that happening in production, so it's generally considered a bad idea to do this in production environments.

This is also touched on in the 12c documentation:

  A typical response to the management problem is to place multiple databases on each server. The problem is that the multiple database instances do not share background processes, system and process memory, or Oracle metadata.

12c's Multitenant model gives you a single container database that manages the resources at operating system level and shares them with the pluggable databases as needed, potentially significantly reducing the total resources the O/S server needs, and hiding/simplifying the management of the plugged-in dtabases.

It's somewhat analogous to running virtual machines on a physical server - the physical server needs fewer actual CPUs and less memory than are allocated in total to all the VMs, and the VM management layer controls the balancing and contention of the resources depending on the actual needs and loads of all the VMs. The VMs themselves aren't really aware of that happening, they just use what they need; similarly pluggable databases aren't really aware that the container is managing their resources rather than the operating system.


PostgreSQL / pgAdminIII: is it possible to send command line commands via the UI

If by "command line" you mean the client program psql, then the answer is no. psql is just another client program like pgAdminIII.

If you have configured File -> Options -> Browswer -> Binary paths -> PG bin path in pgAdminIII, there is a shortcut to open psql:

pg_dump is another separate client application. You run it from the shell of your OS. You don't "send it to Postgres". But you can optionally call it from within pgAdmin. See Tools -> Backup...

All commands starting with the shell prompt $ in Jay Taylor's answer are also commands to run in the shell. Be sure to connect to the right DB.


In Oracle Database world, what you are describing sounds like dNFS-based cloning (http://www.oracle.com/technetwork/server-storage/hardware-solutions/o13-022-rapid-cloning-db-1919816.pdf [PDF], https://oracle-base.com/articles/11g/clonedb-11gr2).

Start with a read-only copy of all the data files, which are read by the clone instance.  Changes/writes are written to delta files ("copy on first write"), and the database looks to these delta files before looking at the read-only copies.  This provides full "copies" of the database content in remarkably little space.  The whole lot can be rolled back by shutting down the instance and removing the delta files.

I've never seen such a thing in MySQL.  It might be possible with fancy storage tricks, but I don't think it's a feature that the database supports.


How to create new users for restored databases?

The thing you're trying to fix is what's known as "Orphaned Users" - I blogged about this a while ago, at http://thelonedba.wordpress.com/2013/02/22/restoring-into-a-different-environment-security-problem-orphaned-users/

However, without seeing your exact error message, it's hard to help you with specifics.


Restructure create and delete events on rows into single table

No restructure is needed.  (Or I don't understand why there might need to be a restructure.)

SELECT  object,
        SUM(type = 'create') - SUM(type = 'delete') AS remaining_ct
    FROM tbl
    GROUP BY object;

This assumes that 'creates' occur before 'deletes'.


There are two type of timeouts.
One is when the client application (API) aborts the batch after a certain amount of time. Called "attention" in a SQL server trace. Default in many APIs is 30 seconds.
The other is when the client requests that SQL server abort the query with error 1222 after a certain time. SET LOCK_TIMEOUT. Default is indefinitely.
Yes, if a blocking lasts longer than any of above timeouts values, then you will get a timeout.
Do the user consider waiting for a long time (blocking) and perhaps even getting a timeout as bad performance. Yes, absolutely.


How to track only DDL statements executed in MySQL

Please look at the MySQL Documentation again

A record of metadata operations such as those just described are written to the file ddl_log.log, in the MySQL data directory. This is a binary file; it is not intended to be human-readable, and you should not attempt to modify it in any way.
ddl_log.log is not created until it is actually needed for recording metadata statements, so it is possible for this file not to be present on a MySQL server that is functioning in a completely normal manner.
There are no user-configurable server options or variables associated with this file.

I looked at the Server Variables and there is no ddl_log option.
You can count ALTER TABLE commands from status variables (Com_alter_table).
SUGGESTION
If you are using MySQL 5.6 and should you decide to bite the bullet and use mysqlbinlog and parse the output, you may need to setup mysqlbinlog on an external server, redirecting output to your own log file for DDLs. You will also have to use stop-never. here is what the MySQL Documentation says on stop-never:

This option is used with --read-from-remote-server. It tells mysqlbinlog to remain connected to the server. Otherwise mysqlbinlog exits when the last log file has been transferred from the server. stop-never implies --to-last-log, so only the first log file to transfer need be named on the command line.
--stop-never is commonly used with --raw to make a live binary log backup, but also can be used without --raw to maintain a continuous text display of log events as the server generates them.
This option was added in MySQL 5.6.0.


SSAS Modelling - many to many with one dimension

I would normalize the fact table into this form:

StudentId Level    Subject
34567     Major    Math
34567     Minor 1  French
34567     Minor 2  Biology
etc.

and add the level as a dimension.


SELECT DISTINCT ON subquery uses inefficient plan

Aaron,

In my recent work, I've been looking into some similar questions with PostgreSQL.  PostgreSQL is almost always pretty good at generating the right query plan, but it isn't always perfect.

Some simple suggestions would be to make sure to run an ANALYZE on your progresses table to make sure that you have updated statistics, but this isn't guaranteed to fix your problems!

For reasons that are probably too long-winded for this post, I've found some odd behaviors in the statistics gathering of ANALYZE and the query planner that may need to be resolved in the long-term.  In the short-term the trick is to rewrite your query to try and hack out the query plan you want.

Without having access to your data for testing, I'll make the following two possible suggestions.

1) Use ARRAY()

PostgreSQL treats arrays and sets of records differently in its query planner.  Sometimes you'll end up with an identical query plan.  In this case, as in many of my cases, you don't.

In your original query you had:

EXPLAIN SELECT "v_latest_progresses".* FROM "v_latest_progresses"
WHERE "v_latest_progresses"."user_id"
IN (SELECT "users"."id" FROM "users" WHERE "users"."company_id"=44);

As a first pass at trying to fix it, try

EXPLAIN SELECT "v_latest_progresses".* FROM "v_latest_progresses"
WHERE "v_latest_progresses"."user_id" =
ANY(ARRAY(SELECT "users"."id" FROM "users" WHERE "users"."company_id"=44));

Note the change of the subquery from IN to =ANY(ARRAY()).

2) Use CTEs

Another trick is to force separate optimizations, if my first suggestion doesn't work.  I know many people use this trick, because queries within a CTE are optimized and materialized separate from the main query.

EXPLAIN
WITH user_selection AS(
  SELECT "users"."id" FROM "users" WHERE "users"."company_id"=44
)
SELECT "v_latest_progresses".* FROM "v_latest_progresses"
WHERE "v_latest_progresses"."user_id" =
ANY(ARRAY(SELECT "id" FROM user_selection));

Essentially, by creating the CTE user_selection using the WITH clause, you are asking PostgreSQL to perform a separate optimization on the subquery

SELECT "users"."id" FROM "users" WHERE "users"."company_id"=44

and then materializing those results.  I then, once again use the =ANY(ARRAY()) expression to try to manually manipulate the plan.

In these cases, you probably can't trust just the results of EXPLAIN, because it already thought that it found the least costly solution.  Make sure to run an EXPLAIN (ANALYZE,BUFFERS)... to find out what it really costs in terms of time and page reads.


Move a MySQL 5.5 database to a server running MySQL 5.1

I have a script to move MySQL data from one version to another as long as it is MySQL 5.x

If you look at my old post MySQL upgrade 5.0.88 to latest, I handle this situation going to a higher version. With slight modifications, I can make this process go backwards.

STEP 01 : mysqldump all databases except mysql, information_schema, performance_schema

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

STEP 02 : dump the grants as pure SQL commands

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

STEP 03 load each file into a new MySQL 5.1 Instance

Login to MySQL as root and run

mysql> source Data.sql
mysql> source Grants.sql

Give it a Try !!!

CAVEAT #1

You cannot mysqldump everything and load it into a different version of MySQL because the mysql schema has columns in the mysql.user table specific to a version.

You cannot run mysql_upgrade to fix it because you are doing a downgrade.

I have mentioned splitting the grants from the data before

Oct 17, 2014 : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
Apr 11, 2013 : MySQL upgrade 5.0.88 to latest (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

CAVEAT #2

The solution I posted would be for Linux, but your database is on a Windows machine. So let me rephrase the solution with Windows in mind ...

STEP 01 : Get the name of every database in the Windows machine (except mysql, information_schema, performance_schema) and dump them. For example, if your databases as db1, db2 and db3, dump them to a text file like this:

C:\> mysqldump -uroot -ppassword --routines --triggers --database db1 db2 db3 > Data.sql

STEP 02 : Dump the Grants to a text file

C:\> mysql -uroot -ppassword -ANe"SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),''';') FROM mysql.user WHERE user<>''" > grant_cmds.sql
C:\> mysql -uroot -ppassword -AN < grant_cmds.sql > Grants.sql

STEP 03 : Edit the Grants.sql

Open Grants.sql in notepad and append a semicolon to every line

STEP 04 load each file into a new MySQL 5.1 Instance

Login to MySQL as root and run

mysql> source Data.sql
mysql> source Grants.sql

CAVEAT #3

I have techniques for using MySQL for Windows with mysqldump along with user-defined variables in the Windows shell : MySQLdump on specific db date


Change bucket count for Primary Key Nonclustered Hash on existing In-Memory table

As Dan Guzman mentioned in the comments, the alter isn't fully online (though should be quick enough). But another alternative that probably has the most minimal downtime is to create a copy of the table with a different name. Then use the sp_rename system stored procedure to rename the old table to a different name, and rename the new table to your old table's original name. This operation is very quick since it's basically just updating a reference in meta data on the server.


Schema binding seems to not be enforced for view doing temporal query

I think one thing in this case must be considered: your view is not referencing the temporal table directly, it is using a feature from SQL Server to get data that is stored on a different table (the history table).
The graph from the doc How do I query temporal data shows how you get the data from a temporal table:

If the picture shows the real data flow, your query doesn't directly "touch" the history table. Therefore, your view with the history data is not exactly dependent on the history table.
Of course the following is not the appropriate way of manipulate temporal data, but I think it helps to exemplify my point:
CREATE VIEW [vwTemporalTest_doingTemporalQuery2]
WITH SCHEMABINDING
    AS SELECT [Id], [Description], [period_start], [period_end]
       FROM [dbo].[TemporalTestHistory];
GO

This view is directly referencing the TemporalTestHistory table and when you try to alter the history table as you did before, it throws the error you expected. Also, this view appears on the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE.

Important remarks
Under the Important remarks of the Creating a system-versioned temporal table you can see that:

The history table must always be schema-aligned with the current or    temporal table, in terms of number of columns, column names, ordering
and data types.

And the Important remarks section of the Changing the schema of a system-versioned temporal table doc says:

Specified schema change is propagated to history table appropriately    (depending on type of change).

From those remarks it seems we're not supposed to do changes directly on the history table as a good practice.
About the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE not showing any info regarding that view I couldn't find any explanation. By the way, this is the conclusion I could get from tests and reading the docs I mentioned and I posted it as it might help to get to a more accurate answer (maybe it will be considered as a bug like you mentioned).


How to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans?

With regards to maintenance plans there is no CRUD implementation. As stated in BOL here:

  To create or manage Maintenance Plans, you must be a member of the sysadmin fixed server role.

You might be able to pull off setting up granular control by granting access to the stored procedures used to create maintenance plans (at the bottom of the article I linked above):

  sp_add_job

  sp_add_jobstep

  sp_add_schedule

  sp_attach_schedule

I would rethink the business need for this before going to that level. You have to understand that the objects used and permissions required for maintenance plans could change between major version, or even service packs and cumulative updates.

If there is a dire business need for this, I would likely create a dedicated account strictly for this and not grant the day-to-day user account the permissions. Then hope I was supporting Enterprise Editions only and use SQL Server Audit to track the usage of that account.

Though you also have to entrust the level of permissions to the individual you are providing it to, whether that means a training lesson prior to giving the account information away or even having your HR department come up with a security policy for privileged access. If I am not comfortable giving privileged access to someone you can be assured it will be voiced (by email) to management, letting them provide the finial decision (by email). I would then save off that little email as documentation on why I provided the access (for auditing purposes).


Sharding a MongoDB instance by a spatial key and a date

my question is to know if this is feasible

A shard key index cannot be multi key, text, or geospatial, so part of your theoretical approach is definitely infeasible. You also mention considering hashed sharding on a datetime field, which could provide better data distribution for otherwise monotonically increasing values like a timestamp. However, a tradeoff is that hashed sharding does not support range queries since adjacent source values will now have distributed shard key values.

  also if mongodb will be clever enough not to split according to the spatial key unless I manually ask for it?

Since a shard key cannot be an array or geospatial value this specific question doesn't really apply.

The default behaviour of MongoDB sharding is to allow shard key range splits (aka "chunk splits") to happen automatically so that the sharded cluster balancer will redistribute data between shards based on chunk imbalances. It is possible to adjust the default behaviour (for example, disabling autosplits for a cluster or balancing for a collection), but you should only do this with careful consideration. Micromanaging a sharded cluster can be counter-productive.


Can two transactions share the same SCN?

For single node Oracle RDBMS installations, an SCN is never duplicated.

With older versions of Oracle RAC, Oracle 9i for example, it was possible that two transactions, running on separate RAC nodes, could end up with the same SCN.  Duplicates happened only in rare situations where high transaction rates on the RAC nodes was coupled with a large value for MAX_COMMIT_PROPAGATION_DELAY.

In Oracle RAC installations of version 11gR2 and later, RAC nodes coordinate changes to the SCN via a broadcast mechanism by default, essentially eliminating the possibility of a duplicate SCN.


Cpu-Alert in SQL Server -2012 standard edition

Have you tried the DMV sys.dm_os_performance_counters ? Using this view you can get several perf. counters, where you can sample by intervals to have an idea how your system is going.

This is the documentation by Microsoft: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql

Here you can find a few examples how to use it:
http://www.sqlservercentral.com/articles/Performance+Tuning/71784/

And here is an example how to get your current CPU usage:

select perfCount.object_name
     , perfCount.counter_name
     , CASE
            WHEN perfBase.cntr_value = 0
            THEN 0
            ELSE (CAST(perfCount.cntr_value AS FLOAT) / perfBase.cntr_value) * 100
      END AS cntr_Value
FROM
    (select *
        from sys.dm_os_performance_counters
        where object_Name = 'SQLServer:Resource Pool Stats'
          and counter_name = 'CPU usage %'
     ) perfCount
inner join (select *
            from sys.dm_os_performance_counters
            where object_Name = 'SQLServer:Resource Pool Stats'
            and counter_name = 'CPU usage % base'
            ) perfBase
ON perfCount.Object_name = perfBase.object_name


PostgreSQL - insert/update violates foreign key constraints

There are a few problems with your tables.  I'll try to address the foreign keys first, since you question asked about them :)

But before that, we should realize that the two sets of tables (the first three you created and the second set, which you created after dropping the first set) are the same.  Of course, the definition of Table3 in your second attempt has syntax and logical errors, but the basic idea is:

CREATE TABLE table3 (
  "ID" bigint NOT NULL DEFAULT '0',
  "DataID" bigint DEFAULT NULL,
  "Address" numeric(20) DEFAULT NULL,
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),
   FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,
   FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);

This definition tell PostgreSQL roughly the following: "Create a table with four columns, one will be the primary key (PK), the others can be NULL.  If a new row is inserted, check DataID and Address: if they contain a non-NULL value (say 27856), then check Table1 for DataID˙and Table2 for Address.  If there is no such value in those tables, then return an error."  This last point which you've seen first:

ERROR: insert or update on table "Table3" violates foreign key constraint
    "Table3_DataID_fkey" DETAIL: Key (DataID)=(27856) is not present in table "Table1".

So simple: if there is no row in Table1 where DataID = 27856, then you can't insert that row into Table3.

If you need that row, you should first insert a row into Table1 with DataID = 27856, and only then try to insert into Table3.  If this seems to you not what you want, please describe in a few sentences what you want to achieve, and we can help with a good design.

And now about the other problems.

You define your PKs as

CREATE all_your_tables (
    first_column NOT NULL DEFAULT '0',
    [...]
    PRIMARY KEY ("ID"),

A primary key means that all the items in it are different from each other, that is, the values are UNIQUE.  If you give a static DEFAULT (like '0') to a UNIQUE column, you will experience bad surprises all the time.  This is what you got in your third error message.

Furthermore, '0' means a text string, but not a number (bigint or numeric in your case).  Use simply 0 instead (or don't use it at all, as I written above).

And a last point (I may be wrong here): in Table2, your Address field is set to numeric(20).  At the same time, it is the PK of the table.  The column name and the data type suggests that this address can change in the future.  If this is true, than it is a very bad choice for a PK.  Think about the following scenario: you have an address '1234567890454', which has a child in Table3 like

ID        DataID           Address             Data
123       3216547          1234567890454       654897564134569

Now that address happens to change to something other.  How do you make your child row in Table3 follow its parent to the new address?  (There are solutions for this, but can cause much confusion.)  If this is your case, add an ID column to your table, which will not contain any information from the real world, it will simply serve as an identification value (that is, ID) for an address.


You need the AUTO_INCRMENT default for your id field, so:

ALTER TABLE `payment_transactions_daily_facts`
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT FIRST;

Then, next time you run your INSERT, the system will create an auto incremental id for you automatically.


mysql - Access denied for user 'user'@'localhost' (using password: NO)

This isn't a database problem at all. The tipoff is the "using password: NO" in the error message. Ensure that you're sending a password in your authentication as follows:

Command line

The correct syntax for the CLI client is:

$ mysql -uroot -ptest

Or you can just use -p (with no value), which will prompt you for a password. Note that there's no space between the flag (-u/-p) and the value (myuser/mypassword).

Rails

In your database.yml file, you should have:

adapter: mysql
database: my_db
hostname: 127.0.0.1
username: root
password: test
socket: /tmp/mysql.sock
pool: 5
timeout: 5000

Running rake db:create should now properly create your database.

See: ruby – How do I set up the database.yml file in Rails?


Optimal way to store leaderboards by month

Why not just add a MonthOf and YearOf column (likely as integer types) to your Leaderboard table? Then you'll only need one table to store all the leaderboards. Whenever you need to show the Leaderboard for a particular month and year, you just add those fields as predicates in your WHERE clause of your query. E.g. WHERE MonthOf = 1 AND YearOf = 2021 to get the Leaderboard for month of January 2021.
As Vérace mentioned, knowing which database system and version you're using, your Leaderboard table's current structure, and some sample data, would be helpful. But in any case, the above generic solution should generally work, and you should ensure the MonthOf and YearOf fields are part of an index for optimal execution of your queries.


In what order does sqllogship.exe process groups of log files?

It appears to handles things in filename order

Yes and no. Yes the files are sorted in filename order which generally happens to be the same as the restore order, thus it looks like it goes in order of filename. No, because the files may be tested to see if they contain the log records for the next restore, which means their name could be slightly different and still process. Note that the filename sort is case insensitive and that filenames do matter.


It depends.

One must weigh the alternatives.  Here is a partial list:

Rows are INSERTed in an ordered list (the BTree that contains the data, and is ordered by the PK) according to the PK.  For a huge table and random PK (eg UUID), this means jumping around a lot, which leads to poor caching.
New rows with an AUTO_INCREMENT PK are inserted at the "end" of the table, thereby avoiding the above point.
Every secondary key has a copy of the PK.  So, if the PK is bulky, you are taking more space for the secondary keys.
Ditto for other tables that need to JOIN to this table (with or without FOREIGN KEYs).
A PK is necessarily UNIQUE.  Hence, DATETIME and TIMESTAMP make risky fields to put into a PK.  (What if two things happened in the same second?)
Will you be fetching records based on the column(s) of a multi-column key that could be the PK?  If so, you get to them faster because you don't have to go through a secondary key faster.
Consecutive records (according to all columns of the PK) are "clustered" next to each other.  This means that fetching multiple rows will need fewer blocks from disk.
Etc, etc.


Monitoring SQL Server 2012 slow queries/queries which are causing blocking

You can make use of the dmv's sys.dm_exec_query_stats and sys.dm_exec_requests to analyse the resource consuming queries along with their duration for what long:

Use the query below to capture the same:

-- Execute the query inside target database
 SELECT TOP 10
      qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
      qs.total_elapsed_time / 1000000.0 AS total_seconds,
      qs.execution_count,
      SUBSTRING (qt.text,qs.statement_start_offset/2,
      (CASE WHEN qs.statement_end_offset = -1
      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
      o.name AS object_name,
      DB_NAME(qt.dbid) AS database_name
FROM
      sys.dm_exec_query_stats qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
      LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE
      qt.dbid = DB_ID()
ORDER BY
      average_seconds DESC;

Moreover would suggest if you are using extended events, you can capture the same. For more on how to set up and use the same, please read this whitepaper from Jonathan Using SQL Server 2008 Extended Events which should get you going alon with here

Also, when the queries run , and what all process run at the background and to check if blocking is really out there along with any particular waits , would suggest you to use SP_WHOISACTIVE from How to Use sp_WhoIsActive to Find Slow SQL Server Queries

Also, if you prefer to use Profiler you can track and troubleshoot the same as explained in How to Identify Slow Running Queries with SQL Profiler


MongoDB replica set - no election after primary node failed

You are missing a node, either another data bearing node or an arbiter.

Here is why. A replica set needs a quorum > 50% of the original replica set members. Additionally, if a single node could decide to become primary, every network partitioning, every failing switch would result in a split brain situation, where two nodes assume they are primary.

Add an arbiter. They are cheap in terms of CPU, RAM and disk space, so you can run it on a small VM. Run it with

mongod --replSet shard0 --smallfiles --nojournal --noprealloc

which further decreases the need of space.

Do NOT run the arbiter on one of the other nodes - it would leverage it's purpose.


I can't tell if this involves Aurora, but here is something worth noting: There are occasions when InnoDB will leave lock lying around. How does this happen ?

About six years ago, I answer this question : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?. In that post I mentioned the following from the MySQL Documentation:

  When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

This is still in the MySQL 5.7 Docs on Deadlock Detection and Rollback
 (paragraph 3)

You stated in the question

  I looked at a couple of tables in information_schema (INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX) but there are 0 records.

In my answer to the 6-year-old question, I showed the query of this tabled used by a Percona Nagios Check plugin:

SELECT COALESCE(MAX(IF(p.command = 'Sleep', p.time, 0)), 0) AS idle_in_trx
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS b ON  b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS r ON  r.trx_id = w.requesting_trx_id
LEFT JOIN  INFORMATION_SCHEMA.PROCESSLIST       AS p ON  p.id     = b.trx_mysql_thread_id;

I tweaked that query to display the Process ID of the Blocking Transaction, the Process ID of the Requesting Transactions blocked and their respective values from INFORMATION_SCHEMA.PROCESSLIST:

SELECT COALESCE(MAX(IF(pb.command='Sleep',pb.time, 0)), 0) idle_in_trx,
pb.ID BLK_ID,pb.USER BLK_USER,pb.HOST BLK_HOST,pb.DB BLK_DB,
pb.COMMAND BLK_CMD,pb.TIME BLK_TIME,pb.STATE BLK_STATE,pb.INFO BLK_INFO,
pr.ID REQ_ID,pr.USER REQ_USER,pr.HOST REQ_HOST,pr.DB REQ_DB,
pr.COMMAND REQ_CMD,pr.TIME REQ_TIME,pr.STATE REQ_STATE,pr.INFO REQ_INFO,w.*
FROM       INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS b  ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX        AS r  ON r.trx_id = w.requesting_trx_id
LEFT JOIN  INFORMATION_SCHEMA.PROCESSLIST       AS pb ON pb.id    = b.trx_mysql_thread_id
LEFT JOIN  INFORMATION_SCHEMA.PROCESSLIST       AS pr ON pr.id    = r.trx_mysql_thread_id
\G

If you run this, you will see one of two things:

The Process ID causing this log jam of locks
Everything coming up NULL (No transactions are blocked). This indicates that the lock is just stuck in LA-LA-LAND (Aurora is at fault). In that event, reboot RDS.


Some troubleshooting:

Run the command (dictated by dumpcmd) from the command-line and see if there are any errors
Does the password have any special characters in it that could mess up in a shell
Try changing mysqldump to the full path, eg: /usr/local/bin/mysqldump
Check the return value from os.system() in your script

Changing to the full path appears to be what you were looking for.


PostgreSQL - tuning multiple clusters on server

You should probably cut max_connections by a lot.  I don't know why it would recommend such a high setting in the first place.  I'd also cut shared_buffers down to the default (128MB) or maybe lower, as PostgreSQL runs on top of the OS file cache and with two separate instances it would probably be best to let the OS decide how to use the available memory, both between instances and for other uses.

I'd leave effective_cache_size and effective_io_concurrency alone.  Those are short term issues, and postgresql makes no effort to partition them between connections even within an instance.  It is possible that two connections on separate instances want to make use of those "facilities" at the exact same time and interfere with each other, but it is also possible that two connections on the same instance would.  So the mere fact that they are being shared is not a reason to lower them.  These setting don't do much for transaction processing anyway, they are mostly important for large analytics-type queries.


LOG all SQL Server queries with their durations

What is your actual goal? Are you trying to track down a troublesome query, performance tune your whole environment, or is this just out of curiosity?
Ultimately, I wouldn't recommend trying to track and log all queries. I suspect you only really care about poorly performing queries which are an actual business impact.
On most production servers, you will have far more queries that either run quickly enough that even a 10-20% performance gain is barely noticeable, or are already optimized well enough to make any performance tuning poor use of your time.
So instead I'd suggest targeting your performance tuning to queries that actually need it.
In any case, you have a few options:
Query Store
If you are running SQL 2016 or higher, the Query Store is my preferred method. It is very low overhead and provides some neat methods to track query performance over time. Books Online has a good article on using it here. Note that it needs to be enabled per-database, and will only capture information once it has been enabled.
DMVs
sys.dm_exec_query_stats stores aggregated (by query plan) stats around query execution. This can be useful for a high level view of what queries are consuming the most resources on a server. You will not get individual execution statistics (unless it's only run once), so it's not really suitable for identifying "one off" performance issues with normally well behaved queries.
It doesn't include the statement text, but you can get this using the sys.dm_exec_sql_text DMF. As an example:
select top 100
    qt.text
    , qs.execution_count
    , qs.total_worker_time
    , qs.total_physical_reads
    , qs.total_logical_reads
    , qs.total_physical_writes
    , qs.total_logical_writes
    , qs.creation_time
    , qs.last_execution_time
from
    sys.dm_exec_query_stats as qs
cross apply
    sys.dm_exec_sql_text(qs.sql_handle) as qt
order by
    total_worker_time desc

Extended Events
If you are really set on capturing every single query, your best bet will be to use XEs. Understand that there will be a significant performance overhead if you go down this route (especially if you're capturing things like the callstack, or doing something crazy like filtering by sql text with a predicate), and it's not something I'd recommend doing by any means.
If you set something like this up, I would suggest being extremely careful and not allowing it to run without monitoring its impact.


Here are three simple methods to do it,

Returning bool: Just use a simple CASE statement.

SELECT name, CASE WHEN value = 'foo' THEN true ELSE false END
FROM table;

Returning bool compact: You can actually make it more terse, so long as value is NOT NULL. That CASE statement is essentially equal to

SELECT name, value = 'foo'
FROM table;

If value is ever null though you'll get null rather than false.
Returning text: If you insist on yes and no instead (I wouldn't do that), you can use strings instead of the special-made boolean type (there is no more compact syntax to write this).

SELECT name, CASE WHEN value = 'foo' THEN 'yes' ELSE 'no' END
FROM table;


I think this might be a problem related to service broker messages - please take a look at this link

https://stackoverflow.com/questions/5172686/sql-server-database-file-not-being-truncated


Let us address first normal form and second normal form distinctly.
First Normal Form (1NF)
You cannot say a table is or is not in 1NF because no two rows contain repeating information.  By repeating information you may be thinking that BOOK doesn't look like this:
BOOK { BookTitle, AuthorName1, AuthorName2, AuthorName3, BookType, ListPrice, AuthorAffil, BookPublisher  }

This really isn't the definition of 1NF.  Instead, BOOK is in 1NF only if
each column in every row will have a single value of whatever type, no matter how arbitrarily complex, that is defined for that column.  While adding multiple AuthorNames columns may be bad design, it does not violate 1NF as each contains the name of a single author.  Now what would violate 1NF is if we had an AuthorNames column and defined it to contain a comma separated list of author names.  In that case we know absolutely BOOK is not in 1NF.
Its also possible we have designed the table variable to be in 1NF, assuming only a single author name would ever be placed into the AuthorName column, but our users start putting in comma separated lists of authors.  Now the resulting table is not in 1NF even though we meant it to be.  In both these cases the table isn't even a relational table anymore because it no longer follows the discipline necessary to gain the properties of a relation.
Secondly, you cannot say BOOK is or is not in 1NF without the presence of at least one candidate key!  As stated right now, BOOK has no candidate keys and thus the same value is allowed to be entered for every column in two or more rows.  This would result in duplicate rows and a table with duplicate rows is not a relational table!
Second Normal Form (2NF)
It appears from looking at the attempt to decompose BOOK a candidate key of BookTitle,AuthorName was assumed.  Even if this assumption were correct, it is not correct to say the original table is now in 2NF.  Instead, of the 3 tables resulting from the decomposition, each must now be evaluated with respect to the functional dependencies.  R2 is in 5NF as its single non key column is fully functional dependent on the key and that key is the only candidate key and it is a single column.  R3 is in 5NF as its only non key column (the assumed "extras") are also fully functionally dependent on the single candidate key and there are no additional candidate keys.  It is only R1 that is now in 2NF but not 3NF as it has a non key column, ListPrice, that is functionally dependent on another non key column, BookType, and thus forms a transitive dependency.
The assumption of a single candidate key of BookTitle,AuthorName may also not be correct!  Perhaps in this particular book world every book of interest has only one author.  If that were the case then the creation of R3 is incorrect with respect to the actual functional dependencies.  Thus it is vital to clarify all the candidate keys and all the functional dependencies, join dependencies, and multi-value dependencies, with the subject matter experts and to never assume them.  Otherwise the wrong design results.  Assuming many authors per book when really there can be only one per book results in an R3 that allows the FD of BookTitle --> AuthorName to be violated.  Assumming one author per book when really there can be many per book results in a table similar to the original BOOK where users find they only have room for one author name, not the many they need, and they find their only choice is to enter sets of author names, delimited by something like commas or pipes.
References
Fabian Pascal's Practical Database Foundation Series and CJ Date's Relational Theory for Computer Professionals are excellent references for understanding what makes a table a relational table and the fundamentals of normalization.  It is from these sources that all the information in this answer was derived.


How to update primary key table based on foreign key with ON DUPLICATE KEY UPDATE

Read the comments in the manual on this page https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html  The technique involves id=LAST_INSERT_ID(id) in the UPDATE clause.

Also near the end of the section on the function: https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id


Update using PK from a lookup table, should I use the PK directly or select it using the associated lookup value? - best practice?

This is a little close to an opinionated question, but in general, I think you'll find option a to be the mostly widely used one from the perspective of the database layer. For the purpose of this answer, I'm using the term enum as synonymous with lookup table, for all intents and purposes.
Enums are inherently meant to numerically give meaning to a concept with the added benefit of being able to verbalize that meaning with a name / string value. But it should be a rare event that the meaning of a specific instance of an enum changes from the original meaning for that numerical representation (in fact, if it did, it would likely logically break any existing references to that enum's numerical value). With that in mind, they shouldn't exactly be considered the same as magic numbers (despite being rather close), and it is the string value / name that is more flexible to change.
Should you use option b, you'd run into two issues:

There's a performance difference (though likely negligible in this case) by doing a second lookup on the status_type table as opposed to your UPDATE statement in option a.

If the name / string value of your enum were to change (remember this is very well more possible than the underlying meaning of the instance of that enum's numerical value to change) then your stored procedure would now be broken.

I would go with option a all day.


Make "NOT NULL" default in Postgresql 9.2

YOU have to define NOT NULL when creating a table as described in documentation :

NOT NULL ==>
The column is not allowed to contain null values.

Examples :

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

Define two NOT NULL column constraints on the table distributors, one of which is explicitly given a name:

CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);


Oracle. How to copy BLOB, LONG RAW and BFILE column between tables

What Oracle says about your error?

  You try to select distinct values from a long column, create a table
  using as select with a long column, insert into a table by selecting
  the long column from another table and an ORA-00997: illegal use of
  LONG datatype ... occurs

  It is a restriction of the long columns that they cannot be used with
  a DISTINCT operator and cannot be referenced when creating a table
  with query  (CREATE TABLE...AS SELECT...) or when inserting into a
  table(or view) with a query (INSERT INTO ... SELECT...)

And what Oracle recommends?

  Oracle strongly recommends that you convert LONG RAW columns to binary
  LOB(BLOB) columns.

Here is how simulated your problem and prepared the solution

 SQL> create table mytable(id number (9,0), c1 long raw, c2 varchar2(256), c3 blob, c4 bfile);

Table created.

SQL> desc mytable
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                         NUMBER(9)
 C1                         LONG RAW
 C2                         VARCHAR2(256)
 C3                         BLOB
 C4                         BINARY FILE LOB

SQL> create table this_other_table as select * from mytable;
create table this_other_table as select * from mytable
                                        *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

SQL> create table this_other_table as select to_lob(c1) c1, c2,c3,c4 from mytable;

Table created.

SQL>


MS Access / MySQL: unlock locked Records

I finally could fix my problem by adding a field named TIMESTAMP of type timestamp and default value CURRENT_TIMESTAMP and attribute = on update CURRENT_TIMESTAMP.
Then i updated all records and set TIMESTAMP=NOW().

For now i did not get any error again.


How to display all the dates between multiple two dates in a table?

The easiest way is to have a calendar table, defined in the following way:

CREATE TABLE calendar
(
    a_day date PRIMARY KEY
) ;

... and filled with all the relevant dates (i.e.: all days from 1990-1-1 to 2100-12-31). For the sake of simplicity, we will fill it only with year 2013:

INSERT INTO
     calendar (a_day)
VALUES
    ('2013-01-01'),
    ('2013-01-02'),
    ('2013-01-03'),
    ('2013-01-04'),
    ('2013-01-05'),
    -- everything up to
    ('2013-12-31') ;

At this point, you can just have a JOIN with the two tables; with the join condition not being an equality, but a range condition:

SELECT
     t.id, c.a_day
FROM
     t
     JOIN calendar c ON c.a_day BETWEEN t.start_date AND t.end_date
ORDER BY
     t.id, c.a_day ;

... and get

id | a_day
-: | :---------
 1 | 2013-01-14
 1 | 2013-01-15
 1 | 2013-01-16
 1 | 2013-01-17
 1 | 2013-01-18
 2 | 2013-02-01
 2 | 2013-02-02
 2 | 2013-02-03
 2 | 2013-02-04

You can see all the setup at dbfiddle here


LAST_DDL_TIME Not Changing after compilation of package body

You might have looked for different object types. It shows the last DDL time for both package and package body.

I have a package/body EMP_ADMIN.

SQL> select object_type, last_ddl_time from  all_objects where object_name='EMP_ADMIN';

OBJECT_TYPE             LAST_DDL_TIME
----------------------- -----------------
PACKAGE                 05-12-17 22:49:50
PACKAGE BODY            05-12-17 22:52:34

SQL> alter package emp_admin compile body;

Package body altered.

SQL> select object_type, last_ddl_time from  all_objects where object_name='EMP_ADMIN';

OBJECT_TYPE             LAST_DDL_TIME
----------------------- -----------------
PACKAGE                 05-12-17 22:49:50
PACKAGE BODY            05-12-17 22:53:19

Look at the difference for DDL time for Package Body not for the Package.

And let's compile the package.

SQL> alter package EMP_ADMIN compile;

Package altered.

SQL>  select object_type, last_ddl_time from  all_objects where object_name='EMP_ADMIN';

OBJECT_TYPE             LAST_DDL_TIME
----------------------- -----------------
PACKAGE                 05-12-17 22:53:44
PACKAGE BODY            05-12-17 22:53:44

The compilation of the packages changes DDL time for both Package and Body.


I have resolved the issue, just thought to sharing in case it helps other in future in case face similar issue.

This is something have to do with apparmor and you don't qualify proper permission,

I have only qualify following in /etc/apparmor.d/usr.sbin.mysqld

/home/ebsbkdrive/mysql_repl_data/* rw

but was lacking read permission on folder itself

/home/ebsbkdrive/mysql_repl_data/ r

more precise first folder read itself followed by read-write within,

/home/ebsbkdrive/mysql_repl_data/ r
/home/ebsbkdrive/mysql_repl_data/* rw

I came to know this when I redirect logs to default location then it worked and also by closely looking how permission sets in armor file for default /var/log/mysql path.


The backup or restore was aborted - what could it be?

Most likely you have defined a command timeout when you connected to your server. Perhaps without thinking about it. Anyhow, the client (SSMS) will cancel the query after the specified amount of time and return that weird looking error of -2 (which wasn't thrown by the server, it is a client generates message). I tested this by connecting with the "Execution time-out" of 1 as in the picture below and then executed the SQL below.

SELECT 'before'
WAITFOR DELAY '00:00:05'
SELECT 'after'

Zero (0) means indefinitely.


Only first column in ORDER BY clause is sorted

You are ordering rows of data, not each column separately.

The row (10, 1) comes before the row  (1, 10)  because of your ORDER BY clause.

The Value2 only comes into play when there is a tie on the first column.

As further explained by Hellion, as far as the database is concerned, the pair (10, 1) is an indivisible unit: it's not two values, it's one set (that happens to contain two values). You can't just break the set into pieces and swap the pieces into different sets willy-nilly. As I said (and as Darko shows in another answer), the ORDER BY clause sorts by the first specified column (Value1), and then if there is more than one row with the same number in Value1, it sorts that sub-set of rows by Value2.

As another illustration, you can also consider the example suggested by A C:

  See also the way the dictionary sorts words: AA, AB, AC, BA, BB, BC, CA, CB, CC... Now replace the first letter with your first column value (even if it's more than one digit) and the second letter with the second column value and there you go - same principle. (Yes, I sorted ASCending for clarity -- DESCending sorted dictionaries are hard to find.)


What is the meaning of `(ORDER BY x RANGE BETWEEN n PRECEDING...)` if x is a date?

Firstly, the expression values current_date - 1 would only be valid if Oracle compatibility mode were in effect -- it mimics the Oracle's datetime arithmetic where the default interval is expressed in (potentially fractional) days.

I think that regardless of Oracle compatibility, the range bounds should be comparable as integers, and comparing DATE values with integers might produce unexpected results. If you convert your DATEs to the number of days since some past moment you can use integer comparisons. You could use JULIAN_DAY(), for example:

select d, avg(x) over (order by julian_day(d)
                   range between 30 preceding
                             and current row)
from test
order by d

which produces the result you expect:

D          2
---------- ---------------------------------
01/01/2016   10.0000000000000000000000000000
01/07/2016   15.0000000000000000000000000000
01/12/2016   20.0000000000000000000000000000

  3 record(s) selected.

In the first fixpak of 10.5 it was allowed to use range over dates, but the results where unpredictable. In recent fixpaks this is no longer allowed, so much of the confusion in the question could have been avoided by using a recent fixpak.


Is there a way to skip errors in procedures and resuming procedure

You can use DECLARE CONTINUE HANDLER FOR SQLSTATE to ignore the key violation by declaring an empty block as an exception handler, as follows:

DELIMITER $$

CREATE PROCEDURE `injatest`.`LoadData` ()
BEGIN

    DECLARE x INT;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END;
    SET x = 1;
    WHILE x <= 14400 DO
        INSERT INTO junc_question_course_iq(q_id,iq_id,ct_id) VALUES
        (CEIL(RAND()*1000), CEIL(RAND()*48), CEIL(RAND()*10));
        SET x = x + 1;
    END WHILE;

END $$

DELIMITER ;


Open SQL Server Configuration Manager (Start > Programs > Microsoft SQL Server 2012 > Configuration Tools). Look at the SQL Server Services node; you should see something like this:

If that right pane is empty, or if you can't find Configuration Manager installed at all, then you don't have SQL Server installed. Go download the latest version (SQL Server 2014) from here:

http://www.microsoft.com/en-us/download/details.aspx?id=42299

You want ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe, unless you're on a 32-bit system for some reason, then you want ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe.

Download that, and try the setup again. You may not be able to name this instance SQLEXPRESS; that's okay, some people have many instances installed and they can't all have the same name.


This is because the tables in either schema are created by a different role (I assume read_write_create_user) than the one the default privileges were defined for (which is you, or the role that run the above set of statements).

You'll find a bit more explanation in my older answer.


Linked servers login failure: Impersonate works, Username/password fails

user/password option it fails, even though the remote user is the same
  as the local user being impersonated

You can't possibly impersonate a SQL login, and a linked server user/password option only allows for SQL logins. Sounds like you are attempting to pass in a Windows user/password credential, something that is not supported.


This sounds a lot like the Automatic tuning features of Azure SQL Database.  In particular, one of the options is:

  CREATE INDEX - Identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved.

You can view these settings here:

You mentioned that you are on SQL Server 2017, though - so this doesn't apply to you per se.

Is this a Managed Instance perhaps?  The docs currently state that automatic index management is not available in MI, but that may have changed from what I hear.

Since you are on an Azure VM, this won't be automatic index tuning.  You're best bet is to try and capture more information about this process by running the following sp_WhoIsActive command while this is occurring (something that was suggested in a similar Q&A from several months ago):

EXEC sp_WhoIsActive @show_system_spids = 1;

In your particular case, it seems index creation was being rolled forward by the database startup/crash recovery process. You would have seen the database in a non-online state, and recovery progress messages in the error log.


Is it possible to have 2 or more databases active, and synchronized between them?

SQL Server scales up not out
HA/DR is separate to scaling
Replication is not designed to publish entire databases
Replication or mirroring add an overhead that defeats your scaling out
Merge replication will have conflicts at some point

Personally, I'd buy a bigger server, go Enterprise x64 edition, and wait for SQL Server 2012 HADRON


Automatically generate database creation script with default values for SIZE and FILEGROWTH

One method to modify SQL scripts is with the Microsoft.SqlServer.TransactSql.ScriptDom. Below is a PowerShell script example that changes the file sizes using a TSqlFragmentVisitor implemented in C# that you can extend for your needs. This same technique may be used to change database names, file paths, size, etc.

Note that if you are using the PowerShell ISE for script development, you'll need to close and re-open the PowerShell ISE after making changes to the C# code in order to reload the .NET app domain.

# C-Sharp TSqlFragmentVisitor visitor class for ExplicitVisit(SizeFileDeclarationOption node)
Add-Type @"
    using System;
    using Microsoft.SqlServer.TransactSql.ScriptDom;
    using System.Collections.Generic;

    public class CreateDatabaseVisitor : TSqlFragmentVisitor
    {

        public Action<CreateDatabaseVisitor, TSqlFragment> Visited;

        public override void ExplicitVisit(SizeFileDeclarationOption node)
        {
            onVisit(node);
        }

        public override void ExplicitVisit(FileGrowthFileDeclarationOption node)
        {
            onVisit(node);
        }

        void onVisit(TSqlFragment node)
        {
            if(Visited != null)
            {
                Visited(this, node);
                node.AcceptChildren(this);
            }
        }

    }
"@ -ReferencedAssemblies "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll"

# create new class instance
$visitor = New-Object CreateDatabaseVisitor

# callback method for each visit
$visitor.Visited = {
    param($obj, $node)
    # Write-host "Visited node type: $($node.GetType())"

    # change SizeFileDeclarationOption as desired
    if($node.GetType().Name -eq "SizeFileDeclarationOption") {
        $newSize = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.IntegerLiteral
        $newSize.Value = 100
        $node.Size = $newSize
        $Node.Units = [Microsoft.SqlServer.TransactSql.ScriptDom.MemoryUnit]::MB
    }

    if($node.GetType().Name -eq "FileGrowthFileDeclarationOption") {
        $newSize = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.IntegerLiteral
        $newSize.Value = 10
        $node.GrowthIncrement = $newSize
        $Node.Units = [Microsoft.SqlServer.TransactSql.ScriptDom.MemoryUnit]::MB
    }

}

$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql120Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
$originalScript = [System.IO.File]::ReadAllText("C:\temp\CreateDatabase.sql")
$scriptReader = New-Object System.IO.StringReader($originalScript)

$frament = $parser.Parse($scriptReader, [ref]$parseErrors)
if($parseErrors.Count -gt 0)
{
    throw "$($parseErrors.Count) parsing errors. First error is: $($parseErrors[0].Message)"
}

$frament.Accept($visitor)

Write-Host "Original Script:"
Write-Host $originalScript

#generate new script
$options = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.SqlScriptGeneratorOptions
$options.SqlVersion = [Microsoft.SqlServer.TransactSql.ScriptDom.SqlVersion]::Sql120
$options.IncludeSemicolons = $true
$generator = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.Sql120ScriptGenerator($options)
[String]$newScript = ""
$generator.GenerateScript($frament, [ref]$newScript)

Write-Host "Modified Script:"
Write-Host $newScript

Example output:

Original Script:
CREATE DATABASE [YourDatabase]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'YourDatabase', FILENAME = N'D:\SqlDataFiles\YourDatabase.mdf' , SIZE = 15360 , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ),
 FILEGROUP [YourDatabase]  DEFAULT
( NAME = N'Data', FILENAME = N'D:\SqlDataFiles\YourDatabase_Data.mdf' , SIZE = 78561280 KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
 LOG ON
( NAME = N'YourDatabase_Log', FILENAME = N'D:\SqlLogFiles\YourDatabase.ldf' , SIZE = 4308416KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )
GO

ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 120
GO

Modified Script:
CREATE DATABASE [YourDatabase] CONTAINMENT = NONE
    ON
    PRIMARY(NAME = N'YourDatabase', FILENAME = N'D:\SqlDataFiles\YourDatabase.mdf', SIZE = 100 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10 MB),
    FILEGROUP [YourDatabase] DEFAULT(NAME = N'Data', FILENAME = N'D:\SqlDataFiles\YourDatabase_Data.mdf', SIZE = 100 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10 MB)
    LOG ON (NAME = N'YourDatabase_Log', FILENAME = N'D:\SqlLogFiles\YourDatabase.ldf', SIZE = 100 MB, MAXSIZE = 2048 GB, FILEGROWTH = 10 MB);

GO
ALTER DATABASE [YourDatabase]
    SET COMPATIBILITY_LEVEL = 120;


How can I select a field value in PostgreSQL based to the date computation within the table?

Sort the result (descending) by the value of end_date - now() and limit to only one row:

SELECT dollar_value, end_date - now()
from dollar_rate
order by end_date - now() desc
limit 1;

The computation end_date - now() is actually unnecessary in this case, you can simplify this to:

SELECT dollar_value, end_date - now()
from dollar_rate
order by end_date desc
limit 1;


The column that you have is a VARCHAR so the ordering applied by the RDBMS will be alphabetical instead of numeric.

What is the smallest possible unit of storage for a HDD these days? GB? One way to solve your problem would be to create another column that transforms the storage into GB (or some other unit). Make sure that this column is integer or numeric and not a VARCHAR. Using your example data and assuming 1000 GB per TB:

'1GB', 1
'500GB', 500
'1TB', 1000
'100TB', 100000

Filtering on the second column is a much more straightforward way to get the results that you want.

If you don't want to create a new column, it would be possible, but messy, to do that conversion within the SQL query.


Basic question: Simplest way to share company databases

It's not really how SQL Server works and I think you're going to see some underwhelming real life implementations of the data distribution process.

Many data publishers (post offices, automotive and parts suppliers) will publish a set data format document along with full monthly data extracts usually in a CSV or similar text format followed by ongoing updates-only extracts. The duty of pulling updates over SFTP, creating a database schema, loading it, and updating it becomes the customer's problem.
Usually a customer will then allocate a unique ID to each table which they'll use for their own referential integrity and to seperate it from the key used by the provider; as providers often delete or reallocate records (even though we know they shouldn't), and you can't have a record of a part sale disappear from someone's invoice just because that part is no longer supplied or something.

Some inter (or intra) company transfers are done literally as entire database backups. In this case you could have a weekly full backup and daily differential backups transferred over SFTP and restored on each client. This requires an outage window on your side to do each restore though.

There are other options. SQL Server Replication (either snapshots, but maybe not daily snapshots because these can't be incremental, or transactional which is an ongoing stream of changes) but it's mostly avoided between companies because they don't want to expose SQL Server to the internet; and it fails too often and requires too much knowledge to troubleshoot and keep going on the client side.

Some data publishers use APIs but only when you're sending a request off; i.e. send a part number get back a part record. It's not usually used to publish complete data sets. And it requires programmers, there's no native SQL interface for it anymore.

I think 1 is the standard way to go. If they want to go the extra mile they can provide a database schema and routines to do everything (pull an initial data stream, then apply changes every day). It's not difficult but it's not trivial either.
2 is the easiest by comparison but also the ugliest.
They can do something in between. Provide a full backup, and then BCP export only new records each day, and provide a script to load these new records into the database. But records do change and get deleted, despite best laid plans, and then those become problems to deal with in this way.
As for your comment on not using SQL Server currently, the free edition (Express) can hold 10GB of data, which is really quite a lot when you get down to it.


Oracle doesn't pre-page the SGA by default, meaning that when you start the DB instance, it will not initially grab all the memory until it needs it.

See http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams201.htm#REFRN10174.

I'd try this:

Set memory_target = memory_max_target = 24GB.
Get rid of sga_max_size, sga_target, pga_aggregate_target in your init parameters, unless specifically needed by some crazy vendor app (in other words, let 11g AMM do its thing instead).
Also clear the db_cache_size from your pfile.  You were basically limiting your cache to 5.4 GB.
Bounce the instance.
Beat it up with workload for a few hours/days (SQL queries, gather stats, rebuild indexes, RMAN backups, expdp).
Check in Task Manager for memory utilization.


The upgrade advisor will work, but you need to make sure to analyze a trace file of activity.  Just pointing it to the database and running it will only analyze the TSQL code stored in the database and not any TSQL code within your application.

In addition to the upgrade advisor, you can run a Profiler trace to capture the events: "Depreciation Final Support" and "Depreciation Announcement".  Both will identify code that will need to be evaluated because of syntax or reference to objects that don't exist in new version.

Check out Books Online for a decription of each of those events: Depreciation Event Category.


MySQL Backup with innobackupex error: Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374

should be fixed in version 2.4.7 and version 2.3.8 https://bugs.launchpad.net/percona-xtrabackup/+bug/1651978, if you can still reproduce using the latest version please update the bug report


Adding subpartitions to existing partitioned table

A new subpartition can be added to the existing partitioned table using the following command:

ALTER TABLE PART_TEST
modify partition OCT19
add subpartition OCT19AXCS
values ('AXCS');


If you have not more than 2 values for domain, this will help:

SELECT [DOMAIN], IIF(First([Q32])<>Last([Q32]), First([Q32])+' '+Last([Q32]),First(Q32)) AS _Q32
FROM [table]
GROUP BY [DOMAIN];


pgAdmin 4: How to establish a connection through an SSH tunnel/interface in Windows

So in this step of that PuTTY SSH tunnel tutorial:

I used instead 5432 as the Source port and my.tunnel.domain:5432 as the Destination.

Then in the connection details:

In the Host Name I placed the server to connect to (the one with the DB: my.db.domain), but the login credentials relate to the tunnel's my.tunnel.domain (e.g. Connection > Data has the tunnel's domain user and Connection > SSH > Auth has the tunnel's domain private key).

I opened this connection (it opens up a window with a terminal, if successful) and finally in the pgAdmin 4 (v2.1) Create Server... window, I just added localhost as the Host name/address and 5432 as the Port, with the credentials relating to the my.db.domain:

The DB connection is established and works normally if the PuTTY window with the terminal is active.

Another and more verbose tutorial on how to set a Putty SSH tunnel can be found here.


Saving SQL Server backup file in multiple parts

Normal backup

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE DBNAME
TO DISK = 'D:\DBNAME.bak'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

to split the size into 3 use the following code, it will split the size of 3 gb into 1 GB each file
you can add more files or less depending on the size you want

SET STATISTICS IO ON

SET STATISTICS TIME ON

BACKUP DATABASE DBNAME TO

DISK = 'D:\DBBackups\ReportServer\DBNAME _Split1.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split2.bak'

,DISK = 'D:\DBBackups\ReportServer\DBNAME _Split3.bak'

SET STATISTICS IO OFF

SET STATISTICS TIME OFF

for more details

http://beyondrelational.com/modules/2/blogs/88/posts/10153/sql-server-split-database-full-backup-to-multiple-files.aspx


MySQL SELECT and SORT performance - v. large table

As mentioned in comments (it is really answer for original question) - using function in WHERE - prevent any indexes using.

First of all, You can check - how many of Your queries use form

SELECT * FROM

this is really bad practice with applications, exactly because - if You add new column, some of Your code could be broken. For example:

INSERT into table2 SELECT * FROM table1

If You can fix this (or You not have problems code), You can:

convert IncorporationDate to Date format and use this column
or easy add 1 more column with proper Date type and use it in queries

But first - You must be sure, all other code will not be broken by new column.

Then You can use one of the tools from answer for Your other question:

Talend - http://www.talend.com
StreamSets - http://www.streamsets.com
Apache NiFi - https://nifi.apache.org,
https://hortonworks.com/apache/nifi/

now much more similar tools present on market, many of them free in parts cover 99% of life cases.
And ETL it more often solution rather than work with build functions only.

Clean
Transform
Load

For answer about next potential question - what with speed?
we load tables from 4M to 40M between instances - MySQL, MS SQL, Redshift
For "small" we use direct insert from ETL tools, for Big - prepare clean file for load, than direct load (Redshift from S3)

Just add live example - "how it could work"

One of Our scripts - run series of queries with search target records by substring from 2 columns + other parameters.
Changing the structure also not possible by the same reasons - a lot of place where need to make changes and no free developers resources. 2 table 3M rows per month.

Original time - 7.5 Hours
Modified process - prepare with Talend tables for past month with all optimised structure.
modification only this script - for exclude substrings from SELECT query, do not touch any other in system. SELECT from prepared tables, write result to same as in original.

Prepare - 5m, script execution - 12m, total time of whole process - 17minutes.


Automating the creation of postgres database

Just run the script with autocommit turned on (the default in psql) and use an explicit transaction block for those statements that should run in a single transaction:
DROP DATABASE IF EXISTS ????

DROP TABLESPACE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????

begin;
  ALTER DEFAULT privileges IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM ????;
  ALTER DEFAULT privileges IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM ????;

  DROP USER IF EXISTS ????;
  DROP USER IF EXISTS ????;
  DROP USER IF EXISTS ????;

  CREATE USER ???? PASSWORD '????';
  CREATE USER ???? PASSWORD '????';
  CREATE USER ???? PASSWORD '????';
commit;

CREATE TABLESPACE fastspace OWNER ???? LOCATION 'c:/????/????';
CREATE TABLESPACE slowspace OWNER ???? LOCATION 'd:/????/????';

DROP DATABASE ????;

CREATE DATABASE ????
  WITH OWNER = ????
       ENCODING = 'UTF8'
       TABLESPACE = fastspace
       LC_COLLATE = 'English_United Kingdom.1252'
       LC_CTYPE = 'English_United Kingdom.1252'
       CONNECTION LIMIT = -1;

begin;
  GRANT CREATE ON DATABASE ???? TO ????;

  COMMENT ON DATABASE ???? IS '????database';

  GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO ????;
  GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ????;
  GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ????;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO ????;

  ALTER DEFAULT privileges IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ????;
commit;


How to track how many times the data is read?

Write a stored procedure and make sure the data is only accessed through that procedure.
In it, you can increment a counter in another table.