Ghost: Error: SQLITE_ERROR: table posts has no column named comment_id

[pascalandy]

To Reproduce

Hi,

I manage a lot a Ghost instance in Docker. I found a random issue I'd like to share.

One website that was running smoothly for months had this error. So I thought it was a random data corruption. Shit happen.

Then the issue happens again. This time, I started a new website from scratch. I rebooted the container about 8 times in 20 minutes. I didn’t touch the content or log in the admin panel. I was just looking at the logs and see if I could see any glitches.

I can’t explain why this error appears the comment_id is suddenly disappearing !?

NAME: RollbackError
CODE: SQLITE_ERROR
Error: SQLITE_ERROR: table posts has no column named comment_id

See the full log here.

Technical details:

• Ghost Version: 2.1.1
• CLI version: 1.9.4
• Node Version: 8.11.4
• Browser/OS: chrome 66, osx 10.13.4
• Server-side: Alpine:3.8 in Docker using this image
• Database: sqlite

Cheers!

[pascalandy]

Actually, I'll re-test using 2.1.3

[kirrg001]

Error: SQLITE_ERROR: table posts has no column named comment_id

This is a migration error. Happens only if you trigger migrations or if migration needs to be executed.

One website that was running smoothly for months had this error. So I thought it was a random data corruption. Shit happen.

This error looks like you migrated from Ghost 1.x to Ghost 2.x, but you were not on the latest Ghost 1.x release.

Let me know 👍

[pascalandy]

I confirm I was running a fresh Ghost 2.1.1 install. I don't see why a trigger migrations happened.

[kirrg001]

Can you pls share detailed steps to reproduce? Thanks :)

[kirrg001]

And: Can you double check if you don't select an existing 1.x database on fresh installation?

[pascalandy]

I did the same process using Ghost 2.1.3. It's all good now. My guess is that v2.1.1 had some bug triggering the migration process.

Step to reproduce:

This time, I started a new website from scratch. I rebooted the container about 8 times in 20 minutes. I didn’t touch the content or log in the admin panel. I was just looking at the logs and see if I could see any glitches.

I'll close this issue. Thanks!

[pascalandy]

Re-opening :( I got the same issue under 2.1.3. I fear this is a weird bug.

In this case:

• the blog was updated from 1.25.4 to 2.1.3
• ran perfectly for few days
• I was editing the Code Injection section and the blog crashed
• Tried the restart the container without success.
• In the logs, I found the same error as described above

My 5 cents: I would like to know what are the scenarios that trigger the migration process.

• Why is ghost wants to migrate a DB that is actually running under 2.1.3?
• Could the system suddenly thinks that one file from the backup DB( from 1.25.4) needs to be migrated?

[kirrg001]

I was editing the Code Injection section and the blog crashed

Do you have the error log available? Interested to hear why you were able to crash Ghost with code injection 🤔

I will investigate as soon as i have enough information.

[kirrg001]

@pascalandy

[pascalandy]

It was the exact same error as https://gist.github.com/pascalandy/8a18172090f743b46e15fbff9015f4fe
mentioned above. but no I didn't track the log this time.

[kirrg001]

The migrations are only triggered if you

A: never executed them, but you said you have installed a fresh blog
B: you delete the migration entries from the database manually

Something must be weird on your container, which i can't explain.
This blog must be on < 1.25 and tries to migrate to 2.0 and this is not allowed. The CLI protects against this and throws an error.

I don't know how to reproduce this.

[kirrg001]

How many blogs are installed on your container?

[pascalandy]

There is only one per container.

[kirrg001]

Can you reproduce this on a fresh container?
If so, please share the exact steps. Thanks :)

[pascalandy]

That's the whole challenge, it's random at this point.

Idea - Could the backup DBs (made of V1.xx) trigger the migration behavior? I see I have bunch of them.

[kirrg001]

As long you don't restore the backup, no.

If it happens again, can you pls share ghost ls and log into mysql and run select * from migrations;.
Furthermore the log you've provided, i need some more lines before the actual migration starts.

[pascalandy]

Sure, will do. But I use SQLite. How can I do a similar request?

[kirrg001]

It's the same SQL query, you just need to login using sqlite3.
sqlite3 path-to-db-file and then select * from migrations;

[pascalandy]

Good to know! Will if I encounter this issue again.

[kirrg001]

@pascalandy Any news? I am not rly sure what else to test. Let me know :)

[pascalandy]

I didn't had the issue since. I'll close this for the moment. Thank you for caring :)

[pascalandy]

RE-OPENING

This website is another blog I manage. I'm trying to understand which issue makes the migration not going smoothly.

In this case:

• the blog was updated from 1.25.5 to 2.2.4
• the blog does not run on 2.2.4 after trying to migrate :((
• Tried the restart the container without success.

EDIT, I used Ghost 1.25.5 and 2.2.4. From Travis:

• devmtl/ghostfire:2.2.4-ff50fdc-20181022_03H0121
• devmtl/ghostfire:1.25.5-9e38cf2-20180820_20H0205

1) Logs generated by Ghost:

https://gist.github.com/pascalandy/0492231e4c88f0b5e742d868c94d0b41#file-log-before-migration

I'm curious about all these warnings

2) Logs available in the container:

https://gist.github.com/pascalandy/0492231e4c88f0b5e742d868c94d0b41#file-migration-bug

3) About doing ghost ls

I can not. The container restarts all the time.

4) The same action, worked within another blog

https://gist.github.com/pascalandy/0492231e4c88f0b5e742d868c94d0b41#file-this-blog-worked-L4

Technical details:

• Ghost Version: 2.2.4 (in Docker)
• CLI version: 1.9.6 (in Docker)
• Node Version: 8.12.0 (in Docker)
• Browser: Version 70.0.3538.67 (Official Build) (64-bit)
• OS X 10.13.6
• Server-side: Alpine:3.8 (in Docker)
• Database: sqlite

[pascalandy]

I upgraded another blog successfully.

There are three warnings from the container:

0236d8dea9af / docker logs --follow 0236d8dea9af
[2018-10-22 02:55:38] WARN knex-migrator is skipping 2.3
[2018-10-22 02:55:38] WARN Current version in MigratorConfig.js is smaller then requested version, use --force to proceed!
[2018-10-22 02:55:38] WARN Please run `knex-migrator migrate --v 2.3 --force` to proceed!

[kirrg001]

@pascalandy

I upgraded another blog successfully.
There are three warnings from the container:

Thanks for sharing. You can ignore the warning. We will release Ghost 2.3.0 today 👍

[kirrg001]

This website is another blog I manage. I'm trying to understand which issue makes the migration not going smoothly.
the blog was updated from 1.25.4 to 2.2.4

It doesn't look like your 1.x blog was on 1.25. The logs show that the migrator tried to insert the demo post 🤔

{"name":"Log","hostname":"client-5342","pid":36,"level":30,"msg":"Adding demo post. ","time":"2018-10-21T18:59:01.498Z","v":0}

Your 1.x has to be on the latest version, which is 1.25.5.

[pascalandy]

EDIT, I was using Ghost 1.25.5 and 2.2.4. From Travis:

• devmtl/ghostfire:2.2.4-ff50fdc-20181022_03H0121
• devmtl/ghostfire:1.25.5-9e38cf2-20180820_20H0205

[pascalandy]

I still can't re-create the issue :-(
Closing

[kirrg001]

@pascalandy urgh frustrating :( Would love to help, but not sure how!