migrate
migrate copied to clipboard
golang-migrate
can't acquire lock mysql db
I am getting cant acquire lock error while connecting to mysql db and during schema migration
same issue here with migrate version v4.11.0 and MySQL 8. we need to restart the MySQL to fix it, is there any special setting in MySQL for handle this issue?
@dhui nope, it just single process running.. I am still facing this issue
Same issue here. However, it doesn't always fail. If one succeeds then the next few calls fail with a acquire lock error. I tried setting the -lock-timeout to a higher number but it seems it always times out after 10 seconds. Even though the DB lock timeout is set to 50. I checked the DB and there are no locks, so I don't get why it keeps failing.
Mysql 8.0 Migrate: latest
我也遇到了相同的问题。目前排查下来是get_lock和release_lock使用的连接不是同一个，导致锁无法释放。
我这边是由于自己debug migrate时，然后执行迁徙导致can't acquire lock。然后我把debug关闭了，就迁移成功了。
我也碰到了类似问题，但原因和这个项目代码无关，而是因为生产环境的复杂性。我们的app通过db-proxy连接mysql，其中db-proxy维护了和mysql的一个连接池，而app拿到的mysql连接实际是db-proxy的连接。在开启事务等场景下，db-proxy会保证app连接上下文对应mysql的同一连接，但其中遗漏了SELECT GET_LOCK。于是在migrate升级获取锁以及再释放时，通过db-proxy执行的连接可能不同，导致获取到了不该拿到的锁或者无法释放锁，而且app重启断开连接后，db-proxy也不会断开mysql的连接，而是放回了自己的连接池，使得下次也可能拿不到锁。 我们的场景可以通过mysql cli连接db-proxy复现，会有概率是如下结果：
I have also encountered a similar issue, but the root cause has nothing to do with the code of this project. It is actually due to the complexity of the production environment. Our app connects to MySQL through a db-proxy, which maintains a connection pool with MySQL. The MySQL connection obtained by the app is actually a connection of the db-proxy. In scenarios such as transaction initiation, the db-proxy ensures that the app's connection context corresponds to the same connection in MySQL. However, SELECT GET_LOCK was overlooked in this process. Therefore, when obtaining and releasing locks during migration upgrades, the connection executed by db-proxy may be different, causing the acquisition of improper locks or inability to release locks. Additionally, when the app restarts and disconnects the connection, db-proxy does not disconnect the MySQL connection but returns it to its own connection pool, making it possible to not acquire the lock next time.
Our scenario can be reproduced by connecting to db-proxy via mysql cli, and there is a chance that the result will be as follows:
> select get_lock('eg', 1);
+---------------------------+
| get_lock('eg', 1) |
+---------------------------+
| 1 |
+---------------------------+
> select release_lock('eg');
+----------------------------+
| release_lock('eg') |
+----------------------------+
| 0 |
+----------------------------+
