Changing the character set of the "enswitch" database - Revision history

Rodolfojcj: Contents moved to the Enswitch documentation web site.

2023-10-29T03:11:07Z

Contents moved to the Enswitch documentation web site.

← Older revision		Revision as of 03:11, 29 October 2023
Line 1:		Line 1:
			The contents previously published in this page have been moved to the Enswitch upgrade guides for versions 4.1 and newer. Please visit https://integrics.com/enswitch/guides/4.1/en/field/enswitch/ to access them.
	=== The situation ===

	In some Enswitch systems these database related errors may be found:

	ERROR 1118 (42000) at line 1614: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

	[ERROR] InnoDB: Cannot add field `some_column` in table `enswitch`.`some_table` because after adding it, the row size is 8329 which is greater than maximum allowed size (8126 bytes) for a record on index leaf page.

	That may happen in these cases:

	* When running Enswitch upgrades that change the database structure.
	* When trying to restore a backup.

	The affected systems are those having the "''latin1''" character set assigned to the "''enswitch''" database or its tables, which is the default character set used at least in these systems:

	* CentOS 7 running MariaDB 5.5.x.
	* Ubuntu 18.04 running Oracle MySQL 5.7.

	=== What to do? ===

	If the "''latin1''" character set is assigned to the "''enswitch''" database or any of its tables, follow the details and apply the change below. If not, no changes will be needed.

	=== How to know which character set is actually used? ===

	To know the character set used by the "''enswitch''" database, run this query:

	show create database enswitch

	To know the tables using the "''latin1''" character set, run this query:

	show table status from enswitch where Collation like 'latin1%'

	=== What change to apply? ===

	The character set will be changed to "''utf8mb4''" at two levels:

	* To the database (this will not change the existing tables, but will be used for new tables that could be added later).
	* To the existing tables.

	Take note that this change may need a considerable time to complete, depending on the size of the database. To get an estimate of the needed running time to complete it, try it first in a test server.

	Attempting that change in MariaDB 5.5.x will not work, then updating it to version 10.x will be needed. Specifically, MariaDB version 10.4.31 or newer will work fine.

	For Oracle MySQL it's likely that version 5.5.x will not work, however this has not been verified.

	Finally, that change works correctly in Oracle MySQL versions 5.7.42 and 8.0.x.

	=== WARNING ===

	It is very important that a '''full backup of your database''' is taken before attempting that change, specially in the production system.

	=== Regarding the database size ===

	Before and after attempting that change, the database size can be estimated with any of these queries:

	SELECT table_schema "DB name", sum( data_length + index_length ) / 1024 / 1024 "DB size in MB", sum( data_free )/ 1024 / 1024 "free/reclaimable space in MB" FROM information_schema.TABLES WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') GROUP BY table_schema;

	SELECT concat(table_schema) 'Database Name', concat(round(SUM(data_length/power(1024,3)),2),'G') DATA, concat(round(SUM(index_length/power(1024,3)),2),'G') 'INDEX', concat(round(SUM(data_free/power(1024,3)),2),'G') 'DATA FREE', concat(round(sum(data_free)/(SUM(data_length+index_length))*100,2)) '% FRAGMENTED', concat(round(SUM(data_length+index_length)/power(1024,3),2),'G') TOTAL FROM information_schema.TABLES WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') GROUP BY table_schema;

	The results can be useful to know if significant differences exist in the needed storage space because of the changed character set. No big differences are expected.

	Those commands are based on the contents found at https://dba.stackexchange.com/questions/14337/calculating-disk-space-usage-per-mysql-db.

	=== Applying the change ===

	After updating the MariaDB or Oracle MySQL version to the proper required version, run these shell commands:

	DB_NAME="enswitch" ; DB_USER="root" ; DB_PASSWORD="YourRootPasswordForMySQL"; DB_CHARSET=utf8mb4 ;
	time (
	echo 'ALTER DATABASE `'"$DB_NAME"'` CHARACTER SET `'$DB_CHARSET'` ;';
	mysql -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" -e "SHOW TABLES" --batch --skip-column-names \
	\| xargs -I{} echo 'select now() as "Before"; ALTER TABLE `'{}'` CONVERT TO CHARACTER SET '$DB_CHARSET' ; select now() as "After";';
	) \
	\| mysql -vv -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME"

	They are based on the contents found at https://stackoverflow.com/a/11873492. In production environments, not using the MySQL root password directly in the command line may be considered; also other MySQL user and password may be used.

	=== Additional references ===

	* https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/ (details about the "variable-length columns that have to be stored on the row's main data page" and the "variable-length columns that have to be stored on the row's overflow pages").
	* https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/#increasing-the-length-of-varchar-columns (explanation of column sizes and their needed bytes per character).
	* https://mysql.rjweb.org/doc.php/limits and https://stackoverflow.com/a/69370285 to know why updating to MariaDB >= 10.x or MySQL >= 5.7 is needed.
	* https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci to better understand the collation values that a character set may use, including the reasons to prefer "''utf8mb4''" over "''utf8''".

Rodolfojcj: Typos fixes.

2023-10-29T01:12:48Z

Typos fixes.

← Older revision		Revision as of 01:12, 29 October 2023
Line 12:		Line 12:
	* When trying to restore a backup.		* When trying to restore a backup.

	~~That~~ affected systems are those having the "''latin1''" character set assigned to the "''enswitch''" database or its tables, which is the default character set used at least in these systems:		The affected systems are those having the "''latin1''" character set assigned to the "''enswitch''" database or its tables, which is the default character set used at least in these systems:

	* CentOS 7 running MariaDB 5.5.x.		* CentOS 7 running MariaDB 5.5.x.
Line 19:		Line 19:
	=== What to do? ===		=== What to do? ===

	If the "''latin1''" character set is assigned to the "''enswitch''" database or any of its tables, follow the details and apply the change ~~bellow~~. If not, no changes will be needed.		If the "''latin1''" character set is assigned to the "''enswitch''" database or any of its tables, follow the details and apply the change below. If not, no changes will be needed.

	=== How to know which character set is actually used? ===		=== How to know which character set is actually used? ===

Rodolfojcj: Shorten contents by removing informative details

2023-10-29T01:05:49Z

Shorten contents by removing informative details

← Older revision		Revision as of 01:05, 29 October 2023
Line 30:		Line 30:

	show table status from enswitch where Collation like 'latin1%'		show table status from enswitch where Collation like 'latin1%'

	=== How to know the default values used by the server? ===

	When creating databases without explicitly setting its default character set or collation, the database server will use the values it has configured as the default ones. To know them, run this query:

	show variables like '%_server'

	which will show these results if "''latin1''" is used:

	+----------------------+-------------------+
	\| Variable_name \| Value \|
	+----------------------+-------------------+
	\| character_set_server \| latin1 \|
	\| collation_server \| latin1_swedish_ci \|
	+----------------------+-------------------+
	2 rows in set (0.00 sec)

	or will show these results if "''utf8mb4''" is used:

	+----------------------+--------------------+
	\| Variable_name \| Value \|
	+----------------------+--------------------+
	\| character_set_server \| utf8mb4 \|
	\| collation_server \| utf8mb4_0900_ai_ci \|
	+----------------------+--------------------+
	2 rows in set (0.01 sec)

	Please note that changing the database server configuration will not be needed.

	=== What change to apply? ===		=== What change to apply? ===

Rodolfojcj: Use a certainty wording in the instructions given

2023-10-20T19:55:49Z

Use a certainty wording in the instructions given

← Older revision		Revision as of 19:55, 20 October 2023
Line 1:		Line 1:
	=== ~~Introduction~~ ===		=== The situation ===

			In some Enswitch systems these database related errors may be found:
	In systems running the "''enswitch''" database with the "''latin1''" character set, at some point an error like this one may happen:

	ERROR 1118 (42000) at line 1614: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.		ERROR 1118 (42000) at line 1614: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

		⚫	[ERROR] InnoDB: Cannot add field `some_column` in table `enswitch`.`some_table` because after adding it, the row size is 8329 which is greater than maximum allowed size (8126 bytes) for a record on index leaf page.
⚫	~~For~~ ~~example when~~ running Enswitch upgrades that change the database structure ~~or when trying to restore a backup~~.

			That may happen in these cases:
	In a test server for Enswitch 4.1 when trying to restore a backup coming from a production system, that error and other similar to the following one were observed:

		⚫	* When running Enswitch upgrades that change the database structure.
⚫	[ERROR] InnoDB: Cannot add field `some_column` in table `enswitch`.`some_table` because after adding it, the row size is 8329 which is greater than maximum allowed size (8126 bytes) for a record on index leaf page.
			* When trying to restore a backup.

	That ~~situation~~ ~~happens~~ in ~~systems~~ having the "''latin1''" character set assigned to the "''enswitch''" database or its tables, which is the default character set used at least in these systems:		That affected systems are those having the "''latin1''" character set assigned to the "''enswitch''" database or its tables, which is the default character set used at least in these systems:

	* CentOS 7 running MariaDB 5.5.x ~~(like the test system already mentioned)~~.		* CentOS 7 running MariaDB 5.5.x.
	* Ubuntu 18.04 running Oracle MySQL 5.7.		* Ubuntu 18.04 running Oracle MySQL 5.7.

			=== What to do? ===
⚫	To know ~~which~~ character set is used by the "''enswitch''" database ~~or any of its tables~~, ~~these queries~~ ~~are~~ ~~useful~~:

			If the "''latin1''" character set is assigned to the "''enswitch''" database or any of its tables, follow the details and apply the change bellow. If not, no changes will be needed.

			=== How to know which character set is actually used? ===

		⚫	To know the character set used by the "''enswitch''" database, run this query:

	show create database enswitch		show create database enswitch

			To know the tables using the "''latin1''" character set, run this query:
	show create table object_versions

			show table status from enswitch where Collation like 'latin1%'
	To know the default character set and collation used by the database server, run this query:

			=== How to know the default values used by the server? ===

			When creating databases without explicitly setting its default character set or collation, the database server will use the values it has configured as the default ones. To know them, run this query:

	show variables like '%_server'		show variables like '%_server'

	which ~~may~~ show these results if "''latin1''" is used:		which will show these results if "''latin1''" is used:

	+----------------------+-------------------+		+----------------------+-------------------+
Line 36:		Line 47:
	2 rows in set (0.00 sec)		2 rows in set (0.00 sec)

	or ~~may~~ show these results if "''utf8mb4''" is used:		or will show these results if "''utf8mb4''" is used:

	+----------------------+--------------------+		+----------------------+--------------------+
Line 46:		Line 57:
	2 rows in set (0.01 sec)		2 rows in set (0.01 sec)

			Please note that changing the database server configuration will not be needed.
	=== A possible solution ===

			=== What change to apply? ===

			The character set will be changed to "''utf8mb4''" at two levels:

	By ~~changing the character set to "''utf8mb4''", which could be applied to some or all existing tables, or to~~ the database (this will not change the existing tables but will be used for new tables that could be added later)~~, that situation will be solved~~.		* To the database (this will not change the existing tables, but will be used for new tables that could be added later).
			* To the existing tables.

	~~Changing both the database and all the tables will work fine.~~ Take note that this change may need a considerable time to complete, ~~then~~ ~~try~~ it in a ~~test~~ ~~server~~ ~~before~~ ~~attempting~~ it in ~~the~~ ~~production~~ ~~environment~~.		Take note that this change may need a considerable time to complete, depending on the size of the database. To get an estimate of the needed running time to complete it, try it first in a test server.

	Attempting that change in MariaDB 5.5.x will not work, then updating it to version 10.x will be needed. Specifically, MariaDB version 10.4.31 ~~works~~ fine.		Attempting that change in MariaDB 5.5.x will not work, then updating it to version 10.x will be needed. Specifically, MariaDB version 10.4.31 or newer will work fine.

	For Oracle MySQL it's likely that version 5.5.x will not work, however this has not been verified.		For Oracle MySQL it's likely that version 5.5.x will not work, however this has not been verified.
Line 74:		Line 90:
	Those commands are based on the contents found at https://dba.stackexchange.com/questions/14337/calculating-disk-space-usage-per-mysql-db.		Those commands are based on the contents found at https://dba.stackexchange.com/questions/14337/calculating-disk-space-usage-per-mysql-db.

	=== Applying the ~~solution~~ ===		=== Applying the change ===

	After updating the MariaDB or Oracle MySQL version to the proper required version, these shell commands ~~may be used~~:		After updating the MariaDB or Oracle MySQL version to the proper required version, run these shell commands:

	DB_NAME="enswitch" ; DB_USER="root" ; DB_PASSWORD="YourRootPasswordForMySQL"; DB_CHARSET=utf8mb4 ;		DB_NAME="enswitch" ; DB_USER="root" ; DB_PASSWORD="YourRootPasswordForMySQL"; DB_CHARSET=utf8mb4 ;

Rodolfojcj: Initial contents

2023-08-31T04:27:52Z

Initial contents

New page

=== Introduction ===

In systems running the "''enswitch''" database with the "''latin1''" character set, at some point an error like this one may happen:

ERROR 1118 (42000) at line 1614: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

For example when running Enswitch upgrades that change the database structure or when trying to restore a backup.

In a test server for Enswitch 4.1 when trying to restore a backup coming from a production system, that error and other similar to the following one were observed:

[ERROR] InnoDB: Cannot add field `some_column` in table `enswitch`.`some_table` because after adding it, the row size is 8329 which is greater than maximum allowed size (8126 bytes) for a record on index leaf page.

That situation happens in systems having the "''latin1''" character set assigned to the "''enswitch''" database or its tables, which is the default character set used at least in these systems:

* CentOS 7 running MariaDB 5.5.x (like the test system already mentioned).
* Ubuntu 18.04 running Oracle MySQL 5.7.

To know which character set is used by the "''enswitch''" database or any of its tables, these queries are useful:

show create database enswitch

show create table object_versions

To know the default character set and collation used by the database server, run this query:

show variables like '%_server'

which may show these results if "''latin1''" is used:

+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
2 rows in set (0.00 sec)

or may show these results if "''utf8mb4''" is used:

+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| character_set_server | utf8mb4 |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
2 rows in set (0.01 sec)

=== A possible solution ===

By changing the character set to "''utf8mb4''", which could be applied to some or all existing tables, or to the database (this will not change the existing tables but will be used for new tables that could be added later), that situation will be solved.

Changing both the database and all the tables will work fine. Take note that this change may need a considerable time to complete, then try it in a test server before attempting it in the production environment.

Attempting that change in MariaDB 5.5.x will not work, then updating it to version 10.x will be needed. Specifically, MariaDB version 10.4.31 works fine.

For Oracle MySQL it's likely that version 5.5.x will not work, however this has not been verified.

Finally, that change works correctly in Oracle MySQL versions 5.7.42 and 8.0.x.

=== WARNING ===

It is very important that a '''full backup of your database''' is taken before attempting that change, specially in the production system.

=== Regarding the database size ===

Before and after attempting that change, the database size can be estimated with any of these queries:

SELECT table_schema "DB name", sum( data_length + index_length ) / 1024 / 1024 "DB size in MB", sum( data_free )/ 1024 / 1024 "free/reclaimable space in MB" FROM information_schema.TABLES WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') GROUP BY table_schema;

SELECT concat(table_schema) 'Database Name', concat(round(SUM(data_length/power(1024,3)),2),'G') DATA, concat(round(SUM(index_length/power(1024,3)),2),'G') 'INDEX', concat(round(SUM(data_free/power(1024,3)),2),'G') 'DATA FREE', concat(round(sum(data_free)/(SUM(data_length+index_length))*100,2)) '% FRAGMENTED', concat(round(SUM(data_length+index_length)/power(1024,3),2),'G') TOTAL FROM information_schema.TABLES WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') GROUP BY table_schema;

The results can be useful to know if significant differences exist in the needed storage space because of the changed character set. No big differences are expected.

Those commands are based on the contents found at https://dba.stackexchange.com/questions/14337/calculating-disk-space-usage-per-mysql-db.

=== Applying the solution ===

After updating the MariaDB or Oracle MySQL version to the proper required version, these shell commands may be used:

DB_NAME="enswitch" ; DB_USER="root" ; DB_PASSWORD="YourRootPasswordForMySQL"; DB_CHARSET=utf8mb4 ;
time (
echo 'ALTER DATABASE `'"$DB_NAME"'` CHARACTER SET `'$DB_CHARSET'` ;';
mysql -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" -e "SHOW TABLES" --batch --skip-column-names \
| xargs -I{} echo 'select now() as "Before"; ALTER TABLE `'{}'` CONVERT TO CHARACTER SET '$DB_CHARSET' ; select now() as "After";';
) \
| mysql -vv -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME"

They are based on the contents found at https://stackoverflow.com/a/11873492. In production environments, not using the MySQL root password directly in the command line may be considered; also other MySQL user and password may be used.

=== Additional references ===

* https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/ (details about the "variable-length columns that have to be stored on the row's main data page" and the "variable-length columns that have to be stored on the row's overflow pages").
* https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/#increasing-the-length-of-varchar-columns (explanation of column sizes and their needed bytes per character).
* https://mysql.rjweb.org/doc.php/limits and https://stackoverflow.com/a/69370285 to know why updating to MariaDB >= 10.x or MySQL >= 5.7 is needed.
* https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci to better understand the collation values that a character set may use, including the reasons to prefer "''utf8mb4''" over "''utf8''".