Bom dia pessoal,
Introdução:
Estou com um problema de TimeOut com conexão ao MySQL. De primeira achei que o problema seria por causa da app (app.jar) estar em um local e o banco em outro local (servidor) e dai a rede ocilar por muito tempo e fechar a conexão, mas ao testar na máquina local também deu o mesmo erro.
Ambiente:
- Sistema Desktop
- MySQL 5.0.67
- Java 6 Update 12
- Tomcat 6.0.18 (Esta instalado na máquina, pois tem parte do sistema online, mas ele não foi usado)
Como é o sistema:
É um sistema de Ponto onde a pessoa passa o cartão, é capturado o ID da pessoa, com isto é inserido no banco a data, hora e quem é a pessoa, seguido de uma consulta para buscar os movimentos desta pessoa no dia corrente.
Como ocorre a situação:
No final do expediante, por volta das 18:00 horas é batido o último ponto e o computador continua ligado com o programa aberto, no outro dia pela manhã, por volta de 8:30 chega a primeira pessoa na empresa e tenta bater seu ponto, no qual não é executado a ação ocorrendo a exceção.
Código da conexão:
[code]package com.washingtonbotelho.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
final public class Conexao {
private static Conexao conexao = null;
private static Connection conn;
private Conexao() {
try {
Class.forName(“com.mysql.jdbc.Driver”);
conn = DriverManager.getConnection(“jdbc:mysql://10.1.1.3/peg”, “root”, “root”); // 10.1.1.3
conn.setAutoCommit(false);
} catch (Exception e) {
System.out.println("[Conexao.conectar] Erro: " + e.toString());
System.exit(0);
}
}
public static void commit(boolean boo) {
try {
if (boo) {
Conexao.getConn().commit();
} else {
Conexao.getConn().rollback();
}
} catch (SQLException sql) {
System.out.println("[Conexao.commit] Erro: " + sql.toString());
}
}
public synchronized static Connection getConn() {
if (conexao == null) {
conexao = new Conexao();
}
return conn;
}
public static void desconectar() throws SQLException {
conn.close();
}
}[/code]
Super DAO (Todos DAOs herdam ela para pegar a conexão):
[code]package com.washingtonbotelho.dao;
import com.washingtonbotelho.util.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
public abstract class Dao {
protected Connection conn = Conexao.getConn();
protected PreparedStatement ps;
protected ResultSet rs;
public abstract void inserir(Object obj) throws SQLException;
public abstract void alterar(Object obj) throws SQLException;
public abstract Object consultar(Object obj) throws SQLException;
public abstract void remover(Object obj) throws SQLException;
public abstract LinkedList<?> carregarLista() throws SQLException;
}[/code]
Código Inserir (Controle):
public boolean inserir(Movimento movimento) { // [OK]
try {
movDao.inserir(movimento);
} catch (SQLException sql) {
System.out.println("[MovimentoApl.inserir] Erro: " + sql.toString());
return false;
}
Código Inserir (DAO):
[code]@Override
public void inserir(Object obj) throws SQLException { // [OK]
movimento = (Movimento) obj;
ps = conn.prepareStatement(
"INSERT INTO Movimento (idFunc, data, hora) " +
"VALUES (?, ?, ?)");
ps.setInt(1, movimento.getIdFunc());
ps.setDate(2, new java.sql.Date(movimento.getData().getTime()));
ps.setTime(3, movimento.getHora());
ps.executeUpdate();
ps.close();
}[/code]
Erro na conexão:
System.setOut(new PrintStream("defaultLog1.log"));
[code][MovimentoApl.inserir] Erro: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 31 ms ago.
[Conexao.commit] Erro: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.
[MovimentoApl.inserir] Erro: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:
** BEGIN NESTED EXCEPTION **
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: Communications link failure
Last packet sent to the server was 31 ms ago.
STACKTRACE:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 31 ms ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
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:3134)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1818)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2022)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1940)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1925)
at com.washingtonbotelho.dao.MovimentoDao.inserir(MovimentoDao.java:159)
at com.washingtonbotelho.controle.MovimentoApl.inserir(MovimentoApl.java:113)
at com.washingtonbotelho.controle.FuncionalFacade.inserir(FuncionalFacade.java:104)
at com.washingtonbotelho.visao.RegistroUi.gravar(RegistroUi.java:82)
at com.washingtonbotelho.visao.RegistroUi.gravarF(RegistroUi.java:403)
at com.washingtonbotelho.visao.RegistroUi.access$000(RegistroUi.java:19)
at com.washingtonbotelho.visao.RegistroUi$1.focusGained(RegistroUi.java:218)
at java.awt.AWTEventMulticaster.focusGained(Unknown Source)
at java.awt.Component.processFocusEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.KeyboardFocusManager.redispatchEvent(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.dispatchEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.net.SocketException: Software caused connection abort: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3119)
… 34 more
** END NESTED EXCEPTION **
[Conexao.commit] Erro: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.
[/code]
Erro do Inserir:
[code]com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:
** BEGIN NESTED EXCEPTION **
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: Communications link failure
Last packet sent to the server was 31 ms ago.
STACKTRACE:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 31 ms ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
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:3134)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1818)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2022)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1940)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1925)
at com.washingtonbotelho.dao.MovimentoDao.inserir(MovimentoDao.java:159)
at com.washingtonbotelho.controle.MovimentoApl.inserir(MovimentoApl.java:113)
at com.washingtonbotelho.controle.FuncionalFacade.inserir(FuncionalFacade.java:104)
at com.washingtonbotelho.visao.RegistroUi.gravar(RegistroUi.java:82)
at com.washingtonbotelho.visao.RegistroUi.gravarF(RegistroUi.java:403)
at com.washingtonbotelho.visao.RegistroUi.access$000(RegistroUi.java:19)
at com.washingtonbotelho.visao.RegistroUi$1.focusGained(RegistroUi.java:218)
at java.awt.AWTEventMulticaster.focusGained(Unknown Source)
at java.awt.Component.processFocusEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.KeyboardFocusManager.redispatchEvent(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.dispatchEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.net.SocketException: Software caused connection abort: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(Unknown Source)
at java.net.SocketOutputStream.write(Unknown Source)
at java.io.BufferedOutputStream.flushBuffer(Unknown Source)
at java.io.BufferedOutputStream.flush(Unknown Source)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3119)
… 34 more
** END NESTED EXCEPTION **
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:985)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1113)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4038)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4004)
at com.washingtonbotelho.dao.MovimentoDao.inserir(MovimentoDao.java:152)
at com.washingtonbotelho.controle.MovimentoApl.inserir(MovimentoApl.java:113)
at com.washingtonbotelho.controle.FuncionalFacade.inserir(FuncionalFacade.java:104)
at com.washingtonbotelho.visao.RegistroUi.gravar(RegistroUi.java:82)
at com.washingtonbotelho.visao.RegistroUi.gravarF(RegistroUi.java:403)
at com.washingtonbotelho.visao.RegistroUi.access$000(RegistroUi.java:19)
at com.washingtonbotelho.visao.RegistroUi$1.focusGained(RegistroUi.java:218)
at java.awt.AWTEventMulticaster.focusGained(Unknown Source)
at java.awt.Component.processFocusEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.KeyboardFocusManager.redispatchEvent(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.dispatchEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
[/code]
Erro do Commit:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:985)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
at com.mysql.jdbc.ConnectionImpl.getMutex(ConnectionImpl.java:3015)
at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4526)
at com.washingtonbotelho.util.Conexao.commit(Conexao.java:37)
at com.washingtonbotelho.controle.FuncionalFacade.inserir(FuncionalFacade.java:110)
at com.washingtonbotelho.visao.RegistroUi.gravar(RegistroUi.java:82)
at com.washingtonbotelho.visao.RegistroUi.gravarF(RegistroUi.java:403)
at com.washingtonbotelho.visao.RegistroUi.access$000(RegistroUi.java:19)
at com.washingtonbotelho.visao.RegistroUi$1.focusGained(RegistroUi.java:218)
at java.awt.AWTEventMulticaster.focusGained(Unknown Source)
at java.awt.Component.processFocusEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.KeyboardFocusManager.redispatchEvent(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(Unknown Source)
at java.awt.DefaultKeyboardFocusManager.dispatchEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Bem, é isso ai pessoal.
Já vi muitas dúvidas ai pela net relacionado ou exatamente iguail, mas nenhuma foi solucionado ou ao menos se foi não foi publicada.
Agradeço desde já aqueles que ao menos tentarem ajudar.
Obrigado.