MySQL Bugs: #103792: Latest connector odbc fails preparing multi-statements without NO

Bug #103792	Latest connector odbc fails preparing multi-statements without NO_SSPS=1
Submitted:	24 May 2021 15:50	Modified:	8 Jun 2021 21:56
Reporter:	Amit Chaudhuri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.24	OS:	CentOS (7)
Assigned to:		CPU Architecture:	x86 (x86_64)
Tags:	multi-statement, NO_SSPS

Description:
Previously working code broke with the release of 8.0.24 connector odbc. We tested combinations of .23,.24,.25 database and connector and the failures implicate the .24 connector (and therefore .25 connector also - simple version bump).

Code review suggests addition of a force_prepare parameter might be relevant.

As I can reproduce the problem by issuing multi-statements via isql I am inclined to think this might be an unintended side effect.

In our case, the multi-statements are important to the overall system integrity at start up, so this prevents operation. And because the new versions are available in published repos, customers could elect to adopt and then be forced to back out etc.

How to repeat:
On a system running 8.0.23 connector odbc create a simple table (1 col integer). Check odbc.ini has no option for NO_SSPS set.
Use isql to issue a multi-statement (e.g. insert into X values 10; insert into X values 20;)
This will succeed.

Upgrade connector to .24 and repeat - this will fail (SQL Prepare failure).

Add NO_SSPS=1 to odbc.ini and restart. Repeat the test - this will succeed.

Hi Amit,

Thank you for your bug report. You are right about the situation with preparing multiple statements being caused by an improper usage.
Here are the facts:

MySQL Server does not support multiple prepared statements per query:
https://dev.mysql.com/doc/refman/8.0/en/prepare.html

Therefore, using SQLPrepare() on such queries is not recommended.

It is true that the earlier versions of MySQL ODBC Connector partially supported this behavior: if the statement did not contain any parameter markers SQLPrepare() would do nothing and SQLExecute() would execute the query as COM_QUERY, which would have the same effect as just calling SQLExecDirect() function.

This was an undocumented behavior, but some users adopted it for their programs. Just as in many other cases of undocumented behavior it changes without warning and some code stops working. The change in the connector was as follows: SQLPrepare() will always prepare a query using COM_STMT_PREPARE even if the query has no parameters. This does not break ODBC API specifications:

https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlprepare-function?view=sql-se...

For more details about COM_STMT_PREPARE check this page:
https://dev.mysql.com/doc/internals/en/com-stmt-prepare.html

Our recommendation would be to use SQLExecDirect() for such multi-statement queries and not to try preparing them with SQLPrepare().

The NO_SSPS=1 option causes the driver never to use COM_STMT_PREPARE/COM_STMT_EXECUTE and forces to execute queries by COM_QUERY as if they were executed by SQLExecDirect().

This is not a bug and the status is set accordingly.

To avoid further confusion this behavior should be documented.
Setting the status for Documenting.

Posted by developer:
 
The MULTI_STATEMENTS documentation was updated accordingly, and now reads as:

Enables support for batched statements. As of 8.0.24, preparing a query
with multiple statements raises an error. The direct
execution of parameter-less statements prepared using the
SQLPrepare() function is not supported. Multiple
statements can only be executed through the SQLExecDirec()
ODBC function.

Thank you for the bug report!