MySQL ConnectorJ and remote machines

[Ike]

Let's say I have a MySQL 4.12 database, opened to the internet on
111.111.111.111 allowing all incoming and outgoing ports. I have a username
and password setup, which CAN connect to this database, from
222.222.222.222.

Also on 222.222.222.222, which is running Tomcat, I have a Java servlet,
called, say, servlet.class in WEB-INF/classes. servlet.class has permissions
755, as does WEB-INF/classes.

Additionally, I have on 222.222.222.222 under WEB-INF/lib
mysql-connector-java-3.1.7-bin.jar also with permissions 755.

The system (222.222.222.222) is running on Linux Kernel version
2.6.9-22.0.2.ELsmp. I am running Java 1.5.0_05. Bear in mind, the very same
parameters, from the same machine (222...) connect to the very same db
(111...) under php (so there is no firewall issue on 111...). Additionally,
if I run a MySQL DB (version 4.0.25) on 222... and connect to it (thus, on
'localhost') I can connect no problem. It is only when I go to connect to a
remote machine that I get the following stack trace:

java.sql.SQLException: Server connection failure during transaction.
Attempted reconnect 3 times. Giving up. at
com.mysql.jdbc.Connection.createNewIO(Connection.j ava:1704)
at com.mysql.jdbc.Connection.(Connection.java:491)
at
com.mysql.jdbc.NonRegisteringDriver.connect(NonReg isteringDriver.java:346)
at java.sql.DriverManager.getConnection(DriverManager .java:525)
at java.sql.DriverManager.getConnection(DriverManager .java:171)

I have ruled out firewall issues here (because I can connect from the same
machine with the same parameters under php), as well, I believe, permissions
issues here. Somehow, it (MySQL ConnectorJ) is NOT able to obtain a
connection. I have looked at the parameters passed through the call stack,
and they are correct. Is there a bug with MySQL Connector J for remote
connections? OR am I missing something stupid here?-Ike

[David Harper]

Ike wrote:

Let's say I have a MySQL 4.12 database, opened to the internet on
111.111.111.111 allowing all incoming and outgoing ports. I have a username
and password setup, which CAN connect to this database, from
222.222.222.222.

Also on 222.222.222.222, which is running Tomcat, I have a Java servlet,
called, say, servlet.class in WEB-INF/classes. servlet.class has permissions
755, as does WEB-INF/classes.

Additionally, I have on 222.222.222.222 under WEB-INF/lib
mysql-connector-java-3.1.7-bin.jar also with permissions 755.

The system (222.222.222.222) is running on Linux Kernel version
2.6.9-22.0.2.ELsmp. I am running Java 1.5.0_05. Bear in mind, the very same
parameters, from the same machine (222...) connect to the very same db
(111...) under php (so there is no firewall issue on 111...). Additionally,
if I run a MySQL DB (version 4.0.25) on 222... and connect to it (thus, on
'localhost') I can connect no problem. It is only when I go to connect to a
remote machine that I get the following stack trace:

java.sql.SQLException: Server connection failure during transaction.
Attempted reconnect 3 times. Giving up. at
com.mysql.jdbc.Connection.createNewIO(Connection.j ava:1704)
at com.mysql.jdbc.Connection.(Connection.java:491)
at
com.mysql.jdbc.NonRegisteringDriver.connect(NonReg isteringDriver.java:346)
at java.sql.DriverManager.getConnection(DriverManager .java:525)
at java.sql.DriverManager.getConnection(DriverManager .java:171)

I have ruled out firewall issues here (because I can connect from the same
machine with the same parameters under php), as well, I believe, permissions
issues here. Somehow, it (MySQL ConnectorJ) is NOT able to obtain a
connection. I have looked at the parameters passed through the call stack,
and they are correct. Is there a bug with MySQL Connector J for remote
connections? OR am I missing something stupid here?-Ike

There are several layers of complexity here (Tomcat configuration, data
source specification, remote access to a MySQL server, ...), and you
might find it useful to eliminate possible causes one at a time.

To that end, may I suggest that you write a very simple Java client
program, which you will run on 222.222.222.222 using the same MySQL
Connector/J JAR file and Java runtime as your Tomcat instance, and which
does nothing more than make a connection to your MySQL server on
111.111.111.111 using an explicit JDBC URL.

I'm talking about a "here's how you make a connection to a MySQL server
using JDBC" kind if program that you will find in chapter one of any
book on JDBC.

If *that* program fails with the same stack trace as you quoted above,
then you know the problem lies with the connection between the two
machines, and you can begin to investigate that part of your setup.

If, on the other hand, your simple JDBC client program *can* connect to
the MySQL server on the other machine, you will know that the problem
probably lies in your Tomcat setup.

This would be my approach to solving this problem. It's worked well for
me in the past, and saved me from hours of banging my head against the
wall in frustration :-)

David Harper
Cambridge, England

[Bill Karwin]

Ike wrote:

Additionally, I have on 222.222.222.222 under WEB-INF/lib
mysql-connector-java-3.1.7-bin.jar also with permissions 755.
For what it's worth, there is a 3.1.12 version of the MySQL
Connector/Java available now.
java.sql.SQLException: Server connection failure during transaction.
Attempted reconnect 3 times. Giving up. at
com.mysql.jdbc.Connection.createNewIO(Connection.j ava:1704) I have ruled out firewall issues here (because I can connect from the same
machine with the same parameters under php), as well, I believe, permissions
issues here. Somehow, it (MySQL ConnectorJ) is NOT able to obtain a
connection.

Can you test using the mysql command-line interface with the same user &
password parameters, to connect to MySQL on the remote host?

Can you share a few lines of Java code that you're using to connect?
In particular, can you show the JDBC URL you're using, and your
exception handling?

Or are you using a Tomcat <resource> to connect? I've seen other folks
describe difficulty getting that to work.

I've also seen people have difficulty using the URL format with the
username & password included as URL parameters. It seems to be more
reliable to use the JDBC getConnection() method with username and
passwords as separate method arguments.

Regards,
Bill K.

[Ike]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:e4*********@enews1.newsguy.com...

Can you test using the mysql command-line interface with the same user &
password parameters, to connect to MySQL on the remote host?

I cannot because it is on a 3rd part server (i.e. 222.... is a 3rd part
server running Tomcat). Although, I CAN connect with those parameters, say,
through php MyAdmin
Can you share a few lines of Java code that you're using to connect?
In particular, can you show the JDBC URL you're using, and your
exception handling?
private Connection makeNewConnection()
throws SQLException {
try {
// Load database driver if not already loaded
Class.forName(driver);//driver is org.gjt.mm.mysql.Driver
// Establish network connection to database
Connection connection =
DriverManager.getConnection(url, username, password);//url is
"jdbc:mysql://67.103.171.173:3306/ggripdb?autoReconnect=true"
return(connection);
} catch(Exception cnfe) {
throw new SQLException("Can't connect with: " +
driver+" to "+url);
}
}
Or are you using a Tomcat <resource> to connect? I've seen other folks
describe difficulty getting that to work.
No
I've also seen people have difficulty using the URL format with the
username & password included as URL parameters. It seems to be more
reliable to use the JDBC getConnection() method with username and
passwords as separate method arguments.
I thnk that is what I am passing here, yes? Thanks Bill, Ike
Regards,
Bill K.

[Ike]

THank you David. Unfortunately, the 222.. machine is hosted by a third
party. I think though, if I send them a simple java JDBC app as you suggest,
and obtain the stack trace, it may tell me something.

Thanks Again! -Ike

[Ike]

One thing that IS different on the machines I am connecting to remotely is
that on the remote machines:

set password for me@'%'=OLD_PASSWORD('mypassword');

I'm wondering if that is not screwing it here? Is there a way to use MySQL
ConnectorJ then handle the new/old password issue so that it is compatible
with php4 ? Thanks, Ike

[Bill Karwin]

Ike wrote:

try {
// Load database driver if not already loaded
Class.forName(driver);//driver is org.gjt.mm.mysql.Driver
// Establish network connection to database
Connection connection =
DriverManager.getConnection(url, username, password);//url is
"jdbc:mysql://67.103.171.173:3306/ggripdb?autoReconnect=true"
return(connection);
} catch(Exception cnfe) {
throw new SQLException("Can't connect with: " +
driver+" to "+url);
}

This message thrown by your new SQLException doesn't match the error
message you reported in your original post. Are you sure this is the
point at which the exception is being thrown?

Also, by catching the exception and doing nothing with it, you have
obscured the cause of the exception. Your exception object is named
cnfe, so I would infer that you believe the only exception that can
occur is a ClassNotFoundException. The DriverManager.getConnection()
can throw different types of exceptions, and with different messages,
but your code is ignoring this important diagnostic information.

Regards,
Bill K.

[David Harper]

Ike wrote:

One thing that IS different on the machines I am connecting to remotely is
that on the remote machines:

set password for me@'%'=OLD_PASSWORD('mypassword');

I'm wondering if that is not screwing it here? Is there a way to use MySQL
ConnectorJ then handle the new/old password issue so that it is compatible
with php4 ? Thanks, Ike

I've been burned by this myself when connecting to MySQL servers from
Perl scripts which use out-of-date versions of the MySQL DBI module
(Perl's equivalent, very loosely, of JDBC).

However, it should not affect Java clients which use a reasonably recent
version of Connector/J.

I have a MySQL 4.1 server which has some users with old-format passwords
and some users with new-format passwords. I can successfully connect to
the server using my simple Java program as both types of user. I guess
the code inside Connector/J figures out which type of authentication to
use automatically.

David Harper
Cambridge, England