📋

Description

This error indicates that MySQL encountered a character set or collation name that it does not recognize or support. It typically occurs during database creation, table alteration, or when setting session variables, preventing the operation from completing.

💬

Error Message

Invalid character set '%s' was specified. It must be either character set name or collation name as supported by server.

🔍

Known Causes

3 known causes

⚠️

Typo or Misspelling

The specified character set or collation name contains a typo or is misspelled, preventing MySQL from recognizing it.

⚠️

Unsupported Character Set/Collation

The character set or collation name provided is not supported by the specific MySQL server version or its current configuration.

⚠️

Incorrect Context Usage

A collation name was used where a character set name was expected, or vice-versa, leading to ambiguity for the server.

🛠️

Solutions

3 solutions available

1. Correcting Character Set/Collation in SQL Statements easy

Identify and fix incorrect character set or collation names directly within your SQL queries or table definitions.

1

Review your SQL statements (e.g., `CREATE TABLE`, `ALTER TABLE`, `INSERT`, `SELECT` with `COLLATE` clause) for any character set or collation names that are misspelled or not recognized by your MySQL server.

2

Consult the list of supported character sets and collations on your MySQL server. You can retrieve this list using the following SQL query:

SHOW CHARACTER SET;
SHOW COLLATION;

3

Replace the invalid character set or collation name with a valid one from the list. For example, if you intended to use 'utf8mb4' but typed 'utf8m4', correct it.

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    data VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

2. Verifying and Setting Default Character Set/Collation medium

Ensure your MySQL server and database have appropriate default character set and collation settings.

1

Connect to your MySQL server using a client (e.g., `mysql` command-line client, MySQL Workbench).

2

Check the current server-wide default character set and collation:

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

3

Check the default character set and collation for your specific database:

USE your_database_name;
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

4

If the defaults are incorrect or not what you expect, you can modify them in your MySQL configuration file (`my.cnf` or `my.ini`). Locate the `[mysqld]` section and add or modify the following lines:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

5

After modifying the configuration file, restart the MySQL server for the changes to take effect.

sudo systemctl restart mysql  # For systemd-based systems
# or
sudo service mysql restart      # For older init systems

6

Alternatively, you can set the database default character set and collation using SQL (this change will persist until the next server restart or if explicitly set in the config file):

ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

3. Updating Client Connection Character Set easy

Ensure the client connecting to MySQL is using a compatible character set to avoid misinterpretation.

1

When connecting to MySQL from your application or client tool, specify the desired character set. This is often done via connection parameters or configuration settings.

2

For the `mysql` command-line client, you can use the `--default-character-set` option:

mysql -u your_user -p --default-character-set=utf8mb4

3

In programming languages, the connection string or API will have a parameter for character set. For example, in Python with `mysql.connector`:

import mysql.connector

config = {
  'user': 'your_user',
  'password': 'your_password',
  'host': 'your_host',
  'database': 'your_database',
  'charset': 'utf8mb4'
}

cnx = mysql.connector.connect(**config)

4

If you are using a tool like MySQL Workbench, check its connection settings for a 'Character Set' or 'Collation' option and set it appropriately (e.g., 'utf8mb4').

🔗

Related Errors

5 related errors

MySQL Error 3935: Invalid Character Set Specification