Hi there,

I'm moving a database from 3.23.49 to 5.0.20a and having problems with a
certain "CREATE TABLE":

CREATE TABLE shp_tbl_def (
id tinyint(3) unsigned NOT NULL auto_increment,
table_name varchar(255) NOT NULL default '',
field_name varchar(255) NOT NULL default '',
form_caption tinytext NOT NULL,
position tinyint(4) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY id_2 (id),
KEY table (table_name),
KEY field (field_name),
KEY position (position)
) TYPE=MyISAM;

If I rename "table" and "field" to "table2" and "field2" the table is
created, otherwise, with the above syntax I get

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 'table (table_name),
KEY field (field_name),
KEY position (position)
) TYPE=M' at line 10

This database is for a hosted site, so I don't know if the key names are
required, and I'm not really in a position to start renaming them.

The same error pops up when I try the insert on 3.23.49 and I got this
CREATE syntax from mysqldump, using '-c -a', '-c', '-a' and no switch at
all.

What can I do to troubleshoot this further?

Darek <darek@nowhere.nohow> wrote in news:fgflrf0205o@news1.newsguy.com:


> This database is for a hosted site, so I don't know if the key names
> are required, and I'm not really in a position to start renaming them.
>
> The same error pops up when I try the insert on 3.23.49 and I got this
> CREATE syntax from mysqldump, using '-c -a', '-c', '-a' and no switch
> at all.
>
> What can I do to troubleshoot this further?

Well, no point in troubleshooting it further because you know the problem:
MySQL has "Reserved Words" that your database is using. You'll have to
change your database.

http://dev.mysql.com/doc/refman/5.0/...ved-words.html

Good Man <heyho@letsgo.com> wrote in
news:Xns99DC933AB6A4Bsonicyouth@216.196.97.131:

> Darek <darek@nowhere.nohow> wrote in
> news:fgflrf0205o@news1.newsguy.com:
>
>
>> This database is for a hosted site, so I don't know if the key names
>> are required, and I'm not really in a position to start renaming
>> them.
>>
>> The same error pops up when I try the insert on 3.23.49 and I got
>> this CREATE syntax from mysqldump, using '-c -a', '-c', '-a' and no
>> switch at all.
>>
>> What can I do to troubleshoot this further?
>
> Well, no point in troubleshooting it further because you know the
> problem: MySQL has "Reserved Words" that your database is using.
> You'll have to change your database.
>
> http://dev.mysql.com/doc/refman/5.0/...ved-words.html

Actually according to that doc it suggests that you may use reserved
words if they are quoted properly:

mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'

mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)


http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

On Fri, 02 Nov 2007 13:30:07 -0500, Good Man wrote:
> Good Man <heyho@letsgo.com> wrote in
> news:Xns99DC933AB6A4Bsonicyouth@216.196.97.131:
>
>> Darek <darek@nowhere.nohow> wrote in
>> news:fgflrf0205o@news1.newsguy.com:
>>
>>
>>> This database is for a hosted site, so I don't know if the key names
>>> are required, and I'm not really in a position to start renaming
>>> them.
>>>
>>> The same error pops up when I try the insert on 3.23.49 and I got
>>> this CREATE syntax from mysqldump, using '-c -a', '-c', '-a' and no
>>> switch at all.
>>>
>>> What can I do to troubleshoot this further?
>>
>> Well, no point in troubleshooting it further because you know the
>> problem: MySQL has "Reserved Words" that your database is using.
>> You'll have to change your database.
>>
>> http://dev.mysql.com/doc/refman/5.0/...ved-words.html
>
> Actually according to that doc it suggests that you may use reserved
> words if they are quoted properly:

Note that "may" doesn't mean the practice is recommendable. Odds are
that doing so will bite the programmer eventually, somehow.

Been there, still have teethmarks.

--
28. My pet monster will be kept in a secure cage from which it cannot escape
and into which I could not accidentally stumble.
--Peter Anspach's list of things to do as an Evil Overlord

On Fri, 02 Nov 2007 14:34:36 -0500, Peter H. Coffin wrote:

> On Fri, 02 Nov 2007 13:30:07 -0500, Good Man wrote:
>> Good Man <heyho@letsgo.com> wrote in
>> news:Xns99DC933AB6A4Bsonicyouth@216.196.97.131:
>>
>>> http://dev.mysql.com/doc/refman/5.0/...ved-words.html
>>
>> Actually according to that doc it suggests that you may use reserved
>> words if they are quoted properly:
>
> Note that "may" doesn't mean the practice is recommendable. Odds are
> that doing so will bite the programmer eventually, somehow.
>
> Been there, still have teethmarks.

Heh. The developers are long gone, so no changes will likely be made to
the code. I'll just cross my fingers that inserts or updates don't cause
any major issues.

Thank you both for the info!