|
pricemate,
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)
|
|
|
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
|
|
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)
|
|
|
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);
|
|
|
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
|
|
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.
|