MySQL Bugs: #46798: SHOW CREATE VIEW causes segmentation fault

Bug #46798	SHOW CREATE VIEW causes segmentation fault
Submitted:	18 Aug 2009 22:36	Modified:	25 Jun 2011 8:33
Reporter:	Chris Hennick	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.31-1ubuntu2	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
On my database, the following all caused error messages.

mysql> show create view max_rarity_histogram;                                                                     
ERROR 2013 (HY000): Lost connection to MySQL server during query                                                  
ERROR 2006 (HY000): MySQL server has gone away                                                                    
No connection. Trying to reconnect...                                                                             
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)        
ERROR:                                                                                                            
Can't connect to the server                                                                                       

ERROR 2006 (HY000): MySQL server has gone away
Segmentation fault

mysql> show create view min_rarity_histogram;                                                                     
ERROR 2013 (HY000): Lost connection to MySQL server during query                                                  
ERROR 2006 (HY000): MySQL server has gone away                                                                    
No connection. Trying to reconnect...                                                                             
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)        
ERROR:                                                                                                            
Can't connect to the server                                                                                       

ERROR 2006 (HY000): MySQL server has gone away
Segmentation fault

$ mysqldump -p -u root mtg -f --tables max_rarity_histogram min_rarity_histogram                     
Enter password:                                                                                                   
-- MySQL dump 10.13  Distrib 5.1.31, for debian-linux-gnu (i486)                                                  
--                                                                                                                
-- Host: localhost    Database: mtg                                                                               
-- ------------------------------------------------------                                                         
-- Server version       5.1.31-1ubuntu2                                                                           

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;  
/*!40101 SET NAMES utf8 */;                                        
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;                        
/*!40103 SET TIME_ZONE='+00:00' */;                                
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;        
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;                           
mysqldump: Got error: 1356: View 'mtg.max_rarity_histogram' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when doing LOCK TABLES                                       
mysqldump: Couldn't execute 'show create table `max_rarity_histogram`': Lost connection to MySQL server during query (2013)                                                                                                         
mysqldump: Couldn't execute 'show table status like 'min\_rarity\_histogram'': MySQL server has gone away (2006)  
mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': MySQL server has gone away (2006)               
mysqldump: Couldn't execute 'show fields from `min_rarity_histogram`': MySQL server has gone away (2006)          
mysqldump: Couldn't execute 'UNLOCK TABLES': MySQL server has gone away (2006)                                    
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;                                                                         

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;          
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;  
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

How to repeat:
Unknown. However, the files for the views are the following:

root@mtgdb:/var/lib/mysql/mtg# cat max_rarity_histogram.frm
TYPE=VIEW
query=(select `desire`.`max_rarity` AS `rarity`,`desire`.`lacking_copies` AS `lacking_copies`,count(0) AS `count_max` from `mtg`.`desire` group by `desire`.`max_rarity`,`desire`.`lacking_copies`)
md5=2a7d7f62476331c3409deb69600370dd
updatable=0
algorithm=0
definer_user=root
definer_host=localhost
suid=2
with_check_option=0
timestamp=2009-08-04 06:07:57
create-version=1
source=(select max_rarity as rarity, lacking_copies, count(*) as count_max from desire group by max_rarity, lacking_copies)
root@mtgdb:/var/lib/mysql/mtg# cat min_rarity_histogram.frm
TYPE=VIEW
query=(select `desire`.`min_rarity` AS `rarity`,`desire`.`lacking_copies` AS `lacking_copies`,count(0) AS `count_min` from `mtg`.`desire` group by `desire`.`min_rarity`,`desire`.`lacking_copies`)
md5=19da438e3b1bb6cbd600cbd82d500099
updatable=0
algorithm=0
definer_user=root
definer_host=localhost
suid=2
with_check_option=0
timestamp=2009-08-04 06:08:29
create-version=1
source=(select min_rarity as rarity, lacking_copies, count(*) as count_min from desire group by min_rarity, lacking_copies)

NB: Attempting to select from these views produces an error (as it should, since the view "desire" no longer exists), but doesn't crash the server.

Thank you for the bug report. Could you please upgrade to latest 5.1.XX release and verify if the issue still exists. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I was experiencing a similar issue under mysql 5.1.32 under OS X:

http://gist.github.com/214362

As can be seen from the output, only one of the view definitions is causing mysql to crash.  This seems to be happening consistently.

After upgrading to the latest point release (mysql  Ver 14.14 Distrib 5.1.39, for apple-darwin9.5.0 (i386) using readline 5.1), restarting the server, and reimporting the database from a mysql dump, this error no longer seems to be occurring.

Thanks for the bug fix.

Unfortunately I found the bug again in version 5.5.8.

mysql> SHOW CREATE VIEW `sakila`.`actor_info`;
ERROR 2013 (HY000): Lost connection to MySQL server during query

And the same problem occurred when SHOW CREATE TABLE command, which, according to the specs should work for views as well, is executed for views.

mysql> SHOW CREATE TABLE  `sakila`.`actor_info`;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Version - 5.5.8
OS - Windows 7

Madhura, please upload the mysql error log so we can see the stack trace of the crash. also send us the "actor_info.frm" file.  Thanks!

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".