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
Last activity: Jun 1, 2023, 06:53 AM