Upgrade from OJS 3.1.2.4 to OJS 3.2.1.2: Email templates: several problems and questions

Hello,

When we upgraded from OJS 3.1.2.4 to OJS 3.2.1.2, we encountered some problems with Email templates and we have also some questions.

1) Email templates appear in other languages
Emails appear in Arabic or in French while the journal is configured only in English
I think we have the same problem as mentioned in this forum post:

@asmecher suggested to remove templates from the database directly
We have among others, emails with prefix “BFR_…”
Do I have to remove them only in “email_templates_settings” and “email_templates” tables for the specific journal or also more generally in “email_templates_default_data” and “email_templates_default” tables ?

I also looked at the difference between the emails of our OJS 3.2.1.2 version migrated several times since OJS 2 with the original OJS 3.2.1.2 version installed from scratch

I found a list of email templates not present in original OJS version 3.2.1.2:

  • BFR_BOOK_ASSIGNED
  • BFR_BOOK_DENIED
  • BFR_BOOK_MAILED
  • BFR_BOOK_REQUESTED
  • BFR_REVIEWER_REMOVED
  • BFR_REVIEW_REMINDER
  • BFR_REVIEW_REMINDER_LATE
  • COPYEDIT_ACK
  • COPYEDIT_AUTHOR_ACK
  • COPYEDIT_AUTHOR_COMPLETE
  • COPYEDIT_AUTHOR_REQUEST
  • COPYEDIT_COMPLETE
  • COPYEDIT_FINAL_ACK
  • COPYEDIT_FINAL_COMPLETE
  • COPYEDIT_FINAL_REQUEST
  • LAYOUT_ACK
  • LUCENE_ARTICLE_INDEXING_ERROR_NOTIFICATION
  • LUCENE_JOURNAL_INDEXING_ERROR_NOTIFICATION
  • LUCENE_SEARCH_SERVICE_ERROR_NOTIFICATION
  • NOTIFICATION_MAILLIST
  • NOTIFICATION_MAILLIST_WELCOME
  • OFR_OBJECT_ASSIGNED
  • OFR_OBJECT_DENIED
  • OFR_OBJECT_MAILED
  • OFR_OBJECT_REQUESTED
  • OFR_REVIEWER_REMOVED
  • OFR_REVIEW_REMINDER
  • OFR_REVIEW_REMINDER_LATE
  • PROOFREAD_ACK
  • PROOFREAD_AUTHOR_ACK
  • PROOFREAD_AUTHOR_COMPLETE
  • PROOFREAD_AUTHOR_REQUEST
  • PROOFREAD_COMPLETE
  • PROOFREAD_LAYOUT_ACK
  • PROOFREAD_LAYOUT_COMPLETE
  • PROOFREAD_LAYOUT_REQUEST
  • PROOFREAD_REQUEST
  • REVIEW_COMPLETE
  • SUBMISSION_COMMENT
  • SUBMISSION_DECISION_REVIEWERS
  • SUBMISSION_UNSUITABLE
  • SWORD_DEPOSIT_NOTIFICATION
  • THESIS_ABSTRACT_CONFIRM

Are they still in use ? Do I have to keep them or to remove them directly in DB like emails with prefix “BFR_…” ?

2) List of “predefined message to use” subjects empty

When an editor wants to assign a submission to a section editor, he can choose a message among a list of “predefined message to use”.
After upgrading, most of the subjects of the messages are empty.
There is only the prefix of the journal [JOURNAL1]

There is the same problem when a manager wants to start a discussion with an author

Is there also something to do in database or there is a problem with the upgrading script ?

3) Page numbers of email templates doesn’t work
In the email templates pages, it is displayed 2 pages but it seems we can’t go to the 2nd page when we click on it
When we filter on the left menu on custom emails, there are less emails but there are still 2 pages displayed and 2nd page doens’t work when when we click on it

4) Filter on modified emails
Is it possible to find emails which have been modified and to filter them ?

Thanks in advance for your answers.
I wish you a happy end year 2020.
Best regards
Helene

We experienced the same problem - e-mail template records had been overwritten in the database (table email_templates_settings) with different language versions during the upgrade from 3.1.2.4 to 3.2.1.2

I hope we can still get the DB backup that we did before upgrading.

Yes, fortunately, we have still the backup and can restore the data for email_templates_settings

@asmecher - the upgrade script should be checked for errors

Unfortunaly, no, we can’t restore the data automatically:

The OJS 3.1.2-4 table structure is

| email_templates |
| email_templates_data |
| email_templates_default |
| email_templates_default_data |

The changed templates are in email_templates_data which has the following columns:

show columns from email_templates_data;
±-----------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------±-------------±-----±----±--------±------+
| email_key | varchar(64) | NO | MUL | NULL | |
| locale | varchar(14) | NO | | en_US | |
| assoc_type | bigint(20) | YES | | 0 | |
| assoc_id | bigint(20) | YES | | 0 | |
| subject | varchar(120) | NO | | NULL | |
| body | text | YES | | NULL | |
±-----------±-------------±-----±----±--------±------+

In OJS 3.2.1-2, tables are now:

| email_templates |
| email_templates_default |
| email_templates_default_data |
| email_templates_settings |

So, email_templates_data (3.1.2-4) has been converted over to email_templates_settings (3.2.1-2), with the standard key-value- column format:

±--------------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±--------------±-------------±-----±----±--------±------+
| email_id | bigint(20) | NO | PRI | NULL | |
| locale | varchar(14) | NO | PRI | | |
| setting_name | varchar(255) | NO | PRI | NULL | |
| setting_value | text | YES | | NULL | |
±--------------±-------------±-----±----±--------±------+

Now, for the affected journal (assoc_id = 9) in question:

OJS 3.1.2-4: email_templates_data
200 rows in 34 different locales.
OJS 3.2.1-2: email_templates_settings
146 rows in 31 different locales

Now, just filtering out the records for en_US locale:

OJS 3.1.2-4: email_templates_data (I can send detailled data if required):
41 rows

E.g.

select * from email_templates_data where assoc_id=9 and locale=“en_US”\G
*************************** 1. row ***************************
email_key: SUBMISSION_ACK
locale: en_US
assoc_type: 256
assoc_id: 9
subject: Submission Acknowledgement
body:

Dear {$authorName},

Thank you for submitting the manuscript, "{$submissionTitle}" to the {$contextName}. We are pleased that you considered EJHC as an outlet for your work. We aim to have the review process completed within 60 days. You will be able to track your paper’s progress through the editorial process by logging into the journal management system:

Manuscript URL: {$submissionUrl}
Username: {$authorUsername}

If you have any questions, please make sure to mention your submission ID which is {$submissionId}.

Best regards,
{$editorialContactSignature}

*************************** 2. row *************************** email_key: SUBMISSION_ACK_NOT_USER locale: en_US assoc_type: 256 assoc_id: 9 subject: Submission Acknowledgement body:

Dear Co-Author,

{$submitterName} has submitted the manuscript, "{$submissionTitle}" to {$contextName}.

If you have any questions, please do not hesitate to contact us. Thank you for considering this journal as a venue for your work.

Kind regards,
{$editorialContactSignature}

*************************** 3. row *************************** email_key: EDITOR_DECISION_INITIAL_DECLINE locale: en_US . . . *************************** 41. row *************************** email_key: revisions_deadline locale: en_US assoc_type: 256 assoc_id: 9 subject: Submission #{$submissionId}: Deadline approaching body:

Dear {$authorName},

On [date] you were informed about the editorial decision regarding your submission #{$submissionId} to the European Journal of Health Communication (EJHC) and were invited to submit a revised version of the manuscript.

This is just a gentle reminder that the deadline for the submission of the revised manuscript is on [date]. After this date, the submission will be closed if we have not received the revised version by then.

To upload the revised manuscript, log into https://www.ejhc.org/login. Under ‘Submissions’ your manuscript will be listed with a note that revisions have been requested. After clicking on the manuscript, you can upload the revised version in the section ‘Revisions’. There you can also upload the revision letter.

Thank you for submitting your manuscript to the European Journal of Health Communication and we look forward to receiving your revision.

Best regards,
{$editorialContactSignature}

OJS 3.2.1-2: email_templates_settings
2 rows, see here:

select * from email_templates_settings where locale=“en_US” AND email_id in (select email_id from email_templates where context_id=9)\G
*************************** 1. row ***************************
email_id: 18
locale: en_US
setting_name: body
setting_value:

Dear {$authorName},

It is our pleasure to accept your manuscript entitled "{$submissionTitle}" for publication in the European Journal of Health Communication. The latest comments by the reviewers who read your manuscript are included at the foot of this letter.

In order to proceed with the production process, could you please upload the final de-anonymized and proofread files of your manuscript in the journal system. To do so, log in to the system and access the latest review round in your manuscript. There, you can upload the final files in the section 'Revisions’.

Next, the de-anonymized manuscript will then undergo a final language check. If the language adheres to our standards, the manuscript will advance to the typesetting stage. We will inform you about the procedure in more detail as soon as the language check has been concluded.

Kind regards,
{$editorialContactSignature}

*************************** 2. row *************************** email_id: 18 locale: en_US setting_name: subject setting_value: Editor Decision on submission {$submissionId}

So, near complete data loss. 82 rows should be expected (subject, body).

So, what is the recommendation to us to do now? We cant go back, because the current OJS 3.2.1-2 database is now in production since nearly one month.

Hi @mpbraendle,

The code that performed the database migrations is here:

I’m not sure why you’d be missing your data. The queries are pretty straightforward. But since you have a backup of your old database, you should be able to use that to get the data back into your production db. (Of course, try first on a backup of your production db!)

Thank you @NateWr, I will give it a try.

Hi @NateWr

this is exactly the point where the mistakes happens

  1. SELECT email_id, locale, subject, ‘subject’
  2. FROM email_templates_data

There is no column with name email_id in email_templates_data and there the errror seems to happen!

As written above, the columns are

show columns from email_templates_data;
±-----------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------±-------------±-----±----±--------±------+
| email_key | varchar(64) | NO | MUL | NULL | |
| locale | varchar(14) | NO | | en_US | |
| assoc_type | bigint(20) | YES | | 0 | |
| assoc_id | bigint(20) | YES | | 0 | |
| subject | varchar(120) | NO | | NULL | |
| body | text | YES | | NULL | |
±-----------±-------------±-----±----±--------±------+

@NateWr, @asmecher - I have reported this as a Github Issue, Bug in DB upgrade script (OJS 3.1.2-4 --> OJS 3.2.1-2) --> data loss! · Issue #6549 · pkp/pkp-lib · GitHub

I’ll close this thread so the conversation happens on the Github issue (rather than both places).