insert data java.sql.SQLException: Broken pipe (Write failed)

[mullerhai]

Environment

• OS version: mac lastest
• JDK version: 1.8
• ClickHouse Server version: lastest
• ClickHouse Native JDBC version: 2.5.4
• (Optional) Spark version: N/A 3.0.1
• (Optional) Other components' version: N/A

use spark insert batch data to clickhouse meet error link log https://github.com/ClickHouse/ClickHouse/issues/6503

Error logs

    try {
      //var rowCount = 0
      stmt.setQueryTimeout(options.queryTimeout)
      df.collect().foreach { row =>
        var i = 0
        while (i < numFields) {
          if (row.isNullAt(i)) {
            stmt.setNull(i + 1, nullTypes(i))
          } else {
            setters(i).apply(stmt, row, i)
          }
          i = i + 1
        }
        stmt.addBatch()
//        stmt.execute() // too slow  badly

      }
      stmt.executeBatch()
//      stmt.executeLargeBatch()

    } finally {
      stmt.close()
    }

Steps to reproduce

Other descriptions

[pan3793]

The code is out of context, would you like to provide a runnable code snippet which can reproduce the issue?

[geraldo-netto]

Dear Friends,

I`m also facing this broken pipe errorr when I try to stress test ClickHouse Unfortunately, I cannot open the source code of my JMeter sampler, but in my case, it seems JDBC driver does not batch insert with the same performance when compared to the CSV file loading (native client)

JMeter log:

2021-07-05 16:07:05,323 INFO o.a.j.e.StandardJMeterEngine: Running the test!
2021-07-05 16:07:05,324 INFO o.a.j.s.SampleEvent: List of sample_variables: []
2021-07-05 16:07:05,324 INFO o.a.j.p.j.s.JavaSampler: Created class: clickhouse.JDBCBatchLoader. Uses tearDownTest: 
2021-07-05 16:07:05,324 INFO o.a.j.g.u.JMeterMenuBar: setRunning(true, *local*)
2021-07-05 16:07:05,938 INFO o.a.j.e.StandardJMeterEngine: Starting ThreadGroup: 1 : Thread Group
2021-07-05 16:07:05,938 INFO o.a.j.e.StandardJMeterEngine: Starting 24 threads for group Thread Group.
2021-07-05 16:07:05,938 INFO o.a.j.e.StandardJMeterEngine: Thread will continue on error
2021-07-05 16:07:05,938 INFO o.a.j.t.ThreadGroup: Starting thread group... number=1 threads=24 ramp-up=1 delayedStart=false
2021-07-05 16:07:05,939 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-1
2021-07-05 16:07:05,941 INFO o.a.j.t.ThreadGroup: Started thread group number 1
2021-07-05 16:07:05,942 INFO o.a.j.e.StandardJMeterEngine: All thread groups have been started
2021-07-05 16:07:05,981 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-2
2021-07-05 16:07:06,022 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-3
2021-07-05 16:07:06,064 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-4
2021-07-05 16:07:06,106 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-5
2021-07-05 16:07:06,148 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-6
2021-07-05 16:07:06,190 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-7
2021-07-05 16:07:06,232 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-8
2021-07-05 16:07:06,275 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-9
2021-07-05 16:07:06,317 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-10
2021-07-05 16:07:06,358 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-11
2021-07-05 16:07:06,400 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-12
2021-07-05 16:07:06,442 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-13
2021-07-05 16:07:06,485 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-14
2021-07-05 16:07:06,527 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-15
2021-07-05 16:07:06,569 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-16
2021-07-05 16:07:06,610 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-17
2021-07-05 16:07:06,652 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-18
2021-07-05 16:07:06,694 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-19
2021-07-05 16:07:06,736 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-20
2021-07-05 16:07:06,778 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-21
2021-07-05 16:07:06,820 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-22
2021-07-05 16:07:06,863 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-23
2021-07-05 16:07:06,905 INFO o.a.j.t.JMeterThread: Thread started: Thread Group 1-24
2021-07-05 16:10:35,525 ERROR c.JDBCBatchLoader: ClickHouse exception, code: 1002, host: localhost, port: 8123; Broken pipe (Write failed)
ru.yandex.clickhouse.except.ClickHouseUnknownException: ClickHouse exception, code: 1002, host: localhost, port: 8123; Broken pipe (Write failed)
	at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.getException(ClickHouseExceptionSpecifier.java:92) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:56) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:25) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	at ru.yandex.clickhouse.ClickHouseStatementImpl.sendStream(ClickHouseStatementImpl.java:1076) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	at ru.yandex.clickhouse.ClickHouseStatementImpl.sendStream(ClickHouseStatementImpl.java:1031) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	at ru.yandex.clickhouse.ClickHouseStatementImpl.sendStream(ClickHouseStatementImpl.java:1024) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	at ru.yandex.clickhouse.ClickHousePreparedStatementImpl.executeBatch(ClickHousePreparedStatementImpl.java:381) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	at ru.yandex.clickhouse.ClickHousePreparedStatementImpl.executeBatch(ClickHousePreparedStatementImpl.java:364) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	at clickhouse.JDBCBatchLoader.runTest(JDBCBatchLoader.java:72) [clickhouse-0.0.1-SNAPSHOT.jar:?]
	at org.apache.jmeter.protocol.java.sampler.JavaSampler.sample(JavaSampler.java:197) [ApacheJMeter_java.jar:5.4.1]
	at org.apache.jmeter.threads.JMeterThread.doSampling(JMeterThread.java:638) [ApacheJMeter_core.jar:5.4.1]
	at org.apache.jmeter.threads.JMeterThread.executeSamplePackage(JMeterThread.java:558) [ApacheJMeter_core.jar:5.4.1]
	at org.apache.jmeter.threads.JMeterThread.processSampler(JMeterThread.java:489) [ApacheJMeter_core.jar:5.4.1]
	at org.apache.jmeter.threads.JMeterThread.run(JMeterThread.java:256) [ApacheJMeter_core.jar:5.4.1]
	at java.lang.Thread.run(Thread.java:829) [?:?]
Caused by: java.net.SocketException: Broken pipe (Write failed)
	at java.net.SocketOutputStream.socketWrite0(Native Method) ~[?:?]
	at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:110) ~[?:?]
	at java.net.SocketOutputStream.write(SocketOutputStream.java:150) ~[?:?]
	at org.apache.http.impl.io.SessionOutputBufferImpl.streamWrite(SessionOutputBufferImpl.java:124) ~[httpcore-4.4.13.jar:4.4.13]
	at org.apache.http.impl.io.SessionOutputBufferImpl.flushBuffer(SessionOutputBufferImpl.java:136) ~[httpcore-4.4.13.jar:4.4.13]
	at org.apache.http.impl.io.SessionOutputBufferImpl.write(SessionOutputBufferImpl.java:167) ~[httpcore-4.4.13.jar:4.4.13]
	at org.apache.http.impl.io.ChunkedOutputStream.flushCacheWithAppend(ChunkedOutputStream.java:121) ~[httpcore-4.4.13.jar:4.4.13]
	at org.apache.http.impl.io.ChunkedOutputStream.write(ChunkedOutputStream.java:179) ~[httpcore-4.4.13.jar:4.4.13]
	at org.apache.http.impl.io.ChunkedOutputStream.write(ChunkedOutputStream.java:166) ~[httpcore-4.4.13.jar:4.4.13]
	at ru.yandex.clickhouse.ClickHousePreparedStatementImpl$BatchHttpEntity.writeTo(ClickHousePreparedStatementImpl.java:420) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	at ru.yandex.clickhouse.ClickHouseStatementImpl$WrappedHttpEntity.writeTo(ClickHouseStatementImpl.java:99) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	at org.apache.http.impl.DefaultBHttpClientConnection.sendRequestEntity(DefaultBHttpClientConnection.java:156) ~[httpcore-4.4.13.jar:4.4.13]
	at org.apache.http.impl.conn.CPoolProxy.sendRequestEntity(CPoolProxy.java:152) ~[httpclient-4.5.12.jar:4.5.12]
	at org.apache.http.protocol.HttpRequestExecutor.doSendRequest(HttpRequestExecutor.java:238) ~[httpcore-4.4.13.jar:4.4.13]
	at org.apache.http.protocol.HttpRequestExecutor.execute(HttpRequestExecutor.java:123) ~[httpcore-4.4.13.jar:4.4.13]
	at org.apache.http.impl.execchain.MainClientExec.execute(MainClientExec.java:272) ~[httpclient-4.5.12.jar:4.5.12]
	at org.apache.http.impl.execchain.ProtocolExec.execute(ProtocolExec.java:186) ~[httpclient-4.5.12.jar:4.5.12]
	at org.apache.http.impl.execchain.RetryExec.execute(RetryExec.java:89) ~[httpclient-4.5.12.jar:4.5.12]
	at org.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:185) ~[httpclient-4.5.12.jar:4.5.12]
	at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:83) ~[httpclient-4.5.12.jar:4.5.12]
	at ru.yandex.clickhouse.ClickHouseStatementImpl.sendStream(ClickHouseStatementImpl.java:1064) ~[clickhouse-jdbc-0.3.1-SNAPSHOT.jar:0.3.1-SNAPSHOT]
	... 11 more

Thank You very much/Kind Regards, Geraldo Netto

[sundy-li]

@geraldo-netto You are using ru.yandex.clickhouse, but this is the native driver.

[geraldo-netto]

I beg your pardon @sundy-li , Youre right, in any case, I saw some performance differences between native client and JDBC As soon as I have more information, Ill report it back to you

[leachli]

I also encountered this problem when USING Native. Is there any solution based on Native

[leachli]

I committed a batch of 250,000 once while migrating data