Not the answer you need?
Register and ask your own question!

Error with pt-online-schema-change when rebuilding partitioned table

ancient_techancient_tech EntrantInactive User Role Beginner
Hi,

I'm in the progress of migrating a large amount of tables from InnoDB to TokuDB and have found what looks like a bug.
Scenario is that I have a table with partition in InnoDB and want to use pt-online-schema-change to change the partitioning layout and migrate to TokuDB.
CREATE TABLE temp.`test_1` (
  `id` int(1) DEFAULT NULL
) DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (2) ,
 PARTITION p2 VALUES LESS THAN (4) ) */
set global default_storage_engine=TokuDB;

I then use --default-engine option to the new table will be created with TokuDB
pt-online-schema-change --dry-run --progress percentage,1 --no-drop-new-table --default-engine --alter "reorganize partition p1,p2  into (\
PARTITION p3 VALUES LESS THAN (6), \
PARTITION p4 VALUES LESS THAN (9), \
PARTITION pMAX VALUES LESS THAN MAXVALUE);" D=temp,t=test_1,h=localhost

PTdebug shows the table that is created looks like this:
# pt_online_schema_change:9618 622 CREATE TABLE `temp`.`_test_1_new` (
#   `id` int(1) DEFAULT NULL
# ) DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
# /*!50100 PARTITION BY RANGE (id)
# (PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
#  PARTITION p2 VALUES LESS THAN (4) ENGINE = InnoDB) */

And I get the error "The mix of handlers in the partitions is not allowed in this version of MySQL". This because the table is created with the default engine (TokuDB) but the partitions are created with InnoDB as it was on the original table.
So it looks like that --default-engine doesn't handle the ENGINE on each partition.

Brgds

Comments

  • ancient_techancient_tech Entrant Inactive User Role Beginner
    Suggestion for fix is a new regex for default-engine. Replace:
    $sql =~ s/\s+ENGINE=\S+//;
    
    With:
    $sql =~ s/\s*ENGINE\s*=\s*\w+//g;
    

    Better would be to be able to have an option like --new-table-engine that allows you to decided what engine the new table will use.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.