Bug #85830 MySQL startup blocked for 20 minutes on partition-engine-check.
Submitted: 6 Apr 12:07 Modified: 29 Nov 9:24
Reporter: Jean-François Gagné Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.7.17 OS:Any
Assigned to:

[6 Apr 12:07] Jean-François Gagné
Description:
Hi,

In [1], I can read: "As of MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, [...]".

[1]: https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_disable-partitio...

It looks like a check has been added on MySQL startup to check for tables using the deprecated partition engine.  This check can be disabled with '--disable-partition-engine-check'.

However, this check might take a very long time see below (almost 20 minutes):

2017-04-06T11:34:51.070461Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.
2017-04-06T11:34:51.070478Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-04-06T11:53:41.730694Z 0 [Note] End of list of non-natively partitioned tables

The logs above have been produced on this system:

> SELECT count(*) FROM INFORMATION_SCHEMA.TABLES;
+----------+
| count(*) |
+----------+
|   191061 |
+----------+
1 row in set (0.78 sec)

See suggested fix.

Note that I classified this bug as S2 as delaying MySQL startup can have very bad consequences.  It looks to me that this check is not a good reason to delay MySQL startup.

Many thanks,

JFG

How to repeat:
See description.

Suggested fix:
Run the check in a sub thread.

Improve logging so it becomes clean that MySQL is not stuck.
[6 Apr 13:17] Umesh Shastry
Hello Jean,

Thank you for the report and feedback.
Observed this with 5.7.17 build.

Thanks,
Umesh
[6 Apr 13:18] Umesh Shastry
test results

Attachment: 85830.results (application/octet-stream, text), 3.25 KiB.

[25 Sep 6:18] Umesh Shastry
As documented, "MySQL 8.0 does not currently support partitioning of tables using any storage engine other than InnoDB, such as MyISAM. An attempt to create a partitioned tables using a storage engine that does not supply native partitioning support fails with ER_CHECK_NOT_IMPLEMENTED." - With 8.0.3, my old test case ended up with:

ERROR 1178 (42000): The storage engine for the table doesn't support native partitioning

tried with Innodb, and as expected no issues after restarting.

.
.
2017-09-25T06:13:45.809317Z 1 [Note] InnoDB: 8.0.3 started; log sequence number 6442305280
2017-09-25T06:14:16.070736Z 0 [Note] InnoDB: Loading buffer pool(s) from /export/umesh/server/binaries/Trunk/87827/mysql-8.0.3/85830/ib_buffer_pool
2017-09-25T06:14:16.091509Z 1 [Note] Found data dictionary with version 1
2017-09-25T06:14:17.090848Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170925  8:14:17
2017-09-25T06:14:17.459604Z 0 [Note] InnoDB: DDL log recovery : begin
2017-09-25T06:14:17.459659Z 0 [Note] InnoDB: DDL log recovery : end
2017-09-25T06:14:17.459757Z 0 [Note] InnoDB: Waiting for purge to start
.
.
2017-09-25T06:14:17.934149Z 0 [Note] IPv6 is available.
2017-09-25T06:14:17.934177Z 0 [Note]   - '::' resolves to '::';
2017-09-25T06:14:17.934260Z 0 [Note] Server socket created on IP: '::'.
2017-09-25T06:14:17.964383Z 4 [Note] Event Scheduler: scheduler thread started with id 4
2017-09-25T06:14:17.964529Z 0 [Note] /export/umesh/server/binaries/Trunk/87827/mysql-8.0.3/bin/mysqld: ready for connections. Version: '8.0.3-rc-log'  socket: '/tmp/mysql_ushastry.sock'  port: 3306  MySQL Community Server (GPL)
[29 Nov 9:24] Jon Stephens
Documented fix in the MySQL 5.7.21 changelog as follows:

    Checking for tables that used the generic partitioning handler
    could cause delays of several minutes when starting the MySQL
    Server. To keep this from happening, the
    --disable-partition-engine-checks option is now enabled by
    default.

Updated description of the option in the Manual as well as Note referring to it in the 5.7 Partitioning chapter.

Closed.