Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.11.11
-
None
-
Debian 12
-
Not for Release Notes
Description
We have a Table with a Trigger before Insert:
CREATE TABLE `Z` (
|
`AUTOID` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
`ID` int(11) NOT NULL,
|
`EXPRESSION` text NOT NULL,
|
`COMMENT` text DEFAULT NULL,
|
`INSERT_TIME` datetime DEFAULT NULL,
|
`UPDATE_TIME` datetime DEFAULT NULL,
|
`USER_CHANGED_ROW` varchar(254) DEFAULT NULL,
|
PRIMARY KEY (`AUTOID`),
|
UNIQUE KEY `ID` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
CREATE TRIGGER TRG_Z_INSERT_STANDARD BEFORE INSERT ON Z FOR EACH ROW
|
BEGIN
|
END;;
|
The trigger fills INSERT_TIME, but we removed this for testing and used an empty trigger.
We copy entries from an other Table with the following command:
insert ignore into Z (`ID`)
|
select `ID` from CONF_ELEMENTS LIMIT 3;
|
We removed every thing for testing and only copy the id. Both ID fields are int(11).
The three entries have ids 1,2,3.
Only the first entry gets imported. The other fail, because the ID gets lost and converted from NULL to 0. This violates the unique constrain.
If we "log" the NEW.ID value inside of the trigger we get
NEW.ID: 1
NEW.ID: NULL
NEW.ID: NULL
If I execute the select we get 1,2,3 as expected.
I can reproduce this reliable if we need to test something.
It could be reproduced executing the following statements:
CREATE DATABASE test;
|
use test;
|
CREATE TABLE `Z` (
|
`AUTOID` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
`ID` int(11) NOT NULL,
|
`COMMENT` text DEFAULT NULL,
|
`INSERT_TIME` datetime DEFAULT NULL,
|
`UPDATE_TIME` datetime DEFAULT NULL,
|
`USER_CHANGED_ROW` varchar(254) DEFAULT NULL,
|
PRIMARY KEY (`AUTOID`),
|
UNIQUE KEY `ID` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
CREATE TABLE `A` (
|
`AUTOID` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
`ID` int(11) NOT NULL,
|
PRIMARY KEY (`AUTOID`),
|
UNIQUE KEY `ID` (`ID`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
insert into A (ID) values (1);
|
insert into A (ID) values (2);
|
insert into A (ID) values (3);
|
|
|
CREATE TRIGGER TRG_Z_INSERT_STANDARD
|
BEFORE INSERT ON Z
|
FOR EACH ROW
|
BEGIN
|
END;
|
|
|
insert into Z (`ID`)
|
select `ID` from A LIMIT 3;
|
After that select * from Z; should contain three rows, but doesn't with mariadb version 10.11. Version 11.8 produces the correct result.
Attachments
Issue Links
- duplicates
-
-
- Closed
-