[OJS 2.4.8.5 to 3.0.2.3] Upgrade failed: DB: Data too long

rensousa · May 28, 2020, 1:54am

Trying to upgrade ojs 2.4.8.5 to 3.2.0.3, on a Debian9 and PHP7.2 system. I get error message:

[schema: lib/pkp/xml/schema/common.xml]
ERROR: Upgrade failed: DB: Data too long for column 'setting_value' at row 5587

Upgrade check

php tools/upgrade.php check
Code version:      3.2.0.3
Database version:  2.4.8.5
Latest version:    3.2.0.3
Database version is older than code version
Run "tools/upgrade.php upgrade" to

Can someone help me ?

Regards,

Renato L. Sousa

asmecher · May 28, 2020, 4:00pm

Hi @rensousa,

I don’t think the type of any of the setting_value columns has changed, so either it must’ve been either extended manually in your 2.4.8-5 database, or something has changed regarding your character set configuration. I’d suggest checking the default character set in the database to ensure that it’s UTF-8.

Regards,
Alec Smecher
Public Knowledge Project Team

rensousa · May 28, 2020, 9:20pm

Hi @asmecher,

The default character set in the database is UTF8, as show below:

CREATE DATABASE ojs2_rvq /*!40100 DEFAULT CHARACTER SET utf8mb4 */

The create structure also shows that the default charset of table is UTF8:

DROP TABLE IF EXISTS `announcement_settings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `announcement_settings` (
  `announcement_id` bigint(20) NOT NULL,
  `locale` varchar(5) NOT NULL DEFAULT '',
  `setting_name` varchar(255) NOT NULL,
  `setting_value` mediumtext DEFAULT NULL,
  `setting_type` varchar(6) NOT NULL,
  UNIQUE KEY `announcement_settings_pkey` (`announcement_id`,`locale`,`setting_name`),
  KEY `announcement_settings_announcement_id` (`announcement_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

The “Localization settings” in config.inc.php file is as follows:

client_charset = utf-8
connection_charset = utf8

Do you see something wrong?

Regards,

Renato L. Sousa

asmecher · May 28, 2020, 10:36pm

HI @rensousa,

All of that looks OK; what are the column types for columns named setting_value in your OJS 2.x database for site_settings, user_settings, notification_settings, notification_subscription_settings, and plugin_settings?

Regards,
Alec Smecher
Public Knowledge Project Team

rensousa · May 29, 2020, 2:41pm

Hi @asmecher,

In all the tables mentioned, the type is:

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
|                           ...   
| setting_value | mediumtext  | YES  |     | NULL    |       |
|                           ...   
+---------------+-------------+------+-----+---------+-------+

Regards,

Renato L. Sousa

rensousa · May 29, 2020, 2:46pm

Hi @asmecher,

Is there any form of debugging that I can know in which table and what values originated the error?

Regards,

Renato L. Sousa

asmecher · May 29, 2020, 3:48pm

Hi @rensousa,

Try turning on the debug option in config.inc.php, restoring your database and files area from backup, and re-running the upgrade. This will be very verbose but only the last few messages will be relevant.

Regards,
Alec Smecher
Public Knowledge Project Team

rensousa · May 29, 2020, 7:13pm

Hi @asmecher

I enabled the debug options in the configuration, as suggested. I restored the database, the file area and ran the upgrade procedure again, but I didn’t get many details about the error:

[pre-install]
[load: upgrade.xml]
[version: 3.2.0.3]
[code: Installer Installer::checkPhpVersion]
[code: Installer Installer::syncSeriesAssocType]
[data: dbscripts/xml/upgrade/2.4.0_preupdate_usageStatistics.xml (skipped)]
[data: dbscripts/xml/upgrade/2.4.0_preupdate_usageStatistics2.xml (skipped)]
[code: Installer Installer::removeReviewEntries]
[data: dbscripts/xml/upgrade/3.0.0_preupdate.xml]
[data: dbscripts/xml/upgrade/3.0.0_adaptTimedViews.xml (skipped)]
[data: dbscripts/xml/upgrade/3.0.0_adaptReferrals.xml]
[data: dbscripts/xml/upgrade/3.0.0_adaptBooksForReview.xml]
[data: dbscripts/xml/upgrade/remove_timed_views_bots.xml (skipped)]
[data: dbscripts/xml/upgrade/3.0.0_preupdate_commentsToEditor.xml]
[data: dbscripts/xml/upgrade/3.1.0_preupdate_review_assignments.xml]
[data: dbscripts/xml/upgrade/3.1.0_preupdate_notes.xml]
[data: dbscripts/xml/upgrade/3.1.0_preupdate_payments.xml]
[data: dbscripts/xml/upgrade/3.1.1_preupdate_citations.xml]
[data: dbscripts/xml/upgrade/3.1.2_preupdate_user_author_names.xml]
[code: Installer Installer::migrateSubmissionCoverImages]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_email_templates.xml]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_versioning_articleGalleySettings.xml]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_versioning.xml]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_last_activity.xml]
[schema: lib/pkp/xml/schema/common.xml]
ERROR: Upgrade failed: DB: Data too long for column 'setting_value' at row 5587

Any idea what’s wrong?

Regards,

Renato L. Sousa

asmecher · May 29, 2020, 7:28pm

Hi @rensousa,

Hmm, you’re right, that’s not terribly helpful. For each of the tables above, try the follow query:

 SELECT MAX(LENGTH(setting_value)) FROM site_settings;

(…substituting the table name in place of site_settings). This will help identify which table likely contains the problematic entry or entries.

Regards,
Alec Smecher
Public Knowledge Project Team

rensousa · May 29, 2020, 11:33pm

Hi @asmecher,

Checking the values I see that I have serious problems with SPAMS users in my system.

The problem is in the user_settings table which displays huge values for setting_value. Excludes the greater of length but the error continues.

See the 10 largest sizes of the field.

 SELECT LENGTH(setting_value) as len FROM user_settings order by len DESC LIMIT 10;
+-------+
| len   |
+-------+
| 65557 |
| 65535 |
| 65483 |
| 60198 |
| 56464 |
| 55773 |
| 42306 |
| 40694 |
| 39765 |
| 37203 |
+-------+

More than 100 users are added to the database per day, even enabling the recaptcha version 2 and setting the validation_timeout to 2 days.
Daily I run a script via CRON that excludes (via merge) expired users.

What should I do ? What is the acceptable length for the update process to finish successfully?

Regards,

Renato L. Sousa

asmecher · May 30, 2020, 12:05am

Hi @rensousa,

I would expect a mediumtext field to support more than 2^16 (65536) characters – did you check the other tables? – but that appears to be the limit you’re hitting, with some content in user_settings already over that limit. I would suggest trying to merge the users with outsized lengths in setting_value before running the upgrade script, to see if that gets you beyond the error you were encountering.

There are some additional tools that might help mitigate spammers in the form of plugins available to OJS 3.x; hopefully once you’ve been able to make the upgrade some mix of these will discourage spammers.

Regards,
Alec Smecher
Public Knowledge Project Team

rensousa · May 30, 2020, 3:06pm

Hi @asmecher,

This upgrade is very difficult !!!

Deletes the largest lenght from the user_settings table and the upgrade proceeded, but stopped at another stage.

[schema: lib/pkp/xml/schema/submissions.xml]
ERROR: Upgrade failed: DB: Data too long for column 'setting_value' at row 14598

Analyzing the tables again, I found the article_settings table with huge values for the setting_value field.

  > SELECT user_id,setting_name,LENGTH(setting_value) as len FROM article_settings,articles where article_settings.article_id=articles.article_id  order by len DESC LIMIT 10;
+---------+--------------+-------+
| user_id | setting_name | len   |
+---------+--------------+-------+
|       1 | abstract     | 65565 |
|    2719 | abstract     | 65564 |
|    2600 | abstract     | 65539 |
|    2083 | abstract     | 50225 |
|    3262 | abstract     | 40311 |
|       1 | abstract     | 39813 |
|       1 | abstract     | 39443 |
|       1 | abstract     | 38692 |
|       1 | abstract     | 37789 |
|    5119 | abstract     | 37754 |
+---------+--------------+-------+

The big problem is that of the 10 largest lenght, 5 belong to the admin user (user_id=1) and I can’t just delete (merge) that user?

How can I solve this?

Regards,

Renato L. Sousa

asmecher · May 30, 2020, 3:14pm

Hi @rensousa,

Are those spam submissions, or valid ones?

Regards,
Alec Smecher
Public Knowledge Project Team

rensousa · May 30, 2020, 3:48pm

Hi @asmecher,

They are valid articles.
The field is injected with large HTML code.

Will I have to clean this make up by hand?

Regards,

Renato L .Sousa

asmecher · May 30, 2020, 5:25pm

Hi @rensousa,

In that case, it’s worth debugging why mediumtext columns are seemingly limited to 65536 characters when they should be longer. Would you be willing to privately send me a database dump to test an upgrade with?

Regards,
Alec Smecher
Public Knowledge Project Team

rensousa · May 30, 2020, 7:12pm

Hi @asmecher,

Yes, I really appreciate your help!
How can I send you the database dump?

Regards,

Renato L. Sousa

rensousa · May 30, 2020, 8:23pm

Hi @asmecher,

Which tables are part of the update process below:

[schema: lib/pkp/xml/schema/submissions.xml]
ERROR: Upgrade failed: DB: Data too long for column 'setting_value' at row 14598

I adjusted some records of the article_settings table, but it didn’t solve

Regards,

Renato L. Sousa

rensousa · June 3, 2020, 9:59pm

Hi @asmecher,

It will no longer be necessary to send the dump for you to test. I was able to identify the large fields and used a text browser to eliminate the HTML codes.
The update process ended successfully !!!

Thank you very much for your great help!

Regards,

Renato L. Sousa

asmecher · June 3, 2020, 10:03pm

Hi @rensousa,

Glad to hear you were able to get through this!

Regards,
Alec Smecher
Public Knowledge Project Team