Details
Major Description
We have identified a bug in MariaDB starting from version 10.6.21 (not present in the previous version 10.6.16)
that causes duplicate primary key errors an INSERT INTO ... SELECT ... statement.
Also tested on 10.11.11 facing same issue.
The error occurs only and precisely when the following conditions are simultaneously met:
1. A BEFORE UPDATE trigger is active on the target table of the INSERT INTO statement.
The trigger's specific actions do not appear to be relevant; the error occurs even with a trigger that performs no operations.
2. The INSERT INTO statement explicitly specifies the list of columns to insert data into (e.g., INSERT INTO table (column1, column2) SELECT ...).
3. The SELECT statement returns more than one row to be inserted into the target table.
If any of these conditions are not met (e.g., no BEFORE UPDATE trigger, INSERT INTO without specifying columns, or SELECT returning only one row),
the INSERT INTO ... SELECT ... operation executes successfully,
otherwise use 0 for integer and empty string for string data type for all SELECT results because of SQL_MODE not having STRICT_ALL_TABLES or STRICT_TRANS_TABLES.
Steps to Reproduce where tables are innodb ( using ARIA same issue as well):
|
|
create schema andre; |
use andre |
|
|
create or replace table table_vim (col1 int,col2 varchar(50) not null, primary key(col1,col2)); |
create or replace table table_vim_bck (id int auto_increment primary key,modified_timestamp timestamp,col1 int,col2 varchar(50) not null); |
insert into table_vim values (0,''); |
|
|
DELIMITER $$
|
CREATE or replace TRIGGER `tgr_table_vim_bu` BEFORE UPDATE ON `table_vim` FOR EACH ROW BEGIN |
INSERT INTO table_vim_bck VALUES (NULL,now(),OLD.col1,OLD.col2); |
END
|
$$
|
DELIMITER ;
|
this is the behavior:
– insert more than 1 row (NO colum name used on INSERT) --> Success
MariaDB [andre]> INSERT INTO `table_vim` select 1,left(md5(rand()),20) from seq_1_to_2; |
Query OK, 2 rows affected (0.006 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
– insert more than 1 row (colum name used on INSERT) --> Failed
MariaDB [andre]> INSERT INTO `table_vim` (col1,col2) select 2,left(md5(rand()),20) from seq_1_to_2; |
ERROR 1062 (23000): Duplicate entry '0-' for key 'PRIMARY' |
– insert 1 row (colum name used on INSERT) --> Success
MariaDB [andre]> INSERT INTO `table_vim` (col1,col2) select 2,left(md5(rand()),20) from seq_1_to_1; |
Query OK, 1 row affected (0.004 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
|
|
MariaDB [andre]> select * from table_vim; |
+------+----------------------+ |
| col1 | col2 |
|
+------+----------------------+ |
| 0 | |
|
| 1 | 26c850db9d39a33b1046 |
|
| 1 | 3871952d34846384b4b6 |
|
| 2 | cd8100bc06b07f3ae92a |
|
+------+----------------------+ |
4 rows in set (0.000 sec) |
— dropping the Trigger and insert 3 rows (colum name used on INSERT) --> Success
MariaDB [andre]> drop trigger tgr_table_vim_bu; |
Query OK, 0 rows affected (0.006 sec) |
|
|
MariaDB [andre]> INSERT INTO `table_vim` (col1,col2) select 7,left(md5(rand()),20) from seq_1_to_3; |
Query OK, 3 rows affected (0.005 sec) |
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [andre]> select * from table_vim; |
+------+----------------------+ |
| col1 | col2 |
|
+------+----------------------+ |
| 0 | |
|
| 1 | 29cc9c6ef5cdbaa00206 |
|
| 1 | a5bfc3f2878c1eb8aaa6 |
|
| 2 | ab04bcc0d789fe4b3f84 |
|
| 7 | 30405f86737cfe30b65d |
|
| 7 | 678e2e3be8b37edaac9f |
|
| 7 | bf4615a844c37df1f9a0 |
|
+------+----------------------+ |
7 rows in set (0.000 sec) |
We hope this information is helpful for analyzing and resolving the issue.
Attachments
Issue Links
- duplicates
-
MDEV-36026 Problem with INSERT SELECT on NOT NULL columns while having BEFORE UPDATE trigger
-
- Closed
-