Marten, Yeah, my experience has been that InnoDB is great when it's working but a complete nightmare when it stops working. I have scripts to deal with this which I'm actually hoping to release to the public in the near future. Essentially, what you need to do is edit your my.cnf to bring MySQL up with innodb_force_recovery. Then you should run check table on every table (hence the script part of the equation). For the tables that report corrupt, you can try to dump their data out although this never works for me. It always says SELECT failed due to corrupt key index.
Regardless of whether or not you manage to dump the data out of the corrupt tables, drop them, stop mysql. Edit the my.cnf to comment out the innodb_force_recovery statement because you can't write to the database when in recovery mode and restart the server. Hopefully it will actually start now that the corrupt tables are gone. Now you can import your dumps and off you go! Chances are you will need to do most of this with the --socket flag to prevent your application from accessing your database while you are doing this. Make sure you then use the --socket flag on all your dump and import commands or in the connection string in your check script. Hope this helps. On Nov 7, 2007 2:45 AM, Marten Lehmann <[EMAIL PROTECTED]> wrote: > Hello, > > today I got this in my logs and mysql stopped working. > > InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html > InnoDB: how to resolve the issue. > 071107 8:46:26 InnoDB: Flushing modified pages from the buffer pool... > 071107 8:46:26 InnoDB: Started; log sequence number 1 1008136481 > /var/mysql/mysql-4.1.18/libexec/mysqld: ready for connections. > Version: '4.1.18-log' socket: '/tmp/mysql.sock' port: 3306 Source > distribution > InnoDB: Error: page n:o stored in the page read in is 538976288, should be > 1644! > 071107 8:46:28 InnoDB: Error: page 538976288 log sequence number 538976288 > 538976288 > InnoDB: is in the future! Current system log sequence number 1 1008136645. > InnoDB: Your database may be corrupt. > InnoDB: Database page corruption on disk or a failed > InnoDB: file read of page 1644. > InnoDB: You may have to recover from a backup. > 071107 8:46:28 InnoDB: Page dump in ascii and hex (16384 bytes): > len 16384; hex > 2020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020 > 071107 8:46:29 InnoDB: Page checksum 138226020, prior-to-4.0.14-form > checksum 3161919040 > InnoDB: stored checksum 538976288, prior-to-4.0.14-form stored checksum > 538976288 > InnoDB: Page lsn 538976288 538976288, low 4 bytes of lsn at page end 538976288 > InnoDB: Page number (if stored to page already) 538976288, > InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 538976288 > InnoDB: Database page corruption on disk or a failed > InnoDB: file read of page 1644. > > So great, that mysql doesn't even tell me at which table or database to look. > > InnoDB: You may have to recover from a backup. > InnoDB: It is also possible that your operating > InnoDB: system has corrupted its own file cache > InnoDB: and rebooting your computer removes the > InnoDB: error. > InnoDB: If the corrupt page is an index page > InnoDB: you can also try to fix the corruption > InnoDB: by dumping, dropping, and reimporting > InnoDB: the corrupt table. You can use CHECK > InnoDB: TABLE to scan your table for corruption. > InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html > InnoDB: about forcing recovery. > InnoDB: Ending processing because of a corrupt database page. > > Number of processes running now: 2 > mysqld process hanging, pid 19729 - killed > mysqld process hanging, pid 19729 - killed > 071107 08:46:29 mysqld restarted > 071107 8:46:29 [ERROR] Can't start server: Bind on TCP/IP port: Address > already in use > 071107 8:46:29 [ERROR] Do you already have another mysqld server running on > port: 3306 ? > 071107 8:46:29 [ERROR] Aborting > > 071107 8:46:29 [Note] /var/mysql/mysql-4.1.18/libexec/mysqld: Shutdown > complete > > 071107 08:46:29 mysqld ended > > > I cannot count any more how often I had problems with innodb. I regret ever > compiled it in, but now that a lot of users are using it, I need to recover > from the problem. > > But there is one major problem: All error messages and recovery documentation > of innodb sound, as if I would deal with just a few tables. But I have > hundrets of databases with thousands of tables! So there is no fast dump and > insert of tables. > > Any ideas (besides from dumping everything and importing at then)? > > Regards > Marten > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- Richard Edward Horner Engineer / Composer / Electric Guitar Virtuoso [EMAIL PROTECTED] http://richhorner.com - updated June 28th -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
