My company has a product that allows our clients to perform tasks offline and then sync with the online version. We have ran into a problem that has grown more and more frequent.
The problem is two fold:
During the syncing process, it appears that, at times, data loss occurs somewhere. This wouldn't be a big issue, except that the MySQL binary log is missing data for that sync. It does show in the database that SOME of the data was written, but NONE of the data was written to the log (even the data that was written to the DB). I need a solution that ensures 100% consistency between the data in the database and the MySQL log. The tables that this frequently occurs in are either MyISAM or InnoDB (depending on the size of the client).
On the offline devices, there is frequent data corruption. We are using the server2go package (WAMP) to host a derivative of our PHP/MySQL based product on their device. Are there any methodologies outside of software I/O abstraction that would allow a more stable platform?
This issue was resolved by cloning every change to the mysql DB into a SQLite database and grafting on a "backup" module onto the DBAL that automatically detected data corruption and repaired the table and restored missing rows from the SQLite Database.