Delete all content throws error when subscriber is associated to a post

[kirrg001]

When trying to delete all content, we are getting a SQL error. Seems that deleting the content does't work if there is a subscriber associated with the post because of a constraint.

delete from `posts` where `id` = 294 - ER_ROW_IS_REFERENCED_: 
Cannot delete or update a parent row: a foreign key constraint fails 
(`blog-495478`.`subscribers`, CONSTRAINT `subscribers_post_id_foreign` FOREIGN KEY 
(`post_id`) REFERENCES `posts` (`id`))

Important: This bug does not exist using sqlite.

Steps to Reproduce

1. Subscribe to a post
2. Go to Labs
3. Press Delete All Content button

Technical details:

• Ghost Version: 0.11.4
• Node Version: 4.4.7
• Database: MySQL

[vivekannan]

https://github.com/TryGhost/Ghost/blob/master/core/server/api/db.js#L94

Replace this,

var collections = [
            models.Post.findAll(queryOpts),
            models.Tag.findAll(queryOpts)
];

with this,

var collections = [
            models.Subscriber.findAll(queryOpts),
            models.Post.findAll(queryOpts),
            models.Tag.findAll(queryOpts)
];

[kirrg001]

@vivekannan Thanks 👍

We would be happy about a PR, which 1. reproduces the error in a test and add's your fix on top to proof that it fixes this bug.

[vivekannan]

OK. I am struggling to get the db integration test to create subscribers associated with a post so that the it fails during the deleteAllContent test. Also, how are posts properly deleted even with tags associated with them? Shouldn't it raise the same constraint error when deleting posts with subscribers?

[kirrg001]

@vivekannan Did you run your test with MySQL?

NODE_ENV=testing-mysql TRAVIS=true

[vivekannan]

Yes. I did run the test with NODE_ENV set to testing-mysql. However, the test passes as posts with subscribers are not created while testing the deleteAllContents API. So, I have to make changes to the test so that it creates dummy posts with subscribers.

https://github.com/TryGhost/Ghost/blob/master/core/test/integration/api/api_db_spec.js

The testUtils.setup doesn't create posts with subscribers.

[ErisDS]

We have this same issue for trying to delete a single post when it has a subscriber tied to it, but not when trying to delete a subscriber (I'm sure we fixed that one, but can't find the issue).

I have confirmed this on Ghost(Pro) blog today:

Create post, subscribe from post:

• try to delete post -> ER_ROW_IS_REFERENCED_
• try to delete subscriber -> works
• now delete post -> works

Also, the fix implemented here for deleteAllContent is, IMO, the wrong fix.

Delete all Content is meant to delete content = posts & tags. Deleting subscribers is unexpected behaviour IMO.

In the alpha, we have removed the constraint that is causing these issues. So this issue is LTS only.

[kirrg001]

Delete all Content is meant to delete content = posts & tags. Deleting subscribers is unexpected behaviour IMO.

I see 👍

Have scheduled it for the next LTS.