Bug #31472 multi_update crashes at assert about trans_safe

• Submitted: 9 Oct 2007 8:59
• Modified: 3 Feb 2008 9:56
• Reporter: Andrei Elkin
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0.50
• OS: Any
• CPU Architecture: Any

[9 Oct 2007 8:59] Andrei Elkin

Description:
The assertion at multi_update::send_eof() does not hold under the scenario
described in How2Repeat.

mysqld: sql_update.cc:1735: virtual bool multi_update::send_eof(): Assertion `trans_safe || !updated || thd->transaction.stmt.modified_non_trans_table' failed.

Here is the stack:

#0  multi_update::send_eof (this=0x8cb58a0) at sql_update.cc:1717
#1  0x082647da in do_select (join=0x8cb5910, fields=0xb50c3304, table=0x0, procedure=0x0) at sql_select.cc:10375
#2  0x0827ab3e in JOIN::exec (this=0x8cb5910) at sql_select.cc:2101
#3  0x08276781 in mysql_select (thd=0x8c7d128, rref_pointer_array=0x8c7e1e8, tables=0x8cb52e0, wild_num=0, fields=@0xb50c3304, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=1342177408, result=0x8cb58a0, unit=0x8c7de8c, select_lex=0x8c7e0c4) at sql_select.cc:2279
#4  0x08290a78 in mysql_multi_update (thd=0x8c7d128, table_list=0x8cb52e0, fields=0x8c7e158, values=0x8c7e32c, conds=0x0, options=0, handle_duplicates=DUP_ERROR, ignore=false, unit=0x8c7de8c, select_lex=0x8c7e0c4) at sql_update.cc:973
#5  0x082101b8 in mysql_execute_command (thd=0x8c7d128) at sql_parse.cc:3510

How to repeat:
CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
insert into t3 values (1,3),(2,6);
insert into t4 values (1,1),(3,2);

# do it

UPDATE t3,t4 SET t4.a=t3.a+1; ### -> ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
A w/a: is to remove asserts referring to the member.

A solution:

I think presumption for mutli_update::trans_safe to be set to zero at multi_update::multi_update
or multi_update::initialize_tables() is incorrect.

trans_safe := false should happen only when a non-transactional table gets modified. 
Therefore, at initialization the member must be be set to true.

The following patch should suffice:

*** /tmp/geta16260	2007-10-09 11:57:19.000000000 +0300
--- sql_update.cc	2007-10-09 11:55:36.000000000 +0300
*************** multi_update::multi_update(TABLE_LIST *t
*** 994,1001 ****
    :all_tables(table_list), leaves(leaves_list), update_tables(0),
     tmp_tables(0), updated(0), found(0), fields(field_list),
     values(value_list), table_count(0), copy_field(0),
!    handle_duplicates(handle_duplicates_arg), do_update(1), trans_safe(0),
!    transactional_tables(1), ignore(ignore_arg)
  {}
  
  
--- 994,1001 ----
    :all_tables(table_list), leaves(leaves_list), update_tables(0),
     tmp_tables(0), updated(0), found(0), fields(field_list),
     values(value_list), table_count(0), copy_field(0),
!    handle_duplicates(handle_duplicates_arg), do_update(1), trans_safe(1),
!    transactional_tables(1), ignore(ignore_arg), error_handled(0)
  {}
  
  
*************** multi_update::initialize_tables(JOIN *jo
*** 1202,1208 ****
    if ((thd->options & OPTION_SAFE_UPDATES) && error_if_full_join(join))
      DBUG_RETURN(1);
    main_table=join->join_tab->table;
!   trans_safe= transactional_tables= main_table->file->has_transactions();
    table_to_update= 0;
  
    /* Any update has at least one pair (field, value) */
--- 1202,1208 ----
    if ((thd->options & OPTION_SAFE_UPDATES) && error_if_full_join(join))
      DBUG_RETURN(1);
    main_table=join->join_tab->table;
!   //trans_safe= transactional_tables= main_table->file->has_transactions();
    table_to_update= 0;
  
    /* Any update has at least one pair (field, value) */

[9 Oct 2007 8:59] Andrei Elkin

Earlier reported
Bug #31472  multi_update crashes at assert about trans_safe
is set to be the parent.

[2 Feb 2008 3:52] Valeriy Kravchuk

I can NOT repeat this with 5.0.54:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.54-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t3;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table t4;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t3 (a int, PRIMARY KEY (a), b int unique) ENGINE=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE t4 (a int, PRIMARY KEY (a), b int unique) ENGINE=Innodb;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into t3 values (1,3),(2,6);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t4 values (1,1),(3,2);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE t3,t4 SET t4.a=t3.a+1;
ERROR 1062 (23000): Duplicate entry '2' for key 1

[3 Feb 2008 9:56] Andrei Elkin

The bug is a duplication of Bug#30763 fixed with a patch for Bug #29136 as said on the parent bugs pages.