Upgrade from OJS 2.2.4 to 2.4.8 Error

It looks like the upgrade is trying to create a signoffs table in the database. We checked the old 2.2.4 database and that table was not in the database.

It creates part of the table and the latest dates are in 2012. I am not sure where the data used to create this table is coming from.

What should we try to do?

Thanks

Rick

Should we try to upgrade to an earlier version? What would anyone suggest?

Thanks - Just a thought.

Rick

Hi @RickMath,

Thanks for posting more information. The signoffs table is a legitimate and necessary table in the database. It looks like the error is happening because a user ID is missing, probably because a user was deleted or merged, related to this issue. This post outlines steps you can take to identify and replace the missing user IDs.

Best,
Amanda Stevens
Public Knowledge Project Team

Thanks Amanda

Is this the same User ID that is in the Users table. I would assume that. Should I look for duplicate User IDs in the User table? Then should we go back to the original database and try the upgrade of continue with the copy we are trying to upgrade.

Rick

I did go ahead and check the Users table and there are no duplicates. Where else should I look?

I downloaded the Users table and the Roles table. There are 115 UserIDs in the Roles table that are not in the Users Table. Could this be the cause of the problem? Are there other tables where UserIDs may not match the Users table?

Thanks

Hi @RickMath,

It’s more likely that a user ID is missing in a submission record. Did you try running the query here to identify the missing data in the signoffs table?

Thanks,
Amanda Stevens
Public Knowledge Project Team

I am not sure what you mean by a submission record. Is that in the Articles table?

The signoffs table did not exist in the 2.2.4 database. When the upgrade created the signoffs table there were 3531 records created. 1397 of these records had no matching UserID in the Users table.

There are 1124 records in the roles table. 115 of these do not have matching IDs in the Users table.

There are no UserIDs in the Articles table without matching UserIDs in the User table.

Can you tell me what other tables are being used in creating the Signoffs table? I need to check other matches if there are tables other than Roles and Articles that are used to create the Signoffs table.

Thanks

I have searched through the tables in the database for tables with UserIDs. There were tables with UserIDs that did not exist in the Users table.

I queried for UserID records in the tables that did not exist in the Users table.

The tables were
UserSettings
Roles
NotificationStatus
CompletedPayments
Articles

I have deleted records from UserSettings and Roles where there were no UserIDs in the Users table.

I ran the following by exporting the tables to Access
SELECT signoffs.symbolic, signoffs.assoc_type, signoffs.assoc_id, users.UserID, signoffs.user_id
FROM signoffs LEFT JOIN users ON signoffs.user_id = users.UserID
WHERE (((users.UserID) Is Null) AND ((signoffs.user_id) Is Not Null And (signoffs.user_id)<>0));
There were no records.

The Notification table has 93 UserIds that are not in the Users table. I have not deleted records in this yet. Could this be causing the problem?

The only tables that seem to have the various dates that are used in the signoffs sql are
copyed_assignments
edit_assignments
layouted_assignments
proof_assignments
review_assignments

I found a ReviewerID in review _assignments that did not exist in the Users table.
None of the other assignments tables had nonzero UserIDs

What other tables do I need to check?

Hi @RickMath,

I suspect your OJS 2.2.4 database is referring to a nonexistent user in the proof_assignments table. Try running…

SELECT p.proof_id FROM proof_assignments p LEFT JOIN users u ON (u.user_id = p.proofreader_id) WHERE u.user_id IS NULL;

This should generate a list of proof_assignments entries that refer to nonexistent users. The easiest solution will be to delete these.

Before you do any of this, of course, make sure you back up your OJS 2.2.4 database. Then apply any modifications necessary prior to the upgrade script being run, like this correction. Then run the upgrade script.

Regards,
Alec Smecher
Public Knowledge Project Team

Alec

Thanks for your response.

We found two articleIDs in several of the assignment tables that did not exist in the Articles table. These have been deleted. All of the other records in the table have proofreaderIDs of 0 (zero). We also found these two article IDs in the other assignments tables and in the review-rounds table. These also have been deleted.

The notification_status table has many UserIDs that are not in the users table. Are these a problem? This is the only remaining table of concern.

There was also a UserID that was in a few tables that was not in the Users table. These also have been deleted in those tables.

I have made the corrections in a backup copy before the attempted upgrade. Should we start again with the backup copy instead of the copy where the upgrade was attempted?

Thanks so much for you help. You are always on track with your recommendations.

Rick

Hi @RickMath,

The notifications system is an area where cruft can build up – we’ll be cleaning this up a little in future releases. For the moment, I don’t think those extra notification_status entries will cause problems, but you may as well remove them just to remove the temptation, as it were.

You should definitely restore from backup before correcting data errors and running the upgrade script; anytime your upgrade fails with an error message, your database will be marooned somewhere between the two versions, and there currently isn’t a way to pick up the upgrade from where it left off.

And a note for when you upgrade from OJS 2.x to 3.x, also ensure that you take a backup of your files directory. The upgrade process rearranges this. (Upgrades within various 2.x versions do not rearrange the files directory – just the 2.x to 3.x upgrade.)

Regards,
Alec Smecher
Public Knowledge Project Team

Thanks so much Alec :grinning:

I have cleaned up the notifications_status file and we are going to try again.

We will test 2.4.8 before going to 3.X

What 3.X version can we upgrade to after 2.4.8?

Happy Holidays!!!

Rick

Hi @RickMath,

You can upgrade from any OJS 2.4.x release to any OJS 3.x release. I’d recommend using the latest (currently OJS 3.1.0-1). And yes, it’s a good idea to run a test upgrade – not only to see whether the upgrade works as expected, but to review the workflow changes etc. to make sure you’re ready to make the leap.

Happy holidays!

Regards,
Alec Smecher
Public Knowledge Project Team

Alec

I cleaned up all the tables and the tech ran the upgrade again.

We received this error

A database error has occurred: Incorrect table definition; there can be only one auto column and it must be defined as a key

I have authorized them to log into my user account. What do we need to diagnose this?

Thanks

Rick

We tried the upgrade again and here is what resulted. I know the copyed_assignments table was in the database as it was one that I checked for errors several times.

What should we try next? It looks like the upgrade may be going to 2.3 first.

Thanks

Below are the steps performed for upgrade :

  1. Back up the folder OJS and renamed to OJS.paul
  2. Copy the contents of 2.4.8 packages to folder OJS
  3. Edit the config.inc.php to match the database configuration. Database used was jamtorg_ojs.
  4. Copy the files folder and public folder from current installation.

Here is the output when we run the upgrade script :

jamtorg@tango [~/public_html/OJS]# php tools/upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 2.4.8.0]
[data: dbscripts/xml/upgrade/2.3.0_usersettings.xml]
[schema: lib/pkp/xml/schema/signoff.xml]
[data: dbscripts/xml/upgrade/2.3.0_preupdate.xml]
ERROR: Upgrade failed: DB: Table ‘jamtorg_ojs.copyed_assignments’ doesn’t exist

Hi @RickMath,

This suggests that the copyed_assignments table doesn’t exit in your OJS 2.2.4 database. It should – I suspect something has happened in your database backup/restore process, or that your config.inc.php is still pointing at the wrong database.

After restoring your 2.2.x database and checking your config.inc.php, try connecting to MySQL manually, using the credentials in config.inc.php, and ensuring that the copyed_assignments table exists. It definitely should with a 2.2.4 database.

Regards,
Alec Smecher
Public Knowledge Project Team

Alec

I have attached two screen shots before and after the attempted upgrade.

copyed 1,jpg is before and copyed 2.jpg is after.

What should we check for in the config.inc.php?

I checked the database before I gave them the go ahead and the table was there.

We can try again but any further suggestions would help. We do not assign copyeditors so the id is zero as you can see in the first shot. I have checked the articles table and the copyed_assignments tables for ids in the assignments table that do not exist in the articles table.

Copyed 1
Copyed 2

Thanks

I did check the config.inc.php file and the correct database is there.

Sorry for not checking this before the previous reply.

I received this from the hosting service. Also see above the apparent attacks to the forum.

Hello,

I have further tested the upgrade and found that the exact error is below :

====

A database error has occurred: Table 'jamtorg_ojsnew.counter_monthly_log' doesn't exis

====

From what i can see the table counter_monthly_log doesn’t exist in the jamtorg_old database as well and possible this is causing the issue with the upgrade. Please provide this detail to support forum and see what they have to say.

I also received this.

Hello,

I have tried once again using a new copy of database ‘jamtorg_ojsnew’ and found that after running the upgrade script, the database table copyed_assignments is missing from the database. I think this is causing the error with the upgrade. Somehow the database table is getting removed after running the upgrade script.