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 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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 4019) - 03 April 2024
Legacy Cumulus 1 release 1.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
MX upload missing SQL data
Moderator: mcrossley
-
- Posts: 67
- Joined: Wed 15 Feb 2017 6:07 pm
- Weather Station: Davis VP2
- Operating System: Windows 11 Pro
- Location: Bismarck, ND USA
- Contact:
MX upload missing SQL data
I had my fresh MX version uploading to my MySQL database for about a week. Everything worked fine. I didn't notice, but a week later, I had a permissions issue with the database and missed about a weeks worth of data. I have everything working reliably so far. But is there a way to push the missing dayfile info up to the database?
-
- Posts: 67
- Joined: Wed 15 Feb 2017 6:07 pm
- Weather Station: Davis VP2
- Operating System: Windows 11 Pro
- Location: Bismarck, ND USA
- Contact:
Re: MX upload missing SQL data
I was able to figure out how to manually enter in the 7 days worth of data. The CSV import failed for some reason. The only discrepancy I found is the dayfile.txt file is missing the last two columns that the dayfile database had. It showed wind directions in both of those fields.
Is that stored anywhere? I just manually put NA into the field for now.
Is that stored anywhere? I just manually put NA into the field for now.
-
- Posts: 1809
- Joined: Sat 17 Dec 2011 11:55 am
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 11 x64
- Location: Dorset - UK
- Contact:
Re: MX upload missing SQL data
Is this what you're looking for?
http://wiki.sandaysoft.com/a/ImportCumulusFile
I have to use it occasionally - importing the dayfile or monthly data. My IP address isn't static, so if lose my connection, I have to add my IP to the remote MySQL to allow me to connect to it. I can't always do it if it happens while i'm asleep, or away. So, the import file works nicely to fill in any gaps.
http://wiki.sandaysoft.com/a/ImportCumulusFile
I have to use it occasionally - importing the dayfile or monthly data. My IP address isn't static, so if lose my connection, I have to add my IP to the remote MySQL to allow me to connect to it. I can't always do it if it happens while i'm asleep, or away. So, the import file works nicely to fill in any gaps.
- mcrossley
- Posts: 12756
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: MX upload missing SQL data
Could you set up a cron job to monitor your external IP address and make the changes automatically when a different IP is detected?Mapantz wrote: My IP address isn't static, so if lose my connection, I have to add my IP to the remote MySQL to allow me to connect to it. I can't always do it if it happens while i'm asleep, or away. So, the import file works nicely to fill in any gaps.
-
- Posts: 67
- Joined: Wed 15 Feb 2017 6:07 pm
- Weather Station: Davis VP2
- Operating System: Windows 11 Pro
- Location: Bismarck, ND USA
- Contact:
Re: MX upload missing SQL data
I actually was able to put basically my whole subnet in security wise into MySQL. Yes, it is technically a bigger security risk. But the odds of my neighbors accessing my database are quite remote. But it should safeguard against IP changes.
I had a dns entry in. But apparently GoDaddy isn't always resolving those correctly. That's why it quit working.
But I think I have it all figured out now.
I had a dns entry in. But apparently GoDaddy isn't always resolving those correctly. That's why it quit working.
But I think I have it all figured out now.
-
- Posts: 1183
- Joined: Fri 27 Jul 2012 11:29 am
- Weather Station: Chas O, Maplin N96FY, N25FR
- Operating System: rPi 3B+ with Buster (full)
Re: MX upload missing SQL data
My daily database table has a very different schema to that in the Wiki (in mine I incorporate daily information from 2 different files and have more clever column names), but I do have a web form where I choose a row (date of a day) and also choose to update any one single column. It then continues by extracting the relevant field from the dayfile.txt log.
What follows is not exactly the code I use because that uses my schema, but my attempt at representing it in a way that might be useful to you.
$log_field is the value read from log file for the selected date and selected field.
$SQL will contain the updating instructions to be run like any other SQL command.
$bearing_fieldSelected is the name of the column in your schema containing the numerical bearing in angular degrees.
$compass_fieldSelected is the name of the column in your schema containing the compass direction in letters.
$rowMetDayStamp contains the date to be updated in format 'yyyy-mm-dd'.
this is included in a loop if you have several rows (days) to update.
If I choose one of the wind bearings in the dayfile.txt, my php will update both the bearing column in my schema and the related compass direction column in the same row.92merc wrote:The only discrepancy I found is the dayfile.txt file is missing the last two columns that the dayfile database had. It showed wind directions in both of those fields.
What follows is not exactly the code I use because that uses my schema, but my attempt at representing it in a way that might be useful to you.
$log_field is the value read from log file for the selected date and selected field.
$SQL will contain the updating instructions to be run like any other SQL command.
$bearing_fieldSelected is the name of the column in your schema containing the numerical bearing in angular degrees.
$compass_fieldSelected is the name of the column in your schema containing the compass direction in letters.
$rowMetDayStamp contains the date to be updated in format 'yyyy-mm-dd'.
Code: Select all
$SQL = "UPDATE $dayFileTableName SET $bearing_fieldSelected = ";
if (trim($log_field) ==="" || $log_field < 0 or $log_field > 360 || $log_field == NULL){
// set both bearing and compass direction to null if value empty, out of range or null
$SQL .= 'NULL' . ", " . $compass_fieldSelected . "=" . " 'NULL' ";
}else{
# if not null in log file, then update both columns
$SQL .= " '$log_field' ";
$alphabeticValue = $compassp[((((int)$log_field + 11.25) / 22.5) % 16 )];
$SQL .= ", " . $compass_fieldSelected . "= ' " . $alphabeticValue . " ' ";
}
}
}
$SQL .= " WHERE LogDate = '$rowMetDayStamp'";
-
- Posts: 1809
- Joined: Sat 17 Dec 2011 11:55 am
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 11 x64
- Location: Dorset - UK
- Contact:
Re: MX upload missing SQL data
That's a good idea!mcrossley wrote:Could you set up a cron job to monitor your external IP address and make the changes automatically when a different IP is detected?Mapantz wrote: My IP address isn't static, so if lose my connection, I have to add my IP to the remote MySQL to allow me to connect to it. I can't always do it if it happens while i'm asleep, or away. So, the import file works nicely to fill in any gaps.
I've also had a possible brainwave.. Does the Remote MySQL allow a domain name to be added? I have DynDNS set up on my router, so that a host name will always resolve to my IP, even when it changes.
-
- Posts: 67
- Joined: Wed 15 Feb 2017 6:07 pm
- Weather Station: Davis VP2
- Operating System: Windows 11 Pro
- Location: Bismarck, ND USA
- Contact:
Re: MX upload missing SQL data
" Does the Remote MySQL allow a domain name to be added?"
GoDaddy's does. And that's what I did with my No-IP host name. But for some reason, it quit working. That's when I figured out GoDaddy wasn't doing a lookup on my host name for some reason. And since it was basically IP security, it didn't allow MX to do the data upload to the database. I had to open it up instead to the subnet of IP addresses my ISP hands out to my area. So far that has worked. I left my No-IP name in there just in case though.
GoDaddy's does. And that's what I did with my No-IP host name. But for some reason, it quit working. That's when I figured out GoDaddy wasn't doing a lookup on my host name for some reason. And since it was basically IP security, it didn't allow MX to do the data upload to the database. I had to open it up instead to the subnet of IP addresses my ISP hands out to my area. So far that has worked. I left my No-IP name in there just in case though.