Error
Error Code:
1300
MariaDB Error 1300: Invalid Character String Encoding
Description
MariaDB Error 1300, "Invalid character string", occurs when the database encounters characters that are not valid for the specified character set or the context in which they are used. This typically indicates a mismatch between the character encoding of the data being processed and the character set configured for the database, table, or connection, leading to operations being rejected.
Error Message
Invalid %s character string: '%s'
Known Causes
4 known causesConnection Character Set Mismatch
The client application's character set differs from the database or table's character set, causing characters to be misinterpreted during data transmission.
Unsupported Characters in String
The string contains characters that cannot be represented or are not valid within the target character set (e.g., trying to store a Unicode emoji in a LATIN1 column).
Data Import/Export Encoding Mismatch
When importing data from external files, the file's encoding does not match the character set of the database or the import tool's assumed encoding.
Incorrect String Literal Syntax
The string literal itself might be malformed or contain unescaped characters that are interpreted incorrectly by the SQL parser, especially with multi-byte character sets.
Solutions
3 solutions available1. Correct Client Connection Encoding easy
Ensure your client application is sending data with the correct character set.
1
Identify the character encoding your client application is using. Common encodings include UTF-8, Latin1, etc.
2
Configure your client application or its connection string to explicitly set the character set to match what your database expects. For example, in many programming languages, you might set a connection option.
Example for Python with `mysql.connector`:
python
import mysql.connector
cnx = mysql.connector.connect(
user='user',
password='password',
host='host',
database='database',
charset='utf8mb4' # Or the appropriate charset
)
Example for command-line `mysql` client:
bash
mysql -u user -p -h host --default-character-set=utf8mb4 database
3
If you are using a web application, check your framework's configuration or HTTP headers for character set settings.
2. Set Database and Table Character Sets medium
Ensure your database and table schemas are configured with compatible character sets.
1
Check the current character set and collation for your database and affected tables.
sql
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';
-- For a specific table:
SHOW CREATE TABLE your_table_name;
2
Alter the database and/or table to use a compatible character set, such as `utf8mb4` for broad Unicode support, which is generally recommended.
sql
-- Alter database (requires appropriate privileges)
ALTER DATABASE your_database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
-- Alter table
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- If you need to alter a specific column:
ALTER TABLE your_table_name MODIFY your_column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3
Re-insert or update the problematic data after changing the character set. This might be necessary to properly encode existing data.
3. Configure Server-Wide Defaults medium
Set default character set and collation for new databases and tables at the server level.
1
Locate your MariaDB configuration file (e.g., `my.cnf` or `mariadb.conf.d/*.cnf`).
2
Add or modify the following lines under the `[mysqld]` section to set the server's default character set and collation. `utf8mb4` is highly recommended for modern applications.
ini
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
3
Restart the MariaDB server for the changes to take effect.
bash
sudo systemctl restart mariadb
# or
sudo service mariadb restart
4
After restarting, verify the server defaults are set correctly.
sql
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';