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 4018) - 28 March 2024
Legacy Cumulus 1 release v1.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 4018) - 28 March 2024
Legacy Cumulus 1 release v1.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
CumulusMX MySQL DB
Moderator: mcrossley
- PaulMy
- Posts: 3778
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
CumulusMX MySQL DB
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
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
Davis Vantage Pro2+
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
- mcrossley
- Posts: 12697
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: CumulusMX MySQL DB
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
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
- PaulMy
- Posts: 3778
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
Re: CumulusMX MySQL DB
I have tried to create these new tables and after CumulusMX indicates 'Attempting create' then quickly a 'parsererror' and MXdiags shows
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
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
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
Davis Vantage Pro2+
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
- mcrossley
- Posts: 12697
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: CumulusMX MySQL DB
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?
Is CMX actually running on the same server as the database?
- PaulMy
- Posts: 3778
- Joined: Sun 28 Sep 2008 11:54 pm
- Weather Station: Davis VP2 Plus 24-Hour FARS
- Operating System: Windows8 and Windows10
- Location: Komoka, ON Canada
- Contact:
Re: CumulusMX MySQL DB
No, my DB is remote on my GoDaddy webserver.
Paul
Paul
Davis Vantage Pro2+
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
C1 www.komokaweather.com/komokaweather-ca
MX www.komokaweather.com/cumulusmx/index.htm /index.html /index.php
MX www.komokaweather.com/cumulusmxwll/index.htm /index.html /index.php
MX www. komokaweather.com/cumulusmx4/index.htm
- mcrossley
- Posts: 12697
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: CumulusMX MySQL DB
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.
You may find that you also have to add your home IP to the allowed remote access list on the DB.
-
- Posts: 159
- 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
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:
Thanks.
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:
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?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
Thanks.
bigmac
Menston Weather
Menston Weather
- mcrossley
- Posts: 12697
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: CumulusMX MySQL DB
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.
SQL will always fail if there is a mismatch between the table structure and the statement.
-
- Posts: 159
- 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
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?
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?
bigmac
Menston Weather
Menston Weather
- mcrossley
- Posts: 12697
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: CumulusMX MySQL DB
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.
If you already have data in your tables then you could amend the structure in something like phpMyAdmin to add the missing columns.
-
- Posts: 159
- 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
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?
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?
bigmac
Menston Weather
Menston Weather
- mcrossley
- Posts: 12697
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: CumulusMX MySQL DB
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.
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.
-
- Posts: 159
- 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
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?
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?
bigmac
Menston Weather
Menston Weather
- mcrossley
- Posts: 12697
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: CumulusMX MySQL DB
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.
-
- Posts: 159
- 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
And it was as simple as that! Done!
Thank you again for your patience Mark!
Thank you again for your patience Mark!
bigmac
Menston Weather
Menston Weather