Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
None
-
None
Description
pt-online-schema-change pt-online-schema-change --no-use-insert-ignore is broken:
- no INSERT statements can happen during the --no-use-insert-ignore
- UPDATE and DELETE can only be done on rows that are already copied.
How to reproduce:
mysql> ... drop database pt; create database pt; use pt create table test (id int primary key auto_increment, otherid int); insert into test values(null,null); insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; insert into test select null, null from test; update test set otherid=id;
# pt-online-schema-change --version pt-online-schema-change 3.0.3
# touch /tmp/ptosc.pause ; # pt-online-schema-change --pause-file=/tmp/ptosc.pause --no-use-insert-ignore --execute D=pt,t=test --alter "add unique key (otherid)" No slaves found. See --recursion-method if host perconaserver has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. 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 `pt`.`test`... Creating new table... Created new table pt._test_new OK. Altering new table... Altered `pt`.`_test_new` OK. 2017-06-13T20:28:11 Creating triggers... 2017-06-13T20:28:11 Created triggers OK. 2017-06-13T20:28:11 Copying approximately 262682 rows... Sleeping 60 seconds because /tmp/ptosc.pause exists
When you get this...
In another terminal:
# mysql -e "insert into pt.test values (null, 10000000);" # rm /tmp/ptosc.pause
This creates a row. the trigger on the table will copy it to the new table.
....
then in the other terminal:
Copying `pt`.`test`: 0% 04:21:16 remain 2017-06-13T20:32:51 Dropping triggers... 2017-06-13T20:32:51 Dropped triggers OK. 2017-06-13T20:32:51 Dropping new table... 2017-06-13T20:32:51 Dropped new table OK. `pt`.`test` was not altered. 2017-06-13T20:32:51 Error copying rows from `pt`.`test` to `pt`.`_test_new`: 2017-06-13T20:32:51 DBD::mysql::st execute failed: Duplicate entry '458732' for key 'PRIMARY' [for Statement "INSERT LOW_PRIORITY INTO `pt`.`_test_new` (`id`, `otherid`) SELECT `id`, `otherid` FROM `pt`.`test` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 4241 copy nibble*/" with ParamValues: 0='368360', 1='458732'] at /bin/pt-online-schema-change line 11011.
Because insert ignore is not used, it will fail...
Credits mainly to dov.endress and Team atlas (bradley.mickel, emanuel.calvo, maxbube, dov.endress, marcelo.goncalves)