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 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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
Moderator: mcrossley
- mcrossley
- Posts: 12763
- 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
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!)
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!)
- mcrossley
- Posts: 12763
- 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
The "upload seconds" does not appear to 'clock aligned', but the "upload minutes does. Is that intentional?
- mcrossley
- Posts: 12763
- 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
Oh, one more
Are the SQL uploads triggered during the catch-up processing at Cumulus startup?
Are the SQL uploads triggered during the catch-up processing at Cumulus startup?
- steve
- Cumulus Author
- Posts: 26701
- Joined: Mon 02 Jun 2008 6:49 pm
- Weather Station: None
- Operating System: None
- Location: Vienne, France
- Contact:
Re: MySQL - Custom uploads
That was intentional, because of the amount of logging that would result.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".
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.The "Custom upload minutes" SQL statement does get logged, but again, not the results.
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"):Could the entry 'line' be a box, the SQL tends to be very long on one line and difficult to read/edit.
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"
}
}
},
I had assumed/hoped that would be the case, but I haven't tried it.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?
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.The "upload seconds" does not appear to 'clock aligned', but the "upload minutes does. Is that intentional?
Yes, of course For the 'standard' ones.Are the SQL uploads triggered during the catch-up processing at Cumulus startup?
Steve
- mcrossley
- Posts: 12763
- 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
OK.steve wrote:That was intentional, because of the amount of logging that would result.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".
OKsteve wrote: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.The "Custom upload minutes" SQL statement does get logged, but again, not the results.
Done, and for custom minutes - works a treat.steve wrote: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"):Could the entry 'line' be a box, the SQL tends to be very long on one line and difficult to read/edit.
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" } } },
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 SQLsteve wrote:I had assumed/hoped that would be the case, but I haven't tried it.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?
OK.steve wrote: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.The "upload seconds" does not appear to 'clock aligned', but the "upload minutes does. Is that intentional?
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?steve wrote:Yes, of course For the 'standard' ones.Are the SQL uploads triggered during the catch-up processing at Cumulus startup?
- mcrossley
- Posts: 12763
- 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
PS: Thanks very much for this functionality - I think it is a great enhancement to Cumulus
- steve
- Cumulus Author
- Posts: 26701
- Joined: Mon 02 Jun 2008 6:49 pm
- Weather Station: None
- Operating System: None
- Location: Vienne, France
- Contact:
Re: MySQL - Custom uploads
leftover debugging code, I'll take the 'raw' one out.mcrossley wrote:I see the 'cutom minute' process logs both the raw and processed SQL
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.No chance of the 'standard' SQL being read from an ini file is there?
Steve
- mcrossley
- Posts: 12763
- 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
I just add #IsSunUp to the real-time table. I guess that should be available?
- steve
- Cumulus Author
- Posts: 26701
- Joined: Mon 02 Jun 2008 6:49 pm
- Weather Station: None
- Operating System: None
- Location: Vienne, France
- Contact:
Re: MySQL - Custom uploads
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
- mcrossley
- Posts: 12763
- 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
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.
- mcrossley
- Posts: 12763
- 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
Just in case anyone was wondering what I was doing (ha! ), here is what I have configured in the Cumulus "update minutes" config.
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.
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);
-
- 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
I would like to second that!!mcrossley wrote:PS: Thanks very much for this functionality - I think it is a great enhancement to Cumulus
Yes please!!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.
Oh, one minor typo in your error messages: "Error encountered during costm seconds MySQL operation." I to mention that I spent a few minutes wondering what a costm seconds SQL operation was, I even considered reading the manual!