Uploaded image for project: 'MariaDB Connector/Python'
  1. MariaDB Connector/Python
  2. CONPY-136

Incorrect "DML" behaviour inside Trigger with InnoDB as destiny

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Not a Bug
    • Affects Version/s: None
    • Fix Version/s: N/A
    • Component/s: Generic
    • Labels:
      None
    • Environment:
      MariaDB 10.5.8
      Windows 10 64bits

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

        Attachments

          Activity

            People

            Assignee:
            georg Georg Richter
            Reporter:
            MarcT Marcelo Titonelli Pio da Cruz
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration