[Koha-devel] MySQL 5.0 problem

[Thomas D]

I was hoping that someone who has more experience with MySQL would have
given a proper answer for this question already.  Maybe no one else had
noticed the issue.  The issue has now become manifest on the koha listserv
discussion.  I had not expected that this would really be a problem but it
may well be a nuisance to change the code to ensure proper usage.  Quoting
is much preferable to changing well chosen column names.  I hope the quoting
people do is ANSI compliant.  Koha 3.0 should be ensuring support for the
use of other databases such as PostgreSQL and not accidentally introducing a
dependency on any particular database software.  A routine should check a
database flavour parameter for flavour specific syntax but default to
ANSI/ISO SQL-92 unless a later ANSI/ISO SQL standard must be required and
cannot otherwise be implemented.

>From the official reference.  MySQL AB. Database, table, index, column, and
alias names. In MySQL 5.0 reference manual. - 2005. - [Section] 9.2.
http://dev.mysql.com/doc/refman/5.0/en/legal-names.html .

"An identifier may be quoted or unquoted. If an identifier is a reserved
word or contains special characters, you must quote it whenever you refer to
it. For a list of reserved words, see Section 9.6, “Treatment of Reserved
Words in MySQL”. Special characters are those outside the set of
alphanumeric characters from the current character set, ‘_’, and ‘$’.

The identifier quote character is the backtick (‘`’):

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

If the server SQL mode includes the ANSI_QUOTES mode option, it is also
allowable to quote identifiers with double quotes:

mysql> CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax. (...)
mysql> SET sql_mode='ANSI_QUOTES';
mysql> CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)"


Thomas D


Quoting Stephen Hedges <shedges at skemotah.com> :
> ---------------- Beginning of the original message ------------------
> 
> I've been trying to install Koha with MySQL 5.0, but I get an
> error when
> the installer tries to create the "issues" table.  The problem
> turns out
> to be simple to explain, but harder to fix.
> 
> Starting with version 5.0, MySQL treats "return" as a reserved
> word, and
> the "issues" table has a column named "return."  That's what
> causes the
> error.  The easy and immediate fix is to avoid using MySQL 5.0
> or higher. 
> Eventually, however, it would be nice to change the name of
> this column so
> Koha users can use all the features of MySQL 5+.
> 
> -- 
> Stephen Hedges
> Skemotah Solutions, USA
> www.skemotah.com  --  shedges at skemotah.com
> 
> 
> 
> 
> 
> 
> _______________________________________________
> Koha-devel mailing list
> Koha-devel at nongnu.org
> http://lists.nongnu.org/mailman/listinfo/koha-devel
> 
> ------------------- End of the original message ---------------------




---------------------------------------------
Protect your mails from viruses thanks to Alinto Premium services http://www.alinto.com