Missing author given name - Upgrade from 3.1.2-4 to 3.2.1-4

[Ptran]

Hello, After the upgraded from 3.1.2-4 to 3.2.1-4. I’m having a couple of issues but couldn’t find any similar posts in the forum to help fix mine.

1. Missing or replaced author’s given names in both “All active” and “Archives” sections. ( Please see screenshots)

2. got replaced with “N/A,N/A” or double display the author’s names under Archives Issues -

My server specs:

I’ve turned on ‘stacktrace’, the upgrade process went quite smooth, it didn’t give me any PHP error logs besides couple warnings.

Anyone with suggestions to help fix these issues?
thanks in advance
-Peter

[rcgillis]

Hi @Ptran,

Interesting …
Are you able to provide a screenshot of when you try to edit the metadata for one of these submissions that is showing up as “N/A” - you may have to briefly unpublish and republish the article in order to do so.

-Roger
PKP Team

[Ptran]

Hi Roger,
Thanks for your reply, I’ve tried to unpublish and republish for both articles and issues. But the issue is still happenning the same. There are a couple more "warming " messages I don’t know if that would help you. Please see below

Any suggestions ?
thanks for all your help!
-PeterT

[NateWr]

Hi @Ptran,

It sounds like something’s not right with the author details. I’m not sure why, but I can see that in your upgrade process one key migration was skipped:

[code: Installer Installer::migrateUserAndAuthorNames (skipped)]

It looks like that migration will be skipped if the table users_tmp already exists. ojs/upgrade.xml at main · pkp/ojs · GitHub

It looks like that’s a fairly substantial migration: ojs/Upgrade.inc.php at main · pkp/ojs · GitHub

I might recommend trying to run the upgrade again from scratch, and make sure that no users_tmp table exists…

[Ptran]

Hi NateWr,

Thanks for your reply, I have restored my backup of the database before the upgrade and search for “users_tmp” table. But I couldn’t find any. Please see my screen shot below.

Any other suggestion?

thanks
-Ptran

[NateWr]

Have you tried to run the upgrade again to see if that migration is skipped?

[Ptran]

Yes, I did, the migration is skipped and similar missing author’s name issues are showing.

thanks
-Ptran

[NateWr]

I’d recommend checking the user_settings table before and after the migration to see if there are rows with a setting_name of givenName, and how the name entries change before and after the migration.

I’m not sure, but in 3.1.2-4 the given name setting may be firstName. The following SQL query will tell you the setting names in your database:

select setting_name from user_settings group by setting_name order by setting_name asc;

[Ptran]

Sorry, I’m not very good with MYSQL, but I can provide the screenshots before and after the migration as you suggested. Unfortunately, I don’t see any changes. What command to find out more details of the name entries?

BEFORE MIGRATION

AFTER MIGRATION

screen shots before migration - OJS version 3.1.2-4

After migration - OJS version 3.2.1-4

thanks
-Ptran

[NateWr]

Hi @Ptran,

Can you try running the following SQL query before and after upgrade?

select * from user_settings where setting_name="givenName" and user_id in (select user_id from user_settings where setting_name="familyName" and setting_value="Grzelczyk");

I suspect this is a localized names issue and hopefully this query will indicate what might be going on. Does your journal support more than one language? Which languages are enabled? What language is active in your screenshots?

[Ptran]

Here are the capture screenshots for your SQL query - before and after migration. It looks the same.

Before migration

After migration

My journal has English as the primary language.

Language setting before migration

Language setting after migration

I got a screenshot after the migration with all the Columns / Characters set and Collation. I’m not sure why only some are set to “utf8” - "utf8_general_ci and the rest are still in “latin1” - Latin1swedish_ci. Although I’ve created and set the database to “utf8” - “utf8_general_ci”. I don’t know if that is causing any issues.

any thoughts?

thanks
Ptran

[NateWr]

Hi @Ptran,

Sorry, I had you look in the user_settings table but we actually want to look in the author_settings table. I forgot the author records are distinct. So the SQL query that we want to run is:

select * from author_settings where setting_name="givenName" and author_id in (select author_id from author_settings where setting_name="familyName" and setting_value="Grzelczyk");

I’m not sure why only some are set to “utf8” - "utf8_general_ci and the rest are still in “latin1” - Latin1swedish_ci.

That’s definitely going to cause some problems and is common with older databases. Our admin guide has some guidance on how to convert tables to utf8.

[Ptran]

Hi,
Your new SQL query before migration.

And after migration:

these two screens gave the same results.

thanks
-P

[NateWr]

Hi @Ptran, unfortunately, I am stumped. The data looks correct to me. All I can suggest is that you take a look at the code itself and see if you can determine why the author string is coming out wrong. This is where the “short author string” (Name et al) is compiled:

pkp/pkp-lib/blob/stable-3_2_1/classes/publication/PKPPublication.inc.php#L180-L205

[Ptran]

Hi Nate, Thanks so much for all your help!. Unfortunately, I’m not a developer to be able to look at the code myself and try to debug it. Is there a way you can help file this as a bug to OJS developer so they can take a look? It doesn’t look like to me I can see anything on my end as a user. I have seven journals with major security holes that waiting to be upgrade and if this bug can’t be fix. It is going to a big problem for us.
thanks again.
-Ptran

[asmecher]

Hi all,

This may be related to Author name is not localized in How-to-Cite citation · Issue #6862 · pkp/pkp-lib · GitHub – which is already fixed in OJS 3.3.0-5, but there are also patches linked in the issue that may apply cleanly to 3.2.x.

Regards,
Alec Smecher
Public Knowledge Project Team

[Ptran]

Hi Alec,
Thanks for checking it out. I don’t know if I should start a new post or keep the new issues in here ( you let me know). I’ve followed your suggestion to do the upgrade from 3.1.2-4 to 3.3.0-5.
Here are my steps:

1. Drop my current testing DB-3.2.1-4
2. Created an empty DB with this SQL query
   %CREATE DATABASE mytestDB CHARACTER SET utf8 collate utf8_general_ci;
3. Restore my good working backup DB-3.1.2-4.
4. Restore my good working backup of “spool/files” data.
5. Extracted a new source code version 3.3.0-5.
6. Modified the config.inc.php file and changed all permission.
   The migration failed halfway with many errors. Please see below.
7. Ran the upgrade command: % PHP tools/upgrade upgrade

It failed halfway and came out with many errors. Please see below

=========
[pre-install]

[load: upgrade.xml]

[version: 3.3.0.5]

[code: Installer Installer::checkPhpVersion]

[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 (skipped)]

[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]

[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/categories.xml]

[schema: lib/pkp/xml/schema/notes.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]

[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: 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: 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::migrateUserAndAuthorNames (skipped)]

[data: dbscripts/xml/upgrade/3.2.0_stylesheet.xml]

[data: dbscripts/xml/upgrade/3.2.0_archiving_settings.xml]

[data: dbscripts/xml/upgrade/3.2.0_update.xml]

[data: dbscripts/xml/upgrade/3.2.0_navigation_menu_items_locale_change.xml]

[code: Installer Installer::migrateSiteLocales]

[code: Installer Installer::migrateSidebarBlocks]

[code: Installer Installer::migrateSiteStylesheet]

[code: Installer Installer::migrateMetadataSettings]

[code: Installer Installer::createLicenseTerms]

[code: Installer Installer::installEmailTemplate]

[code: Installer Installer::changeUserRolesAndStageAssignmentsForStagePermitSubmissionEdit]

[data: dbscripts/xml/upgrade/3.2.0_versioning.xml]

[data: dbscripts/xml/upgrade/3.2.0_versioning_submissionCategories.xml]

[data: dbscripts/xml/upgrade/3.2.0_last_activity.xml]

[data: dbscripts/xml/upgrade/3.2.0_url_path.xml]

[code: Installer Installer::setStatsEmailSettings]

[code: Installer Installer::fixLibraryFiles]

[code: Installer Installer::installEmailTemplate]

[note: docs/release-notes/README-3.2.0]

[data: dbscripts/xml/upgrade/3.2.1_update.xml]

[code: Installer Installer::installEmailTemplate]

[migration: PKPv3_2_1SubeditorCategoriesMigration]

[note: docs/release-notes/README-3.2.1]

[migration: PKPv3_3_0UpgradeMigration]

A submission file was expected but not found at journals/1/articles/1/submission/proof/1-12-2-1-10-20090602.doc.
…
…
…
A submission file was expected but not found at journals/1/articles/534/submission/534-1-3685-1-2-20191121.txt.

A submission file was expected but not found at journals/1/articles/534/submission/534-1-3685-2-2-20191121.txt.

A submission file was expected but not found at journals/1/articles/534/submission/534-1-3687-1-2-20191121.txt.

A submission file was expected but not found at journals/1/articles/599/submission/599-12-4288-1-2-20201006.doc.

ERROR: Upgrade failed: DB: PDOException: SQLSTATE[HY000]: General error: 1005 Can’t create table ‘testdb.#sql-469_10337’ (errno: 150) in /var/www/testdb/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115

Stack trace:

#0 /var/www/testdb/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(115): PDOStatement->execute(NULL)

#1 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(464): Doctrine\DBAL\Driver\PDOStatement->execute()

#2 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(‘alter table `re…’, Array)

#3 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘alter table `re…’, Array, Object(Closure))

#4 /opt/rh/httpd24/root/var/www/jeunesse/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(465): Illuminate\Database\Connection->run(‘alter table `re…’, Array, Object(Closure))

#5 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(102): Illuminate\Database\Connection->statement(‘alter table `re…’)

#6 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(290): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))

#7 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(151): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))

#8 /var/www/testdb/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(538): Illuminate\Database\Schema\Builder->table(‘review_round_fi…’, Object(Closure))

#9 /var/www/testdb/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(141): PKPv3_3_0UpgradeMigration->_migrateSubmissionFiles()

#10 /opt/rh/httpd24/root/var/www/jeunesse/lib/pkp/classes/install/Installer.inc.php(405): PKPv3_3_0UpgradeMigration->up()

#11 /var/www/testdb/lib/pkp/classes/install/Installer.inc.php(251): Installer->executeAction(Array)

#12 /var/www/testdb/lib/pkp/classes/install/Installer.inc.php(174): Installer->executeInstaller()

#13 /var/www/testdb/lib/pkp/classes/cliTool/UpgradeTool.inc.php(89): Installer->execute()

#14 /var/www/testdb/lib/pkp/classes/cliTool/UpgradeTool.inc.php(65): UpgradeTool->upgrade()

#15 /var/www/testdb/tools/upgrade.php(22): UpgradeTool->execute()

#16 {main}

Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[HY000]: General error: 1005 Can’t create table ‘jeunesse.#sql-469_10337’ (errno: 150) in /var/www/testdb/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18

Stack trace:

#0 /var/www/testdb/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(117): Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))

#1 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(464): Doctrine\DBAL\Driver\PDOStatement->execute()

#2 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(664): Illuminate\Database\Connection->Illuminate\Database{closure}(‘alter table `re…’, Array)

#3 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘alter table `re…’, Array, Object(Closure))

#4 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(465): Illuminate\Database\Connection->run(‘alter table `re…’, Array, Object(Closure))

#5 /var/www/jeunesse/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(102): Illuminate\Database\Connection->statement(‘alter table `re…’)

#6 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(290): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))

#7 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(151): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))

#8 /var/www/testdb/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(538): Illuminate\Database\Schema\Builder->table(‘review_round_fi…’, Object(Closure))

#9 /var/www/testdb/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(141): PKPv3_3_0UpgradeMigration->_migrateSubmissionFiles()

#10 /var/www/testdb/lib/pkp/classes/install/Installer.inc.php(405): PKPv3_3_0UpgradeMigration->up()

#11 /var/www/testdb/lib/pkp/classes/install/Installer.inc.php(251): Installer->executeAction(Array)

#12 /var/www/testdb/lib/pkp/classes/install/Installer.inc.php(174): Installer->executeInstaller()

#13 /var/www/testdb/lib/pkp/classes/cliTool/UpgradeTool.inc.php(89): Installer->execute()

#14 /var/www/testdb/lib/pkp/classes/cliTool/UpgradeTool.inc.php(65): UpgradeTool->upgrade()

#15 /var/www/testdb/tools/upgrade.php(22): UpgradeTool->execute()

#16 {main}

Next Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1005 Can’t create table ‘testdb.#sql-469_10337’ (errno: 150) (SQL: alter table review_round_files add constraint review_round_files_submission_file_id_foreign foreign key (submission_file_id) references submission_files (submission_file_id)) in /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php:671

Stack trace:

#0 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(631): Illuminate\Database\Connection->runQueryCallback(‘alter table `re…’, Array, Object(Closure))

#1 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Connection.php(465): Illuminate\Database\Connection->run(‘alter table `re…’, Array, Object(Closure))

#2 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Blueprint.php(102): Illuminate\Database\Connection->statement(‘alter table `re…’)

#3 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(290): Illuminate\Database\Schema\Blueprint->build(Object(Illuminate\Database\MySqlConnection), Object(Illuminate\Database\Schema\Grammars\MySqlGrammar))

#4 /var/www/testdb/lib/pkp/lib/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php(151): Illuminate\Database\Schema\Builder->build(Object(Illuminate\Database\Schema\Blueprint))

#5 /var/www/testdb/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(538): Illuminate\Database\Schema\Builder->table(‘review_round_fi…’, Object(Closure))

#6 /var/www/testdb/lib/pkp/classes/migration/upgrade/PKPv3_3_0UpgradeMigration.inc.php(141): PKPv3_3_0UpgradeMigration->_migrateSubmissionFiles()

#7 /var/www/testdb/lib/pkp/classes/install/Installer.inc.php(405): PKPv3_3_0UpgradeMigration->up()

#8 /var/www/testdb/pkp/classes/install/Installer.inc.php(251): Installer->executeAction(Array)

#9 /var/www/testdb/lib/pkp/classes/install/Installer.inc.php(174): Installer->executeInstaller()

#10 /var/www/testdb/lib/pkp/classes/cliTool/UpgradeTool.inc.php(89): Installer->execute()

#11 /var/www/testdb/lib/pkp/classes/cliTool/UpgradeTool.inc.php(65): UpgradeTool->upgrade()

#12 /var/www/testdb/tools/upgrade.php(22): UpgradeTool->execute()

#13 {main}

====================
any suggestions are appreciated !!
-ptran

[NateWr]

Hi @Ptran, the foreign key error looks related to Foreign key constraint error during upgrade in review_round_files.submission_file_id · Issue #6337 · pkp/pkp-lib · GitHub. That should have been fixed in 3.3.0-5, but it’s possible that you’re seeing a foreign key constraint for missing submission files that are not covered by that fix.

I’d investigate the messages that say “A submission file was expected but not found at…” and try to clean up any references to files that have been removed.

I don’t think that these error messages are related to the name issue that you’re facing. But maybe once you’ve got to 3.3.0-5 the issue will be fixed, as Alec suggests.

[Ptran]

Hi Alec and Nate,
After restored to my backup DB3.1.2-4.
I followed Nate’s Issue #6337 solution. I ran the SQL query:

SELECT submission_file_id FROM review_round_files LEFT JOIN submission_files USING (submission_file_id) WHERE submission_files.submission_file_id IS NULL

I got no result. Am I on the right track? What is the next possible step that I can try?

thanks
Ptran

[NateWr]

Looking at your screenshot, the columns in submission_files indicate that the database is a 3.3.x database. Some of the columns (eg - created_at) didn’t exist in 3.1.2-4.