Email log empty

[aleskl]

Dear OJS developers!

Something happened to our email logs - the Email log under the History tab for the article contains only very recent emails. We did and upgrade to 2.4.0 at September 13th, and all the emails that are now recorded in the Email log are newer - that is why I suspect it has something to do with the upgrade. Older emails dissapeared from logs. Is it possible to restore them?

Thanks,
Ales

[JasonNugent]

Hi Ales,

What version did you upgrade from?

Regards,
Jason

[aleskl]

Upgraded to 2.4.0 from 2.3.6.

I can see older emails in the database, they just don't show on the webpage.

[JasonNugent]

Hi Ales,

The 2.4.0 upgrade renamed the article_email_log table to 'email_log' and changed the column called 'article_id' to 'assoc_id'. I assume that this is correct on your installation? I am looking at a local install here that I upgraded (from 2.2.3 no less) and my old emails show in the history/email log.

Regards,
Jason

[aleskl]

Thank you Jason, I have checked the database and it is like you wrote: 'email_log' table with 'assoc_id' column. Perhaps the function retrieving data was not updated properly? But in that case no emails would be shown. Only the new ones are shown here.

Then I looked at the values in the 'assoc-id' column. Quite a lot of records have NULL in that column. When I manually entered the number of the paper, where I first observed this problem, the email appeared in the email log! So the database is structured as it should be and the functions work ok. The problem is the NULL values in 'assoc_id'.

So the problem is not date, but apparently all emails did not get a value for 'assoc_id'. Also 'event_type' column is often NULL and there seems to be a correlation, both columns are often NULL together. I should probably check the database from the backup, but I don't know how to browse a database from an archive?

Ok, there is some connection also with the date. The last NULL assoc_id is recorded 2012-09-11 and after that a few dozens of emails were sent that all got their 'assoc_id' number. Perhaps the NULL values were introduced (randomly?) during database upgrade?

Regards,
Ales

[JasonNugent]

Hi Ales,

How is your old database archived? If it was a mysql dump, you can just create a new database and import it with a mysql command via the command line. If it's a zip or an archive of the actual MySQL database table files themselves, that's a bit more work but that would be an unusual way to create a backup. I'd be interested in knowing whether or not your old article_id column contained null values. The upgrade command should have only renamed the column with no other constraints applied.

Regards,
Jason

[aleskl]

I did a stupid thing, mysql dump in our backup contains drop table statements and specifies exactly the database, so I overwritten the database with an old version. When I opened the webpage and saw a database error on the front page; I really started sweating. Luckily I had a quite recent backup, so I restored that and it's ok now, perhaps just some new events with the papers will be reverted, I have to check that.

But I have looked at article_email_log and some emails had NULL values in id column, so this is something that was happening before also. I'll monitor this further and see if it's happening with new emails also.

[piotreba]

Hello, I experienced the same after upgrading from 2.3.8 to 2.4.2. Now I have no older email logs available (they are in DB)! What is more, some papers present email logs of different submissions... That causes a lot of troubles... Has someone found any solution?

[piotreba]

As I understood, data from the table article_email_log, column article_ID should be in the assoc_ID column, email_log table in the upgraded database (overriding existing values in the latter table)?

[asmecher]

Hi piotreba,

Between OJS 2.3.x and 2.4.x, the article_email_log table is renamed to email_log, and the columns are changed as follows:

• article_id becomes assoc_id
• assoc_type needs to be set to 257

This should happen during upgrade, but if you encountered errors during the upgrade process, these steps (and others) may not have been fully completed.

Regards,
Alec Smecher
Public Knowledge Project Team

[piotreba]

Maybe someone has any php script or other solution that would help updating this incorectly inserted data? There are thousands of emails that require correction...

[piotreba]

Ok, the case is not so serious...
Just simple UPDATE SQL statement was needed, maybe will be helpful for others:
UPDATE `pre_ojs_update_db`.`article_email_log`, `post_ojs_update_db`.`email_log` SET `post_ojs_update_db`.`email_log`.`assoc_id` = `pre_ojs_update_db`.`article_email_log`.`article_id` WHERE `post_ojs_update_db`.`email_log`.`log_id` = `pre_ojs_update_db`.`article_email_log`.`log_id`