Bug #2930 multitable update does not replicate
Submitted: 23 Feb 2004 17:34 Modified: 26 Feb 2004 15:10
Reporter: Przemyslaw Popielarski Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.0.18 OS:Linux (Linux x86)
Assigned to: CPU Architecture:Any

[23 Feb 2004 17:34] Przemyslaw Popielarski
Description:
When doing multitable update on master, the slaves does not catch up with changes. Got binlog-do-db=DB on master's my.cnf, but DB was selected (use DB).
All tables were from this DB. Only one table was to be updated on master.

How to repeat:
UPDATE 
 tKsidata,
 tKsi_ksiazki 	LEFT JOIN tKsi_autorzy as a1 ON (tKsi_ksiazki.KSI=a1.KSI AND a1.LP='1' AND a1.GR='0')
	LEFT JOIN tKsi_autorzy as a2 ON (tKsi_ksiazki.KSI=a2.KSI AND a2.LP='2' AND a2.GR='0')
	LEFT JOIN tKsi_autorzy as a3 ON (tKsi_ksiazki.KSI=a3.KSI AND a3.LP='3' AND a3.GR='0')
	LEFT JOIN tKsigdanskksiazki ON tKsi_ksiazki.KSI=tKsigdanskksiazki.KSI
	LEFT JOIN tKsigliwiceksiazki ON tKsi_ksiazki.KSI=tKsigliwiceksiazki.KSI
	LEFT JOIN tKsikrakowksiazki ON tKsi_ksiazki.KSI=tKsikrakowksiazki.KSI
	LEFT JOIN tKsiwysksiazki ON tKsi_ksiazki.KSI=tKsiwysksiazki.KSI
	LEFT JOIN tBooksextra ON tKsi_ksiazki.KSI=tBooksextra.KSI
	LEFT JOIN tUnits_original ON tKsi_ksiazki.DZIAL=tUnits_original.DZIAL
	LEFT JOIN tSeries_original ON tKsi_ksiazki.SERIA=tSeries_original.SERIA
	LEFT JOIN tCovers ON tKsi_ksiazki.KSI=tCovers.KSI,
	tGrupy_original
SET 
	tKsidata.KSI=LCASE(tKsi_ksiazki.KSI), 
	tKsidata.PKW=tKsi_ksiazki.PKW, 
	tKsidata.TYTUL=tKsi_ksiazki.TYTUL,
	tKsidata.WYD=LCASE(tKsi_ksiazki.WYD), 
	tKsidata.ROK=tKsi_ksiazki.ROK, 
	tKsidata.NRW=tKsi_ksiazki.NRW, 
	tKsidata.STRON=tKsi_ksiazki.STRON,
	tKsidata.ISBN=tKsi_ksiazki.ISBN, 
	tKsidata.D_POCZ=tKsi_ksiazki.D_POCZ, 
	tKsidata.VAT_REALNY=CASE tKsi_ksiazki.SVAT WHEN 2 THEN 0 WHEN 3 THEN 2 WHEN 4 THEN 7 WHEN 5 THEN 22 WHEN 6 THEN 12 WHEN 7 THEN 17 WHEN 8 THEN 3 ELSE NULL END, 
	tKsidata.CENA_REALNA=IF(tKsi_ksiazki.C_ZAOK0,TRUNCATE(((tKsi_ksiazki.C_DET-tKsi_ksiazki.C_DET*tKsi_ksiazki.RABAT/100)/tKsi_ksiazki.C_ZAOK0)+0.5,0)*tKsi_ksiazki.C_ZAOK0,tKsi_ksiazki.C_DET-tKsi_ksiazki.C_DET*tKsi_ksiazki.RABAT/100),
	tKsidata.C_DET=IF(tKsi_ksiazki.C_ZAOK0,TRUNCATE(tKsi_ksiazki.C_DET/tKsi_ksiazki.C_ZAOK0+0.5,0)*tKsi_ksiazki.C_ZAOK0, tKsi_ksiazki.C_DET),
	tKsidata.C_WAL0=tKsi_ksiazki.C_WAL0,
	tKsidata.RABAT=tKsi_ksiazki.RABAT,
	tKsidata.STAN_REALNY=IF(tKsi_ksiazki.STAN-tKsi_ksiazki.S_REZ+IFNULL(tKsigdanskksiazki.STAN,0)-IFNULL(tKsigdanskksiazki.S_REZ,0)+IFNULL(tKsigliwiceksiazki.STAN,0)-IFNULL(tKsigliwiceksiazki.S_REZ,0)+IFNULL(tKsikrakowksiazki.STAN,0)-IFNULL(tKsikrakowksiazki.S_REZ,0)+IFNULL(tKsiwysksiazki.STAN,0)-IFNULL(tKsiwysksiazki.S_REZ,0) < 0, 0, tKsi_ksiazki.STAN-tKsi_ksiazki.S_REZ+IFNULL(tKsigdanskksiazki.STAN,0)-IFNULL(tKsigdanskksiazki.S_REZ,0)+IFNULL(tKsigliwiceksiazki.STAN,0)-IFNULL(tKsigliwiceksiazki.S_REZ,0)+IFNULL(tKsikrakowksiazki.STAN,0)-IFNULL(tKsikrakowksiazki.S_REZ,0)+IFNULL(tKsiwysksiazki.STAN,0)-IFNULL(tKsiwysksiazki.S_REZ,0)),
	tKsidata.STAN_WAW=IF(tKsi_ksiazki.STAN-tKsi_ksiazki.S_REZ <= 0, 0, tKsi_ksiazki.STAN-tKsi_ksiazki.S_REZ),
	tKsidata.NAZWISKO_1=a1.NAZW, 
	tKsidata.IMIE1_1=IF(CHAR_LENGTH(a1.IM1)=1,CONCAT(a1.IM1,'.'),a1.IM1), 
	tKsidata.IMIE2_1=IF(CHAR_LENGTH(a1.IM2)=1,CONCAT(a1.IM2,'.'),a1.IM2), 
	tKsidata.NAZWISKO_2=a2.NAZW, 
	tKsidata.IMIE1_2=IF(CHAR_LENGTH(a2.IM1)=1,CONCAT(a2.IM1,'.'),a2.IM1), 
	tKsidata.IMIE2_2=IF(CHAR_LENGTH(a2.IM2)=1,CONCAT(a2.IM2,'.'),a2.IM2), 
	tKsidata.NAZWISKO_3=a3.NAZW, 
	tKsidata.IMIE1_3=IF(CHAR_LENGTH(a3.IM1)=1,CONCAT(a3.IM1,'.'),a3.IM1), 
	tKsidata.IMIE2_3=IF(CHAR_LENGTH(a3.IM2)=1,CONCAT(a3.IM2,'.'),a3.IM2),
	tKsidata.GRKOD=tGrupy_original.GRKOD,
	tKsidata.DZIALID=tUnits_original.DZIALID,
	tKsidata.SERIAID=tSeries_original.SERIAID,
	tKsidata.KSIDESCPL=CONCAT_WS('<p></p>',IFNULL(tBooksextra.BOOKCOMMENT1PL,tBooksextra.BOOKCOMMENT1EN),IFNULL(tBooksextra.BOOKCOMMENT2PL,tBooksextra.BOOKCOMMENT2EN),IFNULL(tBooksextra.BOOKCOMMENT3PL,tBooksextra.BOOKCOMMENT3EN)),
	tKsidata.KSIDESCEN=CONCAT_WS('<p></p>',tBooksextra.BOOKCOMMENT1EN,tBooksextra.BOOKCOMMENT2EN,tBooksextra.BOOKCOMMENT3EN),
	tKsidata.TOC=tBooksextra.TOC,
	tKsidata.MIESIAC=tBooksextra.MIESIAC,
	tKsidata.BINDING=tBooksextra.BINDING,
	tKsidata.STATUS=tBooksextra.STATUS,
	tKsidata.COVER=IF(tCovers.KSI,'1','0'),
	tKsidata.SHOWPRICE=IFNULL(tBooksextra.SHOWPRICE,1),
	tKsidata.VISIBLE=IFNULL(tBooksextra.VISIBLE,1)
WHERE
	tKsidata.KSI=tKsi_ksiazki.KSI AND
	tKsi_ksiazki.ID1=tGrupy_original.ID1 AND tKsi_ksiazki.ID2=tGrupy_original.ID2
600 rows updated on master, no row updated on slave.
[24 Feb 2004 11:29] MySQL Verification Team
Could not repaat with 4.0.18.

I got this in both binary log and executed on the slave:

[/mnt/work/mysql-4.1]$ ./client/mysqlbinlog /usr/local/var/sinisa-bin.000001 
# at 4
#040224 21:28:07 server id 1  log_pos 4         Start: binlog v 3, server v 4.1.2-alpha-debug-log created 040224 21:28:07 at startup
# at 79
#040224 21:28:23 server id 1  log_pos 79        Query   thread_id=1     exec_time=0     error_code=0
use bug;
SET TIMESTAMP=1077650903;
create table t1 (id bigint(20) auto_increment primary key, title text);
# at 183
#040224 21:28:27 server id 1  log_pos 183       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1077650907;
create table t2 (id bigint(20) auto_increment primary key, title text);
# at 287
#040224 21:28:38 server id 1  log_pos 287       Intvar
SET INSERT_ID=1;
# at 315
#040224 21:28:38 server id 1  log_pos 315       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1077650918;
insert into t1 set id=NULL, title="test";
# at 389
#040224 21:28:39 server id 1  log_pos 389       Intvar
SET INSERT_ID=2;
# at 417
#040224 21:28:39 server id 1  log_pos 417       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1077650919;
insert into t1 set id=NULL, title="test";
# at 491
#040224 21:28:39 server id 1  log_pos 491       Intvar
SET INSERT_ID=3;
# at 519
#040224 21:28:39 server id 1  log_pos 519       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1077650919;
insert into t1 set id=NULL, title="test";
# at 593
#040224 21:28:41 server id 1  log_pos 593       Intvar
SET INSERT_ID=4;
# at 621
#040224 21:28:41 server id 1  log_pos 621       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1077650921;
insert into t1 set id=NULL, title="test";
# at 695
#040224 21:28:44 server id 1  log_pos 695       Intvar
SET INSERT_ID=1;
# at 723
#040224 21:28:44 server id 1  log_pos 723       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1077650924;
insert into t2 set id=NULL, title="test";
# at 797
#040224 21:28:53 server id 1  log_pos 797       Intvar
SET INSERT_ID=2;
# at 825
#040224 21:28:53 server id 1  log_pos 825       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1077650933;
insert into t2 set id=NULL, title="test";
# at 899
#040224 21:30:22 server id 1  log_pos 899       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1077651022;
update t1 left join t2 on t1.id = t2.id set t1.title="tttt";
# at 992
[24 Feb 2004 12:19] Przemyslaw Popielarski
Okey, tried again today and got this in master's and slave's logs. 
Seems to be ok now.
[26 Feb 2004 15:10] Guilhem Bichot
Hi,
Indeed, I tested this and it worked.
Guilhem