Symptoms
Watermarks will be missing in newly generated emails and you can see unique key violation error in system logs
Example error:
FAILED TRYING TO EXECUTE ON CONNECTION 10: INSERT INTO sys_watermark (`number`,`sys_id`,`sys_updated_by`,`sys_created_on`,`sys_mod_count`,`source_id`,`sys_updated_on`,`source_table`,`email`,`sys_created_by`) VALUES('MSG495258698','2435f2b8db272b00c70cfff31d9619d6','system','2019-02-08 15:23:01',0,'8dc3fa70db272b00c70cfff31d961916','2019-02-08 15:23:01','sys_progress_worker','ec35f2b8db272b00c70cfff31d9619d6','system') /* <Instancename>019, gs:glide.scheduler.worker.4, tx:20353e78db272b00c70cfff31d9619c4 */
Unique Key violation detected by database (Duplicate entry 'MSG495258698' for key 'sys_watermark_index1')
: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'MSG495258698' for key 'sys_watermark_index1': org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:132)
org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106)
org.mariadb.jdbc.MySQLStatement.executeQueryEpilog(MySQLStatement.java:274)
org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:302)
org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:393)
Release
Applicable for all
Cause
During clone if the number maintenance table is not excluded all the watermarks in Production instance will be carried out to sub-prod instance.
OR
If the sys_number_counter table is added in Preserve data then number count will be out of sync between two instances (Source and target)
So in the sub-prod instance when an email is generated new watermark will be inserted into the sys_watermark table, and at that point, if the newly generated watermark does exist in the sub-prod instance which is carried from prod we will see unique key violation error in system logs and email will be generated without watermarks.
Resolution
- Login to Production instance from where you did clone.
- Go to the sys_number_counter table and check for watermark under the table column.
- Check the current number for watermark table and add +10 to that number, (for example, if the number is 564534 add 10 to this number which will be 564544)
- Now login to sub-prod instance and go to Number maintenance table and check for MSG under Prefix column.
- Open that MSG record and update the Number field with 564544.
- So from now whatever the email generated it will be carried out with watermark from 564544+ which is randomized and incremental