LinHES Forums
http://forums.linhes.org/

Managed to kill my R6 db. Is it unrepairable?
http://forums.linhes.org/viewtopic.php?f=6&t=20253		 Page 1 of 1
Author:  tophee [ Fri Aug 21, 2009 9:10 am ]
Post subject:  Managed to kill my R6 db. Is it unrepairable?
Hi,
Was trying to get vdpau working, and then I noticed MythWelcome wasn't working so tried to fix that. After turning it off and MythWelcome not appearing to do anything, I powered back on and instead of getting back into myth, I arrived at the initial myth setup page, where I was asked my local host name: localhost, and then after clicking next I was told I could not connect to the db.

I tried to run optimize_mythdb.py (which appears to have replaces optimize_db.sh) and it errored, I manually corrected a number of *.MYI files, though one file (recordedseek.MYI) was objecting to be fixed.
I tried optimize_mythdb.pl again and got this error.
Code:
optimize_mythdb.py
/usr/lib/python2.6/site-packages/MySQLdb/__init__.py:34: DeprecationWarning: the sets module is deprecated
  from sets import ImmutableSet
Traceback (most recent call last):
  File "/usr/LH/bin/optimize_mythdb.py", line 6, in <module>
    db = MySQLdb.connect(host="localhost", user="mythtv", passwd="mythtv", db="mythconverg")
  File "/usr/lib/python2.6/site-packages/MySQLdb/__init__.py", line 74, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.6/site-packages/MySQLdb/connections.py", line 170, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2002, "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)")


Any thoughts? Preferably in the next hour!

Regards Chris
Author:  tophee [ Fri Aug 21, 2009 9:16 am ]
Post subject: 
In addition, the info from manually trying to fix the db yields this:
Code:
[root@mythtv mythconverg]# myisamchk -r recordedseek.MYI
Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200
Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295
- recovering (with sort) MyISAM-table 'recordedseek.MYI'
Data records: 4418528
myisamchk: error: Can't create new tempfile: 'recordedseek.TMD'
MyISAM-table 'recordedseek.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
[root@mythtv mythconverg]#
Author:  tophee [ Fri Aug 21, 2009 9:37 am ]
Post subject: 
While there does not appear to be anything recent in my mythbackend.log that is of any use, there is in my mythfrontend.log.

Here are some extracts:
cat: /usr/local/share/mythtv/.releasetype: No such file or directory
/usr/lib/python2.6/site-packages/MySQLdb/__init__.py:34: DeprecationWarning: the sets module is deprecated
from sets import ImmutableSet
______Start of hostype config_____
Stand alone system being configured
Not changing ntp configuration
Checking mysql for install
mysql is already installed, will not install
Checking mythdb-initial for install
mythdb-initial is already installed, will not install
Checking avahi for install
avahi is already installed, will not install
Checking portmap for install
portmap is already installed, will not install
Checking nfs-utils for install
nfs-utils is already installed, will not install
Checking local-website for install
local-website is already installed, will not install
Checking myth2ipod for install
myth2ipod is already installed, will not install
Checking mythtv-status for install
Installing mythtv-status
Adding service mysql
Adding service mythbackend
Adding service avahi
Adding service portmap
Adding service nfs-utils
Adding service netfs
Adding service lighttpd
Adding service frontend
Adding service hal
Restarting service mysqld
Configuring database connection
__End of hostype config

____Start of IR____
Adding service lircd
Configuring receiver Hauppauge
Removing service igdaemon
Removing service tatir
------NEED TO SETUP CHANGE_CHANNEL------
HostBlasterType : None
Triggering udev
Disabling LCD support
Removing service lcdd
__End IR

systemconfig.py exit code 0
2009-08-21 15:10:28.504 Deleting UPnP client...
2009-08-21 15:10:34.890 New DB connection, total: 2
2009-08-21 15:10:34.890 Connected to database 'mythconverg' at host: localhost
2009-08-21 15:10:34.891 mythfrontend version: 0.21.20080304-1 www.mythtv.org
2009-08-21 15:10:34.891 Enabled verbose msgs: important general
Xlib: extension "Generic Event Extension" missing on display ":0.0".
Xlib: extension "Generic Event Extension" missing on display ":0.0".
2009-08-21 15:10:35.070 No theme dir: /root/.mythtv/themes/Retro
Xlib: extension "Generic Event Extension" missing on display ":0.0".
2009-08-21 15:10:35.071 Primary screen 0.
2009-08-21 15:10:35.071 Using screen 0, 1920x1080 at 0,0
2009-08-21 15:10:35.072 No theme dir: /root/.mythtv/themes/Retro
2009-08-21 15:10:35.072 Switching to square mode (Retro)
Xlib: extension "Generic Event Extension" missing on display ":0.0".
Xlib: extension "Generic Event Extension" missing on display ":0.0".
2009-08-21 15:10:35.082 Using the OpenGL painter
2009-08-21 15:10:35.083 JoystickMenuClient Error: Joystick disabled - Failed to read /root/.mythtv/joystickmenurc
mythtv: could not open config file /root/.lircrc
mythtv: No such file or directory
2009-08-21 15:10:35.083 Failed to read lirc config /root/.lircrc for mythtv
Xlib: extension "Generic Event Extension" missing on display ":0.0".
2009-08-21 15:10:35.696 Loading from: /usr/share/mythtv/themes/Retro/base.xml
2009-08-21 15:10:35.735 Loading from: /usr/share/mythtv/themes/default/base.xml
2009-08-21 15:10:35.764 Registering Internal as a media playback plugin.
2009-08-21 15:10:35.850 MonitorRegisterExtensions(0x100, gif,jpg,png)
2009-08-21 15:10:35.871 Using NV NPOT texture extension
2009-08-21 15:10:35.957 MythMusic adding CD-Writer: 1,0,0 -- DVDRAM GSA-H10A
2009-08-21 15:10:35.977 Key > is bound to multiple actions in context Music.
2009-08-21 15:10:35.981 MonitorRegisterExtensions(0x40, ogg,mp3,aac,flac)
Failed to bind for SIP connection 192.168.1.7
SIP listening on IP Address :5060 NAT address
SIP: Cannot register; proxy, username or password not set
Destroying SipFsm object
2009-08-21 15:10:36.028 Got error 28 from storage engine QMYSQL3: Unable to execute query
2009-08-21 15:10:36.028 SourceManager: NeedSourceFor: Unable to find source for 2, L1769, 1
QIntDict: Cannot insert null item
2009-08-21 15:10:36.028 SourceManager: NeedSourceFor: Unable to find source for 3, L1769, 1
QIntDict: Cannot insert null item
2009-08-21 15:10:36.028 SourceManager: NeedSourceFor: Unable to find source for 2, L1769, 0
QIntDict: Cannot insert null item
2009-08-21 15:10:36.028 SourceManager: NeedSourceFor: Unable to find source for 3, L1769, 0
QIntDict: Cannot insert null item
2009-08-21 15:10:36.030 Connecting to backend server: 127.0.0.1:6543 (try 1 of 5)
2009-08-21 15:10:36.031 Using protocol version 40
2009-08-21 15:10:36.032 No theme dir: /root/.mythtv/themes/Retro


Xlib: extension "Generic Event Extension" missing on display ":0.0".
2009-08-21 15:10:35.082 Using the OpenGL painter
2009-08-21 15:10:35.083 JoystickMenuClient Error: Joystick disabled - Failed to read /root/.mythtv/joystickmenurc
mythtv: could not open config file /root/.lircrc
mythtv: No such file or directory
2009-08-21 15:10:35.083 Failed to read lirc config /root/.lircrc for mythtv
Xlib: extension "Generic Event Extension" missing on display ":0.0".
2009-08-21 15:10:35.696 Loading from: /usr/share/mythtv/themes/Retro/base.xml
2009-08-21 15:10:35.735 Loading from: /usr/share/mythtv/themes/default/base.xml
2009-08-21 15:10:35.764 Registering Internal as a media playback plugin.
2009-08-21 15:10:35.850 MonitorRegisterExtensions(0x100, gif,jpg,png)
2009-08-21 15:10:35.871 Using NV NPOT texture extension
2009-08-21 15:10:35.957 MythMusic adding CD-Writer: 1,0,0 -- DVDRAM GSA-H10A
2009-08-21 15:10:35.977 Key > is bound to multiple actions in context Music.
2009-08-21 15:10:35.981 MonitorRegisterExtensions(0x40, ogg,mp3,aac,flac)
Failed to bind for SIP connection 192.168.1.7
SIP listening on IP Address :5060 NAT address
SIP: Cannot register; proxy, username or password not set
Destroying SipFsm object
2009-08-21 15:10:36.028 Got error 28 from storage engine QMYSQL3: Unable to execute query
2009-08-21 15:10:36.028 SourceManager: NeedSourceFor: Unable to find source for 2, L1769, 1
QIntDict: Cannot insert null item
2009-08-21 15:10:36.028 SourceManager: NeedSourceFor: Unable to find source for 3, L1769, 1
QIntDict: Cannot insert null item
2009-08-21 15:10:36.028 SourceManager: NeedSourceFor: Unable to find source for 2, L1769, 0
QIntDict: Cannot insert null item
2009-08-21 15:10:36.028 SourceManager: NeedSourceFor: Unable to find source for 3, L1769, 0
QIntDict: Cannot insert null item
2009-08-21 15:10:36.030 Connecting to backend server: 127.0.0.1:6543 (try 1 of 5)
2009-08-21 15:10:36.031 Using protocol version 40
2009-08-21 15:10:36.032 No theme dir: /root/.mythtv/themes/Retro
Author:  tophee [ Fri Aug 21, 2009 2:05 pm ]
Post subject: 
No one?
Looks like it's a fresh install for me. Oh well, there was tonnes of stuff I'd never get around to watching.

If anyone has a suggestion, I'll be doing it tomorrow morning.
Author:  goofee [ Fri Aug 21, 2009 4:48 pm ]
Post subject: 
I'll take a stab at it. No promises.

I'm thinking mysql won't start because the recordedseek table has an issue. If you erase(or move) the recordedseek.MYD, recordedseek.MYI, and recordedseek.frm files from /var/lib/mysql/mythconverg/ then mysql should start without the recordedseek table. If you have a good recent back up you should then be able to restore. If not you can recreate the table. Here's a snip from my backup. (it's R5.5 if that matters)
Code:
DROP TABLE IF EXISTS `recordedseek`;

CREATE TABLE `recordedseek` (
  `chanid` int(10) unsigned NOT NULL default '0',
  `starttime` datetime NOT NULL default '0000-00-00 00:00:00',
  `mark` mediumint(8) unsigned NOT NULL default '0',
  `offset` bigint(20) unsigned NOT NULL,
  `type` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`chanid`,`starttime`,`type`,`mark`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

From there you can use
Code:
mythcommflag --rebuild
to rebuild the seek table data.

Good luck.

Not sure what this one means though. Drive full? Permissions?
Quote:
myisamchk: error: Can't create new tempfile: 'recordedseek.TMD'
Author:  tjc [ Fri Aug 21, 2009 8:53 pm ]
Post subject: 
Well taking into account the time difference most of the US folks were still at work while you were posting, ... That probably explains why it took ~7 hours for the first response.

The Disaster Recovery pages on the wiki have pointers on how to restore your DB in a case like this. Much of that was taken from posts that Cecil, myself and others had made on the topic.

For example see the last half of this page for how to get the system to recognize your recordings again:
http://www.knoppmythwiki.org/index.php?page=RepairingMythConvergDB

Good luck!
Author:  tophee [ Sat Aug 22, 2009 1:32 am ]
Post subject: 
Hi,
Thanks for posting back.

tjc, thank you for a bit of perspective. I was getting a little desperate. :oops: I had to be somewhere yesterday evening and I'd already spent a few hours trying to fix it.

As it happens, I had been using the disaster recovery page to try and repair the db. Some of the standard tools found in 5.5 don't appear to be there such as
Code:
myth.rebuilddatabase.pl --try_default


I suppose that's what you get for running a beta.

Goofee - I tried renaming the relevant files as suggested... still no go.

I'm also getting this error from the frontend that reports when restarting the computer "No Unphp backends found ".

I have a backup, but unfortunately it's not that recent.

I think I'll go with that and see from there.
Author:  cahlfors [ Sat Aug 22, 2009 7:02 am ]
Post subject: 
From your second post:
Quote:
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

Did this not help? I've found that such little suggestions that show up in the log files are invaluable - you just have to find them!

It also speaks of not being able to create a file. Could there be a permissions problem or full disk?

Also, if you need to reinstall - backup the recordings directory. There is a utility here somewhere which can recover such things. I don't know how much manual work is involved, though. There could be significant difficulty in finding descriptive names for the recordings and you may end up with the task of viewing everything and renaming from the menu. For better or worse...

Cheers and crossed fingers,
/Chris
Author:  tophee [ Sat Aug 22, 2009 2:44 pm ]
Post subject: 
Hi Chris,

I did try using the -o and -f options but no joy. The disks weren't full either.

So I gave up and reinstalled.

I initially used my last backup. As it turns out it was 3 months old. (How did that happen?) ... Perhaps there should be an automated backup that runs once week so people are never without a recent backup from a cronjob.

Anyway, after that I decided there was nothing there that I would miss particularly. And most things get repeated eventually.

Not sure what I did to corrupt something, but I messed it up big time.

Regards Chris
Author:  cahlfors [ Sun Aug 23, 2009 6:26 am ]
Post subject: 
Quote:
Perhaps there should be an automated backup that runs once week so people are never without a recent backup from a cronjob.


Here's mine:
Code:
cat backupmysql.sh
#!/bin/sh
#Dumps the mythconverg database - daily backup
#Keeps the last 7 days
DAY=($(/bin/date +%u))
DUMPFILE="mythbe_$DAY.sql"
nice -n 19 /usr/bin/mysqldump -u mythtv -pmythtv mythconverg -c > /myth/backup/$DUMPFILE
cp /myth/backup/$DUMPFILE /mnt/NAPOLEON/backup/Ludde
exit 0

Crontab:
Code:
57 4 * * *   root   /etc/cron.daily/backupmysql.sh >>/var/log/mythtv/backupmysql.log 2>&1

The cp statement copies the dump file to my file server, which is properly backed up with copies stored in two locations - house + garage. Change to suit your needs.

The dump is NOT failsafe, since it dumps while online (thus not disturbing any recordings in progress). A proper backup should always be performed offline but, as you know, the best backup is the one that gets done. I've never had any problems with these, although I've only needed them once. I've had database errors like yours, but always managed to sort them out by following the suggestions in the logs. I've also deleted an occasional record that doesn't seem right with Webmin, an extremely dangerous but easy to use tool. Knowing that I have these online dumps if all else fails has been a great comfort in daring to deal with the problems (I'm a database illiterate).

Cheers,
/Chris
Author:  tjc [ Sun Aug 23, 2009 10:30 am ]
Post subject: 
There are several excellent threads about setting up automated backups, and a number of scripts and utilities that have been written to help. For more information I'd suggest searching for "idle.sh" which is a script I wrote to check if it's safe to do a backup (no conflicting recordings or the like) and lets you delay the backup as needed.

You'll find a lot a references related db optimization, but substituting mythbackup for optimize_db.sh is the main difference.

Here's one that shows a simplistic approach:
http://knoppmyth.net/phpBB2/viewtopic.php?t=18000

OBTW - This thread points to idle.sh if you don't already have it on your system. If not be sure to get the most recent version from the wiki:
http://knoppmyth.net/phpBB2/viewtopic.php?t=17988
Page 1 of 1 All times are UTC - 6 hours
Powered by phpBB® Forum Software © phpBB Group
http://www.phpbb.com/