Migrate MySQL databases to utf8mb4

Last modified by Mohamed Boussaa on 2022/04/12 16:12

cogMigrate existing databases to utf8mb4 character set
Type
Category
Developed by

Guillaume Delhumeau

Rating
0 Votes
LicenseGNU Lesser General Public License 2.1

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.*

def getTables(connection) {
   def tables = [];
    connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY).withCloseable { stmt ->
       def resultset = stmt.executeQuery('show tables;');
       while (resultset.next()) {
            tables.add(resultset.getString(1));
       }
       return tables;
   }
}

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('=========================================================================================================');
   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}]...");
            executeUpdate(connection, "ALTER DATABASE ${dbName} CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;");

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

           // 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;");
           }

           // 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('');

           // 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);
}

{{/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
Tags:
     

Get Connected