DDL for temporary tables is replicated

[Seppo Jaakola]

DDL statements related to temporary tables seems to be replicated. This is unnecessary and should be avoided.

Howto reproduce:

1. start two nodes and enable wsrep debugging.
2. issue in node1:
   CREATE TEMPORARY TABLE test.t (i int);
   DROP TABLE test.t;
3. browse error log in node2

[Mohammed Munazir (mdmunazir)]

I follow the bug#

DDL statements related to temporary tables seems to be replicated.
This is unnecessary and should be avoided.

Howto reproduce:

1. start two nodes and enable wsrep debugging.
2. issue in node1:
   CREATE TEMPORARY TABLE test.t (i int);
   DROP TABLE test.t;
3. browse error log in node2

When i run this
CREATE TEMPORARY TABLE test.t (i int);
   DROP TABLE test.t;
------> I didn't receive any error.... but when i run on my database
which with name ets... on Node 1

i run this query ..... [SQL] create temporary table contractSigned as
select B.Contract_ID, B.Contract_Sign_Date, A.BP_Id, C.CD_Id,
B.Start_Date, C.Length_Of_Training, C.Length_Of_Employment,
C.Training_Fee, C.Training_Percentage, C.After_Training_Percentage,
C.TotalSlot, D.AIS_Date, D.PNSFD_Month,
Date(DATE_ADD(D.AIS_Date,interval C.Length_Of_Training month) ) as
trainingEnd, Date( DATE_ADD(D.AIS_Date,interval (C.Length_Of_Training
+Length_Of_Employment) month) ) as EmploymentEnd from cbp_bpprofile A,
cbp_contract B, cbp_contractdeliverable C, cbp_cdiplan D where A.BP_Id
= B.BP_Id and B.Contract_ID = C.Contract_ID and C.CD_Id = D.CD_Id and
B.Contract_Sign_Date != '0000-00-00' and D.PNSFD_Month != 0 and
A.Industry_Type != '' and A.Industry_Type = 359 and
Year(B.Contract_Sign_Date) >= '2008'

[Err] 2003 - Can't connect to MySQL server on '10.10.5.105' (10061)

-------------------------------------------------------------------------------------------------------------------------------------
Logs from Node 2::::

111212 21:06:26 [Note] WSREP: (701dfd2a-24c5-11e1-0800-8daf71774292,
'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive
peers: tcp://10.10.5.105:4567
111212 21:06:27 [Note] WSREP: (701dfd2a-24c5-11e1-0800-8daf71774292,
'tcp://0.0.0.0:4567') reconnecting to
34da9138-24c5-11e1-0800-930e8a02f5ed (tcp://10.10.5.105:4567), attempt
0
111212 21:06:30 [Note] WSREP:
evs::proto(701dfd2a-24c5-11e1-0800-8daf71774292, OPERATIONAL,
view_id(REG,34da9138-24c5-11e1-0800-930e8a02f5ed,2)) suspecting node:
34da9138-24c5-11e1-0800-930e8a02f5ed
111212 21:06:30 [Note] WSREP: remote endpoint tcp://10.10.5.105:4567
changed identity 34da9138-24c5-11e1-0800-930e8a02f5ed ->
d1a5601c-24eb-11e1-0800-8b54d5fdd857
111212 21:06:30 [Note] WSREP: (701dfd2a-24c5-11e1-0800-8daf71774292,
'tcp://0.0.0.0:4567') turning message relay requesting off
111212 21:06:31 [Note] WSREP:
evs::proto(701dfd2a-24c5-11e1-0800-8daf71774292, GATHER, view_id(REG,
34da9138-24c5-11e1-0800-930e8a02f5ed,2)) suspecting node:
34da9138-24c5-11e1-0800-930e8a02f5ed
111212 21:06:32 [Note] WSREP:
evs::proto(701dfd2a-24c5-11e1-0800-8daf71774292, GATHER, view_id(REG,
34da9138-24c5-11e1-0800-930e8a02f5ed,2)) suspecting node:
34da9138-24c5-11e1-0800-930e8a02f5ed
111212 21:06:32 [Note] WSREP:
evs::proto(701dfd2a-24c5-11e1-0800-8daf71774292, GATHER, view_id(REG,
34da9138-24c5-11e1-0800-930e8a02f5ed,2)) suspecting node:
34da9138-24c5-11e1-0800-930e8a02f5ed
111212 21:06:33 [Note] WSREP:
evs::proto(701dfd2a-24c5-11e1-0800-8daf71774292, GATHER, view_id(REG,
34da9138-24c5-11e1-0800-930e8a02f5ed,2)) suspecting node:
34da9138-24c5-11e1-0800-930e8a02f5ed
111212 21:06:33 [Note] WSREP:
evs::proto(701dfd2a-24c5-11e1-0800-8d...

[Seppo Jaakola (seppo-jaakola)]

FIx pushed in wsrep-5.5: http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3666
--to avoid replication of create temporary table

[Seppo Jaakola (seppo-jaakola)]

FIx for DROP & ALTER pushed in wsrep-5.5: http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3667