MySQL Bugs: #12204: "connection" keyword is reserved and cannot be used as a field name

Bug #12204	"connection" keyword is reserved and cannot be used as a field name
Submitted:	27 Jul 2005 11:59	Modified:	6 Mar 2007 19:38
Reporter:	Wongy Ygnow	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (Linux, Windows XP)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
In MySQL Server 5.0, the keyword "connection" seems reserved, and cannot be used as a field name. But a workaround is possible (see below).

This is not an issue in the previous version of MySQL Server (4.1).

How to repeat:
Create a database (dbtest). Create a table in this database (tbltest). Name a field in this table as "connection".

Connect to the database and execute the following SQL query:
SELECT connection FROM tbltest;

This will generate the following error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'connection from tbltest' at line 1

Suggested fix:
Either never use the keyword "connection" to name fields, or put the word between ` ` in a query.

i.e.: SELECT `connection` FROM tbltest;

this will work...

The ` ` are not needed with other field names.

In general, since we do not know which word is reserved or not (at least I do not know), it is advised to put everytime the ` `. We never know...

Hi! 

Absolutely correct. Connection is reserverd word. it's described in
http://dev.mysql.com/doc/mysql/en/reserved-words.html

I was exactly looking for such information! Thanx for that.

Nevertheless, it is not explicitely mentioned that those words are reserved as from 5.0, since for 4.1 the word "connection" is not reserved, f.i.

Antony, could you check why CONNECTION should be reserved (and "un-reserve" it if it should not) ?

As far as I can see it need not to be reserved.

From the documents that I have readily at hand, CONNECTION is actually defined as a reserved word in the SQL99 specification. Fortunately, it is defined as a non-reserved word in the SQL03 specification.

There are several tokens we define as reserved keywords which SQL03 defines as non-reserved and vice versa.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19147

ChangeSet@1.2390, 2007-01-31 17:00:43-08:00, antony@ppcg5.local +3 -0
  Bug#12204
    "CONNECTION is treated as a reserved keyword"
    Allow CONNECTION to be an unreserved keyword.
    Includes test.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19522

ChangeSet@1.2405, 2007-02-07 14:22:19-08:00, antony@ppcg5.local +3 -0
  Bug#12204
    "CONNECTION is a reserved keyword"
    Allow connection to be used as an ordinary identifier
    Tests included.

Pushed to 5.1.16 and 5.0.36.

Noted in 5.0.36, 5.1.16 changelogs.

CONNECTION is no longer treated as a reserved word.

It is still in 5.0.36 package.

Also 5.2 branch is affected.

See also Bug #26787

The fix did not get into 5.0.36, but in 5.0.38. The changelog needs to be updated.

Moved 5.0.36 changelog entry to 5.0.38.