MySQL Bugs: #108155: BEFORE INSERT trigger causes Error 1048 during UPDATE

Bug #108155	BEFORE INSERT trigger causes Error 1048 during UPDATE
Submitted:	16 Aug 2022 16:22	Modified:	12 Mar 11:04
Reporter:	Jason Brunette	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A BEFORE INSERT trigger can cause an UPDATE to throw error 1048 "Column ... cannot be null" under specific conditions:

- STRICT_TRANS_TABLES must NOT be in effect. 
- Table has NOT NULL columns.
- A BEFORE INSERT trigger must exist on the table... it can have code or it can be blank.
- INSERT sets one column's value but leaves another NOT NULL field uninitialized, triggering warning error 1364 "Field ... doesn't have a default value".
- UPDATE attempts to set the other NOT NULL field's value, triggering Error 1048.
- INSERT and UPDATE must occur on the same connection. If you do the INSERT on one connection and the UPDATE on another, the error does not occur.

Workarounds I've found:
- Include all NON NULL fields in INSERT statement
- Do the INSERT with one connection, and UPDATE on another
- Include a BEFORE UPDATE trigger... it can have code or it can be blank.

How to repeat:
ERROR TEST CASE:

DROP TABLE IF EXISTS error_1048_test;
CREATE TABLE `error_1048_test` (
  `c1` char(1) NOT NULL,
  `c2` char(1) NOT NULL
) ENGINE=InnoDB;
DROP TRIGGER IF EXISTS `error_1048_test_BEFORE_INSERT`;
CREATE TRIGGER `error_1048_test_BEFORE_INSERT` BEFORE INSERT ON `error_1048_test` FOR EACH ROW begin
end;
INSERT INTO error_1048_test(c1) VALUES ("a");
UPDATE error_1048_test SET c2 = 'b';

WORKAROUND TEST CASE #1 (include all fields in INSERT statement):

DROP TABLE IF EXISTS error_1048_test;
CREATE TABLE `error_1048_test` (
  `c1` char(1) NOT NULL,
  `c2` char(1) NOT NULL
) ENGINE=InnoDB;
DROP TRIGGER IF EXISTS `error_1048_test_BEFORE_INSERT`;
CREATE TRIGGER `error_1048_test_BEFORE_INSERT` BEFORE INSERT ON `error_1048_test` FOR EACH ROW begin
end;
INSERT INTO error_1048_test(c1, c2) VALUES ("a", "b");
UPDATE error_1048_test SET c2 = 'b';

WORKAROUND TEST CASE #2 (include a BEFORE UPDATE trigger):

DROP TABLE IF EXISTS error_1048_test;
CREATE TABLE `error_1048_test` (
  `c1` char(1) NOT NULL,
  `c2` char(1) NOT NULL
) ENGINE=InnoDB;
DROP TRIGGER IF EXISTS `error_1048_test_BEFORE_INSERT`;
CREATE TRIGGER `error_1048_test_BEFORE_INSERT` BEFORE INSERT ON `error_1048_test` FOR EACH ROW begin
end;
DROP TRIGGER IF EXISTS `error_1048_test_BEFORE_UPDATE`;
CREATE TRIGGER `error_1048_test_BEFORE_UPDATE` BEFORE UPDATE ON `error_1048_test` FOR EACH ROW begin
end;
INSERT INTO error_1048_test(c1) VALUES ("a");
UPDATE error_1048_test SET c2 = 'b';

Hi Mr. Brunette,

Thank you for your bug report.

However, we can not repeat the behaviour that you report with 8.0.30.

Your triggers are empty and do nothing. The only correct behaviour is that INSERT command MUST (for the kind of table that you have created) contain values that do nothing.

In our testing, we get errors whenever there is only one column in your INSERT, regardless of which triggers are present and which are not present.

Hence, even in your last example, when both triggers are present, we do get the error, which is correct behaviour.

Can't repeat.

"In our testing, we get errors whenever there is only one column in your INSERT, regardless of which triggers are present and which are not present."

That's why I mentioned that strict mode must be turned off. Run this before running the error case:

SET SESSION sql_mode = ''; 

With that turned off, the behavior is as described.

Hi Mr. Brunette,

We got the same behaviour with SQL_MODE annulled as with full SQL_MODE.

Not a bug.

So just to be absolutely clear, when you run the following, you're saying that you are getting a HARD ERROR (NOT a warning) during the INSERT statement, correct? Can you double check? Turning off strict mode would not make that a hard error.

SET SESSION sql_mode = ''; 
DROP TABLE IF EXISTS error_1048_test;
CREATE TABLE `error_1048_test` (
  `c1` char(1) NOT NULL,
  `c2` char(1) NOT NULL
) ENGINE=InnoDB;
DROP TRIGGER IF EXISTS `error_1048_test_BEFORE_INSERT`;
CREATE TRIGGER `error_1048_test_BEFORE_INSERT` BEFORE INSERT ON `error_1048_test` FOR EACH ROW begin
end;
INSERT INTO error_1048_test(c1) VALUES ("a");
UPDATE error_1048_test SET c2 = 'b';

Hi,

I have added the necessary change to the SQL_MODE at the very start of the script.

Simply, regardless of the presence of triggers or not, DML that adds both columns passes without errors and warnings, while in every other case, the error is returned.

Hi,
Just ran into this issue, thanks Jason for including the workarounds!

It's surprising that this is marked as "can't repeat". I would like to elaborate, hoping that helps in repeating this.

On 8.0.26 and earlier, this succeeds:

---
mysql> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS error_1048_test;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `error_1048_test` (
    ->   `c1` char(1) NOT NULL,
    ->   `c2` char(1) NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TRIGGER IF EXISTS `error_1048_test_BEFORE_INSERT`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TRIGGER `error_1048_test_BEFORE_INSERT` BEFORE INSERT ON `error_1048_test` FOR EACH ROW begin
    -> end;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO error_1048_test(c1) VALUES ("a");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> UPDATE error_1048_test SET c2 = 'b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
---

Starting with 8.0.27, and up to 8.0.33 (latest when writing this) the last UPDATE fails with:

---
mysql> UPDATE error_1048_test SET c2 = 'b';
ERROR 1048 (23000): Column 'c2' cannot be null
---

"c2 cannot be null", well, nobody wants it to be NULL. That error just can't be right.

As Jason mentions, adding a no-op BEFORE UPDATE trigger makes the UPDATE work again. 
This goes thru on 8.0.27 and later with no errors:

---
SET SESSION sql_mode='';
DROP TABLE IF EXISTS error_1048_test;
CREATE TABLE `error_1048_test` (
  `c1` char(1) NOT NULL,
  `c2` char(1) NOT NULL
) ENGINE=InnoDB;
DROP TRIGGER IF EXISTS `error_1048_test_BEFORE_INSERT`;
CREATE TRIGGER `error_1048_test_BEFORE_INSERT` BEFORE INSERT ON `error_1048_test` FOR EACH ROW begin
end;
CREATE TRIGGER `error_1048_test_BEFORE_UPDATE` BEFORE UPDATE ON `error_1048_test` FOR EACH ROW begin
end;
INSERT INTO error_1048_test(c1) VALUES ("a");
UPDATE error_1048_test SET c2 = 'b';
---

Hi Mr. Brunette.

There are sql_modes that are deprecated and there are some that were made mandatory since 8.0.27.

By setting sql_mode to '', you are removing mandatory SQL modes. 

In this case, you removed a mode that requires that all columns have some value, whether by default or explicitly in your statement.

Hence, your error is in the INSERT, which surfaces in the UPDATE.

Please, read our entire Reference Manual, very carefully.

Not a bug.

I am experiencing this issue as well.  I did not find any reference to "mandatory SQL modes" in the documentation.  My understanding is that "Implicit Default Handling" would set the default values in the case that there was no default value and STRICT_TRANS_TABLES was not in effect.

Hi,

Your conclusions are wrong.

First of all, you should search for the page about sql_mode, not the one you quoted.

Next, STRICT_TRANS_TABLES has nothing to do with it and implicit defaults are not available for all types and change from version to version.

Not a bug.

I guess I'll keep fighting for this, since there is absolutely a bug somewhere.

Regarding your comment on 18 Aug 2022, "We got the same behaviour with SQL_MODE annulled as with full SQL_MODE." ... that cannot be right. The lack of STRICT_TRANS_TABLES should make the INSERT warn, not error.

Can you please paste here the results of the following statements?

SET SESSION sql_mode = '';
DROP TABLE IF EXISTS error_1048_test;
CREATE TABLE `error_1048_test` (
  `c1` char(1) NOT NULL,
  `c2` char(1) NOT NULL
) ENGINE=InnoDB;
INSERT INTO error_1048_test(c1) VALUES ("a");
UPDATE error_1048_test SET c2 = 'b';

... and then do the same for the following, which simply adds the blank BEFORE INSERT trigger:

SET SESSION sql_mode = '';
DROP TABLE IF EXISTS error_1048_test;
CREATE TABLE `error_1048_test` (
  `c1` char(1) NOT NULL,
  `c2` char(1) NOT NULL
) ENGINE=InnoDB;
DROP TRIGGER IF EXISTS `error_1048_test_BEFORE_INSERT`;
CREATE TRIGGER `error_1048_test_BEFORE_INSERT` BEFORE INSERT ON `error_1048_test` FOR EACH ROW begin
end;
INSERT INTO error_1048_test(c1) VALUES ("a");
UPDATE error_1048_test SET c2 = 'b';

Please humor me. Thanks.

Hi Verification Team,

Regarding "There are sql_modes that are deprecated and there are some that were made mandatory since 8.0.27." and "First of all, you should search for the page about sql_mode, not the one you quoted."

I have reread the sql-mode page and see that the following is said: "The default SQL mode in MySQL 8.0 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION."  

While these are the default modes, nothing indicates that these are the required so I assume no modes is a valid state.

With my instance of this issue, I am using an AWS RDS database and the mode was set to NO_ENGINE_SUBSTITUTION.

Hi All,

Both of the above test cases returned no errors at all. Everything worked smoothly and fine.

We even added SHOW WARNINGS in several places and got no warnings at all.

We must, however, point out that we did our tests on MySQL 8.0.35 release binary.

We are only supporting our own binaries and not any of the clone's products.

Can't repeat.

Thanks for testing again. I'm hoping you could take a look at the following short Youtube video I made. It shows me installing a vanilla, release copy of MySQL Community Server 8.0.35, initializing a brand new database, connecting with the mysql command line utility and running the statements that trigger the error. Reminder, the test sets sql_mode to '', turning STRICT_TRANS_TABLES off, so no errors should occur.

Could you tell me please what I'm doing differently than what you're doing please?

https://www.youtube.com/watch?v=IBe0-IOF0WE

Here is a follow-up, even shorter video demonstrating that the connection used to do the test is somehow corrupted as reconnecting, clearing sql_mode again and running the exact same UPDATE statement suddenly works. Why would it fail on the first connection and not the second, when both connections clear sql_mode?

https://www.youtube.com/watch?v=7SjArIu6T30

Hi,

We truly can not say exactly why.

You could have some problem with your OS, including TCP/IP setup , tuning and similar ...... It is possible that your permission on the socket file and the directories above it are not well set.

It is also possible that you have some random RAM or disk problems. Most of these problems are transient and cannot be caught by hardware checking software. We use highly reliable hardware, with ECC RAM parity checks. We also use quality HDD, cached by quality SSD.

It is also problems that you have some MySQL warnings that you did not print or some messages in the MySQL and system error logs that you have not reviewed.

We ran your tests many times and had no problems with a single test. Without a single MySQL warning, without a single line in the (verbosity=3) error log, nor any error in the OS error logs.

This error occurs on multiple platforms. I first encountered it on RHEL 8, then performed the test on Windows 11 as an administrator. Multiple users here have indicated they can reproduce it. Are you saying we all have the exact same, unexplainable hardware/software issues that somehow only affect this impossibly specific case?

My RHEL 8 server is an Azure virtual machine that is rebuilt completely from scratch every few months. Should I notify Microsoft that their Azure platform is defective?

This was my last shot. Sorry folks.

The idea that a vendor would dismiss an inconsistent out-of-the-box experience in their database product using a tiny test case is... it's something else.

The idea that a vendor would dismiss an inconsistent out-of-the-box experience in their database product using a tiny test case is... it's something else.

Hi,

This is a forum for the reports with fully reproducible test cases.

We have tested several times all of the test cases that you have sent us and we did not get a single error nor a single warning.

If we can not repeat it, then we can not fix it.

I believe Bug 113464 which has been verified is a duplicate of this issue.

Hi,

Yes, it could be a duplicate.

However, that test case we managed to repeat, while this test case worked without problems.

That means that some of the settings in this report were not provided.

Hence, you can follow the destiny of the other, mentioned bug report.

Really interesting that the code provided to recreate this bug (108155) is the same (except for table/column names) as the code in https://bugs.mysql.com/bug.php?id=113464 but this is marked as not a bug and that one is marked severe.

We've encountered this today and can confirm running "FLUSH TABLES my_table" where my_table is the affected table will allow the subsequent update to succeed whereas otherwise it would have failed.

I also linked here on a related StackOverflow issue, as originally we were suspecting it was a bug in PHP's PDO library, so it's worth the maintainers of that knowing to direct here in the event people report to them.

Hi,

You are quite correct.

This bug report is a duplicate of the following bug:

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

Hence, follow it's destiny and then you will know when is it fixed.

We have also left a remark in that bug report.