MySQL Bugs: #86983: MySQL ODBC Connector 5.1.13-1 on RHEL7.3

Bug #86983	MySQL ODBC Connector 5.1.13-1 on RHEL7.3
Submitted:	7 Jul 2017 9:23	Modified:	27 Jul 2017 19:32
Reporter:	Vibin Varghese	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	MySQL 5.6.10	OS:	Red Hat (RHEL 7.3 (64-bit))
Assigned to:		CPU Architecture:	Any
Tags:	5.1, connector, ODBC, RHEL7

Description:
Hi,
I'm running COBOL with MySQL 5.6.10(Aurora 1.11, InnoDB 1.2.10).

This is a legacy application getting migrated from Mainframe and the program trying to retrieve data using cursors.

The problem is my application has two "active"(opened) cursors, and while fetching data from the second cursor data seems incorrect.
SQLCODE [+0000002014]
SQLERRMC [[MySQL][ODBC 5.3(a) Driver] Commands out of sync; you can't run this command now ]

From previously reported bug(https://bugs.mysql.com/bug.php?id=71431), I understand this is an issue from ODBC Connector versions 5.2 and on, and this works okay with 5.1.

I do not find the 5.1 version supported for RHEL7 here - https://dev.mysql.com/downloads/connector/odbc/

I would've liked to know if there's going to be an issue if I install "mysql-connector-odbc-5.1.13-1.el6.x86_64.rpm" on RHEL7.3.

Would be great if you can highlight any known incompatibilities or issues if I go ahead.

Any other known recent solutions to this if available, would be awesome!

Thanks,
Vibin Varghese.

How to repeat:
Any program with two active/open cursors with MySQL ODBC Connector 5.2 or 5.3 has this issue.

Hi Vibin,

Sorry to hear you get this issue with Connector/ODBC 5.3.
Before proceeding with the bug I need to clarify one thing.
You said this:

"The problem is my application has two "active"(opened) cursors, and while fetching data from the second cursor data seems incorrect."

It assumes that your application is opening two Cursor at the client side.
At the same time you gave the reference a bug 71431, which is completely different thing. There is no cursors on the client side, but on the server side in the stored procedure.

Can you please tell us which case is yours:
 A. Two open cursors on the client side
 B. Two cursors inside the stored procedure

Thanks.

Hi Bogdan,

Apologies if there was a confusion.
My issue is with two open cursor at the client side.

Thanks,
Vibin

Hi Bogdan,
An quick update on the issue.
I just discovered that the issue with multiple open cursor at the client goes away if I remove the NO_CACHE=1 option in the odbc.ini file.
The NO_CACHE was put in place to prevent connector from caching a huge result sets causing memory overflow at client.
Right now, stuck between rock and a hard place!

Do you know why NO_CACHE causes this? 

And is there a way for me to kill both birds at the same time, i.e..
(a) handling huge result sets
(b) multiple open cursors at client side. 

Thanks,
Vibin

Hi Vibin,

Thanks for the update.
The thing with cashing is as follows: MySQL Network protocol does not support multiple active cursors on the client side within the same connection. It is a one way street. You cannot do anything until the results from the first cursor are consumed by a client. Any attempt to operate otherwise will result in "commands out of sync" error.

By default the ODBC driver automatically reads the whole result set into the memory, which makes the connection available for other purposes (such as cursor #2). In NO_CACHE mode the rows are read one by one and until they are all consumed other commands are not allowed.
This is the general rule if you have several statements/cursors within the same connection.

Another way of dealing with this problem is using two or more concurrent connections. Your application can use multiple connections at the same time without the danger of collision or protocol limitations as explained above. With this approach you have to keep in mind that the context of one connection (such as variables, temp tables, session states etc) will not be available to other connections.

I do not think you hit a bug here. So far it looks as expected behavior. The errors are caused by usage scenarios trying to go over limitations.

Thanks for the clarification.

Regards,
Vibin