>Description:
I have two MySQL servers (version 3.23.39) configured for two-
way replication; call them server A and server B. When a large
row (~ 3Mb) is entered into a mediumblob field in a table on server A,
this row is replicated to server B. Since the servers are
configured for two-way replication, server B generates an entry
in its own binary log for the record, which server A then
attempts to replicate (it ultimately should ignore the row, since
the log indicates that it was generated at server A). However,
server A logs an error:
Error reading packet from server: Lost connection to MySQL server
during query (read_errno 0,server_errno=2013)
Slave: Failed reading log event, reconnecting to retry, log
'serverb-bin.007' position 2896726
>How-To-Repeat:
Note that the following scenario is extrapolated from my actual
running servers. I don't have the machines available to set up
a test environment; the example below simply has names changed
to protect the innocent :)
/etc/my.cnf, server A:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_allowed_packet=16M
log-bin
log-slave-updates
server-id=1
master-host=servera
master-user=repl
master-password=foo
master-port=3306
replicate-do-db=test_db
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
/etc/my.cnf, server B:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_allowed_packet=16M
log-bin
log-slave-updates
server-id=2
master-host=serverb
master-user=repl
master-password=foo
master-port=3306
replicate-do-db=test_db
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Example table definition:
CREATE TABLE test_table (row_id varchar(17) NOT NULL default '',
bigdata mediumblob,
PRIMARY KEY (row_id)) TYPE=MyISAM;
Insert a large row into test_db.test_table on server A:
INSERT INTO test_table VALUES ('this_row', '[Approx. 3Mb data]');
Observe error logs on server A:
Error reading packet from server: Lost connection to MySQL
server during query (read_errno 0,server_errno=2013)
Slave: Failed reading log event, reconnecting to retry, log
'serverb-bin.007' position 2896726
Slave: reconnected to master 'repl@servera:3306',replication
resumed in log 'serverb-bin.007' at position 2896726
[error sequence repeats]
>Fix:
Manually change server A's slave position using the following
methodology. Note that using slave_skip_counter does not work; it
reports the same error as above:
From the error log on server A, determine the byte position in server
B's binary log at which server A is failing:
In this example, 2896726
Shut down the MySQL server on server A:
mysqladmin shutdown
Determine the byte position of the row following the problematic
one in server B's binary log:
mysqlbinlog -j 2896726 serverb | less
Browse past the mediumblob insert, finding the position of
the following record (identified by "^# at nnnn")
Edit the master.info file on server A, replacing the slave position
(second line) with the value of "nnnn" from server B.
Restart MySQL on server A.
>Submitter-Id: <submitter ID>
>Originator: Simon Cocking
>Organization:
Network Reconnaissance Pty. Ltd.
>MySQL support: none
>Synopsis: Two-way replication of rows > ~3Mb failing
>Severity: serious
>Priority: high
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.39 (Official MySQL RPM)
>Server: /usr/bin/mysqladmin Ver 8.21 Distrib 3.23.39, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.39-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 8 min 48 sec
Threads: 10 Questions: 876 Slow queries: 0 Opens: 32 Flush tables: 1 Open tables:
26 Queries per second avg: 1.659
>Environment:
System: Linux 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 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-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-81)
Compilation info: CC='egcs' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
CXX='egcs' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 May 29 01:52 /lib/libc.so.6 -> libc-2.2.2.so
-rwxr-xr-x 1 root root 1236396 Apr 7 07:58 /lib/libc-2.2.2.so
-rw-r--r-- 1 root root 26350254 Apr 7 05:27 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 7 05:27 /usr/lib/libc.so
Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static
--with-client-ldflags=-all-static --without-berkeley-db --without-innodb
--enable-assembler --with-mysqld-user=mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
--with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin
--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql
--infodir=/usr/info --includedir=/usr/include --mandir=/usr/man
'--with-comment=Official MySQL RPM'
--
Message protected by MailGuard: e-mail anti-virus and content filtering.
http://www.mailguard.com.au
---------------------------------------------------------------------
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
