Unable to add version comment with semi colon

[swjain23]

Software versions MySqlConnector version: 2.1.0 Server type (MySQL, MariaDB, Aurora, etc.) and version: Azure MySQL 8.0.15 .NET version: 4.8 (Optional) ORM NuGet packages and versions:

Describe the bug Trying to add a comment which has a semi-colon as it is setting a session variable, but it is resulting in a MySQL syntax exception.

Tried both ways, but got an error:

/!80030 SET SESSION sql_generate_invisible_primary_key = 0/; DROP DATABASE IF EXISTS {0};

or

/!80030 SET SESSION sql_generate_invisible_primary_key = 0;/ DROP DATABASE IF EXISTS {0};

Exception MySqlConnector.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; DROP DATABASE IF EXISTS testdb230829_7hjcryu;

Expected behavior I expect to be able to pass a version specific comment and be able to run the above query without any syntax errors.

Additional context Add any other context about the problem here.

[bgrainger]

/*!80030 SET SESSION sql_generate_invisible_primary_key = 0*/;
DROP DATABASE IF EXISTS {0};

This is a SQL syntax error, as your statement evaluates to the following, which begins with an illegal leading semicolon:

;
DROP DATABASE IF EXISTS {0};

For the second example, are you sure it's a legal SQL command to send to MySQL? If I use the command-line client, I get the following output:

Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DELIMITER $$
mysql> /*!80030 SET SESSION sql_generate_invisible_primary_key = 0;*/
    -> DROP DATABASE IF EXISTS testdb230829_7hjcryu;
    -> $$
Query OK, 0 rows affected (0.01 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*/
DROP DATABASE IF EXISTS testdb230829_7hjcryu' at line 1
mysql>

What makes you think this is a bug in MySqlConnector?

[swjain23]

I agree that the first one would have an issue as there is a preceding semi-colon.

For the second one, I tried it via workbench and it did not complain, so I am thinking that the server does support it, it is the client that is giving a syntax error:

[bgrainger]

I performed a packet capture on MySQL Workbench sending that query to the server. The server returns an error; MySQL Workbench simply doesn't show it to you:

It's also easy to see that the SQL isn't executed: after refreshing the list of schemas, the named database isn't actually dropped.

[swjain23]

It is not supported by the server itself, it would have been a great feature to have.

Thanks for looking into it, closing the ticket now.