[mythtvnz] Dead... again.

[Steve Holdoway]

On Mon, 07 Jul 2008 17:44:15 +1200
AlanP <alan.p at orcon.net.nz> wrote:

> This time, KnoppMyth reckons that the mysql.hosts table is in the wrong 
> format, and mysqld will not bloody load. Stupid bloody unstable thing. I'm 
> tempted to backup all the recordings to a 1TB drive, install XP and put 
> Media Portal or MCE or something on it.
> 
> Anyone know how to fix this? I tried myisamchk -r on the 
> /var/lib/mysql/mysql/host.MYI file, no joy.
> 

if you make a backup, and try to fix...

cd /var/lib/mysql/mysql
mkdir save
cp *MYI save
myisamchk --force --fast --update-state *MYI

you should have some luck restarting the database server. If you can get mysql up without privileges, then the following will create an empty host table ( mysql 5.0 ):

SET character_set_client = utf8;
CREATE TABLE `host` (
  `Host` char(60) collate utf8_bin NOT NULL default '',
  `Db` char(64) collate utf8_bin NOT NULL default '',
  `Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `References_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Show_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Create_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Alter_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  `Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
  PRIMARY KEY  (`Host`,`Db`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges;  Merged with database privileges';

However, mysql is just not that unreliable - millions of websites out there run on it! There must be a cause for this database corruption, and knoppmyth isn't it. Power? Hardware? Any info in the mysql logs, or the system ones??

Steve
-- 
Steve Holdoway <steve at greengecko.co.nz>