“无法初始化字符集utf8mb4"；与Windows mysql-python [英] "Can't initialize character set utf8mb4" with Windows mysql-python

问题描述

尝试从一个远程数据库连接到远程mysql数据库时出现错误 Windows 7客户端通过 python 2.7 + MySQLdb 1.2.5 + sqlalchemy 1.0.9 .这是由于最近将服务器的默认字符集更改为 utf8mb4 的结果.服务器正在运行 MySQL 5.5.50 .

I'm getting an error try to connect to a remote mysql database from a Windows 7 client via python 2.7 + MySQLdb 1.2.5 + sqlalchemy 1.0.9. This is a result of recently changing the server's default character set to utf8mb4. The server is running MySQL 5.5.50.

我这样连接:

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

错误是:

  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\\)")

服务器的my.cnf包含以下内容:

The server's my.cnf 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

我从Ubuntu客户端连接到数据库没有问题，因此我怀疑问题出在Windows客户端，而不是服务器的配置.

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

MySQL文档建议该错误消息可能是由于客户端在编译时不支持多字节字符集:

The MySQL documentation suggests the error message could be due to the client being compiled without multibyte character set support:

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

但是，由于这是Windows，我只是在下载客户端，而无法控制其编译标志.

However, as this is Windows I'm simply downloading the client and don't have control over its compilation flags.

我尝试了多种安装MySQLdb的方法:

I've tried installing MySQLdb in a variety of ways:

• 从dev.mysql.com下载并安装MySQL Connector/Python .msi
• 从pypi下载并安装MySQLdb 1.2.5 .exe
• 在Windows命令提示符下运行"pip install mysql-python"

这些结果中的每一个都会导致MySQLdb库似乎无法处理utf8mb4字符集.

Each of these results in a MySQLdb library that can't seem to handle the utf8mb4 character set.

任何帮助将不胜感激！

推荐答案

请考虑以下清单:

1. 您是否检查了 MySQL配置文件( /etc/my.cnf)?应该是:

[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 verify them via:

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)

-感谢 Mathias的博客文章

强制实施要在Python和MySQL之间使用的UTF-8:

Enfore enforce UTF-8 to be used 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()

• 感谢 Tomasz Nguyen对stackoverflow的回答
• thanks to Tomasz Nguyen's answer on stackoverflow

MySQL的官方提示关于Can't initialize character set:

此错误可能具有以下任何原因:

This error can have any of the following causes:

• 字符集是一个多字节字符集，并且您不支持客户端中的字符集.在这种情况下，您需要通过使用-DDEFAULT_CHARSET=charset_name或-DWITH_EXTRA_CHARSETS=charset_name选项运行CMake重新编译客户端.请参见第2.9.4节"MySQL源配置选项" .

• The character set is a multibyte character set and you have no support for the character set in the client. In this case, you need to recompile the client by running CMake with the -DDEFAULT_CHARSET=charset_name or -DWITH_EXTRA_CHARSETS=charset_name option. See Section 2.9.4, "MySQL Source-Configuration Options".

所有标准MySQL二进制文件均使用-DWITH_EXTRA_CHARSETS=complex进行编译，从而支持所有多字节字符集.请参见第2.9.4节"MySQL源配置选项" .

All standard MySQL binaries are compiled with -DWITH_EXTRA_CHARSETS=complex, which enables support for all multibyte character sets. See Section 2.9.4, "MySQL Source-Configuration Options".

字符集是一个简单的字符集，未编译到mysqld中，并且字符集定义文件不在客户端希望找到它们的位置.

The character set is a simple character set that is not compiled into mysqld, and the character set definition files are not in the place where the client expects to find them.

在这种情况下，您需要使用以下方法之一来解决此问题:

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

• 使用对字符集的支持重新编译客户端.请参见第2.9.4节"MySQL源配置选项" .

向客户端指定字符集定义文件所在的目录.对于许多客户，您可以使用--character-sets-dir选项.

Specify 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 character definition files to the path where the client expects them to be.

这篇关于“无法初始化字符集utf8mb4"；与Windows mysql-python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！