Error
Error Code:
3937
MySQL Error 3937: Invalid Collation Specified
Description
MySQL Error 3937 indicates that an invalid or unsupported collation name was provided in an SQL statement. This error typically occurs when creating or altering databases, tables, or columns, preventing the operation from completing because the server cannot recognize the specified collation.
Error Message
Invalid collation '%s' was specified. It must be a collation name as supported by server.
Known Causes
3 known causesTypo or Misspelling
The collation name was entered incorrectly, containing a typo, incorrect casing, or an extra character, making it unrecognized by the server.
Unsupported Collation Version
The specified collation is not supported by your current MySQL server version, perhaps being too new, deprecated, or specific to another server configuration.
Collation Not Registered
The desired collation, while potentially valid, is not enabled or recognized by the specific MySQL server instance's configuration.
Solutions
3 solutions available1. Verify and Correct Collation Name easy
Ensure the collation name used in your SQL statement is a valid, server-supported collation.
1
Identify the collation name that is causing the error. This will be represented by '%s' in the error message.
2
Connect to your MySQL server and list all available collations. Execute the following SQL query:
SHOW COLLATION;3
Compare the collation name from the error message with the list of available collations. If it's misspelled or not supported, correct it in your SQL statement. For example, if you intended to use 'utf8mb4_unicode_ci' but misspelled it as 'utf8mb4_unicde_ci', correct the spelling.
4
Re-execute your SQL statement with the corrected collation name.
2. Set a Default Supported Collation easy
Modify your SQL statement to use a known, supported default collation for your character set.
1
Determine the character set you are using (e.g., 'utf8mb4', 'latin1').
2
Consult the MySQL documentation or use `SHOW COLLATION;` (as in Solution 1) to find a common and supported collation for that character set. For 'utf8mb4', 'utf8mb4_unicode_ci' is a widely used and safe choice. For 'latin1', 'latin1_swedish_ci' is common.
3
Modify your SQL statement to explicitly specify this supported collation. This might involve changing `CREATE TABLE` or `ALTER TABLE` statements, or `ALTER DATABASE` statements.
CREATE TABLE my_table (
id INT PRIMARY KEY,
name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);4
Re-execute your SQL statement.
3. Update MySQL Server Configuration for Collation Support medium
Ensure your MySQL server is configured to support the desired collations and character sets.
1
Locate your MySQL server's configuration file (e.g., `my.cnf` on Linux/macOS, `my.ini` on Windows).
2
Open the configuration file in a text editor.
3
Under the `[mysqld]` section, ensure that the `character-set-server` and `collation-server` are set to supported values. If you are trying to use a specific collation that is not being recognized, it's possible the underlying character set isn't properly configured or available.
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci4
If you are missing collations entirely, you might need to ensure the MySQL installation includes the necessary character set data. This is usually handled by the installation but can sometimes be an issue with custom builds or older versions.
5
Save the configuration file.
6
Restart the MySQL server for the changes to take effect. The command to restart varies by operating system and installation method.
sudo systemctl restart mysql # For systemd-based Linux systems
# or
net stop MySQL && net start MySQL # For Windows Services7
After restarting, re-run your SQL statement that was previously failing.