MySQL lost records in a master slave scheme

Question

We have developed a web application that uses two MySQL servers set up with replication (master-slave scheme). The application was to be used for a couple of days only, when ~ 50-60 users would enter numeric data. The database was setup to used InnoDB tables. Before the big day, we made a test when a great portion of the expected users entered data in order to see the cpu utilization of the master server and its performance in general.

The big day came and when the data entry began we experienced an issue of losing data from one table. The user data entry form included data that were saved in two different tables. We discovered an application bug that the data for the first table was outside the transaction for the second. This would explain the behavior of not saving data for the sub-form (the second table). However, after fixing the bug and rolling out the newer version, after ~ 2 hours, the issue happened again. The were several records that had the data for the main form saved (the first table) and the data for the sub-form lost (the second table). The users entered again the missing records. Querying the database returned 0 records of lost rows. And then, the issue stroke again. Without explanation, we had somewhat ~170 lost records from the sub-form (the second table).

Could a possible corrupted ibd file lead to such a situation? After doing some basic research in Google, it came down to a post saying that when using InnoDB plugin (as in our case) and you upgrade to a newer version, inconsistencies could arise and you have to delete your database and re-create it again to be sure that all is ok. When we started developing the application, the MySQL version used was 5.1.56 (Debian 6 stable) and at some point, 5.1.61 was pushed as an update. Could this upgrade lead to a situation when some relatively medium-weight contention cause lost records?

Answer 1

My guess would be that there are three(3) aspects to look over:

ASPECT #1

I would be concerned with InnoDB settings because there are new features in InnoDB (starting with the plugin as of MySQL 5.1.38) that allow for accessing multiple CPUs. If you do not set the parameters, InnoDB will behave like it did before the enhancements. In fact, there are cases where InnoDB in older versions of MySQL will operate faster than newer version:

• How do I properly perform a MySQL bake-off?
• Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
• Using multiple cores for single MySQL queries on Debian
• Is the CPU performance relevant for a database server?

ASPECT #2

I would be very carful when upgrading MySQL 5.1 to another version of MySQL 5.1 because MySQL 5.1 introduced the concept of installing plugins because you now have the table mysql.plugin. If you install a newer version of MySQL, make sure you do not hose mysql.plugin. You have to just reinstall the plugin. This, in itself, is not the cause of your problem. Nevertheless, you need to be aware of this in the event that this is a cause.

ASPECT #3

If you have not already done this, I would raise innodb_log_buffer_size to 32M. The default is 8M. Increasing it will reduce disk I/O. Please do not go beyond this, since large COMMITs can and will form I/O-based bottlenecks.

RECOMMENDATION

I would upgrade completely to MySQL 5.5 because all of the InnoDB Plugin is natively installed in MySQL 5.5. As highlighted in my previous links, you must configure and tune the settings needed for InnoDB to use multiple CPUs. Increase your log buffer

UPDATE 2012-06-03 18:30 EDT

Since you cannot install MySQL 5.5, you still have aspects 2 and 3 you could implement.

• Install the InnoDB Plugin
• Increase the innodb_log_buffer_size to 32M