Database Migration to Release 6.3.0

Make sure to read through these steps several times before attempting to migrate your database for use with release 6.3.0

First and foremost, you must be using the latest release 6.2.11 or 6.2.12 before attempting this migration. If you are not using any of those releases, then you will need to upgrade to one of them. Secondly, make a backup of your system files before proceeding.

Now, follow these steps exactly and in order.

Step 1

You need to replace your current CLI program with the new one. The folder in the zip file matches your folder structure from the root of your install. Just drop the files in place to merge with what’s already there.

Step 2

Open a shell session and navigate to the root of your install. To make sure you are using the latest CLI program and no errors occur, run the following command ./etsis cli version. The following answer should return:

ETSIS-CLI 1.1.0

Step 3

We will not touch or delete the current database until we have made sure that everything is working. Create a new database using the following collation/charset: utf8mb4_unicode_ci

Step 4

Open config.php and change the database connection to match the one below:

$app->inst->singleton('db', function () {
    $pdo = new \PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS, [\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'"]);
    $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    $pdo->query("SET CHARACTER SET 'utf8mb4'");
    return new \Liten\Orm($pdo);
});

Step 5

Open phinx.php and replace the return statement with the one below:

return [
    "paths" => [
        "migrations" => "app/migrations"
    ],
    "environments" => [
        "default_migration_table" => "migrations",
        "default_database" => "production",
        "production" => [
            "adapter" => "mysql",
            "host" => DB_HOST,
            "name" => DB_NAME,
            "user" => DB_USER,
            "pass" => DB_PASS,
            "charset" => 'utf8mb4',
            "collation" => 'utf8mb4_unicode_ci',
            "port" => DB_PORT
        ]
    ]
];

Step 6

In your currently used database you should only have 85 tables. Download this table list to compare with yours. Delete any tables that do not appear in the list. If you have custom tables which you’ve added, then of course you don’t need to delete those.

Step 7

Now, we want a dump file of data only. So run the following command:

./etsis db backup --dir=app/tmp --no-create-info

Please note that your dump is public but this is only temporary and will need to be deleted later in the process.

Step 8

Change DB_NAME in both config.php and phinx.php to match the name of your newly created database.

Step 9

Download the database structure and import it into your newly created database. If you’ve added custom fields for some tables, make sure to add those fields to the tables in the new database. Furthermore, when creating those custom fields, make sure they follow the same order and placement as found in your current database.

Step 10

Now, import the data dump into your new database. If there are any errors, they must be fixed. Fix the error(s), drop the data and try the import again. When there are no errors, then move on to the next step.

Step 11

We need to make sure there are no issues with running the new migration. So, we will need to change the charset on the migrations table. There should only be three files in the migration’s folder. Open PHPMyAdmin, go to your new database, and run the following:

DROP TABLE IF EXISTS `migrations`;
CREATE TABLE `migrations` (
  `version` bigint(20) NOT NULL,
  `migration_name` varchar(100) DEFAULT NULL,
  `start_time` timestamp NULL DEFAULT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `breakpoint` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `migrations` VALUES(20160916002812, 'InitialSchema', '2017-06-17 08:23:22', '2017-06-17 08:23:31', 0);
INSERT INTO `migrations` VALUES(20160916035404, 'CurrencyTable', '2017-06-17 08:23:31', '2017-06-17 08:23:31', 0);

When you run ./etsis core status, the following answer should return:

 Status  [Migration ID]  Started              Finished             Migration Name
----------------------------------------------------------------------------------
     up  20160916002812  2017-06-17 08:23:22  2017-06-17 08:23:31  InitialSchema
     up  20160916035404  2017-06-17 08:23:31  2017-06-17 08:23:31  CurrencyTable
   down  20170706151405                                            DbSchemaUpdate

Step 12

Now, we need to run the new migration by using the following command:

./etsis core migrate

There can’t be and should be no errors. If there are errors, you need to fix those errors. Once they are fixed, drop all the tables in the new database and start over again with Step 9. When there are no errors, then go on to the next and final step.

Step 13

Now, we need to update etSIS to the latest release by running the following command:

./etsis core update

Now, your system has been updated to release 6.3.0. Log into your system and make sure everything is working and that there are no errors. If there are errors, try to fix them. If you can’t figure out an error, then seek out help in the forum. If it just isn’t working out for you, then revert to your backup and old database until the issues can be sorted.

Once you’ve updated successfully, delete the dump file from the tmp directory as well as the system backup you created. Make sure to update to the latest releases of the plugins and modules if you are using them. If you are updating modules, make sure to delete these files before upgrading:

app/modules/comm-mgmt.module.php
app/modules/myet.module.php
app/routers/comm-mgmt.router.php
app/routers/myet.router.php

Lastly, you will need to visit the Roles screen, and update each of the roles since some of the permissions were renamed.