Parsing of RENAME statements does not invalidate caches for all tables that are renamed

[GoogleCodeExporter]

What steps will reproduce the problem?

CREATE TABLE `mytest` (  `id` int(10) NOT NULL AUTO_INCREMENT,  `new_col` 
int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx` (`new_col`)  ) 
ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO mytest values (1,1),(2,2),(3,3);

CREATE TABLE `mytest2` (  `id` int(10) NOT NULL AUTO_INCREMENT,  `new_col` 
int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx` (`new_col`)  ) 
ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into mytest2 select * from mytest;

alter table mytest2 ADD COLUMN new_col2 INT(11) NULL;

rename table mytest to mytest_20141208_1, mytest2 to mytest;

insert into dba.mytest(id,new_col6) values (8,800);

What is the expected output?
Replication should happen with no errors

What do you see instead?
Slave fails with error:
INFO   | jvm 1    | 2014/12/09 10:43:29 | 2014-12-09 09:43:29,128 [tsandbox - 
q-to-dbms-0] ERROR pipeline.SingleThreadStageTask Event application failed: 
seqno=34 fragno=0 message=java.sql.SQLSyntaxErrorException: Unknown column '' 
in 'field list'
INFO   | jvm 1    | 2014/12/09 10:43:29 | 
com.continuent.tungsten.replicator.applier.ApplierException: 
java.sql.SQLSyntaxErrorException: Unknown column '' in 'field list'
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
com.continuent.tungsten.replicator.applier.JdbcApplier.applyOneRowChangePrepared
(JdbcApplier.java:1041)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
com.continuent.tungsten.replicator.applier.MySQLApplier.applyOneRowChangePrepare
d(MySQLApplier.java:391)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
com.continuent.tungsten.replicator.applier.JdbcApplier.applyRowChangeData(JdbcAp
plier.java:1243)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
com.continuent.tungsten.replicator.applier.JdbcApplier.apply(JdbcApplier.java:13
40)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
com.continuent.tungsten.replicator.applier.ApplierWrapper.apply(ApplierWrapper.j
ava:101)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask.apply(SingleTh
readStageTask.java:797)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask.runTask(Single
ThreadStageTask.java:530)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
com.continuent.tungsten.replicator.pipeline.SingleThreadStageTask.run(SingleThre
adStageTask.java:179)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
java.lang.Thread.run(Thread.java:695)
INFO   | jvm 1    | 2014/12/09 10:43:29 | Caused by: 
java.sql.SQLSyntaxErrorException: Unknown column '' in 'field list'
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
org.drizzle.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:99)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
org.drizzle.jdbc.DrizzlePreparedStatement.executeUpdate(DrizzlePreparedStatement
.java:128)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
com.continuent.tungsten.replicator.applier.JdbcApplier.applyOneRowChangePrepared
(JdbcApplier.java:1005)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       ... 8 more
INFO   | jvm 1    | 2014/12/09 10:43:29 | Caused by: 
org.drizzle.jdbc.internal.common.QueryException: Unknown column '' in 'field 
list'
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
org.drizzle.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:46
4)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       at 
org.drizzle.jdbc.DrizzlePreparedStatement.executeUpdate(DrizzlePreparedStatement
.java:125)
INFO   | jvm 1    | 2014/12/09 10:43:29 |       ... 9 more

What is the possible cause?
Applier uses a cache that is not updated

Original issue reported on code.google.com by stephane...@continuent.com on 9 Dec 2014 at 11:17

• Blocking: #1074

[GoogleCodeExporter]

From discussion with Stephane:

1.) Is the workaround to put the Replicator back online?

Yes, not sure the customer feels very comfortable with it though.

2.) How much time would the fix require?

I would go for 1 day (maybe more, but cannot say sure).
This requires a change of the parser for RENAME TABLE.
It would also require a change to handle the fact that a single statement could 
update more than one table at a time.
This could probably be restricted to RENAME case for now.

3.) How encapsulated vs. destabilising the fix would be? Looks pretty safe.

Looks pretty safe to me as well (as long as we don’t change anything else 
than RENAME parsing / handling).

Original comment by linas.vi...@continuent.com on 9 Dec 2014 at 1:21

• Added labels: Sponsored

[GoogleCodeExporter]

An auto-online cron job until this is fixed should be used.

Original comment by linas.vi...@continuent.com on 11 Dec 2014 at 3:27

[GoogleCodeExporter]

This issue was updated by revision r2714.

Fixed RENAME TABLE parsing : now all tables from the list will be invalidated 
from the cache 

Original comment by stephane...@continuent.com on 12 Dec 2014 at 3:51

[GoogleCodeExporter]

Partly done and committed.

This shows the problem :

CREATE TABLE `mytest` (  `id` int(10) NOT NULL AUTO_INCREMENT,  `new_col` 
int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx` (`new_col`)  ) 
ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into mytest values (1,1),(2,2),(3,3);

set session binlog_format=row;
insert into mytest (id) values(100);

create table mytest2 as select * from mytest;
alter table mytest2 ADD COLUMN new_col2 INT(11) NULL;
rename table mytest to mytest_20141212, mytest2 to mytest;
insert into mytest(id,new_col2) values (8,800);

--> this should fail before the fix and works fine after

Still a few filters should be fixed

Original comment by stephane...@continuent.com on 12 Dec 2014 at 5:31

• Changed state: Started

[GoogleCodeExporter]

Original comment by stephane...@continuent.com on 13 Dec 2014 at 12:07

• Now blocking: #1074

[GoogleCodeExporter]

Original comment by stephane...@continuent.com on 8 Jan 2015 at 7:35

• Changed state: QA

[GoogleCodeExporter]

Validated that the described test does indeed fail on earlier versions but now 
succeeds as described

Original comment by eric.har...@continuent.com on 13 Jan 2015 at 5:16

• Changed state: Documenting