MySQL 8.0.20 - Master Replica scheme, increasing delay between Source and Replica We have a Source Database of about 1TB, and a Replica on another server (similar architecture).
We have setup Master Slave replication and delay between Master and Slave is always growing since.
Master server is "hyghly" used by cron which insert/update a lot of data, but after some researches, I have to admit I'm a bit stuck.
Could you help me diagnose the cause of the problem? Many thanks for any hint
Master / Slave server specs: 24 cores / 256 GB DDR4 / NVMe
htop says CPU usage:  6% / RAM usage: ~50% / IO: up: ~0,23 Mb/s, down: ~3,5 Mb/s
MySQL variables : https://justpaste.it/mysql_variables
Mysql > show replica status \G result:
             Replica_IO_State: Waiting for master to send event  <- most of the times
                  Source_Host: XX.XX.XX.XX (Source private IP)
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.001720
          Read_Source_Log_Pos: 1024908194                        <- increasing normally
               Relay_Log_File: Linux2-relay-bin.000296
                Relay_Log_Pos: 915186810                         <- increasing normally
        Relay_Source_Log_File: binlog.001676
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 915186601                         <- incrising normally
              Relay_Log_Space: 48272449293                       <- incrising normally
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 56693                             <- increasing slowly
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: ece58e3c-5ac0-11eb-ab4b-00505601ddac
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: waiting for handler commit        <- most of the times
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:

Please remember that MySQL Replication is single threaded by nature.
It has two threads

I/O Thread transmission of binlog events from the Master into the Slave's relay logs
SQL Thread processes binlog events collected in the Slave's relay logs in the order
received

From the looks of the variables, I would recommend just two temporary changes:
SET GLOBAL sync_binlog = 0;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

This will loosen up the writes done by the SQL threads because you have binary logging on the Slave. Every transaction written on the Slave has to be logged and the binlogs flushed. Setting sync_binlog to 0 lets the OS cache/flush rather than mysqld.
You also want to loosen the strictness of ACID compliance on the Slave by setting innodb_flush_log_at_trx_commit to 2. This will cause the InnoDB storage engine with more regularity rather than flushing on each transaction commit.
You will have to set innodb_flush_method to O_DIRECT to further assign flushes of pages to mysqld rather than the OS.
I have mentioned these things before

Dec 07, 2012 : Dynamic change to innodb_flush_log_at_trx_commit
Feb 10, 2012 : Is it safe to use innodb_flush_log_at_trx_commit = 2
May 04, 2011 : Clarification on MySQL innodb_flush_method variable

Since innodb_flush_method is not dynamic, you must restart mysqld.
Please add the following variable to your my.cnf
[mysqld]
innodb_flush_method = O_DIRECT

Then, restart mysqld. After that login to mysql and run
SET GLOBAL sync_binlog = 0;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
START SLAVE; /* If replication does not start automatically */

Replication should start catching up. Once caught up, change the variables back with
SET GLOBAL sync_binlog = 1;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

UPDATE 2021-02-16 10:12 EST
Why would one think of using these variables on a temporary basis ?
Over the years, I have had clients that would have one of the following setups:

sync_binlog=0 and innodb_flush_log_at_trx_commit=2 : This would be on read-only slaves
sync_binlog=1 and innodb_flush_log_at_trx_commit=1 : This would be for slaves you would want to become a Master should you wish to failover.

SCENARIO : Suppose you had a Master and 3 Slaves. You have the Master with sync_binlog=1 and innodb_flush_log_at_trx_commit=1. The Slave would have sync_binlog=0 and innodb_flush_log_at_trx_commit=2.
If you wish to have a failover, you would set up one of the slaves that is fully caught up (Seconds_Behind_Master=0) to have sync_binlog=1 and innodb_flush_log_at_trx_commit=1. Then point applications to the that new Slave and setup the other Slaves to replicate from the newly promoted Master.
You can use things like ProxySQL / Orchestrator to set up such steps for you.
This just one example of why these variables would be changed on a temporary basis.
CAPTAIN'S LOG : SUPPLEMENTAL
If you have the redo logs (ib_logfile0,ib_logfile1), binary logs, slow logs, or generals logs stored in the same data volume as the data, they can slow down writes to the database. How so ???
At the disk level

All Logs are written sequentially
All data is usually written in random order

Storing logs on a separate disk volume can also speed up write performance. I learned this from a Facebook Engineer's Blog. (See also How do I determine how much data is being written per day through insert, update and delete operations? and
MySQL on SSD - what are the disadvantages?)


Is index_type ignored on MySQL primary keys in create table? I have seen a create table whose primary key had an identifier where it should have an index_type, something like that:

create table a (foo INTEGER, PRIMARY KEY foo_id (foo));

The thing is that according to documentation it shouldn't be allowed, as where foo_id is should be, if something, an index_type (USING {BTREE | HASH}).

If I run a show create table for this table foo_id is removed.

Does MySQL ignore it or I'm missing something?

You cannot name a PRIMARY KEY. The name PRIMARY KEY is the designation of the arbitrary, or preferred, unique index (candidate key) for accessing the table. A table can have multiple unique keys, but only one PRIMARY KEY.

EXAMPLE: Employee table with three unique keys

EmployeeID
Driver's License Number
Social Security Number

You can pick a name out of a hat, flip a coin, or ask project managers which one of the three indexes should be the PRIMARY KEY.

You can create a unique index without it being the PRIMARY KEY as follows:

create table a (foo INTEGER, UNIQUE KEY foo_id (foo));

Check these other links about PRIMARY KEYs vs Unique Keys

Why do primary keys have names of their own?
What Is The Point of a Primary Key?

As for the other question: the Index Type

When it comes to the Index Type, here are the defaults:

The MEMORY storage engine and NDB (MySQL Cluster) use HASH as the index_type.

You could specify BTREE as the index type
It tends to bloat more than HASH indexes

The index_type for MyISAM and InnoDB is BTREE.

MyISAM and InnoDB DO NOT SUPPORT HASH INDEXES
You have to emulate HASH indexing

To find out the index type, run this query:

select index_name,index_type from information_schema.statistics
where table_schema='test' and table_name='a';

Here is an example:

mysql> use test
Database changed
mysql> create table a (foo INTEGER, PRIMARY KEY foo_id (foo));
Query OK, 0 rows affected (0.07 sec)

mysql> show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `foo` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`foo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select index_name,index_type from information_schema.statistics
    -> where table_schema='test' and table_name='a';
+------------+------------+
| index_name | index_type |
+------------+------------+
| PRIMARY    | BTREE      |
+------------+------------+
1 row in set (0.00 sec)

mysql>


Calculating storage requirements of individual fields based on actual usage and properties? I am attempting to determine if anyone has built a handy utility to query a set of fields and estimate a range of expected data usage of the set of fields.

I have looked at the Microsoft Article describing the process, as well as saw this other similar question but it just describes a generic process.

What I am looking for is query to tell me the estimated actual field usage requirements (minimum, medium, maximum) of a particular fields based on how they are actually utilized (i.e. is only 5 bytes of a 12 byte varchar in use) and taking into consideration the data type vs. another set of fields.

That is, if I have Fields A+B+E+G+Z2 it will requires between 9232 and 12344 space for 1 million rows, but if I do A+B+E+G it will only require 7212 to 9632 space for 1 million rows based on the actual usage of the existing fields.

The reason I am asking is I am trying to easily determine data storage requirements and retention for existing fields and weighing them against their usefulness for business purposes. Yes you can argue storage is cheap but I am looking for a utility query to perform exactly this.

At this point I do not care so much about the index size. In SQL 2008 I cannot find a utility to do this, and the closest thing I can get to it is the Top Tables Report for the entire table and all fields.

Perhaps this is a good start - You'd have to calculate: each data type times expected row count. Of course this is approximate result due to format/and possibly padding overhead, but might be a start.


What is the equivalent of MS Access InStr function in SQL Server 2008? This query runs in Access 2013 but I need it converted to SQL Server 2008. The issue is that the INSTR() function does not exist in SQL Server and the query references the field generated in same query EXPR1.

How would this be changed over to SQL Server 2008?

SELECT PartID, Description, Service,
InStr(1,[Description]," ") AS Expr1,
Left([Description],[Expr1]) AS part
FROM TI
WHERE (((PartID) Is Not Null) AND ((Service)="JTP"));

InStr is pretty similar to CHARINDEX. So, your query can be written as:

SELECT  PartID,
        [Description],
        [Service],
        CHARINDEX(' ',[Description]) Expr1,
        LEFT([Description],CHARINDEX(' ',[Description])) Part
FROM TI
WHERE PartID IS NOT NULL
AND [Service] = 'JTP';


Add column to database, used with logical replication generates large WAL files I have a PostgreSQL 10 master database with a logical replication, but I faced with an issue.
I added one more column (int, without default value) to a big table in master (weight 39gb, over 100 million entries) within a transaction, and updated its value with a same value in another column.
begin;
alter table test add column "onecolumn" int;
update table test set "onecolumn"="secondcolumn"
commit;

Finally it generated large WAL files with weight 39gb, so Postgres replicated whole table instead of only a column.
Why Postgres generated so big WAL files? Because real weight of adding one column with int should be much less.
Replica identity of the table is set to default

When you update a row, a new copy of the complete row is written to the table (and to WAL). This is how PostgreSQL works.
So after the update, the table will be more than twice as big, and you have generated a lot of WAL.
One thing you can do is run the update in batches of 10 million rows and run VACUUM on the table between these batches. That won't reduce WAL (that is not possible), but it will prevent table bloat. But you cannot do that in a single transaction.
In short, everything is working as it should.


SQL Server COUNT() seems to be ignoring WHERE I am trying to set up a SQL Server query that lists the count of people signed up for tours during a conference event and then displays the total sum. This is my SQL code that is coming close, but it is ignoring the WHERE clause for the grand total (but not the individual totals):

SELECT     SiteTour, COUNT(SiteTour) AS count
FROM       tblConference
WHERE      (RegistrationType NOT LIKE 'cancellation')
GROUP BY   SiteTour
UNION ALL
SELECT     'TOTAL' AS SiteTour, COUNT(SiteTour) AS Expr1
FROM       tblConference AS tblConference_1

Here is what it is producing:

         0
Tour1   22
Tour2   30
Tour3   30
----------
TOTAL   83

(Tour1 count of 22 is correct, because there is one RegistrationType cancellation. However, the TOTAL of 83 should be 82.)

Also, I am getting an initial row, I guess based on counting NULLs, that shows a 0. Is there some way prevent this?

Please help me correct this. Thank you.

You need to add the same where clause in your union select.

SELECT     SiteTour, COUNT(SiteTour) AS count
FROM       tblConference
WHERE      (RegistrationType NOT LIKE 'cancellation')
GROUP BY   SiteTour
UNION ALL
SELECT     'TOTAL' AS SiteTour, COUNT(tblConference_1.SiteTour) AS Expr1
FROM       tblConference AS tblConference_1
WHERE      (tblConference_1.RegistrationType NOT LIKE 'cancellation')


What is default behavior of PostgreSQL without specifying COLLATION? What is default behavior of PostgreSQL, if i don't specify collation externally? One of the main questions, is it case-sensitive or not?

A collation is an SQL schema object that maps an SQL name to operating system locales. Whatever you shall setup as a default collation settings for the database. By default implicitly that collation will choose your database.

In PostgreSQL nevertheless, the initial set of collation names is
platform-dependent.

There are no case insensitive collations, but there is the citext extension.
The standard approach to doing case-insensitive matches in PostgreSQL has been to use the lower function when comparing values, for example
SELECT * FROM tab WHERE lower(col) = LOWER(?);

This works reasonably well, but has a number of drawbacks:
It makes your SQL statements verbose, and you always have to remember to use lower on both the column and the query value.
It won't use an index, unless you create a functional index using lower.
If you declare a column as UNIQUE or PRIMARY KEY, the implicitly generated index is case-sensitive. So it's useless for case-insensitive searches, and it won't enforce uniqueness case-insensitively.
From the documentation:

The citext data type allows you to eliminate calls to lower in SQL queries, and allows a primary key to be case-insensitive. citext is locale-aware, just like text, which means that the matching of upper case and lower case characters is dependent on the rules of the database's LC_CTYPE setting. Again, this behavior is identical to the use of lower in queries. But because it's done transparently by the data type, you don't have to remember to do anything special in your queries.

For your reference: PostgreSQL Documentation & StackOverFlow Ref Here


SQL server Listener removal Hi I am planning to drop and recreate the listener with same name what is the best approach? Can i drop the listener from WFCS by removing the resorces or Drop the listener from SQL using SQL command? Please let me know which is the best approach?

Use TSQL, SSMS, or Powershell, per:  Remove an availability group listener (SQL Server)


How do I get Azure SQL query history/statistics/performance? I'm migrating away from Azure SQL and my new cloud hosting service wants stats like "estimated inserts per hour on average", traffic/bandwidth usage per month, or "average read/writes on table x per day".

How can I get statistics on prior DB usage per table or database with Azure?

Depending on how long the system has been up on the current underlying server, you may be able to get some of the info you're looking for from these DMVs (though many are limited to a maximum of 14 or 30 days of data retention).

sys.database_connection_stats - while it doesn't provide any of the specific info you're after, it can give you some clue about how busy your server has been, cycle fluctuations, etc.
sys.dm_db_index_usage_stats - again, depending on how far back this data goes, this can show you number of read/write operations against tables over the entire period (no breakdown by hour or day, you'll have to average). Also note that an update that affects one row and another that affects 2 billion rows are each treated equivalently as a single write operation.
sys.resource_stats - shows CPU usage, storage, and I/O metrics in 5-minute intervals over the past 14 days (or less). Not sure how valuable this info is for your needs - not sure how you will translate avg_data_io_percent to something meaningful for your new hosting provider. You can get more granular data from sys.dm_db_resource_stats (every 15 seconds), but this only goes back an hour (and requires elevated privileges).
sys.bandwidth_usage - on a v11 server you may be able to see some details about in/out bandwidth. On v12, however, this view has been deprecated and returns an empty result. I don't know of a direct replacement; you should be able to get some info on this in "Resource Costs" in the Azure portal.

You can get a rough idea about how far back your DMV data goes, you can use this as a ballpark:

SELECT MIN(start_time) FROM sys.event_log WITH (NOLOCK);

That DMV may have some useful info too, but there are warnings all over the place about the impact it can have on performance - you're migrating away, but I'm putting this here for future readers.


Search on part of a column in a SQL Server cross-database view I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views and stored procedures into the vendor's database for our custom applications.

One of the columns contains building and room data like this:
DON-203, DON-303, SGRUE-102, EVN-1010

Basically, it's a set of letters (that indicates the building) followed by a hyphen followed by digits (which indicates the room number). I need to perform queries on just the room number (numeric) portion of the column.

I can't use a precomputed column in the table, since I don't have write access to the original database.
I can't create triggers in the original database
Scalar functions will return the correct results, but seems incredibly inefficient for large datasets that will be queried very frequently
As far as I know, I can't use an indexed view, since the view refers to a table in a different database.
New buildings can be added to the program at any time, so I can't hardcode a list of all the building prefixes somewhere. They're also not stored anywhere.

Is there a decent way to handle this situation?

EDIT: Here's a sample schema (with all irrelevant data removed)

CREATE TABLE [dbo].[Room]
(
    [RoomID] [int] IDENTITY(0,1) NOT NULL,
    [Description] [varchar](30) NOT NULL,
    CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED
    (
            [RoomID] ASC
    )
)

There is a non-unique index on Description in ascending order

Sample data:

RoomID  Description
0       DON-101
1       DON-102
2       DON-103
...
118     DON-427
119     DON-428
...
124     EVN-107
125     EVN-108
...
511     NGRI-1007
512     NGRI-1008
...
564     NGRI-227
565     NGRI-228

Are you allowed to replicate that table in another database? Using transactional replication, for latest data access. If yes, then I'd test a more cumbersome process like replicating the needed table, building another table or materialized view with a computed column as you need.

If replication is not an option, I'd say that you need to establish a scheduled process to take that table in your database and continue locally to process the data. Exactly like the first suggestion, but using your own scripts and schedule.

That, or I think you're stick to the scalar functions that aren't really the best option from performance point of view.


Monitoring replication on mysql I have managed to setup master and slave replication.

It is working fine. What are the possibilities that it might go down?

Is there any alerting tool to monitor that?

Another thing: Can I run a separate db in my replication db which I just run for testing purposes?

Replication can break or misbehave in all sorts of fun and exciting ways.  You need to monitor for three things:

1. Replication is running and has not stopped due to error

To monitor whether or not replication is running is simply a matter of programmatically checking SHOW SLAVE STATUS and looking at the values for Slave_IO_Running and Slave_SQL_Running.  Both should be "yes".  pmp-check-mysql-replication-running from the Percona Monitoring Plugins for Nagios is written for this task.

2. Replication is performing well (slave lag behind master is within an acceptable range)

You need to make sure that the slave has not lagged behind the master by too far.  "Too far" is determined by what your application can tolerate and by how many binary logs you keep on the master server.  Because replication on the slave is single-threaded, slaves can easily get lagged behind.  SHOW SLAVE STATUS has the Seconds_Behind_Master value, but is not a reliable indicator of actual lag, and frequently will jump around.  In order to accurately measure replication lag, you need an external application to insert a timestamp into a table periodically.  You can then measure that value from the slave and compare it against the current time to get actual replication delay.  pt-heartbeat is a daemon that will insert a heartbeat into a table on your server.  You can then alert on that value with pmp-check-mysql-replication-delay to make sure it is within your specified parameters.

3. The data on the servers is in sync.

There are many ways that a master and slave can get out of sync so that the data differs.  You need to detect those differences and correct them periodically because a small difference can, over time, turn into a very large difference, especially with statement-based replication.  This is no small task, and pt-table-checksum is designed to calculate these differences.  Run this weekly.  pmp-check-pt-table-checksum is a Nagios plugin to alert when the slave has data discrepancies relative to the master.  To actually fix the differences, use pt-table-sync.

pt-table-checksum has been recently rewritten and is pretty easy to use.  pt-table-sync has a lot of options and can be confusing.  Read the documentation for these thoroughly as you can really shoot yourself in the foot if you aren't careful.  Here is a webinar about these tools.

  Another thing: Can I run a separate db in my replication db which I just run for testing purposes?

There is nothing preventing you from modifying (or supplementing) the data on the slave, though generally I would recommend against it.  Best practice is to have the slave be read_only=1.  However, real life tends to trump best practices and often slaves are used as reporting servers.  My suggestion would be to make very clear access privileges for those using the slave for data modification and to have all additional tables in a separate schema.


Can I use `base/pgsql_tmp` directory for non-admin applications? ((
  edit after answer: this is a typical
  XY question/problem,
  there are no restriction on use absolute path,
  on PostgreSQL's FileSystem functions. As @LaurenzAlbe comment, "just try".
))

I need to export/import JSON files, CSV files, XML, GeoJSON, etc. Example:

CREATE EXTENSION adminpack;
SELECT pg_catalog.pg_file_write(
  'base/pgsql_tmp/test1_hello.txt',
  E'Hello\n\n\tWorld!',
  false
);

All are temporary (exists in a BEGIN/COMMIT), but no problem?

no risk on filename conflict? (e.g. my filename and a pgsql's managed temporary table filename)
no risk of directory (base) volume overflow? (e.g. when I save a big file there)
no risk on read-and-write sequences over same filename? They will be synchronous on BEGIN/COMMIT  sections?

NOTES AND CONTEXT

Sometimes we can say that "database servers aren't designed for general file I/O" (ref), but sometimes we need it (!),  to simplify and automatize ETL or interface procedures.

About item 1 and the practical use, to simplify export/import procedures (and not need to remember where is data_directory), is perhaps better to create a ${data_directory}/base/pgsql_tmp/MyArea as symbolic link to other area  (e. eg. /tmp/MyRealArea).

Question 1:

It is a bad idea to create these files in the PostgreSQL data directory.  That is for database files only.  In addition, you cannot be certain that pgsql_tmp is there at all (it is only created when the first temporary file is written).

Use an absolute path and write the file somewhere else, ideally in /var.

Question 2

Of course there is a risk to fill up the file system - either through the files that you write, or through temporary files that the database writes.

Question 3

That is up to your code; the database does not guarantee that you don't write over the same file in two concurrent transactions.


Migration to Oracle Autonomous Database What prerequisites, if any (such as version number, database objects in use), does an existing Oracle database have to fulfil in order to be able to migrate it straight away to Oracle Autonomous Database? I've read the FAQ but it doesn't address this.

It appears Oracle version 18c is compatible with Autonomous database, but not prior versions.


Salvaging data from corrupt tables PostgreSQL Am in the process of salvaging  very crucial data from a corrupted database.

Am getting the following error from a very crucial table
ERROR:  invalid page header in block 94206 of relation base/11974232/11975439

when I set zero_damaged_pages to on , I realized I end up losing alot of pages which contain data that's significant to a meaningful recovery.

so my question is:-

1.How do I maximize data recovery without loosing the pages?

when I do a query on some tables, the connection gets lost before the query executes. usually the query runs for few seconds before the disconnection. How do I deal with this

If you have a recent backup, consider using that.

Otherwise, before you do anything else, make a copy of the directories containing the data you wish to recover. You may wish to use http://www.forensicswiki.org/wiki/Ddrescue to make a very low-level copy of the partition. It works in a way that will tolerate faults in the underlying storage medium.

From there you may wish to hire data recovery experts or PostgreSQL experts. If that's not an option, I would be prepared to get your hands dirty with the documentation for PostgreSQL's on-disk storage, so you can consider hand-repairing some of the data to make it valid again (to the extent that this is possible).


Using IF ... PREPARE ... EXECUTE ... DEALLOC to create a temporary block? What are the comparative benefits of these two statements,
SET @stmt = (SELECT IF(
  (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = DATABASE() AND
            TABLE_NAME = 'foo' AND
            COLUMN_NAME = 'bar'
  ) > 0,
  'DO 0',
  'ALTER TABLE `foo` ADD COLUMN `bar` varchar(32) NOT NULL;'
));
PREPARE sth FROM @stmt;
EXECUTE sth;
DEALLOCATE PREPARE sth;

vs..
DROP PROCEDURE IF EXISTS `proc`;
DELIMITER $$
CREATE PROCEDURE proc()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*)
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_SCHEMA = DATABASE() AND
                            TABLE_NAME = 'foo' AND
                            COLUMN_NAME = 'bar');
    IF _count = 0 THEN
        ALTER TABLE `foo` ADD COLUMN `bar` varchar(32) NOT NULL;
    END IF;
END $$
DELIMITER ;
CALL proc();
DROP PROCEDURE `proc`;

I'm just wondering if either approach is prefered over the other?

NOT EXISTS is more readable and does not need to go through to get a full count.
BEGIN
    IF (NOT EXISTS(SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'foo'
              AND COLUMN_NAME = 'bar' ))
    THEN
        ALTER TABLE `foo` ADD COLUMN `bar` varchar(32) NOT NULL;
    END IF;
END $$


SQL Server Concurrent Inserts and Deletes From: SQL Server 2014 Concurrent input issue

Follow up question:
How do we Delete and Reinsert rows in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc? Do we still use (UPDLOCK, SERIALIZABLE) or another lock?

We have two tables: Order and OrderLineDetail. Table Order is the parent which stores general information; and OrderLineDetail is the child table, since orders have multiple detail line items.

Orders can be updated, so we first check if OrderId is present in the table, and insert or update accordingly.

Due to customer modifications, line issue, server busy, etc, the Orders files can be sent more than once. We have a time Lastfiledatetime column. If the incoming timestamp is older, it has no effect on the table.

For the OrderLineDetail table, sometimes we don't have natural keys or surrogate keys in our files, so we delete all the child OrderLineDetail items, and repopulate (this is an xml file older legacy system).

Create Table Orders
(OrderId bigint primary key,  -- this is in xml files
 Lastfiledatetime datetime null
)

Create Table OrderLineDetail
(OrderLineDetailid bigint primary key identity(1,1),  -- this is Not in the xml files
 OrderLineDescription varchar(50),
 OrderLineQuantity int,
 OrderId bigint foreign key references Orders(OrderId),
 Lastfiledatetime datetime
)

We work in a multithreading, parallel processing environment, using SQL Server 2016 Read Committed Snapshot Isolation level.

To update the Order parent, we do this:

BEGIN TRANSACTION;

IF NOT EXISTS
(
    SELECT *
    FROM Order WITH (UPDLOCK, SERIALIZABLE)
    WHERE Order ID=@OrderID
)
    INSERT INTO Order () VALUES ()
ELSE
    UPDATE Order
    SET ...
    WHERE OrderID=@OrderID
    AND LastFileDatetime<@CreatedTime;

COMMIT TRANSACTION;

New question

How do we Delete and Reinsert the child OrderLineDetail table in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc?
Do we still use (UPDLOCK, SERIALIZABLE) or another lock?

BEGIN TRANSACTION;

IF EXISTS
(
    SELECT *
    FROM OrderLineDetail WITH (UPDLOCK, SERIALIZABLE)
    WHERE Order ID=@OrderID
)
    DELETE OrderLineDetail
    WHERE OrderId=@OrderId
    AND LastFileDatetime<@CreatedTime;

    INSERT INTO OrderLineDetail () VALUES ()

COMMIT TRANSACTION;

The UPDLOCK, SERIALIZABLE pattern is all about avoiding incorrect results (including false key violation errors) due to race conditions, when performing a particularly common operation known as an UPSERT - update an existing row if it exists; insert a new row otherwise.

  ...while avoiding racing conditions, deadlocks, etc?

You seem to be looking for a magic combination of hints that will allow a database system to perform highly concurrent operations without conflict. In general, there is no such thing. The only way to avoid deadlocks completely is to always modify objects in the same order.

This can be difficult, even impossible, to achieve in a foreign key relationship. Consider that when inserting a new object, you must add the parent row before the child row(s). When deleting an object, you must remove the child(ren) before the parent.

This is not to say that careful locking will not still prevent race conditions, but it cannot guarantee to avoid deadlocks in all situations.

As far as the question goes, yes, using the UPDLOCK, SERIALIZABLE hint will protect from UPSERT race conditions, but no, it will not prevent deadlocks. It may even contribute to increasing their frequency. This is tricky to assess in advance, and even experienced SQL Server database designers can get it wrong.

For more complex requirements involving multiple objects, a common solution is to always explicitly exclusively lock the parent row before modifying the children, even where the natural order is to process the child first.

For example, when deleting an Order:

DECLARE @OrderID bigint = 12345;

BEGIN TRANSACTION;

    -- EXTRA STEP
    -- Dummy update with XLOCK to exclusively lock the parent row
    UPDATE TOP (1) dbo.Orders WITH (XLOCK)
    SET Lastfiledatetime = NULL
    WHERE OrderId = @OrderID;

    -- Remove children
    DELETE dbo.OrderLineDetail
    WHERE OrderId = @OrderID;

    -- Remove parent
    DELETE dbo.Orders
    WHERE OrderId = @OrderID;

COMMIT TRANSACTION;

The extra step of exclusively locking the parent row will help eliminate deadlocks, if all modifications follow the same modification order: parent first, then child(ren). If any process accesses objects in the reverse order, the possibility of a deadlock due to incompatible locks occurs.

Note that can be important to actually modify something in the parent row, since SQL Server may not honour the exclusive lock (XLOCK) in some situations, if it can tell that it is not necessary for the operation being performed.

A second implementation of roughly the same idea is to use application locks, as mentioned in the Q & A Implementing application locks within SQL Server (Distributed Locking Pattern) (see the documentation links there).

This is easier in some respects, but again, you do have to ensure that all code that modifies the protected objects uses the same scheme. As soon as anything can access the underlying object(s) without taking the required application lock(s), the whole scheme breaks down.

An example below shows how we might take an exclusive application lock on a particular order number before processing elements of the order:

BEGIN TRANSACTION;

    -- The order number we want exclusive access to
    DECLARE @OrderID integer = 12345;

    -- Compute the locking resource string
    DECLARE @Resource nvarchar(255) = N'dbo.Order' + CONVERT(nvarchar(11), @OrderID);

    -- Return code from sys.sp_getapplock
    DECLARE @RC integer;

    -- Build dynamic SQL
    DECLARE @SQL nvarchar(max) =
        N'
        EXECUTE @RC = sys.sp_getapplock
            @Resource = ' + QUOTENAME(@Resource) + N',
            @LockMode = Exclusive,
            @LockTimeout = -1;'

    -- Try to acquire the lock
    EXECUTE sys.sp_executesql
        @SQL,
        N'@RC integer OUTPUT',
        @RC = @RC OUTPUT;

    -- Do something with the return code value if necessary
    SELECT @RC;

    --- Sensitive operations go here

    -- Release the application lock early if you can
    -- using sys.sp_releaseapplock

ROLLBACK TRANSACTION;

Concurrency is hard; sorry about that.


Can we reconstruct/reset a broken Postgresql data directory without losing data? I'm trying to restore a database from a broken data directory that have lost many of its files accidentally.

I have tried unsuccessfully to install new instance of the same Postgresql version and then copy (with overwrite) the broken datadir to the new one.
here the remaining datadir that I'm working on :

   |-base
   |---1
   |---16384
   |---17950
   |---19621
   |---pgsql_tmp
   |-global
   |-pg_multixact
   |---members
   |---offsets
   |-pg_notify
   |-pg_stat_tmp
   |-pg_tblspc
   |-pg_xlog
   |---archive_status

It may be possible to rescue some of your data. It will not be straightforward.

Hopefully you took a full file-level backup of your postgres directories as soon as you noticed the problem. A recent filesystem backup will also he useful.

Then, subscribe to the postgresql-general mailing list and post a full description of your problem there. Bear in mind it is the weekend and you will need to be patient.


Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts? We have a database server running Centos 5.6 64bit 64Gb of RAM. The server is idle most of the time. However, every hour it gets a lot of DELAYED inserts. Up to 3 mil INSERTs per hour into a MyISAM table. The version of MySQL is: 5.1.56-community-log

For some reason, the server eventually eats all the memory it has, goes into swap and becomes very slow. Even after NOTHING else is happening on the server, it stays that way.

Here's a picture showing htop:

How we do these inserts. There's a job every hour that runs on a server and starts doing a lot of inserts. All inserts are DELAYED. Inserts are NOT extended but we use mysqli::multiple_query to send 2500 of these single statement inserts to the server in one batch.

What would also help is to look inside MySQL and see where it's consuming all that memory. We've already checked all the usual buffers and they are way below the amount of memory we have.

According to the MySQL Documentation

When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.
Inserts from many clients are bundled together and written in one block (This consumes memory). This is much faster than performing many separate inserts.
The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost.
INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows (This consumes memory).
For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM. This is definitely the case for a MyISAM table that experiences no DELETEs and UPDATEs.

Based on these excerpts from the MySQL Documentation, I would like to suggest the following options:
OPTION 1
Change your target table from MyISAM to InnoDB. This will eliminate the need to use INSERT DELAYED altogether. With InnoDB using MVCC for ACID compliance, the InnoDB log files will better protect data in the events of a crash via MySQL calling for InnoDB to do crash recovery. You will need to tune InnoDB accordingly.
OPTION 2
If you cannot switch the table to InnoDB, you must go with extended INSERTs. However, you must adjust bulk_insert_buffer_size (its default is 8M) to a much larger to compensate using extended INSERT.
Either way, you need to stop using INSERT DELAYED like yesterday.


I have an instance of RDS of MySQL 5.7.10 I do not understand why memory bar seems to exceed to the red zone with 24.5 MB while RDS server should be with 1Gib

If you have the performance_schema enabled, then this will consume roughly 500Mb of RAM, then in addition to that you need to take into account your buffer pool size settings (RDS defaults these to 3/4 of the RAM).

Adding these two together can push the allocated ram size over 1Gb quite quickly - note that you have 182Mb of swap used too.

The 24.5Mb of free RAM is probably the OS file cache, which can be discarded by the OS should other RAM demands become too much.

Try turning off the performance_schema in the RDS parameter group and see if that makes any difference.


Why does LEN() function badly underestimate cardinality in SQL Server 2014? I have a table with a string column and a predicate that checks for rows with a certain length. In SQL Server 2014, I am seeing an estimate of 1 row regardless of the length I am checking for. This is yielding very poor plans because there are actually thousands or even millions of rows and SQL Server is choosing to put this table on the outer side of a nested loop.

Is there an explanation for the cardinality estimate of 1.0003 for SQL Server 2014 while SQL Server 2012 estimates 31,622 rows? Is there a good workaround?

Here is a short reproduction of the issue:

-- Create a table with 1MM rows of dummy data
CREATE TABLE #customers (cust_nbr VARCHAR(10) NOT NULL)
GO

INSERT INTO #customers WITH (TABLOCK) (cust_nbr)
    SELECT TOP 1000000
        CONVERT(VARCHAR(10),
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
GO

-- Looking for string of a certain length.
-- While both CEs yield fairly poor estimates, the 2012 CE is much
-- more conservative (higher estimate) and therefore much more likely
-- to yield an okay plan rather than a drastically understimated loop join.
-- 2012: 31,622 rows estimated, 900K rows actual
-- 2014: 1 row estimated, 900K rows actual
SELECT COUNT(*)
FROM #customers
WHERE LEN(cust_nbr) = 6
OPTION (QUERYTRACEON 9481) -- Optionally, use 2012 CE
GO

Here is a more complete script showing additional tests

I have also read the whitepaper on the SQL Server 2014 Cardinality Estimator, but didn't find anything there that clarified the situation.

For the legacy CE, I see the estimate is for 3.16228 % of the rows – and that is a "magic number" heuristic used for column = literal predicates (there are other heuristics based on predicate construction – but the LEN wrapped around the column for the legacy CE results matches this guess-framework).  You can see examples of this on a post on Selectivity Guesses in absence of Statistics by Joe Sack, and Constant-Constant Comparison Estimation by Ian Jose.
-- Legacy CE: 31622.8 rows
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  ( QUERYTRACEON 9481); -- Legacy CE
GO

Now as for the new CE behavior, it looks like this is now visible to the optimizer (which means we can use statistics).  I went through the exercise of looking at the calculator output below, and you can look at the associated auto-generation of stats as a pointer:
-- New CE: 1.00007 rows
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  ( QUERYTRACEON 2312 ); -- New CE
GO

-- View New CE behavior with 2363 (for supported option use XEvents)
SELECT  COUNT(*)
FROM    #customers
WHERE   LEN(cust_nbr) = 6
OPTION  (QUERYTRACEON 2312, QUERYTRACEON 2363, QUERYTRACEON 3604, RECOMPILE); -- New CE
GO

/*
Loaded histogram for column QCOL:
[tempdb].[dbo].[#customers].cust_nbr from stats with id 2
Using ambient cardinality 1e+006 to combine distinct counts:
  999927

Combined distinct count: 999927
Selectivity: 1.00007e-006
Stats collection generated:
  CStCollFilter(ID=2, CARD=1.00007)
      CStCollBaseTable(ID=1, CARD=1e+006 TBL: #customers)

End selectivity computation
*/

EXEC tempdb..sp_helpstats '#customers';

--Check out AVG_RANGE_ROWS values (for example - plenty of ~ 1)
DBCC SHOW_STATISTICS('tempdb..#customers', '_WA_Sys_00000001_B0368087');
--That's my Stats name yours is subject to change

Unfortunately the logic relies on an estimate of the number of distinct values, which is not adjusted for the effect of the LEN function.
Possible workaround
You can get a trie-based estimate under both CE models by rewriting the LEN as a LIKE:
SELECT COUNT_BIG(*)
FROM #customers AS C
WHERE C.cust_nbr LIKE REPLICATE('_', 6);

Information on Trace Flags used:

2363: shows a lot of information, including statistics being loaded.
3604: prints the output of DBCC commands to the messages tab.


select/union output from two columns into three I'm sure there is an easy answer to this, but my PostgreSQL isn't up to it at the moment.

I have a table hostname_facts with three columns:

hostname
name
value

hostname is a list of fully-qualified server hostnames.
name contains things like role (a TLA defining a server's role in the environment), env (part of an FQDN), memorysize, eth1_ip, fqdn, etc.
value - the actual value of role, env, memorysize, eth1_ip, fqdn, etc.

There are about 800 hostnames in this table.

From this table I've been asked to create a flat file that can be used as an inventory list for Ansible, which needs to be in the format:

[role.env]
hostname, value_of_role, value_of_env
hostname, value_of_role, value_of_env
hostname, value_of_role, value_of_env
...

So, I need need to pull out hostname sorted by value of name = 'role' as well as name = 'env'. This is stumping me!

I used:

select hostname,value from hostname_facts
where name = 'role'
union
select hostname,value from hostname_facts
where name = 'env'
order by value ASC

But that returns the 'name' values all in one long column, 'role' followed by 'env', with all hostnames repeated twice (obviously).

Can anyone suggest how to get just one set of hostnames with 'role' and 'env' as two columns alongside, rather than one underneath the other?

After your clarifying comment I suggest:

SELECT hostname, r.value AS role, e.value AS env
FROM   hostname_facts r
JOIN   hostname_facts e USING (hostname)
WHERE  r.name = 'role'
AND    e.name = 'env'
ORDER  BY hostname;

It does the same as the query @zgguy already posted, just simpler.


Upload multiple csv file to multiple tables sqlServer I have over than 200 csv files that each of them is diffrent table.

I create now a new DB and want to upload them all so I will have all the table.

I not accessable to old DB to backup in diffrent way.

I looking for a simple way to do it fast  (script/gui/ 3pary tool)

I found I can do it with sqlServer gui but I cant upload there multiple files, only 1 by 1, so it will takes hours.

I saw this post How to upload multiple csv files to sql server without constrains but it not help me, not found there any solution.

I you want to use powershell to upload these CSV files, without having to code/script, take a look dbatools.io. Look for the cmdlet Import-DbaCsv

All the documentation is available on the site. Just download the tools from github or the powershell gallery.

You can use Get-ChildItem cmdlet to get all the CSV files in a folder and use the pipeline to send the files to Import-DbaCsv (optionally specifying the -AutoCreateTable flag). For example:

Get-ChildItem -Path "C:\Path\To\your\CSVs" | ForEach-Object {
    Import-DbaCsv -Csv $_.FullName -SQLInstance "localhost" -Database "yourdb" -AutoCreateTable
}


How to get exception context for a manually raised exception in PL/pgSQL? In Postgres, we get the "stack trace" of exceptions using this code:

EXCEPTION WHEN others THEN
    GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;

This works fine for "natural" exceptions, but if we raise an exception using

RAISE EXCEPTION 'This is an error!';

...then there is no stack trace. According to a mailing list entry, this might be intentional, although I can't for the life of me figure out why. It makes me want to figure out another way to throw an exception other than using RAISE. Am I just missing something obvious? Does anyone have a trick for this? Is there an exception I can get Postgres to throw that would contain a string of my choosing, so that I would get not only my string in the error message, but the full stack trace as well?

Here's a full example:

CREATE OR REPLACE FUNCTION error_test() RETURNS json AS $$
DECLARE
    v_error_stack text;
BEGIN

    -- Comment this out to see how a "normal" exception will give you the stack trace
    RAISE EXCEPTION 'This exception will not get a stack trace';

    -- This will give a divide by zero error, complete with stack trace
    SELECT 1/0;

-- In case of any exception, wrap it in error object and send it back as json
EXCEPTION WHEN others THEN

    -- If the exception we're catching is one that Postgres threw,
    -- like a divide by zero error, then this will get the full
    -- stack trace of the place where the exception was thrown.
    -- However, since we are catching an exception we raised manually
    -- using RAISE EXCEPTION, there is no context/stack trace!
    GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT;

    RAISE WARNING 'The stack trace of the error is: "%"', v_error_stack;

    return to_json(v_error_stack);
END;
$$ LANGUAGE plpgsql;

This behaviour appears to be by design.

In src/pl/plpgsql/src/pl_exec.c the error context callback explicitly checks to see if it's being called in the context of a PL/PgSQL RAISE statement and, if so, skips emitting the error context:

/*
 * error context callback to let us supply a call-stack traceback
 */
static void
plpgsql_exec_error_callback(void *arg)
{
        PLpgSQL_execstate *estate = (PLpgSQL_execstate *) arg;

        /* if we are doing RAISE, don't report its location */
        if (estate->err_text == raise_skip_msg)
                return;

I can't find any specific reference as to why that is the case.

Internally in the server, the context stack is generated by processing the error_context_stack, which is a chained callback that appends information to a list when called.

When PL/PgSQL enters a function it adds an item to the error context callback stack. When it leaves a function it removes an item from that stack.

If the PostgreSQL server's error reporting functions, like ereport or elog are called, it calls the error context callback. But in PL/PgSQL if it notices that it's being called from a RAISE its callbacks intentionally do nothing.

Given that, I don't see any way to achieve what you want without patching PostgreSQL. I suggest posting mail to pgsql-general asking why RAISE doesn't provide the error context now that PL/PgSQL has GET STACKED DIAGNOSTICS to make use of it.

(BTW, the exception context is not a stack trace as such. It looks a bit like one because PL/PgSQL adds each function call to the stack, but it's also used for other details in the server.)


Connect to a remote MySQL Server from a dynamic IP address My ISP at home uses a dynamic IP address. I'm trying to access a MySQL database at work. How can I connect to it (using MySQL Workbench)?

I could grant all privileges to % so that any IP address will do, but I think this is NOT VERY SAFE. I don't know if granting privilege to my current IP Address will work.

If it does, it will still be tedious to grant privilege every time the IP changes.

Thanks in advance!

Do you have the possibility to connect to your office network using a VPN? Then you could receive a static IP address in the range of your internal network. For this one, you could easily grant the necessary privileges.


SQL Performance Monitor vs DMV Dynamic Management Views We are trying to measure SQL Server Performance metrics. Is it better to record Performance Monitor (PerfMon) or DMV Dynamic Management Views to measure CPU, Memory, Buffer pages, etc?
There are certain metrics, that PerfMon can measure which DMV cannot, and other way around DMV can measure certain things Perfmon can't. I am referring to common shared metrics.

In general, does Microsoft have best practice, to use Perfmon or DMV in SQL Server?
I assume answer is Perfmon with remote computer,  since measuring things with DMV takes lag/pressure on the central server. Perfmon can be utilized from remote server, and reduce load.

Thank you,

I think the best advice is to use everything that is available, or at least the bits that work best for your workload.

Also consider extended events (often abbreviated to XE) which while harder to get into initially can offer a wealth of information about specific events rather than just counts of things happening as you'll get from PerfMon, and imposes less load than constantly polling DMVs.

Use PerfMon to monitor the general health of the SQL instances, and when that or other indications suggest a problem use information from DMVs to look at what is happening now or has been happening recently, and extended events to "catch things live" and record them for later analysis.

[SQL Profiler can do a fair amount of what XE does too, and is easier to get started with, but it is deprecated so hasn't been updated much since 2012, may vanish completely in later releases, and isn't available against Azure databases at all (apart of course from on-prem SQL Server running in VMs)]


Format Phone Numbers In SQL Server I am using SQL Server 2008 and I found this solution which is only for SQL Server 2012 - what is the equivelant to format a phone number in SQL Server 2008?

FORMAT([PhoneField],'###-###-####')

Assuming that the [PhoneField] is 10 digits, such as '1234567890' then you could use STUFF to put the proper values in.  E.g.

select STUFF(STUFF('1234567890',7,0,'-'),4,0,'-');

But please note that this only answers formatting a phone number for the 10-digit answers.  If you are using internationally dialed numbers, you need to investigate the various forms of phone numbers you might need to handle.

See: https://en.wikipedia.org/wiki/National_conventions_for_writing_telephone_numbers


What am I doing wrong with my math? I have a table of fatalities and populations that I need to normalize to fatalities per 100K people:

CREATE TABLE all_states (
  state character varying(20) NOT NULL,
  fatalities integer NOT NULL,
  population integer NOT NULL
);

I want to calculate the fatality rate per 100,000 people, so I did:

SELECT *, (fatalities/population)*100000 AS rate
FROM all_states;

But what I get is just 0s:

STATE          population   fatalities  rate
"Alabama"      4802740      75          0
"Alaska"       722718       39          0
"Arizona"      6482505      69          0
"Arkansas"     2937979      93          0
"California"   37691912     390         0

I feel like I'm missing something super simple here. I tried casting it as a float but that didn't change the results:

SELECT state, population, fatalities,
       CAST(((fatalities/population)*100000) AS float) AS rate
FROM all_states;

The problem here is that / operator in integer division truncates the result, check  Mathematical Functions and Operators

Try with:

SELECT *,(fatalities::float/population)*100000 AS rate FROM all_states;

Or

SELECT *,(fatalities::numeric/population)*100000 AS rate FROM all_states;


Sql Server 2016 errror Incorrect syntax near 'EXTERNAL' I have two SQL Server 2016 VMs in Azure. I am trying to connect these two sql server instances using External datasource. I can safely connect the two instances from my local pc, remotely.
While querying using external table/data source, I am getting this:

Kindly note

i) I have used both RDBMS/SHARD_MAP_Manager

ii) Credential is valid credential

iii) I know, i can use Linked Servers, but i don't want it. As i am planning to move these databases to Sql Azure Db. External Table and datasource is not mandatory. You can suggest alternatives other than LinkedServers.

So i need a query or part of query that will work with both, Sql Server(Virtual/Physical server) and Sql Azure(Db as a Service)

Update

Same query ran flawlessly on Sql Azure db.

External data source type SHARD_MAP_MANAGER and RDBMS is only supported only on Azure SQL Database v12 or later. It is not supported on SQL Server 2016.

Ref:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql

-- Elastic Database query only: a shard map manager as data source
-- (only on Azure SQL Database v12 or later)
CREATE EXTERNAL DATA SOURCE data_source_name
    WITH (
        TYPE = SHARD_MAP_MANAGER,
        LOCATION = '<server_name>.database.windows.net',
        DATABASE_NAME = '\<ElasticDatabase_ShardMapManagerDb'>,
        CREDENTIAL = <ElasticDBQueryCred>,
        SHARD_MAP_NAME = '<ShardMapName>'
    )
[;]

-- Elastic Database query only: a remote database on Azure SQL Database as data source
-- (only on Azure SQL Database v12 or later)
CREATE EXTERNAL DATA SOURCE data_source_name
    WITH (
        TYPE = RDBMS,
        LOCATION = '<server_name>.database.windows.net',
        DATABASE_NAME = '<Remote_Database_Name>',
        CREDENTIAL = <SQL_Credential>
    )
[;]


Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself I encounter the following error:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

when I was trying these commands

USE ASPState
GO

GRANT EXECUTE ON CreateTempTables TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON DeleteExpiredSessions TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON GetMajorVersion TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON GetHashCode TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetAppID TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItem TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItem2 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItem3 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItemExclusive TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItemExclusive2 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetStateItemExclusive3 TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempGetVersion TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempInsertStateItemLong TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempInsertStateItemShort TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempInsertUninitializedItem TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempReleaseStateItemExclusive TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempRemoveStateItem TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempResetTimeout TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemLong TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemLongNullShort TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemShort TO [R2Server\AAOUser]
GO
GRANT EXECUTE ON TempUpdateStateItemShortNullLong TO [R2Server\AAOUser]
GO

So, how do I know what type of permissions my account R2Server\AAOUser have for the database ASPState? and what are the ways to solve the error (so that I can grant these permissions to my account)?

As per the comments, if you're already the db owner of that database, than you don't need to grant any permission for the db.

Now, in order to find out what specific permissions you have, you can use the following queries:

find permissions on specific db:

USE AdventureWorks2008R2;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

find permissions on all the server:

SELECT * FROM fn_my_permissions(NULL, 'SERVER');
GO

See some other info here: Find out SQL Server Logins and associated Server Roles, on MSDN forum.


Should a SKU be associated to warehouse or supply table? This may be a trivial question for any of you experienced in supply chain models. The model I am building in PosgreSQL has 3 tables:
create table supply (
  id       serial primary key,
  barcode  varchar,
  title    varchar
  ...
);

create table warehouse (
  id     serial primary key,
  title  varchar,
  ...
);

create table warehouse_supply (
  id            serial primary key,
  warehouse_id  bigint not null,
  supply_id     bigint not null,
  ...
  foreign key (supply_id) references supply (id),
  foreign key (warehouse_id) references warehouse (id)
);

I was tempted to include an SKU (Stock Keeping Unit) column at table supply, but the very definition of SKU made me think it is a warehouse right to state their own SKU naming policy (this could occur with 3rd party warehouses).
Am I right? Should I include an sku varchar column at warehouse_supply instead of supply?

If the SKU is an attribute of a supply entity being located at a warehouse, then I would agree with your assessment to put it in the warehouse_supply table.
OTOH, if the company has decided that SKUs are the same regardless of the location, then it is an attribute of supply.


Oracle sys SYSDBA audit information to a report I am trying to produce a monthly report on Audit.

I am able to query the DBA_AUDIT_TRAIL, for all other users and no issue in that.

I wanted to include the SYS/SYSDBA activities in this report and I know this is not recorded in SYS.AUD$ table but its recorded as an operating system files called .AUD. I know this is done to make sure that DBAs don't go and modify these data.

Because this is just a reporting how can I get this details to my report?

Is there a way I can query the operating system files eg. XXXX.AUD ?

Thanks

As you already know that SYS user audit information is stored in an OS file there is no such tables or views to query in the database.

Workaround is to create an external table.

Here is an example from AskTom.

Audit file as External Table input


mysql performance test We are planing to test the performance of the new DB server. For that we have the below the plan:

get production sql queries
replay those sql queries against the different DB

I was planing to use pt-log-player to play the sql queries and use the pt-query-digest to analyze the result files which gets generated from pt-log-player after execution. So my questions are:

how to get the production query in the first place. enabling slow log query is not an option due to performance reason (as our DB admin denied about that)
Why pt-log-player is deprecated, was there any bug or whats the reason behind.
Or someone can suggest me a complete different toolset to do the perf test. Thanks.

1) Although I prefer tcpdump myself I would ask what is the reason not to enable slow logs because it is not that big of an overhead. With reasonable thresholds it's actually very useful.

If you want to be super cautious you can safely enable it with a conservative long_query_time and slowly dropping it. The variable is dynamic so you can change it on the fly if you experience issues. Also depending on the flavor of your server (Percona for example) you may be able to set log_slow_rate_limit which was designed for exactly this purpose.

Another way to do it would be to use tcpdump which you can do on the server, on a shadow server (if your networking setup supports it) or on the application server as well. Then you can use pt-query-digest to extract the queries from it:

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

This is a very versatile solution so it's worth to get familiar with.

More info can be found on https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

2) This is just an assumption but there are many-many ways to execute/replay queries so I think that's the reason for the dropped support.

3) If you have your queries you can also use percona playback, mysqlslap, sysbench or any other tools to execute them as you wish. If you want to write your own stress tool I have a web request playback code example which can give you some ideas.


Finding oracle physical database files I'm using oracle 11g and I need to find the database files to copied to another server. The database was created from migrating from SQL Server 2008 to 11g. There doesn't seem to be a counterpart for sql server's mdf/ldf files.

I read that there are dbf's/ctl's instead but I'm not sure which one is what I need. Also there aren't any files named after the database user that owns the database (after migration, it's the user that has the database) which leaves me puzzled.

I need to know which files are needed so I can copy the oracle database to another remote oracle server easily.

An Oracle database instance is made basically of three kinds of files: database files (aka datafiles), controlfile, and redo log files.

You can check their names and paths by issuing these simple queries, logged with a DBA privileged account:

select name   from v$datafile;
select name   from v$controlfile;
select member from v$logfile;

Your question is a very newbie one. No problem with it, of course, but you should take a read on Oracle Database Concepts manual.

https://docs.oracle.com/database/121/CNCPT/toc.htm


MySQL queries in SQL-Server? How can we best use our existing MySQL queries with SQLserver?

Alternately, what type of changes do we need when we are converting our database server from MySQL to SQLServer?

If it is porting that you are refering to then...MySQL uses a different flavour of SQL compared to Microsoft SQL Server which uses T-SQL (TransactSQL)

There are guides out there if you search that will show you there difference in syntax and perhaps even convert blocks of SQL for you.


Oracle 11g, Will droping partition reuse free space on bigfile tablespace I have four range partitioned tables which belongs to bigfile tablespace. Also local indexes on that tables belongs to other bigfile tablespace.
My partitioning range is one day and I have stored procedure that drops partitions older than 30 days fro each table.
As for now (15 days from start of gathering data) my datafiles (both data and index) are growing steadily as expected.
My question is when stored procedure drops first partition older than 30 days will that space be avaliable to new partitions or datafile wil continue to grow?

It will be available. Depending on your space management settings it can nearly fit the new partition entirely in the space or there might be some gaps, but the majority of space will be reused.

I would not expect that the grow stops immediately after 30 days depending on the fragmentations but it will eventually. Using uniform extends or at least a big minimal size for extends will help, especially if you expect large partitions.

When using LOBs retention might come into play as well.


Wait Stats, Server Up Time and CXPACKET I have been looking at the following post and using the query to look at my top wait stats:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

My top wait stat is CXPACKET and according to the total wait time I have a number which converts to about 700 days.  I know that the server has only been up 58 days so how can this be accurate?

Does CXPACKET total for each thread?  In which case how do I know if this is a problem?

It's strange how many "problems" CXPACKET is causing :)

You can find my answer to similar question at this link: https://dba.stackexchange.com/a/141399/28611

But I will re-post my answer here as well

The CXPACKET was always the confusing wait type for younger DBAs and some predictably wrong reactions are generally expected. There are multiple aspects of the CXPACKET wait type and I've tried in Troubleshooting the CXPACKET wait type in SQL Server article to bring on the table most of the reasons for high CXPACKET but also to explain the background of the CXPACKET, as the proper understanding of parallelism in SQL Server is the key for understanding

So for those who don't want to get in details, I will post the summary of the article here (but I would definitely suggest reading of the article for complete info about CXPACKET wait type):

  Do not set MAXDOP to 1, as this is never the solution
  Investigate the query and CXPACKET history to understand and    determine whether it is something that occurred just once or twice,
  as it could be just the exception in the system that is normally
  working correctly
  Check the indexes and statistics on tables used by the query and make    sure they are up to date
  Check the Cost Threshold for Parallelism (CTFP) and make sure that    the value used is appropriate for your system
  Check whether the CXPACKET is accompanied with a LATCH_XX (possibly    with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well). If this is the
  case than the MAXDOP value should be lowered to fit your hardware
  Check whether the CXPACKET is accompanied with a LCK_M_XX (usually    accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is
  the case, then parallelism is not the bottleneck. Troubleshoot those
  wait stats to find the root cause of the problem and solution


Determine sql server installation (server or client tools only) Think about the situation that you have a software audit.
The company use a software facility management tool to determine installed software on a machine.
This software that seek for installed software is able to create packages to find software.

At the moment the software seeks for special registry keys to see if sql server is installed.

I got a false alert on one machine, because there only management studio (client tools) are installed.
So I need to identify if the full server database server is installed or client tools only. Can I determine this finding registry keys?

take a look here :

  HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLServer
  HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server

SQL Server 2008 R2

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLNCLI10\CurrentVersionSQL

SQL Server 2008

SQL Server Native Client 10 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLNCLI10\CurrentVersion

SQL Server 2005

SQL Server Native Client 9 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Native Client\CurrentVersion


Tools for working with xtrabackup files? I have database backups in xtrabackup format. I would like to load them into hive without restoring to a mysql instance and use sqoop to then do the transfer.

If there are libraries to work with the format I can probably write my own utility to load the data into hive. I'm also more than happy to receive other suggestions on how to do that.

There are tools to extract data from XtraBackup data files. But don't forget about a redo log. Without it the Xtrabackup copy is incomplete. To make the backup usable you have to apply the redo log which means you need to restore the backup.

I would look at mydumper or mysqldump with -T option and then upload the dumps to hive.


Displaying the order of the columns of a table I created a table, and want to find the display the order of its columns.
Should I use the following query to display the info ordered by column_id?

select * from sys.columns c
where c.object_id = object_id('Customer')
order by column_id

create table dbo.Customer
(
    CustomerId int primary key,
    CustomerName varchar(255),
    CustomerAddress varchar(255),
    EnrollmentDate date
)

Reading Microsoft SQL Server documentation, I am seeing the information below, so want to be sure:

Column name  Data type  Description
-----------  ---------  ----------------------------------------------
column_id:   int        ID of the column. Is unique within the object.
                        Column IDs might not be sequential.

column_id is a reasonable proxy for the column ordinal, since it is impossible to insert a column between two existing columns in SQL Server without dropping and recreating the table.

As the documentation states, column_id values may not be sequential if you drop a column from a table.

You can also make use of the COLUMNPROPERTY() function to return the actual ordinal for each column.

Consider a quick example:

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
CREATE TABLE dbo.t
(
    c1 int
    , c2 int
    , c3 int
    , c4 int
);

ALTER TABLE dbo.t DROP COLUMN c1;
ALTER TABLE dbo.t ADD c5 int;
ALTER TABLE dbo.t ALTER COLUMN c2 char(3);

SELECT o.name
    , c.name
    , c.column_id
    , ordinal = COLUMNPROPERTY(c.object_id, c.name, 'ordinal')
FROM sys.columns c
    INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = N't'

The output looks like:

╔══════╦══════╦═══════════╦═════════╗
║ name ║ name ║ column_id ║ ordinal ║
╠══════╬══════╬═══════════╬═════════╣
║ t    ║ c2   ║         2 ║       1 ║
║ t    ║ c3   ║         3 ║       2 ║
║ t    ║ c4   ║         4 ║       3 ║
║ t    ║ c5   ║         5 ║       4 ║
╚══════╩══════╩═══════════╩═════════╝


Delete Slow Query Log File I was logging slow queries of my system. Now I have few queries to be optimized and reset the global slow_query_log variable to 0. Now I want to delete slow query log file mysqld-slow.log. can anyone tell me what is the standard way to do this ?

I am using Cent OS and do not want to delete or affect other log files (i.e. general log or binary log)
Thanks.

As of 5.5.3, FLUSH LOGS will close and reopen the slowlog.  (In old versions, FLUSH had no effect on the slowlog.)

So, on *nix OS, this should work without restarting the server:

rm (to delete) or mv the slowlog to another name.  (Note: mysqld will continue to write to the file, even though you changed the name.)
FLUSH LOGS;.  As of 5.5.3, you can limit the effect via FLUSH SLOW LOGS;

See http://bugs.mysql.com/bug.php?id=14104 and http://bugs.mysql.com/bug.php?id=60878

I would not turn off the slowlog -- next month someone will add a naughty query and you will want to know about it.


Have full SQL Server restore but losing 1 day of data I have SQL 2008 R2 64bit and it is backed up with ASIGRA SQL backup. The issue I have is that we had corruption on the server and the DB got corrupted yet the LDF file is in tact and does have all the changes since the last backup.

So I can easily restore the backup, but Can I  then implement the changes logged in the LDF file so I can get back the day of changes we lost.

First things first, before you restore anything - back up your log with no_truncate.

If your database is offline, then take a copy of your LDF file first, then bring your database online, ignoring any errors, and then do your log backup.

This log backup is then the last one in your recovery process, which involves a full database backup which doesn't have corruption, and all the log backups since.

But also, consider what kind of corruption it is. If it's in a non-clustered index only, then you could drop and recreate the index and remove your corruption that way.


Trigger - If time between? I wish to use a mysql trigger that will update a column called peak to 0 or 1 based on if the CURTIME() is between 08:01 & 11:59.

Can anyone help with this trigger?

You need something like that:

BEGIN
  IF CURTIME() BETWEEN '08:00:00' AND '11:59:59'
    THEN UPDATE ...
  END IF
END

This trigger will be activated each time the event occure but it will do something only if current time is within the given range and will do nothing otherwise.


Snapshot Backups for SQL Server Databases We are researching the direction of using Snapshot backups for SQL Server, rather than more traditional backup methods. We are using CommVault (and would be for snapshot too..)

I am trying to understand, better, how they work compared to traditional backups, or agent backups using CommVault. As I am not really a storage guy, I don't quite understand how the restore processes work from a snapshot backup for a SQL Database.

I do understand the absolute speed benefit, but would like to hear more about the pro's and con's of utilizing snapshot backups for SQL Server.

Cons:

necessitates installing the add-on for Commvault prior to running a restore.  In an emergency, this can add a brutal layer of indirection.
prevents piece-meal-restores of individual file groups, since the entire database must be restored to be consistent.
You need to know how to use Commvault, instead of just the built-in tools.
backup history is obfuscated inside SQL Server, such that you only see a guid as the target of a backup.

Pros:

you may get better compression of the backup file
storage guys feel like they're managing backups (maybe a con, quite frankly)
backup process may be faster.

There are undoubtedly a metric ton of other pros and cons I'm not thinking of.

Personally, as a DBA, I'd way rather use SQL Server native backups.

An exercise that might help you decide on an appropriate methodology might be to get the business folks to thoroughly evaluate their recovery point objective and recovery time objective.

If they can afford to lose 24 hours of data in those databases, probably nightly backups are ok.  If you can't afford to lose more than, say, 15 minutes of data, you'll need 15 minute log backups whenever data is being written to the database as well as full backups.  If the VDI/VSS backup solution doesn't offer log backups, or they can't afford the space for frequent full backups, then you know you cannot use that kind of system.  As for recovery time objective, if they can put up with a week of downtime after a disaster occurs, then you're probably ok to go with a VDI/VSS-based backup system.


Translating MySQL query to PostgreSQL I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error.

MySQL:

SELECT ticket.queue_id, queue.name, article.create_time,
COUNT(article.id)
FROM article
JOIN ticket
JOIN queue
ON article.ticket_id=ticket.id
and ticket.queue_id=queue.id
GROUP BY ticket.queue_id

Postgres:

SELECT ticket.queue_id, queue.name, article.create_time,
sum(article.id) FROM article
JOIN ticket
JOIN queue
ON article.ticket_id=ticket.id
AND ticket.queue_id=queue.id
GROUP BY ticket.queue_id;

The error:

ERROR:  syntax error at or near "GROUP"
LINE 7: GROUP BY ticket.queue_id;
         ^

I'm quite at a loss here, as I can't really pinpoint my error.
The same error appears when I leave out the 'sum' statement in the query.

Any help much appreciated.

You must group by all of the columns i the select clause:

GROUP BY ticket.queue_id, queue.name, article.create_time

or add aggregate functions such as MAX:

SELECT ticket.queue_id, MAX(queue.name), MAX(article.create_time),
       COUNT(article.id)
FROM article
JOIN ticket
    ON article.ticket_id=ticket.id
JOIN queue
    ON ticket.queue_id=queue.id
GROUP BY ticket.queue_id

I also moved the ON clause.

SQL92 required a full group by, SQL99 loosened this and requires that all columns in the select clause should be functionally dependent of the group by clause. AFAIK, all vendors kept the stricter SQL92 definition.

You can make MySQL more compliant with other vendors by adding ONLY_FULL_GROUP_BY to @@sql_mode. By default MySQL will not complain if you "under specify" the group by, instead it will give you a random row. Example:

create table t (x int, y int, z int);
insert into t (x,y,z) values (1,1,1),(1,2,1);

select x,y,count(*) from t group by x;
+------+------+----------+
| x    | y    | count(*) |
+------+------+----------+
|    1 |    1 |        2 |
+------+------+----------+
1 row in set (0.00 sec)

set @@sql_mode = 'ONLY_FULL_GROUP_BY'

select x,y,count(*) from t group by x;
ERROR 1055 (42000): 'mysql.t.y' isn't in GROUP BY

There is also a bunch of other settings (PIPES_AS_CONCAT for example) that can be added to @@sql_info to make MySQL more compliant.


Need advice regarding RMAN's working I have configured a RMAN script which first takes the backup of the database and then deletes the obsolete backupsets according to the present retention policy.Now is there any possibility of the given scenario

Scenario: The backup process starts and failed abruptly dues to some reason creating incomplete backupset.Then the backup deletion part of script runs and deletes the latest full/working backup(not the one that failed but the one which the script created on the previous run)of the database provided that the retention policy is redundancy 1.Hence I lose the only available/working backup of my database.

Can it happen?

It will only delete the earlier backup if the current one is successful.
You can try doing a full backup, cancel it and do list backup.


MySQL CURDATE() and CURRENT_TIMESTAMP return different value? I am facing a little confusion because of CURRENT_DATE value and CURRENT_TIMESTAMP value shows different date on MySQL.

Scenario :
1) User calls API from Lumen backend to insert data on a table. One column used CURRENT_DATE (sales_date) as insert value and one column use CURRENT_TIMESTAMP (temp_timestamp) as default value

2) Query exactly looks like this :

$_reg = DB::insert("INSERT INTO t_sales(sales_no,ref_no, sales_date,customer_id,branch_id,user_id,sales_info) VALUES(?,?,curdate(),?,?,?,?)", [$a, $b, $cid, $branch, $uid,$info]);

3) Querying the result would give different value. Date column shows '2020-01-09' AND TIMESTAMP column shows '2020-01-10 06:44:33' (as per date insert.. I am on GMT+0700 timezone)

4) weirder results, if I log into mysql using CLI or SQLYog from my PC and issue SELECT CURRENT_DATE, CURRENT_TIMESTAMP both shows correct date '2020-01-10'

Environment :

Lumen backend located on same physical machine as MySQL backend
Lumen explicitely set timezone to Asia/Jakarta (which shouldn't matter as it is in PHP)
MySQL version is 8.0 with time_zone set to SYSTEM on my.cnf
System use Ubuntu 18.04 server with tzdata set to Asia/Jakarta (UTC+7). timedatectl shown like this as per now (of course when I insert earlier it shows 2020-01-09 for Universal Time and RTC time)

Local time: Fri 2020-01-10 06:33:37 WIB
Universal time: Fri 2020-01-10 01:33:37 UTC
RTC time: Fri 2020-01-10 01:33:38
Time zone: Asia/Jakarta (WIB, +0700)
System clock synchronized: yes
systemd-timesyncd.service active: yes
RTC in local TZ: no

Why CURRENT_DATE and CURRENT_TIMESTAMP have different value?

Why CURRENT_DATE and CURRENT_TIMESTAMP have different value?

They returns values which have different datatypes.

MySQL 8.0 Reference Manual  /  Functions and Operators  /  Date and Time Functions

  CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

  CURDATE()

  Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in string or numeric context.

  CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().

  NOW([fsp])

  Returns the current date and time as a value in 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format, depending on whether the function is used in string or numeric context. The value is expressed in the session time zone.

  If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.

  NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.


psql \watch implementation for MariaDB? Repeatedly execute the current query Quote from current Postgres manual

  \watch [ seconds ]
  Repeatedly execute the current query buffer (as \g
  does) until interrupted or the query fails. Wait the specified number
  of seconds (default 2) between executions. Each query result is
  displayed with a header that includes the \pset title string (if any),
  the time as of query start, and the delay interval.

Is there some similar functionality for MariaDB or MySQL?

For example this statement I wish to repeat every 2 seconds. In Linux it's the watch command

NAME
       watch - execute a program periodically, showing output fullscreen

run statement till state changes.

SELECT * FROM TABLE WHERE state = retry

Not quite what you want, but this shell command might suffice:

$ mysql ... -e "SELECT * FROM TABLE WHERE state = retry"


What would a schema look like for setting up tables for RPG character creation restrictions? I'm trying to figure out how to layout tables for an RPG database where information such as character race, class, and deity have restrictions on what combination is permissible.
For example:
Race
-----------
id | name
1  | Human
2  | Elf
3  | Dwarf

Class
-----------
id | name
1  | Warrior
2  | Wizard
3  | Thief

Deity
-----------
id | name
1  | GodA
2  | GodB
3  | GodC

I want to limit certain combinations such as only humans and elves can be wizards, only humans and dwarves can be warriors, but all three can be thieves. But I also want to restrict the deity to certain race/class combinations. For example, humans can only worship GodA or GodB but NOT GodC; choosing to be a thief means you can't worship GodB so you end up only being able to choose GodA, etc.
What kind of schema can be used to map this information, is there a name for this type of relational data, and what would a query look like to retrieve the final mapping?

There's not a specific schema name for what you're trying to achieve but there are two ways you can go about achieving it depending on which database management system you end up using.

You can create one linking table for each combination of values. Every legal combination will get it's own row, and have a foreign key field of the id of each base table that it's linking together. This is generally known as a dimension combination table.

You can alternatively look into having a more denormalized table that stores each instance of the character that's been selected, with foreign key refencing fields to each id of the base tables, with a composite constraint on what value combinations can be entered in each field. (The availability of this kind of complex constraint feature will be dependent on the database system you use.)


Using WHERE / AND in Joins Can someone elaborate on how the following two queries would provide different results?

That is, how the query is processed differently using WHERE or AND.

select
    orders.*,
    accounts.*
from
    orders
left join
    accounts
    on orders.account_id = accounts.account_id
where
    accounts.sales_rep_id = 321500;

select
    orders.*,
    accounts.*
from
    orders
left join
    accounts
    on orders.account_id = accounts.account_id
    and accounts.sales_rep_id = 321500;

As Lennart has suggested, use a minimal example and see what happens.

create table accounts(account_id int primary key, sales_rep_id int);

create table orders(order_id int primary key, account_id int);

insert into accounts values
(1, 321500),
(2, null),
(3, 2000),
(4, 3000);

insert into orders values
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 2),
(6, 3);

There is a LEFT JOIN involved, that means it returns always all rows of orders table, plus those rows of accounts table with the same account_id. But, the WHERE clause filters final result to those with sales_rep_id = 321500.

order_id | account_id | account_id | sales_rep_id
-------: | ---------: | ---------: | -----------:
       1 |          1 |          1 |       321500
       2 |          1 |          1 |       321500

However the second query returns all rows of orders table, but only accounts with the same account_id and sales_rep_id = 321500.

order_id | account_id | account_id | sales_rep_id
-------: | ---------: | ---------: | -----------:
       1 |          1 |          1 |       321500
       2 |          1 |          1 |       321500
       3 |          2 |       null |         null
       4 |          2 |       null |         null
       5 |          2 |       null |         null
       6 |          3 |       null |         null

db<>fiddle here


SQL Server Mirroring vs 3rd party replication products such as DoubleTake I'm charged with looking after several SQL Server instances, most of which use SQL Server mirroring to replicate data for DR purposes.

I automatically script logins, roles & jobs to the mirror, in the event of a failover we literally need to run a script to bring the mirrors online, apply the logins, apply the rolls and jobs and away they go (give or take a few mismatched SIDs & the usual niggles).

Now, I have documented this failover process in case I am not on site in the event of a failure; however, management deems the process to be too complicated and keeps trying to push 3rd party replication products such as DoubleTake that could do instance level replication.

(Company did not want to use clustering for instance level as the shared storage would be a single point of failure – they were not willing to buy multiple replicating SANs.)

We have databases ranging from a few meg to 200GB+.

Is DoubleTake really practical for DR in this manner? Can I trust that the data will be bang up to date between the primary and secondary? (We operate high safety mode mirroring for this very reason.)

I've read lots of opinions on forums, but always either biased or unconfirmed; I'm after some first-hand experience with these products.

SQL Server's native database mirroring includes something really slick: automatic page repair.  If the principle detects a corrupt page on disk, it fetches a clean copy of the page from the mirror automatically without taking the database down.  You don't get that level of database repair with any third party product.  That database awareness is one of the reasons I prefer the native tools - well, that and I just don't trust third party filter drivers on database servers.  I've seen too many blue screens of death due to third party filter driver bugs.

Bad news: database mirroring is deprecated in SQL 2012.  This means it's still around for this version, but will be removed in the future.

Fantastic news: SQL Server 2012 introduced AlwaysOn Availability Groups to replace mirroring, and they're awesome.  You can actually query or back up from the mirror, and you get up to four mirrors.  Some can be synch and some can be asynch.  This gives you all kinds of flexibility over database mirroring, and it blows away the third party tools.


Non numeric attributes in fact table (to track data source)? I am creating a data warehouse using star schemas, following the Kimball methodology. One of the rules is only put numeric values in fact tables.

I am considering putting an "id" in a fact table, in order to track the source of the data that the fact was inserted from. Users may upload data in a variety of ways, I am required to support a use case where a user has made a mistake in their data upload and wishes to redact it. I am considering therefore putting job and row ids on all uploaded data and keeping the raw data uploaded (prior to ETL from a staging area). This way the users will be able to see a log of uploads, and can redact them and replace with correct data if need be.

Is it really bad to put these ids in the fact table? An alternative would be to put them in a special tracking dimension. However, this dimension will have one row per fact table row, so does it not make sense to put them in the fact table?

I will typically have my business key in the fact table so I can easily track back to the source system for any questions. I usually will put a unique constraint on it to ensure the granularity of the fact table is the same as the business key.


PostgreSQL function not executed when called from inside CTE Just hoping to confirm my observation and get an explanation about why this is happening.

I have a function defined as:

CREATE OR REPLACE FUNCTION "public"."__post_users_id_coin" ("coins" integer, "userid" integer) RETURNS TABLE (id integer) AS '
UPDATE
users
SET
coin = coin + coins
WHERE
userid = users.id
RETURNING
users.id' LANGUAGE "sql" COST 100 ROWS 1000
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER

When I call this function from a CTE, it executes the SQL command but does not trigger the function, for example:

WITH test AS
(SELECT * FROM __post_users_id_coin(10, 1))

SELECT
1 -- Select 1 but update not performed

On the other hand, if I call the function from a CTE and then select the result of the CTE (or call the function directly without CTE) it executes the SQL command and does trigger the function, for example:

WITH test AS
(SELECT * FROM __post_users_id_coin(10, 1))

SELECT
*
FROM
test -- Select result and update performed

or

SELECT * FROM __post_users_id_coin(10,1)

Since I don't really care about the result of the function (just need it to perform the update) is there any way of getting this to work without selecting the result of the CTE?

That's kind of expected behaviour. CTEs are materialized but there is an exception.

If a CTE is not referenced in the parent query then it is not materialized at all. You can try this for example and it will run fine:

WITH not_executed AS (SELECT 1/0),
     executed AS (SELECT 1)
SELECT * FROM executed ;

Code copied from a comment in Craig Ringer's blog post:
PostgreSQL’s CTEs are optimization fences.

Before trying out this and several similar queries, I thought that the exception was: "when a CTE is not referenced in the parent query or in another CTE and doesn't reference itself another CTE". So, if you wanted the CTE to be executed but the results not shown in the query result, I thought this would be a workaround it (referencing it in another CTE).

But alas, it doesn't work as I expected:

WITH test AS
    (SELECT * FROM __post_users_id_coin(10, 1)),
  execute_test AS
    (TABLE test)
SELECT 1 ;     -- no, it doesn't do the update

and therefore, my "exception rule" is not correct. When a CTE is referenced by another CTE and none of them is referenced by the parent query, the situation is more complicated and I'm not sure exactly what happens and when the CTEs are materialized. I can't find any reference for such cases in the documentation either.

I don't see any better solution than using what you already suggested:

SELECT * FROM __post_users_id_coin(10, 1) ;

or:

WITH test AS
    (SELECT * FROM __post_users_id_coin(10, 1))
SELECT *
FROM test ;

If the function updates multiple rows and you get many rows (with 1) in the result, you could aggregate to get a single row:

SELECT MAX(1) AS result FROM __post_users_id_coin(10, 1) ;

but I'd prefer to have the results of the function that does an update returned, with SELECT * as your example, so whatever calls this query knows if there were updates and what the changes in the table were.


Oracle "Import from Export Files" screen refusing both SYSADMIN and Normal users I have just installed Oracle 11g on Win64, and I want to import some .dmp files.

I am trying with the tool Oracle Enterprise Manager 11g:

If I log in with SYS as SYSADMIN, and go to Import from Export Files, I am told Role Error. Export and Import are not supported on 10g databases for users logged in with the SYSDBA role. Logout and login using a different role before trying again.
If I log in with SYSMAN as Normal, the Import from Export Files screen shows no error, but when I click Create Directory Object then OK, I get: Errors. Failed to commit: ORA-01031: insufficient privileges. You are not logged in with SYSDBA privilege. You cannot assign privileges to directory object.

I tried creating a "Directory Object" as Normal, but unfortunately it is not visible under SYSADMIN.

How can I use this Import from Export Files screen ?

The SYSMAN account is for adminstering Enterprise Manager, rather then the database; see the predefined user accounts. I would shy away from modifying anything about any of those accounts, even granting an additional role, unless specifically told to by Oracle.

The first message you got says that import isn't supported when logged in with the SYSDBA role. That doesn't mean you can't import as SYS, just that you have to log in as SYS with the NORMAL role rather than as SYSDBA.

This assumes you want to do a full import though; for partial (e.g. schema-level) imports you might want to create the user first and perform the import as that user, but I'm not sure that's what you're looking for at the moment.


Oracle: finding rows without children in a table with a foreign key? Suppose I have two tables with a foreign key relationship.  How can I find the rows in the "parent" table for which there is no corresponding "child" row?

For example:

create table a(x number primary key);
create table b(x number);
alter table b add constraint b_fk foreign key(x) references a(x) enable;

insert into a values(1);
insert into b values(1);
insert into a values(2);
commit;

delete from a where x = 2; -- will succeed
delete from a where x = 1; -- will fail, because there is a child row.

select ??? as "rows with no children" from a;

rows with no children
---------------------
                    2

A semantically appropriate way of expressing this would be:

select ...
from   a
where  not exists (
         select null
         from   b
         where  b.x = a.x)

NOT EXISTS uses an anti-semi-join which allows the optimiser to be very responsive to the table and join cardinalities.