MySQL Bugs: #1403: MySQLD Crash

Bug #1403	MySQLD Crash
Submitted:	25 Sep 2003 18:49	Modified:	2 Oct 2003 20:38
Reporter:	Matthew Yonkovit	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1	OS:	Linux (Redhat Linux 8)
Assigned to:		CPU Architecture:	Any

Description:
The following results in a mysqld crash:

SELECT (
max( max_val ) - max( min_val )
) * rand( ) + max( min_val )
FROM cost_to_maintain
WHERE age <= 19

MySQL said: 
#2013 - Lost connection to MySQL server during query

From the 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 against 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=134217728
read_buffer_size=4190208
sort_buffer_size=8388600
max_used_connections=2
max_connections=100
threads_connected=1

It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1359471 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x87c44c8
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...
Cannot determine thread, fp=0xbe5fed98, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x811d7e8
0x40051929
0x2
0x80d79c1
0x80d772d
0x80ca55b
0x8113405
0x8154969
0x81537e2
0x814ef89
0x814952b
0x8149c69
0x81470db
0x812a7b4
0x812eb59
0x81295af
0x812921a
0x8128b6d
0x4004bada
0x402978c7
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/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 0x87c9750 = select (max(max_val)-max(min_val))*rand()+max(min_val) from cost_to_maintain where age <= 19
thd->thread_id=10

Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 10 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.

Number of processes running now: 0
030925 21:35:54  mysqld restarted
/usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4.1.0-alpha-log'  socket: '/tmp/mysql.sock'  port: 3306
030925 21:35:55  InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 802659081
InnoDB: Doing recovery: scanned up to log sequence number 0 807901696
InnoDB: Doing recovery: scanned up to log sequence number 0 813144576
InnoDB: Doing recovery: scanned up to log sequence number 0 818387456
InnoDB: Doing recovery: scanned up to log sequence number 0 823630336
InnoDB: Doing recovery: scanned up to log sequence number 0 828873216
InnoDB: Doing recovery: scanned up to log sequence number 0 834116096
InnoDB: Doing recovery: scanned up to log sequence number 0 839358976
InnoDB: Doing recovery: scanned up to log sequence number 0 844601856
InnoDB: Doing recovery: scanned up to log sequence number 0 847186308
030925 21:35:57  InnoDB: Starting an apply batch of log records to the database...
.....

I compiled from the source, and using embeded functions if that matters.  I also get random curruption of the function table also.  I do not know if this is the cause.  I compiled with gcc 3.2.

on the curruption ... One function keeps on getting curropt.  Dropping the function with drop function X; results in an error, saying the function can not be found ( does not give the function name rather some random number of characters ).  The function will still exist in the func table.  If I shutdown the db and repiar and check all tables, the function will still not drop.  The only way I have found is to delete the record in the func table.  After doing this I can readd the function and the function works again.  in the error log, when these occur I get a "030923 11:16:02  Aborted connection 6266 to db: 'simgmbeta2' user: 'web' host: `localhost' (Got an error reading communication packets)"

How to repeat:
Every time I run the query: SELECT (
max( max_val ) - max( min_val )
) * rand( ) + max( min_val )
FROM cost_to_maintain
WHERE age <= 19  the DB crashes.

Forgot Kernel 2.4.18-14smp

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

I wasn't able to repeat this with current 4.1 from BK tree. Chance is this is some problem fixed since 4.1.0

Please provide some more information.

What is your table structure? Did you installed 4.1 from RPM or it is built from source?

I compiled directly from source using gcc 3.2. Using the following flags:
./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-rdynamic --datadir=/data/mysql --with-mysqld-use

Here is the table def:

CREATE TABLE `cost_to_maintain` (
  `age` int(2) NOT NULL default '0',
  `min_val` int(11) NOT NULL default '0',
  `max_val` int(11) NOT NULL default '0',
  PRIMARY KEY  (`age`)
) TYPE=InnoDB CHARSET=latin1;

INSERT INTO `cost_to_maintain` (`age`, `min_val`, `max_val`) VALUES (15, 0, 0);
INSERT INTO `cost_to_maintain` (`age`, `min_val`, `max_val`) VALUES (25, 1000, 7500);

Do you need my config file?

I posted this towards the bottom, maybe you missed it:

"I compiled from the source, and using embeded functions if that
matters.  I also get random curruption of the function table also.  I
do not know if this is the cause.  I compiled with gcc 3.2.

on the curruption ... One function keeps on getting curropt.  Dropping
the function with drop function X; results in an error, saying the
function can not be found ( does not give the function name rather some
random number of characters ).  The function will still exist in the
func table.  If I shutdown the db and repiar and check all tables, the
function will still not drop.  The only way I have found is to delete
the record in the func table.  After doing this I can readd the
function and the function works again.  in the error log, when these
occur I get a "030923 11:16:02  Aborted connection 6266 to db:
'simgmbeta2' user: 'web' host: `localhost' (Got an error reading
communication packets)"
How to repeat:
Every time I run the query: SELECT (
max( max_val ) - max( min_val )
) * rand( ) + max( min_val )
FROM cost_to_maintain
WHERE age <= 19  the DB crashes.  "

With your table I get result:

  30.014935890226 

Which seems to be Ok. Ofcourse this also means mysqld isn't crashing. 
This is either related to some problem already fixed in source tree or it may be caused by bad build. Being RedHat user for years I have serious concerns about RH 8.0 building environment. I was rarely able to see mysql-test succeeding when compiled mysql on RH 8.0

(Test above is on RedHat 9)

Please try our 4.1.0 RPMs and see if you will get same crash.

You are right I have missed this text about compilation and embedded functions :(

What do you mean by "embedded"? Do you mean UDF functions?

If you get corruption on mysql.func table this should be separate issue. Please test it with RPMs too. You will need Max version as Standard is statically linked and UDFs will not work.

4.1.1 seems to have fixed this problem for me.  I am still having the random currption with the UDF functions however.  Should I open a new bug?  

Here is the symptoms:
 SQL-query : 

DROP FUNCTION convertxp

MySQL said:

#1128 - Function '@yŒ	' is not defined

checking the table results in an ok.

CHECK TABLE `func`
Table 	           Op 	Msg_type 	Msg_text
mysql.func 	check 	status      	OK

select * from func returns convertxp as valid function.  

if I delete from func where name = 'convertxp', then recreate the function everything works again.  

As suggested I am downloading max right now.

When I go to the binaries the original problem comes back.  This time the server does not crash however just the connection.  In testing it, it only occurs when I take a random number and multiply it by the min/max of two other numbers.  

SELECT  rand()*(max(2)-max(1)) from mysql.user
 will crash the connections

select rand() *(max(2)) from mysql.user will not. 

As I stated before this seemed to go away with 4.1.1 ...  the random curroption of UDF function still reamins in both the binary and the source version of 4.1 and 4.1.1

I was able to work around the aggregate problem, by returning the data to my app and doing the math there.  But the function problem is most troublesome.  This was a function that had been working for the better part of 9 months in 4.X.  I recompiled everything with gcc 3.3 ( including our app which uses mysql++ ).  The function still goes curropt after a short time.  If I bring down Mysql and start it back up... the function comes back ( which is different then before ).  Is it possible something maybe getting curropted in memory?  and by downing the server that memory is cleared.

I did some more investigation on another server.  This server is running Redhat 7.3, I installed 4.1.0 binaries... ( also occurs on suse 8.2 compiled from source)

this fails on all of the servers:
mysql> select rand() * (max(2) - max(1)) from mysql.user;
ERROR 2013: Lost connection to MySQL server during query

This however works:
mysql> select rand(99) * (max(2) - max(1)) from mysql.user;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    132855
Current database: mysql

+------------------------------+
| rand(99) * (max(2) - max(1)) |
+------------------------------+
|          4.4852515862032e-06 |
+------------------------------+
1 row in set (0.02 sec)

I tested with a server built with 2 days older BK tree 4.1 and I wasn't able
to repeat the behavior reported. The last 2 queries you had reported:

Suse 8.2 Pro

mysql> select rand() * (max(2) - max(1)) from mysql.user;
+----------------------------+
| rand() * (max(2) - max(1)) |
+----------------------------+
|           0.75697656232582 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select rand(99) * (max(2) - max(1)) from mysql.user;
+------------------------------+
| rand(99) * (max(2) - max(1)) |
+------------------------------+
|              0.9233482386203 |
+------------------------------+
1 row in set (0.04 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 4.1.1-alpha-debug |
+-------------------+
1 row in set (0.02 sec)