| 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: | |
| 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 2:34]
MySQL Verification Team
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]
MySQL Verification Team
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.
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.