Page 1 of 1
MX upload missing SQL data
Posted: Sat 08 Apr 2017 3:43 pm
by 92merc
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?
Re: MX upload missing SQL data
Posted: Sat 08 Apr 2017 5:02 pm
by 92merc
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.
Re: MX upload missing SQL data
Posted: Sat 08 Apr 2017 5:27 pm
by Mapantz
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.
Re: MX upload missing SQL data
Posted: Sat 08 Apr 2017 6:01 pm
by mcrossley
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.
Could you set up a cron job to monitor your external IP address and make the changes automatically when a different IP is detected?
Re: MX upload missing SQL data
Posted: Sat 08 Apr 2017 7:12 pm
by 92merc
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.
Re: MX upload missing SQL data
Posted: Sat 08 Apr 2017 7:41 pm
by sfws
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.
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.
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.
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'";
this is included in a loop if you have several rows (days) to update.
Re: MX upload missing SQL data
Posted: Sat 08 Apr 2017 8:03 pm
by Mapantz
mcrossley wrote: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.
Could you set up a cron job to monitor your external IP address and make the changes automatically when a different IP is detected?
That's a good idea!
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.
Re: MX upload missing SQL data
Posted: Sun 09 Apr 2017 5:02 am
by 92merc
" 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.