Database migration between versions

[smcgrat]

Hi, this might not be possible but I would appreciate some advice please. And apologies if I have missed something in the documentation about this.

We already have a containerised v5.0.beta.7.3 instance of cocnerto running and I want to migrate it to a newer version but maintain the current database contents. This hasn't work out of the box for me though unfortunatley.

Is it possible to migrate existing database contents from an older concerto version to the database of a recent concerto build?

Many thanks

Sean

Concerto Platform version

v5.0.beta.7.3

Expected behavior

migration of database to v5.0.12

Actual behavior

database import fails:

$ /usr/bin/mysqldump -P 3306 -h 172.18.0.3 -u root -p concerto < concerto-2020-04-30-11.39.59.sql
Enter password: 
-- MySQL dump 10.13  Distrib 5.7.29, for Linux (x86_64)
--
-- Host: 172.18.0.3    Database: concerto
-- ------------------------------------------------------
-- Server version       5.7.30

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1146: Table 'concerto.IQ_items' doesn't exist when using LOCK TABLES

Steps to reproduce the issue

1. Clone repo git clone https://github.com/campsych/concerto-platform /usr/local/concerto
2. Edit docker-compose.yml file as necessary
3. Build new containers docker-compose up --build -d
4. mysqldump from previous install, (v5.0.beta.7.3)
5. Trying to import the above dump into the new containers fails

[bkielczewski]

$ /usr/bin/mysq -P 3306 -h 172.18.0.3 -u root -p concerto < concerto-2020-04-30-11.39.59.sql

Shouldn't that rather be /usr/bin/mysql -P 3306 -h 172.18.0.3 -u root -p concerto < concerto-2020-04-30-11.39.59.sql. mysqldump is for exporting. Keep the concerto container down when doing that (docker-compose down concerto). Then after import re-up.

If you have actual files from the previous install (./data/mysql) you don't have to do import at all - the database container should just run with these files.

But importing is one thing. I don't know if the new Concerto will be able to automatically migrate the old database structure to the current one. We can guarantee that it would for 5.0 versions (non-beta) but that is old beta. Worth a try though.

[smcgrat]

Firstly, apologies for the delay in responding and thanks for your help.

Sorry, yes, copy and paste snafu re: mysqldump instead of mysql. I have tried with mysql to import the db and actually get the following error:

$ mysql -P 3306 -h 172.18.0.3 -u root -p concerto < concerto-2020-04-30-11.39.59.sql
Enter password:
ERROR 1813 (HY000) at line 216: Tablespace '`concerto`.`IQ_items`' exists

But I think that is irrelevant for me now anyway. Your suggestion of taking the actual files over from the old VM seems to have worked. I had tried that before but the concerto container was failing because it couldn't connect to the database container. So I copied the files across, connected to the database container with the mysql client and manually added the concerto mysql user as per the docker-compose.yml file and it seems to be working now.

Many thanks and sorry for wasting your time.

Sean

[bkielczewski]

ERROR 1813 (HY000) at line 216: Tablespace 'concerto.IQ_items' exists

Looks like target database already existed. In this situation either drop the target database entirely or create a dump with DROP TABLE IF EXISTS, which is done by adding --add-drop-table option to mysqldump.

Anyway, no worries, cheers.

Best, b.