MySQL Bugs: #50883: If field name is "desc", MySQL fails without meaningful error message.

Bug #50883	If field name is "desc", MySQL fails without meaningful error message.
Submitted:	3 Feb 2010 14:55	Modified:	3 Feb 2010 15:18
Reporter:	Sasha Shepherd	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Linux (Shared Host)
Assigned to:		CPU Architecture:	Any
Tags:	error code, FATAL ERROR, syntax error

Description:
This one took me four hours to track down.

Granted, I'm a newbie, and an experienced developer would have probably caught it a lot faster. But it gave me great headache.

I had a database with a field called "desc" - short for description, possibly a common field name. I created it in PHPMyAdmin.

I have been tring to INSERT INTO quotes (desc) VALUES ($_POST['desc1'])

(of course, I was inserting 20 things at once, confusing matters).

The message was the generally unhelpful: - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version near ***

I don't know if this is already in the documentation; it is hard to search for a term like "desc" because it is used so frequently.

How to repeat:
1. Name a data item "desc"

2. Do an operation on that item. I have been doing INSERT INTO, but I assume it would work for any.

Also, please note the mysql_errno value: There is none!

Suggested fix:
Give a warning or fatal error when a table is created with a column named "desc", Give a more meaningful error message.

This is NOT a bug. DESC is a reserved word and must be quoted. Looks like PHPMyAdmin did a quting job for you when you had created that table. Look:

mysql> create table tdesc(desc int);
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 'desc int)' at line 1
mysql> create table tdesc(`desc` int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tdesc(desc) values(1);
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 'desc) values(1)' at line 1
mysql> insert into tdesc(`desc`) values(1);
Query OK, 1 row affected (0.01 sec)
 
Read http://dev.mysql.com/doc/refman/5.1/en/identifiers.html and http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html.