Bug #106585 mysqlx (X DevAPI) does not work properly with Russian characters
Submitted: 27 Feb 10:04 Modified: 28 Feb 9:21
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / Python Severity:S2 (Serious)
Version:8.0.27, 8.0.28 OS:Any
Assigned to: CPU Architecture:Any

[27 Feb 10:04] Sveta Smirnova
Description:
When I want to search for a string with non-English characters mysqlx corrupts part of the string.

How to repeat:
1. Create a table and insert a row into it:

mysql🐬> CREATE TABLE russian(
    -> name VARCHAR(255));
Query OK, 0 rows affected (0,03 sec)

mysql🐬> INSERT INTO russian VALUES('света');
Query OK, 1 row affected (0,00 sec)

2. Check if you can query it from the command line:

mysql🐬> SELECT * FROM russian WHERE name LIKE 'све%';
+------------+
| name       |
+------------+
| света      |
+------------+
1 row in set (0,00 sec)

3. Now try to do the same with mysqlx. First, create the test code:

import mysqlx

session = mysqlx.get_session({
    'host': 'localhost',
    'port': 33060,
    'user': 'sveta',
    'password': '', 
})

result = session.sql("select name n1, name from test.russian where name like 'све%'").execute()
#result = session.sql("select name n1, name from test.russian where name like ?").bind('све%%сс').execute()
while True:
  row = result.fetch_one()
  if row is None:
    break
  print("Name: %s" % (row[0]))

4. Run it and get error:

$ python3 test_rus.py 
Traceback (most recent call last):
  File "test_rus.py", line 11, in <module>
    result = session.sql("select name n1, name from test.russian where name like 'све%'").execute()
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/statement.py", line 616, in execute
    return self._connection.send_sql(self)
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/connection.py", line 421, in wrapper
    result = func(self, *args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/connection.py", line 1040, in send_sql
    return SqlResult(self)
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/result.py", line 1081, in __init__
    super(SqlResult, self).__init__(connection)
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/result.py", line 1056, in __init__
    super(RowResult, self).__init__(connection)
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/result.py", line 939, in __init__
    self._init_result()
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/result.py", line 951, in _init_result
    self._columns = self._connection.get_column_metadata(self)
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/connection.py", line 421, in wrapper
    result = func(self, *args, **kwargs)
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/connection.py", line 1195, in get_column_metadata
    return self.protocol.get_column_metadata(result)
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/protocol.py", line 982, in get_column_metadata
    msg = self._read_message(result)
  File "/usr/local/lib/python3.8/dist-packages/mysqlx/protocol.py", line 476, in _read_message
    raise OperationalError(msg["msg"], msg["code"])
mysqlx.errors.OperationalError: 1064: 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 ''св' at line 1

5. Now replace result = session.sql("select name n1, name from test.russian where name like ?").bind('све%').execute()
with
result = session.sql("select name n1, name from test.russian where name like ?").bind('све%%сс').execute()

and find that you can search for the string even if it does match.
[27 Feb 10:04] Sveta Smirnova
MySQL Shell in Python mode works fine:

 MySQL  cookbook  Py > \sql select name n1, name from test.russian where name like 'све%'
+-------+-------+
| n1    | name  |
+-------+-------+
| света | света |
+-------+-------+
1 row in set (0.0008 sec)
[27 Feb 10:06] Sveta Smirnova
For result = session.sql("select name n1, name from test.russian where name like ?").bind('све%').execute() I get no results and general query log contains:

2022-02-27T10:06:35.024225Z     41309 Query     /* xplugin authentication */ SELECT /*+ SET_VAR(SQL_MODE = 'TRADITIONAL') */ @@require_secure_transport, `authentication_string`, `plugin`, (`account_locked`='Y') as is_account_locked, (`password_expired`!='N') as `is_password_expired`, @@disconnect_on_expired_password as `disconnect_on_expired_password`, @@offline_mode and (`Super_priv`='N') as `is_offline_mode_and_not_super_user`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject` FROM mysql.user WHERE 'sveta' = `user` AND '%' = `host`
2022-02-27T10:06:35.025615Z     41309 Query     select name n1, name from test.russian where name like 'св'
[28 Feb 9:21] MySQL Verification Team
Hello Sveta,

Thank you for the report and feedback.
Verified as described with C/Python 8.0.28(VS 2019, Python 3.10) on Wind10.

Sincerely,
Umesh