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 email when it should have referenced NEW.email.
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 UPDATE or DELETE), you still need to explicitly include NEW when referencing the data!