upgrade error, "dberror:duplicate entry"

[rthomas]

Had trouble upgrading from v2.2.2.0 to v2.3.7 with database error. Saw earlier post to turn on debug, try again, and to report results.

Immediately below are results. Further below are my environment details.

Code: Select all

entry 'interest-4096-861' for key 2
                                       ADOConnection._Execute(INSERT INTO controlled_vocabs (symbolic, assoc_type, assoc_id) VALUES ('interest', 4096, '861')) % line  845, file: adodb.inc.php
                                    ADOConnection.Execute(INSERT INTO controlled_vocabs (symbolic, assoc_type, assoc_id) VALUES (?, ?, ?), Array[3]) % line  198, file: DAO.inc.php
                                 DAO.update(INSERT INTO controlled_vocabs (symbolic, assoc_type, assoc_id) VALUES (?, ?, ?), Array[3]) % line  739, file: Upgrade.inc.php
                              Upgrade.migrateReviewingInterests(Object:Upgrade, Array[1]) % line    0, file:
                           call_user_func(Array[2], Object:Upgrade, Array[1]) % line  410, file: Installer.inc.php
DB Error: Duplicate entry 'interest-4096-861' for key 2

Server details:
* OS X Server, 10.5.8
* PHP 5.2.9
* apache2
* mysql 5.0.92

[rthomas]

I realize this is open source, etc. But I'm wondering if someone from PKP could indicate when they might have a chance to respond -- or perhaps if there's another way to get support even if paid. Because of the vulnerability -- I'm in quite an unpleasant situation.

Thanks in advance for any information or advice.

[rthomas]

Some more info...

I had one of our LAMP developers take a look at this. Here's his feedback in case it helps...

OK, from what I can tell. The query that is run to get the items to insert into controlled_vocabs is generating data to create a controlling key and a detailed key. It creates a controlling key for the user, then creates detailed keys for each record. It isn't making sure the user data is unique before inserting. To me that seems like a bug in the upgrade. We could try changing the controlling key query to ignore duplicates by changing it from INSERT INTO controlled_vocabs to INSERT IGNORE INTO controlled_vocabs. I think that would work, but I don't know all the implications of these records, so I can't be sure. The best solution would be to get a fix from PKP since I think this is a bug.

[rthomas]

Anyone out there got some advice?

[jmacgreg]

Hi rthomas,

Apologies for the late reply. This is an upgrade issue that I've run into myself. This might be some older upgrade bug having to do with localizing the user interests table; you should be able to just remove the dead settings.

If you look at the 'user_settings' table, for all rows where 'setting_name' column equals "interests", you'll see that some rows have a value (probably "en_US") for the 'locale' column, while others are blank. The rows with zero info in the 'locale' column are duplicates (you can check against the other rows with locale info in them), and are causing the duplicate entry error. If you restore your database, then delete those duplicate columns, the upgrade process should complete just fine. Of course, you'll want to ensure that you have working backups before upgrading, and should double-check all data post-upgrade.

I'm going to ask the developer responsible for the reviewing interests code (who helped me troubleshoot this issue previously) to provide any further information you might need, and to file a bug report if necessary. If you have any further questions, please let us know!

Cheers,
James

[mcrider]

Hi rthomas,

Your change from 'INSERT' to 'INSERT IGNORE' will work if you are using MySQL -- You can make the change at about line 739 of the classes/install/Upgrade.inc.php file (INSERT INTO controlled_vocabs...). Officially, it will have to be done differently since INSERT IGNORE isn't standard SQL, but for your purposes this should resolve your issue.

Cheers,
Matt

[mcrider]

Hi all,

FYI, we fixed the root cause of this which is an issue caused by the ugprade from OJS 2.1.1: http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=7406. However, for journals that have already upgrade through OJS 2.1.1, the issue that causes the above error may still exist. It can be resolved by removing the rows from the user_settings table where the setting_name is interests and the locale is empty. You can do this by running this SQL statement:

Code: Select all

DELETE FROM user_settings WHERE setting_name = 'interests' and locale IS NULL OR locale = '';

Note though that in some installations where this has happened, there are values for the interests set in the fields that should be deleted. You may want to investigate these settings and possibly merge the (localized and non-localized) values together.

Cheers,
Matt

[agcorneliu]

hy all
I tell you briefly my problem: I had installed on a server (older - OpenSuse 11.4) OJS version 2.6 which worked perfectly.
I recently made ​​updates to the server (OpenSuse 12.1 - x64) and I wanted to reinstall OJS - the same that worked on the old severe. Backup worked but when I use it gives me an error like << DB Error: Duplicate entry 'om0jqkcplsgfd7uogguq0l3u3k5lghmdeq3ndinr' for key 'sessions_pkey' >>
Moreover, we can say that OJS installation works, but it does not work any new and clean version with the same error like OJS << DB Error: Duplicate entry 'om0jqkcplsgfd7uogguq0l3u3k5lghmdeq3ndinr' for key 'sessions_pkey' >>
Site we found at http:www.analegeo.uaic.ro, thanked you expect an answer as quickly as possible. I can say I'm the one responsible for installing the server and the OJS.
I tried about all the variations on this site, give me a solution please ...
with thanks, Corneliu

[mcrider]

Hi Corneliu,

Please post new subjects as new threads, your post is unrelated to this thread. For your problem, try emptying the sessions table in your database.

Cheers,
Matt