Professional Web Applications Themes

Problem with MySQL 5.0.18, tion and stored procedures - MySQL

Hello everybody out there, I've got a problem with MySQL 5, stored procedures and tion. I've searched the MySQL Doentation and the web for quite a while but have found nothing helpful. Given the following: 2 MySQL Servers (5.0.18-max-log) ting 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 CHT=latin1 REATE TABLE `personen2` ( `id` int(11), `vorname` varchar(50) default NULL, `nachname` varchar(50) default NULL, `text` text ) ENGINE=INNODB DEFAULT CHT=latin1 With some random junk ...

  1. #1

    Default Problem with MySQL 5.0.18, tion and stored procedures


    Hello everybody out there,

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

    Given the following:

    2 MySQL Servers (5.0.18-max-log) ting 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 CHT=latin1


    REATE TABLE `personen2` (
    `id` int(11),
    `vorname` varchar(50) default NULL,
    `nachname` varchar(50) default NULL,
    `text` text
    ) ENGINE=INNODB DEFAULT CHT=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 tion 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 Guest

  2. #2

    Default Re: Problem with MySQL 5.0.18, tion and stored procedures

    Marcel Noe wrote:
    > Hello everybody out there,
    >
    > I've got a problem with MySQL 5, stored procedures and tion. I've
    > searched the MySQL Doentation 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 tion 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 CHT=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 CHT=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 te.

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

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: Problem with MySQL 5.0.18, tion and stored procedures

    Marcel Noe wrote:
    > Hello everybody out there,
    >
    > I've got a problem with MySQL 5, stored procedures and tion. I've
    > searched the MySQL Doentation and the web for quite a while but have found
    > nothing helpful.
    >
    > Given the following:
    >
    > 2 MySQL Servers (5.0.18-max-log) ting 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 CHT=latin1
    >
    >
    > REATE TABLE `personen2` (
    > `id` int(11),
    > `vorname` varchar(50) default NULL,
    > `nachname` varchar(50) default NULL,
    > `text` text
    > ) ENGINE=INNODB DEFAULT CHT=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 tion 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
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

  4. #4

    Default Re: Problem with MySQL 5.0.18, tion and stored procedures

    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 Guest

  5. #5

    Default Re: Problem with MySQL 5.0.18, tion and stored procedures

    Marcel Noe <usenet-marcelxcore.net> wrote:
    > I'm currently writing a bug report. Thanks for the offer.
    [url]http://bugs.mysql.com/bug.php?id=16983[/url]

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

    Greetings, Marcel
    Marcel Noe Guest

  6. #6

    Default Re: Problem with MySQL 5.0.18, tion and stored procedures

    Marcel Noe wrote:
    > Marcel Noe <usenet-marcelxcore.net> wrote:
    >
    >> I'm currently writing a bug report. Thanks for the offer.
    >
    > [url]http://bugs.mysql.com/bug.php?id=16983[/url]
    >
    > 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
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

  7. #7

    Default Re: Problem with MySQL 5.0.18, tion and stored procedures

    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 Guest

  8. #8

    Default Re: Problem with MySQL 5.0.18, tion and stored procedures

    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
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

Similar Threads

  1. Replies: 1
    Last Post: December 4th, 11:33 AM
  2. MySQL 5 + MX7 stored proc problem
    By jperkins99 in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 29th, 09:12 PM
  3. Troubles in stored procedures (mySQL) in CFMX7
    By Euripide in forum Coldfusion Database Access
    Replies: 13
    Last Post: February 24th, 04:02 PM
  4. Stored Procedures mySQl 5, CFMX 6.1
    By WeeJames in forum Coldfusion Database Access
    Replies: 3
    Last Post: December 20th, 07:00 PM
  5. cf 7 and mysql 5 stored procedures and temporary tables
    By gnurulz in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 02:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139