Bug #21381 Engine not notified about multi-table UPDATE IGNORE
Submitted: 1 Aug 2006 8:31 Modified: 10 Oct 2006 18:14
Reporter: Paul McCullagh (Basic Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1, 5.1 OS:
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: duplicate, IGNORE, key, UPDATE

[1 Aug 2006 8:31] Paul McCullagh
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);
  }
[20 Sep 2006 19: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 12:44] Sergey Vojtovich
Was approved by Ingo and Antony.
[5 Oct 2006 13: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 9: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 11:16] Sergey Vojtovich
Fixed in 4.1.22, 5.0.27, 5.1.12.
[10 Oct 2006 18: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 16:42] Paul Dubois
The 5.0.x fix is in 5.0.30.