Bug #44653 Server crash noticed when executing random queries with partitions.
Submitted: 4 May 2009 20:41 Modified: 13 Jul 2009 13:23
Reporter: Hema Sridharan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1, 6.0, mysql-6.0-backup OS:Linux
Assigned to: Martin Hansson
Triage: Triaged: D1 (Critical)

[4 May 2009 20:41] Hema Sridharan
Description:
When executing random queries with partitions_procedures_triggers using RQG load, mysqld crashed as follows(backtrace may not be complete)

Program terminated with signal 6, Aborted.
#0  0x00000036a3c0b122 in pthread_kill () from /lib64/libpthread.so.0
#0  0x00000036a3c0b122 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000d763c3 in my_write_core (sig=6) at stacktrace.c:309
#2  0x0000000000731c71 in handle_segfault (sig=6) at mysqld.cc:2710
#3  <signal handler called>
#4  0x00000036a3030045 in raise () from /lib64/libc.so.6
#5  0x00000036a3031ae0 in abort () from /lib64/libc.so.6
#6  0x00000036a3029756 in __assert_fail () from /lib64/libc.so.6
#7  0x000000000079f536 in open_table (thd=0x133b1968, table_list=0x133b4ab8, 
 mem_root=0x4e1454f0, action=0x4e14556c, flags=0) at sql_base.cc:2848^M
#8  0x000000000079ffdc in open_tables (thd=0x133b1968, start=0x4e1455d0, 
counter=0x4e145604, flags=0) at sql_base.cc:3772
#9  0x00000000007a0780 in open_and_lock_tables_derived (thd=0x133b1968, 
 tables=0x133b4ab8, derived=true, flags=0) at sql_base.cc:4233
#10 0x000000000074f0a7 in open_and_lock_tables (thd=0x133b1968, 
tables=0x133b4ab8) at ../../sql/mysql_priv.h:1591
#11 0x000000000074494e in mysql_execute_command (thd=0x133b1968)
at sql_parse.cc:2657^
#12 0x000000000074baa2 in mysql_parse (thd=0x133b1968, 
inBuf=0x133b4670 "CREATE TABLE IF NOT EXISTS t6 ( `pk` INTEGER NOT NULL AUTO_INCREMENT , `int` INTEGER , PRIMARY KEY ( `pk` ) ) PARTITION BY HASH ( `pk` ) PARTITIONS 4 SELECT `pk` , `int_key` FROM E", length=180, ^M
found_semicolon=0x4e146f20) at sql_parse.cc:5937^M
#13 0x000000000074cd4c in dispatch_command (command=COM_QUERY, thd=0x133b1968, 
packet=0x1341c839 "CREATE TABLE IF NOT EXISTS t6 ( `pk` INTEGER NOT NULL AUTO_INCREMENT , `int` INTEGER , PRIMARY KEY ( `pk` ) ) PARTITION BY HASH ( `pk` ) PARTITIONS 4 SELECT `pk` , `int_key` FROM E", packet_length=180)
at sql_parse.cc:1049
#14 0x000000000074e233 in do_command (thd=0x133b1968) at sql_parse.cc:731
#15 0x000000000073ab56 in handle_one_connection (arg=0x133b1968)
at sql_connect.cc:1146
#16 0x00000036a3c062e7 in start_thread () from /lib64/libpthread.so.0
#17 0x00000036a30ce3bd in clone () from /lib64/libc.so.6

port: 19306  Source distribution
mysqld: sql_base.cc:2848: bool open_table(THD*, TABLE_LIST*, MEM_ROOT*, enum_open_table_action*, uint): Assertion `!table->auto_increment_field_not_null' failed.
090504 23:09:10 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary^M
or one of the libraries it was linked against is corrupt, improperly built,^M
or misconfigured. This error can also be caused by malfunctioning hardware.^M
We will try our best to scrape up some info that will hopefully help diagnose^M
the problem, but since we have already crashed, something is definitely wrong^M
and this may fail.
^M
key_buffer_size=1048576
read_buffer_size=131072
max_used_connections=11
max_threads=151
thread_count=11
connection_count=11
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 60696 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x133b1968
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x4e1470f0 thread_stack 0x40000
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(my_print_stacktrace+0x32) [0xd76317]
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(handle_segfault+0x2a6) [0x731a88]
/lib64/libpthread.so.0 [0x36a3c0de60]
/lib64/libc.so.6(gsignal+0x35) [0x36a3030045]
/lib64/libc.so.6(abort+0x110) [0x36a3031ae0]
/lib64/libc.so.6(__assert_fail+0xf6) [0x36a3029756]
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, enum_open_table_action*, unsigned int)+0x1122) [0x79f536]
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x442) [0x79ffdc]
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(open_and_lock_tables_derived(THD*, TABLE_LIST*, bool, unsigned int)+0x64) [0x7a0780]
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(open_and_lock_tables(THD*, TABLE_LIST*)+0x27) [0x74f0a7]
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(mysql_execute_command(THD*)+0x1978) [0x74494e]
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x278) [0x74baa2]
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x974) [0x74cd4c]
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(do_command(THD*)+0x227) [0x74e233]
/export/home2/tmp/wl4195f/mysql-6.0-backup/sql/mysqld(handle_one_connection+0x11a) [0x73ab56]
/lib64/libpthread.so.0 [0x36a3c062e7]
/lib64/libc.so.6(clone+0x6d) [0x36a30ce3bd]

How to repeat:
perl runall.pl --basedir=/export/home2/tmp/wl4195f/mysql-6.0-backup --grammar=/export/home/tmp/perl_new/mysql-test-extra-6.0/mysql-test/gentest/conf/partitions_procedures_triggers.yy 
                                     
Observed this crash in mysql-6.0 tree as well
[4 May 2009 21:00] Sveta Smirnova
Thank you for the report.

Verified as described.
[11 Jun 2009 13:52] Martin Hansson
May be used to trigger bug

./runall.pl --threads=1 --queries=573 --rows=1 --basedir=/data0/martin/bzr/mysql-5.1-bugteam --grammar=/data0/martin/bzr/mysql-test-extra-\
6.0/mysql-test/gentest/conf/partitions_procedures_triggers.yy
[18 Jun 2009 8:58] Martin Hansson
A properly minimized test case.

Attachment: bug44653.test (application/octet-stream, text), 564 bytes.

[19 Jun 2009 9:39] 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/76653

2936 Martin Hansson	2009-06-19
      Bug#44653: Server crash noticed when executing random queries with partitions.
      
      When opening a table, it is imperative that the flag
      TABLE::auto_increment_field_not_null be false. But if an error occured during
      the creation of a table (e.g. the table exists already) with an auto_increment
      column and a BEFORE trigger that used the INSERT ... SELECT construct, the
      flag was not reset until after error checking. Thus if an error occured, the
      function returned immediately and it was not done. Crash happened if the table
      was opened again. Fixed by resetting the flag before error checking.
     @ mysql-test/r/trigger.result
        Bug#44653: Test result
     @ mysql-test/t/trigger.test
        Bug#44653: Test case
     @ sql/sql_insert.cc
        Bug#44653: Fix: Make sure to set this field before returning in case of error
[19 Jun 2009 11:38] 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/76670

2936 Martin Hansson	2009-06-19
      Bug#44653: Server crash noticed when executing random queries with partitions.
      
      When opening a table, it is imperative that the flag
      TABLE::auto_increment_field_not_null be false. But if an error occured during
      the creation of a table (e.g. the table exists already) with an auto_increment
      column and a BEFORE trigger that used the INSERT ... SELECT construct, the
      flag was not reset until after error checking. Thus if an error occured, the
      function returned immediately and it was not done. Crash happened if the table
      was opened again. Fixed by resetting the flag before error checking.
     @ mysql-test/r/trigger.result
        Bug#44653: Test result
     @ mysql-test/t/trigger.test
        Bug#44653: Test case
     @ sql/sql_insert.cc
        Bug#44653: Fix: Make sure to unset this field before returning in case of error
[21 Jun 2009 10:48] 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/76768

2937 Martin Hansson	2009-06-21
      Bug#44653: Server crash noticed when executing random queries with partitions.
      
      When opening a table, it is imperative that the flag
      TABLE::auto_increment_field_not_null be false. But if an error occured during
      the creation of a table (e.g. the table exists already) with an auto_increment
      column and a BEFORE trigger that used the INSERT ... SELECT construct, the
      flag was not reset until after error checking. Thus if an error occured, the
      function returned immediately and it was not done. Crash happened if the table
      was opened again. Fixed by resetting the flag after error checking.
      ******
      Bug#44653: Server crash noticed when executing random queries with partitions.
      
      When opening a table, it is imperative that the flag
      TABLE::auto_increment_field_not_null be false. But if an error occured during
      the creation of a table (e.g. the table exists already) with an auto_increment
      column and a BEFORE trigger that used the INSERT ... SELECT construct, the
      flag was not reset until after error checking. Thus if an error occured, the
      function returned immediately and it was not done. Crash happened if the table
      was opened again. Fixed by resetting the flag after error checking.
     @ mysql-test/r/trigger.result
        Bug#44653: Test result
     @ mysql-test/t/trigger.test
        Bug#44653: Test case
     @ sql/sql_insert.cc
        Bug#44653: Fix: Make sure to unset this field before returning in case of error
[21 Jun 2009 10:50] 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/76769

2936 Martin Hansson	2009-06-21
      Bug#44653: Server crash noticed when executing random queries with partitions.
      
      When opening a table, it is imperative that the flag
      TABLE::auto_increment_field_not_null be false. But if an error occured during
      the creation of a table (e.g. the table exists already) with an auto_increment
      column and a BEFORE trigger that used the INSERT ... SELECT construct, the
      flag was not reset until after error checking. Thus if an error occured, the
      function returned immediately and it was not done. Crash happened if the table
      was opened again. Fixed by resetting the flag after error checking.
     @ mysql-test/r/trigger.result
        Bug#44653: Test result
     @ mysql-test/t/trigger.test
        Bug#44653: Test case
     @ sql/sql_insert.cc
        Bug#44653: Fix: Make sure to unset this field before returning in case of error
[22 Jun 2009 12:52] Kristofer Pettersson
Requested more detailed comments, but patch approved.
[8 Jul 2009 13:30] Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:martin.hansson@sun.com-20090622140142-t735nplwp0yg9alg) (merge vers: 5.1.37) (pib:11)
[9 Jul 2009 7:37] Bugs System
Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:martin.hansson@sun.com-20090622140142-t735nplwp0yg9alg) (merge vers: 5.1.37) (pib:11)
[10 Jul 2009 11:21] Bugs System
Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:martin.hansson@sun.com-20090622143256-jxcqoks6cl2lcm30) (merge vers: 5.4.4-alpha) (pib:11)
[13 Jul 2009 12:49] Jon Stephens
Not sure why this is still categorised as Partitioning when the test case doesn't seem to make any use of partitioned tables?
[13 Jul 2009 12:50] Jon Stephens
Documented bugfix in the 5.1.37 and 5.4.4 changelogs as follows:

        If an error occurred during the creation of a table (for
        example, the table already existed) having an AUTO_INCREMENT
        column and a BEFORE trigger that used the INSERT ... SELECT
        construct, an internal flag was not reset properly. This led to
        a crash the next time that the table was opened again.
[12 Aug 2009 22:30] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 1:50] Paul Dubois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:33] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[7 Oct 2009 20:21] Paul Dubois
The 5.4 fix has been pushed to 5.4.2.