SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep sql not support

[deskau]

How can this SQL statement be triggered?
I am using an Oceanbase database, which is compatible with MySQL; The same code triggered this SQL statement, but MySQL does not trigger it.

[alvinloong]

By the way, why did we use
SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep;
instead of
SELECT SLEEP(0) INTO @dummy; as mentioned in #1115

Some mysql compatible databases may support
SELECT SLEEP(0) INTO @dummy;
But does not support
SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep;

[bgrainger]

By the way, why did we use
SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep;

I did that to avoid overwriting a user-defined variable. @dummy is not a keyword or a reserved word, so it's quite plausible that someone could be using it as a variable or parameter name.

Is it the private-use area character U+E001 that's causing problems with Oceanbase? Does SELECT SLEEP(0) INTO @MySqlConnector__Sleep work as valid syntax? Or is it the whole statement that fails, no matter what the variable is named?

[bgrainger]

Looks like it's probably the PUA character that's causing problems with Oceanbase:

obclient [(none)]> select sleep(0) into @__MySqlConnector__Sleep;
Query OK, 1 row affected (0.001 sec)

obclient [(none)]> select sleep(0) into @MySqlConnectorSleep;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '?MySqlConnector?Sleep' at line 1
[172.17.0.5:2882] [2024-07-31 14:31:24.298425] [YB42AC110005-00061E8BEBAAC369-0-0]

[alvinloong]

Hi bgrainger,

Thanks a lot for your prompt response and quick test with OceanBase!

Actually I tested following SQL in OceanBase MySQL, MySQL 5.7 and MySQL 8.0, all failed with 'Unknown database' error.

SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep;

For the following test case, only the first sql with PUA character failed when running directly from mysql command line in OceanBase MySQL, MySQL 5.7 and MySQL 8.0. And the variable MySqlConnector__Sleep looks more user friendly without any worry of spectial or PUA characters.

SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep;
SELECT SLEEP(0) INTO @xxxMySqlConnectorxxxSleep;
SELECT SLEEP(0) INTO @dummy;
SELECT SLEEP(0) INTO @MySqlConnector__Sleep;

Here are the test results:

OceanBase

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [test]> SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep;
ERROR 1049 (42000): Unknown database
    -> ;
ERROR 1210 (HY000): Invalid argument
MySQL [test]> SELECT SLEEP(0) INTO @xxxMySqlConnectorxxxSleep;
Query OK, 1 row affected (0.003 sec)

MySQL [test]> SELECT SLEEP(0) INTO @dummy;
Query OK, 1 row affected (0.003 sec)

MySQL [test]> SELECT SLEEP(0) INTO @MySqlConnector__Sleep;
Query OK, 1 row affected (0.002 sec)

MySQL 5.7.32

Server version: 5.7.32-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [test]> SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep;
ERROR 1049 (42000): Unknown database 'e001mysqlconnectorue001sleep;'
    -> ;
ERROR 3061 (42000): User variable name '' is illegal
MySQL [test]> SELECT SLEEP(0) INTO @xxxMySqlConnectorxxxSleep;
Query OK, 1 row affected (0.00 sec)

MySQL [test]> SELECT SLEEP(0) INTO @dummy;
Query OK, 1 row affected (0.00 sec)

MySQL [test]> SELECT SLEEP(0) INTO @MySqlConnector__Sleep;
Query OK, 1 row affected (0.00 sec)

MySQL 8.0.31

Server version: 8.0.31 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> SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep;
ERROR 1049 (42000): Unknown database 'e001mysqlconnectorue001sleep;'
    -> ;
ERROR 3061 (42000): User variable name '' is illegal
mysql> SELECT SLEEP(0) INTO @xxxMySqlConnectorxxxSleep;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT SLEEP(0) INTO @dummy;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT SLEEP(0) INTO @MySqlConnector__Sleep;
Query OK, 1 row affected (0.00 sec)

Thanks again,

Alvin

[alvinloong]

Hi bgrainger,

Thanks to your test case, now I can know what the sql string before encoding is (by UTF-16BE) and verified on MySQL.

After encoding (could not be run directly from MySQL command line):

SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep;

Before encoding (could/should be run directly from MySQL command line):

SELECT SLEEP(0) INTO @MySqlConnectorSleep;

Here are the test results:

OceanBase

MySQL [test]> select sleep(0) into @MySqlConnectorSleep;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'MySqlConnectorSleep' at line 1

MySQL 5.7.32

MySQL [test]> select sleep(0) into @MySqlConnectorSleep;
Query OK, 1 row affected (0.00 sec)

MySQL 8.0.31

mysql> select sleep(0) into @MySqlConnectorSleep;
Query OK, 1 row affected (0.00 sec)

[alvinloong]

Hi bgrainger,

Really appreciate your help on this issue.

Is that possible to use one of the following two user friendly variables?

SELECT SLEEP(0) INTO @MySqlConnector__Sleep;
SELECT SLEEP(0) INTO @__MySqlConnector__Sleep;

So that it is more readable and can be run directly from MySQL command line without having to decode the sql. It might also help other MySQL compatible databases to avoid the same issue (it is very considerate of you to take care of so many databases ).

After all, it is a nice-to-have optimization, considering that it take some extra work and testing.

OceanBase

MySQL [test]> select sleep(0) into @MySqlConnectorSleep;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'MySqlConnectorSleep' at line 1
MySQL [test]>

MySQL [test]> SELECT SLEEP(0) INTO @MySqlConnector__Sleep;
Query OK, 1 row affected (0.002 sec)

MySQL [test]> SELECT SLEEP(0) INTO @__MySqlConnector__Sleep;
Query OK, 1 row affected (0.002 sec)

Also verified on MySQL 5.7 and MySQL 5.8.

[bgrainger]

Just FYI for anyone trying to test this:

SELECT SLEEP(0) INTO @\uE001MySqlConnector\uE001Sleep;

\uNNNN is a C# escape sequence that can be used in C# source code. You can't paste that into MySQL CLI as-is and expect it to work.

[bgrainger]

Is that possible to use one of the following two user friendly variables?

Yes, it seems reasonable to use one of those to improve server compatibility with a very low risk of colliding with a real user-defined variable.

[deskau]

I also want to ask a question, under what circumstances will this SQL statement be triggered. thank you

[bgrainger]

It will be triggered after a query has been cancelled (due to CancellationToken or CommandTimeout elapsing).

[alvinloong]

FYI

OceanBase MySQL has supported following SQL in latest version:

SELECT SLEEP(0) INTO @MySqlConnectorSleep;