github / github/gh-ost

Lock acquisition failing, migration fails after exhausting retries

Open
#1,109 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.