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

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

    XMLWordPrintable

Details

    • Bug
    • Status: On Hold
    • High
    • Resolution: Unresolved
    • 3.0.6
    • None
    • None

    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)

✘ [email protected]  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)

✘ [email protected]  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

      ✘ [email protected]  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`.

[email protected]  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)

✘ [email protected]  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.

      Attachments

        Issue Links

          relates to

          Improvement - An improvement or enhancement to an existing feature or task. PT-205 pt-online-schema-change show message on rocksdb if binlog_format != row

          • High - Crashes, loss of data, severe memory leak.
          • Done

          Activity

            People

              Unassigned Unassigned
              tomislav.plavcic@percona.com Tomislav Plavcic
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:

                Smart Checklist