0

On my local machine (MacOS) I have MariaDB 10.4, Python 3.7.4 and mysqlclient 1.4.4. On remote server (FreeBSD) I have MySQL 5.2, Python 3.7.2 and mysqlclient 1.4.4.

Database is in utf8 encoding on the both machines.

My script using query with CONCAT, like:

SELECT IF(agreements.date <> '0000-00-00', CONCAT(agreements.date, ' 00:00:00 +0300'), '').....

Why on my local machine class of values in resulting tuple is always STR, but on remote server class is STR or BYTES and each column value where I used mysql's CONCAT function starts with b'...' and has BYTES class. I know that I can decode byte values (b.decode()) and leave other str values as is

data = cursor.fetchall()
l = [list(x) for x in data]
for d in l:
    for idx, vals in enumerate(d):
        if isinstance(vals, bytes):
            d[idx] = vals.decode()

but why did this happen - MySQL version??

I see two ways: decode via Python as I posted or use mysql's CONVERT <column> USING utf8 function as @Joe McKenna said. But why shouldn't I do this on a local computer I don't know :( MySQL server versions are different, but charset config is same.

Example script:

#!/usr/local/bin/python3
# coding: utf8


import MySQLdb

connection = MySQLdb.Connection(
    user='root',
    passwd='password',
    db='billing',
    host='localhost',
    charset='utf8'
)

cursor = connection.cursor()


cursor.execute("SELECT '', CONCAT(birthdate, ' TEST!!!!!') from accounts")
for d in cursor.fetchall():
    print(d)

cursor.close()
connection.close()

Output:

('', b'1958-11-11 TEST!!!!!')
('', b'0000-00-00 TEST!!!!!')
('', b'0000-00-00 TEST!!!!!')
('', b'0000-00-00 TEST!!!!!')
('', b'0000-00-00 TEST!!!!!')
('', b'0000-00-00 TEST!!!!!')
('', b'0000-00-00 TEST!!!!!')
('', b'0000-00-00 TEST!!!!!')
('', b'1950-10-09 TEST!!!!!')

UPDATE:

Result of mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Localhost:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8               |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8_general_ci    |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+

Remote server:

+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_client     | utf8            |
| character_set_connection | utf8            |
| character_set_database   | utf8            |
| character_set_filesystem | binary          |
| character_set_results    | utf8            |
| character_set_server     | utf8            |
| character_set_system     | utf8            |
| collation_connection     | utf8_general_ci |
| collation_database       | utf8_general_ci |
| collation_server         | utf8_general_ci |
+--------------------------+-----------------+
Serg
  • 109
  • 2
  • 10

1 Answers1

1

Embed the output type in all queries with 'convert' keyword like in this post:

Convert output of MySQL query to utf8

SELECT column1, CONVERT(column2 USING utf8) FROM my_table WHERE my_condition;

Or you can use the 'alter' command to change each column in the db to the datatype you want:

https://dba.stackexchange.com/questions/33365/tell-mysql-to-start-using-utf-8-encoding-without-convert-toing-it

Joe McKenna
  • 135
  • 5
  • Thanks for Your answer, but why i should convert output? My database is already in utf8, tables are also in utf8. And if I doing query without CONCAT, I get's normal data. Is CONCAT function returns binary data? If so - why on macOS it is string, not bytes. P.S. CONVERT(column USING utf8) of course works. – Serg Oct 03 '19 at 13:44
  • 1
    The results of the mysql concat function vary with the arguments datatype as follows: *** • Returns the string that results from concatenating the arguments. • Returns a nonbinary string, if all arguments are nonbinary strings. • Returns a binary string, if the arguments include any binary strings. • If the argument is numeric, it is converted to its equivalent nonbinary string form. • Returns NULL if any argument is NULL. *** adding a 'convert' on the outside of the 'concat' gives more consistency in return data type. – Joe McKenna Oct 03 '19 at 16:23