Recent Posts
Sponsors
 |
How to Fix MySQL Database (MyISAM / InnoDB)Mike Peters, 10-02-2008 |
So... your shiny MySQL database is no longer running and you want to fix it?
You've come to the right place!
I've assembled a list of 7 ways to fix your MySQL database when a simple restart doesn't do the trick, or when you have corrupt tables.
Simple MySQL restart:
/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown
/usr/local/mysql/bin/mysqld_safe &
1. Corrupt MyISAM tables
MySQL database allows you to define a different MySQL storage engine for different tables. The storage engine is the engine used to store and retrieve data. Most popular storage engines are MyISAM and InnoDB.
MyISAM tables -will- get corrupted eventually. This is a fact of life.
Luckily, in most cases, MyISAM table corruption is easy to fix.
To fix a single table, connect to your MySQL database and issue a:
repair TABLENAME
To fix everything, go with:
/usr/local/mysql/bin/mysqlcheck --all-databases -uUSERNAME -pPASSWORD -r
A lot of times, MyISAM tables will get corrupt and you won't even know about it unless you review the log files.
I highly suggest you add this line to your /etc/my.cnf config file. It will automatically fix MyISAM tables as soon as they become corrupt:
[mysqld]
myisam-recover=backup,force
If this doesn't help, there are a few additional tricks you can try.
2. Multiple instances of MySQL
This is pretty common. You restart MySQL and the process immediately dies.
Reviewing the log files will tell you another instance of MySQL may be running.
To stop all instances of MySQL:
/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown
killall mysql
killall mysqld
Now you can restart the database and you will have a single running instance
3. Changed InnoDB log settings
Once you have a running InnoDB MySQL database, you should never ever change these lines in your /etc/my.cnf file:
datadir = /usr/local/mysql/data
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
InnoDB log file size cannot be changed once it has been established. If you change it, the database will refuse to start.
4. Disappearing MySQL host tables
I've seen this happen a few times. Probably some kind of freakish MyISAM bug.
Easily fixed with:
/usr/local/bin/mysql_install_db
5. MyISAM bad auto_increment
If the auto_increment count goes haywire on a MyISAM table, you will no longer be able to INSERT new records into that table.
You can typically tell the auto_increment counter is malfunctioning, by seeing an auto_increment of -1 assigned to the last inserted record.
To fix - find the last valid auto_increment id by issuing something like:
SELECT max(id) from tablename
And then update the auto_increment counter for that table
ALTER TABLE tablename AUTO_INCREMENT = id+1
6. Too many connections
Your database is getting hit with more connections than it can handle and now you cannot even connect to the database yourself.
First, stop the database:
/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown
If that doesn't help you can try "killall mysql" and "killall mysqld"
Once the database stopped, edit your /etc/my.cnf file and increase the number of connections. Don't go crazy with this number or you'll bring your entire machine down.
On a dedicated database machine we typically use:
max_connections = 200
wait_timeout = 100
Try restarting the database and see if that helps.
If you're getting bombarded with queries and you need to be able to connect to the database to make some table changes, set a different port number in your /etc/my.cnf file, start the database, make any changes, then update the port back to normal (master-port = 3306) and restart.
7. Corrupt InnoDB tables
InnoDB tables are my favorite. Transactional, reliable and unlike MyISAM, InnoDB supports concurrent writes into the same table.
InnoDB's internal recovery mechanism is pretty good. If the database crashes, InnoDB will attempt to fix everything by running the log file from the last timestamp. In most cases it will succeed and the entire process is transparent.
Unfortunately if InnoDB fails to repair itself, the -entire- database will not start. MySQL will exit with an error message and your entire database will be offline. You can try to restart the database again and again, but if the repair process fails - the database will refuse to start.
This is one reason why you should always run a master/master setup when using InnoDB - have a redundant master if one fails to start.
Before you go any further, review MySQL log file and confirm the database is not starting due to InnoDB corruption.
There are tricks to update InnoDB's internal log counter so that it skips the queries causing the crash, but in our experience this is not a good idea. You lose data consistency and will often break replication.
Once you have corrupt InnoDB tables that are preventing your database from starting, you should follow this five step process:
Step 1: Add this line to your /etc/my.cnf configuration file:
[mysqld]
innodb_force_recovery = 4
Step 2: Restart MySQL. Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored.
Step 3: Dump all tables
Step 4: Shutdown database and delete the data directory. Run mysql_install_db to create MySQL default tables
Step 5: Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database. (It should start normally now)
Step 6: Restore everything from your backup
You've come to the right place!
I've assembled a list of 7 ways to fix your MySQL database when a simple restart doesn't do the trick, or when you have corrupt tables.
Simple MySQL restart:
/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown
/usr/local/mysql/bin/mysqld_safe &
1. Corrupt MyISAM tables
MySQL database allows you to define a different MySQL storage engine for different tables. The storage engine is the engine used to store and retrieve data. Most popular storage engines are MyISAM and InnoDB.
MyISAM tables -will- get corrupted eventually. This is a fact of life.
Luckily, in most cases, MyISAM table corruption is easy to fix.
To fix a single table, connect to your MySQL database and issue a:
repair TABLENAME
To fix everything, go with:
/usr/local/mysql/bin/mysqlcheck --all-databases -uUSERNAME -pPASSWORD -r
A lot of times, MyISAM tables will get corrupt and you won't even know about it unless you review the log files.
I highly suggest you add this line to your /etc/my.cnf config file. It will automatically fix MyISAM tables as soon as they become corrupt:
[mysqld]
myisam-recover=backup,force
If this doesn't help, there are a few additional tricks you can try.
2. Multiple instances of MySQL
This is pretty common. You restart MySQL and the process immediately dies.
Reviewing the log files will tell you another instance of MySQL may be running.
To stop all instances of MySQL:
/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown
killall mysql
killall mysqld
Now you can restart the database and you will have a single running instance
3. Changed InnoDB log settings
Once you have a running InnoDB MySQL database, you should never ever change these lines in your /etc/my.cnf file:
datadir = /usr/local/mysql/data
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
innodb_log_files_in_group = 2
innodb_log_file_size = 5242880
InnoDB log file size cannot be changed once it has been established. If you change it, the database will refuse to start.
4. Disappearing MySQL host tables
I've seen this happen a few times. Probably some kind of freakish MyISAM bug.
Easily fixed with:
/usr/local/bin/mysql_install_db
5. MyISAM bad auto_increment
If the auto_increment count goes haywire on a MyISAM table, you will no longer be able to INSERT new records into that table.
You can typically tell the auto_increment counter is malfunctioning, by seeing an auto_increment of -1 assigned to the last inserted record.
To fix - find the last valid auto_increment id by issuing something like:
SELECT max(id) from tablename
And then update the auto_increment counter for that table
ALTER TABLE tablename AUTO_INCREMENT = id+1
6. Too many connections
Your database is getting hit with more connections than it can handle and now you cannot even connect to the database yourself.
First, stop the database:
/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown
If that doesn't help you can try "killall mysql" and "killall mysqld"
Once the database stopped, edit your /etc/my.cnf file and increase the number of connections. Don't go crazy with this number or you'll bring your entire machine down.
On a dedicated database machine we typically use:
max_connections = 200
wait_timeout = 100
Try restarting the database and see if that helps.
If you're getting bombarded with queries and you need to be able to connect to the database to make some table changes, set a different port number in your /etc/my.cnf file, start the database, make any changes, then update the port back to normal (master-port = 3306) and restart.
7. Corrupt InnoDB tables
InnoDB tables are my favorite. Transactional, reliable and unlike MyISAM, InnoDB supports concurrent writes into the same table.
InnoDB's internal recovery mechanism is pretty good. If the database crashes, InnoDB will attempt to fix everything by running the log file from the last timestamp. In most cases it will succeed and the entire process is transparent.
Unfortunately if InnoDB fails to repair itself, the -entire- database will not start. MySQL will exit with an error message and your entire database will be offline. You can try to restart the database again and again, but if the repair process fails - the database will refuse to start.
This is one reason why you should always run a master/master setup when using InnoDB - have a redundant master if one fails to start.
Before you go any further, review MySQL log file and confirm the database is not starting due to InnoDB corruption.
There are tricks to update InnoDB's internal log counter so that it skips the queries causing the crash, but in our experience this is not a good idea. You lose data consistency and will often break replication.
Once you have corrupt InnoDB tables that are preventing your database from starting, you should follow this five step process:
Step 1: Add this line to your /etc/my.cnf configuration file:
[mysqld]
innodb_force_recovery = 4
Step 2: Restart MySQL. Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored.
Step 3: Dump all tables
Step 4: Shutdown database and delete the data directory. Run mysql_install_db to create MySQL default tables
Step 5: Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database. (It should start normally now)
Step 6: Restore everything from your backup
|
|
Mike Peters, 10-03-2008 |
Recently I was faced with the daunting task of reparing an InnoDB database gone bad. The database would not start due to corruption.
First step was turning-on InnoDB force-recovery mode, where InnoDB starts but ignores all UPDATEs and INSERTs.
Add this line to /etc/my.cnf:
innodb_force_recovery = 2
Now we can restart the database:
/usr/local/bin/mysqld_safe &
(Note: If MySQL doesn't restart, keep increasing the innodb_force_recovery number until you get to innodb_force_recovery = 8)
Save all data into a temporary alldb.sql (this next command can take a while to finish):
mysqldump --force --compress --triggers --routines --create-options -uUSERNAME -pPASSWORD --all-databases > /usr/alldb.sql
Shutdown the database again:
mysqladmin -uUSERNAME -pPASSWORD shutdown
Delete the database directory. (Note: In my case the data was under /usr/local/var. Your setup may be different. Make sure you're deleting the correct directory)
rm -fdr /usr/local/var
Recreate the database directory and install MySQL basic tables
mkdir /usr/local/var
chown -R mysql:mysql /usr/local/var
/usr/local/bin/mysql_install_db
chown -R mysql:mysql /usr/local/var
Remove innodb_force_recovery from /etc/my.cnf and restart database:
/usr/local/bin/mysqld_safe &
Import all the data back (this next command can take a while to finish):
mysql -uroot --compress < /usr/alldb.sql
And finally - flush MySQL privileges (because we're also updating the MySQL table)
/usr/local/bin/mysqladmin -uroot flush-privileges
-
Note: For best results, add port=8819 (or any other random number) to /etc/my.cnf before restarting MySQL and then add --port=8819 to the mysqldump command. This way you avoid the MySQL database getting hit with queries while the repair is in progress.
First step was turning-on InnoDB force-recovery mode, where InnoDB starts but ignores all UPDATEs and INSERTs.
Add this line to /etc/my.cnf:
innodb_force_recovery = 2
Now we can restart the database:
/usr/local/bin/mysqld_safe &
(Note: If MySQL doesn't restart, keep increasing the innodb_force_recovery number until you get to innodb_force_recovery = 8)
Save all data into a temporary alldb.sql (this next command can take a while to finish):
mysqldump --force --compress --triggers --routines --create-options -uUSERNAME -pPASSWORD --all-databases > /usr/alldb.sql
Shutdown the database again:
mysqladmin -uUSERNAME -pPASSWORD shutdown
Delete the database directory. (Note: In my case the data was under /usr/local/var. Your setup may be different. Make sure you're deleting the correct directory)
rm -fdr /usr/local/var
Recreate the database directory and install MySQL basic tables
mkdir /usr/local/var
chown -R mysql:mysql /usr/local/var
/usr/local/bin/mysql_install_db
chown -R mysql:mysql /usr/local/var
Remove innodb_force_recovery from /etc/my.cnf and restart database:
/usr/local/bin/mysqld_safe &
Import all the data back (this next command can take a while to finish):
mysql -uroot --compress < /usr/alldb.sql
And finally - flush MySQL privileges (because we're also updating the MySQL table)
/usr/local/bin/mysqladmin -uroot flush-privileges
-
Note: For best results, add port=8819 (or any other random number) to /etc/my.cnf before restarting MySQL and then add --port=8819 to the mysqldump command. This way you avoid the MySQL database getting hit with queries while the repair is in progress.
 |
Dendy, 10-06-2008 |
this is great :)
thanks!
thanks!
 |
CalmQuiet, 10-06-2008 |
Can you advise whether your command-line guidance can be converted into strategies that those of us can use who run PHP/MySQL apps on common shared-server hosting situations?
In most such situations we cannot run command-line for MySQL.
I've not spent a lot of time thinking about "what if" db gets corrupted. Have you got another article to suggest for those of us without complete control over MySQL servers?
Also, any recommendations on strategies for reducing likelihood of corruption?
Finally, if you'll tolerate one more newbie question, Your comments comparing ISAM and InnoDB make me wonder about the choices: sounds like ISAM is more vulnerable to corruptions, but InnoDB will be trickier to repair. Is that a fair summary? Is that going to be true on shared host where there's no command-line access?
Many thanks for sharing your expertise!
In most such situations we cannot run command-line for MySQL.
I've not spent a lot of time thinking about "what if" db gets corrupted. Have you got another article to suggest for those of us without complete control over MySQL servers?
Also, any recommendations on strategies for reducing likelihood of corruption?
Finally, if you'll tolerate one more newbie question, Your comments comparing ISAM and InnoDB make me wonder about the choices: sounds like ISAM is more vulnerable to corruptions, but InnoDB will be trickier to repair. Is that a fair summary? Is that going to be true on shared host where there's no command-line access?
Many thanks for sharing your expertise!
|
|
Mike Peters, 10-07-2008 |
CalmQuiet,
If you're running on a shared-server hosting platform, you can still export/import all data by using phpmyadmin (should be provided by your host). I strongly discourage using InnoDB databases on a shared hosting platform.
"Any recommendations on strategies for reducing likelihood of corruption?"
Corruption can typically be traced back to either hardware failure or high load. If your data is important make sure you are on a RAID1 or RAID5 setup (so that if one hard drive dies, your server will continue running normally). Continually monitor your MySQL queries-per-second number. If it gets over 200, it's time to consider a dedicated server. If it get's over 1,000, it's time to look into replication or sharding.
"Sounds like ISAM is more vulnerable to corruptions, but InnoDB will be trickier to repair. Is that a fair summary"
Yes that's one way of putting it. The main motivation of switching from MyISAM to InnoDB is transaction support. If you can do without transactions and decide to stick with MyISAM - you're in good company. Many large web services rely on MyISAM exclusively.
For more information about the differences between MyISAM and InnoDB, please read MySQL storage engines
If you're running on a shared-server hosting platform, you can still export/import all data by using phpmyadmin (should be provided by your host). I strongly discourage using InnoDB databases on a shared hosting platform.
"Any recommendations on strategies for reducing likelihood of corruption?"
Corruption can typically be traced back to either hardware failure or high load. If your data is important make sure you are on a RAID1 or RAID5 setup (so that if one hard drive dies, your server will continue running normally). Continually monitor your MySQL queries-per-second number. If it gets over 200, it's time to consider a dedicated server. If it get's over 1,000, it's time to look into replication or sharding.
"Sounds like ISAM is more vulnerable to corruptions, but InnoDB will be trickier to repair. Is that a fair summary"
Yes that's one way of putting it. The main motivation of switching from MyISAM to InnoDB is transaction support. If you can do without transactions and decide to stick with MyISAM - you're in good company. Many large web services rely on MyISAM exclusively.
For more information about the differences between MyISAM and InnoDB, please read MySQL storage engines
 |
Michael, 10-08-2008 |
I run on a shared system VPS, one of my sites uses mysql with myphpadmin, there are around 1700 members with a data base and the site gets only around 60 visits a day. My hosting company keeps changing servers and now has the support hosted in India, they aren't much help. When they switched to India mysql tables went screwing and the myphpadmin kept crashing when you tried to access the tables, they tried to fix it but now 23 of the tables show in use. I just want them fixed and will move my web sites elsewhere, any suggestions or help for having someone fix the tables so i can copy them and move on. Thanks mucho
 |
Dawn Rossi, 10-08-2008 |
Michael,
Sounds like you really need to switch a hosting company!
Please contact us and we'll be happy to help with your database migration
Sounds like you really need to switch a hosting company!
Please contact us and we'll be happy to help with your database migration
 |
Don, 10-21-2008 |
Mike ,,, you really did a great job... Im new to MySQL and InnoDB,,, Thanks for the help!!!
 |
Frank Furter, 12-07-2008 |
Cheeser Muffin!! I am switching to InnoDB tables today! Thanks.
 |
TD, 12-10-2008 |
Mike
In your reply to CalmQuiet you said:
" I strongly discourage using InnoDB databases on a shared hosting platform. "
So that would imply that anyone who has a website that has some kind of transaction function would need to fork out the money for a dedicated server solution, would it not?
A lot of website owners would balk at that.
Could you please elaborate a little on why shared hosting doesnt play well with InnoDB tables?
Great article by the way!
In your reply to CalmQuiet you said:
" I strongly discourage using InnoDB databases on a shared hosting platform. "
So that would imply that anyone who has a website that has some kind of transaction function would need to fork out the money for a dedicated server solution, would it not?
A lot of website owners would balk at that.
Could you please elaborate a little on why shared hosting doesnt play well with InnoDB tables?
Great article by the way!
 |
dynsight, 02-07-2009 |
This is a good article, but what I don't care about the data?
Mine is a relatively new installation, and if I start mysql with "skip innodb" in my.cnf, all is well (except no Innodb). I have tried all force recovery, and none worked.
Anyway of just deleting the innodb files and having them rebuilt?
Mine is a relatively new installation, and if I start mysql with "skip innodb" in my.cnf, all is well (except no Innodb). I have tried all force recovery, and none worked.
Anyway of just deleting the innodb files and having them rebuilt?
 |
Mohammad, 05-29-2009 |
Nice post....
 |
ulph0, 06-16-2009 |
i had a case which was similar, but the database connection went away each time i tried to dump the corrupted tables. these tables were just cache tables so i did first a mysqldump --no-data, then a mysqldump --add-drop-table --ignore-table=db_name.tbl_name1 --ignore-table=db_name.tbl_name2 ... and importet both files to a fresh database similiar as described above. so at least these tables were set up as empty ones in the new dartabase.
 |
ppa108, 08-27-2009 |
My mysql server cou;dn't start after power suddenly shut down.I got the INNODB. can't start ... error.
I tried to follow your suggestion but I couldn't find my.cnf under etc and under data folder as well.
Please help me.
I tried to follow your suggestion but I couldn't find my.cnf under etc and under data folder as well.
Please help me.
 |
Nasir, 06-19-2010 |
Nice article, thanks
 |
Ehab, 07-11-2010 |
I'm facing a repeated issue with MySQL 4, that all of a sudden all my tables disappears, nevertheless that all databases exists
i don't know what is going on !!!!
please help me
i don't know what is going on !!!!
please help me
 |
en, 07-29-2010 |
Very helpful post. God bless you.
It was important in my case to run (as root) mysql_create_db with the --user=mysql option.
It was important in my case to run (as root) mysql_create_db with the --user=mysql option.
|
|
en, 07-29-2010 |
Sorry, mysql_install_db and NOT mysql_create_db above.
|
|
en, 07-29-2010 |
Some errors with innodb may be bugs.
For example, my hyperic database would crash when an update operation was attempted with error:
InnoDB: Assertion failure in thread [num] in file row/row0mysql.c line 1534
That was resolved by upgrading from 5.1.35 to 5.1.49.
For example, my hyperic database would crash when an update operation was attempted with error:
InnoDB: Assertion failure in thread [num] in file row/row0mysql.c line 1534
That was resolved by upgrading from 5.1.35 to 5.1.49.
|
|
Nasir, 08-10-2010 |
Its a great post.
 |
Raheem, 12-18-2010 |
I don't know anything about MYSQL. I joined new company. They have a application written in php with mysql. The database server crashed and all I have provided is a copy of database from backup. I am trying to look for the tables; I can see the table names but I can't browse them.I get all kinds of errors like innodb is not available.
When I change the my.ini default engine to myism the sql command prompt disappear. I have no clue how the previous connection was setup and it is all confusing. Please guide me through the step one. Also there is version issue as well. The server crashed was 5.0 and there is no 5.0 anywhere to down load to see may be this was I can see the data.
Please Please help.
When I change the my.ini default engine to myism the sql command prompt disappear. I have no clue how the previous connection was setup and it is all confusing. Please guide me through the step one. Also there is version issue as well. The server crashed was 5.0 and there is no 5.0 anywhere to down load to see may be this was I can see the data.
Please Please help.
 |
will, 12-23-2010 |
"...make sure you are on a RAID1 or RAID5 setup"
Raid5 is terrible for databases, very slow, much better would be raid10, you get the benefit of speed and redundancy then.
I would even go so far as to say i would be happier to have no raid than raid5. the overhead for keeping parity information up to date on a heavy write biased server is not worth it.
Raid5 is terrible for databases, very slow, much better would be raid10, you get the benefit of speed and redundancy then.
I would even go so far as to say i would be happier to have no raid than raid5. the overhead for keeping parity information up to date on a heavy write biased server is not worth it.
 |
Shahryar Ghazi, 12-27-2010 |
liked your article
here is a similar and relevant with some not-so-well-known differences
geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/notsowellknown-differences-between-myisam-and-innodb-mysql-server-storage-engines.php
here is a similar and relevant with some not-so-well-known differences
geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/notsowellknown-differences-between-myisam-and-innodb-mysql-server-storage-engines.php
 |
Larry Irwin, 01-27-2011 |
I just "repaired" the error
[ERROR] /usr/sbin/mysqld: Incorrect information in file: '....frm'
in the following manner:
1) shutdown mysqld if it is running.
2) move the ib_logfile files to a save directory
3) start mysqld
This will re-create the ib_logfiles and the problem goes away...
[ERROR] /usr/sbin/mysqld: Incorrect information in file: '....frm'
in the following manner:
1) shutdown mysqld if it is running.
2) move the ib_logfile files to a save directory
3) start mysqld
This will re-create the ib_logfiles and the problem goes away...
 |
Charles, 02-01-2011 |
I started mysql with innodb_force_recovery to 4
The MySQL process is running at 100% CPU now.. is it OK? Does it mean it try to recover?
The MySQL process is running at 100% CPU now.. is it OK? Does it mean it try to recover?
 |
Abu, 03-17-2011 |
Hi,
am new to this room, but going into i love the activities.
well i have issued to work it out for me.
i developed application that uses MYSQL database as the back end and VB2008 IDE for the Front End (User interface)
all modules in perfect condition..
But!
issues of BACKUP of the Database server;
1 the PC System used as MYSQL Server is having two (2) HDD i.e one (80G HDD have OS) and the other one (300G HDD will be Slave)and act as the location for the backup of the database.
Now how do i go about it.
should i use a vb-script and call it @ start or end application.
and what code or script to use.
thanks
am new to this room, but going into i love the activities.
well i have issued to work it out for me.
i developed application that uses MYSQL database as the back end and VB2008 IDE for the Front End (User interface)
all modules in perfect condition..
But!
issues of BACKUP of the Database server;
1 the PC System used as MYSQL Server is having two (2) HDD i.e one (80G HDD have OS) and the other one (300G HDD will be Slave)and act as the location for the backup of the database.
Now how do i go about it.
should i use a vb-script and call it @ start or end application.
and what code or script to use.
thanks
 |
Vidyut, 05-22-2011 |
You just saved my sanity, my hair and possibly my life :D
 |
MhK, 05-23-2011 |
Great stuff, Mike.
I've already recovered MySQL from corrupt InnoDB, reinitiated the ibdata1 issue by deleting it according to MySQL documents. But now, some tables are corrupted and they give "Got error 1 from storage engine" error. Do you have any solution for recovering those tables? Repair command doesn't work and also it's not possible to make backup of faulty databases :|
I've already recovered MySQL from corrupt InnoDB, reinitiated the ibdata1 issue by deleting it according to MySQL documents. But now, some tables are corrupted and they give "Got error 1 from storage engine" error. Do you have any solution for recovering those tables? Repair command doesn't work and also it's not possible to make backup of faulty databases :|
 |
MySQL Database Recovery, 05-26-2011 |
There is lots of reason mysql database corruption but you point out here how to repair mysql database manually? But you still not able to recover or repair database than you can use free or paid tools that helps to repair mysql database.
 |
MhK, 05-27-2011 |
Thanks for the reply, but do you have any tool to recommend? Please note that the corrupted MySQL databases are on a linux-based machine which is using InnoDB and I do have a backup of ibdata before the innodb crash date.
 |
kfi, 05-30-2011 |
I am enjoying the conversation. I have similar problems but MySQL is running on MS windows 2003 so i could not follow the instructions. All my tables have vanished. I need to restore them as soon as possible. any help
|
|
MySQL Database Recovery, 05-31-2011 |
@MhK If you really wants a tool for repair or recovery than I suggest you "Stellar Phoenix Database Recovery for MySQL". It supports MySQL engines like MyISAM and InnoDB. It effective to repair corrupted mysql database table.
 |
Peter Bowey, 07-04-2011 |
@Mike Peters
Many thanks Mike,
this 'guide' has been of the 'few' very successful methods I finally utilized to restore a partially corrupted 'innodb' after upgrading from Fedora 14 to 15.
The new MySQL with Fedora 15 left me a innodb warning:
InnoDB: "Warning: allocated tablespace" 1642, "old maximum was 0"
Researching the above MySQL 'report', I tested this was a partial corruption of my innodb. Your recovery method allowed me 100% recovery - with no errors.
Many thanks Mike,
this 'guide' has been of the 'few' very successful methods I finally utilized to restore a partially corrupted 'innodb' after upgrading from Fedora 14 to 15.
The new MySQL with Fedora 15 left me a innodb warning:
InnoDB: "Warning: allocated tablespace" 1642, "old maximum was 0"
Researching the above MySQL 'report', I tested this was a partial corruption of my innodb. Your recovery method allowed me 100% recovery - with no errors.
 |
Pete, 07-05-2011 |
Hi,
I've been using mysql v5 for years now and have had no issues until today. I've been monitoring the various process on my pc and have never seen mysql use more than 349mb of memory. Today however as soon as the service starts it just keeps eating up memory until over a gig is used, I can't connect with SQL administrator or query browser and eventually the service crashes. Why is mysql trying to do? is this a bug or is this a case of database corruption?
I've been using mysql v5 for years now and have had no issues until today. I've been monitoring the various process on my pc and have never seen mysql use more than 349mb of memory. Today however as soon as the service starts it just keeps eating up memory until over a gig is used, I can't connect with SQL administrator or query browser and eventually the service crashes. Why is mysql trying to do? is this a bug or is this a case of database corruption?
|
|
Pete, 07-05-2011 |
Hi Mike,
Thanks for the info on this website. It enabled me to fix the issue.
Great stuff!
Thanks for the info on this website. It enabled me to fix the issue.
Great stuff!
 |
andre, 10-10-2011 |
thank you, thank you, thank you. this got my hosting server back up, after a three-day nightmare..
 |
handa faran, 12-15-2011 |
thanks but i was looking for way to recover some missing rows, that i have no clue what delted them
 |
dbapv, 01-16-2012 |
mysqld]
myisam-recover=backup,force
can thisn be implemented on Mysql slave server as after putting it. all the tables are being checked and log is full of "marked crashed messages and repair"
myisam-recover=backup,force
can thisn be implemented on Mysql slave server as after putting it. all the tables are being checked and log is full of "marked crashed messages and repair"
 |
hugh coughlan, 06-15-2012 |
my server appears to up and running and I can execute queries from MySQl Workbench. However I am repeatedly getting error msg 'Error 2006: MySQL server has gone away' when I try to insert from my application.
What is the 1st step to diagnose this?
What is the 1st step to diagnose this?
 |
Wesley, 06-15-2012 |
Our database was too big to simply dump and reload when one or more of our InnoDB tables got corrupted. So we dumped one table at a time, dropped it, and then tried to restart mysqld without the innodb_force_recovery setting. After doing 5 tables this way it started up without errors, so we then reloaded each of the dumped/dropped tables and all was eventually ok. To figure out which table was most likely corrupted we did a ls -lt *.ibd from the data directory to see which tables had been updated last - then worked down the list
 |
basem ajarmah, 08-15-2012 |
thanks to Larry Irwin, 01-27-2011
I just "repaired" the error
[ERROR] /usr/sbin/mysqld: Incorrect information in file: '....frm'
in the following manner:
1) shutdown mysqld if it is running.
2) move the ib_logfile files to a save directory
3) start mysqld
This will re-create the ib_logfiles and the problem goes away...
>>> done
I just "repaired" the error
[ERROR] /usr/sbin/mysqld: Incorrect information in file: '....frm'
in the following manner:
1) shutdown mysqld if it is running.
2) move the ib_logfile files to a save directory
3) start mysqld
This will re-create the ib_logfiles and the problem goes away...
>>> done
 |
kushal, 08-17-2012 |
On running My Project i am getting Error:com.mysql.jdbc.exceptions.MySQLSyntaxErrorEx ception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1.
Plz Solve As Soon As you Can.
Plz Solve As Soon As you Can.
 |
Sean, 09-19-2012 |
You saved me a lot of customers with this one. Thank you so so so much!!!!!
 |
Peter, 11-16-2012 |
The most common reason for corrupted db is a full hard disk with no more space. Always the first thing to check & address before fixing mysql.
 |
opstalj, 04-11-2013 |
Great article ! Thanks.
 |
Atul, 04-22-2013 |
Hi Mike,
I have configured mysql backup in bacula ans it take backup using lvm snapshot ( /vat/lib/mysql is lvm partition ) and when I resotred the backup from bacula to another server its going into repairing mode everytime when I start mysql after restore. I tried this 2-3 times still its going into repairing mode. All tables are fine on main server from where we restored the backup. Could you please tell me why its going everytime into the repairing mode as it take 2 days to repair. Database size is 290G , in that one of the tbale size is 180G bit its not corrupted on original server. Also before taking lvm snapshot it lock the tables . Please advice how get rig from this. Thanks.
Regards
Atul
I have configured mysql backup in bacula ans it take backup using lvm snapshot ( /vat/lib/mysql is lvm partition ) and when I resotred the backup from bacula to another server its going into repairing mode everytime when I start mysql after restore. I tried this 2-3 times still its going into repairing mode. All tables are fine on main server from where we restored the backup. Could you please tell me why its going everytime into the repairing mode as it take 2 days to repair. Database size is 290G , in that one of the tbale size is 180G bit its not corrupted on original server. Also before taking lvm snapshot it lock the tables . Please advice how get rig from this. Thanks.
Regards
Atul
 |
gocabraless, 10-02-2013 |
Greate! It works for me, a little try and error but, it save me a lot of work. Thank you! Basic procedure was:
innodb data corrupt recovery process
- cup of good coffee (very important!!!)
- logout every body
- shutdown db clients & services
- look db log (mysql freaky bug ? ? ?)
- look sys log (hardware failure? ? ?)
- modify /etc/my.cnf add
[mysqld]
innodb_force_recovery = 4
- start database
- NO ONE MUST BE CONNECTED!!!
- dump everything (take a lot of time, coffee)
mysqldump --force --compress --triggers --routines --create-options -uUSERNAME -pPASSWORD --all-databases > /tmp/fulldb.sql
- stop database
- rename old data directory
- create new data directory
- instruct mysql install_new_db create basic schema
- start database
- restore fulldb.sql (take a long time, coffee)
- restart database
- look db log (ok, everything is fine!)
- all services up, check for data loss
and finally, mitigate future failures, add
[mysqld]
myisam-recover=backup,force
Thank you every body.
innodb data corrupt recovery process
- cup of good coffee (very important!!!)
- logout every body
- shutdown db clients & services
- look db log (mysql freaky bug ? ? ?)
- look sys log (hardware failure? ? ?)
- modify /etc/my.cnf add
[mysqld]
innodb_force_recovery = 4
- start database
- NO ONE MUST BE CONNECTED!!!
- dump everything (take a lot of time, coffee)
mysqldump --force --compress --triggers --routines --create-options -uUSERNAME -pPASSWORD --all-databases > /tmp/fulldb.sql
- stop database
- rename old data directory
- create new data directory
- instruct mysql install_new_db create basic schema
- start database
- restore fulldb.sql (take a long time, coffee)
- restart database
- look db log (ok, everything is fine!)
- all services up, check for data loss
and finally, mitigate future failures, add
[mysqld]
myisam-recover=backup,force
Thank you every body.
 |
Michael, 03-23-2014 |
Step by step process can be found here
www.technology91.com/starting-mysql-in-innodb-recovery-mode/
www.technology91.com/starting-mysql-in-innodb-recovery-mode/
 |
Dan, 07-09-2014 |
These instructions (at step 1!!!) saved me today after spending hours trying to figure out a performance problem with mysqld on Linux. . Thank you!
 |
Moe, 09-26-2014 |
How and where exactly to execute these commands, I am not that much engine identifier, can you please say step by step or show easy link to a toturial way, thanks
 |
Kee, 05-26-2016 |
Thanks, this article realy saved me. A corupt ibdata file and a lot of important information lost. Googleing there´s lots of suggestions out there but this was ther real stuff.
 |
Dazy Parker, 09-20-2016 |
I tried you methods but, these methods not worked for me. I have taken the help of my friends and colleges for this issue and all were helpless.
At last I bought Stellar Phoenix Database Repair for MySQL tool and it easily fixed the issue.
At last I bought Stellar Phoenix Database Repair for MySQL tool and it easily fixed the issue.
 |
Sandra Diaz, 09-28-2016 |
I recently stumbled upon your website & found it quite fascinating based on posted contents. I have another solution to these type of issue. Some days ago, I already faced this type of problem and I used third party tool to recover table. I would like to suggest you systools SQL database recovery tool, you can easily retrieve corrupted table without loss of any information.
|
|
Subscribe Now to receive new posts via Email as soon as they come out.
Comments
Post your comments
