ghost:5-alpine Latest update gone bad: SQLITE_ERROR: too many terms in compound SELECT

[grigio]

Issue Summary

I've always updated ghost via ghost:5-alpine image but this time it broken the current installation

Steps to Reproduce

docker compose up

Ghost Version

ghost:5-alpine

Node.js Version

ghost:5-alpine

How did you install Ghost?

docker

Database type

SQLite3

Browser & OS version

No response

Relevant log / error output

logs 


ost_id`, 0 as `sort_order` - SQLITE_ERROR: too many terms in compound SELECT
 |     at /var/lib/ghost/versions/5.59.0/node_modules/knex-migrator/lib/index.js:1032:19
  | 
 | [2023-08-13 14:26:49] WARN Ghost is shutting down
 | [2023-08-13 14:26:49] WARN Ghost has shut down
 | [2023-08-13 14:26:49] WARN Your site is now offline
 | [2023-08-13 14:26:49] WARN Ghost was running for a few seconds

### Code of Conduct

- [X] I agree to be friendly and polite to people in this repository

[grigio]

maybe related to https://github.com/knex/knex/issues/5644

[grigio]

WORKAROUND roll back to ghost:5.54-alpine is latest bug-free release

[daniellockyer]

@grigio Are you able to post more content from the logs? Half of the output is cut off 🙂

[grigio]

@daniellockyer

Updated from ghost:5.54-alpine to ghost:5-alpine

docker compose up
[+] Running 1/1
 ✔ Container blog-app-1  Created                                                                                                                                            0.2s 
Attaching to blog-app-1
blog-app-1  | [2023-08-15 17:59:20] INFO Ghost is running in production...
blog-app-1  | [2023-08-15 17:59:20] INFO Your site is now available on https://....
blog-app-1  | [2023-08-15 17:59:20] INFO Ctrl+C to shut down
blog-app-1  | [2023-08-15 17:59:20] INFO Ghost server started in 2.219s
blog-app-1  | [2023-08-15 17:59:20] WARN Database state requires migration.
blog-app-1  | [2023-08-15 17:59:21] INFO Creating database backup
blog-app-1  | [2023-08-15 17:59:21] INFO Database backup written to /var/lib/ghost/content/data/tech-blog.ghost.2023-08-15-17-59-21.json
blog-app-1  | [2023-08-15 17:59:21] INFO Running migrations.
blog-app-1  | [2023-08-15 17:59:21] INFO Creating built in collections
blog-app-1  | [2023-08-15 17:59:21] INFO Populating built-in collections
blog-app-1  | [2023-08-15 17:59:21] WARN Batch inserting 631 collection posts for collection 64dbbcf9b3d1650001cf21bd
blog-app-1  | [2023-08-15 17:59:21] INFO Rolling back: insert into `collections_posts` (`collection_id`, `id`, `post_id`, `sort_order`) select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21bf' as `id`, '63489e377e2a250001abcdb4' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c0' as `id`, '6348a28fc87fd00001c12635' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c1' as `id`, '6348a28fc87fd00001c12636' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c2' as `id`, '6348a28fc87fd00001c12637' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c3' as `id`, '6348a28fc87fd00001c12638' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c4' as `id`, '6348a28fc87fd00001c12639' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `co
...
c53' as `post_id`, 0 as `sort_order` - SQLITE_ERROR: too many terms in compound SELECT.
blog-app-1  | [2023-08-15 17:59:21] INFO Deleting built in collections
blog-app-1  | [2023-08-15 17:59:21] INFO Rollback was successful.
blog-app-1  | [2023-08-15 17:59:22] ERROR insert into `collections_posts` (`collection_id`, `id`, `post_id`, `sort_order`) select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21bf' as `id`, '63489e377e2a250001abcdb4' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c0' as `id`, '6348a28fc87fd00001c12635' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c1' as `id`, '6348a28fc87fd00001c12636' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c2' as `id`, '6348a28fc87fd00001c12637' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c3' as `id`, '6348a28fc87fd00001c12638' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`,
...
blog-app-1  | {"config":{"transaction":true},"name":"2023-07-10-05-16-55-add-built-in-collection-posts.js"}
blog-app-1  | "Error occurred while executing the following migration: 2023-07-10-05-16-55-add-built-in-collection-posts.js"
blog-app-1  | 
blog-app-1  | Error ID:
blog-app-1  |     300
blog-app-1  | 
blog-app-1  | Error Code: 
blog-app-1  |     SQLITE_ERROR
blog-app-1  | 
blog-app-1  | ----------------------------------------
blog-app-1  | 
blog-app-1  | Error: insert into `collections_posts` (`collection_id`, `id`, `post_id`, `sort_order`) select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21bf' as `id`, '63489e377e2a250001abcdb4' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c0' as `id`, '6348a28fc87fd00001c12635' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c1' as `id`, '6348a28fc87fd00001c12636' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c2' as `id`, '6348a28fc87fd00001c12637' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c3' as `id`, '6348a28fc87fd00001c12638' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c4' as `id`, '6348a28fc87fd00001c12639' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c5' as `id`, '6348a28fc87fd00001c1263a' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c6' as `id`, '6348a28fc87fd00001c1263b' as `post_id`, 0 as `sort_order` union all select '64dbbcf9b3d1650001cf21bd' as `collection_id`, '64dbbcf9b3d1650001cf21c7' as `id`, '6348a28fc87

[jameshome]

I'm seeing the same issue with the same migration on a non-docker install.

[jameshome]

@grigio, do you also have a large number of posts in your development environment? I'm guessing that might be a factor here.

[grigio]

@grigio, do you also have a large number of posts in your development environment? I'm guessing that might be a factor here.

~ 500

[jameshome]

Yeah, that checks out. I bet few people are running their dev instances against their production databases, which is why this error isn't more widespread.

[github-actions[bot]]

Our bot has automatically marked this issue as stale because there has not been any activity here in some time.

The issue will be closed soon if there are no further updates, however we ask that you do not post comments to keep the issue open if you are not actively working on a PR.

We keep the issue list minimal so we can keep focus on the most pressing issues. Closed issues can always be reopened if a new contributor is found. Thank you for understanding 🙂