| Bug #29019 | REPLACE/INSERT IGNORE/UPDATE IGNORE doesn't work | ||
|---|---|---|---|
| Submitted: | 11 Jun 2007 14:53 | Modified: | 7 Jul 2007 19:01 |
| Reporter: | Antony Curtis | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Federated storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.0/5.1 | OS: | Any |
| Assigned to: | Antony Curtis | CPU Architecture: | Any |
[11 Jun 2007 19:03]
MySQL Verification Team
Thank you for the bug report.
[19 Jun 2007 23: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/29138 ChangeSet@1.2492, 2007-06-19 16:58:27-07:00, antony@ppcg5.local +4 -0 Bug#29019 "REPLACE/INSERT IGNORE/UPDATE IGNORE doesn't work" Federated does not record neccessary HA_EXTRA flags in order to support REPLACE/INSERT IGNORE/UPDATE IGNORE. Implement ::extra() to capture flags neccessary for functionality. New function append_ident() to better escape identifiers.
[20 Jun 2007 22:40]
Brian Aker
Fix reuse of length variable and its good.
[21 Jun 2007 0: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/29229 ChangeSet@1.2492, 2007-06-20 17:09:16-07:00, antony@ppcg5.local +4 -0 Bug#29019 "REPLACE/INSERT IGNORE/UPDATE IGNORE doesn't work" Federated does not record neccessary HA_EXTRA flags in order to support REPLACE/INSERT IGNORE/UPDATE IGNORE. Implement ::extra() to capture flags neccessary for functionality. New function append_ident() to better escape identifiers.
[21 Jun 2007 18:20]
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/29330 ChangeSet@1.2492, 2007-06-21 11:20:24-07:00, antony@ppcg5.local +4 -0 Bug#29019 "REPLACE/INSERT IGNORE/UPDATE IGNORE doesn't work" Federated does not record neccessary HA_EXTRA flags in order to support REPLACE/INSERT IGNORE/UPDATE IGNORE. Implement ::extra() to capture flags neccessary for functionality. New function append_ident() to better escape identifiers.
[28 Jun 2007 7:23]
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/29805 ChangeSet@1.2504, 2007-06-28 00:23:14-07:00, antony@ppcg5.local +4 -0 Bug#29019 "REPLACE/INSERT IGNORE/UPDATE IGNORE doesn't work" Federated does not record neccessary HA_EXTRA flags in order to support REPLACE/INSERT IGNORE/UPDATE IGNORE. Implement ::extra() to capture flags neccessary for functionality. New function append_ident() to better escape identifiers consistantly.
[2 Jul 2007 16:14]
Antony Curtis
queued to engines tree
[7 Jul 2007 16:34]
Bugs System
Pushed into 5.1.21-beta
[7 Jul 2007 16:35]
Bugs System
Pushed into 5.0.46
[7 Jul 2007 19:01]
Paul DuBois
Noted in 5.0.46, 5.1.21 changelogs. REPLACE, INSERT IGNORE, and UPDATE IGNORE did not work for FEDERATED tables.
Description: When using INSERT IGNORE, REPLACE or UPDATE IGNORE, duplicate key errors are not peroperly ignored and with REPLACE, the existing row is not replaced. How to repeat: simply use INSERT IGNORE or REPLACE or UPDATE IGNORE with a Federated table Suggested fix: Apply the following patch: ===== storage/federated/ha_federated.cc 1.101 vs edited ===== --- 1.101/storage/federated/ha_federated.cc 2007-06-11 06:52:17 -07:00 +++ edited/storage/federated/ha_federated.cc 2007-06-11 06:51:17 -07:00 @@ -1839,7 +1839,13 @@ /* start both our field and field values strings */ - insert_string.append(STRING_WITH_LEN("INSERT INTO `")); + if (!ignore_duplicates) + insert_string.append(STRING_WITH_LEN("INSERT INTO `")); + else + if (replace_duplicates) + insert_string.append(STRING_WITH_LEN("REPLACE INTO `")); + else + insert_string.append(STRING_WITH_LEN("INSERT IGNORE INTO `")); insert_string.append(share->table_name, share->table_name_length); insert_string.append('`'); insert_string.append(STRING_WITH_LEN(" (")); @@ -2053,7 +2059,10 @@ update_string.length(0); where_string.length(0); - update_string.append(STRING_WITH_LEN("UPDATE `")); + if (!ignore_duplicates) + update_string.append(STRING_WITH_LEN("UPDATE `")); + else + update_string.append(STRING_WITH_LEN("UPDATE IGNORE `")); update_string.append(share->table_name); update_string.append(STRING_WITH_LEN("` SET ")); @@ -2767,6 +2776,45 @@ mysql_errno(mysql), mysql_error(mysql))); my_error(error_code, MYF(0), error_buffer); DBUG_RETURN(error_code); +} + + +/** + @brief Handles extra signals from MySQL server + @param operation Signal + */ +int ha_federated::extra(ha_extra_function operation) +{ + DBUG_ENTER("ha_federated::extra"); + switch (operation) { + case HA_EXTRA_IGNORE_DUP_KEY: + ignore_duplicates= TRUE; + break; + case HA_EXTRA_WRITE_CAN_REPLACE: + replace_duplicates= TRUE; + break; + case HA_EXTRA_WRITE_CANNOT_REPLACE: + replace_duplicates= FALSE; + break; + case HA_EXTRA_NO_IGNORE_DUP_KEY: + reset(); + break; + default: + /* do nothing */ + DBUG_PRINT("info",("unhandled operation: %d", (uint) operation)); + } + DBUG_RETURN(0); +} + + +/** + @brief Resets the storage engine to a 'newly opened' state. + */ +int ha_federated::reset() +{ + ignore_duplicates= FALSE; + replace_duplicates= FALSE; + return 0; } ===== storage/federated/ha_federated.h 1.46 vs edited ===== --- 1.46/storage/federated/ha_federated.h 2007-06-11 06:52:17 -07:00 +++ edited/storage/federated/ha_federated.h 2007-06-11 06:51:52 -07:00 @@ -88,6 +88,7 @@ MYSQL_ROW_OFFSET current_position; // Current position used by ::position() int remote_error_number; char remote_error_buf[FEDERATED_QUERY_BUFFER_SIZE]; + bool ignore_duplicates, replace_duplicates; private: /* @@ -216,6 +217,8 @@ int rnd_pos(uchar *buf, uchar *pos); //required void position(const uchar *record); //required int info(uint); //required + int extra(ha_extra_function operation); + int reset(); void update_auto_increment(void); int repair(THD* thd, HA_CHECK_OPT* check_opt);