1

Background

There is a quite-big MySQL 8.0 server with more than 500 databases. The default character set/collation for the server is 'utf8mb3'. 'utfmb3' is listed as deprecated on the official MySQL website, and is expected to be removed in a future release (Source: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html). It is recommended to switch to full utf8 encoding with 'utf8mb4'.

This server has been running for quite some years on version 8.0, and a few years ago a rule was set to create all new databases on 'utf8mb4'. This leaves only the initial 200 databases, which are still sitting on the old encoding ('utf8mb3').

Problem

It is mentioned on the linked documentation, that 'utf8mb3' will be removed in a future MySQL version. As we would want to keep up-to-date with MySQL versions with our database, I am wondering how this will affect our "old" databases (old means using 'utf8mb3').

I have already outlined a few strategies for automatically migrating those databases to the new encoding, however:

  • They won't really benefit from the new encoding - no issues were seen in the last years.
  • Automating such a big thing is quite risky due to obvious reasons. Some columns will accept less characters than before, which will certainly cause problems in some of those databases, which will need manual modification.

I would like to avoid having to migrate those databases if it is not absolutely needed, and this is mainly the thing I could not find yet about this in my research on this topic.

As it is mentioned that 'utf8mb3' will be removed in a future release, what will this mean for databases with such encoding? Will there be some sort of a fallback solution for them and will they be able to still function normally? As mentioned, all new databases are using the new encoding - this is only for supporting the 'old' ones, which I would like to avoid having to migrate due to the issues this can cause.

3
  • "Some columns will accept less characters than before" -- What are you referring to? Most (all?) issues like that were solved in 5.7.
    – Rick James
    Commented Jul 19, 2023 at 14:57
  • Do check SHOW CREATE TABLE to verify that the columns are utf8mb4. That is, the Database and Table being changed is not sufficient.
    – Rick James
    Commented Jul 19, 2023 at 15:45
  • I have verified that the new databases are utf8mb4 (tables and columns included). Regarding your first question, this is when you have for example a total character size of all columns of 21844 characters (VARCHAR columns in our case). If you are using the old encoding, this is 21844*3 = 65532 bytes (under the limit), however with the new encoding it is 21844*4 = 87376 bytes (over the limit). The latest means you have to either transfer columns to TEXT or BLOB, our shorten their size. Doing it automatically it's kind of hard to detect. Commented Jul 20, 2023 at 6:26

1 Answer 1

1

TEXT and BLOB columns are limited to 2^16 bytes. If you already have 21844 utf8mb3 characters in a TEXT column, that will continue to take under 65536 bytes after converting to utf8mb4.

Notes on the example below:

  • TINYTEXT is limited to 2^8 bytes.
  • Note how 255 letters worked, but 256 failed.
  • The Euro sign is a 3-byte character in both utf8mb3 and utf8mb4.
  • Note how 85 Euro signs worked, but 86 failed.
  • All rows are intact after the ALTER.

Set up:

mysql> create table ttext ( tt tinytext character set utf8mb3 );
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                     |
+---------+------+---------------------------------------------------------------------------------------------+
| Warning | 1287 | 'utf8mb3' is deprecated and will be removed in a future release. Please use utf8mb4 instead |
+---------+------+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO ttext (tt) VALUES (REPEAT('a', 255));
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO ttext (tt) VALUES (REPEAT('b', 256));
ERROR 1406 (22001): Data too long for column 'tt' at row 1

mysql> INSERT INTO ttext (tt) VALUES (REPEAT('€', 85));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO ttext (tt) VALUES (REPEAT('€', 86));
ERROR 1406 (22001): Data too long for column 'tt' at row 1

mysql> SELECT * FROM ttext;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt                                                                                                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| €€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE ttext\G
*************************** 1. row ***************************
       Table: ttext
Create Table: CREATE TABLE `ttext` (
  `tt` tinytext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
1 row in set (0.00 sec)

Convert:

mysql> ALTER TABLE ttext CONVERT TO CHARSET utf8mb4;
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE ttext\G
*************************** 1. row ***************************
       Table: ttext
Create Table: CREATE TABLE `ttext` (
  `tt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SELECT * FROM ttext;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt                                                                                                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| €€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€€                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 
2
  • Has any timeline or specific release been targeted for the removal?
    – Aaron
    Commented Aug 18, 2023 at 16:31
  • @Aaron - No. And Oracle is specifically tight-lipped about "target releases". I would guess 8.1. But again. I have no clue of when that might happen.
    – Rick James
    Commented Sep 13, 2023 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.