Bug #57778 failed primary key add to partitioned innodb table inconsistent and crashes
Submitted: 27 Oct 2010 17:26 Modified: 13 Dec 2010 7:45
Reporter: derek c Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.5.6-rc OS:Linux (centos 5.5)
Assigned to: Mattias Jonsson CPU Architecture:Any
Tags: crash, innodb, partitioning
Triage: Triaged: D1 (Critical)

[27 Oct 2010 17:26] derek c
Description:
Does not affect 5.1.41 in the debian package.

Affects 5.5.6-rc installed from rpm from dev.mysql.com
Does not affect a non-partitioned version of the table.

101027 10:19:14 [ERROR] Table ./dwc/a#P#p0 has a primary key in InnoDB data dictionary, but not in MySQL!
101027 10:19:14 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 15491598 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x38b7fa80
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 = 0x48e94b00 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x39)[0x92a209]
/usr/sbin/mysqld(handle_segfault+0x350)[0x4f9e40]
/lib64/libpthread.so.0[0x378840eb10]
/usr/sbin/mysqld[0x7ee474]
/usr/sbin/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3e)[0x67a12e]
/usr/sbin/mysqld(_ZN12ha_partition4openEPKcij+0x1f6)[0x90e196]
/usr/sbin/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3e)[0x67a12e]
/usr/sbin/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x59f)[0x5e43af]
/usr/sbin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootP18Open_table_context+0x8c6)[0x536fd6]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x8d4)[0x538fe4]
/usr/sbin/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTbjP19Prelocking_strategy+0x51)[0x539481]
/usr/sbin/mysqld[0x56b2bf]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x2131)[0x56d941]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x106)[0x571a46]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1222)[0x5734a2]
/usr/sbin/mysqld(_Z10do_commandP3THD+0xc4)[0x5738f4]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x873)[0x60a573]
/usr/sbin/mysqld(handle_one_connection+0x54)[0x60ab44]
/lib64/libpthread.so.0[0x378840673d]
/lib64/libc.so.6(clone+0x6d)[0x3787cd3d1d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x38baa510 = SELECT * FROM a
thd->thread_id=3
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

How to repeat:
mysql> CREATE TABLE a ( id bigint not null, val int not null ) PARTITION BY KEY(id) PARTITIONS 16;
Query OK, 0 rows affected (0.90 sec)

mysql> INSERT INTO a values (1,1),(1,2);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE a ADD PRIMARY KEY (id);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> SELECT * FROM a;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[27 Oct 2010 17:54] Miguel Solorzano
Thank you for the bug report.

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.7-rc-Win X64-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 > CREATE TABLE a ( id bigint not null, val int not null ) PARTITION BY KEY(id)
    -> PARTITIONS 16;
Query OK, 0 rows affected (4.72 sec)

mysql 5.5 >INSERT INTO a values (1,1),(1,2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.5 >ALTER TABLE a ADD PRIMARY KEY (id);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql 5.5 >SELECT * FROM a;
ERROR 2013 (HY000): Lost connection to MySQL server during query

0000000140172F74    mysqld.exe!ha_innobase::open()[ha_innodb.cc:3763]
000000013FDC78E6    mysqld.exe!handler::ha_open()[handler.cc:2113]
000000014032C372    mysqld.exe!ha_partition::open()[ha_partition.cc:2603]
000000013FDC78E6    mysqld.exe!handler::ha_open()[handler.cc:2113]
000000013FF5C436    mysqld.exe!open_table_from_share()[table.cc:1994]
000000013FE3FCD7    mysqld.exe!open_table()[sql_base.cc:2994]
000000013FE43948    mysqld.exe!open_and_process_table()[sql_base.cc:4347]
000000013FE42D1B    mysqld.exe!open_tables()[sql_base.cc:4776]
000000013FE44B89    mysqld.exe!open_and_lock_tables()[sql_base.cc:5365]
000000013FDC2420    mysqld.exe!open_and_lock_tables()[sql_base.h:459]
000000013FF365A7    mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4530]
000000013FF2ED9E    mysqld.exe!mysql_execute_command()[sql_parse.cc:2175]
000000013FF38E85    mysqld.exe!mysql_parse()[sql_parse.cc:5594]
000000013FF2C5F1    mysqld.exe!dispatch_command()[sql_parse.cc:1142]
000000013FF2BB46    mysqld.exe!do_command()[sql_parse.cc:811]
000000013FE169BD    mysqld.exe!do_handle_one_connection()[sql_connect.cc:1192]
000000013FE16767    mysqld.exe!handle_one_connection()[sql_connect.cc:1132]
000000014033B13B    mysqld.exe!pthread_start()[my_winthread.c:62]
000000014044F905    mysqld.exe!_callthreadstartex()[threadex.c:348]
000000014044F8D8    mysqld.exe!_threadstartex()[threadex.c:331]
0000000076C1BE3D    kernel32.dll!BaseThreadInitThunk()
0000000076D56A51    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 00000000032AECA0=SELECT * FROM a
thd->thread_id=1
thd->killed=NOT_KILLED
[3 Nov 2010 19:25] Mattias Jonsson
The problem is that a previous partition succeeded with adding the primary key and it is not reverted when another partition fails to add a primary key.

But InnoDB does not support online/fast drop of FK (and as I understand it does an internal table copy in the online/fast add of FK too). This makes reverting the add fk very hard (since some of the partitions does not have the same definition).

I will try to only allow pairs of HA_ONLINE_ADD/HA_ONLINE_DROP in ha_partition::alter_table_flags(). This will currently do a table copy in the mysql_alter_table function instead of letting the innodb handler internally copy/recreate the table.
[5 Nov 2010 11: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/122935

3115 Mattias Jonsson	2010-11-05
      Bug#57778: failed primary key add to partitioned innodb table inconsistent and crashes
      
      It was possible to issue an ALTER TABLE ADD PRIMARY KEY on
      an partitioned InnoDB table that failed and crashed the server.
      
      The problem was that it succeeded to create the PK on at least
      one partition, and then failed on a subsequent partition, due to
      duplicate key violation. Since the partitions that already had added
      the PK was not reverted all partitions was not consistent with the
      table definition, which caused the crash.
      
      The solution was to add a revert step to ha_partition::add_index()
      that dropped the index for the already succeeded partitions, on failure.
     @ mysql-test/r/partition.result
        updated result
     @ mysql-test/t/partition.test
        Added test
     @ sql/ha_partition.cc
        Only allow ADD/DROP flags in pairs, so that they can be reverted on failures.
        If add_index() fails for a partition, revert (drop the index) for the previous
        partitions.
     @ sql/handler.h
        Added some extra info in a comment.
[16 Nov 2010 1:26] Mattias Jonsson
pushed to mysql-5.5-bugteam and merged to mysql-trunk-bugfixing
[18 Nov 2010 14:33] Jon Stephens
Documented as follows in the 5.5.8 changelog:

      Issuing ALTER TABLE ... ADD PRIMARY KEY on a partitioned InnoDB 
      table could cause the MySQL Server to crash.

Set Need Merge status, waiting for push to mysql-trunk.
[5 Dec 2010 12:41] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[13 Dec 2010 7:45] Jon Stephens
Issue doesn't appear in any 5.6 release; no additional change log entry needed; closed without further action.
[16 Dec 2010 22:31] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)