My similar problem was solved.
1、Export database SQL file
2、Reinstall VPS system Ubuntu 20.04 LTS, and install the latest Ghost version
3、Temporarily set up a new VPS, install system Ubuntu 18.04 LTS, and install version 4.2.1. Import the SQL file. Go to the backend to export the data file and return to the second step to import.
Why not just roll back the update, export the data file and start fresh?
Still annoying but much less effort…
Edit:
Confirmed exporting and importing the content on a new up-to-date instance works. Except for the images these are missing.
Back up this path folder in advance: /content/images
Thanks. Will do. Had hoped it’s part of the content backup but know better now.
I would love to wait on v4.2.X for now and upgrade successfully later without having to migrate to a new VM. Is there a bugfix in the pipeline?
Same, I just want to update normally. So far no news regarding this. Hopefully @Kevin or @Hannah will tell us more.
P.S I am not using Digital Ocean, but Hetzner Cloud.
I experience exactly the same problem. Running Ghost without any modification and default template on VPS (no one click app). Latest change was Ubuntu update. Any solutions?
I won’t reinstall Ghost, because of downtime. Should we report this on GitHub?
The issue is not reproducible by using the DO one-click install as-is, starting Ghost 3.0 updating to 4.2 and then upgrading to 4.3. We’ve also successfully updated thousands of production sites.
The problem is environment related, likely to do with having updated across major mysql versions at some point (probably as a side effect of updating ubuntu) - this is a database administration issue and is not something Ghost can control. Please do not @ our staff members for support with self-hosting issues - this is a community forum & we will help out when we can.
The error is telling you two tables in your DB are incompatible - members and members_products:
UNKNOWN_CODE_PLEASE_REPORT: Referencing column ‘member_id’ and referenced column ‘id’ in foreign key constraint ‘members_products_member_id_foreign’ are incompatible.
There’s likely a setting in MySQL causing the issue somewhere: charset/collation is my first guess but it could be a row format or sql mode issue too etc.
To debug collation issues, you need to check collation on both the table with the foreign key and the table being referenced.
Run this on both tables:
show full columns from members;
show full columns from members_products;
And also look at your global collations:
SELECT @@character_set_database, @@collation_database;
Your charset should be utf8mb4 and your collation should be utf8mb4_general_ci .
For further debugging, you can look up SQL modes as follows:
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;
You can look up row formats to ensure they are all set to Dynamic as follows:
SELECT `table_name`, `row_format`
FROM `information_schema`.`tables`
WHERE `table_schema`=DATABASE();
The ANSI and ANSI_QUOTES SQL modes are not supported and should be disabled.
Everything seems to be OK, beside the fact that members_products is still not found. Thanks for the help.
I will wait until someone come up with possible solution and how to fix it. It seems like there is plenty of users who experience this issue.
I managed to fix this, but I am not sure if this is correct way. I created empty member products, because it didn’t exists.
CREATE TABLE members_products (name VARCHAR(20));
And after that
ghost update 4.3.2 --force
#update
Deleting members in admin is not possible anymore. I am looking how to fix it. You will get next message. Otherwise everything seems to work normally.
Database error, cannot delete member. Unable to update nested relation. [object Object]
Adding manually missing database does not help, either.
CREATE TABLE members_products (
id varchar(24) NOT NULL PRIMARY KEY,
member_id varchar(24) NOT NULL,
product_id varchar(24) NOT NULL,
sort_order int NOT NULL DEFAULT ‘0’
);
@Jack_Oakley you need the foreign keys with ON DELETE CASCADE for member deletion to work. If you try to add the key I expect you’ll see the same error as before.
It’s certainly not recommended to manually create tables rather than letting the migrations handle it.
Can you check collation and charset for your manually created members_products table? That might provide some insight that is lost when the migration automatically rolls back creation of the table when it fails.
I know, but unfortunately I didn’t find any other way :(
Collation and charset seems to be correct. So, I don’t really understand why Ghost didn’t want to create new table. I will try to add ON DELETE CASCADE and get back here to inform, if it helped.
mysql> show full columns from members_products;
±-----------±------------±-------------------±-----±----±--------±------±--------------------------------±--------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
±-----------±------------±-------------------±-----±----±--------±------±--------------------------------±--------+
| id | varchar(24) | utf8mb4_general_ci | NO | PRI | NULL | | select,insert,update,references | |
| member_id | varchar(24) | utf8mb4_general_ci | NO | | NULL | | select,insert,update,references | |
| product_id | varchar(24) | utf8mb4_general_ci | NO | | NULL | | select,insert,update,references | |
| sort_order | int | NULL | NO | | 0 | | select,insert,update,references | |
±-----------±------------±-------------------±-----±----±--------±------±--------------------------------±--------+
Charset:
mysql> SHOW CREATE TABLE members_products;
±-----------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±-----------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| members_products | CREATE TABLEmembers_products(
idvarchar(24) COLLATE utf8mb4_general_ci NOT NULL,
member_idvarchar(24) COLLATE utf8mb4_general_ci NOT NULL,
product_idvarchar(24) COLLATE utf8mb4_general_ci NOT NULL,
sort_orderint NOT NULL DEFAULT ‘0’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
±-----------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
I tried to add foreign key, but I think I am missing something. Do you know what exactly is incorrect here?
mysql> CREATE TABLE members_products (
→ id varchar(24) NOT NULL PRIMARY KEY,
→ member_id varchar(24) NOT NULL,
→ product_id varchar(24) NOT NULL,
→ sort_order int NOT NULL DEFAULT ‘0’,
> → FOREIGN KEY (member_id)
> → REFERENCES members (id)
> → ON DELETE CASCADE,
> → FOREIGN KEY (product_id)
> → REFERENCES products (id)
> → ON DELETE CASCADE
→ );
ERROR 3780 (HY000): Referencing column ‘product_id’ and referenced column ‘id’ in foreign key constraint ‘members_products_ibfk_2’ are incompatible.
That’s the same error that the migrations ran into. Can you check the collation and charset on your products table?
Interesting. I wonder why only products got incorrect collation. I guess I have to fix it now and remove members_products. After that I will try to update to new Ghost version then it comes and see what’s going to happen.
Rollback seems not to work anymore and I can’t restore 4.2.2.
mysql> show full columns from products;
±-----------±-------------±-------------------±-----±----±--------±------±--------------------------------±--------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
±-----------±-------------±-------------------±-----±----±--------±------±--------------------------------±--------+
| id | varchar(24) | utf8mb4_0900_ai_ci | NO | PRI | NULL | | select,insert,update,references | |
| name | varchar(191) | utf8mb4_0900_ai_ci | NO | UNI | NULL | | select,insert,update,references | |
| slug | varchar(191) | utf8mb4_0900_ai_ci | NO | UNI | NULL | | select,insert,update,references | |
| created_at | datetime | NULL | NO | | NULL | | select,insert,update,references | |
| updated_at | datetime | NULL | YES | | NULL | | select,insert,update,references | |
±-----------±-------------±-------------------±-----±----±--------±------±--------------------------------±--------+
5 rows in set (0.01 sec)mysql> SHOW CREATE TABLE products;
±---------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±---------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| products | CREATE TABLEproducts(
idvarchar(24) NOT NULL,
namevarchar(191) NOT NULL,
slugvarchar(191) NOT NULL,
created_atdatetime NOT NULL,
updated_atdatetime DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEYproducts_name_unique(name),
UNIQUE KEYproducts_slug_unique(slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
±---------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Funny. I am not able to change collation for products.
mysql> ALTER TABLE products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ERROR 3780 (HY000): Referencing column ‘product_id’ and referenced column ‘id’ in foreign key constraint ‘stripe_products_product_id_foreign’ are incompatible.
Same goes with
mysql> ALTER TABLE products MODIFY id VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ERROR 3780 (HY000): Referencing column ‘product_id’ and referenced column ‘id’ in foreign key constraint ‘stripe_products_product_id_foreign’ are incompatible.
I think I am totally screwed and the only way is to reinstall Ghost.
Even running next one is not working. It says that everything is changed, but collate is still utf8mb4_0900_ai_ci.
SELECT CONCAT(“ALTER TABLE
", TABLE_NAME,"convert to character set utf8mb4 collate utf8mb4_general_ci;”) AS mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=“ghostdb”
AND TABLE_TYPE=“BASE TABLE”;
It seems like what may be happening is that these newer tables products, stripe_products, members_products, etc have been created using MySQL 8’s default collation which is utf8mb4_0900_ai_ci. When manually creating the members_products table it’s likely your mysql client is set up with its own default connection collation that matches the earlier mysql version which is why you’re seeing a mismatch with that.
If you’re altering collations you’ll need to drop foreign keys, update the tables, then recreate the foreign keys. You can either update all tables to use the utf8mb4_0900_ai_ci collation, or find and change the variable that’s defining the default and match that to your earlier utf8mb4_general_ci collation.
Regarding the migrations you’ve probably got into an “unknown” state by manually creating tables and partially letting migrations run through. Re-installing may be your best bet if you’re uncomfortable getting the database back into a known and consistent state.
I’ve just done this and can confirm updating all the tables to use utf8mb4_0900_ai_ci before running the ghost update command works. (I did it on my test server using a modified version of the query given here to generate the table update queries.)
I see this thread is marked as solved, but for those of us that have not changed our MySQL tables collations or created new tables manually and just want to upgrade from 4.2.2 to 4.3.x, what will the fix be that will allow for an upgrade?
Have you upgraded Ubuntu from 18 to 20 since you first installed Ghost? That also bumps your installed MySQL version and will have caused your database collations to get out of sync.