0

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.

Improve this question
5
  • What changes were you making with pt-osc? (Alas, I should blog about why Foreign keys are not worth having.) Commented May 29, 2023 at 16:51
  • the pt-osc is changing a column type (from id to varchar) on the orders table. Of course, if you know a better way please please let me know! I am stuck with some responsibilities of looking after this huge database. Commented May 29, 2023 at 17:08
  • And that is the PRIMARY KEY? In which case, it must copy the entire table over, and probably has to jump through hoops to get it done. Please show me the column definition before and after the change, plus sample values. I have some thoughts on what to do, but need more details to avoid barking up an unworkable tree. Commented May 29, 2023 at 17:18
  • And do you have any cases of ORDER BY id that would be messed up by changing from int to varchar? And do you have access to the application code? Commented May 29, 2023 at 17:20
  • No, it is not a primary key. It is an additional field that is not used in any FK. It MIGHT be used in an index, but I didn't check that. Also ALL the data in that column is the value of 0. Commented May 29, 2023 at 17:49

1 Answer 1

Reset to default
1

I don't think pt-osc is needed for your task. (In older versions, it was needed for virtually all ALTERs.)

Since you are running MySQL 8, you should be able to do

ALTER TABLE ... ADD COLUMN item_promo_id VARCHAR ... ALGORITHM=INPLACE;

cf: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

Then change the code to abandon item_promotion_id and use item_promo_id instead.

FOREIGN KEYs are never "required". They do two things:

  • Apply an existence check during INSERTs. This is just "good to have".
  • Make sure that it has a suitable INDEX.

Note that ALTER TABLE ... ADD INDEX ... ALGORITHM=INPLACE is available. It is not fast, but it is non-invasive.

cf: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

Improve this answer
3
  • Thanks. I was actually thinking to just leave the column and change the code to a new column. Perhaps out of scope, but, in future of course we might need to really use pt-osc on this table. How to do it? Commented May 30, 2023 at 0:08
  • At least on my test system I get an error of ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE. Also on production system of 8.0.23-14 Percona Server Commented May 30, 2023 at 3:14
  • Actually 'INSTANT is the default algorithm as of MySQL 8.0.12, and INPLACE before that.' for ADD COLUMN. So I don't need to specify that. I just worry about table locking and taking 24 hours to process the table. Commented May 30, 2023 at 3:20

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.