[BioSQL-l] Possible Mysql 5.x bug

[James Swetnam]

First off, thank you very much to the developers for creating and
maintaining such a useful and interesting project.  I think I have
found a small syntactical bug; as a caveat, however, I am not a
database developer and have very little experience in these matters.
    I do know how to read documentation though, which I've relied  
heavily
    on to write this email.
    As per the biopython setup tutorial I'm attempting to run the  
biosqldb-
    mysql.sql file on Mac OS X Leopard.  Here is my mysql version  
string:
    cardozo13:sql james$ mysql -V
    mysql  Ver 14.12 Distrib 5.0.54-20071214, for apple-darwin9.1.0
    (powerpc) using  EditLine wrapper
    And my procedure (after grabbing the biosql source via CVS).
    cardozo13:sql james$ mysqladmin -u root -p create bioseqdb
    Enter password:
    cardozo13:sql james$ mysql -u root bioseqdb -p < biosqldb-
    mysql.sqlEnter password:
    ERROR 1064 (42000) at line 169: 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 '--CREATE INDEX ontrel_subjectid ON
    term_relationship(subject_term_id)' at line 1
    Interesting.  Let's take a look at line 169:

    --CREATE INDEX ontrel_subjectid ON  
term_relationship(subject_term_id);

    And an excerpt from the documentation for my version of MySQL (5.0
    reference manual), section 1.8.5.6. '--' as the Start of a Comment:

    Standard SQL uses “--” as a start-comment sequence. MySQL Server  
uses
    “#” as the start comment character. MySQL Server 3.23.3 and up also
    supports a variant of the “--” comment style. That is, the “--”  
start-
    comment sequence must be followed by a space (or by a control
    character such as a newline). The space is required to prevent
    problems with automatically generated SQL queries that use  
constructs
    such as the following, where we automatically insert the value of  
the
    payment for payment:

    OK. So after replacing all the lines in which -- is not followed  
by a
    space (thank you regexps), it works beautifully.

    cardozo13:sql james$ mysql -u root bioseqdb -p < biosqldb-mysql.sql
    Enter password:

    Should this change be implemented?  Or am i missing something?

    James Swetnam
    Research Technician
    New York University School of Medicine







- Done.



---------- Forwarded message ----------
From: "James Swetnam" <jswetnam at gmail.com>
To: biosql-l-request at lists.open-bio.org
Date: Thu, 6 Mar 2008 18:10:07 -0500
Subject: Comment Syntax bug Generates error on
Hello.

First off, thank you very much to the developers for creating and
maintaining such a useful and interesting project.  I think I have
found a small syntactical bug; as a caveat, however, I am not a
database developer and have very little experience in these matters.
I do know how to read documentation though, which I've relied heavily
on to write this email.

As per the biopython setup tutorial I'm attempting to run the biosqldb-
mysql.sql file on Mac OS X Leopard.  Here is my mysql version string:

cardozo13:sql james$ mysql -V
mysql  Ver 14.12 Distrib 5.0.54-20071214, for apple-darwin9.1.0
(powerpc) using  EditLine wrapper

And my procedure (after grabbing the biosql source via CVS).

cardozo13:sql james$ mysqladmin -u root -p create bioseqdb
Enter password:
cardozo13:sql james$ mysql -u root bioseqdb -p < biosqldb-
mysql.sqlEnter password:
ERROR 1064 (42000) at line 169: 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 '--CREATE INDEX ontrel_subjectid ON
term_relationship(subject_term_id)' at line 1

Interesting.  Let's take a look at line 169:

--CREATE INDEX ontrel_subjectid ON term_relationship(subject_term_id);

And an excerpt from the documentation for my version of MySQL (5.0
reference manual), section 1.8.5.6. '--' as the Start of a Comment:

Standard SQL uses “--” as a start-comment sequence. MySQL Server uses
“#” as the start comment character. MySQL Server 3.23.3 and up also
supports a variant of the “--” comment style. That is, the “--” start-
comment sequence must be followed by a space (or by a control
character such as a newline). The space is required to prevent
problems with automatically generated SQL queries that use constructs
such as the following, where we automatically insert the value of the
payment for payment:

OK. So after replacing all the lines in which -- is not followed by a
space (thank you regexps), it works beautifully.

cardozo13:sql james$ mysql -u root bioseqdb -p < biosqldb-mysql.sql
Enter password:

Should this change be implemented?  Or am i missing something?

James Swetnam
Research Technician
New York University School of Medicine







Reply
		
Forward