MySQL problems

[griffo42]

Hi

I am running CumulusMX version b3148. My website is: kstwx.net/cumx/index.htm.

freddie wrote: ↑
Mon Sep 27, 2021 4:22 pm
Have you looked at your user table in mysql to check that user kstwx197 is permitted to access the database from the host on which MX is running? In the user table you have columns Host and User. You should have a row where your kstwx197 user is listed against the IP address or hostname of the MX host. If this row is present then you need to look at whether the user has been granted insert privileges on the Cumulus tables.

I have looked at all of the above suggestions and look OK to me given that the RealtimeTable is being updated every minute or so. This seems to be too often to me.

The wiki area that I looked at seems to indicate that I can change the MySQL settings from the settings pages of the settings dropdown in the dashboard: I can't find that. The only thing that I can find which relates to MySQL is a page in which the 3 tables can be created. In relation to my MySQL issues (see below), these are the MySQL entries in my cumulus.ini file:

[MySQL]
Host=xxxx
Port=xxxx
User=xxxx
Pass=xxxx
Database=xxxx
MonthlyMySqlEnabled=1
RealtimeMySqlEnabled=1
DayfileMySqlEnabled=1
MonthlyTable=monthlycumx
DayfileTable=dayfilecumx
RealtimeTable=realtimecumx
RealtimeRetention=7Days
CustomMySqlSecondsCommandString=
CustomMySqlMinutesCommandString=
CustomMySqlRolloverCommandString=
CustomMySqlSecondsEnabled=0
CustomMySqlMinutesEnabled=0
CustomMySqlRolloverEnabled=0
CustomMySqlSecondsInterval=10
CustomMySqlMinutesIntervalIndex=6
RealtimeMySql1MinLimit=1
UpdateOnEdit=1
BufferOnFailure=0

With the above settings, these are an example of the errors that are reported in my MXDiags file every 5 minutes:

2021-10-02 17:20:02.898 DoLogFile: Error encountered during MySQL operation = Unknown column 'Humidex' in 'field list'
2021-10-02 17:20:12.826 Realtime[140]: Error encountered during MySQL operation = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
2021-10-02 17:21:13.846 Realtime[142]: Error encountered during MySQL operation = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
2021-10-02 17:22:13.788 Realtime[144]: Error encountered during MySQL operation = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
2021-10-02 17:23:13.909 Realtime[146]: Error encountered during MySQL operation = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

Advice as to how fix the above errors and lengthen the update schedule as well as any other advice and/or recommended additions to the cumulus.ini entry cited above will be much appreciated.

best wishes
Keith

[freddie]

The errors you list are totally different to the errors you reported last week, which threw me for a bit. You must've fixed the earlier error as there is no way you could get your current errors without addressing the previous ones.

MySQL settings are in the interface under Settings->MySQL settings. I wouldn't go hacking around in Cumulus.ini unless you're sure you know what you're doing.

The Realtime table is there for real-time data, so a frequent update is to be expected - mine updates every two seconds.
The Monthly table is where you record periodic data - most use an update interval of 5-10 minutes on this table.
The Dayfile table is where you store data that summarises a 24-hour period.

Your particular error is indicating that there is a missing column in your database table - Humidex. I would address this error first before adjusting any settings.

[freddie]

I've just taken a look at your web site and notice that you have data going back 10 years. There was an update to MX in August 2020 that introduced Humidex - which meant extra columns in monthly files and dayfile, and extra columns to add to database tables. This thread contains some discussion on the change.

[mcrossley]

The changes were...

Dayfile table:
- MaxHumidex - decimal(5,1)
- TMaxHumidex - varchar(5)

Monthly table:
- Humidex - decimal(5,1)

[griffo42]

Thanks Freddie and Mark for your replies. All is good now after having deleted the 3 tables that I had previously installed in my database and started again. I am now getting no errors in my MXDiags file.

Could I indulge you with a related problem? I am unable to find the correct syntax to use to have your (Mark) MySQL utility to populate the tables with my historical data. I am running Win10 home. There is no exe/bat/etc file in the download to run.

Thanks

Keith

[freddie]

You need to download the ExportToMySql utility from https://github.com/cumulusmx/ExportToMy ... v1.3.0.zip

The utility should be placed in your MX installation location.

Example syntax to use can be found at https://github.com/cumulusmx/ExportToMy ... /README.md

[jlmr731]

First did you make a backup of your table before you deleted them? if so you can import them into your new tables.

You can also use Marks export to mysql utility found here https://cumuluswiki.org/a/Software#ExportToMySQL and this next link should help you run the utility https://github.com/cumulusmx/ExportToMy ... /README.md

I was sniped by freddie

[griffo42]

Thanks @freddie and @jlmr731.

Thanks for directing me to those 2 posts. I had read them and tried many combinations to ge them to run with no result. I have tried the "run" command and also the command prompt to follow this instruction - "The utility must be run from your Cumulus MX root folder (the folder that holds your CumulusMX.exe file).". The results from attempting to do so are:

Screenshot 2021-10-05 134135.jpg

Thanks for any help you can provide.

Keith

[ConligWX]

did you extract the files from the zip archive?

you should have two files within the zip.

ExportToMySQL.exe
ExportToMySQL.exe.config

[griffo42]

Thanks to all who advised me on this problem. All good.

Keith