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'
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!