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 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

[8 Oct 2015 15:02] Mathieu van Loon
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 :(
[9 Oct 2015 5:41] Chiranjeevi Battula
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
[9 Oct 2015 5:42] Chiranjeevi Battula
screenshot

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

[13 Oct 2015 23:43] Abraham Guyt
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.
[5 May 2016 12:15] Chiranjeevi Battula
http://bugs.mysql.com/bug.php?id=81323 marked as duplicate of this one.
[19 Jan 8:57] Cedric Counotte
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.
[19 Jan 17:04] Daniel Valdez
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.
[20 Jan 4:09] Bradley Grainger
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.)
[20 Jan 10:26] Abraham Guyt
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.
[20 Jan 16:20] Daniel Valdez
Posted by developer:
 
Thanks for the hint, Bradley.
[21 Jan 15:56] Christine Cole
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.