Slave thread exists after error executing invalid query from master logs

>Description:
A REPLACE query was executed on the master with a NULL value submitted in
a non-NULL (from table definition) column.
The mysql server on the master reported the error correctly.
The query was logged
The slave read the query, and exited upon the error generated following
the run of the query.
The slave-thread could not be re-started because it kept failing running
the query stored in the master log.

Slave error log:

021020 23:21:44 Slave: error running query 'REPLACE INTO tbl(x, y, z, w)
VALUES (NULL ,'abc','abc','abc')'
021020 23:21:44 Error running query, slave aborted. Fix the problem, and
re-start the slave thread with "mysqladmin start-slave". We stopped
at log 'serverx-bin.002' position 609
021020 23:21:44 Slave thread exiting, replication stopped in log
'serverx-bin.002' at position 609
021021 15:22:41 Slave: connected to master
'slave@ip.ip.ip.ip:3306', replication started in log 'serverx-bin.002' at
position 609
ERROR: 1048 Column 'x' cannot be null
021021 15:22:41 Slave: error running query 'REPLACE INTO tbl(x, y, z, w)
VALUES (NULL ,'abc','abc','abc')'
021021 15:22:41 Error running query, slave aborted. Fix the problem, and
re-start the slave thread with "mysqladmin start-slave". We stopped
at log 'serverx-bin.002' position 609
021021 15:22:41 Slave thread exiting, replication stopped in log
'serverx-bin.002' at position 609

>How-To-Repeat:

>Fix:
I believe the slave thread should not terminate upon running an invalid
SQL query. Perhaps the error should be logged and operation should continue
with the next event in the master log.


>Submitter-Id:
>Originator: Lucian Kafka
>Organization: Conexim Australia http://www.conexim.com.au

>MySQL support: none
>Synopsis: Slave thread exists after error executing invalid query
from master logs
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.53 (Source distribution)

>Environment:
System: Linux xxx.xxx.xxx 2.4.17 #1 Fri Feb 22 17:37:33 EST 2002 i686 unknown
Architecture: i686

Some paths: /usr/local/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.2 2.96-108.7.2)
Compilation info: CC='gcc' CFLAGS='-O3
-mpentiumpro' CXX='gcc' CXXFLAGS='-O3 -mpentiumpro -felide-constructors
-fno-exceptions -fno-rtti' \
LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Oct 18 12:35 /lib/libc.so.6 ->
libc-2.2.4.so
-rwxr-xr-x 1 root root 1285884 Sep 10 02:10 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27336078 Sep 10 01:48 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 10 01:48 /usr/lib/libc.so
lrwxrwxrwx 1 root root 10 Jun 14 13:22
/usr/lib/libc-client.a -> c-client.a
Configure command: ./configure --prefix=/usr/local/mysql --enable-assembler
--with-mysqld-ldflags=-all-static --localstatedir=/var/mysql/data\
'CFLAGS=-O3 -mpentiumpro' 'CXXFLAGS=-O3 -mpentiumpro -felide-constructors
-fno-exceptions -fno-rtti' CXX=gcc


------------------------------------------------------------ ---------
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 bugs-thread12816@lists.mysql.com
To unsubscribe, e-mail

Re: Slave thread exists after error executing invalid query from master logs

On Tuesday 22 October 2002 10:34, Lucian Daniel Kafka wrote:
> >Description:
> =09A REPLACE query was executed on the master with a NULL value submitt=
ed in=20
> a non-NULL (from table definition) column.
> =09The mysql server on the master reported the error correctly.
> =09The query was logged
> =09The slave read the query, and exited upon the error generated follow=
ing=20
> the run of the query.
> =09The slave-thread could not be re-started because it kept failing run=
ning=20
> the query stored in the master log.
>

Dear Lucian,

We would really appreciate if you will be able to provide repeatable exam=
ple=20
for the problem. Master and slave configuration as well as complete set o=
f=20
commands which should be executed to reproduce the error.

As I undertand from your explanation REPLACE query terminates with error =
on=20
the master, but which error ? Is this pharsing stage error or execution=20
stage error ? This is important as if it is pharsing stage error query=20
should not be logged at all, while if it is execution stage (while some r=
ows=20
were already replaces) it should be logged with special error code.=20


Speaking about slave behaviour - by default replication is configured to =
stop=20
on every query execution error. This was choosen to avoid "silent=20
inconsistent" of the slave - just warning in the error log is not enough=
in=20
this case.

If you would like repication to continue in case of error you may set lis=
t of=20
errors which should be ignored by slave thread.



--=20
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com M: +7 095 725 4955


------------------------------------------------------------ ---------
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 bugs-thread12861@lists.mysql.com
To unsubscribe, e-mail

Slave thread exists after error executing invalid query from master logs

Hi!

>>>>> "Lucian" == Lucian Daniel Kafka writes:

>> Description:
Lucian> A REPLACE query was executed on the master with a NULL value submitted in
Lucian> a non-NULL (from table definition) column.
Lucian> The mysql server on the master reported the error correctly.
Lucian> The query was logged
Lucian> The slave read the query, and exited upon the error generated following
Lucian> the run of the query.
Lucian> The slave-thread could not be re-started because it kept failing running
Lucian> the query stored in the master log.

Lucian> Slave error log:

Lucian> 021020 23:21:44 Slave: error running query 'REPLACE INTO tbl(x, y, z, w)
Lucian> VALUES (NULL ,'abc','abc','abc')'
Lucian> 021020 23:21:44 Error running query, slave aborted. Fix the problem, and
Lucian> re-start the slave thread with "mysqladmin start-slave". We stopped
Lucian> at log 'serverx-bin.002' position 609



As Peter said in a previous email:

We would be very interested to get an example of the above so that we
can repeat this.

What is strange in this case is that the master should never have
logged this query as it didn't update anything on the master.

I did a test of this as follows:

mysql> create table t1 (a int not null);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(null);
ERROR 1048: Column 'a' cannot be null
mysql> replace into t1 values(null);
ERROR 1048: Column 'a' cannot be null

And the binary log only contaned the 'create table' command.

It would be interesting to know what is different in your case...

Regards,
Monty

--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com

------------------------------------------------------------ ---------
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 bugs-thread12953@lists.mysql.com
To unsubscribe, e-mail

Re: Slave thread exists after error executing invalid query from master logs

At 06:11 PM 11/11/2002 +0200, you wrote:

>Hi!
>
> >>>>> "Lucian" == Lucian Daniel Kafka writes:
>
> >> Description:
>Lucian> A REPLACE query was executed on the master with a NULL
>value submitted in
>Lucian> a non-NULL (from table definition) column.
>Lucian> The mysql server on the master reported the error correctly.
>Lucian> The query was logged
>Lucian> The slave read the query, and exited upon the error
>generated following
>Lucian> the run of the query.
>Lucian> The slave-thread could not be re-started because it kept
>failing running
>Lucian> the query stored in the master log.
>
>Lucian> Slave error log:
>
>Lucian> 021020 23:21:44 Slave: error running query 'REPLACE INTO tbl(x,
>y, z, w)
>Lucian> VALUES (NULL ,'abc','abc','abc')'
>Lucian> 021020 23:21:44 Error running query, slave aborted. Fix the
>problem, and
>Lucian> re-start the slave thread with "mysqladmin start-slave". We stopped
>Lucian> at log 'serverx-bin.002' position 609
>
>
>
>As Peter said in a previous email:
>
>We would be very interested to get an example of the above so that we
>can repeat this.

Sorry - I didn't get that email, as I have unsubscribed from the list...

Basically, the reason for the failure was slightly different - below is an
exact snippet of SQL causing it (code from the mydns import utility). It
looks (as someone pointed out) that @variable assignment don't carry across
replication - and that makes the slave unrecoverable with SQL statements
like below.


INSERT IGNORE INTO soa
(origin,ns,mbox,serial,refresh,retry,expire,minimum,ttl) VALUES
('somedomain.net.au.','ns.somens.com.au.','hostmaster.someho stmaster.com.au.',2001042401,28800,7200,604800,86400,86400);

SELECT @soa_id:=id FROM soa WHERE origin='somedomain.net.au.';

UPDATE soa SET
ns='ns.somens.com.au.',mbox='hostmaster.somehostmaster.com.a u.',serial=2001042401,refresh=28800,retry=7200,expire=604800 ,minimum=86400,ttl=86400
WHERE id=@soa_id;

REPLACE INTO rr (zone,name,type,data,ttl) VALUES
(@soa_id,'','NS','ns.somens.com.au.',86400);

Please CC reply to my email.



Kind regards,

Lucian Kafka
www.conexim.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 bugs-thread12966@lists.mysql.com
To unsubscribe, e-mail

Re: Slave thread exists after error executing invalid query from master logs

Hi!

>>>>> "Lucian" == Lucian Daniel Kafka writes:



Lucian> Basically, the reason for the failure was slightly different - below is an
Lucian> exact snippet of SQL causing it (code from the mydns import utility). It
Lucian> looks (as someone pointed out) that @variable assignment don't carry across
Lucian> replication - and that makes the slave unrecoverable with SQL statements
Lucian> like below.


Lucian> INSERT IGNORE INTO soa
Lucian> (origin,ns,mbox,serial,refresh,retry,expire,minimum,ttl) VALUES
Lucian> ('somedomain.net.au.','ns.somens.com.au.','hostmaster.someho stmaster.com.au.',2001042401,28800,7200,604800,86400,86400);

Lucian> SELECT @soa_id:=id FROM soa WHERE origin='somedomain.net.au.';

Lucian> UPDATE soa SET
Lucian> ns='ns.somens.com.au.',mbox='hostmaster.somehostmaster.com.a u.',serial=2001042401,refresh=28800,retry=7200,expire=604800 ,minimum=86400,ttl=86400
Lucian> WHERE id=@soa_id;

Lucian> REPLACE INTO rr (zone,name,type,data,ttl) VALUES
Lucian> (@soa_id,'','NS','ns.somens.com.au.',86400);

ok, that explains the problem.

The current replication doesn't work with user variables used to
update tables; This is listed on the replication section of the
manual and is scheduled to be fixed in 4.1.

Sorry for the inconvenience.

Regards,
Monty

------------------------------------------------------------ ---------
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 bugs-thread12982@lists.mysql.com
To unsubscribe, e-mail