Error after update to 4.35.0

Developer help Self-hosting
1

A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: create table posts_products (id varchar(24) not null, post_id varchar(24) not null, product_id varchar(24) not null, sort_order int unsigned not null default ‘0’) default character set utf8mb4 - UNKNOWN_CODE_PLEASE_REPORT: Unable to create or change a table without a primary key, when the system variable ‘sql_require_primary_key’ is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

Help: Error occurred while executing the following migration: 2022-01-20-05-55-add-post-products-table.js

Setup information

Ghost Version
4.34.3

Node.js Version
v16.13.2

Database type
MySQL 8

Relevant log / error output

Debug Information:
    OS: Ubuntu, v20.04.3 LTS
    Node Version: v16.13.2
    Ghost Version: 4.35.0
    Ghost-CLI Version: 1.18.1
    Environment: production
    Command: 'ghost update'
Message: Ghost was able to start, but errored during boot with: create table `posts_products` (`id` varchar(24) not null, `post_id` varchar(24) not null, `product_id` varchar(24) not null, `sort_order` int unsigned not null default '0') default character set utf8mb4 - UNKNOWN_CODE_PLEASE_REPORT: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
Help: Error occurred while executing the following migration: 2022-01-20-05-55-add-post-products-table.js

Stack: Error: Ghost was able to start, but errored during boot with: create table `posts_products` (`id` varchar(24) not null, `post_id` varchar(24) not null, `product_id` varchar(24) not null, `sort_order` int unsigned not null default '0') default character set utf8mb4 - UNKNOWN_CODE_PLEASE_REPORT: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
    at Server.<anonymous> (/usr/local/lib/node_modules/ghost-cli/lib/utils/port-polling.js:73:28)
    at Object.onceWrapper (node:events:509:28)
    at Server.emit (node:events:390:28)
    at emitCloseNT (node:net:1681:8)
    at processTicksAndRejections (node:internal/process/task_queues:82:21)
2

Also encountered an error when my docker image did auto-update this night.

I think it was this update:
4.34.3 → 4.35.0
I am not 100% certain since I just follow the docker tag ghost:4 and update every night when there is a new version available.

The container keeps restarting with the following output:

root@webspace:~# docker logs -f blog_ghost_1
[2022-02-08 05:47:09] INFO Ghost is running in production...
[2022-02-08 05:47:09] INFO Your site is now available on https://www.ajfriesen.com/
[2022-02-08 05:47:09] INFO Ctrl+C to shut down
[2022-02-08 05:47:09] INFO Ghost server started in 0.751s
[2022-02-08 05:47:09] WARN Database state requires migration.
[2022-02-08 05:47:10] INFO Creating database backup
[2022-02-08 05:47:10] INFO Database backup written to: /var/lib/ghost/content/data/ajfriesen.ghost.2022-02-08-05-47-10.json
[2022-02-08 05:47:10] INFO Running migrations.
[2022-02-08 05:47:10] INFO Adding table: posts_products
[2022-02-08 05:47:10] INFO Rolling back: 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..
[2022-02-08 05:47:10] INFO Dropping table: posts_products
[2022-02-08 05:47:10] INFO Rollback was successful.
[2022-02-08 05:47:10] 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.

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.

{"config":{"transaction":false},"name":"2022-01-20-05-55-add-post-products-table.js"}
"Error occurred while executing the following migration: 2022-01-20-05-55-add-post-products-table.js"

Error ID:
    300

Error Code: 
    UNKNOWN_CODE_PLEASE_REPORT

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

MigrationScriptError: 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.
    at DatabaseStateManager.makeReady (/var/lib/ghost/versions/4.35.0/core/server/data/db/state-manager.js:95:32)
    at MigrationScriptError.KnexMigrateError (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/errors.js:7:26)
    at new MigrationScriptError (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/errors.js:25:26)
    at /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/index.js:1032:19
    at processTicksAndRejections (internal/process/task_queues.js:95:5)

Error: UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
    at Query.Sequence._packetToError (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/var/lib/ghost/versions/4.35.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 (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/Connection.js:198:25)
    at /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:134:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:128:12)
    at Client_MySQL.query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/client.js:168:17)
    at Runner.query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:151:36)
    at Runner.queryArray (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:248:31)
    at processTicksAndRejections (internal/process/task_queues.js:95:5)
    at async /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:277:18
    at async Object.up (/var/lib/ghost/versions/4.35.0/core/server/data/migrations/utils.js:257:13)

[2022-02-08 05:47:10] WARN Ghost is shutting down
[2022-02-08 05:47:10] WARN Ghost has shut down
[2022-02-08 05:47:10] WARN Your site is now offline
[2022-02-08 05:47:10] WARN Ghost was running for a few seconds
[2022-02-08 05:47:12] INFO Ghost is running in production...
[2022-02-08 05:47:12] INFO Your site is now available on https://www.ajfriesen.com/
[2022-02-08 05:47:12] INFO Ctrl+C to shut down
[2022-02-08 05:47:12] INFO Ghost server started in 0.635s
[2022-02-08 05:47:12] WARN Database state requires migration.
[2022-02-08 05:47:13] INFO Creating database backup
[2022-02-08 05:47:13] INFO Database backup written to: /var/lib/ghost/content/data/ajfriesen.ghost.2022-02-08-05-47-13.json
[2022-02-08 05:47:13] INFO Running migrations.
[2022-02-08 05:47:13] INFO Adding table: posts_products
[2022-02-08 05:47:13] INFO Rolling back: 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..
[2022-02-08 05:47:13] INFO Dropping table: posts_products
[2022-02-08 05:47:13] INFO Rollback was successful.
[2022-02-08 05:47:13] 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.

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.

{"config":{"transaction":false},"name":"2022-01-20-05-55-add-post-products-table.js"}
"Error occurred while executing the following migration: 2022-01-20-05-55-add-post-products-table.js"

Error ID:
    300

Error Code: 
    UNKNOWN_CODE_PLEASE_REPORT

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

MigrationScriptError: 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.
    at DatabaseStateManager.makeReady (/var/lib/ghost/versions/4.35.0/core/server/data/db/state-manager.js:95:32)
    at MigrationScriptError.KnexMigrateError (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/errors.js:7:26)
    at new MigrationScriptError (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/errors.js:25:26)
    at /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/index.js:1032:19
    at processTicksAndRejections (internal/process/task_queues.js:95:5)

Error: UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
    at Query.Sequence._packetToError (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/var/lib/ghost/versions/4.35.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 (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/Connection.js:198:25)
    at /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:134:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:128:12)
    at Client_MySQL.query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/client.js:168:17)
    at Runner.query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:151:36)
    at Runner.queryArray (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:248:31)
    at processTicksAndRejections (internal/process/task_queues.js:95:5)
    at async /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:277:18
    at async Object.up (/var/lib/ghost/versions/4.35.0/core/server/data/migrations/utils.js:257:13)

[2022-02-08 05:47:13] WARN Ghost is shutting down
[2022-02-08 05:47:13] WARN Ghost has shut down
[2022-02-08 05:47:13] WARN Your site is now offline
[2022-02-08 05:47:13] WARN Ghost was running for a few seconds

Reverted to 4.34.3 again for now.

3

i have the same issue…

opened an issue on github

have tried 4.36 version too…still can’t migrate database…
I’ll stick to 4.34.3 for now too…

5

Thanks for checking.
Wanted to do that as well but will wait a little.

I could not replicate the upgrade problem with a local docker compose install.
But I did just use the default ghost database data. I did not use my production data for this yet.

6

I proposed a solution in GitHub issue:

7

Your solution was the only one that worked for me, many thanks for your assistance.

1 Like
8

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.