3 Replies Latest reply on Feb 9, 2017 5:39 AM by Anup Dey

    com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.

    Subhash Kadam Newbie

      Hi All,

       

      I am facing above issue in our production environment. It comes sometimes after 15 mins of jboss restart or sometimes after 25-20 days. please help me out. I have jboss-as-7.1.3.Final version.

        • 1. Re: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
          Anup Dey Expert

          This indicates that connectivity to the database was interrupted or terminated by something outside the JBoss process.

           

          Please provide the standalone or domain .xml that includes the configuration for the datasource involved in this failure so that we can review the configuration.

           

          MySQL implicitly closed the database connection because the connection has been inactive for too long. If your program then fetches a bad connection from the connection-pool that causes the MySQLNonTransientConnectionException: No operations allowed after connection closed.

           

          MySQL suggests:

           

          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.

           

          Something outside of the JBoss  JVM/process has terminated or severed the connection to the MySQL server

          Make sure that connectivity to the database server is possible.

           

          Ensure that JBoss is terminates idle connections more quickly than the database server

          Ensure that JBoss is configured to validate connections

          • 2. Re: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
            Subhash Kadam Newbie

            HI Anup,

            Thanks for reply.

             

            Following is a datasource for which we got the issue. here we are managing connections manually not JTA managed. we checked if there is any connection leak; but we didn't find any.

             

            in same stack trace we also got following exceptions

             

            1. Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

            The last packet successfully received from the server was 915,153 milliseconds ago.  The last packet sent successfully to the server was 915,154 milliseconds ago.

             

            2. Caused by: java.net.SocketException: Connection reset

             

            Datasource -

             

            <datasource jta="false" jndi-name="java:/DB" pool-name="DB" enabled="true" use-java-context="true" use-ccm="false">

              <connection-url>jdbc:mysql://172.22.1.113:3306/databse</connection-url>

              <driver-class>com.mysql.jdbc.Driver</driver-class>

              <connection-property name="useUnicode">

              true

              </connection-property>

              <connection-property name="connectionCollation">

              utf8_general_ci

              </connection-property>

              <connection-property name="characterSetResults">

              utf8

              </connection-property>

              <driver>MySqlJDBCDriver</driver>

              <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

              <pool>

              <min-pool-size>90</min-pool-size>

              <max-pool-size>110</max-pool-size>

              <prefill>true</prefill>

              </pool>

              <security>

              <user-name>developer</user-name>

              <password>******</password>

              </security>

              <validation>

              <validate-on-match>false</validate-on-match>

              <background-validation>false</background-validation>

              <exception-sorter class-name="org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter"/>

              </validation>

              <statement>

              <prepared-statement-cache-size>100</prepared-statement-cache-size>

              <share-prepared-statements>true</share-prepared-statements>

              </statement>

              </datasource>

            • 3. Re: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
              Anup Dey Expert

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

              - This exception seems to be specific to MySQL when there is a network issue or the DB was down. Since the error states the DB was not reachable for 28,956,564 milliseconds.

              - It seem to indicate issues that are potentially outside of JBoss. If not related to network connectivity, perhaps the database was under heavy load or for some other reason rejecting traffic.

               

              - This could be caused when the database is down/network issue/firewall.

               

              - Could you please check the following:

               

              * Try telnet/ping the DB.

              * Check if there is a firewall configured between DB and JBoss server.

              * Try running a standalone jdbc code from the same machine on which JBoss server is running.

               

              - By seeing the above reported error it seems that application couldn't establish the connection with database, see the stack trace error throws from MySQL driver. JBoss only reports the error.

               

              - The error seems to be due to the usage of connections(probably those that were idle for some time) that are closed.

               

              - Need to verify the database is running correctly at the specified destination

               

              - Verify the connection properties.

               

              - Make sure that an instance of database is running on the host and accepting TCP/IP connections at the port.

               

              - Make sure that TCP connections to the port are not blocked by a firewall.

               

              - This is a generic network issue where the other side of the connection has closed the connection. This could be happening due to firewalls, switches on the network or the actual back-end server (in this case the MySQL server)

               

              - It should be investigated by:

                - network administrators

                - back-end server administrators (in this case MySQL admins)

               

              - The error that you see signifies that the MySQL driver is unable to make a connection to the database, and it is timing out. This is usually a network issue.

               

              - The fact that restarting JBoss might fix the connections suggests that you may have invalid connections left in your pool after the network issue.

               

              - You can also check your database connectivity is properly established between your application and database

               

              - Test the network with the usual means like ping, telnet, ssh... connection tests.

               

              - I recommend that you check with your DBA/Network Admin for troubleshot during the time you saw this issue.

               

              Also you need to implement connection validation mechanism which is missed:

              ~~~

              <validation>

              <validate-on-match>true</validate-on-match>

                <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"></valid-connection-checker>

                <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"></exception-sorter>

                </validation>

              ~~~