Problem with SQL catch-up procedure?

[philpugh]

Yesterday I noticed that my SQL server had gone off-line (still not sure how) but when I brought it back on line I expected the catch-up procedure to kick in and update the relevant databases. This does not appear to be happening.. I have included a section of the log file across the time that I re-instated the SQL server.

It is obviously trying to insert the missing information but failing with duplicate key entries (LogDate / LogDateTime) The first entries logged seem to be my ExtraSensors log - but there are no catch-up entries for any of the tables involved.

What is interesting is that the 'failed' catch-up listed shows data that is NOT correct for the time it is trying to log and that in every case the LogDateTime in the failed entries appears to be the current time and not the failed log time - is this a problem or is there something I am missing here?

[freddie]

Funnily enough I had this problem too. Connection lost Friday evening and restored Saturday lunchtime. MX logged a single failed DB query being done once the connection was restored, but no others. I restarted MX a couple of times before the connection was restored as I initially thought the problem was on the MX host - but I didn't think that mattered as the failed queries are stored in the local sqlite database. I resolved the connection by restarting the DB host.

[philpugh]

Since restarting the MariaDb it is logging the Monthly and Dayfile tables correctly as well as the Custom SQL for my ExtraSensors it’s just not restoring the missing entries. They are in the CMX files (Dayfile.txt and monthly logs). I have taken a copy of the CumulusMX.db SQLite database for extra backup but I should be able to get the entries from the Dayfile or Monthly logs. It tries to do the recovery each time it logs data (every 5 mins). I will enable debug logging now and leave running overnight.

[philpugh]

Just to close this down. All three weather stations had the same issue - neither the standard tables (Daily and Monthly) recovered any of the missing data - but this is easily solved by use of Export-To-MySQL.exe which repopulated these tables for each of the systems.

The ExtraSensor tables I create through Custom SQL in CumulusMX for one of the systems didn't repopulate either - it tried but kept complaining about duplicate keys - and it appeared to be trying to update using the current date/time for the missing entries.

I recovered these from the log files and MSExcel to create MySQL statements for use with phpMyAdmin.

[mcrossley]

Phil, I'll take a look at this, but your log shows that "something" was being inserted into your database by the catch-up process as the count of outstanding queries reduces each time it tries.

It looks like some successful statements may be being cached as well.

I intend to give this process a bit on love in the next main release.

Note that the catch-up data is only cached in memory, so if you restart CMX it will be lost.

[mcrossley]

PS: If you enable debug logging if this happens again you will see the catch-up statements being executed.

[philpugh]

Funny you should mention that Mark....

I did turn on debug logging towards the end of this saga and this is the extract of the log file from that moment.

You can see it trying to execute the update to my ExtraSensors SQL table but failing on duplicate key (LogDateTime) and putting the current date/time as the LogDateTime instead of the "real" LogDateTime.

Between each attempt the number of catch up statements to execute does count down -BUT none of these statements (Monthly, Daily, or my ExtraSensor) make it into the tables.

Cheers

[freddie]

Funnily enough I had this problem too. Connection lost Friday evening and restored Saturday lunchtime. MX logged a single failed DB query being done once the connection was restored, but no others. I restarted MX a couple of times before the connection was restored as I initially thought the problem was on the MX host - but I didn't think that mattered as the failed queries are stored in the local sqlite database. I resolved the connection by restarting the DB host.

I did the same as Phil and used various tools to create the insert statements from the log files. My learning point in this exercise was that the failed queries are discarded on restart. I won't forget that one

[Mapantz]

I think I had the same problem.

My host went offline for a couple of hours overnight. CMX started adding back to the SQL server when it was fixed, but none of the missing data was inserted.

[mcrossley]

I have been giving the MySQL catch-up some love for the next release.

So...
1. The failed commands will be cached persistently across CMX restarts (in the SQLite db - so you could access them there as well).
2. There will be a viewer/editor for the cached statements along with the existing Purge All function, so you can remove or edit a rouge statement or two that is blocking the rest.
3. Every time I test the feature it works flawlessly! I have tried disconnecting the network and reconnecting, and changing the database name in the config to something invalid and then changing it back. In both cases the queued commands are applied OK. So not sure what is going on at the moment

Admittedly most of the testing has been with my new version, but I performed the same tests before and they seemed to work.

[mcrossley]

@freddie @philpugh could you test a new build and see if the MySQL catch-up in it works any better?

[freddie]

Yep sure.