This is sort of a "Hail Mary" pass. Last play of the football game, time has run out, and I'm the quarterback with the ball in my hand, and I have to pass it and hope. We're down by 5 points, if the pass is completed and he runs into the end zone we win the game. Anything else? well...
So here's what's up. First some background information. This is the most puzzling befuddling thing I've ever encountered. We are running Moodle 3.8 using MySql database. I believe this is a MySql bug.
This morning my automated system ran as typical on the Friday before new term starts. I authored a system that retrieves from our School Management System the information we need to know what courses to build for the next term. This system is a hybrid like system in that there's data needing to be selected outside of the moodle database, and that piece of it uses MySqli in its script to insert some data into a non-moodle table. The data is student enrollments. Based on the student enrollments, my system then creates each unique course, backs up courses we utilize as templates and then restores each backup into the appropriate new term course. Normally there's some exceptions that happen in the backup and/or restore process, but this morning it executed without making a single exception. I thought my day was blessed. Normally I have to rebuild a few courses manually due to some bugs that still exist in some of the modules that are being backed up or restored. Things beyond my control. But as I said, not one single exception, and this included like 85 courses and 120-130 student enrollments.
The instructors are enrolled via another script, and that script uses mysqli because it too grabs data from the School Management System and inserts into another non-moodle table in the moodle database. Then the script attempts to enroll instructors into their courses, by selecting the data via mysqli and then executing native moodle code. This is where things started to get weird. Only a few instructors were enrolled. I got phone calls asking me to please enroll the instructors and I responded that it was done, if there's a mistake please speak to the Registrar department. Registrar showed the settings were good, so I looked into the matter more deeply.
This is when I found out the most bizarre thing I've ever seen. Using SqlYog (a MySql client based developer tool), I queried the mdl_course table and was surprised to find that the last course showing in the mdl_course table was one from February, 2020. Well we have 5 week terms and many courses were taught between February 2020 and now. None of them were visible.
I went deeper into the rabbit hole and eventually ran mysql console on the server, and queried mdl_course table and found that records did exist. Same query I ran in SqlYog client and it returned no rows. I then studied up on the search engines looking for some bug that would define this problem but I found none. I decided to attempt to OPTIMIZE, and rebuild the indexes. I dropped the indexes, recreated them. Thinking the indexes were hosed. But after rebuilding them I ran the same query to show the last 100 or so courses created and was still turning up the same result as earlier, that being the last course from February 2020.
I then thought, it must be something to do with the database engine. Perhaps I should restart it. Stopped the service and restarted it. That didn't provide any different behavior. I then resorted to use mysqldump to create a backup of just mdl_course and nothing else. Then I scheduled a time to reboot the server. Waited until the evening when students are less likely to be using Moodle. Rebooted the server. No behavioral changes after that. So then I decided to stop all other programs that use this Moodle database, to ensure none of them would fail while I rebuild mdl_course. So I then rebuilt mdl_course, and in the MySql console on the server I ran the same query I ran before, and it showed the same records as earlier, it was rebuild properly.
I then went back to my machine and in SqlYog attempted to select the same data, and it returned zero results again and the last course showing in the database was still February 2020.
The common denominator here? I would guess it's Mysqli perhaps but not really sure if SqlYog uses Mysqli interface. I think it has a native connection and doesn't piggyback off Mysqli. Just thinking. Not entirely sure. But both Mysqli and SqlYog are returning the same weirdness behavior.
I don't really know what to do. Moodle is running fine but I can't get the instructors enrolled, and all the other software that I have created uses Mysqli client to do database access that is not specific to native Moodle code.
I've also researched nearly every Mysql bug pertaining to select results not matching the real data and haven't turned up any advice other than rebuilding indexes or rebuilding the table in question. And now I'm at my wits end.
Help? Please! 🤔