Welcome to the Cumulus Support forum.

Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024

Cumulus MX V4 beta test release 4.0.0 (build 4017) - 17 March 2024

Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)

Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki

MySQL - Custom uploads

Topics about the Beta trials up to Build 3043, the last build by Cumulus's founder Steve Loft. It was by this time way out of Beta but Steve wanted to keep it that way until he made a decision on his and Cumulus's future.

Moderator: mcrossley

Locked
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

MySQL - Custom uploads

Post by mcrossley »

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: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL - Custom uploads

Post by mcrossley »

The "upload seconds" does not appear to 'clock aligned', but the "upload minutes does. Is that intentional?
User avatar
mcrossley
Posts: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL - Custom uploads

Post by mcrossley »

Oh, one more :lol:

Are the SQL uploads triggered during the catch-up processing at Cumulus startup?
User avatar
steve
Cumulus Author
Posts: 26702
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 »

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: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL - Custom uploads

Post by mcrossley »

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: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL - Custom uploads

Post by mcrossley »

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: 26702
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 »

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: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL - Custom uploads

Post by mcrossley »

I just add #IsSunUp to the real-time table. I guess that should be available?
User avatar
steve
Cumulus Author
Posts: 26702
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 »

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: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL - Custom uploads

Post by mcrossley »

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: 12689
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2/WLL
Operating System: Bullseye Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: MySQL - Custom uploads

Post by mcrossley »

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: 220
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 »

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!
Locked