DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. sql SELECT * FROM test.test_return_items LIMIT 2000 OFFSET 0 plugin nrhl_ds_dsrc_dbms_mysql_prod_01_ecom SqlOperatorImpl JDBC_SUB_SCAN Location 2:0:2 SqlOperatorImpl JDBC_SUB_SCAN Location 2:0:2 Fragment 2:0 [Error Id: d4282dca-8475-411e-ac65-90ed4d27705b on localhost:31010] (java.sql.SQLNonTransientConnectionException) (conn:658348721) Could not send query: Broken pipe org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get():156 org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException():118 org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue():247 org.mariadb.jdbc.MariaDbStatement.executeInternal():322 org.mariadb.jdbc.MariaDbStatement.executeQuery():492 com.dremio.exec.store.jdbc.JdbcRecordReader.setup():171 com.dremio.exec.store.CoercionReader.setup():109 com.dremio.sabot.op.scan.ScanOperator$1.run():188 com.dremio.sabot.op.scan.ScanOperator$1.run():184 java.security.AccessController.doPrivileged():-2 javax.security.auth.Subject.doAs():422 org.apache.hadoop.security.UserGroupInformation.doAs():1807 com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():184 com.dremio.sabot.op.scan.ScanOperator.setupReader():176 com.dremio.sabot.op.scan.ScanOperator.setup():157 com.dremio.sabot.driver.SmartOp$SmartProducer.setup():552 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63 com.dremio.sabot.driver.SmartOp$SmartProducer.accept():522 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.Pipeline.setup():58 com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():331 com.dremio.sabot.exec.fragment.FragmentExecutor.run():227 com.dremio.sabot.exec.fragment.FragmentExecutor.access$800():83 com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():577 com.dremio.sabot.task.AsyncTaskWrapper.run():92 com.dremio.sabot.task.slicing.SlicingThread.run():71 Caused By (java.sql.SQLException) Could not send query: Broken pipe org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.handleIoException():1472 org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery():169 org.mariadb.jdbc.MariaDbStatement.executeInternal():316 org.mariadb.jdbc.MariaDbStatement.executeQuery():492 com.dremio.exec.store.jdbc.JdbcRecordReader.setup():171 com.dremio.exec.store.CoercionReader.setup():109 com.dremio.sabot.op.scan.ScanOperator$1.run():188 com.dremio.sabot.op.scan.ScanOperator$1.run():184 java.security.AccessController.doPrivileged():-2 javax.security.auth.Subject.doAs():422 org.apache.hadoop.security.UserGroupInformation.doAs():1807 com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():184 com.dremio.sabot.op.scan.ScanOperator.setupReader():176 com.dremio.sabot.op.scan.ScanOperator.setup():157 com.dremio.sabot.driver.SmartOp$SmartProducer.setup():552 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63 com.dremio.sabot.driver.SmartOp$SmartProducer.accept():522 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.Pipeline.setup():58 com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():331 com.dremio.sabot.exec.fragment.FragmentExecutor.run():227 com.dremio.sabot.exec.fragment.FragmentExecutor.access$800():83 com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():577 com.dremio.sabot.task.AsyncTaskWrapper.run():92 com.dremio.sabot.task.slicing.SlicingThread.run():71 Caused By (java.net.SocketException) Broken pipe java.net.SocketOutputStream.socketWrite0():-2 java.net.SocketOutputStream.socketWrite():109 java.net.SocketOutputStream.write():153 org.mariadb.jdbc.internal.io.output.StandardPacketOutputStream.flushBuffer():103 org.mariadb.jdbc.internal.io.output.AbstractPacketOutputStream.flush():157 org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery():163 org.mariadb.jdbc.MariaDbStatement.executeInternal():316 org.mariadb.jdbc.MariaDbStatement.executeQuery():492 com.dremio.exec.store.jdbc.JdbcRecordReader.setup():171 com.dremio.exec.store.CoercionReader.setup():109 com.dremio.sabot.op.scan.ScanOperator$1.run():188 com.dremio.sabot.op.scan.ScanOperator$1.run():184 java.security.AccessController.doPrivileged():-2 javax.security.auth.Subject.doAs():422 org.apache.hadoop.security.UserGroupInformation.doAs():1807 com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():184 com.dremio.sabot.op.scan.ScanOperator.setupReader():176 com.dremio.sabot.op.scan.ScanOperator.setup():157 com.dremio.sabot.driver.SmartOp$SmartProducer.setup():552 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79 com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63 com.dremio.sabot.driver.SmartOp$SmartProducer.accept():522 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.StraightPipe.setup():102 com.dremio.sabot.driver.Pipeline.setup():58 com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():331 com.dremio.sabot.exec.fragment.FragmentExecutor.run():227 com.dremio.sabot.exec.fragment.FragmentExecutor.access$800():83 com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():577 com.dremio.sabot.task.AsyncTaskWrapper.run():92 com.dremio.sabot.task.slicing.SlicingThread.run()
Hi @HLNA,
Does this issue happen instantly with the given query? Do other queries fail in the same way?
The error comes from the MariaDB JDBC driver which is returning a “broken pipe” error, meaning the server terminated the connection.
Yes, @jduong what could be the setting… rather … where can I do the setting? where can I make the timeout settings? it’s nowhere in the documentation and the error is repeating. Before going for enterprise would love to test the community for all cases. Help appreciated!
Btw, the query, I am using is to connect MysqL and SQL sever
I see references to wait_timeout for connections that have been idle for more than 8 hours:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout
This is a setting on the MySQL database.
@jduong The failures happen even after a fresh restart; I understand we can set the timeout at the respective server level, I am surprised by this failures… because by default our servers have 28800 seconds for wait_timeout.
@jduong this seems to be frequently happening… can I replace mariadb with mysql driver? if so which version of mysql jdbc driver should be used?
I am using Dremio 1.4 version.
Do you see the Broken Pipe error happening when you run this query:
SELECT * FROM test.test_return_items LIMIT 2000 OFFSET 0
using the MariaDB JDBC driver from other applications, such as DBVisualizer? Does it happen with the MySQL JDBC driver as well?
Trying to narrow down if this is an issue in the driver, server, or application layer.
@jduong as few have reported… I have been using both jdbc drivers in other applications that works fine; (wait_timeout is 28800 in both servers)
For dremio, currently, I have not replaced the jdbc driver… but, what’s the reason that we’re usign mariadb? can I do drop in replacement of mysql 5.1.45 jdrbc jar for mariadb 1.6.2 (the latest in dremio 1.4)… or it’s not going to solve the problem?
The MariaDB driver is being used for licensing reasons. You wouldn’t be able to drop in the MySQL JDBC driver as the connection URLs generated are the MariaDB format. I’m not sure if the MySQL driver will help with this issue, since both drivers work for you in other contexts.
I’d expect the problem to be from stale connections being left in the connection pool (we use Apache DBCP under the hood for connection pooling), but it’d be odd that you’d get this right after a server reset if that’s the case.
@jduong Thanks for quick rejoinders! Understood! But, on other clients if we don’t timeout/disconnect/fail, the tool might be keeping the connection alive (eg:- select 1 ?) … is there any other settings in jdbc (client settings) URL that can be done? if so, how do I do it? I don’t see any option for setting/passing the extra jdbc properties… it’ld be helpful… pls lemme know. For Apache db connection pooling, is there a setting I can try?
@jduong
The following query worked; but, not sure if it’ll work after some idle time.
SELECT * FROM test.test_return_items LIMIT 2000 OFFSET 0