| Bug #21381 | Engine not notified about multi-table UPDATE IGNORE | ||
|---|---|---|---|
| Submitted: | 1 Aug 2006 10:31 | Modified: | 10 Oct 2006 20:14 |
| Reporter: | Paul McCullagh (Basic Quality Contributor) (SCA) | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S2 (Serious) |
| Version: | 4.1, 5.1 | OS: | |
| Assigned to: | Sergey Vojtovich | Target Version: | |
| Tags: | UPDATE, IGNORE, duplicate, key | ||
[20 Sep 2006 21:02]
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/12280 ChangeSet@1.2548, 2006-09-20 19:34:27+05:00, svoj@mysql.com +3 -0 BUG#21381 - Engine not notified about multi-table UPDATE IGNORE Though this is not storage engine specific problem, I was able to repeat this problem with BDB and NDB engines only. That was the reason to add a test case into ndb_update.test. As a result different bad things could happen. BDB has removed duplicate rows which is not expected. NDB returns an error. For multi table update notify storage engine about UPDATE IGNORE as it is done in single table UPDATE.
[5 Oct 2006 14:44]
Sergey Vojtovich
Was approved by Ingo and Antony.
[5 Oct 2006 15:21]
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/13112 ChangeSet@1.2548, 2006-10-05 18:23:53+05:00, svoj@mysql.com +3 -0 BUG#21381 - Engine not notified about multi-table UPDATE IGNORE Though this is not storage engine specific problem, I was able to repeat this problem with BDB and NDB engines only. That was the reason to add a test case into ndb_update.test. As a result different bad things could happen. BDB has removed duplicate rows which is not expected. NDB returns an error. For multi table update notify storage engine about UPDATE IGNORE as it is done in single table UPDATE.
[6 Oct 2006 11:46]
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/13223 ChangeSet@1.2558, 2006-10-06 14:47:58+05:00, svoj@mysql.com +2 -0 Per discussion with pekka removed non-deterministic test case for bug#21381.
[9 Oct 2006 13:16]
Sergey Vojtovich
Fixed in 4.1.22, 5.0.27, 5.1.12.
[10 Oct 2006 20:14]
Paul DuBois
Noted in 4.1.22, 5.0.27, 5.1.12 changelogs. For multiple-table UPDATE statements, storage engines were not notified of duplicate-key errors.
[25 Oct 2006 18:42]
Paul DuBois
The 5.0.x fix is in 5.0.30.
Description: There is a bug that can cause an engine to crash when executing a statement of the form: UPDATE IGNORE t1, t2 ...; The problem is, extra(HA_EXTRA_IGNORE_DUP_KEY) is not called in this case to inform the engine to ignore the duplicate key errors. I have suggested a bug fix for the version 4.1 below. As far as I can tell, the code must also be added to mysql_multi_update_prepare() in version 5.1 as well. How to repeat: Execute a statement of the form UPDATE IGNORE t1, t2, and observe in the debugger that engine interface function extra() is not called with the value HA_EXTRA_IGNORE_DUP_KEY. However, in the case of a single table UPDATE IGNORE extra(HA_EXTRA_IGNORE_DUP_KEY) is called. Suggested fix: I have tested this bug fix with the PBXT storage engine: ------ MySQL 4.1.16 ----- file: sql_update.cc ----- line: 687 ------ int mysql_multi_update(THD *thd, TABLE_LIST *table_list, List<Item> *fields, List<Item> *values, COND *conds, ulong options, enum enum_duplicates handle_duplicates, bool ignore, SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex) { int res; TABLE_LIST *tl; TABLE_LIST *update_list= (TABLE_LIST*) thd->lex->select_lex.table_list.first; List<Item> total_list; multi_update *result; DBUG_ENTER("mysql_multi_update"); /* Setup timestamp handling */ for (tl= update_list; tl; tl= tl->next) { TABLE *table= tl->table; /* Only set timestamp column if this is not modified */ if (table->timestamp_field && table->timestamp_field->query_id == thd->query_id) table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; /* We only need SELECT privilege for columns in the values list */ table->grant.want_privilege= (SELECT_ACL & ~table->grant.privilege); /* PMC: engines may have to ignore duplicate key errors */ if (ignore) table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); }