Error in accessing MySql database from Java

[joomleex]

Hello.

 

I have uploaded a deployed a java .war file to my account on Johnny server, following instructions on creating a java/jsp application in eclipse. All worked fine because accessing a MySql database. When trying to do so, I started getting a "java.sql.SQLException: No suitable driver found" error.

 

At first I got the same error while accessing MySql in my local computer. I solved it (following suggestions) by copying the MySql ConnectJ .jar file both in tomcat/lib and jdk/jre/lib/ext folders and in WEB-INF/lib folder in the eclipse project (the latter was immaterial in having the database accessed). However, after uploading and deploying the .war file on heliohost the error continued to obtain.

 

After trying a few additional modifications (such as specifying directly the mysql database address) the server stopped accepting to deploy the .war file and started giving a message "Java deployment failed with errors. For further information please contact support". So, I am stuck now.

 

Thank you for your support,

 

Giuliano

[joomleex]

Update: I erased the .war file, replacet the version 8 of the ConnectorJ with mysql-connector-java-5.1.47-bin.jar, uploaded and deployed. Now there is no deployment error, yet the connection with the MySql database is not working. Here is (part of) the error stack:

org.apache.jasper.JasperException: An exception occurred processing JSP page [/index.jsp] at line [65]

 

62: String mysql_database = "joomleex_idara";

63: String mysql_username = "joomleex_admin";

64: String mysql_password = "<removed>";

65: Connection connection = DriverManager.getConnection(

66: "jdbc:mysql://127.0.0.1/" + mysql_database,

67: mysql_username,

68: mysql_password

Root Cause

javax.servlet.ServletException: java.sql.SQLException: No suitable driver found for jdbc:mysql://127.0.0.1/joomleex_idara

I can figure out the error depends from either [a] the connection string (which I copied from the example on heliohost) or the lack of suitable driver on tomcat/lib and java/(jdk/jre)/lib/ext on heliohost server. If anybody has a clue that changing the connection string might solve the issue, please send me suggestions. Case is possible, since I got the same error on my local pc before copying the connector jar on both tomcat and java lib folders.

 

In the latter case, a possible solution would be to upload in the .war WEB-INF/lib folder another connector which matches exactly what is in tomcat (and java) lib folders on the server. Since I have no access to those folders, I can only imagine. Please give me some hint if that could be a solution.

 

Any help will be very appreciated.

 

Thank you,

 

Giuliano

Edited March 24, 2019 by Krydos
removed password

[joomleex]

While waiting for a solution, I found an alternative that works perfectly (even better to me): I opened a free account on MongoDB.Atlas (500 MB space) and connected to it through the Java driver. It worked like a charm (suffices to drop the MongoDB java driver into the WEB-INF/lib folder).

 

To me, the solution is perfect, since I strongly prefer MongoDB over MySql. However, a solution to the issue I raised might still be useful to whomever prefers, or for some reason is compelled, to use MySql.

[Krydos]

Likely when the .war was refusing to deploy due to errors is when you had it the closest. I've seen a lot of .wars refuse to deploy because java doesn't play nice on a multi-user system and for whatever reason it tries to open 100 mysql connections during deployment. Obviously this is going to fail because you're on a server with other users than just you. If that is why it was failing to deploy all you would have needed to do was limit the number of simultaneous connections to something sane like 6, or even better 1. This is all just guesses without any actual evidence though so who knows what the actual reason was.

[joomleex]

Thank you for your answer. Have you any idea about how could I limit the number of simltaneous connections and/or close open connections in order to avoid spawning too many processes?

[Krydos]

jdbc:mysql://127.0.0.1:3306/joomleex_db?max-connections=1

 

https://stackoverflow.com/a/2225275/2336864

[joomleex]

Thank you for this link. However, I still cannot understand how the lack of this would hinder a .war file from being deployed. It could make problems when actually launching the servlet, not while deploying it.

[wolstech]

I'm pretty sure the whole thing fails if it won't start here. Our deployment procedure includes starting it...when you deploy it, the server renames the war if needed, unpacks it and configures Tomcat for it, then launches it.

 

You're correct that in theory one could unpack/set up the war without actually starting it, but what's the point? Tomcat wars here run as another user, and from another folder you can't access, so there's no point in leaving a dead war deployed. You can't access the deployed files, so it's useless if it won't run.

[joomleex]

I see... yet in other cases the war did deploy and a message about no suitable driver was given. Which version mysql java connector is actually expected to be in WEB-INF/lib? Is there any other project that succeeded in connecting correctly?

 

I'll give a try to your suggestion, of course. Thank you again!

 

Giuliano

[Krydos]

As far as I know you can use any mysql connector version that you want. Yes, pretty much all deployed .war servlets connect successfully to mysql. Here is a simple .jsp example. It isn't even a deployed .war, just a file named index.jsp

<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

<sql:setDataSource var="mysql_test" driver="com.mysql.jdbc.Driver"

     url="jdbc:mysql://localhost/krydos_test?serverTimezone=UTC"
     user="krydos_test"  password="<removed>"/>

<sql:query dataSource="${mysql_test}" var="result">
SELECT * from java;
</sql:query>

<c:forEach var="row" items="${result.rows}">
<c:out value="${row.data}"/>
</c:forEach>

It's an old example so I'm using an old connector but it's working with mysql-connector-java-6.0.5.jar in /WEB-INF/lib/ You can download my exact version from http://krydos.heliohost.org/mysql.jsp/WEB-INF/lib/mysql-connector-java-6.0.5.jar if you want, or the newest version can be downloaded from https://dev.mysql.com/downloads/connector/j/ Keep in mind that if you're doing a simple .jsp example like mine the .jar files aren't loaded until Tomcat restarts. It restarts on its own every few days, or you can request a restart. If you do this all within a deployed .war you don't need to restart Tomcat, it loads everything up when it's deployed.