Mysql8 upgrade: one table has developed a problem with null constraints

Question

We carried out an upgrade of a production system recently, from mysql5.7 to mysql8.

This was extensively tested on CI, local environments, staging. All queries ran as expected. With the move to mysql8 on prod just one table has developed a bizarre problem. It can be quantified as:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'my_col' cannot be null' with query:
'UPDATE my_table SET my_col = '1' WHERE table_id='abc123' LIMIT 1;'

The column is a varchar with not null and no default. But as you can see from the query, we are not trying to set it as null. We're trying to set it as a string "1". This error does not happen every time the query is run; it seems that if the application runs it twice in two subsequent requests it will work properly the second time.

To make this even more fun, this issue only occurs when run via our web application (PHP using PDO) - running this in mysql-client CLI works fine. The query fails via the web application both using parameterised queries and also if the whole query including inputs (which is insecure yes) is supplied as a single string query.

Answer 1

Ok so this was fun. Turns out it's this bug from mysql 8 versions 8.0.27 and updwards (as of the time of this post there's no fix, and it's present in the current version 8.0.36). There's two code-level workarounds (see below)

https://bugs.mysql.com/bug.php?id=113464

Here's what explains the sporadic nature (i.e. a user tries again and it works) and why the bug didn't happen for us on testing or CLI.

• The bug occurs if a table has a trigger ON INSERT (before or after)
• However it only actually causes a problem on an UPDATE query to the same table
• And specifically the UPDATE must be run over the same connection to the database as the INSERT

Thus when we recreated any of this via CLI, that's a different connection as it's going via a bastion server. And when users hit submit repeatedly, the chances are that multiple requests land on different hosts, which have their own connection pools. It was sporadic (sometimes it took them 2 or 3 requests) because PDO caches connections within mod_apache, so multiple requests to one server may re-use the same connection.

Fixes (at present until mysql release a proper patch):

• Don't use an INSERT trigger (may not be feasible) if you intend to UPDATE the same row soon after (within the same request or using connection pools)
• Flush your table before making a subsequent UPDATE request:

try {
  $pdo->exec('FLUSH TABLES my_table');
}
catch (\Throwable $e){
  // log this in case it fails
}

// run your update here and the error won't happen

If the FLUSH command fails check that your database user has FLUSH or RELOAD privileges (alongside all other current privileges).