3

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.

  • 2
    Look at the actual SQL used in the "migration." – Michael - sqlbot Apr 2 '17 at 0:24
  • Can you safely change 255 to 191 for all lengths? – Rick James Apr 2 '17 at 2:08
  • I agree with @Michael-sqlbot - check - what inside Your code, it easy can have direct command to create database/tables in utf8mb4, You can edit and replace code for proper encoding. – a_vlad Apr 2 '17 at 2:26
  • I use utf8mb4 for the complete database, however, for my keys that are strings I explicitly specify them as latin1, this is useful for char() columns where if you used utf8mb4 all characters take 4x the space – santiago arizti Aug 19 at 18:00
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.
  • it not only look like, it is exactly. Settings affected for new objects only, and only if not overrides by direct command in SQL code. – a_vlad Apr 2 '17 at 5:36
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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