Sample Header Ad - 728x90

Failure Case of "drop_swap" Option in alter-foreign-keys-method for Percona's pt-online-schema-change Tool

0 votes
0 answers
70 views
I am looking at the option called "alter-foreign-keys-method " in the pt-online-schema-change tool where it uses the drop_swap. In the documentation, it says there are 2 risks: > First, for a short time between dropping the original table and renaming the temporary table, the table to be altered simply does not exist, and queries against it will result in an error. Secondly, if there is an error and the new table cannot be renamed into the place of the old one, then it is too late to abort, because the old table is gone permanently. What I am trying to understand is the second risk where the renaming of the table will fail. According to the MySQL documentation, >RENAME TABLE tbl_name TO new_tbl_name changes internally generated foreign key constraint names and user-defined foreign key constraint names that begin with the string “tbl_name_ibfk_” to reflect the new table name. InnoDB interprets foreign key constraint names that begin with the string “tbl_name_ibfk_” as internally generated names. > Foreign key constraint names that point to the renamed table are automatically updated unless there is a conflict, in which case the statement fails with an error. A conflict occurs if the renamed constraint name already exists. In such cases, you must drop and re-create the foreign keys for them to function properly. Would this be the only scenario where the rename table will fail? Because I think I can just write a plugin or a script to make sure that no constraint named "tbl_name_ibfk_" exists before pt-online-schema-change tool runs.
Asked by Brian Estrada (114 rep)
Dec 6, 2024, 01:11 AM