aio-libs / aio-libs/aiomysql

No fast-track for bulk inserts in Cursor.executemany with INSERT/REPLACE syntax introduced in MySQL 8.0.19

Abierto
#968 3 comentarios 1 reacción 0 asignados Ver en GitHub
bug
Lenguaje dominante
Python
Estrellas
1.9k
Forks
272
Métricas de merge de PR
Sin PR fusionados en 30 d

Descripción

### Describe the bug

When calling `Cursor.executemany` with an `INSERT` or `REPLACE` SQL statement, `aiomysql` compares the statement against a regular expression `RE_INSERT_VALUES` and if there is a match, a different, fast-track execution path is taken as opposed to expanding `executemany` into a series of `execute` statements.

Unfortunately, the regular expression tests against the older `INSERT` or `REPLACE` syntax in MySQL versions prior to 8.0.19 only. For example, the following is a match:
```
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
```

This causes a warning message to be emitted with MySQL 8.0.20 and later:
```
/usr/local/lib/python3.11/site-packages/aiomysql/cursors.py:239: Warning: 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
```

However, if the new recommended syntax is adopted, the fast-track course is not longer chosen, and execution significantly slows down. This is because the new syntax is no longer a match for `RE_INSERT_VALUES`:
```
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;
```

On the contrary, execution speed is restored if we slightly modify the regular expression that SQL statements are tested against:
```
RE_INSERT_VALUES = re.compile(
r"\s*((?:INSERT|REPLACE)\s.+\sVALUES?\s+)"
+ r"(\(\s*(?:%s|%\(.+\)s)\s*(?:,\s*(?:%s|%\(.+\)s)\s*)*\))"
+ r"(\s*(?:(?:AS|ON DUPLICATE).*)?);?\s*\Z",
re.IGNORECASE | re.DOTALL,
)
```
This will make both old-style and new-style syntax pass.

### To Reproduce

Try a [bulk INSERT statement](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html) with the old syntax:
```
INSERT INTO "DataTable"
("id", "data") VALUES (%s, %s)
ON DUPLICATE KEY UPDATE
"data" = VALUES("data")
```

A warning message is emitted in MySQL 8.0.20 and later.

Try a bulk INSERT statement with the new syntax (MySQL 8.0.19 and later):
```
INSERT INTO "DataTable"
("id", "data") VALUES (%s, %s) AS EXCLUDED
ON DUPLICATE KEY UPDATE
"data" = EXCLUDED."data"
```

Execution significantly slows down.

### Expected behavior

Execution speed does not diminish when using the new MySQL 8.0.20 syntax for `INSERT`.

### Logs/tracebacks

```python-traceback
n/a
```

### Python Version

```console
Python 3.11.5
```

### aiomysql Version

```console
Name: aiomysql
Version: 0.2.0
Summary: MySQL driver for asyncio.
Home-page: https://github.com/aio-libs/aiomysql
Author: Nikolay Novik
Author-email: nickolainovik@gmail.com
License: MIT
Location: /usr/local/lib/python3.11/site-packages
Requires: PyMySQL
Required-by:
```

### PyMySQL Version

```console
Name: PyMySQL
Version: 1.1.0
Summary: Pure Python MySQL Driver
Home-page:
Author:
Author-email: Inada Naoki , Yutaka Matsubara
License: MIT License
Location: /usr/local/lib/python3.11/site-packages
Requires:
Required-by: aiomysql
```

### SQLAlchemy Version

_No response_

### OS

macOS 13.5.2

### Database type and version

```console
MySQL 8.1.0
```

### Additional context

_No response_

### Code of Conduct

- [X] I agree to follow the aio-libs Code of Conduct

Guía de contribución

Abrir la guía de contribución

Evaluación

Este issue todavía no se ha evaluado.

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.