Corrupt tables/ error messages

badboy

I've been running my php/MySQL scripts for over a year without any problems whatsoever, however in the last week some tables have suddenly been corrupted !

My Hosting provider says it's my scripting and that if a table is not locked another process could possibly corrupt a table and will not provide any other support.

Today PHPMyadmin suddenly stopped accessing my database and is giving the following error messages, Any Ideas on what could be causing this ??

Warning: Access denied for user: 'us53515c@localhost' (Using password: YES) in /lib.inc.php on line 255

Last week when attempting to save a "Structure and data" backup of a perfectly good database I noticed that PHPMyadmin was saving only a portion of the database !

Realy appreciate the help !

Pedro

Sxooter

Your hosting provider is full of soft squishy brown matter that comes from the north end of a south bound mule.

MySQL may have a few issues, but it WILL NOT corrupt data because of improper user level locking.

More than likely either the drive your database is living on is dying, or the memory in the server is bad. Here's the simple three step process to fix the problem.

Back up all your data locally.
Open a new account elsewhere
Put your site online there.

When they ask you why you left point out their utter uselessness in helping you on this problem.

If you have shell access, try compiling big programs like the linux kernel or Postgresql or MySQL and see if you get sig11 errors (You're on Unix/Linux right?) If you see sig11 errors in Linux, you've almost always got hardware issues.

superwormy

Well, the access denied is something your host is going to need to fix, more than likely a permissions problem on ehtir end.

BUT, corrupted tables are very possibly, do your scripts put a heavy load on MySQL? Lots of concurrent users / lots of queries running at once?

That can cause Error 127 table handler errors in MySQL, MySQL has serious issues under heavy load.

Fix them at the command line with myisamchk or with the CHECK TABLE / REPAIR TABLE queries.

Sxooter

Please note, a clarification. I wasn't saying MySQL never corrupts it's tables, it does, and usually under heavy parallel write load. My point was that locking or not locking the table makes no difference.

Plus, most of the times it happens, you'll find bad memory in the system that becomes more of an issue under parallel load where it's more likely to get accessed by the database. A LOT of MySQL servers sit on crapola hardware, and no database can make up for broken memory.

If you run 100 kernel compiles or something like that, and log the output, you can grep it for sig 11's, the classic sign of a hardware problem on a linux box. using a '-j 4' switch to make will help thrash memory as well. The higher the number the more compile threads the machine will launch while building.

Since most machines never really use all their memory, these problems are often unknown until load increases.

I'm not saying MySQL isn't the problem, it may well be, but until you've proven the hardware is reliable, it's all a crap shoot.

csn

I've never actually had, or directly known anybody that had MySQL data become corrupted (probably jinxing myself now). It's always been fixable with REPAIR TABLE. One time though I had to rename TMP files to MYD. Almost all of these problems were caused by the server being restarted in the middle of REPAIR or other operations.

BTW, what exactly is sig 11 and why does it indicate possible hardware problems?

Sxooter

Originally posted by csn
I've never actually had, or directly known anybody that had MySQL data become corrupted (probably jinxing myself now). It's always been fixable with REPAIR TABLE. One time though I had to rename TMP files to MYD. Almost all of these problems were caused by the server being restarted in the middle of REPAIR or other operations.

Exactly. Most of the time you need to run the file fix utilities either the machine died, a drive error occured, mysql was stopped poorly (kill -9 is not the way to stop a database, although postgresql survives it quite nicely, and autorecovers quickly) or some other problem occured like that.

BTW, what exactly is sig 11 and why does it indicate possible hardware problems?

Basically it's an error that occurs when a process fails a sanity check. I.e. some data that could not possible be what it is, is. They're generally caused by memory errors or corrupted program files, hence the danger is that if you're seeing lots of sig11s, that for everyone that crashes a program, there could be dozens of single bit memory errors going by in your data stream.

Bad memory isn't so uncommon as folks think it is, and no database can overcome it.

csn

Originally posted by Sxooter
Bad memory isn't so uncommon as folks think it is, and no database can overcome it.

Yeah, I was researching memory a while ago and was surprised by the error rate of non-parity/error-checking memory. Then I wondered why I've never seen/experienced any erroneous data (that I know of) .

Sxooter

Originally posted by csn
Yeah, I was researching memory a while ago and was surprised by the error rate of non-parity/error-checking memory. Then I wondered why I've never seen/experienced any erroneous data (that I know of) .

I think the "that I know of" part is what most people don't get.

csn

Where would the errors show up though? In many years I've never seen erroneous data retrieved from databases, files, and so on. Or would it be subtler - like sig 11's?

Sxooter

The errors show up in single bit changes in your data. If it's something like a word processing document, someone might think a word was misspelled or something like that. For every sig11 you see, there's probably subtler corruption taking place you don't. How is anyone gonna know that one or two numbers in the database are wrong now because of bad memory? If it was supposed to be 100 and it turned into 100,000,000 someone might notice, but a change from 9.375 to 9.385 isn't that big, so you might have plenty of little problems in your data if your memory isn't good, and most would be hidden by the fact that their variation form norm looks like noise to most users.