Lock acquisition failing, migration fails after exhausting retries
- Dominant language
- Go
- Stars
- 13.6k
- Forks
- 1.4k
- Avg merge
- 2h 31m
- Merged PRs (30d)
- 4
Description
We have a frequently used MySQL 5.7 table for which we are running an ADD COLUMN migration. Everything works great until cutover, where we can't get a lock on the table despite numerous retries:
```
2022-03-16 02:59:33 INFO Grabbing voluntary lock: gh-ost.1068094.lock
2022-03-16 02:59:33 INFO Setting LOCK timeout as 6 seconds
2022-03-16 02:59:33 INFO Looking for magic cut-over table
2022-03-16 02:59:33 INFO Creating magic cut-over table `schema1`.`_table1_20220314190603_del`
Copy: 97573227/97573227 100.0%; Applied: 583110; Backlog: 2/1000; Time: 31h53m30s(total), 22h3m10s(copy); streamer: mysql-bin-changelog.171267:50654065; Lag: 0.01s, State: migrating; ETA: due
2022-03-16 02:59:33 INFO Magic cut-over table created
2022-03-16 02:59:33 INFO Locking `schema1`.`table1`, `schema1`.`_table1_20220314190603_del`
Copy: 97573227/97573227 100.0%; Applied: 583113; Backlog: 0/1000; Time: 31h53m35s(total), 22h3m10s(copy); streamer: mysql-bin-changelog.171267:50717917; Lag: 0.01s, State: migrating; ETA: due
2022-03-16 02:59:39 ERROR Error 1205: Lock wait timeout exceeded; try restarting transaction
2022-03-16 02:59:39 INFO Looking for magic cut-over table
2022-03-16 02:59:39 ERROR Error 1205: Lock wait timeout exceeded; try restarting transaction
2022-03-16 02:59:39 INFO Dropping magic cut-over table
2022-03-16 02:59:39 INFO Dropping table `schema1`.`_table1_20220314190603_del`
2022-03-16 02:59:39 INFO Table dropped
2022-03-16 02:59:40 INFO Grabbing voluntary lock: gh-ost.1068100.lock
2022-03-16 02:59:40 INFO Setting LOCK timeout as 6 seconds
2022-03-16 02:59:40 INFO Looking for magic cut-over table
2022-03-16 02:59:40 INFO Creating magic cut-over table `schema1`.`_table1_20220314190603_del`
2022-03-16 02:59:40 INFO Magic cut-over table created
2022-03-16 02:59:40 INFO Locking `schema1`.`table1`, `schema1`.`_table1_20220314190603_del`
Copy: 97573227/97573227 100.0%; Applied: 583166; Backlog: 0/1000; Time: 31h53m40s(total), 22h3m10s(copy); streamer: mysql-bin-changelog.171267:52012375; Lag: 0.01s, State: migrating; ETA: due
2022-03-16 02:59:46 ERROR Error 1205: Lock wait timeout exceeded; try restarting transaction
2022-03-16 02:59:46 INFO Looking for magic cut-over table
2022-03-16 02:59:46 ERROR Error 1205: Lock wait timeout exceeded; try restarting transaction
```
Once this fails, the socket file is removed and the migration is failed. The table is large so the migration takes many hours.
Our questions are these:
- Can we resume the migration at this point?
- Can we escalate the lock type or timeout such that we can more reliably acquire it?
Contributor guide
Assessment
This issue has not been assessed yet.