Base de connaissances

Warden version 6.00+ now uses utf8mb4 as the default character on new installations if it is supported by your MySQL/MariaDB version.  Some admins might want to convert their databases from utf8mb3 to utf8mb4 which supports the full Unicode standard, including emojis and rare characters. Your database version must support using indexed varchar(255) with utf8mb4:

Key takeaway: While varchar(255) utf8mb4 can be stored from MySQL 5.5.3 / MariaDB 5.5 onwards, indexing that column without extra configuration requires MySQL 5.7.7+ or MariaDB 10.2+.

Admins can use the instructions below to convert their danami_warden database to utf8mb4:

1. Export the 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 changes look correct before importing (You should see only utf8mb4 references, no plain utf8 remaining):

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

3. Delete then re-create the danami_warden database using utf8mb4 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;"

4. Re-import the database:

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

5. Verify after utf8mb4_unicode_ci collation after import:

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 |
+-------------------+--------------------+