Cumulus Support

Posted: **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!)

Posted: **Sun 24 May 2015 1:43 pm**

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

Posted: **Sun 24 May 2015 1:45 pm**

Oh, one more

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

Posted: **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.

Posted: **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?

Posted: **Sun 24 May 2015 2:54 pm**

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

Posted: **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.

Posted: **Sun 24 May 2015 3:39 pm**

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

Posted: **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.

Posted: **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.

Posted: **Sun 24 May 2015 7:51 pm**

Just in case anyone was wondering what I was doing (ha!

), 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.

Posted: **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

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

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!

Cumulus Support

MySQL - Custom uploads

MySQL - Custom uploads

Re: MySQL - Custom uploads

Re: MySQL - Custom uploads

Re: MySQL - Custom uploads

Re: MySQL - Custom uploads

Re: MySQL - Custom uploads

Re: MySQL - Custom uploads

Re: MySQL - Custom uploads

Re: MySQL - Custom uploads

Re: MySQL - Custom uploads

Re: MySQL - Custom uploads

Re: MySQL - Custom uploads