Database Update Error: Duplicate entry 'title-en_US'

[pkahai]

Hello,

I was trying to update from OJS 2.1.1 to 2.3.8 but got the error: A database error has occurred: Duplicate entry 'title-en_US' for key 1. I thought I was jumping over too many intermediate updates, so I decided to try updating to v2.2. Still the same error. I found a few suggestions on this forum but those did not solve my problem. I have attached a partial debug dump at the point the error occurs and halts the update process.

After having spent an inordinate amount of man-hours on this problem, I am ready to tear my hair out (or whatever's left on my head, so please, someone, respond quickly before it is all gone!).

The site, jstem.org, is hosted on GoDaddy's Linux Shared hosting. I had updated the PHP version to 5.x PRIOR to performing the update, so I don't know whether that may have caused a problem.

[asmecher]

Hi pkahai,

I wouldn't recommend trying to upgrade via an intermediate version; the upgrade process in the newest 2.3.x release is most tested and refined.

Is it possible that you've received this error message after trying and failing to run a prior upgrade? If so, the prior upgrade attempt probably left your database in an inconsistent state and attempting to run the upgrade script again will cause problems. The best thing to do in that case is restore your database to a backup from before you attempted to run the upgrade and start fresh, reporting any error messages you encounter here.

Upgrading from an old installation of OJS that was run on PHP4 shouldn't be a problem, as long as you're using a new 2.3.x release (which will be PHP5 compatible). Because PHP has occasionally changed the format it stores serialized content in over the years, you may lose a few journal settings, but they should not be critical. Once the upgrade is finished, run through your setup pages to double-check that everything is as expected.

Regards,
Alec Smecher
Public Knowledge Project Team

[pkahai]

I wasn't operating on the original database but a copy of it. Also, I first tried to update to 2.3.8 and failed with the same error message.

[asmecher]

Hi pkahai,

In that case, you've probably inherited someone else's failed upgrade. The problem relates to an upgrade query scripted in dbscripts/xml/upgade/2.2.0_localize.xml:

Code: Select all

INSERT INTO site_settings (setting_name, setting_value, setting_type, locale) SELECT 'title', title, 'string', primary_locale FROM site

This part of the upgrade from 2.1.x to 2.2.0. It sounds to me like this statement has already been run, and your upgrade is attempting to run it again, resulting in duplicate entries. Perhaps someone tried to upgrade to something post-2.2.x earlier and gave up without returning the installation to its previous state properly.

If an upgrade has failed in the past, then your database is already in an indeterminate state somewhere between the two versions. If it has been used for some time since that happened, then the situation will be still more complicated because additional content will have been added using the old database conventions.

Do you have any information about the maintenance history of the installation?

Regards,
Alec Smecher
Public Knowledge Project Team

[pkahai]

There was another person who I took over from in early August. However, I do not have an update history although I see older versions backed-up as per your upgrade instructions.

As far as fixing the database manually, I am a very knowledgeable database user and, if something needs to be done manually, I can do it. A row needs deleting, table dropped, data updated etc., I can do. Also, I can look through and type into PHP code but, unfortunately, I cannot program in it. Thus, if there is something I can do, be glad to do it. If it involves something you have to do imperatively, I can let you in on the database. And, if we need to talk, I can phone you, Skype you, or Google Talk you (or Hangout!).

[asmecher]

Hi pkahai,

There are a lot of unknowns, not least of which are 1) when someone tried to upgrade your system and 2) what they tried to upgrade it to. I would suggest treating your database as "2.1.1 with unknown quirks" and trying to work through the upgrade process, dealing with whatever error you see, restoring to pre-upgrade database (plus any data fixes you've accumulated en route), then trying again and dealing with the next.

The upgrade process is scripted in dbscripts/xml/upgrade.xml.

To work around this initial problem, for example, you'll need to

Code: Select all

DELETE FROM site_settings WHERE setting_name IN ('title', 'intro', 'about', 'contact_name', 'contact_email');

I won't be able to walk you through every step of this, but I may be able to pitch in the odd piece of advice.

Regards,
Alec Smecher
Public Knowledge Project Team

[pkahai]

Yes, I agree that you just cannot walk me through the process. But, I did try deleting all records from the Site_Settings table, then running the script. If you notice the error dump, it stops when trying to create an Index. How do I get past that?

[asmecher]

Hi pkahai,

Do you have multiple entries in the "site" table? There should only be one.

Regards,
Alec Smecher
Public Knowledge Project Team

[pkahai]

There is just one entry.

[pkahai]

Just discovered that there is a database backup I had made even before I began the update process - that was August 10th. If all that has changed on the site since that time is just the registered users, can I use that database, add the "newer" users to it, and I am back to the pre-update stage? I am assuming that I, and no one prior to me, messed-up somewhere.

Thanks for your assistance, Alex. Maybe, just maybe, I might be able to get the database in working order again.