Error updating to Ghost 5.80 from 5.23, blog down, please help

Bugs
1

Hello, I just updated my Ghost blog from 5.23 to 5.80.
After the update, I tried to restart Ghost, but it fails with this message:

Ghost instance is not running! Starting...
✖ Starting Ghost
A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: insert into `collections_posts` (`collection_id`, `id`, `post_id`, `sort_order`) select '65f02f0dafb7eeb96c4c90a6' as `collection_id`, '65f02f0dafb7eeb96c4c90a8' as `id`, '637a812a81cfb759fad27265' as `post_id`, 0 as `sort_order` union all select '65f02f0dafb7eeb96c4c90a6' as `collection_id`,
... (many more rows) ...
, '65314decb0c970c6a6eca545' as `post_id`, 0 as `sort_order` - SQLITE_ERROR: too many terms in compound SELECT
Context: [object Object]
Help: Error occurred while executing the following migration: 2023-07-10-05-16-55-add-built-in-collection-posts.js

Debug Information:
    OS: Debian GNU/Linux, v11
    Node Version: v18.19.1
    Ghost Version: 5.80.2
    Ghost-CLI Version: 1.25.3
    Environment: development
    Command: 'ghost restart'

Apparently it is possible that Inserting more than 500 items at once causes this error (SQLITE_ERROR: too many terms in compound SELECT.).

But the thing is… I have thousands of articles and I don’t want to delete them.
Can you help me?

Issue Summary

  • Explain roughly what’s wrong
    Ghost is not starting
  • What did you expect to happen?
    Well, you know, Ghost starting/running?

Steps to Reproduce

  1. Start with an install of v5.23 or below 5.80
  2. Apparently the number of posts can be a cause, so get more than 500 posts.
  3. Run ghost update and ghost restart

Setup information

OS: Debian GNU/Linux, v11
Node Version: v18.19.1
Ghost Version: 5.80.2
Ghost-CLI Version: 1.25.3
Environment: development
Command: 'ghost restart'

How did you install Ghost? Command line
Provide some details about your install of Ghost if you are self-hosting.

Provide details of your host & operating system
Include further details about your hosting and OS.

Database type
MySQL 5.7 / MySQL 8 / SQLite 3 / Other
mysql Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)

Browser & OS version
Include if reporting frontend bugs.
Browser is Chrome, but it is irrelevant here.

Relevant log / error output
(See above)

1 Like
2

Ok, I managed to solve it manually. The issue is in the script 2023-07-10-05-16-55-add-built-in-collection-posts.js.

Specifically, in this function:

const insertPostCollections = async (knex, collectionId, postIds) => {
    logging.warn(`Batch inserting ${postIds.length} collection posts for collection ${collectionId}`);

    const collectionPosts = postIds.map((postId) => {
        return {
            id: (new ObjectID()).toHexString(),
            collection_id: collectionId,
            post_id: postId,
            sort_order: 0
        };
    });

    await knex.batchInsert('collections_posts', collectionPosts, 1000);
};

The problem is the batch size. I guess it does not make sense to have it set to 1000 if the maximum size for batch inserts in MySQL is 500. So lowering to 100 for example does the trick.

Now, can you kindly confirm that even by reducing the batch size to 100, all posts would be updated in the database as per the new Ghost 5.80 requirements? (looking at the code, it seems that is the case, but just want to be sure).

Thank you. A PR here would be quite easy I guess, just decreasing the limit to 100.

1 Like