BUG#34499578: MySQLCursor.executemany() fails to correctly identify BULK data loading ops

oscpache · oscpache · commit 1a74a1f1b00f · 2022-10-13T11:27:35.000-05:00
The MySQLCursor.executemany() method fails to batch insert data since
the regular expression (RE) sentinel used does not detect batch cases
correctly resulting in using the one-on-one insert, hence an overall
performance issue.

With this patch the RE sentinel is fixed, hence restoring back the
batch insert mechanism. Also, this patch fixes BUG#34555367 and is
the product of a contribution from Alex Cazacu.

Thank you for the contribution.

Change-Id: I784ea8c44d5f1f6dcd2d6d9448e173393689242d
diff --git a/CHANGES.txt b/CHANGES.txt
@@ -15,6 +15,7 @@ v8.0.32
 - WL#15036: Support for type hints
 - BUG#34689812: Fix datetime conversion when using prepared cursors
 - BUG#34556157: Kerberos authorization fails when using SSPI as security interface
+- BUG#34499578: MySQLCursor.executemany() fails to correctly identify BULK data loading ops
 - BUG#30089671: Fix decoding VARBINARY columns when using a prepared cursor
 
 v8.0.31
diff --git a/lib/mysql/connector/cursor.py b/lib/mysql/connector/cursor.py
@@ -85,7 +85,7 @@
 RE_SQL_INSERT_STMT = re.compile(
     rf"({SQL_COMMENT}|\s)*INSERT({SQL_COMMENT}|\s)"
     r"*INTO\s+[`'\"]?.+[`'\"]?(?:\.[`'\"]?.+[`'\"]?)"
-    r"{{0,2}}\s+VALUES\s*\(.+(?:\s*,.+)*\)",
+    r"{0,2}\s+VALUES\s*\(.+(?:\s*,.+)*\)",
     re.I | re.M | re.S,
 )
 RE_SQL_INSERT_VALUES = re.compile(r".*VALUES\s*(\(.*\)).*", re.I | re.M | re.S)
diff --git a/tests/test_bugs.py b/tests/test_bugs.py
@@ -7185,3 +7185,66 @@ def test_datetime_with_prepared_cursor(self):
             )
             res = cur.fetchall()
             self.assertEqual(res[0], self.data)
+
+
+class BugOra34499578(tests.MySQLConnectorTests):
+    """BUG#34499578: MySQLCursor.executemany() fails to correctly identify BULK data loading ops.
+
+    The MySQLCursor.executemany() method fails to batch insert data since
+    the regular expression (RE) sentinel used does not detect batch cases
+    correctly resulting in using the one-on-one insert, hence an overall
+    performance issue.
+    """
+
+    table_name = "BugOra34499578"
+    field1, field2 = "color", "country"
+
+    def setUp(self):
+        config = tests.get_mysql_config()
+        with mysql.connector.connect(**config) as cnx:
+            with cnx.cursor() as cur:
+                cur.execute(f"DROP TABLE IF EXISTS {self.table_name}")
+                cur.execute(
+                    f"""
+                    CREATE TABLE {self.table_name}(
+                        {self.field1} char(32),
+                        {self.field2} char(32)
+                    )
+                    """
+                )
+            cnx.commit()
+
+    def tearDown(self):
+        config = tests.get_mysql_config()
+        with mysql.connector.connect(**config) as cnx:
+            cnx.cmd_query(f"DROP TABLE IF EXISTS {self.table_name}")
+
+    @foreach_cnx()
+    def test_batch_insert_happens_in_executemany(self):
+        seq_params = [["red", "portugal"], ["green", "mexico"], ["blue", "argentina"]]
+        with self.cnx.cursor() as cur:
+            cur.executemany(
+                f"""
+                INSERT INTO {self.table_name} (
+                    {self.field1},
+                    {self.field2})
+                    VALUES(
+                        %s,
+                        %s
+                    )
+                """,
+                seq_params,
+            )
+            # if batch insert happens, it means all parameters must be
+            # somewhere in the executed statement, else just a parameters
+            # subset is in, particularly the last parameter.
+            self.assertTrue(
+                all(
+                    [
+                        param.encode("utf8", errors="ignore") in cur._executed
+                        for params in seq_params
+                        for param in params
+                    ]
+                ),
+                "Batch insert failed!",
+            )

Original file line number	Diff line number	Diff line change
`@@ -85,7 +85,7 @@`
`85`	`85`	`RE_SQL_INSERT_STMT = re.compile(`
`86`	`86`	`rf"({SQL_COMMENT}\|\s)*INSERT({SQL_COMMENT}\|\s)"`
`87`	`87`	r"*INTO\s+[`'\"]?.+[`'\"]?(?:\.[`'\"]?.+[`'\"]?)"
`88`		`- r"{{0,2}}\s+VALUES\s\(.+(?:\s,.+)*\)",`
	`88`	`+ r"{0,2}\s+VALUES\s\(.+(?:\s,.+)*\)",`
`89`	`89`	`re.I \| re.M \| re.S,`
`90`	`90`	`)`
`91`	`91`	`RE_SQL_INSERT_VALUES = re.compile(r".VALUES\s(\(.\)).", re.I \| re.M \| re.S)`