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

Bug #1324295 reported by Kem Mason on 2014-05-28
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server
Status tracked in 5.7
5.1
Undecided
Unassigned
5.5
Undecided
Unassigned
5.6
High
Unassigned
5.7
High
Unassigned

Bug Description

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;

Kem Mason (bulbous) wrote :

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?

Changed in percona-server:
status: New → Incomplete

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

tags: added: upstream
Download full text (4.2 KiB)

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...

Read more...

Kem Mason (bulbous) wrote :

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.

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.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.