Author Topic: MySQL Database broken - how can I fix it ?  (Read 1248 times)

Viking

  • Addicted
  • *
  • Posts: 521
    • View Profile
MySQL Database broken - how can I fix it ?
« on: June 08, 2009, 09:28:54 pm »
Hi,

had a power failure and as the server came up again the MySQL database was broken.

I assume I just have to drop an INDEX and recreate it, but would need the SQL to do that ...

see "index PRIMARY of table pluto_main/DesignObjVariation_DesignObjParameter" i log below :

Code: [Select]
Jun  8 21:22:20 dcerouter mysqld_safe[8734]: started
Jun  8 21:22:20 dcerouter mysqld[8737]: 090608 21:22:20  InnoDB: Started; log sequence number 0 354105811
Jun  8 21:22:20 dcerouter mysqld[8737]: 090608 21:22:20 [Warning] 'user' entry 'root@lxmce-core' ignored in --skip-name-resolve mode.
Jun  8 21:22:20 dcerouter mysqld[8737]: 090608 21:22:20 [Note] /usr/sbin/mysqld: ready for connections.
Jun  8 21:22:20 dcerouter mysqld[8737]: Version: '5.0.45-Debian_1ubuntu3'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Debian e
tch distribution
Jun  8 21:22:21 dcerouter /etc/mysql/debian-start[8792]: Upgrading MySQL tables if necessary.
Jun  8 21:22:21 dcerouter /etc/mysql/debian-start[8798]: Looking for 'mysql' in: /usr/bin/mysql
Jun  8 21:22:21 dcerouter /etc/mysql/debian-start[8798]: Looking for 'mysqlcheck' in: /usr/bin/mysqlcheck
Jun  8 21:22:21 dcerouter /etc/mysql/debian-start[8798]: This installation of MySQL is already upgraded to 5.0.45, use --force if you still need to run mysql_upgrade
Jun  8 21:22:21 dcerouter /etc/mysql/debian-start[8806]: Checking for insecure root accounts.
Jun  8 21:22:21 dcerouter /etc/mysql/debian-start[8810]: WARNING: mysql.user contains 5 root accounts without password!
Jun  8 21:22:21 dcerouter /etc/mysql/debian-start[8811]: Checking for crashed MySQL tables.
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: Database page corruption on disk or a failed
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: file read of page 348.
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: You may have to recover from a backup.
Jun  8 21:22:22 dcerouter mysqld[8737]: 090608 21:22:22  InnoDB: Page dump in ascii and hex (16384 bytes):
Jun  8 21:22:22 dcerouter mysqld[8737]:  len 16384; hex 4e49ffb5000001 .....
<SNIP>
Jun  8 21:22:22 dcerouter mysqld[8737]:  Package_Source_Compat                           ` K        82    -   ;InnoDB: End of page dump
Jun  8 21:22:22 dcerouter mysqld[8737]: 090608 21:22:22  InnoDB: Page checksum 584375288, prior-to-4.0.14-form checksum 2515821980
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: stored checksum 1313472437, prior-to-4.0.14-form stored checksum 2147483648
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: Page lsn 0 46945312, low 4 bytes of lsn at page end 755044480
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: Page number (if stored to page already) 348,
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: Page may be an index page where index id is 0 974
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: (index PRIMARY of table pluto_main/DesignObjVariation_DesignObjParameter)
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: Database page corruption on disk or a failed
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: file read of page 348.
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: You may have to recover from a backup.
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: It is also possible that your operating
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: system has corrupted its own file cache
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: and rebooting your computer removes the
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: error.
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: If the corrupt page is an index page
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: you can also try to fix the corruption
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: by dumping, dropping, and reimporting
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: the corrupt table. You can use CHECK
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: TABLE to scan your table for corruption.
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: See also InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: about forcing recovery.
Jun  8 21:22:22 dcerouter mysqld[8737]: InnoDB: Ending processing because of a corrupt database page.
Jun  8 21:22:22 dcerouter /etc/mysql/debian-start[8825]: WARNING: mysqlcheck has found corrupt tables
Jun  8 21:22:22 dcerouter /etc/mysql/debian-start[8825]: /usr/bin/mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ...  FAST'
Jun  8 21:22:22 dcerouter /etc/mysql/debian-start[8825]:
Jun  8 21:22:22 dcerouter /etc/mysql/debian-start[8825]:  Improperly closed tables are also reported if clients are accessing
Jun  8 21:22:22 dcerouter /etc/mysql/debian-start[8825]:  the tables *now*. A list of current connections is below.
Jun  8 21:22:22 dcerouter /etc/mysql/debian-start[8825]:
Jun  8 21:22:22 dcerouter mysqld_safe[8828]: Number of processes running now: 0
Jun  8 21:22:22 dcerouter mysqld_safe[8830]: restarted
Jun  8 21:22:22 dcerouter mysqld[8833]: InnoDB: Warning: we did not need to do crash recovery, but log scan
Jun  8 21:22:22 dcerouter mysqld[8833]: InnoDB: progressed past the checkpoint lsn 0 354105811 up to lsn 0 354105821
Jun  8 21:22:22 dcerouter mysqld[8833]: 090608 21:22:22  InnoDB: Started; log sequence number 0 354105811
Jun  8 21:22:22 dcerouter mysqld[8833]: 090608 21:22:22 [Warning] 'user' entry 'root@lxmce-core' ignored in --skip-name-resolve mode.
Jun  8 21:22:22 dcerouter mysqld[8833]: 090608 21:22:22 [Note] /usr/sbin/mysqld: ready for connections.
Jun  8 21:22:22 dcerouter mysqld[8833]: Version: '5.0.45-Debian_1ubuntu3'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Debian etch distribution

Greetings
Viking
« Last Edit: June 09, 2009, 10:29:32 am by Viking »

pigdog

  • NEEDS to work for LinuxMCE
  • ***
  • Posts: 1005
    • View Profile
Re: MySQL Database broken - how can I fix it ?
« Reply #1 on: June 08, 2009, 10:54:28 pm »
Hi Viking,

It's probably not what you want to hear but I think your best bet is to reload.

If a MySQL guru has any other ideas?

Sorry buddy!

colinjones

  • Alumni
  • LinuxMCE God
  • *
  • Posts: 3003
    • View Profile
Re: MySQL Database broken - how can I fix it ?
« Reply #2 on: June 08, 2009, 11:44:27 pm »
Somewhere in the forums there is a post that explains how to drop the entire database, and rebuild it from the installation files (basically importing some schema and db templates used during the initial build) but I can't remember where that post was... perhaps do some searching?

Zaerc

  • Alumni
  • LinuxMCE God
  • *
  • Posts: 2256
  • Department of Redundancy Department.
    • View Profile
Re: MySQL Database broken - how can I fix it ?
« Reply #3 on: June 09, 2009, 01:54:12 am »
You could just try checking the tables and repairing the crashed ones from the mysql prompt like this:
Code: [Select]
CHECK TABLE <tablename>;
REPAIR TABLE <tablename>;
See the (online) mysql documentation for more info.
"Change is inevitable. Progress is optional."
-- Anonymous


Viking

  • Addicted
  • *
  • Posts: 521
    • View Profile
Re: MySQL Database broken - how can I fix it ?
« Reply #4 on: June 09, 2009, 10:28:44 am »
Hi,

thanks for you replys :)

Unfortunatly one can not use "REPAIR TABLE" - at least that is what MySQL tells me when trying. "Note: it is not possible on this type of database" (or was it table - don't remember exactly)


It came down to finding out wht was broken. I did it like this.

1. mysqlcheck --all-databases

tells me what database is broken - in my case it is pluto_main

2. mysqldump pluto_main >/tmp/db_sql_dump.sql

Dumps the sql that is needed to recreate the database. It then stops at the ponit where the DB is broken. Above the end of the log there then is the code to drop and recreate the broken table. It is then empty and data has to be filled again.

In my case these thre tables was broken :

DesignObjVariation_DesignObjParameter
DeviceTemplate_DeviceData
psc_dce_batdet


Does anyone know what is in these three tables ? The question is if makes sence to import just these three from the last "backup" (cold copy of the mysql directory) ? Or should I rather start over with a fresh database ?

Can I find an Database documentation anywhere ?

Greetings
Viking

Example how the drop/recreate part looks :
Code: [Select]
DROP TABLE IF EXISTS `DesignObjVariation_DesignObjParameter`;
CREATE TABLE `DesignObjVariation_DesignObjParameter` (
  `FK_DesignObjVariation` int(11) NOT NULL default '0',
  `FK_DesignObjParameter` int(11) NOT NULL default '0',
  `Value` text,
  `FK_Skin` int(11) default NULL,
  `FK_Language` int(11) default NULL,
  `Ignore` tinyint(1) NOT NULL default '0',
  `psc_id` int(11) default NULL,
  `psc_batch` int(11) default NULL,
  `psc_user` int(11) default NULL,
  `psc_frozen` tinyint(1) default '0',
  `psc_mod` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `psc_restrict` int(11) default NULL,
  PRIMARY KEY  (`FK_DesignObjVariation`,`FK_DesignObjParameter`),
  UNIQUE KEY `psc_id` (`psc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Viking

  • Addicted
  • *
  • Posts: 521
    • View Profile
Re: MySQL Database broken - how can I fix it ?
« Reply #5 on: June 09, 2009, 10:45:44 am »
Hi Colin,

Somewhere in the forums there is a post that explains how to drop the entire database, and rebuild it from the installation files (basically importing some schema and db templates used during the initial build) but I can't remember where that post was... perhaps do some searching?

Only found how to recreate the media Database :
http://forum.linuxmce.org/index.php?topic=6408.0

Would the rest work the same way ?

Greetings
Viking

Viking

  • Addicted
  • *
  • Posts: 521
    • View Profile
Re: MySQL Database broken - how can I fix it ?
« Reply #6 on: June 09, 2009, 01:42:05 pm »
Hi,

I also found this quite old sqlCVS post :
http://forum.linuxmce.org/index.php?topic=2973.msg14725#msg14725

Is that the way of doing it ? I would not like to get an 0810 database ;)

Greetings
Viking

UNi

  • Veteran
  • ***
  • Posts: 53
  • Would you like Cheese with that Whine?
    • View Profile
Re: MySQL Database broken - how can I fix it ?
« Reply #7 on: June 09, 2009, 08:48:06 pm »
I used SQLCVS revert the other day on 710 to restore a corrupted template...and it worked great...but I didn't have a lot to lose because my Core wasn't highly customized.  I did it several times and it worked fine.  Go to the WebAdmin interface, Advanced, SQLCVS, Diff.  YMMV...back up your data.

-UNi
« Last Edit: June 09, 2009, 08:53:20 pm by UNi »

Viking

  • Addicted
  • *
  • Posts: 521
    • View Profile
Re: MySQL Database broken - how can I fix it ?
« Reply #8 on: June 09, 2009, 10:35:45 pm »
Hi Uni,

thanks :) learned something new.
took a look with the web page and looks good - I think the only changes I see there are the ones I have made myself.

Greetings
Viking