Limited data loss at settings tables when migrating from a version smaller than 3.3.0

[jonasraoni]

Describe the bug
The resolution of the issue #9487 created a critical issue for users upgrading from an older application (< 3.3.0).

The line is attempting to grab the foreign key (e.g. submission_id, genre_id, etc), by assuming that the first column will always contain the field:
https://github.com/pkp/ojs/blob/8517ea2ddd6a88ed713067b81824a2495549df45/classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php#L165

But after the update below, the expectation was broken:
https://github.com/pkp/ojs/blame/8517ea2ddd6a88ed713067b81824a2495549df45/classes/migration/upgrade/OJSv3_3_0UpgradeMigration.inc.php#L114

The result is that the update clause will use the setting_name as key instead of the id, this will make all settings with the same setting_name to share the same value.

It's better to not trust in the order of columns and use a proper field name, as users might also change the order.

EDIT: After some analysis, I'd say the surface area is limited to the profileImage of users. External plugins are out of the analysis, but I'd guess few of them make use of such object field.

As the profile image ends with the user id, it's possible to recover the images with a migration, by searching for a file that follows the name pattern profileImage-{$userID}.ext.

What application are you using?
OJS 3.3

[jonasraoni]

Looks like the surface of this issue isn't big. The object setting isn't used often, some entities which are managed by the schema/JSON (site_settings, announcement_settings, author_settings, publication_galley_settings, journal_settings, email_templates_settings, publication_settings, submission_settings) and the entities plugin_settings and review_form_element_settings are not going to be affected.

The setting entities which remain, and barely make use of object settings, are the following:

Common for all applications

• announcement_type_settings
• category_settings
• citation_settings
• controlled_vocab_entry_settings
• data_object_tombstone_settings
• event_log_settings
• filter_settings
• genre_settings
• library_file_settings
• metadata_description_settings
• navigation_menu_item_assignment_settings
• navigation_menu_item_settings
• notification_settings: I found a case, but looks like it's a bug (setting name contents, should be a string, but it's an object with the following content {"oldPassword":"The current password you entered is invalid"}), perhaps an old one from an OJS 2 installation
• notification_subscription_settings
• review_form_settings
• submission_file_settings
• user_group_settings
• user_settings: The profileImage makes use of an object setting

OJS specific

• issue_galley_settings
• issue_settings
• section_settings
• static_page_settings
• subscription_type_settings

OMP specific

• publication_format_settings
• series_settings
• spotlight_settings
• static_page_settings
• submission_chapter_settings

OPS specific

• section_settings

[jonasraoni]

The following MySQL query will generate another query that might be suitable to detect the cases where a data loss happened.

Execute it, copy all the rows of the response (they will be SQL commands), remove the trailing UNION ALL at the end and run the query again:

SELECT CONCAT('SELECT ', QUOTE(TABLE_NAME), ', setting_name, setting_value, COUNT(0) FROM ', TABLE_NAME, ' WHERE ', COLUMN_NAME, ' = ', QUOTE('object'), ' GROUP BY setting_name, setting_value HAVING COUNT(0) > 1 UNION ALL ')
FROM information_schema.COLUMNS x
WHERE COLUMN_NAME = 'setting_type'
AND TABLE_NAME NOT IN (
    'review_form_element_settings', 'plugin_settings',
    'site_settings', 'announcement_settings', 'author_settings', 'publication_galley_settings', 'journal_settings', 'press_settings', 'server_settings', 'email_templates_settings', 'publication_settings', 'submission_settings'
)
AND EXISTS (SELECT 0 FROM information_schema.COLUMNS y WHERE y.TABLE_NAME = x.TABLE_NAME AND y.TABLE_SCHEMA = x.TABLE_SCHEMA AND y.COLUMN_NAME = 'setting_name')

If you have many OJS/OPS/OMP databases on the same server, add AND TABLE_SCHEMA = 'your_database_name' to the end of the query.

[jonasraoni]

@asmecher, check if you agree with the fixes:

• pkp/pkp-lib#10249 Added migration to recover from data loss on the pr… #10256
• Bugfix stable 3 3 0 10249 fix data loss on settings ojs#4379

[asmecher]

@jonasraoni, thanks, I added a couple comments on the OJS PR.