MySQL Bugs: #98816: Syntax error while using array inserts

Bug #98816	Syntax error while using array inserts
Submitted:	3 Mar 2020 15:45	Modified:	7 May 2020 11:28
Reporter:	Jean-Christophe Voogden	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.19	OS:	Windows
Assigned to:		CPU Architecture:	x86

Description:
When using array inserts (SQLSetStmtAttr(..SQL_ATTR_PARAMSET_SIZE...)) with the windows ODBC driver (tested with the 32bits version), we get syntax errors reported.

The driver generates an invalid query :

1583247634: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 'INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'FNT', 'HALPFNT' at line 1;
1583247634:INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'FNT', 'HALFNT')INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'FNT', 'HALPFNT')INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'MCI', 'HALMACRO');
1583247634:Using direct execution;
1583247634:query has been executed;
1583247634: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 'INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'FNT', 'HALPFNT' at line 1;
1583247634:INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'FNT', 'HALFNT')INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'FNT', 'HALPFNT')INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'MCI', 'HALMACRO')INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'MCI', 'HALPMAC')£yþÑ'
IâŸ·ubræAµ¡Yx^¦pØŽd²ÆO¼·{Ÿ?¾=Š[Ôò$;

=> ';' separator beetween queries is missing, corrupted characters appearing at the end...

The previous versions (8.0.18) worked, but does not seems to use 'multiple statements) :

1583247709:INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'FNT', 'HALFNT');
1583247709:Using direct execution;
1583247709:query has been executed;
1583247709:INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'FNT', 'HALPFNT');
1583247709:Using direct execution;
1583247709:query has been executed;
1583247709:INSERT INTO "A2SGPFI" ("GNOMOD", "GTYOBJ", "GNOFIC") VALUES ('', 'MCI', 'HALMACRO');
1583247709:Using direct execution;
1583247709:query has been executed;
...

Same ODBC code in both cases. The problem is identical in Ansi and Unicode versions of the driver.

The ODBC DSN is generated automatically with the following options :

Driver=myodbc8w.dll;Server=localhost;Port=3306;Database=test;BIG_PACKETS=1;FORWARD_CURSOR=1;MULTI_STATEMENTS=1;FOUND_ROWS=1;no_ssps=1;

Server is MySQL Community 8.0.13.

How to repeat:
Use SQLSetSmtAttr with SQL_ATTR_PARAM_BIND_TYPE / SQL_ATTR_PARAMSET_SIZE / SQL_ATTR_PARAM_STATUS_PTR to describe a parameter array, and SQLExecDirect to run the query.

Thank you for the bug report. Please provide a complete test case (database SQL script, client program C file, real result print and expected result). Thanks.

Test case

Attachment: arrays.c (text/plain), 4.29 KiB.

Sorry for the delay. The attached test case is self contained and work correctly with odbc driver 8.0.18 and below but fails with odbc driver 8.0.19.

The test program assumes a anonymous local connection to the "test" database, with proper privileges.

The program is build using Microsoft Visual Studio 2017 compiler, in 32bits.

As a side note, it also fails with the 64-bit driver

Precisions : the problem also occurs with version 8.0.20. It seems related to the desactivation of server-side prepared statements (no_ssps=1 in DSN).

Precisions : the problem also occurs with version 8.0.20. It seems related to the desactivation of server-side prepared statements (no_ssps=1 in DSN).

Thank you for the feedback.

Posted by developer:
 
I was able to repeat this problem with the ODBC driver version 8.0.20, but since then the ODBC received a patch addressing a similar issue.
With this patched version the inserts worked as expected. The fix should be published in the version 8.0.21.
Closing the bug.

Is it possible to somehow get a pre-release version of 8.0.21 of the ODBC Driver, for testing if this fix resolves a very similar issue that we are seeing?