mysql problem

[Lynn David Newton]

  >> ERROR 1064 at line 679: You have an error in your SQL syntax near 'field(field),
  gt> I recall having some kind of problem like this.
  gt> My solution was to put a "use" command at the top
  gt> of the mysql dump.

"use" has nothing to do with it. I had accounted for
the need to tell mysql which database to use.

As I learned from the mysql email list, the probelm was
that more recent versions of MySQL are more picky about
using reserved words as column names. One oughtn't use
the name "field" as a field name because it's an SQL
reserved word.

There is a kluge that allows one to get around it. Use
-Q (or --quote-names) as mysqldump options and it will
quote the names with backticks. Looks weird, but it
works.

Note that this is an undesireable solution. The name
"field" should never have been used in the first place.
However, I started working in early October for a
company that has a large legacy of data and Perl code
in which certain templates are used. We have fields
named 'field' in tables through our databases. It would
be a tedious, though doable task to fix them all. What
is *not* fixable as easily is the 150,000 lines or so
of Perl code that creates and manipulates this stuff,
or the data itself, in cases where we have columns
whose contents consist of lists of field names, some of
which may include "field". All that stuff would
instantly break for all our clients if we were to start
monkeying with it.

So although I may be annoyed with the programmer who
originally began using the reserved word "field" for
column names back when MySQL was not as picky about it,
at least I have a viable workaround for now.

I also added -Q to the call to mysqldump in my database
backup script.

Thank you to those who responded.

-- 
Lynn David Newton
Phoenix, AZ