Database corruption? SQL errors and Posting probs

New Member Joined: Oct 09, 2006 Posts: 11

Evening everyone,

I've started having this issue a few hours ago, and dispite multiple attempts at "fixing" this, I have been unable to come up with something that works.

It first happened as an SQL error message

Code:

Could not insert new word matches





DEBUG MODE





SQL Error : 5 Out of memory (Needed 2555876 bytes)





INSERT INTO nuke_bbsearch_wordmatch (post_id, word_id, title_match) SELECT 6637, word_id, 1 FROM nuke_bbsearch_wordlist WHERE word_text IN ('test', 'ignore')





Line : 252


File : functions_search.php

From here on out, things became weirder and weirder.

I've looked around to find out what this message exactly means, and a way to get past this, and apparently, rebuilding the whole search list from scratch was what most (both nuke and phpbb wise) said would fix this.
Just it didn't. I tried to run a script that is supposed to rebuild the searchlist, and to a certain degree it works, only that it breaks very quickly (2nd post) with pretty much the same error message.

I tried emtpying the search tables, and reentering the data, tried working with empty tables, even emptied out the all the posts. Hell, I actually deleted the whole d*** database, then recreated and reimported. Still the same.

For one, the "View posts since last visit" only shows how many new threads there are, but not the actual threads.
For another, no new posts show up anymore, I can create threads, post replies, etc, but the listing doesn't update, I can only access new threads by entering the ID.

I am basically stumped. When I originally imported the database after a hosting change a few weeks ago, everything seemed to work fine. I made the mistake of not making daily database updates, the only ones I have are from the switch, and one made immedeately after this started.

I looked at what I thought could have made the difference, removed all posts, topics, private messages, etc etc etc, basically back to zero with almost everything, and cannot get this fixed. If anyone has any idea what else I can check, please give me a tell.

Regards
Quin

Posted: Tue Oct 24, 2006 1:48 am

I hate to say this, but this seems as if it is a server issue, the mysql server to be exact.

Not often do I see this problem but when I do, Its cause has been that the host has put some restrictions on the mysql server to save server load.

Ohh wait, Ive seen this also when a server was under attack, ussually ddos or something of that nature. PHP-Nukes real downside is that it almost demands the mysql servers full attention, others have been looking into ways to reduce the amount of calls to the server but alas, we are left with holding back for a while. Perhaps something will come up later but as long as people use standard nuke, these sql calls will stay. Sorry that I was not able to be more helpfull, perhaps others have seen other incidents where this might occur.

Posted: Tue Oct 24, 2006 3:32 am

Well, I have been able to narrow it down to the Database itself really, after a lengthy backup process to make sure I don't lose anything, I did a reset of the database to RavenNuke proper. This has solved the problem in general, I was able to finally get rid of the error, now I just have to figure out what part of the database is causing this corruption. Bang Head

Oh how I hate troubleshooting Neutral

registered · Posted: Tue Oct 24, 2006 12:02 pm

"Out of memory" usuallly means there's not enough space for your database. Are there any database file limits?

Posted: Tue Oct 24, 2006 8:50 pm

Wish I knew exact limits, but I highly doubt it. We're not running on free hosting here.
And as I said, I did a couple of database resets, then reimported truncated versions of my backup where I had deleted posts, topics, searchlists, tracked IPs, etc, at one point my database after a wipe was down 10MB and still threw out the error.

*sigh* Unless someone has a better idea, I'm just gonna have to go through all the tables one by one to find the bad one, then figure out which of the entries in that table are causing the corruption.
Thanks anyway

Posted: Tue Oct 24, 2006 9:51 pm

You may need to check the /tmp directory on your server, is it full?

Posted: Wed Oct 25, 2006 6:29 am

10GB of space, of which currently less than 1 is used, so I don't think that's the case.

Actually, by now, I believe to have narrowed it down to the Character Set of the database. At least, that, or the non-rebuilt searchlist, but I have my doubts there, as even when there is no searchlist, the error still happens with the full old backup.

I had changed the charset to utf8 unicode right after I set up the database as is, instead of the latin1 swedish set it was with the default ravennuke. I now have essentially reset the database to ravennuke proper (created the tables) and imported all data without changing the charset, and it resulted in not having any error. I have honestly not the faintest idea why this is, but it's the only point left that I haven't tried out so far.

So I'm probably going to go with that.
Speaking of which, does anyone know of a program to convert the charsets from one to another? I have noticed a few buggy chars in the database that prevent the posts/topics/whatever from showing up. I'd like to fix those chars, but not one by one.

P.S.: I noticed something weird while going through the database, and it's probably phpnuke default, but... why is the default timezone GMT+10? I mean, in general, most english speaking people don't tend to live that far away Razz

Any chance of getting this changed in the default distro?
user_timezone in the nuke_users table