MySQL Bugs: #108431: Empty TEXT columns not read correctly in Connector/ODBC

Bug #108431	Empty TEXT columns not read correctly in Connector/ODBC
Submitted:	8 Sep 2022 14:09	Modified:	30 Jun 8:45
Reporter:	Sruli Ganor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi, This bug occurs in tables with TEXT columns (including TINY, LONG & MEDIUM TEXT), and only in driver 8.0.30. All the previous driver versions, starting from 5.3, behaved correctly.

Suppose you have 2 rows. One with all the columns filled, and the second where the TEXT columns are empty (i.e. '', not null). Then, when reading the 2nd row, the empty TEXT columns will contain the values of the FIRST row.

This occurs only if you bind the TEXT columns as SQL_BINARY. In our case we must read the text as binary, so this bug is a real pain.

Thanks!

How to repeat:
CREATE TABLE txt_tab (a int primary key, txt TEXT, longtxt LONGTEXT);
INSERT INTO txt_tab VALUES (1, 'text_1', 'longtext_1');
INSERT INTO txt_tab VALUES (2, '', '');

Now run this program. For clarity I removed error handling code etc.
:
#define	BUFF_SIZE 256
int     intBuff = 0;
SQLLEN	iIntLen = 0, iTxtLen = 0, iLongLen = 0;
char	txtBuff[BUFF_SIZE], longBuff[BUFF_SIZE];

SQLDriverConnect(hDbc, ...);
SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
SQLPrepare(hStmt, L"SELECT a, txt, longtxt FROM txt_tab", SQL_NTS);
SQLBindCol(hStmt, 1, SQL_INTEGER, &intBuff,	sizeof(intBuff), &iIntLen);
SQLBindCol(hStmt, 2, SQL_BINARY,  &txtBuff,	BUFF_SIZE,	 &iTxtLen);
SQLBindCol(hStmt, 3, SQL_BINARY,  &longBuff,	BUFF_SIZE,	 &iLongLen);

SQLExecute(hStmt);
do {
   memset(txtBuff,  0, BUFF_SIZE);
   memset(longBuff, 0, BUFF_SIZE);
   iIntLen = iTxtLen = iLongLen = 0;

   ret = SQLFetch(hStmt);
   printf("Row %d, text='%s' len=%lld, longtext='%s' len=%lld\n",
          intBuff, txtBuff, iTxtLen, longBuff, iLongLen);
while (ret == SQL_SUCCESS);

The output will look like this:

Row 1 text='text_1' len=6, longtext='longtext_1' len = 10
Row 2 text='text_1' len=6, longtext='longtext_1' len = 10

Note: the text values of row 2 are duplicate of row 1, instead of empty.

This bug causes production systems to generate wrong data and there is no workaround.

Let me emphasize that our application is used in production by many customers. Oracle recommended MySQL users to upgrade to MySQL ODBC driver 8.0.30.
Once they upgraded, the bug appeared and affected their systems without a workaround :(

I'm experiencing the same problem when using MySQL as a source in Qlik Replicate. I triend different driver versions, from 8.0.29 to 8.0.32, but all ehave the same. Please we need a fix soon as it impacts production systems.

Thank you

This issue occurs also on the newest ODBC driver version - 8.0.33, can we get an ETA for the fix of this regression?

Hello Sruli Ganor,

Thank you for the bug report.
Please upgrade to latest version and report us back if issue persist even in latest version along with test case. Thank you.

Regards,
Ashwini Patil

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I tested on the last driver version 8.4.
Unfortunately the bug is still there. :( 

Note that the bug can be avoided by adding NO_SSPS=1 to the connection string.

Thanks

This issue occurs in all 32 bit drivers after 8.0.23 including the latest driver 8.0.37.

The test case provided earlier should be sufficient. Its simple and clear. And note that the issue will also occur if the SQLBindCol API is changed from SQL_BINARY to SQL_C_CHAR.

Its the SQLPrepare that causes this. The option NO_SSPS=1 changes SQLPrepare so that SQLExecute acts like SQLExecDirect. In this case the issue does not occur. And that is NOT a workaround. Binding parameters will fail which are a requirement of performant applications.

I'll upload another repro, but its more complex and only adds an example of it working using SQLExecDirect and failing using SQLPrepare/SQLExecute.

Production systems are effected by this due to users obeying the directive of Oracle to update to the latest ODBC driver. An urgent fix is required. Its been in the system a long time. Its about time it was fixed.

C code Example of it working using SQLExecDirect & failing with SQLPrepare/SQLExecute

Attachment: MYSQLRepro.zip (application/x-zip-compressed, text), 6.01 KiB.

Clarifying the version of the driver that introduced this defect.
8.0.23 works
8.0.24 and all following versions fail.

Hello Sruli Ganor,

Thank you for the details.
Please upgrade to latest version and report us back if issue persist even in latest version along with test case. Thank you.

Regards,
Ashwini Patil

Hello,
I tested it with the lateset connector version 9.3 and it worked fine.
Thanks for fixing this bug.
In the meantime, I've found a workaroud for the bug: adding NO_SSPS=1 to the connection string.

Unfortunately, we can't yet upgrade to connector 9.x due to the open bug #117608. It's a critical bug for us. Since we haven't recieved an ETA or even documentation explaining the new behavior, we are considering moving to connectors from other vendors.
Best regards

Hello Sruli Ganor,

Thank you for the feedback.
Closing the report for now.

Regards,
Ashwini Patil