From MySQL 5.6 partitioning to 5.7 and beyond

As you may already know,  since MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is completely removed in MySQL 8.0.

So now, in MySQL 5.7, the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines do.

MySQL 5.7 supports generic partitions & native partitions

What does that imply for users using partitions in an earlier version MySQL  migrating to 5.7 ?

As the documentation describes it, the generic partitioning handler is still supported in all MySQL 5.7 releases, although it is deprecated in 5.7.17 and later. (Note that “deprecated” is not the same as “removed”.) Partitioned tables created in older versions of MySQL continue to work in 5.7 but if you modify them then they will be upgraded to the new native partition handler. You can also upgrade them manually.

MySQL 5.6

Let’s have a look with an example of two tables that we will partition in MySQL 5.6.

Initial state:

mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest2          |
+------------------+
2 rows in set (0.08 sec)

[root@mysql1 sbtest]# ls -lh
total 432M
-rw-rw----. 1 mysql mysql   65 Nov 22 12:20 db.opt
-rw-rw----. 1 mysql mysql 8.5K Nov 22 12:24 sbtest1.frm
-rw-rw----. 1 mysql mysql 232M Nov 22 12:24 sbtest1.ibd
-rw-rw----. 1 mysql mysql 8.5K Nov 22 12:26 sbtest2.frm
-rw-rw----. 1 mysql mysql 181M Nov 22 12:26 sbtest2.MYD
-rw-rw----. 1 mysql mysql  20M Nov 22 12:26 sbtest2.MYI

As you can see, we have two tables, one in InnoDB (sbtest1) and one in MyISAM (sbtest2).

Let’s create the partitions:

mysql> ALTER TABLE sbtest1 PARTITION BY KEY() partitions 5;
mysql> ALTER TABLE sbtest2 PARTITION BY KEY() partitions 5;

We can see the changes on the filesystem too:

[root@mysql1 sbtest]# ls -lh
total 499M
-rw-rw----. 1 mysql mysql   65 Nov 22 12:20 db.opt
-rw-rw----. 1 mysql mysql 8.5K Nov 22 12:32 sbtest1.frm
-rw-rw----. 1 mysql mysql   40 Nov 22 12:32 sbtest1.par
-rw-rw----. 1 mysql mysql  56M Nov 22 12:33 sbtest1#P#p0.ibd
-rw-rw----. 1 mysql mysql  56M Nov 22 12:33 sbtest1#P#p1.ibd
-rw-rw----. 1 mysql mysql  60M Nov 22 12:33 sbtest1#P#p2.ibd
-rw-rw----. 1 mysql mysql  60M Nov 22 12:33 sbtest1#P#p3.ibd
-rw-rw----. 1 mysql mysql  60M Nov 22 12:33 sbtest1#P#p4.ibd
-rw-rw----. 1 mysql mysql 8.5K Nov 22 12:36 sbtest2.frm
-rw-rw----. 1 mysql mysql   40 Nov 22 12:36 sbtest2.par
-rw-rw----. 1 mysql mysql  36M Nov 22 12:36 sbtest2#P#p0.MYD
-rw-rw----. 1 mysql mysql 3.9M Nov 22 12:36 sbtest2#P#p0.MYI
-rw-rw----. 1 mysql mysql  36M Nov 22 12:36 sbtest2#P#p1.MYD
-rw-rw----. 1 mysql mysql 3.9M Nov 22 12:36 sbtest2#P#p1.MYI
-rw-rw----. 1 mysql mysql  37M Nov 22 12:36 sbtest2#P#p2.MYD
-rw-rw----. 1 mysql mysql 4.0M Nov 22 12:36 sbtest2#P#p2.MYI
-rw-rw----. 1 mysql mysql  37M Nov 22 12:36 sbtest2#P#p3.MYD
-rw-rw----. 1 mysql mysql 4.0M Nov 22 12:36 sbtest2#P#p3.MYI
-rw-rw----. 1 mysql mysql  37M Nov 22 12:36 sbtest2#P#p4.MYD
-rw-rw----. 1 mysql mysql 4.0M Nov 22 12:36 sbtest2#P#p4.MYI

We can use Information_Schema to identify the partitioned tables:

mysql> SELECT count(*) as '# partitions', t1.TABLE_SCHEMA, t1.TABLE_NAME, ENGINE, CREATE_OPTIONS
       FROM INFORMATION_SCHEMA.PARTITIONS t1
       JOIN INFORMATION_SCHEMA.TABLES t2 
         ON t2.TABLE_SCHEMA=t1.TABLE_SCHEMA 
        AND t2.TABLE_NAME=t1.TABLE_NAME
       WHERE t1.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
       GROUP BY TABLE_SCHEMA, TABLE_NAME having count(*)> 1;
+--------------+--------------+------------+--------+----------------+
| # partitions | TABLE_SCHEMA | TABLE_NAME | ENGINE | CREATE_OPTIONS |
+--------------+--------------+------------+--------+----------------+
|            5 | sbtest       | sbtest1    | InnoDB | partitioned    |
|            5 | sbtest       | sbtest2    | MyISAM | partitioned    |
+--------------+--------------+------------+--------+----------------+
2 rows in set (0.03 sec)

So far so good. Let’s upgrade to MySQL 5.7

Upgrade to MySQL 5.7

When we upgrade to MySQL 5.7, in the error log we can see this information after booting mysqld:

Version: '5.7.20'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2017-11-22T12:48:49.265005Z 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-11-22T12:48:49.265007Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-11-22T12:48:49.322838Z 2 [Warning] The partition engine, used by table 'sbtest.sbtest1', is deprecated and will be removed in a future release. Please use native partitioning instead.
2017-11-22T12:48:49.323335Z 2 [Warning] The partition engine, used by table 'sbtest.sbtest2', is deprecated and will be removed in a future release. Please use native partitioning instead.
2017-11-22T12:48:49.323662Z 0 [Note] End of list of non-natively partitioned tables

During the mysqld’s start process, MySQL verifies the type of the partitioned tables. We can see in the error log the [Warning] messages about our 2 tables using deprecated generic-partitions.

Note that this check will be removed from the start process in the furute 5.7 releases.

We can also verify this in Information_Schema:

SELECT count(*) as '# partitions', t1.TABLE_SCHEMA, t1.TABLE_NAME, 
       ANY_VALUE(ENGINE), ANY_VALUE(CREATE_OPTIONS)
       FROM INFORMATION_SCHEMA.PARTITIONS t1
       JOIN INFORMATION_SCHEMA.TABLES t2 
         ON t2.TABLE_SCHEMA=t1.TABLE_SCHEMA 
        AND t2.TABLE_NAME=t1.TABLE_NAME
       WHERE t1.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
       GROUP BY TABLE_SCHEMA, TABLE_NAME having count(*)> 1;
+--------------+--------------+------------+-------------------+---------------------------+
| # partitions | TABLE_SCHEMA | TABLE_NAME | ANY_VALUE(ENGINE) | ANY_VALUE(CREATE_OPTIONS) |
+--------------+--------------+------------+-------------------+---------------------------+
|            5 | sbtest       | sbtest1    | InnoDB            | partitioned               |
|            5 | sbtest       | sbtest2    | MyISAM            | partitioned               |
+--------------+--------------+------------+-------------------+---------------------------+
2 rows in set, 2 warnings (0.00 sec)

Please note that the query is a bit different than in 5.6.

This time, we have 2 warning messages, let’s check them:

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: The partition engine, used by table 'sbtest.sbtest1', 
         is deprecated and will be removed in a future release. 
         Please use native partitioning instead.
*************************** 2. row ***************************
  Level: Warning
   Code: 1287
Message: The partition engine, used by table 'sbtest.sbtest2', 
         is deprecated and will be removed in a future release. 
         Please use native partitioning instead.
2 rows in set (0.00 sec)

This is indeed how we can verify the tables using the old generic partition engine.

How to upgrade to native partitioning ?

There are several ways to migrate from the old engine to the new native one:

  • running mysql_upgrade
  • using ALTER TABLE … UPGRADE PARTITIONING
  • force altering the table
  • modifying the partition structure

mysql_upgrade

Once MySQL is started, you should (I would like to say must) run mysql_upgrade. During this process, we can see that the generic partition tables are identified and those in InnoDB are directly upgraded to the new native partition engine:

[root@mysql1 sbtest]# mysql_upgrade 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
...
Checking databases.
sbtest.sbtest1
error    : Partitioning upgrade required. Please dump/reload to fix it or do: ALTER TABLE `sbtest`.`sbtest1` UPGRADE PARTITIONING
warning  : The partition engine, used by table 'sbtest.sbtest1', is deprecated and will be removed in a future release. Please use native partitioning instead.
sbtest.sbtest2                                     OK
warning  : The partition engine, used by table 'sbtest.sbtest2', is deprecated and will be removed in a future release. Please use native partitioning instead.
sys.sys_config                                     OK

Upgrading tables
Running  : ALTER TABLE `sbtest`.`sbtest1` UPGRADE PARTITIONING
status   : OK
Upgrade process completed successfully.
Checking if update is needed.

Note that even if the process first advice you to upgrade the partitions of the InnoDB table, it does it automatically just after.

After this process we can see on the filesystem that the .par file is now gone for the InnoDB table:

[root@mysql1 sbtest]# ls -lh
total 492M
-rw-rw----. 1 mysql mysql   65 Nov 23 08:14 db.opt
-rw-r-----. 1 mysql mysql 8.5K Nov 23 08:25 sbtest1.frm
-rw-rw----. 1 mysql mysql  56M Nov 23 08:17 sbtest1#P#p0.ibd
-rw-rw----. 1 mysql mysql  56M Nov 23 08:17 sbtest1#P#p1.ibd
-rw-rw----. 1 mysql mysql  60M Nov 23 08:17 sbtest1#P#p2.ibd
-rw-rw----. 1 mysql mysql  60M Nov 23 08:17 sbtest1#P#p3.ibd
-rw-rw----. 1 mysql mysql  60M Nov 23 08:17 sbtest1#P#p4.ibd
-rw-rw----. 1 mysql mysql 8.5K Nov 23 08:25 sbtest2.frm
-rw-rw----. 1 mysql mysql   40 Nov 23 08:17 sbtest2.par
-rw-rw----. 1 mysql mysql  36M Nov 23 08:17 sbtest2#P#p0.MYD
-rw-rw----. 1 mysql mysql 3.9M Nov 23 08:17 sbtest2#P#p0.MYI
-rw-rw----. 1 mysql mysql  36M Nov 23 08:17 sbtest2#P#p1.MYD
-rw-rw----. 1 mysql mysql 3.9M Nov 23 08:17 sbtest2#P#p1.MYI
-rw-rw----. 1 mysql mysql  37M Nov 23 08:17 sbtest2#P#p2.MYD
-rw-rw----. 1 mysql mysql 4.0M Nov 23 08:17 sbtest2#P#p2.MYI
-rw-rw----. 1 mysql mysql  37M Nov 23 08:17 sbtest2#P#p3.MYD
-rw-rw----. 1 mysql mysql 4.0M Nov 23 08:17 sbtest2#P#p3.MYI
-rw-rw----. 1 mysql mysql  37M Nov 23 08:17 sbtest2#P#p4.MYD
-rw-rw----. 1 mysql mysql 4.0M Nov 23 08:17 sbtest2#P#p4.MYI

The MyISAM table is still untouched. We can verify this again with I_S :

SELECT count(*) as '# partitions', t1.TABLE_SCHEMA, t1.TABLE_NAME, 
       ANY_VALUE(ENGINE), ANY_VALUE(CREATE_OPTIONS)
       FROM INFORMATION_SCHEMA.PARTITIONS t1
       JOIN INFORMATION_SCHEMA.TABLES t2 
         ON t2.TABLE_SCHEMA=t1.TABLE_SCHEMA 
        AND t2.TABLE_NAME=t1.TABLE_NAME
       WHERE t1.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
       GROUP BY TABLE_SCHEMA, TABLE_NAME having count(*)> 1;
+--------------+--------------+------------+-------------------+---------------------------+
| # partitions | TABLE_SCHEMA | TABLE_NAME | ANY_VALUE(ENGINE) | ANY_VALUE(CREATE_OPTIONS) |
+--------------+--------------+------------+-------------------+---------------------------+
|            6 | sbtest       | sbtest1    | InnoDB            | partitioned               |
|            5 | sbtest       | sbtest2    | MyISAM            | partitioned               |
+--------------+--------------+------------+-------------------+---------------------------+
2 rows in set, 1 warning (4.21 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1287
Message: The partition engine, used by table 'sbtest.sbtest2', 
         is deprecated and will be removed in a future release. 
         Please use native partitioning instead.
1 row in set (0.17 sec)

As MyISAM doesn’t support native partitioning, the table is untouched.

ALTER TABLE … UPGRADE PARTITIONING

If for any reason, you don’t want to run mysql_upgrade yet, you can upgrade to the InnoDB partitioned tables using ALTER TABLE... UPGRADE PARTITIONING

This is a metadata change and it’s a quick operation:

mysql> ALTER TABLE sbtest1 UPGRADE PARTITIONING;
Query OK, 0 rows affected, 1 warning (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show warnings\G 
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: The partition engine, used by table 'sbtest.sbtest1',
is deprecated and will be removed in a future release.
Please use native partitioning instead.
1 row in set (0.00 sec)

Unfortunately, we see a warning. This warning comes from when the table is open. Next time it won’t happen anymore.

ALTER TABLE ENGINE=InnoDB

Exactly like above, just forcing the ENGINE=InnoDB again will also perform the change but this time the operation is also quick but touches also all the partitions and no warning:

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

If we compare the filesystem we cab see that only the frm file is touched in case of ALTER … UPGRADE PARTITIONING, and all files are touched in case of ALTER … ENGINE=InnoDB.
Tables was created at 08:47 and the ALTER was done on sbtest1 at 15:16. On sbtest3, the table was created at 9.00 and the ALTER was run at 15:24 :

-rw-rw----. 1 mysql mysql       65 Nov 23 08:45 db.opt
-rw-r-----. 1 mysql mysql     8632 Nov 23 15:16 sbtest1.frm
-rw-rw----. 1 mysql mysql 58720256 Nov 23 08:47 sbtest1#P#p0.ibd
-rw-rw----. 1 mysql mysql 58720256 Nov 23 08:47 sbtest1#P#p1.ibd
-rw-rw----. 1 mysql mysql 62914560 Nov 23 08:47 sbtest1#P#p2.ibd
-rw-rw----. 1 mysql mysql 62914560 Nov 23 08:47 sbtest1#P#p3.ibd
-rw-rw----. 1 mysql mysql 62914560 Nov 23 08:47 sbtest1#P#p4.ibd
-rw-r-----. 1 mysql mysql     8632 Nov 23 15:24 sbtest3.frm
-rw-r-----. 1 mysql mysql   114688 Nov 23 15:24 sbtest3#P#p0.ibd
-rw-r-----. 1 mysql mysql   114688 Nov 23 15:24 sbtest3#P#p1.ibd
-rw-r-----. 1 mysql mysql   114688 Nov 23 15:24 sbtest3#P#p2.ibd
-rw-r-----. 1 mysql mysql   114688 Nov 23 15:24 sbtest3#P#p3.ibd
-rw-r-----. 1 mysql mysql   114688 Nov 23 15:24 sbtest3#P#p4.ibd

Modifying the partition structure

As soon as we touch at the partitioning structure, the metadata gets changed to the new native partition engine.
For example, if we modify the partition in this case adding a new partition the table gets directly updated to native partition if the engine supports it:

mysql> ALTER TABLE sbtest1 PARTITION BY KEY() partitions 6;
Query OK, 1000000 rows affected (1 min 9.46 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

MySQL 8.0

So we saw that if we have partitioned tables in 5.6, if they use an engine supporting native partitioning, the tables will be modified during the upgrade process, manually or when there is some structure change. But if you have MyISAM partitioned tables, they will stay intact and you will still be able to use. But what if we upgrade to 8.0 as the warning message explicitly says it will removed in the next release ?

In fact, if we keep MyISAM partitioned tables, MySQL 8.0 won’t start with messages like these in the error log:

2017-11-22T17:08:37.351519Z 2 [ERROR] /usr/sbin/mysqld: The 'partitioning' feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1'
2017-11-22T17:08:37.353183Z 2 [ERROR] /usr/sbin/mysqld: Can't find file: './sbtest/sbtest2.frm' (errno: 0 - Success)
2017-11-22T17:08:37.353210Z 2 [ERROR] Error in reading file ./sbtest/sbtest2.frm
2017-11-22T17:08:37.356218Z 2 [ERROR] Error in creating TABLE_SHARE from sbtest2.frm file.
2017-11-22T17:08:37.861365Z 0 [ERROR] Failed to Populate DD tables.
2017-11-22T17:08:37.862162Z 0 [ERROR] Aborting

The only viable solution will be then to downgrade to 5.7 and change the table’s engine to InnoDB (or any other engine supporting native partitioning).

If you plan to migrate to MySQL 8.0, don’t forget then to check if you still have some MyISAM partitioned tables:

mysql> SELECT count(*) as '# partitions', t1.TABLE_SCHEMA, t1.TABLE_NAME, 
       ANY_VALUE(ENGINE) ENGINE, ANY_VALUE(CREATE_OPTIONS) OPTIONS,
       CONCAT(ROUND((ANY_VALUE(t2.index_length)+
            ANY_VALUE(t2.data_length))/1024/1024),'MB') AS SIZE
       FROM INFORMATION_SCHEMA.PARTITIONS t1
       JOIN INFORMATION_SCHEMA.TABLES t2 
         ON t2.TABLE_SCHEMA=t1.TABLE_SCHEMA 
        AND t2.TABLE_NAME=t1.TABLE_NAME
       WHERE ENGINE='myisam' 
         AND t1.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
       GROUP BY TABLE_SCHEMA, TABLE_NAME having count(*)> 1
+--------------+--------------+------------+--------+-------------+-------+
| # partitions | TABLE_SCHEMA | TABLE_NAME | ENGINE | OPTIONS     | SIZE  |
+--------------+--------------+------------+--------+-------------+-------+
|            5 | sbtest       | sbtest2    | MyISAM | partitioned | 200MB |
+--------------+--------------+------------+--------+-------------+-------+
1 row in set, 1 warning (0.02 sec)

I hope you will enjoy the new native partitions !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

4 Comments

  1. Salut Frédéric, 2 questions:

    1) you write “this check will be removed from the start process in the furute 5.7 releases”: could you tell us more about this ? Is this related to my Bug#85830 ?

    2) you also write “don’t forget then to check if you still have some MyISAM tables”: MyISAM is not a problem in MySQL 8.0, right ? The problem is only with MyISAM partitioned tables, right ?

    Merci et à bientôt à Bruxelles.

    • Hi JF,

      Thank you for your comment, first the obvious one, yes it’s about MyISAM partitioned tables as the ENGINE doesn’t support native partition. I’ll update the post to make it more clear.
      About why the check will be removed at start up of mysqld, I don’t know if this is linked to your bug. It might be, but I don’t know. However I think that as mysql_upgrade does that and that you should run it after each upgrade, that should be enough. (And you this blog explains it too). A bientôt à Bruxelles !

  3. After 20 years of MySQL it’s the first time I read your articles and I want to congratulate with you for the clarity and completeness.
    Thanks a lot for your help.
    Mimmo

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue