Welcome to the new home of the Cumulus Support forum.

Latest Cumulus release v1.9.4 (build 1099) - 28 November 2014
Latest Cumulus MX release v3.0.0 build 3046 - 02 January 2019. See the Wiki for download

CumulusMX MySQL DB

Discussion of version 3 of Cumulus, which runs on Windows, Linux, and OS X. All Cumulus MX queries in here, please.

Moderator: mcrossley

Post Reply
User avatar
PaulMy
Posts: 1749
Joined: Sun 28 Sep 2008 11:54 pm
Weather Station: Davis Vantage Pro 2+ w/Envoy USB
Operating System: Windows-8
Location: Komoka, ON Canada
Contact:

CumulusMX MySQL DB

Post by PaulMy » Sat 01 Dec 2018 7:23 pm

I have an existing DB which include Monthly, Dayfile and realtime tables and these are being updated from my Cumulus 1.

Now would like to also have CumulusMX update MySQL and as I understand these tables have the default Monthly, Dayfile and Realtime viewtopic.php?f=27&t=12908&start=15#p107639
Can I just set the table names in CumulusMX settings as MonthlyMX, DayfileMX and RealtimeMX to avoid overwriting the existing tables?


Thanks,
Paul

User avatar
mcrossley
Posts: 5314
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: CumulusMX MySQL DB

Post by mcrossley » Sun 02 Dec 2018 12:41 pm

Yes, that should work - you will need to run each pf the "create tables" buttons after saving the new table names.

What I would do though (if you want to preserve your Cumulus 1 data into MX) is to "copy" your existing tables including the data to the new table names. Then all you need to do is amend the table names in MX. It will then append to your historic data in the copied tables

User avatar
PaulMy
Posts: 1749
Joined: Sun 28 Sep 2008 11:54 pm
Weather Station: Davis Vantage Pro 2+ w/Envoy USB
Operating System: Windows-8
Location: Komoka, ON Canada
Contact:

Re: CumulusMX MySQL DB

Post by PaulMy » Sun 02 Dec 2018 8:12 pm

I have tried to create these new tables and after CumulusMX indicates 'Attempting create' then quickly a 'parsererror' and MXdiags shows

Code: Select all

2018-12-02 14:36:05.329 CREATE TABLE MonthlyMX (LogDateTime DATETIME NOT NULL,Temp decimal(4,1) NOT NULL,Humidity decimal(4,0) NOT NULL,Dewpoint decimal(4,1) NOT NULL,Windspeed decimal(4,1) NOT NULL,Windgust decimal(4,1) NOT NULL,Windbearing VARCHAR(3) NOT NULL,RainRate decimal(4,1) NOT NULL,TodayRainSoFar decimal(4,1) NOT NULL,Pressure decimal(6,2) NOT NULL,Raincounter decimal(6,1) NOT NULL,InsideTemp decimal(4,1) NOT NULL,InsideHumidity decimal(4,0) NOT NULL,LatestWindGust decimal(5,1) NOT NULL,WindChill decimal(4,1) NOT NULL,HeatIndex decimal(4,1) NOT NULL,UVindex decimal(4,1),SolarRad decimal(5,1),Evapotrans decimal(4,1),AnnualEvapTran decimal(5,1),ApparentTemp decimal(4,1),MaxSolarRad decimal(5,1),HrsSunShine decimal(3,1),CurrWindBearing varchar(3),RG11rain decimal(4,1),RainSinceMidnight decimal(4,1), WindbearingSym varchar(3),CurrWindBearingSym varchar(3),PRIMARY KEY (LogDateTime)) COMMENT = "Monthly logs from Cumulus"
2018-12-02 14:36:06.424 Error encountered during MySQL operation.
2018-12-02 14:36:06.425 Can't connect to MySQL server on 'localhost' (10061):
No connection could be made because the target machine actively refused it 127.0.0.1:3306
I am already successfully doing a couple of ImportCumulusFile.php from my Cumulus 1 files and have used the settings from this in CumulusMX MySQL settings Server details:
Host name: changed default '127.0.0.1' to 'localhost' which is the same as in the working ImportCumulusFile.php
Port: using the default '3306'
User name: the same as in the working ImportCumulusFile.php
Password: the same as in the working ImportCumulusFile.php
Database: the same as in the working ImportCumulusFile.php
I have stopped CumulusMX after saving the settings and then restart and try to create..

I note that the the MXDiags on the last 2 lines shown above refer to 'local'host' and then on next line also 'because the target machine actively refused it 127.0.0.1:3306'
When I go to my MySQL webserver database where the existing tables are and select Monthly in the top header it shows as string -
Server: localhost:3306 >>Database: xxxx >>Table: Monthly "Monthly log"
the Database: xxxx shows the actual database name as in the ImportCumulusFile.php and CumulusMX settings.

I can't see it but could I have some settings wrong?

Thanks,
Paul

User avatar
mcrossley
Posts: 5314
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: CumulusMX MySQL DB

Post by mcrossley » Sun 02 Dec 2018 8:39 pm

Localhost will resolve to 127.0.0.1 so that is nothing to worry about.

Is CMX actually running on the same server as the database?

User avatar
PaulMy
Posts: 1749
Joined: Sun 28 Sep 2008 11:54 pm
Weather Station: Davis Vantage Pro 2+ w/Envoy USB
Operating System: Windows-8
Location: Komoka, ON Canada
Contact:

Re: CumulusMX MySQL DB

Post by PaulMy » Sun 02 Dec 2018 8:42 pm

No, my DB is remote on my GoDaddy webserver.

Paul

User avatar
mcrossley
Posts: 5314
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: CumulusMX MySQL DB

Post by mcrossley » Sun 02 Dec 2018 8:52 pm

Ah, so you will have to use your host name rather than localhost. Same as FTP.
You may find that you also have to add your home IP to the allowed remote access list on the DB.

bigmac
Posts: 161
Joined: Wed 13 May 2009 9:35 am
Weather Station: Davis Vantage Pro 2 Wireless
Operating System: Raspbian wheezy
Location: Nr Ilkley, West Yorks, UK
Contact:

Re: CumulusMX MySQL DB

Post by bigmac » Sat 12 Jan 2019 9:06 am

I have just moved hosts for my website and as part of the process decided I would try and get CumulusMX to do all the MYSQL stuff as well.
However, it appears to be failing when trying to update the MYSQL table. I originally had a problem connecting which seems to be resolved after discussion with the host, but now in MXDIAG I get this message:
2019-01-12 00:00:00.344 Dayfile.txt opened for writing
2019-01-12 00:00:00.345 Writing entry to dayfile.txt
2019-01-12 00:00:00.358 INSERT IGNORE INTO Dayfile (LogDate,HighWindGust,HWindGBear,THWindG,MinTemp,TMinTemp,MaxTemp,TMaxTemp,MinPress,TMinPress,MaxPress,TMaxPress,MaxRainRate,TMaxRR,TotRainFall,AvgTemp,TotWindRun,HighAvgWSpeed,THAvgWSpeed,LowHum,TLowHum,HighHum,THighHum,TotalEvap,HoursSun,HighHeatInd,THighHeatInd,HighAppTemp,THighAppTemp,LowAppTemp,TLowAppTemp,HighHourRain,THighHourRain,LowWindChill,TLowWindChill,HighDewPoint,THighDewPoint,LowDewPoint,TLowDewPoint,DomWindDir,HeatDegDays,CoolDegDays,HighSolarRad,THighSolarRad,HighUV,THighUV,HWindGBearSym,DomWindDirSym) Values('19-01-11',17.0,262,'23:36',2.7,'08:02',8.3,'23:43',1024.92,'23:46',1031.83,'10:55',0.0,'00:00',0.0,5.9,31.0,6.0,'21:08',84,'00:04',96,'08:47',0.25,0.0,8.3,'23:43',6.9,'23:23',1.0,'08:02',0.0,'00:00',2.7,'08:02',6.6,'23:50',1.8,'08:02',258,12.4,0.0,128,'11:54',0.0,'00:00','W','WSW')
2019-01-12 00:00:01.014 Error encountered during MySQL operation.
2019-01-12 00:00:01.014 Unknown column 'LowHum' in 'field list'
2019-01-12 00:00:01.025 Raincounter = 1.2 Raindaystart = 1.2
I don't have a LowHum column (in addition to some others as well) in the MySQL dayfile table, so is it erroring because of this and why does it not just ignore it if it isn't there?
Thanks.

User avatar
mcrossley
Posts: 5314
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: CumulusMX MySQL DB

Post by mcrossley » Sat 12 Jan 2019 10:39 am

How did you create the tables? Did you let Cumulus do it?

SQL will always fail if there is a mismatch between the table structure and the statement.

bigmac
Posts: 161
Joined: Wed 13 May 2009 9:35 am
Weather Station: Davis Vantage Pro 2 Wireless
Operating System: Raspbian wheezy
Location: Nr Ilkley, West Yorks, UK
Contact:

Re: CumulusMX MySQL DB

Post by bigmac » Sat 12 Jan 2019 5:04 pm

Thanks Mark. I created the table when I first used the ImportCumulusFile script of Cumulus1 created by DAJ and then continued to use the same table when I upgraded to CumulusMX. I never got round to upgrading to your version 2 of the script :(
Is it because CumulusMX effectively uses version 2 of ImportCumulusFile which has a more detailed table structure? If so, do I just allow CumulusMX to recreate the table and will it populate with all the old data correctly or do I need to do something clever myself?

User avatar
mcrossley
Posts: 5314
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: CumulusMX MySQL DB

Post by mcrossley » Sat 12 Jan 2019 5:15 pm

In the SQL settings page for MX there are buttons to create the databases, they will create the tables with the correct structure. I haven't compared the structure, but MX works with my tables that were created with v2 of the import script.

If you already have data in your tables then you could amend the structure in something like phpMyAdmin to add the missing columns.

bigmac
Posts: 161
Joined: Wed 13 May 2009 9:35 am
Weather Station: Davis Vantage Pro 2 Wireless
Operating System: Raspbian wheezy
Location: Nr Ilkley, West Yorks, UK
Contact:

Re: CumulusMX MySQL DB

Post by bigmac » Sat 12 Jan 2019 6:01 pm

Thanks again Mark. I have created a new table with all the new column names and I think I have managed to copy all the data over from the old table. For the new columns there was obviously no corresponding data so when I view it in phpMyAdmin, it just shows "null" for the missing data. I presume this is ok?
For my last question, (which might be obvious to most, but not me!) can I force an update of the SQL table now rather than waiting for rollover to do it automatically?

User avatar
mcrossley
Posts: 5314
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: CumulusMX MySQL DB

Post by mcrossley » Sat 12 Jan 2019 8:13 pm

Nulls are ok.

MX will add a row to the monthly table at each archive interval, and the day table at the end of day rollover. Not sure what you want to force, today's day entry will not be available until the end of day.

If you want to catch up some missing data, then you can still use the import script to "fill in the blanks", the MX table should be compatible with the V2 script.

bigmac
Posts: 161
Joined: Wed 13 May 2009 9:35 am
Weather Station: Davis Vantage Pro 2 Wireless
Operating System: Raspbian wheezy
Location: Nr Ilkley, West Yorks, UK
Contact:

Re: CumulusMX MySQL DB

Post by bigmac » Sun 13 Jan 2019 5:06 pm

Thanks again Mark, much appreciated. That has got the upload working at midnight, so I got yesterdays info entered into the table.
Last problem seems to be updating the missing data (since mid-November 2018). The script fails with a "500 Internal Server Error" so obviously some miconfiguration somewhere.
This is the config line I am using: http://www.menstonweather.co.uk/ImportC ... ayfile.txt and I have double & triple checked the config in the script to make sure the correct db user etc is the same as that configured in the CumulusMX setup.
Obviously something is wrong, but what?
Is it likely to be a permissions thing?

User avatar
mcrossley
Posts: 5314
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: CumulusMX MySQL DB

Post by mcrossley » Sun 13 Jan 2019 5:50 pm

Try using version 2.8 of the script, there is an issue with version 3 of the script for some people that I haven't had time to investigate yet.

bigmac
Posts: 161
Joined: Wed 13 May 2009 9:35 am
Weather Station: Davis Vantage Pro 2 Wireless
Operating System: Raspbian wheezy
Location: Nr Ilkley, West Yorks, UK
Contact:

Re: CumulusMX MySQL DB

Post by bigmac » Sun 13 Jan 2019 6:06 pm

And it was as simple as that! Done!
Thank you again for your patience Mark!

Post Reply