Migrating mysql database to a server with no full unicode support

Migrating mysql database to a server with no full unicode support

by Luis De Sousa -
Number of replies: 6

Hi! moodlers

I have a mysql database with full unicode support: (collation = utf8mb4_unicode_ci, file_format = Barracuda, row_format = Dynamic, large_prefix = true).

I need to migrate that database to a shared hosting server which does not offer full unicode support. I need to keep the utf8mb4 character set because a lot of content with emojis has been used on the site.

On this shared server I can not use Dynamic or Compressed row_format. I can use utf8mb4 with row_format = Compact, but moodle has many tables with indexes that use key columns with sizes above 191 characters, therefore, I can not import the database from an .sql file unless I change the size of those keys.

I edited the dumped .sql file and resized the keys from 255 and 200 characters to 191 characters, so I was able to restore the database on the new server with collation = utf8mb4_unicode_ci and row_format = Compact (Antelope).

I did a test installation of the site and it works perfect, but I'm not sure if I can use it on production site.

I ask: Can I use that database with the changes in the size of the index keys?. Will I have problems installing new updates or plugins?

Thanks in advance
Greetings from Venezuela

Average of ratings: -
In reply to Luis De Sousa

Re: Migrating mysql database to a server with no full unicode support

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

If your new server doesn't have full unicode support then you can't have utf8mb4 - by definition. 

So, no, you can't do that.

Average of ratings: -
In reply to Howard Miller

Re: Migrating mysql database to a server with no full unicode support

by Luis De Sousa -

Thanks, Howard.

You're right, by definition utf8mb4 is the encoding introduced by MySQL which fully supports Unicode.

Sorry, when I wrote that the server does not have full support I actually meant that it does not allow creating or altering tables to the Barracuda file_format, Dynamic/Compressed row_format and large_prefix is disabled. In fact, the server uses MySQL 5.6 and utf8mb4 was introduced by mysql from version 5.5.3.


Average of ratings: -
In reply to Howard Miller

Re: Migrating mysql database to a server with no full unicode support

by Luis De Sousa -

Continuation...

My misconception was because the moodle documentation (https://docs.moodle.org/36/en/MySQL_full_unicode_support) states that "MySQL does provide full four byte UTF-8 support, but it requires certain database settings to be configure. ", that is: innodb_file_format = Barracuda, innodb_file_per_table = 1, innodb_large_prefix.

Additionally, the environment report warning that the database is not using full UTF-8 support, shows texts like: 

"Full UTF-8 support in MySQL and MariaDB requires the Barracuda file format ..."

"For full support of UTF-8 both MySQL and MariaDB require you to change your MySQL setting 'innodb_large_prefix' to 'ON' ..."

But again, Howard, I agree with you, since the use of utf8mb4 encoding is, by definition, full unicode support, even with Antelope file format and large_prefix disabled

Attachment warning.gif
Average of ratings: -
In reply to Luis De Sousa

Re: Migrating mysql database to a server with no full unicode support

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

Specifically it's Moodle that requires Barracuda for full Unicode support with MySQL due to the column sizes.

You can use utf8mb4 with Antelope but as you've discovered indexes will need more space. For a column that's VARCHAR(255) this needs up to 3,072 bytes for any index with utf8mb4 and it's this index size which needs innodb_large_prefix to be enabled. Then innodb_large_prefix needs Barracuda which needs innodb_file_per_table.

It may be possible to translate your site's database from utf8mb4 to utf8 but that would be a lot of work to take a step backwards. It's far better to change your new server to one that has the support Moodle recommends.
Average of ratings: Useful (1)
In reply to Leon Stringer

Re: Migrating mysql database to a server with no full unicode support

by Luis De Sousa -

Thanks Leon Stringer.

"It is possible to translate the database of your site from utf8mb4 to utf8, but it would be a lot of work to take a step back."

I already tried that option and the real problem of translating the database to utf8 is that users have used emojis in many places and that migration does not eliminate only the emojis but completely disappear the rows in which there are emojis.

What do you think about using utf8_unicode_ci with Antelope file_format, changing the size of large keys indexes from 255 to 191?. Like this (see attached image):


Attachment img02.gif
Average of ratings: -
In reply to Luis De Sousa

Re: Migrating mysql database to a server with no full unicode support

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

You mean "using utf8mb4_unicode_ci with Antelope file_format" by reducing the index sizes? I don't recommend this. You'd no longer have a standard Moodle site and this could cause issues with subsequent upgrades, e.g. if a Moodle upgrade altered database objects you've modified. Also I think this just won't work. It may work for mdl_analytics_predict_samples from your example but there are tables where the indexes are for unique columns (e.g. mdl_filter_config) where you can't reduce the index size without reducing the column size too. You'd lose data if your data exceeded the smaller size and the now non-standard site could result in support issues which affect only your site.

You don't say why you need to migrate to this server, maybe the decision has been made for you or maybe there's some other issue forcing you down this path but if possible this decision should be reconsidered. The utf8mb4 recommendation was introduced in 2016 with Moodle 3.1. Trying to force a utf8mb4 Moodle site to run on Antelope is a bad idea in my opinion.

Average of ratings: -