Command: apply_migration, originalError: Error 1845: LOCK=NONE is not supported for this operation. Try LOCK=SHARED

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

I’ve got this error during app schema migration:

{"timestamp":"2023-01-11 11:53:09.043 Z","level":"fatal","msg":"Failed to apply database migrations.","caller":"sqlstore/store.go:169","error":"driver: mysql, message: failed when applying migration, command: apply_migration, originalError: Error 1845: LOCK=NONE is not supported for this operation. Try LOCK=SHARED., query: \n\nSET @preparedStatement = (SELECT IF(\n (\n SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS\n WHERE table_name = 'Posts'\n AND table_schema = DATABASE()\n AND index_name = 'idx_posts_create_at_id'\n ) > 0,\n 'SELECT 1;',\n 'CREATE INDEX idx_posts_create_at_id on Posts(CreateAt, Id) LOCK=NONE;'\n));\n\nPREPARE createIndexIfNotExists FROM @preparedStatement;\nEXECUTE createIndexIfNotExists;\nDEALLOCATE PREPARE createIndexIfNotExists;\n\n"}

Any options do I have? Any workaround to pass this with extra manual steps and then use app as usual? This is needed only for schema migration.

Thanks

1 Like
2

Here you could either altogether remove the “LOCK=NONE” option from the DDL OR, as recommended in the error message, use the “LOCK=SHARED” with the DDL to let it complete.

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-index-operations

2 Likes
3

I confirm, it worked. Thank you.

1 Like