Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQLITE_ERROR: too many SQL variables (Upgrade 2.35.0 to 3.0.0) #11263

Closed
gierschv opened this issue Oct 23, 2019 · 2 comments · Fixed by #11270
Closed

SQLITE_ERROR: too many SQL variables (Upgrade 2.35.0 to 3.0.0) #11263

gierschv opened this issue Oct 23, 2019 · 2 comments · Fixed by #11270
Assignees
@naz
Labels
bug [triage] something behaving unexpectedly

Comments

@gierschv
Copy link

gierschv commented Oct 23, 2019 •
edited

Hi there,

Issue Summary

When I tried to upgrade my Ghost from 2.35.0 to 3.0.0, the process crashes in loop with when the database is being migrated:

SQLITE_ERROR: too many variables / Error occurred while executing the following migration: 05-populate-posts-meta-table.js

Technical details:

  • Ghost Version: 3.0.0 (upgraded from 3.0.0)
  • Node Version: 10.16.3
  • Browser/OS: Ubuntu
  • Database: SQLite

Thank you!
@danymill
Copy link

danymill commented Oct 23, 2019 •
edited

Same issue with the same versions except for OS: MacOS 10.15 Catalina and previous Ghost version is 2.30.2.
I couldn't rollback, so I had to install clear copy 3.0.0 and import content from backup.

@naz naz self-assigned this Oct 24, 2019
@naz naz added bug [triage] something behaving unexpectedly data labels Oct 24, 2019
@naz
Copy link
Member

naz commented Oct 24, 2019

The issue here is with hitting SQLite's internal SQLITE_LIMIT_VARIABLE_NUMBER limit when updating with large amount of posts having metadata fields set. This limit is set to 999 by default but can differ depending on the distro.

Think a possible solution would be rewriting the 05-populate-posts-meta-table.js migration to use iterative method and update posts in small batches so the most standard limit of 999 params is never hit 🤔

Related issue: #5810

naz added a commit to naz/Ghost that referenced this issue Oct 24, 2019
@naz
🐛 Fixed 3.0 migration for SQLite
e8cb5a6 
closes TryGhost#11263

- The issue here is with hitting SQLite's internal SQLITE_LIMIT_VARIABLE_NUMBER limit when updating with large amount of posts having metadata fields set (ref.: https://sqlite.org/limits.html#max_variable_number)
- Transforming migration to iterative method avoided inserting lots of records at once
@naz naz mentioned this issue Oct 24, 2019
Merged
naz added a commit that referenced this issue Oct 28, 2019
@naz
🐛 Fixed 3.0 migration for SQLite (#11270)
99c6351 
closes #11263

- Fixed `3.0/05-populate-posts-meta-table.js` migration failure when having >999 posts with metadata in the database
- The issue here is with hitting SQLite's internal SQLITE_LIMIT_VARIABLE_NUMBER limit when updating with a large amount of posts having metadata fields set (ref.: https://sqlite.org/limits.html#max_variable_number)
- Transforming migration to iterative method avoided inserting lots of records at once
@naz naz mentioned this issue Nov 7, 2019
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@naz naz

Labels
bug [triage] something behaving unexpectedly
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

🐛 Fixed 3.0 migration for SQLite naz/Ghost
3 participants
@gierschv @naz @danymill