Bug #73932 multi-update crash with long primary key

• Submitted: 16 Sep 2014 12:49
• Modified: 9 Feb 2015 17:48
• Reporter: Guilhem Bichot
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[16 Sep 2014 12:49] Guilhem Bichot

Description:
verified with trunk
revision-id:[e-mail]-20140916082540-hq7b3vpurt40w1bb ; have
not checked 5.6

CREATE TABLE `t1` (
   `a` varchar(255) NOT NULL,
   `b` varchar(255) NOT NULL,
   `c1` varchar(255) DEFAULT NULL,
   `d` varchar(255) NOT NULL,
    PRIMARY KEY (`a`,`b`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into t1 (a,b,d) values(1,4,3),(2,5,4);

update t1 as t2 straight_join t1 set t1.c1=t1.c1+1;

#1  0x0000000000e4860b in my_write_core (sig=11) at /home/mysql_src/bzrrepos_new/trunk3/mysys/stacktrace.c:247
#2  0x000000000083f3d7 in handle_fatal_signal (sig=11) at /home/mysql_src/bzrrepos_new/trunk3/sql/signal_handler.cc:219
#3  <signal handler called>
#4  0x0000000000af328b in fill_record (thd=0x7fde14011f90, ptr=0x7fde14057e28, values=..., bitmap=0x0, insert_into_fields_bitmap=0x0) at /home/mysql_src/bzrrepos_new/trunk3/sql/sql_base.cc:9402
#5  0x0000000000c0bb9a in multi_update::send_data (this=0x7fde1405a4d0, not_used_values=...) at /home/mysql_src/bzrrepos_new/trunk3/sql/sql_update.cc:2238
#6  0x0000000000b262b1 in end_send (join=0x7fde1405a590, qep_tab=0x7fde1405be28, end_of_records=false) at /home/mysql_src/bzrrepos_new/trunk3/sql/sql_executor.cc:2833
#7  0x0000000000b231d4 in evaluate_join_record (join=0x7fde1405a590, qep_tab=0x7fde1405bcb0) at /home/mysql_src/bzrrepos_new/trunk3/sql/sql_executor.cc:1606
#8  0x0000000000b2264b in sub_select (join=0x7fde1405a590, qep_tab=0x7fde1405bcb0, end_of_records=false) at /home/mysql_src/bzrrepos_new/trunk3/sql/sql_executor.cc:1269
#9  0x0000000000b231d4 in evaluate_join_record (join=0x7fde1405a590, qep_tab=0x7fde1405bb38) at /home/mysql_src/bzrrepos_new/trunk3/sql/sql_executor.cc:1606
#10 0x0000000000b2264b in sub_select (join=0x7fde1405a590, qep_tab=0x7fde1405bb38, end_of_records=false) at /home/mysql_src/bzrrepos_new/trunk3/sql/sql_executor.cc:1269
#11 0x0000000000b21e96 in do_select (join=0x7fde1405a590) at /home/mysql_src/bzrrepos_new/trunk3/sql/sql_executor.cc:922
#12 0x0000000000b1fdc9 in JOIN::exec (this=0x7fde1405a590) at /home/mysql_src/bzrrepos_new/trunk3/sql/sql_executor.cc:190
#13 0x0000000000ba6a70 in mysql_select (thd=0x7fde14011f90, fields=..., select_options=1342177408, result=0x7fde1405a4d0, select_lex=0x7fde14079d50) at /home/mysql_src/bzrrepos_new/trunk3/sql/sql_select.cc:1055
#14 0x0000000000c0963a in mysql_multi_update

How to repeat:
see description

Suggested fix:
because of long composite key (3*255), the temp table created by multi-update, which wants to store rowid (=pk) for second table, gets a "unique constraint" (hash_field). Then this crashes.

[9 Feb 2015 17:48] Paul DuBois

Noted in 5.7.6 changelog.

The capabililty of using InnoDB for temporary tables in MySQL 5.7.5
resulted in certain queries failing: Some queries involving
multiple-table UPDATE, queries involving long PRIMARY KEY values, and
queries involving DISTINCT SUM().