"Unable to initialize utf8mb4 character set" using mysql-python Windows

Question

I get an error message, trying to connect to a remote mysql database from a Windows 7 client through python 2.7 + MySQLdb 1.2.5 + sqlalchemy 1.0.9 . This is the result of a recent change in the default server character set to utf8mb4 . The server is running MySQL 5.5.50 .

I connect as follows:

DB_ENGINE = sqlalchemy.create_engine("mysql+mysqldb://{user}:{pass}@{host}:{port}/{database}?charset=utf8mb4".format(**DB_SETTINGS)) Session = sqlalchemy.orm.sessionmaker(bind=DB_ENGINE) 

Mistake:

  File "C:\Applications\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 385, in connect return self.dbapi.connect(*cargs, **cparams) File "C:\Applications\Python27\lib\site-packages\MySQLdb\__init__.py", line 81, in Connect return Connection(*args, **kwargs) File "C:\Applications\Python27\lib\site-packages\MySQLdb\connections.py", line 221, in __init__ self.set_character_set(charset) File "C:\Applications\Python27\lib\site-packages\MySQLdb\connections.py", line 312, in set_character_set super(Connection, self).set_character_set(charset) sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2019, "Can't initialize character set utf8mb4 (path: C:\\mysql\\\\share\\charsets\\)") 

The my.cnf server contains the following:

 init_connect = 'SET collation_connection = utf8mb4_unicode_ci' init_connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci skip-character-set-client-handshake 

I have no problem connecting to the database from the Ubuntu client, so I suspect that the problem is with the Windows client and not with the server configuration.

The MySQL documentation suggests that the error message may be due to client compilation without multibyte character support:

http://dev.mysql.com/doc/refman/5.7/en/cannot-initialize-character-set.html

However, since it is Windows, I just load the client and have no control over its compilation flags.

I tried installing MySQLdb in various ways:

• Download and install MySQL Connector / Python.msi from dev.mysql.com
• Download and install MySQLdb 1.2.5.exe from pypi
• Running "pip install mysql-python" from a Windows command prompt

Each of these results is a MySQLdb library that cannot process the utf8mb4 character set.

Any help would be greatly appreciated!

Answer 1

Consider the following checklist:

• Have you checked the MySQL configuration file (/etc/my.cnf) ? It should be:

   [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci 

  And you can check them with:

   mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+--------------------+ | 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 | | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +--------------------------+--------------------+ 10 rows in set (0.00 sec) 

  -thanks to Mathias Blog Post

• Before using UTF-8 for use between Python and MySQL:

   # Connect to mysql. dbc = MySQLdb.connect(host='###', user='###', passwd='###', db='###', use_unicode=True) # Create a cursor. cursor = dbc.cursor() # Enforce UTF-8 for the connection. cursor.execute('SET NAMES utf8mb4') cursor.execute("SET CHARACTER SET utf8mb4") cursor.execute("SET character_set_connection=utf8mb4") # Do database stuff. # Commit data. dbc.commit() # Close cursor and connection. cursor.close() dbc.close() 

  • thanks fooobar.com/questions/840555 / ...

• MySQL official advice on Can't initialize character set :

  This error may have one of the following reasons:

  • A character set is a multibyte character set, and you do not have character set support in the client. In this case, you need to recompile the client by running CMake with the option -DDEFAULT_CHARSET=charset_name or -DWITH_EXTRA_CHARSETS=charset_name . See Section 2.9.4, “MySQL Source Settings” .

  • All standard MySQL binaries are compiled using DWITH_EXTRA_CHARSETS=complex , which allows all multibyte character sets to be supported. See Section 2.9.4, “MySQL Source Settings” .

  • A character set is a simple character set that is not compiled in mysqld, and the character set definition files are not where the client expects them to be searched.

    In this case, to solve the problem you need to use one of the following methods:

    • Recompile the character set client. See Section 2.9.4, “MySQL Source Settings” .

    • Indicate to the client the directory where the character set definition files are located. For many clients, you can do this with the --character-sets-dir option.

    • Copy the symbol definition files to the path where the client expects them.

Answer 2

I went around trying to get this to work on Windows.

The only thing that seems to work for me is to execute this after the connection is established:

 set names utf8mb4;