"user" Incorrectly Treated As Mysql Reserved Keyword

[number_one]

The word “user” is being incorrectly identified as a MySQL reserved keyword, so it gets colored in blue and capitalized (depending on case preferences).

 

While there is a MySQL USER() function, there is no user keyword, so it should not be treated as such.

 

Just typing the word “user” (without quotes) will result in the word being treated as a keyword (by default this means it gets colored in blue and capitalized).  If you type “user(” (without quotes) then it gets correctly identified as a function (and thus colored in pink and capitalized).

 

The correct behavior would be to not treat the word user any different unless there is a parenthesis immediately adjacent (at which point it would detect it as a MySQL function).

 

Hopefully this has been clear enough.  I couldn’t find any reference to this issue already present in the forum.

[peterlaursen]

Let me clarify MySQL “reserved words” and “keywords”. The term “reserved keyword” is a mismatch of terms that has no place on Earth. 

 

“reserved words”  are listed here https://dev.mysql.com/doc/refman/5.6/en/reserved-words.html. A “reserved word” is reserved because the MySQL parser need them to parse the statement (and you need to `quote` them, should they be used as identifiers). 

 

“keywords” are words/tokens often used in SQL. If you check the link above you will see that neíther “CREATE”, “DROP”, “DELETE”, “INSERT” nor “UPDATE” are “reserved words”. Still they are “keywords” in the meaning that they are commonly used in SQL. SQL “keywords” are not strictly defined anywhere in MySQL documentation.  It is a ‘grey area’ what is a “keyword”, actually . 

 

That said: I don’t remember right now why we added USER to the keywords list in SQLyog. But I believe because in some type(s) of statement USER is a keyword. We will check. 

[number_one]

Thanks for looking into it.

 

In any case I don’t think “user” should be colored as a keyword because it is a very common name for a table and/or field.  In fact the default MySQL database has a “user” table.  There is certainly a user function, but that can be distinguised by the word being directly adjacent to a parenthesis.

[peterlaursen]

If you are right,  it would probably also apply to “HOST”.

[number_one]

Peter, I’m a bit confused.  The link you provided has all of those words you mentioned (“CREATE”, “DROP”, “DELETE”, “INSERT”, “UPDATE”) listed as MySQL reserved words.

 

But “user” (and “host” as you mentioned) are not in the list.

 

As an aside, it’s interesting to look back through the MySQL documentation history and see how many reserved words have been added as each new version is released.  You’d think that especially for some of the more specialized features they would have added some type of MySQL prefix to prevent such a large number of fairly common words becoming reserved.  I can imagine that it has caused a fair number of problems for users that have upgraded and are using schema files without everything being escaped.  For example, MySQL 5.7 adds “GENERATED”, “VIRTUAL”, and “STORED” among others as new reserved words.

[peterlaursen]

My mistake – I only checked the first column of the table.

 

But there are several tokens/words we treat as “keywords” that are not “reserved words”. USER is there because in statements “CREATE USER .. ” and “DROP USER .. ” for instance, “USER” is part of the SQL statement syntax. “reserved words” are words that the MySQL parser needs to be sure is part of statement syntax. This is the same in any programming langue: they all have a number of “reserved words” that the compiler|intepreter|parser *must* use. Without “reserved words” no computer language would never compile|interpret (and run).

 

Our “keywords” are stored in a file named “Keywords.db” – they are not ‘hard-coded’ in the program code. This file is located in the installation folder and is a SQLite database. You can see what is in there and you can add your own if you want and also remove those that you don’t want by editing the file in any SQLite client such as this one https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ for instance. Note that everytime you install an upgrade it will be overwritten (so if you edit the file, keep a backup).  

[[email protected]]

Let me clarify MySQL “reserved words” and “keywords”. The term “reserved keyword” is a mismatch of terms that has no place on Earth. 

 

 

Actually, the term “reserved keyword” used by the OP,  is correct.

 

It’s used in Transact-SQL terminology — including “USER”.

[peterlaursen]

The term “Transact-SQL” does not apply to MySQL. It is the MS SQL ‘SQL-dialect’. 

[Author]

Posts