Bug #38274 String "view" in beginning of COMMENT in CREATE TABLE makes a broken view table

• Submitted: 22 Jul 2008 0:19
• Modified: 22 Jul 2008 3:28
• Reporter: Pekka Saarinen
• Status: Can't repeat
• Category: MySQL Server: Views
• Severity: S2 (Serious)
• Version: 5.0.X
• OS: Any
• CPU Architecture: Any

[22 Jul 2008 0:19] Pekka Saarinen

Description:
String "view" (without quotes) in the very beginning of COMMENT string in CREATE TABLE is interpreted as "make a view" by mysql.

This was tested mysql 5.0.51b-community-nt that came with latest WAMP-server in Vista 64. Also happens with rpm 5.0.45-community-log in Linux (64 bit RHEL 5)

How to repeat:
CREATE TABLE `sometable1` (
  `somerow` int(11) NOT NULL auto_increment,
  `otherrow` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`somerow`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='blah viewblah blah blah';

creates a table.

CREATE TABLE `sometable2` (
  `somerow` int(11) NOT NULL auto_increment,
  `otherrow` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`somerow`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='viewblah blah blah blah';

creates a view (broken view).

Suggested fix:
Programming :)

[22 Jul 2008 3:28] Valeriy Kravchuk

Sorry, but I can not repeat the behaviour described with newer versions:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.62-enterprise-gpl-nt MySQL Enterprise Server (GPL)

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

mysql> CREATE TABLE `sometable2` (
    ->   `somerow` int(11) NOT NULL auto_increment,
    ->   `otherrow` varchar(255) NOT NULL default '',
    ->   PRIMARY KEY  (`somerow`)
    -> ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='viewblah blah blah blah'
;
Query OK, 0 rows affected (0.08 sec)

mysql> show create table sometable2\G
*************************** 1. row ***************************
       Table: sometable2
Create Table: CREATE TABLE `sometable2` (
  `somerow` int(11) NOT NULL auto_increment,
  `otherrow` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`somerow`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='viewblah blah blah blah'
1 row in set (0.00 sec)