Database keeps using utfmb4 instead of utf8

Question

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.

Answer 1

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.

Answer 2

These options should allow your server to use longer utf8mb4 indexes:-

Added to the [mysqld] section of the my.cnf file.

# Make sure we can handle UTF8mb4 indexes
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_compression_algorithm = zlib
innodb_large_prefix = 1

Hope that helps you. utf8mb4 is what the world outside of mysql/mariadb calls UTF-8 and unless you use it, any users adding emoji characters (and a lot of other ones) will fail to insert.