Holly crap.... the amount of stupid going on over here with MySQL upgrades is just beyond retarded. Documentation is very lackluster, forums and users are generally unhelpful (even though some really try to be and I thank those that tried to help) but over all this whole process has just been a mess...
Ok so first off. I know that MySQL 5.1 is end of life so I began looking through the processes needed to perform an upgrade. I found the online documentation and the incremental upgrade paths I needed to take easily enough (aka MYSQl 5.1 to 5.5. then 5.6 then to 5.7 then 8) etc... https://dev.mysql.com/doc/refman/8.0/en/windows-upgrading.html
So I tried both upgrade methods listed in the above documentation. The "installer" upgrade doesn't work because as it says clearly in the documentation it doesn't support even minor upgrades (aka 5.5 to 5.6 for example)
I tried the 2nd method "Upgrading MySQL Using the Windows ZIP Distribution" which worked until the very when you realize you cant start the MySQL services.... after hours of troubleshooting I found the issue was with MySQL 5.5 using a different version of Microsoft Visual C++ (2018 I believe) I was missing from my server installs but I had the previous version installed for MySQL 5.1. BUT IT DOESNT SAY THAT ANYWHERE IN THE DOCUMENTATION... After finally getting it up and running I had too many other issues that were not mentioned in the above documentation using this method I just said I'm done and went off to figure it out myself.
So now on my own. I go ahead and restore my VM back to MySQL 5.1 and go to town. I have normal SQL experience so how hard can this be right??? (AHAHAHAHAHHAHA kill me.)
I go ahead and backup my databases. Mind you, starting with just a dump because I thought that would be good enough. Plus from online searches I couldn't find much information on Dumps Vs Self Contained or any of the switches such as "single transactions only" and "export schema" etc...
I uninstalled MYSQL 5.1 and installed MySQL 5.5. Go to import the dump and realize a downside to dump files is that you have to recreate the databases. Ok, no biggy. Recreated databases and imported my Dump file. Ran into error 2006 (hy000). Did some research on it and found the issue is with max_allowed_packet being too low. So... went into the my.ini file and changed it to 512M, restarted MySQL service and boom imports were successful. (and another oddity, wtf is with this always needing to migrate your my.ini to the new install MYSQL version???? It took them to MYSQL 5.6 to realize that my.ini needed to be in a location that doesn't get changed during upgrading???, this documented anywhere in the upgrade process? NOPE)
I begin testing and realize none of my WordPress sites weren't functional (unable to connect to the database) after some quicking thinking I realize that user accounts are not included in backups (like why the hell not though???) so I recreated the user accounts and boom WordPress sites are back up and running. I check my Confluence site and getting an error stating "detected tables with non-default character encoding" even though the database was recreated using Confluences recommended MySQL DB settings... After messing around with my.ini and dumping/creating the databases and manually checking tables. I was unable to find the problem. At this point, I decided to start all over again, but this time using Self Contained backups.
I restored my VM, back to MySQL 5.1, and created both Dumps and Self Contained backups. Go through the whole process again of upgrading MySQL to 5.5 and then restoring the databases with Self Contained backups this time and recreated user accounts. BOOM FINALLY! Everything is running well on MySQL 5.5. I perform a MySQL upgrade and run the MySQL Checker. Everything comes back good to go.
So now I worked out the kinks, right? I should be able to upgrade to MySQL 5.6 without any problems RIGHT????? EH NO WHY WOULD IT BE THAT EASY!?!?!?!?
I go to perform the same process but now on MySQL 5.5. Created new Self Contained backups, dumped the old databases, remove MySQL 5.5 and install MySQL 5.6 then import the Self Contained dumps, recreate the user accounts, and edit the new my.ini... can you guess what happened next????? I go to test things and now Confluence is working just fine but both my WordPress sites are "unable to connect to database" status... ok cool, I've run into this issue before. Maybe I entered in the usernames/passwords wrong? I delete the accounts and recreate them, hmmm nope! Sites are still down... I do some research on the issue and some are saying "plugins, themes, usernames/passwords blah blah blah" but I know that isn't the case because I had this same problem on MySQL 5.5 and fixed it already...
After hours of self troubleshooting and manually comparing differences between MySQL 5.5 and MySQL 5.6 I noticed that the default User accounts created with MySQL 5.6 are different than MySQL 5.5. In MySQL 5.6 they add 4 additional accounts. 2 are for loopback and IPV6 and 2 are localhost anonymous accounts. I think to myself "ok well I created the user accounts for my WordPress site so surely these anonymous accounts can't be causing the issues" but just for the hell of it, I delete both anonymous accounts, and BOOM THE SITES ARE BACK ONLINE. WTF ARE YOU FOR REAL?!!??!?!?!?!?!?!?!? WHY ARE THOSE ACCOUNTS CREATED BY DEFAULT AND WHY ARE THEY AFFECTING THINGS THAT AREN'T EVEN USING THEM!?!?!?!?!?
So I'm sure you can guess now. I'm finally on MYSQL 5.6. Performed the MySQL Upgrade commands and MySQL checkers. Everything came back OK.
This is just what I had to go through so far. A lot of this could have been avoided with better MySQL upgrade documentation. It's just sad and the number of people that have gone through similar issues or worse it's BAAADDD! Just from my googling today I've found tons of users having issues such as receiving an error code, but the resolution has zero to do with what the error code returned or stated it was. It's just insane.
Tomorrow I'll be going through the process of upgrading to MySQL 5.7 and I think I will stick on that for a while before attempting the upgrade to MYSQL 8.
But thanks for listening. It's been a long day and needed to rant about it. Maybe this can help a few others get past the nonsense that is MySQL upgrading and maybe made a few of you laugh. I'm sure you have been through similar issues that were caused due to piss poor program design and no fault of your own.