Migration error after upgrading from 3.32.2 to 3.33.0

Developer help
1

Hi guys,
There seems to be some issue regarding the migrations when trying to upgrade Ghsot from version 3.32.2 to latest one - 3.33.0

[2020-09-15 14:15:23] INFO Ghost boot 4.107s                                                                                                                                                                                                                                                                                                                                                                 
[2020-09-15 14:15:23] INFO Creating database backup                                                                                                                                                                                                                                                                                                                                                          
[2020-09-15 14:15:23] INFO Database backup written to: /var/www/ghost_ro/content/data/lungu-florin.ghost.2020-09-15-12-15-23.json                                                                                                                                                                                                                                                                            
[2020-09-15 14:15:23] INFO Adding table: email_batches                                                                                                                                                                                                                                                                                                                                                       
[2020-09-15 14:15:24] WARN Skipping drop table "email_recipients" - does not exist                                                                                                                                                                                                                                                                                                                           
[2020-09-15 14:15:24] INFO Dropping table: email_batches                                                                                                                                                                                                                                                                                                                                                     
[2020-09-15 14:15:24] ERROR                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
NAME: MigrationScriptError                                                                                                                                                                                                                                                                                                                                                                                   
CODE: UNKNOWN_CODE_PLEASE_REPORT                                                                                                                                                                                                                                                                                                                                                                             
MESSAGE: alter table `email_batches` add constraint `email_batches_email_id_foreign` foreign key 
(`email_id`) references `emails` (`id`) - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 
'email_id' and referenced column 'id' in foreign key constraint 'email_batches_email_id_foreign' are 
incompatible.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
level: normal                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
{"name":"01-add-email-recipients-tables.js"}                                                                                                                                                                                                                                                                                                                                                                 
"Error occurred while executing the following migration: 01-add-email-recipients-tables.js"                                                                                                                                                                                                                                                                                                                  
MigrationScriptError: alter table `email_batches` add constraint `email_batches_email_id_foreign` 
foreign key (`email_id`) references `emails` (`id`) - UNKNOWN_CODE_PLEASE_REPORT: Referencing 
column 'email_id' and referenced column 'id' in foreign key constraint 'email_batches_email_id_foreign' 
are incompatible.                                                                                        at 
MigrationScriptError.KnexMigrateError (/var/www/ghost_ro/versions/3.33.0/node_modules/knex-
migrator/lib/errors.js:7:26)                                                                                                                                                                                                                                                                                   
at new MigrationScriptError (/var/www/ghost_ro/versions/3.33.0/node_modules/knex-
migrator/lib/errors.js:25:26)                                                                                                                                                                                                                                                                                               
at /var/www/ghost_ro/versions/3.33.0/node_modules/knex-migrator/lib/index.js:1055:19                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
Error: UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'email_id' and referenced column 'id' 
in foreign key constraint 'email_batches_email_id_foreign' are incompatible.                                                                                                                                                                                                                                         
at Query.Sequence._packetToError 
(/var/www/ghost_ro/versions/3.33.0/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)

System informations:
OS: Ubuntu Server 20.04
MySQL: 8.0.21
Ghost: 3.33.0
Ghost install: ghost-cli

It seems that schemas creation fails or is not done correctly.

After now, all upgrades done through ghost-cli gone fine without errors.

2

@floryn90 we diagnosed the error in your issue, you need to change your database setup…

| latin1 | latin1_swedish_ci |

This is the main problem, somehow your default database charset/collation has changed. That means the new tables are being created with a different charset to the existing emails table so the columns used in the foreign key constraint are not compatible.

You’ll need to modify your database server config (or specific database settings) to use the utf8mb4 charset and utf8mb4_general_ci collation.

3

Hi @Kevin, Thanks for your reply! I changed the database setup and tried again without success.

I think it’s not so relevant since all updates worked fine until this release and I’m using this setup from some time ago.

4

mysql> show full columns from emails where Field = “id”;
±------±------------±-------------------±-----±----±--------±------±--------------------------------±--------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
±------±------------±-------------------±-----±----±--------±------±--------------------------------±--------+
| id | varchar(24) | utf8mb4_general_ci | NO | PRI | NULL | | select,insert,update,references |
±------±------------±-------------------±-----±----±--------±------±--------------------------------±--------+
1 row in set (0.01 sec)

mysql> SELECT @@character_set_database, @@collation_database;
±-------------------------±---------------------+
| @@character_set_database | @@collation_database |
±-------------------------±---------------------+
| utf8mb4 | utf8mb4_general_ci |
±-------------------------±---------------------+
1 row in set (0.00 sec)

5

fixed the issue by commenting the down function from /core/server/data/migrations/versions/3.33/01-add-email-recipients-tables.js