MySQL Bugs: #15543: Selecting records from a view always causes a server crash

Bug #15543	Selecting records from a view always causes a server crash
Submitted:	7 Dec 2005 1:49	Modified:	30 Dec 2005 18:34
Reporter:	Michael Huang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.16	OS:	Windows (Windows)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
I have an `orders` table which has a `groupid` column.
Then I create an `ordergroups_view` view based on this table to represent grouping information dynamically.

When I select records from the view the very first time, it works and returns results. But when I reissue the select statement, the server crashes. The situation repeats every time.

How to repeat:
# Table "orders" DDL

CREATE TABLE `orders` (
  `id` int(11) NOT NULL auto_increment COMMENT 'auto-generated id',
  `submitdate` date default NULL COMMENT 'order submit date',
  `customerid` varchar(10) default NULL,
  `customerponumber` varchar(10) default NULL,
  `description` varchar(255) default NULL,
  `billto_name` varchar(50) default NULL,
  `billto_companyname` varchar(50) default NULL,
  `billto_addressline1` varchar(50) default NULL,
  `billto_addressline2` varchar(50) default NULL,
  `billto_city` varchar(20) default NULL,
  `billto_state` varchar(20) default NULL,
  `billto_zip` varchar(10) default NULL,
  `billto_phone` varchar(30) default NULL,
  `shipto_name` varchar(50) default NULL,
  `shipto_companyname` varchar(50) default NULL,
  `shipto_addressline1` varchar(50) default NULL,
  `shipto_addressline2` varchar(50) default NULL,
  `shipto_city` varchar(20) default NULL,
  `shipto_state` varchar(20) default NULL,
  `shipto_zip` varchar(10) default NULL,
  `shipto_phone` varchar(30) default NULL,
  `discountamount` decimal(10,2) default '0.00',
  `freightamount` decimal(10,2) default '0.00',
  `totalamount` decimal(10,2) default '0.00',
  `totaltax` decimal(10,2) default '0.00',
  `shippingmethod` tinyint(3) unsigned NOT NULL default '0',
  `paymentterms` tinyint(3) unsigned NOT NULL default '0',
  `freightterms` tinyint(3) unsigned NOT NULL default '0',
  `shippingdate` date NOT NULL default '0000-00-00',
  `shippingagent` varchar(50) default NULL,
  `invoicenumber` varchar(20) default NULL,
  `warehouseid` varchar(2) default NULL,
  `groupid` int(11) NOT NULL,
  `wf_state` tinyint(4) NOT NULL,
  `wf_current` int(11) NOT NULL,
  `wf_previous` int(11) NOT NULL,
  `wf_substate` tinyint(4) NOT NULL,
  `wf_subcurrent` int(11) NOT NULL,
  `wf_subprevious` int(11) NOT NULL,
  `wf_createdby` varchar(20) default NULL,
  `wf_createdon` date default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB

# View "ordergroups_view" DDL

CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`%` 
SQL SECURITY DEFINER VIEW `ordergroups_view` AS 
select `orders`.`groupid` AS `id`,`orders`.`wf_state` AS `wf_state` from `orders` group by `orders`.`groupid` 

# The select statement

SELECT * FROM ordergroups_view;

Thank you for a problem report. Sorry, but I was not able to repeat the problem you described (on Linux):

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.17    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `orders` (
    ->   `id` int(11) NOT NULL auto_increment COMMENT 'auto-generated id',
    ->   `submitdate` date default NULL COMMENT 'order submit date',
    ->   `customerid` varchar(10) default NULL,
    ->   `customerponumber` varchar(10) default NULL,
    ->   `description` varchar(255) default NULL,
    ->   `billto_name` varchar(50) default NULL,
    ->   `billto_companyname` varchar(50) default NULL,
    ->   `billto_addressline1` varchar(50) default NULL,
    ->   `billto_addressline2` varchar(50) default NULL,
    ->   `billto_city` varchar(20) default NULL,
    ->   `billto_state` varchar(20) default NULL,
    ->   `billto_zip` varchar(10) default NULL,
    ->   `billto_phone` varchar(30) default NULL,
    ->   `shipto_name` varchar(50) default NULL,
    ->   `shipto_companyname` varchar(50) default NULL,
    ->   `shipto_addressline1` varchar(50) default NULL,
    ->   `shipto_addressline2` varchar(50) default NULL,
    ->   `shipto_city` varchar(20) default NULL,
    ->   `shipto_state` varchar(20) default NULL,
    ->   `shipto_zip` varchar(10) default NULL,
    ->   `shipto_phone` varchar(30) default NULL,
    ->   `discountamount` decimal(10,2) default '0.00',
    ->   `freightamount` decimal(10,2) default '0.00',
    ->   `totalamount` decimal(10,2) default '0.00',
    ->   `totaltax` decimal(10,2) default '0.00',
    ->   `shippingmethod` tinyint(3) unsigned NOT NULL default '0',
    ->   `paymentterms` tinyint(3) unsigned NOT NULL default '0',
    ->   `freightterms` tinyint(3) unsigned NOT NULL default '0',
    ->   `shippingdate` date NOT NULL default '0000-00-00',
    ->   `shippingagent` varchar(50) default NULL,
    ->   `invoicenumber` varchar(20) default NULL,
    ->   `warehouseid` varchar(2) default NULL,
    ->   `groupid` int(11) NOT NULL,
    ->   `wf_state` tinyint(4) NOT NULL,
    ->   `wf_current` int(11) NOT NULL,
    ->   `wf_previous` int(11) NOT NULL,
    ->   `wf_substate` tinyint(4) NOT NULL,
    ->   `wf_subcurrent` int(11) NOT NULL,
    ->   `wf_subprevious` int(11) NOT NULL,
    ->   `wf_createdby` varchar(20) default NULL,
    ->   `wf_createdon` date default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`%`
    -> SQL SECURITY DEFINER VIEW `ordergroups_view` AS
    -> select `orders`.`groupid` AS `id`,`orders`.`wf_state` AS `wf_state` from
    -> `orders` group by `orders`.`groupid` ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-----------------------------------+
| Level | Code | Message                           |
+-------+------+-----------------------------------+
| Note  | 1449 | There is no 'root'@'%' registered |
+-------+------+-----------------------------------+
1 row in set (0.00 sec)

mysql> drop view `ordergroups_view`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost`  SQL SECURITY DEFINER VIEW `ordergroups_view` AS  select `orders`.`groupid` AS `id`,`orders`.`wf_state` AS `wf_state` from `orders` group by `orders`.`groupid`;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM ordergroups_view;
Empty set (0.00 sec)

I can try on 5.0.16 and Windows also, but, please, inform first, is it a fresh 5.0.16 installation or you perfromed an upgrade from 4.1.x?

Hi there

It was a fresh 5.0.15 installation and I later upgraded it to 5.0.16. The
tables were newly created. I have executed the same steps all over again
under 5.0.16 just now, and the problem repeats the same. Please have a try
on Window.

Cheers

Thank you for the suggestion. I was able to repeat the problem just as described on official 5.0.16-nt running on 64-bit XP:

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>mysql -uroot -p -P3307 test
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

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

mysql> CREATE TABLE `orders` (
    ->   `id` int(11) NOT NULL auto_increment COMMENT 'auto-generated id',
    ->   `submitdate` date default NULL COMMENT 'order submit date',
    ->   `customerid` varchar(10) default NULL,
    ->   `customerponumber` varchar(10) default NULL,
    ->   `description` varchar(255) default NULL,
    ->   `billto_name` varchar(50) default NULL,
    ->   `billto_companyname` varchar(50) default NULL,
    ->   `billto_addressline1` varchar(50) default NULL,
    ->   `billto_addressline2` varchar(50) default NULL,
    ->   `billto_city` varchar(20) default NULL,
    ->   `billto_state` varchar(20) default NULL,
    ->   `billto_zip` varchar(10) default NULL,
    ->   `billto_phone` varchar(30) default NULL,
    ->   `shipto_name` varchar(50) default NULL,
    ->   `shipto_companyname` varchar(50) default NULL,
    ->   `shipto_addressline1` varchar(50) default NULL,
    ->   `shipto_addressline2` varchar(50) default NULL,
    ->   `shipto_city` varchar(20) default NULL,
    ->   `shipto_state` varchar(20) default NULL,
    ->   `shipto_zip` varchar(10) default NULL,
    ->   `shipto_phone` varchar(30) default NULL,
    ->   `discountamount` decimal(10,2) default '0.00',
    ->   `freightamount` decimal(10,2) default '0.00',
    ->   `totalamount` decimal(10,2) default '0.00',
    ->   `totaltax` decimal(10,2) default '0.00',
    ->   `shippingmethod` tinyint(3) unsigned NOT NULL default '0',
    ->   `paymentterms` tinyint(3) unsigned NOT NULL default '0',
    ->   `freightterms` tinyint(3) unsigned NOT NULL default '0',
    ->   `shippingdate` date NOT NULL default '0000-00-00',
    ->   `shippingagent` varchar(50) default NULL,
    ->   `invoicenumber` varchar(20) default NULL,
    ->   `warehouseid` varchar(2) default NULL,
    ->   `groupid` int(11) NOT NULL,
    ->   `wf_state` tinyint(4) NOT NULL,
    ->   `wf_current` int(11) NOT NULL,
    ->   `wf_previous` int(11) NOT NULL,
    ->   `wf_substate` tinyint(4) NOT NULL,
    ->   `wf_subcurrent` int(11) NOT NULL,
    ->   `wf_subprevious` int(11) NOT NULL,
    ->   `wf_createdby` varchar(20) default NULL,
    ->   `wf_createdon` date default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB
    -> ;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost`
    -> SQL SECURITY DEFINER VIEW `ordergroups_view` AS
    -> select `orders`.`groupid` AS `id`,`orders`.`wf_state` AS `wf_state` from
    -> `orders` group by `orders`.`groupid` ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM ordergroups_view;
Empty set (0.05 sec)

mysql> SELECT * FROM ordergroups_view;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
ERROR:
Can't connect to the server

So, it is a bug in 5.0.16 (not repeatable on later versions), and, possibly, Windows-only. Please, wait for 5.0.17 (to be released soon).

I have the same issue on Linux using 5.0.16.

Bug report http://bugs.mysql.com/bug.php?id=15715 was marked as a duplicate of this one.

I can't repeat it on Linux SuSE 10.0, MySQL 5.0-bk (.19)

Reggie reports that this bug can't be repeated against 5.0.18-windows (32 bit).
Is it 64-bit Windows specific? Is it 64 bit specific?
Could you please verify it against the latest 5.0 on a 64 bit Windows platform?

An update: can't repeat it against 64 bit Windows as well. Please reopen the bug report if it's still repeatable in your environment when the latest version of MySQL server is used.