Upgrade from OJS 2.2.4 to 2.4.8 Error

Software Support
1

We are trying to upgrade our installation from 2.2.4 to 2.4.8 and then on to 3.0.X

We are getting the error Upgrade to 2.4.8 failed with database error "Column ‘user_id’ cannot be null ".

Is there something we are doing wrong?

Thanks

Rick

2

Hi @RickMath,

Can you take a look at your PHP error log and see if there is further information about the error there?

Thanks,
Amanda Stevens
Public Knowledge Project Team

3

We do not see anything the Error Log in the public-html folder. Is there somewhere else we need to look or is there something else we need to activate.

This is the only message in the log
[05-Dec-2017 22:40:05 America/Denver] ojs2 has produced an error
Message: WARNING: strftime() [function.strftime]: It is not safe to rely on the system’s timezone settings. You are required to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected ‘America/Denver’ for ‘MST/-7.0/no DST’ instead
In file: /home/jamtorg/public_html/lib/pkp/lib/smarty/Smarty_Compiler.class.php
At line: 400
Stacktrace:
Server info:
OS: Linux
PHP Version: 5.3.29
Apache Version: N/A
DB Driver: mysql

4

Hi @RickMath,

The location of your PHP error log will depend on your hosting provider’s configuration, but usually it’s a file called error_log somewhere within your account. You should be able to see a log of your upgrade in the log file. Do you want to have another look?

Thanks,
Amanda Stevens
Public Knowledge Project Team

5

Here is the output with debug enabled. Hopefully you can help us interpret this.

php tools/upgrade.php upgrade

(mysql): SELECT * FROM versions WHERE current = 1

(mysql): SELECT * FROM versions WHERE current = 1

(mysql): SELECT * FROM versions WHERE current = 1

(mysql): SELECT * FROM versions WHERE current = 1

(mysql): SELECT * FROM versions WHERE current = 1

(mysql): SELECT * FROM versions WHERE current = 1

[pre-install]

(mysql): SELECT * FROM versions WHERE current = 1

[load: upgrade.xml]
[version: 2.4.8.2]
[data: dbscripts/xml/upgrade/2.3.0_usersettings.xml]

(mysql): SHOW TABLES

(mysql): select version()

(mysql): SHOW COLUMNS FROM user_settings

[schema: lib/pkp/xml/schema/signoff.xml]

(mysql): select version()

(mysql): SHOW INDEX FROM signoffs

(mysql): SHOW COLUMNS FROM signoffs

(mysql): SHOW COLUMNS FROM signoffs

(mysql): DROP INDEX signoff_symbolic ON signoffs

(mysql): ALTER TABLE signoffs MODIFY COLUMN signoff_id BIGINT NOT NULL AUTO_INCREMENT

(mysql): ALTER TABLE signoffs MODIFY COLUMN symbolic VARCHAR(32) NOT NULL

(mysql): ALTER TABLE signoffs MODIFY COLUMN assoc_type BIGINT NOT NULL DEFAULT 0

(mysql): ALTER TABLE signoffs MODIFY COLUMN assoc_id BIGINT NOT NULL DEFAULT 0

(mysql): ALTER TABLE signoffs MODIFY COLUMN user_id BIGINT NOT NULL

(mysql): ALTER TABLE signoffs MODIFY COLUMN file_id BIGINT

(mysql): ALTER TABLE signoffs MODIFY COLUMN file_revision BIGINT

(mysql): ALTER TABLE signoffs MODIFY COLUMN date_notified DATETIME

(mysql): ALTER TABLE signoffs MODIFY COLUMN date_underway DATETIME

(mysql): ALTER TABLE signoffs MODIFY COLUMN date_completed DATETIME

(mysql): ALTER TABLE signoffs MODIFY COLUMN date_acknowledged DATETIME

(mysql): ALTER TABLE signoffs MODIFY COLUMN user_group_id BIGINT

(mysql): ALTER TABLE signoffs ADD UNIQUE INDEX signoff_symbolic (assoc_type, assoc_id, symbolic, user_id, user_group_id, file_id, file_revision)

[data: dbscripts/xml/upgrade/2.3.0_preupdate.xml]

(mysql): SHOW TABLES

(mysql): select version()

(mysql): SHOW COLUMNS FROM groups

(mysql): SHOW COLUMNS FROM announcements

(mysql): SHOW COLUMNS FROM announcement_types

(mysql): SHOW COLUMNS FROM email_templates

(mysql): SHOW COLUMNS FROM email_templates_data

(mysql): INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, file_id, file_revision, date_notified, date_underway, date_completed, date_acknowledged) SELECT ‘SIGNOFF_COPYEDITING_INITIAL’, 257, c.article_id, c.copyeditor_id, a.copyedit_file_id, c.initial_revision, c.date_notified, c.date_underway, c.date_completed, c.date_acknowledged FROM copyed_assignments c LEFT JOIN articles a ON (c.article_id = a.article_id AND c.initial_revision > 0) WHERE c.copyeditor_id IS NOT NULL

(mysql): INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, file_id, file_revision, date_notified, date_underway, date_completed, date_acknowledged) SELECT ‘SIGNOFF_COPYEDITING_AUTHOR’, 257, c.article_id, a.user_id, a.copyedit_file_id, c.editor_author_revision, c.date_author_notified, c.date_author_underway, c.date_author_completed, c.date_author_acknowledged FROM copyed_assignments c LEFT JOIN articles a ON (c.article_id = a.article_id AND c.editor_author_revision > 0) WHERE a.user_id IS NOT NULL

(mysql): INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, file_id, file_revision, date_notified, date_underway, date_completed, date_acknowledged) SELECT ‘SIGNOFF_COPYEDITING_FINAL’, 257, c.article_id, c.copyeditor_id, a.copyedit_file_id, c.final_revision, c.date_final_notified, c.date_final_underway, c.date_final_completed, c.date_final_acknowledged FROM copyed_assignments c LEFT JOIN articles a ON (c.article_id = a.article_id AND c.final_revision > 0) WHERE c.copyeditor_id IS NOT NULL

(mysql): INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, file_id, date_notified, date_underway, date_completed, date_acknowledged) SELECT ‘SIGNOFF_LAYOUT’, 257, l.article_id, l.editor_id, l.layout_file_id, l.date_notified, l.date_underway, l.date_completed, l.date_acknowledged FROM layouted_assignments l

(mysql): INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, date_notified, date_underway, date_completed, date_acknowledged) SELECT ‘SIGNOFF_PROOFREADING_AUTHOR’, 257, p.article_id, a.user_id, p.date_author_notified, p.date_author_underway, p.date_author_completed, p.date_author_acknowledged FROM proof_assignments p LEFT JOIN articles a ON (p.article_id = a.article_id)

1048: Column ‘user_id’ cannot be null
ADOConnection._Execute(INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, date_notified, date_underway, date_completed, date_acknowledged) …, false)% line 864, file: /home/jamtorg/public_html/lib/pkp/lib/adodb/adodb.inc.php
ADOConnection.Execute(INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, date_notified, date_underway, date_completed, date_acknowledged) …)% line 452, file: /home/jamtorg/public_html/lib/pkp/classes/install/Installer.inc.php
Installer.executeSQL(INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, date_notified, date_underway, date_completed, date_acknowledged) …)% line 447, file: /home/jamtorg/public_html/lib/pkp/classes/install/Installer.inc.php
Installer.executeSQL(Array[10])% line 400, file: /home/jamtorg/public_html/lib/pkp/classes/install/Installer.inc.php
Installer.executeAction(Array[3])% line 269, file: /home/jamtorg/public_html/lib/pkp/classes/install/Installer.inc.php
ERROR: Upgrade failed: DB: Column ‘user_id’ cannot be null

6

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

7

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

Thanks - Just a thought.

Rick

8

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

9

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?

10

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

11

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

12

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

13

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?

14

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

15

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

16

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

17

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

18

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

19

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

20

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