search

sunmingtao / sample-code

3 stars 4 forks source link

FlywayException: Unable to lock table `amberdb`.`schema_version` #329

Closed sunmingtao closed 2 years ago

sunmingtao commented 2 years ago

Error happens due to a failed flyway migration

18:20:50.573 java[22165]: Caused by: org.flywaydb.core.api.FlywayException: Unable to lock table `amberdb`.`schema_version`
18:20:50.573 java[22165]:       at org.flywaydb.core.internal.dbsupport.Table.lock(Table.java:181)
18:20:50.573 java[22165]:       at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:112)
18:20:50.573 java[22165]:       at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:175)
18:20:50.573 java[22165]:       at org.flywaydb.core.internal.command.DbMigrate$2.doInTransaction(DbMigrate.java:173)
18:20:50.573 java[22165]:       at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
18:20:50.573 java[22165]:       at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:173)
18:20:50.573 java[22165]:       at org.flywaydb.core.Flyway$1.execute(Flyway.java:959)
18:20:50.573 java[22165]:       at org.flywaydb.core.Flyway$1.execute(Flyway.java:917)
18:20:50.573 java[22165]:       at org.flywaydb.core.Flyway.execute(Flyway.java:1373)
18:20:50.573 java[22165]:       at org.flywaydb.core.Flyway.migrate(Flyway.java:917)
18:20:50.573 java[22165]:       at amberdb.AmberDb.<init>(AmberDb.java:36)
18:20:50.573 java[22165]:       at amberdb.AmberDb.<init>(AmberDb.java:40)
18:20:50.573 java[22165]:       at au.gov.nla.banjo.services.AmberDbService.get(AmberDbService.java:64)
18:20:50.573 java[22165]:       at au.gov.nla.banjo.services.AmberDbService.get(AmberDbService.java:52)
18:20:50.573 java[22165]:       at au.gov.nla.banjo.job.jobs.cron.MarcJob.lambda$doExecute$0(MarcJob.java:99)
18:20:50.573 java[22165]:       at org.jdbi.v3.sqlobject.WithHandleMethodHandlerFactory.lambda$null$0(WithHandleMethodHan
sunmingtao commented 2 years ago

Confirm the schema_version is indeed locked

SHOW OPEN TABLES WHERE `Table` LIKE '%schema_version%' AND `Database` LIKE 'amberdb' AND In_use > 0;

Find the SQL that locks it

show processlist;

Kill the process that locks the table. (Look for the DDLs in your script SQL)

kill 24964831;

Run the first statement again to make sure the schema_version is no longer locked