How to check if you DB is ready for OJS3?

[marc]

Hi,

I’m starting to move some of my 2.4.x journals to 3.1.x… but some are quite old (10 years or more) and looks like DB data is a little messed up.

Those journals work perfectly in OJS 2 but during upgrade I get duplicated index errors or upgrade simply fails (without showing any error)…

So the question is: Is there a way to check the DB data integrity to find and fix the offending rows that don’t let upgrade do it’s job?

Cheers,
m.

[marc]

I see in forum that usually migration errors is due:
a) Partial migration corrupts the DB.
b) Doing migration without setting installed=Off in config.inc.php
c) Permissions issue.

I checked this twice in this old DB and I’m still getting an migration error.

As is explained in this forum setting config var to “debug=On” and running upgrade from commandline gives more info.

In my case log shows this:

Query: INSERT INTO review_round_files (submission_id, review_round_id, stage_id, file_id, revision) SELECT afm.article_id, rr.review_round_id, 3, afm.file_id, afm.revision FROM article_files_migration afm, articles_migration am, review_rounds rr WHERE am.revised_file_id = afm.file_id AND rr.submission_id = afm.article_id AND rr.round = afm.round failed. Duplicate entry '373-48-452-1' for key 'review_round_files_pkey'
1062: Duplicate entry '373-48-452-1' for key 'review_round_files_pkey'
							ADOConnection._Execute(INSERT INTO review_round_files (submission_id, review_round_id, stage_id, file_id, revision) SELECT afm.article_id, rr.review_ro..., false)% line 1051, file: /var/www/html/lib/pkp/lib/adodb/adodb.inc.php
						ADOConnection.Execute(INSERT INTO review_round_files (submission_id, review_round_id, stage_id, file_id, revision) SELECT afm.article_id, rr.review_ro...)% line  440, file: /var/www/html/lib/pkp/classes/install/Installer.inc.php
					Installer.executeSQL(INSERT INTO review_round_files (submission_id, review_round_id, stage_id, file_id, revision) SELECT afm.article_id, rr.review_ro...)% line  435, file: /var/www/html/lib/pkp/classes/install/Installer.inc.php
				Installer.executeSQL(Array[111])% line  396, file: /var/www/html/lib/pkp/classes/install/Installer.inc.php
			Installer.executeAction(Array[3])% line  265, file: /var/www/html/lib/pkp/classes/install/Installer.inc.php
ERROR: Upgrade failed: DB: Duplicate entry '373-48-452-1' for key 'review_round_files_pkey'

I can’t find any reference in to 373-48-452-1 in db or in the original files.

I found a post that show a very similar error, but the workaround is not working in my case:

Failure when upgrading from OJS 2.4.8.0 to 3.0 (Postgres)

Hi @asmecher Is that query. But it’s insane. ojs3=# select count(*) from submission_files; count 179843 (1 row) Time: 36.331 ms ojs3=# select count(*) from articles_migration; count 92639 (1 row) Time: 30.285 ms ojs3=# select count(*) FROM submission_files sf, articles_migration am WHERE am.submission_file_id=sf.file_id; count 36354 (1 row) Time: 78.130 ms Is there some explanation? Is there other way to upgrade? Regards, Tarcisio Pereira.

Any suggestion about how to find the offending row/article?

As I asked in the first post… what about an script to check the DB integrity?
Most of the questions published by sysadmins in this forum will be selfanswered.

Cheers,
m.

[bozana]

Hi @marc

Hmm… Well… We do not have any script to check the DB integrity…

Where exactly in the upgrade process, in which function, is the error occurring? – What is the last log entry in your console where you start the upgrade?

Eventually this issue could help: Upgrade to 3.0.0: pkey violation on review_rounds · Issue #1921 · pkp/pkp-lib · GitHub

Best,
Bozana

[marc]

Hi Bozana,

Thanks for your answer.

About the script… may be we don’t need a separate script to check the DB integrity, but we can extend the upgrade.php one to detect usual issues as:

a) partial migration: just checking ojs3 new or temporary tables?
b) set installed to off: so the upgrade script will refuse to run.
c) permissions: check permissions in file’s folders.

I don’t want to give you all more work… indeed I thinks those improvements will save a lot of time for both, the installers and the PKP forum supporters. But it’s just an idea, so if is ok for you, let’s talk about this in the tech meeting.

So let me change this post focus to my specific DB issue.

Today I also arrived to this post and looks very close to my issue.

The author published some queries to find duplicated rows (I translated them to pure mysql). Here you have my results:

SELECT COUNT(*) FROM review_rounds;
# 1749

SELECT COUNT(DISTINCT `submission_id`) FROM review_rounds;
# 1538

SELECT  COUNT(DISTINCT(CONCAT(`submission_id`,"-",IFNULL(`stage_id`, 'NULL'),"-",`round`))) FROM    review_rounds;
# 1747

SELECT COUNT(DISTINCT `review_round_id`) FROM review_rounds;
# 1749

So, looks like two rows are wrong. A little bit more mysql magic shows the culpit:

SELECT DISTINCT(CONCAT(`submission_id`," | ",IFNULL(`stage_id`, 'NULL')," | ",`round`)) as DuplicatedRows, COUNT(*) c FROM review_rounds GROUP BY DuplicatedRows HAVING c > 1;
# 1413 | NULL | 1	3

So looks like there are 3 rows in review_rounds with “submission_id = 1413” that are:

submission_id	round	review_revision	status	review_round_id	stage_id
1413	1	1	NULL	930	NULL
1413	1	1	NULL	931	NULL
1413	1	1	NULL	949	NULL

So, compared to a working row that it’s supposed to work fine:

submission_id	round	review_revision	status	review_round_id	stage_id
1436	1	1	NULL	948	NULL
1436	2	2	NULL	1098	NULL
1436	3	4	NULL	1150	NULL

… IMHO, looks like “round” or “review_revision” is wrong because in 1413 is not incremental.

I will modify both to see if the upgrade script is now able to work and I’ll be back.

[marc]

Discourse remembers me that I missed to publish this.

A simplification of the former process: Run this query to find duplicate rows (if any) in “review_rounds” table:

SELECT COUNT(*) as c, review_rounds.* FROM review_rounds GROUP BY review_round_id HAVING c > 1

Cheers,
m.

[marc]

Sorry, but there is an error in the query. It finds duplicated IDs in field review_round_id of the review_round table… and this is not what we are looking for.

We need to find rows with duplicated submission_id&round. This is the right way to get those files:

SELECT rr.* FROM review_rounds as rr WHERE rr.review_round_id 
    NOT IN (SELECT MIN(rrr.review_round_id) FROM review_rounds as rrr 
            GROUP BY rrr.submission_id, rrr.round);

Yes… I’m still digging into this.

Cheers,
m.

[marc]

SUMMARY:

The issue was fixed removing duplicated rows in review_rounds table:

SELECT rr.* FROM review_rounds as rr WHERE rr.review_round_id 
NOT IN (SELECT MIN(rrr.review_round_id) FROM review_rounds as rrr 
        GROUP BY rrr.submission_id, rrr.round);

Issue was also related with DB integrity issues in articles’ table:

SELECT * FROM articles WHERE revised_file_id = review_file_id

More info about the issue here: Add integrity checks before upgrade process · Issue #3351 · pkp/pkp-lib · GitHub

The initial discussion about the DB check generated a thread about how to improve the upgrade.php script here:

Add integrity checks before upgrade process

Cheers,
m.