>Description:
I have two DB servers that we will simply call MASTER and
SLAVE. MASTER is started with --binlog-do-db=DB1 so that only DB1 will be
replicated to SLAVE. When SLAVE is initially started everything seems
normal, in that all update/delete/insert/etc. queries are propogated to
SLAVE. However, if I issue a query similar to the one below, the SLAVE
dies:
Example Query: INSERT INTO DB2.sometable (field1,field2) SELECT
field1,field2 FROM DB1.sometable WHERE
(some condition);
Query Explanation: Basically it moves all data from one table in
DB1 to a table in DB2
Error:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Master_Host: master.host
Master_User: replication
Master_Port: 3306
Connect_retry: 60
Log_File: master-bin.001
Pos: 31191383
Slave_Running: No
Replicate_do_db:
Replicate_ignore_db:
Last_errno: 1146
Last_error: error 'Table 'DB2.sometable' doesn't exist' on
query 'INSERT INTO DB2.sometable (field1,field2) SELECT field1,field2 FROM
DB1.sometable WHERE (some condition);'
Skip_counter: 0
1 row in set (0.00 sec)
>How-To-Repeat:
I can reproduce this error with the above configuration at any
time. All servers are binary distribution of 3.23.44 for linux
(glibc). MySQLd startup options for each server are as follows:
MASTER
------
--set-variable back_log=100
--set-variable long_query_time=5
--set-variable delayed_insert_timeout=60
--set-variable delayed_insert_limit=30
--set-variable max_connections=256
--set-variable max_connect_errors=10000
--set-variable table_cache=256
--set-variable wait_timeout=60
--set-variable thread_cache_size=10
--set-variable key_buffer_size=64M
--log-bin
--server-id=1
--binlog-do-db=DB1
--user=mysql
SLAVE
-----
--set-variable back_log=100
--set-variable long_query_time=5
--set-variable max_connections=256
--set-variable max_connect_errors=10000
--set-variable table_cache=256
--set-variable wait_timeout=60
--master-host=master.host
--master-user=someuser
--master-password=XXXXXXX
--server-id=2
--user=mysql
>Fix:
Well I can rewrite my query, but that doesn't seem like a
permanant solution. I'm open to suggestions. The query IMHO shouldn't be
getting put into the binary log since the DB that is effected is not
the one specified with --binlod-do-db flag. I suspect whatever
--binlog-do-db does it at fault. This behavior may be by design, I don't
know. Any response would be appreciated, I am not on the mysql list so
please respond via personal email if possible. Even if this behavior is
intentional I would appreciate knowing, so I can determine what needs
redesigned in my system.
>Submitter-Id: <submitter ID>
>Originator: wrath
>Organization:
>MySQL support: none
>Synopsis: Replication errors with INSERT INTO......SELECT FROM....
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.44 (Official MySQL binary)
>Environment:
System: Linux XXXXXX 2.2.19 #2 Fri Jun 8 04:23:06 UTC 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3
-mpentium -felide-constructors' LDFLAGS='-static'
LIBC:
lrwxrwxrwx 1 root root 13 Apr 19 2001 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x 1 root root 1013224 Mar 21 2000 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20266642 Mar 20 2000 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Mar 20 2000 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official
MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-assembler
--with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared
---------------------------------------------------------------------
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
