MySQL Bugs: #110348: AFTER INSERT trigger that updates a second table, causes an odd error

Bug #110348	AFTER INSERT trigger that updates a second table, causes an odd error
Submitted:	13 Mar 2023 4:29	Modified:	16 Mar 2023 21:18
Reporter:	Brett Gardner	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.31	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If I have a table A, this is updated in an AFTER UPDATE trigger on table B, I get an error that a column cannot in A cannot be null.

However, the column is not set to NULL

This error disappears if table A has a "BEFORE UPDATE" trigger

Using GDB, I think I've tracked this down to the code falling into the else case here 

https://github.com/mysql/mysql-server/blob/8.0/sql/sql_base.cc#L9910

How to repeat:
I will attach a recreate case once the bug report has been created

Hi Mr. Gardner,

Thank you for your bug report.

However, we need a fully repeatable test case.

Hence, we need all data for both tables.

You can send us only the data that are relevant. For example, only those data for the table B that are actually UPDATEd with the UPDATE in question.

Next, please reduce your test case only to two triggers. The one BEFORE UPDATE and the one AFTER UPDATE. Also, we need the exact update on table B that causes the error. We suppose that with the same update, BEFORE UPDATE does not cause the error.

We can not repeat the behaviour without all of the above issues.

Can't repeat.

Apologies, it is actually the AFTER INSERT that exhibits the behaviour. My test case includes those insert statements that trigger the problem.

I've simplified the test recreate case to bring the number of triggers down to those required. Simply running the test case, eg

cat recreate-asset.sql | mysql recreate_bug

will trigger the error

To summarise the problem

Table A, has a BEFORE INSERT trigger, but cannot have a BEFORE UPDATE trigger
Table B has an AFTER INSERT trigger, that updates the record in table A

From my new modified test case, if you add in the BEFORE UPDATE trigger for table A (uncomment lines 79-84), the bug no longer occurs

Hi Mr. Gardner,

We did not manage to repeat what you have reported.

I think that error messages that we have got are much more verbose then those that you reported.

That is because we have removed the BEGIN; statement.

The first INSERT fails with the error message:

ERROR 1364 (HY000) at line 89: Field 'VersionID' doesn't have a default value

Second INSERT fails with the other error message:

ERROR 1452 (23000) at line 92: Cannot add or update a child row: a foreign key constraint fails (`test1`.`asset_version`, CONSTRAINT `asset_version_ibfk_1` FOREIGN KEY (`id`) REFERENCES `asset` (`id`) ON DELETE CASCADE)

But, in the end it does not matter.

See, you have started a transaction, you have not committed any of the INSERTs, so the  NEW.VersionID is unknown for the AFTER trigger.

Hence, this is not a bug.

It's definitely a bug and worse than that, it is a regression from mysql 5.6.

However, running it against the mysql docker container 8.0.31 I get your error message about "VersionID does not have a default value" and when I correct that, I do not get my original error.

There must be further configuration of mysql to trigger the bug. I will continue investigation as to get an accurate recreate case.

So I've found how to recreate it with the recreate-asset-simplified.sql file

The sql_mode must be set to NO_ENGINE_SUBSTITUTION only, this must be set for the SESSION

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

So I think what is happening here is that the first insert to asset succeeds, but when the record is attempted to be updated in the same transaction via the "AFTER INSERT" trigger on asset_version, it doesn't like the fact that the VersionID column is NULL in the asset record

Now, while this could be considered "correct" behaviour:

1. This is a regression in behaviour from mysql 5.6
2. Adding a BEFORE UPDATE trigger on asset, which does not have to set the VersionID column, means the error is not returned.

Hi Mr. Gardner,

Your last two messages are very unclear to us.

First of all, 5.6 is not maintained for several years now, so no compatibility with 5.6 is maintained any longer. Hence, regression is irrelevant.

Actually, if you read our Reference Manual, you will find out a list of incompatibilities between 5.7 and 8.0, although 5.7 is still maintained (for hard bugs only). These incompatibilities are intentional and are here to stay.

Second, regarding engine substitution, which storage engine is substituted with which ????

Third, in 5.7 and 8.0, uncommitted transactions only take locks and do not store any values in the table itself. New row(s) are added only during the commit phase.

Hence, this is not a bug.

Sorry for repeated comments.

It was a bug in our forum software .........

Ignore the Mysql 5.6 part, I understand 5.6 is not maintained anymore.

It still seems like a bug to me, albeit a minor one.

sql_mode = NO_ENGINE_SUBSTITUION - ONLY, ie STRICT_TRANS_TABLES is NOT in effect
BEGIN
INSERT INTO asset ... - leaves the VersionID column NULL, although it is a NOT NULL column
INSERT INTO asset_version - AFTER INSERT trigger attempts to set the VersionID column of asset, ie would correct the invalid NOT NULL column but gets blocked by the "VersionID cannot be NULL" error

Now the above seems fine to leave as the behaviour, although it is confusing ultimately the first insert into ASSET is the problem.

The remaining, very minor, bug to me, is that if there is a BEFORE UPDATE trigger on asset, which does not modify the VersionID then the "VersionID cannot be NULL" error is not returned, and the AFTER UPDATE trigger for asset_version completes successfully.

Hi,

We ran a script with that trigger commented out ......

We commented out this trigger:

CREATE TRIGGER `befupd_asset` BEFORE UPDATE ON `asset` FOR EACH ROW BEGIN
        IF (@ignoreupdate IS NULL) THEN
                SET NEW.LastModDate = @current_date, NEW.LastModNode = @current_node,
                NEW.LastModContactID = COALESCE(@current_contactid,OLD.LastModContactID), NEW.LastModSeqNumber = (@seqnum:=@seqnum+1);
        END IF;
END//

Now ......

Without BEGIN, we get:

ERROR 1364 (HY000) at line 89: Field 'VersionID' doesn't have a default value
ERROR 1452 (23000) at line 92: Cannot add or update a child row: a foreign key constraint fails (`test1`.`asset_version`, CONSTRAINT `asset_version_ibfk_1` FOREIGN KEY (`id`) REFERENCES `asset` (`id`) ON DELETE CASCADE

With BEGIN, we get:

ERROR 1364 (HY000) at line 89: Field 'VersionID' doesn't have a default value
ERROR 1452 (23000) at line 92: Cannot add or update a child row: a foreign key constraint fails (`test1`.`asset_version`, CONSTRAINT `asset_version_ibfk_1` FOREIGN KEY (`id`) REFERENCES `asset` (`id`) ON DELETE CASCADE)

Not a bug.

Recreate case including changing the sql_mode

Attachment: simple-recreate.sql (application/sql, text), 1.44 KiB.

Your recreate did not change the sql_mode. The bug only occurs if you do not have STRICT_TRANS_TABLES enabled.

See the updated simple-recreate.sql case

The reason I still think this is a bug, is that a statement which is setting VersionID = <not null> returns an error messaqe "Column 'VersionID' cannot be null" which leads to confusion because that statement is not trying to set it to null.

Still the same result.

We recommend the default sql_mode, which DOES include STRICT_TRANS_TABLES in 8.0.