micwho's blog

If you want to keep a secret, you must also hide it from yourself.

While I inserted some data to mysql using MySQLdb(python2.7), got an error as below:

Traceback (most recent call last):
  File "test.py", line 51, in <module>
    user(i)
  File "test.py", line 28, in user
    cursor.execute(sql)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x8F\\x8E K...' for column 'bio' at row 1") 

The sql data was encoded as utf8 byte array already with data.encode(‘utf8’), so the sql data was ok. After a few tries and searching from google, I got the point. The mysql configuration was wrong, then I changed it. And finally the error was solved finally.

The configuration file: /etc/mysql/my.cnf

System info & Software info: ubuntu 16.04 mysql Ver 14.14 Distrib 5.7.19, for Linux (x86_64)

Appended the text bellow to the configuration file

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server=utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server='utf8mb4'

PS

My database table’s encoding was ‘utf8’, and mysql utf8 encoding’s max_length is 3 bytes, but some characters(such as emoji) has 4 bytes length, the utf8mb4 encoding can hold 4 bytes length data, so that’s it!