[Lxr-dev] [ lxr-Bugs-1209273 ] "release" a reserved word in MySQL 5.x

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Bugs item #1209273, was opened at 2005-05-26 17:33
Message generated for change (Comment added) made by mbox
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=390117&aid=1209273&group_id=27350

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: Database interface
Group: None
>Status: Closed
>Resolution: Fixed
Priority: 5
Private: No
Submitted By: Jeff Warnica (jeffwarnica)
Assigned to: Malcolm Box (mbox)
Summary: "release" a reserved word in MySQL 5.x

Initial Comment:
And perhaps earlier versions too.

The table lxr_releases can not be created..

The MySQL docs claim that you can use reserved words as
identifiers if you quote them, but testing the CREATE
TABLE with "release" quoted also failed. 

----------------------------------------------------------------------

Comment By: Malcolm Box (mbox)
Date: 2009-03-23 12:27

Message:
This has now been fixed in CVS.

If you can install the new version and check that it solves your
problem, then it would be very useful.

Thanks for reporting this defect and helping to make LXR better.

----------------------------------------------------------------------

Comment By: Joshua Barr (stemcel)
Date: 2007-07-26 23:04

Message:
Logged In: YES 
user_id=1838421
Originator: NO

Summary says that CREATE TABLE with "release" quoted fails, but you need
to use back ticks (i.e. `release`) to quote the keyword. All instances of
"release" in initdb-mysql and all those that are not Perl scalars in
..../lib/LXR/Index/MySQL.pm need to be quoted or have their names changed.
"release" is not a reserved keyword in MySQL 4.x.

----------------------------------------------------------------------

Comment By: aaron (ipo23)
Date: 2007-05-13 01:44

Message:
Logged In: YES 
user_id=1619342
Originator: NO

same here - aaron@texas:/usr/share/doc/lxr-cvs/examples$ mysql --version
mysql  Ver 14.12 Distrib 5.0.38, for pc-linux-gnu (x86_64) using readline
5.2
aaron@texas:/usr/share/doc/lxr-cvs/examples$ 

release needs to be quoted with backquotes as a field definition as well
as in the primary key clause
then it works

----------------------------------------------------------------------

Comment By: Brian St. Pierre (bstpierre)
Date: 2006-10-06 18:40

Message:
Logged In: YES 
user_id=10357

Looking in CVS, it doesn't appear that the patch was applied to Mysql.pm.
(I do see the change in initdb-mysql.)

----------------------------------------------------------------------

Comment By: Danny (perunaion)
Date: 2006-09-16 03:20

Message:
Logged In: YES 
user_id=1047608

Release 1559658, attempts to address this issue.
Its a patch of the two modified files (Mysql.pm and
initdb-mysql).  Hopefully that will help fix this.

----------------------------------------------------------------------

Comment By: Nobody/Anonymous (nobody)
Date: 2006-07-14 00:59

Message:
Logged In: NO 

You need to change BOTH the initdb-mysql script and the
Mysql.pm module (8 occurrences total).  You can either
change the name of the 'release' field (which was the route
I took), or quote it with back-ticks.

----------------------------------------------------------------------

Comment By: Laurence Passmore (lmop)
Date: 2006-06-07 09:25

Message:
Logged In: YES 
user_id=1410237

I think you need to change lib/LXR/Index/Mysql.pm thus:

1) s/r.release/r.`release`/g

2) s/release =/`release` =/g

3) s/ release\)/ `release`\)/g

(I say think because I haven't tested it; I currently have other issues
with my LXR 
installation that is preventing me from getting it working at all...)

----------------------------------------------------------------------

Comment By: Malcolm Box (mbox)
Date: 2006-06-06 23:24

Message:
Logged In: YES 
user_id=215386

Apparently not a good fix - reopening

----------------------------------------------------------------------

Comment By: Malcolm Box (mbox)
Date: 2006-06-05 11:16

Message:
Logged In: YES 
user_id=215386

Now fixed in CVS. Thanks to lmop for the fix.

----------------------------------------------------------------------

Comment By: S Melody (prplehaze2)
Date: 2006-05-31 16:37

Message:
Logged In: YES 
user_id=911986

I used this fix to create the tables, but indexing seems to
fail, most likely because release is a reserved word:

DBD::mysql::st execute failed: 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 'release =
'head'' at line 1 at lib/LXR/Index/Mysql.pm line 209.
DBD::mysql::st execute failed: 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 'release)
values ('387', 'head')' at line 1 at lib/LXR/Index/Mysql.pm
line 213.

----------------------------------------------------------------------

Comment By: Laurence Passmore (lmop)
Date: 2005-12-21 17:20

Message:
Logged In: YES 
user_id=1410237

This is a simple fix. initdb-mysql should be changed as follows
(basically, 
backtick (`) the column name `release` to escape/quote it):

create table lxr_releases (
        fileid          int not null references lxr_files,
        `release`       char(255) binary not null,
        primary key     (fileid, `release`)
);

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=390117&aid=1209273&group_id=27350