Setting up MySQL logging

[cvzyl]

I set up a MySQL server on the Pi running CMX. I successfully connected to the database I created and created the tables (Monthly, Dayfile and Realtime) using the buttons at the bottom. I enabled monthly, realtime and dayfile uploads. It's been running for about an hour now but the realtime table is still empty. At what interval should it log? Under custom upload (seconds, minutes and hours), should I enable the tickboxes and what should the SQL command be?

Would be glad if someone can point me to a resource where I can get info on setting this up.

Thanks
Cobus

[steve]

The realtime SQL uploads happen at whatever realtime interval you have configured. Have you enabled realtime? You don't need any of the custom settings, those are for custom SQL uploads, not the built in ones that you are trying to use. Are the monthly data uploads working?

Instructions for the SQL facility are in the mx release announcement for the build where I introduced the facility, it should be fairly easy to find.

My internet connection is quite poor where I am at the moment, so it is hard for me to help you much at the moment.

[cvzyl]

Thanks for the reply steve.

I have switched on the tickbox under Realtime.txt upload to enable the function. There is no time period configuration there, only "Retention string".
If I run SELECT * from Realtime; in mysql I get an empty set.

I will try find the description in the release post.

[steve]

There is definitely a realtime update period setting in the internet settings, along with the setting to enable realtime. You don't need realtime.txt upload for this, that's nothing to do with SQL updates.

You didn't answer my question about whether the monthly data SQL update is working. If not, if you turn on debug logging, those SQL updates are logged to the diags file, which may help to explain why you are having a problem getting it working.

[cvzyl]

steve

Yes, when I select * from Monthly it seems like there is data in there, one line every 10 minutes.

Here is a screenshot of the settings I enabled, you will see that Monthly, Realtime and Dayfile are all selected but only Monthly has data in it.



There are 3 more upload options but they are all custom - second, minute and hourly interval.

[steve]

But do you have realtime enabled in the Internet settings? Without that, no realtime updates of any kind will occur.

Note that many settings in MX currently require a restart to take effect.

[cvzyl]

Thanks steve, now I understand. Realtime is enabled under Internet settings > Web/FTP settings and not under the MySQL settings.
I found it and enabled the setting, now the Realtime table is being populated.

Two more questions:
1. How long is data kept in the Realtime table? My Retention string is 1 DAY, does that mean it will keep data for the past 24 hours? If I delete the retention string what will happen?
2. When is the Dayfile table updated and is there a setting I have to enable to do that?

Thanks
Cobus

[steve]

1. How long is data kept in the Realtime table? My Retention string is 1 DAY, does that mean it will keep data for the past 24 hours?

Yes

If I delete the retention string what will happen?

It will not delete any of the data - I think. It wasn't designed to retain all of the realtime updates, so I would have to look at the code, and I can't do that right now.

2. When is the Dayfile table updated

When dayfile.txt is updated, I.e at the end of the day.

and is there a setting I have to enable to do that?

No, because doing end of day rollover actions is not an option.