MariaDB having syntax in a new MariaDB install

Question

I have developed some Python scripts that inserts data into a MariaDB database using PyMySQL library.

The scripts was working well until yesterday. Yesterday the HDD of the server died. I just installed 3 disks (1 for boot and SWAP, the other two are in a raid 1 with EXT4 and / mounting point). When the main packages are ready, I found the scripts aren't working well due to the next query

INSERT
INTO
    devices(
        source_id,
        serialnumber,
        NAME,
        location_desc
    )
SELECT
    2,
    '5996B',
    'Barbate',
    'CADIZ'
WHERE NOT EXISTS
    (
    SELECT
        1
    FROM
        devices
    WHERE
        serialnumber = '5996B'
)

This query insert a device into devices table if the device not exists. The scripts work well in my local MariaDB server, but not in the production one, giving the next error:

Error

SQL query: Documentation

INSERT
INTO
    devices(
        source_id,
        serialnumber,
        NAME,
        location_desc
    )
SELECT
    2,
    '5996B',
    'Barbate',
    'CADIZ'
WHERE NOT EXISTS
    (
    SELECT
        1
    FROM
        devices
    WHERE
        serialnumber = '5996B'
)

MariaDB said: Documentation
#1064 - 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
        1
    FROM
        devices
    W' at line 14

I think that is a compatibility issue with a newer version of MariaDB. How can I fix it without rewriting my Python scripts?

EDIT:

Results for: SELECT VERSION();

Response in local server (where the SQL statement works): 10.4.6-MariaDB

Response in production server (where the SQL statement doesn't work): 10.3.17-MariaDB-0+deb10u1

Answer 1

I found a solution:

FROM DUAL is required in some mysql-server version, so to get it working the query needs to be changed to:

INSERT
INTO
    devices(
        source_id,
        serialnumber,
        NAME,
        location_desc
    )
SELECT
    2,
    '5996B',
    'Barbate',
    'CADIZ'
FROM DUAL
WHERE NOT EXISTS
    (
    SELECT
        1
    FROM
        devices
    WHERE
        serialnumber = '5996B'
)

I hope somebody find this useful