pt-online-schema-change resume functionality doesn't work with ADD INDEX
General
Escalation
General
Escalation
Description
Environment
None
AFFECTED CS IDs
CS0052072
relates to
Activity
Show:
Sveta Smirnova March 21, 2025 at 7:20 PM
Sveta Smirnova
March 21, 2025 at 7:20 PM
This is fixed by fix for https://perconadev.atlassian.net/browse/PT-2389.
Adam Nichols November 14, 2024 at 5:32 PM
Adam Nichols
November 14, 2024 at 5:32 PM
Thank you for the reply, I will give it a shot.
Aaditya Dubey November 6, 2024 at 4:08 PM
Aaditya Dubey
November 6, 2024 at 4:08 PM
Hi @Adam Nichols
Thank you for the report.
This report is now verified.
Please use the same test case that is mentioned here at https://perconadev.atlassian.net/browse/PT-2389
After building the table and data, please run the following command:
./pt-online-schema-change --alter="ADD INDEX idx_email(email)" h=127.0.0.1,P=8039,u=msandbox,p=msandbox,D=test,t=employees --progress time,5 --no-drop-new-table --no-drop-triggers --history --history-table=testdb.pt_osc_history --chunk-size=10 --executekill the job using ctrl+c
...
^C# Exiting on SIGINT.
Not dropping triggers because the tool was interrupted. To drop the triggers, execute:
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employees_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employees_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employees_ins`
Not dropping the new table `test`.`_employees_new` because the tool was interrupted. To drop the new table, execute:
DROP TABLE IF EXISTS `test`.`_employees_new`;
`test`.`employees` was not altered.
History saved. Job id: 5Resume the job:
$:~/percona-toolkit-3.6.0/bin$ ./pt-online-schema-change --alter="ADD INDEX idx_email(email)" h=127.0.0.1,P=8039,u=msandbox,p=msandbox,D=test,t=employees --progress time,5 --no-drop-new-table --no-drop-triggers --history --history-table=testdb.pt_osc_history --chunk-size=10 --execute --resume=5
2024-11-06T16:03:42 Job 6 started.
No slaves found. See --recursion-method if host localhost 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 `test`.`employees`...
Altering new table...
Not dropping the new table `test`.`_employees_new` because --no-drop-new-table was specified. To drop the new table, execute:
DROP TABLE IF EXISTS `test`.`_employees_new`;
`test`.`employees` was not altered.
History saved. Job id: 6
Error altering new table `test`.`_employees_new`: DBD::mysql::db do failed: Duplicate key name 'idx_email' [for Statement "ALTER TABLE `test`.`_employees_new` ADD INDEX idx_email(email)"] at ./pt-online-schema-change line 9812.
Not dropping the new table `test`.`_employees_new` because --no-drop-new-table was specified. To drop the new table, execute:
DROP TABLE IF EXISTS `test`.`_employees_new`;
`test`.`employees` was not altered.
History saved. Job id: 6Done
Details
Details
Assignee
Sveta Smirnova
Sveta SmirnovaReporter
Adam Nichols
Adam NicholsPriority
MediumComponents
Fix versions
Needs QA
Yes
Created October 16, 2024 at 6:10 PM
Updated March 21, 2025 at 7:20 PM
Resolved March 21, 2025 at 7:20 PM
OSC fails to resume an alter that adds an index. Version is 8.0.31 for SQL, 3.6.0 for OSC.
The source is
METADATA_ENTRYand the destination_METADATA_ENTRY_new.Error altering new table `cromwell`.`_METADATA_ENTRY_new`: DBD::mysql::db do failed: Duplicate key name 'IX_METADATA_ENTRY_WEU_MK' [for Statement "ALTER TABLE `cromwell`.`_METADATA_ENTRY_new` ADD INDEX IX_METADATA_ENTRY_WEU_MK (WORKFLOW_EXECUTION_UUID, METADATA_KEY), ADD INDEX IX_METADATA_ENTRY_WEU_CF_JSI_JRA_MK (WORKFLOW_EXECUTION_UUID, CALL_FQN, JOB_SCATTER_INDEX, JOB_RETRY_ATTEMPT, METADATA_KEY)"] at /usr/bin/pt-online-schema-change line 9812.I am testing on Dev and causing the interruption deliberately by stopping the MySQL instance. See below for the OSC invocations and select output.
Original invocation:
pt-online-schema-change --history --execute --no-drop-new-table --no-drop-triggers --alter "ADD INDEX IX_METADATA_ENTRY_WEU_MK (WORKFLOW_EXECUTION_UUID, METADATA_KEY), ADD INDEX IX_METADATA_ENTRY_WEU_CF_JSI_JRA_MK (WORKFLOW_EXECUTION_UUID, CALL_FQN, JOB_SCATTER_INDEX, JOB_RETRY_ATTEMPT, METADATA_KEY)" h=127.0.0.1,u=cromwell,p=XXXXXXXX,D=cromwell,P=3306,t=METADATA_ENTRY --chunk-time=0.05 --max-load Threads_running=25 --critical-load Threads_running=1000 --tries swap_tables:600:1;Log message & job ID when I restart the MySQL instance:
Error copying rows from `cromwell`.`METADATA_ENTRY` to `cromwell`.`_METADATA_ENTRY_new`: 2024-10-16T17:45:15 DBD::mysql::st execute failed: Server shutdown in progress [for Statement "UPDATE `percona`.`pt_osc_history` SET lower_boundary = ?, upper_boundary = ? WHERE job_id = 1" with ParamValues: 0=622099214, 1=622099787] at /usr/bin/pt-online-schema-change line 12324. History saved. Job id: 1Resume invocation:
pt-online-schema-change --history --execute --no-drop-new-table --no-drop-triggers --resume=1 --alter "ADD INDEX IX_METADATA_ENTRY_WEU_MK (WORKFLOW_EXECUTION_UUID, METADATA_KEY), ADD INDEX IX_METADATA_ENTRY_WEU_CF_JSI_JRA_MK (WORKFLOW_EXECUTION_UUID, CALL_FQN, JOB_SCATTER_INDEX, JOB_RETRY_ATTEMPT, METADATA_KEY)" h=127.0.0.1,u=cromwell,p=XXXXXXXX,D=cromwell,P=3306,t=METADATA_ENTRY --chunk-time=0.05 --max-load Threads_running=25 --critical-load Threads_running=1000 --tries swap_tables:600:1;