search

TryGhost / Ghost

Independent technology for modern publishing, memberships, subscriptions and newsletters.
https://ghost.org
MIT License
46.82k stars 10.2k forks source link

Upgrade ghost failed - migration migration is into error state #13736

Closed qdupuy closed 2 years ago

qdupuy commented 2 years ago

Hello,

I am trying to do a ghost migration from the version 4.8.3 to the 4.9.3 version. However, I get the following error :

- Inspecting operating system
[2021-09-10 11:42:46] INFO Ghost is running in production...
[2021-09-10 11:42:46] INFO Your site is now available on https://x/
[2021-09-10 11:42:46] INFO Ctrl+C to shut down
[2021-09-10 11:42:46] INFO Ghost server started in 1.126s
[2021-09-10 11:42:47] WARN Database state requires migration.
[2021-09-10 11:42:49] INFO Creating database backup
[2021-09-10 11:42:49] INFO Database backup written to: /bitnami/ghost/content/data/x-blog.ghost.2021-09-10-11-42-49.json
[2021-09-10 11:42:49] INFO Adding posts_meta.feature_image_alt column
[2021-09-10 11:42:49] INFO Adding posts_meta.feature_image_caption column
[2021-09-10 11:42:49] INFO Adding "portal_products" record to "settings" table with product - 6092756d29bf23000188d5d7
[2021-09-10 11:42:49] INFO Skipping migration of newsletter_show_header setting. Matches new defaults
[2021-09-10 11:42:49] INFO Deleting newsletter_show_header setting
[2021-09-10 11:42:49] INFO Adding permission for resetAllPasswords:authentication
[2021-09-10 11:42:49] WARN Adding permission(Reset all passwords) to role(Administrator)
[2021-09-10 11:42:49] INFO Adding table: benefits
[2021-09-10 11:42:50] INFO Adding table: products_benefits
[2021-09-10 11:42:50] INFO Dropping table: products_benefits
[2021-09-10 11:42:50] INFO Dropping table: benefits
[2021-09-10 11:42:50] INFO Removing permission(Reset all passwords) from role(Administrator)
[2021-09-10 11:42:50] INFO Removing permission for resetAllPasswords:authentication
[2021-09-10 11:42:50] INFO Adding newsletter_show_header setting
[2021-09-10 11:42:50] INFO Deleting newsletter_show_header_{title,icon} settings
[2021-09-10 11:42:50] INFO Reverting portal_products setting to empty array
[2021-09-10 11:42:50] INFO Removing posts_meta.feature_image_caption column
[2021-09-10 11:42:50] INFO Removing posts_meta.feature_image_alt column
[2021-09-10 11:42:50] ERROR alter table `products_benefits` add constraint `products_benefits_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can't create table `x_blog_ghost`.`products_benefits` (errno: 150 "Foreign key constraint is incorrectly formed")

alter table `products_benefits` add constraint `products_benefits_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can't create table `x_blog_ghost`.`products_benefits` (errno: 150 "Foreign key constraint is incorrectly formed")

{"config":{"transaction":false},"name":"03-add-products-benefits-table.js"}
"Error occurred while executing the following migration: 03-add-products-benefits-table.js"

Error ID:
    300

Error Code: 
    ER_CANT_CREATE_TABLE

----------------------------------------

InternalServerError: alter table `products_benefits` add constraint `products_benefits_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can't create table `x_blog_ghost`.`products_benefits` (errno: 150 "Foreign key constraint is incorrectly formed")
    at DatabaseStateManager.makeReady (/opt/bitnami/ghost/versions/4.9.3/core/server/data/db/state-manager.js:98:32)
    at async initDatabase (/opt/bitnami/ghost/versions/4.9.3/core/boot.js:57:5)
    at async bootGhost (/opt/bitnami/ghost/versions/4.9.3/core/boot.js:320:9)

Looking on the web, I can’t necessarily find anything about my problem. Some people talk about a knex problem.

Did I miss a step?

Despite my research and the outcome #13426 I still have the problem and it bothers me greatly that I am not up to date

github-actions[bot] commented 2 years ago

Note from our bot: The needs info label has been added to this issue. Updating your original issue with more details is great, but won't notify us, so please make sure you leave a comment so that we can see when you've updated us.

daniellockyer commented 2 years ago

@qdupuy Have you followed the documentation we gave in the final comment of #13426? I suspect that your products table has a different collation than the default of your DB, so any new tables with foreign keys would become malformed and fail during creation. To confirm whether this is the case, please can you run the following and paste the results here:

# Collation default
SHOW COLLATION WHERE `Default` = 'Yes';

# Displays the collation of `products`
SHOW CREATE TABLE products;

It's also worth trying to update to the latest version of Ghost because we've fixed several issues with migrations since 4.9.3, and we'd be unable to fix any potential bugs unless you updated to the latest.

I will leave this issue open for a short time but I believe we have already linked to the solution and it's usually down to database configuration errors.

qdupuy commented 2 years ago

output for collation :

SHOW COLLATION WHERE `Default` = 'Yes';
+---------------------+----------+----+---------+----------+---------+
| Collation           | Charset  | Id | Default | Compiled | Sortlen |
+---------------------+----------+----+---------+----------+---------+
| big5_chinese_ci     | big5     |  1 | Yes     | Yes      |       1 |
| dec8_swedish_ci     | dec8     |  3 | Yes     | Yes      |       1 |
| cp850_general_ci    | cp850    |  4 | Yes     | Yes      |       1 |
| hp8_english_ci      | hp8      |  6 | Yes     | Yes      |       1 |
| koi8r_general_ci    | koi8r    |  7 | Yes     | Yes      |       1 |
| latin1_swedish_ci   | latin1   |  8 | Yes     | Yes      |       1 |
| latin2_general_ci   | latin2   |  9 | Yes     | Yes      |       1 |
| swe7_swedish_ci     | swe7     | 10 | Yes     | Yes      |       1 |
| ascii_general_ci    | ascii    | 11 | Yes     | Yes      |       1 |
| ujis_japanese_ci    | ujis     | 12 | Yes     | Yes      |       1 |
| sjis_japanese_ci    | sjis     | 13 | Yes     | Yes      |       1 |
| hebrew_general_ci   | hebrew   | 16 | Yes     | Yes      |       1 |
| tis620_thai_ci      | tis620   | 18 | Yes     | Yes      |       4 |
| euckr_korean_ci     | euckr    | 19 | Yes     | Yes      |       1 |
| koi8u_general_ci    | koi8u    | 22 | Yes     | Yes      |       1 |
| gb2312_chinese_ci   | gb2312   | 24 | Yes     | Yes      |       1 |
| greek_general_ci    | greek    | 25 | Yes     | Yes      |       1 |
| cp1250_general_ci   | cp1250   | 26 | Yes     | Yes      |       1 |
| gbk_chinese_ci      | gbk      | 28 | Yes     | Yes      |       1 |
| latin5_turkish_ci   | latin5   | 30 | Yes     | Yes      |       1 |
| armscii8_general_ci | armscii8 | 32 | Yes     | Yes      |       1 |
| utf8_general_ci     | utf8     | 33 | Yes     | Yes      |       1 |
| ucs2_general_ci     | ucs2     | 35 | Yes     | Yes      |       1 |
| cp866_general_ci    | cp866    | 36 | Yes     | Yes      |       1 |
| keybcs2_general_ci  | keybcs2  | 37 | Yes     | Yes      |       1 |
| macce_general_ci    | macce    | 38 | Yes     | Yes      |       1 |
| macroman_general_ci | macroman | 39 | Yes     | Yes      |       1 |
| cp852_general_ci    | cp852    | 40 | Yes     | Yes      |       1 |
| latin7_general_ci   | latin7   | 41 | Yes     | Yes      |       1 |
| utf8mb4_general_ci  | utf8mb4  | 45 | Yes     | Yes      |       1 |
| cp1251_general_ci   | cp1251   | 51 | Yes     | Yes      |       1 |
| utf16_general_ci    | utf16    | 54 | Yes     | Yes      |       1 |
| utf16le_general_ci  | utf16le  | 56 | Yes     | Yes      |       1 |
| cp1256_general_ci   | cp1256   | 57 | Yes     | Yes      |       1 |
| cp1257_general_ci   | cp1257   | 59 | Yes     | Yes      |       1 |
| utf32_general_ci    | utf32    | 60 | Yes     | Yes      |       1 |
| binary              | binary   | 63 | Yes     | Yes      |       1 |
| geostd8_general_ci  | geostd8  | 92 | Yes     | Yes      |       1 |
| cp932_japanese_ci   | cp932    | 95 | Yes     | Yes      |       1 |
| eucjpms_japanese_ci | eucjpms  | 97 | Yes     | Yes      |       1 |
+---------------------+----------+----+---------+----------+---------+
40 rows in set (0.001 sec)

products output : 

SHOW CREATE TABLE products;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| products | CREATE TABLE `products` (
  `id` varchar(24) NOT NULL,
  `name` varchar(191) NOT NULL,
  `slug` varchar(191) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `description` varchar(191) DEFAULT NULL,
  `monthly_price_id` varchar(24) DEFAULT NULL,
  `yearly_price_id` varchar(24) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

And yes, before posting, I did some research and testing to correct myself. I don't make an issue as soon as I encounter an error and don't look for a solution first

github-actions[bot] commented 2 years ago

Hey @qdupuy 👋

Our team needed some more info to get to the bottom of this, however we've not heard back from you. We're going to close this for now, but let us know if you manage to dig up some more info and we'll reopen.

HujinoKun commented 2 years ago

Already answered with informations

qdupuy commented 2 years ago

@daniellockyer Re-open this issue plz

ah, I just saw that I replied with my other account on the previous comment

qdupuy commented 2 years ago

Up

@daniellockyer

If you are missing information, don't hesitate to contact me

daniellockyer commented 2 years ago

@qdupuy Can you confirm you have tried updating to the latest Ghost version?

qdupuy commented 2 years ago

@daniellockyer Yes,

Trying with 4.32.0

Error :

[2022-01-04 13:32:35] ERROR alter table `products_benefits` add constraint `products_benefits_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can't create table `x_blog_ghost`.`products_benefits` (errno: 150 "Foreign key constraint is incorrectly formed")

alter table `products_benefits` add constraint `products_benefits_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can't create table `x_blog_ghost`.`products_benefits` (errno: 150 "Foreign key constraint is incorrectly formed")

{"config":{"transaction":false},"name":"03-add-products-benefits-table.js"}
"Error occurred while executing the following migration: 03-add-products-benefits-table.js"

Error ID:
    300

Error Code: 
    ER_CANT_CREATE_TABLE

----------------------------------------

MigrationScriptError: alter table `products_benefits` add constraint `products_benefits_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can't create table `x_blog_ghost`.`products_benefits` (errno: 150 "Foreign key constraint is incorrectly formed")
    at DatabaseStateManager.makeReady (/opt/bitnami/ghost/versions/4.32.0/core/server/data/db/state-manager.js:95:32)
    at MigrationScriptError.KnexMigrateError (/opt/bitnami/ghost/versions/4.32.0/node_modules/knex-migrator/lib/errors.js:7:26)
    at new MigrationScriptError (/opt/bitnami/ghost/versions/4.32.0/node_modules/knex-migrator/lib/errors.js:25:26)
    at /opt/bitnami/ghost/versions/4.32.0/node_modules/knex-migrator/lib/index.js:1032:19
    at processTicksAndRejections (internal/process/task_queues.js:95:5)

Error: ER_CANT_CREATE_TABLE: Can't create table `x_blog_ghost`.`products_benefits` (errno: 150 "Foreign key constraint is incorrectly formed")
    at Query.Sequence._packetToError (/opt/bitnami/ghost/versions/4.32.0/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/opt/bitnami/ghost/versions/4.32.0/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/opt/bitnami/ghost/versions/4.32.0/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/opt/bitnami/ghost/versions/4.32.0/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/opt/bitnami/ghost/versions/4.32.0/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/opt/bitnami/ghost/versions/4.32.0/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/opt/bitnami/ghost/versions/4.32.0/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/opt/bitnami/ghost/versions/4.32.0/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:293:12)
    at readableAddChunk (internal/streams/readable.js:267:9)
    at Socket.Readable.push (internal/streams/readable.js:206:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    --------------------
    at Protocol._enqueue (/opt/bitnami/ghost/versions/4.32.0/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/opt/bitnami/ghost/versions/4.32.0/node_modules/mysql/lib/Connection.js:198:25)
    at /opt/bitnami/ghost/versions/4.32.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:134:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/opt/bitnami/ghost/versions/4.32.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:128:12)
    at Client_MySQL.query (/opt/bitnami/ghost/versions/4.32.0/node_modules/knex-migrator/node_modules/knex/lib/client.js:168:17)
    at Runner.query (/opt/bitnami/ghost/versions/4.32.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:151:36)
    at Runner.queryArray (/opt/bitnami/ghost/versions/4.32.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:248:31)
    at processTicksAndRejections (internal/process/task_queues.js:95:5)
    at async /opt/bitnami/ghost/versions/4.32.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:277:18
    at async Object.up (/opt/bitnami/ghost/versions/4.32.0/core/server/data/migrations/utils.js:257:13)

[2022-01-04 13:32:35] WARN Ghost is shutting down
[2022-01-04 13:32:35] WARN Ghost has shut down
[2022-01-04 13:32:35] WARN Your site is now offline
[2022-01-04 13:32:35] WARN Ghost was running for a few seconds
daniellockyer commented 2 years ago

@qdupuy Ok, thanks for confirming that 🙂 Can you paste the output of the following SQL query against your DB?

SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema = 'x_blog_ghost';
qdupuy commented 2 years ago

Of course,

Output :

MariaDB [x_blog_ghost]> SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_schema = 'x_blog_ghost';
+--------------------+----------------------------------------+--------------------+
| table_schema       | table_name                             | table_collation    |
+--------------------+----------------------------------------+--------------------+
| x_blog_ghost | actions                                | utf8mb4_general_ci |
| x_blog_ghost | api_keys                               | utf8mb4_general_ci |
| x_blog_ghost | brute                                  | utf8mb4_general_ci |
| x_blog_ghost | email_batches                          | utf8mb4_general_ci |
| x_blog_ghost | email_recipients                       | utf8mb4_general_ci |
| x_blog_ghost | emails                                 | utf8mb4_general_ci |
| x_blog_ghost | integrations                           | utf8mb4_general_ci |
| x_blog_ghost | invites                                | utf8mb4_general_ci |
| x_blog_ghost | labels                                 | utf8mb4_general_ci |
| x_blog_ghost | members                                | utf8mb4_general_ci |
| x_blog_ghost | members_email_change_events            | utf8mb4_general_ci |
| x_blog_ghost | members_labels                         | utf8mb4_general_ci |
| x_blog_ghost | members_login_events                   | utf8mb4_general_ci |
| x_blog_ghost | members_paid_subscription_events       | utf8mb4_general_ci |
| x_blog_ghost | members_payment_events                 | utf8mb4_general_ci |
| x_blog_ghost | members_products                       | utf8mb4_general_ci |
| x_blog_ghost | members_status_events                  | utf8mb4_general_ci |
| x_blog_ghost | members_stripe_customers               | utf8mb4_general_ci |
| x_blog_ghost | members_stripe_customers_subscriptions | utf8mb4_general_ci |
| x_blog_ghost | members_subscribe_events               | utf8mb4_general_ci |
| x_blog_ghost | migrations                             | utf8mb4_general_ci |
| x_blog_ghost | migrations_lock                        | utf8mb4_general_ci |
| x_blog_ghost | mobiledoc_revisions                    | utf8mb4_general_ci |
| x_blog_ghost | permissions                            | utf8mb4_general_ci |
| x_blog_ghost | permissions_roles                      | utf8mb4_general_ci |
| x_blog_ghost | permissions_users                      | utf8mb4_general_ci |
| x_blog_ghost | posts                                  | utf8mb4_general_ci |
| x_blog_ghost | posts_authors                          | utf8mb4_general_ci |
| x_blog_ghost | posts_meta                             | utf8mb4_general_ci |
| x_blog_ghost | posts_tags                             | utf8mb4_general_ci |
| x_blog_ghost | products                               | utf8mb4_general_ci |
| x_blog_ghost | roles                                  | utf8mb4_general_ci |
| x_blog_ghost | roles_users                            | utf8mb4_general_ci |
| x_blog_ghost | sessions                               | utf8mb4_general_ci |
| x_blog_ghost | settings                               | utf8mb4_general_ci |
| x_blog_ghost | snippets                               | utf8mb4_general_ci |
| x_blog_ghost | stripe_prices                          | utf8mb4_general_ci |
| x_blog_ghost | stripe_products                        | utf8mb4_general_ci |
| x_blog_ghost | tags                                   | utf8mb4_general_ci |
| x_blog_ghost | tokens                                 | utf8mb4_general_ci |
| x_blog_ghost | users                                  | utf8mb4_general_ci |
| x_blog_ghost | webhooks                               | utf8mb4_general_ci |
+--------------------+----------------------------------------+--------------------+
42 rows in set (0.002 sec)

@daniellockyer

matthanley commented 2 years ago

Hey @qdupuy - we've gone about as far as we can trying to diagnose this with you, and so far we don't have any reproduction case.

I'm closing this issue for now, but if you can document steps we can follow to reliably reproduce this I'll be happy to take another look.

We use GitHub solely for bug-tracking and on-going feature development, and right now we have no evidence that this is a bug in Ghost rather than an environmental issue.

Our forum is a great place to get community support, plus it helps create a central location for searching problems/solutions.

Thanks!

qdupuy commented 2 years ago

Hello @matthanley

Re-open this issue please.

ghost migration from the version 4.8.3 to the 4.9.3 version,

Kubernetes version : 1.20

Mariadb version : 10.5.11

if you have the sql query to reproduce the creation of the products_benefits table, i would like to have it in order to do a new migration test