Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-240

pt-online-schema-change doesn't work for MyRocks

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: 3.0.6
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      There was a fix for MyRocks in PT-205 but it seems to me this is not enough.
      So what this fix is preventing is doing "ENGINE=RocksDB" while binlog_format!=row but this is not enough because I can use pt-osc and try to do other table alters and they won't work.

      Here's some cases:
      #1: binlog_format=row, add column to rocksdb table failing because rocksdb doesn't support gap log

      master [localhost] {msandbox} ((none)) > show variables like 'binlog_format';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | binlog_format | ROW   |
      +---------------+-------+
      1 row in set (0.01 sec)
      
      ✘ plavi@bender  rsandbox_Percona-Server-5_7_20  pt-online-schema-change --execute --alter "ADD COLUMN c1 INT" h=127.0.0.1,P=20795,u=msandbox,p=msandbox,D=world_rocksdb,t=City
      Found 2 slaves:
      bender -> SBslave1:20796
      bender -> SBslave2:20797
      Will check slave lag on:
      bender -> SBslave1:20796
      bender -> SBslave2:20797
      Operation, tries, wait:
       analyze_table, 10, 1
       copy_rows, 10, 0.25
       create_triggers, 10, 1
       drop_triggers, 10, 1
       swap_tables, 10, 1
       update_foreign_keys, 10, 1
      Altering `world_rocksdb`.`City`...
      Creating new table...
      Created new table world_rocksdb._City_new OK.
      Altering new table...
      Altered `world_rocksdb`.`_City_new` OK.
      2018-01-03T10:38:53 Creating triggers...
      2018-01-03T10:38:53 Created triggers OK.
      2018-01-03T10:38:53 Copying approximately 10365 rows...
      2018-01-03T10:38:53 Dropping triggers...
      2018-01-03T10:38:53 Dropped triggers OK.
      2018-01-03T10:38:53 Dropping new table...
      2018-01-03T10:38:53 Dropped new table OK.
      `world_rocksdb`.`City` was not altered.
      2018-01-03T10:38:53 Error copying rows from `world_rocksdb`.`City` to `world_rocksdb`.`_City_new`: 2018-01-03T10:38:53 DBD::mysql::st execute failed: Using Gap Lock without full unique key in multi-table or multi-statement transactions is not allowed. You need to either rewrite queries to use all unique key columns in WHERE equal conditions, or rewrite to single-table, single-statement transaction.  Query: INSERT LOW_PRIORITY IGNORE INTO `world_rocksdb`.`_City_new` (`id`, `name`, `countrycode`, `district`, `population`) SELECT `id`, `name`, `countrycode`, `district`, `population` FROM `world_rocksdb`.`City` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) A [for Statement "INSERT LOW_PRIORITY IGNORE INTO `world_rocksdb`.`_City_new` (`id`, `name`, `countrycode`, `district`, `population`) SELECT `id`, `name`, `countrycode`, `district`, `population` FROM `world_rocksdb`.`City` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 25807 copy nibble*/" with ParamValues: 0='1', 1='1000'] at /usr/bin/pt-online-schema-change line 11352.
      

      #2: binlog_format=statement, add column to rocksdb table

      master [localhost] {msandbox} ((none)) > show variables like 'binlog_format';
      +---------------+-----------+
      | Variable_name | Value     |
      +---------------+-----------+
      | binlog_format | STATEMENT |
      +---------------+-----------+
      1 row in set (0.01 sec)
      
      ✘ plavi@bender  rsandbox_Percona-Server-5_7_20  pt-online-schema-change --execute --alter "ADD COLUMN c1 INT" h=127.0.0.1,P=20795,u=msandbox,p=msandbox,D=world_rocksdb,t=City
      Found 2 slaves:
      bender -> SBslave1:20796
      bender -> SBslave2:20797
      Will check slave lag on:
      bender -> SBslave1:20796
      bender -> SBslave2:20797
      Operation, tries, wait:
       analyze_table, 10, 1
       copy_rows, 10, 0.25
       create_triggers, 10, 1
       drop_triggers, 10, 1
       swap_tables, 10, 1
       update_foreign_keys, 10, 1
      Altering `world_rocksdb`.`City`...
      Creating new table...
      Created new table world_rocksdb._City_new OK.
      Altering new table...
      2018-01-03T10:45:20 Dropping new table...
      2018-01-03T10:45:20 Dropped new table OK.
      `world_rocksdb`.`City` was not altered.
      Error altering new table `world_rocksdb`.`_City_new`: DBD::mysql::db do failed: Can't execute updates on master with binlog_format != ROW. [for Statement "ALTER TABLE `world_rocksdb`.`_City_new` ADD COLUMN c1 I
      NT"] at /usr/bin/pt-online-schema-change line 9194.
      

      #3: binlog_format=row, alter engine=rocksdb
      from InnoDB->RocksDB it works, but from RocksDB->InnoDB it doesn't and it actually doesn't because some RocksDB issue (cannot have string indexed column with non-binary collation). I think I'll report MyRocks issue for this since it doesn't allow create table with this, but it allows alter table with this from InnoDB to RocksDB

      ✘ plavi@bender  rsandbox_Percona-Server-5_7_20  pt-online-schema-change --execute --alter "ENGINE=RocksDB" h=127.0.0.1,P=20795,u=msandbox,p=msandbox,D=world,t=City
      Found 2 slaves:
      bender -> SBslave1:20796
      bender -> SBslave2:20797
      Will check slave lag on:
      bender -> SBslave1:20796
      bender -> SBslave2:20797
      Operation, tries, wait:
       analyze_table, 10, 1
       copy_rows, 10, 0.25
       create_triggers, 10, 1
       drop_triggers, 10, 1
       swap_tables, 10, 1
       update_foreign_keys, 10, 1
      Altering `world`.`City`...
      Creating new table...
      Created new table world._City_new OK.
      Altering new table...
      Altered `world`.`_City_new` OK.
      2018-01-03T10:54:46 Creating triggers...
      2018-01-03T10:54:46 Created triggers OK.
      2018-01-03T10:54:46 Copying approximately 4141 rows...
      2018-01-03T10:54:46 Copied rows OK.
      2018-01-03T10:54:46 Analyzing new table...
      2018-01-03T10:54:46 Swapping tables...
      2018-01-03T10:54:46 Swapped original and new tables OK.
      2018-01-03T10:54:46 Dropping old table...
      2018-01-03T10:54:46 Dropped old table `world`.`_City_old` OK.
      2018-01-03T10:54:46 Dropping triggers...
      2018-01-03T10:54:46 Dropped triggers OK.
      Successfully altered `world`.`City`.
      
      plavi@bender  rsandbox_Percona-Server-5_7_20  pt-online-schema-change --execute --alter "ENGINE=InnoDB" h=127.0.0.1,P=20795,u=msandbox,p=msandbox,D=world,t=City
      Found 2 slaves:
      bender -> SBslave1:20796
      bender -> SBslave2:20797
      Will check slave lag on:
      bender -> SBslave1:20796
      bender -> SBslave2:20797
      Operation, tries, wait:
       analyze_table, 10, 1
       copy_rows, 10, 0.25
       create_triggers, 10, 1
       drop_triggers, 10, 1
       swap_tables, 10, 1
       update_foreign_keys, 10, 1
      Altering `world`.`City`...
      Creating new table...
      `world`.`City` was not altered.
      Error creating new table: DBD::mysql::db do failed: Unsupported collation on string indexed column world._City_new.CountryCode Use binary collation (binary, latin1_bin, utf8_bin). [for Statement "CREATE TABLE `world`.`_City_new` (
       `ID` int(11) NOT NULL AUTO_INCREMENT,
       `Name` char(35) NOT NULL DEFAULT '',
       `CountryCode` char(3) NOT NULL DEFAULT '',
       `District` char(20) NOT NULL DEFAULT '',
       `Population` int(11) NOT NULL DEFAULT '0',
       `c1` int(11) DEFAULT NULL,
       PRIMARY KEY (`ID`),
       KEY `CountryCode` (`CountryCode`)
      ) ENGINE=ROCKSDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1"] at /usr/bin/pt-online-schema-change line 10409.
      

      #4: binlog_format=statement, alter engine=rocksdb
      This one is actually ok and this was the fix in PT-205, but it's only one of many situations.

      master [localhost] {msandbox} ((none)) > show variables like 'binlog_format';
      +---------------+-----------+
      | Variable_name | Value     |
      +---------------+-----------+
      | binlog_format | STATEMENT |
      +---------------+-----------+
      1 row in set (0.01 sec)
      
      ✘ plavi@bender  rsandbox_Percona-Server-5_7_20  pt-online-schema-change --execute --alter "ENGINE=RocksDB" h=127.0.0.1,P=20795,u=msandbox,p=msandbox,D=world,t=City
      Found 2 slaves:
      bender -> SBslave1:20796
      bender -> SBslave2:20797
      Will check slave lag on:
      bender -> SBslave1:20796
      bender -> SBslave2:20797
      Operation, tries, wait:
       analyze_table, 10, 1
       copy_rows, 10, 0.25
       create_triggers, 10, 1
       drop_triggers, 10, 1
       swap_tables, 10, 1
       update_foreign_keys, 10, 1
      Altering `world`.`City`...
      `world`.`City` was not altered.
      Cannot change engine to RocksDB while binlog_format is other than 'ROW' at /usr/bin/pt-online-schema-change line 10071.
      

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  carlos.salguero Carlos Salguero
                  Reporter:
                  tomislav.plavcic@percona.com Tomislav Plavcic
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  0 Start watching this issue

                  Dates

                  • Created:
                    Updated: