Jump to content

Deleting rows from tables in the database


Recommended Posts

I've found some rows in my database where the data appears to be corrupted.
The process I believe corrupted them, was trying to restore to a backup process twice, without allowing enough time for the first restore to resolve.
The outcome, is that our search index crashes on a weekly basis.  These corrupted rows are in the tables the search index is constructed from, like the forum_posts table, and status updates table.
The indicators that the rows are corrupted are as follows:  
  ~ member_id = 0 (in all other rows, member_id has a positive integer value)
  ~ many various user names for these rows, all with member_id = 0
  ~ stream_key = null for some of them.  Others it's present.  It's present in all non-corrupted rows.

I want to delete these corrupted rows, but I'm not sure if it will cause problems.
Is it ok to delete them?  Are there any steps I should take before or after?
Is it best to delete them using phpMyAdmin, and if so using SQL queries, or just the normal interfaces Delete Row option?

Link to comment
Share on other sites

  • 1 month later...

I'd recommend submitting a ticket. I'm not really clear what you mean by your search index corrupts weekly. Do you mean the database table itself becomes corrupt? If so, first I'd probably recommend deleting the table entirely, then using the support tool to recreate it, followed by rebuilding the search index (there's a button to do this on the search settings page in the AdminCP). Additionally, if you are using MyISAM tables I would recommend switching to InnoDB if using MySQL 5.7+.

member_id=0 for a post simply means the post was made by a guest (and they would have been asked for a name, so seeing various different names isn't an inherent issue). It is unlikely you should see member_id=0 for status updates however.

I would never recommend deleting rows in the database without a very good working knowledge of how everything plays in together (and without good backups first).

Link to comment
Share on other sites

I've tried submitting a ticket quite a few times already.  I would honestly feel a little uncomfortable hassling the support team with this issue again.  To be clear, they never made me feel like I was bothering them, they were always very very polite, courteous, helpful and precise with their responses... I just kind of figured I'd exhausted the issue by how many times I'd asked about it.  And pretty much every time, they ended up resolving that it's an issue with the host.  And my host is unable to do anything helpful, as they won't touch the actual database.  The only thing they'll do is restore it to a backup if required, which is pretty much their default suggestion.

What you wrote here is actually very helpful tho, I did not know I could delete the table entirely (not truncate it) and that the IPS software would actually create a new one.  I would've tried that if I had not already hired someone to help deal with it.  And if it comes to it we just might.

And yes about the memberid=0 being guest posts, I did figure that out eventually, and thank you for that.  

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...