Changing the character set of the "enswitch" database

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.

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.
• 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 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

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

 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?

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".