Database upgrade problem from 2.4.7.1 to 3.1.1-4

Hi,

I have problem upgrading OJS. When I run:
php tools/upgrade.php upgrade

root@vm-test02:/var/www/html/ojs#  
root@vm-test02:/var/www/html/ojs# php tools/upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 3.1.1.4]

[code: Installer Installer::checkPhpVersion]

[code: Installer Installer::syncSeriesAssocType]
[data: dbscripts/xml/upgrade/2.4.0_preupdate_usageStatistics.xml (skipped)]
[data: dbscripts/xml/upgrade/2.4.0_preupdate_usageStatistics2.xml (skipped)]

[code: Installer Installer::removeReviewEntries]
[data: dbscripts/xml/upgrade/3.0.0_preupdate.xml]
[data: dbscripts/xml/upgrade/3.0.0_adaptTimedViews.xml (skipped)]
[data: dbscripts/xml/upgrade/3.0.0_adaptReferrals.xml]
[data: dbscripts/xml/upgrade/3.0.0_adaptBooksForReview.xml]
[data: dbscripts/xml/upgrade/remove_timed_views_bots.xml (skipped)]
[data: dbscripts/xml/upgrade/3.0.0_preupdate_commentsToEditor.xml]
[data: dbscripts/xml/upgrade/3.1.0_preupdate_review_assignments.xml]
[data: dbscripts/xml/upgrade/3.1.0_preupdate_notes.xml]
[data: dbscripts/xml/upgrade/3.1.0_preupdate_payments.xml]
[data: dbscripts/xml/upgrade/3.1.1_preupdate_citations.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/notes.xml]
[schema: lib/pkp/xml/schema/views.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: Duplicate entry ‘anthropocene’ for key ‘submission_search_keyword_text’
root@vm-test02:/var/www/html/ojs#

The submission_search_keyword_text appear to be a new table that the script create, however there is some problem. Also it does not appear to be a dublicate key

MariaDB [ojs]>  
MariaDB [ojs]> select * from submission_search_keyword_list where keyword_text like ‘%anthropocene’;
±-----------±--------------+
| keyword_id | keyword_text  |
±-----------±--------------+
|     243773 | �anthropocene |
|     482508 | anthropocene  |
±-----------±--------------+
2 rows in set (0.00 sec)

MariaDB [ojs]>  
MariaDB [ojs]> show index from submission_search_keyword_list;                                       
±-------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±-------------
-+
| Table                          | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment
|
±-------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±-------------
-+
| submission_search_keyword_list |          0 | PRIMARY  |            1 | keyword_id  | A         |      482508 |     NULL | NULL   |      | BTREE      |         |              
|
±-------------------------------±-----------±---------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±-------------
-+
1 row in set (0.00 sec)

MariaDB [ojs]>

I have tried to optimize all tables before upgrade, but since this is a new table I don’t know how to solve this problem.

I am using Debian 8

Best regards
Anders

Hi Anders,

You can try cleaning out the search keyword tables in your 2.4 install before the upgrade. Those would be submission_search_object_keywords, submission_search_keyword_list, and submission_search_objects. If the upgrade runs successfully you can rebuild the search index with the CLI tool:

php tools/rebuildSearchIndex.php

and see how it goes. Before you do this, please make a backup if you haven’t already.

Cheers,
Jason

Hi Jason,

Thanks for the reply but I don’t see these tables in my old install

mysql> use ojs
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
±--------------------------------------+
| Tables_in_ojs                         |
±--------------------------------------+
| access_keys                           |  
| announcement_settings                 |  
| announcement_type_settings            |  
| announcement_types                    |  
| announcements                         |  
| article_comments                      |                                                                                                                                         
| article_files                         |                                                                                                                                         
| article_galley_settings               |                                                                                                                                         
| article_galleys                       |                                                                                                                                         
| article_html_galley_images            |                                                                                                                                         
| article_notes                         |                                                                                                                                         
| article_search_keyword_list           |                                                                                                                                         
| article_search_object_keywords        |                                                                                                                                         
| article_search_objects                |                                                                                                                                         
| article_settings                      |                                                                                                                                         
| article_supp_file_settings            |                                                                                                                                         
| article_supplementary_files           |                                                                                                                                         
| article_xml_galleys                   |  
| articles                              |  
| auth_sources                          |  
| author_settings                       |  
| authors                               |  
| books_for_review                      |  
| books_for_review_authors              |  
| books_for_review_settings             |  
| captchas                              |  
| citation_settings                     |  
| citations                             |  
| comments                              |  
| completed_payments                    |  
| controlled_vocab_entries              |  
| controlled_vocab_entry_settings       |  
| controlled_vocabs                     |  
| custom_issue_orders                   |  
| custom_section_orders                 |  
| data_object_tombstone_oai_set_objects |  
| data_object_tombstone_settings        |  
| data_object_tombstones                |  
| dataverse_files                       |  
| dataverse_studies                     |  
| edit_assignments                      |  
| edit_decisions                        |  
| email_log                             |  
| email_log_users                       |  
| email_templates                       |  
| email_templates_data                  |  
| email_templates_default               |  
| email_templates_default_data          |  
| event_log                             |  
| event_log_settings                    |  
| external_feed_settings                |  
| external_feeds                        |  
| filter_groups                         |  
| filter_settings                       |  
| filters                               |  
| gifts                                 |  
| group_memberships                     |  
| group_settings                        |  
| groups                                |  
| institutional_subscription_ip         |  
| institutional_subscriptions           |  
| issue_files                           |  
| issue_galley_settings                 |  
| issue_galleys                         |  
| issue_settings                        |  
| issues                                |  
| journal_settings                      |  
| journals                              |  
| metadata_description_settings         |  
| metadata_descriptions                 |  
| metrics                               |  
| mutex                                 |  
| notes                                 |  
| notification_mail_list                |  
| notification_settings                 |  
| notification_subscription_settings    |  
| notifications                         |  
| oai_resumption_tokens                 |  
| object_for_review_assignments         |  
| object_for_review_persons             |  
| object_for_review_settings            |  
| objects_for_review                    |  
| paypal_transactions                   |  
| pln_deposit_objects                   |  
| pln_deposits                          |  
| plugin_settings                       |  
| processes                             |  
| published_articles                    |  
| queued_payments                       |  
| referral_settings                     |  
| referrals                             |  
| review_assignments                    |  
| review_form_element_settings          |  
| review_form_elements                  |  
| review_form_responses                 |  
| review_form_settings                  |  
| review_forms                          |  
| review_object_metadata                |  
| review_object_metadata_settings       |  
| review_object_type_settings           |  
| review_object_types                   |  
| review_rounds                         |  
| roles                                 |  
| rt_contexts                           |  
| rt_searches                           |  
| rt_versions                           |  
| scheduled_tasks                       |  
| section_editors                       |  
| section_settings                      |  
| sections                              |  
| sessions                              |  
| signoffs                              |  
| site                                  |  
| site_settings                         |  
| static_page_settings                  |  
| static_pages                          |  
| subscription_type_settings            |  
| subscription_types                    |  
| subscriptions                         |  
| temporary_files                       |  
| theses                                |  
| usage_stats_temporary_records         |  
| user_interests                        |  
| user_settings                         |  
| users                                 |  
| versions                              |  
±--------------------------------------+
126 rows in set (0.00 sec)

mysql>

Hi Jason,

I took a guess a truncated the following three tables

| article_search_keyword_list           |                                                                                                                                         
| article_search_object_keywords        |                                                                                                                                         
| article_search_objects                |

After that the upgrade completed, but I saw a lot of this:

PHP Notice:  unserialize(): Error at offset 68 of 210 bytes in /var/www/html/ojs/lib/pkp/classes/db/DAO.inc.php on line 352

Is that a problem?

They also came when I ran the php tools/rebuildSearchIndex.php script.

The system appears to be running normally.

/Anders

Hi Anders,

Ah, yes sorry about that - and glad that the upgrade worked out okay.

Those unserialize errors might be content encoding mismatches. When you upgraded, did you maintain any UTF-8 character settings across your configuration files?

Cheers,
Jason