MySQL table issues after updating
Posted: Tue 08 Dec 2020 2:21 am
It took some troubleshooting, but I've determined that every time I update CumulusMX after a new SQL value is added to any table, it "breaks" my tables. What this means is that, data is still logged to the table, but I cannot see any new data in myphpadmin and any scripts I've written (such as querying the table to extract data) fail because the most recent values (don't exist).
For instance, I updated on 11/23 from b3094 to b3096. Everything looked good to go. One of my scripts queries the 'monthly' table each day to pull the rainfall between 7am the day before and 7am of the current day to calculate the rainfall and submit to CoCoRaHs. I checked my logs yesterday on their site and noticed no entries, even though it has rained since the 23rd. I manually ran a SQL select query and the value returned was Null. I didn't have time to look at it.
I sat down this evening to investigate and what I found is that whenever a new column is added to any of the three tables (dayfile, monthly, realtime), it "breaks" the table in the way I described above. In this case, the Humidex column was added. A few months ago, I updated from 3086 to 3094, and had the same type of issue. I don't recall how I fixed it that time, but it took a few days. This evening, I figured out that if I created a new table using the feature in the MX dashboard, it'll start updating right away. I then have to copy all of the data out of the old table and into the new. The copy function in myphpadmin won't work for this as it tries to create a new table with this data instead of simply copy, so I have to write a long insert query including all of the columns from the old table and inserting them into the new table, minus the new column that now exists.
I understand this is probably a very unique issue, and my platform is also unique in that everything is local. Except for paying for my domain name, everything is local; mysql db, web server, and all. Aside from that, I was wondering if anybody else has experienced anything like this, and if they have any ideas on fixing the root cause.
For instance, I updated on 11/23 from b3094 to b3096. Everything looked good to go. One of my scripts queries the 'monthly' table each day to pull the rainfall between 7am the day before and 7am of the current day to calculate the rainfall and submit to CoCoRaHs. I checked my logs yesterday on their site and noticed no entries, even though it has rained since the 23rd. I manually ran a SQL select query and the value returned was Null. I didn't have time to look at it.
I sat down this evening to investigate and what I found is that whenever a new column is added to any of the three tables (dayfile, monthly, realtime), it "breaks" the table in the way I described above. In this case, the Humidex column was added. A few months ago, I updated from 3086 to 3094, and had the same type of issue. I don't recall how I fixed it that time, but it took a few days. This evening, I figured out that if I created a new table using the feature in the MX dashboard, it'll start updating right away. I then have to copy all of the data out of the old table and into the new. The copy function in myphpadmin won't work for this as it tries to create a new table with this data instead of simply copy, so I have to write a long insert query including all of the columns from the old table and inserting them into the new table, minus the new column that now exists.
I understand this is probably a very unique issue, and my platform is also unique in that everything is local. Except for paying for my domain name, everything is local; mysql db, web server, and all. Aside from that, I was wondering if anybody else has experienced anything like this, and if they have any ideas on fixing the root cause.
