View unanswered posts    View active topics

All times are UTC - 6 hours





Post new topic Reply to topic  [ 3 posts ] 
Print view Previous topic   Next topic  
Author Message
Search for:
PostPosted: Sat Oct 13, 2012 12:24 pm 
Offline
Joined: Tue Mar 28, 2006 8:26 pm
Posts: 804
Location: Minneapolis, MN
The mySQL on my friend's Knoppmyth R5.5 machine won't start, following a power failure.
The machine starts and runs, but since mySQL isn't running, mythtv can't do anything.

Since SSH still works fine, I looked around his machine via SSH.
I can see that mythtv keeps trying to connect to the database (over and over) and fails.
The Mythtv backend log shows:
Code:
Cannot login to database?

Would you like to configure the database connection now? [yes]
[console is not interactive, using default 'yes']
Database host name: [localhost]
[console is not interactive, using default 'localhost']
Should I test connectivity to this host using the ping command? [yes]
[console is not interactive, using default 'yes']
Database non-default port: [0]
[console is not interactive, using default '0']
Database name: [mythconverg]
[console is not interactive, using default 'mythconverg']
Database user name: [mythtv]
[console is not interactive, using default 'mythtv']
Database password: [mythtv]
[console is not interactive, using default 'mythtv']
Unique identifier for this machine (if empty, the local host name will be used)$
[console is not interactive, using default '']


I looked at his syslog file a day or 2 after the power failure.
It looks like power died around 3pm on Oct.3. When power came back at 5pm (17:04 hours), it booted and mysql detected the crash:

Code:
Oct  3 17:04:11 mythtv mysqld_safe[3262]: started
Oct  3 17:04:11 mythtv mysqld[3265]: 121003 17:04:11  InnoDB: Database was not shut down normally!
Oct  3 17:04:11 mythtv mysqld[3265]: InnoDB: Starting crash recovery.
Oct  3 17:04:11 mythtv mysqld[3265]: InnoDB: Reading tablespace information from the .ibd files...
Oct  3 17:04:11 mythtv mysqld[3265]: InnoDB: Restoring possible half-written data pages from the doublewrite
Oct  3 17:04:11 mythtv mysqld[3265]: InnoDB: buffer...
Oct  3 17:04:11 mythtv mysqld[3265]: 121003 17:04:11  InnoDB: Starting log scan based on checkpoint at
Oct  3 17:04:11 mythtv mysqld[3265]: InnoDB: log sequence number 0 111426.
Oct  3 17:04:11 mythtv mysqld[3265]: InnoDB: Doing recovery: scanned up to log sequence number 0 111426
Oct  3 17:04:11 mythtv mysqld[3265]: 121003 17:04:11  InnoDB: Started; log sequence number 0 111426
Oct  3 17:04:11 mythtv mysqld[3265]: 121003 17:04:11 [Note] /usr/sbin/mysqld: ready for connections.
Oct  3 17:04:11 mythtv mysqld[3265]: Version: '5.0.32-Debian_7etch1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Debian etch distribution
Oct  3 17:04:12 mythtv /etc/mysql/debian-start[3302]: Upgrading MySQL tables if necessary.
Oct  3 17:04:12 mythtv ntpdate[2803]: step time server 64.73.32.134 offset 0.232328 sec
Oct  3 17:04:13 mythtv /etc/mysql/debian-start[3348]: Checking for crashed MySQL tables.


My friend manually restarted around noon on Oct.4 and mysql compained some more:
Code:
Oct  4 12:23:01 mythtv mysqld_safe[3232]: started
Oct  4 12:23:01 mythtv mysqld[3235]: 121004 12:23:01  InnoDB: Database was not shut down normally!
Oct  4 12:23:01 mythtv mysqld[3235]: InnoDB: Starting crash recovery.
Oct  4 12:23:01 mythtv mysqld[3235]: InnoDB: Reading tablespace information from the .ibd files...
Oct  4 12:23:01 mythtv mysqld[3235]: InnoDB: Restoring possible half-written data pages from the doublewrite
Oct  4 12:23:01 mythtv mysqld[3235]: InnoDB: buffer...
Oct  4 12:23:01 mythtv mysqld[3235]: 121004 12:23:01  InnoDB: Starting log scan based on checkpoint at
Oct  4 12:23:01 mythtv mysqld[3235]: InnoDB: log sequence number 0 111426.
Oct  4 12:23:01 mythtv mysqld[3235]: InnoDB: Doing recovery: scanned up to log sequence number 0 111426
Oct  4 12:23:01 mythtv mysqld[3235]: 121004 12:23:01  InnoDB: Started; log sequence number 0 111426
Oct  4 12:23:01 mythtv mysqld[3235]: 121004 12:23:01 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'db'
Oct  4 12:23:01 mythtv mysqld_safe[3246]: ended
Oct  4 12:23:05 mythtv ntpdate[2771]: step time server 38.229.71.1 offset -0.019226 sec
Oct  4 12:23:07 mythtv kernel: eth0: no IPv6 routers present
Oct  4 12:23:13 mythtv kernel: Clocksource tsc unstable (delta = 3917961016 ns)
Oct  4 12:23:15 mythtv /etc/init.d/mysql[3381]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Oct  4 12:23:15 mythtv /etc/init.d/mysql[3381]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed
Oct  4 12:23:15 mythtv /etc/init.d/mysql[3381]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Oct  4 12:23:15 mythtv /etc/init.d/mysql[3381]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Oct  4 12:23:15 mythtv /etc/init.d/mysql[3381]:


With the thought that his mythconverg database files might be corrupted, I did try and repair the mySQL tables following this forum post:
viewtopic.php?f=21&t=20885

1. /etc/init.d/mythtv-backend stop
2. /etc/init.d/mysql stop
3. cd /var/lib/mysql/mythconverg
4. myisamchk -f -r *.MYI
During this part, it says it's fixing database records. Then it finishes.
5. /etc/init.d/mysql start
At this point, I always get the "Starting MySQL database server: mysqld. . . . . . . . . . . failed!" message.

When I compare my friend's database file folder (/var/lib/mysql/mythconverg) with the same folder in my still-functioning R5.5 machine, it looks like all the database files are there.

I found 2 posts from Ubuntu users with the exact same symptoms. In both cases, the users reinstalled mySQL - they never figured out how to replace the 1 or 2 needed files, etc.
http://askubuntu.com/questions/139859/m ... not-launch
http://www.edugeek.net/forums/nix/97829 ... aches.html

Would it be easy to reinstall the mySQL program files on my friends R5.5 machine?
Or would it be easy to copy the mySQL program files from my R5.5 machine over to his via SSH?

I see that R5.5 has mySQL version 5.0.32.

Can I use apt-get to remove and reinstall mysql-server-5.0?
I see when I do a test, that the current Debian stable version is 5.0.51z-24.

Code:
root@mythtv:/var/log/mythtv# apt-get install mysql-server-5.0 -s
Reading package lists... Done
Building dependency tree... Done
The following extra packages will be installed:
  libmysqlclient15off mysql-client-5.0 mysql-common
Suggested packages:
  tinyca
Recommended packages:
  mailx libhtml-template-perl
The following packages will be upgraded:
  libmysqlclient15off mysql-client-5.0 mysql-common mysql-server-5.0
4 upgraded, 0 newly installed, 0 to remove and 882 not upgraded.
Inst mysql-common [5.0.32-7etch1] (5.0.51a-24 Debian:5.0.1/stable)
Inst libmysqlclient15off [5.0.32-7etch1] (5.0.51a-24 Debian:5.0.1/stable)
Inst mysql-client-5.0 [5.0.32-7etch1] (5.0.51a-24 Debian:5.0.1/stable)
Conf mysql-common (5.0.51a-24 Debian:5.0.1/stable)
Inst mysql-server-5.0 [5.0.32-7etch1] (5.0.51a-24 Debian:5.0.1/stable)
Conf libmysqlclient15off (5.0.51a-24 Debian:5.0.1/stable)
Conf mysql-client-5.0 (5.0.51a-24 Debian:5.0.1/stable)
Conf mysql-server-5.0 (5.0.51a-24 Debian:5.0.1/stable)


I think I need to change the /etc/apt/sources.list to point to an archive source?
It looks like the "sid" and "stable" repositories from 2008-2009 are gone?

Code:
root@mythtv:/etc/mythtv# apt-get update
Ign http://ftp.debian.org stable Release.gpg
Ign http://debian.slimdevices.com unstable Release.gpg
Ign http://ftp.debian.org stable Release
Hit http://debian.slimdevices.com unstable Release
Ign http://ftp.debian.org stable/main Packages/DiffIndex
Get:1 http://www.debian-multimedia.org sid Release.gpg [198B]
Ign http://debian.slimdevices.com unstable/main Packages/DiffIndex
Ign http://ftp.debian.org stable/contrib Packages/DiffIndex
Ign http://ftp.debian.org stable/non-free Packages/DiffIndex
Ign http://ftp.debian.org stable/main Sources/DiffIndex
Ign http://ftp.debian.org stable/contrib Sources/DiffIndex
Ign http://ftp.debian.org stable/non-free Sources/DiffIndex
Get:2 http://www.debian-multimedia.org sid Release [32.6kB]
Ign http://debian.slimdevices.com unstable/main Packages
Ign http://ftp.debian.org stable/main Packages
Hit http://debian.slimdevices.com unstable/main Packages
Ign http://ftp.debian.org stable/contrib Packages
Ign http://ftp.debian.org stable/non-free Packages
Ign http://ftp.debian.org stable/main Sources
Ign http://ftp.debian.org stable/contrib Sources
Ign http://ftp.debian.org stable/non-free Sources
Get:3 http://www.debian-multimedia.org sid/main Packages/DiffIndex [2023B]
Err http://ftp.debian.org stable/main Packages
  404 Not Found [IP: 130.89.148.12 80]
Err http://ftp.debian.org stable/contrib Packages
  404 Not Found [IP: 130.89.148.12 80]
Err http://ftp.debian.org stable/non-free Packages
  404 Not Found [IP: 130.89.148.12 80]
Err http://ftp.debian.org stable/main Sources
  404 Not Found [IP: 130.89.148.12 80]
Get:4 http://www.debian-multimedia.org sid/main Sources/DiffIndex [2023B]
Err http://ftp.debian.org stable/contrib Sources
  404 Not Found [IP: 130.89.148.12 80]
Get:5 http://www.debian-multimedia.org sid/main 2012-10-20-1954.25.pdiff [31B]
Get:6 http://www.debian-multimedia.org sid/main 2012-10-20-1954.25.pdiff [31B]
Err http://ftp.debian.org stable/non-free Sources
  404 Not Found [IP: 130.89.148.12 80]
Get:7 http://www.debian-multimedia.org sid/main 2012-10-20-1954.25.pdiff [31B]
Get:8 http://www.debian-multimedia.org sid/main 2012-10-20-1950.28.pdiff [42B]
Get:9 http://www.debian-multimedia.org sid/main 2012-10-20-1950.28.pdiff [42B]
Get:10 http://www.debian-multimedia.org sid/main 2012-10-20-1950.28.pdiff [42B]
Get:11 http://www.debian-multimedia.org sid/main 2012-10-20-2347.08.pdiff [558B]
Get:12 http://www.debian-multimedia.org sid/main 2012-10-20-2347.08.pdiff [558B]
Get:13 http://www.debian-multimedia.org sid/main 2012-10-20-2347.08.pdiff [558B]
Get:14 http://www.debian-multimedia.org sid/main 2012-10-20-1954.25.pdiff [31B]
Get:15 http://www.debian-multimedia.org sid/main 2012-10-20-1954.25.pdiff [31B]
Get:16 http://www.debian-multimedia.org sid/main 2012-10-20-1954.25.pdiff [31B]
Get:17 http://www.debian-multimedia.org sid/main 2012-10-20-2347.08.pdiff [604B]
Get:18 http://www.debian-multimedia.org sid/main 2012-10-20-2347.08.pdiff [604B]
Get:19 http://www.debian-multimedia.org sid/main 2012-10-20-2347.08.pdiff [604B]
Fetched 38.1kB in 2s (17.7kB/s)
Failed to fetch http://ftp.debian.org/dists/stable/main/binary-i386/Packages.gz  404 Not Found [IP: 130.89.148.12 80]
Failed to fetch http://ftp.debian.org/dists/stable/contrib/binary-i386/Packages.gz  404 Not Found [IP: 130.89.148.12 80]
Failed to fetch http://ftp.debian.org/dists/stable/non-free/binary-i386/Packages.gz  404 Not Found [IP: 130.89.148.12 80]
Failed to fetch http://ftp.debian.org/dists/stable/main/source/Sources.gz  404 Not Found [IP: 130.89.148.12 80]
Failed to fetch http://ftp.debian.org/dists/stable/contrib/source/Sources.gz  404 Not Found [IP: 130.89.148.12 80]
Failed to fetch http://ftp.debian.org/dists/stable/non-free/source/Sources.gz  404 Not Found [IP: 130.89.148.12 80]
Reading package lists... Done
E: Some index files failed to download, they have been ignored, or old ones used instead.

...No response from forum members :(

edit 10-28-2012

I searched for and found some archive repositories at: http://archive.debian.org/debian/dists/

I think Knoppmyth R5.5 was created mainly from the Debian etch branch, so I think I can pull "vintage 2008" apt-get files from:
http://archive.debian.org/debian/dists/etch/

I just inserted the following lines in my /etc/apt/sources.list:
Code:
deb http://archive.debian.org/debian/ etch main
deb-src http://archive.debian.org/debian/ etch main

And I've been able to successfully have the "apt-get update" command talk to it and get an update list of packages!

In a VMware virtual R5.5 virtual machine, the next step is to test and see if I can replace mysql-server-5.0 from the apt-get repository and still have a working KM virtual machine?

update:

I have been able to fetch mysql-server-5.0 from the etch archive, but it won't finish installing...no idea why.
I've tried several times.

FIRST TIME:
Code:
root@mythtv:~# apt-get install mysql-server-5.0
Reading package lists... Done
Building dependency tree... Done
The following extra packages will be installed:
  libmysqlclient15off mysql-client-5.0 mysql-common
Suggested packages:
  tinyca
Recommended packages:
  mailx
The following packages will be upgraded:
  libmysqlclient15off mysql-client-5.0 mysql-common mysql-server-5.0
4 upgraded, 0 newly installed, 0 to remove and 122 not upgraded.
Need to get 34.4MB of archives.
After unpacking 311kB disk space will be freed.
Do you want to continue [Y/n]? y
Get:1 http://archive.debian.org etch/main mysql-common 5.0.32-7etch12 [55.9kB]
Get:2 http://archive.debian.org etch/main libmysqlclient15off 5.0.32-7etch12 [1795kB]
Get:3 http://archive.debian.org etch/main mysql-client-5.0 5.0.32-7etch12 [7194kB]
Get:4 http://archive.debian.org etch/main mysql-server-5.0 5.0.32-7etch12 [25.4MB]
Fetched 34.4MB in 1m59s (288kB/s)
Preconfiguring packages ...
(Reading database ... 92282 files and directories currently installed.)
Preparing to replace mysql-common 5.0.32-7etch1 (using .../mysql-common_5.0.32-7etch12_all.deb) ...
Unpacking replacement mysql-common ...
Preparing to replace libmysqlclient15off 5.0.32-7etch1 (using .../libmysqlclient15off_5.0.32-7etch12_i386.deb) ...
Unpacking replacement libmysqlclient15off ...
Preparing to replace mysql-client-5.0 5.0.32-7etch1 (using .../mysql-client-5.0_5.0.32-7etch12_i386.deb) ...
Unpacking replacement mysql-client-5.0 ...
Setting up mysql-common (5.0.32-7etch12) ...
(Reading database ... 92282 files and directories currently installed.)
Preparing to replace mysql-server-5.0 5.0.32-7etch1 (using .../mysql-server-5.0_5.0.32-7etch12_i386.deb) ...
Stopping MySQL database server: mysqld.
Stopping MySQL database server: mysqld.
Unpacking replacement mysql-server-5.0 ...
Setting up libmysqlclient15off (5.0.32-7etch12) ...
Setting up mysql-client-5.0 (5.0.32-7etch12) ...
Setting up mysql-server-5.0 (5.0.32-7etch12) ...
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing mysql-server-5.0 (--configure):
 subprocess post-installation script returned error exit status 1
Errors were encountered while processing:
 mysql-server-5.0
E: Sub-process /usr/bin/dpkg returned an error code (1)
root@mythtv:~#


MOST RECENT TIME:
Code:
root@mythtv:/var/lib/mysql/mythconverg# apt-get install mysql-server-5.0
Reading package lists... Done
Building dependency tree... Done
mysql-server-5.0 is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 122 not upgraded.
2 not fully installed or removed.
Need to get 0B of archives.
After unpacking 0B of additional disk space will be used.
Setting up mysql-server-5.0 (5.0.32-7etch12) ...
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
invoke-rc.d: initscript mysql, action "start" failed.
dpkg: error processing mysql-server-5.0 (--configure):
 subprocess post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-5.0; however:
  Package mysql-server-5.0 is not configured yet.
dpkg: error processing mysql-server (--configure):
 dependency problems - leaving unconfigured
Errors were encountered while processing:
 mysql-server-5.0
 mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)
root@mythtv:/var/lib/mysql/mythconverg#


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 Sun Nov 04, 2012 3:25 pm, edited 1 time in total.


Top
 Profile  
 
PostPosted: Sun Nov 04, 2012 3:22 pm 
Offline
Joined: Tue Mar 28, 2006 8:26 pm
Posts: 804
Location: Minneapolis, MN
It's fixed!

I was NOT able to extract any .deb files from the big KNOPPIX file. (It would still be good to know how Cecil does it.)
I was able to connect to an archived repository from 2008 and pull the same version of the mysql-server-5.0.
Unfortunately the dpkg process did NOT fully complete, so I wasn't able to reinstall mysql-server-5.0.
It turns out that reinstalling mysql-servre-5.0 would not have helped in this case, anyway.

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:13 pm, edited 1 time in total.


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


Top
 Profile  
 

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 


All times are UTC - 6 hours




Who is online

Users browsing this forum: No registered users and 10 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:  
Powered by phpBB® Forum Software © phpBB Group

Theme Created By ceyhansuyu