Probably a permissions problem.

Hi

I connect to my MySQL database using MyODBC.

I have a table called 'log' in a database called 'home'. When I try to
do an insert like this:
insert into log (userid,event,event_date) values (blah blah....

I get this error response from MySQL:
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 'log
(userid,event,event_d
ate) values ('nt','HomeDB Started',now())' at line 1

However, if I use :
insert into home.log (userid,event,event_date) values (blah blah....

It all works OK.
I have another table in the same database which doesn't exhibit this
problem.

Is this some sort of permissions or access rights issue?

Any help appreciated.

Regards

Nick

Re: Probably a permissions problem.

>I connect to my MySQL database using MyODBC.
>
>I have a table called 'log' in a database called 'home'. When I try to
>do an insert like this:
>insert into log (userid,event,event_date) values (blah blah....
>
>I get this error response from MySQL:
>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 'log
>(userid,event,event_d
>ate) values ('nt','HomeDB Started',now())' at line 1

Is log a MySQL keyword in your version of MySQL?
If so, quote the table name (`log`). Or perhaps it's `event`
that's a keyword (5.1.6 and later). What version are you running?

>However, if I use :
>insert into home.log (userid,event,event_date) values (blah blah....
>
>It all works OK.
>I have another table in the same database which doesn't exhibit this
>problem.
>
>Is this some sort of permissions or access rights issue?

Permissions and access rights issues should not draw 'syntax error'
error messages. If they do, file a bug report.

Re: Probably a permissions problem.

Gordon Burditt wrote:
> >I connect to my MySQL database using MyODBC.
> >
> >I have a table called 'log' in a database called 'home'. When I try to
> >do an insert like this:
> >insert into log (userid,event,event_date) values (blah blah....
> >
> >I get this error response from MySQL:
> >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 'log
> >(userid,event,event_d
> >ate) values ('nt','HomeDB Started',now())' at line 1
>
> Is log a MySQL keyword in your version of MySQL?
> If so, quote the table name (`log`). Or perhaps it's `event`
> that's a keyword (5.1.6 and later). What version are you running?

MySQL version 4.1.9-nt. On Win2K.
>
> >However, if I use :
> >insert into home.log (userid,event,event_date) values (blah blah....
> >
> >It all works OK.
> >I have another table in the same database which doesn't exhibit this
> >problem.
> >
> >Is this some sort of permissions or access rights issue?
>
> Permissions and access rights issues should not draw 'syntax error'
> error messages. If they do, file a bug report.

Well, I don't understand it. I have the same Database setup at home,
and it all works fine. Both systems are Win2K with the same versions of
MyODBC and MySQL. There must be some difference in user permissions
causing this.

Thanks for the suggestions anyway.

Re: Probably a permissions problem.

Gordon Burditt wrote:
> >I connect to my MySQL database using MyODBC.
> >
> >I have a table called 'log' in a database called 'home'. When I try to
> >do an insert like this:
> >insert into log (userid,event,event_date) values (blah blah....
> >
> >I get this error response from MySQL:
> >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 'log
> >(userid,event,event_d
> >ate) values ('nt','HomeDB Started',now())' at line 1
>
> Is log a MySQL keyword in your version of MySQL?
> If so, quote the table name (`log`). Or perhaps it's `event`
> that's a keyword (5.1.6 and later). What version are you running?

MySQL version 4.1.9-nt. On Win2K.
>
> >However, if I use :
> >insert into home.log (userid,event,event_date) values (blah blah....
> >
> >It all works OK.
> >I have another table in the same database which doesn't exhibit this
> >problem.
> >
> >Is this some sort of permissions or access rights issue?
>
> Permissions and access rights issues should not draw 'syntax error'
> error messages. If they do, file a bug report.

Well, I don't understand it. I have the same Database setup at home,
and it all works fine. Both systems are Win2K with the same versions of
MyODBC and MySQL. There must be some difference in user permissions
causing this.

Thanks for the suggestions anyway.

Re: Probably a permissions problem.

Control Freq wrote:
> I have the same Database setup at home,
> and it all works fine. Both systems are Win2K with the same versions of
> MyODBC and MySQL.

I just thought of a possibility: LOG() is a built-in math function.
MySQL treats a space between a function and its parentheses as
significant. So "LOG (...)" is different from "LOG(...)".

Is it possible that in one environment, you don't have a space after
"LOG", so MySQL thinks you mean the LOG() math function? Which would of
course be nonsensical in the context of an INSERT statement, in a place
where a table name belongs.

If you are building the SQL statement with string concatenation, watch
out for situations like the following:

$sql_statement = "insert into log"
+ "(userid, event, event_date) values ..."

Notice the above creates a string with "log" right next to the following
parenthesis, with no space in between. This may confuses the MySQL
parser.

In fact, trying it in my MySQL instance, I was able to reproduce the
error you saw by putting no space in "log(...", and it works correctly
by putting a space in "log (...".

Regards,
Bill K.

Re: Probably a permissions problem.

Probably a silly questions, but is the same user which created the
table log, the same user which is trying to access the table?

Failing that you can always create a synonym name for the table...

Description:

The CREATE SYNONYM statement (create_synonym_statement) defines a
synonym (alternative name) for a table name.

Syntax:

::=
CREATE [PUBLIC] SYNONYM [.] FOR



Control Freq wrote:
> Gordon Burditt wrote:
> > >I connect to my MySQL database using MyODBC.
> > >
> > >I have a table called 'log' in a database called 'home'. When I try to
> > >do an insert like this:
> > >insert into log (userid,event,event_date) values (blah blah....
> > >
> > >I get this error response from MySQL:
> > >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 'log
> > >(userid,event,event_d
> > >ate) values ('nt','HomeDB Started',now())' at line 1
> >
> > Is log a MySQL keyword in your version of MySQL?
> > If so, quote the table name (`log`). Or perhaps it's `event`
> > that's a keyword (5.1.6 and later). What version are you running?
>
> MySQL version 4.1.9-nt. On Win2K.
> >
> > >However, if I use :
> > >insert into home.log (userid,event,event_date) values (blah blah....
> > >
> > >It all works OK.
> > >I have another table in the same database which doesn't exhibit this
> > >problem.
> > >
> > >Is this some sort of permissions or access rights issue?
> >
> > Permissions and access rights issues should not draw 'syntax error'
> > error messages. If they do, file a bug report.
>
> Well, I don't understand it. I have the same Database setup at home,
> and it all works fine. Both systems are Win2K with the same versions of
> MyODBC and MySQL. There must be some difference in user permissions
> causing this.
>
> Thanks for the suggestions anyway.