View unanswered posts    View active topics

All times are UTC - 6 hours





Post new topic Reply to topic  [ 17 posts ] 
Go to page Previous  1, 2

Print view Previous topic   Next topic  
Author Message
Search for:
 Post subject: Re: any mySQL experts?
PostPosted: Sun Nov 04, 2012 3:20 pm 
Offline
Joined: Tue Mar 28, 2006 8:26 pm
Posts: 804
Location: Minneapolis, MN
Good thought - disk space was the first thing I checked - 68% free on the root partition...not the problem.

The good news is, IT'S FIXED!

The bad news is, I was NOT able to extract any .deb files from the big KNOPPIX file.
The good news is, I was able to connect to an archived repository from 2008 and pull the same version of the mysql-server-5.0.
The bad news is, the dpkg process did not fully complete, so I wasn't able to reinstall mysql-server-5.0.

I did finally get hold of my friend Brad to look at this with me. He was able to get mysql started and forced a rebuild of the damaged database.
He looked at the error messages and saw that the "db" table (part of the "mysql" database) was the corrupted one - not the mythtv "mythconverg" database.

Here is Brad's description of what he had to do to fix the database problems.
Quote:
It was fairly easy to fix once the exact problem was understood.

I didn't cut any log or error snippets, but the key error message ended up being:
Code:
[ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'db'


mySQL stores data on disk organized fairly logically - /var/lib/mysql is the base directory, and sub-directories are different databases, with the file groups in those directories being individual table information (generally).

The mySQL privilege information determines what users have access to which data, and is stored in the "mysql" database in the "db" table. The confusing part was that the error was complaining about an incorrect file format ('db'), which led me to think that somehow the format of a table was changed to something inappropriate.

However, what the error actually is trying to say is "table 'db' has an incorrect file format" - in other words, it's corrupted.

So, I started the mySQL server on a separate VTTY in a mode that essentially bypassed the privilege/user mechanism (and related tables):
Code:
mysqld --skip-grant &


From there, I could start the mySQL command line console on another VTTY. Because the privilege/user mechanism had been bypassed, I checked to see that the user table was OK by manually inserting a new user.

From the mysql command line console, first
Code:
use user;
then
Code:
insert into user values('localhost','maint','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0);
verified that mySQL and the user table was functioning as expected.

However an error similar to the original error above appeared on the VTTY that mysqld was launched from, as well as a warning indicated on command execution within the command line interface (but no specifics).

From the command line console,
Code:
repair table db;
showed that it was damaged/corrupt, and normal repair methods weren't going to work.

Although this is generally used as a last resort, I opted to take a more blunt approach and force it to try and recreate the MYI information from the FRM file -
Code:
repair table db use_frm;


Ultimately, that is what brought some level of functionality back. Although, deleting the "db" table and restoring/recreating it would have allowed things to start again, it would likely have smashed the MythTV functionality and required a lot more forensic rebuilding than I had time or patience to do.

All is working again on the Knoppmyth R5.5 machine. In the end, it was a corrupted mysql database file! The trick was getting mysql to start up in a broken state!

I hope this helps someone else!
Eric

_________________
KnoppMyth R5.5, Asus A8N-VM CSM (nvidia 6150 onboard video), AMD Athlon 64 dual-core 4200+, two 1GB sticks DDR 400, HD-3000 HDTV card, PVR-150 card, Iguanaworks RS-232 IR receiver/transmitter, Pioneer DVR-110 DVD burner


Last edited by neutron68 on Tue Nov 13, 2012 10:12 pm, edited 1 time in total.


Top
 Profile  
 
PostPosted: Sun Nov 04, 2012 9:08 pm 
Offline
Joined: Mon Dec 24, 2007 9:47 am
Posts: 535
Location: Ottawa, Canada
Thanks for posting the follow up! no doubt it will save someone else in the future.


Top
 Profile  
 

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 17 posts ] 
Go to page Previous  1, 2



All times are UTC - 6 hours




Who is online

Users browsing this forum: No registered users and 31 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group

Theme Created By ceyhansuyu