Continuent Developer Community
Welcome, Guest
Please Login or Register.    Lost Password?
Continuent Developer Community Continuent Community Tungsten Replicator
Foreign Key Constraint Error when replicating (1 viewing) (1) Guest
Go to bottom Post Reply Favoured: 0
TOPIC: Foreign Key Constraint Error when replicating
#682
Cody Payne (User)
Fresh Boarder
Posts: 18
graphgraph
User Offline Click here to see the profile of this user
Foreign Key Constraint Error when replicating 4 Months ago Karma: 0  
Our replication breaks seemingly every time we run the below SQL from master to the slave via the Replicator

Running: tungsten-community-1.3-rc-2

INFO | jvm 1 | 2010/05/06 13:14:20 | 2010-05-06 13:14:20,401 ERROR replicator.applier.JdbcApplier PreparedStatement failed: INSERT INTO benchmark.cnx_touchpoint ( tpid , session_id , tp_guid , orgid , contact_id , group_uid , pipe_opp_uid , call_uid , sched_call_uid , cls_uid , type , call_reason_id , resolutionid , aid , chat_session_uid , start_time , end_time , agent_callback , dw_loaded , call_reason ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )
INFO | jvm 1 | 2010/05/06 13:14:20 | Arguments:
INFO | jvm 1 | 2010/05/06 13:14:20 | - ROW# = 0
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(1: tpid) = 16354368
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(2: session_id) =
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(3: tp_guid) = 67092962-5933-11df-a5b3-001b78be2e0a
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(4: orgid) = 33
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(5: contact_id) = 4444502
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(6: group_uid) =
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(7: pipe_opp_uid) =
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(8: call_uid) =
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(9: sched_call_uid) =
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(10: cls_uid) = 19426
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(11: type) = L
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(12: call_reason_id) =
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(13: resolutionid) =
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(14: aid) = 15543
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(15: chat_session_uid) =
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(16: start_time) = 2010-05-06 13:18:35.0
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(17: end_time) =
INFO | jvm 1 | 2010/05/06 13:14:20 | - COL(18: agen
INFO | jvm 1 | 2010/05/06 13:14:20 | 2010-05-06 13:14:20,401 ERROR replicator.pipeline.SingleThreadStageTask [thl-to-dbms] Event application failed: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails ("benchmark"."cnx_touchpoint", CONSTRAINT "FK_TOUCHPOINT_REF_ACCT" FOREIGN KEY ("aid" REFERENCES "account" ("AID")
INFO | jvm 1 | 2010/05/06 13:14:20 | com.continuent.tungsten.replicator.applier.ApplierException: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails ("benchmark"."cnx_touchpoint", CONSTRAINT "FK_TOUCHPOINT_REF_ACCT" FOREIGN KEY ("aid" REFERENCES "account" ("AID")
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.continuent.tungsten.replicator.applier.JdbcApplier.applyOneRowChangePrepared (JdbcApplier.java:938)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.continuent.tungsten.replicator.applier.JdbcApplier.applyRowChangeData(JdbcAp plier.java:1031)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.continuent.tungsten.replicator.applier.JdbcApplier.apply(JdbcApplier.java: 1103)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.continuent.tungsten.replicator.applier.ApplierWrapper.apply(ApplierWrapper.j ava:90)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask.runTask(Single ThreadStageTask.java:331)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask.run(SingleThre adStageTask.java:122)
INFO | jvm 1 | 2010/05/06 13:14:20 | at java.lang.Thread.run(Thread.java:595)
INFO | jvm 1 | 2010/05/06 13:14:20 | Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails ("benchmark"."cnx_touchpoint", CONSTRAINT "FK_TOUCHPOINT_REF_ACCT" FOREIGN KEY ("aid" REFERENCES "account" ("AID")
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1011)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)
INFO | jvm 1 | 2010/05/06 13:14:20 | at com.continuent.tungsten.replicator.applier.JdbcApplier.applyOneRowChangePrepared (JdbcApplier.java:913)


Doing a ls -l w/ the Manager we get:
+-----------------------------------------------------------------------+
|bcondb04.corporate.connextions.net: EPLICATOR(role=slave, |
|state=OFFLINE:ERROR) |
+-----------------------------------------------------------------------+
| appliedLastEventId: NONE |
| appliedLastSeqno: -1 |
| appliedLatency: -1.0 |
| clusterName: default |
| currentEventId: NONE |
| host: bcondb04.corporate.connextions.n |
|et |
| latestEpochNumber: -1 |
| masterConnectUri: thl://bcondb03/ |
| masterListenUri: thl://bcondb04/ |
| maximumStoredSeqNo: -1 |
| minimumStoredSeqNo: -1 |
| pendingError: Event application failed: |
| com.mysql.jdbc.exceptions.MySQLI |
| ntegrityConstraintViolationExcep |
| Cannot add or update a child row: |
| a foreign key constraint fails |
| ("benchmark"."cnx_touchpoint", |
| CONSTRAINT |
| "FK_TOUCHPOINT_REF_ACCT" FOREIGN |
| KEY ("aid" REFERENCES "account" |
| ("AID") |
| pendingErrorCode: NONE |
| pendingErrorEventId: 000009:0000000000077650;0 |
| pendingErrorSeqno: 185 |
| pendingExceptionMessage: com.mysql.jdbc.exceptions.MySQLI |
| ntegrityConstraintViolationExcep |
| Cannot add or update a child row: |
| a foreign key constraint fails |
| ("benchmark"."cnx_touchpoint", |
| CONSTRAINT |
| "FK_TOUCHPOINT_REF_ACCT" FOREIGN |
| KEY ("aid" REFERENCES "account" |
| ("AID") |
| resourceJdbcDriver: com.mysql.jdbc.Driver |
| resourceJdbcUrl: jdbc:mysql://bcondb04:3306/${DBN |
| AME}?jdbcCompliantTruncation=fal |
| se&zeroDateTimeBehavior=convertT |
| oNull&tinyInt1isBit=false&allowM |
| ultiQueries=true&yearIsDateType= |
| false |
| resourceVendor: mysql |
| role: slave |
| seqnoType: java.lang.Long |
| sourceId: bcondb04 |
| state: OFFLINE:ERROR |
| timeInStateSeconds: 1513.881 |
| uptimeSeconds: 1514.481 |
+-----------------------------------------------------------------------+
+-----------------------------------------------------------------------+
|bcondb04ATASERVER(state=ONLINE) |
+-----------------------------------------------------------------------+
| state: ONLINE |
+-----------------------------------------------------------------------+


Using the thl skip -seqno 185 worked one time after we restarted the replication service on bcondb04 but now seems I have to do it multiple times as there are more then just one of these replication updates waiting to be placed on the slave


Any ideas?
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
#683
Cody Payne (User)
Fresh Boarder
Posts: 18
graphgraph
User Offline Click here to see the profile of this user
Re:Foreign Key Constraint Error when replicating 4 Months ago Karma: 0  
Think figured this out...issue w/ our two DB's Accounts not being in sync.

Sorry for the false alarm.
 
Report to moderator   Logged Logged  
  The administrator has disabled public write access.
#686
Robert Hodges (Moderator)
Moderator
Posts: 218
graph
User Offline Click here to see the profile of this user
Location: Berkeley California
Re:Foreign Key Constraint Error when replicating 4 Months ago Karma: 1  
Hi Cody!

Glad you got it figured out. Have you tried using consistency checks to test table consistency? It's built into the replicator. The command is 'trepctl check <db>.<table>'. You can use this in future to test synchronization.

Cheers, Robert
 
Report to moderator   Logged Logged  
 
Robert Hodges
Continuent CTO
  The administrator has disabled public write access.
Go to top Post Reply
Powered by FireBoardget the latest posts directly to your desktop