I'm working on upgrading my forum from 1.1.21 to 2.0.19 (on the way to 2.1) and I've hit another snag with the upgrade.php script. When I run it, I get:
Executing upgrade script 1 of 2
Executing: "Updating and creating indexes..." (1 of 19 - of this script)
Error! Duplicate key name 'IP'
What is going on here?
Quote from: von Corax on June 02, 2022, 12:47:59 PMI'm working on upgrading my forum from 1.1.21 to 2.0.19 (on the way to 2.1) and I've hit another snag with the upgrade.php script. When I run it, I get:
Executing upgrade script 1 of 2
Executing: "Updating and creating indexes..." (1 of 19 - of this script)
Error! Duplicate key name 'IP'
What is going on here?
Do you have access to your phpmyadmin? If you do, go to the database that you have used to install your smf forum and first of all make a backup of your smf_log_errors table, then run this sql query:
ALTER TABLE `smf_log_errors` DROP INDEX `ip`;After you do that, re-run the upgrader again.
Okay; I'm doing that now.
EDIT: I started the upgrader from the point it left off; should I have done that or should I have started over?
Since this is 2 hours later, which did you end up doing and did it work?
Quote from: Steve on June 02, 2022, 02:58:47 PMSince this is 2 hours later, which did you end up doing and did it work?
I tried picking up where I left off, but didn't seem to be making any progress, so I restored the 1.1 version of the database and tried running upgrade.php again. I've just noticed, however, that I'm running desperately low on disk space, so that's probably why it seems to have stalled. I may have to roll back and then see what my sysadmin says about expanding the VM's disk allocation.
Oh, well; at least I'm getting good practice in disaster recovery...
Quote from: von Corax on June 02, 2022, 03:07:59 PMI tried picking up where I left off, but didn't seem to be making any progress, so I restored the 1.1 version of the database and tried running upgrade.php again. I've just noticed, however, that I'm running desperately low on disk space, so that's probably why it seems to have stalled. I may have to roll back and then see what my sysadmin says about expanding the VM's disk allocation.
Lack of memory can cause the hanging of the upgrader. Hopefully increasing the limit will help.
If you run into the same error that you posted in your op, deleting that index will fix the issue. The index should be readded again by the upgrader. If for some strange reason that will not happen, let me know and I wll post a query for you to add it manually.
Anyways, good luck and let us know how it will go.
upgrade.php doesn't seem to want to resume an upgrade already in progress. I've decided to back out and try again tomorrow. I'll follow up here when I do.
I've fixed the "Duplicate key name 'ID'" problem, but now I'm getting "Duplicate key name 'ID_MEMBER'". I assume the fix is the same; what table am I altering this time?
Quote from: von Corax on June 03, 2022, 12:26:48 PMI've fixed the "Duplicate key name 'ID'" problem, but now I'm getting "Duplicate key name 'ID_MEMBER'". I assume the fix is the same; what table am I altering this time?
What is the full error message?
Quote from: Doug Heffernan on June 03, 2022, 02:33:22 PMQuote from: von Corax on June 03, 2022, 12:26:48 PMI've fixed the "Duplicate key name 'ID'" problem, but now I'm getting "Duplicate key name 'ID_MEMBER'". I assume the fix is the same; what table am I altering this time?
What is the full error message?
Same as before, just "Error! Duplicate key name 'ID_MEMBER'."
I ran it again with extra debugging messages turned on and got
Updating indexes on "log_mark_read"...done
Updating indexes on "themes"...done
Error! Duplicate key name 'ID_MEMBER'
I hope that can give someone some idea of where I should look.
Quote from: von Corax on June 03, 2022, 04:49:55 PMI ran it again with extra debugging messages turned on and got
Updating indexes on "log_mark_read"...done
Updating indexes on "themes"...done
Error! Duplicate key name 'ID_MEMBER'
I hope that can give someone some idea of where I should look.
That helped a lot. It pointed us in the right direction. Run this query:
ALTER TABLE smf_themes DROP INDEX ID_MEMBER;And as always, do not forget to make a backup of the smf_themes table before you run that query.
I've got past the smf_themes table; now the error message is
Checking for "classic" and removing it if necessary...done
Renaming personal message tables...done
Error! Table 'smf_pm_recipients' already exists
Should I just drop that table, or is there something else I should change?
Okay, I think I'm looking at the right .sql file now, and I've found the statement
RENAME TABLE {$db_prefix}im_recipients
TO {$db_prefix}pm_recipients;
and I don't seem to have a table named smf_im_recipients, so I'm not going to drop anything without instruction.
Quote from: von Corax on June 04, 2022, 02:02:20 PMOkay, I think I'm looking at the right .sql file now, and I've found the statement
RENAME TABLE {$db_prefix}im_recipients
TO {$db_prefix}pm_recipients;
and I don't seem to have a table named smf_im_recipients, so I'm not going to drop anything without instruction.
This tells me that the upgrader is trying to rename the smf_im_recipients table to smf_pm_recipients, but the last mentioned table already exists in the database. Hence the table already exists error message.
It looks like the smf_pm_recipients is not part of the default smf 1.1.x version, but it might have been added to your database by a third party mod which coincidently had a table named precisely like the default one in smf 2.0.x.
If that is the case, you can safely drop that table from the database. I am not familiar with Smf 1.1.x so I can not say with a 100% certainty that that is the case in regard to the aforementioned table. So before you delete it, make really sure that it has been added by a mod and make a backup of it first.
Eh? That tells me the upgrader is *confused*. Do not drop the pm_recipients table from your install.
In SMF 1.0, the system wasn't called personal messages, it was called instant messages and was housed in an instant_messages table, with the recipients table for each message being the im_recipients table.
This was renamed in 1.1 to personal_messages and pm_recipients respectively.
What I'm curious about is why the upgrader is trying to run the 1.0 -> 1.1 conversion on your database which would explain more than one of the issues you're having.
If you have a backup from before the upgrade, you could remove the upgrade-1.0.sql and upgrade-1.1.sql files before you start to upgrade from 1.1 to 2.0.
Okay, smf_pm_recipients has 62617 records, so I think I'll try Arantor's suggestion first. (I found the above RENAME statement in upgrade_1-1.sql, which I assumed was upgrading from 1.1.)
EDIT: Okay, I deleted upgrade_1-0.sql and upgrade_1-1.sql, and upgrade.php is complaining that "The upgrader was unable to find some crucial files."
I tried it again, just deleting upgrade_1-0.sql and got the "Table exists" error described above.
Quote from: von Corax on June 04, 2022, 02:19:34 PMOkay, smf_pm_recipients has 62617 records, so I think I'll try Arantor's suggestion first.
That would be the way to go. It looks like that table is part of smf after all. As I already mentioned above, I am not familiar with those old versions.
For what it's worth, I did not have these problems when I did a practice run on my development box. The biggest difference I can see is that dev is running mysql 5.7.33 while the production box is running mysql 5.5.62. Does that offer any insight into what's going on here?
Quote from: von Corax on June 04, 2022, 03:28:46 PMFor what it's worth, I did not have these problems when I did a practice run on my development box. The biggest difference I can see is that dev is running mysql 5.7.33 while the production box is running mysql 5.5.62. Does that offer any insight into what's going on here?
Did you have an exact duplicate of your live forum on your development box or was it a newly installed test forum?
It should be an exact duplicate. I followed the instructions for moving to a new host.
This is just wild speculation on my part, but my test box is running Ubuntu 16 while the production box is running Ubuntu 14. Is there any possibility that might account for the difference in behaviour between the two installations?
Quote from: von Corax on June 04, 2022, 06:03:49 PMIt should be an exact duplicate.
It should be or it is, because there is a difference.
Quote from: von Corax on June 06, 2022, 02:21:07 PMThis is just wild speculation on my part, but my test box is running Ubuntu 16 while the production box is running Ubuntu 14. Is there any possibility that might account for the difference in behaviour between the two installations?
Assuming that you have mirrored an exact copy of your main forum to the test forum, and there the upgrade to 2.0.19 goes through without any issues, but the upgrade of your main forum throws different errors, then it stands to reason that the difference between your test and main forum 's envirement could be the cause.
How did the upgrade go btw? Did it complete successfully?
Quote from: Doug Heffernan on June 06, 2022, 04:12:26 PMQuote from: von Corax on June 04, 2022, 06:03:49 PMIt should be an exact duplicate.
It should be or it is, because there is a difference.
I know; "In theory there's no difference between theory and practice..." I dumped the database and tarred the /forum directory, and it worked after I installed it on the test box, so it's as exact a copy as I know how to make it.
Quote from: Doug Heffernan on June 06, 2022, 04:12:26 PMQuote from: von Corax on June 06, 2022, 02:21:07 PMThis is just wild speculation on my part, but my test box is running Ubuntu 16 while the production box is running Ubuntu 14. Is there any possibility that might account for the difference in behaviour between the two installations?
Assuming that you have mirrored an exact copy of your main forum to the test forum, and there the upgrade to 2.0.19 goes through without any issues, but the upgrade of your main forum throws different errors, then it stands to reason that the difference between your test and main forum 's envirement could be the cause.
How did the upgrade go btw? Did it complete successfully?
It did not. I'm still hitting the "Table 'smf_pm_recipients' already exists" error. Does the definition of that table change, or is it only the name?
The name only changes during the 1.0 to 1.1 upgrade. It has 'already happened' by the time the upgrade is being started.
Quote from: Arantor on June 07, 2022, 02:47:34 PMThe name only changes during the 1.0 to 1.1 upgrade. It has 'already happened' by the time the upgrade is being started.
What about the 1.1 to 2.0 upgrade? I'm wondering if renaming the table might get me past the problem. I'm also wondering, if I mangle the table by accident, if I can restore it from backup without having to roll back the entire database.
The error of 'table already exists' is the result of the 1.0 to 1.1 upgrade - not a later one. Renaming it will just break in other ways. It'll just be the next part of the upgrade that fails owing to the next schema change.
In your database, in the smf_settings table, what is given where the 'variable' is smfVersion?
Quote from: von Corax on June 07, 2022, 02:49:19 PMI'm wondering if renaming the table might get me past the problem. I'm also wondering, if I mangle the table by accident, if I can restore it from backup without having to roll back the entire database.
Additionally, the table structure will be from the 1.1.x version, and it can not be used on the 2.0.x/2.1.x versions.
Quote from: von Corax on June 07, 2022, 02:44:48 PMI know; "In theory there's no difference between theory and practice..." I dumped the database and tarred the /forum directory, and it worked after I installed it on the test box, so it's as exact a copy as I know how to make it.
Here is an idea. Why do not make a backup of your upgraded test forum, install a brand new 2.0.19 forum in your live envirement and restore the backup there and run the repair_settings.php file afterwards to update all the paths and urls.
Quote from: Arantor on June 07, 2022, 02:55:55 PMThe error of 'table already exists' is the result of the 1.0 to 1.1 upgrade - not a later one. Renaming it will just break in other ways. It'll just be the next part of the upgrade that fails owing to the next schema change.
In your database, in the smf_settings table, what is given where the 'variable' is smfVersion?
I shows as 1.1.16, despite the fact that I've installed the 1.1.21 patch.
Quote from: Doug Heffernan on June 07, 2022, 04:16:46 PMHere is an idea. Why do not make a backup of your upgraded test forum, install a brand new 2.0.19 forum in your live envirement and restore the backup there and run the repair_settings.php file afterwards to update all the paths and urls.
I'd lose too many posts doing just that, but what I will try is re-cloning to test, upgrading test, and cloning back to production. I'll schedule that for tomorrow.
Quote from: von Corax on June 07, 2022, 07:48:47 PMQuote from: Doug Heffernan on June 07, 2022, 04:16:46 PMHere is an idea. Why do not make a backup of your upgraded test forum, install a brand new 2.0.19 forum in your live envirement and restore the backup there and run the repair_settings.php file afterwards to update all the paths and urls.
I'd lose too many posts doing just that, but what I will try is re-cloning to test, upgrading test, and cloning back to production. I'll schedule that for tomorrow.
That is what I meant, to re-clone the main forum again, sorry if I was not clear about it. Anyways, let us know how it will go.
I got it running by downloading it to test, upgrading the database there, and then uploading and reinstalling it. Now I just need to fix the æsthetics. ::)