Bug #19463 Bug in executing SQL scripts that has a key named Group

• Submitted: 1 May 2006 18:48
• Modified: 1 May 2006 19:58
• Reporter: mertol ozyoney
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.x
• OS: Windows (windows)
• CPU Architecture: Any

[1 May 2006 18:48] mertol ozyoney

Description:
Hi;

The following SQL returns an error

CREATE TABLE users (
  UserId bigint(20) NOT NULL auto_increment,
  UserName varchar(255) NOT NULL default '',
  Password varchar(255) NOT NULL default '',
  Name varchar(255) NOT NULL default '',
  Surname varchar(255) NOT NULL default '',
  Email varchar(255) default NULL,
  Email2 varchar(255) default NULL,
  Mobile bigint(20) default NULL,
  Group varchar(255) NOT NULL default 'world',
  Comment varchar(255) default NULL,
  PRIMARY KEY  (UserId),
  UNIQUE KEY UserName (UserName),
  UNIQUE KEY UserName_2 (UserName),
  UNIQUE KEY UserId (UserId),
  UNIQUE KEY UserName_3 (UserName),
  KEY Name (Name)
) TYPE=MyISAM;

But if you change the name of the Group key to anything else it runs perfectly. I quess its about the 'group by' reserved key...

Mertol
best regards
 

How to repeat:
CREATE TABLE users (
  UserId bigint(20) NOT NULL auto_increment,
  UserName varchar(255) NOT NULL default '',
  Password varchar(255) NOT NULL default '',
  Name varchar(255) NOT NULL default '',
  Surname varchar(255) NOT NULL default '',
  Email varchar(255) default NULL,
  Email2 varchar(255) default NULL,
  Mobile bigint(20) default NULL,
  Group varchar(255) NOT NULL default 'world',
  Comment varchar(255) default NULL,
  PRIMARY KEY  (UserId),
  UNIQUE KEY UserName (UserName),
  UNIQUE KEY UserName_2 (UserName),
  UNIQUE KEY UserId (UserId),
  UNIQUE KEY UserName_3 (UserName),
  KEY Name (Name)
) TYPE=MyISAM;

[1 May 2006 19:58] MySQL Verification Team

Thank you for the bug report. Yes Group it is a reserved word:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

if you want to use that word you need to use it enclosed with
``:

mysql> CREATE TABLE users (
    ->   UserId bigint(20) NOT NULL auto_increment,
    ->   UserName varchar(255) NOT NULL default '',
    ->   Password varchar(255) NOT NULL default '',
    ->   Name varchar(255) NOT NULL default '',
    ->   Surname varchar(255) NOT NULL default '',
    ->   Email varchar(255) default NULL,
    ->   Email2 varchar(255) default NULL,
    ->   Mobile bigint(20) default NULL,
    ->   `Group` varchar(255) NOT NULL default 'world',
    ->   Comment varchar(255) default NULL,
    ->   PRIMARY KEY  (UserId),
    ->   UNIQUE KEY UserName (UserName),
    ->   UNIQUE KEY UserName_2 (UserName),
    ->   UNIQUE KEY UserId (UserId),
    ->   UNIQUE KEY UserName_3 (UserName),
    ->   KEY Name (Name)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql>