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.

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

They may need a considerable time to complete, depending on the size of the database. To get an estimate of the needed running time, try first in a test server.

Attempting those changes 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, the needed changes work 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 the next steps, specially in the production system.

Regarding the database size

Before and after attempting the changes, 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.

Applying the changes

After updating the MariaDB or Oracle MySQL version to the proper required version, apply the changes detailed below.

First, change the database character set and collation by running these commands:

DB_NAME="enswitch" ; DB_USER="root" ; DB_PASSWORD="YourRootPasswordForMySQL"; DB_CHARSET="utf8mb4" ; DB_COLLATION="utf8mb4_unicode_ci" ;
echo "ALTER DATABASE $DB_NAME CHARACTER SET $DB_CHARSET COLLATE $DB_COLLATION;" | mysql -u $DB_USER -p"$DB_PASSWORD"

Next, generate to the standard output the statements that will be used to change each table:

mysql -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo "ALTER TABLE {} CONVERT TO CHARACTER SET $DB_CHARSET COLLATE $DB_COLLATION;"

That output will look like this:

...
...
ALTER TABLE nv_numbers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE object_versions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE outcosts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
...
...

Next, run each of those statatements in the MySQL command line interface, like this sample shows:

MySQL [(none)]> use enswitch;
...
...
MySQL [enswitch]> ALTER TABLE nv_numbers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
MySQL [enswitch]> ALTER TABLE object_versions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
MySQL [enswitch]> ALTER TABLE outcosts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
...
...

Additional considerations

To know the character sets available in your system, run this command in the MySQL command line interface:
- SHOW CHARACTER SET
To know the collations available in your system for the utf8mb4 character set, run this command in the MySQL command line interface:
- SHOW COLLATION like 'utf8mb4%'

Additional references

There are different ways to calculate the disk space usage per MySQL database.
The approach to change the character set and collation of a MySQL database and its tables is the base for the commands above.
Knowing the InnoDB's row formats helps to identify the differences between 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".
The explanation of varchar column sizes and their needed bytes per character.
Knowing about the MySQL limits and how to move from MariaDB 5.5 gives the reasons to update to MariaDB >= 10.x or MySQL >= 5.7.
Understanding the collation values that UTF based character sets may use offers arguments to prefer "utf8mb4" over "utf8".
Newer MySQL and MariaDB versions may recommend a collation different than utf8mb4_unicode_ci, which may have better performance and other improvements.