Search code examples
mysqlgoogle-cloud-platformgoogle-cloud-sql

Why would Mysql return "error Column cannot be null"?

We recently moved our mysql version to mysql 8 but we are facing a really strange behavior some (valid) queries that would work before like a charm stopped working (sometimes) for no reason and mysql return an error but if we re-run it sometimes it works.

Here's an example :

UPDATE `Members` set `ProZipCode` = 75001, `ProCity` = 'Paris', `ActivationCode` = 'AAA-15372695', `State` = 1 WHERE `MemberId` = 42;

If i run this query 10 times in a row it will work like 6 times but 4 times it would throw an error (randomly) both in Laravel or in phpmyadmin :

Integrity constraint violation : Column 'ActivationCode' cannot be null

We are using mysql version 8.0.36

Edit with some asked clarification :

  • The mysql version is 8.0.36
  • The field 'ActivationCode' is a classic varchar(255) NOT NULL column
  • There is no foreign key or so in the table, there is only one before insert trigger to generete a MemberHash, no event, lock or whatever else
  • I'm running the exact same query manually 10 times in a row on phpmyadmin or via an API call (with the same data)
  • The ActivationCode is not auto-generated, i'm updating it in the query
  • The database is hosted on Google Cloud SQL there are 3 flags : log_bin_trust_function_creators (on) , sql_mode (NO_ENGINE_SUBSTITUTION, ALLOW_INVALID_DATES), explicit_default_for_timestamp(off)
  • Other servers are on cloud run
  • Of course I could just change the column to accept null value but that's not possible since this error occurs on multiple column/table
Solution

  • It seems downgrading to the latest mysql 5.7 version fixed our problem. We tried upgrading to the latest mysql 8 version but it didn't worked.

    A really strange behaviour ...