Bug #76374 Partitioned tables not upgraded to use native partitioning

• Submitted: 18 Mar 2015 10:30
• Modified: 4 Sep 2015 15:00
• Reporter: Mattias Jonsson
• Status: Closed
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.7.6
• OS: Any
• CPU Architecture: Any

[18 Mar 2015 10:30] Mattias Jonsson

Description:
In 5.7.6 InnoDB supports native partitioning, wl#6035, but upgrading a table from earlier versions (<5.7.6) will not use this. Only newly created partitioned innodb tables will use the feature.

There are no easy way to ALTER an existing table to use the native partitioning without copying the data.

How to repeat:
Create a table in < 5.7.6, upgrade (and use mysql_upgrade) to 5.7.6+ and the table will still not be using native partitioning.

Suggested fix:
Allow a maintenance SQL command to change the .frm to have db_type = DB_TYPE_INNODB, instead of db_type = DB_TYPE_PARTITION_DB + default_part_db_type = DB_TYPE_INNODB and remove the .par file without the need of a full table scan or copy.

mysql_upgrade basically works like:
CHECK TABLE t FOR UPGRADE;
depending on the output:
OK - do nothing
%ALTER TABLE% in Msg_text and Msg_type != "note" - Rebuild the table (ALTER TABLE t FORCE) unless PARTITION BY ... then run the alter command
Msg_type = "status"
Else repair it by 'REPAIR TABLE t'

Common requirement is that CHECK TABLE, ha_partition::check_for_upgrade(), will notice that the base storage engine can handle partitioning by it self and return HA_ADMIN_NEEDS_UPGRADE (or _CHECK or _ALTER).

And then there are several option for fixing it:
1. Fix so that REPAIR TABLE will update the .frm file and remove the .par file. Without touching the data files!
2. Fix ALTER TABLE t FORCE so it will update the .frm file and remove the .par file. Without touching the data files!
3. Update open_binary_frm, if db_type = DB_TYPE_PARTITION_DB and default_part_db_type engine supports partitioning, then replace db_type with default_part_db_type. This would open the table using native partitioned engines even if the table was created to use ha_partition generic engine. But it would not remove the .par file.

[4 Sep 2015 15:00] Jon Stephens

Documented fix in the MySQL 5.7.9 changelog as follows:

    There was no way to upgrade existing partitioned tables to use
    the native partitioning implemented for InnoDB tables in MySQL
    5.7.6. This fix adds support to both mysql_upgrade and the mysql
    client for upgrading partitioned InnoDB tables created in
    previous releases, which used the ha_partition handler, to use
    InnoDB native partitioning.
    
      ·mysql_upgrade now checks for all InnoDB tables that were
      created using the generic ha_partition handler and attempts
      to upgrade them to InnoDB native partitioning.
      
      ·In the mysql client, pre-5.7.6 partitioned InnoDB tables can
      be upgraded one by one to native partitioning using the
      ALTER TABLE ... UPGRADE PARTITIONING statement implemented
      in this release.

Also added the new syntax to ALTER TABLE description in the Manual, noted behaviour changes in the What's New and mysql_upgrade sections of the Manual as well.

Closed.