Upgrade from 5.3.1 with MySQL to 5.3.2

Updated: March 18, 2024

Why a migration script is needed

A database structure change was introduced with Nuxeo 5.3.2 to fix query with operator IN (for more details, see https://jira.nuxeo.com/browse/NXP-5183 ).

Below is an example of structure change:

Sample structure in Nuxeo DM <= 5.3.1

CREATE TABLE `common` (
 `id` varchar(36) NOT NULL,
 `icon` varchar(4000) DEFAULT NULL,
 `icon-expanded` varchar(4000) DEFAULT NULL,
 `size` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `common_id_hierarchy_fk` (`id`),
 CONSTRAINT `common_id_hierarchy_fk` FOREIGN KEY (`id`) REFERENCES `hierarchy` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Sample structure in Nuxeo DM >= 5.3.2

CREATE TABLE `common` (
 `id` varchar(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
 `icon` varchar(4000) DEFAULT NULL,
 `icon-expanded` varchar(4000) DEFAULT NULL,
 `size` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `common_id_hierarchy_fk` (`id`),
 CONSTRAINT `common_id_hierarchy_fk` FOREIGN KEY (`id`) REFERENCES `hierarchy` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

See the part CHARACTER SET latin1COLLATE latin1_bin that was added for the column id.

As a consequence, the columns (mainly id) used for foreign key don't have the same definition and it is no more possible to add new constraints, needed by the features of Nuxeo DM. trying to upgrade to Nuxeo DM 5.4.x will raise this error

java.sql.SQLException: Can't create table 'nuxeodb.#sql-5f31_37d' (errno: 150)

Steps for the migration

You need to follow the steps below to migrate your database structure:

  • download the script upgradeMySQL.sh attached to this page

  • edit these properties in the file upgradeMySQL.sh

    DB_HOST=localhost
    DB_PORT=3306
    DB_NAME=nuxeo
    DB_USER=user
    DB_PWD=password
    
    
  • change permission for the script to run it

    $ chmod u+x upgradeMySQL.sh
    
  • launch the script

    $ ./updateMySQL.sh
    
  • if everything is fine, you'll have a message to confirm the upgrade was done

    Database structure upgraded successfully

Upgrade to Nuxeo DM 5.4.1

Now your database is upgraded, you can test it against Nuxeo DM 5.4.1 Once you've downloaded and unzipped it,

  • start it without editing anything: it's needed because you'll need to get the lastest hot fixes to make it work

    $ cd $NUXEO_HOME/bin
    $ ./nuxeoctl start
    
    
  • follow the wizard steps and register on Nuxeo Connect (needed to download Hot Fixes) if you don't have already an account

  • restart
  • log in using the default credentials
  • navigate to Nuxeo Admin Center > Update Center > Software updates tab
  • download and install all availables hot fixes (actually at least the three first ones)
  • stop the server

    $ ./nuxeoctl stop
    
    
  • edit the configuration file nuxeo.confand set the database parameters

    nuxeo.templates=mysql
    nuxeo.db.name=nuxeodb
    nuxeo.db.user=user
    nuxeo.db.password=password
    nuxeo.db.host=localhost
    nuxeo.db.port=3306
    
    
  • restart the server

    $ ./nuxeoctl start
    
    
  • contemplate that all data are present :smile: