Database upgrade error when upgrading to 3.1.1.2

upgrade
ojs3

#1

When I upgrade my database (postgresql) from 3.1.0.1 to 3.1.1.2 I get the following error:

[code: Installer Installer::migrateStaticPagesToNavigationMenuItems]

[code: Installer Installer::migrateSRLocale]

DB Error: ERROR: value too long for type character varying(5)

ojs2: DB Error: ERROR: value too long for type character varying(5)

#2

Hi @Steve_Michaels,

What is the complete output of your upgrade script?

Regards,
Alec Smecher
Public Knowledge Project Team


#3

It’s taken a while to get back to this (we’re moving the library and changing ILS’s)

I’m still getting the error with 3.1.1.4

[pre-install]
[load: upgrade.xml]
[version: 3.1.1.4]

[code: Installer Installer::checkPhpVersion]
[data: dbscripts/xml/upgrade/3.1.1_preupdate_citations.xml (skipped)]
[schema: lib/pkp/xml/schema/common.xml]
[schema: lib/pkp/xml/schema/log.xml]
[schema: lib/pkp/xml/schema/announcements.xml]
[schema: lib/pkp/xml/schema/scheduledTasks.xml]
[schema: lib/pkp/xml/schema/temporaryFiles.xml]
[schema: lib/pkp/xml/schema/metadata.xml]
[schema: lib/pkp/xml/schema/reviews.xml]
[schema: lib/pkp/xml/schema/reviewForms.xml]
[schema: lib/pkp/xml/schema/controlledVocab.xml]
[schema: lib/pkp/xml/schema/submissions.xml]
[schema: lib/pkp/xml/schema/submissionFiles.xml]
[schema: lib/pkp/xml/schema/notes.xml]
[schema: lib/pkp/xml/schema/views.xml]
[schema: lib/pkp/xml/schema/genres.xml]
[schema: lib/pkp/xml/schema/tombstone.xml]
[schema: lib/pkp/xml/schema/rolesAndUserGroups.xml]
[schema: lib/pkp/xml/schema/metrics.xml]
[schema: lib/pkp/xml/schema/views.xml]
[schema: lib/pkp/xml/schema/libraryFiles.xml]
[schema: lib/pkp/xml/schema/navigationMenus.xml]
[schema: dbscripts/xml/ojs_schema.xml]
[data: dbscripts/xml/indexes.xml]

[code: Installer Installer::installDefaultNavigationMenus]
WARNING: The NavigationMenu (ContextId: 2, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 2, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
PHP Warning: file_put_contents(/var/www/html/pkp/cache/fc-locale-8c15ce7dfec31e437114d80f1f936272.php): failed to open stream: Permission denied in /var/www/html/pkp/lib/pkp/classes/cache/FileCache.inc.php on line 90
PHP Warning: file_put_contents(/var/www/html/pkp/cache/fc-locale-a7a4b1f73f3b922eb712521db2537797.php): failed to open stream: Permission denied in /var/www/html/pkp/lib/pkp/classes/cache/FileCache.inc.php on line 90
WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 1, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 0, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.

[code: Installer Installer::migrateStaticPagesToNavigationMenuItems]

[code: Installer Installer::migrateSRLocale]

DB Error: ERROR: value too long for type character varying(5)

ojs2: DB Error: ERROR: value too long for type character varying(5)

#4

Hi @Steve_Michaels,

It looks like one of your locale columns is too short. Can you restore from backup and try the upgrade again, but this time with the debug option in config.inc.php turned on? That’ll dump all the SQL queries the upgrade process runs, and the last one before the error message should be the problem query. Post it here and I’ll see what I can suggest.

Regards,
Alec Smecher
Public Knowledge Project Team


#5

(postgres7): UPDATE books_for_review_settings SET locale = ? WHERE locale = ?
[ (0=>‘sr_RS@latin’) (1=>‘sr_SR’) ]
-----



PHP Warning: pg_query_params(): Query failed: ERROR: value too long for type character varying(5) in /var/www/html/pkp/lib/pkp/lib/adodb/drivers/adodb-postgres7.inc.php on line 228
-1: ERROR: value too long for type character varying(5)
ADOConnection._Execute(UPDATE books_for_review_settings SET locale = ? WHERE locale = ?, Array[2])% line 1047, file: /var/www/html/pkp/lib/pkp/lib/adodb/adodb.inc.php
ADOConnection.Execute(UPDATE books_for_review_settings SET locale = ? WHERE locale = ?, Array[2])% line 228, file: /var/www/html/pkp/lib/pkp/classes/db/DAO.inc.php
DAO.update(UPDATE books_for_review_settings SET locale = ? WHERE locale = ?, Array[2])% line 2666, file: /var/www/html/pkp/classes/install/Upgrade.inc.php
Upgrade.migrateLocale(sr_SR, sr_RS@latin, s:5)% line 2777, file: /var/www/html/pkp/classes/install/Upgrade.inc.php
Upgrade.migrateSRLocale(Object:Upgrade, Array[1])% line 0, file:

DB Error: ERROR: value too long for type character varying(5)

ojs2: DB Error: ERROR: value too long for type character varying(5)

#6

Hi @Steve_Michaels,

Great, that’s helpful. For the moment this will require a manual correction before you run the upgrade script, as noted below. I’ve flagged this for attention if/when we rewrite the Objects for Review plugin, which is not currently available in OJS 3.x.

To work around this, after you restore from backup but before you execute the upgrade script, run the following SQL statement:

ALTER TABLE books_for_review_settings CHANGE COLUMN locale locale varchar(14) NOT NULL DEFAULT '';

Let me know whether this works! [ed: SQL corrected for a small typo.]

Regards,
Alec Smecher
Public Knowledge Project Team


#7

The table doesn’t exist in ojs.


#8

Hi @Steve_Michaels,

Sorry, there was a typo in the table name. I’ve corrected it above.

Regards,
Alec Smecher
Public Knowledge Project Team


#9

That got me started in the right direction. There were actually several tables that had the locale column the wrong width. Once that was corrected, it worked fine.

books_for_review_settings
group_settings
referral_settings
review_object_metadata_settings
review_object_type_settings
static_page_settings


#10

Hello, I am trying to update from 3.1.0.1 to 3.1.1-4, when I update the database with the command
(php upgrade.php upgrade) by console I receive the following alert:

WARNING: The NavigationMenu (ContextId: 2, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 2, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 1, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 1, Title: Primary Navigation Menu, Area: primary) will be skipped because the specified area has already a NavigationMenu attached.
WARNING: The NavigationMenu (ContextId: 0, Title: User Navigation Menu, Area: user) will be skipped because the specified area has already a NavigationMenu attached.

After the update I detect that not all the shipments appear, some are missing.
I have read in the forum a similar problem with the following solution before updating but it has not worked.

ALTER TABLE books_for_review_settings CHANGE COLUMN locale locale varchar (14) NOT NULL DEFAULT ‘’;

then try to make that change in the following tables
books_for_review_settings
group_settings
referral_settings
review_object_metadata_settings
review_object_type_settings
static_page_settings

I appreciate any suggestion
Thank you


#11

Hi @faud,

Can you post this question as a new topic? I’m not sure it’s the same issue as the one discussed above.

Regards,
Alec Smecher
Public Knowledge Project Team