Bug #34973 Insert ... on duplicate key update problem with federated tables
Submitted: 1 Mar 2008 18:05 Modified: 4 Mar 2008 8:36
Reporter: [ name withheld ] (Basic Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S2 (Serious)
Version:5.1.23-rc-community OS:Windows
Assigned to: CPU Architecture:Any
Tags: federate tables, ON DUPLICATE KEY UPDATE, qc, REPLACE

[1 Mar 2008 18:05] [ name withheld ] 
Description:
INSERT ... SELECT ... ON DUPLICATE KEY UPDATE doesn't seem to work with federated tables, while REPLACE ... SELECT ... works as expected.

How to repeat:
Create the same table in database test and remote_test (used to simulate a remote mysql server)
USE test;
DROP TABLE IF EXISTS `test`.`catalogue`;
CREATE TABLE  `test`.`catalogue` (
  `product_code` char(10) NOT NULL,
  `product_name` varchar(45) DEFAULT NULL,
  `product_desc` varchar(500) DEFAULT NULL,
  `product_weight` decimal(10,0) DEFAULT NULL,
  `product_colour` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE DATABASE `remote_test`;

DROP TABLE IF EXISTS `remote_test`.`catalogue`;
CREATE TABLE  `remote_test`.`catalogue` (
  `product_code` char(10) NOT NULL,
  `product_name` varchar(45) DEFAULT NULL,
  `product_desc` varchar(500) DEFAULT NULL,
  `product_weight` decimal(10,0) DEFAULT NULL,
  `product_colour` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now create a federated table in test database pointng to catalogue table in remote_test database

DROP TABLE IF EXISTS `test`.`remote_catalogue`;
CREATE TABLE  `test`.`remote_catalogue` (
  `product_code` char(10) NOT NULL,
  `product_name` varchar(45) NOT NULL,
  `product_desc` varchar(500) NOT NULL,
  `product_weight` decimal(10,0) NOT NULL,
  `product_colour` varchar(45) NOT NULL,
  PRIMARY KEY (`product_code`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root:nt300jk@127.0.0.1:3306/remote_test/catalogue';

Now insert a row in test.catalogue

mysql> INSERT INTO
    ->  catalogue
    -> VALUES
    ->  ('AB12G', 'Lg. Widget', 'A large widget', 34, 'blue');
Query OK, 1 row affected (0.00 sec)

Now perform a fist sync using

mysql> INSERT INTO
    ->  remote_catalogue
    ->   (product_code, product_name, product_desc, product_weight, product_colo
ur)
    -> SELECT
    ->  *
    -> FROM
    ->  catalogue
    -> ON DUPLICATE KEY UPDATE
    ->  product_code = VALUES(product_code),
    ->  product_name = VALUES(product_name),
    ->  product_desc = VALUES(product_desc),
    ->  product_weight = VALUES(product_weight),
    ->  product_colour = VALUES(product_colour);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Check on the other side:

mysql> select * from catalogue;
+--------------+--------------+----------------+----------------+---------------
-+
| product_code | product_name | product_desc   | product_weight | product_colour
 |
+--------------+--------------+----------------+----------------+---------------
-+
| AB12G        | Lg. Widget   | A large widget |             34 | blue
 |
+--------------+--------------+----------------+----------------+---------------
-+
1 row in set (0.00 sec)

Now issue an update on test.catalogue

mysql> update test.catalogue set product_weight = 33 where product_code = 'AB12G
';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now attempt another sync

mysql> INSERT INTO
    ->  remote_catalogue
    ->   (product_code, product_name, product_desc, product_weight, product_colo
ur)
    -> SELECT
    ->  *
    -> FROM
    ->  catalogue
    -> ON DUPLICATE KEY UPDATE
    ->  product_code = VALUES(product_code),
    ->  product_name = VALUES(product_name),
    ->  product_desc = VALUES(product_desc),
    ->  product_weight = VALUES(product_weight),
    ->  product_colour = VALUES(product_colour);
ERROR 1022 (23000): Can't write; duplicate key in table 'remote_catalogue'

This shouln't happen and the row should get updated!
A sync using REPLACE works, see 

mysql> replace into remote_catalogue select * from catalogue;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

And on the remote site:

mysql> select * from catalogue;
+--------------+--------------+----------------+----------------+---------------
-+
| product_code | product_name | product_desc   | product_weight | product_colour
 |
+--------------+--------------+----------------+----------------+---------------
-+
| AB12G        | Lg. Widget   | A large widget |             33 | blue
 |
+--------------+--------------+----------------+----------------+---------------
-+
1 row in set (0.00 sec)
[3 Mar 2008 21:25] Sveta Smirnova 
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please see bug #25511 for details.
[4 Mar 2008 8:36] [ name withheld ] 
Hi Sveta,
thanks for your feedback, so this is a duplicate of bug 25511 which is now closed.
What you should add and make clear is that REPLACE works (at least for me), when in bug 25511 is mentioned that

" ...
[21 Mar 2007 11:26] Victoria Reznichenko

The same happens with REPLACE command:

mysql> create table test1( id int primary key, col1 int)engine=federated
connection='mysql://victoria:******@127.0.0.1:3307/test/test1';
Query OK, 0 rows affected (0.03 sec)

mysql> replace into test1 values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> replace into test1 values(1,1);
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1062: Duplicate entry '1' for
key 1' from FEDERATED" ....

This should be misleading
Thanks and best regards