Permission Errors

["Piotrowski, Brian" <pi...@corp.efni.com>]

We just moved our site to a new server, but I am having problems connecting
to the MySQL database.  Whenever our site tries to connect, I get the error
message:

Error: 500
Location: /examples/jsp/test/dbquery.jsp
Internal Servlet Error:
javax.servlet.ServletException: Cannot load connection class
'java.sql.SQLException: Invalid authorization specification: Access denied
for user: 'root@localhost.localdomain' (Using password: YES)'.
	at
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImp
l.java, Compiled Code)
	at
jsp.test._0002fjsp_0002ftest_0002fdbquery_0002ejspdbquery_jsp_28._jspService
(_0002fjsp_0002ftest_0002fdbquery_0002ejspdbquery_jsp_28.java, Compiled
Code)
	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java,
Compiled Code)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java, Compiled
Code)
	at
org.apache.jasper.runtime.JspServlet$JspServletWrapper.service(JspServlet.ja
va, Compiled Code)
	at
org.apache.jasper.runtime.JspServlet.serviceJspFile(JspServlet.java,
Compiled Code)
	at org.apache.jasper.runtime.JspServlet.service(JspServlet.java,
Compiled Code)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java, Compiled
Code)
	at
org.apache.tomcat.core.ServletWrapper.handleRequest(ServletWrapper.java,
Compiled Code)
	at
org.apache.tomcat.core.ContextManager.service(ContextManager.java, Compiled
Code)
	at
org.apache.tomcat.service.connector.Ajp12ConnectionHandler.processConnection
(Ajp12ConnectionHandler.java, Compiled Code)
	at
org.apache.tomcat.service.TcpConnectionThread.run(SimpleTcpEndpoint.java,
Compiled Code)
	at java.lang.Thread.run(Thread.java, Compiled Code)

I know there is a password problem, but for the life of me I can't figure
out what it is!  Here are some specs on the server:
server name: enhance.efni.com

in my code, I have this string to connect to the server:
Connection myConn =
DriverManager.getConnection("jdbc:mysql:///MyDB?user=root&password=XXXXXXX")
;

In my USER table for the MySQL database, I have a few entries including:
host: localhost
user: root
pass: (root password)
permissions (Y on all permissions)

host: enhance.efni.com
user: root
pass: (root password)
permissions (Y on all permissions)

Do I have to modify any other tables to give Tomcat access to the MySQL
tables?

Thanks,

Brian.

-----------------------------------
Brian Piotrowski
Developer / Network Administrator
Moneyramp.com
(705) 475-9531
www.moneyramp.com
-----------------------------------

Re: Permission Errors

[Robert Burrell Donkin <Ro...@appleonline.net>]

"Piotrowski, Brian" wrote:

> We just moved our site to a new server, but I am having problems connecting
> to the MySQL database.  Whenever our site tries to connect, I get the error
> message:
>
> in my code, I have this string to connect to the server:
> Connection myConn =
> DriverManager.getConnection("jdbc:mysql:///MyDB?user=root&password=XXXXXXX")
> ;
>

i'm certainly no expert but i do have a couple of things that you might try:

a. test out your basic mysql data access code in a standalone java app until it
works well, then call your underlying classes from web environment. i even
sometimes go as far as outputting to a html text box in an application if the
data access stuff's not working well. It's much, much easier to debug it this
way. Also, any artifacts you might leave lying about should have a lower chance
of making it out onto the web...

b. check the syntax for your JDBC URI for your driver

        (is it an URI or an URL...? and why? maybe somebody might like to
enlighten me...)

mine uses something like "jdbc:mysql://localhost/test?user=monty
password=camel"

hope you find this (vaguely) useful
robert

Re: Permission Errors

[Robert Burrell Donkin <Ro...@appleonline.net>]

Dave Newton wrote:

> Which JDBC drivers are you using? I ended up using the multi-argument
> version of getConnection("jdbc:mysql://localhost/DBName", "user", "passwd")
> even though the documentation said to do otherwise, and it worked (I haven't
> tested w/ the single-argument version since then~if it ain't broke, and all
> that).

i believe that you can either enclude the user and password in the URL (or
should that be URI) you pass to the driver, or you can pass them as additional
arguments (as you have done). MySQL has a funny way of encoding them into to
URL and i've seen more than one posting (on various lists) which contains
probably errors in the URL. Brian's URL looked a bit screwy and i had an URL
that definitely work to hand so I posted that.  Passing user and password as
additional parameters is probably better.

> If I can be of help, let me know, and probably keep the JDBC traffic off of
> the tomcat list since it's not a tomcat issue.

agreed. the only reason i replied was that i sort of think that giving some
tips even on something slightly off topic posting usually wastes less bandwidth
in the long run and is much politer. it has the additional advantage that this
kind of configuration (tomcat-JDBC-MySQL) is likely to become more and more
popular and hopefully (you never know) if there's an answer in the archives, it
won't get asked so frequently.
maybe brian piotrowski got bored or maybe got it fixed. i don't see any reply
posted from him so maybe i'll forward all these emails - and he might like to
take you up on your offer.

cheers
robert

RE: Permission Errors

[Dave Newton <da...@solaraccess.com>]

>
DriverManager.getConnection("jdbc:mysql:///MyDB?user=root&password=XXXXXXX")

> a. test out your basic mysql data access code in a standalone
> java app until it works well

I can also recommend this approach~dealing with only one set of problems
at a time is much nicer :)

> b. check the syntax for your JDBC URI for your driver
> mine uses something like
"jdbc:mysql://localhost/test?user=monty&password=camel"

Which JDBC drivers are you using? I ended up using the multi-argument
version of getConnection("jdbc:mysql://localhost/DBName", "user", "passwd")
even though the documentation said to do otherwise, and it worked (I haven't
tested w/ the single-argument version since then~if it ain't broke, and all
that).

I've had weird things happening with my MySQL JDBC stuff~I'd add or modify a
user
and the changes didn't seem to affect anything... for awhile, then stuff
started
working. The weirdness persisted over multiple mysqld startups/shutdowns, so
I was,
and am, puzzled.

Your user table seems fine, I also used the db table; I don't know if I
actually
needed to or not.

I just now (like five minutes ago) got all this working on a JSP through
some
auto-generated beans. My debug process looked like this:

1) connect to DB from java app
2) retrieve records from java app
3) connect to DB from JSP
4) retrieve records from JSP
5) connect/retrieve from DB using auto bean from java app
6) connect/retrieve from DB using auto bean from JSP

If I can be of help, let me know, and probably keep the JDBC traffic off of
the tomcat list since it's not a tomcat issue.

Dave Newton