Notifications

30 views

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

 

Article Information

Last Updated:2019-05-21 11:52:04
Published:2019-02-11