Hello everyone, I am having a problem updating OJS from where I work, which is in version 2.4.5.0 to version 3.1.2, I am getting the following error message at the time the initial update process changes the table “User_settings”.
I already checked the table in the database but I can’t find the problem. If anyone is aware of what may be happening I appreciate it.
Below is the excerpt in which the error is reported:
(mysqli): DROP INDEX user_settings_user_id ON user_settings
(mysqli): ALTER TABLE user_settings MODIFY COLUMN user_id BIGINT NOT NULL
(mysqli): ALTER TABLE user_settings MODIFY COLUMN locale VARCHAR(14) NOT NULL DEFAULT ‘’
(mysqli): ALTER TABLE user_settings MODIFY COLUMN setting_name VARCHAR(255) NOT NULL
(mysqli): ALTER TABLE user_settings MODIFY COLUMN assoc_type BIGINT DEFAULT 0
(mysqli): ALTER TABLE user_settings MODIFY COLUMN assoc_id BIGINT DEFAULT 0
(mysqli): ALTER TABLE user_settings MODIFY COLUMN setting_value TEXT
(mysqli): ALTER TABLE user_settings MODIFY COLUMN setting_type VARCHAR(6) NOT NULL
(mysqli): ALTER TABLE user_settings ADD INDEX user_settings_user_id (user_id)
(mysqli): ALTER TABLE user_settings ADD UNIQUE INDEX user_settings_pkey (user_id, locale, setting_name, assoc_type, assoc_id)
Query: ALTER TABLE user_settings ADD UNIQUE INDEX user_settings_pkey (user_id, locale, setting_name, assoc_type, assoc_id) failed. Duplicate entry ‘9800–orcid-0-0’ for key ‘user_settings_pkey’
1062: Duplicate entry ‘9800–orcid-0-0’ for key ‘user_settings_pkey’
Hi @iurycastro,
Can you check in your OJS 2.4.5 database to see whether you have two users with the same ORCID. You can do this by running the following database query…
SELECT setting_value FROM user_settings WHERE setting_name='orcid' GROUP BY user_id HAVING count(*) > 1;
Regards,
Alec Smecher
Public Knowledge Project Team
First of all thank you for your help. I ran the passed sql command, but it returns me no records.
Hi @iurycastro,
Strange – can you tell me what you get for…
SELECT * FROM user_settings WHERE user_id = 9800;
Please obscure anything that you don’t want to post publicly.
Regards,
Alec Smecher
Public Knowledge Project Team
Hi, I managed to resolve the error by giving the “group by” command to the “user_settings” table and sending the grouped data to a new table, I believe there were some duplicate records in it. When I ran the update process again, another error came to me:
--------------------------------------------- (excerpt from error) -----------------------------------------
(mysqli): INSERT INTO journal_settings (setting_name, setting_value, setting_type, journal_id) SELECT ‘themePluginPath’, ‘default’, ‘string’, journal_id FROM journals
(mysqli): UPDATE event_log SET message=‘submission.event.submissionSubmitted’ WHERE message=‘log.author.submitted’
(mysqli): UPDATE submissions SET stage_id=3 WHERE submission_id IN (SELECT DISTINCT submission_id FROM review_assignments)
(mysqli): UPDATE submissions SET stage_id=3 WHERE stage_id = 1 AND submission_id IN (SELECT DISTINCT submission_id FROM review_rounds)
(mysqli): UPDATE submissions SET stage_id=4 WHERE submission_id IN (SELECT e1.submission_id FROM edit_decisions e1 LEFT JOIN edit_decisions e2 ON (e2.edit_decision_id > e1.edit_decision_id AND e2.submission_id = e1.submission_id) WHERE e2.edit_decision_id IS NULL AND e1.decision = 1)
(mysqli): UPDATE review_rounds SET stage_id=3
Query: UPDATE review_rounds SET stage_id=3 failed. Duplicate entry ‘2061-3-1’ for key ‘review_rounds_submission_id_stage_id_round_pkey’
1062: Duplicate entry ‘2061-3-1’ for key ‘review_rounds_submission_id_stage_id_round_pkey’
ADOConnection._Execute(UPDATE review_rounds SET stage_id=3, false) % line 1051, file: adodb.inc.php
ADOConnection.Execute(UPDATE review_rounds SET stage_id=3) % line 446, file: Installer.inc.php
Installer.executeSQL(UPDATE review_rounds SET stage_id=3) % line 441, file: Installer.inc.php
Installer.executeSQL(Array[112]) % line 399, file: Installer.inc.php
Installer.executeAction(Array[3]) % line 265, file: Installer.inc.php
A database error has occurred: Duplicate entry ‘2061-3-1’ for key ‘review_rounds_submission_id_stage_id_round_pkey’
Thanks in advance for your help.
Hi @iurycastro,
Hmm, I’m concerned about the data errors you’re running into – is it possible that the database you’re working with before running the upgrade script has already had a partial upgrade run on it?
Regards,
Alec Smecher
Public Knowledge Project Team
I really think it’s not possible, I already checked the same after reading a forum post here that deals with this partially updated database problem. Every time I get a certain error, I delete the database and create a new one from scratch from my backup of version 2.4.5.0, before running the update again.
Note: The version of OJS 2.4.5.0 that is running in our institution works perfectly without bugs.
Hi, Checking the “review_rounds” table I noticed some duplicate records again, so I did the same procedure as I did for the “user_settings” table, using “group by” to group and insert into a new table, in order to have a clean table without duplication. I think it worked because the duplication error disappeared, but a new error already appears to me:
(mysqli): SHOW INDEXES FROM review_round_files
Query: SHOW INDEXES FROM review_round_files failed. Table ‘dbseer_prod.review_round_files’ doesn’t exist
1146: Table ‘dbseer_prod.review_round_files’ doesn’t exist
ADOConnection._Execute(SHOW INDEXES FROM review_round_files, false) % line 1051, file: adodb.inc.php
ADOConnection.Execute(SHOW INDEXES FROM review_round_files) % line 331, file: adodb-mysqli.inc.php
ADODB_mysqli.MetaIndexes(review_round_files, false, false) % line 216, file: adodb-datadict.inc.php
ADODB_DataDict.MetaIndexes(review_round_files) % line 482, file: adodb-xmlschema.inc.php
dbTable.create(Object:adoSchema) % line 347, file: adodb-xmlschema.inc.php
(mysqli): SHOW COLUMNS FROM review_round_files
Query: SHOW COLUMNS FROM review_round_files failed. Table ‘dbseer_prod.review_round_files’ doesn’t exist
1146: Table ‘dbseer_prod.review_round_files’ doesn’t exist
ADOConnection._Execute(SHOW COLUMNS FROM review_round_files, false) % line 1051, file: adodb.inc.php
ADOConnection.Execute(SHOW COLUMNS FROM review_round_files) % line 607, file: adodb-mysqli.inc.php
ADODB_mysqli.MetaColumns(review_round_files, true, false) % line 204, file: adodb-datadict.inc.php
ADODB_DataDict.MetaColumns(review_round_files) % line 494, file: adodb-xmlschema.inc.php
dbTable.create(Object:adoSchema) % line 347, file: adodb-xmlschema.inc.php
(mysqli): SHOW INDEXES FROM review_files
Query: SHOW INDEXES FROM review_files failed. Table ‘dbseer_prod.review_files’ doesn’t exist
1146: Table ‘dbseer_prod.review_files’ doesn’t exist
ADOConnection._Execute(SHOW INDEXES FROM review_files, false) % line 1051, file: adodb.inc.php
ADOConnection.Execute(SHOW INDEXES FROM review_files) % line 331, file: adodb-mysqli.inc.php
ADODB_mysqli.MetaIndexes(review_files, false, false) % line 216, file: adodb-datadict.inc.php
ADODB_DataDict.MetaIndexes(review_files) % line 482, file: adodb-xmlschema.inc.php
dbTable.create(Object:adoSchema) % line 347, file: adodb-xmlschema.inc.php
(mysqli): SHOW COLUMNS FROM review_files
Query: SHOW COLUMNS FROM review_files failed. Table ‘dbseer_prod.review_files’ doesn’t exist
1146: Table ‘dbseer_prod.review_files’ doesn’t exist
ADOConnection._Execute(SHOW COLUMNS FROM review_files, false) % line 1051, file: adodb.inc.php
ADOConnection.Execute(SHOW COLUMNS FROM review_files) % line 607, file: adodb-mysqli.inc.php
ADODB_mysqli.MetaColumns(review_files, true, false) % line 204, file: adodb-datadict.inc.php
ADODB_DataDict.MetaColumns(review_files) % line 494, file: adodb-xmlschema.inc.php
dbTable.create(Object:adoSchema) % line 347, file: adodb-xmlschema.inc.php
(mysqli): DROP INDEX review_assignments_submission_id ON review_assignments
(mysqli): DROP INDEX review_assignments_reviewer_id ON review_assignments
(mysqli): DROP INDEX review_assignments_form_id ON review_assignments
(mysqli): ALTER TABLE review_assignments DROP COLUMN cancelled
(mysqli): ALTER TABLE review_assignments DROP COLUMN regret_message
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN review_id BIGINT NOT NULL AUTO_INCREMENT
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN submission_id BIGINT NOT NULL
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN reviewer_id BIGINT NOT NULL
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN competing_interests TEXT
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN recommendation TINYINT
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN date_assigned DATETIME
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN date_notified DATETIME
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN date_confirmed DATETIME
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN date_completed DATETIME
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN date_acknowledged DATETIME
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN date_due DATETIME
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN date_response_due DATETIME
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN last_modified DATETIME
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN reminder_was_automatic TINYINT NOT NULL DEFAULT 0
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN declined TINYINT NOT NULL DEFAULT 0
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN replaced TINYINT NOT NULL DEFAULT 0
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN reviewer_file_id BIGINT
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN date_rated DATETIME
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN date_reminded DATETIME
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN quality TINYINT
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN review_round_id BIGINT
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN stage_id TINYINT NOT NULL DEFAULT 1
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN review_method TINYINT NOT NULL DEFAULT 1
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN round TINYINT NOT NULL DEFAULT 1
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN step TINYINT NOT NULL DEFAULT 1
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN review_form_id BIGINT
(mysqli): ALTER TABLE review_assignments MODIFY COLUMN unconsidered TINYINT
(mysqli): ALTER TABLE review_assignments ADD INDEX review_assignments_submission_id (submission_id)
(mysqli): ALTER TABLE review_assignments ADD INDEX review_assignments_reviewer_id (reviewer_id)
(mysqli): ALTER TABLE review_assignments ADD INDEX review_assignments_form_id (review_form_id)
(mysqli): ALTER TABLE review_assignments ADD INDEX review_assignments_reviewer_review (reviewer_id, review_id)
(mysqli): ALTER TABLE review_rounds MODIFY COLUMN review_round_id BIGINT NOT NULL AUTO_INCREMENT
Query: ALTER TABLE review_rounds MODIFY COLUMN review_round_id BIGINT NOT NULL AUTO_INCREMENT failed. Incorrect table definition; there can be only one auto column and it must be defined as a key
1075: Incorrect table definition; there can be only one auto column and it must be defined as a key
ADOConnection._Execute(ALTER TABLE review_rounds MODIFY COLUMN review_round_id BIGINT NOT NULL AUTO_INCREMENT, false) % line 1051, file: adodb.inc.php
ADOConnection.Execute(ALTER TABLE review_rounds MODIFY COLUMN review_round_id BIGINT NOT NULL AUTO_INCREMENT) % line 446, file: Installer.inc.php
Installer.executeSQL(ALTER TABLE review_rounds MODIFY COLUMN review_round_id BIGINT NOT NULL AUTO_INCREMENT) % line 441, file: Installer.inc.php
Installer.executeSQL(Array[50]) % line 375, file: Installer.inc.php
Installer.executeAction(Array[3]) % line 265, file: Installer.inc.php
Thanks in advance for your help.
If it is inconvenient to send so many lines of code, please let me know.
Hi @iurycastro,
Hmm, can you try this query in your OJS 2.x database?
SELECT * FROM versions WHERE product='ojs2' AND current=1;
It seems as though your installation thinks it’s older than 2.4.5 – it appears to be running scripts to upgrade itself to 2.4.x from an older version.
Regards,
Alec Smecher
Public Knowledge Project Team
Hi, Executing the command without “AND current = 1;” brings me the following lines.
Note: Just one update in our magazine from an older version to version 2.4.5.0
Hi @iurycastro,
Can you try disabling the debug option in config.inc.php and posting the full results of the upgrade script? (Anonymize anything you don’t want to expose.)
Regards,
Alec Smecher
Public Knowledge Project Team
