Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Error foreign key constraint are incompatible. #20464

Open
1 task done
koznov opened this issue Jun 26, 2024 · 1 comment
Open
1 task done

Error foreign key constraint are incompatible. #20464

koznov opened this issue Jun 26, 2024 · 1 comment
Labels
needs:triage [triage] this needs to be triaged by the Ghost team

Comments

@koznov
Copy link

koznov commented Jun 26, 2024

Issue Summary

Hello!
I'm facing issue with upgrading to latest version from 5.60 after migrating to MySQL 8 with upgrading OS to Ubuntu 20.04.
I've applied recommendations from article about ER_FK_INCOMPATIBLE_COLUMNS and that doesn't help. I'm

SHOW TABLE STATUS giving me collation utf8mb4_0900_ai_ci at all tables.

mysql> show create database ghost\G
*************************** 1. row ***************************
Database: ghost
Create Database: CREATE DATABASE ghost /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci / /!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

mysql> show variables like '%collation_connection%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
1 row in set (0.01 sec)

mysql>

mysql> SELECT @@GLOBAL.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.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)

mysql>

MySQL Server version: 8.0.37-0ubuntu0.20.04.3 (Ubuntu)

Steps to Reproduce

Installation should be updated from MySQL 5.7 to 8 and upgrade from 5.x to 5.60.0 and then to 5.86.2.
Database collation is updated to utf8mb4_0900_ai_ci. Update to latest version.

Ghost Version

5.60.0

Node.js Version

v18.20.3

How did you install Ghost?

Ubuntu 18.04 upgraded to 20.04, manual installation

Database type

MySQL 8

Browser & OS version

Version 126.0.6478.115 (Official Build) / Windows 11

Relevant log / error output

user@server:/var/www/ghost$ ghost update

Love open source? We’re hiring JavaScript Engineers to work on Ghost full-time.
https://careers.ghost.org



+ sudo systemctl is-active ghost_vmadm-in
✔ Checking system Node.js version - found v18.20.3
✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
✔ Checking current folder permissions
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
✔ Checking memory availability
✔ Checking free space
✔ Checking for available migrations
✔ Checking for latest Ghost version

# 5.86.2

* 🐛 Fixed YouTube bookmark creation - Kevin Ansfield

---

View the changelog for full details: https://github.com/tryghost/ghost/compare/v5.86.1...v5.86.2

✔ Fetched release notes
Version already installed.
ℹ Downloading and updating Ghost [skipped]
+ sudo systemctl stop ghost_vmadm-in
✔ Stopping Ghost
✔ Linking latest Ghost and recording versions
+ sudo ln -s /var/www/ghost/current/content/themes/source /var/www/ghost/content/themes/source
✔ Linking built-in themes
+ sudo systemctl start ghost_vmadm-in
+ sudo systemctl stop ghost_vmadm-in
✖ Restarting Ghost
A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: alter table `recommendation_click_events` add constraint `recommendation_click_events_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete SET NULL - Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'recommendation_click_events_member_id_foreign' are incompatible.
Help: Error occurred while executing the following migration: 2023-09-12-11-22-10-add-recommendation-click-events-table.js
Suggestion: journalctl -u ghost_vmadm-in -n 50

Debug Information:
    OS: Ubuntu, v20.04.6 LTS
    Node Version: v18.20.3
    Ghost Version: 5.86.2
    Ghost-CLI Version: 1.26.0
    Environment: production
    Command: 'ghost update'

Additional log info available in: /home/user/.ghost/logs/ghost-cli-debug-2024-06-26T07_17_50_484Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/ghost-cli/ for troubleshooting.



? Unable to upgrade Ghost from v5.60.0 to v5.86.2. Would you like to revert back to v5.60.0? (Y/n)



Jun 26 09:17:43 vmadmin systemd[1]: Started Ghost systemd service for blog: vmadm-in.
Jun 26 09:17:44 vmadmin node[45362]: Love open source? We’re hiring JavaScript Engineers to work on Ghost full-time.
Jun 26 09:17:44 vmadmin node[45362]: https://careers.ghost.org
Jun 26 09:17:44 vmadmin node[45362]: - Inspecting operating system
Jun 26 09:17:47 vmadmin node[45393]: [2024-06-26 07:17:47] INFO Ghost is running in production...
Jun 26 09:17:47 vmadmin node[45393]: [2024-06-26 07:17:47] INFO Your site is now available on https://vmadm.in/
Jun 26 09:17:47 vmadmin node[45393]: [2024-06-26 07:17:47] INFO Ctrl+C to shut down
Jun 26 09:17:47 vmadmin node[45393]: [2024-06-26 07:17:47] INFO Ghost server started in 2.218s
Jun 26 09:17:47 vmadmin node[45393]: [2024-06-26 07:17:47] INFO Bootstrap client was closed.
Jun 26 09:17:47 vmadmin node[45393]: [2024-06-26 07:17:47] WARN Database state requires migration.
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Creating database backup
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Database backup written to /var/www/ghost/content/data/vm-admin.ghost.2024-06-26-07-17-48.json
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Running migrations.
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Adding permission for browse:recommendation
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Adding permission(Browse recommendations) to role(Administrator)
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Adding permission(Browse recommendations) to role(Admin Integration)
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Adding permission(Browse recommendations) to role(Editor)
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Adding permission(Browse recommendations) to role(Author)
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Adding permission(Browse recommendations) to role(Contributor)
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Adding permission for read:recommendation
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Adding permission(Read recommendations) to role(Administrator)
Jun 26 09:17:48 vmadmin node[45393]: [2024-06-26 07:17:48] INFO Adding permission(Read recommendations) to role(Admin Integration)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission(Read recommendations) to role(Editor)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission(Read recommendations) to role(Author)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission(Read recommendations) to role(Contributor)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission for edit:recommendation
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission(Edit recommendations) to role(Administrator)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission(Edit recommendations) to role(Admin Integration)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission for add:recommendation
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission(Add recommendations) to role(Administrator)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission(Add recommendations) to role(Admin Integration)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission for destroy:recommendation
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission(Delete recommendations) to role(Administrator)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding permission(Delete recommendations) to role(Admin Integration)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding table: recommendations
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding setting: recommendations_enabled
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Adding table: recommendation_click_events
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Rolling back: alter table `recommendation_click_events` add constraint `recommendation_click_events_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete SET NULL - Referencing col>
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Dropping table: recommendation_click_events
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Dropping setting: recommendations_enabled
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Dropping table: recommendations
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Delete recommendations) from role(Admin Integration)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Delete recommendations) from role(Administrator)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission for destroy:recommendation
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Add recommendations) from role(Admin Integration)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Add recommendations) from role(Administrator)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission for add:recommendation
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Edit recommendations) from role(Admin Integration)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Edit recommendations) from role(Administrator)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission for edit:recommendation
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Read recommendations) from role(Contributor)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Read recommendations) from role(Author)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Read recommendations) from role(Editor)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Read recommendations) from role(Admin Integration)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Read recommendations) from role(Administrator)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission for read:recommendation
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Browse recommendations) from role(Contributor)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Browse recommendations) from role(Author)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Browse recommendations) from role(Editor)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Browse recommendations) from role(Admin Integration)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission(Browse recommendations) from role(Administrator)
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Removing permission for browse:recommendation
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Rollback was successful.
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] ERROR alter table `recommendation_click_events` add constraint `recommendation_click_events_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete SET NULL - Referencing column 'member_i>
Jun 26 09:17:49 vmadmin node[45393]:
Jun 26 09:17:49 vmadmin node[45393]: alter table `recommendation_click_events` add constraint `recommendation_click_events_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete SET NULL - Referencing column 'member_id' and referenced column 'id>
Jun 26 09:17:49 vmadmin node[45393]: {"config":{"transaction":false},"name":"2023-09-12-11-22-10-add-recommendation-click-events-table.js"}
Jun 26 09:17:49 vmadmin node[45393]: "Error occurred while executing the following migration: 2023-09-12-11-22-10-add-recommendation-click-events-table.js"
Jun 26 09:17:49 vmadmin node[45393]: Error ID:
Jun 26 09:17:49 vmadmin node[45393]:     300
Jun 26 09:17:49 vmadmin node[45393]: Error Code:
Jun 26 09:17:49 vmadmin node[45393]:     ER_FK_INCOMPATIBLE_COLUMNS
Jun 26 09:17:49 vmadmin node[45393]: ----------------------------------------
Jun 26 09:17:49 vmadmin node[45393]: Error: alter table `recommendation_click_events` add constraint `recommendation_click_events_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete SET NULL - Referencing column 'member_id' and referenced col>
Jun 26 09:17:49 vmadmin node[45393]:     at /var/www/ghost/versions/5.86.2/node_modules/knex-migrator/lib/index.js:1032:19
Jun 26 09:17:49 vmadmin node[45393]:     at Packet.asError (/var/www/ghost/versions/5.86.2/node_modules/mysql2/lib/packets/packet.js:728:17)
Jun 26 09:17:49 vmadmin node[45393]:     at Query.execute (/var/www/ghost/versions/5.86.2/node_modules/mysql2/lib/commands/command.js:29:26)
Jun 26 09:17:49 vmadmin node[45393]:     at Connection.handlePacket (/var/www/ghost/versions/5.86.2/node_modules/mysql2/lib/connection.js:481:34)
Jun 26 09:17:49 vmadmin node[45393]:     at PacketParser.onPacket (/var/www/ghost/versions/5.86.2/node_modules/mysql2/lib/connection.js:97:12)
Jun 26 09:17:49 vmadmin node[45393]:     at PacketParser.executeStart (/var/www/ghost/versions/5.86.2/node_modules/mysql2/lib/packet_parser.js:75:16)
Jun 26 09:17:49 vmadmin node[45393]:     at Socket.<anonymous> (/var/www/ghost/versions/5.86.2/node_modules/mysql2/lib/connection.js:104:25)
Jun 26 09:17:49 vmadmin node[45393]:     at Socket.emit (node:events:517:28)
Jun 26 09:17:49 vmadmin node[45393]:     at addChunk (node:internal/streams/readable:368:12)
Jun 26 09:17:49 vmadmin node[45393]:     at readableAddChunk (node:internal/streams/readable:341:9)
Jun 26 09:17:49 vmadmin node[45393]:     at Readable.push (node:internal/streams/readable:278:10)
Jun 26 09:17:49 vmadmin node[45393]:     at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
Jun 26 09:17:49 vmadmin node[45393]:
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] WARN Ghost is shutting down
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] WARN Ghost has shut down
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] WARN Your site is now offline
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] WARN Ghost was running for a few seconds
Jun 26 09:17:49 vmadmin node[45393]: [2024-06-26 07:17:49] INFO Bootstrap client was closed.
Jun 26 09:17:49 vmadmin systemd[1]: Stopping Ghost systemd service for blog: vmadm-in...
Jun 26 09:17:50 vmadmin node[45362]: /usr/lib/node_modules/ghost-cli/lib/process-manager.js:46
Jun 26 09:17:50 vmadmin node[45362]:         throw error;
Jun 26 09:17:50 vmadmin node[45362]:         ^
Jun 26 09:17:50 vmadmin node[45362]: {
Jun 26 09:17:50 vmadmin node[45362]:   message: {
Jun 26 09:17:50 vmadmin node[45362]:     statusCode: 500,
Jun 26 09:17:50 vmadmin node[45362]:     errorType: 'MigrationScriptError',
Jun 26 09:17:50 vmadmin node[45362]:     level: 'critical',
Jun 26 09:17:50 vmadmin node[45362]:     id: 300,
Jun 26 09:17:50 vmadmin node[45362]:     context: {
Jun 26 09:17:50 vmadmin node[45362]:       config: { transaction: false },
Jun 26 09:17:50 vmadmin node[45362]:       name: '2023-09-12-11-22-10-add-recommendation-click-events-table.js'
Jun 26 09:17:50 vmadmin node[45362]:     },
Jun 26 09:17:50 vmadmin node[45362]:     help: 'Error occurred while executing the following migration: 2023-09-12-11-22-10-add-recommendation-click-events-table.js',
Jun 26 09:17:50 vmadmin node[45362]:     code: 'ER_FK_INCOMPATIBLE_COLUMNS',
Jun 26 09:17:50 vmadmin node[45362]:     property: null,
Jun 26 09:17:50 vmadmin node[45362]:     redirect: null,
Jun 26 09:17:50 vmadmin node[45362]:     hideStack: false,
Jun 26 09:17:50 vmadmin node[45362]:     message: "Ghost was able to start, but errored during boot with: alter table `recommendation_click_events` add constraint `recommendation_click_events_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delet>
Jun 26 09:17:50 vmadmin node[45362]:     name: 'MigrationScriptError',
Jun 26 09:17:50 vmadmin node[45362]:     errno: 3780,
Jun 26 09:17:50 vmadmin node[45362]:     sqlState: 'HY000',
Jun 26 09:17:50 vmadmin node[45362]:     sqlMessage: "Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'recommendation_click_events_member_id_foreign' are incompatible.",
Jun 26 09:17:50 vmadmin node[45362]:     sql: 'alter table `recommendation_click_events` add constraint `recommendation_click_events_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete SET NULL'
Jun 26 09:17:50 vmadmin node[45362]:   }
Jun 26 09:17:50 vmadmin node[45362]: }
Jun 26 09:17:50 vmadmin node[45362]: Node.js v18.20.3
Jun 26 09:17:50 vmadmin systemd[1]: ghost_vmadm-in.service: Main process exited, code=exited, status=1/FAILURE
Jun 26 09:17:50 vmadmin systemd[1]: ghost_vmadm-in.service: Failed with result 'exit-code'.
Jun 26 09:17:50 vmadmin systemd[1]: Stopped Ghost systemd service for blog: vmadm-in.

Code of Conduct

  • I agree to be friendly and polite to people in this repository
@github-actions github-actions bot added the needs:triage [triage] this needs to be triaged by the Ghost team label Jun 26, 2024
@koznov
Copy link
Author

koznov commented Jun 26, 2024 •
edited
Loading 

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='ghost';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_0900_ai_ci     |
+----------------------------+------------------------+
1 row in set (0.00 sec)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
needs:triage [triage] this needs to be triaged by the Ghost team
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@koznov