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
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
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
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
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
Here is the output with debug enabled. Hopefully you can help us interpret this.
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
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 :
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