Sample Header Ad - 728x90

pt-online-schema-change drop_swap doesn't work, so what to do?

0 votes
1 answer
624 views
After waiting 24 hours for a ptosc this:
2023-05-29T11:29:40 Copied rows OK.
2023-05-29T11:29:40 Max rows for the rebuild_constraints method: 2710
Determining the method to update foreign keys...
2023-05-29T11:29:40   xxx_production.click_tracks: too many rows: 4325947; must use drop_swap                                                                                                  
--alter-foreign-keys-method=drop_swap doesn't work with MySQL 8.0+
See https://bugs.mysql.com/bug.php?id=89441 
2023-05-29T11:29:40 Dropping triggers...
2023-05-29T11:29:42 Dropped triggers OK.
Not dropping the new table xxx_production._orders_new because --swap-tables failed.  To drop the new table, execute:                                                                           
DROP TABLE IF EXISTS xxx_production._orders_new;
xxx_production.orders was not altered.
orders is a table with 136 million rows. But I think the issue is the click_tracks table that has 4.3 million rows. If drop_swap must be used, but the next line says drop_swap doesn't work on MySQL8....what are we supposed to do exactly? EDIT: *Before*
mysql> describe orders;
+----------------------------------+--------------+------+-----+---------+----------------+
| Field                            | Type         | Null | Key | Default | Extra          |
+----------------------------------+--------------+------+-----+---------+----------------+
...
| item_promotion_id                | int          | YES  |     | NULL    |                |
*After*
| item_promotion_id                | varchar(25)  | YES  |     | NULL    |                |
*Command*
pt-online-schema-change --critical-load='Threads_running=600' --alter-foreign-keys-method=auto --execute --alter "MODIFY COLUMN item_promotion_id varchar(25)" D=xxx_production,t=orders
Column (item_promotion_id) is NOT in a FK or used in an INDEX. Could the issue actually be a FK between orders and click_tracks? EDIT 2: Sadly, 'just' running the ADD COLUMN c VARCHAR, ALGORITHM=INPLACE; fails on tables so large because we hit ERROR 1062 (23000): Duplicate entry. This is described in the MySQL documentation . So it seems we are back to pt-osc but instead of doing an ALTER doing an ADD. EDIT 3: Trying to do an ADD COLUMN using pt-osc results in the same failure!
pt-online-schema-change --critical-load='Threads_running=600' --alter-foreign-keys-method=auto --execute --alter "ADD COLUMN item_promotion_ref varchar(25)" D=xxx_production,t=orders
2023-06-01T06:11:27 Max rows for the rebuild_constraints method: 3090
Determining the method to update foreign keys...
2023-06-01T06:11:27   xxx_production.click_tracks: too many rows: 4325947; must use drop_swap                                                                         
--alter-foreign-keys-method=drop_swap doesn't work with MySQL 8.0+
See https://bugs.mysql.com/bug.php?id=89441 
2023-06-01T06:11:27 Dropping triggers...
2023-06-01T06:11:28 Dropped triggers OK.
Not dropping the new table xxx_production._orders_new because --swap-tables failed.  To drop the new table, execute:                                                  
DROP TABLE IF EXISTS xxx_production._orders_new;
xxx_production.orders was not altered.
I think there is nothing I can do with this table. Time to hire a DBA.
Asked by phil (153 rep)
May 29, 2023, 12:48 PM
Last activity: Jun 1, 2023, 06:53 AM