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?