Socket Server with MySQL

R

RVic

I have a Java socket server that uses MySQL (5.0.27-community) as its
backend. I've noticed if the server sits idle for a lengthy period of
time (I have duplicated the results to generate a stack trace by not
making a socket connection for 48 hours) it faults out with the stack
trace listed below. I believe it is likely a mysql configuration
problem and/or a java ServerSocket or Socket issue.

My hope is someone can look at this stack trace or my description of
the problem here, and I might glean insight into this problem. Thank
you. -R Vince


UPDATE CHANNELS SET associatecount=1 WHERE ID=1;
com.mysql.jdbc.CommunicationsException: Communications link failure
due to under
lying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
2351)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.execute(Statement.java:529)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:619)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:612)
at GG.UpxPanel.listOutToClientBrowswers(UpxPanel.java:206)
at GG.UpxPanel.transmitList(UpxPanel.java:265)
at GG.UpxPanel.transmitList(UpxPanel.java:249)
at GG.UpxPanel.hit(UpxPanel.java:575)
at GG.UpxPanel.hear(UpxPanel.java:242)
at GG.UpxFrontPanel.say(UpxFrontPanel.java:1134)
at GG.UpxFrontPanel.perform(UpxFrontPanel.java:830)
at GG.UpxFrontPanel.incoming(UpxFrontPanel.java:697)
at GG.GGServer$Server2Connection.run(GGServer.java:262)
at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **



Last packet sent to the server was 47 ms ago.
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
2563)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.execute(Statement.java:529)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:619)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:612)
at GG.UpxPanel.listOutToClientBrowswers(UpxPanel.java:206)
at GG.UpxPanel.transmitList(UpxPanel.java:265)
at GG.UpxPanel.transmitList(UpxPanel.java:249)
at GG.UpxPanel.hit(UpxPanel.java:575)
at GG.UpxPanel.hear(UpxPanel.java:242)
at GG.UpxFrontPanel.say(UpxFrontPanel.java:1134)
at GG.UpxFrontPanel.perform(UpxFrontPanel.java:830)
at GG.UpxFrontPanel.incoming(UpxFrontPanel.java:697)
at GG.GGServer$Server2Connection.run(GGServer.java:262)
at java.lang.Thread.run(Unknown Source)
java.sql.SQLException: No operations allowed after connection
closed.Connection
was implicitly closed due to underlying exception/error:


** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
2351)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.execute(Statement.java:529)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:619)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:612)
at GG.UpxPanel.listOutToClientBrowswers(UpxPanel.java:206)
at GG.UpxPanel.transmitList(UpxPanel.java:265)
at GG.UpxPanel.transmitList(UpxPanel.java:249)
at GG.UpxPanel.hit(UpxPanel.java:575)
at GG.UpxPanel.hear(UpxPanel.java:242)
at GG.UpxFrontPanel.say(UpxFrontPanel.java:1134)
at GG.UpxFrontPanel.perform(UpxFrontPanel.java:830)
at GG.UpxFrontPanel.incoming(UpxFrontPanel.java:697)
at GG.GGServer$Server2Connection.run(GGServer.java:262)
at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **



Last packet sent to the server was 47 ms ago.

STACKTRACE:

com.mysql.jdbc.CommunicationsException: Communications link failure
due to under
lying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
2351)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.execute(Statement.java:529)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:619)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:612)
at GG.UpxPanel.listOutToClientBrowswers(UpxPanel.java:206)
at GG.UpxPanel.transmitList(UpxPanel.java:265)
at GG.UpxPanel.transmitList(UpxPanel.java:249)
at GG.UpxPanel.hit(UpxPanel.java:575)
at GG.UpxPanel.hear(UpxPanel.java:242)
at GG.UpxFrontPanel.say(UpxFrontPanel.java:1134)
at GG.UpxFrontPanel.perform(UpxFrontPanel.java:830)
at GG.UpxFrontPanel.incoming(UpxFrontPanel.java:697)
at GG.GGServer$Server2Connection.run(GGServer.java:262)
at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **



Last packet sent to the server was 47 ms ago.
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
2563)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.execute(Statement.java:529)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:619)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:612)
at GG.UpxPanel.listOutToClientBrowswers(UpxPanel.java:206)
at GG.UpxPanel.transmitList(UpxPanel.java:265)
at GG.UpxPanel.transmitList(UpxPanel.java:249)
at GG.UpxPanel.hit(UpxPanel.java:575)
at GG.UpxPanel.hear(UpxPanel.java:242)
at GG.UpxFrontPanel.say(UpxFrontPanel.java:1134)
at GG.UpxFrontPanel.perform(UpxFrontPanel.java:830)
at GG.UpxFrontPanel.incoming(UpxFrontPanel.java:697)
at GG.GGServer$Server2Connection.run(GGServer.java:262)
at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **


at com.mysql.jdbc.Connection.checkClosed(Connection.java:1842)
at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:
4884)
at GG.UpxFrontPanel.transactQplayer(UpxFrontPanel.java:466)
at GG.UpxPanel.serializeList(UpxPanel.java:1334)
at GG.UpxPanel.transmitList(UpxPanel.java:268)
at GG.UpxPanel.transmitList(UpxPanel.java:249)
at GG.UpxPanel.hit(UpxPanel.java:575)
at GG.UpxPanel.hear(UpxPanel.java:242)
at GG.UpxFrontPanel.say(UpxFrontPanel.java:1134)
at GG.UpxFrontPanel.perform(UpxFrontPanel.java:830)
at GG.UpxFrontPanel.incoming(UpxFrontPanel.java:697)
at GG.GGServer$Server2Connection.run(GGServer.java:262)
at java.lang.Thread.run(Unknown Source)
No operations allowed after connection closed.Connection was
implicitly closed d
ue to underlying exception/error:


** BEGIN NESTED EXCEPTION **

com.mysql.jdbc.CommunicationsException
MESSAGE: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
2351)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.execute(Statement.java:529)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:619)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:612)
at GG.UpxPanel.listOutToClientBrowswers(UpxPanel.java:206)
at GG.UpxPanel.transmitList(UpxPanel.java:265)
at GG.UpxPanel.transmitList(UpxPanel.java:249)
at GG.UpxPanel.hit(UpxPanel.java:575)
at GG.UpxPanel.hear(UpxPanel.java:242)
at GG.UpxFrontPanel.say(UpxFrontPanel.java:1134)
at GG.UpxFrontPanel.perform(UpxFrontPanel.java:830)
at GG.UpxFrontPanel.incoming(UpxFrontPanel.java:697)
at GG.GGServer$Server2Connection.run(GGServer.java:262)
at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **



Last packet sent to the server was 47 ms ago.

STACKTRACE:

com.mysql.jdbc.CommunicationsException: Communications link failure
due to under
lying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
2351)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.execute(Statement.java:529)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:619)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:612)
at GG.UpxPanel.listOutToClientBrowswers(UpxPanel.java:206)
at GG.UpxPanel.transmitList(UpxPanel.java:265)
at GG.UpxPanel.transmitList(UpxPanel.java:249)
at GG.UpxPanel.hit(UpxPanel.java:575)
at GG.UpxPanel.hear(UpxPanel.java:242)
at GG.UpxFrontPanel.say(UpxFrontPanel.java:1134)
at GG.UpxFrontPanel.perform(UpxFrontPanel.java:830)
at GG.UpxFrontPanel.incoming(UpxFrontPanel.java:697)
at GG.GGServer$Server2Connection.run(GGServer.java:262)
at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **



Last packet sent to the server was 47 ms ago.
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:
2563)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
at com.mysql.jdbc.Statement.execute(Statement.java:529)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:619)
at GG.UpxFrontPanel.writeItToDB(UpxFrontPanel.java:612)
at GG.UpxPanel.listOutToClientBrowswers(UpxPanel.java:206)
at GG.UpxPanel.transmitList(UpxPanel.java:265)
at GG.UpxPanel.transmitList(UpxPanel.java:249)
at GG.UpxPanel.hit(UpxPanel.java:575)
at GG.UpxPanel.hear(UpxPanel.java:242)
at GG.UpxFrontPanel.say(UpxFrontPanel.java:1134)
at GG.UpxFrontPanel.perform(UpxFrontPanel.java:830)
at GG.UpxFrontPanel.incoming(UpxFrontPanel.java:697)
at GG.GGServer$Server2Connection.run(GGServer.java:262)
at java.lang.Thread.run(Unknown Source)


** END NESTED EXCEPTION **
 
A

Axel Schwenke

RVic said:
I have a Java socket server that uses MySQL (5.0.27-community) as its
backend. I've noticed if the server sits idle for a lengthy period of
time (I have duplicated the results to generate a stack trace by not
making a socket connection for 48 hours) it faults out with the stack
trace listed below. I believe it is likely a mysql configuration
problem and/or a java ServerSocket or Socket issue.
Click to expand...

Looks like you run into wait_timeout. Idle client connections steal
resources, therefore the MySQL server closes them after a timeout
(default is 8 hours).

You should not keep a MySQL connection open if you don't need it.
Unlike other DBMS, MySQL is pretty fast on opening a connection.

Also there is the ping() method for a MySQL connection object. This
method checks if the connection is still alive and (if enabled)
reconnects if necessary. Many connection pools call ping() before
they hand a connection from the pool to the caller.


XL
 
R

RVic

Axel,

Thanks but I don't think that is the problem. You see, it is throwing
a java.io.EOFException. I think I have a connection -- before
processing each message, I call the method below, which either returns
a Connection, or reestablishes the pool:

public java.sql.Connection getacon() {
if (ggserver.connectionPool != null) {
try {
conn = ggserver.connectionPool.getConnection();
} catch (SQLException sqlx) {

}
}
if (conn == null) {// something happened that may have killed the
pool!
try {
ggserver.connectionPool.closeAllConnections();
ggserver.setupConnectionPool(ggserver.driver, ggserver.url
+ ggserver.dbname, ggserver.dbuser,
ggserver.dbpassword);
} catch (Exception dontstop) {

}
try {
conn = ggserver.connectionPool.getConnection();
} catch (SQLException sqly) {

}
}
return conn;
}
 
R

RVic

Oh, wait ...I see what you are saying. My pool which is returning the
connections doesn;t know that MySQl has terminated it.

I can't seem to encounter any examples for the syntax of pinging a
mysql connection -- can you point me to one? Thanks, R. Vince
 
A

Axel Schwenke

RVic said:
Oh, wait ...I see what you are saying. My pool which is returning the
connections doesn;t know that MySQl has terminated it.

I can't seem to encounter any examples for the syntax of pinging a
mysql connection -- can you point me to one? Thanks, R. Vince
Click to expand...

The method is named ping(). For the auto-reconnect property etc see:

http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

scroll down to "High Availability and Clustering"


XL
 
A

Arne Vajhøj

RVic said:
I have a Java socket server that uses MySQL (5.0.27-community) as its
backend. I've noticed if the server sits idle for a lengthy period of
time (I have duplicated the results to generate a stack trace by not
making a socket connection for 48 hours) it faults out with the stack
trace listed below. I believe it is likely a mysql configuration
problem and/or a java ServerSocket or Socket issue.
Click to expand...

From the practical perspective then I think that:
* if you do not use a connection pool, then you should
send a SELECT 1 every hour to keep things alive
* if you do use a connection pool, then you should
use one capable of verifying connections and return
a valid to you (and if your connection pool does not
provide that, then find one that does)

Arne
 
A

Arne Vajhøj

Axel said:
You should not keep a MySQL connection open if you don't need it.
Unlike other DBMS, MySQL is pretty fast on opening a connection.
Click to expand...

That will make the code work well with MySQL but bad with
other databases. If the intent is to write database independent
code, then it is not an optimal solution.

Arne
 
J

Jerry Stuckle

Arne said:
That will make the code work well with MySQL but bad with
other databases. If the intent is to write database independent
code, then it is not an optimal solution.

Arne
Click to expand...

Not necessarily. It's also a bad idea to leave unused connections open
for hours on Oracle, SQL Server and DB2.

If you're using the connections, there is no problem keeping them open
in MySQL or any other database. But if you're not using the
connections, you should NOT leave them open in any database I'm familiar
with.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
(e-mail address removed)
==================
 
A

Arne Vajhøj

Jerry said:
Not necessarily. It's also a bad idea to leave unused connections open
for hours on Oracle, SQL Server and DB2.

If you're using the connections, there is no problem keeping them open
in MySQL or any other database. But if you're not using the
connections, you should NOT leave them open in any database I'm familiar
with.
Click to expand...

This is from the MySQL perspective.

From the application perspective one should close the connection,
but that does release it back to the connection pool - the connection
pool keep the connection open in the database.

Arne
 
J

Jerry Stuckle

Arne said:
This is from the MySQL perspective.

From the application perspective one should close the connection,
but that does release it back to the connection pool - the connection
pool keep the connection open in the database.

Arne
Click to expand...

It is also from the perspective of Oracle, SQL Server and DB2. All of
them recommend AGAINST keeping pools of unused connections open for
extended periods. The needlessly use system resources which could be
used for other things - and can slow down the entire system.

And FYI - MySQL is NOT the first SQL database I ever dealt with. That
was DB2, back in the 1980's, when I worked for IBM.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
(e-mail address removed)
==================
 
A

Arne Vajhøj

Jerry said:
It is also from the perspective of Oracle, SQL Server and DB2. All of
them recommend AGAINST keeping pools of unused connections open for
extended periods. The needlessly use system resources which could be
used for other things - and can slow down the entire system.
Click to expand...

Well - I believe that they do recommend use of connection pools.

Arne
 
J

Jerry Stuckle

Arne said:
Well - I believe that they do recommend use of connection pools.

Arne
Click to expand...

Yes, they do. But the do not recommend using pools which have
connections which are unused for great lengths of time.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
(e-mail address removed)
==================
 
R

Roedy Green

java.sql.SQLException: No operations allowed after connection
closed.Connection
Click to expand...

This looks like the crucial part of the error message. You used a
connection after you closed it. Perhaps you reopened using the same
variable but the reopen failed and you used it anyway.
 
A

Arne Vajhøj

Yes, they do. But the do not recommend using pools which have
connections which are unused for great lengths of time.
Click to expand...

I am pretty sure that they recommend having a minimum number
of free connections available at all time.

Arne
 
J

Jerry Stuckle

Arne said:
I am pretty sure that they recommend having a minimum number
of free connections available at all time.

Arne
Click to expand...

Which means if you average 2 connections, but once per month you require
100 connections for 5 seconds, you need to keep over 100 connections
available at all times. This is a huge waste of system resources for
the majority of the month. This can slow the entire system down.

It's why they don't recommend using pools which have connections which
are unused for great lengths of time.

If you are going to make a blanket statement, a better one would be to
NOT use connection pools - unless absolutely needed, which is what is
recommended by the RDBMS manufacturers. And they know better than
anyone else what is best for their code.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
(e-mail address removed)
==================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MySql Data Truncation 3
db server connection timeout -> error 0
Strange problem with Hibernate pool connection 1
JSP, Connecting to SQL throws exception 4
java.net.SocketException: Connection reset 0
A JDBC Question --- Deadloack 7
Roller problems - EOFExceptions 1
InnoDb Transaction timeout 0

Members online

Total: 68 (members: 1, guests: 67)
Robots: 474

Forum statistics

Threads
473,995
Messages
2,570,226
Members
46,816
Latest member
nipsseyhussle

Latest Threads

Top