# MySQL utf8mb3 to utf8mb4: What CONVERT TO CHARACTER SET Locks, Refuses and Silently Changes

> On MySQL 8.4.11, `ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4` ran in place with writes allowed when no converted column was indexed, and copied the table when one was: 9.3 seconds on 2,000,000 rows, with an `INSERT` waiting 7.8 of them. It is refused by a foreign key on a string column (error 3780), an index that would pass 3,072 bytes (3886 or 1071), a `VARCHAR` over 16,383 characters (1074) and values the new collation treats as equal (1062). When it runs, it turns `TEXT` into `MEDIUMTEXT` and stops ignoring trailing spaces.

Source: https://schemity.com/blog/mysql-convert-utf8mb3-to-utf8mb4/

Converting a MySQL table from `utf8mb3` to `utf8mb4` is one statement, and what that statement costs depends on something it never mentions: whether any column it converts is in an index. I ran it on MySQL 8.4.11 in a throwaway Docker container on a laptop. On a 2,000,000-row table with no index on its string column, it finished in 2.8 seconds and an `INSERT` sent during it returned in 0.1 seconds. On a 2,000,000-row table with one indexed `VARCHAR`, it copied the table for 9.3 seconds and the `INSERT` waited 7.8 of them. Other tables refused the conversion outright, for four different reasons, and the ones that converted came back with a different column type and different rules for which strings are equal.

The reason to do it anyway is that `utf8mb3`, the character set MySQL long called `utf8`, stores at most 3 bytes a character. An emoji needs 4, and in strict mode, the default, the write fails:

```sql
CREATE TABLE customers (
  id   BIGINT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL,
  name VARCHAR(200) NOT NULL,
  bio  TEXT,
  UNIQUE KEY customers_code_key (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO customers (code, name) VALUES ('rocket', 'Rocket 🚀');
-- ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x9A\x80' for column 'name' at row 1
```

The `CREATE TABLE` itself already carries a warning on 8.4: `'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead` (warning 1287).

## How do you convert utf8mb3 to utf8mb4 in MySQL?

Run `ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4` on each table, because changing the table's default character set converts nothing.

The [`ALTER TABLE` documentation](https://dev.mysql.com/doc/refman/8.4/en/alter-table.html) separates the two. `CONVERT TO CHARACTER SET` changes "the table default character set and all character columns (CHAR, VARCHAR, TEXT)". `DEFAULT CHARACTER SET` changes only "the character set that is used if you do not specify the character set for columns that you add to a table later". The second one is the trap, because it succeeds and the table then reports `utf8mb4`:

```sql
CREATE TABLE notes (
  id   BIGINT AUTO_INCREMENT PRIMARY KEY,
  body VARCHAR(200) NOT NULL
) DEFAULT CHARSET=utf8mb3;

ALTER TABLE notes DEFAULT CHARACTER SET utf8mb4;
```

After it, `information_schema.tables` showed the table as `utf8mb4_0900_ai_ci`, the column was still `utf8mb3`, and `SHOW CREATE TABLE` had written the old set onto the column itself: `body varchar(200) CHARACTER SET utf8mb3 NOT NULL`. The emoji insert failed with the same error 1366. A check that reads the table's collation passes; the data has not moved.

## Does converting to utf8mb4 lock the table?

Only when a converted column is in an index: then MySQL copies the table and every write waits for the copy, and otherwise the conversion runs in place with writes allowed.

Two tables of 2,000,000 rows each show both cases. `plain` has one `VARCHAR(255)` and no index on it. `events` has a `VARCHAR(40)` with an index and a `VARCHAR(255)`, 369 MB on disk. While each conversion ran, a second session sent one `INSERT`:

| Table | `CONVERT TO CHARACTER SET utf8mb4` | `INSERT` during it | With `ALGORITHM=INPLACE, LOCK=NONE` |
| --- | --- | --- | --- |
| `plain`, no string column indexed | 2.4 s | 0.09 s | Accepted, 2.8 s |
| `events`, one string column indexed | 9.3 s | 7.8 s, returned when the copy ended | Refused, error 1846 |

On `events` a `SELECT` sent during the copy still returned in 0.10 seconds: reads go through, writes do not. The refusal reads `ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.` The manual's [online DDL table](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html) lists "Converting a character set" as an in-place operation, and that held for `plain`. It does not say that one index on a converted column takes the in-place path away, and that is the common case: a table whose only strings are unindexed is rare.

The same split holds per column. On an unindexed column, `MODIFY body VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL` took 0.09 seconds on 2,000,000 rows, and an emoji insert worked straight after. On the indexed column it was refused in place with the same error 1846. Two other directions copy the table even with no index: back from `utf8mb4` to `utf8mb3`, and from `latin1` to `utf8mb4`, where the stored bytes have to change.

So add `ALGORITHM=INPLACE, LOCK=NONE` to the statement before you run it for real. It costs nothing when the conversion can run in place, and it turns a surprise table copy into an error. The timings are from a laptop: read them as the gap between the two cases, not as what your server will take. How the other `ALTER TABLE` forms compare is in [which MySQL changes lock the table](https://schemity.com/blog/mysql-alter-table-lock-instant-inplace-copy/).

## What stops the conversion from running?

Four things: a foreign key on a string column, an index that would pass 3,072 bytes, a `VARCHAR` longer than 16,383 characters, and two values the new collation treats as one.

| Error | What MySQL said | What to do |
| --- | --- | --- |
| 3780 | `Referencing column 'customer_code' and referenced column 'code' in foreign key constraint 'orders_customer_code_fkey' are incompatible.` | Convert both tables in one session with `foreign_key_checks = 0` |
| 3886, 1071 | `The resulting size of index 'pages_url_key' would exceed the max key length of 3072 bytes.` or `Specified key was too long; max key length is 3072 bytes` | Shorten the column, index a prefix, or index a hash |
| 1074 | `Column length too big for column 'a' (max = 16383); use BLOB or TEXT instead` | Change the column to a `TEXT` type first |
| 1062 | `Duplicate entry 'Hauptstrasse' for key 'streets.streets_name_key'` | Resolve the duplicates, or name a collation that keeps them apart |

**The foreign key.** `orders.customer_code` references `customers.code`, both `VARCHAR(40)`. Converting `customers` alone failed with 3780, and so did converting `orders` alone: the two columns of a key must share a character set at every moment, and each statement would leave them apart. The manual says the same in general terms: with `foreign_key_checks` enabled, "character set conversion is not permitted on tables that include a character string column used in a foreign key constraint". Turning the check off for the session let both statements through, and the key enforced again afterwards: an order for an unknown customer failed with error 1452.

```sql
SET foreign_key_checks = 0;
ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4;
SET foreign_key_checks = 1;
```

**The index.** InnoDB keeps at most 3,072 bytes of a key and counts every character at its widest. A unique index on `pages.url VARCHAR(1000)` is 3,000 bytes in `utf8mb3` and 4,000 in `utf8mb4`, so the conversion was refused with error 3886. A plain index on the same column, and a two-column index on a `VARCHAR(767)` and a `BIGINT`, were refused with error 1071 instead. The quick fix is a prefix, `UNIQUE KEY pages_url_key (url(768))`, and the conversion then ran. But a unique prefix index compares only the prefix: two URLs that differed after character 768 could not both be stored any more, and the second insert failed with a duplicate entry error. Uniqueness had changed from "the whole URL" to "the first 768 characters". A stored hash keeps the original rule at any length:

```sql
ALTER TABLE pages DROP INDEX pages_url_key,
  ADD COLUMN url_hash BINARY(32) AS (UNHEX(SHA2(url, 256))) STORED NOT NULL,
  ADD UNIQUE KEY pages_url_hash_key (url_hash);
```

With it, the two long URLs both went in and an exact repeat was refused.

**The long column.** A `VARCHAR` can hold 65,535 bytes, which is 21,845 characters at 3 bytes each and 16,383 at 4. A `VARCHAR(20000)` column, legal in `utf8mb3`, refused the conversion with error 1074.

**The duplicate.** With no `COLLATE` clause the columns move from `utf8mb3_general_ci` to `utf8mb4_0900_ai_ci`, and the two disagree about `ß`: the old collation compares it equal to `s`, the new one to `ss`. A unique `name` column holding `Hauptstraße` and `Hauptstrasse` was valid before and refused the conversion with 1062. Plain accents are not the issue: `é` equals `e` in both.

## What does CONVERT TO CHARACTER SET change without telling you?

Two things in the table itself, the type of `TEXT` columns and how trailing spaces compare, and one outside it: a join to a table you have not converted yet stops using that table's index.

**`TEXT` becomes `MEDIUMTEXT`.** After the conversion, `customers.bio` was `mediumtext`. The manual explains it: `CONVERT TO` "changes the data type as necessary to ensure that the new column is long enough to store as many characters as the original column", and a `TEXT` column's 65,535 bytes hold fewer 4-byte characters than 3-byte ones. An ORM model that still says `TEXT` now disagrees with the database, and the next schema comparison reports a difference nobody made on purpose.

**Trailing spaces start to count.** `utf8mb3_general_ci` pads before comparing, so a unique column that held `'sql'` refused `'sql '` with a duplicate entry error. `utf8mb4_0900_ai_ci` does not pad. After the conversion the same insert was accepted, the table held both values, and `WHERE name = 'sql'` matched one row. Input that used to be caught as a duplicate is now stored as a second value.

**The unconverted side of a join loses its index.** A rollout converts tables one at a time, so for a while a `utf8mb4` table joins a `utf8mb3` one. I joined a converted `kinds` table to a 200,000-row `utf8mb3` copy of `events` on `kind`. With both sides in `utf8mb4`, `EXPLAIN` showed a `ref` lookup on `events_kind_idx`. With `events` still in `utf8mb3`, it scanned the whole index, an estimated 198,074 rows, and warned: `Cannot use ref access on index 'events_kind_idx' due to type or collation conversion on field 'kind'` (warning 1739). MySQL converts the `utf8mb3` column to compare it, and a converted column cannot be looked up in its own index. The `utf8mb4` side keeps its index, so the slow queries are the ones that look up rows in a table still waiting its turn.

## CONVERT TO CHARACTER SET or MODIFY each column: which should you use?

Use `CONVERT TO` when you want every column moved at once and accept the type changes; use `MODIFY` per column when the column types must stay exactly as they are.

| | `CONVERT TO CHARACTER SET` | `MODIFY` each column | `DEFAULT CHARACTER SET` |
| --- | --- | --- | --- |
| Converts existing columns | All of them | The ones you name | None |
| Copies the table, holding writes | If any string column is indexed | If a column you name is indexed | No |
| `TEXT` stays `TEXT` | No, it became `MEDIUMTEXT` | Yes | Yes |
| Collation | The set's default, unless you add `COLLATE` | Whatever each column names | Unchanged on existing columns |

The per-column form is the one the manual recommends when the types matter, and it kept `body TEXT` as `text` here:

```sql
ALTER TABLE articles DEFAULT CHARACTER SET utf8mb4,
  MODIFY title VARCHAR(200) CHARACTER SET utf8mb4 NOT NULL,
  MODIFY body TEXT CHARACTER SET utf8mb4;
```

It has its own cost: `MODIFY` restates the whole column. A `VARCHAR(50) NOT NULL DEFAULT 'untitled'` with a comment, modified as `VARCHAR(50) CHARACTER SET utf8mb4`, came back nullable with no default and no comment.

Decide the collation on purpose, and be careful with the one that looks safest. `CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci` keeps the old comparisons, so the `Hauptstraße` table converted without an error. But `utf8mb4_general_ci` compares every character outside the Basic Multilingual Plane as equal: a unique column that held `🚀` refused `🎉` with error 1062. For a table converted so that it can hold emoji, that is the wrong trade. `utf8mb4_0900_ai_ci` tells them apart, at the price of resolving the duplicates it finds first.

## How do you find what the conversion will hit before you run it?

Ask the catalog for four lists, the columns still in `utf8mb3`, the indexes that pass 3,072 bytes at 4 bytes a character, the foreign keys on string columns and the `VARCHAR` columns over 16,383 characters, then read the migration against the live schema.

```sql
-- 1. every column still to convert
SELECT table_name, column_name, column_type
FROM information_schema.columns
WHERE table_schema = 'shop' AND character_set_name = 'utf8mb3';

-- 2. indexes the conversion would push past 3,072 bytes (errors 3886 and 1071)
SELECT s.table_name, s.index_name,
       SUM(CASE WHEN c.character_set_name = 'utf8mb3'
                THEN IFNULL(s.sub_part, c.character_maximum_length) * 4
                ELSE IFNULL(c.character_octet_length, 8) END) AS bytes_in_utf8mb4
FROM information_schema.statistics s
JOIN information_schema.columns c
  ON c.table_schema = s.table_schema AND c.table_name = s.table_name
 AND c.column_name = s.column_name
WHERE s.table_schema = 'shop'
GROUP BY s.table_name, s.index_name
HAVING SUM(c.character_set_name = 'utf8mb3') > 0 AND bytes_in_utf8mb4 > 3072;

-- 3. foreign keys on string columns: convert both tables together (error 3780)
SELECT k.constraint_name, k.table_name, k.column_name,
       k.referenced_table_name, k.referenced_column_name
FROM information_schema.key_column_usage k
JOIN information_schema.columns c
  ON c.table_schema = k.table_schema AND c.table_name = k.table_name
 AND c.column_name = k.column_name
WHERE k.table_schema = 'shop' AND k.referenced_table_name IS NOT NULL
  AND c.character_set_name = 'utf8mb3';

-- 4. columns too long for 4 bytes a character (error 1074)
SELECT table_name, column_name, character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'shop' AND character_set_name = 'utf8mb3'
  AND data_type = 'varchar' AND character_maximum_length > 16383;
```

Query 2 counts 8 bytes for each column that is not a string, enough for integers and dates; on the test schema it returned the three indexes MySQL went on to refuse, at 4,000, 4,000 and 3,076 bytes. None of the four finds the duplicates a new collation creates. For those, rehearse the conversion on a copy of the data.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Connected to a small copy of this schema, its [schema lint](https://schemity.com/doc/schema-lint/) reported each of the three tables once, naming its `utf8mb3` string columns, with the reason: an emoji written to them is refused with error 1366. A table is one finding because it is one fix, and a `utf8mb3` column inside a `utf8mb4` table is reported on its own.

![Schemity's Lint drawer on a MySQL 8.4 diagram of customers, orders and pages, all three tables in utf8mb3: under Fails at runtime (3), one finding per table reads Table is utf8mb3, which stores at most 3 bytes a character, so an emoji written to its text columns is refused, and names its columns: bio, code, name for customers, customer_code, note for orders, and title, url for pages. Red bars on the canvas mark the three tables.](https://schemity.com/images/blog/mysql-convert-utf8mb3-to-utf8mb4/lint-utf8mb3-tables.webp) A second rule reports a key longer than InnoDB keeps, counted at the column's own character set, so a column switched to `utf8mb4` in the diagram shows the 3,072-byte problem before any migration exists.

For the migration itself, paste the file into the [impact analysis](https://schemity.com/doc/impact-analysis/). I gave it the three `CONVERT TO CHARACTER SET` statements for `customers`, `orders` and `pages`, with the two `SET foreign_key_checks` lines around the first pair, against that database with 300,000 rows loaded into `pages`. It analysed all five statements, with none left unread, and reported a table rewrite for each of the three tables with its row count and size, because each has an indexed string column; held-back writes on `pages` while the copy reads its rows, which it reports for tables from 10,000 rows up; and that changing `customers.code` reaches `orders` through `orders_customer_code_fkey`, the pair error 3780 is about. That is what it means to see the impact of every change before it runs: the copy, the wait and the key it touches are on screen while the statement is still text.

![Schemity's SQL migration drawer on the same MySQL diagram after pasting the five statements: 5 statements, 5 analysed, 0 not analysed. Rewrites the table (3) lists customers to change code with about 1 row and 32 KB on disk, orders to change customer_code with about 1 row and 32 KB, and pages to change url with about 299.2K rows and 43 MB. Holds back other statements (1) says the change holds back writes to pages while it reads about 299.2K rows. Spreads through the diagram (1) says changing customers.code reaches orders via orders_customer_code_fkey, 1 hop.](https://schemity.com/images/blog/mysql-convert-utf8mb3-to-utf8mb4/impact-convert-to-utf8mb4.webp) It does not predict the refusals from a migration file: the key limit, the long column and the duplicates still need the queries above and a rehearsal.

If the conversion is part of a larger move, the neighbouring cases are measured in [MariaDB to MySQL schema differences](https://schemity.com/blog/mariadb-to-mysql-schema-differences/), where the default collation is the first thing MySQL refuses, and in [MySQL to PostgreSQL schema conversion](https://schemity.com/blog/mysql-to-postgresql-schema-conversion/), where the character set stops being a per-column decision at all.

## Frequently asked questions

### Does `ALTER TABLE ... DEFAULT CHARACTER SET utf8mb4` convert the existing columns?

No. It changes only the character set that columns added later will get. On MySQL 8.4.11 the existing column stayed `utf8mb3`, and inserting an emoji still failed with error 1366.

### Does converting a table to utf8mb4 lose data?

Not the characters: every `utf8mb3` character is stored identically in `utf8mb4`. What can change is which values count as equal, because the default collation moves from `utf8mb3_general_ci` to `utf8mb4_0900_ai_ci`. A unique column holding both `Hauptstraße` and `Hauptstrasse` refused the conversion with error 1062, and `'sql '` with a trailing space became a different value from `'sql'`.

### Why does MySQL say the index would exceed the max key length of 3072 bytes?

InnoDB keeps at most 3,072 bytes of a key and counts each character at the character set's widest: 3 bytes in `utf8mb3`, 4 in `utf8mb4`. An index on `VARCHAR(1000)` is 3,000 bytes before the conversion and 4,000 after, so MySQL 8.4.11 refuses it, with error 3886 for a unique index and 1071 for any other. A prefix index fits, but a unique prefix index compares only the prefix; a unique index on a hash of the column keeps whole-value uniqueness.
