kstan Posted October 15, 2022 Share Posted October 15, 2022 A couple of months ago I tried to upgrade to version 4.7.0, but it went terribly wrong. During the initial database query, the database crashed so thoroughly it wasn't even salvageable. I had to restore it with a database backup that, fortunately, was only a couple of days old. So now I want to try again, but because of my previous failed attempt, I'm using extra caution. I put the long query into my MySQL Navicat software and asked it to explain the query. It doesn't get far. It tells me that just about every line has sql errrors. For instance: EXPLAIN DROP TABLE `core_modules` > 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TABLE `core_modules`' at line 1 > Time: 0,118s So naturally, I checked my MySQL server version, which is MySQL 8. There is no upgrade available. What on earth is wrong with my setup and what should I do? Link to comment Share on other sites More sharing options...
Stuart Silvester Posted October 15, 2022 Share Posted October 15, 2022 EXPLAIN statements only work with SELECT, DELETE, INSERT, REPLACE and UPDATE queries, that likely explains why you're seeing an error message. My only suggestion is to try the update again with 4.7.3 and make sure that you have a working backup of your community before you start. Link to comment Share on other sites More sharing options...
kstan Posted October 15, 2022 Author Share Posted October 15, 2022 Just now, Stuart Silvester said: EXPLAIN statements only work with SELECT, DELETE, INSERT, REPLACE and UPDATE queries, that likely explains why you're seeing an error message. My only suggestion is to try the update again with 4.7.3 and make sure that you have a working backup of your community before you start. Thank you! I did not know that. I will try the update again and hope for the best. Link to comment Share on other sites More sharing options...
Jim M Posted October 15, 2022 Share Posted October 15, 2022 You're most welcome. Glad we could provide some insight at least to one part of your puzzle 🙂 . Keep in mind too that any upgrade you should take a full backup (files and database) before starting. This way, if anything does go wrong from your server to the software, you have a backup of your community that you can restore. kstan 1 Link to comment Share on other sites More sharing options...
kstan Posted October 15, 2022 Author Share Posted October 15, 2022 Failed again. I'm getting "Incorrect key file for table..." error messages for different tables when I try to load the forum. I try to repair tables, but they are unrepairable. Now trying to reestablish the database from a backup. Any ideas? Link to comment Share on other sites More sharing options...
Jim M Posted October 15, 2022 Share Posted October 15, 2022 11 minutes ago, kstan said: Failed again. I'm getting "Incorrect key file for table..." error messages for different tables when I try to load the forum. I try to repair tables, but they are unrepairable. Now trying to reestablish the database from a backup. Any ideas? I would advise contacting your hosting provider for assistance here. Something is happening with your MySQL server if this is happening for all database tables when being attempted. kstan 1 Link to comment Share on other sites More sharing options...
Miss_B Posted October 15, 2022 Share Posted October 15, 2022 25 minutes ago, kstan said: Failed again. I'm getting "Incorrect key file for table..." error messages for different tables when I try to load the forum. This usually happens due to disk issues on the MySQL server and/or corrupted keys/tables. kstan 1 Link to comment Share on other sites More sharing options...
kstan Posted October 15, 2022 Author Share Posted October 15, 2022 Okay, I will do a thorough database check and see what else I can do on the server side. Link to comment Share on other sites More sharing options...
kstan Posted October 23, 2022 Author Share Posted October 23, 2022 One week later... The database no longer crashes when I attempt the upgrade. It still tells me there is a problem with the database and to run a long query before proceding with the upgrade. I do that. It goes fine, no errors, but after finishing the query, it once again tells me there is a problem with the database and to run the same script again. In other words, I'm going around in circles and never get to the point where I can do the actual upgrade. Any ideas? Link to comment Share on other sites More sharing options...
Miss_B Posted October 23, 2022 Share Posted October 23, 2022 4 minutes ago, kstan said: One week later... The database no longer crashes when I attempt the upgrade. It still tells me there is a problem with the database and to run a long query before proceding with the upgrade. I do that. It goes fine, no errors, but after finishing the query, it once again tells me there is a problem with the database and to run the same script again. In other words, I'm going around in circles and never get to the point where I can do the actual upgrade. Any ideas? What is the actual query that it keeps asking you to run? Link to comment Share on other sites More sharing options...
kstan Posted October 23, 2022 Author Share Posted October 23, 2022 26 minutes ago, Miss_B said: What is the actual query that it keeps asking you to run? It's too long to post, but I attached it. query.txt Link to comment Share on other sites More sharing options...
Randy Calvert Posted October 23, 2022 Share Posted October 23, 2022 You should generally run those commands via SSH within MySQL. If you don’t know how to do that, you should contact your host for assistance. Link to comment Share on other sites More sharing options...
kstan Posted October 23, 2022 Author Share Posted October 23, 2022 Does phpmyadmin or similar MySQL software qualify as SSH? Because I did that and the result was the same. Link to comment Share on other sites More sharing options...
Randy Calvert Posted October 23, 2022 Share Posted October 23, 2022 (edited) 6 minutes ago, kstan said: Does phpmyadmin or similar MySQL software qualify as SSH? Because I did that and the result was the same. You can use phpMyAdmin, but it is still running through a web browser. This means if it takes several minutes for the commands to run, there is a reasonable chance it could time out in the web browser depending on how long Apache is configured on the server to allow the script to execute and how long it will wait before giving up. SSH is not subject to timeouts like can happen via a web browser. So it’s generally safer to recommend doing it one way that has less opportunity for something to go wrong. 🙂 Edited October 23, 2022 by Randy Calvert Link to comment Share on other sites More sharing options...
kstan Posted October 23, 2022 Author Share Posted October 23, 2022 I also used Navicat for MySQL which is a desktop app. It did take a few minutes, but it performed all the commands without error. Link to comment Share on other sites More sharing options...
Marc Stridgen Posted October 24, 2022 Share Posted October 24, 2022 Are you using PHP 8? If so, could you test with 7.4 and see ig that then upgrades? Just seen a report of something very similar Miss_B 1 Link to comment Share on other sites More sharing options...
kstan Posted October 24, 2022 Author Share Posted October 24, 2022 9 hours ago, Marc Stridgen said: Are you using PHP 8? If so, could you test with 7.4 and see ig that then upgrades? Just seen a report of something very similar Thank you for the suggestion. I now tried to roll back from PHP 8 to 7.4, but unfortunately, it made no difference. I still go around in circles. Link to comment Share on other sites More sharing options...
Miss_B Posted October 24, 2022 Share Posted October 24, 2022 What version are you trying to upgrade from? Link to comment Share on other sites More sharing options...
kstan Posted October 24, 2022 Author Share Posted October 24, 2022 8 minutes ago, Miss_B said: What version are you trying to upgrade from? 4.7.0 Link to comment Share on other sites More sharing options...
Miss_B Posted October 24, 2022 Share Posted October 24, 2022 1 minute ago, kstan said: 4.7.0 I meant from what version did you try to upgrade to 4.7.0? Link to comment Share on other sites More sharing options...
kstan Posted October 24, 2022 Author Share Posted October 24, 2022 20 minutes ago, Miss_B said: I meant from what version did you try to upgrade to 4.7.0? Hmm.... I see what you mean. When you asked your question, I went to my dashboard to check, and my dashboard says I have 4.7.0, so that's what I replied. However, the upgrade to 4.7.0 never finished succesfully, which is the whole problem. Another detail is that the upgrade I'm trying to perform says it includes version 4.7.0 through 4.7.3. So I probably still have 4.6.12.1, but my dashboard thinks I have 4.7.0. Or something. I'm confused. Link to comment Share on other sites More sharing options...
Miss_B Posted October 24, 2022 Share Posted October 24, 2022 23 minutes ago, kstan said: Hmm.... I see what you mean. When you asked your question, I went to my dashboard to check, and my dashboard says I have 4.7.0, so that's what I replied. However, the upgrade to 4.7.0 never finished succesfully, which is the whole problem. Another detail is that the upgrade I'm trying to perform says it includes version 4.7.0 through 4.7.3. So I probably still have 4.6.12.1, but my dashboard thinks I have 4.7.0. Or something. I'm confused. Is your forum running without issues right now or do you get errors? It looks like the upgrade wasn't finished successfully indeed. As it hapens a couple of weeks ago I was contacted by a member here who had a very similiar issue. What I did was, roll back to the backup made before the upgrade and then re-do it again and fixing the problems that popped up, which were several sql queries as in your case. After that the upgrade went through smoothly. My advice to you if I may, is to roll back to the most recent backup made before the upgrade and re-do the upgrade again. Link to comment Share on other sites More sharing options...
kstan Posted October 24, 2022 Author Share Posted October 24, 2022 50 minutes ago, Miss_B said: Is your forum running without issues right now or do you get errors? It looks like the upgrade wasn't finished successfully indeed. As it hapens a couple of weeks ago I was contacted by a member here who had a very similiar issue. What I did was, roll back to the backup made before the upgrade and then re-do it again and fixing the problems that popped up, which were several sql queries as in your case. After that the upgrade went through smoothly. My advice to you if I may, is to roll back to the most recent backup made before the upgrade and re-do the upgrade again. It appears to be running without errors. Everything seems fine except that it won't update. As far as a backup, since the problem first started in July, I no longer have a backup that old. What I would be interested in knowing is if it actually performed those changes to the database to make it ready for 4.7. If so, would it be possible to install IC 4.7.3 from scratch with a new database and then, after installation, connect it to the old database? Link to comment Share on other sites More sharing options...
Jim M Posted October 24, 2022 Share Posted October 24, 2022 1 minute ago, kstan said: If so, would it be possible to install IC 4.7.3 from scratch with a new database and then, after installation, connect it to the old database? This would not work, I'm afraid. You would just have the same issues. I have created a ticket to look into why these errors keep cropping up for you and won't resolve. It could be a bug in the system but could also be something isn't completely being recognized as changed. Please watch your email for further correspondence. Link to comment Share on other sites More sharing options...
Solution kstan Posted November 6, 2022 Author Solution Share Posted November 6, 2022 I just want to report that the problem has finally been resolved. In the end converting the database to utf8mb4 did the trick. I can't find it now, but I think someone at some point asked me why I hadn't done that. I replied that I intended to, but I wanted the other problem resolved first before messing with converting the database. Nowhere that I saw was utf8mb4 listed as a requirement to upgrade, but nonetheless, that's what solved it. Thank you to everybody who tried to help. Much appreciated! Meddysong 1 Link to comment Share on other sites More sharing options...
Recommended Posts