Scott:
See my comments below regarding the replication bug you have reported.
error from log file:
010410 15:18:20 Slave: connected to master 'navrep@hsNavYkfPrd4:3306',
replication started in log 'hsNavYkfPrd4-bin.060' at position 14290269
ERROR: 1064 You have an error in your SQL syntax near '' at line 1
010410 15:18:34 Slave: did not get the expected error running query
from master - expected: 'Got an error reading communication packets',
got 'no error'
010410 15:18:34 Slave: error running query 'drop table
scratch.#sql-6fd2_9b3'
010410 15:18:34 Error running query, slave aborted. Fix the problem,
and re-start the slave thread with "mysqladmin start-slave". We stopped
at log 'hsNavYkfPrd4-bin.060' position 14298032
010410 15:18:34 Slave thread exiting, replication stopped in log
'hsNavYkfPrd4-bin.060' at position 14298032
The bug is that somehow the query that dropped the temporary table gets
logged wrong - I will take a look at the code and see how this could be
possible, and make sure it gets fixed before 3.23.37 is out. In the mean
time, there are three possible workarounds:
* do not manually drop the temporary table - the master will drop it when you
disconnect, and it will use different ( hopefully bug-free) code to log the
drop of the table
* when you hit the replication error on the slave, do SET SQL_SKIP_COUNTER=1;
SLAVE START to skip over the problem entry
* since you seem to be generating a unique name for the temporary table
anyway, and are dropping it manually later, there is really no advantage in
using the TEMPORARY attribute - just drop the temporary keyword
>First off, I assumed that anything dealing with the scratch database
wouldn't be logged into the binlog file (they are)
>Also, all of the tables created in the scratch database are temporary
tables.
>How can I make it so that these updates do not go across a slow link
when they are not needed
>Basically the sequence of commands that causes this
connect to DB
DROP TABLE IF EXISTS scratch.3340_tmp;
CREATE TEMPORARY TABLE scratch.3340_tmp SELECT * FROM ntm_user_detail
WHERE ....;
ALTER TABLE scratch.3340_tmp ADD PRIMARY
KEY(vch_station,vch_series,vch_number);
INSERT INTO scratch.3340_tmp SELECT * FROM ntm_detail WHERE ...;
SELECT vch_station,vch_series,vch_number FROM scratch.3340_tmp;
>and all of that repeats until the connection is closed.
I've also attempted adding 'replicate-ignore-db=scratch' to the
/etc/my.cnf file with no apparent results.
>Am I doing something wrong here?
replicate-ignore-db=scratch (on the slave) works only if you do "use scratch"
before you start modifying the table in scratch. If you are using db.table
syntax, you should use replicate-wild-ignore-table=scratch.%. The only way to
not send the queries to the slave at all is not to log them. This can be
accomplished in several ways:
* issue SET SQL_LOG_BIN=0 to turn off binary logging and SET SQL_LOG_BIN=1 to
turn this back ok - this requires PROCESS privilege
* use binlog-ignore-db=scratch on the master and make sure to do "use
scratch" before you start the updates you do not want to be logged, and "use
some_other_db" to turn the logging back on
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sasha Pachev <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA
<___/
---------------------------------------------------------------------
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
