| Bug #107533 | sql-mode NO_AUTO_VALUE_ON_ZERO does not work with BEFORE INSERT Trigger | ||
|---|---|---|---|
| Submitted: | 9 Jun 2022 20:24 | Modified: | 16 Jun 2022 14:18 |
| Reporter: | C. Deniz Akyuz | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 8.0.28 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 Jun 2022 13:31]
MySQL Verification Team
Hi Mr. Akyuz, Thank you for your bug report. However, you have an error in your test case. The new value of `id` column in the UPDATE is not 0, but NULL, which is why the error is generated. Hence ,it is not a bug.
[10 Jun 2022 16:11]
C. Deniz Akyuz
I am sorry. I was probably not clear about the bug.
The bug is in the behavior of auto-increment after the value is changed in the INSERT TRIGGER (not in the update trigger - there it works as it should)
The bug is seen in the result of:
INSERT INTO TEST (name) VALUE('DEFAULT');
The wrong result is
4 default
The expected result is
0 default
In the INSERT TRIGGER he value of the id field is updated to 0 if NULL but the auto-increment overrides that (to 4 in this case).
[14 Jun 2022 12:07]
MySQL Verification Team
Hi Mr. Akyuz, Sorry, but this is not a bug. If you read our Reference Manual, you will notice that our auto-increment columns can not have a value of 0 (zero). Not a bug.
[14 Jun 2022 12:33]
C. Deniz Akyuz
Please read your own reference manual before making a comment on a bug submission. https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.
[14 Jun 2022 13:42]
MySQL Verification Team
Exactly ........ Only NULL will generate the next sequence number and not 0.
[14 Jun 2022 16:32]
C. Deniz Akyuz
Thank you for the reply.
If I am understanding you correctly: The AUTO_INCREMENT looks at the value of the id before it is set in the INSERT TRIGGER. Then if the incoming NULL id in the INSERT TRIGGER is set to another number, e.g. 7, it should ignore that, and AUTO_INCREMENT should use the next id, as well. But it does not as shown in the simpler example below.
One of the behaviors below must be a bug. If you ask me, I prefer that the incoming NULL id set to 0 in the TRIGGER is left as 0 - but I am OK with any consistent behavior.
-- -----
DROP TABLE IF EXISTS TEST;
-- -----
CREATE TABLE TEST
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, name VARCHAR(15) NOT NULL
, UNIQUE KEY name (name)
);
-- -----
DROP TRIGGER IF EXISTS TEST$BIT;
-- -----
delimiter ;;
CREATE TRIGGER TEST$BIT
BEFORE INSERT
ON TEST
FOR EACH ROW
BEGIN
SET NEW.name = LOWER(NEW.name);
IF NEW.name = 'default'
THEN
SET NEW.id := 0;
ELSEIF NEW.name = 'seven'
THEN
SET NEW.id := 7;
END IF;
END
;;
delimiter ;
-- -----
INSERT INTO TEST (id, name) VALUE(NULL, 'AAA');
INSERT INTO TEST (id, name) VALUE(NULL, 'BBB');
INSERT INTO TEST (id, name) VALUE(NULL, 'DEFAULT');
INSERT INTO TEST (id, name) VALUE(NULL, 'SEVEN');
SELECT * FROM TEST;
/**
1 aaa
2 bbb
3 default <- AUTO_INCREMENT changed the id set to 0 in the TRIGGER
7 seven <- AUTO_INCREMENT left the id = 7 as set in the TRIGGER
**/
/**
EXPECTED if AUTO_INCREMENT checks for NULL id before the BEFORE INSERT TRIGGER
(As I think you describe the spec)
1 aaa
2 bbb
3 default <- AUTO_INCREMENT changed the id set to 0 in the TRIGGER
4 seven <- AUTO_INCREMENT should have set the incoming NULL id to 4
**/
EXPECTED
/**
EXPECTED if AUTO_INCREMENT checks for NULL id after the BEFORE INSERT TRIGGER
(As I understand the spec)
1 aaa
2 bbb
0 default <- AUTO_INCREMENT should leave the id 0 as set in the TRIGGER
7 seven <- AUTO_INCREMENT should leave the id 7 as set in the TRIGGER
**/
[15 Jun 2022 11:52]
MySQL Verification Team
Hi Mr. Akyuz, Actually, the behaviour of the INSERT on the auto-increment key is totally independent whether you use it in trigger or in straight query. When 0 or NULL are specified, then the next generated auto-increment value will be inserted. If you specify a number that is not-existent in the table's column, that it will be inserted. That is all described in our Reference Manual. Hence, this is expected behaviour.
[15 Jun 2022 14:58]
C. Deniz Akyuz
When the sql_mode contains 'NO_AUTO_VALUE_ON_ZERO', the AUTO_INCREMENT should be triggered only when the id IS NULL, not when it is 0. https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html As you can see in the example below, in straight insert, the behavior is exactly as specified in the manual: With sql_mode 'NO_AUTO_VALUE_ON_ZERO' id=0 is not updated. However, the bug I am reporting is when the NULL id is changed to 0 in the BEFORE INSERT TRIGGER. In that case the AUTO_INCREMENT uses the next id value to overwrite of the value 0 assigned in the trigger. -- ----- DROP TABLE IF EXISTS TEST; -- ----- CREATE TABLE TEST ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY , name VARCHAR(15) NOT NULL , UNIQUE KEY name (name) ); -- ----- SET @@SESSION.sql_mode := CONCAT('NO_AUTO_VALUE_ON_ZERO,', @@SESSION.sql_mode); INSERT INTO TEST (id, name) VALUE(NULL, 'AAA'); INSERT INTO TEST (id, name) VALUE(NULL, 'BBB'); INSERT INTO TEST (id, name) VALUE(0, 'ZERO'); INSERT INTO TEST (id, name) VALUE(NULL, 'SEVEN'); SELECT * FROM TEST; /** AS EXPECTED ( Setting NO_AUTO_VALUE_ON_ZERO in sql_mode allows id = 0 1 AAA 2 BBB 3 SEVEN 0 ZERO <- id = 0 AUTO_INCREMENT did not update the id **/ TRUNCATE TABLE TEST; SET @@SESSION.sql_mode := REPLACE(@@SESSION.sql_mode, 'NO_AUTO_VALUE_ON_ZERO,', ''); INSERT INTO TEST (id, name) VALUE(NULL, 'AAA'); INSERT INTO TEST (id, name) VALUE(NULL, 'BBB'); INSERT INTO TEST (id, name) VALUE(0, 'ZERO'); INSERT INTO TEST (id, name) VALUE(NULL, 'SEVEN'); SELECT * FROM TEST; /** AS EXPECTED After removing 'NO_AUTO_VALUE_ON_ZERO' from sql-mode id inserted with value 0, gets the next auto-increment value 1 AAA 2 BBB 4 SEVEN 3 ZERO <- AUTO_INCREMENT updated the id with insert value 0 **/
[16 Jun 2022 12:30]
MySQL Verification Team
Hi Mr. Akyuz, We do understand what you are reporting. But, since 0 (zero) can not be inserted in the auto-increment column, then there is no other option left to the engine. Engine might issue an error and rollback the entire transaction and that is a change, so dramatic and unnecessary, that nobody wishes that you get a rollback of the entire transaction, due to the error of the programmer.
[16 Jun 2022 12:34]
C. Deniz Akyuz
I understand. Can you please explain what sql-mode NO_AUTO_VALUE_ON_ZERO does? Here is the reference for your convenience: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
[16 Jun 2022 12:41]
MySQL Verification Team
Hi Mr. Akyuz, Thank you for your question. However, this is a forum for reports on the real bugs. This is not a forum for discussing the contents of our Reference Manual.
[16 Jun 2022 13:14]
C. Deniz Akyuz
Excuse me. You have a bug in your system I am trying to explain the spec. I am not trying to discuss your user manual. You (erroneously) said: "0 (zero) can not be inserted in the auto-increment column" But you manual says that you can (please read it) using the sql-mode NO_AUTO_VALUE_ON_ZERO: NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
[16 Jun 2022 13:24]
MySQL Verification Team
Hi Mr. Akyuz, Let us quote from that paragraph: " NO_AUTO_VALUE_ON_ZERO suppresses this behaviour for 0 so that only NULL generates the next sequence number." In some previous versions, 0 could be inserted, but it lead to huge problems, as discussed further in the paragraph. That behaviour has been changed and is documented in 8.0 release notes. We shall write to the documentation team to make that small change in our Manual. This report is now closed.
[16 Jun 2022 13:28]
MySQL Verification Team
This is now a verified documentation bug.
[16 Jun 2022 14:18]
C. Deniz Akyuz
Thank you. But, if the documentation is wrong and NO_AUTO_VALUE_ON_ZERO is not supposed to work, then this following is a bug:
-- -----
DROP TABLE IF EXISTS TEST;
-- -----
CREATE TABLE TEST
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, name VARCHAR(15) NOT NULL
, UNIQUE KEY name (name)
);
-- -----
SET @@SESSION.sql_mode := CONCAT('NO_AUTO_VALUE_ON_ZERO,', @@SESSION.sql_mode);
INSERT INTO TEST (id, name) VALUE(NULL, 'AAA');
INSERT INTO TEST (id, name) VALUE(NULL, 'BBB');
INSERT INTO TEST (id, name) VALUE(0, 'ZERO');
INSERT INTO TEST (id, name) VALUE(NULL, 'SEVEN');
SELECT * FROM TEST;
/**
Setting NO_AUTO_VALUE_ON_ZERO in sql_mode allows id = 0
1 AAA
2 BBB
3 SEVEN
0 ZERO <- id = 0 AUTO_INCREMENT did not assign the next auto-id
**/
[17 Jun 2022 13:22]
MySQL Verification Team
Hi, This will be resolved in documentation. If anything else is required, a new, code bug will be created internally.
[18 Nov 2022 11:42]
Stefan Hinz
Posted by developer: Please point out what *exactly* needs to be changed in the MySQL 8.0 manual.
Description: When the sql-mode includes NO_AUTO_VALUE_ON_ZERO, the AUTO_INCREMENT should allow 0 values. However, it does not work when the id is set to 0 inside the BEFORE INSERT TRIGGER. How to repeat: -- ----- DROP TABLE IF EXISTS TEST; -- ----- CREATE TABLE TEST ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY , name VARCHAR(15) NOT NULL , UNIQUE KEY name (name) ); -- ----- DROP TRIGGER IF EXISTS TEST$BIT; -- ----- delimiter ;; CREATE TRIGGER TEST$BIT BEFORE INSERT ON TEST FOR EACH ROW BEGIN SET NEW.name = LOWER(NEW.name); IF NEW.name = 'default' THEN IF IFNULL(NEW.id, 0) <> 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'TEST$BIT: none-0 id for name=''default'''; END IF; SET NEW.id := 0; END IF; END ;; delimiter ; -- ----- DROP TRIGGER IF EXISTS TEST$BUT; -- ----- delimiter ;; CREATE TRIGGER TEST$BUT BEFORE UPDATE ON TEST FOR EACH ROW BEGIN SET NEW.name = LOWER(NEW.name); IF NEW.name = 'default' THEN IF IFNULL(NEW.id, 0) <> 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'TEST$BUT: none-0 id for name=''default'''; END IF; SET NEW.id := 0; END IF; END ;; delimiter ; -- ----- INSERT INTO TEST (name) VALUE('AAA'); INSERT INTO TEST (name) VALUE('BBB'); INSERT INTO TEST (name) VALUE('CCC'); INSERT INTO TEST (name) VALUE('DEFAULT'); SELECT * FROM TEST; /** 1 aaa 2 bbb 3 ccc 4 default **/ UPDATE TEST SET id = NULL WHERE name = 'default'; SELECT * FROM TEST; /** 1 aaa 2 bbb 3 ccc 0 default **/ INSERT INTO TEST (id, name) VALUE(4, 'DEFAULT'); -- 1644 - TEST$BIT: none-0 id for name='default' UPDATE TEST SET id = 4 WHERE name = 'default'; -- 1644 - TEST$BUT: none-0 id for name='default' SHOW VARIABLES LIKE '%version%'; /** ... immediate_server_version 999999 innodb_version 8.0.28 original_server_version 999999 version 8.0.28 version_comment MySQL Community Server - GPL version_compile_machine arm64 version_compile_os macos11 version_compile_zlib 1.2.11 **/ SHOW VARIABLES LIKE 'sql_mode'; -- NO_AUTO_VALUE_ON_ZERO /** REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,TIME_TRUNCATE_FRACTIONAL **/ Suggested fix: When the sql-mode includes NO_AUTO_VALUE_ON_ZERO, the AUTO_INCREMENT should allow 0 values even when set inside a BEFORE INSERT TRIGGER.