Bug #18764 Delete conditions causing inconsistencies in Federated tables
Submitted: 3 Apr 2006 23:24 Modified: 17 Aug 2006 10:26
Reporter: Kyle Anderson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S2 (Serious)
Version:5.0.21-BK, 5.0.19 OS:Linux (Linux, OSX 10.4.5)
Assigned to: Bugs System CPU Architecture:Any

[3 Apr 2006 23:24] Kyle Anderson
Description:
I was attempting to use Federated tables for quick-and-dirty replication: 2 MyISAM identical tables, and a Federated table to link them.  I would delete from a local table, then insert from the Federated.  Certain delete conditions (ranges and functions) are corrupting subsequent inserts with the same key value, inserting inconsistent data.  Simple delete conditions, or flushing the table between deletes & inserts prevent the problem.  

Observed on OSX 10.4.5 with a Federated table pointing to a Gentoo Linux server, using the MySQL command line prompt.  The query cache is disabled on both machines (query_cache_size=0 & query_cache_type=OFF).

How to repeat:
# On Server A:
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `val1` varchar(255) NOT NULL,
  `val2` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

# On Server B:
CREATE TABLE `test_local` (
  `id` int(11) NOT NULL,
  `val1` varchar(255) NOT NULL,
  `val2` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test_local` VALUES (1, 'foo', 'bar'),
(2, 'bar', 'foo');

CREATE TABLE `test_remote` (
  `id` int(11) NOT NULL,
  `val1` varchar(255) NOT NULL,
  `val2` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://<user>@<host>/test/test';

mysql> insert into `test_remote` select * from `test_local`;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_remote;
+----+------+------+
| id   | val1 | val2 |
+----+------+------+
| 1    | foo  |  bar  |
| 2    | bar  | foo   |
+----+------+------+
2 rows in set (0.01 sec)  
#Expected result

mysql> delete from test_remote where id in (1,2);
Query OK, 2 rows affected (0.03 sec) 
# Expected result

mysql> insert into `test_remote` select * from `test_local`;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1062: Duplicate entry '0' for key 1' from FEDERATED  
# Unexpected

mysql> select * from test_remote;
+----+------+------+
| id   | val1 | val2 |
+----+------+------+
| 0    |        |        |
+----+------+------+
1 row in set (0.01 sec)  
# Inconsistent
[4 Apr 2006 12:18] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.21-BK (ChangeSet@1.2134.1.1, 2006-04-04 11:50:54+02:00):

mysql> CREATE TABLE `test_local` (
    ->   `id` int(11) NOT NULL,
    ->   `val1` varchar(255) NOT NULL,
    ->   `val2` varchar(255) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `test_local` VALUES (1, 'foo', 'bar'),
    -> (2, 'bar', 'foo');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `test_remote` (
    ->   `id` int(11) NOT NULL,
    ->   `val1` varchar(255) NOT NULL,
    ->   `val2` varchar(255) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=latin1
    -> CONNECTION='mysql://root@127.0.0.1:3307/test/test';
Query OK, 0 rows affected (0.02 sec)

mysql> show create table test_remote\G
*************************** 1. row ***************************
       Table: test_remote
Create Table: CREATE TABLE `test_remote` (
  `id` int(11) NOT NULL,
  `val1` varchar(255) NOT NULL,
  `val2` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:3307/test/test'
1 row in set (0.01 sec)

mysql> insert into `test_remote` select * from `test_local`;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_remote;
+----+------+------+
| id | val1 | val2 |
+----+------+------+
| 1  | foo  | bar  |
| 2  | bar  | foo  |
+----+------+------+
2 rows in set (0.01 sec)

mysql> delete from test_remote where id in (1,2);
Query OK, 2 rows affected (0.01 sec)

mysql> insert into `test_remote` select * from `test_local`;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1062: Duplicate entry '0' for key 1' from FEDERATED
mysql> select * from test_remote;
+----+------+------+
| id | val1 | val2 |
+----+------+------+
| 0  |      |      |
+----+------+------+
1 row in set (0.00 sec)
[12 May 2006 1:40] Patrick Galbraith
I have the same results. It all has to do with the table cache still thinking there are two rows in "test_remote". Flush tables may be the workaround.
[1 Jun 2006 16:10] Patrick Galbraith
I am working on a patch for this at the current moment, and hope to have code for review by June 2nd
[7 Jun 2006 16:28] Patrick Galbraith
I should have a patch committed for review by end of day June 7th, or 8th
[8 Jun 2006 0:57] Patrick Galbraith
Fix on it's way!

mysql> insert into `test_remote` select * from `test_local`;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_remote;
+----+------+------+
| id | val1 | val2 |
+----+------+------+
|  1 | foo  | bar  |
|  2 | bar  | foo  |
+----+------+------+
2 rows in set (0.00 sec)

mysql> delete from test_remote;
Query OK, 2 rows affected (0.00 sec)

mysql> insert into `test_remote` select * from `test_local`;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_remote;
+----+------+------+
| id | val1 | val2 |
+----+------+------+
|  1 | foo  | bar  |
|  2 | bar  | foo  |
+----+------+------+
2 rows in set (0.00 sec)
[8 Jun 2006 2:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7375
[20 Jun 2006 21:40] Patrick Galbraith
disregard previous patch. Need to separate out patches.
[22 Jun 2006 17:38] Patrick Galbraith
Have been given instructions on how I can fix this from Monty. It involes a loop over fields in sql_insert to set all field query ids to thd query id, and then removing logic from write_row in federated to not check query ids.
[27 Jun 2006 1:12] Patrick Galbraith
patch ready to be commited. Here's the real fix :)

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

mysql> insert into test_remote select * from test_local;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from test_remote where id in (1,2);
Query OK, 2 rows affected (0.00 sec)

mysql> insert into test_remote select * from test_local;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test_remote;
+----+------+------+
| id | val1 | val2 |
+----+------+------+
|  2 | bar  | foo  | 
|  1 | foo  | bar  | 
+----+------+------+
2 rows in set (0.00 sec)

mysql> show create table test_remote;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                        |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_remote | CREATE TABLE `test_remote` (
  `id` int(11) NOT NULL,
  `val1` varchar(255) NOT NULL,
  `val2` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@localhost/test/test' | 
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[27 Jun 2006 6:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8290
[12 Jul 2006 5:56] Patrick Galbraith
I've submitted a patch via bk, but mail hasn't sent out mail or updated this bug automatically.
[13 Jul 2006 13:53] Ingo Strüwing
Hehe. And how do you think anyone could review your patch?
Please get your mail problems solved, run "bk fix -c", and "bk citool" again.
[13 Jul 2006 22:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9143
[13 Jul 2006 22:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9149
[13 Jul 2006 22:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9150
[18 Jul 2006 15:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9280
[18 Jul 2006 15:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9282
[19 Jul 2006 1:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9313
[19 Jul 2006 2:52] Patrick Galbraith
5.0.25-debug
[21 Jul 2006 19:37] Antony Curtis
pushed to 5.0.25 repository
[2 Aug 2006 7:31] Calvin Sun
merged into 5.1.12.
[16 Aug 2006 15:54] Patrick Galbraith
merged from 5.1-engines to main (5.1.12)
[17 Aug 2006 10:26] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25 and 5.1.12 changelogs.