Upgrade to 3.3.0.2 failed: Data truncated for column 'context_id' #6708

claytondf · 2021-02-03T00:25:28Z

Describe the bug
Dear,
I tried to perform the upgrade procedure 3 times without success, follows below the error log

To Reproduce
Steps to reproduce the behavior:

Back up your files and database
Remove files of existing installation.
Restore your config.inc.php with all new changes present in new version.

root@edu-srv-omp:/var/www/html/tools# php upgrade.php lastest
Upgrade tool
Usage: upgrade.php command
Supported commands:
check perform version check
latest display latest version info
upgrade execute upgrade script
download download latest version (does not unpack/install)
root@edu-srv-omp:/var/www/html/tools# php upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.3.0.2]
[code: Installer Installer::checkPhpVersion]
[code: Installer Installer::installDefaultNavigationMenus]
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]
[migration: PKPv3_3_0UpgradeMigration]
ERROR: Upgrade failed: DB: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'context_id' at row 1 (SQL: ALTER TABLE email_templates CHANGE context_id context_id BIGINT DEFAULT 0 NOT NULL)

Visit http://domain.com/[[DIR]]/index.php/index/install/upgrade and click on "Upgrade" throws above error.

What application are you using?
OMP 3.3.0.2

Additional information
Using PHP 7.4.14 and 10.3.27-MariaDB-0+deb10u1 - Debian 10

asmecher · 2021-02-03T01:12:16Z

@claytondf, this seems like a data error. Do you have an unusual (very large or non-numeric) value for context_id in email_templates in your 3.2.1-3 database?

claytondf · 2021-02-03T13:02:06Z

@asmecher I'm going to perform an analysis email_templates

I will perform an analysis on the email_templates table. The context_id column is NULL in all records

claytondf · 2021-02-03T13:31:04Z

@asmecher

There was an advance in the update process after I reset all email templates. Only it fell into another error :-(

[pre-install]
[load: upgrade.xml]
[version: 3.3.0.2]
[code: Installer Installer::checkPhpVersion]
[code: Installer Installer::installDefaultNavigationMenus]
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]
[migration: PKPv3_3_0UpgradeMigration]
ERROR: Upgrade failed: DB: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'url' in 'field list' (SQL: select url, navigation_menu_item_id from navigation_menu_items where context_id = 1)
root@edu-srv-omp:/var/www/html/tools#

asmecher · 2021-02-03T20:29:39Z

@claytondf, it looks like you first countered...

ERROR: Upgrade failed: DB: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'omp.journal_settings' doesn't exist (SQL: select js.journal_id as id , js.setting_value as locales from journal_settings as js inner join journals as j on j.journal_id = js.journal_id where js.setting_nam e = supportedFormLocales)

...which is due to #6710 (comment), which will be resolved shortly.

Then you edited the comment for a new message:

ERROR: Upgrade failed: DB: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'url' in 'field list' (SQL: select url, navigation_menu_item_id from navigation_menu_items where context_id = 1)

I think this might arise from attempting to re-run an upgrade on a database that has already experienced a failed upgrade. Did you restore your database from backup before trying again?

claytondf · 2021-02-03T21:22:41Z

@asmecher
I used snapshots (VM) 3.2.1-2;
upgraded to 3.2.1-3;
Reset all email templates;
and updated to version 3.3.0.2

I tried in several ways

root@edu-srv-omp:/var/www/html/tools# php upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.3.0.2]
[code: Installer Installer::checkPhpVersion]
[code: Installer Installer::installDefaultNavigationMenus]
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]
[migration: PKPv3_3_0UpgradeMigration]
Removing orphaned submission_files entry ID 223 with submission_id 64
Removing orphaned submission_files entry ID 224 with submission_id 64
Removing orphaned submission_files entry ID 225 with submission_id 64
Removing orphaned submission_files entry ID 226 with submission_id 64
Removing orphaned submission_files entry ID 227 with submission_id 64
Removing orphaned submission_files entry ID 230 with submission_id 64
Removing orphaned submission_files entry ID 231 with submission_id 64
Removing orphaned submission_files entry ID 238 with submission_id 68
Removing orphaned submission_files entry ID 243 with submission_id 74

ERROR: Upgrade failed: DB: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'omp.journal_settings' doesn't exist (SQL: select js.journal_id as id, js.setting_value as locales from journal_settings as js inner join journals as j on j.journal_id = js.journal_id where js.setting_name = supportedFormLocales)

claytondf · 2021-02-03T21:43:42Z

Thank @asmecher
I'll wait for the version 3.3.0-3.
the patch #6710, did not work for OMP.

asmecher · 2021-02-03T21:45:21Z

@claytondf, that issue is not resolved yet -- watch for an additional patch in the next day or so, or in the meantime you can change references to journal_settings to press_settings in that file as a temporary work-around. (Edit: Also journals to presses.)

claytondf · 2021-02-04T00:16:34Z

@asmecher
I even tried it, only there is no journal_settings in the OMPv3_3_0UpgradeMigration.inc.

OMPv3_3_0UpgradeMigration.inc.zip

asmecher · 2021-02-04T00:24:16Z

@claytondf, that's the wrong file -- look for PKPv3_3_0UpgradeMigration.inc.php (it'll be in lib/pkp/classes/migration/upgrade).

claytondf · 2021-02-04T03:23:55Z

@asmecher, I made the changes to the code, according to your guidance, but the error continues for the OMP.

	$rows = Capsule::table('press_settings as js')
		->join('journals as j', 'j.journal_id', '=', 'js.journal_id')
		->where('js.setting_name', '=', 'supportedFormLocales')
		->select(['js.journal_id as id', 'js.setting_value as locales'])
		->get();
	foreach ($rows as $row) {
		// account for some locale settings stored as assoc arrays
		$locales = empty($row->locales)	? [] : unserialize($row->locales);
		$locales = array_values($locales);
		Capsule::table('press_settings')
			->where('journal_id', '=', $row->id)
			->whereIn('setting_name', $settingsWithDefaults)
			->whereNotIn('locale', $locales)
			->delete();

root@edu-srv-omp:/var/www/html/tools# php upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.3.0.2]
[code: Installer Installer::checkPhpVersion]
[code: Installer Installer::installDefaultNavigationMenus]
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]
[migration: PKPv3_3_0UpgradeMigration]
Removing orphaned submission_files entry ID 223 with submission_id 64
Removing orphaned submission_files entry ID 224 with submission_id 64
Removing orphaned submission_files entry ID 225 with submission_id 64
Removing orphaned submission_files entry ID 226 with submission_id 64
Removing orphaned submission_files entry ID 227 with submission_id 64
Removing orphaned submission_files entry ID 230 with submission_id 64
Removing orphaned submission_files entry ID 231 with submission_id 64
Removing orphaned submission_files entry ID 238 with submission_id 68
Removing orphaned submission_files entry ID 243 with submission_id 74
ERROR: Upgrade failed: DB: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'omp.journals' doesn't exist (SQL: select js.journal_id as id, js.setting_value as locales from **press_settings** as js inn er join journals as j on j.journal_id = js.journal_id where js.setting_name = supportedFormLocales)

asmecher · 2021-02-04T03:42:49Z

@claytondf, you'll also need to change journals to presses. Or wait for a patch to come out for this -- likely tomorrow.

claytondf · 2021-02-04T10:37:17Z

@asmecher, Thank you! I'll wait for the patch!
"Data truncated for column 'context_id in email_templates" is it a bug too ?

asmecher · 2021-02-08T21:44:36Z

@claytondf, can you try setting email_templates.context_id to 0 where it's currently NULL?

claytondf · 2021-02-09T14:41:13Z

@asmecher

I have already successfully updated the system to version 3_3_0-3, and for updating it was necessary to reset all email templates. At the moment I can no longer simulate the problem.
I thank you for all the support in solving this problem.
Thank you very much.

claytondf · 2021-02-10T16:51:15Z

@asmecher,
Describe the bug
Unfortunately the OMP system does not allow you to change and save email templates.

To Reproduce
Steps to reproduce the behavior:

WorkFlow->Emails->Email Templates

Meu.Video.mp4

claytondf · 2021-02-10T17:29:22Z

@asmecher

Log to Apache2:

[Wed Feb 10 14:23:03.147849 2021] [:notice] [pid 21927] ModSecurity: Status engine is currently disabled, enable it by set SecStatusEngine to On.
[Wed Feb 10 14:23:03.232939 2021] [mpm_prefork:notice] [pid 21928] AH00163: Apache/2.4.38 (Debian) OpenSSL/1.1.1i configured -- resuming normal operations
[Wed Feb 10 14:23:03.232992 2021] [core:notice] [pid 21928] AH00094: Command line: '/usr/sbin/apache2'
[Wed Feb 10 14:24:47.494142 2021] [php7:notice] [pid 21930] [client 170.150.1xx.x:56637] PHP Notice: Trying to access array offset on value of type null in /var/www/html/plugins/themes/default/DefaultThemePlugin.inc.php on line 151, referer: https://xxx.xxx.xxx/index.php/editoraunb/management/settings/workflow
[Wed Feb 10 14:24:47.744830 2021] [php7:notice] [pid 21930] [client 170.150.1xx.x:56637] PHP Notice: Trying to access array offset on value of type null in /var/www/html/plugins/themes/default/DefaultThemePlugin.inc.php on line 151, referer: https://xxx.xxx.xxx/index.php/editoraunb/management/settings/workflow
[Wed Feb 10 14:24:48.199674 2021] [php7:notice] [pid 21930] [client 170.150.1xx.x:56637] PHP Notice: Trying to access array offset on value of type null in /var/www/html/plugins/themes/default/DefaultThemePlugin.inc.php on line 151, referer: https://xxx.xxx.xxx/index.php/editoraunb/management/settings/workflow
[Wed Feb 10 14:24:48.303025 2021] [php7:notice] [pid 21929] [client 170.150.1xx.x:56642] PHP Notice: Trying to access array offset on value of type null in /var/www/html/plugins/themes/default/DefaultThemePlugin.inc.php on line 151, referer: https://xxx.xxx.xxx/index.php/editoraunb/management/settings/workflow
[Wed Feb 10 14:24:48.315518 2021] [php7:notice] [pid 21933] [client 170.150.1xx.x:56640] PHP Notice: Trying to access array offset on value of type null in /var/www/html/plugins/themes/default/DefaultThemePlugin.inc.php on line 151, referer: https://xxx.xxx.xxx/index.php/editoraunb/management/settings/workflow
[Wed Feb 10 14:24:48.319620 2021] [php7:notice] [pid 21929] [client 170.150.1xx.x:56642] PHP Notice: Undefined index: en_US in /var/www/html/lib/pkp/controllers/grid/settings/submissionChecklist/SubmissionChecklistGridHandler.inc.php on line 98, referer: https://xxx.xxx.xxx/index.php/editoraunb/management/settings/workflow
[Wed Feb 10 14:24:48.321980 2021] [php7:notice] [pid 21931] [client 170.150.1xx.x:56641] PHP Notice: Trying to access array offset on value of type null in /var/www/html/plugins/themes/default/DefaultThemePlugin.inc.php on line 151, referer: https://xxx.xxx.xxx/index.php/editoraunb/management/settings/workflow
[Wed Feb 10 14:24:48.352248 2021] [php7:notice] [pid 21932] [client 170.150.1x.x:56639] PHP Notice: Trying to access array offset on value of type null in /var/www/html/plugins/themes/default/DefaultThemePlugin.inc.php on line 151, referer: https://xxx.xxx.xxx/index.php/editoraunb/management/settings/workflow
[Wed Feb 10 14:24:57.612411 2021] [php7:notice] [pid 21995] [client 170.150.1xx.x:56643] PHP Notice: Trying to access array offset on value of type null in /var/www/html/plugins/themes/default/DefaultThemePlugin.inc.php on line 151, referer: https://xxx.xxx.xxx/index.php/editoraunb/management/settings/workflow

asmecher · 2021-02-10T17:32:09Z

@claytondf, that looks like a different issue; can you open a new entry for it?

NateWr changed the title ~~Upgrade failed: DB: Version 3.2.1.3 to 3.3.0.2~~ Upgrade to 3.3.0.2 failed: Data truncated for column 'context_id' Feb 3, 2021

claytondf closed this as completed Feb 10, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Upgrade to 3.3.0.2 failed: Data truncated for column 'context_id' #6708

Upgrade to 3.3.0.2 failed: Data truncated for column 'context_id' #6708

claytondf commented Feb 3, 2021 •

edited

Loading

asmecher commented Feb 3, 2021

claytondf commented Feb 3, 2021

claytondf commented Feb 3, 2021 •

edited

Loading

asmecher commented Feb 3, 2021

claytondf commented Feb 3, 2021

claytondf commented Feb 3, 2021

asmecher commented Feb 3, 2021 •

edited

Loading

claytondf commented Feb 4, 2021

asmecher commented Feb 4, 2021

claytondf commented Feb 4, 2021

asmecher commented Feb 4, 2021

claytondf commented Feb 4, 2021

asmecher commented Feb 8, 2021

claytondf commented Feb 9, 2021

claytondf commented Feb 10, 2021

claytondf commented Feb 10, 2021

asmecher commented Feb 10, 2021

Upgrade to 3.3.0.2 failed: Data truncated for column 'context_id' #6708

Upgrade to 3.3.0.2 failed: Data truncated for column 'context_id' #6708

Comments

claytondf commented Feb 3, 2021 • edited Loading

asmecher commented Feb 3, 2021

claytondf commented Feb 3, 2021

claytondf commented Feb 3, 2021 • edited Loading

asmecher commented Feb 3, 2021

claytondf commented Feb 3, 2021

claytondf commented Feb 3, 2021

asmecher commented Feb 3, 2021 • edited Loading

claytondf commented Feb 4, 2021

asmecher commented Feb 4, 2021

claytondf commented Feb 4, 2021

asmecher commented Feb 4, 2021

claytondf commented Feb 4, 2021

asmecher commented Feb 8, 2021

claytondf commented Feb 9, 2021

claytondf commented Feb 10, 2021

claytondf commented Feb 10, 2021

asmecher commented Feb 10, 2021

claytondf commented Feb 3, 2021 •

edited

Loading

claytondf commented Feb 3, 2021 •

edited

Loading

asmecher commented Feb 3, 2021 •

edited

Loading