[Python] 在 Ubuntu 18 上無法用舊版 mysql.connector 連上 MariaDB?
因為聽說 MariaDB 和 MySQL 相容性很高,
所以這次在將專案從 MySQL 轉移到 MariaDB 時,
理所當然就沿用 Python 原本用的 mysql.connector
來連 MariaDB,
不過卻出現 Character set ‘utf8’ unsupported 錯誤:
$ python -c 'import mysql.connector; mysql.connector.connect(user="user", password="password", host="mariadb.test-mariadb.svc.cluster.local")' Traceback (most recent call last): File "<string>", line 1, in <module> File "/usr/local/lib/python3.12/site-packages/mysql/connector/pooling.py", line 287, in connect return MySQLConnection(*args, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/mysql/connector/connection.py", line 137, in __init__ self.connect(**kwargs) File "/usr/local/lib/python3.12/site-packages/mysql/connector/abstracts.py", line 1095, in connect self._open_connection() File "/usr/local/lib/python3.12/site-packages/mysql/connector/connection.py", line 544, in _open_connection self.set_converter_class(self._converter_class) File "/usr/local/lib/python3.12/site-packages/mysql/connector/abstracts.py", line 1271, in set_converter_class self.converter = convclass(charset_name, self._use_unicode) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/mysql/connector/conversion.py", line 136, in __init__ MySQLConverterBase.__init__(self, charset, use_unicode, str_fallback) File "/usr/local/lib/python3.12/site-packages/mysql/connector/conversion.py", line 59, in __init__ self.set_charset(charset) File "/usr/local/lib/python3.12/site-packages/mysql/connector/conversion.py", line 73, in set_charset self.charset_id = CharacterSet.get_charset_info(self.charset)[0] ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/mysql/connector/constants.py", line 775, in get_charset_info info = cls.get_default_collation(charset) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/usr/local/lib/python3.12/site-packages/mysql/connector/constants.py", line 746, in get_default_collation raise ProgrammingError(f"Character set '{charset}' unsupported") mysql.connector.errors.ProgrammingError: Character set 'utf8' unsupported
我們這專案是在 Ubuntu 18 上使用 Python 3.6,
稍微實驗了一下,如果用新版的 Python 3.12,
它預設裝好的 mysql-connector-python 套件就不會遇到這個問題。
當然升級 Ubuntu 與 Python 可能是最佳解,
不過相對的要升級的東西也會非常多,所以只能先找尋快速解…
試試看升級 mysql-connector-python 到最新版,
看來可能是 Python 太舊的關係,導致可用的 mysql-connector-python 最多只到 8.0.33:
$ pip install mysql-connector-python==8.2.0 Collecting mysql-connector-python==8.2.0 Could not find a version that satisfies the requirement mysql-connector-python==8.2.0 (from versions: 8.0.5, 8.0.6, 8.0.11, 8.0.12, 8.0.13, 8.0.14, 8.0.15, 8.0.16, 8.0.17, 8.0.18, 8.0.19, 8.0.20, 8.0.21, 8.0.22, 8.0.23, 8.0.24, 8.0.25, 8.0.26, 8.0.27, 8.0.28, 8.0.29, 8.0.30, 8.0.31, 8.0.32, 8.0.33) No matching distribution found for mysql-connector-python==8.2.0
試著裝 mysql-connector-python 8.0.33 與 8.0.32 都遇到了問題,
降到 8.0.31 時又出現 __future__ 的問題:
>>> import mysql.connector Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/usr/local/lib/python3.6/site-packages/mysql/connector/__init__.py", line 32, in <module> from .connection_cext import CMySQLConnection File "/usr/local/lib/python3.6/site-packages/mysql/connector/connection_cext.py", line 40, in <module> from .abstracts import MySQLConnectionAbstract File "/usr/local/lib/python3.6/site-packages/mysql/connector/abstracts.py", line 33 from __future__ import annotations ^ SyntaxError: future feature annotations is not defined
也就是說新版的 mysql-connector-python 可以解問題,
但無法在 Ubuntu 18 / Python 3.6 裝起來…
如果要改成用 Python 的 mariadb 套件的話,
也會遇到 Ubuntu 18 可用的 mariadb-connector-c 太舊,
導致 mariadb 套件裝不起來的問題…
最後在網路上找到一個解法,
手動下載並安裝較新、但可在 Ubuntu 18 上使用的 mariadb-connector-c:
curl -O https://downloads.mariadb.com/Connectors/c/connector-c-3.1.10/mariadb-connector-c-3.1.10-ubuntu-bionic-amd64.tar.gz tar xvf mariadb-connector-c-3.1.10-ubuntu-bionic-amd64.tar.gz --directory /usr --strip-components 1 echo "/usr/lib/mariadb/" > /etc/ld.so.conf.d/mariadb.conf ldconfig
這時安裝 mariadb 套件就沒問題了:
pip install mariadb==1.0.11
用 mariadb 套件去連 MariaDB 資料庫也沒問題了:
python -c 'import mariadb; mariadb.connect(user="user", password="password", host="mariadb.test-mariadb.svc.cluster.local")'
參考資料:Character set ‘utf8’ unsupported in python mysql connector