OMP: database error has occurred

When we do a database upgrade (OMP1.0 => 1.1.1-1) we get following error:

A database error has occurred: Duplicate entry ‘submissionAgency-1048585-1’ for key ‘controlled_vocab_symbolic’

When we set the logging on debug we get het next messages (bottom):

(mysql): UPDATE event_log SET assoc_type = ‘1048585’ WHERE assoc_type = ‘513’
(mysql): UPDATE controlled_vocabs SET symbolic = ‘submissionAgency’ WHERE symbolic = ‘monographAgency’
1062: Duplicate entry ‘submissionAgency-1048585-1’ for key ‘controlled_vocab_symbolic’
ADOConnection._Execute(UPDATE controlled_vocabs SET symbolic = ‘submissionAgency’ WHERE symbolic = ‘monographAgency’, false) % line 1051, file: adodb.inc.php
ADOConnection.Execute(UPDATE controlled_vocabs SET symbolic = ‘submissionAgency’ WHERE symbolic = ‘monographAgency’) % line 440, file: Installer.inc.php
Installer.executeSQL(UPDATE controlled_vocabs SET symbolic = ‘submissionAgency’ WHERE symbolic = ‘monographAgency’) % line 435, file: Installer.inc.php
Installer.executeSQL(Array[39]) % line 396, file: Installer.inc.php
Installer.executeAction(Array[3]) % line 265, file: Installer.inc.php

someone an idea what the problem is?

Hi @ABDO,

Is this an upgrade on a database that already experienced a failed upgrade? If an upgrade fails, make sure to restore it to its state before the upgrade was executed before trying again.

Regards,
Alec Smecher
Public Knowledge Project Team

No this was the first try. Copied the 1.1.1.-1 version to the server and changed config.inc.php (installed = off). went to the url and pressed Click here for the upgraded. There is a existing database so need to do a new install of the database.

Hi @ABDO,

On your pre-upgrade database, what do you get when you query…SELECT * FROM controlled_vocabs WHERE symbolic = 'submissionAgency';Regards,
Alec Smecher
Public Knowledge Project Team

This is the result:

start:

end:

Hi @ABDO,

Hmm, strange – did you start out with OMP 1.0 beta?

Regards,
Alec Smecher
Public Knowledge Project Team

According to the header this is indeed a beta: meta name=“generator” content=“OMP 1.0 Beta”.
Should we have used a different procedure in case of the beta version?

Hi @Franklinx and @ABDO,

I think what’s happened is that you started out with OMP 1.0 beta, then upgraded the code to OMP 1.0.0 and did not update your database; then, later, updated your code to 1.1.1. What’s resulted is a data set that’s a mix of OMP 1.0 beta and OMP 1.0.0. Is that a possibility?

You may be able to get the upgrade to complete by commenting out or removing the following lines:

…but there may be other areas in the data that will need attention.

Regards,
Alec Smecher
Public Knowledge Project Team

The upgrade was done in a lab environment and was not yet put live. The real thing out on the web is still the somewhat upgraded OMP 1.0 beta. Sounds like we have to update the database first (finish the upgrade to OMP 1.0.0) and then move to 1.1.1. Any guidance on how to update the 1.0B database that you could give us?

I removed the 1.1.1 versio of OMP and copied 1.0 to the server.

When i ga to the install-url, i get the next page.

But i am not upgrading from 0.x version.

Click here…

Upgrade to 1.0 gives the same error.

Errors occurred during installation:
A database error has occurred: Duplicate entry ‘submissionAgency-1048585-1’ for key ‘controlled_vocab_symbolic’

When I commenting out the next line

 22                 <!-- rename the symbolic controlled vocab fields to the generic pkp-lib field name -->
 23 <!--            <query>UPDATE controlled_vocabs SET symbolic = 'submissionAgency' WHERE symbolic = 'monographAgency'</query> -->
 24                 <query>UPDATE controlled_vocabs SET symbolic = 'submissionDiscipline' WHERE symbolic = 'monographDiscipline'</query>
 25                 <query>UPDATE controlled_vocabs SET symbolic = 'submissionKeyword' WHERE symbolic = 'monographKeyword'</query>
 26                 <query>UPDATE controlled_vocabs SET symbolic = 'submissionLanguage' WHERE symbolic = 'monographLanguage'</query>
 27                 <query>UPDATE controlled_vocabs SET symbolic = 'submissionSubject' WHERE symbolic = 'monographSubject'</query>

The next error wil come:

Errors occurred during installation:
A database error has occurred: Duplicate entry ‘submissionDiscipline-1048585-1’ for key ‘controlled_vocab_symbolic’

When commenting out all the rename line:

 22                 <!-- rename the symbolic controlled vocab fields to the generic pkp-lib field name -->
 23 <!--            <query>UPDATE controlled_vocabs SET symbolic = 'submissionAgency' WHERE symbolic = 'monographAgency'</query> -->
 24 <!--            <query>UPDATE controlled_vocabs SET symbolic = 'submissionDiscipline' WHERE symbolic = 'monographDiscipline'</query> -->
 25 <!--            <query>UPDATE controlled_vocabs SET symbolic = 'submissionKeyword' WHERE symbolic = 'monographKeyword'</query> -->
 26 <!--            <query>UPDATE controlled_vocabs SET symbolic = 'submissionLanguage' WHERE symbolic = 'monographLanguage'</query> -->
 27 <!--            <query>UPDATE controlled_vocabs SET symbolic = 'submissionSubject' WHERE symbolic = 'monographSubject'</query> -->
 28
 29 <!--            <query>UPDATE controlled_vocab_entry_settings SET setting_name = 'submissionAgency' WHERE setting_name = 'monographAgency'</query> -->
 30 <!--            <query>UPDATE controlled_vocab_entry_settings SET setting_name = 'submissionDiscipline' WHERE setting_name = 'monographDiscipline'</query> -->
 31 <!--            <query>UPDATE controlled_vocab_entry_settings SET setting_name = 'submissionKeyword' WHERE setting_name = 'monographKeyword'</query> -->
 32 <!--            <query>UPDATE controlled_vocab_entry_settings SET setting_name = 'submissionLanguage' WHERE setting_name = 'monographLanguage'</query> -->
 33 <!--            <query>UPDATE controlled_vocab_entry_settings SET setting_name = 'submissionSubject' WHERE setting_name = 'monographSubject'</query> -->

the upgrade is a succes.

But can it do any harm now we didn’t renamed those fields?

Hi @ABDO,

The problem is that your pre-upgrade database contains entries for both sets of metadata field names, when it should only contain one or the other. The upgrade attempts to migrate content from the old format to the new format, but data already exists in the new format, so you get a collision. The only way I could imagine this mix getting created is if you ran OMP 1.1.1 code with a database from OMP 1.0b that hadn’t been properly upgraded. It would probably be a good idea to survey your pre-upgrade database manually to see what data didn’t get migrated.

As it currently stands, with the upgrade executed after commenting out those upgrade fields, you will have taken the OMP 1.1.1 fields and ignored the OMP 1.0b fields. That will give you the same behavior you will have had in your OMP 1.1.1 installation, so I suspect you’ll be OK to carry on with the upgrade you’ve done.

Regards,
Alec Smecher
Public Knowledge Project Team

Thanks for helping us!

Hey there,

Where are doing the upgrade from beta to OMP 1.2.0 and we get another database error:

Errors occurred during installation:

A database error has occurred: Duplicate entry ‘512-1-0’ for key 'press_features_pkey’

What is the trick here to ignore that?

Greeting

Hi @ABDO,

This isn’t related to the original question; could you post this as a new thread? That’ll help to keep the forum organized.

Regards,
Alec Smecher
Public Knowledge Project Team