JDBC Connection Issue

[jross]

Hi All,

I'm running a simple perl script that checks if port 3306(mysql) is open
or not and display's either PASSED or FAILED

Perl Code: http://pastebin.org/108205

This small script seems to be working fine, but when I put into place
our java applications that are using mysql though the jdbc driver
started to crash (The services crash over night after a jboss restart).

I started to do some testing and made a small java class to replicate
what our java apps are doing

Java Code: http://pastebin.org/108202

Running both these scripts at the some time seemed to be ok, but after a
while the jdbc driver was complaining about

"Server configuration denies access to data source"

Output of `netstat -tnap | grep 3306` after running and crashing it a
few times: http://pastebin.org/108212

As you can see there a heap of packets queued for sending and receiving
after a crash.

The perl script is closing the socket correctly, but I'm not sure if the
jdbc driver is closing the sockets correctly??

It could be something in the java code. I did have java complaining
about the stack size saying "increase stack size with ulimit -s ". I
increased the stack size and got an error which was logged, I have
attached the log to this email.


What I'm using:

JDBC Driver: mysql-connector-java-2.0.14-bin.jar
JAVA: j2sdk1.4.2_10
Perl: v5.10.0
OS: Fedora Core 4


How I tested -

After compiling my java class I run:

while [ 0=0 ];do java -cp /path/to/jdbc_driver.jar:. test_class; sleep
3;done

Then run the perl script with:

while [ 0=0 ]; do ./perl_script.pl; sleep 2; done

Note: Running the perl script every 2 seconds or more took awhile to
crash the java class, but deceasing it speeded up the crash, especially
if you have no sleep command (it will crash straight away).

Any Help would be appreciated and I'm happy to provide any more info if
needed

Cheers

Joel.C

 

[markspace]

JDBC Driver: mysql-connector-java-2.0.14-bin.jar
JAVA: j2sdk1.4.2_10

These two are very old. Can you upgrade? Java in on version 6 now, 4
is no longer supported, and 5 is past EOL for free (unpaid) customers.
A 2.0 Connector appears to be ancient. 5 is the current version, 3 is
the earliest still available on MySQL's website.

 

[EJP]

Hi All,

I'm running a simple perl script that checks if port 3306(mysql) is open
or not and display's either PASSED or FAILED

Why? I'm not a fan of this sort of thing. It doesn't establish whether
the resource will be available *when you go to use it*; when you do go
to use it you still have to cope with all the appropriate exceptions; so
actually it is a complete waste of time.

The only valid way to test whether a resource is available for use is to
use it.

 

[Joel Ross]

These two are very old. Can you upgrade? Java in on version 6 now, 4
is no longer supported, and 5 is past EOL for free (unpaid) customers. A
2.0 Connector appears to be ancient. 5 is the current version, 3 is the
earliest still available on MySQL's website.

Unfortunately these two version are what we are using I have tried
mysql-connector 3 and I still have the same issues. We are looking into
updating java in the future but at the moment it's too much work because
of all the new changes.


Cheers

 

[Roedy Green]

Note: Running the perl script every 2 seconds or more took awhile to
crash the java class, but deceasing it speeded up the crash, especially
if you have no sleep command (it will crash straight away).

It is possibly all that is happening is you have MySQL so busy it
can't attend to Java's request.

Why do you need to run the Perl script so often?
--
Roedy Green Canadian Mind Products
http://mindprod.com

The first 90% of the code accounts for the first 90% of the development time. The remaining 10% of the code accounts for the other 90% of the development time.
~ Tom Cargill

 

[Lew]

Unfortunately these two version are what we are using I have tried
mysql-connector 3 and I still have the same issues. We are looking into
updating java in the future but at the moment it's too much work because
of all the new changes.

They aren't that new. Nearly six years in dog years is over four decades in
human years. And there aren't that many changes: get rid of "enum" in your
code and hope you aren't using the commons-lang version of that, and voilà.

That said, I also was just on a project that still uses 1.4. To their credit,
they're finally now upgrading (gradually) to the (also now obsolescent) Java 5.

The actual conversion took about a week for roughly a million lines of code.
Upgrading from Rational Application Developer 6 to version 7 took them another
two months.

 

[markspace]

Unfortunately these two version are what we are using I have tried
mysql-connector 3 and I still have the same issues. We are looking into
updating java in the future but at the moment it's too much work because
of all the new changes.

OK, fair enough. I don't have either of those versions on my system
here, so any testing I do of your program is going to use a different
runtime and a different Connect/J. If I find no issues... well, it
won't mean that much for you, will it.

Other idea: what version of MySQL are you connecting to?

OK, I notice a couple of things. One, when I first tried your program,
I was surprised that it ran, because I didn't add my own password and
user name yet. Then I noticed that you're printing only the
getMessage() of the exception, which is easy to miss, and rarely set to
anything useful. Please modify your Java program to print the entire
exception and stack trace. I made those changes below, I think you
could be missing an error trace from the Java program, just like I did.

Also, what happens if you don't run the Perl program, just the Java? If
it's Perl that causes the problem, I'd guess that the problem is in
Perl, not Java. Please try to get the Java program to fail by itself.

Test program with better error reporting below:


package mysqltest;

import java.sql.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintWriter;

class test
{

Connection conn;

public static void main( String[] args ) throws Exception
{

new test();
}

public test() throws Exception
{
PrintWriter result = null;
// File logFile = new File( "/tmp/socket/debug.log" );
result =
// new PrintWriter( new FileOutputStream(
//logFile.getName() ) );
new PrintWriter( System.out );
DriverManager.setLogWriter( result );
Class.forName( "com.mysql.jdbc.Driver" ).newInstance();
String url =

"jdbc:mysql://127.0.0.1/pos?autoReconnect=true&socketTimeout=120";
conn = DriverManager.getConnection( url, "user", "passwd" );

doSelectTest();
conn.close();

}

private void doSelectTest() throws Exception
{
System.out.println( "[OUTPUT FROM SELECT]" );
String query = "SELECT * FROM Table;";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery( query );
while( rs.next() )
{
String s = rs.getString( "column" );
System.out.println( s );
}
}
}

 

[jross]

Other idea: what version of MySQL are you connecting to?

I'm using an old version of MySQL 4.0 ( Where living in the past =) )

Also, what happens if you don't run the Perl program, just the Java? If
it's Perl that causes the problem, I'd guess that the problem is in
Perl, not Java. Please try to get the Java program to fail by itself.

The java app runs fine without perl. I might write a script in a
different language other then perl and see what it does, it could be perl

 

[jross]

It is possibly all that is happening is you have MySQL so busy it
can't attend to Java's request.

I have increased the MAX Connection variable in MySQL and it made no
difference

Why do you need to run the Perl script so often?

I don't need to run it that often it normally happens over 24/48 hour
period. I'm just running it that many times for testing purpose because
it cause the same error to occur.

Cheers

 

[jross]

The java app runs fine without perl. I might write a script in a
different language other then perl and see what it does, it could be perl

Running this command also caused the same error.

while [ 0=0 ];do nc -vvvz 127.0.0.1 3306;done

 

[Arne Vajhøj]

I'm running a simple perl script that checks if port 3306(mysql) is open
or not and display's either PASSED or FAILED

Perl Code: http://pastebin.org/108205

This small script seems to be working fine, but when I put into place
our java applications that are using mysql though the jdbc driver
started to crash (The services crash over night after a jboss restart).

I started to do some testing and made a small java class to replicate
what our java apps are doing

Java Code: http://pastebin.org/108202

Running both these scripts at the some time seemed to be ok, but after a
while the jdbc driver was complaining about

"Server configuration denies access to data source"

Are you sure that you always get the connections closed ?

The perl script is closing the socket correctly, but I'm not sure if the
jdbc driver is closing the sockets correctly??

It could be something in the java code. I did have java complaining
about the stack size saying "increase stack size with ulimit -s ". I
increased the stack size and got an error which was logged, I have
attached the log to this email.

What I'm using:

JDBC Driver: mysql-connector-java-2.0.14-bin.jar
JAVA: j2sdk1.4.2_10
Perl: v5.10.0
OS: Fedora Core 4

The log files says:

Java VM: Java HotSpot(TM) Client VM (1.3.1_18-b01 mixed mode)

Arne

 

[Arne Vajhøj]

These two are very old. Can you upgrade? Java in on version 6 now, 4 is
no longer supported, and 5 is past EOL for free (unpaid) customers. A
2.0 Connector appears to be ancient. 5 is the current version, 3 is the
earliest still available on MySQL's website.

2.0 can be found if looking for it:

http://downloads.mysql.com/archives.php?p=mysql-connector-java-2.0&v=2.0.14

But it is 8 years old.

And the page clearly states:
MySQL Server versions before MySQL 5.0 are no longer supported.

Arne

 

[Arne Vajhøj]

That said, I also was just on a project that still uses 1.4. To their
credit, they're finally now upgrading (gradually) to the (also now
obsolescent) Java 5.

The actual conversion took about a week for roughly a million lines of
code.

But how long time did it take to retest?

Arne

 

[jross]

Updating the mysql-connector to version 3 and java to version 1.5 fixed
the problem. But I need to use mysql-connector version 2 and java
version 1.4, so I gave up and will test it in a better way.

Thanks everyone for the helpful advice

Cheers

Joel

 

[Roedy Green]

I have increased the MAX Connection variable in MySQL and it made no
difference

You still could be saturating it; perhaps there is something it locks
for such a request.
--
Roedy Green Canadian Mind Products
http://mindprod.com

The first 90% of the code accounts for the first 90% of the development time. The remaining 10% of the code accounts for the other 90% of the development time.
~ Tom Cargill

 

[Lew]

But how long time did it take to retest?

The test suites ran in a few hours.

They had extensive test suites in place already. Nearly everything just
worked, so most of the stuff passed their tests and didn't require adjustment.
The biggest single problem came from code that depended on a Java 1.4 bug
that initialized classes upon reference to the 'class' literal. As I recall
(the conversion itself was a couple of years ago), the workaround was quickly
found and implemented. The bulk of the week of conversion was renaming things
that had 'enum' as part of the package name, and those were Apache Commons
Lang libraries that already had a substitute package set in place so we just
used the IDE's global search-and-replace for that.

That one bug (dependency on class initialization from the 'class' literal) was
the only serious issue, and affected only a few classes in the code base. For
the rest, we found that Java 5 supported Java 1.4 source just fine.

 

[markspace]

while [ 0=0 ];do nc -vvvz 127.0.0.1 3306;done

Glad you found something out. Can you just upgrade the Connect/J driver
with out touching the Java version? Upgrading the Connect/J driver
sounds like the easiest thing to do.

Thanks for posting that nc command btw. There's so much crazy, useful
stuff in Unix that I just don't know about most of it. I need a *nix
cook book of useful commands or something.

 

[jross]

Glad you found something out. Can you just upgrade the Connect/J driver
with out touching the Java version? Upgrading the Connect/J driver
sounds like the easiest thing to do.

Yeah I updated to MySQL 4.1, JDBC Driver 3 and JAVA 1.5 and all worked
fine. Running JDBC 3 under java 1.4 and 1.3 complained about an
exception Could not find the Class Savepoint, which must be new in 1.5,
I could work around this, but I'm going to find another way of doing it
like running the command

"mysql -Bse "use db; select COUNT(*) from table;"

and check if it returns a value

It's good to know that perl wasn't the problem.

Thanks for posting that nc command btw. There's so much crazy, useful
stuff in Unix that I just don't know about most of it. I need a *nix
cook book of useful commands or something.

No worries I got plenty of them in my pocket =)

Thanks for your time and effort

Cheers

Joel.C

 

[Arne Vajhøj]

The test suites ran in a few hours.

They had extensive test suites in place already.

Didn't they do a full test?

(unit tests is not a full test)

Arne

 

[Lew]

Didn't they do a full test?

Yes. I don't recall implying that they didn't.

(unit tests is not a full test)

I did not say unit-test suites.

They had extensive test suites in place already. Nor did they stop testing
after just a few runs.

Most of the code was not changed at all; only a few classes were affected by
the conversion.

As a matter of fact there is a regular and ongoing practice of testing at
various scale on that project, including performance tests. While I have
issues with a number of aspects of the coding and testing standards there, the
tests were sufficient to reveal potential difficulties with the conversion to
Java 5. In fact they did uncover the only issue that actually occurred, a
reliance on references to the 'class' literal to initialize classes, and that
was in the Apache commons-lang library, not the project-specific code. Since
then, the system has gone into production with no further difficulties.

The fact is that the conversion from Java 1.4 to 5 on that project was fast,
about a week for about a million lines of Java code, uncovering only the issue
in the third-party library. They got around that by using Java enums in place
of the commons-lang version, and using a few hacks to communicate values
between the Java 5 servers and those still running Java 1.4 and the
commons-lang library.

I'm really not sure where you're going with your questions and comments, Arne.
I get the impression you're trying to make out that upgrading from Java 1.4 to
Java 5 is harder than it really is.