# Probleme mit Datenbankanbindung



## Gast2 (12. Sep 2009)

Hi,
ich habe in meiner Anwendung schon seit längerem Probleme mit meiner Datenbankanbindung. 
Meine Anwendung ist ne etwas komplexere Swing Applikation in der einige Threads laufen, es sind also immer mehrere Datenbankverbindungen offen. Als Treiber verwende ich Connection/J mit Commons-DBCP als Connection Pool.
Jetzt bekomme ich abundzu folgende Fehlermeldungen:


```
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
The last packet sent successfully to the server was 439 milliseconds ago, 
which  is longer than the server configured value of 'wait_timeout'. You should
consider either expiring and/or testing connection validity before use in your
application, increasing the server configured values for client timeouts, or using
the Connector/J connection property 'autoReconnect=true' to avoid this problem.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3270)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1932)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
        at cnc.database.DbConnector.executeQuery(DbConnector.java:131)
        at cnc.gui.ProgramsDialog.updateProgramsTable(ProgramsDialog.java:741)
        at cnc.gui.ProgramsDialog.updateProgramsTable(ProgramsDialog.java:703)
        at cnc.gui.ProgramsDialog.formComponentShown(ProgramsDialog.java:589)
        at cnc.gui.ProgramsDialog.access$400(ProgramsDialog.java:56)
        at cnc.gui.ProgramsDialog$5.componentShown(ProgramsDialog.java:250)
        at java.awt.AWTEventMulticaster.componentShown(AWTEventMulticaster.java:162)
        at java.awt.AWTEventMulticaster.componentShown(AWTEventMulticaster.java:162)
        at java.awt.AWTEventMulticaster.componentShown(AWTEventMulticaster.java:162)
        at java.awt.Component.processComponentEvent(Component.java:6095)
        at java.awt.Component.processEvent(Component.java:6043)
        at java.awt.Container.processEvent(Container.java:2041)
        at java.awt.Component.dispatchEventImpl(Component.java:4630)
        at java.awt.Container.dispatchEventImpl(Container.java:2099)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Caused by: java.net.SocketException: Software caused connection abort: socket write error
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
        at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
        at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3251)
        ... 28 more
2009-09-12 00:20:02,846 [AWT-EventQueue-0] ERROR: SQLQuery konnte nicht ausgeführt werden
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet ...
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3270)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1932)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
        at cnc.database.DbConnector.executeQuery(DbConnector.java:131)
        at cnc.database.ProgramRelationManager.getCompanyList(ProgramRelationManager.java:160)
        at cnc.gui.ProgramsDialog.updateCompanyDropdown(ProgramsDialog.java:659)
        at cnc.gui.ProgramsDialog.formComponentShown(ProgramsDialog.java:590)
        at cnc.gui.ProgramsDialog.access$400(ProgramsDialog.java:56)
        at cnc.gui.ProgramsDialog$5.componentShown(ProgramsDialog.java:250)
        at java.awt.AWTEventMulticaster.componentShown(AWTEventMulticaster.java:162)
        at java.awt.AWTEventMulticaster.componentShown(AWTEventMulticaster.java:162)
        at java.awt.AWTEventMulticaster.componentShown(AWTEventMulticaster.java:162)
        at java.awt.Component.processComponentEvent(Component.java:6095)
        at java.awt.Component.processEvent(Component.java:6043)
        at java.awt.Container.processEvent(Container.java:2041)
        at java.awt.Component.dispatchEventImpl(Component.java:4630)
        at java.awt.Container.dispatchEventImpl(Container.java:2099)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
Caused by: java.net.SocketException: Software caused connection abort: socket write error
        at java.net.SocketOutputStream.socketWrite0(Native Method)
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
        at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
        at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
        at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3251)
        ... 28 more
```

Ich habe schon vieles probiert... auch wie in der Fehlermeldung angeraten den Parameter autoReconnect=true angehängt.
Habt ihr irgendwelche Ideen was ich noch probieren könnte um das Problem in den Griff zu bekommen?

Gruß,
Eike

EDIT:
Hier noch eine ähnliche Fehlermeldung die ich grad noch gesehen habe: (das Fettgedruckte könnte interessant sein, weiß aber leider nicht damit anzufangen)


```
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
        at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
        at cnc.database.DbConnector.executeQuery(DbConnector.java:129)
        at cnc.gui.ProgramsDialog.updateProgramsTable(ProgramsDialog.java:741)
        at cnc.gui.ProgramsDialog.updateProgramsTable(ProgramsDialog.java:703)
        at cnc.gui.ProgramsDialog.formComponentShown(ProgramsDialog.java:589)
        at cnc.gui.ProgramsDialog.access$400(ProgramsDialog.java:56)
        at cnc.gui.ProgramsDialog$5.componentShown(ProgramsDialog.java:250)
        at java.awt.AWTEventMulticaster.componentShown(AWTEventMulticaster.java:162)
        at java.awt.AWTEventMulticaster.componentShown(AWTEventMulticaster.java:162)
        at java.awt.AWTEventMulticaster.componentShown(AWTEventMulticaster.java:162)
        at java.awt.Component.processComponentEvent(Component.java:6095)
        at java.awt.Component.processEvent(Component.java:6043)
        at java.awt.Container.processEvent(Container.java:2041)
        at java.awt.Component.dispatchEventImpl(Component.java:4630)
        at java.awt.Container.dispatchEventImpl(Container.java:2099)
        at java.awt.Component.dispatchEvent(Component.java:4460)
        at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
        at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)
[B]Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.[/B]
        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2455)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2906)
        ... 30 more
```


----------



## Gast2 (14. Sep 2009)

So langsam bin ich mit meinem Latein am Ende.
Nun kommt es auch noch vor, dass z.b.

```
myCon.createStatement();
```
abundzu null als Ergebnis liefert... Irgendwas ist da ziemlich im argen :rtfm:
Habe schon den neuesten mysql-connector runtergeladen. Brachte jedoch auch keine Verbesserung.

Hat vielleicht jemand einen Vorschlag für ne neue Treiber + pool combi, so dass ich das ganze zeugs mal austauschen kann?


----------



## masta // thomas (14. Sep 2009)

Hast du mal das MySQL wait_timeout überprüft (SELECT @@global.wait_timeout bzw. SELECT @@session.wait_timeout)? Wenn du die Berechtigungen hast, könntest du ggf. den Wert höher setzen. Falls du diese Berechtigung nicht hast, schau dir die Werte für interactive_timeout an. Falls diese höher sind, könntest du über den JDBC-URL-Parameter interactiveClient=true den "Verbindungs-Timeout gemäß INTERACTIVE_TIMEOUT anstelle von WAIT_TIMEOUT" einstellen.

Viel Erfolg!


----------



## Gast2 (15. Sep 2009)

Ich hatte diese 2 Werte in meiner my.ini auf jeweils 60 Sekunden gesetzt. Habe das jetzt wieder entfernt und jetzt steht es auf 28800 Sekunden.

Ich denke dass sollte schonmal das Problem lösen, dass die Verbindungen nach 60 Sekunden von selbst abgebaut werden, da hätte ich auch selbst drauf kommen können 

Schonmal vielen Dank für den Tipp, werde mal schauen ob das ganze jetzt etwas stabiler läuft


----------

