[SOLVED] Error upgrading OJS 2.4.8-5 to 3.2.0-3. DB Error: Data too long for column 'setting_value'

Software Support
1

Hello.

Upgrading an OJS 2.4.8-5 to 3.2.0-3 i obtained this error:

PHP Warning:  Cannot use a scalar value as an array in /home/mari/Escritorio/UP/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/db/SettingsDAO.inc.php on line 35
PHP Fatal error:  Uncaught Exception: DB Error: Data too long for column 'setting_value' at row 1 Query: INSERT INTO journal_settings
					(journal_id, setting_name, setting_value, setting_type, locale)
					VALUES (?, ?, ?, ?, ?) in /home/mari/Escritorio/UP/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php:703
Stack trace:
#0 /home/mari/Escritorio/UP/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php(231): DAO->handleError(Object(ADODB_mysqli), 'INSERT INTO jou...')
#1 /home/mari/Escritorio/UP/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/db/SettingsDAO.inc.php(213): DAO->update('INSERT INTO jou...', Array)
#2 /home/mari/Escritorio/UP/7MAY20/pruebasSED/ojs-3.2.0-3/classes/install/Upgrade.inc.php(2193): SettingsDAO->updateSetting(1, 'about', Array, 'string', true)
#3 /home/mari/Escritorio/UP/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->concatenateIntoAbout(Object(Upgrade), Array)
#4 /home/mari/Escritorio/UP/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/instal in /home/mari/Escritorio//UP/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/db/DAO.inc.php on line 703

Thanks

3

Hi @juanito,

This is the concatenateIntoAbout step, which takes “About the Journal” information from several settings and builds them into a single setting to adapt to changes in the setup interface. It seems like one or several of these is extremely long, so the database column runs out of space. I’d suggest checking the settings for journal ID 1, particularly the focusAndScope, peerReviewProcess, publicationFrequency, and openAccessPolicy settings, to see if they’re overlong.

One approach to resolving this could be to disable that step for the upgrade, then review the settings and re-introduce any content via the UI, editing it to shorten if necessary. To do that, you’d need to skip the step for certain journals, e.g. by editing classes/install/Upgrade.inc.php, finding the loop inside the concatenateIntoAbout function:

while ($journal = $journals->next()) {

You could skip journal ID 1 by adding this line just below it:

if ($journal->getId() == 1) continue;

This would migrate the content for all journals except journal ID 1, which you’d have to tackle manually.

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like
4

Thank you very much @asmecher .

It’s a multijournal. What do you think if:

MariaDB [r]> ALTER TABLE journal_settings MODIFY COLUMN setting_value MEDIUMTEXT;

before upgrade action. Thanks.

5

SELECT on OJS 2 database before upgrade

MariaDB [r]> SELECT journal_id, setting_name, setting_type, LENGTH(setting_value) as len FROM journal_settings order by len DESC LIMIT 10;
+------------+------------------+--------------+-------+
| journal_id | setting_name     | setting_type | len   |
+------------+------------------+--------------+-------+
|          1 | customAboutItems | object       | 61474 |
|         18 | customAboutItems | object       | 52953 |
|         40 | customAboutItems | object       | 45846 |
|          1 | authorGuidelines | string       | 28689 |
|          3 | authorGuidelines | string       | 26983 |
|         22 | authorGuidelines | string       | 24268 |
|         40 | authorGuidelines | string       | 24111 |
|         18 | focusScopeDesc   | string       | 22195 |
|         16 | authorGuidelines | string       | 21543 |
|         57 | authorGuidelines | string       | 21048 |
+------------+------------------+--------------+-------+
10 rows in set (0.013 sec)
6

Hi @juanito,

Instead of running an ALTER TABLE statement, I’d suggest editing the type in the descriptor. Find the table description in dbscripts/xml/ojs_schema.xml:

<table name="journal_settings">
    ...
    <field name="setting_value" type="X"/>
    ...
</table>

Edit the X to XL. (See adodb.org for available types.)

If you just run the ALTER TABLE statement, when ADODB syncs the database descriptor against the database the type will be returned to TEXT.

If this change works, make sure you maintain the change when you upgrade OJS!

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like
7

Thanks @asmecher .

Sorry, i have now another error.

PKP-Database-Logger 1591324531,171: -----
(mysqli): UPDATE submissions SET stage_id=3 WHERE submission_id IN (SELECT DISTINCT submission_id FROM review_assignments) 
   
-----

PKP-Database-Logger 1591324531,171: -----
(mysqli): UPDATE submissions SET stage_id=3 WHERE stage_id = 1 AND submission_id IN (SELECT DISTINCT submission_id FROM review_rounds) 
   
-----

PKP-Database-Logger 1591324531,171: -----
(mysqli): UPDATE submissions SET stage_id=4 WHERE submission_id IN (SELECT e1.submission_id FROM edit_decisions e1 LEFT JOIN edit_decisions e2 ON (e2.edit_decision_id > e1.edit_decision_id AND e2.submission_id = e1.submission_id) WHERE e2.edit_decision_id IS NULL AND e1.decision = 1) 
   
-----

PKP-Database-Logger 1591324531,171: -----
(mysqli): UPDATE review_rounds SET stage_id=3 
   
-----

PKP-Database-Logger 1591324531,171: Query: UPDATE review_rounds SET stage_id=3 failed. Duplicate entry '10558-3-1' for key 'review_rounds_submission_id_stage_id_round_pkey'
PKP-Database-Logger 1591324531,171: 1062: Duplicate entry '10558-3-1' for key 'review_rounds_submission_id_stage_id_round_pkey'
							ADOConnection._Execute(UPDATE review_rounds SET stage_id=3, false)% line 1246, file: /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/lib/vendor/adodb/adodb-php/adodb.inc.php
						ADOConnection.Execute(UPDATE review_rounds SET stage_id=3)% line  442, file: /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php
					Installer.executeSQL(UPDATE review_rounds SET stage_id=3)% line  437, file: /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php
				Installer.executeSQL(Array[111])% line  395, file: /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php
			Installer.executeAction(Array[3])% line  263, file: /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php
ERROR: Upgrade failed: DB: Duplicate entry '10558-3-1' for key 'review_rounds_submission_id_stage_id_round_pkey'

I applied these sentences

CREATE TABLE review_rounds_old SELECT * FROM review_rounds;
DELETE FROM review_rounds WHERE review_round_id NOT IN (SELECT MIN(review_round_id) FROM review_rounds_old GROUP BY submission_id, round);
DROP TABLE review_rounds_old;

New errors:

PKP-Database-Logger 1591396325,2932: -----
(mysqli): SELECT * FROM submissions WHERE submission_id = 5017 
   
-----

PKP-Database-Logger 1591396325,2932: -----
(mysqli): SELECT * FROM submission_settings WHERE submission_id = '5017' 
   
-----

PKP-Database-Logger 1591396325,2932: -----
(mysqli): select * from `publications` as `p` where `p`.`submission_id` in (5017) order by `p`.`version` asc 
   
-----

PKP-Database-Logger 1591396325,2932: -----
(mysqli): INSERT INTO submission_supplementary_files
				(file_id, revision)
			VALUES
				(38976, 1) 
   
-----

PKP-Database-Logger 1591396325,2932: -----
(mysqli): DELETE FROM review_round_files
			WHERE submission_id = 5017 AND stage_id = 3 AND file_id = 38976 AND revision = 1 
   
-----

PKP-Database-Logger 1591396325,2932: -----
(mysqli): INSERT INTO review_round_files
				(submission_id, review_round_id, stage_id, file_id, revision)
			VALUES (5017, 2006, 3, 38976, 1) 
   
-----

PKP-Database-Logger 1591396325,2932: -----
(mysqli): SELECT r.*, r2.review_revision FROM review_assignments r
			LEFT JOIN review_rounds r2 ON (r.review_round_id = r2.review_round_id) WHERE	r.submission_id = 5017 AND r2.review_round_id = 2006 AND r2.stage_id = 3 ORDER BY review_id 
   
-----

PKP-Database-Logger 1591396325,2932: -----
(mysqli): SELECT * FROM users WHERE user_id = 5774 
   
-----

PHP Fatal error:  Uncaught Error: Call to a member function getFullName() on null in /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewAssignment/ReviewAssignmentDAO.inc.php:485
Stack trace:
#0 /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewAssignment/ReviewAssignmentDAO.inc.php(69): ReviewAssignmentDAO->_fromRow(Array)
#1 /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewAssignment/ReviewAssignmentDAO.inc.php(218): ReviewAssignmentDAO->_getReviewAssignmentsArray('SELECT r.*, r2....', Array)
#2 /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/classes/install/Upgrade.inc.php(1494): ReviewAssignmentDAO->getBySubmissionId('5017', 2006, 3)
#3 /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc.php(417): Upgrade->provideSupplementaryFilesForReview(Object(Upgrade), Array)
#4 /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/install/Installer.inc in /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewAssignment/ReviewAssignmentDAO.inc.php on line 485

I applied

DELETE FROM review_assignments WHERE review_id IN (SELECT r.review_id FROM review_assignments r LEFT JOIN users u ON (r.reviewer_id = u.user_id) WHERE u.user_id IS NULL);

But now

PKP-Database-Logger 1591468778,479: -----
(mysqli): SELECT * FROM users WHERE user_id = 7145 
   
-----

PKP-Database-Logger 1591468778,479: -----
(mysqli): SELECT * FROM user_settings WHERE user_id = '7145' 
   
-----

PKP-Database-Logger 1591468778,479: -----
(mysqli): SELECT * FROM submissions WHERE submission_id = 11142 
   
-----

PHP Fatal error:  Uncaught Error: Call to a member function getContextId() on null in /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewAssignment/ReviewAssignment.inc.php:542
Stack trace:
#0 /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewAssignment/ReviewAssignment.inc.php(518): ReviewAssignment->isRead()
#1 /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewRound/ReviewRound.inc.php(213): ReviewAssignment->getStatus()
#2 /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewRound/ReviewRoundDAO.inc.php(260): ReviewRound->determineStatus()
#3 /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewAssignment/ReviewAssignmentDAO.inc.php(467): ReviewRoundDAO->updateStatus(Object(ReviewRound))
#4 /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewAssignment/ReviewAssignmentDAO.inc.php(544): ReviewAssignmentD in /home/mari/Escritorio/EUS/7MAY20/pruebasSED/ojs-3.2.0-3/lib/pkp/classes/submission/reviewAssignment/ReviewAssignment.inc.php on line 542

Perhaps, i did all bad

Thanks

8

Hi @juanito,

Could you post that as a new topic? That’ll help to keep the forum organized.

Thanks,
Alec Smecher
Public Knowledge Project Team

9

I am sorry @asmecher , you are right

I created a new thread [SOLVED] Error upgrading OJS 2.4.8-5 to 3.2.0-3 : Uncaught Error: Call to a member function getContextId()

1 Like