Bug #25511 Federated: INSERT failures
Submitted: 10 Jan 2007 0:03 Modified: 18 Jul 2007 17:53
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S3 (Non-critical)
Version:5.2.0, 5.0.38-bk OS:Linux (SUSE 10.0 / 64-bit)
Assigned to: Antony Curtis CPU Architecture:Any
Tags: bfsm_2007_04_05

[10 Jan 2007 0:03] Peter Gulutzan
Description:
On a remote computer, I create a table with a primary key.
On a local computer, I create a matching federated table.
On the remote computer, I insert a row.
On the local computer, I try to insert a row with
INSERT ... ON DUPLICATE KEY UPDATE.
This fails.
It would work if I said it on the remote computer.
On the local computer, I try to insert a row with
INSERT IGNORE.
This fails.
It would work if I said it on the remote computer.

I am aware that there are reasons for this,
but the documentation says that INSERT works.
If it's hard, then just document as a limitation:
INSERT ... ON DUPLICATE KEY UPDATE doesn't work.
INSERT IGNORE doesn't work.

How to repeat:
On remote computer, say:
        USE test
        CREATE TABLE t1 (s1 INT, PRIMARY KEY (s1), s2 INT);
        INSERT INTO t1 VALUES (1,2);

On local computer, say:
        USE test
        CREATE TABLE t1 (s1 INT, PRIMARY KEY (s1), s2 INT)
        ENGINE=FEDERATED
        CONNECTION='mysql://Remote@192.168.1.106/test/t1';
        /* Change User and Host to what's appropriate for you. */

On local computer, say:
        INSERT INTO t1 VALUES (1,3)
        ON DUPLICATE KEY UPDATE s2 = 3;

The error message will be:
ERROR 1296 (HY000): Got error 10000 'Error on remote
system: 1062: Duplicate entry '1' for KEY 'PRIMARY''
from FEDERATED

On local computer, say:
        INSERT IGNORE INTO t1 VALUES (1,3);

The error message will be "ERROR 1296 ...", again.
[10 Jan 2007 2:34] Miguel Solorzano
Thank you for the bug report.

local>        CREATE TABLE t1 (s1 INT, PRIMARY KEY (s1), s2 INT)
    ->         ENGINE=FEDERATED
    ->         CONNECTION='mysql://miguel@192.168.0.121/test/t1';
Query OK, 0 rows affected (0.30 sec)

local>INSERT INTO t1 VALUES (1,3)
    ->         ON DUPLICATE KEY UPDATE s2 = 3;
ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1062: Duplicate entry '1' for key 'PRIMARY'' from FEDERATED
local>
[15 Mar 2007 12:42] Axel Schwenke
This problem exists in current 5.0 as well.
[21 Mar 2007 10: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
[26 Apr 2007 13:50] mark whitehouse
I've run into the same problem. When can we expect a fix for this ?
[21 May 2007 23:02] Jim Winstead
The problem here is that the Federated storage engine doesn't have any special handling for duplicate key errors in ha_federated::write_row(), so the upper levels aren't see the expected duplicate key error. But to completely support this, we need to know which key caused the error, and there doesn't appear to be a way to get this from the remote server short of parsing the error message (which is language-dependent).
[12 Jun 2007 0:13] Jim Winstead
Throwing this one back.
[20 Jun 2007 2:37] 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/29143

ChangeSet@1.2493, 2007-06-19 19:36:48-07:00, antony@ppcg5.local +4 -0
  bug#25511
    "Federated INSERT failures"
    Federated does not correctly handle "INSERT ... ON DUPLICATE KEY UPDATE"
    If an "ON DUPLICATE KEY UPDATE" is in effect while writing a row,
    we want to retieve the keynr which has failed and pass that info back.
    A match between the remote key definition and local keys is required.
    Both local and remote servers require matching PK definition.
    We detect if ON DUPLICATE KEY UPDATE is in effect by checking for
    HA_EXTRA_RETRIEVE_PRIMARY_KEY.
    This patch builds on Bug29019
[20 Jun 2007 23:06] Brian Aker
Return an error, Federated does not support this option.
[21 Jun 2007 8:58] 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/29250

ChangeSet@1.2493, 2007-06-21 01:57:36-07:00, antony@ppcg5.local +4 -0
  Bug#25511
    "Federated INSERT failures"
    Federated does not correctly handle "INSERT ... ON DUPLICATE KEY UPDATE"
    If it is in effect, we must permit mysqld to retry the insert operation
    by translating the error code returned. We also check if the local
    primary key definition is adequate to identify a specific row on the
    remote server by its primary key.
    This patch builds on Bug29019
[21 Jun 2007 19:45] 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/29336

ChangeSet@1.2493, 2007-06-21 12:44:56-07:00, antony@ppcg5.local +4 -0
  Bug#25511
    "Federated INSERT failures"
    Federated does not correctly handle "INSERT ... ON DUPLICATE KEY UPDATE"
    If it is in effect, we must permit mysqld to retry the insert operation
    by translating the error code returned. We also check if the local
    primary key definition is adequate to identify a specific row on the
    remote server by its primary key.
    This patch builds on Bug29019
[22 Jun 2007 17:06] 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/29427

ChangeSet@1.2493, 2007-06-22 10:04:09-07:00, antony@ppcg5.local +4 -0
  Bug#25511
    "Federated INSERT failures"
    Federated does not correctly handle "INSERT ... ON DUPLICATE KEY UPDATE"
    If it is in effect, we must permit mysqld to retry the insert
    operation. We check if the local primary key definition is adequate 
    to identify a specific row on the remote server by its primary key.
  This patch builds on Bug29019
[28 Jun 2007 20:36] 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/29903

ChangeSet@1.2505, 2007-06-28 13:36:26-07:00, antony@ppcg5.local +6 -0
  Bug#25511
    "Federated INSERT failures"
    Federated does not correctly handle "INSERT...ON DUPLICATE KEY UPDATE"
    However, implementing such support is not reasonably possible without
    increasing complexity of the storage engine: checking that constraints
    on remote server match local server and parsing error messages.
    This patch causes 'ON DUPLICATE KEY' to fail with ER_DUP_KEY message
    if a conflict occurs and not to fail silently.
[2 Jul 2007 16:13] Antony Curtis
queued to engines tree
[7 Jul 2007 16:34] Bugs System
Pushed into 5.1.21-beta
[7 Jul 2007 16:36] Bugs System
Pushed into 5.0.46
[18 Jul 2007 17:53] Paul Dubois
Noted in 5.0.46, 5.1.21 changelogs.

The FEDERATED storage engine failed silently for INSERT ... ON
DUPLICATE KEY UPDATE if a duplicate key violatio occurred. FEDERATED
does not support ON DUPLICATE KEY UPDATE, so now it correctly returns
an ER_DUP_KEY error if a duplicate key violation occurs.