[Solved] Some contributor list articles disappear after upgrading from ojs 3.1.2.4 to 3.2.0.2

andrewdion04 · April 28, 2020, 3:29am

hi, I have a problem when I finished migrating from ojs 3.1.2.4 to 3.2.0.2, some contributor list articles disappeared after the upgrade which made the article name disappear, for example in this article http://ajstd.org/index.php/ajstd/article/view/582

Older articles using OJS 3.1.2.4 can be accessed at http://old.ajstd.org/index.php/ajstd/issue/view/77
contributor list exist before upgrade

The question is, how to manually fill in the contributor list through the database? or can it be an unpublish issue, then add contributors then publish again, will there be an effect on DOI?

Server Information

Setting Name	Setting Value
OS platform	Linux
PHP version	7.2.30
Apache version	Apache
Database driver	mysqli
Database server version	10.0.38-MariaDB
OJS Version	3.2.0.2

ctgraham · May 1, 2020, 1:09pm

I haven’t made the jump to 3.2 yet. @NateWr, do you have any guesses on the loss of contributors between 3.1.2 and 3.2?

NateWr · May 4, 2020, 9:03am

This is the first I’ve heard of such a case. @andrewdion04 would you be willing to share (privately) your pre-upgrade database, so that we can run an upgrade locally and reproduce the issue?

If so, please send it to me by DM.

andrewdion04 · May 4, 2020, 2:49pm

I’ll DM the database

andrewdion04 · May 4, 2020, 3:45pm

the bug has been resolved by upgrading using OJS 3.2.0.3

torres · May 26, 2020, 1:56pm

Good morning,
Solved? Where I can find the soluction? May you share this soluction for us?
I’m trying a long time migrate withou sucess.
Thanks a lot
@torres

torres · June 6, 2020, 6:32pm

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.

asmecher · June 8, 2020, 9:01pm

Hi @torres,

After you run the upgrade, do the contents of your authors and author_settings tables appear to be approximately correct, i.e. are there about the right number of rows? (You can compare this with your pre-upgrade database.)

Regards,
Alec Smecher
Public Knowledge Project Team

torres · June 10, 2020, 1:09pm

Dear @asmecher,
I am sending the tests you requested. I appreciate the attention.
Augusto Torres

DATABASE SERVER

$ psql ojsdatabase (database in production)
psql (9.5.21)
Type “help” for help.

DATABASE IN PRODUCTION

ojsdatabase=# SELECT COUNT(*) FROM authors;
count

1470
(1 row)

(9385 rows)

ojsdatabase=# SELECT COUNT(*) FROM author_settings;
count

9385
(1 row)

CREATING CLEAR DATABASE AND RESTORE DUMP FROM PRODUCTION DATABASE

ojsdatabase=# DROP DATABASE ojsdatabasenew;
DROP DATABASE
ojsdatabase=# CREATE DATABASE ojsdatabasenew;
CREATE DATABASE

pg_dump -U pgsql seerojsdatabase --inserts > Backup-seerojsdatabase-10062020.sql

POPULATING NEW DATABASE

psql ojsdatabasenew

ojsdatabasenew=# \i ~user/Backup-seerojsdatabase-10062020.sql

NECESSARY ALTER THIS TO RUN UPGRADE (elseif the process are broken)

ojsdatabasenew=# ALTER TABLE author_settings ALTER COLUMN setting_type DROP NOT NULL;
ALTER TABLE
ojsdatabasenew=# ALTER TABLE journal_settings ALTER COLUMN setting_type DROP NOT NULL;
ALTER TABLE
ojsdatabasenew=# ALTER TABLE authors ALTER COLUMN submission_id DROP NOT NULL;
ALTER TABLE

ojsdatabasenew=#\q

PREPARING WEBSERVER TO MIGRATE

webserver# cd /usr/local/www/
fetch http://pkp.sfu.ca/ojs/download/ojs-3.2.0-2.tar.gz

tar -xvzf ojs-3.2.0-2.tar.gz
chown -R www:www ojs-3.2.0-2
chmod -R 755 ojs-3.2.0-2

cp -R ./ojs-3.1.2-4/public ./ojs-3.2.0-2/
chown -R www:www ojs-3.2.0-2
cp -R ./ojs-3.1.2-4/config.ini.php ./ojs-3.2.0-2/

cd ojs-3.2.0-2/tools
php upgrade.php check
Code version: 3.1.2.4
Database version: 3.1.2.4
php upgrade.php upgrade
Successfully upgraded to version 3.2.0.2

DATABASE SERVER (COMING BACK)

ojsdatabasenew=# SELECT * from authors;

author_id | submission_id | seq | email | user_group_id | include_in_browse | publication_id
------------±-----------------±----±-------------±--------------±-----------------±---------------
1181 | 1005 | 1 | user@domain.com | 31 | 1 |

ojsdatabasenew# SELECT COUNT(*) FROM authors;
count

1470
(1 row)

ojsdatabasenew=# SELECT COUNT(*) FROM author_settings;
count

12299 <== DIFFRENT NUMBERS OF ROWS FROM PRODUCTION DATABASE
(1 row)

OBSSERVATIONS:
All procedures were performed on a copy of the production database, using version ojs-3.2.0.2, where articles are imported but the authors disappear. If migrations are made using ojs-3.2.0.3, all authors and articles disappear as reported in the history of this post

asmecher · June 10, 2020, 11:23pm

Hi @torres,

Could you privately send me a copy of your pre-upgrade database to test with?

Regards,
Alec Smecher
Public Knowledge Project Team

asmecher · June 18, 2020, 11:51pm

Hi all,

Thanks to @torres’ database dump, I was able to track down the problem with published content no longer being presented in the table of contents. It’s documented here, with a fix to be included in OJS/OMP 3.2.1: PostgreSQL upgrade to 3.2.0 resets all submissions to unpublished · Issue #6022 · pkp/pkp-lib · GitHub

Regards,
Alec Smecher
Public Knowledge Project Team

ji_farquharson · November 26, 2020, 2:31pm

I’ve just encountered this problem as well: since upgrading OJS from 3.1.2.1 to 3.2.1.2, the contributor list for all articles (published and unpublished) is now blank. Is there a fix for this? There is now no author information associated with any of the published articles.

ji_farquharson · November 30, 2020, 11:32pm

Hi @asmecher: is there a specific fix for this? I’ve encountered this problem on upgrading to 3.2.1.2.

ji_farquharson · December 19, 2020, 2:34pm

I’d appreciate any input here @NateWr, @asmecher, or anyone else who has encountered and resolved this problem, because there are obviously lots of knock-on effects of the contributor lists being empty (including how the articles appear on Scholar etc.). Worst-case scenario, can I manually add the missing entries to the database?

NateWr · December 21, 2020, 10:05am

Hi @ji_farquharson,

The main thing that happens with authors in the upgrade from 3.1.x to 3.2.x is that they are reassigned from a submission_id to a publication_id. So in 3.1.x your author table will look like this:

author_id	submission_id	etc…
1	1	…
2	1	…
3	2	…

And in 3.2.x your author table will look like this:

author_id	publication_id	etc…
1	1	…
2	1	…
3	2	…

I’d recommend checking your author records to see if the publication_id has been set correctly. If it hasn’t – if you see a bunch of NULL values there – then the current_publication_id might not be set correctly in the submissions table. Try the following SQL query to check for NULL values there:

select submission_id,current_publication_id from submissions where current_publication_id IS NULL;

(If there are NULL values, this suggests to me that the upgrade may not failed when it was run.)

In all cases, the ids should stay the same

ji_farquharson · December 21, 2020, 2:30pm

Thanks for the response. There are no NULL values in current_publication_id, but on checking the authors table I can see that submission_id hasn’t been reassigned for anything published before the upgrade: all pre-upgrade publication_id values are NULL. Is this expected, or should I update these values to reflect the submission_id data?

NateWr · December 22, 2020, 10:54am

So the author table has a publication_id column, but all the values for pre-upgrade authors are NULL? Something is going wrong there. I’m not sure if there is a way to match authors to publications anymore, unless you have got a backup of the original database. Do you?

If so, you can look at the submission_id for each author record in the old database. Then use that to match the author record to the correct publication_id in the new database. That will only work if none of the submissions have had new versions created since the update.

It sounds like you have submissions that have been done since the upgrade – so I’m guessing this is happening on your production database and not a test upgrade? If this is a test instance and you have the opportunity to try the upgrade again, I’d recommend that instead. Keep an eye on error logs and things to see if there’s a problem. This is the code that processes the author migration when upgrading:

github.com

pkp/ojs/blob/main/dbscripts/xml/upgrade/3.2.0_versioning.xml#L128-L144


		<!-- connect authors to publications -->
		<query driver="postgres,postgres64,postgres7,postgres8,postgres9">
			UPDATE authors
			SET publication_id = s.current_publication_id
			FROM submissions s, temp_authors ta
			WHERE authors.author_id = ta.author_id AND s.submission_id = ta.submission_id
		</query>
		<query driver="mysql,mysqli">
			UPDATE authors as a
			SET a.publication_id = (
				SELECT s.current_publication_id
				FROM submissions as s
				LEFT JOIN temp_authors as ta
					ON s.submission_id = ta.submission_id
				WHERE a.author_id = ta.author_id
			)
		</query>

ji_farquharson · December 22, 2020, 2:26pm

You are correct: the publication_id column exists, but hasn’t been filled (everything pre-upgrade is NULL). The submission_id column also exists, with all the pre-upgrade values (everything post-upgrade is NULL). I will go ahead and manually fill in the correct publication_id values (as you surmise, this error arose on the production database).

Thanks for all your help here.

NateWr · December 22, 2020, 2:58pm

Huh. That sounds to me like maybe the schema sync didn’t complete or something… Kind of baffled on that one. Do you by any chance have temp tables in your database? eg - temp_authors, temp_submissions?

ji_farquharson · December 22, 2020, 5:49pm

Nope. No temp tables that I can see, so they seem to have dropped successfully.

[Solved] Some contributor list articles disappear after upgrading from ojs 3.1.2.4 to 3.2.0.2

Server Information

Dear @asmecher, I am sending the tests you requested. I appreciate the attention. Augusto Torres

ojsdatabase=# SELECT COUNT(*) FROM authors; count

ojsdatabase=# SELECT COUNT(*) FROM author_settings; count

ojsdatabasenew# SELECT COUNT(*) FROM authors; count

ojsdatabasenew=# SELECT COUNT(*) FROM author_settings; count

Dear @asmecher,
I am sending the tests you requested. I appreciate the attention.
Augusto Torres

ojsdatabase=# SELECT COUNT(*) FROM authors;
count

ojsdatabase=# SELECT COUNT(*) FROM author_settings;
count

ojsdatabasenew# SELECT COUNT(*) FROM authors;
count

ojsdatabasenew=# SELECT COUNT(*) FROM author_settings;
count