Unable to run migrations: table knex_migrations_lock is not created

[vdechef]

Environment

• Knex 0.95.14
• MariaDB 10.3.31 (mysql Ver 15.1 Distrib 10.3.31-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2)
• Debian 10
• Node 14.16.1

How to reproduce

I created a minimal project to cause the error: https://github.com/vdechef/knexexample
Just clone, install and run knex migration:latest

Bug

When running knex migrations, I get the following error:

$ npx knex migrate:latest

Using environment: development
insert into `knex_migrations_lock` (`is_locked`) select 0 where not exists (select * from `knex_migrations_lock`) - ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where not exists (select * from `knex_migrations_lock`)' at line 1
Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where not exists (select * from `knex_migrations_lock`)' at line 1
    at Query.Sequence._packetToError (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:315:20)
    at addChunk (internal/streams/readable.js:309:12)
    --------------------
    at Protocol._enqueue (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/mysql/lib/Connection.js:198:25)
    at /home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/knex/lib/dialects/mysql/index.js:132:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/knex/lib/dialects/mysql/index.js:126:12)
    at executeQuery (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
    at Client_MySQL.query (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/knex/lib/client.js:144:12)
    at Runner.query (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/knex/lib/execution/runner.js:130:36)
    at ensureConnectionCallback (/home/vincent/de v/sandbox/knexmigrate/knexexample/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)
    at Runner.ensureConnection (/home/vincent/dev/sandbox/knexmigrate/knexexample/node_modules/knex/lib/execution/runner.js:272:20)

This error is due to the table knex_migrations_lock not being created when the migrations are done. It is present since version 0.95.12 and was added by the commit #4694. When I revert this commit, the error is not thrown anymore.

I will have to stick to version 0.95.11 for now, but it won't be a long term solution and I could not find a workaround.

[OlivierCavadenti]

Resolved by #4865

[kibertoad]

Released in 0.95.15