Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.8
-
MariaDB 10.5.4
Windows 10 64 bits
Description
CREATE TABLE `table1` (
|
`id` int(11) DEFAULT NULL, |
`name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL |
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PAGE_CHECKSUM=1; |
|
|
CREATE TABLE `table2` (
|
`id` int(11) DEFAULT NULL, |
`name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL |
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PAGE_CHECKSUM=1; |
|
|
CREATE TRIGGER `table1_before_insert` BEFORE INSERT ON `table1`
|
FOR EACH ROW BEGIN
|
insert into table2(`id`, `name`) values (NEW.`id`, NEW.`name`);
|
END
|
import mariadb |
conn=mariadb.connect(user="test", database="test") |
cursor=conn.cursor()
|
vals = [(1,'John'), (2,'Smith'), (3,'Carol')] |
cursor.executemany("INSERT INTO table1 VALUES (?,?)", vals) |
cursor.execute("SELECT * FROM table1") |
rows= cursor.fetchall()
|
print(rows)
|
Expected output:
[(1, 'John', 1, 'John'), (2, 'Smith', 1, 'John'), (3, 'Carol', 1, 'John'), (1, 'John', 2, 'Smith'), (2, 'Smith', 2, 'Smith'), (3, 'Carol', 2, 'Smith'), (1, 'John', 3, 'Carol'), (2, 'Smith', 3, 'Carol'), (3, 'Carol', 3, 'Carol')]
Current output:
[(1, 'John', 1, 'John'), (1, 'John', 1, 'John')]
Expected rows on tables:
Table1:
"1" "John"
"2" "Smith"
"3" "Carol"
Table2:
"1" "John"
"2" "Smith"
"3" "Carol"
Current rows on tables:
Table1:
"1" "John"
Table2:
"1" "John"
"1" "John"
Running insert on mariadb console, works correctly:
insert into Table1 values(1,'John'),(2,'Smith'),(3,'Carol');
Attachments
Issue Links
- causes
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
- split to
-
-
- Closed
-
Activity
Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
the bug also affects bulk update , as shown here :
https://stackoverflow.com/questions/78204056/hibernate-sqlintegrityconstraintviolation-duplicate-data-entry-issue-after-upg
https://dba.stackexchange.com/questions/339389/weird-behaviour-with-batch-and-triggermariadb?noredirect=1#comment660144_339389
let me know if it is better to create separate issue for bulk update.
Please check it and file the new bug report in case the update scenario is still reproducible.
I will check the use case for UPDATE statement and add results in comments.
Thank you for your bug report, this is a server bug, so I will reclassify and reassign this ticket.
static int test_conpy135(MYSQL *mysql){int rc;MYSQL_STMT *stmt;MYSQL_BIND bind;unsigned int vals[]= {1,2,3};unsigned int array_size= 3;MYSQL_ROW row;MYSQL_RES *result;rc= mysql_query(mysql, "CREATE OR REPLACE TABLE t1 (a int)");check_mysql_rc(rc, mysql);rc= mysql_query(mysql, "CREATE OR REPLACE TABLE t2 (a int)");check_mysql_rc(rc, mysql);rc= mysql_query(mysql, "CREATE TRIGGER t1_before_insert BEFORE INSERT ON t1\n""FOR EACH ROW BEGIN\n"" INSERT INTO t2 (a) VALUES (NEW.a);\n""END");check_mysql_rc(rc, mysql);stmt= mysql_stmt_init(mysql);rc= mysql_stmt_prepare(stmt, SL("INSERT INTO t1 VALUES (?)"));check_stmt_rc(rc, stmt);memset(&bind, 0, sizeof(MYSQL_BIND));bind.buffer_type= MYSQL_TYPE_LONG;bind.buffer= vals;rc= mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, &array_size);check_stmt_rc(rc, stmt);rc= mysql_stmt_bind_param(stmt, &bind);check_stmt_rc(rc, stmt);rc= mysql_stmt_execute(stmt);check_stmt_rc(rc, stmt);diag("affected rows: %lld", mysql_stmt_affected_rows(stmt));rc= mysql_query(mysql, "SELECT 't1', a FROM t1 UNION SELECT 't2', a FROM t2");check_mysql_rc(rc, mysql);result= mysql_store_result(mysql);while ((row = mysql_fetch_row(result)))diag("%s | %s ", row[0], row[1]);mysql_free_result(result);return OK;}Output without trigger:
# affected rows: 3# t1 | 1# t1 | 2# t1 | 3# t2 | 1# t2 | 2# t2 | 3Output with trigger:
# affected rows: 1# t1 | 1# t2 | 1