MySQL Bugs: #92748: MySQL Connector/ODBC linked server issue with 5.3.11 Unicode Driver

Bug #92748	MySQL Connector/ODBC linked server issue with 5.3.11 Unicode Driver
Submitted:	11 Oct 2018 11:08	Modified:	25 Jun 2021 20:45
Reporter:	A T	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.3.11	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Consider this scenario:
1) You install MySQL Connector/ODBC 5.3 on a computer with Microsoft SQL Server;
2) You create a linked server connection to any MySQL server using this Connector/ODBC;
3) You confirm that test connection made successfully;
4) You try to execute any given SQL statement using OPENQUERY that contains newline in the beginning, for example:

SELECT * 
FROM OPENQUERY(MYSQL,'
	SELECT * FROM INFORMATION_SCHEMA.TABLES
')

In this case you will receive the following error:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "
	SELECT * FROM INFORMATION_SCHEMA.TABLES
". The OLE DB provider "MSDASQL" for linked server "MYSQL" indicates that either the object has no columns or the current user does not have permissions on that object.

How to repeat:
1) Install MySQL Connector/ODBC 5.3 on a computer with Microsoft SQL Server;
2) Create a linked server connection to any MySQL server using this Connector/ODBC;
3) Confirm that test connection made successfully;
4) Try to execute any given SQL statement using OPENQUERY that contains newline in the beginning, for example:

SELECT * 
FROM OPENQUERY(MYSQL,'
	SELECT * FROM INFORMATION_SCHEMA.TABLES
')

Ensure you receive an error message.

Eliminate newline at the beginning of the SQL statement:

SELECT * 
FROM OPENQUERY(MYSQL,'SELECT * FROM INFORMATION_SCHEMA.TABLES
')

Make sure that new query executes successfully.

Suggested fix:
Ignore new line at the beginning of the query. The issue did no exists in the previous versions of Connector/ODBC driver.

Result

Attachment: 92748.png (image/png, text), 107.41 KiB.

Thank you for the bug report. Please see prior attached screenshot.

Can you please confirm that you have used version 5.3.11 to repeat?

Thank you for the feedback. I tested with 5.3.11 ANSI Driver, now testing with the 5.3.11 Unicode driver I got the error reported. Synopsis changed.

Posted by developer:
 
We no longer support .5.3 but this scenario is failing also when using 8.0 driver. However the error message is different in that case:

Msg 7347, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' for linked server 'MYSQL' returned data that does not match expected data length for column '[MSDASQL].TABLE_TYPE'. The (maximum) expected data length is 22, while the returned data length is 20.

The fix will be done for the version 8.0. of the driver.

Posted by developer:
 
The issue happens because of enum column type in INFORMATION_SCHEMA.TABLES.TABLE_TYPE.
MS SQL Server does not support ENUM same as ODBC standard.
For this reason Connector/ODBC reports the column as SQL_CHAR type. However, MS SQL expect the CHAR data to always be the same fixed length as specified in the column definition. If the actual data is shorter it must be padded with spaces.

Posted by developer:
 
The issue with ENUM and CHAR columns was fixed in the patch for bug#32537000.
The patch for this bug fixes the errors related to new line and tab symbols in UNICODE version of the driver.
By default it worked, but with disabled SSPS (NO_SSPS=1) the issue was still possible. Now this behavior is fixed.

Fixed as of the upcoming MySQL Connector/ODBC 8.0.24 release, and here's the proposed changelog entry from the documentation team:

Connector/ODBC report ENUM columns as SQL_CHAR as the ODBC standard does
not support ENUM, and MS SQL Server expects CHAR data to always be the
same fixed length as specified in the column definition. This fixes errors
related to new line and tab symbols present in the UNICODE version of the
driver. The workaround is to enable SSPS (default) by not setting
NO_SSPS=1.

Thank you for the bug report.