| 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: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 4.0.18 | OS: | Linux (Linux x86) |
| Assigned to: | CPU Architecture: | Any | |
[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
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.