Scenario

The main My Alerts process has been running successfully, BUT the SMTP Mail Server has failed and emails have not been sent for several days. Now the issue with the SMTP Mail Server has been fixed and the MyAlerts administrator wants to resend the failed email. Unfortunately My Alerts does not store the generated email so we need to roll back the My Alerts database to where it was before the SMTP Mail Server crashed so My Alerts can be re-run and the failed emails resent.

The following steps take you through the process of rolling back the My Alerts cache.

This example shows a rollback to midnight on the 6th December 2016. You will probably need to roll back to another date. Say you wanted to rollback to midnight on the 12th July 2017.

Replace anywhere in these scripts where 2016-12-06 occurs with 2017-07-15.
Replace anywhere in these scripts where 2016-12-07 occurs with 2017-07-16.

Step-by-step guide

Step 1 - Backup the My Alerts database

This can be achieved by using the Database Backup script supplied by Astun. If you have implemented an automated backup procedure then take the last backup and copy it somewhere it won't be overwritten.

Step 2 - Create a table of records to be deleted from the cache

Now we need to extract from the My Alerts history cache all of the records for which we wish to re-send email. This can be done via PG Admin III.

create table myalert.del_cache as
select userid,stamp,cacheid
from myalert.tbl_history_cache
where stamp > '2016-12-06 23:59' order by userid,stamp desc;

Step 3 - Delete the extracted records from the cache

Now that we have created a table of all the details that need to be re-sent we can delete the original details from the My Alerts cache e.g.

delete
from myalert.tbl_cache a using myalert.del_cache b
where a.userid = b.userid
and a.cacheid = b.cacheid;

Step 4 - Create a table of records to be re-inserted

The next step is to create a table of all the records that need to be re-inserted back into the My Alerts cache.

create table myalert.ins_cache as

select a.cacheid,a.userid,max(a.stamp) as stamp
from myalert.tbl_history_cache a,


(select userid,stamp,cacheid from myalert.tbl_history_cache
where stamp > '2016-12-06 23:59') as b

where a.stamp < '2016-12-07'
and b.userid = a.userid and b.cacheid = a.cacheid
group by a.userid,a.cacheid order by a.userid,a.cacheid;

Step 5 - Insert the details back into your My Alerts database

Now we are ready to insert the details back into the My Alerts database.

insert into myalert.tbl_cache
(cacheid,userid,locationid,timestamp,xml,layergroupid)

select a.cacheid,a.userid,a.locationid,a.stamp,a.xml,a.layergroupid
from myalert.tbl_history_cache a,
myalert.ins_cache b
where a.userid = b.userid
and a.cacheid = b.cacheid 
and a.stamp = b.stamp;

Step 6 - Run My Alerts in debug mode

This allows you to check that the expected emails are going to be generated. This can be done via iShare Studio - please see My Alerts Console for information on how to run in debug mode by adding the -debug parameter.

Step 7 - Run My Alerts for real

Remove the -debug parameter and let My Alerts run for real and generate the required email.

Browser not supported