MySQL Database Using utfmb4 Instead of utf8 – How to Fix

encodingmariadbMySQL

Using MariaDB 10.1. The default encoding is utf8mb4, but I have an existing database that uses utf8.

So in order to re-use the same DB, I would have to keep using utf8 for now. So I set the server variables to the following in my /etc/mysql/my.cnf:

[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

To ensure it's working, I checked the following:

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_client     | utf8            |
| character_set_connection | utf8            |
| character_set_database   | utf8            |
| character_set_filesystem | binary          |
| character_set_results    | utf8            |
| character_set_server     | utf8            |
| character_set_system     | utf8            |
| collation_connection     | utf8_general_ci |
| collation_database       | utf8_unicode_ci |
| collation_server         | utf8_unicode_ci |
+--------------------------+-----------------+

However, everytime I run migration with new tables and columns, they are created with encoding: utf8mb4, collation: utfmb4_general_ci, which further throws the Specified key was too long; max key length is 767 bytes error because I have column with varchar(255) which needs to be indexed.

Have been working on this for hours and couldn't find anything else to try. Please advise. Thank you.

Best Answer

So it looks like when the server variables are set, they don't change the database that had already been created before. So what I did was to:

  1. Set the server variables as shown in the question.
  2. Verify that they are now as expected as shown in the question.
  3. Recreate the database again, and check that the database encoding and collation is correct.
Related Question