Changing the character set of the "enswitch" database: Difference between revisions

Content deleted Content added

Inline

Latest revision as of 03:11, 29 October 2023

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.

@@ 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.
-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%'
-=== 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''".

Changing the character set of the "enswitch" database: Difference between revisions

Latest revision as of 03:11, 29 October 2023

Navigation menu

Search