[#CONPY-136] Incorrect "DML" behaviour inside Trigger with InnoDB as destiny

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=InnoDB 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

def database_on_demand():

    return mariadb.connect(

        user="root", database="test", host="127.0.0.1", password="123456", connect_timeout=180, read_timeout=180, write_timeout=180)

conn = database_on_demand()

cursor = conn.cursor()

rows = [(1, 'John'), (2, 'Smith'), (3, 'Carol')]

cursor.execute("TRUNCATE table1;")

cursor.execute("TRUNCATE table2;")

for values in rows:

    cursor.execute("INSERT INTO table1 VALUES (?,?)", values)

cursor.execute("SELECT COUNT(*) FROM table1, table2;")

rows = cursor.fetchall()

print(rows)

conn2 = database_on_demand()

cursor2 = conn2.cursor()

cursor2.execute("SELECT COUNT(*) FROM table1, table2;")

rows = cursor2.fetchall()

print(rows)

Expected output:
[(9,)]
[(9,)]

Current output:
[(9,)]
[(0,)]

Note the buffer on current output, it counts 9 on same connection which inserted, but there's 0 rows on table2.
------------------------
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"
"2" "Smith"
"3" "Carol"

table2:

------------------------

Inserting directly from console works:

TRUNCATE Table1;

TRUNCATE Table2;

insert into Table1 values(1,'John'),(2,'Smith'),(3,'Carol');

Inserting in one command line, on Python, does not work:
Change the part:

 for values in rows:

     cursor.execute("INSERT INTO table1 VALUES (?,?)", values)

to:

cursor.execute("insert into Table1 values(1,'John'),(2,'Smith'),(3,'Carol');");

If the engine from table2 is changed from InnoDB to Aria or MyIsam, it works from python as well.

[CONPY-136] Incorrect "DML" behaviour inside Trigger with InnoDB as destiny Created: 2020-12-23 Updated: 2020-12-24 Resolved: 2020-12-24
Status:	Closed
Project:	MariaDB Connector/Python
Component/s:	Generic
Affects Version/s:	None
Fix Version/s:	N/A

[CONPY-136] Incorrect "DML" behaviour inside Trigger with InnoDB as destiny Created: 2020-12-23 Updated: 2020-12-24 Resolved: 2020-12-24