MySQL Bugs: #87457: ODBC Driver Issue with SELECT for UPDATE

Bug #87457	ODBC Driver Issue with SELECT for UPDATE
Submitted:	17 Aug 2017 14:57	Modified:	29 Jan 2018 20:56
Reporter:	Vibin Varghese	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	MySQL 5.6.10	OS:	Red Hat (RHEL 7.3)
Assigned to:		CPU Architecture:	Any
Tags:	connector, ODBC, select for update

Description:
Hi There,

This is to report a potential bug with the way Connector processes SELECT FOR UPDATE statements. 

I'm running into an issue with results from Prefetch feature of MySQL ODBC Connector.
This a COBOL implementation running embedded SQL hitting Amazon RDS(MySQL), using MySQL ODBC Connector.

A below cursor SQL statement fails with invalid syntax error.

SELECT * FROM COP_CORRSP WHERE BATCH_NO > "548550" FOR UPDATE

The PREFECTH was set as 1000 while this SQL open was issued, and I hit the below error.

------------------------------------------------------------------------------------------------------
DIAG [42000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.10]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 'LIMIT  0, 1000' at line 1
------------------------------------------------------------------------------------------------------

The reference to this ticket was initially made with bug - https://bugs.mysql.com/bug.php?id=87213 (Comment "[17 Aug 10:36] Bogdan Degtyariov")

Environment Details
OS - RHEL 7, 64-bit
Database - Amazon Aurora (Aurora 1.11, InnoDB 1.2.10)
MySQL 5.6.10
ODBC - mysql-connector-odbc-5.3.8-1.el7.x86_64
 

Thanks,
Vibin

How to repeat:
'Cursor Select for Update' with PREFETCH set for Driver.

Hello Vibin Varghese,

Thank you for the report.

Thanks,
Chiranjeevi.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 5.3.10 release, and here's the changelog entry:

SELECT FOR UPDATE statements could fail with PREFETCH set.

Thank you for the bug report.