Does mysqld --ndb-cluster need to be run on MySQL Cluster data nodes? I have my management node and sql node on the same server, and 2 data nodes (3 machines in total). Do I need to run ./mysqld --ndb-cluster (or /etc/init.d/mysql-server start) on 1) the data nodes, or 2) on the sql node, or 3) on both?

I am following this which doesn't seem to specify.
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-install-first-start.html

Thanks.

There are three types of nodes

SQL
Storage (Data)
Management

According to MySQL Clustering, page 7 paragraphs 3,4 say:

  Storage Nodes

  Stroage nodes store the fragments of data that make up the tables held in a cluster and the early work in processing queries. Storage nodes require a large amount of RAM and relatively high-performance machines. The bulk of the processing is done on the storage nodes., and these are the nodes that an administrator spends most of his or her time tweaking. They are completetly controlled the the management node via the configuration file and management client.

  SQL Nodes

  SQL nodes, which run on standard MySQL servers, are the nodes that
  applications can connect to, In small simple clsuters, these nodes are
  often run on the same physical servers as the storage nodes.
  Essentially, SQL nodes provides the "face" to the cluster and operate
  exactly as standard MySQL servers. They then connect to the storage
  nodes behind, which do the early processing on the queries and return the
  results for final processing at the SQL nodes. The amount of
  processing done on the SQL and storage nodes changes form query to
  query.

Based on this, you could run mysqld on a Storage Node if the dataset is small. You are not obligated to do so. It is preferable if you had a separate Storage Node and a separate SQL node. If you have separate Storage and SQL nodes, then do not run mysqld on the Storage Node.


INNER JOIN Giving time out on large database Getting time out on this script

UPDATE
    uk_data AS ud
  INNER JOIN
    uk_pc AS up
        ON ud.cat10 = up.WardCode
SET
    ud.cat8 = up.Latitude,
    ud.cat9 = up.Longitude;

uk_pc Table has 1,755,213 entries and uk_data has 24,510 entries.

Is there any other way to do this where I don't get time out?

I want to update uk_data cat8 and cat9 with uk_pc Latitude and Longitude.

Table1:  uk_pc
    Latitude
    Longitude
    WardCode

Table2:  uk_data
    cat8
    cat9
    cat10

in both tables WardCode and cat10 have same value.

Table Definitions

Table1 uk_data

CREATE TABLE IF NOT EXISTS `uk_data` (
  `slno` int(100) NOT NULL AUTO_INCREMENT,
  `comp_name` varchar(150) DEFAULT NULL,
  `comp_no` varchar(50) DEFAULT NULL,
  `comp_street` varchar(100) DEFAULT NULL,
  `comp_area` varchar(100) DEFAULT NULL,
  `comp_post_code` varchar(50) DEFAULT NULL,
  `comp_phone` varchar(100) DEFAULT NULL,
  `comp_phone1` varchar(100) DEFAULT NULL,
  `cat1` varchar(100) DEFAULT NULL,
  `cat2` varchar(100) DEFAULT NULL,
  `cat3` varchar(100) DEFAULT NULL,
  `cat4` varchar(100) DEFAULT NULL,
  `cat5` varchar(100) DEFAULT NULL,
  `cat6` varchar(100) DEFAULT NULL,
  `cat7` varchar(100) DEFAULT NULL,
  `cat8` varchar(100) DEFAULT NULL,
  `cat9` varchar(100) DEFAULT NULL,
  `cat10` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`slno`),
  UNIQUE KEY `Phone` (`comp_phone`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31717 ;

Table2(uk_pc)

    DROP TABLE IF EXISTS `uk_pc`;
CREATE TABLE `uk_pc` (
  `slno` int(11) NOT NULL AUTO_INCREMENT,
  `Postcode` varchar(25) DEFAULT NULL,
  `Latitude
Latitude` decimal(9,6) DEFAULT NULL,
  `Longitude` decimal(9,6) DEFAULT NULL,
  `Easting` varchar(25) DEFAULT NULL,
  `Northing` varchar(25) DEFAULT NULL,
  `GridRef` varchar(25) DEFAULT NULL,
  `County` varchar(25) DEFAULT NULL,
  `District` varchar(25) DEFAULT NULL,
  `Ward` varchar(25) DEFAULT NULL,
  `DistrictCode` varchar(25) DEFAULT NULL,
  `WardCode` varchar(15) DEFAULT NULL,
  `Country` varchar(25) DEFAULT NULL,
  `CountyCode` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`slno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

At the very least, you should have an index on WardCode on uk_pc.  If you also add Latitude and Longitude it will become covering for this query and so the table will not need to be used at all.

Your datatypes aren't really the most efficient, so I'd definitely revisit your table designs.  narrower columns (Latitude and Longitude are better off as decimal and would never be 100 characters long) will store better in the tables (and of course in the indexes as well).  The width of varchar columns might seem to be something you don't neet to worry about, but it does affect what the database engine sets aside while performing queries since it does not know if it will encounter larger strings in the data.  In general, limiting the working set like this can only help, especially when it is easily foreseeable.


Transaction Rollback on DROP DATABASE Does MySQL use transactions for DROP DATABASE... statement (all tables use InnoDB engine)?

Basically I would like to know if any data will be lost if I kill DROP DATABASE... query while it's running?

I've tested this with tables and it works but I don't have big enough database to test it:

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (8.57 sec)

mysql> DELETE FROM t1;

Another MySQL session:

mysql> SHOW PROCESSLIST;
+-----+------+-----------+------+---------+------+----------+-------------------+-----------+---------------+
| Id  | User | Host      | db   | Command | Time | State    | Info              | Rows_sent | Rows_examined |
+-----+------+-----------+------+---------+------+----------+-------------------+-----------+---------------+
| 211 | root | localhost | test | Query   |    0 | init     | SHOW PROCESSLIST  |         0 |             0 |
| 242 | root | localhost | test | Query   |    4 | updating | DELETE FROM t1    |         0 |        879266 |
+-----+------+-----------+------+---------+------+----------+-------------------+-----------+---------------+
2 rows in set (0.00 sec)

mysql> KILL 242;
Query OK, 0 rows affected (0.00 sec)

back to first session

mysql> SELECT COUNT(*) FROM t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    283
Current database: test

+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (6.91 sec)

There are several SQL statements you cannot use ROLLBACK  as MySQL Implicitly Commits those Statements such as:

CREATE / ALTER / DROP DATABASE
CREATE /ALTER / DROP / RENAME / TRUNCATE TABLE
CREATE / DROP INDEX
CREATE / DROP EVENT
CREATE / DROP FUNCTION
CREATE / DROP PROCEDURE

For Such Statements it doesn't matter weather you are using explicit Commit or not the statement will Commit implicitly ass soon as you execute it.


Domain Admin on AWS lacking user rights to edit the top 200 using the Management Studio interface I setup an application + SQL Server on AWS. Everything works fine my SQL User can read/write to any table.

Now I connected to the SQL Server using my domain admin account using windows authentication in SQL Server. It works fine and I can insert / update / delete rows. But as soon as I try to edit a table using the interface, meaning select "Edit Top 200 Rows" I get the following error:

The requested operation cannot be completed. The computer must be
trusted for delegation and the current user account must be configured to
allow delegation.

In the local Security Policy I have enabled domain Admins and also my exact account to the following and also all admins:

Enable computer and user Accounts to be trusted for delegation

What else am I missing? I use SQL Server 2014 Standart on Windows Server 2012

OK, I think the Work Around mentioned at serverfault.com/questions/696804/... is a very valid answer so I am listing it directly here.  I have the exact same issue in Microsoft Azure (editing a table SQL Management Studio).

I fixed it by setting DWORD regkey ProtectionPolicy to 1 for the sub-key HKEY_LOCAL_MACHINE\Software\Microsoft\Cryptography\Protect\Providers\df9d8cd0-1501-11d1-8c7a-00c04fc297eb


Striping IO with datafiles on different physical mounts I have a server with three directly attached disk controllers. Each of these can provide internal redundancy and IO-striping accross disks, but I'm wondering what is the best way to configure the whole system.

Would it be sensible to allocate three datafiles for each tablespace, each datafile being created in a mount provided by a single controller? Oracle seems to distribute data somewhat evenly accross datafiles when doing inserts, but would this be enough striping for queries?

We are mostly concerned about read performance for single aggregate queries (data warehouse) and want to read data in parallel from each controller.

One answer would be to use ASM or LVM to do the striping, Oracle would then see only one mount point where to store datafiles. We do have a configuration with ASM + number of JBOD-disks; it does perform reasonably well, but ASM has it's own issues and we are considering alternatives.

Your plan of adding three datafiles on different mounts will not work well. Oracle doesn't stripe optimally within tablespaces. It just allocates extents in a reasonably efficient manner. The method you suggested is striped better than one disk, but it's not even close to optimal. Basically, you can't have really good striping without ASM or an external disk management system.

The other problem is that having three independently redundant arrays is either going to waste a lot of disk space or create three seperate points of failure. Think about it - if you are keeping one copy of each data block in each controller, then within that controller you will have multiple copies of the data again. You'll end up with 4, 6 or 9 copies of each block. That's a massive waste of resources. Conversely, without redundancy across controllers you are turning each controller into an SPOF (Single Point Of Failure).

Essentially you have two options:

Let Oracle manage the RAID functionality entirely. Turn off redundancy in the three controllers, set up ASM with normal/high redundancy. Create your diskgroups using an equal number of disks from each controller. Create three failure groups, one for each controller. This will achieve the redundancy and striping that you want.
Manage the redundancy entirely without Oracle. Use LVM or similar system to present a completely redundant, appropriately striped disk to Oracle. This will probably also involve turning off redundancy within each controller, and letting LVM manage the redundancy across controllers. The result is the same.

TL:DR - Your redundancy across disks within each controller is pointless when you have multiple controllers. Disable it and create redundancy across controllers instead.


Restoring a Database in an Availability Group I located an extremely helpful process outlined in the thread below, but needed some clarification. This may be a silly question, but I'm a bit new to Availability Groups. In the steps outlined in the thread's answer, is it true that after restoring, you only have to add the newly restored database into the secondary? Will it appear back into the primary automatically? Thank you for any assistance!

Restoring a SQL Server 2012 database in high availability

Yes, you need to populate databases on secondary nodes in an availability group yourself, they do not appear "automatically".  The full documentation is found on MSDN, with multiple ways to execute.  The short version is:

Your primary database needs to be in FULL recovery mode.
You must restore the database in NORECOVERY mode on the secondary node. Logs must be applied so that the secondary database is as close to the current transaction state of the primary as possible.
Once you have done this, you need to join the secondary database to the availability group: ALTER DATABASE [foo] SET HADR AVAILABILITY GROUP = [bar];


SQL Server 2012 - cannot login Just installed a single instance of SQL Server 2012. The instance name is called Example and the server is called WIN2K8R2. It's on a virtual machine and I am RDP'd into it. I'm trying to login with SQL Server Management Studio.

When trying to login, I get this error.

A network-related or instance-specific error occured while establishing a connection to the SQL server.

All SQL Server Services are 'running'. It's a clean build of Win2k8 R2.

Server Type: Database Engine
Server Name: WIN2k8R2\Example
Auth: Windows Auth

Resolved.

1) Deleted VM.
2) Spun up a new VM.
3) Installed SQL Server 2012

Able to login to the database from RDP.

During installation you were prompted to add Windows users to the SQL instance. Did you add any? Unless you added yourself as a SQL user you won't be able to connect.

if you added Administrators as SQL users then you must run SSMS w/o the LUA token restriction, in other words you must right click the launch icon and select "run as Administrator"
if you did not add any user as  SQL user then you must follow the steps described in Connect to SQL Server When System Administrators Are Locked Out
if you don't know what you did and cannot follow the steps mentioned above, discard the VM and install SQL again. Pay attention to the installation procedure and make sure you add yourself as a valid SQL user. Read Database Engine Configuration - Account Provisioning for more details.

  All SQL Server Services are 'running'.

Make sure SQL Server Browser Service is running.


missing WAL file on standby server following a backup I seem to have an intermittent problem with my postgres backup/restore to standby

Postgres, is version 9.2 running on red hat 6 enterprise

i have a production server (PROD1) and a hot standby (STANDBY1), on sunday evening i have a script which puts
PROD1 into backup mode, performs a tar on the $PGDATA directory, copies the file to STANDBY1, a script on STANDBY1
is then called to stop postgres on STANDBY1, unpack the tar, and start postgres again.

In the meantime the archive_command on PROD1 is continuing to copy WAL files over the STANDBY1, so in theory STANDBY1
will be brought up to date. Then for the remainder of the week the WAL files are applied until the next Sunday when the
process starts again.

code extract for backup script on PROD1

psql -X -U postgres --set ON_ERROR_STOP=on <<PSQL
select pg_start_backup('${WEEK_START}_DataBackup','true');
PSQL

tar -zcf $WEEKLYBACKUP_DIRECTORY/full_backup_${WEEK_START}.tar.gz --exclude=$PGDATA/pg_xlog/* --exclude=$PGDATA/pg_xlogArchive/*
--exclude=$PGDATA/pg_xlogRestored --exclude=$PGADTA/postmaster.pid --exclude=$PGDATA/postmaster.opts $PGDATA/

psql -X -U postgres --set ON_ERROR_STOP=on <<PSQL
select pg_stop_backup();
PSQL

scp -p $WEEKLYBACKUP_DIRECTORY/full_backup_${WEEK_START}.tar.gz $DR_SERVER:$WEEKLYBACKUP_DIRECTORY/.

ssh -T $DR_SERVER <<EOF >> $MAILFILE
  cd /var/lib/pgsql/Scripts
  ./postgres-hotStandby $WEEK_START Y
EOF

code extract for hotstandby script on STANDBY1

pg_ctl stop -D $PGDATA -m i >> $LOGFILE
tar -zxf $WEEKLYBACKUP_DIRECTORY/full_backup_${RESTORE_WEEK}.tar.gz

# create a recovery file
echo "standby_mode = 'on'" >$PGDATA/recovery.conf
echo "primary_conninfo = 'host=${LIVE_SERVER}'" >>$PGDATA/recovery.conf
echo "restore_command = 'cp -f $PGDATA/pg_xlog/%f $PGDATA/pg_xlogRestored'" >>$PGDATA/recovery.conf
echo "trigger_file = '${PGDATA}/failover'" >>$PGDATA/recovery.conf
sed -i 's/#hot_standby = off/hot_standby = on/g' $PGDATA/postgresql.conf
pg_ctl -w -D $PGDATA start

the above process works every second time it runs and fails the other times, giving the message

  "psql: FATAL:  the database system is starting up"

when i try and
get into psql.

looking in the log file on STANDBY1, postgres is awaiting a WAL file which it says...

  cp: cannot stat
  `/vol2/postgres92/pgsql/data/pg_xlog/000000010000005200000003': No
  such file or directory   2015-04-24 00:10:40 EDT -LOG:  streaming
  replication successfully connected to primary   2015-04-24 00:10:40
  EDT -FATAL:  could not receive data from WAL stream: FATAL:  requested
  WAL segment 000000010000005200000003 has already been removed

when pg_start_backup is run, this creates a WAL file which is shipped to STANDBY1 (eg. 000000010000005200000003), and when pg_stop_backup is run this
creates a end of backup WAL (eg. 000000010000005200000003.00000020.backup), which is also correctly shipped to STANDBY1. But when the failure occurs
the pg_start_backup WAL seems to go missing on the STANDBY1 server.

hopefully this all makes some sort of sense to someone, any ideas what is going wrong? I seem unable to reproduce this on my test systems, and i'm thinking it
may be a timing issue as due to the size and location of PROD1 and STANDBY1, the scp between the servers takes 15mins.

thanks for any help you can give

This part generating recovery.conf is suspicious:

echo "restore_command = 'cp -f $PGDATA/pg_xlog/%f $PGDATA/pg_xlogRestored'" >>$PGDATA/recovery.conf

It seems that the source and destination of this restore_command are reversed.

Compare yours to the example from the doc in Continuous archiving:

restore_command = 'cp /mnt/server/archivedir/%f %p'

The restore must copy from the archive directory (which typically is outside $PGDATA and entirely under the DBA's control) into $PGDATA/pg_xlog (as expanded by %p).

I'm assuming that $PGDATA/pg_xlogRestored is your archive directory since I don't see what else it could be. Personally I'd place it outside of $PGDATA by principle but that's not mandatory.


Avamar SQL Agent running against live SQL Server databases We use a large company as our service provider.

The tech people from that service company are pushing very hard to install something called the Avamar SQL Agent which purportedly will be used to replace my old reliable full, differential, and transaction log backups.  I am told that it constantly monitors the mdf, ndf, and ldf files for any changes and then writes the changed items (pages, ???) somewhere (???).

I am all for new technology once it is proven but I am skeptical of this approach that I will no longer need to take SQL Server native compressed backups (again full, diff, and tlog).

So far I have taken the stand that I am not going to bless this adventure unless and until they can prove to me through testing that it does not foul up my mdf, ndf, and ldf files and that a restore is still possible.  Here again I don’t know what or who would do the restore per se… is it some derivation of SQL Server restore, some Avamar restore, some combination, etc.

Does anyone have any experience with this product?

Please ask them and also, as max pointed out, test whether such tool can provide point in time recovery. Test various scenarios and see if it matches your RPO and RTO. I am sure its not free of cost only go if it REALLY provides some thing more than what native SQL Server Tsql backup provides. I have not found any tool more reliable than TSQL built in code for backup.

Ask them,if possible, to do a POC (proof of concept) and show you how it would be better than native TSQL backup. You have to let them know that there is no point in choosing any thing blindly if it does same work as a tool which you have free( inbuilt). You can professionally ask them above questions. Plus if you use such tool you might need to hire a person who is expert in this tool so might be some more extra cost.

Think all pros an cons and then move


Order by inside subquery Suppose that i select into a temptable as follow by performing grouping from an existing table, and this is current temptable value:

Title Count
-------------
Sr.   11
Mrs.  2
Sra.  3
Ms.   415
Mr.   578

What I want to achieve is a comma separated value for title as follow, which is order by count

[Mrs.], [Sra.], [Sr.], [Ms.], [Mr.]

However, currently i can only able to achieve a comma separated value as follow, which is the default ordering in #temptable

[Sr.], [Mrs.], [Sra.], [Ms.], [Mr.]

Below is my code. How could i achieve what i wanted?
Note: I don't assume nor expect the sequence is already in order when inserting into the #temptable

DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(Title)
FROM
   (
    SELECT Title FROM #tempTable1
   ) AS P

You can use STUFF function in this way:

DECLARE @Columns nvarchar(max);

SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Title)
                      FROM tempTable1
                      ORDER BY [Count]
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'), 1, 1, '');

SELECT @Columns;

| (No column name)                     |
| :----------------------------------- |
|  [Mrs.], [Sra.], [Sr.], [Ms.], [Mr.] |

db<>fiddle here

Alternatively and depending on your SQL-Server version you can use STRING_AGG function:

DECLARE @Columns nvarchar(max);

SELECT
    STRING_AGG(QUOTENAME(Title), ',') WITHIN GROUP (ORDER BY [Count])
FROM
    tempTable1;

SELECT @Columns;


Select last record from my table I am new to SQL and PHP. I'm having a problem with selecting the last record from my table. I run the following query:

SELECT * FROM 2521118 ORDER BY id DESC LIMIT 1

My table name is 2521118 and the fields are:

id (set for auto-incrementing)
timestamp
meter_readings

Here is my error:

  Error in query (1064): Syntax error near '2521118 ORDER BY id DESC LIMIT 1' at line 1

Can someone please help me out with this?

As explained in the documentation, there are rules on what makes a valid identifier. This rule is what affects your case:

  Identifiers may begin with a digit but unless quoted may not consist solely of digits.

So, as @ypercube says, you need to quote your table name, either with double quotes or backticks:

SELECT * FROM "2521118" ORDER BY id DESC LIMIT 1

or

SELECT * FROM `2521118` ORDER BY id DESC LIMIT 1


mysql large number of opened tables -- what does this mean and how can I reduce? I have 300 databases with a maximum of 59 tables each. My hosting provider says I have a lot of opened tables. I am not having any performance issues; but it is causing issues with backup (I use rackspace's cloud databases which uses Percona XtraBackup)

What can cause a lot of open tables and how can I decrease this? I don't get too much traffic and performance is good. I am not sure I understand exactly what opened tables is either.

I do have table_definition_cache set to 4096 as I wasn't able to run queries on views until I increased it.

mysql> SHOW GLOBAL STATUS LIKE 'Open_%';
mysql> +--------------------------+----------+
    -> | Variable_name            | Value    |
    -> +--------------------------+----------+
    -> | Open_files               | 7        |
    -> | Open_streams             | 0        |
    -> | Open_table_definitions   | 4102     |
    -> | Open_tables              | 4096     |
    -> | Opened_files             | 44025318 |
    -> | Opened_table_definitions | 1660409  |
    -> | Opened_tables            | 1857375  |
    -> +--------------------------+----------+

Here are the commands requested:

https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984

table_open_cache is the setting that is most likely to get the host off your back.

SHOW VARIABLES LIKE '%open%';  And how long had the system been up when the STATUS was captured?  (Need "per second", not absolute counts.)

Provide me with ram size, SHOW VARIABLES and SHOW GLOBAL STATUS; I will critique dozens of things.

Are any of the tables PARTITIONed?  (Each 'partition' is effectively a separate 'table'.)

Also, rethink your 300x59; those are moderately large numbers.

VARIABLES & STATUS Analysis

Observations:
Version: 5.6.17-log;
4 GB of RAM;
Uptime = 145d 22:45:14;
You are not running on Windows;
Running 64-bit version;
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

key_buffer_size -- lower to 20M; your 400M is wasting RAM.

table_open_cache seems to be doing fine.  So, I don't see the need in increasing open file limit.  (Nor is there any harm.)

A lot of table scans, etc.  Suggest you lower long_query_time to 1 and turn on the slow_query_log.  Then we can look in the slowlog for which queries need improvement -- either via composite indexes or other techniques.

expire_logs_days is currently 1.  That gie you only one day to discover that something is broken in replication and/or binlogs before you lose info.

Remove most or all OPTIMIZE TABLE calls.

Details and other observations

( Opened_tables ) = 0.15/second -- This is a reasonably low and quite respectable rate.  After all, you have been up for 20 weeks, and this is a 'counter'.  Point out the 20 weeks to the Hosting provider.  table_open_cache is number of entries in a cache for "Opened" tables.  Perhaps there is a flurry of opening during backup, but no opening the rest of the time?  As for "I do have table_definition_cache set to 4096 as I wasn't able to run queries on views until I increased it." -- Do your VIEWs touch hundreds or thousands of tables?  (Yikes!)

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (400M - 1.2 * 4180 * 1024) / 4096M = 9.6% -- Percent of RAM wasted in key_buffer.
-- Decrease key_buffer_size.

( Key_blocks_used * 1024 / key_buffer_size ) = 4,180 * 1024 / 400M = 1.0% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.

( innodb_buffer_pool_size / _ram ) = 2,306,867,200 / 4096M = 53.7% -- % of RAM used for InnoDB buffer_pool -- For only 4GB of RAM, this is probably OK, unless you have other applications in the same server.

( table_open_cache ) = 4,096 -- Number of table descriptors to cache
-- Several hundred is usually good.  However, you have a lot of tables, so, this may be OK, or even too small.

( table_open_cache_instances ) = 1 -- Suggest changing to 8.

Table_open_cache_overflows and _misses are each less than 0.1/sec. -- the table_open_cache is big enough.

( Open_tables / table_open_cache ) = 100% -- But, since you have been Up for several months, this is not necessarily bad.

( (Com_show_create_table + Com_show_fields) / Questions ) = (153314 + 9291657) / 543294681 = 1.7% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.

Query cache -- various STATUS values indicate that it is pretty good as it stands.

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 10,616,310 / (10616310 + 13112655) = 44.7% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( Com_rollback / Com_commit ) = 646,634 / 1329015 = 48.7% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic

( Select_scan ) = 16,557,535 / 12609914 = 1.3 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 16,557,535 / 57256550 = 28.9% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 1587 - 1526 ) / ( 1587 + 1526 ) = 2.0% -- Are you closing your prepared statements?
-- Add Closes.

( binlog_format ) = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( expire_logs_days ) = 1 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.

( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( max_connect_errors ) = 10,000 -- A small protection against hackers.
-- Perhaps no more than 200.

( Connections ) = 28,367,185 / 12609914 = 2.2 /sec -- Connections
-- Increase wait_timeout; use pooling?

( Max_used_connections / max_connections ) = 167 / 410 = 41% -- Suggest lowering max_connections to, say, 200.

( innodb_io_capacity_max ) = 800 -- This is rather low, but perhaps OK.

( report_port ) = 1 -- This is probably bad, but I have no experience to be more specific.  Most people leave it at 3306.

Com_create_db every 4 minutes and Com_drop_db every 2 minutes -- Very high; what's going on?

Com_optimize = 4.9/hour -- OPTIMIZE is virtually useless, especially on InnoDB.


PostgreSQL + PG_Monz (Zabbix) DB size too large > 1073741824 I an new to Postgres (9.3 on Ubuntu 14.04) monitoring and I have been using Zabbix/PG_monz.

I am seeing errors that my database has "too many temp bytes" and that my database is "too large". My actual backup files are 400 MB daily.

I am wondering if anyone is familiar with PG_Monz or just what this error might be about. Thank you in advance!

I found the answer with regards to how and why this is happening.
PG_Monz is using the following query to track temp bytes:

UserParameter=psql.db_temp_bytes[*],psql -h $1 -p $2 -U $3 -d $4 -t -c "select temp_bytes from pg_stat_database where datname = '$5'"

However, this select statement does not show the current temp bytes, instead it shows an increment of all temp bytes ever created since the last stats reset. IE: It's not showing a delta over a time period which would be the accurate number.

Looking at this query and then checking the folder, I see it's empty and therefore my server actually has no temp bytes.

select current_setting('data_directory') || '/base/pgsql_tmp'

Source: https://www.marshut.net/wnsmt/postgresql-9-2-4-temp-files-never-released.html

The same goes for a DB being too large:

Zabbix threshhold is incorrect for the way PostgreSQL reports size of databases:
See info about select pg_database_size(dbname)


SQL TOP statement, how to make sure it's "random"? I was studying SQL where I encountered a TOP statement. i.e.

SELECT TOP 2 * FROM Persons

select at most 2 row from table Persons.

However, if I were to use it for statistical reason, I'd like such process to be random: i.e. independent of time and order in the table.

My questions was that:

How to make sure TOP statement was totally "random"(it's selection was not ordered)? Further, how to make sure TOP statement was ordered with respect to some variable( i.e. age)
How did the TOP statement actually work?

I'm going to assume you're using SQL Server, as that's the version of SQL I know of that uses TOP rather than LIMIT.

Data in SQL Server is always in some sort of order; however, what that order is can only be guaranteed when you include an ORDER BY clause in your SQL statement.

Without an ORDER BY clause, the order of a dataset is determined by the process required to generate it, and the order in which is was stored.

If a table doesn't change, and you run the same SQL statement against it multiple times, you'll get the data back in the same order. That's because the query engine is applying the same process each time to generate the resulting data set.

So, if you were to issue your query against a table (with no inserts, updates, or deletes) multiple times, you'll get the same 2 rows each time.

Note: depending on the structure of the table (for instance, whether or not there's a clustered index), and activity against the table, your SELECT TOP 2 * might give different results on a table that's not basically static. That's why you need an ORDER BY to guarantee the order, even if today a query run without an ORDER BY returns the data in the order you want it consistently.

With TOP and ORDER BY, the query engine must at least partially sort your data (if it can locate and sort the top n rows, it doesn't have to sort the rest). With TOP but not ORDER BY, it simply selects the first n rows of the dataset it's generated.

If you want a random ordering, you (strange as it would sound) need to order the data by something random. ORDER BY NEWID() (as suggested in the comments by Lamak) would do this. NEWID() generate a new GUID each time it runs, and those are not generated in any sort of sequential order. This SO question explains how that works. It also describes how TOP works further.

As sp_BlitzErik points out, ORDER BY NEWID() is not fast, and is slower the larger the table is (it has to generate a GUID for each row in the table, regardless of the TOP number of rows to be returned). He links to How to Get a Random Row from a Large Table, an article on his company's website, for additional suggestions for getting truly random rows from a table.

Basically, the options are to use a relatively obscure query option called TABLESAMPLE, which can present problems; or, use the RAND function to generate a random ID value to return one row (dumping random numbers into a temp table if you need more than one or two rows, or a variable number of rows each time you run). You can also use RAND with the OFFSET ... FETCH clause to get a single random row in SQL 2012 or later.


combine two select queries? I have two Mysql select queries:

select TTS.SSML, TTS_TAG.SORTID, TTS.VoiceId , TTS_TAG.TAGID FROM TTS
left join TTS_TAG
on TTS_TAG.TTSID = TTS.TTSID
where TTS.MID = 15

select max(TTS_TAG.SORTID) as maxsort from TTS_TAG
where TTS_TAG.TAGID = 1

I wish to combine both statements into one select statement, with the aggregate value added as a field in resulting output record?

The second aggregate select statement retrieves the maximum 'sort' for all records with the value 1 - if this second select yielded '3', then my desired record set would be:

# SSML, SORTID, VoiceId, TAGID, maxsort
'ssml data', '1', 'Geraint', '4', '3'

Thanks Lennart!
A second sub select query within the first worked.

select TTS.SSML, TTS_TAG.SORTID, TTS.VoiceId , TTS_TAG.TAGID,
(select max(TTS_TAG.SORTID) from TTS_TAG
where TTS_TAG.TAGID = 1) as maxsort
FROM TTS
left join TTS_TAG
on TTS_TAG.TTSID = TTS.TTSID
where TTS.MID = 15


Field = Parameter OR Parameter IS NULL Pattern I am aware of the parameter sniffing issues associated with stored procedures written with a predicate like the following:

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    SELECT [Field] FROM [dbo].[Table]
    WHERE [Field] = @Parameter
    OR @Parameter IS NULL;
END;

Depending on the value of the parameter, Scalar or NULL on first execution, a plan is cached that will likely be sub optimal for the opposite value.

Assuming [Field] is scalar, and the clustering index on a table. What are the pros and cons to the following approaches to writing a stored procedure(s) to support the query:

Conditioned selects in same stored procedure

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    IF(@Parameter IS NOT NULL) BEGIN;
        SELECT [Field]
        FROM [dbo].[Table]
        WHERE [Field] = @Parameter;
    END;
    ELSE BEGIN;
        SELECT [Field]
        FROM [dbo].[Table];
    END;
END;

Dynamic SQL within stored procedure

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    DECLARE @sql NVARCHAR(MAX) = N'';
    SET @sql += N'SELECT [Field]'
    SET @sql += N'FROM [dbo].[Table]';

    IF(@Parameter IS NOT NULL) BEGIN;
        @sql += N'WHERE [Field] = @Parameter';
    END;

    SET @sql += N';';

    EXEC sp_executesql @sql N'@Parameter INT', @Parameter;
END;

Separate stored procedures

CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
    SELECT [Field]
    FROM [dbo].[Table]
    WHERE [Field] = @Parameter;
END;

CREATE PROCEDURE [dbo].[GetAll] AS BEGIN;
    SELECT [Field]
    FROM [dbo].[Table];
END;

They’re all excellent. Really. They all have the same impact of having two plans in the cache, which is what you want.

As you get more and more parameters, you will find the Dynamic SQL option is clearest, even though it looks scarier to beginners.

If this were a function I’d suggest avoiding multi-statement options, so that the QO could do its stuff more nicely.


Where does it make sense to store Indexes for partitioned tables? Prior to table partitioning, all the indexes were stored on a separate drive, per this question.  Now that I partitioned a couple of tables, SSMS is offering to store indexes in the partition scheme.  Is this a good idea?  Or should I be storing indexes on a separate drive, like I was before?  What are the pros or cons of either?

Putting an index on a partition scheme is exactly the same as putting a table on one. All it means in terms of storage is that the storage for the index is partitioned according to the partitioning column that you select. The partitions in a partition scheme still end up pointing to filegroups eventually, so you can still point the partition scheme at filegroups that are on your separate drives.

So the pros and cons are pretty much exactly the same as for table partitioning, and the query optimiser will make very similar choices in terms of partitioned .v. not. However, from my own experience, I can say that partitioning makes a lot of sense if you are regularly making queries that can be fully satisfied with the data from a single partition - and the sample applies to indexes.


MySQL - grant all privileges on *.* brings back Access denied GRANT ALL PRIVILEGES ON *.* TO  'username'@'localhost'  IDENTIFIED  BY  'somepassword';

Brings back error :

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

If I give a database name it works.

GRANT ALL PRIVILEGES ON dbname.* TO  'username'@'localhost'  IDENTIFIED  BY  'somepassword';

I did select * from user where user ='root'; and it has Y under every *_priv column.

I'm running MySQL Server version: 5.5.38. Does anyone know why this is happening?

You did not upgrade properly try running:

/usr/bin/mysql_upgrade -u root -p


Change SSIS package location (REPOST from SO) My SSIS Package are located in shared drive with a long path name. To make the packages easier and not to remember long path name, I use "Map my network drive" option and use X: drive as an alias to access packages. Next thing I know that all my packages locations have been updated as "X:\Package5.dtsx". Now if the pseudo network drive is disconnect, I cannot access the packages (outside the server).

Please find the snapshot that will justify what I actually meant.

How can I change the package name from X:\ to actual share drive name (where my package are stored)? I don't want to do some changes that could effect how this package run since they are very critical to the business continuity

Can you point the 'full path' to the UNC path i.e. \\<servername>\<sharename>\<directoryname>\<packagename> instead and see if that works?

Just like when you map the "X" drive to \\<servername>\<sharename> just use that in the full path of one of your packages and run to see if it'll work.

If one of your jobs work like that (assuming most all are setup the same and this way, etc), you can probably script out the SQL Agent jobs through SSMS by pressing F7 (once SQL Agent jobs is highlighted), selecting them all from the right pane window, right click, then create to new query window, then do a mass CTRL+H and do a find and replace to replace X:\ with \\<servername>\<sharename>, and then run that.

Just be sure the SSIS proxy account or the SQL Server Agent account has appropriate NTFS and SHARE permissions where the SSIS packages reside to read them.


Excluding database objects from a DacPac database deployment? As I understand it, a sql server DacPac (visual studio data-tier app) applies to the entire database that it's deployed in. However, for my particular database, I have some tables and stored procs that I want to always remain unchanged in one environment, regardless of the dacpac's current state.
I've been reading about partial/composite database projects, but I'm not sure if/how this can be done with DacPac, or if that is even the solution I'm really looking for. Any ideas?

Instead of using a DACPAC, you might find generating database scripts more flexible for your needs if you want to exclude specific entities. Here's a good walkthrough on how to do it: Methods to script SQL Server database objects


SQL Server online maintenance - False publicity? I'm being challenged since we have customers that are seeing different types of errors while running what is supposed to be online maintenance.  errors like timeouts, scan stopped due to data movement and more.
Some of our customers purchased SQL server enterprise edition to be able to get the rebuild index online feature.
To replicate the issues, I've been testing on a big database the dbcc checkdb, reorganize and rebuild of indexes and the update of statistics while I bombard the server with transactions on a database that is almost 1TB.
My first test was with the checkdb with maxdop=1 while the sqlServer was processing 124000 small transactions... I received a timeout from my application which has a the timeout set to 5 minutes.
Research on the way the check db works , is that is creates a snapshot, uses tempdb intensively and nolocks are created to create the snapshot...
So how can one of my small transactions be blocked if it doesn't lock tables?
My second test was to reorganize all indexes (which is also supposed to be online) while processing 124000 transactions, This time I got a deadlock...
My third test was to update all statistics with maxdop=1 while running 124000 transactions.  The error that was received in this case is: Could not continue scan with no lock due to data movement
my forth test to rebuild all indexes online while running 124000 transactions will be running soon and I will update my post with the results.
The maxdop=1 that I used where it can be used was to eliminate contention of resources.
I've read multiple articles inclusing Paul Randal's explanation of online vs offline of the rebuild of indexes and understand the difference... but as I said, I'm being challenged to explain why it isn't really online although Microsoft claims it's online.
Any input would be much appreciated.
What am I missing

A lot of questions here. I have a feeling the answers won't make you happy, though.
DBCC CheckDB

To replicate the issues, I've been testing on a big database the dbcc checkdb, reorganize and rebuild of indexes and the update of statistics while I bombard the server with transactions on a database that is almost 1TB.

I don't know that I'd lumped DBCC CHECKDB with "online maintenance", but it certainly isn't an offline activity by nature. That being said, it can still be resource intensive which could manifest as an application "not working" although the database is technically still online and available.

I received a timeout from my application which has a the timeout set to 5 minutes...So how can one of my small transactions be blocked if it doesn't lock tables?

That doesn't indicate any blocking, especially since DBCC CHECKDB doesn't cause blocking unless you specify it should. You should take a closer look at monitoring the queries to see what the cause may be for the timeout - it could be a lot of things honestly.
Online Index Builds

My second test was to reorganize all indexes (which is also supposed to be online) while processing 124000 transactions, This time I got a deadlock...

Was the deadlock with the index reorganize statement, or another application statement? I can't recall a reorganize causing this issue ever for me, but deadlocks are a fact of life and the application should be able to gracefully retry when it encounters them. The operation being online in no way guarantees you won't get deadlocks.
Most people schedule index maintenance overnights/weekends to help avoid related issues around locking, blocking, and resource contention. Testing against a normal business hour workload is bound to give you problems like you're seeing.
Update Statistics

My third test was to update all statistics with maxdop=1 while running 124000 transactions. The error that was received in this case is: Could not continue scan with no lock due to data movement

This one's on you (or rather, the application): don't use NOLOCK and you won't get NOLOCK related errors. Again, not sure I'd recommend updating all stats on a busy system during high volume, so this seems like a test destined to have issues as well.
Technical Definitions

I've read multiple articles inclusing Paul Randal's explanation of online vs offline of the rebuild of indexes and understand the difference... but as I said, I'm being challenged to explain why it isn't really online although Microsoft claims it's online.

If you'd read this then you know that online doesn't mean no locks are taken. The definition of the word is highly contextual. You can point whoever you need to to the documentation on online rebuilds:

Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server Database Engine will select the user or application activity as a deadlock victim.

Conclusion
All this is to say that regardless of maintenance operations being able to be "online", most people don't test them during full workloads nor expect them to work well during normal transactional volume.
Most of the time you really won't need to rebuild/reorg all indexes or update all stats at the same time, even during maintenance windows. If you have a VLDB, split out that DBCC CHECKDB into different windows (like Paul has covered so well).
I'd focus more on how/when these things will actually run. Use something like Ola's maintenance solution so you're not making SQL Server do more work than it has to.
And have your customer scale back their expectations!
See also

Why does create index with online block access to the table over a period of minutes?
How to reproduce "Could not continue scan with nolock due to data movement"
Bad Habits: Putting NOLOCK Everywhere
CHECKDB From Every Angle: Consistency Checking Options for a VLDB


Does sqlite preserve COLLATE on tables in a view? In sqlite3 collations are defined per-column. If you CREATE TEMPORARY TABLE foo AS SELECT ... the resulting temporary table foo does not inherit any custom collations from the columns in the SELECT.  What happens if you CREATE VIEW foo AS SELECT ...? Does the view "table" keep column collations or lose them?

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


SQL Server Mirror failover on failing storage Recently we had an issue with a sql server 2008 r2 HA mirror. I have tried to reproduce this in a lab environment and i came to the conclusion that i am missing something OR something is not possible.

I am using 2 sql server 2008 r2 servers. Both the servers have a iscsi storage connection to a storage server. Mirroring is done with a witness server in HS mode.
i have followed a blog post by Glenn Berry about how to create a group failover, he told me on twitter it would also be usable in case of failing storage. However, somewhere i can't really get things to work.

What am i doing. I have a sql-01 as princiapl and a sql-02 as mirror. 2 databases running in HA mirror mode. I have a failover script running on WMI 7 and 8. When i go to my storage server to disable the storage of sql-01 i would suspect that eventually i get a failover to sql-02. but it doesn't...

Anyone can give me some hints on this?

Edit : Failover with a shutdown-ed storage for sql-01 looks working, because of a tempdb that is getting corrupt which needs recovery (according to logs). Therefor the instance has to shutdown (also in the logs), so probably that is my reason i can failover from sql-01 to sql-02, by accident and apparently not by design.

Just to move my part into an Answer.
It would appear the "problem" is that the script in the blog ( http://sqlserverperformance.wordpress.com/2012/03/27/sql-server-database-mirroring-tips-and-tricks-part-3/ ) is using an ALTER DATABASE dbname SET PARTNER FAILOVER; to failover the rest of the 'failover' group.

As all the dbs were on the same disk, this fails as SQL SERVER can't run the ALTER DATABASE command if it can't get in to the database. So moving the databases onto seperate disks fixes that.

But that sort of makes sense. If all the databases are on the same disk then they should failover automatically as a 'group' as they all need to be fail overed. The referenced script would make sense if you did have databases on seperate disks that needed to failover together if just one of the disks failed.


Query uses Primary Key Index instead of Nonclustered index defined I have this simple query and I am confused why the index is not working.

SELECT lop.RecordID ProspectusID,
    c.c_code CourseCode,
    c.c_desc CourseDescription,
    COALESCE(c.c_major, '') MajorName,
    lop.EffectiveYear EffectiveSchoolYear,
    COALESCE(COUNT(pc.ProspectusID), 0) SubjectCount
FROM dbo.ListOfProspectus AS lop
    INNER JOIN dbo.courselist AS c ON lop.CourseCode = c.c_code
    LEFT JOIN dbo.ProspectusContent AS pc ON lop.RecordID = pc.ProspectusID -- <== problem
WHERE lop.CourseCode IN ('BSIT')
GROUP BY lop.RecordID, c.c_code, c.c_desc, c.c_major, lop.EffectiveYear
ORDER BY CourseCode, EffectiveSchoolYear

which generates the following plan,

I have an index defined on dbo.ProspectusContent.ProspectusID.

CREATE NONCLUSTERED INDEX [idx_ProspectusContent_prospectusid]
ON [dbo].[ProspectusContent]
(
    [ProspectusID] ASC
)

But why is it still using the primary key index?

ALTER TABLE [dbo].[ProspectusContent]
ADD CONSTRAINT [ProspectusContent_pk_RecordID] PRIMARY KEY CLUSTERED
(
    [RecordID] ASC
)

If I remove the following computed column: COALESCE(COUNT(pc.ProspectusID), 0) SubjectCount in the SELECT clause, the server generates this plan:

UPDATE

However, when I try to execute a query on table dbo.ProspectusContent alone, it uses the nonclustered index,

SELECT ProspectusID
FROM dbo.ProspectusContent AS pc
WHERE pc.ProspectusID = 2

By the way, idx_ProspectusContent_prospectusid is the same with idx_dmcasarms_ProspectusContent_prospectusid, just convention name policy.

If there's anything missing detail in my question, please do me so I can update it. Thank you.

The table only has 2 rows. The cost difference between a table scan and an NC seek is negligible. SQL Server is pretty good about finding the cheapest way to getting things done. I'd expect that when the table gets bigger, the NC index will start getting used.

To compare performance, you can force the index to be used with an index hint:

SELECT lop.RecordID ProspectusID,
    c.c_code CourseCode,
    c.c_desc CourseDescription,
    COALESCE(c.c_major, '') MajorName,
    lop.EffectiveYear EffectiveSchoolYear,
    COALESCE(COUNT(pc.ProspectusID), 0) SubjectCount
FROM dbo.ListOfProspectus AS lop
    INNER JOIN dbo.courselist AS c ON lop.CourseCode = c.c_code
    LEFT JOIN dbo.ProspectusContent AS pc WITH (INDEX(idx_ProspectusContent_prospectusid)) ON lop.RecordID = pc.ProspectusID
WHERE lop.CourseCode IN ('BSIT')
GROUP BY lop.RecordID, c.c_code, c.c_desc, c.c_major, lop.EffectiveYear
ORDER BY CourseCode, EffectiveSchoolYear

In production, I would use the hint as a last resort though. Given proper indexes and statistics, the query optimizer can typically build the cheapest plan on its' own.

Read more about hints: http://technet.microsoft.com/en-us/library/ms181714.aspx


You need to query the CTE and return * to see the changed data.

   with updated as (update bippy set id = 100 where id = 1 returning *)
   select id, ts from updated;

Or if you want all rows returned (updated and non-updated):

   with updated as (update bippy set id = 100 where id = 1 returning id, ts)
   select id, ts from updated
   UNION ALL
   select id, ts from bippy where id <> 1;

From the documentation:

7.8.2. Data-Modifying Statements in WITH

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query. An example of this is that in

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

the outer SELECT would return the original prices before the action of the UPDATE, while in

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

the outer SELECT would return the updated data.


Incorrect definition of table mysql.event after migrating database to mariadb I did a mysqldump to dump all databases into a .sql file, then installed a mariadb server. There are no errors after the installation. But after restoring the databases, I started to observe the following two errors:

  [ERROR] Incorrect definition of table mysql.event: expected column
  'sql_mode' at position 14 to have type
  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'),
  found type
  set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
  [ERROR] Event Scheduler: An error occurred when initializing system
  tables. Disabling the Event Scheduler.

It looks like something within the mysql.event table got overwritten. I did not create any events, so I wasn't expecting anything to be affected here. What should be done to eliminate this error?

On centos 7:

  mysql  Ver 15.1 Distrib 5.5.50-MariaDB, for Linux (x86_64) using readline 5.1

mysql_upgrade -u root -p

This issue is fixed.


How do I optimize this extremely simple MySQL query? I am using MySQL 5.7 (can't change this), and I am kind of new to MySQL. I have a fair amount of experience with SQL sever so just trying to work through some of these quirks.

The query I want to run is

select *
from temp_main_working t
  left outer join temp_sf s on t.PM_ORDER_ID = s.pm_order_id
where s.pm_order_id is null

The explain looks like this

If I change the outer join to an inner join (which obviously won't give me what I want), as such

select *
from temp_main_working t
  inner join temp_sf s on t.PM_ORDER_ID = s.pm_order_id

I get an explain that looks like this

which is obviously much better for performance.

In other cases where i use a left join, it managed to use associated indexes just fine. What is going on here? How do I get it to run efficiently?

UPDATE

Including explain for Akina's suggested query

Try

SELECT t.*
FROM temp_main_working t
WHERE NOT EXISTS ( SELECT 1
                   FROM temp_sf s
                   WHERE t.pm_order_id = s.pm_order_id )

UPDATE

id select_type        table partitions type   possible_keys key     key_len ref                rows filtered Extra
1  PRIMARY            t     null       index  null          PRIMARY 4       null               1    100      Using where; Using index
2  DEPENDENT SUBQUERY s     null       eq_ref PRIMARY       PRIMARY 4       test.t.pm_order_id 1    100      Using index


RMAN backup copy - read once and write many? I am trying to find out what happens in this scenario:

BACKUP COPIES 2

What actually happens in the BACKUP COPIES above?  Does Oracle RMAN read the data files each time for each separate copy?  Or does it read only once and write as many copies as specified?

In case you specify COPIES 2 each block is read once and written two times to backupset copies. The two backupset copies are supposed to be bit-to-bit identical. Both copies have the same backupset key (BS_key) in RMAN. You cannot mix tape and disk copies - either both copies go to DEVICE DISK or both to DEVICE SBT.


Server with mariadb sometimes dies under high load I have a dedicated server i7-4770 with 32GB RAM. On server installed CentOs 7.8, MariaDB 10.5.8, PHP 7.4.14, Nginx 16.1. All tables in the database are InnoDB.
When the load on the database increases significantly and I see more than 20,000,000 requests per hour in PHPMyAdmin, the server dies unexpectedly. After I reboot the server from the hosting company's control panel - the server is working fine. But as soon as the load on the database becomes large, the server dies again.
In the logs of MariaDB 10.5.8, before the server dies, only 1 error appears:
[Warning] Detected table cache mutex contention at instance 1: 22% waits. Additional table cache instance cannot be activated: consider raising table_open_cache_instances. Number of active instances: 1.

I think the reason for the death of the server is in her. But the problem is that when I specify table_open_cache_instances = 8 and restart the MariaDB, the request
SHOW VARIABLES LIKE 'table_open_cache_instances'

gives me a value of 1. After reboot server value also remains 1. Other values such as max_connections are set correctly.
My /etc/my.cnf.d/server.cnf (=my.cnf)
slow_query_log      = 1
slow_query_log_file = /var/log/mariadb/slow_queries.log
long_query_time     = 1

max_connections = 836
key_buffer_size = 256M
read_buffer_size = 16M
join_buffer_size = 16M

query_cache_size = 0
query_cache_type = 0
query_cache_limit = 1M

tmp_table_size = 512M
max_heap_table_size = 512M

innodb_buffer_pool_size = 22G
innodb_log_file_size = 512M

innodb_flush_log_at_trx_commit = 0

thread_cache_size = 512

table_open_cache = 207000
open_files_limit = 207000

table_definition_cache     = 1400

table_open_cache_instances = 8

How can I solve this problem? How do I edit my MariaDB 10.5.8 config?

I assume you hit the open_files_limit. This should be indicated somewhere further up of your warning about the instances in your MariaDB Error Log.
The problem here is, that MariaDB behaves differently than MySQL:
In MySQL the number of FD (open_files_limit) used is: table_open_cache x 2 + ...
In MariaDB the number of DD used is table_open_cache x table_open_cache_instances x 2 + ...
https://mariadb.com/kb/en/server-system-variables/#table_open_cache_instances
Why they made it different I do not know.
So in your case you have:
table_open_cache = 207000 x table_open_cache_instances = 8
= requiring 1.6M TOC entries but you only have defined 207k as open_files_limit = 207000
So either lower the table_open_cache by at least 8 times or increase the open_files_limit by at least 8 times.
3 additional thoughts:

Do you really need 200k or 1.6M FD??? That happens only very rarely when you have multi-tenant set-ups or 100ks of tables.

See also here: https://fromdual.com/mysql-shared-hosting-configuration

Files are configured today in SystemD: https://fromdual.com/node/1270#systemd

The effective number of FD (open_files_limit) you need is calculated in:

MariaDB: table_open_cache x table_open_cache_instances x 2 + max_connections + 40
MySQL: table_open_cache x 2 + max_connections + 10

Regards,
Oli


Print, transaction and cursor performance in Transact-sql I'm having some performance issues when I run a script. I was reading about the problem with print and its performance, but I cannot figure out how to reduce the impact.

The algorithm is O(n^2). The main loop goes through around 50k elements. For each of them it performs some selects to retrieve data from different tables and finally it does two sub-loops

Main loop(n)
-- some selects...
   nested loop(n^2)
   nested loop(n^2)

There are different prints inside, not only because of debugging purpose, but also for requirements.
Also, when I wrap all the script into a transaction, its turns dramatically low performance.

Three points:

-Is there a way to reduce the impact of print?

-What is better in performance?: cursor or loop with a counter

-It is normal the low performance within a transaction?

Code:

http://pastebin.com/X5SiKcgq

Server: Windows Server 2008 R2(2Gb RAM)
DBMS: SQL Server 2008 R2
Client: Windows 7 running SQL Management Studio 2012.

The requirement is to show the output with incorrect rows. It will be (in production) a small list with max 80lines. It doesn't create a print for 50k. This is why I don't know how to resolve the performance issue, when the print are relatively uncommon in the output. Now my test case shows between 500-800 lines. The script will run only once in 3 environments.

You could declare a table variable, insert to that rather than printing and dump it in toto at the end of your process:

declare @message table (i int identity(1,1), words varchar(100));

The identity column will keep things in chronological order.  @tables' contents survive a rollback so are preferable over #tables or normal tables for this purposes.

I haven't profiled this suggestion; I throw it out there for what it's worth.


SQL Server second transaction log seems to be used as well - not only after first one is full we have come across a strange phenomenon using a second transaction log file for a database. Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) Standard Edition. On Windows Server 2016, a single instance, no cluster or replication.
We have added the second log file after the first was already in use the whole time. We thought the second file would only be in use after the first one has no more space to grow / is full.
I know that it is probably not such a good idea to have two log files in the first place, but for the moment we would like to have the second one as an insurance, so to speak.
Currently there is enough free space on the first log volume, 416 GB, there are 983 GB free on the second. The first log file is allowed to have a maximum size of 300 GB, currently has a size of 40 GB, growth by 1024 MB, limit 300 GB. The second log is currently 16 GB, growth by 1024 MB, limit 2097152 MB.
Now when we execute
select * FROM sys.dm_db_log_info(DB_ID('MultileLogFiles'))

we see that both log files are actually in use (please see below) - or at least that is our conclusion, we may be wrong. The Resource Monitor supports our claim and shows that the second log file is also used, both appear from time to time and the 2nd one has also grown, not only the first one.
Could anyone give us a hint why that should be the case, why SQL Server does not wait until log file 1, the first in the list, is full or may be the other one and not the first, but why both? That seems strange.
Best regards,
Harry
database_id    file_id    vlf_begin_offset    vlf_size_mb    vlf_sequence_number    vlf_active    vlf_status    vlf_parity    vlf_first_lsn    vlf_create_lsn
    7            2             8192              0.24              603616                1            2            128    000935e0:00000010:0001    00000000:00000000:0000
    7            2             262144            0.24              603617                1            2            128    000935e1:00000010:0001    00000000:00000000:0000
    7            2             516096            0.24              603598                1            2            128    000935ce:00000010:0001    00000000:00000000:0000
(...)
    7            3             4437573632        128               603683                1            2            128    00093623:00000010:0001    00092757:0003fd82:002e
    7            3             4571791360        128               603684                1            2            128    00093624:00000010:0001    00092757:0003fd82:002e
(...)
    7            3             15040774144       1024              603464                0            0            64     00000000:00000000:0000    00092787:001ffd67:00c0
    7            3             16114515968       1024              603466                0            0            64     00000000:00000000:0000    00092789:001ffd45:002e
(...)

The Log files are used like if it was only one file. Both files have VLF so let's say the first log file have VLF1 to VLF10 and the second one have VLF11 to VLF15. It will write down stuff in the VLF1 and once that one is full, will continue in VLF2, etc... once it reach VLF15, it will then go back to VLF1 (not before) so it's 100% normal that both files still get used.
If you want to get rid of your second file, you'll have to take log backup until the "active" VLF is on the file #1 and then, you'll be able to delete the file.


Pivot Multiple Columns with a Cross Apply I am trying get the following result:

At the moment, my query (and hopefully a guide to table structure) resembles:

WITH CoreGateway AS
(
SELECT      g.GatewayTitle
,           g.GatewayOrder
,           ggs.GatewayGatewayStepId
FROM        ref.Gateway             g
JOIN        ref.GatewayGatewayStep  ggs ON  ggs.GatewayId       = g.GatewayId
JOIN        ref.GatewayStep         gs  ON  gs.GatewayStepId    = ggs.GatewayStepId
WHERE       gs.GatewayStepTitle     = 'Carbon Calculated'
)
, CarbonCalc AS
(
SELECT      pg.ProjectId
,           pg.ProjectGatewayId
,           pg.GatewayGatewayStepId
,           cc.tC02
,           cc.CarbonCost
FROM        pmp.ProjectGateway      pg
LEFT JOIN   pmp.CarbonCalculated    cc  ON  cc.ProjectGatewayId = pg.ProjectGatewayId
WHERE       pg.ProjectId = 3021
)
, CarbonData AS
(
SELECT      cc.ProjectId
,           cg.GatewayTitle
,           cg.GatewayTitle + '1' AS GatewayTitle1
,           cg.GatewayOrder
,           cc.tC02
,           cc.CarbonCost
FROM        CoreGateway     cg
LEFT JOIN   CarbonCalc      cc ON cc.GatewayGatewayStepId = cg.GatewayGatewayStepId
)
SELECT      p.ProjectId
,           p.SolutionId
,           p.NeedId
,           p.ProjectCode
,           p.ProjectDescription
,           ISNULL([Gateway 1], 0.0)        AS Gateway1_tC02
,           ISNULL([Gateway 3], 0.0)        AS Gateway3_tC02
,           ISNULL([Gateway 5], 0.0)        AS Gateway5_tC02
,           ISNULL([Gateway 9], 0.0)        AS Gateway9_tC02
,           ISNULL([Gateway 11], 0.0)       AS Gateway1_CarbonCost
,           ISNULL([Gateway 31], 0.0)       AS Gateway3_CarbonCost
,           ISNULL([Gateway 51], 0.0)       AS Gateway5_CarbonCost
,           ISNULL([Gateway 91], 0.0)       AS Gateway9_CarbonCost
FROM        CarbonData  cd
PIVOT       (
            MAX(tC02) FOR GatewayTitle IN ([Gateway 1], [Gateway 3], [Gateway 5], [Gateway 9])
            ) tc02
PIVOT       (
            MAX(CarbonCost) FOR GatewayTitle1 IN ([Gateway 11], [Gateway 31], [Gateway 51], [Gateway 91])
            ) tc02
JOIN        pmp.Project p   ON p.ProjectId = tc02.ProjectId

Which yields a result of:

As we can see, not all data has been entered in either ProjectGateway or CarbonCalculated, which is fine as they are optional. And the Pivot appears to be performing a Cartesian.

What is the best approach to pivot data for the 2 fields, when columns can be optional?

I eventually managed to get this working. Had to use a Cross Apply to flatten for the multiple pivots:

WITH CoreGateway AS
(
SELECT      pg.ProjectId
,           g.GatewayTitle
,           ggs.GatewayGatewayStepId
,           cc.tC02
,           cc.CarbonCost
FROM        pmp.ProjectGateway      pg
LEFT JOIN   pmp.CarbonCalculated    cc  ON  cc.ProjectGatewayId         = pg.ProjectGatewayId
JOIN        ref.GatewayGatewayStep  ggs ON  ggs.GatewayGatewayStepId    = pg.GatewayGatewayStepId
JOIN        ref.Gateway             g   ON  g.GatewayId                 = ggs.GatewayId
JOIN        ref.GatewayStep         gs  ON  gs.GatewayStepId            = ggs.GatewayStepId
WHERE       gs.GatewayStepTitle     = 'Carbon Calculated'
)
, CarbonData AS
(
SELECT      cg.ProjectId
,           cg.GatewayTitle + CarbonType AS GatewayTitle
,           CarbonValue
FROM        CoreGateway     cg
CROSS APPLY (
            VALUES  (cg.tC02, 'tC02')
            ,       (cg.CarbonCost, 'CC')
            ) c (CarbonValue, CarbonType)
)
SELECT      p.ProjectId
,           p.SolutionId
,           p.NeedId
,           p.ProjectCode
,           p.ProjectDescription
,           ISNULL([Gateway 1CC], 0.0)      AS Gateway1_CarbonCost
,           ISNULL([Gateway 3CC], 0.0)      AS Gateway3_CarbonCost
,           ISNULL([Gateway 3CC], 0.0) -
            ISNULL([Gateway 1CC], 0.0)      AS Gateway3_CarbonCost_Diff
,           ISNULL([Gateway 5CC], 0.0)      AS Gateway5_CarbonCost
,           ISNULL([Gateway 5CC], 0.0) -
            ISNULL([Gateway 1CC], 0.0)      AS Gateway5_CarbonCost_Diff
,           ISNULL([Gateway 9CC], 0.0)      AS Gateway9_CarbonCost
,           ISNULL([Gateway 9CC], 0.0) -
            ISNULL([Gateway 1CC], 0.0)      AS Gateway9_CarbonCost_Diff
,           ISNULL([Gateway 1tC02], 0.0)    AS Gateway1_tC02
,           ISNULL([Gateway 3tC02], 0.0)    AS Gateway3_tC02
,           ISNULL([Gateway 3tC02], 0.0) -
            ISNULL([Gateway 1tC02], 0.0)    AS Gateway3_tC02_Diff
,           ISNULL([Gateway 5tC02], 0.0)    AS Gateway5_tC02
,           ISNULL([Gateway 5tC02], 0.0) -
            ISNULL([Gateway 1tC02], 0.0)    AS Gateway5_tC02_Diff
,           ISNULL([Gateway 9tC02], 0.0)    AS Gateway9_tC02
,           ISNULL([Gateway 9tC02], 0.0) -
            ISNULL([Gateway 1tC02], 0.0)    AS Gateway9_tC02_Diff
FROM        (
            SELECT  cd.ProjectId
            ,       cd.GatewayTitle
            ,       cd.CarbonValue
            FROM    CarbonData  cd
            )   cc
PIVOT       (
            MAX(CarbonValue) FOR GatewayTitle IN    (   [Gateway 1CC], [Gateway 3CC], [Gateway 5CC], [Gateway 9CC]
                                                    ,   [Gateway 1tC02], [Gateway 3tC02], [Gateway 5tC02], [Gateway 9tC02]
                                                    )
            ) carpvt
JOIN        pmp.Project p   ON p.ProjectId = carpvt.ProjectId;


MySQL Selecting and deleting whole entries I'm a total noob to mysql and i`m still trying to get my head around it. I need to select duplicate entries in a song DB and delete only those entries not in a category. I can get t to work with select. though there's an error but not with delete. Could someone help and point out my total noobness mistake?

Select Code

Select Songlist.ID,Artist,Title,Filename,CategoryID
from Songlist,categorylist
where Artist = "Lady Gaga" AND Title = "Bad Romance" AND
(Categorylist.CategoryID <> 12)

Delete Code

Delete from *
where Artist = "Lady Gaga" AND Title = "Bad Romance" AND
Categorylist.CategoryID <> 12

Thanks in advance.

MySQL's DELETE syntax requires the table names involved in the DELETE.

However, I don't think the SELECT query is working as you want it to, either.

You have not established any connection between Songlist and Categorylist. So, it's going to give you a cross join - every row in Songlist wit the specified Artist and Title, matched with every row in Categorylist where CategoryID is not 12. If you had two matching rows in Songlist, and 11 matching rows in Categorylist, your result would be 22 rows.

Assuming you have a CategoryID in Songlist, you'd presumably want rows where the Songlist.CategroyID is not 12.

Try this:

Select Songlist.ID,Artist,Title,Filename,CategoryList.CategoryID
  from Songlist
         LEFT OUTER JOIN Categorylist ON (Songlist.CategoryID = Categorylist.CategoryID)
 where Artist = "Lady Gaga"
   AND Title = "Bad Romance"
   AND (   Categorylist.CategoryID IS NULL
        OR Songlist.CategoryID <> 12
       )
;

This should find all records with your artist and title, where either there's no record in Categorylist with the CategoryID in Songlist, or where the CategoryID is something other than 12.

Then, the delete statement to delete those records from Songlist would be:

DELETE Songlist
  FROM Songlist
         LEFT OUTER JOIN Categorylist ON (Songlist.CategoryID = Categorylist.CategoryID)
 where Artist = "Lady Gaga"
   AND Title = "Bad Romance"
   AND (   Categorylist.CategoryID IS NULL
        OR Songlist.CategoryID <> 12
       )
;

Presumably, leaving only any rows with that artist and title where the CategoryID is 12.

NOTE: Code is, obviously, untested.


Using a third location for log shipping I have a problem with log shipping in SQL Server 2012.

I have tried to configure log shipping between server A and server B. The log destination is on my PC (shared folder). This scenario is working successfully.

Now the problem is when I try to make the logshipping between two servers.  I make a shared folder from SYNOLOGY and INFORTREND. Even when I have changed the permissions, it fails to be executed. The log history of the failure shows:

  The last step to run was step 1 (Log shipping backup log job step).
  Executed as user: NT Service\SQLSERVERAGENT.
  The step failed.
  Cannot open backup device '\...\TESTLAGI_20160601082323.trn'.
  Operating system error 86 (The specified network password is not correct.)
  BACKUP LOG is terminating abnormally.

I don't know what's wrong with it. Is it because the SQL Server log shipping doesn't support a third place to put logs for log shipping?

I have added permission for EVERYONE, and given it full control. It should be working, because when I use a shared folder on my PC, it works great, but when I use storage machine, it fails.

The program which performs log shipping runs under a specific UserID. Assuming you are using SQL Server Agent to perform log shipping on a schedule, log shipping will happen using SQL Server Agent's credentials (but see below for proxy accounts). You can find this by starting SQL Server Configuration, highlighting SQL Server Services and right-clicking on SQL Server Agent. The account listed there must have write permission on NAS device. Importantly, the source Agent and the destination device must share a source of authentication. If they are on different network domains, say, you may have to get your LAN administrators involved to establish the trust between the two devices.

Proxies: it is possible to establish additional credentials which SQL Agent will use to run particular jobs. See here for information. If your site is using proxies to run log shipping then it is the proxy account which will need access to the NAS.

To test, I would suggest you create a new job with a single step that copies a small text file from the DB server to the NAS. Use the same credentials for this as you do for log shipping. Run this and adjust permissions until it works. Then duplicate those permission for the actual log shipping job and re-test.


Parsing Typed XML with a default namespace and inconsistent root node I have a structured, typed xml file.

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
 <NewLeads>
  <OutputSchema>
  <root xmlns="" type="array">
   <item type="object">
   <SaleProperty type="object">
    <Type type="string">Freehold</Type>
    <PostDistrict type="string">xxx</PostDistrict>
    <Address type="string">address</Address>
     <Value type="number">17.0</Value>
   </SaleProperty>
   <Transaction type="string">SaleOnly</Transaction>
   <Quote type="object">
    <Sale type="object">
     <Fees type="number">450.0</Fees>
     <Disbursements type="number">0.0</Disbursements>
     <StampDuty type="number">0.0</StampDuty>
     <Total type="number">450.0</Total>
    </Sale>
   <Discount type="number">0.0</Discount>
   <VAT type="number">90.0</VAT>
   <Total type="number">540.0</Total>
  </Quote>
  <MoverId type="number">12345678</MoverId>
  <Name type="string">Mr AS</Name>
  <Email type="string">as@yahoo.ppp</Email>
  <Telephone type="string">0123456789</Telephone>
  <Comments type="string">Joint ownership</Comments>
  <EstimatedMoveDate type="string">2015-11-25T05:57:00</EstimatedMoveDate>
  <Charge type="number">4.99</Charge>
  <ChargeStatus type="string">Chargeable</ChargeStatus>
 </item>
</root>
</OutputSchema></NewLeads></WebService>

Additionally, object SaleProperty may be missing, instead may be object PurchaseProperty. Similarly, object Quote can contain Sale, Purchase, Remortgage objects or sale and Purchase simultaneously
I can't find any tips on inserting it into one table via xquery on SQL Server 2012. Basic problem is that every time I'm building query to extract even one value (i.e. Transaction) only thing except errors I get is empty string.

my sample query (based on MSDN examples):

SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/root/item/transaction') as t(c)

I would like to have an example how to insert this xml into table (where every possible element has it's own column.

I assume that problem is with namespaces and strong typing of the xml. However, this is what I get from web service. I work on xml both missing and containing second xmlns declaration in root element, as well as with structure trimmed down to just root.

The three problems with your XQuery that I can see are (and none of this has anything to do with it being Typed XML):

You are not specifying the correct path to the /root node. It should be:

SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/transaction') as t(c)

XML is case-sensitive so you need to use an upper-case "T" for the "Transaction" node:

SELECT t.c.value('(.)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item/Transaction') as t(c)

While those fixes would probably get you 1 value back (in this case, it should be "SaleOnly"), it won't iterate through the "item" nodes because you were too specific in the path supplied to the .nodes() function. Instead, the final node of that specification should be "item", which is what you want to iterate through. And in that case, you move the "Transaction" part up to the .value() function:

SELECT t.c.value('(./Transaction)[1]','varchar(50)') as type
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)

Regarding the statement of:

  I work on xml both missing and containing second xmlns declaration in root element, as well as with structure trimmed down to just root.

The "structure trimmed down to just root" should be handleable by removing everything between the first / and the / right before /root... (i.e. the WebService/NewLeads/OutputSchema). So the resulting path would be:

from @ixml.nodes('//root/item') as t(c)

NOTE:
I am not able to get this to work 100% with the namespace declared in the <WebService> element (please see additional notes as this is no longer the case). Taking that out makes it work. Giving it a prefix, such as xmlns:something="http://www.orbis-software.com/WebSvcCon" makes it work, but then that needs to be declared in the methods.  The only way I can get it to work right now is by declaring the default namespace in each XML function (.nodes and .value) as follows:

SELECT t.c.value('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
                  (./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('declare default element namespace "http://www.orbis-software.com/WebSvcCon";
                  /WebService/NewLeads/OutputSchema/root/item') as t(c)

NOTE 2:
Even better, you can use WITH XMLNAMESPACES to declare one or more namespaces to use for the entire query, so no need to define in each XML function. The following both work:

;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('/WebService/NewLeads/OutputSchema/root/item') as t(c)

;WITH XMLNAMESPACES (DEFAULT 'http://www.orbis-software.com/WebSvcCon')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)

However, just keep in mind that if the document is missing the <WebService xmlns="http://www.orbis-software.com/WebSvcCon"> element and hence has no default namespace, then you need to remove the ;WITH XMLNAMESPACES part. Of course, if the <root> element has its own default namespace, then maybe you will need to keep it. You can play around with it until it works, now that you know the connection between these pieces.

NOTE 3:
If you do end up having two default namespaces declared -- one in the <WebService> element and one in the <root> element -- then you need to specify the URI noted in <root xmlns="bob"> and the // syntax instead of the fully-qualified path.  So if your XML looked like:

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
 <NewLeads>
  <OutputSchema>
  <root xmlns="http://someplace" type="array">

You would then use:

;WITH XMLNAMESPACES (DEFAULT 'http://someplace')
SELECT t.c.value('(./Transaction)[1]','varchar(50)') as [type]
from @ixml.nodes('//root/item') as t(c)

But that won't help if you do have the <WebService> element and yet the <root> element is missing the xmlns declaration. In that case you still need to specify the namespace noted in the <WebService> element. Fun, fun, fun :-).

NOTE 4:
Even more better: incorporating something mentioned in @wBob's answer, we can actually get rid of the ;WITH XMLNAMESPACES clause, and instead use a namespace wildcard. You just need to prefix every node of every XML function with *:. Now the query should look as follows:

SELECT t.c.value('(./*:Transaction)[1]','varchar(50)') AS [type],
       t.c.value('(./*:SaleProperty/*:PostDistrict)[1]','varchar(50)') AS [PostDistrict]
FROM  @ixml.nodes('//*:root/*:item') t(c);

Doing this means that the query works in all of your scenarios:

Full structure starting with "WebService" node, second xmlns declaration:

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
  <NewLeads>
    <OutputSchema>
      <root xmlns="uri" type="array">

Full structure starting with "WebService" node, single xmlns declaration:

<WebService xmlns="http://www.orbis-software.com/WebSvcCon">
  <NewLeads>
    <OutputSchema>
      <root type="array">

Trimmed down structure starting with "root" node, single xmlns declaration:

<root xmlns="uri" type="array">


MySQL (or MariaDB) multi-master without MMM I've been reading blogs on MySQL multi-master setup.  The way I've used MySQL replication in the past was master-slave where if the master died (or was being taken down for maintenance) we would shut down the master, promote the slave to master, point the configs to the old slave, and do our work.  The new master would then become the slave (or we would fail back).

I'm trying to design a master-master setup.  I will not be using it in the performance sense of writing to both of them primarially, however I just want the secondary master to be ready as a backup and avoid the whole deal of promoting the slave to master (just point the configs to the secondary master).

I've seen some blogs describing this setup, but they didn't look too professional.  Basically your replica set is set up like a traditional master-slave, however Server 1 is both a master and slave of Server 2 and Server 2 is a master and slave of Server 1.

Are there any issues or caveats with this setup?  Is it recommended?  Will this allow me to direct writes to the secondary master, take down the primary, bring it back up, have it catch back up, then point my configs back to the original primary master?

The biggest caveat with only 2 machines in a cluster is the possibility of "split-brain", in which the nodes lose communication with one another, and each assumes that they are now the Master, possibly getting out of sync with the other.  This risk is mitigated if you can absolutely ensure that writes will not go to the 2 Masters separately while they are not in communication.  It is generally advisable to have at least 3 (and always preferably an odd number of nodes) to prevent these types of situations.

I did something similar to what you are proposing (with 3 nodes, not just two) as a MariaDB/Galera Cluster.  In actuality, it is a Multi-Master setup, in which a write to any server will be propagated to the others, but in practice I treat it as a Master-Slave with automatic failover.

For Context, I will describe how I do my Master-Slave automatic failover using HAProxy:

I use HAProxy on my application servers and set up two ports, one that my application treats as the Master for writes, and one that the application treats as the Slave for reads.  In my HAProxy, the Master port is set to use only the Master, but fail to the Slaves in a particular order.  If the Master goes down, HAProxy detects it and redirects writes to the next server, which becomes the de facto Master.

My HAProxy Slave port balances between the Slave nodes (you could include the Master, too) using one of several different balancing algorithms.  If one of Slaves goes down, HAProxy continues balancing with whatever is available until the Slave comes back up.

If the Master goes down, HAProxy re-directs and the application just works.  When the Master comes back up, it syncs with the other machines before accepting connections.  When it begins accepting connections, then HAProxy starts sending writes to it, and it becomes the Master again, and all is right with the world.

In your situation, you can do something similar, using HAProxy as your connection/failover tool.  You do not have to split out your reads and writes, but I do so as to take advantage of the extra machines.  I do like the MariaDB/Galera solution.  I would hesitate only having 2 nodes.  It feels dangerous.


Can AWS Data Migration Service be used to integrate non AWS SQL server with Redshift? I have a SQL Server 2012 hosted on a standalone machine. I want to migrate it to my AWS Redshift (already existing data warehouse).

My question is wether it is possible via AWS Data migration service ?

I am also open to other efficient methods for migration. Currently I am doing the following steps

taking a backup of the SQL server DB in the standalone server.
uploading it to AWS-S3.
Droping and restoring the Db from S3 in AWS-RDS (Sql-server)

I would like this data to be present in my data warehouse i.e AWS-Redshift

According to the following resource link, it seems you can: https://aws.amazon.com/about-aws/whats-new/2016/05/aws-database-migration-service-now-supports-migrations-to-amazon-redshift/
Or
https://aws.amazon.com/about-aws/whats-new/2017/05/aws-schema-conversion-tool-exports-from-sql-server-to-amazon-redshift/


PostgreSQL pgpool-II replication mode doesn't work? I'm experimenting with PostgreSQL and pgpool-II and trying to setup native replication mode on pgpool. The pgpool documentation says In the native replication mode, Pgpool-II is responsible for synchronizing databases.. So I didn't change any configuration on PostgreSQL, just set up two nodes via Puppet, made proper firewall configuration, so I can directly connect to nodes from other hosts on the network. pgpool-II logs seem Ok, don't show any obvious errors and pgpool starts normally, it can even connect to one of the nodes Actually it always connects to backend_hostname0. I even don't see any messages about replication itself. And when I stop the backend_hostname0 it seems like it doesn't see my second node and says: DETAIL:  all backend nodes are down, pgpool requires at least one valid node.

My pgpool configuration is like the following (I just copied that standard example config and added my posgresql backends):

replication_mode = on

backend_hostname0 = 'pg2.example.com'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/postgres/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'localhost'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/postgres/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

Maybe I missed something, can't get this thing to work.
Thank you in advance.

Postgres instances are normally replicated using Streaming replication which uses the WAL(Write Ahead Logs), which are transferred to secondary database to be replicated. So all incoming queries from application will go to the primary database and gets executed. These executions generate WALs which are then transferred to the secondary database and applied so that the secondary comes in sync with primary database.

As per pgpool documentation, the replication it uses is a statement based replication where the application connects to pgpool server instead of database(primary/secondary), and the incoming queries are then forwarded to database instances to be replicated.

There are a lot of disadvantages of using pgpool replication setup. Functions like random(), CURRENT_TIMESTAMP, and sequences can have different values on different databases, also if the pgpool crashes, no query will execute, since the application is connected to pgpool, not the database.


ignored in stored procedure with clause Hi i'm currently working with oracle that will give me my requered output.
I able to make the query but when i'm putting it in stored procedure it gives me error of Error(11,1): PL/SQL: SQL Statement ignored and Error(15,20): PL/SQL: ORA-00936: missing expression

here's  my query not in procedure:

with
t1  as (
select * from vw_break_time_doublebreak
/* where clause that satisfies your query */
where date_time >= date '2020-01-01' and date_time < date '2020-02-01'
)
,t2 as (
select shift, trunc(min(min(date_time)) over (), 'mm') mn, last_day(max(max(date_time)) over ()) mx
from t1
group by shift
)
,t3 as (
select shift, mn + level - 1 dateshift from t2
connect by mn + level - 1  <= mx
and prior shift = shift
and prior sys_guid() is not null
)
SELECT t3.shift, t3.dateshift AS date_time, count (CNT) AS COUNT
    FROM t3
    left join
    vw_break_time_doublebreak db
    on (t3.shift = db.shift and t3.dateshift = TRUNC (db.date_time))
GROUP BY t3.shift, dateshift
ORDER BY t3.shift, dateshift;

but when i put it on store procedure it has error:

create or replace PROCEDURE           sp_bt_getcountDblBreakbyshift (
   in_date      IN       VARCHAR2,
   in_date_end  IN       VARCHAR2,
   in_pagenum   IN       INT,
   p_rec        OUT      sys_refcursor
)
AS
BEGIN
   OPEN p_rec FOR

with   <-------Error here
t1  as (
select * from vw_break_time_doublebreak
/* where clause that satisfies your query */
where date_time >= date in_date and date_time < date in_date_end <------ error here
)
,t2 as (
select shift, trunc(min(min(date_time)) over (), 'mm') mn, last_day(max(max(date_time)) over ()) mx
from t1
group by shift
)
,t3 as (
select shift, mn + level - 1 dateshift from t2
connect by mn + level - 1  <= mx
and prior shift = shift
and prior sys_guid() is not null
)
SELECT t3.shift, t3.dateshift AS date_time, count (CNT) AS COUNT
    FROM t3
    left join
    vw_break_time_doublebreak db
    on (t3.shift = db.shift and t3.dateshift = TRUNC (db.date_time))
GROUP BY t3.shift, dateshift
ORDER BY t3.shift, dateshift;
END;

sorry i'm just kinda new in oracle and don't know what i'm doing wrong.
hope someone help me out with this.

date in_date, date in_date_end: that is incorrect syntax. Use TO_DATE with the appropriate format string, example: to_date(in_date_end, 'YYYY-MM-DD').

But date values should be passed with DATE type, not VARCHAR2 and all the above becomes unnecessary.


SQL Server configuration / specification recommendations and advice! I am about to deploy a new server at work as follows:
Dell T610
2 x Xeon 2.53 Quad core
24 GB RAM
1 x Perc H700 RAID controller with 1GB Non Volatile memory
8 x 300GB 15k SAS Hard drives

I am looking for recommendations of how to best configure this for use as a SQL server.
I have the following in mind:

Option 1
Allocate a 4GB RAM drive for TempDB
Group all 8 drives into large RAID 10 array for OS, Database and Log files

Option 2
Allocate 4GB RAM drive for TempDB
Use some mixture of RAID arrays (but only have one controller)!

Option 3
Any other suggestions!

For info, the databases in question consist: 1 x 4GB under heavy Read and Write use, 1 x 4GB clone for testing purposes, 2/3 other more minor databases.

What I'd do

4 disks for RAID 10 for all log files
Disk RAID 5 for all data files + OS
Don't bother with RAM disks

Why?:

With 24GB RAM all databases will be in memory so RAID 5 doesn't matter because disk reads won't really happen
Write performance is determined by log file throughput
Data files are larger then log files so RAID 5 is more efficient

What isn't ideal:

Not having one volume per database log file
Separation of local backup drives
Not having separate RAID 1 for OS + binaries

Other options:

Define "heavy write": you could go to RAID 1 for LDFs and another for OS for less than a few million rows per day


Time sheet, rates, date dependent I am trying to figure out the best database design for the following;

I have individuals (stored in Agents table) that enter their billing or time worked each day for different projects. Now we have different rates that we pay based on the person and what the project is. And we also have to account for individuals getting pay cuts or raises over time. So....

I have the following tables;

  Agents   - table of each person
  Projects - table of all projects we work
  Rates    - table with AgentID, ProjectID, StartDate, EndDate, Rate
  Billing  - table with Date, AgentID, ProjectID

So my current implementation a billing entry must be made by an agent, and I have to create a join to the rates table based on AgentID, ProjectID, and Date between StartDate and EndDate. This just seems incredibly ugly and all the billing data can be easily changed if a change were made to the rates. I'm assuming most time sheets calculate the total rate and store it in the table so it never changes unless someone specifically goes back to update the data? I'm just wondering if there is a better method for tracking this sort of data. Thanks

I take it that your main concern is tracking the changing rates and charging the prevailing rate when work was performed. That's not really a problem.

There is a pattern I call Temporal Normal Form. It's a way to track changing data using normalization -- a pattern we all know and love.

So the Rates table will be normalized according to static data:

create table Rates_S(
    ID        int identity,
    AgentID   int not null,
    ProjectID int not null,
    constraint PK_Rates_S primary key( ID )
);

and varying data:

create table Rates_V(
    RateID    int not null,
    Effective date not null,
    Rate      number( 8, 2 ) not null,
    constraint PK_Rates_V primary key( RateID, Effective ),
    constraint FK_Rates_V_Rate_S foreign key( RateID )
        references Rates_S( ID )
);

Notice there is no EndDate field. The rate, once started, continues until changed. This eliminates Row Spanning Dependencies, where different fields in different rows must remain in synch.

So now let's see how much a billing entry will cost the project. We're looking for the rate that was in effect on the date the hours are logged:

select  b.ProjectID, b.AgentID, b.Date, b.Hours * v.Rate as Charge
from    Billing   b
join    Rates_S   s
    on  s.ProjectID = b.ProjectID
    and s.AgentID   = b.AgentID
join    Rates_V   v
    on  v.RateID    = s.ID
    and v.Effective =(
        select  Max( Effective )
        from    Rates_V
        where   RateID = v.RateID
            and Effective <= v.Effective );

Notice I've added how much time is being billed to the Billing table.

This looks like a lot to get one rate, but it is a pattern and once you write it a coupla hundred times, it will sink in. It is also quite efficient.


Can OPENROWSET parameters be built dynamically? This feels like an ‘obvious “yes” ‘ kind of question, but nevertheless: would it not be possible with OPENROWSET to have sub-queries regarding the file path that import for particular procedurally-named files, such as: [Bus] + ‘-‘ + [School] + ‘-‘ + [Time (AM/MD/PM)] + ‘.xml’ ?

So for...

CREATE TABLE Route (Bus (int, 4), School (string, 4), Time (string, 2) )
INSERT INTO Route (0804,CRES,PM),(0758,CRES, MD),(1106,LHS,MD),(0804,CHMS,PM)
GO

CREATE TABLE Excel (Name (char,16))
INSERT INTO Excel (0804-CRES-PM.xml,0758-BVES-AM.xml,1106-LHS-MD.xml)
GO

UPDATE SeatingChart
FROM OPENROWSET (...’[Bus] + ‘-‘ [School] + ‘-‘ + [Route] + ‘.xml’
SET [Route]Seat = Excel.[Route]Seat
WHERE Name = [Bus] + ‘-‘ + [School] + ‘-‘ + [Time] + ‘.xml’

The file being imported from has columns for both the AM & PM seating charts in the same spreadsheet, so I’m not sure know how to about writing the generalized statement that will input into the

You could in theory do this, but it would require building the update statement with dynamic SQL. Basically, you declare a string variable, build it with metadata from the database, and then execute that string. Erland Sommarskog has an epic post on this: The Curse and Blessings of Dynamic SQL.

However, that's gonna require a lot of work and a lot of debugging.

Plus, for performance reasons, you don't really wanna fire a new OPENROWSET for every row of a table you're updating.

Instead, I'd suggest doing a single OPENROWSET call to pull all of the file contents into a temp table, and then doing your update statement from the temp table.


'Out of memory' Error for MariaDB on RDS We have a t2.medium instance on AWS' RDS platform (4gb, 2xCpu). We're using MariaDB 10.1.19 with a single database, ~120 tables and ~2gb of data in total.

After running for about a week we get this error, at first only some requests, then on the heavy requests and then it starts affecting more queries. Eventually resulting in a reboot.

Php (PDO) error:

General error: 5 Out of memory (Needed 262680 bytes)

Mysql (from error.log)

2017-06-14 10:55:29 47531169896192 [ERROR] mysqld: Out of memory (Needed 262680 bytes)

This shows the 'freeable memory' on the instance (the big spike being when we rebooted).

We're were using default mysql config. We ran mysqltuner it mentioned:

Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
join_buffer_size (> 512.0K, or always use indexes with joins)
thread_cache_size (start at 4)
performance_schema = ON enable PFS
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=1G) if possible.
innodb_buffer_pool_instances(=2)

So we've since tried increasing the join_buffer_size (to 512k) and increased the key_buffer_size (to 16M) but didn't seem to make any difference. Not sure if the error is the result of the OS having no ram available, or the mysql process itself.

Any suggestions? (Thanks)

Usually the quick fix is to decrease the biggest memory usage -- which should be innodb_buffer_pool_size.  What is that currently set to?  Do you have other applications running on the same VM?  How much room are they taking?

On a 4GB 'machine', I don't recomment innodb_buffer_pool_size be higher than 1500M, but less if the server is not dedicated to MySQL.