| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | Windows (Windows 2000) |
| Assigned to: | CPU Architecture: | Any | |
[16 Apr 2003 6:56]
MySQL Verification Team
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.
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.