Database Tool to correct for problems

[DaveLembke]

Was wondering if anyone had any suggestions of any database tools that are helpful in performing clean up operations and pointing out any trouble areas for databases that are MySQL based?   His database is running on MySQL 5.1.73 btw.

Currently I am working with a database that this one client has in which there is some corruption and I have been looking at the error logs and then manually going in and correcting the problems, but it seems like as many problems that I solve more seem to show up.

The type of problems have been missing entries or entries that are incorrect for the datatype etc. It all started when his server started to act up and finally crashed. The server had a bad RAM stick and it seems as the bad memory stick caused the MySQL database to corrupt randomly as it was running unhealthy.

He didn't want to have to pay to have it serviced and so he kept just rebooting it when it was down and it would come back up and run some more until the next crash.

When it was no longer just a forced shutdown and reboot but reports started showing problems he then contacted me. Going through it its a BIG MESS. And I thought maybe I should post this on CH to see if anyone has any tools that work well with  MySQL to pinpoint and correct for problems.

Currently I am using SQLyog to go into the database tables with a nice GUI vs manually doing it all from MySQL command shell interface.

At one point I was thinking it might be easier to just export the data from the newest database that is corrupt for data entered after the last backup, and then join the latest data to a restored old backup and then only have to work on correcting the newest of data, but I found out that this client wasn't performing regular backups, and the last backup was performed about 5 months ago at the turn of the new year when he thought it might be a good idea to backup his database.

Installed new RAM into his server and so the server is ready to go now, but the database is in need of some help before it goes live again. The good thing is that the importance of this database is only used on weekends when him and his friends work their 2nd job together on the side, so I have until Friday to clean this up and try some tools against a replicated copy of the database to see if the tools aid in fixing the mess without touching the master database and making matters worse if a tool decides to eat the database etc.

[BC_Programmer]

MySQL comes with MySQL workbench. Though it's been so long since I've used it, all I remember is that it's more reliable than the postgres pgadmin tool.

You are probably looking at correcting it manually.

What kind of errors, specifically? are constraints being violated, or does the database itself actually refuse to launch?

Furthermore, even if you get the database to be consistent, that doesn't mean it is accurate. The entire thing, I'd say, cannot be trusted to be accurate because pretty much anything loaded into the bad RAM will be questionable, which can include numerous internal data structures that got written to disk. While there might be tools that could repair the database structure itself, that doesn't mean the database will work with whatever it is designed for. By way of example a few months ago we accidentally delivered a early development version of a program. It was only in use for a day before we noticed and we were able to deploy the proper version to the affected sites, but because the testing version had a known issue involving rounding we had to go through and verify every single invoice that was created on those days. What I mean is, in those cases, all the database information was completely valid and worked fine, it was the data itself that was corrupted. In the case you mentioned, even if you can coax the data itself into a form that prevents constraints from being violated, that doesn't mean the data itself is still any good. If the actual structures were corrupted I'd be surprised if the actual data itself wasn't affected, especially if a lot of the contents are corrupted and violating constraints.

[DaveLembke]

Hello BC ... Thanks for your assistance with this one.

I agree that the data could be wrong where say a value was changed from 109.99 to 51.00 and as a dollar amount unless you know what was sold to determine if the figure is correct it could get skipped and skew reports and balances etc.

I suggested to the guys that they should start fresh with a new clean database for new entries and for looking up old info in the old database just to be cautious that the info returned may be incorrect.

I found more on this while working with it. The database reports the following:

MySQL is starting with mysql\bin\my.cnf (console)
150505 11:23:07 [Warning] '--skip-locking' is deprecated and will be removed in
a future release. Please use '--skip-external-locking' instead.
150505 11:23:07 [Note] Plugin 'FEDERATED' is disabled.
150505 11:23:08  InnoDB: Started; log sequence number 0 44253
150505 11:23:08 [Warning] mysql.user table is not updated to new password format
; Disabling new password usage until mysql_fix_privilege_tables is run
150505 11:23:08 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20
. The table is probably corrupted
150505 11:23:08 [ERROR] mysql.user has no `Event_priv` column at position 29
150505 11:23:08 [ERROR] Event Scheduler: An error occurred when initializing sys
tem tables. Disabling the Event Scheduler.
150505 11:23:08 [Note] mysql\bin\mysqld: ready for connections.
Version: '5.1.73-community'  socket: ''  port: 3306  MySQL Community Server (GPL
)

From looking online it looks like they upgraded from 5.0 to 5.1 at some point and this problem started with that. This problem above is probably not related to their memory issues that they had because many others out there had the same problem such as one person reporting that 3 systems upgraded from 5.0 to 5.1 got the same problem so it sounds like an upgrade bug.

Called client and asked if he had upgraded the version of MySQL in the past or not. He said yes, he had some college kid doing some work for him and the kid suggested an upgrade to 5.1. I informed him that the upgrade to 5.1 had a few issues and that I am going to try to work out those issues that the kid that he had working on this should have seen and dealt with. From what i have read it looks like the database can actually run ok with these problems and so the kid may have said well it works ... give me my pay for work and moved on figuring no one would ever know any better. OR he clearly missed checking the status of the database and it worked so there must be no problems.

More info here on this:

http://forums.mysql.com/read.php?26,600179,600179#msg-600179

As far as the problems with expecting an INT and getting troubled data from the old database it looks like those problems are gone for now. Reports are now working that didnt work before because they would hang or crash because of garbage entries where somehow alpha string data got passed to an INT32 etc. I ended up sorting the columns using SQLyog to place values lowest to highest and this then shifts the bogus ( non INT data ) to be at the bottom of the list with numbers proceeding the alpha junk that somehow got passed to an INT32 etc. As far as if a value that is an INT was passed as incorrect value to database though, its still hidden in the mix of numbers. So the old data cant be trusted as 100% correct.

Contacted client and informed him that he needs to start with a fresh database and that he would still have access to old database for checking on info that may or may not be correct. Renamed old database and placed it to work side by side the new clean database. New clean database is same name as original database and so the interface they are using will work without problems. The old database with a new name I just needed to change the path in the PHP header to point to OldDatabase and create an alternate button on the main page to point to old database

such as editing the header of the php of the alt main code to be, where username and password is X'd out to not share clients actual database credentials. 

Code: [Select]

<?php
$servername = "localhost";
$username = "xxxxxxxxxx";
$password = "xxxxxxxxxx";
$dbname = "OldDatabase";
So I guess I need to follow this guide to fix the MySQL Database issues http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html

Mainly mysql_upgrade needs to be run on it, but not going to do this until I perform a backup after all the work put into fixing corruption.   http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html

[DaveLembke]

UPDATE:

 Performed the mysql_update fix and it corrected the errors and 1 of 2 warnings with MySQL 5.1.73. The last warning is not critical and so i am going to leave it be. Informed the client to leave the database at its current version unless there is an absolute need to update it. Also gave him a batch to run to perform sql dump for alldatabases when no one is entering any data to perform regular backups via

Code: [Select]

mysqldump --all-databases> alldatabases.sql -uxxxxxxx -pyyyyyyy
where xxxxxxx is the user name and yyyyyyy is the password for the database.

As well as a restore batch that he can point and click to restore the database from that is placed into the root of the batch execution directory of

Code: [Select]

mysql -uxxxxxxx -pyyyyyyy< alldatabases.sql

Below is a copy/paste from the start of the database this morning after the repair. The good thing is that when running this mysql_update instruction all databases displayed OK in the list, so the client should be good to go now. Wanted to post this update of the fix in case anyone else ever runs into this. The correction for the issues of 5.0 to 5.1 were easy, while the problems with the corrupt data meant that he had to start with a clean database and have read-only use of the old database for look up of info that may or may not be correct.

MySQL is starting with mysql\bin\my.cnf (console)
150506 10:32:21 [Warning] '--skip-locking' is deprecated and will be removed in
a future release. Please use '--skip-external-locking' instead.
150506 10:32:21 [Note] Plugin 'FEDERATED' is disabled.
150506 10:32:21  InnoDB: Started; log sequence number 0 44253
150506 10:32:21 [Note] Event Scheduler: Loaded 0 events
150506 10:32:21 [Note] mysql\bin\mysqld: ready for connections.
Version: '5.1.73-community'  socket: ''  port: 3306  MySQL Community Server (GPL
)