DB ERROR value too long for type character varying(64)

Esteemed,
I have installed and working well with ojs-3.1.2.4, however when upgrading to ojs-3.2.0-3, I am getting the error and below, resulting in the loss of all author entries, article name and published pdf.

About facilities:
Postgres 9.5 + PHP 7.2 + Apache-2.4

Would anyone know how to solve this?
Thank you
Augusto

Installer.executeAction(Array[3])% line 263, file: /usr/local/www/data/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php
ERROR: Upgrade failed: DB: ERROR: value too long for type character varying(64)
PKP-Database-Logger 1588790632.13: -----
(postgres7): UPDATE sessions
SET
user_id = ?,
ip_address = ?,
user_agent = ?,
created = ?,
last_used = ?,
remember = ?,
data = ?,
domain = ?
WHERE session_id = ?
[ (0=>null) (1=>’’) (2=>’’) (3=>‘1588790632’) (4=>‘1588790632’) (5=>‘0’) (6=>’’) (7=>’’) (8=>‘611653832ee8ba333fe73101ea288f93’) ]

ERROR: value too long for type character varying(64)
STATEMENT:
UPDATE issues as i
SET url_path = (
SELECT ist.setting_value
FROM issue_settings as ist
WHERE ist.issue_id = i.issue_id
AND ist.setting_name = ‘pub-id::publisher-id’
)

Hi @torres,

Have a look at this thread: Error when updating from 3.1.2-4 to 3.2.0-1 with postgres - #7 by Adriano_Jose

Regards,
Alec Smecher
Public Knowledge Project Team

Drar @asmecher

I had already guided myself from this post but, I don’t understand what results are expected from this command.
Here I am getting zero lines in submit_settings.

Also, I don’t understand how @Adriano_Jose solved this problem, where he found long strings in the submit_galley_settings table.

I already upgraded the database to postgres9 and the upgrade is still running “postgres7.inc.php” routines. Shouldn’t I skip this routine and go straight to “postgres9.inc.php”?

Thank you
Augusto

ojsdatabase=# ALTER TABLE author_settings ALTER COLUMN setting_type DROP NOT NULL;
ALTER TABLE
ojsdatabase=# ALTER TABLE journal_settings ALTER COLUMN setting_type DROP NOT NULL;
ALTER TABLE
ojsdatabase=# SELECT setting_value FROM submission_settings WHERE setting_name=‘pub-id::publisher-id’ AND LENGTH(setting_value) >= 64;
setting_value

(0 rows)

ojs-3.2.0-3/tools# php upgrade.php check
Code version: 3.2.0.3
Database version: 3.1.2.4
Latest version: 3.2.0.3
Database version is older than code version
Run “upgrade.php upgrade” to update

ojs-3.2.0-3/tools# php upgrade.php upgrade
[data: dbscripts/xml/upgrade/3.2.0_url_path.xml]
ERROR: Upgrade failed: DB: ERROR: value too long for type character varying(64)

Hi @torres,
I ran the following sql:

SELECT * FROM submission_galley_settings WHERE setting_name='pub-id::publisher-id' AND LENGTH(setting_value) >= 64;

I found only one record, with galley_id = 11032.
Then I reduced the text:

update submission_galley_settings
SET setting_value = 'Responsabilidade, Direitos Autorais e Permissão para a Publ.'
WHERE galley_id = 11032;

After that it worked just fine.

@asmecher, I believe it is worth changing your sql in that post, to avoid doubts and confusions like this.

Hi @Adriano_Jose and @asmecher
Not yet :frowning:
Someting is very wrong here, searching…
Tks

Look at this:
SELECT * FROM submission_galley_settings WHERE setting_name=‘pub-id::publisher-id’ AND LENGTH(setting_value) >= 64;
galley_id | locale | setting_name | setting_value | setting_type
-----------±-------±-------------±--------------±-------------
(0 rows)

PHP Warning: pg_query(): Query failed: ERROR: value too long for type character varying(64) in /usr/local/www/data/ojs-3.2.0-3/lib/pkp/lib/vendor/adodb/adodb-php/drivers/adodb-postgres7.inc.php on line 259
PKP-Database-Logger 1588873784.19: -1: ERROR: value too long for type character varying(64)
ADOConnection._Execute(UPDATE issues as i SET url_path = (SELECT ist…, false)% line 1246, file: /usr/local/www/data/ojs-3.2.0-3/lib/pkp/lib/vendor/adodb/adodb-php/adodb.inc.php
ADOConnection.Execute( UPDATE issues as i SET url_path = ( SELECT ist…)% line 442, file: /usr/local/www/data/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php
Installer.executeSQL( UPDATE issues as i SET url_path = ( SELECT ist…)% line 437, file: /usr/local/www/data/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php
Installer.executeSQL(Array[4])% line 395, file: /usr/local/www/data/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php
Installer.executeAction(Array[3])% line 263, file: /usr/local/www/data/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php
ERROR: Upgrade failed: DB: ERROR: value too long for type character varying(64)
PKP-Database-Logger 1588873784.19: -----
(postgres8): UPDATE sessions
SET
user_id = ?,
ip_address = ?,
user_agent = ?,
created = ?,
last_used = ?,
remember = ?,
data = ?,
domain = ?
WHERE session_id = ?
[ (0=>null) (1=>’’) (2=>’’) (3=>‘1588873784’) (4=>‘1588873784’) (5=>‘0’) (6=>’’) (7=>’’) (8=>‘e95ba4e16c7e56cc86959efccd39cb56’) ]

I was able to partially migrate the system:
From:
Code version: 3.1.2.4
Database version: 3.1.2.4
Latest version: 3.2.0.3

To:
Code version: 3.2.0.3
Database version: 3.2.0.3
Latest version: 3.2.0.3
Your system is up to date

But all articles and pdf are gone.
3 2 1
The files regarding the submissions are in the files folder with the appropriate permissions.

How can I restore these entries?

thankful

Augusto

Hi @torres,

I think the problem you’re encountering is an issue ID that’s too long for the database. The queries above are a similar problem, but for articles instead of issues. Try this query in your old (pre-upgrade) database:

SELECT * FROM issue_settings WHERE setting_name='pub-id::publisher-id' AND LENGTH(setting_value) >= 64;

If you’re able to find and shorten the offending entry, then re-run the upgrade process successfully, that may resolve the next problem you’re encountering.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher
I had already checked this condition, following your previous tip.

Look:
SELECT * FROM issue_settings WHERE setting_name=‘pub-id::publisher-id’ AND LENGTH(setting_value) >= 64;
issue_id | locale | setting_name | setting_value | setting_type
----------±-------±-------------±--------------±-------------
(0 rows)

I found long records and audated:
SELECT * FROM public.issue_settings WHERE setting_name=‘pub-id::publisher-id’ AND LENGTH(setting_value) >= 64;

update public.issue_settings SET setting_value = ‘Kant e-Prints, S2 (NE), pp. 10-27 Mai-Ago-2017’ WHERE issue_id = 230;

After that, I managed to migrate the system without apparent flaws, but the articles do not appear in the new version.

This is the site in production: Publicações Eletrônicas - CLE/UNICAMP
And this is th new version site: https://www.cle.unicamp.br/ojs-3.2.0-3/
Thank you for your help
Augusto

Hi @torres,

Ah, I think you’ve cracked the issue you were having originally, then – could you post the new question on a new topic? That’ll help keep the forum organized. I’ll watch for the new topic and follow up there.

Thanks,
Alec Smecher
Public Knowledge Project Team

Not yet resolved!!!
A least using PostgreSQL, this problem persist in version 3.2.0.3, inclusive all of the articles names and authors or contributors gone. Making upgrade under 3.2.0.2 we have all of articles published, but without names of contributors. Its possible insert the contributors “one-to-one” editing a new version of papper.

Hi @torres,

Please don’t post the same content on several threads – someone will respond to your other post.

Thanks,
Alec Smecher
Public Knowledge Project Team