Socket Server with MySQL
RVic
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 **
Axel Schwenke
> 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.
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
RVic
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;
}
RVic
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
Axel Schwenke
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
Arne Vajhøj
> 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.
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
Arne Vajhøj
> 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.
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
Jerry Stuckle
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.
jstu...@attglobal.net
==================
Arne Vajhøj
> Arne Vajh�j wrote:
>> Axel Schwenke wrote:
>>> 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.
>>
>> 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.
>
> 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.
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
Jerry Stuckle
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.
Arne Vajhøj
> Arne Vajh�j wrote:
>> Jerry Stuckle wrote:
>>> Arne Vajh�j wrote:
>>>> Axel Schwenke wrote:
>>>>> 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.
>>>>
>>>> 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.
>>>
>>> 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.
>>
>> 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.
>
> 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.
Well - I believe that they do recommend use of connection pools.
Arne
Jerry Stuckle
Yes, they do. But the do not recommend using pools which have
connections which are unused for great lengths of time.
Roedy Green
wrote, quoted or indirectly quoted someone who said :
>java.sql.SQLException: No operations allowed after connection
>closed.Connection
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.
--
Roedy Green Canadian Mind Products
http://mindprod.com
I mean the word proof not in the sense of the lawyers, who set two half proofs equal to a whole one, but in the sense of a mathematician, where half proof = 0, and it is demanded for proof that every doubt becomes impossible.
~ Carl Friedrich Gauss
Arne Vajhøj
> connections which are unused for great lengths of time.
I am pretty sure that they recommend having a minimum number
of free connections available at all time.
Arne
Jerry Stuckle
> On 27-11-2009 22:29, Jerry Stuckle wrote:
>>> Jerry Stuckle wrote:
>>>>> Jerry Stuckle wrote:
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.