MySQL Forums
Forum List  »  Data Recovery

Trying to repair bad table in MySQL
Posted by: Mark Beans
Date: September 12, 2012 12:52PM

Hi-

I will start by apologizing for being a MySQL “noob”.

We are working on a customer’s DB that appears to have corrupted data in at least one field in row 3600. We found the corruption when attempting to upgrade the DB to a newer version of our medical application (my company is a VAR). When our conversion runs it hangs in this table at row 3600 (there are a total of 97450 records in the table) and the MySQL service kicks offline.

We have tried MySQL querys to…

1) delete the bad record
2) replace the data in the bad field with a null or “” or a space
3) dump the bad table to a file (thinking we could remove the bad record(s) and reimport the data to an empty table)

Every time we hit the bad record the MySQL service kicks offline. The only thing we can do with the table is a count of all records. We can also read the data out of the first three fields, when we attempt to read the data in the fourth field the MySQL service kicks offline. Our software application appears to run okay though I am sure it would crash if we could identify the patient with the bad data.

All the MySQL repair articles I have found detail the steps of the repair using the MySQL command line (which I assume is the MySQL shell) but they all appear to require the MyISAM engine. This DB is setup using the InnoDB engine and when I attempt to convert the offending table to MyISAM the MySQL service kicks offline.

This older version of our medical application uses Version: '5.0.26-community-nt' socket: '' port: 5153 MySQL Community Edition (GPL). I have tried all the MySQL Query Browser versions from 1.1.9 to 1.1.20 and the latest version of MySQL WorkBench and always get the same results. The err logs don’t seem to be any help (though I included some info from it at the end of this post).

I started out working in a Windows Server 2003 EE VPS and am now on a dedicated Windows box running Windows Vista Home Premium SP2 with an AMD Phenom II x4 955 processor running at 3.2 Ghz and 2 Gig Ram.

The table structs are…
CREATE TABLE `soapware_charts_xmldocumentitems` (
`DocumentItemID` varchar(36) NOT NULL default '',
`DocumentID` varchar(36) NOT NULL default '',
`ItemType` varchar(50) NOT NULL default '',
`ItemData` longtext NOT NULL,
`CheckSum` varchar(50) NOT NULL default '',
`ItemName` varchar(50) default '',
`Deleted` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`DocumentItemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 4096 kB; InnoDB free: 5120 kB; InnoDB free: 512'$$

We are up against the wall and any help would be roundly appreciated.

Mark Beans
MedicalAIS.com



Here are a few lines related to the crash in the .err log…

120912 12:44:07 InnoDB: Started; log sequence number 38 286627932
120912 12:44:07 [Note] C:\Program Files\SOAPware\SOAPware DataServer\bin\mysqld-nt.exe: ready for connections.
Version: '5.0.26-community-nt' socket: '' port: 5153 MySQL Community Edition (GPL)
120912 13:14:29 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
120912 13:14:29 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 38 286627942.
InnoDB: Doing recovery: scanned up to log sequence number 38 286627942
InnoDB: Last MySQL binlog file position 0 0, file name
120912 13:14:29 InnoDB: Started; log sequence number 38 286627942
120912 13:14:29 [Note] C:\Program Files\SOAPware\SOAPware DataServer\bin\mysqld-nt.exe: ready for connections.
Version: '5.0.26-community-nt' socket: '' port: 5153 MySQL Community Edition (GPL)
120912 13:29:45 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
120912 13:29:45 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 38 286627952.
InnoDB: Doing recovery: scanned up to log sequence number 38 286627952
InnoDB: Last MySQL binlog file position 0 0, file name
120912 13:29:46 InnoDB: Started; log sequence number 38 286627952
120912 13:29:46 [Note] C:\Program Files\SOAPware\SOAPware DataServer\bin\mysqld-nt.exe: ready for connections.
Version: '5.0.26-community-nt' socket: '' port: 5153 MySQL Community Edition (GPL)

Options: ReplyQuote


Subject
Views
Written By
Posted
Trying to repair bad table in MySQL
4350
September 12, 2012 12:52PM
1632
September 26, 2012 08:13AM
1510
October 08, 2012 08:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.