Bug #297 user table in mysql database recognized as reserved word when run in ansi mode
Submitted: 16 Apr 2003 6:28 Modified: 16 May 2003 2:29
Reporter: Jared Reeve Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:Microsoft Windows (Windows 2000)
Assigned to: CPU Architecture:Any

[16 Apr 2003 6:28] Jared Reeve
Description:
If you start the server in ansi mode you are unable to insert values into the user table without adding the database name as the prefix.  I believe this is because in ansi mode all function names are treated as reserved words.  And since USER() is a function, the USER table is being treated as a reserved word, not a table.

How to repeat:
1) Start the server in ansi mode.
2) The following insert will fail.
   - "INSERT INTO USER (HOST, USER, PASSWORD) VALUES ('localhost', 'user', 'password');"
3) A workaround is to add the database name as the prefix.
   - "INSERT INTO MYSQL.USER (HOST, USER, PASSWORD) VALUES ('localhost', 'user', 'password');

Suggested fix:
Could rename the USER table to something that is not already a function.
[16 Apr 2003 6:56] Sinisa Milivojevic
We will never change that as USER table is not supposed to be used directly. 

Instead GRANT / REVOKE and other commands should be used.
[16 May 2003 2:29] Michael Widenius
Jared, as you yourself conclude the probelm is that in ansi mode 'user' is a reserved word.

The way to go around this is to quote the user column name:

INSERT INTO "USER" ("HOST", "USER", "PASSWORD") VALUES ....

I used the ANSI quoting style above, you can of course also use ` here.