A couple of times recently I’ve seen a mysterious error while developing PHP applications using MariaDB. The error logs show something like the following:
Unable to execute INSERT INTO user ( email, password ) VALUES ( :email, :password ). Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'email' in 'where clause'.
This error commonly occurs when writing a query that references a column that isn’t in any of the tables in the query. In that case the problem is easy to diagnose and fix. However, you may notice that the example above doesn’t have a
WHERE clause. The same error message also occurs when there is an incorrectly written
TRIGGER. Since triggers aren’t visible by default when looking at a table in phpMyAdmin, and also aren’t mentioned by the error message, it’s much harder to work out the underlying cause of the problem. If you find a query is failing due to problems with a 'where clause', but you haven’t included a
WHERE in the query, a
TRIGGER is the likely culprit. In this instance, the table had a trigger defined for
AFTER INSERT, but it referenced
Note to self: even though a trigger for
INSERT can only reference newly inserted values (as opposed to the old values that are available in triggers related to
DELETE), you still need to explicitly include
NEW when referencing the data!