MySQL Bugs: #65678: On duplicate key UPDATE VALUE fails regex

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

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)

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.

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!