'MySQL Repair | MySQL Recovery' Blog RSS Feed

Table Corruption after running of Flush and Optimize Commands

samjoseph — Wed, 30 Dec 2009 04:51:09 -0800

There are numerous MySQL commands (with different syntax) that help you to systematically maintain your database. Out of the various commands, the two most common MySQL commands are Flush and Optimize. The Flush command reloads or clears all the internal patches used by MySQL. For using the Flush statement, you will need to have the RELOAD privilege. The Optimize command is used to defragment the database file and to reclaim the unused space. While both the commands hold different functionalities, using these commands one after the another can result into corruption of table. In such situations, the data stored in the table becomes inaccessible. For complete accessibility of table records in such situations, a fundamental way is to restore the data from an updated database backup. But if in case, the backup file is unavailable, then you will need to use advanced MySQL Repair to repair the table.

As a practical case, you run the command “Flush tables” and then immediately run “Optimize table” command. After doing so, the table on which the commands are running gets corrupt and the records stored in it become inaccessible.

Cause:

The root cause for table corruption is that these commands do not wait for each other to perform there particular tasks.

Resolution:

An easy way to repair the corrupted tables is to run the below commands:

1.Check the corruption limit of the table using underneath statement:
mysqlcheck [options] dbname tablename [tablename2... ]
2.Repair the corrupt table using below statement:
repair table tablename [,tablename1...][options]

The above steps enable you to repair the table and bring back the table in working state. But if in case, the above steps fail to resolve the corruption, then it means that the table is beyond the capabilities of Repair command. In such scenarios, the only way to repair your table is by using advanced MySQL Database Repair utility.

These Repair MySQL Database applications use powerful scanning and repairing procedures that enable you to repair the table completely. With highly graphical user-documentation, the software is self explanatory and does not require any technical understanding.

MySQL Table corruption post Migration to MySQL5

samjoseph — Tue, 29 Dec 2009 02:48:30 -0800

With the newer versions of MySQL coming into the market, it becomes obvious for the database users to migrate to the new version. Migration primarily allows you to use the additional functionalities of the new version. Few additional functionalities could be increased speed and security. While migration allows you with numerous benefits, it might also result in corruption of database. Once the database is corrupted, it becomes unmountable making its records inaccessible. In such situations, an updated database backup acts as a boon and allows you to restore the database components. However, there are many occasions where the users either forgets to create a database backup or backup falls short to restore the required data. For such scenarios, only an advanced MySQL Repair software can help.

As a practical case, you migrated your MySQL database from MySQL 4.1 to MySQL5. After doing so, when you attempt to access the records stored in your tables, you receive the below error message:

“wikidb.archive1
Error : Table 'wikidb.archive1' doesn't exist
error : Corrupt
wikidb.categorylinks1
Error : Table 'wikidb.categorylinks1' doesn't exist
error : Corrupt
wikidb.externallinks1
Error : Table 'wikidb.externallinks1' doesn't exist
error : Corrupt
wikidb.filearchive1
Error : Table 'wikidb.filearchive1' doesn't exist
error : Corrupt”

The error message clearly indicates corruption of MySQL tables.

Cause:

Migration can result into MySQL database corruption under following circumstances:

Incomplete migration
Compatibility Issues

Resolution:

To resolve the above error message and to access the data stored in tables, you will need to follow the below steps:

1.Check the extent of table corruption using mysqlcheck [options] dbname tablename [tablename2... ] command.
2.Repair the table using repair table tablename [,tablename1...][options] command.

The overhead steps confirm complete repair in most of the table corruption scenarios. However, if the above steps fail to solve the purpose, then you will need to use advanced MySQL Repair application. Such Repair MySQL Database tools use extensive scanning methods to repair corrupt database objects in most cases. Their interactive user interface makes them easy to understand and implement. In addition, these tools neither delete or modify the original data stored in the database.

MySQL Server crash after passing MySQL open file limit

samjoseph — Tue, 08 Dec 2009 02:42:24 -0800

The file descriptor limit in Windows operating system restricts simultaneous opening of the files to a defined limit of 2048. The limit further causes severe performance issues for a busy MySQL server, because the table cache value needs to be greater than 2048. In most of these scenarios, MySQL server crashes resulting in corruption of MySQL database. In addition, the database becomes unmountable and its records inaccessible. To access your database records in such scenarios, you will need to restore the data from a valid and complete database backup. But if in case, your database is not available or backup file is corrupted, an advanced third-party MySQL Repair application always helps by performing a complete repair.

As a practical instance, your MySQL Server is already running busy and when you attempt to open some more tables, your server crashes displaying the below error message:

“080726 6:06:11 [ERROR] Error in accept: Too many open files
080726 6:08:16 [ERROR] /home/sbester/mysql/5.1/mysql-5.1.28-linux-i686/bin/mysqld: Can't
open file: './test/t1_tmp.frm' (errno: 24)
080726 6:08:17 [ERROR] /home/sbester/mysql/5.1/mysql-5.1.28-linux-i686/bin/mysqld: Can't
open file: './test/t_@002d123.frm' (errno: 24)....”
Your MySQL server shuts down expectedly after the above error message.

Note: In most cases of improper shutdown of MySQL server, your database becomes corrupt and inaccessible.

To prevent database corruption caused due to above reason, you should exceed the default open file limit to 16384 or more by using max-open-files directive in MySQL. For repairing your corrupted database, you should follow the below steps:

1.Use the below command to check the level of corruption:
mysqlcheck [options] dbname tablename [tablename2... ].
2.Repair your database using the below command:
repair table tablename [,tablename1...][options]

The above resolution proves successful in repairing your database after all logical crash scenarios. But the possibilities of failure of the above method also do exist. In such situations, you will need to use advanced MySQL Database Repair application. Such MySQL Recovery tools perform comprehensive repair after by incorporating intensive scanning and repair techniques.

“Error Signal 11” at MySQL Server Startup

samjoseph — Mon, 07 Dec 2009 05:03:19 -0800

Error message at the startup of MySQLD (MySQL Server) primarily occurs when the database is corrupted or damaged. Few main factors responsible for MySQL database corruption are improper MySQLD shutdown, concurrent modification of same database component, and software bugs. Two major outcomes that you might encounter after database corruption are unmountability of database and inaccessibility of records. To surmount both these hazards and easily access the database records, a standard way is to restore it from an updated database backup. However, in case of database backup unavailable, the only alternative left is to repair your database using powerful MySQL Repair application.

Consider a scenario, where you encounter the below error message when you attempt to start MySQLD:

“MySQL got signal 11”

After the above error signal appears, MySQLD shuts down automatically and then restarts on its own, resulting in improper shutdown of your MySQLD.

Note: If the above scenario occurs several times in a single day, the probability of severe database corruption increase manifold.

Cause:

The root cause for the occurrence of the above error message is hitting of MySQLD with a bug.

Resolution:

If your database gets corrupted due to improper shutdown of MySQLD, then you can repair your database using below steps:

You will need to check the level of corruption by running the underneath command:
mysqlcheck [options] dbname tablename [tablename2... ].
After determining the level of corruption, you will need to use the below command to repair your database:
repair table tablename [,tablename1...][options]

The above steps prove successful in repairing the corrupted database in most of the corruption scenarios. However, if the above error message persists even after following the above steps, then it means that database is badly corrupted and requires a powerful third-party MySQL Recovery software.

Upgradation to 5.1.28 results in MySQL Database Corruption

samjoseph — Wed, 02 Dec 2009 04:48:55 -0800

Upgradation to a new version of MySQL database server allows you to remove all the bugs in the previous version. It also enables you to improve the server performance, security, and retrieval results. While upgradation allows you with numerous benefits, it can also result in corruption or damage of your database. In such situations, your database becomes unmountable and its records inaccessible. To successfully access your database records, an ideal way is to restore your database from an updated backup. However, there have been thousands of case studies where the user has either not created any backup or its backup file has been corrupted or damaged due to various internal or external factors. For such corruption scenarios, you will need to repair your database by using advanced MySQL Repair software.

Consider a practical scenario, where you upgraded your system from 5.0.27 to 5.1.28 on Windows Vista operating system. However, when the upgradataion is complete and you attempt to access your database components, they become inaccessible. Furthermore, you also encounter an error message stating that your tables have been corrupted.

Cause:

Few main causes for the corruption of tables are discussed below:

Improper upgradataion
Incomplete upgradation

Resolution:

To resolve the above error message and to access your database records, you will need to follow the below steps:

1.Downgrade to the previous version of MySQL database server.
2.Restore data from an updated backup.
The above resolution proves successful in most of corruption scenarios. The resolution only fails to perform when you have not created any database backup or the backup is corrupted. In such circumstances, the only way left to repair your database and to access the records is by using a powerful third-party MySQL Database Repair application.

Such MySQL Recovery applications use intensive scanning techniques to repair and restore all MySQL database components. With highly graphical user-interface, these tools are easy to understand without any former technical understanding. These non-destructive applications provide safe repair by not making any changes in the original database file.

Delete Operation resulting in MySQL Table corruption

samjoseph — Tue, 24 Nov 2009 04:55:09 -0800

As the name suggests, Delete statement is used to delete selected or all rows from the table. The statement also returns a count of all the rows deleted by the user. However, the user always needs a delete privilege to perform delete operation. While the delete operation is safe and easy to perform, it can also result in corruption of the MySQL table. In such situations, the data stored in the table becomes inaccessible and you receive an error message. To overcome such error message and access the data stored in MySQL table, an updated backup always acts as a boon. But if you have not maintained any database backup or back falls short to restore the required data, then you will need to use efficient third-party MySQL Repair application.

Consider a practical scenario, where you run a delete query to delete some records. However, instead of query deleting the records, below error message pops up stating that the table has been corrupted:

“InnoDB: Failing assertion: fil_page_get_type(page) == FIL_PAGE_TYPE_BLOB...”

After the above error message appears, the records stored in the table become inaccessible.

Cause:

Two reasons responsible for the occurrence of the error message could be buggy BLOB handling code or installation of wrong version of MySQL database server.

Resolution:

To completely resolve the above error message and to access the data stored in the table, you will need to follow the below steps:

1.Run check table on corrupt table
2.Then run repair table command on that table
3.Try and re-run the Delete command.
There is still a slight possibility that your table still remains corrupted after following the above steps. For successful and complete repairing of table, you will need to use an advanced MySQL Recovery software. Such tools use powerful repairing methods to provide absolute MySQL Repair in all situations. With interactive user-interface, these tools are easy-to-understand without any prior technical knowledge. These non destructive tools ensure safe repair of database without making any changes in the original MySQL database.

MySQL table Repairing process Halts

samjoseph — Wed, 18 Nov 2009 21:29:45 -0800

A severely corrupted MySQL database table is always difficult to repair. Corruption in table not only stops the database to mount, but also makes the records saved in the table inaccessible. Few major causes for severe corruption of MySQL database tables are improper system shutdown, software bugs, and human errors. To surpass the effects of table corruption, restoration from a complete and valid database backup always helps. But, the possibilities for backup being corrupted or damaged always exist. In such situations, an ideal way to access your data is by using an advanced third-party MySQL Repair application.

Consider a practical scenario, where you run a query to display the contents saved in a particular table and you encounter an error message stating that the table has been severely corrupted. To repair your corrupted table, you run the below command.

Repair Table Tablename

The above command ran for approximately 15 hours and didn't stopped until you forcefully stopped the never-ending process. Furthermore, while the repairing process was in progress, it occupied all your disk space. The below messages displayed during the repairing process:

-rw-r----- 1 mysql mysql 8.9G May 15 19:52 post.MYD
-rw-r----- 1 mysql mysql 7.8G May 17 18:19 post.MYI
-rw-r----- 1 root root 214M May 17 18:19 post.TMD
-rw-r----- 1 mysql mysql 9.0K Dec 6 11:49 post.frm

The above message indicate that the table from which you want to execute the data is beyond repair of above command. To access your inaccessible records in such situations, you can restore the data from an updated backup. However, in case of no backup availability, only an advanced MySQL Database Repair tool can help.

Such MySQL Recovery tools use high end procedures to repair your corrupted tables and restore them on default or new user-specified location. The database repair software come with highly interactive user-documentation, which guides you through a complete repair process. The non-destructive nature of the software ensures risk-free recovery of recovered data by leaving the original database file untouched and unmodified.

Myisamchk fails to repair MySQL Database Tables

samjoseph — Thu, 12 Nov 2009 05:05:25 -0800

Whenever, you create a Table in MySQL database, it creates three different files (FRM, MYD, and MYI) for storing table format, data, and Index. Corruption in MySQL database tables happens very easily. Few major reasons for table corruption are improper mysqld shutdown, software bugs, and human errors. Once the tables are corrupted, an error message pops up stating the cause for its occurrence. This error message not only makes the database unmountable, but also makes the records stored in the table inaccessible. The error message leaves you in an unrecoverable position leaving you with no option, but to restore data from an updated backup. While restoration of data from an updated backup helps you to recover from any corruption scenario, the possibilities of backup being corrupt or damaged always persist. In such situations, only a powerful third-party MySQL Repair software helps.

Consider a practical situation, where your table is corrupted and you experience the below error message:

“undef error - DBD::mysql::db selectrow_array failed: Table 'attach1_data' is marked as crashed and should be repaired [for Statement "SELECT LENGTH(thedata) FROM attach1_data WHERE id = ?"] at Bugzilla/Attachment.pm line 344 Bugzilla::Attachment::datasize('Bugzilla::Attachment=HASH(0x9df119c)') called”

From the error message, it is crystal clear that the table “attach1_data” is corrupted and need to be repaired. The above error message pops up every time you endeavor to access the records stored in “attach1_data” table.

How to Resolve?

To resolve the above error message and access the records stored in “attach1_data” table, follow the underneath steps:

Check the table by using myisamchk command.
Repair the table using myisamchk -r command.

Note: To avoid “clients are using or haven’t closed the table properly” error message, then perform repair after shutting down mysqld.

Myisamchk command repairs corrupted tables in most circumstances. But if the command repair command fails to repair and the error message remains, then you will need make a selection of an advanced MySQL Recovery software that repair your database. These MySQL Repair tools use effective scanning algorithms to perform standardized repair of corrupt tables.