Professional Web Applications Themes

Ye olde syntax error - MySQL

Removed by Administrator...

  1. Moderated Post

    Default Ye olde syntax error

    Removed by Administrator
    Shane Guest
    Moderated Post

  2. #2

    Default Re: Ye olde syntax error

    Shane wrote: 

    Works fine in my system (Version 4.1.11). What version are you using?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Ye olde syntax error

    Jerry Stuckle wrote:
     
    >
    > Works fine in my system (Version 4.1.11). What version are you using?
    >[/ref]
    Ugh, that may be the problem
    mysql -V
    mysql Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i486) using readline 5.1

    --
    Leela: This is my first visit to the Galaxy of Terror and I'd like it to be
    a pleasant one.

    blog: http://shanes.dyndns.org

    Shane Guest

  4. #4

    Default Re: Ye olde syntax error

    Shane wrote: 
    >>
    >>Works fine in my system (Version 4.1.11). What version are you using?
    >>[/ref]
    >
    > Ugh, that may be the problem
    > mysql -V
    > mysql Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i486) using readline 5.1
    >[/ref]

    OK, I tried it on MySQL 5.0.22 on Windows. Works fine there, also.

    Did you try checking the support forums for your bulletin board package?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Ye olde syntax error

    Jerry Stuckle wrote:
     
    >>
    >> Ugh, that may be the problem
    >> mysql -V
    >> mysql Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i486) using readline
    >> 5.1
    >>[/ref]
    >
    > OK, I tried it on MySQL 5.0.22 on Windows. Works fine there, also.
    >
    > Did you try checking the support forums for your bulletin board package?
    >[/ref]


    Yes.. somebody else has the same issue, with no resolution :-)

    --
    Hermes: Fry! Don't be a hero! It's not covered by the health plan!

    blog: http://shanes.dyndns.org

    Shane Guest

  6. #6

    Default Re: Ye olde syntax error

    Shane wrote: [/ref][/ref]

    Looks like the client is trying to execute multiple statements in one
    query. Not all interfaces permit this. Some require an option be set,
    that is disabled by default. How are these statements being executed?

    Regards,
    Bill K.
    Bill Guest

  7. #7

    Default Re: Ye olde syntax error

    Bill Karwin wrote:
     [/ref]
    >
    > Looks like the client is trying to execute multiple statements in one
    > query. Not all interfaces permit this. Some require an option be set,
    > that is disabled by default. How are these statements being executed?
    >
    > Regards,
    > Bill K.[/ref]

    To be honest I wouldnt know how to answer that question
    How would I get the information you require?
    --
    Fry: That doesn't look like an L, unless you count lowercase.
    Bender: You know we don't!

    blog: http://shanes.dyndns.org

    Shane Guest

  8. #8

    Default Re: Ye olde syntax error

    > and I get this error 


    There should be no comma in front of NOT NULL.

    Best regards
    Dikkie Guest

  9. #9

    Default Re: Ye olde syntax error

    Dikkie Dik wrote:
     
    >
    >
    > There should be no comma in front of NOT NULL.
    >
    > Best regards[/ref]

    Ive got a feeling the problem is earlier. Here is the first two blocks of
    SQL
    CREATE TABLE ib_active_sessions (
    ID varchar(32) DEFAULT '0' NOT NULL,
    MEMBER_NAME varchar(32),
    MEMBER_PASSWORD varchar(32),
    MEMBER_ID varchar(32),
    THIS_IP varchar(16),
    LAST_LOG_IN int(10),
    USER_AGENT varchar(255),
    RUNNING_TIME int(10),
    MEMBER_LOGSTATE tinyint(1),
    LOCATION varchar(160),
    LOG_IN_TYPE tinyint(1),
    MEMBER_GROUP smallint(3),
    PRIMARY KEY (ID)
    );
    CREATE TABLE ib_address_books (
    ID smallint(10) unsigned DEFAULT '0' NOT NULL,
    IN_MEMBER_ID varchar(32) NOT NULL,
    MEMBER_ID varchar(32) NOT NULL,
    IN_MEMBER_NAME varchar(32) NOT NULL,
    RECEIVE_MSG tinyint(1),
    IN_MEMBER_DESC varchar(128),
    PRIMARY KEY (ID)
    );


    My only other guess was that the Primary key for ib_active_sessions is wrong
    (It set as varchar and *all* other primary keys are smaillint's)
    However I changed it to no avail
    the error is pointing at line 15 which is
    );

    Im tempted to remove the semi colon and see what that does

    --
    Fry: We're in the middle of nowhere, which is the safest part of nowhere.

    blog: http://shanes.dyndns.org

    Shane Guest

  10. #10

    Default Re: Ye olde syntax error


    Shane wrote: 
    > >
    > >
    > > There should be no comma in front of NOT NULL.
    > >
    > > Best regards[/ref]
    >
    > Ive got a feeling the problem is earlier. Here is the first two blocks of
    > SQL
    > CREATE TABLE ib_active_sessions (
    > ID varchar(32) DEFAULT '0' NOT NULL,
    > MEMBER_NAME varchar(32),
    > MEMBER_PASSWORD varchar(32),
    > MEMBER_ID varchar(32),
    > THIS_IP varchar(16),
    > LAST_LOG_IN int(10),
    > USER_AGENT varchar(255),
    > RUNNING_TIME int(10),
    > MEMBER_LOGSTATE tinyint(1),
    > LOCATION varchar(160),
    > LOG_IN_TYPE tinyint(1),
    > MEMBER_GROUP smallint(3),
    > PRIMARY KEY (ID)
    > );
    > CREATE TABLE ib_address_books (
    > ID smallint(10) unsigned DEFAULT '0' NOT NULL,
    > IN_MEMBER_ID varchar(32) NOT NULL,
    > MEMBER_ID varchar(32) NOT NULL,
    > IN_MEMBER_NAME varchar(32) NOT NULL,
    > RECEIVE_MSG tinyint(1),
    > IN_MEMBER_DESC varchar(128),
    > PRIMARY KEY (ID)
    > );
    >
    >
    > My only other guess was that the Primary key for ib_active_sessions is wrong
    > (It set as varchar and *all* other primary keys are smaillint's)
    > However I changed it to no avail
    > the error is pointing at line 15 which is
    > );
    >
    > Im tempted to remove the semi colon and see what that does
    >
    > --
    > Fry: We're in the middle of nowhere, which is the safest part of nowhere.
    >
    > blog: http://shanes.dyndns.org[/ref]

    I don't get it. The error's complaining about some table called
    'address_books' and you keep showing us the code for creating a table
    called 'ib_address_books'!

    What's up with that?

    strawberry Guest

  11. #11

    Default Re: Ye olde syntax error

    strawberry wrote:
     
    >>
    >> Ive got a feeling the problem is earlier. Here is the first two blocks
    >> of SQL
    >> CREATE TABLE ib_active_sessions (
    >> ID varchar(32) DEFAULT '0' NOT NULL,
    >> MEMBER_NAME varchar(32),
    >> MEMBER_PASSWORD varchar(32),
    >> MEMBER_ID varchar(32),
    >> THIS_IP varchar(16),
    >> LAST_LOG_IN int(10),
    >> USER_AGENT varchar(255),
    >> RUNNING_TIME int(10),
    >> MEMBER_LOGSTATE tinyint(1),
    >> LOCATION varchar(160),
    >> LOG_IN_TYPE tinyint(1),
    >> MEMBER_GROUP smallint(3),
    >> PRIMARY KEY (ID)
    >> );
    >> CREATE TABLE ib_address_books (
    >> ID smallint(10) unsigned DEFAULT '0' NOT NULL,
    >> IN_MEMBER_ID varchar(32) NOT NULL,
    >> MEMBER_ID varchar(32) NOT NULL,
    >> IN_MEMBER_NAME varchar(32) NOT NULL,
    >> RECEIVE_MSG tinyint(1),
    >> IN_MEMBER_DESC varchar(128),
    >> PRIMARY KEY (ID)
    >> );
    >>
    >>
    >> My only other guess was that the Primary key for ib_active_sessions is
    >> wrong (It set as varchar and *all* other primary keys are smaillint's)
    >> However I changed it to no avail
    >> the error is pointing at line 15 which is
    >> );
    >>
    >> Im tempted to remove the semi colon and see what that does
    >>
    >> --
    >> Fry: We're in the middle of nowhere, which is the safest part of nowhere.
    >>
    >> blog: http://shanes.dyndns.org[/ref]
    >
    > I don't get it. The error's complaining about some table called
    > 'address_books' and you keep showing us the code for creating a table
    > called 'ib_address_books'!
    >
    > What's up with that?[/ref]


    Im presuming that ib_ is prepended to all the table names
    ib_active_sessions
    ib_address_books
    ib_attachments
    ib_authorisations
    etc

    When I make changes to the block I have pasted the error changes
    appropriately, for example I have removed some whitespace, removed the
    comma, tried removing the semi colon
    Each time the error has changed slightly (line numbers reported, etc)


    --
    Leela: Fry, we have a crate to deliver.
    Fry: Well, let's just dump it in the sewer and say we delivered it.
    Bender: Too much work. Let's burn it and say we dumped it in the sewer.
    Leela: OK, if everyone's finished being stupid—
    Fry: I had more, but you go ahead.

    blog: http://shanes.dyndns.org

    Shane Guest

  12. #12

    Default Re: Ye olde syntax error

    Shane wrote: 
    >>
    >>I don't get it. The error's complaining about some table called
    >>'address_books' and you keep showing us the code for creating a table
    >>called 'ib_address_books'!
    >>
    >>What's up with that?[/ref]
    >
    >
    >
    > Im presuming that ib_ is prepended to all the table names
    > ib_active_sessions
    > ib_address_books
    > ib_attachments
    > ib_authorisations
    > etc
    >
    > When I make changes to the block I have pasted the error changes
    > appropriately, for example I have removed some whitespace, removed the
    > comma, tried removing the semi colon
    > Each time the error has changed slightly (line numbers reported, etc)
    >
    >[/ref]

    Shane,

    Strawberry has a good comment - one I missed. He's right - the original
    message was for ib_address_books. That's the code you need to find.

    And no, the ib_ is not being prefixed in the message you showed us.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  13. #13

    Default Re: Ye olde syntax error

    Jerry Stuckle wrote:
     
    >>
    >>
    >>
    >> Im presuming that ib_ is prepended to all the table names
    >> ib_active_sessions
    >> ib_address_books
    >> ib_attachments
    >> ib_authorisations
    >> etc
    >>
    >> When I make changes to the block I have pasted the error changes
    >> appropriately, for example I have removed some whitespace, removed the
    >> comma, tried removing the semi colon
    >> Each time the error has changed slightly (line numbers reported, etc)
    >>
    >>[/ref]
    >
    > Shane,
    >
    > Strawberry has a good comment - one I missed. He's right - the original
    > message was for ib_address_books. That's the code you need to find.
    >
    > And no, the ib_ is not being prefixed in the message you showed us.
    >[/ref]

    I grepped every file in the tarball I was supplied.
    The installation files only refer to ib_<table name>
    as an experiment I have edited the mysql_schema.txt but there was no change
    in the error
    I thought that meant that the file I was editing was not the one causing the
    error, so I changed the table name to ib_banana_address_books
    The error becomes
    mySQL create table error. 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 TABLE banana_address_books ( ID smallint(10) unsigned
    DEFAULT '0' N' at line 15 (CREATE TABLE active_sessions ( ID varchar(32)
    DEFAULT '0' NOT NULL, MEMBER_NAME varchar(32), MEMBER_PASSWORD varchar(32),
    MEMBER_ID varchar(32), THIS_IP varchar(16), LAST_LOG_IN int(10), USER_AGENT
    varchar(255)

    --
    Fry: I used to dream about being an astronaut. I just never had the grades.
    Or the physical endurance. Plus I threw up a lot and nobody liked spending
    a week with me.
    Leela: A week would be a bit much.

    blog: http://shanes.dyndns.org

    Shane Guest

  14. #14

    Default Re: Ye olde syntax error

    Shane wrote: 
    >>
    >>Shane,
    >>
    >>Strawberry has a good comment - one I missed. He's right - the original
    >>message was for ib_address_books. That's the code you need to find.
    >>
    >>And no, the ib_ is not being prefixed in the message you showed us.
    >>[/ref]
    >
    >
    > I grepped every file in the tarball I was supplied.
    > The installation files only refer to ib_<table name>
    > as an experiment I have edited the mysql_schema.txt but there was no change
    > in the error
    > I thought that meant that the file I was editing was not the one causing the
    > error, so I changed the table name to ib_banana_address_books
    > The error becomes
    > mySQL create table error. 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 TABLE banana_address_books ( ID smallint(10) unsigned
    > DEFAULT '0' N' at line 15 (CREATE TABLE active_sessions ( ID varchar(32)
    > DEFAULT '0' NOT NULL, MEMBER_NAME varchar(32), MEMBER_PASSWORD varchar(32),
    > MEMBER_ID varchar(32), THIS_IP varchar(16), LAST_LOG_IN int(10), USER_AGENT
    > varchar(255)
    >[/ref]

    OK, in that case they're doing something to the string before they
    submit it. In that case the source code is pretty much worthless until
    you figure out what they're doing to modify it.

    You're going to have to get help from the support people for your BBS
    software. I don't think anyone here will be able to help you with it.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  15. #15

    Default Re: Ye olde syntax error

    Shane <is-a-geek.net> wrote:
     

    The ';' at the end of the statement is wrong. The fact that there are
    actually two SQL statements is wrong.

    Normally you cannot send multiple SQL statements to the server. There
    are two exceptions:

    1. you "send" the SQL statements via the 'mysql' tool. Then your query
    string is split into statements at unquoted ';' and sent to the
    server as multiple single statements. Note: the splitting is done
    in the client. The server never sees a ';'

    2. beginning with MySQL 4.1 the client/server protocol was extended to
    allow multiple statements being sent to the server at once. However
    this is turned off by default (for good reason [1]). To use the
    multi query feature it has be be turned on - on the client. Please
    consult the manual [2] for details.

    I suspect your ominous "Bulliten Board software" being broken. Maybe
    you should restrain from using it at all.


    [1] the multi query feature dramatically raises the threat from SQL
    injection. Without it, an attacker can only modify the statement
    in place, i.e. skip checking conditions in a SELECT. With multi
    statements turned on an attacker could place arbitrary stuff
    ('; DROP DATABASE foo') into an existing statement. OTOH you gain
    very little from multi statements. My advice: don't use it!

    [2] http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html
    http://php.net/manual/en/function.mysqli-multi-query.php

    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  16. #16

    Default Re: Ye olde syntax error

    Jerry Stuckle wrote: 

    Some web-based software permits adding a "prefix" to the table names as
    an installation option, so that you can run more than one copy of the
    software in one MySQL database instance, and the table names are kept
    separate.
     

    I agree with this, you need to get support from the people who authored
    this product if you don't know how to troubleshoot the errors yourself.

    Regards,
    Bill K.
    Bill Guest

Similar Threads

  1. Replies: 6
    Last Post: September 2nd, 01:22 PM
  2. Syntax error
    By middletree in forum Macromedia ColdFusion
    Replies: 11
    Last Post: May 2nd, 03:10 AM
  3. Help on Syntax error please
    By Gilles in forum MySQL
    Replies: 0
    Last Post: April 28th, 01:51 PM
  4. error : syntax error at or near $1 for over select rows
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 27th, 06:51 PM
  5. syntax error ???
    By Ben in forum PHP Development
    Replies: 1
    Last Post: September 30th, 10:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139