Upgrade from OJS 3.1.2-4 to 3.2.1-1 fails: A database error has occurred: Duplicate entry '0-1' for key 'citations_publication_seq'

Hi Guys, I am working on an OJS upgrade from 3.1.2-4 to 3.2.1-1 and I get stuck because of a database error:
A database error has occurred: Duplicate entry ‘0-1’ for key ‘citations_publication_seq’
I did see this post below on Github but it suggests the issue is resolved. It seems it is not.


Journal uses English and Russian locales.
Database is MySQL.
A solution would be much appreciated.

Some more details:
PHP 7.4.2.
MySQL 5.7.26

PHP log file says:

[12-Jul-2020 19:06:47 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:48 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:49 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:49 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:49 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:49 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:50 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:50 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:50 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:56 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:56 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:56 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:56 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:56 Europe/Amsterdam] PHP Deprecated:  Function get_magic_quotes_runtime() is deprecated in /Users/frank/Sites/ojs_3211/lib/pkp/lib/vendor/adodb/adodb-php/adodb-xmlschema.inc.php on line 1307
[12-Jul-2020 19:06:56 Europe/Amsterdam] Duplicate entry '0-1' for key 'citations_publication_seq'
[12-Jul-2020 19:13:28 Europe/Amsterdam] PHP Deprecated:  Methods with the same name as their class will not be constructors in a future version of PHP; ADODB_Cache_File has a deprecated constructor in /Users/frank/Sites/ojs/lib/pkp/lib/adodb/adodb.inc.php on line 263
[12-Jul-2020 19:13:28 Europe/Amsterdam] PHP Deprecated:  Methods with the same name as their class will not be constructors in a future version of PHP; ADOConnection has a deprecated constructor in /Users/frank/Sites/ojs/lib/pkp/lib/adodb/adodb.inc.php on line 359
[12-Jul-2020 19:13:28 Europe/Amsterdam] PHP Deprecated:  Methods with the same name as their class will not be constructors in a future version of PHP; ADORecordSet has a deprecated constructor in /Users/frank/Sites/ojs/lib/pkp/lib/adodb/adodb.inc.php on line 2921
[12-Jul-2020 19:13:28 Europe/Amsterdam] PHP Deprecated:  Methods with the same name as their class will not be constructors in a future version of PHP; ADORecordSet_array has a deprecated constructor in /Users/frank/Sites/ojs/lib/pkp/lib/adodb/adodb.inc.php on line 3939
[12-Jul-2020 19:13:28 Europe/Amsterdam] PHP Deprecated:  The each() function is deprecated. This message will be suppressed on further calls in /Users/frank/Sites/ojs/lib/pkp/lib/adodb/adodb.inc.php on line 1003
[12-Jul-2020 19:13:28 Europe/Amsterdam] PHP Warning:  Cannot use a scalar value as an array in /Users/frank/Sites/ojs/lib/pkp/classes/core/DataObject.inc.php on line 133
[12-Jul-2020 19:13:28 Europe/Amsterdam] PHP Warning:  Cannot use a scalar value as an array in /Users/frank/Sites/ojs/lib/pkp/classes/core/DataObject.inc.php on line 133
[12-Jul-2020 19:13:28 Europe/Amsterdam] PHP Warning:  Cannot use a scalar value as an array in /Users/frank/Sites/ojs/lib/pkp/classes/core/DataObject.inc.php on line 133
[12-Jul-2020 19:13:28 Europe/Amsterdam] PHP Warning:  Cannot use a scalar value as an array in /Users/frank/Sites/ojs/lib/pkp/classes/core/DataObject.inc.php on line 133
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Deprecated:  Methods with the same name as their class will not be constructors in a future version of PHP; ADODB_Cache_File has a deprecated constructor in /Users/frank/Sites/ojs/lib/pkp/lib/adodb/adodb.inc.php on line 263
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Deprecated:  Methods with the same name as their class will not be constructors in a future version of PHP; ADOConnection has a deprecated constructor in /Users/frank/Sites/ojs/lib/pkp/lib/adodb/adodb.inc.php on line 359
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Deprecated:  Methods with the same name as their class will not be constructors in a future version of PHP; ADORecordSet has a deprecated constructor in /Users/frank/Sites/ojs/lib/pkp/lib/adodb/adodb.inc.php on line 2921
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Deprecated:  Methods with the same name as their class will not be constructors in a future version of PHP; ADORecordSet_array has a deprecated constructor in /Users/frank/Sites/ojs/lib/pkp/lib/adodb/adodb.inc.php on line 3939
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Deprecated:  The each() function is deprecated. This message will be suppressed on further calls in /Users/frank/Sites/ojs/lib/pkp/lib/adodb/adodb.inc.php on line 1003
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Warning:  Cannot use a scalar value as an array in /Users/frank/Sites/ojs/lib/pkp/classes/core/DataObject.inc.php on line 133
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Warning:  Cannot use a scalar value as an array in /Users/frank/Sites/ojs/lib/pkp/classes/core/DataObject.inc.php on line 133
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Warning:  Cannot use a scalar value as an array in /Users/frank/Sites/ojs/lib/pkp/classes/core/DataObject.inc.php on line 133
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Warning:  Cannot use a scalar value as an array in /Users/frank/Sites/ojs/lib/pkp/classes/core/DataObject.inc.php on line 133
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Warning:  Creating default object from empty value in /Users/frank/Sites/ojs/cache/t_compile/54f1464e7fea658d69a5e9eb00e32d6e4c31ec36^52019e87b90081f2c6bfa717994d81ab7712dd1f_0.app.frontendcomponentssearchF.php on line 30
[12-Jul-2020 19:13:29 Europe/Amsterdam] PHP Deprecated:  Array and string offset access syntax with curly braces is deprecated in /Users/frank/Sites/ojs/lib/pkp/lib/vendor/ezyang/htmlpurifier/library/HTMLPurifier/Encoder.php on line 162

Hi @Franklinx,

Have a look at this thread: OJS upgrade error (DB: Column 'publication_id' cannot be null) from 3.1.0-0 to 3.2.1-1

Regards,
Alec Smecher
Public Knowledge Project Team

Ha @asmecher, I did have a look.

First I ran:
SELECT (*) FROM authors a LEFT JOIN submissions s ON (a.submission_id = s.submission_id) WHERE s.submission_id IS NULL
And found five instances that I deleted.

Then I ran:
SELECT c.citation_id FROM citations c LEFT JOIN submissions s ON (c.submission_id = s.submission_id) WHERE s.submission_id IS NULL;
That resulted in a whole bunch, most likely the product of a test journal that was delete some time ago.
I did delete the whole bunch number produced by the query.

If I now run both queries I get zero as result. That is a good sign I guess. But the update keep failing because of the database error: Duplicate entry ‘0-1’ for key ‘citations_publication_seq.

Is there any other query that makes sense to get me out of this mess?

By the way, after the failed upgrade the table “citations” in the MySQLi database lists 0 as publication_id for all citations.

Oops, I got through. In previous updates the upgrade button on the install page referred to versions prior to 3.0. I learned the other procedure by filling in all the required data and choosing not to create new database. I gave it a second look and saw that the first option is now ‘version neutral’. Followed the instructions and it worked like a charm :grinning:

I may have tried this option previously and got stuck because of the errors above. Anyhow, we are now in 3.2 territory!

Hi @Franklinx,

Glad to hear you’re up to date and running!

Regards,
Alec Smecher
Public Knowledge Project Team