search

TryGhost / Ghost

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

Database error after upgrade to 4.44 also on Mysql #14853

Closed fabsau closed 2 years ago

fabsau commented 2 years ago

Issue Summary

Since I could not solve the issue I posted in the forum, which also has been mentioned at #14516, I migrated my blog from mariadb to mysql. Unfortunately the issue persists. For the migration from MariaDB to Mysql 8 I used this guide Under 4.41.3-alpine + mysql everything is working fine. So I see the issue in the upgrade process. Mysql 8 is running with default settings. I also tested 4.46 and 4.47, the error log was always the same.

According to your FAQ "Troubleshooting MySQL databases" the error could be due to enabled ANSI, but this does not seem to be the case(?, not an expert on databases) mysql> SELECT @@sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

Steps to Reproduce

  1. Have running ghost running 4.41.3
  2. Upgrade to 4.44 or higher

Ghost Version

4.44

Node.js Version

Included in docker image I guess

How did you install Ghost?

Docker 20.10.12, Ubuntu 20.04.4 LTS

Database type

MySQL 8

Browser & OS version

No response

Relevant log / error output

2022-05-17T17:32:04.987620726Z [2022-05-17 17:32:04] INFO Ghost is running in production...
2022-05-17T17:32:04.987665341Z [2022-05-17 17:32:04] INFO Your site is now available on https://fabio.sauna.re/
2022-05-17T17:32:04.987671116Z [2022-05-17 17:32:04] INFO Ctrl+C to shut down
2022-05-17T17:32:04.991046940Z [2022-05-17 17:32:04] INFO Ghost server started in 1.691s
2022-05-17T17:32:05.494667737Z [2022-05-17 17:32:05] WARN Database state requires migration.
2022-05-17T17:32:06.132995946Z [2022-05-17 17:32:06] INFO Creating database backup
2022-05-17T17:32:06.140426552Z Ignoring invalid configuration option passed to Connection: filename. This is currently a warning, but in future versions of MySQL2, an error will be thrown if you pass an invalid configuration option to a Connection
2022-05-17T17:32:06.143656223Z Ignoring invalid configuration option passed to Connection: filename. This is currently a warning, but in future versions of MySQL2, an error will be thrown if you pass an invalid configuration option to a Connection
2022-05-17T17:32:06.214741183Z [2022-05-17 17:32:06] INFO Database backup written to: /var/lib/ghost/content/data/fabio-sauna.ghost.2022-05-17-17-32-06.json
2022-05-17T17:32:06.218831797Z [2022-05-17 17:32:06] INFO Running migrations.
2022-05-17T17:32:06.242942778Z [2022-05-17 17:32:06] INFO Adding table: newsletters
2022-05-17T17:32:06.264473202Z [2022-05-17 17:32:06] INFO Adding permission for browse:newsletter
2022-05-17T17:32:06.275354586Z [2022-05-17 17:32:06] INFO Adding permission(Browse newsletters) to role(Administrator)
2022-05-17T17:32:06.280382998Z [2022-05-17 17:32:06] INFO Adding permission for add:newsletter
2022-05-17T17:32:06.288916012Z [2022-05-17 17:32:06] INFO Adding permission(Add newsletters) to role(Administrator)
2022-05-17T17:32:06.294826843Z [2022-05-17 17:32:06] INFO Adding permission for edit:newsletter
2022-05-17T17:32:06.304733980Z [2022-05-17 17:32:06] INFO Adding permission(Edit newsletters) to role(Administrator)
2022-05-17T17:32:06.349853872Z [2022-05-17 17:32:06] INFO Dropping table: newsletters
2022-05-17T17:32:06.355685880Z [2022-05-17 17:32:06] INFO Re-adding table: newsletters
2022-05-17T17:32:06.426895223Z [2022-05-17 17:32:06] INFO Adding table: members_newsletters
2022-05-17T17:32:06.511461337Z [2022-05-17 17:32:06] INFO Adding posts.newsletter_id column
2022-05-17T17:32:06.578528489Z [2022-05-17 17:32:06] INFO Adding foreign key from posts.newsletter_id to newsletters.id
2022-05-17T17:32:06.663046863Z [2022-05-17 17:32:06] INFO Adding members_paid_subscription_events.type column
2022-05-17T17:32:06.685583453Z [2022-05-17 17:32:06] INFO Adding emails.newsletter_id column
2022-05-17T17:32:06.826421297Z [2022-05-17 17:32:06] INFO Adding members_paid_subscription_events.subscription_id column
2022-05-17T17:32:06.869282148Z [2022-05-17 17:32:06] INFO Setting "type" to "updated" for events with different to_plan & from_plan
2022-05-17T17:32:06.873614979Z [2022-05-17 17:32:06] INFO Setting "type" to "expired" for events with null to_plan or the same to_plan & from_plan
2022-05-17T17:32:06.877171539Z [2022-05-17 17:32:06] INFO Setting "type" to "created" for events with null from_plan
2022-05-17T17:32:06.890944923Z [2022-05-17 17:32:06] INFO Rolling back: select `members`.`id` as `member_id`, `members_stripe_customers_subscriptions`.`id`, `members_stripe_customers_subscriptions`.`stripe_price_id`, `members_stripe_customers_subscriptions`.`plan_currency`, `members_stripe_customers_subscriptions`.`updated_at` from `members_stripe_customers_subscriptions` inner join `members_stripe_customers` on `members_stripe_customers`.`customer_id` = `members_stripe_customers_subscriptions`.`customer_id` inner join `members` on `members_stripe_customers`.`member_id` = `members`.`id` where `members_stripe_customers_subscriptions`.`cancel_at_period_end` = true and not `members_stripe_customers_subscriptions`.`status` = 'canceled' - Unknown column 'members_stripe_customers_subscriptions.stripe_price_id' in 'field list'.
2022-05-17T17:32:06.895720731Z [2022-05-17 17:32:06] INFO Setting "type" to null for all rows in "members_paid_subscriptions events"
2022-05-17T17:32:06.904847317Z [2022-05-17 17:32:06] INFO Removing members_paid_subscription_events.subscription_id column
2022-05-17T17:32:06.938541025Z [2022-05-17 17:32:06] INFO Removing emails.newsletter_id column
2022-05-17T17:32:06.939778479Z [2022-05-17 17:32:06] INFO Dropping foreign key from emails.newsletter_id to newsletters.id
2022-05-17T17:32:07.049101124Z [2022-05-17 17:32:07] INFO Removing members_paid_subscription_events.type column
2022-05-17T17:32:07.077631303Z [2022-05-17 17:32:07] INFO Removing posts.newsletter_id column
2022-05-17T17:32:07.078514538Z [2022-05-17 17:32:07] INFO Dropping foreign key from posts.newsletter_id to newsletters.id
2022-05-17T17:32:07.230328083Z [2022-05-17 17:32:07] INFO Dropping table: members_newsletters
2022-05-17T17:32:07.259647888Z [2022-05-17 17:32:07] WARN Ignoring rollback for table recreate: newsletters
2022-05-17T17:32:07.265848403Z [2022-05-17 17:32:07] INFO Removing permission(Edit newsletters) from role(Administrator)
2022-05-17T17:32:07.269395689Z [2022-05-17 17:32:07] INFO Removing permission for edit:newsletter
2022-05-17T17:32:07.279306713Z [2022-05-17 17:32:07] INFO Removing permission(Add newsletters) from role(Administrator)
2022-05-17T17:32:07.284668571Z [2022-05-17 17:32:07] INFO Removing permission for add:newsletter
2022-05-17T17:32:07.293369833Z [2022-05-17 17:32:07] INFO Removing permission(Browse newsletters) from role(Administrator)
2022-05-17T17:32:07.298650498Z [2022-05-17 17:32:07] INFO Removing permission for browse:newsletter
2022-05-17T17:32:07.311643327Z [2022-05-17 17:32:07] INFO Dropping table: newsletters
2022-05-17T17:32:07.321358989Z [2022-05-17 17:32:07] INFO Rollback was successful.
2022-05-17T17:32:07.327892940Z [2022-05-17 17:32:07] ERROR select `members`.`id` as `member_id`, `members_stripe_customers_subscriptions`.`id`, `members_stripe_customers_subscriptions`.`stripe_price_id`, `members_stripe_customers_subscriptions`.`plan_currency`, `members_stripe_customers_subscriptions`.`updated_at` from `members_stripe_customers_subscriptions` inner join `members_stripe_customers` on `members_stripe_customers`.`customer_id` = `members_stripe_customers_subscriptions`.`customer_id` inner join `members` on `members_stripe_customers`.`member_id` = `members`.`id` where `members_stripe_customers_subscriptions`.`cancel_at_period_end` = true and not `members_stripe_customers_subscriptions`.`status` = 'canceled' - Unknown column 'members_stripe_customers_subscriptions.stripe_price_id' in 'field list'
2022-05-17T17:32:07.327932980Z
2022-05-17T17:32:07.327938747Z select `members`.`id` as `member_id`, `members_stripe_customers_subscriptions`.`id`, `members_stripe_customers_subscriptions`.`stripe_price_id`, `members_stripe_customers_subscriptions`.`plan_currency`, `members_stripe_customers_subscriptions`.`updated_at` from `members_stripe_customers_subscriptions` inner join `members_stripe_customers` on `members_stripe_customers`.`customer_id` = `members_stripe_customers_subscriptions`.`customer_id` inner join `members` on `members_stripe_customers`.`member_id` = `members`.`id` where `members_stripe_customers_subscriptions`.`cancel_at_period_end` = true and not `members_stripe_customers_subscriptions`.`status` = 'canceled' - Unknown column 'members_stripe_customers_subscriptions.stripe_price_id' in 'field list'
2022-05-17T17:32:07.327949547Z
2022-05-17T17:32:07.327954094Z {"config":{"transaction":true},"name":"2022-04-08-11-54-add-cancelled-events.js"}
2022-05-17T17:32:07.327957270Z "Error occurred while executing the following migration: 2022-04-08-11-54-add-cancelled-events.js"
2022-05-17T17:32:07.327960020Z
2022-05-17T17:32:07.327962355Z Error ID:
2022-05-17T17:32:07.327965071Z     300
2022-05-17T17:32:07.327967666Z
2022-05-17T17:32:07.327970033Z Error Code:
2022-05-17T17:32:07.327972552Z     ER_BAD_FIELD_ERROR
2022-05-17T17:32:07.327975380Z
2022-05-17T17:32:07.327977857Z ----------------------------------------
2022-05-17T17:32:07.327980602Z
2022-05-17T17:32:07.327983760Z MigrationScriptError: select `members`.`id` as `member_id`, `members_stripe_customers_subscriptions`.`id`, `members_stripe_customers_subscriptions`.`stripe_price_id`, `members_stripe_customers_subscriptions`.`plan_currency`, `members_stripe_customers_subscriptions`.`updated_at` from `members_stripe_customers_subscriptions` inner join `members_stripe_customers` on `members_stripe_customers`.`customer_id` = `members_stripe_customers_subscriptions`.`customer_id` inner join `members` on `members_stripe_customers`.`member_id` = `members`.`id` where `members_stripe_customers_subscriptions`.`cancel_at_period_end` = true and not `members_stripe_customers_subscriptions`.`status` = 'canceled' - Unknown column 'members_stripe_customers_subscriptions.stripe_price_id' in 'field list'
2022-05-17T17:32:07.327997330Z     at DatabaseStateManager.makeReady (/var/lib/ghost/versions/4.46.0/core/server/data/db/state-manager.js:95:32)
2022-05-17T17:32:07.328000122Z     at MigrationScriptError.KnexMigrateError (/var/lib/ghost/versions/4.46.0/node_modules/knex-migrator/lib/errors.js:7:26)
2022-05-17T17:32:07.328002618Z     at new MigrationScriptError (/var/lib/ghost/versions/4.46.0/node_modules/knex-migrator/lib/errors.js:25:26)
2022-05-17T17:32:07.328005109Z     at /var/lib/ghost/versions/4.46.0/node_modules/knex-migrator/lib/index.js:1032:19
2022-05-17T17:32:07.328007615Z     at processTicksAndRejections (internal/process/task_queues.js:95:5)
2022-05-17T17:32:07.328010063Z
2022-05-17T17:32:07.328012403Z Error: select `members`.`id` as `member_id`, `members_stripe_customers_subscriptions`.`id`, `members_stripe_customers_subscriptions`.`stripe_price_id`, `members_stripe_customers_subscriptions`.`plan_currency`, `members_stripe_customers_subscriptions`.`updated_at` from `members_stripe_customers_subscriptions` inner join `members_stripe_customers` on `members_stripe_customers`.`customer_id` = `members_stripe_customers_subscriptions`.`customer_id` inner join `members` on `members_stripe_customers`.`member_id` = `members`.`id` where `members_stripe_customers_subscriptions`.`cancel_at_period_end` = true and not `members_stripe_customers_subscriptions`.`status` = 'canceled' - Unknown column 'members_stripe_customers_subscriptions.stripe_price_id' in 'field list'
2022-05-17T17:32:07.328016099Z     at Packet.asError (/var/lib/ghost/versions/4.46.0/node_modules/mysql2/lib/packets/packet.js:728:17)
2022-05-17T17:32:07.328018735Z     at Query.execute (/var/lib/ghost/versions/4.46.0/node_modules/mysql2/lib/commands/command.js:29:26)
2022-05-17T17:32:07.328021183Z     at Connection.handlePacket (/var/lib/ghost/versions/4.46.0/node_modules/mysql2/lib/connection.js:456:32)
2022-05-17T17:32:07.328023677Z     at PacketParser.onPacket (/var/lib/ghost/versions/4.46.0/node_modules/mysql2/lib/connection.js:85:12)
2022-05-17T17:32:07.328026008Z     at PacketParser.executeStart (/var/lib/ghost/versions/4.46.0/node_modules/mysql2/lib/packet_parser.js:75:16)
2022-05-17T17:32:07.328028347Z     at Socket.<anonymous> (/var/lib/ghost/versions/4.46.0/node_modules/mysql2/lib/connection.js:92:25)
2022-05-17T17:32:07.328030822Z     at Socket.emit (events.js:400:28)
2022-05-17T17:32:07.328033096Z     at addChunk (internal/streams/readable.js:293:12)
2022-05-17T17:32:07.328035364Z     at readableAddChunk (internal/streams/readable.js:267:9)
2022-05-17T17:32:07.328037753Z     at Socket.Readable.push (internal/streams/readable.js:206:10)
2022-05-17T17:32:07.328040118Z     at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
2022-05-17T17:32:07.328042482Z
2022-05-17T17:32:07.331611096Z [2022-05-17 17:32:07] WARN Ghost is shutting down
2022-05-17T17:32:07.336452167Z [2022-05-17 17:32:07] WARN Ghost has shut down
2022-05-17T17:32:07.337231171Z [2022-05-17 17:32:07] WARN Your site is now offline
2022-05-17T17:32:07.341317649Z [2022-05-17 17:32:07] WARN Ghost was running for a few seconds

Code of Conduct

github-actions[bot] commented 2 years ago

Hey @fabsau 👋

We've reviewed your bug report and believe the issue is environment specific, rather than a bug. Many questions can be answered by reviewing our documentation. If you can't find an answer then our forum is a great place to get community support, plus it helps create a central location for searching problems/solutions.