Over the years innodb has caused me more trouble than anything else in the LAMP stack. MyISAM tables seem to work and repair without issue. Innodb seem to be endless trouble. Alas, clients need them so I have to make them work.<\/p>\n\n\n\n
Today a mysql server crashed and wouldn’t restart. A few of the errors:<\/p>\n\n\n\n
File operation call: 'read' returned OS error 71.<\/code><\/pre>\n\n\n\nInnoDB: Operating system error number 2 in a file operation<\/code><\/pre>\n\n\n\namong others. I won’t get into the causes – I’m sure 99% is due to poor shutdowns and other issues that are not really the fault of mysql (other than the lack of graceful failure whichh myisam seems to be so good at).<\/p>\n\n\n\n
Here is a quick fix when under pressure – this won’t fix your problems if you have a single innodb table you need but if you have a mixed set of databases and tables this will get at least something<\/em> working – which in my case was better than nothing.<\/p>\n\n\n\nNote that everything I’m saying to do is very bad form and practice. But if you have critical databases that need to be up it may help you out. As always, back everything up firs<\/em>t just in case. You’ll have a back-up of a broken database but perhaps it will be a bit less<\/em> broke than the version you have if the following goes wrong.<\/p>\n\n\n\nFirst find and move all databases with innodb tables to a temp directory :<\/p>\n\n\n\n
mkdir \/var\/lib\/tmp_disable\nfind \/var\/lib\/mysql -name \"*.ibd\" -print\n# you'll see various files inding in .ibd - look at the directory they are in (the database name) and move that dir out of the mysql data directory\nmv <database-dir> \/var\/lib\/tmp_disable\n<\/code><\/pre>\n\n\n\nFinally move the global .ibd and logs to the tmp directory: <\/p>\n\n\n\n
mv \/var\/lib\/mysql\/ibdata1 \/var\/lib\/mysql\/ib_logfile* \/var\/lib\/tmp_disable<\/code><\/pre>\n\n\n\nmysql won’t like this and it’s not really a good idea. However your mysql server should at least start now. A new (empty) ibdata1 will be created. This will break a lot of things if your data is stored in this global datafile. However if you have each innodb table writing to its own ibd (by setting innodb_file_per_table in \/etc\/my.cnf) you might be ok.<\/p>\n\n\n\n
Next, move each database back into \/var\/lib\/mysql on-by-one, restarting mysql each time to see if it will still start. What to do with the database that is causing the problem? No easy answer to that unfortunately. I usually build a temporary mysql server in a VPS to work on it. At least your overall server will run while you are able to troubleshoot the problem database table(s).<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Over the years innodb has caused me more trouble than anything else in the LAMP stack. MyISAM tables seem to work and repair without issue. Innodb seem to be endless trouble. Alas, clients need them so I have to make them work. Today a mysql server crashed and wouldn’t restart. A few of the errors: … <\/p>\n