Java application deployed on Tomcat doesn't connect to jdbc-mysql

Question

I'm trying to restore our tomcat server, but there's this application that doesn't connect to MySQL properly.

Here's what is happening:

I had an Java + Flex application. The entire application was placed into a directory (not a .war file).

$TOMCAT_WEBAPP/myflex_app/WEB-INF/lib/ -> JDBC mysql driver goes here.

Here's my application config:

    <database>
            <rpgByMoodle user="moodle" password="moodle">
                    <url>jdbc:mysql://localhost:3306/rpgbymoodle</url>
            </rpgByMoodle>
            <moodle user="moodle" password="moodle">
                    <url>jdbc:mysql://localhost:3306/moodle</url>
            </moodle>
    </database>

So, I'm able to connect mysql through the command line client, but the application don't.

I found on the internet people telling to "add new connection" using some Netbeans (or Eclipse) menus.. but, I haven't access to the source code of the application.

I'm running tomcat on Linux. I've checked the tomcat log file at /var/log/tomcat6/ and found nothing about jdbc.

My catalina log:

Aug 4, 2011 9:24:25 AM org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/6.0.20
Aug 4, 2011 9:24:26 AM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
Aug 4, 2011 9:24:26 AM org.apache.catalina.startup.Catalina start
INFO: Server startup in 977 ms

Answer 1

Try putting the JDBC jar file into tomcat-dir/common/lib and restart Tomcat.
Compare question Managing libraries in Tomcat.

If this does still now work, post the extract from Tomcat log file, looking for connection not jdbc as keyword.

Answer 2

I had the exact same problem before and managed to solve it. Apache requires an extra configuration step for jdbc-msql. Firstly, make sure that you have the correct GRANT permissions set up for your SQL database.

Next, follow his solution which is taken from: http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-j2ee.html#connector-j-usagenotes-tomcat

First, install the .jar file that comes with Connector/J in $CATALINA_HOME/common/lib so that it is available to all applications installed in the container.

Next, Configure the JNDI DataSource by adding a declaration resource to $CATALINA_HOME/conf/server.xml in the context that defines your web application:

<Context ....>

...

<Resource name="jdbc/MySQLDB"
           auth="Container"
           type="javax.sql.DataSource"/>

<!-- The name you used above, must match _exactly_ here!

   The connection pool will be bound into JNDI with the name
   "java:/comp/env/jdbc/MySQLDB"
-->

<ResourceParams name="jdbc/MySQLDB">
<parameter>
  <name>factory</name>
  <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>

<!-- Don't set this any higher than max_connections on your
     MySQL server, usually this should be a 10 or a few 10's
     of connections, not hundreds or thousands -->

<parameter>
  <name>maxActive</name>
  <value>10</value>
</parameter>

<!-- You don't want to many idle connections hanging around
     if you can avoid it, only enough to soak up a spike in
     the load -->

<parameter>
  <name>maxIdle</name>
  <value>5</value>
</parameter>

<!-- Don't use autoReconnect=true, it's going away eventually
     and it's a crutch for older connection pools that couldn't
     test connections. You need to decide whether your application
     is supposed to deal with SQLExceptions (hint, it should), and
     how much of a performance penalty you're willing to pay
     to ensure 'freshness' of the connection -->

<parameter>
  <name>validationQuery</name>
  <value>SELECT 1</value> <-- See discussion below for update to this option -->
</parameter>

<!-- The most conservative approach is to test connections
    before they're given to your application. For most applications
    this is okay, the query used above is very small and takes
    no real server resources to process, other than the time used
    to traverse the network.

    If you have a high-load application you'll need to rely on
    something else. -->

 <parameter>
  <name>testOnBorrow</name>
  <value>true</value>
 </parameter>

 <!-- Otherwise, or in addition to testOnBorrow, you can test
    while connections are sitting idle -->

  <parameter>
   <name>testWhileIdle</name>
   <value>true</value>
  </parameter>

 <!-- You have to set this value, otherwise even though
     you've asked connections to be tested while idle,
     the idle evicter thread will never run -->

 <parameter>
   <name>timeBetweenEvictionRunsMillis</name>
   <value>10000</value>
 </parameter>

 <!-- Don't allow connections to hang out idle too long,
     never longer than what wait_timeout is set to on the
     server...A few minutes or even fraction of a minute
     is sometimes okay here, it depends on your application
     and how much spikey load it will see -->

<parameter>
  <name>minEvictableIdleTimeMillis</name>
  <value>60000</value>
</parameter>

<!-- Username and password used when connecting to MySQL -->

<parameter>
 <name>username</name>
 <value>someuser</value>
</parameter>

<parameter>
 <name>password</name>
 <value>somepass</value>
</parameter>

<!-- Class name for the Connector/J driver -->

<parameter>
   <name>driverClassName</name>
   <value>com.mysql.jdbc.Driver</value>
</parameter>

<!-- The JDBC connection url for connecting to MySQL, notice
     that if you want to pass any other MySQL-specific parameters
     you should pass them here in the URL, setting them using the
     parameter tags above will have no effect, you will also
     need to use &amp; to separate parameter values as the
     ampersand is a reserved character in XML -->

<parameter>
  <name>url</name>
  <value>jdbc:mysql://localhost:3306/test</value>
</parameter>