因為聽說 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