A correct MariaDB utf8mb4 configuration is an end-to-end agreement. The client must encode text as declared, the connection must use the intended character set, columns must store the required Unicode range, and collations must implement the comparison and sorting behavior the application expects. Changing only the server default does not convert old tables or repair text that was already misencoded.

Character-set migrations are risky because metadata and bytes can disagree. A latin1 column may contain genuine Western European text, UTF-8 bytes inserted through a broken connection, or a mixture. Blindly running ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 can preserve correct data, transform already-misencoded data again, change column types, collide unique keys under a new collation, rebuild large indexes, and lock production longer than expected.

This guide audits every layer, selects a collation from business semantics, configures new objects and connections, plans table conversions, detects duplicates and mojibake, validates indexes and stored programs, rolls out safely, and defines a recoverable rollback.

Separate character set from collation

A character set maps characters to encoded bytes. utf8mb4 can represent the full Unicode range using up to four bytes per character.

A collation defines comparison, equality, and ordering for strings in a character set. Its name can indicate characteristics such as:

  • ci: case-insensitive comparisons.
  • cs: case-sensitive comparisons.
  • ai: accent-insensitive comparisons.
  • as: accent-sensitive comparisons.
  • bin: binary-oriented comparison behavior.
  • nopad: trailing spaces are significant rather than padded for comparison.

Exact semantics depend on the specific collation and MariaDB release. Do not infer every behavior from a suffix. Test application examples on the deployed server.

These values may be equal under one collation and distinct under another:

resume
résumé
Resume
RESUME

That changes login identifiers, email uniqueness, product-code matching, search results, GROUP BY, DISTINCT, joins, and index ordering. Collation is a data rule, not display decoration.

Understand MariaDB defaults by version and package

Upstream MariaDB changed its server default from latin1 to utf8mb4 in MariaDB 11.6. Current documentation lists utf8mb4_uca1400_ai_ci as the newer upstream default. Debian and Ubuntu packages have historically applied different defaults, including utf8mb4 on releases where upstream remained latin1.

Never derive production behavior from a generic version table. Query it:

SELECT VERSION();
SHOW GLOBAL VARIABLES WHERE Variable_name IN
  ('character_set_server','collation_server',
   'character_set_database','collation_database',
   'character_set_client','character_set_connection',
   'character_set_results');

From MariaDB 10.6, utf8 normally aliases utf8mb3, a maximum three-byte Unicode encoding, unless compatibility mode changes that alias. It cannot reliably store all four-byte characters. Write utf8mb4 explicitly for a full-Unicode design.

Check aliases and supported collations on the exact server:

SHOW CHARACTER SET LIKE 'utf8%';
SHOW COLLATION LIKE 'utf8mb4%';

Do not configure a collation supported on a laptop's MariaDB 11.8 when production runs 10.11. Schema DDL, dumps, replicas, and failover nodes all need support for the selected collation.

Understand the cascade of defaults

Character set and collation cascade from server to database to table to column:

server default
  -> database default for newly created tables
  -> table default for newly created string columns
  -> explicit column character set and collation

Changing a parent default affects future objects that inherit it. It does not rewrite existing child objects.

For example:

ALTER DATABASE orders
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

This changes the database default. Existing tables and columns keep their current definitions until altered. An audit must inspect all levels rather than reporting only @@character_set_server.

Inventory schema defaults

List database defaults:

SELECT SCHEMA_NAME,
       DEFAULT_CHARACTER_SET_NAME,
       DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN
  ('mysql','information_schema','performance_schema','sys')
ORDER BY SCHEMA_NAME;

List table defaults:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_SCHEMA NOT IN
    ('mysql','information_schema','performance_schema','sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

List textual columns:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
       COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME,
       IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE CHARACTER_SET_NAME IS NOT NULL
  AND TABLE_SCHEMA NOT IN
    ('mysql','information_schema','performance_schema','sys')
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

Mixed collations can be intentional: binary API tokens may need byte-oriented equality while human names need linguistic matching. Flag mixtures for review; do not normalize automatically.

Capture SHOW CREATE TABLE for every migration candidate because Information Schema summaries do not preserve all indexes, generated columns, constraints, and comments:

SHOW CREATE TABLE orders.customers;

Audit connection character sets

In a live application session, query:

SELECT @@character_set_client,
       @@character_set_connection,
       @@character_set_results,
       @@collation_connection;

These variables serve different roles:

  • character_set_client: encoding the server expects for incoming statements.
  • character_set_connection: encoding and collation used for literals and conversions during parsing.
  • character_set_results: encoding for returned strings and error messages.
  • collation_connection: collation applied to connection literals where relevant.

Test through the same language runtime, driver version, DSN, pool, proxy, and deployment image as production. A CLI session proves only its own negotiation.

Applications should configure utf8mb4 through the connector's supported option. SET NAMES sets the main connection character-set variables together:

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

Do not execute SET NAMES once on one connection and assume the entire pool uses it. Configure negotiation during each connection establishment. Some connectors discourage manual SET NAMES because their protocol option also controls client-side encoding state.

Configure new server defaults explicitly

On Ubuntu, use an owned late-loading file such as /etc/mysql/mariadb.conf.d/z-charset.cnf:

[mariadb]
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

The example collation is broadly available, but it is not automatically the best linguistic rule. Newer UCA 14 collations can provide more current Unicode behavior on supporting releases. Select from the common denominator required by servers, replicas, clients, dump tooling, and downgrade strategy.

The default_character_set/default-character-set option is a client option, not the server's character_set_server setting. Place client defaults only in an appropriate client group:

[client-mariadb]
default_character_set = utf8mb4

Avoid global client files containing application passwords. Character-set configuration and credentials should have separate ownership.

Inspect parsed server options:

my_print_defaults server mysqld mariadb mariadbd | 
  grep -E 'character[-_]set|collation'

Restart in a controlled window and verify the runtime values. Remember: this changes defaults for new databases or objects; it does not migrate existing data.

Create new schemas and tables deliberately

Use explicit DDL:

CREATE DATABASE orders
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Create tables with an explicit default:

CREATE TABLE orders.customers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(320) NOT NULL,
  display_name VARCHAR(200) NOT NULL,
  api_token VARBINARY(32) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customers_email (email)
) ENGINE=InnoDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

VARBINARY communicates byte semantics for a token. Do not store arbitrary binary data in a text column merely because utf8mb4 accepts many characters.

When an identifier requires case-sensitive equality, use an explicitly tested collation or binary data type according to application semantics. Do not apply a case-sensitive collation to the entire database just to fix one column.

Choose a collation using business examples

Create a representative test set containing:

  • Uppercase and lowercase forms.
  • Accented and unaccented names.
  • Multiple scripts used by customers.
  • Emoji and supplementary-plane characters.
  • Combining and precomposed Unicode forms.
  • Trailing spaces where they matter.
  • Punctuation and numeric substrings.
  • Language-specific characters.

Test equality and ordering explicitly:

SELECT _utf8mb4'resume' COLLATE utf8mb4_unicode_ci
       = _utf8mb4'résumé' COLLATE utf8mb4_unicode_ci AS equal_test;

Compare collations without changing production:

SELECT value
FROM charset_lab.samples
ORDER BY value COLLATE utf8mb4_unicode_ci;

Application normalization is a separate decision. MariaDB collation does not automatically normalize every canonically equivalent Unicode sequence for all business purposes. If usernames need a stable normalized form, define it at the application boundary and store both display and lookup representations where appropriate.

Detect bytes that do not match declared metadata

Before conversion, inspect suspicious values as bytes:

SELECT id, display_name,
       HEX(display_name) AS display_name_hex,
       LENGTH(display_name) AS bytes,
       CHAR_LENGTH(display_name) AS characters
FROM orders.customers
WHERE id IN (101, 102, 103);

LENGTH() reports bytes; CHAR_LENGTH() reports characters under the declared character set. Differences are normal for multibyte text. Hex output provides evidence when diagnosing mojibake.

Common mojibake such as José suggests UTF-8 bytes were interpreted as a single-byte character set at some point. Do not run repeated text replacements. Determine:

  1. Original intended characters, if evidence exists.
  2. Bytes currently stored.
  3. Column metadata used when inserted.
  4. Client encoding declared during insertion.
  5. Every transformation already applied.

Create a verified repair expression in staging and compare byte-for-byte samples. Mixed rows may require classification or external source reconstruction; one global conversion can fix some rows and corrupt others.

Back up and prove restore before conversion

A character-set conversion rewrites data and can be destructive. Prepare:

  • Physical backup compatible with the exact MariaDB version.
  • Logical schema and data export where useful for inspection.
  • Captured routines, views, triggers, events, users, and grants.
  • Restore into an isolated server.
  • Application validation against the restored copy.
  • Measured recovery time and cutover decision point.

Do not call an untested backup a rollback. Large table conversion can change data and indexes in place; rollback may require restoring the complete table or database and reconciling writes after the backup point.

Record table size, row count, indexes, foreign keys, available disk, binary-log growth, replica capacity, and estimated DDL algorithm before scheduling.

Detect uniqueness collisions before changing collation

A new collation may consider two existing values equal. A unique index then prevents conversion or, worse, a careless cleanup discards one business record.

For a candidate email collation, test grouping explicitly in staging:

SELECT email COLLATE utf8mb4_unicode_ci AS candidate_value,
       COUNT(*) AS duplicates,
       GROUP_CONCAT(id ORDER BY id) AS row_ids
FROM orders.customers
GROUP BY email COLLATE utf8mb4_unicode_ci
HAVING COUNT(*) > 1;

This query assumes the source expression can legally use that collation. If the source character set differs, convert in a staging expression matching the planned transformation:

SELECT CONVERT(email USING utf8mb4) COLLATE utf8mb4_unicode_ci AS candidate_value,
       COUNT(*) AS duplicates
FROM orders.customers
GROUP BY candidate_value
HAVING COUNT(*) > 1;

Review every collision with the application owner. Case-insensitive email identity may allow merging, while product codes differing only by case may be separate records.

Estimate index and row impact

utf8mb4 permits up to four bytes per character. Converting from a single-byte or three-byte character set can increase maximum indexed byte length, row size, temporary disk use, redo, binary logs, and replication load.

Inventory indexed textual columns:

SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME,
       s.SEQ_IN_INDEX, s.COLUMN_NAME, s.SUB_PART,
       c.COLUMN_TYPE, c.CHARACTER_SET_NAME, c.COLLATION_NAME
FROM information_schema.STATISTICS AS s
JOIN information_schema.COLUMNS AS c
  ON c.TABLE_SCHEMA = s.TABLE_SCHEMA
 AND c.TABLE_NAME = s.TABLE_NAME
 AND c.COLUMN_NAME = s.COLUMN_NAME
WHERE c.CHARACTER_SET_NAME IS NOT NULL
  AND s.TABLE_SCHEMA = 'orders'
ORDER BY s.TABLE_NAME, s.INDEX_NAME, s.SEQ_IN_INDEX;

Modern InnoDB row formats and releases support larger index keys than old installations, but page size, row format, column length, prefix indexes, and version matter. Test DDL on a restored production copy rather than relying on a generic byte formula.

MariaDB documents that CONVERT TO CHARACTER SET may expand a text data type to preserve its character capacity. For example, converting a single-byte TEXT column to utf8mb4 can result in MEDIUMTEXT. If that schema change is unwanted, use explicit MODIFY definitions for reviewed columns and prove all values fit.

Convert one table in staging

A whole-table conversion is:

ALTER TABLE orders.customers
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

This can rebuild the table and indexes. The supported DDL algorithm and lock behavior depend on release, storage engine, and table definition. Do not promise an online operation without testing the exact DDL.

For column-by-column control:

ALTER TABLE orders.customers
  MODIFY email VARCHAR(320)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci
    NOT NULL,
  MODIFY display_name VARCHAR(200)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci
    NOT NULL;

Preserve every attribute from SHOW CREATE TABLE: nullability, defaults, generated expressions, comments, and column order where relevant. Omitting an attribute can change it.

Measure on a restored production copy:

  • DDL duration and blocking.
  • Peak temporary disk use.
  • Redo and binary-log volume.
  • Replica lag or Galera flow control.
  • CPU and storage saturation.
  • Application read/write correctness during DDL.
  • Resulting column types, indexes, constraints, and row counts.

If a table cannot tolerate the measured blocking, evaluate a proven online-schema-change approach compatible with MariaDB, foreign keys, triggers, replication, and your release. Such tools add their own triggers, copy load, cutover locks, and failure modes. Test them; do not call them zero-risk.

Plan the production migration order

A safe order is usually:

  1. Stop new encoding damage by fixing and testing every client connection.
  2. Establish explicit defaults for new schemas and tables.
  3. Restore a current backup into staging.
  4. Classify source bytes and repair known mojibake with validated transformations.
  5. Test duplicate collisions and index limits under the candidate collation.
  6. Convert low-risk tables first.
  7. Validate application behavior and replication after each batch.
  8. Convert large and critical tables in measured windows.
  9. Recreate or review stored objects whose literal metadata needs updating.
  10. Audit the entire schema again and remove temporary tooling.

Fixing clients first is important. Otherwise, correctly converted tables continue receiving incorrectly declared bytes.

For a rolling application deployment, support both old and new code only if they send equivalent encoding declarations. Do not allow half the fleet to use latin1 and half utf8mb4 against the same text columns.

Review views, routines, triggers, and events

String literals in stored programs and views inherit character-set and collation context from creation time. Changing server or database defaults does not rewrite their stored definitions.

Capture creation metadata:

SHOW CREATE VIEW orders.customer_summary;
SHOW CREATE PROCEDURE orders.find_customer;
SHOW CREATE TRIGGER orders.customers_before_insert;
SHOW CREATE EVENT orders.daily_cleanup;

Inventory routines:

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
       CHARACTER_SET_CLIENT, COLLATION_CONNECTION,
       DATABASE_COLLATION
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'orders';

Column availability varies by release and object type. Use DESCRIBE information_schema.ROUTINES when building automation.

To change literal metadata, MariaDB may require dropping and recreating the stored program or view under the intended connection settings. Preserve definers, security type, SQL mode, comments, grants, and dependencies. Test the recreation in staging.

Validate data after conversion

Validate structure:

SHOW CREATE DATABASE orders;
SHOW CREATE TABLE orders.customers;

Repeat the Information Schema inventory and confirm no unintended textual columns remain. Compare row counts and application-level invariants, not only DDL success.

Round-trip representative Unicode through the application:

CREATE TABLE orders.charset_canary (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  sample VARCHAR(200) NOT NULL
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Insert through the real application driver, then inspect:

SELECT id, sample, HEX(sample),
       LENGTH(sample) AS bytes,
       CHAR_LENGTH(sample) AS characters
FROM orders.charset_canary
ORDER BY id;

Use test strings approved for non-production validation. Include emoji, accents, combining forms, and scripts the product supports. Delete the canary table after testing:

DROP TABLE orders.charset_canary;

Also verify equality, unique constraints, sorting, pagination, case-insensitive search, exports, imports, APIs, queues, backups, replicas, and analytics consumers. A page rendering correctly does not prove uniqueness and ordering semantics.

Diagnose common errors

Incorrect string value

This often means a four-byte character reached a utf8mb3, latin1, or otherwise incompatible column, or the connection declared an incompatible character set.

Inspect the current session and column:

SELECT @@character_set_client,
       @@character_set_connection,
       @@character_set_results,
       @@collation_connection;

SELECT CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'orders'
  AND TABLE_NAME = 'customers'
  AND COLUMN_NAME = 'display_name';

Fix both connector and storage definition. Do not strip emoji as a database workaround unless the product intentionally forbids those characters.

Illegal mix of collations

MariaDB cannot determine a valid comparison between expressions with incompatible collations. Inspect each operand:

SELECT COLLATION(email), CHARSET(email), COERCIBILITY(email)
FROM orders.customers
LIMIT 1;

Check literals, parameters, columns, views, temporary tables, and joined databases. An explicit COLLATE can resolve a specific expression, but repeated query-level patches indicate schema or connection inconsistency.

Text appears as question marks

Question marks may have been substituted by a client, server conversion, export, or display layer. Once original characters were replaced by literal ? bytes, metadata changes cannot reconstruct them. Recover from a trustworthy source or backup.

Text appears double-encoded

Capture hex and reconstruct the exact transformation in staging. Do not apply CONVERT(CAST(...)) recipes from the internet without proving source bytes. Mixed data requires row classification.

Duplicate-key error during conversion

The new collation considers existing keys equal. Stop and resolve collisions with the data owner. Do not drop the unique index just to finish DDL; it may be a core business constraint.

Key too long or row too large

Review index columns, prefixes, row format, InnoDB page size, and data types. Shorten an index only after confirming query and uniqueness semantics. Hash-based lookup columns can help specific designs but introduce collision and maintenance concerns.

Sorting changed after upgrade

The server or collation version may have changed. A default shift can affect newly created objects while old columns retain earlier collations. Compare SHOW CREATE TABLE, supported collation definitions, and application tests. Specify collation explicitly instead of depending on an evolving default.

Avoid unsafe migration shortcuts

Do not:

  • Change only character_set_server and claim existing tables are converted.
  • Assume utf8 means utf8mb4 on MariaDB 10.6 or newer.
  • Run bulk ALTER TABLE without restored-copy timing and disk tests.
  • Convert mixed or mojibake data without inspecting hex bytes.
  • Drop unique indexes to bypass collation collisions.
  • Disable foreign-key checks and assume data remains valid.
  • Use BINARY casts as a universal repair for encoding errors.
  • Trust a logical dump without checking its declared character set.
  • Migrate primary and every replica simultaneously.
  • Skip connector-level round-trip tests.

Roll back realistically

DDL rollback may not mean converting back. A reverse conversion can lose characters not representable in the old set and reproduce different comparison behavior.

Define rollback before cutover:

  • Restore the pre-migration table or database from a tested backup.
  • Quiesce or capture writes so they can be reconciled.
  • Revert application connection changes only when old storage expects them.
  • Restore prior schema definitions, routines, and views.
  • Validate replicas and downstream consumers.
  • Preserve failed-migration evidence for root-cause review.

For large tables, consider a blue/green or shadow-copy migration that preserves the old table until validation. This costs storage and synchronization complexity but can provide a clearer cutback point than in-place conversion.

Production best practices

  • Configure utf8mb4 explicitly; do not rely on utf8 aliases.
  • Select collation from tested equality and ordering requirements.
  • Query actual server, database, table, column, and connection settings.
  • Fix client negotiation before converting stored data.
  • Inspect hex bytes before repairing mojibake.
  • Test uniqueness collisions and index length in advance.
  • Restore production backup into staging and measure DDL.
  • Convert in controlled batches with disk and replication monitoring.
  • Review stored objects and their creation-time metadata.
  • Validate Unicode round trips through every real connector.
  • Compare application semantics, not only row counts.
  • Keep a write-reconciliation and restore-based rollback plan.
  • Re-audit after server upgrades because defaults and collations evolve.

FAQ

Is MariaDB utf8 the same as utf8mb4?

No. On MariaDB 10.6 and newer, utf8 normally aliases utf8mb3, which cannot represent all four-byte Unicode characters. Use utf8mb4 explicitly.

Does changing character_set_server convert existing tables?

No. It changes defaults for new objects. Existing databases, tables, and columns retain their definitions until explicitly altered.

Which utf8mb4 collation should I use?

Choose one supported across the topology whose equality, accent, case, ordering, and padding behavior matches tested business rules. There is no universal choice.

Why does emoji insertion fail?

The column or connection may use a character set that cannot represent four-byte characters. Inspect both the live session variables and column metadata.

Can ALTER TABLE ... CONVERT TO CHARACTER SET change column types?

Yes. MariaDB may enlarge text types to preserve character capacity. Use explicit MODIFY when you need controlled types and have proved the data fits.

Why can a collation migration fail on a unique index?

Values previously distinct may compare equal under the new collation. Detect and resolve those business collisions before conversion.

Can changing metadata fix mojibake?

Only when the exact stored bytes and prior transformation are understood. Blind metadata changes or repeated conversions often corrupt data further.

Must stored procedures be recreated?

Creation-time connection character set and collation affect stored literals. Review SHOW CREATE; recreating under the intended settings may be required.

Conclusion

A production MariaDB utf8mb4 configuration must align client bytes, connection declarations, column storage, and comparison rules. Audit all layers, choose collation using real business examples, and stop new encoding damage before transforming historical data.

Treat conversion as a schema and data migration: inspect bytes, detect key collisions, measure DDL on a restored copy, validate every connector and stored object, and retain a tested restore path. That discipline delivers full Unicode support without turning a default change into silent corruption or broken identity rules.

Suggested Internal Links

Suggested External Sources

Similar Posts