MySQL full unicode support - Revision history

Leonstr: /* UTF-8 */ Added note about utf8 in recent MySQL/MariaDB versions

2023-08-07T10:43:22Z

UTF-8: Added note about utf8 in recent MySQL/MariaDB versions

← Older revision		Revision as of 10:43, 7 August 2023
Line 12:		Line 12:
	To summarise:		To summarise:

	* Fresh installs of Moodle ~~3.1.5 and 3.2.2 onwards~~ will use utf8mb4 by default, if the database server is configured appropriately (see below).		* Fresh installs of Moodle will use utf8mb4 by default, if the database server is configured appropriately (see below).
	* Sites upgrading to Moodle 3.1.5 or 3.2.2 can use the script to update to utf8mb4. In Moodle 3.3~~, 3.4 and 3.5~~ a warning will show that the database isn't using full UTF-8 support and suggest moving to 'utf8mb4_unicode_ci'~~, but you~~ may choose to keep using 'utf8_*'.		* Sites upgrading to Moodle versions beyond 3.1.5 or 3.2.2 can use the script to update to utf8mb4. In Moodle 3.3 onwards a warning will show that the database isn't using full UTF-8 support and suggest moving to 'utf8mb4_unicode_ci'.
			* You may choose to keep using 'utf8_*' unless you have MySQL 8.0.28 or later, or MariaDB 10.6.1 or later, in which case you must update to utf8mb4 (because utf8 was renamed to utf8mb3 in these versions, see MDL-74616).

	===File format===		===File format===

Tim@horizoneducationnetwork.org: /* UTF-8 */clean up, typos fixed: eg. → e.g.

2022-11-03T15:55:57Z

UTF-8: clean up, typos fixed: eg. → e.g.

← Older revision		Revision as of 15:55, 3 November 2022
Line 2:		Line 2:
	==UTF-8==		==UTF-8==

	UTF-8 is a character encoding that most websites use. It encodes each of the 1,112,064 valid code points. To store all of this information, four bytes is required. The most popular values are in the three byte region. MySQL by default only uses a three byte encoding and so values in the four byte range (eg. Asian characters and Emojis) can not be stored. Any attempt to enter a text that contains four byte characters will result in a Moodle database error.		UTF-8 is a character encoding that most websites use. It encodes each of the 1,112,064 valid code points. To store all of this information, four bytes is required. The most popular values are in the three byte region. MySQL by default only uses a three byte encoding and so values in the four byte range (e.g. Asian characters and Emojis) can not be stored. Any attempt to enter a text that contains four byte characters will result in a Moodle database error.

	MySQL does provide full four byte UTF-8 support, but it requires certain database settings to be configured. From version 3.3 on Moodle uses full UTF-8 for both MySQL and MariaDB by default. Existing databases will still run with partial support, but it is recommended to move over to full support.		MySQL does provide full four byte UTF-8 support, but it requires certain database settings to be configured. From version 3.3 on Moodle uses full UTF-8 for both MySQL and MariaDB by default. Existing databases will still run with partial support, but it is recommended to move over to full support.

Leonstr: /* Steps to upgrade */ Changed `to , added semicolons to SQL, minor capitalisation changes`


2021-07-23T19:11:33Z
Steps to upgrade:  Changed <code> to <syntaxhighlight>, added semicolons to SQL, minor capitalisation changes

				
				
				
				
				
				← Older revision
				Revision as of 19:11, 23 July 2021
				
Line 47:
Line 47:






<code> <syntaxhighlight lang="ini">
[client] [client]
default-character-set = utf8mb4 default-character-set = utf8mb4
Line 62:
Line 62:
[mysql] [mysql]
default-character-set = utf8mb4 default-character-set = utf8mb4
</code> </syntaxhighlight>
* Restart your MySQL server. * Restart your MySQL server.
* Run the CLI script to convert to the new Character set and Collation (requires Moodle 3.1.5, 3.2.2 or newer):   * Run the CLI script to convert to the new character set and collation (requires Moodle 3.1.5, 3.2.2 or newer):  
<pre> <pre>
$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci $ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
Line 74:
Line 74:
NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data. NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data.



* Adjust the $CFG->dboptions Array in your '''config.php''' to make sure that Moodle uses the right Collation when connecting to the MySQL Server:   * Adjust the $CFG->dboptions array in your '''config.php''' to make sure that Moodle uses the right collation when connecting to the MySQL Server:  
<pre> <pre>
$CFG->dboptions = array( $CFG->dboptions = array(
Line 83:
Line 83:
</pre> </pre>



If you only have access to the database command line (or something like phpmyadmin) you can try the following sql commands: If you only have access to the database command line (or something like phpMyaAdmin) you can try the following SQL commands:
<code> <syntaxhighlight lang="sql">
SET GLOBAL innodb_file_format = barracuda SET GLOBAL innodb_file_format = barracuda;



SET GLOBAL innodb_file_per_table = 1 SET GLOBAL innodb_file_per_table = 1;



SET GLOBAL innodb_large_prefix = 'on' SET GLOBAL innodb_large_prefix = 'on';
</code> </syntaxhighlight>



* Try adding some Emojis (e.g. 😂💩) to your Moodle site to verify that the upgrade was successful. * Try adding some Emojis (e.g. 😂💩) to your Moodle site to verify that the upgrade was successful.



Justineuro: added " = true" to "innodb_large_prefix"; mysql fails without setting it to true
2020-08-06T04:44:34Z
added " = true" to "innodb_large_prefix"; mysql fails without setting it to true

				
				
				
				
				
				← Older revision
				Revision as of 04:44, 6 August 2020
				
Line 54:
Line 54:
innodb_file_format = Barracuda innodb_file_format = Barracuda
innodb_file_per_table = 1 innodb_file_per_table = 1
innodb_large_prefix innodb_large_prefix = true



character-set-server = utf8mb4 character-set-server = utf8mb4



Brugger at 21:31, 3 May 2020
2020-05-03T21:31:10Z


				
				
				
				
				
				← Older revision
				Revision as of 21:31, 3 May 2020
				
Line 35:
Line 35:
** On Linux based systems you will want to alter my.cnf. This may be located in '/etc/mysql/'. ** On Linux based systems you will want to alter my.cnf. This may be located in '/etc/mysql/'.
** Make the following alterations to my.cnf: ** Make the following alterations to my.cnf:

Important Change; InnoDB: The following InnoDB file format configuration options were deprecated in MySQL 5.7.7 and are now removed:

    innodb_file_format

    innodb_file_format_check

    innodb_file_format_max

    innodb_large_prefix


<code> <code>
[client] [client]



Anderson.blaine at 23:44, 2 March 2020
2020-03-02T23:44:26Z


				
				
				
				
				
				← Older revision
				Revision as of 23:44, 2 March 2020
				
Line 56:
Line 56:
$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci $ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
</pre> </pre>
  * Make sure to repair and optimize all databases and tables.
<pre>
mysqlcheck -u root -p --auto-repair --optimize --all-databases
</pre>
NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data. NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data.






Howardsmiller: /* File format */
2018-07-17T08:43:49Z
File format

				
				
				
				
				
				← Older revision
				Revision as of 08:43, 17 July 2018
				
Line 17:
Line 17:
===File format=== ===File format===



To allow for large indexes on columns that are a varchar, a combination of settings needs to be set. The file format for the system needs to be using "Barracuda". This allows for the row format to be set to "Compressed" or "Dynamic". To enable this setting see the upgrade steps listed below. To allow for large indexes on columns that are a varchar, a combination of settings needs to be set. The file format for the system needs to be using "Barracuda". This allows for the row format to be set to "Compressed" or "Dynamic". To enable this setting see the upgrade steps listed below. Moodle will not install if you have large format enabled without the Barracuda file format.



===File per table=== ===File per table===



Howardsmiller: /* UTF-8 */
2018-07-17T08:40:31Z
UTF-8

				
				
				
				
				
				← Older revision
				Revision as of 08:40, 17 July 2018
				
Line 13:
Line 13:



* Fresh installs of Moodle 3.1.5 and 3.2.2 onwards will use utf8mb4 by default, if the database server is configured appropriately (see below). * Fresh installs of Moodle 3.1.5 and 3.2.2 onwards will use utf8mb4 by default, if the database server is configured appropriately (see below).
* Sites upgrading to Moodle 3.1.5 or 3.2.2 can use the script to update to utf8mb4. In Moodle 3.3 a warning will show that the database isn't using full UTF-8 support and suggest moving to 'utf8mb4_unicode_ci', but you may choose to keep using 'utf8_*'. * Sites upgrading to Moodle 3.1.5 or 3.2.2 can use the script to update to utf8mb4. In Moodle 3.3, 3.4 and 3.5 a warning will show that the database isn't using full UTF-8 support and suggest moving to 'utf8mb4_unicode_ci', but you may choose to keep using 'utf8_*'.



===File format=== ===File format===



Howardsmiller: /* Steps to upgrade */
2017-12-11T16:03:55Z
Steps to upgrade

				
				
				
				
				
				← Older revision
				Revision as of 16:03, 11 December 2017
				
Line 57:
Line 57:
</pre> </pre>



NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases it may consider dumping and re-importing your data. NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data.



* Adjust the $CFG->dboptions Array in your '''config.php''' to make sure that Moodle uses the right Collation when connecting to the MySQL Server:   * Adjust the $CFG->dboptions Array in your '''config.php''' to make sure that Moodle uses the right Collation when connecting to the MySQL Server:  



Howardsmiller: /* Steps to upgrade */
2017-12-11T16:00:31Z
Steps to upgrade

				
				
				
				
				
				← Older revision
				Revision as of 16:00, 11 December 2017
				
Line 57:
Line 57:
</pre> </pre>



NOTE: For large sites, this script will be slow. It is recommended to dump and re-import your data. NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases it may consider dumping and re-importing your data.



* Adjust the $CFG->dboptions Array in your '''config.php''' to make sure that Moodle uses the right Collation when connecting to the MySQL Server:   * Adjust the $CFG->dboptions Array in your '''config.php''' to make sure that Moodle uses the right Collation when connecting to the MySQL Server:

← Older revision		Revision as of 19:11, 23 July 2021
Line 47:		Line 47:


	<~~code~~>		<syntaxhighlight lang="ini">
	[client]		[client]
	default-character-set = utf8mb4		default-character-set = utf8mb4
Line 62:		Line 62:
	[mysql]		[mysql]
	default-character-set = utf8mb4		default-character-set = utf8mb4
	</~~code~~>		</syntaxhighlight>
	* Restart your MySQL server.		* Restart your MySQL server.
	* Run the CLI script to convert to the new ~~Character~~ set and ~~Collation~~ (requires Moodle 3.1.5, 3.2.2 or newer):		* Run the CLI script to convert to the new character set and collation (requires Moodle 3.1.5, 3.2.2 or newer):
	<pre>		<pre>
	$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci		$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
Line 74:		Line 74:
	NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data.		NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data.

	* Adjust the $CFG->dboptions ~~Array~~ in your '''config.php''' to make sure that Moodle uses the right ~~Collation~~ when connecting to the MySQL Server:		* Adjust the $CFG->dboptions array in your '''config.php''' to make sure that Moodle uses the right collation when connecting to the MySQL Server:
	<pre>		<pre>
	$CFG->dboptions = array(		$CFG->dboptions = array(
Line 83:		Line 83:
	</pre>		</pre>

	If you only have access to the database command line (or something like ~~phpmyadmin~~) you can try the following ~~sql~~ commands:		If you only have access to the database command line (or something like phpMyaAdmin) you can try the following SQL commands:
	<~~code~~>		<syntaxhighlight lang="sql">
	SET GLOBAL innodb_file_format = barracuda		SET GLOBAL innodb_file_format = barracuda;

	SET GLOBAL innodb_file_per_table = 1		SET GLOBAL innodb_file_per_table = 1;

	SET GLOBAL innodb_large_prefix = 'on'		SET GLOBAL innodb_large_prefix = 'on';
	</~~code~~>		</syntaxhighlight>

	* Try adding some Emojis (e.g. 😂💩) to your Moodle site to verify that the upgrade was successful.		* Try adding some Emojis (e.g. 😂💩) to your Moodle site to verify that the upgrade was successful.

← Older revision		Revision as of 04:44, 6 August 2020
Line 54:		Line 54:
	innodb_file_format = Barracuda		innodb_file_format = Barracuda
	innodb_file_per_table = 1		innodb_file_per_table = 1
	innodb_large_prefix		innodb_large_prefix = true

	character-set-server = utf8mb4		character-set-server = utf8mb4

← Older revision		Revision as of 23:44, 2 March 2020
Line 56:		Line 56:
	$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci		$ php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
	</pre>		</pre>
			* Make sure to repair and optimize all databases and tables.
			<pre>
			mysqlcheck -u root -p --auto-repair --optimize --all-databases
			</pre>
	NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data.		NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data.

← Older revision		Revision as of 08:43, 17 July 2018
Line 17:		Line 17:
	===File format===		===File format===

	To allow for large indexes on columns that are a varchar, a combination of settings needs to be set. The file format for the system needs to be using "Barracuda". This allows for the row format to be set to "Compressed" or "Dynamic". To enable this setting see the upgrade steps listed below.		To allow for large indexes on columns that are a varchar, a combination of settings needs to be set. The file format for the system needs to be using "Barracuda". This allows for the row format to be set to "Compressed" or "Dynamic". To enable this setting see the upgrade steps listed below. Moodle will not install if you have large format enabled without the Barracuda file format.

	===File per table===		===File per table===

← Older revision		Revision as of 08:40, 17 July 2018
Line 13:		Line 13:

	* Fresh installs of Moodle 3.1.5 and 3.2.2 onwards will use utf8mb4 by default, if the database server is configured appropriately (see below).		* Fresh installs of Moodle 3.1.5 and 3.2.2 onwards will use utf8mb4 by default, if the database server is configured appropriately (see below).
	* Sites upgrading to Moodle 3.1.5 or 3.2.2 can use the script to update to utf8mb4. In Moodle 3.3 a warning will show that the database isn't using full UTF-8 support and suggest moving to 'utf8mb4_unicode_ci', but you may choose to keep using 'utf8_*'.		* Sites upgrading to Moodle 3.1.5 or 3.2.2 can use the script to update to utf8mb4. In Moodle 3.3, 3.4 and 3.5 a warning will show that the database isn't using full UTF-8 support and suggest moving to 'utf8mb4_unicode_ci', but you may choose to keep using 'utf8_*'.

	===File format===		===File format===

← Older revision		Revision as of 16:03, 11 December 2017
Line 57:		Line 57:
	</pre>		</pre>

	NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases ~~it may~~ consider dumping and re-importing your data.		NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases you might consider dumping and re-importing your data.

	* Adjust the $CFG->dboptions Array in your '''config.php''' to make sure that Moodle uses the right Collation when connecting to the MySQL Server:		* Adjust the $CFG->dboptions Array in your '''config.php''' to make sure that Moodle uses the right Collation when connecting to the MySQL Server:

← Older revision		Revision as of 16:00, 11 December 2017
Line 57:		Line 57:
	</pre>		</pre>

	NOTE: ~~For~~ large sites, this ~~script will be slow~~. ~~It is recommended to dump~~ and re-~~import~~ your data.		NOTE: On very large sites this may take a long time to run. You should probably establish how long on a test install before taking your live site offline. In some cases it may consider dumping and re-importing your data.

	* Adjust the $CFG->dboptions Array in your '''config.php''' to make sure that Moodle uses the right Collation when connecting to the MySQL Server:		* Adjust the $CFG->dboptions Array in your '''config.php''' to make sure that Moodle uses the right Collation when connecting to the MySQL Server:

MySQL full unicode support - Revision history

Leonstr: /* UTF-8 */ Added note about utf8 in recent MySQL/MariaDB versions

Tim@horizoneducationnetwork.org: /* UTF-8 */clean up, typos fixed: eg. → e.g.

Leonstr: /* Steps to upgrade */ Changed to , added semicolons to SQL, minor capitalisation changes

Justineuro: added " = true" to "innodb_large_prefix"; mysql fails without setting it to true

Brugger at 21:31, 3 May 2020

Anderson.blaine at 23:44, 2 March 2020

Howardsmiller: /* File format */

Howardsmiller: /* UTF-8 */

Howardsmiller: /* Steps to upgrade */

Howardsmiller: /* Steps to upgrade */

Leonstr: /* Steps to upgrade */ Changed `to , added semicolons to SQL, minor capitalisation changes`