Bug #83435 ALTER TABLE is very slow when using PARTITIONED table
Submitted: 19 Oct 2016 0:40 Modified: 19 Oct 2016 5:34
Reporter: Roel Van de Paar (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S1 (Critical)
Version:5.7.15/5.6 OS:Any
Assigned to: CPU Architecture:Any

[19 Oct 2016 0:40] Roel Van de Paar
Description:
mysql> insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
ERROR 1406 (22001): Data too long for column 'c2' at row 1
mysql> insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
ERROR 1406 (22001): Data too long for column 'c2' at row 1
mysql> alter table t1 engine=InnoDB;

Query OK, 0 rows affected (7.56 sec)      <<<<<<< (!)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;
Empty set, 2 warnings (2.19 sec)       <<<<<<< (!)

How to repeat:
DROP DATABASE test;CREATE DATABASE test;USE test;
SET @@session.default_storage_engine=MYISAM;
CREATE TABLE t1(c1 INT,c2 CHAR)PARTITION BY LINEAR KEY(c1) PARTITIONS 99;
CREATE UNIQUE INDEX i1 ON t1(c1);
insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
alter table t1 engine=InnoDB;
SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;
[19 Oct 2016 1:21] Miguel Solorzano
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.17 Source distribution PULL: 2016-OCT-14

Copyright (c) 2000, 2016, 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 5.7 > DROP DATABASE test;CREATE DATABASE test;USE test;
ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist
Query OK, 1 row affected (0.00 sec)

Database changed
mysql 5.7 > SET @@session.default_storage_engine=MYISAM;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > CREATE TABLE t1(c1 INT,c2 CHAR)PARTITION BY LINEAR KEY(c1) PARTITIONS 99;
Query OK, 0 rows affected (0.07 sec)

mysql 5.7 > CREATE UNIQUE INDEX i1 ON t1(c1);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.7 > insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
ERROR 1406 (22001): Data too long for column 'c2' at row 1
mysql 5.7 > insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
ERROR 1406 (22001): Data too long for column 'c2' at row 1
mysql 5.7 > alter table t1 engine=InnoDB;
Query OK, 0 rows affected (33.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.7 > SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;
Empty set, 2 warnings (12.30 sec)

mysql 5.7 >
[19 Oct 2016 1:24] Miguel Solorzano
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 Source distribution PULL: 2016-OCT-14

Copyright (c) 2000, 2016, 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 5.6 > DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
mysql 5.6 > SET @@session.default_storage_engine=MYISAM;
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 > CREATE TABLE t1(c1 INT,c2 CHAR)PARTITION BY LINEAR KEY(c1) PARTITIONS 99;
Query OK, 0 rows affected (0.03 sec)

mysql 5.6 > CREATE UNIQUE INDEX i1 ON t1(c1);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.6 > insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
ERROR 1062 (23000): Duplicate entry '1099202399' for key 'i1'
mysql 5.6 > insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
ERROR 1062 (23000): Duplicate entry '0' for key 'i1'
mysql 5.6 > alter table t1 engine=InnoDB;
Query OK, 5 rows affected (41.47 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql 5.6 > SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;
+------+------+
| c1   | c2   |
+------+------+
|    0 | 2    |
+------+------+
1 row in set, 4 warnings (0.02 sec)

mysql 5.6 >
[19 Oct 2016 1:29] Roel Van de Paar
Note the different query outcome on 5.6 also.

Also see;
https://bugs.launchpad.net/percona-server/+bug/1634708
[19 Oct 2016 1:34] Miguel Solorzano
Thank you for the bug report.
[19 Oct 2016 5:27] Shane Bester
I'm trying to understand what this bug report is really about.
So I made did some tests on my 5.7.16 windows that has write caching enabled on disk:

Server only starts with --open-files-limit=20000 --table-open-cache=10000

created in myisam and alter to innodb: http://pastebin.com/DXwWHW8a
created directly in innodb           : http://pastebin.com/9zgWSpP6

Can you check if the above give unexpected times?
[19 Oct 2016 5:34] Roel Van de Paar
Thanks. Yes, testcase can be reduced for slow duration;

mysql> CREATE TABLE t1(c1 INT,c2 CHAR,UNIQUE KEY(c1)) ENGINE=MyISAM PARTITION BY LINEAR KEY(c1) PARTITIONS 99;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE t1 ENGINE=InnoDB;
Query OK, 0 rows affected (7.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

There is also query difference.