Bug #65678 On duplicate key UPDATE VALUE fails regex
Submitted: 19 Jun 2012 21:56 Modified: 30 Jun 2012 10:09
Reporter: Chris Priebe Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:0.3.2-dev OS:Any
Assigned to: Geert Vanderkelen CPU Architecture:Any

[19 Jun 2012 21:56] Chris Priebe
Description:
There is a bad regex for multiple inserts that fails on DUPLICATE KEY

The following regex from cursor.py is greedy
RE_SQL_INSERT_VALUES = re.compile(r'\sVALUES\s*(\(.*\))', re.I)

INSERT INTO table (field1,field2) VALUES (%s,%s) ON DUPLICATE KEY UPDATE field1=VALUES(field1)

This results in (%s,%s) ON DUPLICATE KEY UPDATE field1=VALUES(field1)

How to repeat:
See above

Suggested fix:
Changing the regex to add a ? should give the desired output
RE_SQL_INSERT_VALUES = re.compile(r'\sVALUES\s*(\(.*?\))', re.I)
[29 Jun 2012 9:04] Geert Vanderkelen
Reproduced using latest source from repository.
Thanks for reporting this bug. It will be addressed in next release.

To reproduce:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

 >>> cur.executemany("INSERT INTO t1 (id,c1) VALUES (%s,%s) ON DUPLICATE KEY UPDATE c1=VALUES(c1)", [(1,1),(2,2)])
mysql.connector.errors.ProgrammingError: 1064: b"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(2,2) ON DUPLICATE KEY UPDATE c1=VALUES(c1)' at line 1"
>>> cur._executed
b'INSERT INTO t1 (id,c1) VALUES (1,1) ON DUPLICATE KEY UPDATE c1=VALUES(c1),(2,2) ON DUPLICATE KEY UPDATE c1=VALUES(c1)'

Workaround would be to use .execute() and loop over the data for queries using ON DUPLICATE KEY.
[30 Jun 2012 10:09] Geert Vanderkelen
The regular expression was indeed to greedy, and the proposed extra '?' is indeed working.
The fix for this bug will be part of next releases. Thanks again for reporting!