MySQL Bugs: #85830: MySQL startup blocked for 20 minutes on partition-engine-check.

Bug #85830	MySQL startup blocked for 20 minutes on partition-engine-check.
Submitted:	6 Apr 2017 12:07	Modified:	29 Nov 2017 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:		CPU Architecture:	Any

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.

Hello Jean,

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

Thanks,
Umesh

test results

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

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)

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.