search

danielcheng007 / tungsten-replicator

Automatically exported from code.google.com/p/tungsten-replicator
0 stars 0 forks source link

Oracle UPDATEs that change no columns lead to OptimizeUpdatesFilter removing too much #355

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?

1. Have the OptimizeUpdatesFilter enabled.
2. On the Oracle master generate an UPDATE which changes nothing.

What is the expected output?

Slave replicator applies this void UPDATE.

What do you see instead?

Slave replicator breaks with an error:

INFO   | jvm 1    | 2012/07/31 17:19:04 | Event application failed: seqno=5 
fragno=0 message=java.sql.SQLException: ORA-01747: invalid user.table.column, 
table.column, or column specification
INFO   | jvm 1    | 2012/07/31 17:19:04 | 
INFO   | jvm 1    | 2012/07/31 17:19:04 | Failing statement : UPDATE DEMO.TEST 
SET  WHERE NIL =  ? 
INFO   | jvm 1    | 2012/07/31 17:19:04 | Arguments:
INFO   | jvm 1    | 2012/07/31 17:19:04 |  - ROW# = 0
INFO   | jvm 1    | 2012/07/31 17:19:04 |   - KEY(1: NIL) = 317991
INFO   | jvm 1    | 2012/07/31 17:19:04 | 
com.continuent.tungsten.replicator.applier.ApplierException: 
java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or 
column specification
INFO   | jvm 1    | 2012/07/31 17:19:04 | 
INFO   | jvm 1    | 2012/07/31 17:19:04 |       at 
com.continuent.tungsten.replicator.applier.JdbcApplier.applyOneRowChangePrepared
(JdbcApplier.java:1012)
INFO   | jvm 1    | 2012/07/31 17:19:04 |       at 
com.continuent.tungsten.replicator.applier.JdbcApplier.applyRowChangeData(JdbcAp
plier.java:1129)

What is the possible cause?

Seems that the statement was trimmed too much:

UPDATE DEMO.TEST SET  WHERE NIL =  ?

Here's the underlying THL entry:

SEQ# = 5 / FRAG# = 0 (last frag)
- TIME = 2012-07-31 17:19:00.0
- EPOCH# = 0
- EVENTID = 167955950
- SOURCEID = tun
- METADATA = [service=fromoracle;shard=DEMO]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- SQL(0) =
 - ACTION = UPDATE
 - SCHEMA = DEMO
 - TABLE = TEST
 - ROW# = 0
  - COL(1: NIL) = 317991
  - COL(2: ID_MED) = c9580753bc923
  - COL(3: NICK) = test.test02
  - COL(4: MAIL) = test@test.com
  - COL(5: POSTAL) = NULL
  - KEY(1: NIL) = 317991
  - KEY(2: ID_MED) = c9580753bc923
  - KEY(3: NICK) = test.test02
  - KEY(4: MAIL) = test@test.com
  - KEY(5: POSTAL) = NULL

What is the proposed solution?

a. Leave all columns if nothing changed.
b. Leave only PK.
c. Skip the UPDATE completely.

Additional information

This is Oracle RAC to Oracle topology.

Original issue reported on code.google.com by linas.vi...@continuent.com on 1 Aug 2012 at 8:02

GoogleCodeExporter commented 9 years ago 
Committed a fix via r926.

If all columns are static (Issue 355), we leave all intact (not removed). In 
this corner case, we can't remove the event completely, because if there are 
triggers (eg. which count UPDATEs), they would not fire. Hence, instead, we 
leave the event unchanged and unoptimized.

Original comment by linas.vi...@continuent.com on 3 Aug 2012 at 9:27

GoogleCodeExporter commented 9 years ago

Original comment by linas.vi...@continuent.com on 9 Oct 2012 at 3:36