Welcome to the Cumulus Support forum.

Latest Cumulus MX release v3.2.4 build 3060 - 10 December 2019.
    Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014

    See the Wiki for downloading either version.

    MySQL - Custom uploads

    Discussion of version 3 of Cumulus, which runs on Windows, Linux, and OS X. All Cumulus MX queries in here, please.

    Moderator: mcrossley

    Post Reply
    User avatar
    mcrossley
    Posts: 5815
    Joined: Thu 07 Jan 2010 9:44 pm
    Weather Station: Davis VP2
    Operating System: Stretch Lite rPi
    Location: Wilmslow, Cheshire, UK
    Contact:

    MySQL - Custom uploads

    Post by mcrossley » Sun 24 May 2015 1:41 pm

    Observations:
    The processed SQL is not logged in the diag files for "Custom upload seconds"**, in fact nothing seems to get logged unless there is an error.
    It would be nice for debugging to see the processed SQL statement, and normal logging to see "X rows affected".

    The "Custom upload minutes" SQL statement does get logged, but again, not the results.

    Could the entry 'line' be a box, the SQL tends to be very long on one line and difficult to read/edit.

    Q: Can you put multiple statements separated with semicolons?

    I have an extra column on my realtime table so I am adding a 'upload seconds' SQL statement set to 60 seconds to do the insert, then adding a 'upload minute' SQL set to 1 minute to do the 'retention' deletion. Maybe they could be combined as semicolon separated commands?

    **(we really need simpler names to refer to these entries in the config!)

    User avatar
    mcrossley
    Posts: 5815
    Joined: Thu 07 Jan 2010 9:44 pm
    Weather Station: Davis VP2
    Operating System: Stretch Lite rPi
    Location: Wilmslow, Cheshire, UK
    Contact:

    Re: MySQL - Custom uploads

    Post by mcrossley » Sun 24 May 2015 1:43 pm

    The "upload seconds" does not appear to 'clock aligned', but the "upload minutes does. Is that intentional?

    User avatar
    mcrossley
    Posts: 5815
    Joined: Thu 07 Jan 2010 9:44 pm
    Weather Station: Davis VP2
    Operating System: Stretch Lite rPi
    Location: Wilmslow, Cheshire, UK
    Contact:

    Re: MySQL - Custom uploads

    Post by mcrossley » Sun 24 May 2015 1:45 pm

    Oh, one more :lol:

    Are the SQL uploads triggered during the catch-up processing at Cumulus startup?

    User avatar
    steve
    Cumulus Author
    Posts: 26717
    Joined: Mon 02 Jun 2008 6:49 pm
    Weather Station: None
    Operating System: None
    Location: Vienne, France
    Contact:

    Re: MySQL - Custom uploads

    Post by steve » Sun 24 May 2015 2:00 pm

    mcrossley wrote:Observations:
    The processed SQL is not logged in the diag files for "Custom upload seconds"**, in fact nothing seems to get logged unless there is an error.
    It would be nice for debugging to see the processed SQL statement, and normal logging to see "X rows affected".
    That was intentional, because of the amount of logging that would result.
    The "Custom upload minutes" SQL statement does get logged, but again, not the results.
    I think that it always says '0 rows affected' so I took it out, but I'll check when I get chance. Does the monthly one say '1 rows were affected'? Edit: Yes it does, so I could put the number of rows affected message back in for the custom one.
    Could the entry 'line' be a box, the SQL tends to be very long on one line and difficult to read/edit.
    Yes, I can probably do that. Actually, until I get around to it, you can change it for yourself: Edit the MySqlOptions.json file in the interface/json directory. Do something like this (i.e. add the "type" setting for the "command"):

    Code: Select all

    "customseconds": {
                "collapsed": true,
                "helper": "A custom SQL command to be executed at an interval in seconds - can include webtags",			
                "fields": {
                    "enabled": {
                        "rightLabel": "Enabled"
                    },
                    "command": {
                        "type": "textarea"
                   }
                }
            },
    Q: Can you put multiple statements separated with semicolons?

    I have an extra column on my realtime table so I am adding a 'upload seconds' SQL statement set to 60 seconds to do the insert, then adding a 'upload minute' SQL set to 1 minute to do the 'retention' deletion. Maybe they could be combined as semicolon separated commands?
    I had assumed/hoped that would be the case, but I haven't tried it.
    The "upload seconds" does not appear to 'clock aligned', but the "upload minutes does. Is that intentional?
    Yes, it's the same as reatime.txt upload etc, it just starts a timer. Trying to align to the clock would be difficult and may not come out exactly on the right second anyway.
    Are the SQL uploads triggered during the catch-up processing at Cumulus startup?
    Yes, of course :) For the 'standard' ones.
    Steve

    User avatar
    mcrossley
    Posts: 5815
    Joined: Thu 07 Jan 2010 9:44 pm
    Weather Station: Davis VP2
    Operating System: Stretch Lite rPi
    Location: Wilmslow, Cheshire, UK
    Contact:

    Re: MySQL - Custom uploads

    Post by mcrossley » Sun 24 May 2015 2:54 pm

    steve wrote:
    mcrossley wrote:Observations:
    The processed SQL is not logged in the diag files for "Custom upload seconds"**, in fact nothing seems to get logged unless there is an error.
    It would be nice for debugging to see the processed SQL statement, and normal logging to see "X rows affected".
    That was intentional, because of the amount of logging that would result.
    OK.
    steve wrote:
    The "Custom upload minutes" SQL statement does get logged, but again, not the results.
    I think that it always says '0 rows affected' so I took it out, but I'll check when I get chance. Does the monthly one say '1 rows were affected'? Edit: Yes it does, so I could put the number of rows affected message back in for the custom one.
    OK
    steve wrote:
    Could the entry 'line' be a box, the SQL tends to be very long on one line and difficult to read/edit.
    Yes, I can probably do that. Actually, until I get around to it, you can change it for yourself: Edit the MySqlOptions.json file in the interface/json directory. Do something like this (i.e. add the "type" setting for the "command"):

    Code: Select all

    "customseconds": {
                "collapsed": true,
                "helper": "A custom SQL command to be executed at an interval in seconds - can include webtags",			
                "fields": {
                    "enabled": {
                        "rightLabel": "Enabled"
                    },
                    "command": {
                        "type": "textarea"
                   }
                }
            },
    Done, and for custom minutes - works a treat.
    steve wrote:
    Q: Can you put multiple statements separated with semicolons?

    I have an extra column on my realtime table so I am adding a 'upload seconds' SQL statement set to 60 seconds to do the insert, then adding a 'upload minute' SQL set to 1 minute to do the 'retention' deletion. Maybe they could be combined as semicolon separated commands?
    I had assumed/hoped that would be the case, but I haven't tried it.
    I've moved both my statements into the custom minute section, the insert is which is teh first statement is running OK, but the delete will need another day to catch up I think - no errors being logged. I see the 'cutom minute' process logs both the raw and processed SQL :)
    steve wrote:
    The "upload seconds" does not appear to 'clock aligned', but the "upload minutes does. Is that intentional?
    Yes, it's the same as reatime.txt upload etc, it just starts a timer. Trying to align to the clock would be difficult and may not come out exactly on the right second anyway.
    OK.
    steve wrote:
    Are the SQL uploads triggered during the catch-up processing at Cumulus startup?
    Yes, of course :) For the 'standard' ones.
    Ah, I was hoping the realtime would get uploaded at the 'logger' interval for catch-ups. But not now I have a customised version. No chance of the 'standard' SQL being read from an ini file is there?

    User avatar
    mcrossley
    Posts: 5815
    Joined: Thu 07 Jan 2010 9:44 pm
    Weather Station: Davis VP2
    Operating System: Stretch Lite rPi
    Location: Wilmslow, Cheshire, UK
    Contact:

    Re: MySQL - Custom uploads

    Post by mcrossley » Sun 24 May 2015 2:54 pm

    PS: Thanks very much for this functionality - I think it is a great enhancement to Cumulus :clap: :clap: :clap:

    User avatar
    steve
    Cumulus Author
    Posts: 26717
    Joined: Mon 02 Jun 2008 6:49 pm
    Weather Station: None
    Operating System: None
    Location: Vienne, France
    Contact:

    Re: MySQL - Custom uploads

    Post by steve » Sun 24 May 2015 3:28 pm

    mcrossley wrote:I see the 'cutom minute' process logs both the raw and processed SQL :)
    leftover debugging code, I'll take the 'raw' one out.
    No chance of the 'standard' SQL being read from an ini file is there?
    To get custom ones done during logger download, you mean? I suppose instead there could be options to execute the custom ones during logger download. I'm just not sure that all web tags work during logger download. As they are never needed until live running, they may not necessarily. They probably are, it's just not something I've done consciously. Simple live data is probably OK, it's things like trends that might not be.
    Steve

    User avatar
    mcrossley
    Posts: 5815
    Joined: Thu 07 Jan 2010 9:44 pm
    Weather Station: Davis VP2
    Operating System: Stretch Lite rPi
    Location: Wilmslow, Cheshire, UK
    Contact:

    Re: MySQL - Custom uploads

    Post by mcrossley » Sun 24 May 2015 3:39 pm

    I just add #IsSunUp to the real-time table. I guess that should be available?

    User avatar
    steve
    Cumulus Author
    Posts: 26717
    Joined: Mon 02 Jun 2008 6:49 pm
    Weather Station: None
    Operating System: None
    Location: Vienne, France
    Contact:

    Re: MySQL - Custom uploads

    Post by steve » Sun 24 May 2015 3:45 pm

    No, because it won't be able to take into account the logger time during the logger download. It will give a result based on the current time. Now that web tags have this new use, I'll have to look at getting them all to work during logger download. The same thing is going to apply to the custom http uploads, in cases where people are going to be using those for storing data.
    Steve

    User avatar
    mcrossley
    Posts: 5815
    Joined: Thu 07 Jan 2010 9:44 pm
    Weather Station: Davis VP2
    Operating System: Stretch Lite rPi
    Location: Wilmslow, Cheshire, UK
    Contact:

    Re: MySQL - Custom uploads

    Post by mcrossley » Sun 24 May 2015 4:10 pm

    OK, not to worry, I don't have the luxury of that functionality with Cumulus now, so nothing lost. If you add this ability in the future to CMX, great.

    User avatar
    mcrossley
    Posts: 5815
    Joined: Thu 07 Jan 2010 9:44 pm
    Weather Station: Davis VP2
    Operating System: Stretch Lite rPi
    Location: Wilmslow, Cheshire, UK
    Contact:

    Re: MySQL - Custom uploads

    Post by mcrossley » Sun 24 May 2015 7:51 pm

    Just in case anyone was wondering what I was doing (ha! :roll:), here is what I have configured in the Cumulus "update minutes" config.

    Code: Select all

    INSERT IGNORE INTO realtime (LogDateTime,temp,hum,dew,wspeed,wlatest,bearing,rrate,rfall,press,currentwdir,beaufortnumber,windunit,tempunitnodeg,pressunit,rainunit,windrun,presstrendval,rmonth,ryear,rfallY,intemp,inhum,wchill,temptrend,tempTH,TtempTH,tempTL,TtempTL,windTM,TwindTM,wgustTM,TwgustTM,pressTH,TpressTH,pressTL,TpressTL,version,build,wgust,heatindex,humidex,UV,ET,SolarRad,avgbearing,rhour,forecastnumber,isdaylight,SensorContactLost,wdir,cloudbasevalue,cloudbaseunit,apptemp,SunshineHours,CurrentSolarMax,IsSunny,IsSunUp) VALUES ('<#date format=yyyy-MM-dd> <#time format=HH:mm:ss>',<#temp>,<#hum>,<#dew>,<#wspeed>,<#wlatest>,<#bearing>,<#rrate>,<#rfall>,<#press>,'<#currentwdir>',<#beaufortnumber>,'<#windunit>','<#tempunitnodeg>','<#pressunit>','<#rainunit>','<#windrun>',<#presstrendval>,<#rmonth>,<#ryear>,<#rfallY>,<#intemp>,<#inhum>,<#wchill>,'<#temptrend>',<#tempTH>,'<#TtempTH>',<#tempTL>,'<#TtempTL>',<#windTM>,'<#TwindTM>',<#wgustTM>,'<#TwgustTM>',<#pressTH>,'<#TpressTH>',<#pressTL>,'<#TpressTL>','<#version>','<#build>',<#wgust>,<#heatindex>,<#humidex>,<#UV>,<#ET>,<#SolarRad>,<#avgbearing>,<#rhour>,<#forecastnumber>,<#isdaylight>,<#SensorContactLost>,'<#wdir>',<#cloudbasevalue>,'<#cloudbaseunit>',<#apptemp>,<#SunshineHours>,<#CurrentSolarMax>,<#IsSunny>,<#IsSunUp>);
    SELECT @latest:=MAX(LogDateTime) FROM realtime;
    DELETE IGNORE FROM realtime WHERE LogDateTime < DATE_SUB(@latest, INTERVAL 7 DAY);
    
    The messing around with a variable for the delete is so that I don't leave the table with no data in it - or only one row. If I didn't do that then I would use NOW() and I would have to set the TZ to UK as the MySQL server is in a different TZ from the station - that had me head scratching for a while until I remembered the TZ thing. My old server was in the UK TZ so it wasn't a an issue.

    Adrian Hudson
    Posts: 216
    Joined: Mon 03 Jan 2011 4:27 pm
    Weather Station: Davis Vantage Pro2
    Operating System: Win 7
    Location: Willand, mid Devon.
    Contact:

    Re: MySQL - Custom uploads

    Post by Adrian Hudson » Sun 24 May 2015 9:51 pm

    mcrossley wrote:PS: Thanks very much for this functionality - I think it is a great enhancement to Cumulus :clap: :clap: :clap:
    I would like to second that!!
    steve wrote:... Now that web tags have this new use, I'll have to look at getting them all to work during logger download. The same thing is going to apply to the custom http uploads, in cases where people are going to be using those for storing data.
    Yes please!! :roll: :roll:

    Oh, one minor typo in your error messages: "Error encountered during costm seconds MySQL operation." I :oops: to mention that I spent a few minutes wondering what a costm seconds SQL operation was, I even considered reading the manual!

    Post Reply