c3p0 cria novas conexões até estourar de conexões do mysql

What is classpath? Membro desde: 20/08/2004 16:06:07 Mensagens: 9 Offline

Srs, pelo que vi o meu c3p0 não está configurado corretamente, pois ao abrir o meu sistema, jsf + richfaces + jpa + mysql eu acompanho os processos do mysql no administrador e a medida que eu logo novamente ele nunca encerra as conexões antiga ele sempre cria novas. O número de conexões novas é dado pelo min_size configurado no c3p0 do persistence no caso 2, ou seja, sempre que me logo ele adiciona 2 novas conexões e por ai nunca obedecendo o limite de 20 que eu coloquei. Ai sempre ao alcançar 100 conexões o mysql para.

Seguem os arquivo abaixo para verificação.

Valeu pela ajuda!

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0"
	xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">

<persistence-unit name="crateus" transaction-type="RESOURCE_LOCAL">
		<provider>org.hibernate.ejb.HibernatePersistence</provider>
		<mapping-file>META-INF/consultas.xml</mapping-file>
		<properties>
			<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
			<property name="hibernate.connection.username" value="crateus" />
			<property name="hibernate.connection.password" value="123456" />
			<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/crateus" />
			<property name="hibernate.show_sql" value="false" />
			<property name="hibernate.format_sql" value="true" />
			<property name="hibernate.hbm2ddl.auto" value="update" />
			<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />									
			<property name="hibernate.connection.autoReconnect" value="true" />
			<property name="hibernate.connection.autoReconnectForPools" value="true"/>			
			<property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" />		
			<property name="hibernate.c3p0.min_size" value="2"/>
			<property name="hibernate.c3p0.min_size" value="2"/>						 
			<property name="hibernate.c3p0.timeout" value="100"/>						 
			<property name="hibernate.c3p0.idle_test_period" value="150"/>		
		</properties>	
	</persistence-unit>

</persistence>

arquivo: log4j.properties - esse log4j eu peguei da net, não sei como é o funcionamento dele!

log do console:

08/02/2012 10:37:03 org.apache.catalina.core.AprLifecycleListener init
INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Program Files\Java\jre6\bin;.;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Program Files (x86)\PC Connectivity Solution\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Dell\DW WLAN Card;C:\Program Files\WIDCOMM\Bluetooth Software\;C:\Program Files\WIDCOMM\Bluetooth Software\syswow64;
08/02/2012 10:37:03 org.apache.tomcat.util.digester.SetPropertiesRule begin
AVISO: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:prefeitura' did not find a matching property.
08/02/2012 10:37:03 org.apache.coyote.http11.Http11Protocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
08/02/2012 10:37:03 org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 521 ms
08/02/2012 10:37:03 org.apache.catalina.core.StandardService start
INFO: Starting service Catalina
08/02/2012 10:37:03 org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/6.0.26
log4j:WARN No appenders could be found for logger (org.apache.myfaces.webapp.StartupServletContextListener).
log4j:WARN Please initialize the log4j system properly.
08/02/2012 10:37:07 org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
08/02/2012 10:37:07 org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
08/02/2012 10:37:07 org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/30  config=null
08/02/2012 10:37:07 org.apache.catalina.startup.Catalina start
INFO: Server startup in 3403 ms

Classe usuarioDAO

package br.com.azurium.prefeitura.infra.banco;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import br.com.azurium.prefeitura.comum.entidade.Usuario;
import br.com.azurium.prefeitura.comum.excecao.InfraException;

public class UsuarioDAO extends DAO<Usuario> {
	
	public UsuarioDAO() {
		super(Usuario.class);
	}
	
	public Usuario autenticacao(Usuario usuario) throws InfraException {		
		EntityManager gerente = null;
		Usuario u = new Usuario();
		
		try {
			gerente = fabrica.createEntityManager();
			String jpql = "select u from Usuario u where u.login = '" + usuario.getLogin()
					+ "' and u.senha = '" + usuario.getSenha().trim()
					+ "' and u.status = 'ATIVO'";			
			Query consulta = gerente.createQuery(jpql);
			u = (Usuario) consulta.getSingleResult();
			return u;
		} catch (Exception e) {
			throw new InfraException(e);
		} finally {
			if (gerente != null && gerente.isOpen()) {
				gerente.close();				
			}
		}
	}

public Usuario verificaLogin(String login) throws InfraException {
		EntityManager gerente = null;
		try {
			gerente = fabrica.createEntityManager();
			String jpql = "select u from Usuario u where u.login = '" + login
					+ "'";
			Query consulta = gerente.createQuery(jpql);
			consulta.setMaxResults(1);
			return (Usuario) consulta.getSingleResult();
		} catch (Exception e) {
			return null;
		} finally {
			if (gerente != null && gerente.isOpen()) {
				gerente.close();
			}
		}
	}
	
	
}

What is classpath? Membro desde: 20/08/2004 16:06:07 Mensagens: 9 Offline

Adicionar a classe que faz a autenticação do usuario e o coloca em sessão:

public void autenticacao(Usuario usuario) throws NegocioException {

HttpServletRequest request;
		Usuario u = new Usuario();
		
		try {
			//usuario.setSenha(criptografia(usuario.getSenha()));
			u = dao.autenticacao(usuario);
			
			request = (HttpServletRequest) FacesContext.getCurrentInstance().getExternalContext().getRequest();
			
			UsuarioPermissaoService usuarioPermissaoService = new UsuarioPermissaoService();		
			request.getSession().setAttribute("permissao", usuarioPermissaoService.obterPermissoes(u));

request.getSession().setAttribute("perfil", u.getPerfil());
			request.getSession().setAttribute("msg", "");
			
			request.getSession().setAttribute("usuario", u);			
			request.getSession().setAttribute("logado", u.getLogin());
			request.getSession().setAttribute("nome", u.getNome());			
			
			DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
			String dataAcesso = dateFormat.format(u.getUltimoAcesso());

dateFormat = new SimpleDateFormat("HH:mm");
			dataAcesso = dataAcesso + " às "
					+ dateFormat.format(u.getUltimoAcesso()) + "h.";
			request.getSession().setAttribute("acesso", dataAcesso);

u.setUltimoAcesso(new Date());
			dao.alterar(u);

} catch (InfraException e) {
			e.printStackTrace();
			throw new NegocioException("Erro na autenticação.");
		}
	}

bilball wrote:persistence.xml
<?xml version="1.0" encoding="UTF-8"?> <persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"> <persistence-unit name="crateus" transaction-type="RESOURCE_LOCAL"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <mapping-file>META-INF/consultas.xml</mapping-file> <properties> <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" /> <property name="hibernate.connection.username" value="crateus" /> <property name="hibernate.connection.password" value="123456" /> <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/crateus" /> <property name="hibernate.show_sql" value="false" /> <property name="hibernate.format_sql" value="true" /> <property name="hibernate.hbm2ddl.auto" value="update" /> <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" /> <property name="hibernate.connection.autoReconnect" value="true" /> <property name="hibernate.connection.autoReconnectForPools" value="true"/> <property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" /> <property name="hibernate.c3p0.min_size" value="2"/> <property name="hibernate.c3p0.min_size" value="2"/> <property name="hibernate.c3p0.timeout" value="100"/> <property name="hibernate.c3p0.idle_test_period" value="150"/> </properties> </persistence-unit> </persistence>

Se este é teu persistence.xml, note que ele não contém a propriedade "hibernate.c3p0.max_size" configurada. Apenas a min_size por duas vezes.

What is classpath? Membro desde: 20/08/2004 16:06:07 Mensagens: 9 Offline

Eu já tinha feito tanta modificação que nem vi: mas eu modifiquei para como estava antes:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0"
	xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
[img]
	<persistence-unit name="amontada" transaction-type="RESOURCE_LOCAL">
		<provider>org.hibernate.ejb.HibernatePersistence</provider>
		<mapping-file>META-INF/consultas.xml</mapping-file>
		<properties>
			<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
			<property name="hibernate.connection.username" value="amontada" />
			<property name="hibernate.connection.password" value="123456" />
			<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/amontada" />
			<property name="hibernate.show_sql" value="false" />
			<property name="hibernate.format_sql" value="true" />
			<property name="hibernate.hbm2ddl.auto" value="update" />
			<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />									
			<property name="hibernate.connection.autoReconnect" value="true" />
			<property name="hibernate.connection.autoReconnectForPools" value="true"/>			
			<property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" />		
			<property name="hibernate.c3p0.min_size" value="2"/>
			<property name="hibernate.c3p0.max_size" value="20"/>						 
			<property name="hibernate.c3p0.timeout" value="100"/>						 
			<property name="hibernate.c3p0.idle_test_period" value="150"/>		
		</properties>	
	</persistence-unit>

</persistence>

e nada, ele sempre abre duas conexões no banco a cada login e não importa se eu faço logout ou a sessão expirar ele sempre manterá as conexões abertas:
e se por exemplo eu efetuado 11 logins, então ele terá 22 processos ativos e se eu for no console do mysql e "matar" todos eles assim que der timetout ele recupera os 22 processos e assim vai, ou seja, rapidamente ele atinge 100 conexões e derruba:

Assim que "matei" todos eles no mysql o console do eclipse apareceu uns avisos: segue um trecho pois é muito extenso.

com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2 08/Fev/2012:11:02:14-0300 DEBUG NewPooledConnection:491 - com.mchange.v2.c3p0.impl.NewPooledConnection@2ffce3d5 closed by a client.
java.lang.Exception: DEBUG -- CLOSE BY CLIENT STACK TRACE
	at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:491)
	at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:191)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470)
	at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964)
	at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2 08/Fev/2012:11:02:14-0300 DEBUG C3P0PooledConnectionPool:476 - Successfully destroyed PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@2ffce3d5
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2 08/Fev/2012:11:02:14-0300 DEBUG BasicResourcePool:967 - Successfully destroyed resource: com.mchange.v2.c3p0.impl.NewPooledConnection@2ffce3d5
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0 08/Fev/2012:11:02:14-0300 DEBUG C3P0PooledConnectionPool:217 - com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@6024e39f.acquireResource() returning. 
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0 08/Fev/2012:11:02:14-0300 DEBUG BasicResourcePool:1644 - trace com.mchange.v2.resourcepool.BasicResourcePool@4cb2fd30 [managed: 1, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@21dbd515)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0 08/Fev/2012:11:02:14-0300 DEBUG BasicResourcePool:422 - decremented pending_acquires: 1
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2 08/Fev/2012:11:02:14-0300 DEBUG C3P0PooledConnectionPool:217 - com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager@6024e39f.acquireResource() returning. 
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2 08/Fev/2012:11:02:14-0300 DEBUG BasicResourcePool:1644 - trace com.mchange.v2.resourcepool.BasicResourcePool@4cb2fd30 [managed: 2, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@21dbd515)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2 08/Fev/2012:11:02:14-0300 DEBUG BasicResourcePool:422 - decremented pending_acquires: 0
Timer-3 08/Fev/2012:11:02:16-0300 DEBUG BasicResourcePool:1935 - Checking for expired resources - Wed Feb 08 11:02:16 GMT-03:00 2012 [com.mchange.v2.resourcepool.BasicResourcePool@153043cc]
Timer-3 08/Fev/2012:11:02:16-0300 DEBUG BasicResourcePool:1447 - BEGIN check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@153043cc]
Timer-3 08/Fev/2012:11:02:16-0300 DEBUG BasicResourcePool:1468 - FINISHED check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@153043cc]
Timer-4 08/Fev/2012:11:02:20-0300 DEBUG BasicResourcePool:1935 - Checking for expired resources - Wed Feb 08 11:02:20 GMT-03:00 2012 [com.mchange.v2.resourcepool.BasicResourcePool@243e0b62]
Timer-4 08/Fev/2012:11:02:20-0300 DEBUG BasicResourcePool:1447 - BEGIN check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@243e0b62]
Timer-4 08/Fev/2012:11:02:20-0300 DEBUG BasicResourcePool:1468 - FINISHED check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@243e0b62]
Timer-2 08/Fev/2012:11:02:22-0300 DEBUG BasicResourcePool:1935 - Checking for expired resources - Wed Feb 08 11:02:22 GMT-03:00 2012 [com.mchange.v2.resourcepool.BasicResourcePool@52620402]
Timer-2 08/Fev/2012:11:02:22-0300 DEBUG BasicResourcePool:1447 - BEGIN check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@52620402]
Timer-2 08/Fev/2012:11:02:22-0300 DEBUG BasicResourcePool:1468 - FINISHED check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@52620402]
Timer-9 08/Fev/2012:11:02:22-0300 DEBUG BasicResourcePool:1935 - Checking for expired resources - Wed Feb 08 11:02:22 GMT-03:00 2012 [com.mchange.v2.resourcepool.BasicResourcePool@1ed73856]
Timer-9 08/Fev/2012:11:02:22-0300 DEBUG BasicResourcePool:1447 - BEGIN check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@1ed73856]
Timer-9 08/Fev/2012:11:02:22-0300 DEBUG BasicResourcePool:1468 - FINISHED check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@1ed73856]
Timer-5 08/Fev/2012:11:02:23-0300 DEBUG BasicResourcePool:1935 - Checking for expired resources - Wed Feb 08 11:02:23 GMT-03:00 2012 [com.mchange.v2.resourcepool.BasicResourcePool@2596b73e]
Timer-5 08/Fev/2012:11:02:23-0300 DEBUG BasicResourcePool:1447 - BEGIN check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@2596b73e]
Timer-5 08/Fev/2012:11:02:23-0300 DEBUG BasicResourcePool:1468 - FINISHED check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@2596b73e]

Agradeço a atenção

E como está o log de inicialização do C3P0? Aquele trecho em que ele mostra quais propriedades estão em execução?

What is classpath? Membro desde: 20/08/2004 16:06:07 Mensagens: 9 Offline

Quando eu inicio o tomcat aparece seguinte:

08/02/2012 11:26:13 org.apache.catalina.core.AprLifecycleListener init
INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Program Files\Java\jre6\bin;.;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Program Files (x86)\PC Connectivity Solution\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Dell\DW WLAN Card;C:\Program Files\WIDCOMM\Bluetooth Software\;C:\Program Files\WIDCOMM\Bluetooth Software\syswow64;
08/02/2012 11:26:13 org.apache.tomcat.util.digester.SetPropertiesRule begin
AVISO: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:prefeitura' did not find a matching property.
08/02/2012 11:26:13 org.apache.coyote.http11.Http11Protocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
08/02/2012 11:26:13 org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 569 ms
08/02/2012 11:26:13 org.apache.catalina.core.StandardService start
INFO: Starting service Catalina
08/02/2012 11:26:13 org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/6.0.26
log4j:WARN No appenders could be found for logger (org.apache.myfaces.webapp.StartupServletContextListener).
log4j:WARN Please initialize the log4j system properly.
08/02/2012 11:26:17 org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
08/02/2012 11:26:17 org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
08/02/2012 11:26:17 org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/36  config=null
08/02/2012 11:26:17 org.apache.catalina.startup.Catalina start
INFO: Server startup in 3498 ms

ola bilbal

o log do hibernate e do c3p0 nao está aparecendo!

voce precisa configurar o log4j (no caso de estar usando o log4j pro hibernate) e setar para aparecer tudo do org.hibernate e do net.sf com INFO para sysout. Caso contrário voce nao conseguira enxergar o que o hibernate esta tomando de decisão

Paulo Silveira wrote:ola bilbal

o log do hibernate e do c3p0 nao está aparecendo!

voce precisa configurar o log4j (no caso de estar usando o log4j pro hibernate) e setar para aparecer tudo do org.hibernate e do net.sf com INFO para sysout. Caso contrário voce nao conseguira enxergar o que o hibernate esta tomando de decisão

Além disso, debugar e ver quando o hibernate é inicializado, caso não ocorra na inicialização do tomcat.

Notícias, artigos e o maior fórum brasileiro sobre Java