Dublicate OJS Prepared Emails

Software Support
1

Hello,

I have upgrades our OJS 11 times until today. OJS has creats dublicate e-mail templates after every upgrades and listing under “Prepared Emails”. Now our OJS has 11 copy of every email templates (total 796). Is it a problem? I’m not sure.

I have checked this situation in MySQL databse. email_templates_default table shows 11 copy of every e-mail template too. Every same template has different e-mail_id.

How can I fix tihs problem?

Can I delete dublicate entries from the email_templates_default table?

Thanks!
Sonmez CELIK

Example Prepared Emails Listing Our OJS
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset
OPEN ACCESS NOTIFY Mail Reader Issue Now Open Access Edit Reset

2

What is your upgrade history? It seems like this was an issue prior to 2.3.3:

https://pkp.sfu.ca/bugzilla/show_bug.cgi?id=5837

I just ran into this problem this week in some old journals and manually cleaned up the database via SQL. The following would give you DELETE statements to review and potentially execute.

select
     concat("delete from ", DATABASE(), ".email_templates_default where email_id = ", email_id)
from
     email_templates_default
where
     email_key in (
          select x.email_key
          from email_templates_default x
          where x.email_id != email_templates_default.email_id and x.email_key = email_templates_default.email_key
     ) and
     email_id != (
          select min(x.email_id)
          from email_templates_default x
          where x.email_key = email_templates_default.email_key
     )
order by email_key
3

Hi ctgraham,

Thank you for your response.
I’m using OJS form 2.3.4.0 release. How to use code that you sent? I have deleted manually dublicate e-mail templates on my test server in MySQL email_templates_default table. It is working. But is it cause a problem in the future? I’m not sure.

Could you share your experience?

Best regards,
Sonmez CELIK

4

Deleting the emails manually will work fine. The SQL I suggested would only be for an advanced user who wanted to automate the cleanup.

5

Thank you for the information. I have deleted dublicate e-mail tepmlates from “email_templates_default” table in MySQL database. It is working.

Best regards
Sonmez CELIK

6

Hi @ctgraham,

I upgraded from 2.4.5.0 to 3.1.2. I encountered the same problem with Ukranian language and scrambled letters appear in my en_US installation. I also inherited some email templates from OJS2 which that i wanted to delete.

I deleted some entries in my database from email_templates_default and email_templates_data tables, such as templates related to Object and Book review. However, now I can’t see in my installation important templates that I did not delete. They still exist in database, but I can not see them in installation, for editing purposes, etc.

How can I fix this? Thanks a lot.
Faruk

7

What specifically did you delete and what currently are you seeing in your database tables:

  • email_templates
  • email_templates_default
  • email_templates_data
  • email_templates_default_data

?

8

Hi @ctgraham. Thanks for looking into this. I searched for rows related to Objects and Books and deleted them.
The current content of my the tables is below:

email templates:

email_id email_key assoc_type assoc_id enabled
52 REVIEW_REMIND_ONECLICK 256 1 1
51 REVIEW_REMIND_AUTO_ONECLICK 256 1 1
50 REVIEW_REMIND_AUTO 256 1 1
49 REVIEW_REMIND 256 1 1
48 REVIEW_CONFIRM 256 1 1
47 REVIEW_ACK 256 1 1
46 REVIEW_CANCEL 256 1 1
45 REVIEWER_REGISTER 256 1 1
44 PUBLISH_NOTIFY 256 1 1
43 ORCID_COLLECT_AUTHOR_ID 256 1 1
42 COPYEDIT_REQUEST 256 1 1
41 LAYOUT_REQUEST 256 1 1
40 LAYOUT_COMPLETE 256 1 1
39 EDITOR_RECOMMENDATION 256 1 1
38 EMAIL_LINK 256 1 1
37 EDITOR_DECISION_SEND_TO_PRODUCTION 256 1 1
36 EDITOR_DECISION_SEND_TO_EXTERNAL 256 1 1
35 EDITOR_DECISION_REVISIONS 256 1 1
34 EDITOR_DECISION_RESUBMIT 256 1 1
33 EDITOR_DECISION_INITIAL_DECLINE 256 1 1
32 EDITOR_DECISION_DECLINE 256 1 1
31 EDITOR_DECISION_ACCEPT 256 1 1
53 REVIEW_REQUEST 256 1 1
54 REVIEW_REQUEST_ATTACHED 256 1 1
55 REVIEW_REQUEST_ONECLICK 256 1 1
56 REVIEW_REQUEST_ONECLICK_SUBSEQUENT 256 1 1
57 REVIEW_REQUEST_REMIND_AUTO 256 1 1
58 REVIEW_REQUEST_REMIND_AUTO_ONECLICK 256 1 1
59 REVIEW_REQUEST_SUBSEQUENT 256 1 1
60 NOTIFICATION 256 1 1
61 COPYEDIT_ACK 256 1 1
62 COPYEDIT_AUTHOR_ACK 256 1 1
63 SUBMISSION_ACK 256 1 1
64 SUBMISSION_DECISION_REVIEWERS 256 1 1
65 USER_REGISTER 256 1 1
66 USER_VALIDATE 256 1 1
67 REVIEW_REQUEST_ATTACHED_SUBSEQUENT 256 1 1
68 REVIEW_COMPLETE 256 1 1
69 REVISED_VERSION_NOTIFY 256 1 1
70 ORCID_REQUEST_AUTHOR_AUTHORIZATION 256 1 1

*** email_templates_default** (I deleted text from body column only here, because the post is limited to 32000 characters)

email_key locale assoc_type assoc_id subject body
REVIEW_REQUEST_SUBSEQUENT en_US 256 1 Article Review Request: BJBMS-{$submissionId}; Second round
REVIEW_REQUEST_ATTACHED_SUBSEQUENT en_US 256 1 Article Review Request: BJBMS-{$submissionId}; Second round
REVIEW_REQUEST_REMIND_AUTO_ONECLICK en_US 256 1 Article Review Request: BJBMS-{$submissionId}; Reminder
REVISED_VERSION_NOTIFY en_US 256 1 Revised Version Uploaded BJBMS-{$submissionId}
SUBMISSION_ACK en_US 256 1 Submission Acknowledgement: BJBMS-{$submissionId}
REVIEW_REQUEST_ONECLICK_SUBSEQUENT en_US 256 1 Article Review Request: BJBMS-{$submissionId}; Second round
REVIEW_REQUEST_REMIND_AUTO en_US 256 1 Article Review Request: BJBMS-{$submissionId}; Reminder
COPYEDIT_ACK en_US 256 1 Copyediting Acknowledgement
COPYEDIT_AUTHOR_ACK en_US 256 1 Copyediting Review Acknowledgement
REVIEW_REQUEST_ONECLICK en_US 256 1 Article Review Request: BJBMS-{$submissionId}
REVIEW_REQUEST_ATTACHED en_US 256 1 Article Review Request
REVIEW_REQUEST en_US 256 1 Article Review Request: BJBMS-{$submissionId}
REVIEW_REMIND_AUTO_ONECLICK en_US 256 1 Automated Submission Review Reminder: BJBMS-{$submissionId}
REVIEW_REMIND_ONECLICK en_US 256 1 Submission Review Reminder: BJBMS-{$submissionId}
REVIEW_CANCEL en_US 256 1 Request for Review Cancelled
REVIEW_ACK en_US 256 1 Article Review Acknowledgement
REVIEW_CONFIRM en_US 256 1 Able to Review
REVIEW_REMIND en_US 256 1 Submission Review Reminder
REVIEW_REMIND_AUTO en_US 256 1 Automated Submission Review Reminder
ORCID_COLLECT_AUTHOR_ID fr_CA 256 1 Soumission ORCID
ORCID_COLLECT_AUTHOR_ID ru_RU 256 1 ORCID материала
ORCID_COLLECT_AUTHOR_ID sv_SE 256 1 ORCID för bidrag
PUBLISH_NOTIFY en_US 256 1 New Issue Published
REVIEWER_REGISTER en_US 256 1 Registration as Reviewer with {$contextName}
ORCID_COLLECT_AUTHOR_ID fi_FI 256 1 Käsikirjoituksen ORCID-tunnisteet
ORCID_COLLECT_AUTHOR_ID de_DE 256 1 ORCID-Meldung
ORCID_COLLECT_AUTHOR_ID en_US 256 1 Submission ORCID
COPYEDIT_REQUEST en_US 256 1 Copyediting Request
ORCID_COLLECT_AUTHOR_ID cs_CZ 256 1 Podání ORCID
ORCID_COLLECT_AUTHOR_ID da_DK 256 1 Indsendelse ORCID
EDITOR_DECISION_SEND_TO_PRODUCTION en_US 256 1 Editor Decision BJBMS-{$submissionId}
EMAIL_LINK en_US 256 1 Article of Possible Interest
EDITOR_RECOMMENDATION en_US 256 1 Editor Recommendation BJBMS-{$submissionId}
LAYOUT_COMPLETE en_US 256 1 Galleys Complete
LAYOUT_REQUEST en_US 256 1 Request Galleys
EDITOR_DECISION_REVISIONS en_US 256 1 Editor Decision BJBMS-{$submissionId}
EDITOR_DECISION_SEND_TO_EXTERNAL en_US 256 1 Editor Decision BJBMS-{$submissionId}
EDITOR_DECISION_RESUBMIT en_US 256 1 Editor Decision BJBMS-{$submissionId}
EDITOR_DECISION_INITIAL_DECLINE en_US 256 1 Editor Decision BJBMS-{$submissionId}
EDITOR_DECISION_DECLINE en_US 256 1 Editor Decision BJBMS-{$submissionId}
EDITOR_DECISION_ACCEPT en_US 256 1 Editor Decision BJBMS-{$submissionId}
REVIEW_COMPLETE en_US 256 1 Article Review Completed: BJBMS-{$submissionId}
REVISED_VERSION_NOTIFY ar_IQ 256 1 تم رفع نسخة منقحة
REVISED_VERSION_NOTIFY da_DK 256 1 Revideret udgave uploadet
NOTIFICATION en_US 256 1 New notification from {$siteTitle}
SUBMISSION_DECISION_REVIEWERS en_US 256 1 Decision on "{$submissionTitle}"; BJBMS-{$submissionId}
USER_REGISTER en_US 256 1 Journal Registration; {$contextName}
USER_VALIDATE en_US 256 1 Validate Your Account with {$contextName}
REVISED_VERSION_NOTIFY es_ES 256 1 Versión revisada cargada
REVISED_VERSION_NOTIFY fa_IR 256 1 آپلود نسخه اصلاح شده
REVISED_VERSION_NOTIFY fi_FI 256 1 Korjattu versio on ladattu
REVISED_VERSION_NOTIFY fr_CA 256 1 Version révisée téléversée
REVISED_VERSION_NOTIFY fr_FR 256 1 Version révisée téléversée
REVISED_VERSION_NOTIFY id_ID 256 1 Versi Revisi telah Diunggah
REVISED_VERSION_NOTIFY pt_PT 256 1 Versão revista enviada
REVISED_VERSION_NOTIFY ru_RU 256 1 Загружена исправленная версия
REVISED_VERSION_NOTIFY sv_SE 256 1 Reviderad version uppladdad
ORCID_COLLECT_AUTHOR_ID ar_IQ 256 1 تقديم ORCID
ORCID_COLLECT_AUTHOR_ID es_ES 256 1 Identificador ORCID
ORCID_COLLECT_AUTHOR_ID it_IT 256 1 Le chiediamo di inserire l’ORCID
ORCID_COLLECT_AUTHOR_ID nl_NL 256 1 ORCID van uw inzending
ORCID_COLLECT_AUTHOR_ID pt_PT 256 1 Submissão ao ORCID
ORCID_COLLECT_AUTHOR_ID sl_SI 256 1 ORCID prispevka
ORCID_COLLECT_AUTHOR_ID tr_TR 256 1 ORCID Gönderisi
ORCID_COLLECT_AUTHOR_ID uk_UA 256 1 ORCID подання
ORCID_REQUEST_AUTHOR_AUTHORIZATION de_DE 256 1 ORCID Zugriff erbeten
ORCID_REQUEST_AUTHOR_AUTHORIZATION en_US 256 1 Requesting ORCID record access
ORCID_REQUEST_AUTHOR_AUTHORIZATION nl_NL 256 1 Toegangsverzoek tot uw ORCID profiel

*** email_templates_data**

email_id email_key can_disable can_edit from_role_id to_role_id
1 NOTIFICATION 0 1 NULL NULL
2 NOTIFICATION_MAILLIST 0 1 NULL NULL
3 NOTIFICATION_MAILLIST_WELCOME 0 1 NULL NULL
4 PASSWORD_RESET_CONFIRM 0 1 NULL NULL
5 PASSWORD_RESET 0 1 NULL NULL
6 USER_REGISTER 0 1 NULL NULL
7 USER_VALIDATE 0 1 NULL NULL
8 REVIEWER_REGISTER 0 1 NULL NULL
9 PUBLISH_NOTIFY 0 1 NULL NULL
10 LOCKSS_EXISTING_ARCHIVE 0 1 NULL NULL
11 LOCKSS_NEW_ARCHIVE 0 1 NULL NULL
12 SUBMISSION_ACK 1 1 NULL 65536
13 SUBMISSION_UNSUITABLE 1 1 512 65536
14 SUBMISSION_COMMENT 0 1 NULL NULL
15 SUBMISSION_DECISION_REVIEWERS 0 1 512 4096
16 EDITOR_ASSIGN 1 1 256 512
17 REVIEW_CANCEL 1 1 512 4096
18 REVIEW_REQUEST 1 1 512 4096
19 REVIEW_REQUEST_SUBSEQUENT 1 1 512 4096
20 REVIEW_REQUEST_ONECLICK 1 1 512 4096
21 REVIEW_REQUEST_ONECLICK_SUBSEQUENT 1 1 512 4096
22 REVIEW_REQUEST_ATTACHED 0 1 512 4096
23 REVIEW_REQUEST_ATTACHED_SUBSEQUENT 0 1 512 4096
24 REVIEW_CONFIRM 1 1 4096 512
25 REVIEW_DECLINE 1 1 4096 512
26 REVIEW_COMPLETE 1 1 4096 512
27 REVIEW_ACK 1 1 512 4096
28 REVIEW_REMIND 0 1 512 4096
29 REVIEW_REMIND_AUTO 0 1 NULL 4096
30 REVIEW_REMIND_ONECLICK 0 1 512 4096
31 REVIEW_REMIND_AUTO_ONECLICK 0 1 NULL 4096
32 EDITOR_DECISION_ACCEPT 0 1 512 65536
33 EDITOR_DECISION_REVISIONS 0 1 512 65536
34 EDITOR_DECISION_RESUBMIT 0 1 512 65536
35 EDITOR_DECISION_DECLINE 0 1 512 65536
36 COPYEDIT_REQUEST 1 1 512 8192
37 COPYEDIT_COMPLETE 1 1 8192 65536
38 COPYEDIT_ACK 1 1 512 8192
39 COPYEDIT_AUTHOR_REQUEST 1 1 512 65536
40 COPYEDIT_AUTHOR_COMPLETE 1 1 65536 512
41 COPYEDIT_AUTHOR_ACK 1 1 512 65536
42 COPYEDIT_FINAL_REQUEST 1 1 512 8192
43 COPYEDIT_FINAL_COMPLETE 1 1 8192 512
44 COPYEDIT_FINAL_ACK 1 1 512 8192
45 LAYOUT_REQUEST 1 1 512 768
46 LAYOUT_COMPLETE 1 1 768 512
47 LAYOUT_ACK 1 1 512 768
48 PROOFREAD_AUTHOR_REQUEST 1 1 512 65536
49 PROOFREAD_AUTHOR_COMPLETE 1 1 65536 512
50 PROOFREAD_AUTHOR_ACK 1 1 512 65536
51 PROOFREAD_REQUEST 1 1 512 12288
52 PROOFREAD_COMPLETE 1 1 12288 512
53 PROOFREAD_ACK 1 1 512 12288
54 PROOFREAD_LAYOUT_REQUEST 1 1 512 768
55 PROOFREAD_LAYOUT_COMPLETE 1 1 768 512
56 PROOFREAD_LAYOUT_ACK 1 1 512 768
57 EMAIL_LINK 0 1 1048576 NULL
58 SUBSCRIPTION_NOTIFY 0 1 NULL 1048576
59 OPEN_ACCESS_NOTIFY 0 1 NULL 1048576
60 SUBSCRIPTION_BEFORE_EXPIRY 0 1 NULL 1048576
61 SUBSCRIPTION_AFTER_EXPIRY 0 1 NULL 1048576
62 SUBSCRIPTION_AFTER_EXPIRY_LAST 0 1 NULL 1048576
63 SUBSCRIPTION_PURCHASE_INDL 0 1 NULL 2097152
64 SUBSCRIPTION_PURCHASE_INSTL 0 1 NULL 2097152
65 SUBSCRIPTION_RENEW_INDL 0 1 NULL 2097152
66 SUBSCRIPTION_RENEW_INSTL 0 1 NULL 2097152
67 CITATION_EDITOR_AUTHOR_QUERY 0 1 NULL NULL
100 EDITOR_RECOMMENDATION 0 1 17 16
99 EDITOR_DECISION_INITIAL_DECLINE 0 1 512 65536
101 REVISED_VERSION_NOTIFY 0 1 NULL 512
72 THESIS_ABSTRACT_CONFIRM 0 1 NULL NULL
73 SWORD_DEPOSIT_NOTIFICATION 0 1 NULL NULL
88 LUCENE_ARTICLE_INDEXING_ERROR_NOTIFICATION 1 1 NULL NULL
89 LUCENE_JOURNAL_INDEXING_ERROR_NOTIFICATION 1 1 NULL NULL
90 LUCENE_SEARCH_SERVICE_ERROR_NOTIFICATION 1 1 NULL NULL
91 MANUAL_PAYMENT_NOTIFICATION 0 1 NULL NULL
92 PAYPAL_INVESTIGATE_PAYMENT 0 1 NULL NULL
93 NOTIFICATION_CENTER_DEFAULT 0 1 NULL NULL
94 REVIEW_REQUEST_REMIND_AUTO 0 1 NULL 4096
95 REVIEW_REQUEST_REMIND_AUTO_ONECLICK 0 1 NULL 4096
96 EDITOR_DECISION_SEND_TO_EXTERNAL 0 1 17 65536
97 EDITOR_DECISION_SEND_TO_PRODUCTION 0 1 17 65536
98 SUBMISSION_ACK_NOT_USER 1 1 NULL 65536
102 ORCID_COLLECT_AUTHOR_ID 0 1 NULL NULL
103 ORCID_REQUEST_AUTHOR_AUTHORIZATION 0 1 NULL NULL

*** email_templates_default_data** (I deleted text from body and decription columns, as the post here is limited to 32000 characters)

email_key locale subject body description
LUCENE_SEARCH_SERVICE_ERROR_NOTIFICATION pt_PT Journal Search Service Error
LUCENE_ARTICLE_INDEXING_ERROR_NOTIFICATION ru_RU Ошибка индексации статьи
LUCENE_JOURNAL_INDEXING_ERROR_NOTIFICATION ru_RU Ошибка индексации журнала
LUCENE_SEARCH_SERVICE_ERROR_NOTIFICATION ru_RU Ошибка службы поиска журнала
SWORD_DEPOSIT_NOTIFICATION ca_ES Notificació de dipòsit
SWORD_DEPOSIT_NOTIFICATION da_DK Deponeringsmeddelelse
SWORD_DEPOSIT_NOTIFICATION de_DE Archivierungsbenachrichtigung
SWORD_DEPOSIT_NOTIFICATION el_GR Ενημερώση για αρχειοθέτηση άρθρου
SWORD_DEPOSIT_NOTIFICATION es_ES Notificación de depósito
SWORD_DEPOSIT_NOTIFICATION eu_ES Gordailuari buruzko jakinarazpena
SWORD_DEPOSIT_NOTIFICATION fr_CA Avis de dépôt
SWORD_DEPOSIT_NOTIFICATION it_IT Notifica di deposito
SWORD_DEPOSIT_NOTIFICATION nl_NL Bericht van opname
SWORD_DEPOSIT_NOTIFICATION pt_BR Notificação de depósito em repositório SWORD
SWORD_DEPOSIT_NOTIFICATION uk_UA Повідомлення про депонування
SWORD_DEPOSIT_NOTIFICATION zh_CN 存入通知
SWORD_DEPOSIT_NOTIFICATION fa_IR مطلع ساختن از دپوزیت
SWORD_DEPOSIT_NOTIFICATION id_ID Notifikasi Deposit
SWORD_DEPOSIT_NOTIFICATION ja_JP デポジット通知
SWORD_DEPOSIT_NOTIFICATION pt_PT Notificação de Depósito
SWORD_DEPOSIT_NOTIFICATION ro_RO Înștiințare pentru depozitare
SWORD_DEPOSIT_NOTIFICATION ru_RU Уведомление о передаче на хранение
THESIS_ABSTRACT_CONFIRM ca_ES Enviament de resum de tesis
THESIS_ABSTRACT_CONFIRM da_DK Indsendelse af afhandligsresumé
THESIS_ABSTRACT_CONFIRM de_DE Dissertations-Abstract-Einreichung
THESIS_ABSTRACT_CONFIRM el_GR Υποβολή Περίληψης Διατριβής
THESIS_ABSTRACT_CONFIRM es_ES Envío de resumen de tesis
THESIS_ABSTRACT_CONFIRM eu_ES Tesi-laburpenaren bidalketa
THESIS_ABSTRACT_CONFIRM fr_CA Soumission de résumé de thèse
THESIS_ABSTRACT_CONFIRM it_IT Richiesta conferma dei dati di una tesi
THESIS_ABSTRACT_CONFIRM nl_NL Thesis samenvatting inzending
THESIS_ABSTRACT_CONFIRM pt_BR Submissão de resumo de tese para a revista {$contextName}
THESIS_ABSTRACT_CONFIRM uk_UA Подання автореферату дисертації
THESIS_ABSTRACT_CONFIRM zh_CN 论文摘要提交
THESIS_ABSTRACT_CONFIRM cs_CZ Odeslání abstraktu závěrečné práce
THESIS_ABSTRACT_CONFIRM fa_IR ساب میشن چکیده پایان نامه
THESIS_ABSTRACT_CONFIRM id_ID Penyerahan Abstrak Tesis
THESIS_ABSTRACT_CONFIRM ja_JP 学位論文要旨の投稿
THESIS_ABSTRACT_CONFIRM pt_PT Submissão de Resumo de Tese
THESIS_ABSTRACT_CONFIRM ro_RO Depunerea abstractului pentru o teză
THESIS_ABSTRACT_CONFIRM ru_RU Отправка автореферата диссертации
PAYPAL_INVESTIGATE_PAYMENT ca_ES Activitat inusual a PayPal
PAYPAL_INVESTIGATE_PAYMENT el_GR Ασυνήθιστη δραστηριότητα PayPal
PAYPAL_INVESTIGATE_PAYMENT ar_IQ نشاط غير عادي من PayPal
MANUAL_PAYMENT_NOTIFICATION fi_FI Ilmoitus manuaalisesta maksusta
PAYPAL_INVESTIGATE_PAYMENT ja_JP 異常なPayPal
PAYPAL_INVESTIGATE_PAYMENT ro_RO Activitate PayPal anormală
PAYPAL_INVESTIGATE_PAYMENT en_US Незвичайна активність PayPal
MANUAL_PAYMENT_NOTIFICATION ca_ES Notificació de Pagament Manual
MANUAL_PAYMENT_NOTIFICATION el_GR Ειδοποίηση μη αυτόματης πληρωμής
MANUAL_PAYMENT_NOTIFICATION ar_IQ إشعار الدفع اليدوي
MANUAL_PAYMENT_NOTIFICATION ja_JP 手作業による料金支払い通知
MANUAL_PAYMENT_NOTIFICATION ro_RO Înștiințare la plata în sistem tradițional
REVISED_VERSION_NOTIFY es_ES Versión revisada cargada
EDITOR_DECISION_INITIAL_DECLINE en_US Editor Decision
REVISED_VERSION_NOTIFY en_US Revised Version Uploaded
MANUAL_PAYMENT_NOTIFICATION en_US Manual Payment Notification
PAYPAL_INVESTIGATE_PAYMENT da_DK Usædvanlig PayPal-aktivitet
PAYPAL_INVESTIGATE_PAYMENT de_DE Ungewöhnliche PayPal-Aktivität
PAYPAL_INVESTIGATE_PAYMENT es_ES Actividad inusual de PayPal
PAYPAL_INVESTIGATE_PAYMENT eu_ES Ezohiko PayPal jarduera
PAYPAL_INVESTIGATE_PAYMENT fr_CA Activité inhabituelle de PayPal
PAYPAL_INVESTIGATE_PAYMENT pt_BR Atividade incomum na conta PayPal
PAYPAL_INVESTIGATE_PAYMENT zh_CN 不正常的PayPal活动
PAYPAL_INVESTIGATE_PAYMENT cs_CZ Neobvyklá PayPal aktivita
PAYPAL_INVESTIGATE_PAYMENT fa_IR فعالیت غیر عدی PayPal
PAYPAL_INVESTIGATE_PAYMENT id_ID Kegiatan pembayaran paypal yang tidak biasa
PAYPAL_INVESTIGATE_PAYMENT pt_PT Actividade incomum na conta PayPal
PAYPAL_INVESTIGATE_PAYMENT ru_RU Необычная активность PayPal
MANUAL_PAYMENT_NOTIFICATION da_DK Meddelelse om manuel betaling
MANUAL_PAYMENT_NOTIFICATION de_DE Benachrichtigung über manuelle Zahlung
MANUAL_PAYMENT_NOTIFICATION es_ES Notificación de pago manual
MANUAL_PAYMENT_NOTIFICATION eu_ES Eskuzko ordainketaren jakinarazpena
MANUAL_PAYMENT_NOTIFICATION fr_CA Avis de paiement manuel
MANUAL_PAYMENT_NOTIFICATION pt_BR Notificação de pagamento manual
MANUAL_PAYMENT_NOTIFICATION uk_UA Інформування про платіж
MANUAL_PAYMENT_NOTIFICATION zh_CN 人工支付通知
MANUAL_PAYMENT_NOTIFICATION cs_CZ Oznámení o manuální platbě
MANUAL_PAYMENT_NOTIFICATION fa_IR اطلاع رسانی پرداخت دستی
MANUAL_PAYMENT_NOTIFICATION id_ID Pemberitahuan Pembayaran Manual
MANUAL_PAYMENT_NOTIFICATION pt_PT Notificação de pagamento manual
MANUAL_PAYMENT_NOTIFICATION ru_RU Уведомление о платеже
NOTIFICATION_CENTER_DEFAULT en_US A message regarding {$contextName}
REVIEW_REQUEST_REMIND_AUTO en_US Article Review Request
REVIEW_REQUEST_REMIND_AUTO_ONECLICK en_US Article Review Request
EDITOR_DECISION_SEND_TO_EXTERNAL en_US Editor Decision
EDITOR_DECISION_SEND_TO_PRODUCTION en_US Editor Decision
SUBMISSION_ACK_NOT_USER en_US Submission Acknowledgement
SUBMISSION_ACK_NOT_USER ca_ES Justificant de recepció de la tramesa
SUBMISSION_ACK_NOT_USER es_ES Acuse de recibo del envío
SUBMISSION_ACK_NOT_USER fr_CA Accusé de réception de la soumission
SUBMISSION_ACK_NOT_USER pt_BR Agradecimento pela submissão
REVISED_VERSION_NOTIFY fa_IR آپلود نسخه اصلاح شده
REVISED_VERSION_NOTIFY fi_FI Korjattu versio on ladattu
REVISED_VERSION_NOTIFY fr_CA Version révisée téléversée
REVISED_VERSION_NOTIFY fr_FR Version révisée téléversée
REVISED_VERSION_NOTIFY id_ID Versi Revisi telah Diunggah
REVISED_VERSION_NOTIFY pt_PT Versão revista enviada
REVISED_VERSION_NOTIFY ru_RU Загружена исправленная версия
REVISED_VERSION_NOTIFY sv_SE Reviderad version uppladdad
ORCID_COLLECT_AUTHOR_ID fr_CA Soumission ORCID
ORCID_COLLECT_AUTHOR_ID sv_SE ORCID för bidrag
ORCID_COLLECT_AUTHOR_ID cs_CZ ORCID ID k příspěvku
ORCID_COLLECT_AUTHOR_ID da_DK Indsendelse ORCID
ORCID_COLLECT_AUTHOR_ID de_DE ORCID Zugriff erbeten
ORCID_REQUEST_AUTHOR_AUTHORIZATION de_DE ORCID Zugriff erbeten
ORCID_COLLECT_AUTHOR_ID en_US Submission ORCID
ORCID_REQUEST_AUTHOR_AUTHORIZATION en_US Requesting ORCID record access
ORCID_COLLECT_AUTHOR_ID es_ES Identificador ORCID
ORCID_COLLECT_AUTHOR_ID fi_FI Käsikirjoituksen ORCID-tunnisteet
ORCID_COLLECT_AUTHOR_ID it_IT Le chiediamo di inserire l’ORCID
ORCID_COLLECT_AUTHOR_ID nl_NL ORCID van uw inzending
ORCID_REQUEST_AUTHOR_AUTHORIZATION nl_NL Toegangsverzoek tot uw ORCID profiel
ORCID_COLLECT_AUTHOR_ID pt_PT Submissão ao ORCID
ORCID_COLLECT_AUTHOR_ID ru_RU ORCID материала
ORCID_COLLECT_AUTHOR_ID sl_SI ORCID prispevka
ORCID_COLLECT_AUTHOR_ID tr_TR ORCID Gönderisi
ORCID_COLLECT_AUTHOR_ID uk_UA ORCID подання
ORCID_COLLECT_AUTHOR_ID ar_IQ تقديم ORCID
PAYPAL_INVESTIGATE_PAYMENT it_IT Attività PayPal sospetta
PAYPAL_INVESTIGATE_PAYMENT nl_NL Ongebruikelijke PayPal activiteit
PAYPAL_INVESTIGATE_PAYMENT sl_SI Unusual PayPal Activity
MANUAL_PAYMENT_NOTIFICATION it_IT Notifica di pagamento manuale
MANUAL_PAYMENT_NOTIFICATION nl_NL Bericht van handmatige betaling
MANUAL_PAYMENT_NOTIFICATION sl_SI Manual Payment Notification
9

When you browse to Settings → Workflow → Emails, you currently see no email templates? Or you are only missing some?

Aside from the oddity of some Unicode coming across correctly, and some Unicode not appearing correctly, it seems that there may be discrepancy between the email_keys present in each table. For example, does the email_key of NOTIFICATION_MAILLIST_WELCOME or PASSWORD_RESET appear in email_templates_default?

Are you able to extract the data for these tables from a backup prior to your changes? Alternately, you might want to clear email_templates and email_templates_data, and restore the data for email_templates_default and email_templates_default_data from a clean install. (This would be a pretty drastic step; be sure you have current backups.)

10

Hi @ctgraham,
I see only 12 email templates. See the screenshot.
Screenshot%20(80)

Yes, the NOTIFICATION_MAILLIST_WELCOME or PASSWORD_RESET do exist in the email_templates_default.

How do these tables depend on each other, and what should I do to harmonize the data, so the appear again in the application?
Thanks
Faruk

11

The email_templates_default table names the known templates within the system. The email_templates_default_data table provides contents for those templates by locale.

The email_templates table names templates which have been overridden from their default within the system. The email_templates_data table provides the contexts for those templates by locale.

12

Thanks @ctgraham :joy: but that can be understood from their names :slight_smile:

I looked at their contents, but could not figure out what to harmonize?
Many email templates are within table, but now shown in the app. When I looked into the tables, compared the shown and not-shown templates, I could not figure out why some are shown, some are not.

Are email_ids important?
If I replace whole tables, I risk all the templates to be hidden in app?

Faruk

13

Everything is linked between these tables by the email_key column. The email_id column is not used as a foreign key anywhere, to my knowledge, so the value of this column can change.

If you replace the tables entirely with default copies from a clean installation, removing any overridden entries, I would expect all the emails to appear again.

The primary considerations are:

  • Does this email_key exist in the email_templates_default table?
  • Does this email_key exist in the email_templates_default_data table for the locale being used?
14
  • Does this email_key exist in the email_templates_default table?
  • Does this email_key exist in > the email_templates_default_data table for the locale being used?

I copied the contents of the tables in one of my previous posts. You found some inconsistencies if I understood you well. I couldn’t identify them as I have no sufficient knowledge about that :confused:
But I suppose email_keys exist in all the tables.
I filtered and deleted only rows for emails concerning Objects and Books.
I use only en_US locale.
I recently upgraded from 2.4.5.4 to 3.1.0, then to 3.1.2. Upgrades finished successfully.

Thanks a lot!
Faruk

15

My first recommendation would be to restore the email tables from a backup just before your deleted the books and objects emails, to see if that brings back all of the emails (even the unwanted ones). We could then move forward from there.

Alternately, if you want to really clean up the templates, I would recommend installing a clean install of OJS, then deleting the contents of the email tables, and inserting the contents from the clean install.

If you really want to dig in to identifying why only twelve email templates are displaying currently, we would need to identify the SQL queries which are running to list the templates, and then see why they are restricted to 12 results.

16

I thought precise deleting of the unwanted emails would do no harm, so I didn’t make any backup of the database.

Installing clean tables and templates seems good like the best available option. But is it safe? I would go for this if you think it is safe.

Thanks
Faruk

17

It is always best practice to maintain a regular backup of your software, and any manual operations on the database should be preceded by an additional one-time backup as well.

Clearing the email_template and email_template_data tables will discard any email customizations you have made, both changes and newly added emails.

The primary risk of resetting the email_template_default and email_template_default_data is that the system you reset from is not configured identically to the system you want to reset. For example, plugins can add email templates, so the clean system would need to have any such plugins enabled (if such are in use).

The safety of this operation is entirely dependent on:

  • you having a backup of the tables you change to revert if needed
  • you scripting the change or otherwise restricting access to the server to prevent the inadvertent sending of an email while you are in the middle of the change.

In a test 3.1.2 environment here I deleted the OFR and BFR emails from an upgraded installation, and all expected emails remained listed in the UI.

mysql> delete from email_templates_default_data where email_key like 'BFR_%' or email_key like 'OFR_%';
Query OK, 259 rows affected (0.00 sec)
mysql> delete from email_templates_default where email_key like 'BFR_%' or email_key like 'OFR_%';
Query OK, 14 rows affected (0.03 sec)

Since your installation didn’t behave this way, there may be something beyond just the data removal to consider. You might want to look in your webserver error log for any relevant messages.

It is also a good idea, if possible, to first test any changes in a copy of the site rather than on the production site itself.

18

Hi @ctgraham,

Thanks a lot for your help. :+1:
I have managed to restore the template emails in UI by copying the fresh tables from fresh OJS3 installation.

Best regards,
Faruk