Hello everybody out there,

I've got a problem with MySQL 5, stored procedures and replication. I've
searched the MySQL Documentation and the web for quite a while but have found
nothing helpful.

Given the following:

2 MySQL Servers (5.0.18-max-log) replicating each other over a local ethernet.

The following two tables:

CREATE TABLE `personen` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`vorname` varchar(50) default NULL,
`nachname` varchar(50) default NULL,
`text` text,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1


REATE TABLE `personen2` (
`id` int(11),
`vorname` varchar(50) default NULL,
`nachname` varchar(50) default NULL,
`text` text
) ENGINE=INNODB DEFAULT CHARSET=latin1

With some random junk in it.

And one of the following procedure (Choose any of them, either one leads
to chaos):

-- procedure 1 --
CREATE PROCEDURE test (param1 VARCHAR(50))
BEGIN
DELETE FROM personen2;
INSERT INTO personen2 (SELECT * FROM personen WHERE nachname = param1);
SELECT * FROM personen WHERE nachname = param1;
END;

-- preocedure 2 --
CREATE PROCEDURE test (param1 VARCHAR(50))
BEGIN

DECLARE tid INT;
DECLARE tvorname VARCHAR(50);
DECLARE tnachname VARCHAR(50);

DELETE FROM personen2;

SELECT vorname, nachname, id
INTO tvorname, tnachname, tid
FROM personen
WHERE nachname = param1;

INSERT INTO personen2 (id, vorname, nachname) VALUES (tid, tvorname, tnachname);

SELECT vorname, nachname, id FROM personen WHERE nachname = param1;
END;



If I execute of these functions (call test('Foo') it runs smoothely on
the muster (data is inserted into 'personen2', as expected) but terribly
fails on the slave:

.....
Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the
right syntax to use near '' at line 1' on query. Default
database: 'test2'. Query: 'INSERT INTO personen2
(id, vorname, nachname) VALUES ( NAME_CONST('tid',1),
NAME_CONST('tvorname',@'
....

After this the replication is stopped.


In my oppinion something is terribly broken here. Maybe I'm just to stupid
or anger the gods but I would expect the master to refuse the whole procedure,
to fail to execute the call or shoot the user but not to happily execute the
query and fry the slaves.

I hope anyone can tell me what's going on.
Is this a bug in the database server?

Greetings and thanks in advance,
marcel

Marcel Noe wrote:
> Hello everybody out there,
>
> I've got a problem with MySQL 5, stored procedures and replication. I've
> searched the MySQL Documentation and the web for quite a while but have found
> nothing helpful.
>
[SNIP]

>
> If I execute of these functions (call test('Foo') it runs smoothely on
> the muster (data is inserted into 'personen2', as expected) but terribly
> fails on the slave:
>
> ....
> Last_Errno: 1064
> Last_Error: Error 'You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the
> right syntax to use near '' at line 1' on query. Default
> database: 'test2'. Query: 'INSERT INTO personen2
> (id, vorname, nachname) VALUES ( NAME_CONST('tid',1),
> NAME_CONST('tvorname',@'
> ...
>
> After this the replication is stopped.
>
>
> In my oppinion something is terribly broken here. Maybe I'm just to stupid
> or anger the gods but I would expect the master to refuse the whole procedure,
> to fail to execute the call or shoot the user but not to happily execute the
> query and fry the slaves.
>
> I hope anyone can tell me what's going on.
> Is this a bug in the database server?
>
> Greetings and thanks in advance,
> marcel

It is indeed a bug.
I tested it with the following script, which should be enough to reproduce the problem:

-- ---- CUT HERE -----------------
drop database if exists test2;
create database test2;
use test2;

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` varchar(50) default NULL,
`c2` varchar(50) default NULL,
`t` text,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1 ;

-- the result is the same for myisam tables

CREATE TABLE `t2` (
`id` int(11),
`c1` varchar(50) default NULL,
`c2` varchar(50) default NULL,
`t` text
) ENGINE=INNODB DEFAULT CHARSET=latin1 ;

insert into t1 values (1,'foo','bar','abc');
insert into t1 values (2,'bar','foobar','xyzx');

delimiter //
-- procedure 1 --
CREATE PROCEDURE test (p1 VARCHAR(50))
BEGIN
DELETE FROM t2;
INSERT INTO t2 SELECT * FROM t1 WHERE c2 = p1;
SELECT * FROM t2 WHERE c2 = p1;
END //

delimiter ;

call test('bar');

-- ---- CUT HERE -----------------

I've noticed that the error starts in the master log-bin, which stores mangled data in 'p1'
as the parameter, and consequently the slaves log-bin receive a wrong query and cannot replicate.

You should report the bug as S1 (critical) following the guidelines at this page:
http://dev.mysql.com/doc/refman/5.0/...tion-bugs.html

ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
http://datacharmer.blogspot.com/

Marcel Noe wrote:
> Hello everybody out there,
>
> I've got a problem with MySQL 5, stored procedures and replication. I've
> searched the MySQL Documentation and the web for quite a while but have found
> nothing helpful.
>
> Given the following:
>
> 2 MySQL Servers (5.0.18-max-log) replicating each other over a local ethernet.
>
> The following two tables:
>
> CREATE TABLE `personen` (
> `id` int(11) NOT NULL AUTO_INCREMENT,
> `vorname` varchar(50) default NULL,
> `nachname` varchar(50) default NULL,
> `text` text,
> PRIMARY KEY (`id`)
> ) ENGINE=INNODB DEFAULT CHARSET=latin1
>
>
> REATE TABLE `personen2` (
> `id` int(11),
> `vorname` varchar(50) default NULL,
> `nachname` varchar(50) default NULL,
> `text` text
> ) ENGINE=INNODB DEFAULT CHARSET=latin1
>
> With some random junk in it.
>
> And one of the following procedure (Choose any of them, either one leads
> to chaos):
>
> -- procedure 1 --
> CREATE PROCEDURE test (param1 VARCHAR(50))
> BEGIN
> DELETE FROM personen2;
> INSERT INTO personen2 (SELECT * FROM personen WHERE nachname = param1);
> SELECT * FROM personen WHERE nachname = param1;
> END;
>
> -- preocedure 2 --
> CREATE PROCEDURE test (param1 VARCHAR(50))
> BEGIN
>
> DECLARE tid INT;
> DECLARE tvorname VARCHAR(50);
> DECLARE tnachname VARCHAR(50);
>
> DELETE FROM personen2;
>
> SELECT vorname, nachname, id
> INTO tvorname, tnachname, tid
> FROM personen
> WHERE nachname = param1;
>
> INSERT INTO personen2 (id, vorname, nachname) VALUES (tid, tvorname, tnachname);
>
> SELECT vorname, nachname, id FROM personen WHERE nachname = param1;
> END;
>
>
>
> If I execute of these functions (call test('Foo') it runs smoothely on
> the muster (data is inserted into 'personen2', as expected) but terribly
> fails on the slave:
>
> ....
> Last_Errno: 1064
> Last_Error: Error 'You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the
> right syntax to use near '' at line 1' on query. Default
> database: 'test2'. Query: 'INSERT INTO personen2
> (id, vorname, nachname) VALUES ( NAME_CONST('tid',1),
> NAME_CONST('tvorname',@'
> ...
>
> After this the replication is stopped.
>
>
> In my oppinion something is terribly broken here. Maybe I'm just to stupid
> or anger the gods but I would expect the master to refuse the whole procedure,
> to fail to execute the call or shoot the user but not to happily execute the
> query and fry the slaves.
>
> I hope anyone can tell me what's going on.
> Is this a bug in the database server?
>
> Greetings and thanks in advance,
> marcel

Inquiring further, it seems that this bug has some relation to Bug #11931, even though
it is triggered by different events.

Please let me know if you want to submit a bug report. Otherwise, I'll do it.

ciao
gmax


--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
http://datacharmer.blogspot.com/

Giuseppe Maxia <gmax_@_cpan_._org> wrote:

> Please let me know if you want to submit a bug report. Otherwise, I'll do it.

I'm currently writing a bug report. Thanks for the offer.

Greetings Marcel

Marcel Noe <usenet-marcel@xcore.net> wrote:

> I'm currently writing a bug report. Thanks for the offer.

http://bugs.mysql.com/bug.php?id=16983

I hope this is a good bug report. It's my first one.

Greetings, Marcel

Marcel Noe wrote:
> Marcel Noe <usenet-marcel@xcore.net> wrote:
>
>> I'm currently writing a bug report. Thanks for the offer.
>
> http://bugs.mysql.com/bug.php?id=16983
>
> I hope this is a good bug report. It's my first one.
>
> Greetings, Marcel

It should be enough.
But you reported it as belonging to MySQL 5.1.5-alpha.
I added a note to confirm that the problem exists in the current version
as well.


ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
http://datacharmer.blogspot.com/

Giuseppe Maxia <gmax_@_cpan_._org> wrote:

> It should be enough.
> But you reported it as belonging to MySQL 5.1.5-alpha.
> I added a note to confirm that the problem exists in the current version
> as well.

I've tried it on 5.1.5-alpha to be sure it is not fixed in the new release.
I thought that I should report it belonging to the most recent version.
Was this wrong? :-)

Greetings, Marcel

Marcel Noe wrote:
> Giuseppe Maxia <gmax_@_cpan_._org> wrote:
>
>> It should be enough.
>> But you reported it as belonging to MySQL 5.1.5-alpha.
>> I added a note to confirm that the problem exists in the current version
>> as well.
>
> I've tried it on 5.1.5-alpha to be sure it is not fixed in the new release.
> I thought that I should report it belonging to the most recent version.
> Was this wrong? :-)
>

I guess so. I bug in the production release is more important than a bug in the
development branch :-(
I hope they will pass it to the right team.

> Greetings, Marcel

Ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
http://datacharmer.blogspot.com/