MySQL Bugs: #78760: Using tabs for whitespace results in "you have an error in your SQL syntax..."

Bug #78760	Using tabs for whitespace results in "you have an error in your SQL syntax..."
Submitted:	8 Oct 2015 15:02	Modified:	21 Jan 2022 15:56
Reporter:	Mathieu van Loon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.9.6, 6.9.7	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Queries that are formatted using newlines (CRLF) and tabs yield "you have an error in your SQL syntax...". Downgrading to version 6.9.5 of the MySQL connector.NET resolves the issue.

The issue appeared in a large codebase that has been in production for many years and in simple queries of the form SELECT x FROM table WHERE y=z AND j=k. In these queries all whitespace are tabs and newlines.

Bug marked as serious because we cannot use the latest version of the connector.

How to repeat:
Construct a query in the form SELECT\t*\r\n\t\tFROM\tSomeTable\r\n\tWHERE\tetcetera.

It appears to be important that all whitespace is newlines or tabs (no spaces). Unfortunately I have not been able to construct a good generic example of the bug, hopefully someone else can provide one. It also seems that the bug does not appear in all queries that follow the above pattern :(

Hello  Mathieu van Loon,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.6, 6.9.7 and working in 6.9.5 version.

Thanks,
Chiranjeevi

screenshot

Attachment: 78760.PNG (image/png, text), 98.90 KiB.

What doesn't help is that reverting back to the still working 6.9.5 version cannot be done using the MySql Installer for Windows (failed download), and requires a manual download from the archives.

This bug seems to occur when using a parametrised query.

http://bugs.mysql.com/bug.php?id=81323 marked as duplicate of this one.

Please ignore my last comment, migrating from MariaDB shows that having a table named 'groups' needs special handling on MySQL (`groups` works but not without those special quotes). 
Damn, I have to migrate an entire DB (duplicated for 200+ clients) built with this kind of problem, hopefully columns are not affected by this problem.

Posted by developer:
 
Hi Cedric Counotte,

So you're hitting the same issue in v8.0.27? Can you gave us a code snippet so we could reproduce the bug? This can be very helpful for us.

There's a repro for this bug in the MySqlConnector integration tests: https://github.com/mysql-net/MySqlConnector/blob/7fed46fcc83991cc2fd3adf3464ea80705dc1975/...

(It serves as a verification that this bug is fixed in MySqlConnector.)

Some years ago I just switched to the better and free MySQLConnector library: https://mysqlconnector.net/ which doesn't have this quirky bugs; much faster as well and native async support.

I would recommend other to do the same.

Posted by developer:
 
Thanks for the hint, Bradley.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.29 release, and here's the proposed changelog entry from the documentation team:

If a SELECT statement was formatted with new lines or tabs, then the related
characters (\n and \t) were ignored during the query analysis and
Connector/NET returned a syntax error.

Thank you for the bug report.