pyODBC error with closed connection

maxjpowers · December 5, 2020, 10:23pm

I’m trying to run a query from pyodbc on a centos 7.6 vm where dremio is on http://localhost:9047 whose data source is an HDFS on that same vm. When I run a query from the UI it works fine, but using pyodbc I get the following.

2020-12-05 22:12:29,223 [UserServer-1] INFO  com.dremio.ConnectionLog - [78f2028e-992b-42e2-af39-2ed5f1c67d95] Connection opened.
	Endpoint: 127.0.0.1:59932
	Protocol Version: 5
	Record Type: n/a
	Record Formats: 
	Support Complex Types: false
	Name: Dremio C++ Client
	Version: 1.17.0 (1.17.0)
	Application: Unknown
	User Properties: 
		schema=
		support_complex_types=false
		userName=mpowers

2020-12-05 22:12:29,223 [UserServer-1] WARN  c.dremio.sabot.rpc.user.UserSession - Ignoring unknown property: SUPPORT_COMPLEX_TYPES
2020-12-05 22:12:29,223 [UserServer-1] WARN  c.dremio.sabot.rpc.user.UserSession - Ignoring unknown property: USERNAME
2020-12-05 22:12:29,233 [out-of-band-observer] INFO  query.logger - {"queryId":"2033fa32-25a4-2311-1707-18b593a2e200","queryText":"NA","start":1607206349228,"finish":1607206349229,"outcome":"COMPLETED","username":"mpowers"}
2020-12-05 22:12:29,244 [out-of-band-observer] INFO  query.logger - {"queryId":"2033fa31-e865-8cba-22d5-138be1e7e400","queryText":"NA","start":1607206349237,"finish":1607206349240,"outcome":"COMPLETED","username":"mpowers"}
2020-12-05 22:12:29,307 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa32-244d-e239-b922-ab16305df800:1:0
2020-12-05 22:12:29,308 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa32-244d-e239-b922-ab16305df800:1:1
2020-12-05 22:12:29,308 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa32-244d-e239-b922-ab16305df800:1:2
2020-12-05 22:12:29,309 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa32-244d-e239-b922-ab16305df800:1:3
2020-12-05 22:12:29,309 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa32-244d-e239-b922-ab16305df800:1:4
2020-12-05 22:12:29,309 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa32-244d-e239-b922-ab16305df800:1:5
2020-12-05 22:12:29,309 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa32-244d-e239-b922-ab16305df800:0:0
2020-12-05 22:12:29,445 [e0 - 2033fa32-244d-e239-b922-ab16305df800:frag:1:0] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa32-244d-e239-b922-ab16305df800:1:0: State to report: FINISHED
2020-12-05 22:12:29,453 [UserServer-1] INFO  c.d.sabot.rpc.user.UserRPCServer - [USER]: Channel closed /127.0.0.1:31010 <--> /127.0.0.1:59932 (user client)
2020-12-05 22:12:29,453 [UserServer-1] INFO  com.dremio.ConnectionLog - [78f2028e-992b-42e2-af39-2ed5f1c67d95] Connection Closed
2020-12-05 22:12:29,454 [e0 - 2033fa32-244d-e239-b922-ab16305df800:frag:0:0] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa32-244d-e239-b922-ab16305df800:0:0: State to report: FINISHED
2020-12-05 22:12:29,455 [e5 - 2033fa32-244d-e239-b922-ab16305df800:frag:1:4] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa32-244d-e239-b922-ab16305df800:1:4: State to report: CANCELLED
2020-12-05 22:12:29,457 [e6 - 2033fa32-244d-e239-b922-ab16305df800:frag:1:1] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa32-244d-e239-b922-ab16305df800:1:1: State to report: CANCELLED
2020-12-05 22:12:29,459 [e3 - 2033fa32-244d-e239-b922-ab16305df800:frag:1:5] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa32-244d-e239-b922-ab16305df800:1:5: State to report: CANCELLED
2020-12-05 22:12:29,463 [e1 - 2033fa32-244d-e239-b922-ab16305df800:frag:1:2] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa32-244d-e239-b922-ab16305df800:1:2: State to report: CANCELLED
2020-12-05 22:12:29,466 [e4 - 2033fa32-244d-e239-b922-ab16305df800:frag:1:3] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa32-244d-e239-b922-ab16305df800:1:3: State to report: CANCELLED
2020-12-05 22:12:29,475 [out-of-band-observer] INFO  query.logger - {"queryId":"2033fa32-244d-e239-b922-ab16305df800","queryText":"SELECT A,B,C FROM  HDFS.data.\"factTable.txt\" WHERE C > 0.0 LIMIT 100","start":1607206349247,"finish":1607206349469,"outcome":"CANCELED","outcomeReason":"Query cancelled by user 'mpowers'","username":"mpowers"}

comparing this to the same query performed on the UI:

2020-12-05 22:13:03,376 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa0f-864a-5997-9831-abe8cb490300:0:0
2020-12-05 22:13:03,377 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa0f-864a-5997-9831-abe8cb490300:1:0
2020-12-05 22:13:03,378 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa0f-864a-5997-9831-abe8cb490300:1:1
2020-12-05 22:13:03,378 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa0f-864a-5997-9831-abe8cb490300:1:2
2020-12-05 22:13:03,378 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa0f-864a-5997-9831-abe8cb490300:1:3
2020-12-05 22:13:03,378 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa0f-864a-5997-9831-abe8cb490300:1:4
2020-12-05 22:13:03,379 [FABRIC-rpc-event-queue] INFO  c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 2033fa0f-864a-5997-9831-abe8cb490300:1:5
2020-12-05 22:13:03,457 [e2 - 2033fa0f-864a-5997-9831-abe8cb490300:frag:1:1] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa0f-864a-5997-9831-abe8cb490300:1:1: State to report: FINISHED
2020-12-05 22:13:03,466 [e0 - 2033fa0f-864a-5997-9831-abe8cb490300:frag:0:0] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa0f-864a-5997-9831-abe8cb490300:0:0: State to report: FINISHED
2020-12-05 22:13:03,471 [e6 - 2033fa0f-864a-5997-9831-abe8cb490300:frag:1:2] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa0f-864a-5997-9831-abe8cb490300:1:2: State to report: CANCELLED
2020-12-05 22:13:03,473 [e4 - 2033fa0f-864a-5997-9831-abe8cb490300:frag:1:4] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa0f-864a-5997-9831-abe8cb490300:1:4: State to report: CANCELLED
2020-12-05 22:13:03,475 [e1 - 2033fa0f-864a-5997-9831-abe8cb490300:frag:1:0] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa0f-864a-5997-9831-abe8cb490300:1:0: State to report: CANCELLED
2020-12-05 22:13:03,476 [e5 - 2033fa0f-864a-5997-9831-abe8cb490300:frag:1:5] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa0f-864a-5997-9831-abe8cb490300:1:5: State to report: CANCELLED
2020-12-05 22:13:03,477 [e3 - 2033fa0f-864a-5997-9831-abe8cb490300:frag:1:3] INFO  c.d.s.e.f.FragmentStatusReporter - 2033fa0f-864a-5997-9831-abe8cb490300:1:3: State to report: CANCELLED
2020-12-05 22:13:03,490 [out-of-band-observer] INFO  query.logger - {"queryId":"2033fa0f-864a-5997-9831-abe8cb490300","schema":"[HDFS, data]","queryText":"SELECT A,B,C FROM \"factTable.txt\" WHERE C>0.0 LIMIT 100;","start":1607206383326,"finish":1607206383480,"outcome":"COMPLETED","username":"mpowers"}

we see its almost the same apart from these two lines

2020-12-05 22:12:29,453 [UserServer-1] INFO c.d.sabot.rpc.user.UserRPCServer - [USER]: Channel closed /127.0.0.1:31010 ↔ /127.0.0.1:59932 (user client)
2020-12-05 22:12:29,453 [UserServer-1] INFO com.dremio.ConnectionLog - [78f2028e-992b-42e2-af39-2ed5f1c67d95] Connection Closed

Why is dremio closing the channel between pyodbc and dremio?

maxjpowers · December 6, 2020, 11:52am

You guys really need to work on your documentation for ODBC’s and JDBC’s what you written have simply isn’t enough and has no comments on any errors that might pop up such as what I’m dealing with.

balaji.ramaswamy · December 7, 2020, 7:24am

Hi @maxjpowers

Thanks for the feedback and will let the docs team know. For your problem, channel closed (client) is usually the client closed the connection. Can you please send us the

query profile for the successful UI job
query profile for the failed JDBC job
server.log when this error happened
If this is on a VM install server.gc

Thanks
Bali

maxjpowers · December 7, 2020, 5:18pm

I have already shared the server.log for both of these. I cannot get access to your ‘download quer profile’ as I’m running dremio ui on a portforward so when I download it I get an http 405 error:

[qtp1444624884-127] ERROR c.d.d.server.GenericExceptionMapper - Unexpected exception when processing POST http://localhost:57645/apiv2/support/2031a330-956e-f26f-d71b-64402b1b1a00/download : java.io.FileNotFoundException: /tmp/dremio-support/66d5f599-57c1-42b7-8586-3749f998986d.zip (Permission denied)
java.io.FileNotFoundException: /tmp/dremio-support/66d5f599-57c1-42b7-8586-3749f998986d.zip (Permission denied)
at java.io.FileOutputStream.open0(Native Method)
at java.io.FileOutputStream.open(FileOutputStream.java:270)
at java.io.FileOutputStream.(FileOutputStream.java:213)
at java.io.FileOutputStream.(FileOutputStream.java:162)
at com.dremio.dac.support.BasicSupportService.generateSupportRequest(BasicSupportService.java:545)
at com.dremio.dac.support.BasicSupportService.downloadSupportRequest(BasicSupportService.java:464)
at com.dremio.dac.support.SupportResource.downloadData(SupportResource.java:83)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:52)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:124)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:167)
at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:176)
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:79)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:469)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:391)
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:80)
at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:253)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:248)
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:244)
at org.glassfish.jersey.internal.Errors.process(Errors.java:292)
at org.glassfish.jersey.internal.Errors.process(Errors.java:274)
at org.glassfish.jersey.internal.Errors.process(Errors.java:244)
at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:265)
at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:232)
at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:680)
at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:394)
at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:346)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:366)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:319)
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:205)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:755)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1617)
at com.dremio.dac.server.tracing.SpanFinishingFilter.doFilter(SpanFinishingFilter.java:46)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
at com.dremio.dac.server.GenericResponseHeadersFilter.doFilter(GenericResponseHeadersFilter.java:46)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
at com.dremio.dac.server.SecurityHeadersFilter.doFilter(SecurityHeadersFilter.java:52)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1604)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:545)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1297)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1212)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:717)
at org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:54)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:500)
at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
at java.lang.Thread.run(Thread.java:748)

and because the requests api does not work for your client I cannot perform a request. I’ve attached as much as I could. All I need to know is what could cause my channel to be closed, and what steps I can take to prevent this. I’ve tried both your REST and ODBC method, and both have failed. The former simply because you’re requests return either None or an html page.

maxjpowers · December 7, 2020, 7:38pm

Turns out the query works using

dataframe = pandas.read_sql(sql,cnxn)

but not with

cursor = cnxn.cursor()
cursor.execute(sql)

The difference between the two queries on the server.log is:

2020-12-07 19:34:26,749 [UserServer-1] INFO com.dremio.ConnectionLog - [b61585f8-5b73-4806-97e8-28e77caa4a74] Connection opened.
Endpoint: 127.0.0.1:42862
Protocol Version: 5
Record Type: n/a
Record Formats:
Support Complex Types: false
Name: Dremio C++ Client
Version: 1.17.0 (1.17.0)
Application: Unknown
User Properties:
schema=
support_complex_types=false
userName=mpowers

2020-12-07 19:34:26,750 [UserServer-1] WARN c.dremio.sabot.rpc.user.UserSession - Ignoring unknown property: SUPPORT_COMPLEX_TYPES
2020-12-07 19:34:26,750 [UserServer-1] WARN c.dremio.sabot.rpc.user.UserSession - Ignoring unknown property: USERNAME
2020-12-07 19:34:26,754 [out-of-band-observer] INFO query.logger - {“queryId”:“20317c3c-d70c-b598-20cd-f038f7647900”,“queryText”:“NA”,“start”:1607369666752,“finish”:1607369666753,“outcome”:“COMPLETED”,“username”:“mpowers”}
2020-12-07 19:34:26,761 [out-of-band-observer] INFO query.logger - {“queryId”:“20317c3d-4ef2-bd14-6949-24781167ea00”,“queryText”:“NA”,“start”:1607369666756,“finish”:1607369666759,“outcome”:“COMPLETED”,“username”:“mpowers”}
2020-12-07 19:34:26,841 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317c3d-29fa-dec0-730e-654b426bf300:1:0
2020-12-07 19:34:26,841 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317c3d-29fa-dec0-730e-654b426bf300:1:1
2020-12-07 19:34:26,841 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317c3d-29fa-dec0-730e-654b426bf300:1:2
2020-12-07 19:34:26,842 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317c3d-29fa-dec0-730e-654b426bf300:1:3
2020-12-07 19:34:26,842 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317c3d-29fa-dec0-730e-654b426bf300:1:4
2020-12-07 19:34:26,842 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317c3d-29fa-dec0-730e-654b426bf300:1:5
2020-12-07 19:34:26,842 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317c3d-29fa-dec0-730e-654b426bf300:0:0
2020-12-07 19:34:26,904 [e5 - 20317c3d-29fa-dec0-730e-654b426bf300:frag:1:4] INFO c.d.s.e.f.FragmentStatusReporter - 20317c3d-29fa-dec0-730e-654b426bf300:1:4: State to report: FINISHED
2020-12-07 19:34:26,911 [e0 - 20317c3d-29fa-dec0-730e-654b426bf300:frag:0:0] INFO c.d.s.e.f.FragmentStatusReporter - 20317c3d-29fa-dec0-730e-654b426bf300:0:0: State to report: FINISHED
2020-12-07 19:34:26,915 [e1 - 20317c3d-29fa-dec0-730e-654b426bf300:frag:1:2] INFO c.d.s.e.f.FragmentStatusReporter - 20317c3d-29fa-dec0-730e-654b426bf300:1:2: State to report: FINISHED
2020-12-07 19:34:26,916 [e6 - 20317c3d-29fa-dec0-730e-654b426bf300:frag:1:1] INFO c.d.s.e.f.FragmentStatusReporter - 20317c3d-29fa-dec0-730e-654b426bf300:1:1: State to report: FINISHED
2020-12-07 19:34:26,916 [e2 - 20317c3d-29fa-dec0-730e-654b426bf300:frag:1:5] INFO c.d.s.e.f.FragmentStatusReporter - 20317c3d-29fa-dec0-730e-654b426bf300:1:5: State to report: FINISHED
2020-12-07 19:34:26,919 [e4 - 20317c3d-29fa-dec0-730e-654b426bf300:frag:1:3] INFO c.d.s.e.f.FragmentStatusReporter - 20317c3d-29fa-dec0-730e-654b426bf300:1:3: State to report: CANCELLED
2020-12-07 19:34:26,923 [e3 - 20317c3d-29fa-dec0-730e-654b426bf300:frag:1:0] INFO c.d.s.e.f.FragmentStatusReporter - 20317c3d-29fa-dec0-730e-654b426bf300:1:0: State to report: CANCELLED
2020-12-07 19:34:26,927 [out-of-band-observer] INFO query.logger - {“queryId”:“20317c3d-29fa-dec0-730e-654b426bf300”,“queryText”:“SELECT * FROM HDFS.data."factTable.txt" WHERE C > 0.0 LIMIT 100”,“start”:1607369666763,“finish”:1607369666925,“outcome”:“COMPLETED”,“username”:“mpowers”}
2020-12-07 19:34:26,943 [UserServer-1] INFO c.d.sabot.rpc.user.UserRPCServer - [USER]: Channel closed /127.0.0.1:31010 ↔ /127.0.0.1:42862 (user client)
2020-12-07 19:34:26,943 [UserServer-1] INFO com.dremio.ConnectionLog - [b61585f8-5b73-4806-97e8-28e77caa4a74] Connection Closed

Whereas the failed script gives

2020-12-07 19:35:37,698 [UserServer-1] INFO com.dremio.ConnectionLog - [30c3f4d8-241f-4f3d-a9e6-de7aea93b752] Connection opened.
Endpoint: 127.0.0.1:42904
Protocol Version: 5
Record Type: n/a
Record Formats:
Support Complex Types: false
Name: Dremio C++ Client
Version: 1.17.0 (1.17.0)
Application: Unknown
User Properties:
schema=
support_complex_types=false
userName=mpowers

2020-12-07 19:35:37,698 [UserServer-1] WARN c.dremio.sabot.rpc.user.UserSession - Ignoring unknown property: SUPPORT_COMPLEX_TYPES
2020-12-07 19:35:37,699 [UserServer-1] WARN c.dremio.sabot.rpc.user.UserSession - Ignoring unknown property: USERNAME
2020-12-07 19:35:37,705 [out-of-band-observer] INFO query.logger - {“queryId”:“20317bf5-c9fe-5e85-6e81-9ce420af1c00”,“queryText”:“NA”,“start”:1607369737702,“finish”:1607369737703,“outcome”:“COMPLETED”,“username”:“mpowers”}
2020-12-07 19:35:37,712 [out-of-band-observer] INFO query.logger - {“queryId”:“20317bf5-b19e-866c-5da6-2f580816e500”,“queryText”:“NA”,“start”:1607369737708,“finish”:1607369737711,“outcome”:“COMPLETED”,“username”:“mpowers”}
2020-12-07 19:35:37,763 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317bf6-1026-5a5e-5c04-03d012fa4300:1:0
2020-12-07 19:35:37,763 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317bf6-1026-5a5e-5c04-03d012fa4300:1:1
2020-12-07 19:35:37,763 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317bf6-1026-5a5e-5c04-03d012fa4300:1:2
2020-12-07 19:35:37,763 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317bf6-1026-5a5e-5c04-03d012fa4300:1:3
2020-12-07 19:35:37,764 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317bf6-1026-5a5e-5c04-03d012fa4300:1:4
2020-12-07 19:35:37,764 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317bf6-1026-5a5e-5c04-03d012fa4300:1:5
2020-12-07 19:35:37,764 [FABRIC-rpc-event-queue] INFO c.d.sabot.exec.FragmentExecutors - Received remote fragment start instruction for 20317bf6-1026-5a5e-5c04-03d012fa4300:0:0
2020-12-07 19:35:37,824 [e5 - 20317bf6-1026-5a5e-5c04-03d012fa4300:frag:1:0] INFO c.d.s.e.f.FragmentStatusReporter - 20317bf6-1026-5a5e-5c04-03d012fa4300:1:0: State to report: FINISHED
2020-12-07 19:35:37,826 [e0 - 20317bf6-1026-5a5e-5c04-03d012fa4300:frag:1:3] INFO c.d.s.e.f.FragmentStatusReporter - 20317bf6-1026-5a5e-5c04-03d012fa4300:1:3: State to report: FINISHED
2020-12-07 19:35:37,830 [e0 - 20317bf6-1026-5a5e-5c04-03d012fa4300:frag:0:0] INFO c.d.s.e.f.FragmentStatusReporter - 20317bf6-1026-5a5e-5c04-03d012fa4300:0:0: State to report: FINISHED
2020-12-07 19:35:37,834 [e4 - 20317bf6-1026-5a5e-5c04-03d012fa4300:frag:1:4] INFO c.d.s.e.f.FragmentStatusReporter - 20317bf6-1026-5a5e-5c04-03d012fa4300:1:4: State to report: CANCELLED
2020-12-07 19:35:37,835 [UserServer-1] INFO c.d.sabot.rpc.user.UserRPCServer - [USER]: Channel closed /127.0.0.1:31010 ↔ /127.0.0.1:42904 (user client)
2020-12-07 19:35:37,835 [UserServer-1] INFO com.dremio.ConnectionLog - [30c3f4d8-241f-4f3d-a9e6-de7aea93b752] Connection Closed
2020-12-07 19:35:37,835 [e3 - 20317bf6-1026-5a5e-5c04-03d012fa4300:frag:1:5] INFO c.d.s.e.f.FragmentStatusReporter - 20317bf6-1026-5a5e-5c04-03d012fa4300:1:5: State to report: CANCELLED
2020-12-07 19:35:37,838 [e2 - 20317bf6-1026-5a5e-5c04-03d012fa4300:frag:1:2] INFO c.d.s.e.f.FragmentStatusReporter - 20317bf6-1026-5a5e-5c04-03d012fa4300:1:2: State to report: CANCELLED
2020-12-07 19:35:37,840 [e6 - 20317bf6-1026-5a5e-5c04-03d012fa4300:frag:1:1] INFO c.d.s.e.f.FragmentStatusReporter - 20317bf6-1026-5a5e-5c04-03d012fa4300:1:1: State to report: CANCELLED
2020-12-07 19:35:37,844 [out-of-band-observer] INFO query.logger - {“queryId”:“20317bf6-1026-5a5e-5c04-03d012fa4300”,“queryText”:“SELECT * FROM HDFS.data."factTable.txt" WHERE C > 0.0 LIMIT 100”,“start”:1607369737716,“finish”:1607369737841,“outcome”:“CANCELED”,“outcomeReason”:“Query cancelled by user ‘mpowers’”,“username”:“mpowers”}

The difference again being the connection is being closed for some reason before the query can completely executed.

maxjpowers · December 9, 2020, 5:02pm

Any ideas, I’ve tried using the dremio sqlalchemy engine but it gives the same result. Is it maybe because I’m not using Dremio enterprise? Since I see for restapi you need enterprise to generate tokens.

fetanchaud · February 3, 2021, 4:48pm

Hi @maxjpowers , I had the same problem in the dbt adapter for dremio which uses pyodbc over the dremio odbc driver. I seems to be simply because I did not try to read the data (dbt does not process data, it simply sends create table|view or insert|update statements).
Once I did a cursor.fetchall() the error disappeared.
I agree this is confusing, having to read data even when the query does not return anything, or in your case you do not need it…
Best regards,
Fabrice

Topic		Replies	Views
Unable to connect to Dremio from Superset	7	2167	April 7, 2021
Problems with ODBC driver and pyodbc when query returns error	7	8214	December 14, 2017
Dremio connector for ODBC in Python	1	169	March 5, 2024
Problems with Dremio ODBC connector and pyodbc 4.0.34	0	924	July 21, 2022
Dremio ODBC connection error	2	2109	May 7, 2021

pyODBC error with closed connection

Related Topics