| Bug #106585 | mysqlx (X DevAPI) does not work properly with Russian characters | ||
|---|---|---|---|
| Submitted: | 27 Feb 2022 10:04 | Modified: | 15 Nov 2022 23:30 |
| Reporter: | Sveta Smirnova (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / Python | Severity: | S2 (Serious) |
| Version: | 8.0.27, 8.0.28 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[27 Feb 2022 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 2022 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 2022 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
[15 Nov 2022 23:30]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Connector/Python 8.0.32 release, and here's the proposed changelog entry from the documentation team: Russian characters were not handled correctly by the c-ext version's X DevAPI driver. String values are now encoded to their byte string representation before being sent to protobuf. Thank you for the bug report.
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.