MySQL Bugs: #15396: Views : Server crashes after 2nd execution of view containing Count()

Bug #15396	Views : Server crashes after 2nd execution of view containing Count()
Submitted:	1 Dec 2005 17:20	Modified:	12 Dec 2005 12:31
Reporter:	Adam Burrett	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.16	OS:	Windows (windows 2003 Server)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
When using a View with a Count() function, the server crashes after the 2nd attempt to run.

The initial attempt executes fine.

How to repeat:
Create a view with any count() function supported by a group by function and on second execution it will crash.

Thank you for the bug report, but you provide not enough info to reproduce this bug.
Please provide table and view definitions (output of SHOW CREATE TABLE and SHOW CREATE VIEW).

Output as requested : 

SHOW CREATE TABLE:

CREATE TABLE `status_code` (
  `STATUS_ID` int(11) NOT NULL,
  `STATUS_DESC` varchar(50) default NULL,
  `STATUS_WIP` bit(1) NOT NULL default '\0',
  `STATUS_GOOD` bit(1) NOT NULL default '\0',
  PRIMARY KEY  (`STATUS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SHOW CREATE VIEW :
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `modelcount` AS select count(`status_code`.`STATUS_ID`) AS `count(status_id)` from `status_code` where (`status_code`.`STATUS_WIP` = 1)

I was not able to reproduce the bug on Windows 2003 server and 5.0.16. I executed "select * from modelcount" several times without any problem.

I have similar problems like Adam with views. 
I am also running 5.0.16 on windows 2003 server.

I tried Adams setup, and it crashes mysql.

Faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0, fault address 0x000abae5.

I wasn't able reproduce it too.

Adam, can you upload my.cnf/my.ini file?
How big is status_code table?

I had one or two rows in the status table when I tested.

my.ini

[client]
port		= 3306
socket		= /tmp/mysql.sock

[mysqld]
basedir=C:/mysql/
datadir=D:/Databasserver/data/
port		= 3306
socket		= /tmp/mysql.sock
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
log-bin=mysql-bin
server-id	= 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

C:\mysql5016>bin\mysql --defaults-file=c:\mysql5016\s5016.ini -uroot -p --prompt="win5016>"
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nt-max

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

win5016>use test
Database changed
win5016>CREATE TABLE `status_code` (
    ->   `STATUS_ID` int(11) NOT NULL,
 <cut>

win5016>CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
    -> `modelcount` AS select count(`status_code`.`STATUS_ID`) AS `count(status_id)`
    -> from `status_code` where (`status_code`.`STATUS_WIP` = 1);
Query OK, 0 rows affected (0.05 sec)

win5016>select * from modelcount;
+------------------+
| count(status_id) |
+------------------+
|                0 |
+------------------+
1 row in set (0.03 sec)

win5016>select * from modelcount;
ERROR 2013 (HY000): Lost connection to MySQL server during query
win5016>

C:\mysql5017>bin\mysql --defaults-file=c:\mysql5017\s5017.ini -uroot -p --prompt="win5017>"
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-nt-max

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

win5017>use test
Database changed
win5017>CREATE TABLE `status_code` (
    ->   `STATUS_ID` int(11) NOT NULL,
<cut>

win5017>select * from modelcount;
+------------------+
| count(status_id) |
+------------------+
|                0 |
+------------------+
1 row in set (0.02 sec)

win5017>select * from modelcount;
ERROR 2013 (HY000): Lost connection to MySQL server during query

C:\mysql5017>bin\mysql --defaults-file=c:\mysql5017\s5017.ini -uroot -p --prompt="win5018>"
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.18-nt

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

win5018>use test
Database changed
win5018>select * from modelcount;
+------------------+
| count(status_id) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

win5018>select * from modelcount;
+------------------+
| count(status_id) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

win5018>select * from modelcount;
+------------------+
| count(status_id) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

win5018>select * from modelcount;
+------------------+
| count(status_id) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

win5018>show variables like "%query_cache%";
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

win5018>