Character set 'utf8' unsupported in python mysql connector

Question

I'm trying to connect my database to a python project using the MySQL connector.

However, when using the code below,

import mysql.connector

mydb =  mysql.connector.MySQLConnection(
  host="localhost",
  user="veensew",
  password="%T5687j5IiYe"
)

print(mydb)

I encounter the following error：

mysql.connector.errors.ProgrammingError: Character set 'utf8' unsupported

I tried to find out why this is happening and I'm getting the same error always.

MySQL Connector version - 8.0.30

I'd appreciate any help. Thank you in advanced!

Woermer

change your password to take a test.I guess your password with % like a unicode. – Penta

@Penta I tried changing the password, but it didn't work... – Woermer

Answer 1

I ran into the same issue. There were apparently some changes in version 8.0.30 to the way utf8_ collations are handled (see MySQL Connector release notes). I installed version 8.0.29 which fixed the issue for me.

pip3 install mysql-connector-python==8.0.29

Seaquake

I would not recommend this... You are going to be stick in version, this will be source of other problems later. – Haman

Doing this changed my error from "mysql.connector.errors.ProgrammingError: Character set 'utf8' unsupported" to "1115 (42000): Unknown character set: 'utf8mb4'" – Glaswegian

Thank you. This worked for me as I am on a slightly older 8.0.32 version on MySQL – Winglet

Answer 2

If you are using a MySQL connector to connect a MariaDB server, you have to switch to the correct connector:

1. install MariaDB connector:

$ pip3 install mariadb

2. change your code to add import mariadb and mariadb.connect. After this step your code should be as the following:

import mariadb
    
mydb =  mariadb.connect(
  host="localhost",
  user="veensew",
  password="%T5687j5IiYe"
)

print(mydb)

And voila :)

/!\ Do not downgrade to an older MySQL connector, some other day you will have more problems...

Haman

The only downside is that the MariaDB connector only supports Python 3.7+ – Loeffler

that is the rigth way to do it :) Thanks! – Claro

Also, the MariaDB connector doesn't seem to be available on Amazon Linux. – Archdiocese

Answer 3

I thought I'd pop in my two cents here. I could be wrong however...

This error I believe will not appear to occur on MYSQL versions higher than 5.5.3, this is because the utf8mb4 character set was introducted.

https://downloads.mysql.com/docs/mysql-5.5-relnotes-en.pdf

The utf8mb4 character set has been added. This is similar to utf8, but its encoding allows up to four bytes per character to enable support for supplementary characters.

However if you are running MySQL versions lower than 5.5.3 The Python connector version (8.0.30) will try to alias utf8 to utf8mb4 (which obliviously doesn't exist yet for versions less than 5.5.3) as stated in its release notes.

https://dev.mysql.com/doc/relnotes/connector-python/en/news-8-0-30.html

Changes in MySQL Connector/Python 8.0.30 (2022-07-26, General
Availability)

..This also makes utf8 an alias to utf8mb4. ..

Because of this alias issue, the following error will occur

mysql.connector.errors.ProgrammingError: Character set 'utf8' unsupported

What the error really means I believe is

mysql.connector.errors.ProgrammingError: Character set 'utf8mb4' unsupported

For fixing this issue I'd say wait on a previous version as suggested by the answer above or upgrading mysql above 5.5.3 (although not really an option for most people).

Seraphim

Same thing happened to me using 8.0.30 version and not 8.0.29 but the MySQL version was 5.6 – Unimproved

Answer 4

In MySQL connector 8.0.30, utf8 character set is renamed to utf8mb3, and utf8 now is an alias for utf8mb4. Try this just in case:

import mysql.connector

mydb =  mysql.connector.MySQLConnection(
    host="localhost",
    user="veensew",
    password="%T5687j5IiYe",
    charset="utf8mb3"
)

print(mydb)

Capillaceous

Answer 5

You can try to encode your password with ("%T5687j5IiYe").encode("utf-8") .

Gisarme

Tried this, but not worked it seems its with the connector I think – Woermer

Answer 6

Looks like this was finally fixed in v8.0.32

https://github.com/mysql/mysql-connector-python/blob/8.0.33/CHANGES.txt#L39

Loeffler