Migration v1 -> v2 fails

[thmang82]

Hi,

i have been running an old instance of Mailtrain 1.24.0 on an EC2 instance inside a docker container.
I am now trying to move that instance to another machine.

After migrating the DB to the new machine, my old 1.24.0 docker container is running on the new instance.
But I thought it was time to migrate to the official docker image in the process.

That did not go well. I run into multiple database migration errors. and finally got stuck

The first error:

migration file "20170506102634_v1_to_v2.js" failed
migration failed with error: ALTER TABLE `custom_forms` MODIFY `list` int unsigned not null - ER_BAD_FIELD_ERROR: Unknown column 'list' in 'custom_forms'

Tried to fix this with:

ALTER TABLE `custom_forms` ADD column `list` int unsigned not null;

Next Error:

migration file "20170506102634_v1_to_v2.js" failed
migration failed with error: ALTER TABLE `custom_forms_data` MODIFY `id` int unsigned not null auto_increment - ER_BAD_FIELD_ERROR: Unknown column 'id' in 'custom_forms_data'

Tried to fix it with:

ALTER TABLE `custom_forms_data` ADD COLUMN `id` int unsigned not null auto_increment, add key (id);

Final error that i cannot circumvent:

migration file "20170506102634_v1_to_v2.js" failed
migration failed with error: create table `namespaces` (`id` int unsigned not null auto_increment primary key, `name` varchar(255), `description` text, `namespace` int unsigned) default character set utf8mb4 - ER_TABLE_EXISTS_ERROR: Table 'namespaces' already exists

Deleting the namespaces does not work due to a foreign key constraint. And renaming it does not help either :-(

This is my database layout pre-migration:

mysql> show tables;
+---------------------+
| Tables_in_mailtrain |
+---------------------+
| attachments |
| blacklist |
| campaign |
| campaign__3 |
| campaign__4 |
| campaign__5 |
| campaign_tracker |
| campaign_tracker__3 |
| campaign_tracker__4 |
| campaign_tracker__5 |
| campaigns |
| confirmations |
| custom_fields |
| custom_forms |
| custom_forms_data |
| import_failed |
| importer |
| links |
| lists |
| queued |
| report_templates |
| reports |
| rss |
| segment_rules |
| segments |
| settings |
| subscription |
| subscription__1 |
| subscription__2 |
| templates |
| trigger |
| triggers |
| tzoffset |
| users |
+---------------------+
34 rows in set (0.00 sec)

mysql> DESCRIBE attachments;
+--------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| campaign | int unsigned | NO | MUL | NULL | |
| filename | varchar(255) | NO | | | |
| content_type | varchar(100) | NO | | | |
| content | longblob | YES | | NULL | |
| size | int | NO | | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------+--------------+------+-----+-------------------+-------------------+

mysql> describe blacklist;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| email | varchar(191) | NO | PRI | NULL | |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> describe campaign;
+--------------+------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| list | int unsigned | NO | MUL | NULL | |
| segment | int unsigned | NO | | NULL | |
| subscription | int unsigned | NO | MUL | NULL | |
| status | tinyint unsigned | NO | MUL | 0 | |
| response | varchar(255) | YES | | NULL | |
| response_id | varchar(255) | YES | MUL | NULL | |
| updated | timestamp | YES | | NULL | |
| created | timestamp | NO | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------+------------------+------+-----+-------------------+-------------------+
9 rows in set (0.01 sec)

mysql> describe campaign_tracker;
+-------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+-------------------+
| list | int unsigned | NO | PRI | NULL | |
| subscriber | int unsigned | NO | PRI | NULL | |
| link | int | NO | PRI | NULL | |
| ip | varchar(100) | YES | | NULL | |
| device_type | varchar(50) | YES | | NULL | |
| country | varchar(2) | YES | | NULL | |
| count | int unsigned | NO | | 1 | |
| created | timestamp | NO | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+--------------+------+-----+-------------------+-------------------+
8 rows in set (0.00 sec)

mysql> describe campaigns
-> ;
+-------------------------+------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+------------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| cid | varchar(255) | NO | UNI | NULL | |
| type | tinyint unsigned | NO | MUL | 1 | |
| parent | int unsigned | YES | MUL | NULL | |
| name | varchar(255) | NO | MUL | | |
| description | text | YES | | NULL | |
| list | int unsigned | NO | | NULL | |
| segment | int unsigned | YES | | NULL | |
| template | int unsigned | NO | | NULL | |
| source_url | varchar(255) | YES | | NULL | |
| editor_name | varchar(50) | YES | | | |
| editor_data | longtext | YES | | NULL | |
| last_check | timestamp | YES | MUL | NULL | |
| check_status | varchar(255) | YES | | NULL | |
| from | varchar(255) | YES | | | |
| address | varchar(255) | YES | | | |
| reply_to | varchar(255) | YES | | | |
| subject | varchar(255) | YES | | | |
| unsubscribe | varchar(255) | NO | | | |
| html | longtext | YES | | NULL | |
| html_prepared | longtext | YES | | NULL | |
| text | longtext | YES | | NULL | |
| status | tinyint unsigned | NO | MUL | 1 | |
| scheduled | timestamp | YES | MUL | NULL | |
| status_change | timestamp | YES | | NULL | |
| delivered | int unsigned | NO | | 0 | |
| blacklisted | int unsigned | NO | | 0 | |
| opened | int unsigned | NO | | 0 | |
| clicks | int unsigned | NO | | 0 | |
| unsubscribed | int unsigned | NO | | 0 | |
| bounced | int unsigned | NO | | 0 | |
| complained | int unsigned | NO | | 0 | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| open_tracking_disabled | tinyint unsigned | NO | | 0 | |
| click_tracking_disabled | tinyint unsigned | NO | | 0 | |
+-------------------------+------------------+------+-----+-------------------+-------------------+
35 rows in set (0.00 sec)

mysql> describe confirmations;
+---------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| cid | varchar(255) | NO | UNI | NULL | |
| list | int unsigned | NO | MUL | NULL | |
| action | varchar(100) | NO | | NULL | |
| ip | varchar(100) | YES | | NULL | |
| data | text | NO | | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+---------+--------------+------+-----+-------------------+-------------------+
7 rows in set (0.00 sec)

mysql> describe custom_fields
-> ;
+----------------+------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| list | int unsigned | NO | MUL | NULL | |
| name | varchar(255) | YES | | | |
| description | text | YES | | NULL | |
| key | varchar(100) | NO | | NULL | |
| default_value | varchar(255) | YES | | NULL | |
| type | varchar(255) | NO | | | |
| group | int unsigned | YES | | NULL | |
| group_template | text | YES | | NULL | |
| column | varchar(255) | YES | | NULL | |
| visible | tinyint unsigned | NO | | 1 | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+----------------+------------------+------+-----+-------------------+-------------------+
12 rows in set (0.00 sec)

mysql> describe custom_forms;
+---------------------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| list | int unsigned | NO | MUL | NULL | |
| name | varchar(255) | YES | | | |
| description | text | YES | | NULL | |
| fields_shown_on_subscribe | varchar(255) | YES | | | |
| fields_shown_on_manage | varchar(255) | YES | | | |
| layout | longtext | YES | | NULL | |
| form_input_style | longtext | YES | | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+---------------------------+--------------+------+-----+-------------------+-------------------+
9 rows in set (0.00 sec)

mysql> describe custom_forms_data;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| form | int unsigned | NO | MUL | NULL | |
| data_key | varchar(255) | YES | | | |
| data_value | longtext | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> describe import_failed;
+---------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| import | int unsigned | NO | MUL | NULL | |
| email | varchar(255) | NO | | | |
| reason | varchar(255) | YES | | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+---------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

mysql> describe importer
-> ;
+------------+------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| list | int unsigned | NO | MUL | NULL | |
| type | tinyint unsigned | NO | | 1 | |
| path | varchar(255) | NO | | | |
| size | int unsigned | NO | | 0 | |
| delimiter | varchar(1) | NO | | , | |
| emailcheck | tinyint unsigned | NO | | 1 | |
| status | tinyint unsigned | NO | | 0 | |
| error | varchar(255) | YES | | NULL | |
| processed | int unsigned | NO | | 0 | |
| new | int unsigned | NO | | 0 | |
| failed | int unsigned | NO | | 0 | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| mapping | text | NO | | NULL | |
| finished | timestamp | YES | | NULL | |
+------------+------------------+------+-----+-------------------+-------------------+
15 rows in set (0.00 sec)

mysql> describe links;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| cid | varchar(255) | NO | UNI | | |
| campaign | int unsigned | NO | MUL | NULL | |
| url | varchar(255) | NO | | | |
| clicks | int unsigned | NO | | 0 | |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> describe users;
+--------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(255) | NO | MUL | | |
| password | varchar(255) | NO | | | |
| email | varchar(255) | YES | UNI | NULL | |
| access_token | varchar(40) | YES | MUL | NULL | |
| reset_token | varchar(255) | YES | MUL | NULL | |
| reset_expire | timestamp | YES | | NULL | |
| created | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+--------------+--------------+------+-----+-------------------+-------------------+
8 rows in set (0.01 sec)

mysql> describe segments;
+---------+------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| list | int unsigned | NO | MUL | NULL | |
| name | varchar(255) | NO | MUL | | |
| type | tinyint unsigned | NO | | NULL | |
| created | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+---------+------------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

mysql> describe settings;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| key | varchar(255) | NO | UNI | | |
| value | text | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe subscription;
+----------------+------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| cid | varchar(255) | NO | UNI | NULL | |
| email | varchar(255) | NO | UNI | | |
| opt_in_ip | varchar(100) | YES | | NULL | |
| opt_in_country | varchar(2) | YES | | NULL | |
| tz | varchar(100) | YES | MUL | NULL | |
| imported | int unsigned | YES | | NULL | |
| status | tinyint unsigned | NO | MUL | 1 | |
| is_test | tinyint unsigned | NO | MUL | 0 | |
| status_change | timestamp | YES | | NULL | |
| latest_open | timestamp | YES | MUL | NULL | |
| latest_click | timestamp | YES | MUL | NULL | |
| created | timestamp | NO | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| first_name | varchar(255) | YES | MUL | NULL | |
| last_name | varchar(255) | YES | MUL | NULL | |
+----------------+------------------+------+-----+-------------------+-------------------+
15 rows in set (0.01 sec)

mysql> describe templates;
+-------------+--------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | MUL | | |
| description | text | YES | | NULL | |
| editor_name | varchar(50) | YES | | | |
| editor_data | longtext | YES | | NULL | |
| html | longtext | YES | | NULL | |
| text | longtext | YES | | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+--------------+------+-----+-------------------+-------------------+
8 rows in set (0.01 sec)

describe triggers;
+-----------------+------------------+------+-----+-------------------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+-------------------+-------------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | MUL | | |
| description | text | YES | | NULL | |
| enabled | tinyint unsigned | NO | MUL | 1 | |
| list | int unsigned | NO | MUL | NULL | |
| segment | int unsigned | NO | | NULL | |
| source_campaign | int unsigned | YES | MUL | NULL | |
| rule | varchar(255) | NO | | column | |
| column | varchar(255) | YES | MUL | NULL | |
| seconds | int | NO | | 0 | |
| dest_campaign | int unsigned | YES | MUL | NULL | |
| count | int unsigned | NO | | 0 | |
| last_check | timestamp | YES | MUL | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-----------------+------------------+------+-----+-------------------+-------------------+
14 rows in set (0.00 sec)

describe tzoffset;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| tz | varchar(100) | NO | PRI | | |
| offset | int | NO | | 0 | |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)