Migrate existing databases to utf8mb4 character set

Description

Just create a page and paste this groovy code:

{{groovy wiki="false"}}
// we set wiki="false" because we will look at the results with outputSyntax="plain", so it is better to control exactly what we display
import java.sql.*

LOGGER = services.logging.getLogger('utf8mb4_Migrator');

def getTables(connection) {
    def tables = [];
    connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).withCloseable { stmt ->
        def resultset = stmt.executeQuery("""
        SELECT t.table_name
        FROM information_schema.TABLES as t
        WHERE t.table_schema = DATABASE()
           AND t.table_type = 'BASE TABLE'
           AND t.table_collation != 'utf8mb4_bin'
        """);
        while (resultset.next()) {
            tables.add(resultset.getString(1));
        }
        return tables;
    }
}

def isMariaDB105(connection) {
    def meta = connection.getMetaData()
    def dbProduct = meta.getDatabaseProductName() 
    def major = meta.getDatabaseMajorVersion()
    def minor = meta.getDatabaseMinorVersion()

    println("   [DEBUG] Detected Database: ${dbProduct} version ${major}.${minor}")
    LOGGER.debug("[DEBUG] Detected Database: ${dbProduct} version ${major}.${minor}")

    if (dbProduct.contains("MariaDB")) {
        if (major > 10 || (major == 10 && minor >= 5)) {
            return true
        }
    }
    return false
}

def getForeignKeyDefinitions(connection, dbName) {
    def fks = [:]

    def sql = """
        SELECT
            k.TABLE_NAME,
            k.CONSTRAINT_NAME,
            k.COLUMN_NAME,
            k.REFERENCED_TABLE_NAME,
            k.REFERENCED_COLUMN_NAME
        FROM information_schema.KEY_COLUMN_USAGE k
        JOIN information_schema.REFERENTIAL_CONSTRAINTS r
          ON k.CONSTRAINT_NAME = r.CONSTRAINT_NAME 
          AND k.CONSTRAINT_SCHEMA = r.CONSTRAINT_SCHEMA
        WHERE k.TABLE_SCHEMA = '${dbName}'
          AND k.REFERENCED_TABLE_NAME IS NOT NULL
        ORDER BY k.CONSTRAINT_NAME, k.ORDINAL_POSITION
    """

    connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).withCloseable { stmt ->
        def rs = stmt.executeQuery(sql)
        while (rs.next()) {
           def table = rs.getString("TABLE_NAME")
            def name = rs.getString("CONSTRAINT_NAME")
            if (!fks.containsKey(name)) {
                fks[name] = [
                    "table": table,
                    "cols": [],
                    "refTable": rs.getString("REFERENCED_TABLE_NAME"),
                    "refCols": []
                ]
            }
            fks[name]["cols"].add(rs.getString("COLUMN_NAME"))
            fks[name]["refCols"].add(rs.getString("REFERENCED_COLUMN_NAME"))
        }
    }
    return fks
}

def executeUpdate(connection, sql) {
    println("      ${sql}");
    connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).withCloseable { stmt ->
        stmt.executeUpdate(sql);
    }
}

def convertDatabase(wikiId) {
    println('');
    println('=========================================================================================================');
    println ("                                   Converting database [${wikiId}]...");
    println('=========================================================================================================');
    LOGGER.warn("Converting database wikiId :[${wikiId}]...");
    def context = xcontext.getContext();
    def currentWiki = services.get('wiki').getCurrentWikiId();
    // Changing the current wiki
    context.setWikiId(wikiId);
    try {
        def store = xwiki.getXWiki().getHibernateStore();
        store.beginTransaction(context);
        def session = store.getSession(context);
        // We are not responsible of closing the connection because it is still used by others parts of XWiki (like some macros)
        def connection = session.connection();

        try {
            // Convert the database itself (for new tables)
            def dbName = store.getSchemaFromWikiName(context);
            println ("\n* Converting the database [${dbName}]...");
            LOGGER.debug("Converting the database dbName : [${dbName}]...");
            executeUpdate(connection, "ALTER DATABASE ${dbName} CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;");

            // Now convert the tables
            def tables = getTables(connection);

            def isMariaDB105 = isMariaDB105(connection)
            def fks = null;
            if (isMariaDB105) {
              fks = getForeignKeyDefinitions(connection, dbName);
              println (fks)
              if (fks.size() > 0) {
                fks.each { name, defs ->
                  if (tables.contains(defs.table )) {
                    executeUpdate(connection, "ALTER TABLE `${defs.table}` DROP FOREIGN KEY `${name}`")
                  }
                }
              }
            } else {
                // Disable foreign key checks
                println ("\n* Disabling foreign key checks");
                executeUpdate(connection, 'set foreign_key_checks=0;');
            }

            println ("\n* Converting tables");
            for (table in tables) {
                println ("\n   Converting table [${table}].");
                executeUpdate(connection, "ALTER TABLE ${table} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;");

            }

            if (isMariaDB105) {
                if (fks != null) {
                    fks.each { name, defs ->
                        if (tables.contains(defs.table )) {
                          def cols = defs.cols.collect { "`$it`" }.join(", ")
                          def refCols = defs.refCols.collect { "`$it`" }.join(", ")
                          executeUpdate(connection, "ALTER TABLE `${defs.table}` ADD CONSTRAINT `${name}` FOREIGN KEY (${cols}) REFERENCES `${defs.refTable}` (${refCols});")
                        }
                    }
                }
            } else {
                // Enable back foreign key checks
                println ("\n* Enabling back foreign key checks");
                executeUpdate(connection, 'set foreign_key_checks=1;');
            }

            // Commit
            store.endTransaction(context, true);
        } catch (Exception e) {
            println('');
            println('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
            println("!!!!!!!!!!!!!!! Error converting the wiki [${wikiId}]. !!!!!!!!!!!!!!!!!!!!");
            println('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
            println('');
            println(e);
            println('');
            LOGGER.error("Error converting the wiki [${wikiId}]", e);

            // Rollback
            store.endTransaction(context, false);

            // Make sure the requester knows something wrong happens, but this has actually not effect inside the
            // "executor" of the rest slave. I just keep it in case one wants to execute this script manually.
            response.setStatus(500);
        }
    } finally {
        context.setWikiId(currentWiki);
    }
}

for (wikiId in services.get('wiki').getAllIds()) {
    convertDatabase(wikiId);
}

LOGGER.warn(" == Databasse Migration to utf8mb4 completed ==");

{{/groovy}}

Examples of use

When it works correctly:

=========================================================================================================
                                   Converting database [subwiki1]...
=========================================================================================================

* Converting the database [subwiki1]...
      ALTER DATABASE subwiki1 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

* Disabling foreign key checks
      set foreign_key_checks=0;

* Converting tables

   Converting table [activitystream_events].
      ALTER TABLE activitystream_events CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [activitystream_events_status].
      ALTER TABLE activitystream_events_status CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [activitystream_events_targets].
      ALTER TABLE activitystream_events_targets CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_aggregatorgroup].
      ALTER TABLE feeds_aggregatorgroup CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_aggregatorurl].
      ALTER TABLE feeds_aggregatorurl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_aggregatorurlgroups].
      ALTER TABLE feeds_aggregatorurlgroups CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_feedentry].
      ALTER TABLE feeds_feedentry CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_feedentrytags].
      ALTER TABLE feeds_feedentrytags CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_keyword].
      ALTER TABLE feeds_keyword CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [mailsender_events].
      ALTER TABLE mailsender_events CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [notification_filter_prefs].
      ALTER TABLE notification_filter_prefs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiattachment].
      ALTER TABLE xwikiattachment CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiattachment_archive].
      ALTER TABLE xwikiattachment_archive CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiattachment_content].
      ALTER TABLE xwikiattachment_content CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiattrecyclebin].
      ALTER TABLE xwikiattrecyclebin CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikicomments].
      ALTER TABLE xwikicomments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikidates].
      ALTER TABLE xwikidates CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikidbversion].
      ALTER TABLE xwikidbversion CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikidoc].
      ALTER TABLE xwikidoc CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikidoubles].
      ALTER TABLE xwikidoubles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikifloats].
      ALTER TABLE xwikifloats CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiid].
      ALTER TABLE xwikiid CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiintegers].
      ALTER TABLE xwikiintegers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilargestrings].
      ALTER TABLE xwikilargestrings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilinks].
      ALTER TABLE xwikilinks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilistitems].
      ALTER TABLE xwikilistitems CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilists].
      ALTER TABLE xwikilists CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilock].
      ALTER TABLE xwikilock CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilongs].
      ALTER TABLE xwikilongs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiobjects].
      ALTER TABLE xwikiobjects CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikipreferences].
      ALTER TABLE xwikipreferences CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiproperties].
      ALTER TABLE xwikiproperties CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikircs].
      ALTER TABLE xwikircs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikirecyclebin].
      ALTER TABLE xwikirecyclebin CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikispace].
      ALTER TABLE xwikispace CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikistatsdoc].
      ALTER TABLE xwikistatsdoc CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikistatsreferer].
      ALTER TABLE xwikistatsreferer CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikistatsvisit].
      ALTER TABLE xwikistatsvisit CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikistrings].
      ALTER TABLE xwikistrings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

* Enabling back foreign key checks
      set foreign_key_checks=1;

=========================================================================================================
                                   Converting database [xwiki]...
=========================================================================================================

* Converting the database [xwiki]...
      ALTER DATABASE xwiki CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

* Disabling foreign key checks
      set foreign_key_checks=0;

* Converting tables

   Converting table [activitystream_events].
      ALTER TABLE activitystream_events CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [activitystream_events_status].
      ALTER TABLE activitystream_events_status CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [activitystream_events_targets].
      ALTER TABLE activitystream_events_targets CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_aggregatorgroup].
      ALTER TABLE feeds_aggregatorgroup CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_aggregatorurl].
      ALTER TABLE feeds_aggregatorurl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_aggregatorurlgroups].
      ALTER TABLE feeds_aggregatorurlgroups CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_feedentry].
      ALTER TABLE feeds_feedentry CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_feedentrytags].
      ALTER TABLE feeds_feedentrytags CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [feeds_keyword].
      ALTER TABLE feeds_keyword CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [mailsender_events].
      ALTER TABLE mailsender_events CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [notification_filter_prefs].
      ALTER TABLE notification_filter_prefs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiattachment].
      ALTER TABLE xwikiattachment CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiattachment_archive].
      ALTER TABLE xwikiattachment_archive CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiattachment_content].
      ALTER TABLE xwikiattachment_content CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiattrecyclebin].
      ALTER TABLE xwikiattrecyclebin CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikicomments].
      ALTER TABLE xwikicomments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikidates].
      ALTER TABLE xwikidates CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikidbversion].
      ALTER TABLE xwikidbversion CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikidoc].
      ALTER TABLE xwikidoc CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikidoubles].
      ALTER TABLE xwikidoubles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikifloats].
      ALTER TABLE xwikifloats CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiid].
      ALTER TABLE xwikiid CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiintegers].
      ALTER TABLE xwikiintegers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilargestrings].
      ALTER TABLE xwikilargestrings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilinks].
      ALTER TABLE xwikilinks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilistitems].
      ALTER TABLE xwikilistitems CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilists].
      ALTER TABLE xwikilists CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilock].
      ALTER TABLE xwikilock CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikilongs].
      ALTER TABLE xwikilongs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiobjects].
      ALTER TABLE xwikiobjects CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikipreferences].
      ALTER TABLE xwikipreferences CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikiproperties].
      ALTER TABLE xwikiproperties CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikircs].
      ALTER TABLE xwikircs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikirecyclebin].
      ALTER TABLE xwikirecyclebin CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikispace].
      ALTER TABLE xwikispace CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikistatsdoc].
      ALTER TABLE xwikistatsdoc CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikistatsreferer].
      ALTER TABLE xwikistatsreferer CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikistatsvisit].
      ALTER TABLE xwikistatsvisit CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

   Converting table [xwikistrings].
      ALTER TABLE xwikistrings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

* Enabling back foreign key checks
      set foreign_key_checks=1;

When an error occurs:

=========================================================================================================
                                   Converting database [subwiki1]...
=========================================================================================================

* Converting the database [subwiki1]...
      ALTERaaaa DATABASE subwiki1 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!! Error converting the wiki [subwiki1]. !!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTERaaaa DATABASE subwiki1 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin' at line 1


=========================================================================================================
                                   Converting database [xwiki]...
=========================================================================================================

* Converting the database [xwiki]...
      ALTERaaaa DATABASE xwiki CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!! Error converting the wiki [xwiki]. !!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTERaaaa DATABASE xwiki CHARACTER SET utf8mb4 COLLATE utf8mb4_bin' at line 1