Single post causing DB errors - How to fix?
 

I put this on the vbulletin.com forums but have yet to get a response.

Thought maybe someone here could help.

I have a site running 3.8.7 and in only one particular thread users are getting a DB crash when trying to load a particular page.

I have my userCP settings to show 30 posts per page.

I have narrowed down the post to Page 25 of 26 pages (I can load Page 24, and I can load Page 26)

When I try to load Page 25 I get a DB crash.


How can I manually go in and see which specific post is causing the issue and delete it?

Here is the URL: https://www.toronto-subaru-club.com/...cycles-25.html

Reading through other solutions I saw that running the "Rebuild Post Cache" sometimes will fix things.... I ran it.

After a while it also crashes.

I suspect it is hitting that post and crashing.


What can I do? I really don't want to delete that thread as it has some great content in it.


Thanks,


D.

We need to know what the database error is.

I am working on it.

Trying to run the post cache process again to try and capture it.

D.

You sure this is the correct url ...cycles-25.html

Here is a new development.

In IE that URL loads fine (albet slow)

That same URL crashes in Chrome.

I am trying to run the Post Cache function in IE to see if it makes a difference.

D.

Here is the contents of the DB error when running the Post Cache Funtion.

It seems to be from something else than the thread that my users are complaining about.

Is there something I can run on the DB to fix all errors?

D.

Update.

I rebuilt all my attachment thumbnails as when I looked in that error from before it seems to point to a post with an attachment.

I am trying the rebuild cache again.

However, in chrome, that page still throws an error if you try to load it.

D.

Here is what I get:

That's a browser crash, not a db error.

It is a browser crash based on a DB issue.

I am still unable to complete a Rebuild Post Cache due to errors.

As they come up, should I delete the posts that cause the crash?

For example this is where that post cache rebuild process crashes everytime right now:

Invalid SQL:

REPLACE INTO postparsed
(postid, dateline, hasimages, pagetext_html , styleid, languageid)
VALUES
(1556695,
1403612046,
1,
'<div style=\"margin:20px; margin-top:5px; \">\r\n <div class=\"smallfont\" style=\"margin-bottom:2px\">Quote:</div>\r\n

How would I go about deleting that one post (1556695) and then retrying the rebuild post cache and deleting each post that errors till I can get it to successfully run all the way through?

OR

Is there something I can just run that will fix all errors in the DB?

The DB is over 10 years old at this point and probably could use some sort of cleaning or reindexing I would think....

Or am I wrong?

D.

Trash the thread for now, meaning, move it to a non publicly visible area of your board like a admin area or a "Garbage Bin" area.

Once you get this sorted you can then move the thread back to the original public forum it was in. This will stop your members from encountering this error while you work on it.

You sure someone has not put some coding in the post that is causing the error.

It is quite possible.... but i have no way to tell.

How can I figure it out?

D.

Do you allow groups to use html

The postid is 1556695, so have you taken a look at your database post table to see what is in there as the text?

I will double check settings for HTML

How do I search the DB for things like this?

I have no idea how to do that.

Or can I just nuke that post?

D.

Yes.

Otherwise go into the db itself, and look in the tables to see what is in that post.

It turns out ... yes... I had HTML enabled. I have now turned it all off.

How can I delete that post in the DB as I can't get to it via the site itself.

OR... since I did have HTML turned on... is there a way to automatically have all HTML removed from all posts?

D.

If you have phpMyAdmin, you should be able to edit the post directly in the post table. You can set the post.visible column to "2" instead of "1" and then it would be soft deleted. Then you should be able to delete it via the thread itself and it will fix all the other tables that need to be changed when you delete a post.

You could truncate it with a SQL query via the AdminCP. Temporary ignorance though, on exact query to use.

Can I do this via the "Execute SQL Query" in the AdminCP ?

I hate to ask... but I am not SQL savy....

What specifically do I do in phpMyAdmin ?


I suppose what you are saying is to "soft delete" the post in the DB then delete it in the forums itself.

Then run the Post Cache rebuild till I find the next post issue and do the process again.


My question is this:

How can I load that specific post in the forums to hard delete it?


D.

--------------- Added [DATE]1403719057[/DATE] at [TIME]1403719057[/TIME] ---------------

Should I try the IMPEX cleaner.php tool?

D.

There are a few places html are turned on group permissions and Forums & Moderators/Forum Manager then edit each forum you have and under Enable / Disable Features
Allow HTML = No
There might also be a setting in the options somewhere, but can't remember so check. It's not a good idea to allow members to use HTML as they can insert code

Having HTML enabled per forum is a security issue IF you cannot trust 100% of your members and not may site owners can so don't think you're one of the few - instead disable the ability to post HTML in ANY forum.

Now that it's disabled rebuild your posts, if html is now disabled all their code is null and void i.e. it will show the exact code used in the post not a flash player or music player it will be the actual code i.e. <object type="code blah blah><more code param etc etc></object> so in other words - once you disable the html it should not affect the site nor produce a database error anymore so no need to delete that thread now, as you were back to business!

*Remember to disable the ability to post html in ALL forums UNLESS its a admin-only forum where only admins can post, if guests or members can view that's fine but do not allow them to post or edit posts in a html postable forum.

What am I running?

Rebuild Post Cache or something else?

D.

Tried... still erroring when running rebuilding cache

D.

Did you ever get the MySQL max_packet_size adjusted?

According to the AdminCP it is set to 16MB



D.

Have it at 16 myself but try putting it to 32 and test it to rule that out as the problem if its not u can always put it back

I will ask my host to make the change.

Anything else I can try in the meantime?

Is it worth me trying the IMPEX tools?

D.

Impex won't do a thing to help out with this problem.

Did you modify that column in the database like I suggested?

Can you tell me how to do that?

I will need exact commands if you don't mind.

D.

--------------- Added [DATE]1403744223[/DATE] at [TIME]1403744223[/TIME] ---------------

Okay... figured out how to use myphpadmin...

I did like you said and took care of the post causing that error.

Now, I get another.


I looked at the post... there was nothing strange in it either.


There has to be a better way than doing this one by one.... it's going to take forever.

or should I just say screw it? ;)


Makes me sort of panicy thinking there are a lot of issues in the DB potentially and that I can't run the rebuild post cache.

Should I turn caching off?

D.

Are you using a caching mod like vboptimes or such like?

Okay... here's an update.

I had my host increase the MySQL max_packet_size to 32M and now I am able to get through a complete run of the Rebuilt Post Cache.

WHEW!

However, there is still that one post in that thread that is causing Chrome to crash.

I can't seem to figure out which post it is or how to deal with it.

I now have ZERO database errors being thrown at me or being able to be captured... but any ideas how I can isolate that one post that is causing that thread to crash?

D.

In the future, you don't really need to run the rebuild post cache function.

truncating the post_parsed table, will let it naturally rebuild.

Do you have any idea what section or topic this bad post is

Update: Something very odd has happened..... and not sure why.

Now... when people click "new posts" nothing comes up even though there are new posts. If you click the "click here for posts in 24 hours" all the new posts are there.

I tried clearing and rebuilding search index after I rebuild the post cache.... still no go.


D.

--------------- Added [DATE]1403803743[/DATE] at [TIME]1403803743[/TIME] ---------------

I have isolated the offending post.

I adjusted my UserCP to only show one post per page and went post by post.

This looks to be the bad post:

https://www.toronto-subaru-club.com/...ycles-745.html


I am going to try and search for it using phpmyadmin

D.

What cookie mark read system are you using? Read posts don't showup in new posts (hint: they're not new)

I made no changes to the cookie settings.

It was all working prior to all this playing around

D.

--------------- Added [DATE]1403804601[/DATE] at [TIME]1403804601[/TIME] ---------------

The good news is I was able to nuke that bad post and that thread is working perfectly now.

D.

--------------- Added [DATE]1403817366[/DATE] at [TIME]1403817366[/TIME] ---------------

I am happy to report that because of all this great advice my site is now working very well.

I managed to find the offending post in that thread and deal with it, that setting change did wonders for the operation of the site, and now that I cleared my own browser cookies the "new posts" are working as they should.


I want to sincerely extend a huge thank you to everyone that chimed in and had such great ideas and suggestions.

Not only did we solve the issues, but I learned a lot as well.

I had never used some of the techniques you illustrated and it is very good information to help me continue on.


THANKS!

D.