Random Table Corruption Getting Worse

Dev Shed Forums Sponsor:

March 14th, 2013, 11:35 AM

diyIT

Registered User

Join Date: Mar 2013

Posts: 2

Time spent in forums: 50 m 9 sec
Reputation Power: 0

Random Table Corruption Getting Worse

I am an owner-operator of a business that is running mysql 5.0.51a with an enterprise ERP system. Over the past while I have been grappling with random table corruption. The corruptions started last fall and have slowly become more frequent, and now this week I have had three. I am definitely not expert at mysql, relying on standard off-the-shelf installs on my systems, and hoping for the best with the basic configurations. I suspect the cause is hardware related: probably the disk or the disk controller, but I cannot yet rule out a mysql configuration or other mysql issue.

Questions:
1. The disk is at 92% full. Can this cause table corruption?

2. Currently the only logging is for errors. How can I change the logging to better discover when and how the table corruptions are occurring?

March 14th, 2013, 01:08 PM

E-Oreo

Lost in code

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Dec 2004

Posts: 8,057

Folding Points: 945 Folding Title: Novice Folder

Time spent in forums: 2 Months 1 Day 5 h 51 m 35 sec
Reputation Power: 7104

Quote:

1. The disk is at 92% full. Can this cause table corruption?

It shouldn't.

Quote:

2. Currently the only logging is for errors. How can I change the logging to better discover when and how the table corruptions are occurring?

If the problem is hardware related, MySQL probably won't be able to log anything more useful about it.

What exactly do you mean by the tables are becoming corrupted?

__________________
PHP FAQ
How to program a basic, secure login system using PHP
Connect with me on LinkedIn

Quote:

Originally Posted by Spad

Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

March 14th, 2013, 02:43 PM

diyIT

Registered User

Join Date: Mar 2013

Posts: 2

Time spent in forums: 50 m 9 sec
Reputation Power: 0

Quote:

Originally Posted by E-Oreo

It shouldn't.
If the problem is hardware related, MySQL probably won't be able to log anything more useful about it.

What exactly do you mean by the tables are becoming corrupted?

I forgot to mention, the storage engine is innodb.

A mysqlcheck on the table produces an error. Here is what the error.log has:

Code:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1477671.
InnoDB: You may have to recover from a backup.
130314  8:34:42  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex [hex string deleted]
130314  8:34:42  InnoDB: Page checksum 412120851, prior-to-4.0.14-form checksum 2820154251
InnoDB: stored checksum 921764659, prior-to-4.0.14-form stored checksum 2820154251
InnoDB: Page lsn 922 3852332481, low 4 bytes of lsn at page end 3852332481
InnoDB: Page number (if stored to page already) 1477671,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 551733
InnoDB: (index PRIMARY of table [database]/[table])
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1477671.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.

I have been successful in dumping table, dropping table, and restoring table from the dumped .sql file. I have also restored corrupted tables from daily backups.

The error log does e4eem to suggest a hardware issue, but then why is it that can restore the table from a dump of itself?

Random Table Corruption Getting Worse

Developer Shed Advertisers and Affiliates