mysql crash caused by =NULL rather than IS NULL?

Description:

We've just experienced a crash caused by the following query:

select backorder.* from backorder
left join tmp_backorder using(order_id, product_id, product_type)
where tmp_backorder.order_id=NULL;

Obviously, the writer of this query intended to use IS NULL,
and reported the problem after his first attempt above kept
crashing the mysql thread. It's a common enough mistake to
make, so I'm sure you'll want to investigate further.

Below is as much information as I could dig up, including
a reproducable test case.

Kind regards,

paul@classical.com



Environment:
============
mysql Ver 11.15 Distrib 3.23.47, for pc-linux-gnu (i686)



How-To-Repeat:
==============
CREATE TABLE `backorder` (
`order_id` char(32) NOT NULL default '',
`product_id` char(32) NOT NULL default '',
`product_type` int(11) NOT NULL default '0',
PRIMARY KEY (`order_id`,`product_id`,`product_type`)
) TYPE=MyISAM;


CREATE TABLE `tmp_backorder` (
`order_id` char(32) NOT NULL default '',
`product_id` char(32) NOT NULL default '',
`product_type` int(11) NOT NULL default '0',
PRIMARY KEY (`order_id`,`product_id`,`product_type`)
) TYPE=MyISAM;


INSERT INTO backorder (order_id, product_id, product_type) VALUES
('3d7ce39b5d4b3e3d22aaafe9b633de51',1206029, 3),
('3d7ce39b5d4b3e3d22aaafe9b633de51',5880836, 3),
('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);



INSERT INTO tmp_backorder (order_id, product_id, product_type) VALUES
('9d9aad7764b5b2c53004348ef8d34500',2315652, 3);


#this query will crash the mysql thread
select backorder.* from backorder
left join tmp_backorder using(order_id, product_id, product_type)
where tmp_backorder.order_id=NULL;


#the crash produces the follow report in the error log:

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
binary or one of the libraries it was linked agaist is corrupt,
improperly built, or misconfigured. This error can also be caused by
malfunctioning hardware. We will try our best to scrape up some info
that will hopefully help diagnose the problem, but since we have already
crashed, something is definitely wrong and this may fail

key_buffer_size=268431360
record_buffer=1044480
sort_buffer=1048568
max_used_connections=93
max_connections=500
threads_connected=90
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections =
1284136 K bytes of memory
Hope that's ok, if not, decrease some variables in the equation

Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Stack range sanity check OK, backtrace follows:
0x807ba8f
0x812c82a
0x80baf11
0x80bc7db
0x80bc416
0x80bb755
0x80bb3c4
0x809d9ab
0x8099758
0x80821c7
0x8086006
0x808153d
0x8080a0c
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x86884d0 = select backorder.* from backorder
left join tmp_backorder using(order_id, product_id, product_type)
where tmp_backorder.order_id=NULL
thd->thread_id=120

Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 120 did to cause the crash. In some cases of
really bad corruption, the values shown above may be invalid

The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash




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

Re: mysql crash caused by =NULL rather than IS NULL?

On Thursday 10 October 2002 00:39, Paul Dixon wrote:
> Description:
>=20
> We've just experienced a crash caused by the following query:
>=20
> select backorder.* from backorder
> left join tmp_backorder using(order_id, product_id, product_type)
> where tmp_backorder.order_id=3DNULL;
>=20
> Obviously, the writer of this query intended to use IS NULL,
> and reported the problem after his first attempt above kept
> crashing the mysql thread. It's a common enough mistake to
> make, so I'm sure you'll want to investigate further.
>=20
> Below is as much information as I could dig up, including
> a reproducable test case.

Thank you very much for such detailed bug report.
I've just checked it with recent BK Tree and it does not seems like it i=
s=20
still on the place. It looks as it was fixed in the meanwhile somewhere f=
rom=20
3.23.47, you have been using.

Could you please upgrade to the recent MySQL version and try again - it m=
ight=20
be something specific about your configuration which leads to the problem=




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

BUG: MyCC 0.8.4 incorrect row count after changing table type to INNODB

Hi,

I am using MYCC v.0.8.4 alpha and MySQL 4.0.4. I have several tables
that when I switch the table type from MyISAM to InnoDB MyCC returns an
incorrect number of records display. If I open a SQL window and execute
a 'Select count(*)' against any of the InnoDB tables I do get the
correct record count. Refreshing the display in MyCC has no effects.

Mark Armer


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

Re: BUG: MyCC 0.8.4 incorrect row count after changing table type to INNODB

For the mail filter:

How-To-Repeat:

----- Original Message -----
From: "Heikki Tuuri"
To:
Cc:
Sent: Friday, October 11, 2002 10:20 PM
Subject: Re: BUG: MyCC 0.8.4 incorrect row count after changing table type
to INNODB


> Mark,
>
> I guess MyCC uses SHOW TABLE STATUS to determine COUNT(*) of a table to
the
> MyCC display. For InnoDB it only gives an approximate value based on 10
> random dives into the table's clustered index tree.
>
> It is my plan to implement a fast COUNT(*) for InnoDB in 2002 or 2003.
Then
> we will get the accurate number instantaneously, just like for MyISAM type
> tables.
>
> Best regards,
>
> Heikki
> Innobase Oy
>
>
> Copied message:
> .......................
> Hi,
>
> I am using MYCC v.0.8.4 alpha and MySQL 4.0.4. I have several tables
> that when I switch the table type from MyISAM to InnoDB MyCC returns an
> incorrect number of records display. If I open a SQL window and execute
> a 'Select count(*)' against any of the InnoDB tables I do get the
> correct record count. Refreshing the display in MyCC has no effects.
>
> Mark Armer
>
>
>



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

Re: BUG: MyCC 0.8.4 incorrect row count after changing table typeto INNODB

Mark Armer wrote:

>Hi,
>
>I am using MYCC v.0.8.4 alpha and MySQL 4.0.4. I have several tables
>that when I switch the table type from MyISAM to InnoDB MyCC returns an
>incorrect number of records display. If I open a SQL window and execute
>a 'Select count(*)' against any of the InnoDB tables I do get the
>correct record count. Refreshing the display in MyCC has no effects.
>
>Mark Armer
>
>
>----------------------------------------------------------- ----------
>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-thread12699@lists.mysql.com
>To unsubscribe, e-mail
>
>
My guess is MyCC is using "SHOW TABLE STATUS" to get the information. If
this is so, this is a shortcoming (if you consider it a shortcoming) of
InnoDB, in that it only has an approximate row count of any table that
it stores, and uses when you issue "SHOW TABLE STATUS". MyISAM has an
internal row counter that is always maintained, so it always has a
correct row count.

For this reason, MyCC most likely does not do a count(*) for every table
in the database, because depending on the table handler, this could take
too long.

See http://www.mysql.com/doc/en/InnoDB_restrictions.html for more
information.

-Mark





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