facing UnicodeDecodeError

[dhyey-shah]

I am using sqlalchemy as interface to mysqlclient to connect to mysql .
I am currently facing this issue:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0x96 in position 11: invalid start byte
...
File "MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "MySQLdb/cursors.py", line 413, in _query
    self._post_get_result()
  File "MySQLdb/cursors.py", line 417, in _post_get_result
    self._rows = self._fetch_row(0)
  File "MySQLdb/cursors.py", line 385, in _fetch_row
    return self._result.fetch_row(size, self._fetch_type)
  File "MySQLdb/connections.py", line 231, in string_decoder
    return s.decode(db.encoding)

I am using utf-8 charset for my table and column both and my connection string uses ?charset=utf8 (which in turn sets mysqlclient charset to utf8 as well).
String stored in db column is:

loans – amount one year

here, while fetching this string, mysql encodes – with latin1 encoding (ie cp1252) ( which i am not sure why )

b'loans \x96 amount one year'

But while decoding, mysqlclient tries to decode it using utf8, which throws the above error.

In our business logic, a snapshot of the DB gets created everytime which is used for querying and is then disposed. So this issue occurs randomly and gets resolved on retry (may be db snapshot issue ?). If it's db issue can anyone suggest why mysql server is defaulting back to default encoding ( latin1 ) ?

Overall, can anyone please suggest any reason for this to occur ?

My env:

python-3
SQLAlchemy==1.2.8
mysqlclient==1.3.13

[methane]

What database do you use?
What is shown by executing SHOW VARIABLES LIKE 'character\_set\_%' from python?

[dhyey-shah]

We us mysql db
Following is the result of the query you asked to execute from python:

('character_set_client', 'utf8mb4')
('character_set_connection', 'utf8mb4')
('character_set_database', 'latin1')
('character_set_filesystem', 'binary')
('character_set_results', 'utf8mb4')
('character_set_server', 'utf8mb4')
('character_set_system', 'utf8')

[dhyey-shah]

@methane further on this, we added logs on production to check what charset is server returning. We saw that for above variables 90% of times we got utf8 only, but 10% times we are getting latin1. I am not sure why is this random behaviour, is this server side issue or client side.

results where we got utf8:

'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'latin1'
'character_set_server', 'utf8mb4'
'character_set_results', 'utf8'
'character_set_filesystem', 'binary'

results where we got latin1:

'character_set_client', 'latin1'
'character_set_connection', 'latin1'
'character_set_database', 'latin1'
'character_set_server', 'utf8mb4'
'character_set_results', 'latin1'
'character_set_filesystem', 'binary'

Connection code:

db_conn = MySQLdb.connect('host',
                                  'user',
                                  'pwd',
                                  'db',
                                  3306, use_unicode=True, charset='utf8')
db_conn.autocommit(True)
cursor = db_conn.cursor()
cursor.execute('Query')
cursor.close()

I also checked the global charset variables, thinking that due to some reason server might be defaulting to global variables, but that too doesnt seem to be the case.

mysql> show global variables like 'character\_set\_%%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8    |
+--------------------------+---------+

Any thoughts on this ?

[methane]

Is it really random? Doesn't it happen only on specific binary or host?

Anyway, mysqlclient==1.3.13 is toooo old.
mysqlclient>=2.1.0 may help you by sending SET NAMES at first.

set_character_set() sends SET NAMES query always. This means all new connections send it too. This solves compatibility issues when server and client library are different version. (#509)

[dhyey-shah]

Is it really random?

I think so, on retry it works with no error.

Doesn't it happen only on specific binary or host?

what do you mean by binary or host ?

Anyway, mysqlclient==1.3.13 is toooo old.
mysqlclient>=2.1.0 may help you by sending SET NAMES at first.

yes, this is part of very old code, and we are planning to update it.

[dhyey-shah]

@methane we were not able to update to mysqlclient 2.1.0. Instead we manually executed SET NAMES explicitly on every connection creation. This should have worked right ?

We are still facing the same issue, so we added logs in production code to get the result of mysql charset returned and we observed that it was random between latin1, utf8 and utf8mb4.

Also few more details:

• we use proxysql in between.
• mysql server version= 8
• mysql client version= 5

I knew a case which was related to mysql server and client version out-of-sync leading to this issue (from mysql docs), but this does't seem the case. We are not able to figure out why its random. Any idea if its related to proxysql or anything you can point to? or maybe due to server load ?

[methane]

@methane we were not able to update to mysqlclient 2.1.0. Instead we manually executed SET NAMES explicitly on every connection creation. This should have worked right ?

It would be good workaround.

We are still facing the same issue, so we added logs in production code to get the result of mysql charset returned and we observed that it was random between latin1, utf8 and utf8mb4.

How did you get "mysql charset returned"?

I knew a case which was related to mysql server and client version out-of-sync leading to this issue (from mysql docs), but this does't seem the case. We are not able to figure out why its random. Any idea if its related to proxysql or anything you can point to? or maybe due to server load ?

I don't know how proxysql works. But my guess is:

• Connection created by proxysql uses latin1 at first.
• You send "SET NAMES utf8". After that, the connection start using utf8.
• You send "SET NAMES utf8mb4" too. After that, the connection start using utf8mb4.

Since proxysql pools connection to MySQL, you see random charset.

[methane]

See sysown/proxysql#2289 (comment) too.