MySQL loading NULLs in numeric columns MySQL 5.6.23, InnoDB

I am loading tables from character delimited text files using the LOAD DATA INFILE commnd and I would like every field with a \N, which is the NULL character in this setting, to place a NULL in the table. Some numeric types have this behavior, whereas others place a 0. I am using FIELDS TERMINATED BY and some columns do properly get NULL values, so it it not a fixed-row format issue.

These are the types I've observed while testing:

INT inserts NULLs
DECIMAL(x,0) inserts NULLs
DECIMAL(x,y) inserts 0.0s
FLOAT inserts 0s
DOUBLE(x,y) inserts 0.0s
DOUBLE inserts 0s

All of the columns in question are defined with DEFAULT NULL. I know that various functions could convert these 0s into NULLs. The question is whether there is a datatype that can handle decimal precision and will also insert NULLs on load.

Additionally, I see a whole bunch of questions relating to misunderstanding of the difference between a string, an empty string, and a null value. (example example example) This is not the issue, as the NULLs are there and are loaded properly into the same column when I redefine it as DECIMAL(x,0), then improperly when defined as DECIMAL(x,3).

Very short answer : No new datatypes have been created to accommodate you.

While we are on this subject

Let's try plain SQL

USE test
DROP TABLE IF EXISTS numtest;
CREATE TABLE numtest
(
  id int not null auto_increment,
  xx decimal(10,3) default null,
  primary key (id)
);
INSERT INTO numtest (id) values (0),(0),(0),(0),(0);
SELECT * FROM numtest;

Does this work ???

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS numtest;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE numtest
    -> (
    ->   id int not null auto_increment,
    ->   xx decimal(10,3) default null,
    ->   primary key (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO numtest (id) values (0),(0),(0),(0),(0);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM numtest;
+----+------+
| id | xx   |
+----+------+
|  1 | NULL |
|  2 | NULL |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
+----+------+
5 rows in set (0.00 sec)

mysql>

OK, fine. It works with SQL. You are asking about LOAD DATA INFILE

You brought up a post I answered : MySQL is inserting "" as 0 in decimal fields. How to stop that?

Let's see if that bug was addressed since it was submitted. I will try to duplicate the code in that bug that did not work.

First let's create that table from the bug report

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE bug_repeat
    -> (
    ->   name varchar(10),
    ->   price decimal(12,6)
    -> )
    -> ENGINE=MYISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE bug_repeat\G
*************************** 1. row ***************************
       Table: bug_repeat
Create Table: CREATE TABLE `bug_repeat` (
  `name` varchar(10) COLLATE ascii_bin DEFAULT NULL,
  `price` decimal(12,6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin
1 row in set (0.00 sec)

mysql>

Next, let's make some data

C:\>type C:\MySQLDBA\bug_test.txt
name,
name,0
,
name,6
name,2
name,
name,0
name,0
name,
name,0

C:\>

Let's run the LOAD DATA INFILE

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

Ouch, what happened

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 1lue: '
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 3lue: '
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 6lue: '
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 9lue: '
4 rows in set (0.00 sec)

mysql> select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name | 0.000000 |
| name | 0.000000 |
|      | 0.000000 |
| name | 6.000000 |
| name | 2.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql>

What's the sql_mode ?

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql>

Let's blank out the sql_mode, truncate the table and reload

mysql> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.02 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 1lue: '
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 3lue: '
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 6lue: '
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 9lue: '
4 rows in set (0.00 sec)

mysql>

Let doctor the input file with \N like the bug report had

C:\>type C:\MySQLDBA\bug_test.txt
name,\N
name,0
\N,\N
name,6
name,2
name,\N
name,0
name,0
name,\N
name,0

C:\>

Let's repeat all of this with InnoDB

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE bug_repeat
    -> (
    ->   name varchar(10),
    ->   price decimal(12,6)
    -> )
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.05 sec)

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
Query OK, 10 rows affected, 4 warnings (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 4

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 1lue: 'N
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 3lue: 'N
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 6lue: 'N
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'price' at row 9lue: 'N
4 rows in set (0.00 sec)

mysql> select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name | 0.000000 |
| name | 0.000000 |
| NULL | 0.000000 |
| name | 6.000000 |
| name | 2.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql>

What version of MySQL am I using ???

mysql> show global variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.22                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql>

What about Linux ???

$ cat /tmp/bug_test.txt
name,\N
name,0
\N,\N
name,6
name,2
name,\N
name,0
name,0
name,\N
name,0

$

Logging in to mysql and trying ...

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS bug_repeat;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE bug_repeat
    -> (
    ->   name varchar(10),
    ->   price decimal(12,6)
    -> )
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.04 sec)

mysql> load data local infile 'C:/MySQLDBA/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
ERROR 2 (HY000): File 'C:/MySQLDBA/bug_test.txt' not found (Errcode: 2 - No such file or directory)
mysql> show warnings\G
Empty set (0.00 sec)

mysql> select * from bug_repeat;
Empty set (0.00 sec)

mysql> truncate table bug_repeat;
Query OK, 0 rows affected (0.04 sec)

mysql> load data local infile '/tmp/bug_test.txt'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> show warnings\G
Empty set (0.00 sec)

mysql> select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name |     NULL |
| name | 0.000000 |
| NULL |     NULL |
| name | 6.000000 |
| name | 2.000000 |
| name |     NULL |
| name | 0.000000 |
| name | 0.000000 |
| name |     NULL |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)

mysql> show global variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.21-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql>

Today's date ???

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-06-25 18:48:10 |
+---------------------+
1 row in set (0.01 sec)

mysql>

It's been a year and one week since that bug report was submitted and nothing has changed.

My answer to MySQL is inserting "" as 0 in decimal fields. How to stop that? still stands as of today.

You need to do this test against MySQL 5.6.23 and see if something has changed.


What does the node health value in a Datastax node compose of? On a datastax Cluster (6.7.8) I can run:
nodetool node_health --all
This gives a list of values for the cluster with IP addresses and health values - eg
IP Address    Node Health
1.2.3.4       1.0
1.2.3.5       0.5

Where the list of health values are between 1 and 0.
But it's not clear what this is telling you, when it is less that 1.0. I want another command to drill down into this value and find out all the things that make it up.
My question is: What does the node health value in a Datastax node compose of?

Searching for the phrase "What does the node health value in a Datastax node compose of?" brings back several links to Datastax documentation pages, which all say basically the same thing:

Node health is a score-based representation of how fit a node is to handle search queries. The node health composite score is based on dropped mutations and uptime. A higher score indicates better node health. Nodes that have a large number of dropped mutations and nodes that are just started have a lower health score.


explaining postgres query I need to make a specific query because I can't seem to find any working solution
I am creating an app like this one
https://anonymous-message-board.freecodecamp.rocks[][1]
basically, the application consists of many boards and each board have a number of threads and each thread have a number of replies
what I need to do is when some user heads to a specific board (makes a get request) I need to send him the threads of that board and the replies of each thread
the replies should be an array within the thread knowing that the threads are sorted by the created_on and limited a to 10 threads also the replies are sorted with created_on and limited to 3 results
here is :
the database tables
CREATE TABLE threads (
    _id SERIAL NOT NULL PRIMARY KEY,
    board_id INT NOT NULL REFERENCES board(id),
    text VARCHAR(255) NOT NULL,
    delete_password VARCHAR(255) NOT NULL,
    reported  BOOLEAN NOT NULL,
    created_on DATE NOT NULL DEFAULT CURRENT_DATE,
    bumped_on DATE  DEFAULT CURRENT_DATE
);

CREATE TABLE board (
    _id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE replies (
   _id SERIAL PRIMARY KEY NOT NULL,
   thread_id INT NOT NULL REFERENCES threads(id),
   text VARCHAR(255) NOT NULL,
   delete_password VARCHAR(255) NOT NULL,
   reported  BOOLEAN NOT NULL,
   created_on DATE NOT NULL DEFAULT CURRENT_DATE
);

however, I tried doing a lot of queries without getting the desired result
my main obstacle was that I need to select the replies for each thread
I tried this :
but as you know here I can't select many columns from a subquery
  SELECT  id as _id, txt as text, delete_password, reported, created_on, bumped_on ,
 (
    select * from replies
    where thread_id = thd.id
    order by created_on DESC
    limit 3
 )  subquery
 from threads thd where board_id = 1
 order by created_on DESC
 limit 10;

tried this also but didn't get the replies array as desired:
select id as _id, txt as text, delete_password, reported, created_on, bumped_on, array_agg(replies) as replies
(select count(*) replycount from replies group by thread_id)

A classical case for a lateral join:
SELECT thd.id as _id,
       thd.txt as text,
       thd.delete_password,
       thd.reported,
       thd.created_on,
       thd.bumped_on,
       r.*
FROM (SELECT *
      FROM threads
      WHERE board_id = 1
      ORDER BY created_on DESC
      LIMIT 10) AS thd
   LEFT JOIN LATERAL
      (SELECT * FROM replies
       WHERE thread_id = thd.id
       ORDER BY created_on DESC
       LIMIT 3) AS r ON TRUE;


This should do it:

SELECT p.id, latitude, longitude, z.id, z.name
  FROM points p
  LEFT JOIN zones z
    ON ( latitude BETWEEN LAT_S AND LAT_N
          AND
         longitude BETWEEN LONG_W AND LONG_E ) ;

[EDIT]

Per your comment, suppose you want to add a FK column to your points table that associates the point to its zone id.  There are some reasons why this would be not recommended, such as if your zone boundaries could ever change, you would have incorrect/inconsistent data.  However for raw performance, maybe you want the zone id denormalized into the points table, keeping in mind that it's like a cached zone id.  The MIN() operator is there to protect you in case you have two zones overlapping.  In that case, you've got bigger problems, but at least the UPDATE works, and picks the lower zone id by fiat.

ALTER TABLE points ADD (zone_id int references zone(id));

UPDATE points SET zone_id =
( SELECT MIN(z.id)
  FROM zones z
  WHERE latitude BETWEEN LAT_S AND LAT_N
          AND
        longitude BETWEEN LONG_W AND LONG_E
);


My SQL server showing different result with same query execution at different time When I execute below query from MySQL server, it shows me different output for each time even though database is stable and not used by anyone except me.

SELECT TABLE_NAME , table_rows
FROM information_schema.tables
WHERE table_schema='DBNAME'
AND TABLE_TYPE != 'VIEW'

MySQL Server Version: 5.1

I am quite confused with this behaviour.

I'm guessing you're getting varying row count in the table_row column? This is expected behaviour for InnoDB-tables.

"For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned."

http://dev.mysql.com/doc/refman/5.1/en/tables-table.html

There is more information here: http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

Notably: "ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing eight random dives to each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate because it does not take all rows into account"


What is the difference between MySQL VARCHAR and TEXT data types? After version 5.0.3 (which allowed VARCHAR to be 65,535 bytes and stopped truncating trailing spaces), is there any major difference between these two data types?

I was reading the list of differences and the only two of note are:

  For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 7.5.1, “Column Indexes”.

and

  BLOB and TEXT columns cannot have DEFAULT values.

So, because of these two limitations on the TEXT datatype, why would you use it over varchar(65535) ? Are there performance ramifications of one over the other?

divided linked to some info that explains the basic issue (there's performance differences), but it's not simple enough to say that one's always better than the other.  (otherwise, there'd be no reason to have both.)  Also, in MyISM, the 64k max size for VARCHAR isn't per field -- it's per record.

Basically, there's 4 ways to store strings in database records:

fixed length
C-style strings (marked with a NULL or similar character at the end of the string)
Pascal style strings (a few bytes to indicate length, then the string)
Pointers (store the string somewhere else)

MyISM uses something similar to #3 for VARCHAR, and a hybrid approach for TEXT where it stores the beginning of the string in the record, then rest of the string somewhere else.
InnoDB is similar for VARCHAR, but stores the complete TEXT field outside of the record.

With 1&4, the stuff in the record is always the same length, so it's easier to skip over if you don't need the string, but need stuff after it.  Both #2 and #3 aren't too bad for short strings ... #2 has to keep looking for the marker, while #3 can skip ahead ... as the strings get longer, #2 gets worse for this particular use case.

If you actually need to read the string, #4 is slower, as you have to read the record, then read the string which might be stored elsewhere on the disk, depending on just how that database handles it. #1 is always pretty straightforward, and again you run into similar issues where for #2 gets worse the longer the string is, while #3 is a little worse than #2 for very small strings, but better as it gets longer.

Then there's storage requirements ... #1 is always a fixed length, so it might have bloat if most strings aren't the max length.  #2 has 1 extra byte; #3 typically has 2 extra bytes if max length = 255, 4 extra bytes if a 64k max.  #4 has the pointer length, plus the rules for #3 typically.

For the specific implementations within MySQL 5.1, the docs for MyISM state:

Support for a true VARCHAR type; a VARCHAR column starts with a length stored in one or two bytes.
Tables with VARCHAR columns may have fixed or dynamic row length.
The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.

While for InnoDB :

The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 15 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.
For each non-NULL variable-length field, the record header contains the length of the column in one or two bytes. Two bytes will only be needed if part of the column is stored externally in overflow pages or the maximum length exceeds 255 bytes and the actual length exceeds 127 bytes. For an externally stored column, the two-byte length indicates the length of the internally stored part plus the 20-byte pointer to the externally stored part. The internal part is 768 bytes, so the length is 768+20. The 20-byte pointer stores the true length of the column.

...

as with so many other things when dealing with databases, if you're not sure what's best for your needs, try benchmarking it with similar data & usage, and see how they behave.


Mysql Server 5.6 Mysqld high memory consumption I have MySQL Server 5.6 installed in a development machine with these specs:

RAM: 4 GB
Windows 7 Professional 64 bits
Intel Pentium CPU G630 @ 2.70 GHZ

Since I installed the MySQL Server, the process mysqld.exe is consumpting 433 MB.

What I want to achieve is to reduce this consumption as much as possible.

What I have tried

I read some posts about adding some parameters to the my.ini, but inside the MySQL directory I have a file called my-default.ini so I added the following properties inside:

[mysqld]

# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
innodb_buffer_pool_size=5M
innodb_log_buffer_size=256K
query_cache_size=0
max_connections=10
key_buffer_size=8
thread_cache_size=0
host_cache_size=0
innodb_ft_cache_size=1600000
innodb_ft_total_cache_size=32000000

# per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=1K
bulk_insert_buffer_size=0
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

#settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K
performance_schema = off

Also I ran these commands to flush the queries cache:

FLUSH QUERY CACHE;
RESET QUERY CACHE;

All these attempts have not reduced an Megabyte, it stills consumption 433 MB.

Thanks in advance

Which version are you using? 5.6.?

I did however came across this bug, hopefully it helps.

http://bugs.mysql.com/bug.php?id=68287


How to make a table that could add many same number of different item? I wanted to create a table to store coupons of users.

For example, there is 3 coupon which is A, B and C etc. A user can have 10 coupon A, 20 coupon B and 5 coupon C etc.

How should I create a table to store these? There are many users in the system. How should I create a table which able to store all these information?

If I add another table just for the user and the coupon I still need to add a table which can count the number of the coupon. I have 8 type of coupon. Can I just use a single table and store each coupon for each column and row for the number of coupon?

You can't do it correctly with a single table, more exactly your database will be redundant and seriously vulnerable against inconsistencies.

You should read about database normalization.

A normalized solution would be this:

Table users, columns:

user_id (integer, also primary key)
...any other user data, name etc...

Table coupons, columns:

coupon_id (integer, also primary key)
...any other coupon data, name, value, etc...

Table user_coupon (it is the important), it is a so named bridge table, columns:

user_id (foreign key to users(user_id))
coupon_id (foreign key to coupons(coupon_id))
count (integer, shows how many coupons with id coupon_id has the user user_id)

The important thing is that the primary key on table user_coupon must be user_id and coupon_id!


Azure PostgreSQL pg_dump impact on backup "chain" I'm a SQL Serve DBA new to PostgreSQL.  Does pg_dump have an impact on the backup chain, i.e., in SQL Server if I needed a copy of a database I would peform a COPYONLY backup so as not to break the lsn sequence.  Does pg_dump impact the native Azure backups being performed in the background?

If Azure is anything like PostgreSQL, an online physical backup won't interfere with pg_dump and vice versa (except that both will generate I/O load).

pg_dump uses a REPEATABLE READ transaction which guarantees a consistent backup.


SQL Server Cluster: There is no SQL Server failover cluster available to join I have set up a SQL Server 2016 Standard cluster (Cluster-SQL) on node 1 (Cluster-Node-1) which is appearing in Windows Failover Cluster manager without issue.

When I attempt to add a second node (Cluster-Node-2) to this cluster and I reach the Cluster Node Configuration, there is no SQL Server instance name listed or available from the drop down.

My Net Admins are at a loss on this and I'm an accidental DBA from a programming background so any suggestions as to what to look for would be appreciated.

To date, we have disabled firewalls on both nodes of the cluster. The SPN's have been set up and I can access the SQL Cluster, Cluster-SQL using SSMS from both nodes.

Any other options to try that are obvious to more experienced DBA's?

Regards,
Brian

Sql Server account has to be the same on both machines, it should be Domain account ( not local user). It should have particular rights in domain, at least during installation, and it should be local administrator.
SQL server browser service should work.
You should not mess with reporting services nor "install all" option, because it may lead to slightly harder configurations scenario. It is better to focus on bare database engine services installation, and after add what you want.


MySQL operations on grouped fields I have one table t1 like this

a|b|c
-|-|-
1|1|2
1|1|3
1|1|6
1|2|3
1|2|4
1|2|7
1|3|5
2|3|2

I would like a query where the results would be similar to running multiple selects like this:

(SELECT * FROM t1  where a=1 AND b=1  LIMIT 2)
UNION ALL
(SELECT * FROM t1  where a=1 AND b=2  LIMIT 2)

....

(SELECT * FROM t1  where a=1 AND b=n  LIMIT 2)

result:

a|b|c
1|1|2
1|1|3
1|2|3
1|2|4
1|3|5

How can I accomplish that without knowing how many instances of b there are for a specific a?

In my professional experience, such bizarre queries being required are often indicative of an unnormalized schema (assuming this isn't a random one-off query you need to do). Perhaps you would be better served by giving us your actual table/column names and then letting us offer suggestions on how you can restructure your data.

BUT, just to prove to you that I've been around the block a few times, here is the actual answer to your question:

SET @a = 1, @max_count = 2, @counter = 0;
SELECT a,b,c, count FROM (
   SELECT
          IF(@counter <= @max_count,
             @counter := @counter + 1,
             @counter := 1) AS count
        , a
        , b
        , c

     FROM ( SELECT a,b,c
            FROM t1
            WHERE a = @a
            ORDER BY a,b,c
          )
       AS t1
)
AS t
HAVING count <= @max_count;

+------+------+------+-------+
| a    | b    | c    | count |
+------+------+------+-------+
|    1 |    1 |    2 |     1 |
|    1 |    1 |    3 |     2 |
|    1 |    2 |    3 |     1 |
|    1 |    2 |    4 |     2 |
|    1 |    3 |    5 |     1 |
+------+------+------+-------+
5 rows in set (0.00 sec)

You must modify the value of @a in the SET statement to the "row group" which you want. You can then ignore the count column that appears in the resultset. Note that the SET statement MUST be run each and EVERY time you want to run the actual query.


How do I stop a master from being a master in mariadb I have a database which used to be setup as a master in a topology. Now I want to remove it as master, and make it a replica. How do I do this?
Is there something similar to how to stop slave but for master? For slaves I could do:
STOP SLAVE;
RESET SLAVE ALL;

I have tried STOP MASTER; and RESET SLAVE MASTER; but those does not exist.
What would the corresponding commands be for master?

There's RESET MASTER, but that just removes the binary logs and resets the binlog file number to 1. It doesn't prevent replicas from connecting. But they will be very confused if they connect and the binlog file number has been reset.
You could revoke REPLICATION SLAVE privilege from the username used by any of the replicas that are currently connecting.
Or you could disable binary logs, which is accomplished by removing the log_bin option from your server options file and restarting. AFAIK, you cannot disable the binary log dynamically.


MSSQL: Auto-numbering rows in commit order General setup

I have an event table with an ID field and some data. Multiple producer threads should be inserting events to this table at the same time. Each batch of inserts is done in a separate transaction, as it should be atomic with respect to other data.

I also have some consumers reading from this table periodically. Each consumer has a pointer to the last ID processed within this event table.

Issue faced

After some experimenting with identity column it seems IDs are assigned to new records at insert time, not commit time. This means that I situation like this might arise:

Producer A inserts event EA into table (ID assigned: 1)
Producer B inserts event EB into table (ID assigned: 2)
Producer B commits
Consumer reads latest event EB, and sets "last processed" pointer to 2
Producer A commits
Consumer will never process event EA, because it has a lower ID, assuming already processed

Question

How do I ensure, that events (DB rows) are numbered in increasing commit order? I don't mind gaps in
the series if transactions are rolled back etc., but there should never be committed an item with a lower ID than what has already been committed (and therefore maybe already processed), and of course they need to be unique.
I would like not to lock the entire table, since many transactions run in parallel and some might run for a while.

Can I use some kind of insert trigger, stored procedure or similar to ensure this, or is it better to do some kind of post-processing in application code?

How do I ensure, that events (DB rows) are numbered in increasing commit order?

You don't.  At least not with eliminating concurrent writers.  You simply don't use a high-water mark for your readers.  Delete the rows after reading, or update them to show that they were processed.

See eg Using tables as Queues


Linking Products to Users Via Categories I'm trying to match products to users where the users has the same set of categories as the product, here is the schema:

User
———
user_id
...

Users Categories
———
user_id
category_id

Products Categories
———
product_id
category_id

Product
———
product_id
disabled
...

As the the amount of products and users grow the matching query gets slower and slower, this is basically what we have right now:

SELECT *
FROM product
WHERE product.disabled = False
AND EXISTS (
    SELECT 1
    FROM products_categories
    WHERE products_categories.category_id = ANY(
        SELECT category_id
        FROM public.users_categories
        WHERE users_categories.user_id = 'some-user-id')
AND product.id = products_categories.product_id)

This seemed to work ok for some users but is horribly slow for others. This is the explain:

Nested Loop  (cost=4367.50..26918.74 rows=44498 width=1377) (actual
time=694.695..2226.479 rows=87239 loops=1)
  InitPlan 1 (returns $0)
    ->  HashAggregate  (cost=36.01..36.02 rows=1 width=16) (actual time=0.073..0.092
    rows=18 loops=1)
          ->  Bitmap Heap Scan on users_categories  (cost=4.49..35.99 rows=8 width=16)
          (actual time=0.028..0.049 rows=18 loops=1)
                Recheck Cond: (user_id = 'some-user-id'::uuid)
                ->  Bitmap Index Scan on ix_public_users_categories_user_id
                (cost=0.00..4.49 rows=8 width=0) (actual time=0.014..0.014 rows=18
                 loops=1)
                      Index Cond: (user_id = 'some-user-id'::uuid)
  ->  HashAggregate  (cost=4331.06..4413.20 rows=8214 width=16) (actual
  time=694.507..843.826 rows=121099 loops=1)
        ->  Index Only Scan using products_categories_pkey on products_categories
        (cost=0.43..4173.20 rows=63142 width=16) (actual time=0.295..387.771
        rows=234490 loops=1)
              Index Cond: (category_id = ANY ($0))
              Heap Fetches: 117462
  ->  Index Scan using product_pkey on product  (cost=0.42..2.73 rows=1 width=1377)
  (actual time=0.007..0.008 rows=1 loops=121099)
        Index Cond: (id = products_categories.product_id)
        Filter: (NOT disabled)
        Rows Removed by Filter: 0
Total runtime: 2312.743 ms

Is there a better / faster way to perform queries like this? Im guessing I don't have my indexes set up correctly.

I'd imagine using JOINs rather than subqueries would be the first step to enhancing the speed of this thing.

Very rough example:

SELECT p.*
FROM product p
INNER JOIN products_categories pc ON p.product_id = pc.product_id
INNER JOIN public.users_categories c ON pc.category_id = c.category_id
WHERE p.disabled = False AND c.user_id = 'some-user-id'

Not gone into a lot of detail there - one of more of your JOINs might need to be an OUTER.


A table with all fields NULLable with important, sensitive data I have a requirement for a database table to hold sensitive patient information about their lifestyle (alcohol/ drug use / injecting etc).

On screen the information is sectioned into tabs - "alcohol", "drugs", "injecting" and so on. Some of the data is co-dependent (ie: data in one tab may depend on what is input in another).

I was considering storing all of the data captured in the tabs (circa 25 fields - maybe more, but not much more) in a single table, because of the aforementioned co-dependency. All of the fields in the table would be NULLable (edit: for two reasons: (1) because people might have alcohol problems but not drug problems and vice versa and (2) the data can be in DRAFT or PRODUCTION state - in draft incomplete data is acceptable, but in production relevant data must be supplied)

To improve data integrity I would employ CHECK constraints to ensure that data was supplied for sets of fields in the table IF certain BIT flags were set (e.g. we are in PRODUCTION mode and a flag named HasUsedAlcohol is set, this mandates all alcohol related fields be populated; HasUsedDrugs would mandate all drug related fields be populated)

Additionally this table may be used to produce reports.

My question is - is this a sensible, maintainable solution? And if not, why not?

Database target is SQL Server 2012.

Thanks in advance.

I wouldn't be too concerned about 25 columns.  That's not an extremely high number.  It doesn't even approach violating Swart's Ten Percent Rule (102 columns).  Of course, the "rule" there is discussing a limit, not a goal, so keep that in mind

In my mind, ideally, each of these separate "forms" (tabs) would be backed by its own table (AlcoholAbuse, DrugAbuse, Injections, etc), which would then be linked back to the patient by a foreign key constraint.

However, you mentioned that there are data validation concerns that cross forms.  Maybe if you answer "yes" to certain questions on the drugs form, then certain questions on the injections form are required.

Since CHECK constraints are really the best way to enforce what you're describing, that would be mean they all need to be in the same table.

Your other options, if things were in separate tables, would be to move validation into the application layer, or use triggers to ensure the data in the different form tables is valid on each insert / update.

Since the number of columns isn't outrageous, and you probably don't want to incur the complexity of dealing with triggers, I think one table with conditional CHECK constraints is a valid solution.

This solution seems maintainable to me, and fairly easy to reason about, since all of the "rules" are in one place (one table, and the constraints applied to that one table).


Loop through list of fields, check against lookup table I have a list of fields:
FIELD_DOMAIN_ENG_VW
+-------------+------------+-------------+
| TABLE_NAME  | FIELD_NAME | DOMAIN_NAME |
+-------------+------------+-------------+
| ENG.TABLE_1 | FIELD_1    | DOMAIN_ABC  |
| ENG.TABLE_1 | FIELD_2    | DOMAIN_XYZ  |
| ENG.TABLE_2 | FIELD_1    | DOMAIN_XYZ  |
+-------------+------------+-------------+

The view looks at all the tables in a geodatabase, and lists any fields that have a domain associated with them (a domain is the GIS equivalent of a lookup table/validation table).
The underlying tables look like this:
TABLE_1
+--------------+--------------+
| FIELD_1      | FIELD_2      |
| {DOMAIN_ABC} | {DOMAIN_XYZ} |
+--------------+--------------+
| A            | X            |
| B            | Y            |
| C            | zzzz         |
| BLACK SHEEP  |              |
+--------------+--------------+

TABLE_2
+--------------+--------------+
| FIELD_1      | FIELD_2      |
| {DOMAIN_XYZ} |              |
+--------------+--------------+
| Z            | ...          |
| Y            |              |
| X            |              |
| asdf         |              |
+--------------+--------------+

The domains look like this:
DOMAIN_VALUES_VW
+------------+------+-------------+
| DOMAIN     | CODE | DESCRIPTION |
+------------+------+-------------+
| DOMAIN_ABC | A    | EH          |
| DOMAIN_ABC | B    | BEE         |
| DOMAIN_ABC | C    | SEE         |
+------------+------+-------------+
| DOMAIN_XYZ | X    | EX          |
| DOMAIN_XYZ | Y    | WHY         |
| DOMAIN_XYZ | Z    | ZEE         |
+------------+------+-------------+

The source is an xml column in a single system table; I've extracted all the domains into this view.
Question
For validation purposes, I have made a query that will check if there are values in a field that do not match the corresponding domain:
INSERT INTO ENG.CV_ERRORS
(TABLE_NAME, FIELD_NAME, ERROR)
SELECT
    'TABLE_1' AS TABLE_NAME
    ,'FIELD_1' AS FIELD_NAME
    ,FIELD_1 AS ERROR
FROM
    ENG.TABLE_1
    LEFT JOIN
        (
        SELECT CODE
        FROM  INFRASTR.D_CV_ENG_VW
        WHERE DOMAIN = 'DOMAIN_ABC'
        )
        ON FIELD_1 = CODE
WHERE
    FIELD_1 IS NOT NULL
    AND CODE IS NULL

+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME | ERROR       |
+------------+------------+-------------+
| TABLE_1    | FIELD_1    | BLACK SHEEP |
+------------+------------+-------------+

However, this query is hardcoded to be run on a single field, in a single table at a time. I need to check all of the fields with domains, in all of the tables in the database - programmatically.
How can I do this? I'm pretty sure this can be done with PL/SQL and dynamic SQL, but I'm so new to PL/SQL that it is proving to be rather difficult.

Native dynamic SQL (in a PL/SQL anonymous block):

01    DECLARE
02        l_table_name VARCHAR2(100);
03        l_field_name VARCHAR2(100);
04        l_domain_name VARCHAR2(100);
05    BEGIN
06        DELETE FROM ENG.CV_ERRORS;
07        FOR list_fields IN (
08            SELECT
09                TABLE_NAME
10                ,FIELD_NAME
11                ,DOMAIN_NAME
12            FROM
13                ENG.FIELD_DOMAIN_ENG_VW
14            WHERE
15                TABLE_NAME NOT LIKE '%ANNO%'
16            )
17        LOOP
18            l_table_name := list_fields.TABLE_NAME;
19            l_field_name := list_fields.FIELD_NAME;
20            l_domain_name := list_fields.DOMAIN_NAME;
21
22            EXECUTE IMMEDIATE
23            'INSERT INTO ENG.CV_ERRORS
24            (TABLE_NAME, FIELD_NAME, ERROR)
25            SELECT
26                :bv1 AS TABLE_NAME
27                ,:bv2 AS FIELD_NAME
28                , ' || l_field_name || ' AS ERROR
29            FROM ' ||
30                l_table_name ||
31                ' LEFT JOIN
32                (
33                SELECT CODE
34                FROM  ENG.D_CV_ENG_VW
35                WHERE DOMAIN = :bv3
36                )
37                ON ' || l_field_name || ' = CODE
40            WHERE
41                ' || l_field_name || ' IS NOT NULL
42                AND
43                CODE IS NULL'
44
45            USING l_table_name, l_field_name, l_domain_name;
46
47        END LOOP;
48    COMMIT;
49    END;

Result set

+------------+------------+-------------+
| TABLE_NAME | FIELD_NAME |    ERROR    |
+------------+------------+-------------+
| TABLE_1    | FIELD_1    | BLACK SHEEP |
| TABLE_1    | FIELD_2    | zzzz        |
| TABLE_2    | FIELD_1    | asdf        |
+------------+------------+-------------+

Steps

Delete all existing rows in ENG.CV_ERRORS (oddly, the ODBC connection I'm using doesn't have truncate privileges for the table).
Loop through the list of fields in FIELD_DOMAIN_ENG_VW.
For each field, generate a dynamic query that looks for values that don't match the corresponding domain. Then insert them into ENG.CV_ERRORS.

It's not all that complicated now that it's all said and done. The hardest part was wrapping my head around bind variables vs. string-concatenated variables (correct terminology?), and when to use each (although I don't fully understand this yet).

Related questions

Bind variable vs. string-concatenated variable

Beginner PL/SQL: Return row value from dynamic SQL function (function, rather than a loop)

For each field name in a list of fields, get the unique values (union)


DBCC CHECKDB Notification There are plenty of questions on DBA.SE regarding DBCC CHECKDB and how to resolve problems when errors are returned. My specific question is on actually getting notified that DBCC CHECKDB returned errors. Most all DBAs know that you can automate the command and should run it often.

I came across this article by Cindy Gross, which has some very good notes. In it she mentions use of SQL Server Agent that if it finds errors from the execution of the CHECKDB command it will fail that step (or job depending on configuration). She points to Paul Randal's blog post on the topic here.

Now I am curious if anyone knows that the Check Database Integrity Task in a maintenance plan would do the same thing? MSDN does not mention that it will and I have not truthfully been an environment where it has come across a corruption issue; so can't say that it does. This would be versus simply setting up a SQL Agent Job with multiple steps that runs the specific command against each database, as Cindy suggested.

Thoughts? Obviously proof is in the pudding so providing more than just a guess would be helpful...

The Check Database Integrity Task provided in the maintenance plan issue DBCC CHECKDB WITH NO_INFOMSGS on the database selected. You can view its command by clicking the view-SQL in the task setup. If you doubt the generated SQL command, you can use SQL profiler to see its SQL command. If corruption was found, the agent job with this maintenance task will generate error and fail (with proper job step setup).

One thing to point out, running DBCC CHECKDB is equal as performing DBCC CHECKALLOC, DBCC CHECKTABLE, DBCC CHECKCATALOG and other validation. If you are running DBCC CHECKDB, you do not have to run them separately. Running them separately usually is to perform specific integrity check or need to spread out the integrity check to smaller task due to limited time to perform entire DBCC CHECKDB. More information can be found here on MSDN.


How do I determine the spid of the session that is making a query on a linked server? I have a session running on a SQL Server instance that is blocking other sessions (holding on to a lock without releasing it).

Querying sys.sysprocesses yielded the hostname of a different SQL Server instance.

I want to know that spid of the originating session that is ultimately responsible for blocking the sessions on the linked server.

How would I do that?

You would have to check running queries on the remote server. You could isolate linked server queries by filtering on your linked server name with the following dmv query.

    SELECT [s].[session_id] AS                          [spid]
       ,[s].[status]
       ,[s].[login_name] AS                         [loginName]
       ,[s].host_name AS                            [hostName]
       ,COALESCE(DB_NAME([r].[database_id]), '') AS [dbName]
       ,[r].[command]
       ,[wt].[wait_type] AS                         [waitType]
       ,[s].[login_time] AS                         [loginTime]
       ,[s].[last_request_end_time] AS              [lastBatch]
       ,[s].[program_name] AS                       [programName]
       ,[t].text AS                                 [lastSQLText]
FROM [sys].[dm_exec_sessions] AS [s]
     LEFT JOIN [sys].[dm_exec_requests] AS [r]
     ON [r].[session_id] = [s].[session_id]
     LEFT JOIN [sys].[dm_exec_connections] AS [c]
     ON [c].[session_id] = [s].[session_id]
     LEFT OUTER JOIN [sys].[dm_os_waiting_tasks] AS [wt]
     ON [s].[session_id] = [wt].[session_id]
     CROSS APPLY [sys].[dm_exec_sql_text]([c].[most_recent_sql_handle])AS [t]
WHERE [s].[status] = 'Running'
  AND [t].[text] like '%LinkedServerName%';


'packet too large' error with packet smaller than max_allowed_packet Some time ago we started storing serialized data in our MySQL database and immediately ran into the well known 'packet too large' error. We increased max_allowed_packet to 64M and all was well.

This morning, I suddenly received a too large packet 26814732 error, even though

mysql> show variables like '%packet%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)

How is this possible? I found this related post, but we are not dealing with mysqldump, we haven't changed any other database configuration settings and we've already been storing BLOBs larger than this one. What could've changed to lead to this situation?

The problem was not in fact MySQL. We use the DRb Ruby library, which gives a very similar error under similar circumstances: too large packet 26814732. Due to familiarity with the MySQL issue referenced here, I didn't read the error message well enough and didn't notice it is slightly different from Packet too Large.


is it possible to modify identify columns start with value in PostgreSQL 13 I have a table rss_sub_source, and the id as a identify column. Now I want to modify the start with value(I forget to define the start with value when I added identify column). I have tried this way:
ALTER TABLE rss_sub_source
    ALTER id SET NOT NULL,  -- optional
    ALTER id ADD GENERATED ALWAYS AS IDENTITY
        (START WITH 1233);

shows this column already as a identify column. Then I modify the SQL like that:
ALTER TABLE rss_sub_source
    ALTER id ADD GENERATED ALWAYS
        (START WITH 1233);

did not work shows SQL Error [42601]: ERROR: syntax error at or near "(" Position: 74. What should I do to make it work? This command still not work:
 ALTER TABLE rss_sub_source
    ALTER id START WITH 1233;

this is my table create command:
-- Drop table

-- DROP TABLE public.rss_sub_source;

CREATE TABLE public.rss_sub_source (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    sub_url varchar NOT NULL,
    created_time int8 NOT NULL DEFAULT date_part('epoch'::text, now()),
    updated_time bigserial NOT NULL,
    sub_status int2 NOT NULL DEFAULT 1,
    rss_type varchar(64) NOT NULL,
    standard_type varchar(32) NOT NULL DEFAULT 'RSS'::character varying,
    standard_version varchar(32) NOT NULL DEFAULT 'rss20'::character varying,
    cron varchar NOT NULL DEFAULT '0 */1 * * * *'::character varying,
    trigger_count int4 NOT NULL DEFAULT 0,
    next_trigger_time timestamp NULL,
    sub_name varchar(256) NOT NULL,
    last_trigger_time timestamptz NULL,
    tags _int4 NULL,
    source_url varchar(256) NULL,
    sub_type varchar NULL,
    intro varchar NULL,
    remark varchar NULL,
    title_hash varchar NULL,
    failed_count int4 NOT NULL DEFAULT 0,
    lang varchar NULL,
    frequency_month int4 NULL DEFAULT 0,
    reputation int4 NULL DEFAULT 0,
    rep_lastest_refresh_time int8 NULL DEFAULT 0,
    scrapy_take_time int4 NULL DEFAULT 0,
    follower int8 NULL,
    censor_status int4 NULL DEFAULT 1,
    etag varchar(64) NULL,
    last_modified varchar(32) NULL,
    editor_pick int4 NULL DEFAULT 0,
    fav_icon_url varchar(128) NULL,
    dynamic_interval int4 NOT NULL DEFAULT 0,
    local_icon_url varchar(256) NULL,
    CONSTRAINT rss_sub_source_pkey PRIMARY KEY (id),
    CONSTRAINT unique_sub_url UNIQUE (sub_url)
);

the table already have 1232 rows. I am using this command to modify the start with value:
ALTER TABLE rss_sub_source
ALTER id SET START WITH 1233;

it execute success but seems not work. How to check the table primary key current start with value?When I am using this command to check the current seed value of id:
select CURRVAL(pg_get_serial_sequence('public.rss_sub_source', 'id'))

shows greater than 1200. But when I insert a new sql in my app, still get a small seed id.

You can use setval() to change the current value of the underlying sequence.
To get the sequence's name use pg_get_serial_sequence()
select setval(pg_get_serial_sequence('public.rss_sub_source', 'id'), 1234);

or more dynamic:
select setval(pg_get_serial_sequence('public.rss_sub_source', 'id'), max(id))
from public.rss_sub_source;

Online demo


How to get multiple row data into a row with multiple columns I have a MySQL table like this:

User_Id  course_name     course_location   course_id
1        course name 1   location 1        1
1        course name 2   location 2        2
1        course name 3   location 1        3
2        course name 2   location 1        2
2        course name 4   location 4        4

How can I get data a result like this:

User_id  course 1       course2        course3        course4
1        yes-location1  yes-location2  yes-location1  NULL
2        NULL           yes-location1  NULL           yes-location4

You must Pivot data using GROUP BY with MAX aggregate and use CASE to filter by User_id.
Query:
SELECT User_id
    , MAX(
        CASE WHEN course_id = 1 THEN course_location END
    ) as Course_1
    , MAX(CASE WHEN course_id = 2 THEN course_location END) as Course_2
    , MAX(CASE WHEN course_id = 3 THEN course_location END) as Course_3
    , MAX(CASE WHEN course_id = 4 THEN course_location END) as Course_4
FROM data
GROUP BY User_id;

Sample query in SQL Fiddle.
You can replace course_location by CONCAT('YES-', course_location) is the leading YES is indeed needed.
Output:
User_Id | Course_1      | Course_2      | Course_3      | Course_4
1       | location 1    | location 2    | location 1    | (null)
2       | (null)        | location 1    | (null)        | location 4


PostgreSQL 9.3: Primary key violated by a trigger INSERT My problem

Consider a table t with many frequent updates from users, from which only the last few are relevant.

In order to keep the table size reasonable, whenever a new row is inserted old rows from the same user_id are deleted. In order to keep an archive, the row is also written to t_history.

Both t and t_history have the same schema, in which id is a bigserial with a primary key constraint.

Implementation

Stored procedure

CREATE FUNCTION update_t_history()
RETURNS trigger
AS
$$
declare
BEGIN
    -- Insert the row to the t_history table. `id` is autoincremented
    INSERT INTO t_history (a, b, c, ...)
    VALUES (NEW.a, NEW.b, NEW.c, ...);

    -- Delete old rows from the t table, keep the newest 10
    DELETE FROM t WHERE id IN (
                  SELECT id FROM t
                  WHERE user_id = NEW.user_id
                  ORDER BY id DESC
                  OFFSET 9);
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

Corresponding insertion trigger:

CREATE TRIGGER t_insertion_trigger
AFTER INSERT ON t
FOR EACH ROW
EXECUTE PROCEDURE update_t_history();

The error

The trigger works well, but when I run a few dozen insertions in a single transaction, I get the following error:

BEGIN
ERROR:  duplicate key value violates unique constraint "t_history_pkey"
DETAIL:  Key (id)=(196) already exists.

Updates

The id field in both tables (from \d+ t):

id|bigint|not null default nextval('t_id_seq'::regclass)
"t_pkey" PRIMARY KEY, btree (id)

PostgreSQL version is 9.3.

Any idea why the stored procedure breaks the primary key constraint in transactions?

Why is t_history.id auto-incremented in the first place? If "both t and t_history have the same schema", and t.id is a serial PK, you can just copy whole rows.

I would also suggest you only copy rows you actually delete from t to t_history - in a data-modifying CTE. This way you do not have overlapping rows (which might be part of the problem).

CREATE FUNCTION update_t_history()
  RETURNS trigger AS
$func$
BEGIN
   -- Keep the newest 10, move older rows to t_history
   WITH del AS (
      DELETE FROM t
      USING (
         SELECT id
         FROM   t
         WHERE  user_id = NEW.user_id
         ORDER  BY id DESC
         OFFSET 10      -- to keep 10 (not 9)
         FOR UPDATE     -- avoid race condition
         ) d
      WHERE t.id = d.id
      RETURNING t.*
      )
   INSERT INTO t_history
   SELECT * FROM del;   -- copy whole row

   RETURN NULL;         -- irrelevant in AFTER trigger
END
$func$  LANGUAGE plpgsql;

The new row is already visible in an AFTER trigger.


WITH ... AS statement not working as expected I get this error:

It arises when I try to execute the following query:

with expensive_service as (
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)
select *
from service except expensive_service;

I was trying to implement WITH ... AS (link to PostgreSQL docs).

This query gives me the desired output I'm looking for:

select *
from service except (
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)

Any aid directing me where the error lies would be greatly appreciated!

The CTE (common table expression) is like a view and you need to treat it like any other view (or table). You need a complete SELECT statement as the second part of the EXCEPT:

with expensive_service as (
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)
select *
from service
except
select *
from expensive_service;

Note that parentheses around the individual parts of a UNION, EXCEPT or INTERSECT are not needed (unless you need an ORDER BY for only that query, e.g. because you are using a LIMIT clause).

So your original query could (should in my opinion) also be written as

select *
from service
except
select s1.*
from service s1, service s2
where s1.price > s2.price


Quick search the most similar objects in the n-dimensional space Lets assume that we have a points in the n-dimensional space. So we have a n coords(n columns) which can describe location of the each point.

We need to implement a table which can be used for a quick searching the most similar points, i.e. points which have the smallest distance to the desired point.

E.g. points in the db:

id  c1  c2  c3  c4  c5
1   5   19  42  12  16
2   3   23  38  15  12
3   14  21  32  33  1
4   12  29  21  24  5

If we want to find the best matching for point with coords:

c1 c2  c3  c4  c5
4  20  40  14  15

We will get points with id 1 and 2.

We also have mean coordinate for each dimension(column) and vector for each point in which first element - number of the dimension in which point has the largest difference from the mean coordinate in this dimension, and last - number of the dimension in which point has the smallest difference. Maybe it can be used for the more rapid filtering points which have the biggest distance to the desired point.

So how can I do something like this using MySQL?

I think the composite index and order by abs(cx - $mycx) can be a good solution, but I can't use it because I will have more then 16 columns which I need to include in the one index.

Any help will be very useful!


Make sure partitioning is working I created a partition for a table. There is a column "archived" (tinyint). 0 goes to PRIMARY data file, 1 to ARCHIVED data file.

ALTER DATABASE MYDB ADD FILEGROUP [ARCHIVED];

ALTER DATABASE MYDB ADD FILE (NAME = N'ARCHIVED', FILENAME
  = N'D:\...\MYDB_ARCHIVED.mdf' ...) TO
  FILEGROUP [ARCHIVED];

CREATE PARTITION FUNCTION FNArchived (tinyint) AS
RANGE LEFT FOR VALUES (0);

CREATE PARTITION SCHEME SCArchived
    AS PARTITION FNArchived
    TO ([PRIMARY],[ARCHIVED]);
GO

The I dropped the primary key and added this :

ALTER TABLE mytable ADD CONSTRAINT PK_mytable PRIMARY KEY NONCLUSTERED  (uid) ON [PRIMARY];
CREATE CLUSTERED INDEX IX_mytable_archived ON mytable (archived) ON SCArchived(archived);

All seems fine there is no error.

But how can I make sure that data is written to the right data file?

In order to validate your partitioning scheme, you can use the $PARTITION system function, detailed on MSDN at http://msdn.microsoft.com/en-us/library/ms188071.aspx

  Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function

So for your example, you could run:

SELECT $PARTITION.FNArchived (1);

or

SELECT $PARTITION.FNArchived (0);

To determine which partition each value will be stored in.


PostgreSQL pg_dump -E encoding option not working I have a UTF8 database qdb and I want to back it up to a plain file using the same UTF8 encoding. I am using pg_dump as I don't have pgAdmin working now. I however can't get pg_dump to output a UTF8-encoded plain SQL dump file, as this is not working:
C:\Program Files\PostgreSQL\12\bin> .\pg_dump.exe -U postgres -F p -O -c -C -E UTF8 qdb > d:\qdb.sql

The output dump file is encoded as UCS-2 LE BOM nonetheless, which makes some German accented letters display incorrectly. I am using Windows 10 and PostgreSQL 12.4. How can we make pg_dump actually dumps a plain SQL UTF8 file?

Forcing a BOM and UTF-16 encoding for redirected output can be done somehow by Windows shells (personally I've experienced this with Powershell and Windows 10).
See e.g. Resolve UTF-8, UTF-16, ASCII inconsistencies for some context.
In the case of pg_dump, using the -f option to specifiy the output file instead of a shell redirection seems like a good idea against that sort of problem. From the manual:

-f file
--file=file
Send output to the specified file. This parameter can be omitted for file based output formats, in which case the standard output is
used.


pg_dump Error while running backup I encountered an error on PostgresSQL. Here is details for the error which i have seen during the backup.

 pg_dump: SQL command failed
 pg_dump: Error message from server: ERROR: found toasted toast chunk for toast value 16431418 in pg_toast_16431418
 pg_dump: The command was: COPY.XXXXXXXXXXX

So what I did:

 VACUUM ANALYZE <table_name>;

it showed me the same errors, then I ran:

 SET zero_damaged_pages = on;
 VACUUM FULL <table_name>;

it zeroed out the bad page header, then I ran:

 REINDEX table pg_toast.pg_toast_16306418;

After running this command, I checked the other process which could be waiting but nothing was in the waiting except this query taking up 99% of CPU cycles. I waited for an hour and seems nothing was happening, I terminated the query.

Then I ran full vacuum and reindexing of database but vacuum again failed

 VACUUM
 RESET

vacuum did not complete

I thought zero-ing out the invalid page header would allow me to back up the database but it did not do anything. I have checked the memory, RAM, hardware etc. and that all looks pretty good. Can someone suggest something please?

I am fairly new to postgres so please be specific, if you can.

What the error message suggests is a bit flipped in this pg_toast_16431418 relation on the exact place where the size is stored (the data size has the information that it's toasted in its upper bits). See TOAST in the 8.4 doc for details.

This does not suggest a problem with the page header, or with an index, or with the catalog.

BUG #5929 indicates it may be caused by a hardware problem, especially if the server doesn't use ECC memory.

You might want to try pageinspect to peek at the faulty data. If the damage is just a flipped bit, in theory it may be flipped back by writing directly at the data page as the last resort.

There are many steps involved:

do a file-level copy with the db shut down and work on the copy. Turn off autovacuum or anything that may touch the data.
find the main table corresponding to the pg_toast table. See Postgres pg_toast in autovacuum - which table?
Select each row of this table one by one until the failure occurs. Note the ctid of the row. It contains the offset of the page containing the row, and the offset of the row withing the page.
Dump the page with get_raw_page(relname text, blkno int) and examine the row. Use postgres doc to parse the data. You want to extract the pointer into the pg_toast_NNN relation.
Convert the pointer into a page (I don't know how to do that. I'd need to dive into the doc). Dump the corresponding page of pg_toast_NNN with get_raw_page() again.

It's no small feat if you don't have any prior knowledge of any of that stuff.
All the information is in the documentation, but in a compact form.


How do you get a count for an aggregated row? I am getting sums for a set of rows, There is an id from each row that relates to 0 to many rows in another table. I am having trouble getting that number into the dataset. So here is what I have which works as I need it to:

SELECT
  sp.advcode         AS Advertiser,
  count(*)           AS Created,
  sum(CASE WHEN i.sent IS NOT NULL THEN 1 ELSE 0 END)    AS Sent,
  sum(CASE WHEN ((i.active = 1) AND (i.sent IS NULL)) THEN 1 ELSE 0 END) AS ActiveUnsent,
  count(i.inquiryId) AS Total,
  sum(CASE WHEN i.campaignLeadId IS NOT NULL THEN 1 ELSE 0 END) AS CampaignLeads,
  sum(CASE WHEN i.active = 0 THEN 1 ELSE 0 END) AS Inactive
FROM inquiry i
LEFT JOIN schoolProfile sp ON sp.schoolProfileId = i.schoolProfileId
WHERE sp.advcode LIKE 'al007'
  AND i.dateCreated BETWEEN '2016-02-02' AND '2016-03-02'
GROUP BY i.schoolProfileId;

I need to count how many times i.inquiryId appears in another table, per returned row. If someone has a clue I would be most grateful.

SELECT  sp.advcode AS Advertiser,
        ii.*
    FROM
      ( SELECT  count(*) AS Created,
                COUNT(i.sent) AS Sent,
                sum((i.active = 1) AND (i.sent IS NULL)) AS ActiveUnsent,
                count(i.inquiryId) AS Total,
                COUNT(i.campaignLeadId) AS CampaignLeads,
                sum(i.active = 0) AS Inactive
            FROM  inquiry i
            WHERE  i.dateCreated >= '2016-02-02'
              AND  i.dateCreated  < '2016-02-02' + INTERVAL 1 MONTH
            GROUP BY  i.schoolProfileId
      ) AS ii
    LEFT JOIN  schoolProfile sp ON sp.schoolProfileId = ii.schoolProfileId ;
    WHERE  sp.advcode LIKE 'al007'

Notes:

By not having a JOIN, the counts are more likely to be correct.
COUNT(nullable-column) simply count how many non-nulls there are
Boolean expressions evaluate in SUM() as 0 or 1, thereby obviating the CASE.
In your BETWEEN, you probably included an extra day.
i needs INDEX(dateCreated)
I assume you are using InnoDB and schoopProfileId is the PRIMARY KEY of schoolProfile?
If you really need LEFT, then you might get NULL for Advertiser?
If you don't need LEFT, then ...

The following might be faster:

SELECT
      ( SELECT  advcode
            FROM  schoolProfile
            WHERE  schoolProfileId = i.schoolProfileId
              AND  advcode LIKE 'al007'
      ) AS advertiser,
        count(*) AS Created,
        COUNT(i.sent) AS Sent,
        sum((i.active = 1) AND (i.sent IS NULL)) AS ActiveUnsent,
        count(i.inquiryId) AS Total,
        COUNT(i.campaignLeadId) AS CampaignLeads,
        sum(i.active = 0) AS Inactive
    FROM  inquiry i
    WHERE  i.dateCreated >= '2016-02-02'
      AND  i.dateCreated  < '2016-02-02' + INTERVAL 1 MONTH
      AND  EXISTS
      ( SELECT  *
            FROM  schoolProfile
            WHERE  schoolProfileId = i.schoolProfileId
              AND  advcode LIKE 'al007'
      )
    GROUP BY  i.schoolProfileId;


Is mysqldump a "hot" backup kind? I see in the documentation that the database backup tools are divided into four categories: hot backups, cold backups, physical backups and logical backups.

I understand the the most important difference between cold and hot backup is that the latter can be done while the database is operating and receiving read&write queries (and the result will be consistent and atomic).

But then, how do I know if mysqldump has this "hot" aspect or "cold" aspect? The documentation just places it in the logical backup category and is not 100% clear about this (yes, it seems to mention that it uses the same snapshot of the DB at the beginning of the mysqldump procedure, which suggests it's definitely a HOT backup tool, but just wanted to double check here).

The tables use InnoDB engine.

First of all, categorizing the backup into the four mentioned categories is not accurate. You can categorize into (hot, warm, cold), and into (physical, logical). In other words, the backup can be cold and physical, or, hot and physical, etc. It cannot be (physical and logical).

Notice please that physical backup is better called "Raw backup", which is a copy of the data files, and other related file, at filesystem level.

mysqldump is logical, i.e. it produces the statements that re-create the DB and/or the tables and/or the data, etc. Therefore it is not "physical"

"Hot Backup" means that the backup can happen without affecting the users, and without taking the application or DB server down, AND, the resulting backup is consistent. There are different strategies and tools to make hot backups. Sometimes, it is conditional to the storage engines you use.

To answer your question: mysqldump is not hot backup tool by default. This is especially true if you are using MyISAM among other engines, and/or tables are being locked during the backup.

But if your tables are pure InnoDB, and you use the --single-transaction option with mysqldump, then, yes, it would be considered hot.

HTH


High CPU and plan cache instability In the past couple of months my SQL Server instance has been experiencing higher than usual CPU usage than before, at times hitting 100% and affecting an application.
There have been no recent changes to any SQL Server configurations or options in that time, the OS may have in terms of Patching but that is another teams area, and it was not operating like this before the past couple of months.When we have high sustained period of CPU use, I see mostly wait stats of CXPACKET, LCK_M_X, THREADPOOL, LATCH_EX, RESOURCE_SEMEPHOR_QUERY_COMPILE.  CXPACKET for parallelism being by far the largest.
I like to run Brent Ozar’s sp_blitzcache and the one thing that stands out is that the plan cache seems to be cleared by something and 99% of the plans are created in the last hour.  This says this could be memory pressure or plan cache instability.I’m not sure what to check of where to look.
I have thought of enabling LPIM which isn’t now but besides this not sure what to try and I would be grateful for any help.
Edit**
I had started to log sp_whoisactive into a table. I found that there was a long running process of the application that was running a very intensive denormalisation process on the database, that gradually built and built until it finally starte a stage when it rebuild indexes.
Obviously this is what killed server performance.  We have made sure this does not run again.
However the business has become very twitchy about the appplication/server.  On friday there was a period when again the CPU maxed out to 100% for about 10 mins, unlike before when it lasted longer, this time it stopped after the 15mins. From looking at the results of sp_whoisactive I can see before the CPU got maxed I started to see a high number of RESOURCE_SEMAPHORE_QUERY_COMPILE wait types, these carried on for about 10 mins. I kind of understand what this wait type is, but did this cause the high CPU for the period, as some kind of cumulative effect of threads waiting?
If it was, how can I fix this so that it won't happen again?
I don't have any scope to tune queries/IXs, would adding more memory help?
*****Edit 2 *****
Update on thisIt seems that the inital issue was another team who started to run a Denormalisation process on the database that was really intensive and just killed the server.
After they were stopped doing this I  recieved very few RESOURCE_SEMAPHORE_QUERY_COMPILE wait types unlike before
The situation was much better than before, however CPU was still hitting 100% at times for longer than I wanted and I was still experiencing plan cache instability, it was continually being trimmed.
After coming across this webpage - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6b9118f3-b6c1-4f39-ae80-d149392656f9/sql-server-2016-memory-pressure-leads-to-the-plan-cache-clearing?forum=sqldatabaseengineI realised that something at the os level had changed and must have been needing more memory, causing memory pressure and then it taking memory resource away from sql server and the plan cache.
I lowered my instance max server memory setting and things have settled down much more, CPU overall is significantly lower and rarely hitting 100% and the plan cache is a nice size and never being significantly trimmed down.

This DBA.StackExchange answer gives a good place to start tracing the issue.
Specifically the linked Microsoft Docs - Plan Caching in SQL Server 2008 docs (albeit a little dated, but still useful) have a section called Plan stability-related reasons of batch recompilations that details all the reasons the plan cache can be cleared out (you should also read the previous section Causes of Recompilations for some useful info).
There are too many reasons to quote from the above article but the one section I find particularly interesting and might be the easiest place to start is the following:

The following operations flush the entire plan cache, and therefore,
cause fresh compilations of batches that are submitted the first time
afterwards: Detaching a database, Upgrading a database to SQL Server
2005, Upgrading a database to SQL Server 2008, Restoring a database, DBCC
FREEPROCCACHE command, RECONFIGURE command, ALTER DATABASE ... MODIFY
FILEGROUP command, Modifying a collation, using ALTER DATABASE … COLLATE
command

In my experience, I've personally seen developers schedule commands such as DBCC FREEPROCCACHE to run routinely and cause similar issues. (Additionally I've seen overuse of the OPTION (RECOMPILE) and WITH RECOMPILE clauses all throughout a database causing similar issues as well.)
If you can eliminate the above checklist then you should follow the rest of the guide in the Plan stability-related reasons of batch recompilations docs, maybe looking next at how often you guys are running DDL queries that would cause plan cache recompilation, and then how often are you guys updating statistics across the board.
It's unfortunately going to be an elimination game most likely and won't be a trivial issue to solve, but best of luck.


SSIS ETL Question/Option In our current prod environment, when the ETL launches, it first checks an environmental variable at the OS level which tells it where the "config" database and table is for it to get the necessary values/parameters for running.

In our test environment though we will have test and QA on the same physical box, but using separate instances of SQL Server.  Feedback I am getting is that since they are on the same physical box, that means one env variable, so we would have to change the env variable depending on whether ETL was running in test or QA.  This is less than desirable.

I understand not wanting to hard code anything into the packages but there has to be a way to accomplish such a thing with multiple environments on the same physical box.  I can't justify splitting out onto separate physical hardware for each env.

What have others done.  Thoughts?  What has worked and what has not?

We do this, the environment varaiable is at the user level, so the user for each environment that runs the SQL agent jobs is different. So on the the dev environment our agent user is something like SQLDev and on the QA environment it is something like SQLQA.

If you are not running from jobs (which I highly suggest doing except on dev while doing actual development), yes you have to change the enviroment variable to the correct one. We have created cmd line scripts to do that easily and placed them on our desktops, so that we can easily switch the environment variables.


MariaDB: how to reduce ibdata file size I found that My MariaDB's ibdata file keep increasing.
So, I've searched for this, and found that innodb_file_per_table should be set as 1.
but, my DBMS's configuration has already set as 1;
Why ibdata file size keep increasing and what else should I do for this.
These below are my dbms info.
DBMS: MariaDB
engine: InnoDB Engine
version: 10.3

** my.cnf **
innodb_file_per_table=ON
transaction-isolation=READ-COMMITTED

The first thought that came to mind was "Wow !!! You must have rather big transactions."
According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace, these are the main issues that cause ibdata1 to grow:

Lots of Transactional Changes
Very Long Transactions
Lagging Purge Thread

The undo logs inside ibdata1 will hold lots of info to support transaction isolation levels. Since you are using READ-COMMITTED, this growth can be more significant.
I have discussed this  before in the DBA StackExchange

Aug 21, 2015 : Transactional DDL workflow for MySQL
Jun 16, 2014 : MySQL Index creation failing on table is full
Apr 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
... and more

SUGGESTION #1
Since you are using MariaDB, please look into configuring the undo logs outside of the system tablespace. Please read the MariaDB docs on innodb_undo_directory and innodb_undo_tablespaces and go from there.
SUGGESTION #2
You may have to mysqldump are your data and load it into a new installation of MariaDB. See my posts in How do I shrink the innodb file ibdata1 without dumping all databases? for other ideas. Either way, this will take some work !!!


How to get the nearest date to a record date in sql? I have the following data structure:

Table A:

ID  |  RequestNumber  |  Date
----+-----------------+-----------
 1  |      1          |2017/09/27
 2  |      1          |2018/06/02

Table B:

RequestNumber  |  Serial  |  Date
---------------+----------+----------
     1         |    1     |2017/09/27
     1         |    2     |2017/09/27
     1         |    6     |2018/06/03
     1         |    7     |2018/06/03
     1         |    8     |2018/06/03

As we can see the nearest date to the first row of Table A is 2017/09/27  in the Table B and the nearest date to the second row is 2018/06/03 in the Table B

So...

I need a query to have each row from Table A with all rows from Table B that is the nearest to the record from Table A (it means 2 records should be returned the first record and 3 records should be returned for the second record)

The expected result would be:

ID  |  RequestNumber  |  Serial  |  Date
----+-----------------+----------+------------
 1  |      1          |    1     | 2017/09/27
 1  |      1          |    2     | 2017/09/27
 2  |      1          |    6     | 2018/06/03
 2  |      1          |    7     | 2018/06/03
 2  |      1          |    8     | 2018/06/03

Thanks in advance

Inner sub-query returns the nearest date.

SELECT
    ta.ID,
    ta.RequestNumber,
    tb.Serial,
    tb.dt
FROM
    ta
JOIN
    tb
    ON tb.RequestNumber = ta.RequestNumber
    AND tb.dt = (SELECT TOP 1
                     dt
                 FROM
                     tb
                 WHERE
                     RequestNumber = ta.RequestNumber
                     AND dt >= ta.dt
                 ORDER BY
                     RequestNumber,
                     dt ASC);

Or you cans use a DENSE_RANK to flag rows by RequestNumber + Date and get the rank = 1.

SELECT
    ta.ID,
    ta.RequestNumber,
    tb.Serial,
    tb.dt
FROM
    ta
CROSS APPLY (
             SELECT RequestNumber, Serial, dt,
                    DENSE_RANK() OVER (PARTITION BY RequestNumber
                                 ORDER BY RequestNumber, dt) rn
             FROM tb
             WHERE tb.RequestNumber = ta.RequestNumber
                   AND tb.dt >= ta.dt
            ) tb
WHERE rn = 1;

Both queries return the same result:

ID | RequestNumber | Serial | dt
-: | ------------: | -----: | :------------------
 1 |             1 |      1 | 27/09/2017 00:00:00
 1 |             1 |      2 | 27/09/2017 00:00:00
 2 |             1 |      6 | 03/06/2018 00:00:00
 2 |             1 |      7 | 03/06/2018 00:00:00
 2 |             1 |      8 | 03/06/2018 00:00:00

db<>fiddle here


How does the transactions interact if you set a different isolation level per connection? I read that I can have different isolation levels per connection and in the server.
The default isolation level is REPEATABLE-READ.
So if I have a transaction that issues a SERIALIZED to the client connection, how does it interact with other transactions from other client connections? Do they all end up being serialized? From set transaction seems not:

  With the SESSION keyword, the statement sets the default transaction
  level for all subsequent transactions performed within the current
  session.

But this seems useless to me. I mean if I set serialized to a client connection then I would want all currently running transactions to be sequential, right? Otherwise what's the point?

Every connection will work with its own transaction isolation level. Setting SERIALIZABLE level for one connection doesn't change isolation level of other connections (new or existing).

However interactions between transactions performed on different isolation level are quite complicated. Everything depends on many factors:

If different transactions operate on the same table/tables?
How your database engine implements transaction isolation levels (pessimistics versus optimistics locking)?
What is characteristics of access from your app to particular tables?

If you have to work with SERIALIZABLE level for some connections in your app you should know answers for above questions.

For example. If you have database with pessimistics locking (e.g. IBM DB2) and you have long running transactions with SERIALIZABLE level it is very probable that your system would hang (or slow down).


I hit the EXEC sp_configure 'show advanced options', 1; But the "backup checksum default" is not showing I followed the steps in Sql server 2012

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sys.sp_configure 'backup checksum default', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

I get the following error message:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 69
The configuration option 'backup checksum default' does not exist, or it may be an advanced option.

Please help me

If you are sysadmin on the SQL Server instance, then you can run the following script to see which options are available:

SELECT [name] FROM sys.configurations

This will give you the following possible setting on a SQL Server 2012 instance:

configuration_id name
---------------- -----------------------------------
101              recovery interval (min)
102              allow updates
103              user connections
106              locks
107              open objects
109              fill factor (%)
114              disallow results from triggers
115              nested triggers
116              server trigger recursion
117              remote access
124              default language
400              cross db ownership chaining
503              max worker threads
505              network packet size (B)
518              show advanced options
542              remote proc trans
544              c2 audit mode
1126             default full-text language
1127             two digit year cutoff
1505             index create memory (KB)
1517             priority boost
1519             remote login timeout (s)
1520             remote query timeout (s)
1531             cursor threshold
1532             set working set size
1534             user options
1535             affinity mask
1536             max text repl size (B)
1537             media retention
1538             cost threshold for parallelism
1539             max degree of parallelism
1540             min memory per query (KB)
1541             query wait (s)
1543             min server memory (MB)
1544             max server memory (MB)
1545             query governor cost limit
1546             lightweight pooling
1547             scan for startup procs
1549             affinity64 mask
1550             affinity I/O mask
1551             affinity64 I/O mask
1555             transform noise words
1556             precompute rank
1557             PH timeout (s)
1562             clr enabled
1563             max full-text crawl range
1564             ft notify bandwidth (min)
1565             ft notify bandwidth (max)
1566             ft crawl bandwidth (min)
1567             ft crawl bandwidth (max)
1568             default trace enabled
1569             blocked process threshold (s)
1570             in-doubt xact resolution
1576             remote admin connections
1577             common criteria compliance enabled
1578             EKM provider enabled
1579             backup compression default
1580             filestream access level
1581             optimize for ad hoc workloads
1582             access check cache bucket count
1583             access check cache quota
16384            Agent XPs
16386            Database Mail XPs
16387            SMO and DMO XPs
16388            Ole Automation Procedures
16390            xp_cmdshell
16391            Ad Hoc Distributed Queries
16392            Replication XPs
16393            contained database authentication

  WARNING: Please do NOT change configuration settings in the table directly. Always use sp_configure <option> <value> to modify the instance.

Or you could read up on the configuration options on Technet:

Server Configuration Options (SQL Server 2012)


How to improve query count execution with mySql replicate? For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table).

And its really time consuming when it comes to million records data.
So I decided to use mySql Replicate to create a slave db for my reporting purpose. But I'm still struggling with these things:

Are you willing to duplicate a part of your db in exchange for query speed ?
Is there any way to add data to user_type column automatically in 'user_activity' table (slave db) when they are syncing?
And how to optimize database for reading purpose? (reporting data, analytics,..)

update:
this is my create sql

Table user

CREATE TABLE `library_th`.`user` (
`id` INT UNSIGNED NOT NULL ,
`username` VARCHAR(100) NOT NULL ,
`password` VARCHAR(32) NOT NULL ,
`user_type` TINYINT NOT NULL DEFAULT '0' COMMENT 'this user is created automatically by system or by them himself?' ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;

Table user_activity:

CREATE TABLE IF NOT EXISTS `user_activity` (
  `id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `action` int(11) NOT NULL DEFAULT '0' COMMENT 'what activity did user do?',
  `datetime_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `user_activity`
  ADD KEY `user_id` (`user_id`);

and my count query that I'm wanna use is:

select count(ua.id) as `total`, u.user_type, ua.action from user_activity ua
inner join user u on ua.user_id = u.id
group by ua.action, u.user_type

Give this a try:

Add this composite index to user_activity:  INDEX(user_id, action).  Then

SELECT SUM(x.ct), u.user_type, x.action
    FROM (
        SELECT user_id, action, COUNT(*) AS ct
            FROM user_activity
            GROUP BY user_id, action
         ) x
    JOIN user u  ON x.user_id = u.id
    GROUP BY x.action, u.user_type;


SQL MATCH AGAINST incomplete string with space I have MySQL database of places, and I want to use the MATCH (...) AGAINST (...) function to find places with two-part names, such as New York. This is a problem because the match against syntax will recognize the second part of the name as an 'OR' condition.

SELECT * FROM places WHERE MATCH (...) AGAINST ('New York')

Will match against columns containing the word "New" or the word "York".

How can I specify the place as one string to match against, or is there a way to escape spaces in the query?

If there is no solution, then I accept workarounds to the problem if I only get results from my database that contain the two-part name.

EDIT: I'm using this for a program where a user types and I query my database for suggestions to predict what the user could be typing, so I need to match against a string that is probably incomplete.

If you want to use Full-Text Search, then in order to get an exact phrase match it would seem that doing a Boolean Full-Text Search would be the way to go, enclosing the term New York in double-quotes, hence:

SELECT * FROM places WHERE MATCH (field) AGAINST ('"New York"' IN BOOLEAN MODE);

But, if you are looking to do a "contains" type of search where the user-entered value is a literal fragment (i.e. what they entered needs to match as-is, but can also be a part of a larger string and is not necessarily the entire match), then perhaps doing a regular LIKE '%{user_input}%' would be best.


Table Relationship cardinality I am designing a database for my application, I got issue in implementing the following logic.

A user may be a Vendor and also a Buyer. I have created a table for USER and created an another table called USERTYPE in that, the type had 'VENDOR' and 'BUYER' but I have to know if a user is both 'vendor' and 'buyer' by using a same email_id (User_id). Any idea or suggestion to implement this logic. Thanks in advance.

Yes, if a user can have many (e.g. up to 2) types, and each type of course has many users, then this is a many-to-many relationship.

Users >--------< UserTypes

Any logical many-to-many relationship needs a third physical table that references both.

Users ----< UserHasType >---- UserTypes

For example, this table could look like the following:

CREATE TABLE UserHasType (
  user_id INT,
  type_id INT,
  PRIMARY KEY (user_id, type_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id),
  FOREIGN KEY (type_id) REFERENCES UserTypes(type_id)
);


DB2 Online Restore but Without Roll Forward? I read a lot of documentations for db2 restore but I could not find how to perform online restore from the last database backup but without roll forwarding of logs?

I will appreciate command example.

On example my last online backup is made 1st february.
I want to do ONLINE RESTORE of that backup but without logs after 1st February (similar with offline restore option WITHOUT ROLL FORWARD).

I am using db2 9.7

Thank you in advance

As per Information Center:

  "If this option is specified when restoring from an online backup
  image, error SQL2537N will be returned."

So you you are left with rolling forward. That being said, you could try the following

db2 rollforward db mydb to end of backup and complete

According to the information for rollforward command

  END OF BACKUP
      Specifies that all partitions in the partitioned database should be rolled forward to the minimum recovery time. See Examples section
  below for an example.

This should restore whatever it is you are attempting to restore up to the point of the end of the backup and not include any rolling forward through transactions logs that may be occurring after the backup. Normally you still have to issue at least this statement for a restore from an offline backup where you use archived logs.

I personally haven't tried nor had the need to do an online and only rollforward to the end of the backup. Give it a shot and let us all know what happens.

  EDIT: To answer your questions below: You must have archival logging
  enabled in order to take online backups. You can still take offline
  backups with archival logging. You do not have to switch back to
  circular to do so. However, once you have archival logging enabled,
  regardless of whether you are restoring from an offline or an online
  backup, you must perform a rollforward when you are done. No way
  around it. See my own previous question on this.

  In order to set up archival logging, you will need to alter the
  database configuration parameter LOGARCHMETH1. By default, this is
  set to OFF, which means circular logging. Your choices for it are as
  follows:

  DISK
  TSM
  USEREXIT
  VENDOR
  LOGRETAIN

  LOGRETAIN tells DB2 to leave the archived logs in the logging
  directory. It will not clean up any of these logs for you (leaving you
  as the DBA to do so). USEREXIT leaves it up to a non-IBM third party
  tool to take care of storing and archiving your logs (usually hand coded in C). Not too
  many people use this anymore from what I understand. DISK and TSM seem
  to be used a fair amount. DISK tells DB2 where you want to archive
  your logs off to (although with a few other parameters set, DB2 can
  automatically clean up logs no longer needed-I'll show you that
  below). TSM tells DB2 to let IBM's Tivoli Storage Manager to archive
  off the logs. There are a few other settings you have to get into for
  that to work. Where I work, we haven't tried that yet.
  And lastly, VENDOR is used to use a third-party vendor product to archive off your logs.

  To set up archival logging of to a different disk spot and have DB2
  automatically clean things for you, you can do the following
  (NOTE:these are in Unix path)

db2 connect to mydb user <admin user> using <admin user password>
#NOTE THE double and single quotes needed on the command line in Unix
db2 "update db cfg using logarchmeth1 'DISK:/dbarch/dblogs'"
db2 update db cfg using num_db_backups 2
db2 update db cfg using rec_his_retentn 2
db2 update db cfg using auto_del_rec_obj on
db2 terminate

  This will tell DB2 to keep 2 full backups for at least two full days.
  Two backups have been kept for two days and another one is taken, the
  oldest backup will be deleted by DB2. DB2 will also then clean up any
  logs that were between the now deleted backup and the next oldest
  retained backup.

  Also note: once you switch to archival logging, DB2 will require you
  to immediately take an offline backup as your next action (ie, the
  database will be placed into BACKUP PENDING mode) and you will be
  unable to do anything else until you do so.

  Hope this helps!


Table design to support pending changes I have a users table. Users can belong to a group. For them I have a group table, with id, title and description.

Now, any changes in the group table must be approved by an admin. So, I need a (preferably fast & easy) way to handle both live and pending group data.

I thought about having a status field in the group table and adding a new record whenever a group is edited. But what do I do when the edit is approved? Copy the title and description back to the live record and delete the pending one? If I delete the old record and mark the new one as live, then I need to update all users records that use the old group_id to the new one. Also, all pending records would need to reference the live records...

Is there a better solution? Part of me just want to say 'forget it' and simply duplicate the fields - have a live_title and a pending_title.

I'm using MySQL. I prefer a solution that is fast and easy to implement (I need to build an API for it).

The pending table is a better concept. It only gets used to create and approve pending elements.

Approval is a transaction:

  BEGIN;

  INSERT INTO title
  SELECT *
  FROM pending_title
  WHERE id = {x};

  DELETE FROM pending_title
  WHERE id = {x};

  COMMIT

This will simplify the lookup logic so you don't need status=approved in every other SQL query.


impdp syntax for schema that is not available on other database My intention is to import the data of schema 'SCOTT'. I have exported it from db1 using expdp scott/tiger schemas=scott dumpfile=scottexp.dmp ......
Now When I try to import the same in database db2 what would be the syntax. I don't have the SCOTT user in database db2(intentionally deleted to test). I tried with sysdba & system but unsuccessful.

impdp xxxxx schemas=yyyyy dumpfile=scottexp.dmp....

Please let me know the xxxxx and yyyyy values here.

Thanks in advance.

impdp SCOTT/password directory=DUMPDIR dumpfile=fromuser.dmp logfile=impdp.log remap_schema=SCOTT:DEV_LOCAL FULL=YES

DEV_LOCAL will be the local empty schema that you should create!


Can the table spool operator be parallelised? Is there something inherently unparallelisable about the table spool operator in SQL Server? I've got several queries that use parallel operators throughout due to the size of the tables, but by tweaking the query slightly to add a table spool (eg adding a second aggregate to the SELECT clause) the entire query suddenly becomes single-threaded.

Is it the table spool that is causing this, or could there be some other reason why SQL Server isn't parallelising operations that it was previously?

I am assume you are meaning common sub expression spools here (i.e. calc once, used many). If so then I would expect this behaviour.

A common sub expression spool is effectively a temp table (its a bit more sophisticated than that but you get the idea). The complex action is completed once and then stored to be re-used many times. If you think about it like this then hopefully you can see why it would be breaking parallelism as the "temp" table can't be read by multiple threads simultaneously due to locking etc.

Adam Machanic has a lot of great info on the things that break parallelism from his SQLBits X session:

https://sqlbits.com/Sessions/Event10/Query_Tuning_Mastery_The_Art_of_Manhandling_Parallelism