[MDEV-8547] Inserts into Federated tables with Autoincrement columns set Auto column to 0 - JIRA

Details

Type: Bug
Status: Confirmed
Priority: Minor
Resolution: Unresolved
Affects Version/s: 10.1, 10.0, 5.5
Fix Version/s: 10.1
Component/s: Storage Engine - Federated
Labels:
- federatedx
- upstream
Environment:
Windows 8.1 64 Bit

Description

Inserts into a Federated table with a auto increment column causes the insertion of 0's into the auto column and if you have sqlmode set to 'no autoincrement on zero' you get duplicate insert errors. Even if specifically pass a null value to the auto column this occurs.

I think the engine is setting defaults for any missing/invalid columns before passing to host and autoinc columns are not null so defualt is 0.

I have worked around it by removing the sqlmode (it was a legacy setting)

I have been using Mysql for some time and I am now trying MariaDB (I hade hoped this bug was fixed!)

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2015-07-28 21:48

Stephen McGarry,

Could you please provide a complete test case and the output from the client?
I either cannot understand what exactly you mean, or cannot repeat it.

MariaDB [test]> DROP TABLE IF EXISTS federated_table, test_table;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CREATE TABLE test_table (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, i INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.19 sec)

MariaDB [test]> 
MariaDB [test]> CREATE TABLE federated_table (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, i INT) 
    -> ENGINE=FEDERATED CONNECTION='mysql://root@localhost:3306/test/test_table';
Query OK, 0 rows affected (0.20 sec)

MariaDB [test]> 
MariaDB [test]> insert into federated_table (i) values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> 
MariaDB [test]> select * from federated_table;
+----+------+
| pk | i    |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from test_table;
+----+------+
| pk | i    |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)

Show

Elena Stepanova added a comment - 2015-07-28 21:48 Stephen McGarry , Could you please provide a complete test case and the output from the client? I either cannot understand what exactly you mean, or cannot repeat it. MariaDB [test]> DROP TABLE IF EXISTS federated_table, test_table; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> CREATE TABLE test_table (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, i INT) ENGINE=MyISAM; Query OK, 0 rows affected (0.19 sec) MariaDB [test]> MariaDB [test]> CREATE TABLE federated_table (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY, i INT) -> ENGINE=FEDERATED CONNECTION='mysql://root@localhost:3306/test/test_table'; Query OK, 0 rows affected (0.20 sec) MariaDB [test]> MariaDB [test]> insert into federated_table (i) values (1),(2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> MariaDB [test]> select * from federated_table; +----+------+ | pk | i | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec) MariaDB [test]> select * from test_table; +----+------+ | pk | i | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec)

Hide

Permalink

Stephen McGarry added a comment - 2015-07-29 12:00 - edited

Helo Elena, thanka for your attention to this bug.
Here is a test session.

MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create user feduser;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON db1.* TO feduser;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create table db1.dest (c1 int auto_increment,c2 int, Primary key (c1));
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> create table db2.fed (c1 int auto_increment,c2 int, Primary key (c1)) Engine=FEDERATED 
Connection='mysql://feduser@127.0.0.1/db1/dest';
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> insert into db1.dest values (null,1),(null,2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [(none)]> select * from db1.dest
     -> ;
+----+------+
+----+------+
+----+------+
2 rows in set (0.00 sec)

MariaDB [(none)]> insert into db2.fed values (null,3),(null,4);
ERROR 1022 (23000): Can't write; duplicate key in table 'fed'
MariaDB [(none)]> select * from db1.dest;
+----+------+
+----+------+
+----+------+
2 rows in set (0.00 sec)

MariaDB [(none)]> insert into db2.fed values (null,3);
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> select * from db1.dest;
+----+------+
+----+------+
+----+------+
3 rows in set (0.00 sec)

As you can see the multiple insert fails with duplicate key error, and the single insert end up setting c1 to 0.
To get this behavior you have to start the server with sql_mode containing 'NO_AUTO_VALUE_ON_ZERO', setting the sql_mode
on a running server does not seem to affect this bug. see below

MariaDB [(none)]> set global sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> insert into db2.fed values (null,5);
ERROR 1022 (23000): Can't write; duplicate key in table 'fed'
MariaDB [(none)]>

Hope this gives you enough to continue.

On 28/07/2015 19:49, Elena Stepanova (JIRA) wrote:

–
Yours
Stephen McGarry
PriceMate Software

Show

Stephen McGarry added a comment - 2015-07-29 12:00 - edited Helo Elena, thanka for your attention to this bug. Here is a test session. MariaDB [(none)]> create database db1; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> create user feduser; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT ALL PRIVILEGES ON db1.* TO feduser; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> create database db2; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> create table db1.dest (c1 int auto_increment,c2 int, Primary key (c1)); Query OK, 0 rows affected (0.03 sec) MariaDB [(none)]> create table db2.fed (c1 int auto_increment,c2 int, Primary key (c1)) Engine=FEDERATED Connection='mysql://feduser@127.0.0.1/db1/dest'; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> insert into db1.dest values (null,1),(null,2); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [(none)]> select * from db1.dest -> ; +----+------+ +----+------+ +----+------+ 2 rows in set (0.00 sec) MariaDB [(none)]> insert into db2.fed values (null,3),(null,4); ERROR 1022 (23000): Can't write; duplicate key in table 'fed' MariaDB [(none)]> select * from db1.dest; +----+------+ +----+------+ +----+------+ 2 rows in set (0.00 sec) MariaDB [(none)]> insert into db2.fed values (null,3); Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> select * from db1.dest; +----+------+ +----+------+ +----+------+ 3 rows in set (0.00 sec) As you can see the multiple insert fails with duplicate key error, and the single insert end up setting c1 to 0. To get this behavior you have to start the server with sql_mode containing 'NO_AUTO_VALUE_ON_ZERO', setting the sql_mode on a running server does not seem to affect this bug. see below MariaDB [(none)]> set global sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> insert into db2.fed values (null,5); ERROR 1022 (23000): Can't write; duplicate key in table 'fed' MariaDB [(none)]> Hope this gives you enough to continue. On 28/07/2015 19:49, Elena Stepanova (JIRA) wrote: – Yours Stephen McGarry PriceMate Software

Hide

Permalink

Stephen McGarry added a comment - 2015-07-29 12:54 - edited

The email to comment engine has destroyed the output of the select statements so here is another copy of the test session.

MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create user feduser;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON db1.* TO feduser;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database db2;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> create table db1.dest (c1 int auto_increment,c2 int, Primary key (c1));
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> create table db2.fed (c1 int auto_increment,c2 int, Primary key (c1)) Engine=FEDERATED Connection='mysql://feduser@127.0.0.1/db1/dest';
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> insert into db1.dest values (null,1),(null,2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [(none)]> select * from db1.dest
    -> ;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)

MariaDB [(none)]> insert into db2.fed values (null,3),(null,4);
ERROR 1022 (23000): Can't write; duplicate key in table 'fed'
MariaDB [(none)]> select * from db1.dest;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)

MariaDB [(none)]> insert into db2.fed values (null,3);
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> select * from db1.dest;
+----+------+
| c1 | c2   |
+----+------+
|  0 |    3 |
|  1 |    1 |
|  2 |    2 |
+----+------+
3 rows in set (0.00 sec)

Show

Stephen McGarry added a comment - 2015-07-29 12:54 - edited The email to comment engine has destroyed the output of the select statements so here is another copy of the test session. MariaDB [(none)]> create database db1; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> create user feduser; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT ALL PRIVILEGES ON db1.* TO feduser; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> create database db2; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> create table db1.dest (c1 int auto_increment,c2 int, Primary key (c1)); Query OK, 0 rows affected (0.03 sec) MariaDB [(none)]> create table db2.fed (c1 int auto_increment,c2 int, Primary key (c1)) Engine=FEDERATED Connection='mysql://feduser@127.0.0.1/db1/dest'; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> insert into db1.dest values (null,1),(null,2); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [(none)]> select * from db1.dest -> ; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec) MariaDB [(none)]> insert into db2.fed values (null,3),(null,4); ERROR 1022 (23000): Can't write; duplicate key in table 'fed' MariaDB [(none)]> select * from db1.dest; +----+------+ | c1 | c2 | +----+------+ | 1 | 1 | | 2 | 2 | +----+------+ 2 rows in set (0.00 sec) MariaDB [(none)]> insert into db2.fed values (null,3); Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> select * from db1.dest; +----+------+ | c1 | c2 | +----+------+ | 0 | 3 | | 1 | 1 | | 2 | 2 | +----+------+ 3 rows in set (0.00 sec)

Hide

Permalink

Elena Stepanova added a comment - 2015-07-31 18:54 - edited

Thank you.

Regarding sql_mode not working dynamically: when you set the global value, it works for new connections; existing connections get the value upon creation and keep it (unless it's changed on the session level). Since the federated connection has already been established when you change the value, it does not notice the change. You need to make federated disconnect/reconnect to get it work.

You mentioned that you had been waiting for a fix from MySQL – did you file the bug or found it already filed? Can you provide a link?

Test case

install soname 'ha_federated';

set global sql_mode=NO_AUTO_VALUE_ON_ZERO;

create table dest (c1 int auto_increment,c2 int, Primary key (c1));
eval create table fed (c1 int auto_increment,c2 int, Primary key (c1)) Engine=FEDERATED Connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/dest';

# Inserts c1 = 1 and c1 = 2 as expected
insert into dest values (null,1),(null,2);
# Inserts c1 = 0
insert into fed values (null,3);
# Attempts to insert c1 = 0 and fails
insert into fed values (null,4);

Show

Elena Stepanova added a comment - 2015-07-31 18:54 - edited Thank you. Regarding sql_mode not working dynamically: when you set the global value, it works for new connections; existing connections get the value upon creation and keep it (unless it's changed on the session level). Since the federated connection has already been established when you change the value, it does not notice the change. You need to make federated disconnect/reconnect to get it work. You mentioned that you had been waiting for a fix from MySQL – did you file the bug or found it already filed? Can you provide a link? Test case install soname 'ha_federated'; set global sql_mode=NO_AUTO_VALUE_ON_ZERO; create table dest (c1 int auto_increment,c2 int, Primary key (c1)); eval create table fed (c1 int auto_increment,c2 int, Primary key (c1)) Engine=FEDERATED Connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/dest'; # Inserts c1 = 1 and c1 = 2 as expected insert into dest values (null,1),(null,2); # Inserts c1 = 0 insert into fed values (null,3); # Attempts to insert c1 = 0 and fails insert into fed values (null,4);

Hide

Permalink

Stephen McGarry added a comment - 2015-07-31 19:17

I had bothered to send one to mysql as they are not actively developing the federated engine. I will if you think I should.
I have also noted the frederatedx engine is much slower than the stock Federated one on mysql (one query runs in 11s not 3s)

Thanks

–
Yours
Stephen McGarry
PriceMate Software

Show

Stephen McGarry added a comment - 2015-07-31 19:17 I had bothered to send one to mysql as they are not actively developing the federated engine. I will if you think I should. I have also noted the frederatedx engine is much slower than the stock Federated one on mysql (one query runs in 11s not 3s) Thanks – Yours Stephen McGarry PriceMate Software

Hide

Permalink

Elena Stepanova added a comment - 2015-07-31 19:32

Generally, our routine is to report upstream bugs to upstream and wait for a while to see if they get fixed; however, with Federated I also don't have any illusions about chances for that. So it's up to you – if you decide to bother filing, please add the link here, so we could cross-reference.

Show

Elena Stepanova added a comment - 2015-07-31 19:32 Generally, our routine is to report upstream bugs to upstream and wait for a while to see if they get fixed; however, with Federated I also don't have any illusions about chances for that. So it's up to you – if you decide to bother filing, please add the link here, so we could cross-reference.

People

Assignee:

Sergei Golubchik

Reporter:

Stephen McGarry

Votes:

0 Vote for this issue

Watchers:

2 Start watching this issue

Dates

Created:

2015-07-27 13:37

Updated:

2015-07-31 19:39

Agile

View on Board