[ODBC-399] Executable Comment Syntax which results in empty command gives 'You have an error in your SQL syntax' Created: 2023-11-01  Updated: 2023-12-12  Resolved: 2023-11-30

Status: Closed
Project: MariaDB Connector/ODBC
Component/s: General
Affects Version/s: 3.1.19
Fix Version/s: 3.1.20, 3.2.2

Type: Bug Priority: Major
Reporter: Tomas Vladik Assignee: Lawrin Novitsky
Resolution: Fixed Votes: 0
Labels: regression


 Description   

We use query with executable comments like this

/*!80019 SELECT * FROM information_schema.applicable_roles ORDER BY host, user */

It worked, when we were using mariadb-connector-odbc-3.1.16-win32.
It returned for MariaDB empty result.

After upgrade ODBC to version 3.1.19
we get for the same query error like this

 Error Message:
 
   System.Data.Odbc.OdbcException : ERROR [42000] [ma-3.1.19][11.1.2-MariaDB-1:11.1.2+maria~ubu2204]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
 
  Stack Trace:
 
     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
 
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
 
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
 
   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
 
   at System.Data.Odbc.OdbcCommand.ExecuteReader()



 Comments   
Comment by Lawrin Novitsky [ 2023-11-22 ]

Thank you for your report.

Is it like you are sending to the server this comment only or is it coming along with a query? Looks like former, but just to be clear.

Comment by Lawrin Novitsky [ 2023-11-22 ]

I could not repeat your problem with version 3.1.19(both 32 and 64b). I tried to execute the query consisting from you comment only. But I tried using C program. I'd need some more information how to repeat it - either Idid something wrong, or maybe some connection string option affects it(unlikely though) - then I'd need your connection string/DSN settings w/out sensitive information. But the best way would be to provide ODBC trace for execution of that part of your application.

Comment by Lawrin Novitsky [ 2023-11-24 ]

tomas.vladikCould you please provide some comments?

Comment by Tomas Vladik [ 2023-11-24 ]

My issue was when called from .NET app.
The query is exactly just

/*!80019 SELECT * FROM information_schema.applicable_roles ORDER BY host, user */

So it uses executable comments - it runs the command on MySQL 8.0.19 and newer, on older MySQL and MariaDB it results into empty command.

Comment by Lawrin Novitsky [ 2023-11-28 ]

tomas.vladik Could you please provide ODBC Trace of the execution? Also, what options do you use when connect? I tried with multistatement enabling option, as you asked questions about it in other ticket. I tried prepare+execute along with direct executions, and could not repeat it. I need something more to be able to (repeast and) fix it.

Comment by Lawrin Novitsky [ 2023-11-30 ]

Other ticket, where also .Net is used and 3.1.16 named last good version, gave me the idea, and I could repeat this issue.
The problem is seems to be that query timeout is set for OdbcCommand. That causes query modification since probably 3.1.17. If there is no query, but a comment only, modification causes syntax error.
This(that query modified) is also possible with SQL_ATTR_MAX_ROWS set, but this feature is in the driver since forever, I think CommandTimeout, or more precisely SQL_ATTR_QUERY_TIMEOUT is the reason for this particular ticket. But the patch fixes both cases.
You can verify if CommandTimeout is really the reason here by setting it to 0.
If it's not - you can re-open the ticket, but I sort of believe, that it is

Comment by Tomas Vladik [ 2023-12-12 ]

I can confirm, that with mariadb-connector-odbc-3.1.20-win32.msi is not the issue reproducible.

Comment by Lawrin Novitsky [ 2023-12-12 ]

Thank you for the confirmation!

Generated at Thu Feb 08 03:28:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.