CODEWITHSUNDEEP
mysqltomcat
0xCAFEBABE
0xCAFEBABE

Reputation: 5666

Tomcat + MySQL: "Invalid authorization specification" when accessing MySQL via Tomcat application

I have been tasked to replace the hardware an existing Apache/Tomcat/MySQL application is running. The old machines are running Ubuntu, while the new machines have a clean and fresh Debian Squeeze 64 bit installation.

I have copied over pretty much everything, backup up and restored the database on the new database server. I've replaced the database connection parameters in the application on the application server to the IP address of the new database server.

The database can be accessed without any problems on the command line. However, accessing the database using the Tomcat application provides the following error message:

Exception while connecting to database: java.sql.SQLException: Invalid authorization specification message from server: "Access denied for user 'root'@'srvbisonapp.local' (using password: YES)"
java.sql.SQLException: Invalid authorization specification message from server: "Access denied for user 'root'@'srvbisonapp.local' (using password: YES)"
java.lang.NullPointerException
java.sql.SQLException: Invalid authorization specification message from server: "Access denied for user 'root'@'srvbisonapp.local' (using password: YES)"
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2001)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1907)
  at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:2524)
  at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:818)
  at com.mysql.jdbc.Connection.createNewIO(Connection.java:1808)
  at com.mysql.jdbc.Connection.<init>(Connection.java:452)
  at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411)
  at java.sql.DriverManager.getConnection(DriverManager.java:620)
  at java.sql.DriverManager.getConnection(DriverManager.java:222)
  at de.branion.db.mysql.DBConnection.connect(DBConnection.java:107)
  at de.branion.db.mysql.DBConnection.connectPermissions(DBConnection.java:41)
  at de.branion.permissions.User.loadFromDB(User.java:75)
  at de.branion.permissions.User.login(User.java:360)
  at org.apache.jsp.login_jsp._jspService(login_jsp.java:133)
  at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
  at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
  at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
  at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
  at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
  at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
  at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
  at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
  at java.lang.Thread.run(Thread.java:636)

Accessing the database with the wrong credentials does not work, and it gives a different error message. So the problem is not with the actual credentials. As I said, access still works on the command line using

mysql -u root -p

As I tried to google the problem, I saw that many had the same kind of trouble over the years, but not many had a real solution. What I've tried so far:

Both were suggestions I found on the net, and both did not help.

I fear it's something esoteric like the old .jar used for accessing MySQL databases is not compatible with the new (recent) version of MySQL, but does not say so. I hope to find a solution that does not require reworking the transfered application, so I'm asking you guys.

I can provide any information that you require.

Thank you.

Upvotes: 1

Views: 6122

Answers (2)

Derek
Derek

Reputation: 1606

I got the same error message and it wasn't nearly so easy to solve. My user could authenticate using the same password, and many SQL statements worked fine. After an hour or two of debugging, I got this narrowed down to a single SELECT statement that was referring to a VIEW defined with SQL SECURITY DEFINER and a DEFINER that had been deleted from the user database. The view was created like this before "deleteduser" was deleted:

CREATE 
DEFINER = `deleteduser`@`%` 
SQL SECURITY DEFINER
VIEW myview AS
...

I think this is a minor bug in MySQL. The error message should have reported a problem authorizing deleteduser instead of loggedinuser, which was already authorized. I reported the bug here.

Upvotes: 3

Mahesh Patil
Mahesh Patil

Reputation: 1551

Access denied for user 'root'@'srvbisonapp.local' (using password: YES)

You can check previledges for this user by executing this command,

show grants for 'root'@'srvbisonapp.local';

And see what privileges this user has.

Also this error clearly indicates that authentication detail is wrong for this user you can change password of existing user using root account of MySQL

GRANT USAGE ON *.* TO 'root'@'srvbisonapp.local' IDENTIFIED BY 'password_you_wish';
FLUSH PRIVILEGES;

Make necessary changes in connection parameter in Tomcat application, this should work

Upvotes: 0

Related Questions