Broken notification & search after user posts delete

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
cmdr
Registered User
Posts: 5
Joined: Tue Jun 25, 2019 9:42 am

Broken notification & search after user posts delete

Post by cmdr » Wed Jul 17, 2019 7:41 am

I deleted ~1,500 posts of a user (which worked), but another SQL command failed after the posts were deleted. Unfortunately, I didn't copy the error. Something couldn't be inserted because of a duplicate primary key.

Anyways, now the notifications are broken in some threads (where that user posted). The direct link to new posts jump to the wrong post/page. A search for some keywords, where results for the same thread are shown, also jump to the wrong page/post.

The question now is: How do I "sync" the deleted posts with whatever insert/update command failed to execute? I have pretty much a vanilla phpBB installation. No extensions that could've interfered with the deletion of the posts.

User avatar
david63
Registered User
Posts: 16320
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: Broken notification & search after user posts delete

Post by david63 » Wed Jul 17, 2019 7:58 am

How did you delete the posts?
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

User avatar
warmweer
Registered User
Posts: 2337
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Broken notification & search after user posts delete

Post by warmweer » Wed Jul 17, 2019 8:11 am

david63 wrote:
Wed Jul 17, 2019 7:58 am
How did you delete the posts?
Probably directly in the posts_table (database).
cmdr wrote:
Wed Jul 17, 2019 7:41 am
I deleted ~1,500 posts of a user (which worked), but another SQL command failed after the posts were deleted.
Its a tried and verified succesful way of messing up the postcount for the user, the postcount in various topics, the total postcount, and probably a plethora of other things which don't come to mind at the moment.

If I would need to remove 1500 posts from a user, I'ld simplify things by removing the user completely.
Use your databse backup to restore the board to it's former state and delete the user's posts using the provided phpBB tools
My board's not broken, it just went peculiar

cmdr
Registered User
Posts: 5
Joined: Tue Jun 25, 2019 9:42 am

Re: Broken notification & search after user posts delete

Post by cmdr » Wed Jul 17, 2019 11:23 am

david63 wrote:
Wed Jul 17, 2019 7:58 am
How did you delete the posts?
warmweer wrote:
Wed Jul 17, 2019 8:11 am
Probably directly in the posts_table (database).
Via ACP, not via SQL(!)
warmweer wrote:
Wed Jul 17, 2019 8:11 am
If I would need to remove 1500 posts from a user, I'ld simplify things by removing the user completely.
This is not what I wanted, because the user would also lose all private messages. The posts were deleted for privacy reasons and not because I wanted to delete the user entirely.
warmweer wrote:
Wed Jul 17, 2019 8:11 am
Use your databse backup to restore the board to it's former state and delete the user's posts using the provided phpBB tools
Not possible, because the problem wasn't noticed for a few days and meanwhile, people did write new posts. Restoring a backup would delete all new posts.

User avatar
david63
Registered User
Posts: 16320
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: Broken notification & search after user posts delete

Post by david63 » Wed Jul 17, 2019 11:59 am

cmdr wrote:
Wed Jul 17, 2019 11:23 am
Via ACP,
Not sure how you would do that as deleting posts is a Moderator function and not an Admin function, unless you used an extension.
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

User avatar
warmweer
Registered User
Posts: 2337
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Broken notification & search after user posts delete

Post by warmweer » Wed Jul 17, 2019 12:05 pm

cmdr wrote:
Wed Jul 17, 2019 11:23 am
david63 wrote:
Wed Jul 17, 2019 7:58 am
How did you delete the posts?
Via ACP, not via SQL(!)
Then you should answer david63's question: how exactly did you delete ~1500 posts? (you mentioned "another SQL command" which made me think you applied that to the database).

The question you posed was: How do I "sync" the deleted posts with whatever insert/update command failed to execute?
What do you mean with "sync"? postcount not updated (user postcount, topic/forum postcount?)
Did you take a copy of the error message?
My board's not broken, it just went peculiar

cmdr
Registered User
Posts: 5
Joined: Tue Jun 25, 2019 9:42 am

Re: Broken notification & search after user posts delete

Post by cmdr » Wed Jul 17, 2019 12:38 pm

warmweer wrote:
Wed Jul 17, 2019 12:05 pm
Then you should answer david63's question: how exactly did you delete ~1500 posts? (you mentioned "another SQL command" which made me think you applied that to the database).
Ok, sorry if I was too brief:

ACP → Users and Groups → entered username → opens "User administration" → panel "Basic tools" → Delete all posts.

The resulting page showed an SQL error. I believe this function executes several SQL commands in a row. First, it deletes all posts, then it does some insert/update in another table (but since I didn't write the SQL error down, I don't know which).
The question you posed was: How do I "sync" the deleted posts with whatever insert/update command failed to execute?
What do you mean with "sync"? postcount not updated (user postcount, topic/forum postcount?)

"Sync": Something like the function in the ACP → General → "Resynchronise post counts". Although I don't want to resynchronize post counts, but notifications and linked topics.

Did you take a copy of the error message?

No, this is exactly my problem. I don't really know which table was affected by the SQL error. I just remember that an SQL INSERT statement failed, because of some duplicate primary key thing. The question now is: What gets inserted where when I execute "delete all posts" and why does this fail and how do I execute this command again without having the original posts?

cmdr
Registered User
Posts: 5
Joined: Tue Jun 25, 2019 9:42 am

Re: Broken notification & search after user posts delete

Post by cmdr » Wed Jul 17, 2019 12:41 pm

david63 wrote:
Wed Jul 17, 2019 11:59 am
cmdr wrote:
Wed Jul 17, 2019 11:23 am
Via ACP,
Not sure how you would do that as deleting posts is a Moderator function and not an Admin function, unless you used an extension.
Well, it seems to be a moderator function (a global moderator can access it), but it is located in the ACP.

User avatar
warmweer
Registered User
Posts: 2337
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Broken notification & search after user posts delete

Post by warmweer » Wed Jul 17, 2019 1:05 pm

cmdr wrote:
Wed Jul 17, 2019 12:38 pm
The question you posed was: How do I "sync" the deleted posts with whatever insert/update command failed to execute?
What do you mean with "sync"? postcount not updated (user postcount, topic/forum postcount?)

"Sync": Something like the function in the ACP → General → "Resynchronise post counts". Although I don't want to resynchronize post counts, but notifications and linked topics.

I'm clueless as to what you mean with synchronising notifications and linked topics.
Perhaps you should PM an admin account + password to someone willing to have a look (I'm not volunteering, lack of time) in order to get a better understanding of the problem (the way I understad it now your board still functions as it should but for some notifications and linked posts? Alternatively you could post some screenshots providing clarification.
Posting the The Support Request Template would also be helpful.
My board's not broken, it just went peculiar

User avatar
EA117
Registered User
Posts: 745
Joined: Wed Aug 15, 2018 3:23 am
Contact:

Re: Broken notification & search after user posts delete

Post by EA117 » Wed Jul 17, 2019 11:29 pm

cmdr wrote:
Wed Jul 17, 2019 12:38 pm
ACP → Users and Groups → entered username → opens "User administration" → panel "Basic tools" → Delete all posts.

The resulting page showed an SQL error. I believe this function executes several SQL commands in a row. First, it deletes all posts, then it does some insert/update in another table (but since I didn't write the SQL error down, I don't know which).
As you may imagine, there are a lot of updates to choose from. (Initiated by delete_posts() in /includes/functions_admin.php.) Just in the core, I stopped counting after fourteen different table deletes and updates, and settled on "a lot." 😜

In addition to following the other good advice thus far, if it had happened to me, one thing I would be trying is to create a new test user, give them at least one post, at least one attachment, and create at least one notification subscription to this user's post which is about to become deleted. Then try and delete that test user's posts, and see if you can repeat the duplicate key error.

There may be other conditions necessary to duplicate the issue that we simply don't know, so the issue may or may not duplicate successfully. e.g. Conditions which are unique to other extensions which are also installed, etc. But duplicating it would be the "easiest" way to identify which action and table had been involved in the error.

Still, even assuming you can identify and resolve that failure 100%, that only prevents future recurrence of the problem. It still doesn't help recover from the "previous partial delete completion" state that the board is currently in. Like warmweer suggested, a better understanding of "which table and error occurred" as well as "what exact problems you're facing now" is probably needed in order for anyone to suggest a path for recovery that doesn't involve restoring the database to a known-good starting point.

User avatar
warmweer
Registered User
Posts: 2337
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Broken notification & search after user posts delete

Post by warmweer » Thu Jul 18, 2019 7:07 am

It was a bit late yesterday but EA117's post gave me an idea (they usually do ;) ).

You could install the most recent backup you have (which still includes those 1500 posts) in a localhost copy of your board, actually 2 copies. And then try the action again on 1 of those.
If it fails, take a note (printscreens) of all error messages and post them here so that we can figure out what went wrong.
If it doesn't fail, then compare the succesfully affected copy with the untouched one to see which tables were affected and how. Perhaps you could then identify the fields/tables which need a little help.
My board's not broken, it just went peculiar

cmdr
Registered User
Posts: 5
Joined: Tue Jun 25, 2019 9:42 am

Re: Broken notification & search after user posts delete

Post by cmdr » Thu Jul 18, 2019 9:20 am

Thank you everybody for your help. I might try to install a recent backup on a local installation, but not sure if it's worth the effort. Maybe it's just easier to start new threads where the problem apparently doesn't occur. Fortunately, the user participated only in a handful of (very long) threads, so not the entire board is affected.
EA117 wrote:
Wed Jul 17, 2019 11:29 pm
Just in the core, I stopped counting after fourteen different table deletes and updates, and settled on "a lot."
Thanks for digging into the code. I was hoping that there was a more simple solution like "if a post is deleted, table Y is also updated, so this little INSERT/UPDATE statement should fix your problems", but it is obviously a lot more complex than that ;)

User avatar
warmweer
Registered User
Posts: 2337
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: Broken notification & search after user posts delete

Post by warmweer » Thu Jul 18, 2019 11:37 am

cmdr wrote:
Thu Jul 18, 2019 9:20 am
Thank you everybody for your help. I might try to install a recent backup on a local installation, but not sure if it's worth the effort. Maybe it's just easier to start new threads where the problem apparently doesn't occur. Fortunately, the user participated only in a handful of (very long) threads, so not the entire board is affected.
Topics, not threads ;)
cmdr wrote:
Thu Jul 18, 2019 9:20 am
Thanks for digging into the code. I was hoping that there was a more simple solution like "if a post is deleted, table Y is also updated, so this little INSERT/UPDATE statement should fix your problems", but it is obviously a lot more complex than that ;)
Actually if you're a bit handy with databases (a sql database can be imported (or linked) in a (MS, Libre or Open)Office db). That can facilitate running a few queries: getting the numbers of posts for each topic/forum, checking where last post, user and time are incorrect, finding out whether there are still notifications for inexistant topics, etc...
Added: I suggest importing it, not linking - just in case you edit something by accident.

Do you get an error message when you open the affected topics?

Also, you could try a very simple action. Set your board offline so that nothing can be added/changed by users other than yourself. Take a backup and then synchronise postcount (if you want to keep the post count of that specific user, write it down so that you can edit the database later). See if that "solves the problem". If it doesn't , just restore the backup database you made earlier.

Anyway the lesson to be learnt from this is: always take a backup before doing any large action (which you can't undo yourself).
My board's not broken, it just went peculiar

Post Reply

Return to “[3.2.x] Support Forum”