OJS 3.1.2.1 - 3.1.2.4 - Upgrade failed due to corrupt 'submission search object keywords' table

Software Support
,
1

Hi,

We are currently on OJS 3.1.2.1 and are attempting to upgrade to OJS 3.1.2.4.

We downloaded the new version of OJS and pointed it to the current database.

When we ran the upgrade script, it ran for 5 minutes and returned the following error:

[root@XXXXX ojs-3.1.2-4]# php tools/upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.1.2.4]
[code: Installer Installer::checkPhpVersion]
[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]
ERROR: Upgrade failed: DB: Index for table 'submission_search_object_keywords' is corrupt; try to repair it
[root@XXXXX ojs-3.1.2-4]#

After checking the submission_search_object_keywords table we noticed that it was timing out and only loading intermittently.

After checking the properties of the table you can see that it’s very large at ~ 2.8GB, much too large for one table.

We’re thinking that because it’s too large, the upgrade resulted with corrupt table error messages.

Can you recommend the best way to approach this so we can upgrade smoothly.

Thanks in advance for your help.

Cheers,
Steve

2

hi @scater

My suggestion is to truncate the submission_search_object_keywords table completely (with the MySQL truncate command), perform your upgrade, and then use the CLI tool for rebuilding the search index afterward.

php tools/rebuildSearchIndex.php

1 Like
3

Thank you @jnugent we will do that.
Best regards,

4

Good Morning @jnugent,

So we truncated the submission_search_object_keywords table completely (with the MySQL truncate command) then ran the

php tools/rebuildSearchIndex.php

this table submission_search_object_keywords had 40163196 rows before and now after truncate/reindex it has 2637292 rows that is significantly less, is this expected or there might be issue during the indexing?

Best regards,

Dung.

1 Like
5

Hi @dung,

That may be normal. Your index was corrupt on the original table, and there may have been hold overs or stale records in the table from before the upgrade. How does the search look now, if you run some tests? Can you find sample articles if you search for them?

Cheers,

6

Hello @jnugent,

I have re-indexed anyways before I asked this question, re-indexing is in process I will test after this process is completed.

thank you kindly!

Dung.

7

Hi @jnugent,

Thanks for your help with this. Just so I know I’m looking in the right place, where can I test this search in OJS? I assumed it had something to do with adding keywords but by your words:

Can you find sample articles if you search for them?

…Makes me think I should be testing this elsewhere.

Cheers,
Steve

8

Hi @scater

I was just referring to the regular search engine that is a part of OJS. After rebuilding the search index I’d spot check to see if I can find articles that I know show be indexed.

Cheers

1 Like
9

I get you, thanks @jnugent !

Cheers,
Steve

10

I would like to edit my comment “So we truncated the submission_search_object_keywords table completely (with the MySQL truncate command) then ran the - php tools/rebuildSearchIndex.php” - this should say 1) truncate the table 2) upgrade 3) rebuild search index.