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 TABLE products ( id varchar(24) NOT NULL, name varchar(191) NOT NULL, slug varchar(191) NOT NULL, created_at datetime NOT NULL, updated_at datetime DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY products_name_unique (name),
UNIQUE KEY products_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.
Yes sir, I did upgrade to 20.04 LTS. Sounds like I am not alone in doing that upgrade not fully understanding the implications of that upgrade process on database compatibility for Ghost. It would be great to get a fix to make whatever database changes are necessary and make the upgrade possible for those of us that find ourselves in this predicament.
Debian / Ubuntu upgrades can have issues with databases, they sometimes show up in Ghost for me, esp. the switch from mysql to MariaDB as the default provider for MySQL.
There was an issue for me in a previous Ghost upgrade, July 2020, where the problem was actually the innodb_default_row_format which previously defaulted to compact and had to be set to dynamic for Ghost to be a happy-chappy.
In that case, my recipie was to:
create a CNF file to set the MariaDB required config: e.g. /etc/mysql/conf.d/mysql_custom.cnf containing innodb_default_row_format = dynamic
sudo systemctl restart mariadb
echo 'use my-ghost-database;show table status' | sudo mysql - this showed that many tables had Row_format compact. (FYI it also shows Collation for all tables)
sudo mysqlcheck -o my-ghost-database
echo 'use my-ghost-database;show table status' | sudo mysql - this showed tables now have Row_format dynamic. (FYI it also shows Collation for all tables)
ghost upgrade
The key points are:
show table status is great for checking ALL tables in a database
mysqlcheck -o database-name is useful to repair and fix general database upgrade issues
Yes to backups! You should be taking backups of your databases regularly, and SQL formats are best because they can be loaded into a newer version of MySQL/MariaDB. Here’s an old post on this: Best practice for backup database and all content - #2 by jeff. There may be a more modern way to do this.
Ubuntu and Debian upgrades are usually pretty safe when they hit production - there are always notes to look at, and MySQL/MariaDB always need a check afterwards. I run the upgrades early on test servers, and about a month after release dates in production. I am already running the next version on my development boxes, which is Debian pre-release Alpha3 aka bullseye, expected out this June.
Once you have issued your ALTER SCHEMA you have made the change permanent so you won’t need to do that next time.
The only additional thing is to run mysqlcheck -o from time to time - maybe every quarter, when there are no updates being performed, as part of your database health maintenance.
I have shell scripts to automate database backups, but I guess the downside of SQL format backups in a case like this is that they will also back up the table collation settings. If you want your database to use the default collation settings for MySQL 8 it might be better to export all content in JSON format from the Ghost Settings > Labs page and reimport those into a fresh install.
The export is not a backup and won’t contain all data from your Ghost site, notably members stats and email history among other “sensitive” settings will be missing. It’s useful for migrating content between instances but isn’t the right tool for a full backup.
If you have an SQL file you can always do a find+replace on it to replace the collation if needed before pulling into a fresh database.
Did you run a table optimise to rebuild each table with the updated collation?
e.g. sudo mysqlcheck -o my_ghost_database
This recreates each table, copies data from the original, fixes minor corruption issues, switches to the new table and finally removes the old original.
I have the same problem. Self-hosting on DigitalOcean, using the Ghost marketplace image. What led to the problem was
upgrading from Ubuntu 18.04 to Ubuntu 20.04 (which also upgrades MySQL from 5 to 8) a couple of weeks ago,
running ghost update today from 4.1.2 to 4.3.3, which was showing the error in the original post above, but then NOT downgrading when prompted, as I should have. I tried various fixes above and just caused further mess.
I have tried, in order:
Restoring my droplet to a backup a couple of days ago (Ubuntu already upgraded, Ghost wasn’t).
Updating packages and ghost-cli.
In MySQL, I checked with SELECT @@character_set_database, @@collation_database; and the default character set was indeed latin1 and default collation latin1_swedish_ci.
I changed the default collation with ALTER SCHEMA ghost_production DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
I have tried ghost update, and ghost failed to start with the same issue, copied below. I have tried ghost update 4.1.2 --force to reinstall the same version, reinstall binary dependencies… that was fine (Ghost still working) but upgrading to 4.3.3 didn’t work.
The problem seems to be that the table members_products doesn’t exist and therefore can’t be altered?
Full error message:
Message: Ghost was able to start, but errored during boot with: alter table 'members_products' add constraint 'members_products_member_id_foreign' foreign key ('member_id') references 'members' ('id') on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'members_products_member_id_foreign' are incompatible.
Help: Error occurred while executing the following migration: 02-add-members-products-table.js
Debug Information:
OS: Ubuntu, v20.04.2 LTS
Node Version: v14.16.1
Ghost Version: 4.3.3
Ghost-CLI Version: 1.16.3
Environment: production
Command: 'ghost update'
The tables are still incompatible. You’ve changed the default, which should fix newly created tables, but not fixed the existing tables as far as I can tell?
