Migration failed in latest version

[milindpatel63]

I have been running ghost latest version in docker on Ubuntu 20.04 for quite a while...and it's been running fine...
Now yesterday, after watchtower update, it failed with error

alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.

[milindpatel63]

I couldn't find out which version was I running previously, but I rolled back to 4.34.3 and it seems to work just fine...

I looked at the commit in GitHub, and the next version is 4.35, which is the latest one right one, and I tried manually switching back to 4.35 and it still gave migration error...

I am using MySQL 8 as database...

[tianon]

I imagine it would probably help to narrow down a little more exactly which version to which version gave the migration error, but I'll have to defer to @acburdine for more as I'm not very familiar with Ghost's migrations. 🙈

[milindpatel63]

I imagine it would probably help to narrow down a little more exactly which version to which version gave the migration error, but I'll have to defer to @acburdine for more as I'm not very familiar with Ghost's migrations. 🙈

yes, it was migrating from 4.34.3 to the next release 4.35 which gave migration error on my server...

although they have released 4.36 right now, but it's not up on docker hub...I'll try migrating when its up and report if it works...

[milindpatel63]

still an issue in version 4.36...
same migration error in mysql database...
i can't go beyond 4.34.3 version

[Torqu3Wr3nch]

Possibly related to this issue?

TryGhost/Ghost#14144

[Torqu3Wr3nch]

Another user using our Docker image reporting this in the official Ghost forums as well:
https://forum.ghost.org/t/error-after-update-to-4-35-0/28349/2

[milindpatel63]

Possibly related to this issue?

TryGhost/Ghost#14144

nope...mine gives a different MySQL error...i posted above..

[Torqu3Wr3nch]

I'm aware that the error messages are different, what's curious is that they both happen to involve the same table.

Regardless, thanks for reporting the issue. I was about to update this weekend (from 4.34.3) and now I'm thinking I'm going to hold back!

[ajfriesen]

I run into this as well: https://forum.ghost.org/t/error-after-update-to-4-35-0/28349/2

I tried to replicate the error locally with docker compose.
I start ghost:4.34.3 with mysql:8. Wait for the initial database to finish.
Add a product to a post for testing purpose.
Start ghost:4.35.0 and no error.

However, on my production blog, I can not update

[petrus-v]

I'm getting same issue in docker environment moving from 4.32.3 to 4.36.0 and latest mysql:8 docker image.

ghost_1  | [2022-02-16 13:46:04] ERROR alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
ghost_1  | 
ghost_1  | alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
ghost_1  | 
ghost_1  | {"config":{"transaction":false},"name":"2022-01-20-05-55-add-post-products-table.js"}
ghost_1  | "Error occurred while executing the following migration: 2022-01-20-05-55-add-post-products-table.js"

[Torqu3Wr3nch]

Upgraded from 4.34.3 to 4.36.1 without a problem. I did not have the post_products table prior to this and I see that it was created with a database migration in the Docker log. I recently just moved to a Docker Ghost container from a local install, so I suspect everyone's problem here either occurred in a previous version or my table is blank (I don't use Ghost memberships).

[Niels-75]

Solution Proposal

Login to your mysql database with an admin user, may be mysql -u root -p, and execute:

SET PERSIST default_collation_for_utf8mb4 = utf8mb4_general_ci;

Afterwards upgrade (or restart) your ghost server.
This will successfully (re-)execute the failed migrations.
This solution works for container-based installations as well.

Validate Config

SELECT * FROM performance_schema.persisted_variables;

SELECT @@default_collation_for_utf8mb4;

Background

The default collation for charset utf8mb4 in MySQL 5.7 is utf8mb4_general_ci, but has been changed to utf8mb4_0900_ai_ci with MySQL 8.0. See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_collation_for_utf8mb4.
So, the proposed solution is to switch back to the previous default.

For instance, if created with MySQL 5.7, table posts looks like

CREATE TABLE `posts` (
  `id` varchar(24) COLLATE utf8mb4_general_ci NOT NULL,
...

And now, with the the new default of MySQL 8.0, table posts_products would be created as

CREATE TABLE `posts_products` (
...
  `post_id` varchar(24) COLLATE utf8mb4_0900_ai_ci NOT NULL,
...
  CONSTRAINT `posts_products_post_id_foreign` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
...

This results in the reported error:

alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.

Remark

The issue does not occur, if you start with a new Ghost installation on MySQL 8.0.

If you started with your Ghost database on MySQL 5.7 and want to go on with the new default collation of MySQL 8.0, you may have two options:

Option 1 (Not verified myself)

Create a new Ghost installation on MySQL 8.0.
Export and import all your content from the old installation into the new installation.

Option 2 (Not verified myself)

Manually migrate your tables. This is possible, but tricky: If you try to change the collation of columns, like

ALTER TABLE `posts`
  MODIFY `id` varchar(24) COLLATE utf8mb4_0900_ai_ci NOT NULL;

you get a similar error with respect to already existing foreign key constraints. Possible solution:

1. Delete all foreign key constraints of the Ghost database model.
2. Migrate all columns (which have the old collation) to the new collation.
3. Re-create the deleted foreign key constraints.

(Caution: If done manually, this is very error prone!)

[wadewinningham]

The config change worked for me, but if it's an option, dumping your database, performing a search/replace on utf8mb4_general_ci with utf8mb4_0900_ai_ci then restoring the file should work as well.

[petemahon]

I have replicated the bug on Linux Mint, with the error on the following upgrade path:
Unable to upgrade Ghost from v4.34.3 to v4.36.2. Would you like to revert back to v4.34.3?

Running on Linux Mint 19.3 Cinnamon, Ubuntu Bionic
OS: LinuxMint, v19.3
Node Version: v14.17.0
Ghost Version: 4.36.2
Ghost-CLI Version: 1.18.1

Upgraded today to MYSQL8 from v5.7 prior to upgrading Ghost.

Ran standard Ghost update and got error above. Tried various solutions:

• edited mysql.cnf with init_connect hard-coded and restarted MYSQL daemon
• logged in to DB as root and executed ALTER DATABASE ghost_production DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci, restarted MYSQL daemon.
• logged in to DB as root and executed SET PERSIST default_collation_for_utf8mb4 = utf8mb4_general_ci;

After each attempt to fix, tried restarting, updating, force updating etc.

The only thing which worked was setting the persistent parameter and then restarting the MYSQL daemon.

Recommended solution on Ubuntu flavoured OS:

1. Login to database
   $ mysql -u username -p
   $ enter password

2. Set persistent DB parameter
   mysql> SET PERSIST default_collation_for_utf8mb4 = utf8mb4_general_ci;
   mysql> exit

3. Restart MYSQL service
   $ sudo service mysql restart

Hope this helps for others. @Niels-75 thanks for your solution.

[like-a-freedom]

Got the same problem during the update (from 4.x to 4.36.3) on DO droplet (Ubuntu 20.04.4 LTS, MySQL 8+).
My solution:

• SET PERSIST default_collation_for_utf8mb4 = utf8mb4_general_ci;
• create new table posts_products
• run ghost

I found that migration 2022-01-20-05-55-add-post-products-table.js was unable to apply:

ghost@ghost-vm:/var/www/ghost$ cat ./versions/4.36.3/core/server/data/migrations/versions/4.35/2022-01-20-05-55-add-post-products-table.js
const {addTable} = require('../../utils');

module.exports = addTable('posts_products', {
    id: {type: 'string', maxlength: 24, nullable: false, primary: true},
    post_id: {type: 'string', maxlength: 24, nullable: false, references: 'posts.id', cascadeDelete: true},
    product_id: {type: 'string', maxlength: 24, nullable: false, references: 'products.id', cascadeDelete: true},
    sort_order: {type: 'integer', nullable: false, unsigned: true, defaultTo: 0}
});

I tried to create table manually and that was ok, so the Ghost CMS ran well

mysql> CREATE TABLE posts_products (
    -> id VARCHAR(24) NOT NULL PRIMARY KEY,
    -> post_id VARCHAR(24) COLLATE utf8mb4_0900_ai_ci NOT NULL, CONSTRAINT posts_products_post_id_foreign FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
    -> product_id VARCHAR(24) NOT NULL, CONSTRAINT posts_products_product_id_foreign FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
    -> sort_order INT UNSIGNED NOT NULL DEFAULT 0);

[ajfriesen]

The config change worked for me, but if it's an option, dumping your database, performing a search/replace on utf8mb4_general_ci with utf8mb4_0900_ai_ci then restoring the file should work as well.

I can confirm this method and it worked just fine on my blog

My steps:

1. Dump the database
2. Create a copy of that SQL dump
3. Replace occurrences of utf8mb4_general_ci with utf8mb4_0900_ai_ci
sed -i 's/utf8mb4_general_ci/utf8mb4_0900_ai_ci/g' new-db.sql
4. Drop ghost database
5. Create a new ghost database
6. Import the new-db.sql dump

Your site should be available again and the update did work flawlessly afterward.

[tianon]

Is this still an issue? (Also relevant: is this an issue that's specific to this Docker image, or one that's general to the Ghost upgrade/migration process?) 👀

[ajfriesen]

As far as I understand:

• This is not specific to docker
• This is also not specific to an operating system

This only happens when you have used started using ghost with MySQL 5.7 and then migrated to MySQL 8.0.

Because these both versions have different defaults you will run into this migration problem.

Why?
Most likely because ghost is using MySQL 8 for a long time now and they did not encounter this case since it depends on the specific setup migrating from MySQL 5.7 over to MySQL 8 and then performing this migration in the ghost version where this pops up.

[tianon]

Thanks for confirming! I'm going to close this (as there's not something we're going to be able to do to fix it in the image), but feel free to continue the conversation to determine the appropriate place to move the discussion. 👍

[surfer190]

Has this issue been raised with MySQL. It had probably wasted 1 million years of developer time so far.

[newtonnthiga]

None of the solutions seem to work for me. I'm using Google's Cloud SQL. I asked a question with details on the forum https://forum.ghost.org/t/database-migration-lock-after-updating-ghost/34667 but i haven't gotten any feedback on there.

Does anyone know of a fix?
You can check https://forum.ghost.org/t/database-migration-lock-after-updating-ghost/34667 for details