Similar thing happens with alter table/drop table. alter table isn't logged to binary log, but drop table is.
----- Original Message ----- From: "Dr. Frank Ullrich" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 31, 2001 10:55 PM Subject: Replication problem (with temporary heap tables ?) > Hi, > > MySQL V. 3.23.33 with binary log and replication turned on: > > this is what i do on the master (in a perl script): > =================================================== > create temporary table TEMP_HITS ( ... ) TYPE=HEAP; > Insert several things into TEMP_HITS; > Select from TEMP_HITS and insert into target table TMP_HITLISTE; > do something with TMP_HITLISTE (see bin log excerpt below); > eventually rename TMP_HITLISTE to hitliste; > > After that my database actions end but in the bin log (see below at the > end of the bin log excerpt) the statement > 'drop table MY_SHOP.t;' comes up which, of course, cannot be replicated > because such a table doesn't exist! > As you can tell from the thread id it is my script that allegedly issued > it! > > => QUESTIONS: where does this statement come from (is it a clean up > action for the temporary table TEMP_HITS ?)? > Why is it propagated into the bin log? > > > from the bin log on the master: > =============================== > # at 71691730 > #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 > error_code=0 > use MY_SHOP; > DROP TABLE IF exists hitliste; > # at 71691794 > #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 > error_code=0 > use MY_SHOP; > create index i_hitl_pt on TMP_HITLISTE (produkt_typ); > # at 71691881 > #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 > error_code=0 > use MY_SHOP; > create index i_hitl_pt_rang on TMP_HITLISTE (pt_rang); > # at 71691969 > #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 > error_code=0 > use MY_SHOP; > create index i_hitl_abs_rang on TMP_HITLISTE (abs_rang); > # at 71692059 > #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 > error_code=0 > use MY_SHOP; > ALTER TABLE TMP_HITLISTE ADD PRIMARY KEY (artikelnummer,produkt_typ); > # at 71692162 > #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 > error_code=0 > use MY_SHOP; > RENAME TABLE TMP_HITLISTE TO hitliste; > # at 71692234 > #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 > error_code=0 > use MY_SHOP; > drop table MY_SHOP.t; > > > the err log on the slave: > ================= > 45219 > 011031 2:17:41 Slave: connected to master > '[EMAIL PROTECTED]:3306', replication started in log > '3306_bin_log.001' at position 71445219 > ERROR: 1051 Unknown table 't' > 011031 3:09:04 Slave: error running query 'drop table MY_SHOP.t' > 011031 3:09:04 Error running query, slave aborted. Fix the problem, > and > > > > Regards, > Frank. > -- > Dr. Frank Ullrich, Netzwerkadministration > Verlag Heinz Heise GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover > E-Mail: [EMAIL PROTECTED] > Phone: +49 511 5352 587; FAX: +49 511 5352 538 > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
