SQL Server crashes when altering a table with multi-key index -- OSX build 5.6.13

[Kem Mason]

When I execute the following code on percona v 5.6.13, I consistently get the server to crash -- I've attached a logfile showing the crash. Every once in awhile when I run it, it doesn't crash, but I always still get the ERROR's at the top of the logfile, that seem to indicate the origin of the bug. Note that this is done with a clean database, and empty table -- I drop / create the table right at the start. Should be easy to reproduce on OSX -- it appears to work fine on an ubuntu 12.04 percona install v 5.6.15.

drop table if exists sometable;
CREATE TABLE sometable
( key1 INT UNSIGNED NOT NULL
, key2 INT UNSIGNED DEFAULT NULL
, key3 CHAR(20) NOT NULL
, data CHAR(80) NOT NULL
, UNIQUE KEY key3_key1_key2 (key3, key1, key2)
, KEY (key1)
, KEY (key2)
, KEY (key3)
) ENGINE=InnoDB;

ALTER TABLE sometable MODIFY key2 INT UNSIGNED NOT NULL DEFAULT 0;
SELECT data FROM sometable WHERE key1 = 1 AND key3 = 3 AND key2 = 2;

[Valerii Kravchuk (valerii-kravchuk)]

I see the same error messages with upstream 5.6.17 on Windows:

2014-05-18 17:46:30 5396 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld: ready for connections.
Version: '5.6.17-log' socket: '' port: 3314 MySQL Community Server (GPL)
2014-05-29 09:30:15 5396 [ERROR] Table .\test\sometable has no primary key in InnoDB data dictionary, but has one in MySQL! If you created the table with a MySQL version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MySQL internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.
2014-05-29 09:30:15 5396 [Warning] Table .\test\sometable key_used_on_scan is 0 even though there is no primary key inside InnoDB.
2014-05-29 09:30:15 5396 [ERROR] Index key1 of test/sometable has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

2014-05-29 09:30:15 5396 [ERROR] Index key2 of test/sometable has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

2014-05-29 09:30:15 5396 [ERROR] Index key3 of test/sometable has 2 columns unique inside InnoDB, but MySQL is asking statistics for 3 columns. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html

and server seems to hang for SELECT and even crash there when running EXPLAIN SELECT ...

So, I think this is upstream MySQL bug. Will you report it to http://bugs.mysql.com (to get a credit) or should I do this for you?

[Valerii Kravchuk (valerii-kravchuk)]

Works fine on 5.5, so looks like a regression in MySQL 5.6.

[Valerii Kravchuk (valerii-kravchuk)]

Crashed PS 5.6.17 on Ubuntu 13.10 easily:

openxs@ao756:~/dbs/p5.6$ mysql -uroot -proot test
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.6.17-65.0-587.saucy (Ubuntu)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> drop table if exists sometable;
Query OK, 0 rows affected (0,21 sec)

mysql> CREATE TABLE sometable
    -> ( key1 INT UNSIGNED NOT NULL
    -> , key2 INT UNSIGNED DEFAULT NULL
    -> , key3 CHAR(20) NOT NULL
    -> , data CHAR(80) NOT NULL
    -> , UNIQUE KEY key3_key1_key2 (key3, key1, key2)
    -> , KEY (key1)
    -> , KEY (key2)
    -> , KEY (key3)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0,89 sec)

mysql> ALTER TABLE sometable MODIFY key2 INT UNSIGNED NOT NULL DEFAULT 0;
Query OK, 0 rows affected, 1 warning (1,94 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1082
Message: InnoDB: Table ./test/sometable has no primary key in InnoDB data dictionary, but has one in MySQL!
1 row in set (0,00 sec)

mysql> SELECT data FROM sometable WHERE key1 = 1 AND key3 = 3 AND key2 = 2;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> exit
Bye
openxs@ao756:~/dbs/p5.6$ sudo tail -80 /var/lib/mysql/ao756.err
key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=2
max_threads=153
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 = 69163 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f9e8e0dd930
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 = 7f9e5c781e10 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x2c)[0x7f9e8bdec10c]
/usr/sbin/mysqld(handle_fatal_signal+0x3cb)[0x7f9e8bb3942b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xfbb0)[0x7f9e8a001bb0]
/usr/sbin/mysqld(+0x8c76a8)[0x7f9e8bf9f6a8]
/usr/sbin/mysqld(+0x779f85)[0x7f9e8be51f85]
/usr/sbin/mysqld(_ZN7handler27multi_range_read_info_constEjP15st_range_seq_ifPvjPjS3_P13Cost_estimate+0xcf)[0x7f9e8ba6472f]
/usr/sbin/mysqld(_ZN10DsMrr_impl16dsmrr_info_constEjP15st_range_seq_ifPvjPjS3_P13Cost_estimate+0x48)[0x7f9e8ba6db58]
/usr/sbin/mysqld(+0x618318)[0x7f9e8bcf0318]
/usr/sbin/mysqld(_ZN10SQL_SELECT17test_quick_selectEP3THD6BitmapILj64EEyybN8st_order10enum_orderE+0xae0)[0x7f9e8bcff0f0]
/usr/sbin/mysqld(+0x66aef0)[0x7f9e8bd42ef0]
/usr/sbin/mysqld(_ZN4JOIN8optimizeEv+0x7d9)[0x7f9e8bd456b9]
/usr/sbin/mys...

[Kem Mason (bulbous)]

I'm not so worried about getting credit as getting it fixed -- I'm guessing if you post it, it might get more attention quicker than if do, so please go ahead and post it.

By the way -- in case anybody happens upon this, and is unfamiliar with this bug, there's a very simple workaround for it.
Drop the multi-index key before altering the field, then recreate the multi-index key afterward.

[Laurynas Biveinis (laurynas-biveinis)]

Based on upstream comments, this will need a re-verification after 5.6.19 is merged, and perhaps even after 5.6.20 is merged. I will update this bug once the trunk is ready for this.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-790