mysql jump and stay to 100% cpu

------=_NextPart_000_0005_01C348DA.B0A24200
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi all,


i have accidently discover a bug on mysql, i have crash my web server, =
it paralysed all services until we kill the process...
after a request mysql jump and stay to 100% cpu.

it seems that mysqld crash when he receive a request that contain a =
simple jointure (from table1 t1, table2 t2) and a right outer join just =
after.

for example this request works correctly :

select u.username
from whosonline w, users u
where u.user_id =3D w.user_id

and this crash the server :

select u.username
from whosonline w, users u
right outer join users u2 on u2.user_id =3D w.user_id
where u.user_id =3D w.user_id

username varchar(50)
all user_id int

it "works" on linux 4.0.12, mysql-4.0.13-win and mysql-3.23.38-win, i =
think all versions are affected :/

i hope it's help you *

friendly
------=_NextPart_000_0005_01C348DA.B0A24200--

Re: mysql jump and stay to 100% cpu

alcane writes:
> Hi all,
>
>
> i have accidently discover a bug on mysql, i have crash my web server, it paralysed all services until we kill the process...
> after a request mysql jump and stay to 100% cpu.
>
> it seems that mysqld crash when he receive a request that contain a simple jointure (from table1 t1, table2 t2) and a right outer join just after.
>
> for example this request works correctly :
>
> select u.username
> from whosonline w, users u
> where u.user_id = w.user_id
>
> and this crash the server :
>
> select u.username
> from whosonline w, users u
> right outer join users u2 on u2.user_id = w.user_id
> where u.user_id = w.user_id
>
> username varchar(50)
> all user_id int
>
> it "works" on linux 4.0.12, mysql-4.0.13-win and mysql-3.23.38-win, i think all versions are affected :/
>
> i hope it's help you *
>
> friendly

Hi!

I have tested your query with tables I have and it worked just fine.

Can you upload your whosonline and users tables (tarred and gzipped)
to :

ftp://support.mysql.com:/pub/mysql/secret

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysql jump and stay to 100% cpu

Hi,

the tables structure is uploaded as alcane.sql.
the request in my previous mail is just an example, when i discovered the
bug i was working on different table
the original request was that :

SELECT u.username
FROM topics t, forums f
RIGHT OUTER JOIN users u ON u.user_id = t.topic_poster
WHERE f.forum_id = t.forum_id
limit 0, 10

with some other field in the select, but just this crash the server too.

I have create a new table "fake_user" with just 2 field, user_id int,
username varchar(50) and user_id as primary key auto_increment like the
original, but with this one the request don't crash
the probleme seems come from my table "users" but not from the box, cause
the probleme can be reproduce on others.


after more test, it seems finally that the server don't crash, but it takes
very long time to make the request, for the request just under, mysql take 1
sec to return one row, and the time climb exponentially for more rows

select u.username
from whosonline w, users u
right join users u2 on u2.user_id = w.user_id
where u.user_id = w.user_id
limit 1

with "limit 2" and the keyword "right" it takes 2.7 sec, and with the
keyword "left" instead of "right" the time is lesser than 1 millisecond.
with "limit 3" i can't see the result after 5 minutes... and with "left" the
time is lesser than 1 millisecond.
the processor is an Athlon 2000+

i have about 4000 rows in my "users" table and it crash, after a clear, with
just 3 rows in the table the server don't crash, and this request takes
0.1334 sec instead of lots and lots more...

select u.username
from whosonline w, users u
right join users u2 on u2.user_id = w.user_id
where u.user_id = w.user_id
limit 3;


so it's more an optimization probleme than a crash, or maybe i don't use the
keyword "right" as it would be...


i hope it's help you

Friendly,
Alcane

ps: to all, i'm sorry for my poor english :P

----- Original Message -----
From: "Sinisa Milivojevic"
To:
Cc:
Sent: Monday, July 14, 2003 1:09 PM
Subject: Re: mysql jump and stay to 100% cpu


> alcane writes:
> > Hi all,
> >
> >
> > i have accidently discover a bug on mysql, i have crash my web server,
it paralysed all services until we kill the process...
> > after a request mysql jump and stay to 100% cpu.
> >
> > it seems that mysqld crash when he receive a request that contain a
simple jointure (from table1 t1, table2 t2) and a right outer join just
after.
> >
> > for example this request works correctly :
> >
> > select u.username
> > from whosonline w, users u
> > where u.user_id = w.user_id
> >
> > and this crash the server :
> >
> > select u.username
> > from whosonline w, users u
> > right outer join users u2 on u2.user_id = w.user_id
> > where u.user_id = w.user_id
> >
> > username varchar(50)
> > all user_id int
> >
> > it "works" on linux 4.0.12, mysql-4.0.13-win and mysql-3.23.38-win, i
think all versions are affected :/
> >
> > i hope it's help you *
> >
> > friendly
>
> Hi!
>
> I have tested your query with tables I have and it worked just fine.
>
> Can you upload your whosonline and users tables (tarred and gzipped)
> to :
>
> ftp://support.mysql.com:/pub/mysql/secret
>
> --
>
> Regards,
>
> --
> For technical support contracts, go to https://order.mysql.com/?ref=msmi
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB
> /_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
> <___/ www.mysql.com Larnaca, Cyprus
>
>


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysql jump and stay to 100% cpu

alcane writes:
> Hi,
>
> the tables structure is uploaded as alcane.sql.
> the request in my previous mail is just an example, when i discovered the
> bug i was working on different table
> the original request was that :
>
> SELECT u.username
> FROM topics t, forums f
> RIGHT OUTER JOIN users u ON u.user_id = t.topic_poster
> WHERE f.forum_id = t.forum_id
> limit 0, 10
>
> with some other field in the select, but just this crash the server too.
>
> I have create a new table "fake_user" with just 2 field, user_id int,
> username varchar(50) and user_id as primary key auto_increment like the
> original, but with this one the request don't crash
> the probleme seems come from my table "users" but not from the box, cause
> the probleme can be reproduce on others.
>
>
> after more test, it seems finally that the server don't crash, but it takes
> very long time to make the request, for the request just under, mysql take 1
> sec to return one row, and the time climb exponentially for more rows
>
> select u.username
> from whosonline w, users u
> right join users u2 on u2.user_id = w.user_id
> where u.user_id = w.user_id
> limit 1
>
> with "limit 2" and the keyword "right" it takes 2.7 sec, and with the
> keyword "left" instead of "right" the time is lesser than 1 millisecond.
> with "limit 3" i can't see the result after 5 minutes... and with "left" the
> time is lesser than 1 millisecond.
> the processor is an Athlon 2000+
>
> i have about 4000 rows in my "users" table and it crash, after a clear, with
> just 3 rows in the table the server don't crash, and this request takes
> 0.1334 sec instead of lots and lots more...
>
> select u.username
> from whosonline w, users u
> right join users u2 on u2.user_id = w.user_id
> where u.user_id = w.user_id
> limit 3;
>
>
> so it's more an optimization probleme than a crash, or maybe i don't use the
> keyword "right" as it would be...
>
>
> i hope it's help you
>
> Friendly,
> Alcane
>
> ps: to all, i'm sorry for my poor english :P
>

Hi!

I tested your case and it worked just fine with 4.0.14:

mysql> select u.username from whosonline w, users u right outer join users u2 on u2.user_id = w.user_id where u.user_id = w.user_id;
Empty set (0.07 sec)

mysql> quit
Bye
[/mnt/work/mysql-4.0]$ mysqladmin shutdown


--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org