Syntax error in your SQL generated by knex - Insert into migration_lock

[helderam]

Environment

Knex CLI version: 0.95.13
Knex Local version: 0.95.14

Database + version:
Server version: 10.3.28-MariaDB-1:10.3.28+maria~focal mariadb.org binary distribution

OS:
KDE neon 5.23 (ubuntu)

Select applicable template from below.

Bug

Error message when command:
$ knex migrate:up

insert into migrations_lock (is_locked) select 0 where not exists (select * from migrations_lock) - 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 migrations_lock)' at line 1

1. Explain what kind of behaviour you are getting and how you think it should do

The migration is not working because the first thing it tries to do is lock on the migratiosn_lock table, however, it is generating invalid SQL for MariaDB.

2. Error message

insert into migrations_lock (is_locked) select 0 where not exists (select * from migrations_lock) - 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 migrations_lock)' at line 1

3. Reduced test code, for example in https://npm.runkit.com/knex or if it needs real
   database connection to MySQL or PostgreSQL, then single file example which initializes
   needed data and demonstrates the problem.

Simple knexfile.js
module.exports ={
client: 'mysql2',
connection: {
host: process.env.127.0.0.1,
user: process.env.teste,
password: process.env.teste_pwd,
database: process.env.teste,
port: process.env.3306,
},
migrations: {
directory: './db/migrations',
tableName: 'migrations'
},
}

Feature discussion / request

I using the mysql2 client

1. Explain what is your use case

Create a new directory:
npm i knex --save
npm i mysql2 --save
sudo npm i knex -g

knex migrate:make teste
knex migrate:up

2. Explain what kind of feature would support this

Then I understood that putting a "from dual" solves the problem

insert into migrations_lock (is_locked) select 0 from dual where not exists (select * from migrations_lock)

3. Give some API proposal, how the feature should work

Missing / erroneus documentation

Send issue to documentation repo, or fix it and send PR https://github.com/knex/documentation

Questions about how to use knex

GitHub issues are for knex development. Please send questions how to use knex to
Stack Overflow or ask about it in gitter chat.

[OlivierCavadenti]

It's the same problem here #4835 and here #4844
I will take the point now.

[kibertoad]

Released in 0.95.15