MySQL Bugs: #28940: Server crash when comparing a datetime field with a user function in a view

Bug #28940	Server crash when comparing a datetime field with a user function in a view
Submitted:	7 Jun 2007 8:42	Modified:	11 Jun 2007 11:00
Reporter:	Kim Carlsen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.37, 5.0.41, 5.1.16	OS:	Any (Linux, Freebsd and windows)
Assigned to:		CPU Architecture:	Any
Tags:	datetime, functions, Views

Description:
mysql crashes when comparing a datetime field with a user defined function in a view definition. 

If connecting with the mysql client to the server (without the -A option) the server will keep crashing no matter what query you feed it. (SELECT 1; is enough to make it crash). If you use the -A option on the mysql client no error is to be detected. 

How to repeat:
create database crashme
use crashme;

DELIMITER $$
CREATE FUNCTION somedatefunction() RETURNS datetime
BEGIN
  RETURN '2007-01-01';
END
$$
DELIMITER ;

create table crash_test (
  test_date datetime,
  primary key(test_date)
);

CREATE VIEW pv_crash_test as
SELECT test_date = somedatefunction()
  FROM crash_test;

Disconnect and reconnect the mysql client

mysql> select 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1

Suggested fix:
It seems strongly tied with the type of test_date, if it is changed to integer or any other type, there is no problem. Replacing somedatefunction with now() also produces no errors.

Thank you for the report.

But version 5.0.37 is a bit old. Please try with current 5.0.41 version and say us result.

Also you choose category "InnoDB", but didn't mentioned how InnoDB storage engine is correlated with the problem. Please, explain.

Just tested on debian with the following mysql versions
version 5.0.41, produced a crash.
version 5.1.16-beta-Debian_3, produced a crash

Innodb might be a wrong catogory to put this in as it also crashes with myisam, but I fail to understand completely what part of mysql causes the bug,
what category would be suitable?

Thank you for the feedback.

General "Server" category is OK.

You said bug is repeatable both on FreeBSD and Debian. So it looks like cross-platform bug. But case is not repeatable on Linux and Mac OS X with default settings. Please provide your congiuration file. Also indicate which binaries do you use on FreeBSD.

Please also indicate if your FreeBSD machine is 64-bit: I can not repeat the case on 32-bit FreeBSD too.

Debian my.cnf version 5.1.16-beta-Debian_3

Attachment: debian.my.cnf (application/octet-stream, text), 943 bytes.

Freebsd my.cnf version 5.0.37

Attachment: freebsd.my.cnf (application/octet-stream, text), 635 bytes.

Debian:
32 bit, experimental debian repository
Linux 2.6.12-rc1-CITI_NFS4_ALL-2 #1 Thu Mar 31 19:56:56 CEST 2005 i686 GNU/Linux
mysql-server-5.1  5.1.16beta-3   mysql database server binaries

Freebsd
32 bit, binaries taken from freebsd ports
mysql-server-5.0.37 Multithreaded SQL database (server)
FreeBSD 6.2-BETA3 FreeBSD 6.2-BETA3 #0: Mon Oct 30 22:15:05 UTC 2006     root@opus.cse.buffalo.edu:/usr/obj/usr/src/sys/SMP  i386

Note: You need to connect to the crashme database before running 'select 1;'

Thank you for the feedback.

I still can not repeat the problem with our binaries. Please try in your environment with our binaries accessible from http://dev.mysql.com/downloads/mysql/5.0.html#freebsd and say us result.

After talking to Kim, I managed to reproduce this bug on Gentoo Linux, MySQL v5.0.40.

dev-db/mysql-5.0.40  USE="berkdb ssl debug -big-tables -cluster -embedded -extraengine -latin1 -max-idx-128 -minimal (-selinux) -static"

Reproduced on: mysql-5.0.41-linux-i686-glibc23.tar.gz

=== Complete output after starting MySQL:

Server version: 5.0.41 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database crashme;
Query OK, 1 row affected (0.00 sec)

mysql> use crashme;
Database changed
mysql> DELIMITER $$
mysql> CREATE FUNCTION somedatefunction() RETURNS datetime
    -> BEGIN
    ->   RETURN '2007-01-01';
    -> END
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> create table crash_test (
    ->   test_date datetime,
    ->   primary key(test_date)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW pv_crash_test as
    -> SELECT test_date = somedatefunction()
    ->   FROM crash_test;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye

~/mysql-5.0.41-linux-i686-glibc23 $ mysql -h 127.0.0.1 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.41 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use crashme;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
Number of processes running now: 0
070607 15:12:25  mysqld restarted

mysql> select 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: crashme

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
ERROR: 
Can't connect to the server

mysql> 
Number of processes running now: 0
070607 15:12:29  mysqld restarted

Also produces crash on Windows. Changing OS to any.

It doesnt crash using the windows mysql client, since it doesnt do tab-completion of tablenames(AFAIK), but connecting from a unix machine causes the mysql server on windows to crash as well. 

I suspect it is the auto-rehash function that produces the server crash.

Thank you for the feedback.

Bug was repeatable with version 5.0.41 if running queries from mysql command line client, but wasn't if use MySQL testsuite. But bug is not repeatable with current development sources. So I close the report as "Can't repeat". Please wait next release.