How to Fix Charset and Collation Conflicts in WordPress

What Charset and Collation Mean

A charset, or character set, determines which characters a database can store. Modern WordPress sites generally require `utf8mb4` because it supports the full Unicode character set, including emoji and characters from different languages. The older `utf8` character set in MySQL and MariaDB is not full UTF-8 and may not support four-byte characters.

Collation defines the rules used to compare and sort text: whether letter case matters, how letters with diacritics are compared, and the order in which results are returned. For example, `utf8mb4_unicode_ci` and `utf8mb4_general_ci` may produce different sorting results.

A conflict occurs when MySQL compares or combines text values with different collations and cannot reconcile them automatically. This may occur at the table level, but also in a specific column, temporary table, or plugin query.

• charset — the character set that can be stored;
• collation — the rules used to compare and sort text;
• `utf8mb4` — the recommended base charset for modern WordPress;
• using the same collation is preferable for columns that are compared with one another.

Create a Backup Before Making Changes

Converting tables changes their structure and may rebuild indexes. Before making changes, create a complete database backup and verify that it can be restored. For large sites, it is also advisable to create a copy of the files and perform the operation during a maintenance window.

If WP-CLI is available, you can create a backup with the command below. It uses the connection settings from WordPress and saves the dump to a file.

• Do not run `ALTER TABLE` on a production database without a verified dump.
• Record the current MySQL or MariaDB and WordPress versions before making changes.
• Check whether the database user has permission to alter tables.
• For a critical site, test the procedure on a copy or staging environment first.

wp db export backup-before-collation.sql

Identify WordPress Tables with Different Character Sets

Do not assume that the table prefix is always `wp_`. You can check it in the configuration file or retrieve it through WP-CLI. The query below uses the `wp_` prefix only as an example.

First, review the charset and collation of all tables in the current database. The `DATABASE()` function automatically uses the database to which the current connection is connected.

• Tables with the same charset but different collations can also cause conflicts.
• Plugins often create their own tables, which may not start with `wp_` or may use a different prefix.
• Check separately the tables that store posts, metadata, comments, taxonomies, and plugin data.

wp db query "
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;"

Check the Charset and Collation of Individual Columns

A table-level setting does not always reflect the actual structure of every column. A text column may have its own `CHARACTER_SET_NAME` and `COLLATION_NAME`, inherited from another schema or changed during an import.

For a quick check of a specific table, use `SHOW FULL COLUMNS`. This is especially important for `wp_posts`, `wp_postmeta`, `wp_comments`, `wp_terms`, `wp_term_taxonomy`, and plugin tables.

wp db query "SHOW FULL COLUMNS FROM wp_posts;"

wp db query "
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND CHARACTER_SET_NAME IS NOT NULL
ORDER BY TABLE_NAME, ORDINAL_POSITION;"

How to Find Suspicious Columns

In the results, look for text columns whose charset differs from `utf8mb4`, or columns using a different collation from the rest of the table. If the error occurs only during search or import, also check the tables created by the relevant plugin.

Do not change the column type, field length, or indexes unless necessary. The purpose of this procedure is to align the character set and collation, not to rebuild the entire schema.

• Compare not only tables but also individual text columns.
• Include third-party plugin tables and custom theme tables.
• Save the diagnostic results before making changes.

Choose a Compatible Collation

The choice of collation depends on the server version. `utf8mb4_unicode_ci` is a common and compatible option for many WordPress installations. MySQL 8 may support collations with the `0900` suffix, but these may not exist in older MySQL versions or in MariaDB. Therefore, do not move such a value between servers without checking first.

You can retrieve the list of available options directly from the server. For a site that must run across different environments, it is generally safer to choose a collation available on all target servers.

wp db query "SELECT VERSION();"
wp db query "SHOW COLLATION LIKE 'utf8mb4%';"

What to Consider When Choosing

Match the charset and collation: a collation for `utf8mb4` must belong to `utf8mb4`, not to the older `utf8` character set. If the site actively uses Ukrainian and other languages, test sorting with real data after conversion.

There is no need to mechanically change every table to the newest available collation. Compatibility with the server version, plugins, backups, and future environments is more important than simply using a newer name.

• Check whether the selected collation is supported by MySQL or MariaDB.
• Use one consistent option for related tables.
• Do not mix collations merely because they are available on the current server.

Convert Tables in Stages

To change a table's charset and collation, use `ALTER TABLE ... CONVERT TO CHARACTER SET`. The command converts the table's text columns, so it should be used carefully and tested on one table first.

The example below uses `utf8mb4_unicode_ci`. Replace it with an option supported by your server and selected after verification.

ALTER TABLE wp_posts
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

ALTER TABLE wp_postmeta
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Order of Operations and Technical Limitations

After converting the first table, check the site before moving to the next one. On large tables, the operation may temporarily block writes, consume disk space, and create additional load.

On older MySQL versions or when long indexes are present, conversion may fail because of index-length limitations. Do not shorten indexes arbitrarily. First determine which column or index caused the issue and create a separate change plan.

If the table belongs to a plugin, check its documentation or schema-update process. A forced structural change may be overwritten during the plugin's next update.

• Start with a small table or a staging copy.
• Schedule a maintenance window for large tables.
• Do not interrupt the operation without understanding its current state.
• After each group of changes, check table status and error logs.

Verify the Result After the Fix

After conversion, run the `information_schema` queries again and confirm that the required tables and text columns have the expected values. Checking only one table does not guarantee that the conflict has disappeared throughout the database.

Then test the functions that previously triggered the error: search, sorting, filters, imports, post creation, and comments. Also verify that Ukrainian characters, emoji, and text with diacritics can be stored correctly.

If the error remains, find the complete SQL query in the PHP, WordPress, or plugin logs. The problem may not be in the tables but in a query that explicitly specifies a different collation with `COLLATE`, creates a temporary table, or compares values from different sources.

• Check the homepage, administration area, and search pages.
• Create a test post containing Cyrillic text and emoji.
• Test sorting by title, name, and metadata.
• Repeat the import or synchronization that originally caused the error.
• Review the logs after testing, not only the message displayed in the browser.

Common Mistakes When Fixing the Problem

The most dangerous approach is to perform a mass collation replacement without first inventorying the database. This can hide the root cause, create index problems, or affect plugin data.

Do not confuse fixing the character encoding with repairing text that has already been corrupted. If data was imported using the wrong charset and has already been converted into characters such as `????` or an invalid sequence, changing the collation will not restore the original content. You need a correct backup or a separate data-recovery migration.

Do not edit a dump in a text editor without understanding its encoding. During export and import, check the charset of the file itself, the MySQL client settings, and the parameters of the migration tool.

• Do not use a collation that is unavailable on the target server.
• Do not change only one column if related tables remain incompatible.
• Do not remove indexes to bypass an error without a plan to restore them.
• Do not treat a successful `ALTER TABLE` execution as proof that the data is correct.
• Do not skip testing on a site copy if the database is large or critical.