Changing the character set of the "enswitch" database
Contents
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".
