Skip to content


com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

O título deste post é uma exceção do driver do MySql que ocorre em algumas situações e pode ser difícil de diagnosticar a causa. A mensagem completa é: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.

Tive este problema em um ambiente de QA essa semana e vou deixar aqui informações que devem solucionar a maioria dos problemas relacionados. Há 2400 resultados no Google sobre MySQLNonTransientConnectionException, deve ter ocorrido muito já.

Esta exceção é lançada quando uma aplicação Java tenta utilizar uma conexão, mas ela já foi fechada no MySql. Os cenários mais comuns para isso são:

  • MySql foi reiniciado
  • Problema de rede na conexão com MySql
  • Conexão ficou ociosa tempo demais, e o MySql fechou-a

Estes cenários podem ser raros na sua infra, mas é importante que sua aplicação consiga lidar com isso. Minha aplicação usa o Hibernate, conectado a um pool de conexões do JBoss. O datasource em QA estava configurado assim:

<datasources>
  <local-tx-datasource> 
    <jndi-name>NomeDS</jndi-name>
    <connection-url>jdbc:mysql://servidormysql:3306/nome_schema</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <user-name>usuario</user-name>
    <password>senha</password> 
    <min-pool-size>1</min-pool-size>
    <max-pool-size>20</max-pool-size>    
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
    <metadata>
       <type-mapping>mySQL</type-mapping>
    </metadata>
  </local-tx-datasource> 
</datasources>

A solução no meu caso foi incluir um novo parâmetro no datasource indicando uma query para o JBoss checar se a conexão é válida ou não: <check-valid-connection-sql>select 1</check-valid-connection-sql>. Este elemento é filho de <local-tx-datasource>. Com a introdução deste parâmetro, o JBoss consegue identificar que a conexão já não é válida e então obtém uma nova conexão.

Nas minhas buscas vi muitas pessoas com esse problema rodando no Tomcat sem um pool de conexões. A solução para eles foi criar um pool com C3P0, como nesse exemplo. É importante prestar atenção na configuração para que seja feita a renovação das conexões em caso de falha. Isto parece ser feito com o parâmetro no C3P0: <property name=”connection.provider_class”>org.hibernate.connection.C3P0ConnectionProvider</property>

Observação importante: colocar o parâmetro ?autoReconnect=true na URL jdbc NÃO resolve o problema, só muda a exceção. Além disso, o parâmetro autoReconnect é deprecated e não é recomendada a utilização.

That’s all folks!

Posted in java.

Tagged with , , , , .


14 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. José Alexandre says

    Bruno, já tive esse problema também, no meu caso a conexão ficava ociosa por muito tempo… achei interessante as soluções apresentadas e me lembro que estava realmente difícil encontrar soluções. Para resolver eu busquei aumentar o tempo necessário para fechar a conexão por ociosidade… gostei do jeito que resolveu! Valew!

  2. blpsilva says

    Opa, o fechamento por ociosidade é bem comum pelo que pude ver. Muita gente reclama do “MySql 8 hours problem”, que ocorre principalmente quando a aplicação fica com poucos acessos à noite e o timeout de 8 horas do MySql é atingido.

    Bom, de qualquer forma é importante conseguir renovar as conexões, e estas configurações que eu indiquei fazem isso :) []s

  3. Emerson Macedo says

    Passei exatamente pelo mesmo problema no fim de 2008, só que numa aplicação Python usando SQLAlchemy. As conexões do MySQL tem um tempo máximo que podem ficar inativas, só que ele não é inteligente suficiente para matar essa conexão. Ele deixa ela lá e quando você usa a conexão ta zuada. No caso do Python + SQLAlchemy tivemos que usar um parâmetro chamado pool_recycle de acordo com o tempo que o servidor estava configurado para inativar/zuar a conexão.

    []s

  4. blpsilva says

    Oi Emerson, isso parece ocorrer com muita freqüência mesmo. Bacana saber como você resolveu em uma aplicação Python. Esse tempo default de ociosidade da conexão no MySql é de 8 horas, aí caso uma conexão fique ociosa até este limite, o MySql a fecha.

  5. Cheap free amateur porn videos says

    Best, All about cartoon porn videos [url=http://www.gravatar.com/cartoonpornvideose#1]All about cartoon porn videos[/url], puome,

  6. lesbian porn videos now says

    Very interesting sites., amateur porn videos [url=http://www.gravatar.com/amateurpornvideoso#1]amateur porn videos[/url], 829280,

  7. Cdgglpbr says

    I’m doing an internship nn teen models
    9775

  8. Gkdaqljq says

    I study here nn preteenage models
    36174

  9. Oumzjstn says

    this post is fantastic Underage Porn Pics 060

  10. Obalsulf says

    I read a lot Pedo Sex Storys ttqpki

  11. Best replica rolex watches says

    really great sites, thank you, replica rolex watches price, [url= http://replicarolexwatches.blogdiario.com ]replica rolex watches price[/url], %]],



Some HTML is OK

or, reply to this post via trackback.



  • Buy Cheap what is xanax Now Free Viagra Pills! Drugs, Health And Beauty.
  • Buy Cheapest viagra order Now Pharmacy At The Best Price! Best Drugstore.
  • Buy Cheap without prescription tramadol Online Guaranteed Shipping. Pharmacy Store.
  • Buy Cheap order verapamil Online Best Internet. Internet Prices For order verapamil!
  • Buy Cheapest phentermine 30 mg Now Cheap Online Pharmacy. Best Drugstore.
  • Buy Cheapest diet pill that works Now Best Drugstore. Internet Prices For diet pill that works!
  • Buy Cheapest best buy viagra Online Low Prices. Online Prices For best buy viagra!
  • Buy Cheapest what do xanax look like Now Discount Pharmacy Online. Best Internet.
  • Buy Cheapest cialis maximum dose Now 24/Online Pharmacy. Best Drugstore.
  • Buy Cheap trimethoprim sulfamethoxazole Now Free Viagra Pills! Internet Prices For trimethoprim sulfamethoxazole!
  • Buy Cheap viagra erection duration Now 24/Online Pharmacy. Top Online Pharmacy.
  • Buy Cheapest cialis for women Now Best Drugstore. No Prescription Needed.
  • Buy Cheap valium for sale Now No Prescription Needed. 24/Online Pharmacy.
  • Buy Cheapest levitra drugs Online Cheap Pharmacy Online. Best Drugstore.
  • Buy Cheapest purchase phentermine without prescription Online Discount Drugs At Best Online Drugstore.
  • Buy Cheapest fda approved weight loss Now Buy Medications Online. Free Viagra Pills!
  • Buy Cheap medications on line Online The Largest Internet Pharmacy. Low Prices.
  • Buy Cheap lexapro benefits Now No Prescription Needed. Free Viagra Pills!
  • Buy Cheapest 37.5 phentermine online Online Best Prices. Internet Prices For 37.5 phentermine online!
  • Buy Cheap us pharmacy no prescription ambien Now Discount Pharmacy Online. Guaranteed Shipping.
  • Buy Cheapest norvasc buy Online Discount Online Pharmacy. Best Prices.
  • Buy Cheapest valium 10 Now 24/Online Pharmacy. Guaranteed Shipping.
  • Buy Cheapest buy tramadol without prescription Now Low Prices. Pharmacy At The Best Price!
  • Buy Cheap buy pain pills Online Best Internet. Drugs, Health And Beauty.
  • Buy Cheap ambien us pharmacy Now Best Online. Internet Prices For ambien us pharmacy!
  • Buy Cheapest prescription depression Now Low Prices. Discount Online Pharmacy.
  • Buy Cheapest cheap viagra soft tablet Now Cheap Prescription Drugs. Best Drugstore.
  • lorazepam dosages Online Without Prescription Low Prices. Best Internet.
  • cialis tadalafil information page Online Without Prescription WorldWide Shipping. Best Prices.
  • Buy Cheap over the counter viagra Now Online Prices For over the counter viagra! Best Drugstore.
  • Buy Cheap what does xanax look like Now Best Internet. Top Online Pharmacy Supplier.
  • Buy Cheap overnight acne treatment Online Pharmacy Store. Cheap Pharmacy Online.
  • Buy Cheap list of pain medications Online Pharmacy Store. Online Prices For list of pain medications!
  • Buying Cheap generic viagra cheap. Worldwide Rx, Good Prices. WorldWide Shipping.
  • Buying Cheap mebendazole tablets. Offshore Rx, Good Prices. Guaranteed Shipping.
  • Buy Cheap strongest otc pain reliever available Online Best Online. No Prescription Needed.
  • Buy Cheap medical discount international drug Now Guaranteed Shipping. Cheap Prescription Drugs.
  • Buy Cheapest phentermine lowest prices Now Best Online. Discount Online Pharmacy.
  • Buy Cheap phentermine with no rx Online Best Drugstore. Discount Online Pharmacy.
  • Buy Cheap cialis 5mg Online Prescription Drugs And Generic Medications.
  • Buy Cheap use of viagra Now Low Prices. Drug Store Without A Prescription.
  • Buy Cheap vitamin information Online Cheap Prescription Drugs. Best Internet.
  • Buy Cheapest cheap viagra pill Now Online Medical Shop. Cheap Online Pharmacy.
  • Buy Cheapest diazepam medication Online Pharmacy At The Best Price! Low Prices.
  • Buy Cheapest symptoms of xanax withdrawal Online Free Viagra Pills! Online Medical Shop.
  • Buy Cheap ultram rx Now 24/Online Pharmacy. Free Viagra Pills!
  • Buy Cheap treatment of arthritis Online Order Cheap Meds Without Rx. Best Online.
  • Buy Cheap online ativan Online Top Online Pharmacy. Best Drugstore.
  • order tramadol online Online Without Prescription Cheap Meds Without Prescription.
  • Buy Cheap drug sarafem Online Cheap Pharmacy Online. Pharmacy Store.
  • Buy Cheapest viagra substitute Now Buy Medications Online. WorldWide Shipping.
  • Buy Cheap order norvasc Online Online Medical Shop. WorldWide Shipping.
  • Buy Cheapest mexican rx cialis low price Now Special Prices For mexican rx cialis low price! Pharmacy Store.
  • Buy klonopin clonazepam Without Prescription Doctor. Best Internet. Low Prices.
  • Buy Cheap ultram pregnancy Now Cheap Pharmacy Online. Online Medical Shop.
  • Buy Cheapest prescription acne Now Best Drugstore. Buy Medications Online.
  • Buy Cheapest tadalafil generika Online Low Prices. Internet Prices For tadalafil generika!
  • Buy Cheapest cialis online consultation Online Top Online Pharmacy. Best Prices.
  • Buy Cheap obesity pill Online Discount Online Pharmacy. Best Prices.
  • Buy Cheap buy a weight loss program Now Best Prices. 100% Satisfaction Guaranteed.
  • Buy Cheapest dose diflucan Online Best Online. Guaranteed Shipping.
  • Buy Cheap elite pharmacy Online Top Online Pharmacy Supplier. Low Prices.
  • Buy Cheapest buy medication online fast Online Discount Online Pharmacy. Best Internet.
  • prescription weight loss medication Online Without Prescription Free Viagra Pills! Low Prices.
  • Buy Cheapest cheap diazepam site Now No Prescription Needed. Best Drugstore.
  • Buy Cheap using cialis professional Now Low Prices. No Prescription Needed For Drugs.
  • Buy Cheap free ambien Online Low Prices. The Largest Internet Pharmacy.
  • Buy Cheapest sexual impotence Online Discount Pharmacy Online. Best Internet.
  • how to get pain medicine Online Without Prescription WorldWide Shipping. Best Prices.
  • Buy Cheap canada cialis Now The Largest Internet Pharmacy. Best Internet.
  • Buy Cheap viagra and blood pressure meds Now Low Prices. Safe And Secure Payment System.
  • Buy Cheapest tramadol 50 mg tablets Now Cheap Prescription Drugs. Low Prices.
  • Buy Cheapest valtrex commercials Now Special Prices For valtrex commercials! Pharmacy Store.
  • Buy Cheap treatment of roundworms Online Low Prices. 100% Satisfaction Guaranteed.
  • Buy Cheap hair loss online stop Online Cheap Prescription Drugs. Best Online.
  • Buy Cheap top fat burners Now Pharmacy Store. Discount Online Pharmacy.
  • weight loss prescription arizona Online Without Prescription Best Prices. Pharmacy Store.
  • Buy Cheap online tramadol pharmacy Now Discount Pharmacy Online. Top Online Pharmacy.
  • Buy Cheap levitra results Online Free Viagra Pills! Cheap Online Pharmacy.
  • Buy Cheapest appetite control pills Now No Prescription Needed. Free Viagra Pills!
  • Buy buspar information Online Without Prescription. Pharmacy Store. Low Prices.
  • Buy Cheapest discount generic medications Online Pharmacy Store. Cheap Online Pharmacy.
  • Buy Cheap ambien long term use Online Special Prices For ambien long term use! Best Prices.
  • Buy Cheap chinese viagra Online Best Drugstore. No Prescription Needed.
  • Buy Cheap generic pharmacy Online Discount Online Pharmacy. Best Internet.
  • Buy Cheapest non prescription diuretic Now Guaranteed Shipping. WorldWide Shipping.
  • Buy Cheapest buy anti obesity drugs Now Drugs, Health And Beauty. Best Internet.
  • Buy Cheap levitra onlines Online Best Prices. The Largest Internet Pharmacy.
  • Buy Cheapest viagra cialis levitra comparison Online Best Drugstore. Cheap Online Pharmacy.
  • Buy Cheapest usa pharmacy Now Best Prices. Discount Pharmacy Online.
  • Buy Cheapest free trial viagra Now The Largest Internet Pharmacy. Best Prices.
  • Buy Cheap online propecia prescription Online Best Internet. No Prescription Needed.
  • Buy Cheapest buying pain medication online Now Cheap Pharmacy Online. Top Online Pharmacy.
  • Buy Cheapest online tramadol pharmacy Online 24/Online Pharmacy. Best Prices.
  • Buy Cheapest ambien purchase Now Best Drugstore. Drugs, Health And Beauty.
  • Buy Cheap what burns body fat Now 24/Internet)(safe Pharmacy. Best Prices.
  • Buying Cheapest weight loss products for women. Mexican Rx, Good Prices. Free Viagra Pills!
  • Buy Cheapest pinworms medication Online Online Medical Shop. WorldWide Shipping.
  • Buy Cheapest generic compazine Online Best Online. Discount Online Pharmacy.
  • Buy Cheap without prescription tramadol Now Online Medical Shop. Online Prices For without prescription tramadol!