INSERT... NOT EXISTS works on MAMP, but doesn't work on real server

Question

I'm trying to migrate my site, which I was developing localy on MAMP server, to live server. All queries works on MAMP as expected. On live server I'm getting errors on queries which have "INPUT... NOT EXISTS".

MAMP server: PHP - 7.4.2 MySQL - 5.7.26 InnoDB

Live server: PHP - 7.4 MariaDB - 10.2 InnoDB

structure and primary keys are the same - checked all tables

$user_id, $id, $date, $summ - are variables, which I'm getting vis POST method.

Query:

if ($result = $link->prepare("INSERT INTO sb_user_checks (user_id, check_id, date_update, summ, updated_by) 
    SELECT ?, ?, ?, ?, 'You'
    WHERE NOT EXISTS (SELECT date_update FROM sb_user_checks WHERE user_id=? AND check_id=? AND date_update=?)
    ")) { 
        $result->bind_param('iisiiis', $user_id, $id, $date, $summ, $user_id, $id, $date);
        $result->execute();
        if (mysqli_affected_rows($link)>0) {echo 'added';}
        $result->close();
    }

I'm getting following error:

#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 date_update FROM sb_user_checks WHERE user_i' at line 3 Is it anything to do with MySQL version, or it's incorrect query? But, again, it works correctly on MAMP.

See https://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments — Barmar, Mar 11 '20 at 18:09
I'm not an expert in PHP but I think that you bind 'iisiiis' to the value that you want to insert to user_id. Is this correct? — forpas, Mar 11 '20 at 18:11
I think `SELECT` should have a `FROM TABLE`, which isn't quite what you want. I'd say the query would be probably `INSERT INTO table (columns) VALUES (placeholder and values) WHERE NOT EXISTS...` — aynber, Mar 11 '20 at 18:18
In MySQL, and I believe MariaDB, you can use `FROM DUAL` when a table is needed for syntactic reasons, but not actually relevant. "DUAL" acts as a single row, column-less table. — Uueerdo, Mar 11 '20 at 18:20
@Barmar, thanks. I think problem described there (link) is not the same. — Devy, Mar 11 '20 at 19:39

score 1 · Accepted Answer · answered Mar 11 '20 at 19:47

In MySQL, and I believe MariaDB, a query may make use of DUAL as a stand in for a table when the syntax requires it but the overall logic does not. For example, in this case:

SELECT ?, ?, ?, ?, 'You'
FROM DUAL
WHERE NOT EXISTS ...

DUAL acts similarly to a single row, no column table; and is really only meant as a stand in for scenarios like this. These factors have a number of implications:

It cannot be used with SELECT *
It cannot be aliased
It cannot be JOINed to.
more... those limitations are just ones I discovered from running quick tests manually as I wrote this answer.

It works like a charm! Thanks a lot! So happy that solution is so simple :) — Devy, Mar 11 '20 at 19:50

INSERT... NOT EXISTS works on MAMP, but doesn't work on real server

1 Answers1