Archiving e-mail logs

Archiving e-mail logs

The e-mail log in InterformNG2 can perform poorly if a very large number of e-mails are stored. If there is a requirement to store e-mail logs for a long time, it is recommended to move email logs to a separate archive for long term storage. One option is to simply copy the email log database entries to a separate database table.

In order for this to be possible, InterformNG2 must be configured to store job and email logs in an external database, for instance MySQL. To configure log storage in an external MySQL database, the following must be added to application-default.properties.
ng2db.dbdatasource.url = jdbc:mysql://localhost:3306/data_{TENANTID}?useSSL=true&enabledTLSProtocols=TLSv1.2&createDatabaseIfNotExist=true&serverTimezone=UTC
ng2db.dbdatasource.driver = com.mysql.jdbc.Driver
ng2db.dbdatasource.hibernateDialect = org.hibernate.dialect.MySQL5InnoDBDialect
ng2db.dbdatasource.username = root
ng2db.dbdatasource.password = root
ng2.poolProvider=com.interform400.connectionPool.InterFormConnectionPool
ng2.combinePools=true
Change host name, username and password to the correct ones for the database used.
See also the manual section on “High Availability Setup” for additional configuration options related to external database.
An email log archive table can then be created in the MySQL database. The following SQL creates the archive table for the “home” tenant in the same schema as the regular email log table.
CREATE TABLE data_home.archive_email (
  `id` int NOT NULL,
  `creator` varchar(200) DEFAULT NULL,
  `from_adr` varchar(254) DEFAULT NULL,
  `message` longblob,
  `status` int DEFAULT NULL,
  `subject` longtext,
  `timestamp` datetime DEFAULT NULL,
  `to_adr` longtext,
  PRIMARY KEY (`id`),
  KEY `idx_emailarch_timestamp` (`timestamp`)
) ENGINE=InnoDB;
Alert
Note that the archive table has less columns than the original log_email table, since some data are not relevant for archiving.
Two triggers can now be configures to update the archive table whenever inserts and updates happen on the main table:
DELIMITER #
CREATE TRIGGER data_home.email_archive_ins_trigger
AFTER INSERT ON data_home.log_email
FOR EACH ROW
BEGIN
    INSERT INTO data_home.archive_email(id, creator, from_adr, message, status, subject, timestamp, to_adr) VALUES (NEW.id, NEW.creator, NEW.from_adr, NEW.message, NEW.status, NEW.subject, NEW.timestamp, NEW.to_adr);
END #
DELIMITER ;


DELIMITER #
CREATE TRIGGER data_home.email_archive_upd_trigger
AFTER UPDATE ON data_home.log_email
FOR EACH ROW
BEGIN
    UPDATE data_home.archive_email SET data_home.archive_email.status=NEW.status WHERE id=NEW.id;
END #
DELIMITER ;
Further considerations
  1. Consider if more columns can be left out of the archive table, to conserve storage space.
  2. Consider adding additional indexes to the archive table, for faster searches.

    • Related Articles

    • Release log Version 2.x.x

      This is the list of the InterformNG2 releases versions 2.x.x and the major new features in each release: Version 2.4.2 Version 2.4.1 Version 2.4.0 Version 2.3.0 Version 2.2.0 Version 2.1.2 Version 2.1.1 Version 2.1.0 Version 2.0.3 Version 2.0.2 ...

    • Release log

      This is the list of the InterformNG2 releases and the major new features in each release: The manual now clearly reflect the status of support for the printer language: Direct Protocol. Direct Protocol is a legacy printer langauge and the support is ...

    • Release log Version 1.x.x

      This is the list of the InterformNG2 releases versions 1.x.x and the major new features in each release: Version 1.9.0 Version 1.8.0 Version 1.7.1 Version 1.7.0 Version 1.6.0 Version 1.5.4 Version 1.5.3 Version 1.5.2 Version 1.5.1 Version 1.5.0 ...

    • Release log Version 3.x.x

      This is the list of the InterFormNG2 releases versions 3.x.x and the major new features in each release: Version 3.5.1 Version 3.5.0 Version 3.4.3 Version 3.4.2 Version 3.4.1 Version 3.4.0 Version 3.3.2 Version 3.3.1 Version 3.3.0 Version 3.2.0 ...

    • Tenant E-mail server

      This section concerns E-mail servers (SMTP setup) for a tenant. A similar setup is available for system emails. For a normal setup you need to configure at least one email server, before you can send emails in InterformNG2. This is covered below. In ...