[OJS 2.4.8 to 3.1.2.1] Old email logs are associated with new submissions

Hi all,
We’re having the strangest problem after our 2.4.8 → 3.1.2.1 upgrade, and I’m not even sure I quite understand the issue yet. The problem is that every time we get a new submission there are already email_log table entries from around 10 years ago with an assoc_id that matches the brand new submission_id. So when the user clicks on Editorial History of a new submission there are already entries from several years ago.

One thing I’m not sure of is if the assoc_id in the email_log table can refer to other tables besides submissions. I looked in our old OJS 2 database, and the email_log rows are there with assoc_id’s that are higher than the maximum article_id in the articles table. So I’m guessing it is associated with other tables, but I can’t figure out which ones. And i don’t want to remove the email_log rows in the new OJS 3 database with assoc_id’s that are larger than our current maximum submission_id, but that means this problem is going to keep happening as we receive new submissions.

Has anyone who was migrated from version 2.x to 3.x experienced something like this?

Thanks,
Nick

Hi @nwoodward,

When you see columns named assoc_id and assoc_type, the assoc_type defines what other table the assoc_id refers to. The assoc_type constants are defined as PHP constants in lib/pkp/classes/core/PKPApplication.inc.php and classes/core/Application.inc.php, e.g.:

define('ASSOC_TYPE_SUBMISSION',                 0x0100009);

…which refers to the submissions table in the database. (You can use a command-line php -r "echo 0x0100009 . \"\n\";" to quickly convert that hex notation to decimal, or you should be able to use the hex notation directly in SQL queries.)

Is it possible that some log entries exist in your old OJS 2.x database for submissions that were deleted?

Regards,
Alec Smecher
Public Knowledge Project Team

Hi Alec,

Thank you for your response. The information about assoc_type is helpful. We inherited this OJS instance last year, and based on the logs going to back to 2008 I think it’s been through a few major releases. I’m pretty sure the answer to your question is yes. It’s also possible that something went wrong with our migration. Because the values I see in the database don’t make much sense, e.g the high number for max assoc_id (1505) compared to the number of submissions (399).

mysql> select distinct(assoc_type) from email_log;
+------------+
| assoc_type |
+------------+
|    1048585 |
+------------+
mysql> select max(assoc_id) from email_log where assoc_type = 1048585;
+---------------+
| max(assoc_id) |
+---------------+
|          1505 |
+---------------+
mysql> select max(submission_id) from submissions;
+--------------------+
| max(submission_id) |
+--------------------+
|                399 |
+--------------------+
mysql> select count(log_id) from email_log where assoc_type = 1048585 and assoc_id > 399;
+---------------+
| count(log_id) |
+---------------+
|           161 |
+---------------+
mysql> select count(log_id) from email_log where assoc_id is null;
+---------------+
| count(log_id) |
+---------------+
|           413 |
+---------------+

I’ve tried to find out if there were other journals in the old OJS 2 instance but haven’t received a response. The id of the only journal in the journals table now is 3, so I imagine so.

Would the best thing to do at this point be to remove the 161 email logs that are associated with submissions with higher IDs than what is currently in the submissions table?

Thanks,
Nick

Hi @nwoodward,

If you inherited a single journal that was previously part of a multi-journal site, then it’s conceivable that the other journals (with higher submission IDs) were removed. To check whether the problem predated the upgrade to 3.2, try (off the top of my head, not confirmed as I don’t have a 2.x database handy)…

SELECT COUNT(*) FROM email_log el WHERE el.assoc_type=257 LEFT JOIN articles a ON (el.assoc_id = a.article_id) WHERE a.article_id IS NULL;

If you get anything more than 0, it means there are entries in email_log that don’t correspond to existing articles. (In OJS 2.x, ASSOC_TYPE_ARTICLE is defined as 257; this was renamed to ASSOC_TYPE_SUBMISSION and changed to 1048585 in OJS 3.x.)

Regards,
Alec Smecher
Public Knowledge Project Team

Hi Alec,
Yep. That email_log query with an assoc_type of 257 on the old database returned 750 results out of 2893 rows.

Thanks,
Nick

Hi @nwoodward,

OK, great, then I’d suggest removing those (keeping a good back-up in case another piece of the puzzle arises).

Regards,
Alec Smecher
Public Knowledge Project Team

Hi Alec,
I made a backup of the database and removed the email logs not tied to any submissions, and that appears to have resolved the problem. We received some new submissions, and they don’t have any old emails associated with them in the editorial history. Thanks for your help!

Thanks,
Nick

1 Like