Multiple updates on 4.0.4

MySql 4.0.4 - mysqld-opt on Win98 /Duron 1200 -128Mb memory (the scrip was
executed from mysql.exe >.\ TstMup.sql)

1.I tried to use multiple updates for something like :
update table_one a set a.total=0 where 1=1;
update table_one a,table_two b set a.total=a.total+b.value where
a.primary_key=b.some_field ....(relation one to many)
I suposed that a.total should be the sum of all related b.value but the
behavior is tottaly different. Is that incorrect ?
2. The server crashed when I tried to study the real behavior of the
multiple updates.

HOW-TO-REPEAT:

-- TestMup.sql
-- test multiple updates
use test;
drop table if exists nr1 ;
drop table if exists nr2;
create table nr1 (n numeric(10));
insert into nr1 values (0) ;
select * from nr1;
create table nr2 (n numeric(10));
insert into nr2 values (1),(2),(4),(8),(16),(32);
select * from nr2;
update nr1 a,nr2 b set a.n=a.n+b.n where 1=1;
SELECT * FROM NR1;
-- the result should have been 63
UPDATE NR1 SET N=0;
SELECT * FROM NR1;
update nr2 b,nr1 a set a.n=a.n+b.n where 1=1;
-- my SERVER (mysqld-opt.exe) crashes here


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

Re: Multiple updates on 4.0.4

Constantinescu Nicolae writes:
>
> MySql 4.0.4 - mysqld-opt on Win98 /Duron 1200 -128Mb memory (the scrip was
> executed from mysql.exe >.\ TstMup.sql)
>
> 1.I tried to use multiple updates for something like :
> update table_one a set a.total=0 where 1=1;
> update table_one a,table_two b set a.total=a.total+b.value where
> a.primary_key=b.some_field ....(relation one to many)
> I suposed that a.total should be the sum of all related b.value but the
> behavior is tottaly different. Is that incorrect ?
> 2. The server crashed when I tried to study the real behavior of the
> multiple updates.
>
> HOW-TO-REPEAT:
>
> -- TestMup.sql
> -- test multiple updates
> use test;
> drop table if exists nr1 ;
> drop table if exists nr2;
> create table nr1 (n numeric(10));
> insert into nr1 values (0) ;
> select * from nr1;
> create table nr2 (n numeric(10));
> insert into nr2 values (1),(2),(4),(8),(16),(32);
> select * from nr2;
> update nr1 a,nr2 b set a.n=a.n+b.n where 1=1;
> SELECT * FROM NR1;
> -- the result should have been 63
> UPDATE NR1 SET N=0;
> SELECT * FROM NR1;
> update nr2 b,nr1 a set a.n=a.n+b.n where 1=1;
> -- my SERVER (mysqld-opt.exe) crashes here
>

Hi!

For your info, a.total in each of the rows is updated only once. That
is how any UPDATE will work. In each row a.total=a.total+b.value will
only add one corresponding value from table b.

Regarding crash, it should not happen and I will test this on Linux
and if I repeat it fix ASAP.

--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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-thread12677@lists.mysql.com
To unsubscribe, e-mail

Re: Multiple updates on 4.0.4

Constantinescu Nicolae writes:
>
> MySql 4.0.4 - mysqld-opt on Win98 /Duron 1200 -128Mb memory (the scrip was
> executed from mysql.exe >.\ TstMup.sql)
>
> 1.I tried to use multiple updates for something like :
> update table_one a set a.total=0 where 1=1;
> update table_one a,table_two b set a.total=a.total+b.value where
> a.primary_key=b.some_field ....(relation one to many)
> I suposed that a.total should be the sum of all related b.value but the
> behavior is tottaly different. Is that incorrect ?
> 2. The server crashed when I tried to study the real behavior of the
> multiple updates.
>
> HOW-TO-REPEAT:
>
> -- TestMup.sql
> -- test multiple updates
> use test;
> drop table if exists nr1 ;
> drop table if exists nr2;
> create table nr1 (n numeric(10));
> insert into nr1 values (0) ;
> select * from nr1;
> create table nr2 (n numeric(10));
> insert into nr2 values (1),(2),(4),(8),(16),(32);
> select * from nr2;
> update nr1 a,nr2 b set a.n=a.n+b.n where 1=1;
> SELECT * FROM NR1;
> -- the result should have been 63
> UPDATE NR1 SET N=0;
> SELECT * FROM NR1;
> update nr2 b,nr1 a set a.n=a.n+b.n where 1=1;
> -- my SERVER (mysqld-opt.exe) crashes here

Hi!

Thank you for your bug report.

It helped me fix a serious bug in multi-table updates. Fix will come
in 4.0.5, and this is a patch that fixes it:

===== sql/sql_update.cc 1.61 vs edited =====
*** /tmp/sql_update.cc-1.61-15872 Thu Aug 8 03:23:03 2002
--- edited/sql/sql_update.cc Sat Oct 12 20:04:40 2002
***************
*** 768,774 ****
thd->proc_info="updating the reference tables";

/* Does updates for the last n - 1 tables, returns 0 if ok */
! int error = do_updates(false); /* do_updates returns 0 if success */

/* reset used flags */
#ifndef NOT_USED
--- 768,774 ----
thd->proc_info="updating the reference tables";

/* Does updates for the last n - 1 tables, returns 0 if ok */
! int error = (num_updated > 1) ? do_updates(false) : 0; /* do_updates returns 0 if success */

/* reset used flags */
#ifndef NOT_USED



--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ 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-thread12710@lists.mysql.com
To unsubscribe, e-mail