30.8.2015
Some time ago my colleague Tomáš Hofman wrote an article about recovering tables in MySql: Obnova InnoDB tabulek v MySQL. If you want to know anything about MySql, ask rather him then me. I cannot even set an user in MySql. I do not like this database, I do not recommend it to my customers, I do not use it in any project and I do not trust applications using it.
Even I try to avoid this database, sometime I got in contact with an application using MySql to store its data. A few years ago I have to use primitive application collecting measured data from photovoltaic plants. The application had few parts: simple PHP script to retrieve data from database, Apache server, program fslurp to communicate with Fronius invertors, perl script to parse output from fslurp and cron utility to start it periodically. Application was very primitive, confusing, sensitive to timing and very hard to setup. All configuration parameters were hard-wired to source code so the code was unique on every power plant. Author of this miracle automatically chooses MySql to store the data - understand that fact as an explanation why I do not trust anything which uses MySql to store its data. When I have to use an application using MySql I'm always afraid of such kind of application.
Database in the application was very simple. It has only two tables - one table stored measured values about a week to past and the second table stored current status of every invertor - author cannot retrieve last status from archive table. Tables were stored in MyISAM format. I do not know if the MyISAM format offers reference integrity, it was not used in the application. Archive table contained a few tens of thousands record, current status table contained not more then one hundred records. Database was very small and simple.
Even the worst horror was seen in the application, it was the MySql database which produced errors most frequently. Application was installed in twenty instances. PC was running without UPS but uptime of the computers was used to be more then a quarter of a year. Typically two errors occurred here:
Frequency two to three times in month. Help was very easy: I had to start the "repair table" command in the database. But the data were lost in time of the crash and it required manual intervention. The crash was usually associated with an interruption of power supply, but quite often tables were damaged without visible reason.
Another much insidious failure was an unexecuted update to table. Records in table with current invertor status was every few minutes overwritten with new status. When the failure occurred, some records were not overwritten. From one to five invertors of 70 were not updated regularly. The update command was normally executed without errors but the old values remained in database. Application had no chance to recognize the error. The only option was to read every written record and compare it with real value. Of course, application did not do it.
When the error occurred, I have to delete the database table and create it again. I was not able to observe any consequence with power supply failures.
Unacceptability of such behavior is in the fact that the data can not be relied upon. With the MySQL database, I was never sure if the data read from the database were accurate, or if I got some nonsense invented by MySql database engine.
We have never found a solution of these problems. And worst, the application was so badly written that it was simpler to create the application from scratch again.
The final solution is application Fotobot The application uses GPL license (some libraries with the license were used).
Application Fotobot uses completely different approach:
Database SQLite seems to be much better for such type of application then MySql. Database is embedded in application, no database server is required. Although the database is single-threaded and single-user, it is no problem in application like the Fotobot. Queries are executed in few milliseconds and unique access to database is granted using single locking. When focused to reliability the SQLite and MySql are simple not comparable. SQLite is so simple and so well tested that the events described above are impossible. We have never experienced any database error when using SQLite.
SQLite - Most Widely Deployed and Used Database Engine.
SQLite database stores its data in a single file. The database is very fast when the database file is small. When the size is about 1 GB or more, start of the application can spend uncomfortable amount of time. After a power supply failures the database engine checks the database file consistency and the application is blocked so it is better to store smaller amount of data. We store currently about hundreds thousands of records, usually not more then million records. Our database file size exceptionally exceeded 100 MB. The database file can be stored to ramdisk so the speed of the database is exceptional even in a Beaglebone computer. Of course the database can manage much greater amount of data but the basic assumption should be an UPS and stable applications without crashes.
Additional note: I tried to find what happens with database when the application starts and why I have to wait a long time so I studied some materials about SQLite. Resolution? I don't know. When the application starts I'm used to see the database file on the disk and also file ending with "-journal". Is it possible that the VACUUM command is started at the beginning? Or have I forgotten to close some transaction with COMMIT? Now it does not mater. The maximal size of the database today barely exceeded hundreds of MB and the speed is infernal.
