I have been trying to set up a DatabaseConnectionPool for a web app for the last couple of days with no success. I have read the relevant sections of the Tomcat docs and a great deal around the subject and think I'm doing everything right, but obviously not because I keep on getting the following error:
Cannot create PoolableConnectionFactory (Access denied for user ''@'localhost' (using password: YES))
I'm not getting the error when I start Tomcat running, but when I try to run the following servlet:
package twittersearch.web;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import twittersearch.model.*;
public class ConPoolTest extends HttpServlet {
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
Context ctx = null;
DataSource ds = null;
Connection conn = null;
try {
ctx = new InitialContext();
ds = (DataSource)ctx.lookup("java:comp/env/jdbc/twittersearchdb");
conn = ds.getConnection();
if(conn != null) {
System.out.println("have a connection from the pool");
}
} catch(SQLException e) {
e.printStackTrace();
} catch(NamingException e) {
e.printStackTrace();
} finally {
try {
if(conn!=null) {
conn.close();
}
} catch(SQLException e) {
e.printStackTrace();
}
}
}
}
The context for the webapp is:
<?xml version='1.0' encoding='utf-8'?>
<Context>
<!-- Configure a JDBC DataSource for the user database -->
<Resource name="jdbc/twittersearchdb"
type="javax.sql.DataSource"
auth="Container"
user="root"
password="mypwd"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/twitter"
maxActive="8"
maxIdle="4"/>
</Context>
What I really don't understand is why the error isn't saying that the access is denied to 'root'@'localhost' when I've specified that that's the user.
What I've tried:
Do I have a duplicate context.xml? No. I deleted the default on in Tomcat 6.0/conf. I tried putting a context.xml in [mywebappname]/META-INF/context.xml but not only did this not work, but resulted in the creation of a file named TwitterSearch.xml which was autogenerated and put in the Tomcat 6.0/conf/Catalina/localhost directory. So now I'm just editing that one and thats the only one I have.
Is it the version of Tomcat? Well, I completely reinstalled the latest version of Tomcat 6.0 so I don't think it's that.
Are we missing some jars? I have the tomcat-dbcp.jar, jconnector.jar and all the other ones that I think I'm meant to have in the Tomcat 6.0/lib directory.
When I look at the passwords in the MySQL database they seem to have been coded for security purposes into a long alpha-numeric string. Is this normal? Should I have this in my context.xml or just the normal password?
I really don't know how I can sort this out and would really appreciate some expert advice.
Many thanks in advance.
Joe
In your configuration context.xml for the webapp you have to change user="root" by username="root"
Your setup looks fine. This looks to purly be a permissions problem.
You need to grant that user access in mysql. While Java will connect to localhost, it will do so using tcp/ip - however in mysql localhost and 127.0.0.1 have different meaning. Issuing this SQL should do the trick.
That assumes Java resolves 'localhost' to 127.0.0.1, if things still doesn't work, you could try changing your connection string to "jdbc:mysql://127.0.0.1:3306/twitter"
As you have it now, the plaintext password.