Can’t Upgrade from v2.27 to v3.8 ER_NO_SUCH_TABLE error #11625
Comments
|
Upgrade to Ghost 3.0.0 then to 3.9.0 :) |
|
I tried that but didn't work. Here: https://forum.ghost.org/t/cant-upgrade-from-v2-27-to-v3-8-er-no-such-table-error/12271/5?u=nspeaks Upgraded to 2.38 which worked but couldn't upgrade to 3.0.0. Anyway, the only thing that worked was to uninstall 2.38 and install the latest version. Had taken backups so it was smooth. |
|
Re-opening because this still needs investigation, migration from 2.x to 3.x should not error. |
|
Was not successful reproducing this bug. Scenarios I've tried:
All resulted in fully working site. My testing environment atm:
This error is most likely coming from Also checked migration |
|
The only additional information I can give now is here: https://forum.ghost.org/t/cant-upgrade-from-v2-27-to-v3-8-er-no-such-table-error/12271/5 |
|
Oh good point @andrewmatveychuk , will have a look 👍 |
|
Just one more thing. I used MariaDB 10.3. Not sure if that matters. And I am pasting the content of the journalctl command here instead of the paste.org link in my original post above. Easier to track. |
|
Had a second look at the #11855. It wasn't clear at the beginning if current issue is definitely the same one or just result in same resulting error: It was unclear because migration fails at some point and in both cases. Resulting error is caused by migrations never reached the point of creating From #11855 it is clear the error is happening when Which is related to faulty migration which was fixed with #11597. The
To push this further would need to get MariaDB instance running locally or get some help from community debugging this issue further. How I suspect one could reproduce the error from instance running from source:
Step 3 should throw same error as #11855 after this SQL statement - NOTE: when running the migration to 3.8 it should have picked up a fix done here. Probably there's some other MariaDB quirk which we missed to test for. |
|
@andrewmatveychuk do you have by any chance an export of the instance that could be safely shared (without sensitive information)? Would be awesome to have fully reproducible scenario for this 👍 |
I run my site on a DigitalOcean droplet. I can create a snapshot and transfer it to you: https://www.digitalocean.com/docs/images/snapshots/how-to/change-owners/ Does it make sense? |
|
@andrewmatveychuk yes that could work. I'm [email protected] on DO |
Hi @naz, |
|
Hi @naz, |
|
Hey @andrewmatveychuk I did receive the snapshot on my account but haven't have a chance to look into it yet. It is on my list of things to do. |
|
Have spent an hour playing with the snapshot provided by @andrewmatveychuk. The issue seems to be some sort of charset/collation bug in MySQL 8. I haven't investigated much further as that seems like a rabbit hole that is not worth of a time investment at this point, given this is an unsupported DB engine. To catch the exact issue don't upgdate to the latest version. Instead upgrade to Ghost After inspecting the DB these are schema definitions for mysql> show create table posts \G
*************************** 1. row ***************************
Table: posts
Create Table: CREATE TABLE `posts` (
`id` varchar(24) COLLATE utf8mb4_general_ci NOT NULL,
`uuid` varchar(36) COLLATE utf8mb4_general_ci NOT NULL,
`title` varchar(2000) COLLATE utf8mb4_general_ci NOT NULL,
`slug` varchar(191) COLLATE utf8mb4_general_ci NOT NULL,
`mobiledoc` longtext COLLATE utf8mb4_general_ci,
`html` longtext COLLATE utf8mb4_general_ci,
`comment_id` longtext COLLATE utf8mb4_general_ci,
`plaintext` longtext COLLATE utf8mb4_general_ci,
`feature_image` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,
`featured` tinyint(1) NOT NULL DEFAULT '0',
`page` tinyint(1) NOT NULL DEFAULT '0',
`status` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'draft',
`locale` varchar(6) COLLATE utf8mb4_general_ci DEFAULT NULL,
`visibility` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'public',
`meta_title` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,
`meta_description` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,
`author_id` varchar(24) COLLATE utf8mb4_general_ci NOT NULL,
`created_at` datetime NOT NULL,
`created_by` varchar(24) COLLATE utf8mb4_general_ci NOT NULL,
`updated_at` datetime DEFAULT NULL,
`updated_by` varchar(24) COLLATE utf8mb4_general_ci DEFAULT NULL,
`published_at` datetime DEFAULT NULL,
`published_by` varchar(24) COLLATE utf8mb4_general_ci DEFAULT NULL,
`custom_excerpt` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,
`codeinjection_head` text COLLATE utf8mb4_general_ci,
`codeinjection_foot` text COLLATE utf8mb4_general_ci,
`og_image` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,
`og_title` varchar(300) COLLATE utf8mb4_general_ci DEFAULT NULL,
`og_description` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
`twitter_image` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,
`twitter_title` varchar(300) COLLATE utf8mb4_general_ci DEFAULT NULL,
`twitter_description` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
`custom_template` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`canonical_url` text COLLATE utf8mb4_general_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `posts_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
mysql> show create table posts_meta \G
*************************** 1. row ***************************
Table: posts_meta
Create Table: CREATE TABLE `posts_meta` (
`id` varchar(24) NOT NULL,
`post_id` varchar(24) NOT NULL,
`og_image` varchar(2000) DEFAULT NULL,
`og_title` varchar(300) DEFAULT NULL,
`og_description` varchar(500) DEFAULT NULL,
`twitter_image` varchar(2000) DEFAULT NULL,
`twitter_title` varchar(300) DEFAULT NULL,
`twitter_description` varchar(500) DEFAULT NULL,
`meta_title` varchar(2000) DEFAULT NULL,
`meta_description` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `posts_meta_post_id_unique` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)Even though `post_id` varchar(24) NOT NULL,
AND
`id` varchar(24) COLLATE utf8mb4_general_ci NOT NULL, Similarly whole table definitions differ in charset: ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
AND
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciBoth MySQL 8 and MariaDB 10 are not yet in recommended stack for Ghost installation. Think it's worth leaving further tackling of the issue to the community for now. @ErisDS let me know if you think otherwise. |
|
@naz thanks for the detailed write up, I think we can safely say this is not a migration bug as it originally appeared and we don't need to do anything further 👍 Summary of this issue: Ghost does not yet officially support MySQL 8. This is because Node.js does not yet have support for MySQL 8. There are two main points of incompatibility.
To the best of my knowledge neither of these things actually with cause problems with Ghost. What does cause problems, generally, is mixed collations. This is known to cause unique constraints to fail. To repeat - MySQL 8 isn't supported in Ghost but probably works. MySQL 8 isn't the problem here, mixed collations is and that's a general administering mysql problem rather than a Ghost problem. I don't know what happened here to end up with the different collations, but I assume mysql was upgraded or the content was transferred. In MySQL 8 the default charset is utf8mb4 (what Ghost uses) & the default collation is utf8mb4_0900_ai_ci. utf8mb4_general_ci was the default in earlier versions of MySQL. I assume this is related. Going to close this as figuring out how to fix it is definitely outside of scope here - although feel free to post in our community. |
ErisDS
removed
bug
Issue Summary
I am trying to upgrade my Ghost v2.27 install to v3.8 and I am getting the following error
Contents of the Log file
Result of the journalctl command - http://bit.ly/2vZxJ6p 2
To Reproduce
Technical details:
The text was updated successfully, but these errors were encountered: