I saved the hardest for last. While technically not really that hard, I did want to keep the website up and running during as much of the migration as possible. As an extra complicating factor, to ease server space on my server, I stopped storing as much data in the mysql database and was storing a lot of the recorddata in external files. These external files were hosted on linode object storage (equivalent to Amazon S3 storage) to further alleviate server disk space woes.
Since I am completely deactivating my linode account, I need to update a good chunk of code to make sure the data is being stored in raw files again. But I think it will be easier to first migrate the database and website while still using linode object storage. That will keep me from having to download all the object storage into files on the remote server and then later transfer them to the new server. I can make the new server continue to use the linode object storage while I’m updating the code.
Migration Strategy Details
- First, zip up all the code and copy to the new server.
2/6 11:18AM - Make sure apache is configured correctly and database permissions setup correctly.
2/6 11:25AM - Make note of latest ride ID uploaded … repeat export process second time if any new rides uploaded before transition completes. But try to do next step quickly to hopefully catch an idle window between uploads. In a VERY busy system, one could disable uploads with a message indicating that the system is down for maintenance. But my system is not that busy to require that. As it turns out, this step was unnecessary because of problems encountered during step 5.
2/6 11:26AM - Dump the entire database to an sql file and copy to the new server.
2/6 11:30AM - Load the database onto the new server using the sql file from step 4. Permissions should already be setup, so at this point, it should “just work”.
2/6 11:35AM – major problems happened during the import. I was out of time to work on this, so I had to debug this later in the day. The problem was again related to how MySQL 5.6 and earlier were much more forgiving in the GIS data added to the database. MySQL 5.7 and later enforce much stricter rules about the GIS data. Previously during my di2stats.com and topocreator.com imports, the primary problem was intermixing geometry types (linestring and polygon) within a column declared to store linestrings. This time, I am not 100% sure what was wrong with a subset of the records as everything was typed correctly. My best guess is that there was either duplicate points on the LINESTRING. It only effected a few records directly, so I haven’t gone back and investigated what was wrong with those yet. The problem, though, was the way phpmyadmin dumps the data – it groups many records together into fewer INSERT statements. If any one of those records fails, then the entire INSERT fails. This means that even though there were only about 15 rides with GIS errors, it ended up preventing about 3000 rides from importing as phpmyadmin must group roughly 200 records together for each insert. The ultimate fix was to configure phpmyadmin to output a separate INSERT for each record. This meant that when I re-ran the import again later, it was able to import all but 15 records. 2/7 7:15AM - Update the domain name to point to the IP address of the new server.
2/7 7:20AM – everything worked perfectly on first run through all the tests! - Update the code to use the file system again instead of linode object storage. See details below:
- First update the code to check for the file on the filesystem before trying to pull from linode storage.
2/8 7:25AM - Next, update the code to store newly uploaded files on the filesystem instead of linode storage.
2/8 7:35AM – note this is how it was originally setup and most of the original code as still there and only needed to be uncommented. - Then migrate all the existing linode files back to files in the filesystem.
2/8 8:15AM – my files downloaded fine, but I noticed a MAJOR data loss of my own files as ALL of 2020 was never migrated to linode storage. I checked other users, and their files were fine – but I think I must have deleted my own files when I was running out of server space all the time and backed them up locally. I am pretty sure I have a backup, but since I have all the original data files from my rides saved, I might as well just re-upload them and then recalculate all my stats rather than dig around for the backup. 2/10 9:45AM – finally resolved plus added new feature (ride searching and sorting) to website to make it easier to delete my 2020 records from the database.
- First update the code to check for the file on the filesystem before trying to pull from linode storage.
After the linode migration finished, I uploaded all my missing 2020 files and then recalculated my current stats. Here is before/during/after screenshots showing the stats. This was mainly to verify that the stats should NOT have changed when re-uploading after deleting all the existing 2020 records. This led me to discover/remember that my “passing stats” were being calculated from the raw FIT files … since the 2020 files were missing, they weren’t included. But the database had summary counts at the time the rides had been imported so they were included in the overall counts. This doesn’t explain the mismatch in the “during” and “after” screenshots. I will investigate this further soon and make up follow-up post explaining what was causing the mismatch between those numbers. Since the server is sitting beside me, it was nice to hear the fans kick up again as it processed all 385 files from 2020. My hunch is that I help debug other people’s FIT files using my account. Chances are that I have some “missing” FIT files that are still in the database from the import I did when helping them. To verify this, the easiest thing will probably be to just delete ALL my rides and re-upload them all since I have all the raw data saved in about 50 different places for fear of ever losing my ride data.
