1

I developed an application on Grails 2.4.4 using jdk 1.7 and MySQL Workbench 6.3. It works for some time, but after some hours of the deployment i try to log in, it stops working and throws the exception "java.net.SocketException: Broken pipe".

2016-10-24 09:40:53,599 [http-nio-8080-exec-12] ERROR errors.GrailsExceptionResolver  - SocketException occurred when processing request: [POST] /login/autenticacao
Broken pipe. Stacktrace follows:
java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3832)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2471)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
    at org.grails.datastore.gorm.GormStaticApi$_methodMissing_closure2.doCall(GormStaticApi.groovy:102)
    at sig.PasswordEncrypt.verificaAutenticacao(PasswordEncrypt.groovy:25)
    at sig.LoginController$_closure1.doCall(LoginController.groovy:20)
    at grails.plugin.cache.web.filter.PageFragmentCachingFilter.doFilter(PageFragmentCachingFilter.java:198)
    at grails.plugin.cache.web.filter.AbstractFilter.doFilter(AbstractFilter.java:63)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

I have an external file with the following configurations:

   beans{
    dataSource(BasicDataSource) {
        url = "jdbc:mysql://127.0.0.1/db_name?autoReconnect=true"
        username = "root"
        password = "root"
        pooled = true
        properties {
            jmxEnabled = true
            initialSize = 5
            maxActive = 50
            maxAge = 10 * 60000
            jdbcInterceptors = "ConnectionState"
            validationInterval = 15000
            minIdle = 5
            maxIdle = 25
            maxWaitMillis = 0
            timeBetweenEvictionRunsMillis = 1000 * 60 * 30
            numTestsPerEvictionRun = 3
            minEvictableIdleTimeMillis = 1000 * 60 * 30
            validationQuery = "SELECT 1"
            validationQueryTimeout = 3
            testOnBorrow = true
            testWhileIdle = true
            testOnReturn = true
            defaultTransactionIsolation = java.sql.Connection.TRANSACTION_READ_COMMITTED
        }
    }
}

I also added port:3306to my.cnf file and mysqld : ALL : ACCEPT to hosts.allow file on /etc folder.

Most of the properties were added after some research in another threads but it still doesnt work. Can anyone help me find a solution to solve this issue?

EDIT

After trying the solution given by Dipak Thoke it still throws the broken pipe exception. It also shows the following error

2016-10-25 09:03:33,683 [http-nio-8080-exec-37] ERROR spi.SqlExceptionHelper  - The last packet successfully received from the server was 38,766,997 milliseconds ago.  The last packet sent successfully to the server was 38,766,997 milliseconds ago. 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.

Should i change the "wait_timeout" value? Cause i already have the "autoReconnect=true" property

Telma Cruz
  • 11
  • 5
  • The reason was the database connections in the pool was not used for some time and the connection got closed, and when the application tries to use that it was throwing the exception. – Dipak Thoke Oct 24 '16 at 09:23
  • Can you restart your appliation – Dipak Thoke Oct 24 '16 at 09:24
  • http://stackoverflow.com/questions/11125962/correct-way-to-keep-pooled-connections-alive-or-time-them-out-and-get-fresh-one try this https://github.com/vahidhedayati/kchat/blob/master/grails-app/conf/kchatConfig.groovy#L22-L29 keep the autoReconnect=true enabled as it is – V H Oct 25 '16 at 18:46

2 Answers2

0

You need to c3p0 your project

step :1

Add this "BuildConfig.groovy"

dependencies {
        // specify dependencies here under either 'build', 'compile'

        compile 'c3p0:c3p0:0.9.1.2'
    }

step 2:

Datasource groovy: Now your data source will be like this only remove the other things.

beans {
    dataSource(BasicDataSource) {
        **url = "jdbc:mysql://127.0.0.1/db_name"**
        driverClassName = "com.mysql.jdbc.Driver"
        username = "root"
        password = "root"
        pooled = true
   }
}

For more info

Do I need to use C3P0 pooling library in my (grails) web application?

How do I configure c3p0 for a grails 2.X application with multiple datasources?.

http://blog.nutpan.com/2013/07/grails-broken-pipe-and-cannot-release.html

Thanks

Community
  • 1
  • 1
Dipak Thoke
  • 1,963
  • 11
  • 18
  • Thank you! Im gonna try it out today. In a few days i'll let you know if it worked :) – Telma Cruz Oct 24 '16 at 17:27
  • I tried to log in into the application a few minutes ago and the same exception was shown. Please check out my edit, i really dont know what else to do – Telma Cruz Oct 25 '16 at 08:18
  • I have some questions @TelmaCruz is this app in production mode or development mode ? – Dipak Thoke Oct 25 '16 at 09:02
  • It's in production mode – Telma Cruz Oct 25 '16 at 09:25
  • Have restart your tomcat ?? – Dipak Thoke Oct 25 '16 at 09:31
  • Not recently.. last time i did it was last week i think – Telma Cruz Oct 25 '16 at 09:58
  • if your mysql connection is ideal for more that 9 hrs i.e no request send to database then connection pool get close so need c3p0 plugin which is use for pooling purpose?. so please restart you tomcat... – Dipak Thoke Oct 25 '16 at 10:06
  • i restarted it now.. i added c3p0 to my dependencies yesterday so i thought it would fixed by now.. we'll see if tomorrow it will still be working – Telma Cruz Oct 25 '16 at 10:20
  • After restarting tomcat is your problem gone away ? – Dipak Thoke Oct 25 '16 at 10:30
  • It only stops working during the night, some hours after the last access so i can only be sure if the problem is solved tomorrow morning probably.. for now i just copied the war file to the server again, restarted tomcat and now ill have to wait to see if it happens again – Telma Cruz Oct 25 '16 at 10:48
  • Still no luck, the wait_timeout error is gone but i still get the broken pipe exception – Telma Cruz Oct 26 '16 at 08:28
  • After all this time it still throws a broken pipe exception.. i've checked the links you posted but nothing seems to work. I don't know what else to do – Telma Cruz Nov 03 '16 at 09:05
0

With the solutions that were presented here my application was always getting a broken pipe exception. I created a webservice with a script the runs every hour, that way the application never loses its connection to the database. Since then the exception was never been thrown again. Thank you all for your answers anyway :)

Telma Cruz
  • 11
  • 5