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)
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
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.