Steps

This converts a MySQL database created with the wrong character set to the utf8mb4 character set and utf8mb4_bin collation that XWiki expects — MySQL Storage Engine and Character Set explains why that pair is a requirement. Nothing in XWiki converts an existing database's character set, so this is a manual operation on a stopped instance.

1. Stop XWiki, then back up the database with the database backup commands: the conversion rewrites every table and cannot be undone.
2. Save this script and run it as the MySQL root user, passing the database name as its only argument (it defaults to xwiki):

   #!/bin/bash
   
   db="${1:-xwiki}"
   
   to_character_set=utf8mb4
   to_collation=utf8mb4_bin
   
   mysql_cmd="mysql -u root"
   
   echo "Changing ($db) character to $to_collation."
   
   $mysql_cmd -e "ALTER DATABASE $db CHARACTER SET $to_character_set COLLATE $to_collation;"
   
   TBL_LIST=$($mysql_cmd -N -s -r -e "use $db;show tables;")
   
   for tbl_name in $TBL_LIST;
   do
   $mysql_cmd -e "SET FOREIGN_KEY_CHECKS=0; alter table $db.$tbl_name convert to character set $to_character_set collate $to_collation; SET FOREIGN_KEY_CHECKS=1;"
   done
   
   echo "Here the result of the operation:"
   $mysql_cmd -e "USE $db;SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE();"

   It sets the database's own default character set and collation, converts each of its tables in turn, and prints the collation of every column it produced. If the MySQL root user has a password, add the -p option to the mysql_cmd line and each call prompts for it.

3. Run the script again for each subwiki's database: it converts one database at a time.
4. Start XWiki.
5. Confirm that no column was left behind. The script's closing query lists the collation of every column; asking it for the columns that are not on the target collation turns it into a single check:

   SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
     FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'xwiki' AND COLLATION_NAME <> 'utf8mb4_bin';

   An empty result is the confirmation: every text column of the database is on utf8mb4_bin, and the wiki that starts on it stores four-byte characters correctly.

FAQ

Why does the script fail with an ERROR 1118 row-size error?

Because the database was never upgraded through a version carrying the column-type migration XWiki runs for itself. "Row size too large" MySQL Error has the diagnosis and the fix — do not widen the columns by hand.

Related

• Configure MySQL.
• Migrate MySQL databases to utf8mb4.