Parallel extractor is incompatible with rename filter

[GoogleCodeExporter]

What steps will reproduce the problem?

This is Oracle to MySQL:

1. On Oracle:

  CREATE TABLE "DEMO"."BIRDS" 
   (    "ID" NUMBER(10,0), 
    "NAME" VARCHAR2(80 CHAR), 
    "NOTE" VARCHAR2(80 CHAR), 
    "IGNORECOL1" VARCHAR2(40 CHAR), 
    "VIN" VARCHAR2(40 CHAR)
   );

  ALTER TABLE "DEMO"."BIRDS" ADD PRIMARY KEY ("ID")

2. On MySQL:

CREATE TABLE `bird` (
  `id` int(11) NOT NULL,
  `name_` varchar(40) DEFAULT NULL,
  `note` varchar(40) DEFAULT NULL,
  `ignorecol1` varchar(20) DEFAULT NULL,
  `vin` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

3. Prepare rename filter config on slave:

$ cat rename.csv
*,birds,*,-,bird,-
*,*,name,-,-,name_

4. Prepare chunks definition on master:

$ cat chunks.csv
DEMO,BIRDS

5. Configure ASYNC and execute setupCDC.sh on master. Remember SCN:

...
Processing DEMO.BIRDS -> 'CT_BIRDS' : OK
...
Done.
Capture started at position 11137265256

6. Install master-slave with something like this:

#!/bin/sh

./current/tools/tpm configure defaults --reset

./current/tools/tpm configure ora_my \
--enable-heterogenous-service=true \
--user=oracle \
--install-directory=/opt/ora_to_mysql/continuent \
--members=hostora,hostmy \
--master=hostora

./current/tools/tpm configure ora_my --hosts=hostora \
--user=oracle \
--replication-user=DEMO_PUB \
--replication-password=DEMO_PUB \
--datasource-oracle-service=ORCL \
--datasource-type=oracle \
--svc-table-engine=CDCASYNC \
--property=replicator.global.extract.db.user=tungsten \
--property=replicator.global.extract.db.password=secret \
--property=replicator.extractor.dbms.transaction_frag_size=10 \
--property=replicator.extractor.dbms.reconnectTimeout=60 \
--property=replicator.extractor.parallel-extractor.ChunkDefinitionFile=/home/ora
cle/downloads/chunks.csv

./current/tools/tpm configure ora_my --hosts=hostmy \
--user=tungsten \
--replication-user=tungsten \
--replication-password=secret \
--svc-applier-filters=CDC,casetransform,rename \
--property=replicator.filter.CDC.from=DEMO_PUB.HEARTBEAT \
--property=replicator.filter.CDC.to=tungsten_ora_my.heartbeat \
--property=replicator.filter.casetransform.to_upper_case=false \
--property=replicator.filter.rename.definitionsFile=/opt/ora_to_mysql/rename.csv
 \
--skip-validation-check=MySQLDumpCheck

./current/tools/tpm install ora_my $@

7. Parallel extract from the master:

./replicator start offline
./trepctl online -provision 11137265256

8. Put the slave online:

./replicator start

What is the expected output?

Table provisions successfully.

What do you see instead?

i. First, TRUNCATE TABLE, added by the parallel extractor, is not renamed:

INFO   | jvm 1    | 2014/02/26 17:40:19 | 2014-02-26 17:40:19,894 [ora_my - 
q-to-dbms-0] ERROR applier.JdbcApplier Statement failed: truncate table 
demo.birds
INFO   | jvm 1    | 2014/02/26 17:40:19 | 2014-02-26 17:40:19,894 [ora_my - 
q-to-dbms-0] ERROR pipeline.SingleThreadStageTask Event application failed: 
seqno=6 fragno=0 message=java.sql.SQLException: Statement failed on slave but 
succeeded on master
INFO   | jvm 1    | 2014/02/26 17:40:19 | 
com.continuent.tungsten.replicator.applier.ApplierException: 
java.sql.SQLException: Statement failed on slave but succeeded on master
INFO   | jvm 1    | 2014/02/26 17:40:19 |   at 
com.continuent.tungsten.replicator.applier.JdbcApplier.applyStatementData(JdbcAp
plier.java:672)
What is the possible cause?

RenameFilter works for row events only. We need to think how to make it 
compatible with parallel extractor. Alternatively, parallel extractor could be 
configured to receive renamed tables on the master. This would require adding 
rename filter to the provision pipeline.

ii. Do `TRUNCATE TABLE bird` manually. This should resolve all problems, but 
then PK violations start to appear:

Duplicate entry '1001' for key 'PRIMARY'

Even if we repeat truncates, new PK violations happen:

Duplicate entry '8997' for key 'PRIMARY' 

iii.  Finally, I truncated this table once again, just to make it pass to other 
seqno. And it did. Then I hit a non-related error, because the other table was 
not there:

INFO   | jvm 1    | 2014/02/26 18:08:56 | WARNING: Could not execute query 
org.drizzle.jdbc.internal.common.query.DrizzleQuery@7c2479a4: Table 
'demo.datetest_cd' doesn't exist
INFO   | jvm 1    | 2014/02/26 18:08:56 | 2014-02-26 18:08:56,368 [ora_my - 
q-to-dbms-0] ERROR applier.JdbcApplier Statement failed: truncate table 
demo.datetest_cd
INFO   | jvm 1    | 2014/02/26 18:08:56 | 2014-02-26 18:08:56,368 [ora_my - 
q-to-dbms-0] ERROR pipeline.SingleThreadStageTask Event application failed: 
seqno=14 fragno=0 message=java.sql.SQLException: Statement failed on slave but 
succeeded on master
INFO   | jvm 1    | 2014/02/26 18:08:56 | 
com.continuent.tungsten.replicator.applier.ApplierException: 
java.sql.SQLException: Statement failed on slave but succeeded on master
INFO   | jvm 1    | 2014/02/26 18:08:56 |     at 
com.continuent.tungsten.replicator.applier.JdbcApplier.applyStatementData(JdbcAp
plier.java:672)

Created the table. Put Replicator back online and here where it's gotten very 
strange:

INFO   | jvm 1    | 2014/02/26 18:10:58 | 2014-02-26 18:10:58,327 [ora_my - 
q-to-dbms-0] ERROR pipeline.SingleThreadStageTask Event application failed: 
seqno=11 fragno=0 message=java.sql.SQLIntegrityConstraintViolationException: 
Duplicate entry '8997' for key 'PRIMARY'
INFO   | jvm 1    | 2014/02/26 18:10:58 | 
com.continuent.tungsten.replicator.applier.ApplierException: 
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '8997' for 
key 'PRIMARY'

It jumped back from seqno 14 to 11!? And sure, it cause duplicate key again.

What is the proposed solution?

Needs careful testing to determine the cause. PK violations and jumping seqno 
positions pose a serious risk.

Original issue reported on code.google.com by linas.vi...@continuent.com on 27 Feb 2014 at 11:05

• Blocked on: #800

[GoogleCodeExporter]

Original comment by linas.vi...@continuent.com on 27 Feb 2014 at 1:49

• Now blocked on: #800

[GoogleCodeExporter]

Proposed solution:

1.) Remove explicit schema name stating from parallel extractor's truncate 
statement (see SQL(0) below):

SEQ# = 0 / FRAG# = 0 (last frag)
...
- METADATA = [schema=DEMO;table=MEDIUMUNS;nbBlocks=1;service=ora_my;shard=DEMO]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- SQL(0) = TRUNCATE TABLE DEMO.MEDIUMUNS
- SQL(1) =
 - ACTION = INSERT
 - SCHEMA = DEMO
 - TABLE = MEDIUMUNS
 - ROW# = 0
  - COL(1: ID) = 10
  - COL(2: VAL) = 1

Move that schema name into defaultSchema parameter instead:

SEQ# = 5067 / FRAG# = 0 (last frag)
...
- METADATA = [schema=DEMO;table=MEDIUMUNS;nbBlocks=1;service=ora_my;shard=DEMO]
- TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
- SCHEMA = DEMO
- SQL(0) = TRUNCATE TABLE MEDIUMUNS
- SQL(1) =
 - ACTION = INSERT
 - SCHEMA = DEMO
 - TABLE = MEDIUMUNS
 - ROW# = 0
  - COL(1: ID) = 10
  - COL(2: VAL) = 1

2.) Extend rename filter to transform defaultSchema name of StatementData 
events. Currently it processes only RBR events.

Original comment by linas.vi...@continuent.com on 18 Mar 2014 at 5:26

• Changed state: Started

[GoogleCodeExporter]

Fixed via r2136.

MC, please add a line to release notes that RenameFilter now supports a basic 
defaultSchema renaming of StatementData events (in dbrename.js fashion). 
Previously it was agnostic to anything that was not a RowChangeData.

Original comment by linas.vi...@continuent.com on 18 Mar 2014 at 6:37

• Changed state: Documenting

[GoogleCodeExporter]

An entry has been added to the 2.2.1 release notes: 

 The rename filter has been updated so that renaming of only the schema name for STATEMENT events. Previously, only ROW events would be renamed by the filter.

The corresponding Rename filter page has been updated with this information and 
version availability.

Original comment by mc.br...@continuent.com on 15 Apr 2014 at 2:15

• Changed state: Fixed