MySQL Syntax Error "right syntax to use near 'desc"

Question

I'm working in Python and using the MySQLdb module. I have a working connection (I can run other queries successfully)

c.execute("ALTER TABLE results ADD COLUMN desc TEXT")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.7/MySQLdb/connections.py", line 35,
    in defaulterrorhandler
    raise errorclass, errorvalue

I'm getting the following error:

_mysql_exceptions.ProgrammingError: (1064, "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 TEXT' at line 1")

I've had similar trouble before; MySQLdb's syntax error messages are terribly non-descriptive.

How can this be fixed?

Step 1. Compare your syntax against this: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html. The error message is not supposed to be descriptive. — S.Lott, Nov 28 '11 at 23:25
I *think* the error message comes from the SQL engine itself, not MySQLdb. — mtrw, Nov 28 '11 at 23:30

score 5 · Accepted Answer · answered Nov 28 '11 at 23:25

5

I believe desc is reserved. It is used in an ORDER BY clause

You may be able to get away with using it if you put back-ticks around it, but I think you would be better off changing the name to a non-reserved word.

answered Nov 28 '11 at 23:25

Adam Wenger

17,100
6
52
63

This is it. `desc` is the mysql keyword for descending order. – Jarek Nov 28 '11 at 23:26
http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html – S.Lott Nov 28 '11 at 23:29
Second time I read it, desc hmmmm. – Tony Hopkinson Nov 28 '11 at 23:35

score 0 · Answer 2 · edited May 23 '17 at 11:55

desc is a reserved keyword in MySQL - documented here: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

If you insist on using desc you should escape it using the back tick character (`) - as described here: How do I escape reserved words used as column names? MySQL/Create Table

I'll recommend you to change the desc column name to e.g. description, as I believe it is best practice never to include reserved keywords in the db schema.

MySQL Syntax Error "right syntax to use near 'desc"

2 Answers2

Linked

Related