Upgrade error: index "user_settings_user_id" does not exist

[Gizzard]

Hi all,

I'm upgrading from 2.1.1 to the most recent stable, 2.2.4. When I run the tools/upgrade script, I get the following error:

Code: Select all

# php tools/upgrade.php upgrade
[load: upgrade.xml]
[version: 2.2.4.0]
[data: upgrade/public_issue_ids.xml]
[data: upgrade/2.2.0_preupdate.xml]
[code: Installer Installer::correctCurrencies]
[code: Installer Installer::migrateIssueLabelAndSettings]
[code: Installer Installer::dropAllIndexes]
[schema: ojs_schema_stage1.xml]
[code: Installer Installer::setJournalPrimaryLocales]
[data: upgrade/2.2.0_localize.xml]
[data: upgrade/2.2.0_usersettings.xml]
ERROR: Upgrade failed: DB: ERROR:  index "user_settings_user_id" does not exist


I'm unclear as to whether this is the following bug:
http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=4562

If so, should I run the "Patch against OJS pre-2.3 CVS"? It seems to be suggested to wait to upgrade to 2.3 instead of applying this patch which will "likely be difficult" (see http://pkp.sfu.ca/support/forum/viewtopic.php?f=8&t=4898).

I'm kind of confused as to whether a stable release of 2.3 is available yet - if so, I'll use that which it appears does not contain this bug.

Any advice would be appreciated.

Thanks a lot.
Gary

[asmecher]

Hi Gary,

Are you sure you're not running an upgrade on a database that previously failed to upgrade? When an upgrade fails, it leaves the database in a partially-upgraded state that causes problems if you try to re-run the early upgrade steps again. You're best to restore your database from backup and try again.

Regards,
Alec Smecher
Public Knowledge Project Team

[Gizzard]

Thanks Alec,

I'm pretty sure this db hasn't had a failed previous upgrade, but I'll restore from backup and try again.

Should I be upgrading to 2.2.4 or 2.3?

Thanks
Gary

[asmecher]

Hi Gary,

If that doesn't work, try running the upgrade with the "debug" option in config.inc.php; then include the last few queries before the error message here.

OJS 2.2.4 is quite stable but hasn't received active development for some time. OJS 2.3.1 is not quite as stable (though still quite usable; you'll probably encounter a few minor bugs) but contains a lot of new features and tweaks. (We'll be releasing OJS 2.3.2 in about a month, and will probably remove the "development" disclaimer from the 2.3.x line at that point.)

If you do decide to go with OJS 2.3.1, I'd suggest patching http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=4828 *before* you run the upgrade script.

Regards,
Alec Smecher
Public Knowledge Project Team

[Gizzard]

Hi Alec,

I tried starting from scratch and upgrading to 2.3.1. I first applied the patch you suggested. This went ok. Then trying to run the upgrade script failed again, this time with a different error:

Code: Select all

     Function: UpgradeTool->execute()
  Server info:
   OS: Linux
   PHP Version: 5.1.6
   Apache Version: N/A
   DB Driver: postgres
   DB server version: PostgreSQL 8.1.18 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46)
-1: ERROR:  null value in column "user_id" violates not-null constraint
                                                        ADOConnection._Execute(INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, file_id, file_revision, date_notified, date_underway, date_comple..., false)% line  864, file: /data/escholarship/journals/lib/pkp/lib/adodb/adodb.inc.php
                                                ADOConnection.Execute(INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, file_id, file_revision, date_notified, date_underway, date_comple...)% line  440, file: /data/escholarship/journals/lib/pkp/classes/install/Installer.inc.php
                                        Installer.executeSQL(INSERT INTO signoffs (symbolic, assoc_type, assoc_id, user_id, file_id, file_revision, date_notified, date_underway, date_comple...)% line  431, file: /data/escholarship/journals/lib/pkp/classes/install/Installer.inc.php
                                Installer.executeSQL(Array[15])% line  396, file: /data/escholarship/journals/lib/pkp/classes/install/Installer.inc.php
                        Installer.executeAction(Array[3])% line  270, file: /data/escholarship/journals/lib/pkp/classes/install/Installer.inc.php
ERROR: Upgrade failed: DB: ERROR:  null value in column "user_id" violates not-null constraint

Any ideas?

Thanks a lot,
Gary

[asmecher]

Hi Gary,

Try removing the user_id <NOTNULL/> constraint on the signoffs table. Edit lib/pkp/xml/schema/signoff.xml and find it there. Remove it and try the upgrade again; report back here on what your success is and I'll commit it to our source code repository. (MySQL is a little more relaxed about these things.)

Regards,
Alec Smecher
Public Knowledge Project Team

[Gizzard]

Thanks again for your help Alec,

I followed your suggestion and it mitigated the original error. Then the same error occurred for another field, assoc_type. So I went through all the xml files in lib/pkp/xml/schema which contained this field and also removed the NOTNULL constraint for it. This seemed to let me go much further but now I get a table not found error:

Code: Select all

   File: /data/escholarship/journals/tools/upgrade.php line 35
     Function: UpgradeTool->execute()
  Server info:
   OS: Linux
   PHP Version: 5.1.6
   Apache Version: N/A
   DB Driver: postgres
   DB server version: PostgreSQL 8.1.18 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46)
-1: ERROR:  table "subscription_ip_old" does not exist
                                                                ADOConnection._Execute(DROP TABLE subscription_ip_old CASCADE, false)% line  864, file: /data/escholarship/journals/lib/pkp/lib/adodb/adodb.inc.php
                                                        ADOConnection.Execute(DROP TABLE subscription_ip_old CASCADE)% line  440, file: /data/escholarship/journals/lib/pkp/classes/install/Installer.inc.php
                                                Installer.executeSQL(DROP TABLE subscription_ip_old CASCADE)% line  431, file: /data/escholarship/journals/lib/pkp/classes/install/Installer.inc.php
                                        Installer.executeSQL(Array[1])% line  431, file: /data/escholarship/journals/lib/pkp/classes/install/Installer.inc.php
                                Installer.executeSQL(Array[1])% line  396, file: /data/escholarship/journals/lib/pkp/classes/install/Installer.inc.php
ERROR: Upgrade failed: DB: ERROR:  table "subscription_ip_old" does not exist

I've no idea what to do about this one.

Thanks Alec,

Gary

[asmecher]

Hi Gary,

That table should be created in dbscripts/xml/upgrade/2.3.0_subscription_ip.xml, then removed in dbscripts/xml/upgrade/2.3.0_subscription_ip2.xml; the upgrade process as a whole is coordinated by dbscripts/xml/upgrade.xml and each step is shown on the output of the upgrade script.

Did the error occur when the dbscripts/xml/upgrade/2.3.0_subscription_ip2.xml script was running? Did the dbscripts/xml/upgrade/2.3.0_subscription_ip.xml script run before that?

Regards,
Alec Smecher
Public Knowledge Project Team

[Gizzard]

Hi Alec,

I can see the call to both 2.3.0_subscription_ip.xml and 2.3.0_subscription_ip.xml in upgrade.xml.

However when I run the upgrade, I see a line like this:

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

but no corresponding line for 2.3.0_subscription_ip.xml, and no creation of that table (though it looks like a rename in the aforementioned file).

I can't seem to figure out why this is not being triggered (if that is indeed the case).

Thanks
Gary

[newone]

This has got to be one of the most annoying aspect of OJS and most unnecessary.

While most open-source programs have a way of upgrading that is fail-proof, this is not the case of OJS. Even WordPress, allows you to automatically update your installation, and it even notifies you when your installation is out of date.

Why can't OJS implement the same thing especially given the amount of posts on update and database error?

[mcrider]

Hi Gary,

I've replicated your bug and put up a patch at http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=5229. Could you try applying it to see if that fixes your problem?

Cheers,
Matt

[Gizzard]

Hi Matt and Alec,

That did the trick, thanks very much for all your help.

Kind regards
Gary

[asadaqain]

FYI.

We are running ojs 2.3.6 and were upgrading to 2.3.7 with postgresql 8.4.9

Ran into user_id not null issue on our test machine. Removed it from signoff.xml and that worked for us. Thanks.

Cheers,