MySQL data upload stalling

[monymony]

Hello Everyone,


I have created a MySQL database and when WC connects for the first time it creates the tables and uploads my first 9 months of data and then just stopped.  Watching the logs I know the problem record is Mar-09-2008 03:00.  When I look at this record in my .CAT files I notice this i the record where daylight savings time started so the previous record is Mar-09-2008 01:55.


68796 t:090150 T:-1 Ti:21.33 D:-8.67 Pr:1015.8449 W:11.27 Wd:293 Wc:-4.83 Wg:33.8 Ph:0 P:0 H:56 Hi:28 Pm:44.7 Py:198.37 V:4
68797 t:090155 T:-0.89 Ti:21.44 D:-8.56 Pr:1015.7771 W:9.66 Wd:270 Wc:-4.28 Wg:27.36 Ph:0 P:0 H:56 Hi:28 Pm:44.7 Py:198.37 V:4
68798 t:090300 T:-0.89 Ti:21.44 D:-8.83 Pr:1015.9803 W:9.66 Wd:270 Wc:-4.28 Wg:27.36 Ph:0 P:0 H:55 Hi:28 Pm:44.7 Py:198.37 V:4
68799 t:090305 T:-0.94 Ti:21.33 D:-8.89 Pr:1016.048 W:12.87 Wd:270 Wc:-5.11 Wg:30.58 Ph:0 P:0 H:55 Hi:28 Pm:44.7 Py:198.37 V:4



Anyone else see this behavior before?  I will note that this .CAT file was converted by me from WeatherLink so maybe I am missing something with how the annual time changes are handled.


Also, for the MySQL database the times are in UTZ and not my local time zone.  Is there any advantage to having the database setup this way?


Thanks again everyone!

[WCDev]

Search the forum for 'SQL' - this has something to do with the SQL timezone I'm sure but can't remember the details off the top of my head.
 

[monymony]

Will do... I had been searching for MySQL but probably should just done the 'sql'.  Thanks for the suggestion.

[monymony]

Hi WC fans!

I did some digging and sure enough there is a work-around for this issue already posted on this forum.  See http://athena.trixology.com/index.php?action=post;quote=7818;topic=879.0 for more information.

Set the timezone using terminal til GMT, start WeatherCat form terminal. Import the data into MySQL -> Solved the issue.

Changed back to Europe/Oslo, and restart WeatherCat.

If anyone runs into this issue here is what worked for me.

• In WC, go to Preferences and then the MISC2 tab.  Change the sampling rate to 30 minutes and turn off ADAPTIVE mode.  Make note of your initial settings so you can change back later.
• Stop SQLDriver from the WC TOOLS dropdown list
• Close WC
• Create a new database in MySQL called WeatherCat following the normal instructions.  If an existing database is already created either delete or rename.
• Open Terminal
• In terminal type the command: sudo systemsetup -gettimezone and make note of the result (i.e. American/New_York)
• Still in terminal type the command: sudo systemsetup -settimezone GMT
• Open WC from with terminal with the command: /applications/weathercat/weathercat.app
• Start SQLDriver from the WC TOOLS dropdown list.  Depending on the amount of data to upload this could take some time to complete.  For my large database that the upload status percentage stopped updating but data was still being sent to MySQL.  I would suggest monitoring the status directly from MySQL by watching the last record in the WeatherCat table.  I would also suggest have the WC Log window open in case there are any issues.
• When upload is complete, close WC
• Open the current WeatherCatData.Cat file (located in /users/{username}/library/WeatherCatData/Location/{CurrentYear}/{CurrentMonth}_WeatherCatDat.cat) with a text editor. 
• Review the current WeatherCatData.Cat to make sure any records that were added during the upload are set to the correct time.  For me, the entries created during the SQL upload were five hours ahead of any existing records so I manually adjusted those records.  This is why in step #1 the updates were set to 30 minutes to limit the records added with an inconsistent time.  Also check to make sure duplicate records were not created during the upload.  In my case I had about 20 entries created at the time the upload started that I had to manually delete.  Once your data is clean, save and close.
• Rename/delete the corresponding {CurrentMonth}_WeatherCatData.cat.hrs file.  This will get automatically recreated after WC is restarted.
• Go back to Terminal and enter the command: sudo systemsetup -settimezone America/New_York (replacing America/New_York with the timezone noted in step #6
• If you are running OSX Mavericks, reboot your computer.
• Start WC and go to Preferences and then the MISC2 tab.  Change the sampling rate back to your original setting.

I know a lot of steps but following these I did get around the issue with the data not uploading.  I hope these instructions will prove helpful for someone!

[monymony]

I finally discovered what is the likely cause of this problem.  MySQL on OSX installs with STRICT_TRANS_TABLES turned on.  If you turn this off following http://stanlemon.net/2013/03/23/mysql-strict-mode-on-os-x/ the dates should import correctly from WC into MySQL.  If you change this setting in the CFG file, don't forget to restart MySQL.


Hope this helps!