Search code examples
jdbcamazon-ec2tomcat8mysql-connector

EC2 Tomcat can’t connect to MySQL

I’m working on an Amazon EC2 instance of Amazon Linux, having followed this tutorial precisely, but I can’t work out how to connect Tomcat to MySQL. How am I meant do do this?

What I’ve tried

I have traversed the directories within /usr/share/tomcat8 and, for each directory:

  1. placed the Connector/J file, mysql-connector-java-5.1.45-bin.jar, obtained from here, in the directory
  2. restarted Tomcat
  3. reloaded my index page, app/index.jsp
  4. got an error: javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "java.sql.SQLException: No suitable driver found for jdbc/foo" from line 8 of /index.jsp

I have placed a copy of the Connector/J jar file among my other Java libraries and added it to my classpath, despite the obvious futility of this approach given how Tomcat handles its classpath.

I have reduced my code about as much as I reasonably can in the hope that something else I’d done had produced this phenomenon as a side effect. Since less than fifty lines of code continue to produce the same error, that’s probably not the case, but technically I haven’t ruled it out.

My files

/usr/share/tomcat8/webapps/app/index.jsp

<!DOCTYPE html>
<%@ page import="java.io.*, java.util.*, java.sql.*, javax.servlet.http.*, javax.servlet.*" @>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<% Class.forName("com.mysql.jdbc.Driver").getInstance(); /* for good measure */ %>
<html>
    <body>
        <sql:query dataSource="jdbc/foo" var="result">
            SELECT name FROM Users;
        </sql:query>
        <c:forEach var="r" items="${result.rows}">
            <c:out value="${r.name}"/><br/>
        </c:forEach>
    </body>
</html>

/usr/share/tomcat8/webapps/app/META-INF/context.xml

<?xml version="1.0" encoding="utf-8"?>
<Context>
    <Resource name="jdbc/foo" auth="Container" type="javax.sql.DataSource" maxTotal="64"
              maxIdle="16" maxWaitMillis="10000" username="user" password="password"
              driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/foo"/>
</Context>

/usr/share/tomcat8/webapps/app/WEB-INF/web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
                             http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0"
         metadata-complete="true>

    <request-character-encoding>UTF-8></request-character-encoding>
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
    <resource-ref>
        <description>Database connection</description>
        <res-ref-name>jdbc/foo</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
</web-app>

My database has a user 'user'@'localhost' IDENTIFIED BY 'password' with SELECT privileges on foo.*. Database foo has a table Users(email varchar(127) NOT NULL PRIMARY KEY, name varchar(127) NOT NULL);

Conclusion

It’s probable that I’ve missed something silly, since this is my first time doing anything on Tomcat outside of my local machine (on which this works just fine, by the way). Can anybody find what I’m doing wrong? (Aside from my debugging process itself: I’m fully aware of how horrible that is.)

TL/DR: I’ve tried a bunch of things and Tomcat still can’t connect to MySQL on my EC2 instance.

Edit

To be clear, since I’m being instructed to place mysql-connector-java-5.1.45-bin.jar in various places or asked where I put it: I’ve tried putting it in literally every directory within tomcat8.

Solution

  • You are not technically doing anything wrong, I have experienced (Same setup as above tomcat and mysql) and rectified this issue on my side, you can validate by having a JSP Database test page not using DataSource, the issue is that most Amazon services do not support things out of the box:

    • JNDI Datasource

    EC2 including Beanstalk lack this feature, as per your code above:

    • dataSource="jdbc/foo"

    Solution is to either resort to:

    1. JDBC DriverManager

    2. Create JNDI Datasource

    References: