[Koha] Help please

[Thomas D]

I was hoping that someone with more experience with MySQL would have given a
proper answer for this question already.  Maybe no one else had noticed the
issue.  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)"

[I will also post this reply to the koha-devel list thread.]

Thomas D

Quoting Stephen Hedges <shedges at skemotah.com> :
> ---------------- Beginning of the original message ------------------
> 
> Actually, MySQL 5.0 has another problem when used with Koha --
> Koha has a
> data column named "return" but this is a MySQL reserved word
> starting with
> version 5.0.  (Didn't your Koha install program throw errors
> when it tried
> to create the database?)  If you must use MySQL 5.0, I don't
> think you can
> use Koha.  Maybe someone else knows a trick that I don't
> know...
> 
> Stephen
> 
> Stephon McCray said:
> > MySQL server is running on the same machine that I'm trying
> to get KOHA to
> > run on...is KOHA not going to run on MySQL 5.0? Do I need to
> install some
> > other program in order for it to work?
> > Thanks,
> > Stephon McCray
> >
> >>-----Original Message-----
> >>From: Stephen Hedges [mailto:shedges at skemotah.com]
> >>Sent: Wednesday, November 30, 2005 01:08 PM
> >>To: 'Stephon McCray'
> >>Cc: 'koha'
> >>Subject: Re: [Koha] Help please
> >>
> >>Here's the official word from MySQL:
> >>
> >>"MySQL 5.0 uses an authentication protocol based on a
> password hashing
> >>algorithm that is incompatible with that used by older
> (pre-4.1) clients.
> >>If you upgrade the server from 4.1, attempts to connect to
> it with an
> >>older client may fail with the following message:
> >>
> >>shell> mysql
> >>Client does not support authentication protocol requested
> >>by server; consider upgrading MySQL client
> >>
> >>To solve this problem, you should use one of the following
> approaches:
> >>
> >> * Upgrade all client programs to use a 4.1.1 or newer
> client library.
> >> * When connecting to the server with a pre-4.1 client
> program, use an
> >>account that still has a pre-4.1-style password.
> >> * Reset the password to pre-4.1 style for each user that
> needs to use
> >>a pre-4.1 client program. This can be done using the SET
> PASSWORD
> >>statement and the OLD_PASSWORD() function:
> >>
> >>mysql> SET PASSWORD FOR
> >> -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
> >>
> >> Alternatively, use UPDATE and FLUSH PRIVILEGES:
> >>
> >>mysql> UPDATE mysql.user SET Password =
> OLD_PASSWORD('newpwd')
> >> -> WHERE Host = 'some_host' AND User = 'some_user';
> >>mysql> FLUSH PRIVILEGES;
> >>
> >> Substitute the password you want to use for “newpwd” in the
> >>preceding examples. MySQL cannot tell you what the original
> password
> >>was, so you'll need to pick a new one.
> >> * Tell the server to use the older password hashing
> algorithm:
> >> 1. Start mysqld with the --old-passwords option.
> >> 2. Assign an old-format password to each account that has
> had its
> >>password updated to the longer 4.1 format. You can identify
> these
> >>accounts with the following query:
> >>
> >>mysql> SELECT Host, User, Password FROM mysql.user
> >> -> WHERE LENGTH(Password) > 16;
> >>
> >> For each account record displayed by the query, use the
> Host
> >>and User values and assign a password using the
> OLD_PASSWORD()
> >>function and either SET PASSWORD or UPDATE, as described
> >>earlier."
> >>
> >>HTH.
> >>
> >>Stephen
> >>
> >>Stephon McCray said:
> >>> Could someone please help me to understand what's going on
> and how I
> >>> can
> >>> fix it.
> >>>
> >>> Running a XP professional SP2 machine with MySQL 5 and
> Apache 2.0.55
> >>> and
> >>> the latest version of Active Perl.
> >>> [Tue Nov 29 17:41:58 2005] [error] [client 127.0.0.1]
> Premature end of
> >>> script headers: mainpage.pl[Tue Nov 29 17:41:58 2005]
> [error] [client
> >>> 127.0.0.1] DBI connect('blank224:localhost','koha',...)
> failed: Client
> >>> does not support authentication protocol requested by
> server; consider
> >>> upgrading MySQL client at
> c:/usr/koha224/intranet/modules/C4/Context.pm
> >>> line 411[Tue Nov 29 17:41:58 2005] [error] [client
> 127.0.0.1] Can't
> >>> call
> >>> method "prepare" on an undefined value at
> >>> c:/usr/koha224/intranet/modules/C4/Koha.pm line
> 326.Also:[Tue Nov 29
> >>> 17:41:41 2005] [error] [client 127.0.0.1] Premature end of
> script
> >>> headers:
> >>> opac-main.pl[Tue Nov 29 17:41:41 2005] [error] [client
> 127.0.0.1] DBI
> >>> connect('blank224:localhost','koha',...) failed: Client
> does not
> >>> support
> >>> authentication protocol requested by server; consider
> upgrading MySQL
> >>> client at c:/usr/koha224/intranet/modules/C4/Context.pm
> line
> >>> 411Thanks,Stephon McCrayIT DirectorWestern Academy Charter
> School
> >>> _______________________________________________
> 
> 
> -- 
> Stephen Hedges
> Skemotah Solutions, USA
> www.skemotah.com  --  shedges at skemotah.com
> 
> _______________________________________________
> Koha mailing list
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
> 
> ------------------- End of the original message ---------------------




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