Tietopankki

Overview

Warden version 6.00 and later defaults to the utf8mb4 character set for new installations, provided the underlying MySQL or MariaDB server supports it. The utf8mb4 charset enables full Unicode compliance, including support for emojis and specialized characters. Existing deployments utilizing utf8mb3 (or legacy utf8) can be migrated to ensure compatibility and expanded character support.

Prerequisites & Compatibility Requirements

Before initiating the migration, verify that your database server version supports indexing varchar(255) columns with the utf8mb4 charset. The following table outlines version-specific requirements and configuration notes:

Note: While varchar(255) utf8mb4 data can be stored on MySQL 5.5.3+ and MariaDB 5.5+, creating indexes on these columns without additional configuration requires MySQL 5.7.7+ or MariaDB 10.2+.

Database Migration Procedure

Follow the steps below to migrate the danami_warden database from utf8mb3/utf8 to utf8mb4.

1. Export the existing database:

mysqldump -u admin -p`cat /etc/psa/.psa.shadow` --single-transaction --routines --triggers danami_warden -r danami_warden.sql

2. Convert the dump file to use utf8mb4:

sed \
  -e 's/utf8mb3_unicode_ci/utf8mb4_unicode_ci/g' \
  -e 's/utf8mb3_general_ci/utf8mb4_general_ci/g' \
  -e 's/utf8mb3_bin/utf8mb4_bin/g' \
  -e 's/utf8mb3/utf8mb4/g' \
  -e 's/utf8_unicode_ci/utf8mb4_unicode_ci/g' \
  -e 's/utf8_general_ci/utf8mb4_general_ci/g' \
  -e 's/utf8_bin/utf8mb4_bin/g' \
  -e 's/CHARACTER SET utf8 COLLATE/CHARACTER SET utf8mb4 COLLATE/g' \
  -e 's/CHARSET=utf8 /CHARSET=utf8mb4 /g' \
  -e 's/CHARSET=utf8;/CHARSET=utf8mb4;/g' \
  -e 's/SET NAMES utf8;/SET NAMES utf8mb4;/g' \
  danami_warden.sql > danami_warden_utf8mb4.sql

3. Verify the converted dump file: Ensure all legacy charset references have been replaced. The output should only contain utf8mb4 entries.

grep -i "utf8" danami_warden_utf8mb4.sql | sort -u

4. Delete and recreate the target database: Drop the existing schema and create a new one configured with utf8mb4_unicode_ci.

mysql -u admin -p`cat /etc/psa/.psa.shadow` -e "DROP DATABASE danami_warden; CREATE DATABASE danami_warden CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

5. Import the modified dump file:

mysql -u admin -p`cat /etc/psa/.psa.shadow` danami_warden < danami_warden_utf8mb4.sql

6. Validate table collations post-import: Confirm that all tables are utilizing the correct collation.

mysql -u admin -p`cat /etc/psa/.psa.shadow` danami_warden -e "SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'danami_warden';"

+-------------------+--------------------+
| TABLE_NAME        | TABLE_COLLATION    |
+-------------------+--------------------+
| bayes_expire      | utf8mb4_unicode_ci |
| bayes_global_vars | utf8mb4_unicode_ci |
| bayes_seen        | utf8mb4_unicode_ci |
| bayes_token       | utf8mb4_unicode_ci |
| bayes_vars        | utf8mb4_unicode_ci |
| config            | utf8mb4_unicode_ci |
| email_templates   | utf8mb4_unicode_ci |
| licenses          | utf8mb4_unicode_ci |
| log_actions       | utf8mb4_unicode_ci |
| log_application   | utf8mb4_unicode_ci |
| log_emails        | utf8mb4_unicode_ci |
| maddr             | utf8mb4_unicode_ci |
| mailaddr          | utf8mb4_unicode_ci |
| msgrcpt           | utf8mb4_unicode_ci |
| msgs              | utf8mb4_unicode_ci |
| policy            | utf8mb4_unicode_ci |
| quarantine        | utf8mb4_unicode_ci |
| redir_url_cache   | utf8mb4_unicode_ci |
| rule_statistics   | utf8mb4_unicode_ci |
| short_url_cache   | utf8mb4_unicode_ci |
| statistics        | utf8mb4_unicode_ci |
| txrep             | utf8mb4_unicode_ci |
| users             | utf8mb4_unicode_ci |
| wblist            | utf8mb4_unicode_ci |
+-------------------+--------------------+

Troubleshooting & Common Issues

• Index Prefix Limit Errors: If you encounter errors related to index prefix lengths during or after migration, your database version may require explicit configuration. For MySQL 5.5.3–5.7.6 and MariaDB 5.5–10.1, ensure the following parameters are enabled in your database configuration file before proceeding:
  • innodb_large_prefix=ON
  • ROW_FORMAT=DYNAMIC or COMPRESSED
• Verification Failure: If the final collation check returns results containing utf8mb3 or legacy utf8, re-run the sed conversion command and verify that all references were successfully replaced before importing.