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) ✘ 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.
Attachments
Issue Links
- relates to
-
PT-205 pt-online-schema-change show message on rocksdb if binlog_format != row
-
- Done
-