Strange SQL Upload Issue

[monymony]

Hello All,

At some point an issue occurred and my MySQL service stopped.  I did not realize this for some time until I spotted something strange in the WC Logs.  Anyway I have resolved the issue and most of the data uploaded as normal but for some reason a month was skipped.  I cannot explain this. 

My question is how to force WC to upload the missing data.  Any ideas?

Thanks!

Jay

[monymony]

I am throwing this out there as I would really like to force WC to upload these missing records or at least dump all SQL records from the point of the problem and upload clean from that point to the current date. 

Surely someone also is using the SQL option.

Please, anyone? 

[WCDev]

The best I can offer is to rebuild the database - see the manual for details, copied below for your convenience.

'If you wish to reset the database, stop the SQLDriver, drop all four tables (using something like phpMyAdmin), then start the SQLDriver again - it'll re-import your weather data.'

[monymony]

Thanks Stu,

Was hoping to avoid this as with nine years of data these tables are massive. 

Jay

[WCDev]

There is an alternative, but its a bit more work. First stop the SQL driver in WeatherCat (and make a copy of the database ), then delete all the records back to 22 Dec 15.

Next edit the time in ~/Library/Application Support/WeatherCatTemp/WeatherCatLastSQLUpload.plist. This is the time of the last upload to the database.

The time in here is held as the number of seconds since 1st Jan 2001 (GMT), so you can either calculate forward from there or calculate the number of seconds back to 22 Dec 2015 from the time 'now' and subtract that from the value in the file. Save the file then restart the SQL Driver, if all went to plan it'll now upload the missing records.

[monymony]

Hi Stu,

I did just drop and rebuild the tables, all 911,465 rows!

I did run into an issue though that WC did not want to automatically recreate the tables.  Not really sure why.  If I reapplied the backup schema and had blank tables then I only got new records, no rebuilt.

I actually ended up doing kind of what you suggested where in WeatherCatLastSQLUpload.plist I set the last upload to 1.  I have always wondered what WC was using to calculate that value stored so  knowing it is from Jan-01-2001 GMT will be very useful going forward.

Thank you for your help with this.  I think my issue is solved for now.

[monymony]

Hum... looking further while the WeatherData table did populate correctly, the ExtraData did not.  This means no rain information.  You can see in the screenshot the point where the upload finished and the normal uploads from WC start.  Any ideas?  Should I create an entry for this on the Bug list?

Let me know.

Jay

[WCDev]

No need, I've just raised one from here. Do you have any other additional sensors on the there, such as additional temp or hum sensors? Do they upload OK?

[monymony]

No, I don't have any other special sensor to worry about.  Since I don't use my MySQL rain data at the moment for any special calculations for my website it is nothing urgent for me to fix.  Thanks for the help!

[monymony]

No need, I've just raised one from here. Do you have any other additional sensors on the there, such as additional temp or hum sensors? Do they upload OK?

Ok, I might have had a brain-fart when I looked at the MySQL data.  For some reason I was expecting rain values in EXTRADATA but did discovered rain did upload properly to WEATHERDATA.  This is what happens when you try and do too much at the same time.  I am sorry for the confusion.

[WCDev]

No probs, the annual and monthly rain however should be in the extradata if available from your station (it's there when running live), so I'll keep the bug open.

Cheers.