| 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: | |
| 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 | ||
[27 Oct 2010 17:26]
derek c
[27 Oct 2010 17:54]
MySQL Verification Team
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)
