[MDEV-24411] Trigger doesn't work correctly with bulk insert - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.5.8
Fix Version/s: 10.5.26, 10.6.19, 10.11.9, 11.1.6, 11.2.5, 11.4.3, 11.5.2, 11.6.0
Component/s: Binary Protocol
Labels:
- CONNECTOR_RELATED
Environment:
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

CONJ-1086 Batch insert inserts only the first row when there is a trigger

Closed

CONJS-182 Batch inserts with duplicate treatment only perform 1 insert

Closed

relates to

MDEV-34958 after Trigger doesn't work correctly with bulk insert

Closed

split to

MDEV-34718 Trigger doesn't work correctly with bulk update

Closed

Activity

Ascending order - Click to sort in descending order

Marcelo Titonelli Pio da Cruz created issue - 2020-12-15 07:03

Marcelo Titonelli Pio da Cruz made changes - 2020-12-15 07:06

Field	Original Value	New Value
Description	{code:java} 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 {code} {code:java} 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) {code} 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');	{code:java} 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 {code} {code:java} 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) {code} 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');

Georg Richter added a comment - 2020-12-15 13:55

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 | 3

Output with trigger:

# affected rows: 1

# t1 | 1

# t2 | 1

Georg Richter added a comment - 2020-12-15 13:55 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 | 3 Output with trigger: # affected rows: 1 # t1 | 1 # t2 | 1

Georg Richter made changes - 2020-12-15 13:57

Component/s		Binary Protocol [ 16120 ]
Component/s	Other [ 16504 ]
Key	~~CONPY-135~~	~~MDEV-24411~~
Affects Version/s		10.5.8 [ 25023 ]
Affects Version/s	1.0.5 [ 25126 ]
Project	MariaDB Connector/Python [ 11400 ]	MariaDB Server [ 10000 ]

Georg Richter made changes - 2020-12-15 13:57

Assignee

Georg Richter [ georg ]

Oleksandr Byelkin [ sanja ]

Georg Richter made changes - 2020-12-15 13:59

Priority

Critical [ 2 ]

Blocker [ 1 ]

Georg Richter made changes - 2020-12-15 18:06

Priority

Blocker [ 1 ]

Critical [ 2 ]

Georg Richter made changes - 2020-12-15 18:09

Summary

Trigger doesn't work correctly with executemany

Trigger doesn't work correctly with bulk insert

Elena Stepanova made changes - 2021-01-14 13:11

Fix Version/s

10.5 [ 23123 ]

Diego Dupin made changes - 2021-02-01 15:25

Labels

CONNECTOR_RELATED

Diego Dupin made changes - 2021-02-01 15:27

Labels

CONNECTOR_RELATED

Oleksandr Byelkin made changes - 2021-06-09 12:11

Status

Open [ 1 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 116872 ]

MariaDB v4 [ 144580 ]

Diego Dupin made changes - 2021-12-08 11:35

Labels

CONNECTOR_RELATED

Diego Dupin made changes - 2021-12-08 11:42

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]

Diego Dupin made changes - 2021-12-08 11:47

Link

This issue is duplicated by ~~CONJS-182~~ [ ~~CONJS-182~~ ]

Oleksandr Byelkin made changes - 2022-06-22 08:41

Assignee

Oleksandr Byelkin [ sanja ]

Dmitry Shulga [ JIRAUSER47315 ]

Ralf Gebhardt made changes - 2022-08-04 08:43

Fix Version/s

10.2 [ 14601 ]

Sergei Golubchik made changes - 2023-01-20 18:26

Priority

Critical [ 2 ]

Major [ 3 ]

Julien Fritsch made changes - 2023-03-03 17:28

Fix Version/s

10.7 [ 24805 ]

Julien Fritsch made changes - 2023-04-27 14:02

Fix Version/s

10.3 [ 22126 ]

Diego Dupin made changes - 2023-06-09 12:21

Link

This issue blocks ~~CONJ-1086~~ [ ~~CONJ-1086~~ ]

Diego Dupin made changes - 2023-06-09 12:22

Link

This issue causes ~~CONJ-1086~~ [ ~~CONJ-1086~~ ]

Diego Dupin made changes - 2023-06-09 12:23

Link

This issue blocks ~~CONJ-1086~~ [ ~~CONJ-1086~~ ]

Diego Dupin made changes - 2023-06-09 12:25

Link

This issue causes ~~CONJS-182~~ [ ~~CONJS-182~~ ]

Diego Dupin made changes - 2023-06-09 12:25

Link

This issue is duplicated by ~~CONJS-182~~ [ ~~CONJS-182~~ ]

Julien Fritsch added a comment - 2023-12-05 09:26

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Julien Fritsch added a comment - 2023-12-05 09:26 Automated message: ---------------------------- Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

JiraAutomate made changes - 2023-12-05 11:48

Status

In Progress [ 3 ]

Stalled [ 10000 ]

JiraAutomate added a comment - 2023-12-05 11:48

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

JiraAutomate added a comment - 2023-12-05 11:48 Automated message: ---------------------------- Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Ralf Gebhardt made changes - 2024-04-02 14:07

Priority

Major [ 3 ]

Critical [ 2 ]

Rudy Gunawan added a comment - 2024-05-11 02:22

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.

Rudy Gunawan added a comment - 2024-05-11 02:22 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.

Pandikrishnan Gurusamy made changes - 2024-05-23 12:23

Assignee

Dmitry Shulga [ JIRAUSER47315 ]

Pandikrishnan Gurusamy [ JIRAUSER52131 ]

Pandikrishnan Gurusamy made changes - 2024-05-23 12:23

Assignee

Pandikrishnan Gurusamy [ JIRAUSER52131 ]

Oleksandr Byelkin [ sanja ]

Pandikrishnan Gurusamy made changes - 2024-05-23 12:24

Priority

Critical [ 2 ]

Blocker [ 1 ]

Sergei Golubchik made changes - 2024-05-23 14:23

Priority

Blocker [ 1 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2024-05-23 14:27

Assignee

Oleksandr Byelkin [ sanja ]

Dmitry Shulga [ JIRAUSER47315 ]

Dmitry Shulga made changes - 2024-06-06 02:37

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Dmitry Shulga added a comment - 2024-06-18 11:52

The branch for review is bb-10.5-~~MDEV-24411~~

Dmitry Shulga added a comment - 2024-06-18 11:52 The branch for review is bb-10.5- MDEV-24411

Dmitry Shulga made changes - 2024-06-18 11:52

Assignee	Dmitry Shulga [ JIRAUSER47315 ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Oleksandr Byelkin added a comment - 2024-06-24 11:14

Remove please adding of two emply lines and it is OK to push

Oleksandr Byelkin added a comment - 2024-06-24 11:14 Remove please adding of two emply lines and it is OK to push

Oleksandr Byelkin made changes - 2024-06-24 11:14

Assignee	Oleksandr Byelkin [ sanja ]	Dmitry Shulga [ JIRAUSER47315 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Dmitry Shulga made changes - 2024-06-25 03:51

Fix Version/s		10.5.26 [ 29832 ]
Fix Version/s		10.6.19 [ 29833 ]
Fix Version/s		10.11.9 [ 29834 ]
Fix Version/s		11.1.6 [ 29835 ]
Fix Version/s		11.2.5 [ 29836 ]
Fix Version/s		11.4.3 [ 29837 ]
Fix Version/s		11.5.2 [ 29838 ]
Fix Version/s		11.6.0 [ 29839 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]

Dmitry Shulga made changes - 2024-06-25 03:52

Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Dmitry Shulga added a comment - 2024-06-25 03:58

Please check it and file the new bug report in case the update scenario is still reproducible.

Dmitry Shulga added a comment - 2024-06-25 03:58 Please check it and file the new bug report in case the update scenario is still reproducible.

Jira Automation (IT) made changes - 2024-07-04 02:35

Zendesk Related Tickets		201572
Zendesk active tickets		201572

Dmitry Shulga added a comment - 2024-08-01 09:56

I will check the use case for UPDATE statement and add results in comments.

Dmitry Shulga added a comment - 2024-08-01 09:56 I will check the use case for UPDATE statement and add results in comments.

Dmitry Shulga made changes - 2024-08-06 03:03

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Dmitry Shulga made changes - 2024-08-06 03:09

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Dmitry Shulga made changes - 2024-08-07 09:51

Link

This issue split to ~~MDEV-34718~~ [ ~~MDEV-34718~~ ]

Dmitry Shulga made changes - 2024-08-07 10:18

Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Diego Dupin made changes - 2024-09-19 09:16

Link

This issue relates to ~~MDEV-34958~~ [ ~~MDEV-34958~~ ]

Luciano Barcaro added a comment - 2024-12-11 16:42

This bug is somehow related to MDEV-29218 ?

Luciano Barcaro added a comment - 2024-12-11 16:42 This bug is somehow related to MDEV-29218 ?

Dmitry Shulga added a comment - 2024-12-13 09:55

MDEV-29218 should be fixed by a patch for the task ~~MDEV-34958~~

Dmitry Shulga added a comment - 2024-12-13 09:55 MDEV-29218 should be fixed by a patch for the task MDEV-34958

People

Assignee:: Dmitry Shulga

Reporter:: Marcelo Titonelli Pio da Cruz

Votes:: 9 Vote for this issue

Watchers:: 16 Start watching this issue

Dates

Created:: 2020-12-15 07:03

Updated:: 2024-12-21 09:01

Resolved:: 2024-08-07 10:18

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration