Bug #104416 for DBD-ODBC: Problems using Impala ODBC library with Perl

Wed May 13 18:47:13 2015 slagelwa [...] yahoo.com - Ticket created

Subject:

Problems using Impala ODBC library with Perl

Considering that we have a bunch of scientists/programmers here that still use Perl and we're starting to use Cloudera CDH I thought I'd try using DBD::ODBC with Impala's ODBC library. Unfortunately I didn't get very far. First it didn't pick up the driver manager shared library properly. I'm using unixODBC (64bit) on CentOS and it defaulted to linking with -lodbc instead of -lodbc64. Quick change of the Makefile and it compiled. The next problem I encountered has to do with AutoCommit -- Impala doesn't support transactions and appears to be returning an error when SQLSetConnectAttr()is called in post_connect() to turn on AutoCommit. There doesn't seem to be a way of avoiding this call and a Google search turned up two links with similar problems: 1) A post on the impala-user Google Groups where a user was having a similar issue using an ETL tool, IBM datastage, which is also expecting to be able to set autocommit. 2) A post on perlmonks about drivers not supporting autocommit and dbi requiring it. Commenting out the autocommit setting it then goes down in flames when it SQLGetInfo(SQL_DRIVER_ODBC_VER), with tracing showing that the connection is closed. Not sure where to take this next to try to get it working. Anyhow I don't have a solution for this yet but I thought I'd share.

Thu May 14 06:01:18 2015 bohica [...] ntlworld.com - Correspondence added

On Wed May 13 18:47:13 2015, JSLAGEL wrote: Show quoted text

> Considering that we have a bunch of scientists/programmers here that > still use Perl and we're starting to use Cloudera CDH I thought I'd > try using DBD::ODBC with Impala's ODBC library. Unfortunately I > didn't get very far. > > First it didn't pick up the driver manager shared library properly. > I'm using unixODBC (64bit) on CentOS and it defaulted to linking with > -lodbc instead of -lodbc64. Quick change of the Makefile and it > compiled. > > The next problem I encountered has to do with AutoCommit -- Impala > doesn't support transactions and appears to be returning an error when > SQLSetConnectAttr()is called in post_connect() to turn on AutoCommit. > There doesn't seem to be a way of avoiding this call and a Google > search turned up two links with similar problems: > > 1) A post on the impala-user Google Groups where a user was having a > similar issue using an ETL tool, IBM datastage, which is also > expecting to be able to set autocommit. > > 2) A post on perlmonks about drivers not supporting autocommit and dbi > requiring it. > > Commenting out the autocommit setting it then goes down in flames when > it SQLGetInfo(SQL_DRIVER_ODBC_VER), with tracing showing that the > connection is closed. Not sure where to take this next to try to get > it working. > > Anyhow I don't have a solution for this yet but I thought I'd share.

Assuming you've got a recent DBI and DBD::ODBC could you set DBI_TRACE=DBD=x.log and export it. Then add the following to you odbcinst.ini file (run odbcinst -j to locate it): [ODBC] Trace = Yes Tracefile = /tmp/unixodbc.log now run your script and post the x.log file and the unixodbc.log file. Thanks Martin -- Martin J. Evans Wetherby, UK

Thu May 14 06:01:19 2015 The RT System itself - Status changed from 'new' to 'open'

Thu May 14 18:39:53 2015 slagelwa [...] yahoo.com - Correspondence added

Subject:	Re: [rt.cpan.org #104416] Problems using Impala ODBC library with Perl
Date:	Thu, 14 May 2015 22:39:34 +0000 (UTC)
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	Joe S <slagelwa [...] yahoo.com>

Thanks. I captured the logs and in additional turned on impala loglevel tracing and included it. From: Martin J Evans via RT <bug-DBD-ODBC@rt.cpan.org> To: slagelwa@yahoo.com Sent: Thursday, May 14, 2015 3:01 AM Subject: [rt.cpan.org #104416] Problems using Impala ODBC library with Perl <URL: https://rt.cpan.org/Ticket/Display.html?id=104416 > On Wed May 13 18:47:13 2015, JSLAGEL wrote: Show quoted text

> Considering that we have a bunch of scientists/programmers here that > still use Perl and we're starting to use Cloudera CDH I thought I'd > try using DBD::ODBC with Impala's ODBC library. Unfortunately I > didn't get very far. > > First it didn't pick up the driver manager shared library properly. > I'm using unixODBC (64bit) on CentOS and it defaulted to linking with > -lodbc instead of -lodbc64. Quick change of the Makefile and it > compiled. > > The next problem I encountered has to do with AutoCommit -- Impala > doesn't support transactions and appears to be returning an error when > SQLSetConnectAttr()is called in post_connect() to turn on AutoCommit. > There doesn't seem to be a way of avoiding this call and a Google > search turned up two links with similar problems: > > 1) A post on the impala-user Google Groups where a user was having a > similar issue using an ETL tool, IBM datastage, which is also > expecting to be able to set autocommit. > > 2) A post on perlmonks about drivers not supporting autocommit and dbi > requiring it. > > Commenting out the autocommit setting it then goes down in flames when > it SQLGetInfo(SQL_DRIVER_ODBC_VER), with tracing showing that the > connection is closed. Not sure where to take this next to try to get > it working. > > Anyhow I don't have a solution for this yet but I thought I'd share.

Assuming you've got a recent DBI and DBD::ODBC could you set DBI_TRACE=DBD=x.log and export it. Then add the following to you odbcinst.ini file (run odbcinst -j to locate it): [ODBC] Trace = Yes Tracefile = /tmp/unixodbc.log now run your script and post the x.log file and the unixodbc.log file. Thanks Martin -- Martin J. Evans Wetherby, UK

Download dbd.log
invalid/octet-stream 724b

Message body not shown because it is not plain text.

Download SimbaImpalaODBC_driver.log
invalid/octet-stream 14k

Message body not shown because it is not plain text.

Download unixodbc.log
invalid/octet-stream 3.8k

Message body not shown because it is not plain text.

Fri May 15 04:04:16 2015 bohica [...] ntlworld.com - Correspondence added

On Thu May 14 18:39:53 2015, JSLAGEL wrote: Show quoted text

> Thanks. I captured the logs and in additional turned on impala > loglevel tracing and included it.

There is not a lot I can do about the auto commit issue other than add a flag of some sort to say don't touch it. The logs don't seem to tell me much, did they come from a stock DBD::ODBC as it still looks like it is trying to set autocommit and I thought you said you'd commented that out. The unixODBC log shows: allocate an env handle set ODBC 3 allocate a connection handle successfully connect to DSN Cloudera64 call to get any connect diagnostics returned none then a really suspicious: [ODBC][24672][1431642910.716716][__handles.c][450] Exit:[SQL_SUCCESS] Environment = 0x2f4d620 with no "Entry". set ODBC 3 on the env handle again allocate a new connection handle successfully connect to DSN Cloudera64 call to get any connection diagnostics returned none then nothing! The DBI log shows it errors attempting to turn autocommit on I'll show the unixODBC log to Nick (he wrote most of unixODBC) when he gets in to work and see what he makes of it. Martin -- Martin J. Evans Wetherby, UK

Fri May 15 06:12:51 2015 bohica [...] ntlworld.com - Correspondence added

On Fri May 15 04:04:16 2015, MJEVANS wrote: Show quoted text

> On Thu May 14 18:39:53 2015, JSLAGEL wrote:

> > Thanks. I captured the logs and in additional turned on impala > > loglevel tracing and included it.

> > There is not a lot I can do about the auto commit issue other than add > a flag of some sort to say don't touch it. > > The logs don't seem to tell me much, did they come from a stock > DBD::ODBC as it still looks like it is trying to set autocommit and I > thought you said you'd commented that out. > > The unixODBC log shows: > > allocate an env handle > set ODBC 3 > allocate a connection handle > successfully connect to DSN Cloudera64 > call to get any connect diagnostics returned none > then a really suspicious: > > [ODBC][24672][1431642910.716716][__handles.c][450] > Exit:[SQL_SUCCESS] > Environment = 0x2f4d620

I showed this to Nick and he said this is because the driver manager does not know if you want logging until you allocate an env handle so you get the exit but not the entry when allocating an env handle. Show quoted text

> with no "Entry". > set ODBC 3 on the env handle again > allocate a new connection handle > successfully connect to DSN Cloudera64 > call to get any connection diagnostics returned none > then nothing! > > The DBI log shows it errors attempting to turn autocommit on > > I'll show the unixODBC log to Nick (he wrote most of unixODBC) when he > gets in to work and see what he makes of it. > > Martin

How did you disable the autocommit as the unixODBC log shows nothing about changing that attribute and there is not mention of any SQLGetInfo calls you mentioned but the DBI/DBD log suggests autocommit failed. Martin -- Martin J. Evans Wetherby, UK

Fri May 15 10:31:12 2015 slagelwa [...] yahoo.com - Correspondence added

Subject:	Re: [rt.cpan.org #104416] Problems using Impala ODBC library with Perl
Date:	Fri, 15 May 2015 14:30:56 +0000 (UTC)
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	Joe S <slagelwa [...] yahoo.com>

Many thanks for looking at this. For the logs I went with the latest stock DBI/DBD::ODBC instead of using the code where I had commented out the autocommit. I'll regenerate the logs with the autocommit skipped. From: Martin J Evans via RT <bug-DBD-ODBC@rt.cpan.org> To: slagelwa@yahoo.com Sent: Friday, May 15, 2015 3:12 AM Subject: [rt.cpan.org #104416] Problems using Impala ODBC library with Perl <URL: https://rt.cpan.org/Ticket/Display.html?id=104416 > On Fri May 15 04:04:16 2015, MJEVANS wrote: Show quoted text

> On Thu May 14 18:39:53 2015, JSLAGEL wrote:

> > Thanks. I captured the logs and in additional turned on impala > > loglevel tracing and included it.

> > There is not a lot I can do about the auto commit issue other than add > a flag of some sort to say don't touch it. > > The logs don't seem to tell me much, did they come from a stock > DBD::ODBC as it still looks like it is trying to set autocommit and I > thought you said you'd commented that out. > > The unixODBC log shows: > > allocate an env handle > set ODBC 3 > allocate a connection handle > successfully connect to DSN Cloudera64 > call to get any connect diagnostics returned none > then a really suspicious: > > [ODBC][24672][1431642910.716716][__handles.c][450] > Exit:[SQL_SUCCESS] > Environment = 0x2f4d620

I showed this to Nick and he said this is because the driver manager does not know if you want logging until you allocate an env handle so you get the exit but not the entry when allocating an env handle. Show quoted text

> with no "Entry". > set ODBC 3 on the env handle again > allocate a new connection handle > successfully connect to DSN Cloudera64 > call to get any connection diagnostics returned none > then nothing! > > The DBI log shows it errors attempting to turn autocommit on > > I'll show the unixODBC log to Nick (he wrote most of unixODBC) when he > gets in to work and see what he makes of it. > > Martin

How did you disable the autocommit as the unixODBC log shows nothing about changing that attribute and there is not mention of any SQLGetInfo calls you mentioned but the DBI/DBD log suggests autocommit failed. Martin -- Martin J. Evans Wetherby, UK

Fri May 15 11:11:54 2015 bohica [...] ntlworld.com - Correspondence added

On Fri May 15 10:31:12 2015, JSLAGEL wrote: Show quoted text

> Many thanks for looking at this. For the logs I went with the latest > stock DBI/DBD::ODBC instead of using the code where I had commented > out the autocommit. I'll regenerate the logs with the autocommit > skipped.

Cheers, look forward to seeing them. Martin -- Martin J. Evans Wetherby, UK

Fri May 15 13:37:13 2015 slagelwa [...] yahoo.com - Correspondence added

Subject:	Re: [rt.cpan.org #104416] Problems using Impala ODBC library with Perl
Date:	Fri, 15 May 2015 17:36:55 +0000 (UTC)
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	Joe S <slagelwa [...] yahoo.com>

Here's the next pass at the logs. I commented out the call to SQLSetConnectAttr() in the post_connect function of dbdimp.c. The logs show that the subsequent SQLGetInfo() calls all seem to fail. I checked on the server side and I do see the connection come in and it executes a "SHOW DATABASES" (I've included this log too). Oh, I should also say I can connect using isql packaged with unixODBC64 and everything seems to function correctly. And lastly, I got pinged back on the impala-users mailing list and was told there's an option, EnableSimulatedTransactions=1, which should help with the AutoCommit. Unfortunately that didn't seem to help the problem. I'm concerned that the driver log messages keep saying Invalid Connection handle on the subsequent SQLGetInfo() calls... -Joe From: Martin J Evans via RT <bug-DBD-ODBC@rt.cpan.org> To: slagelwa@yahoo.com Sent: Friday, May 15, 2015 8:12 AM Subject: [rt.cpan.org #104416] Problems using Impala ODBC library with Perl <URL: https://rt.cpan.org/Ticket/Display.html?id=104416 > On Fri May 15 10:31:12 2015, JSLAGEL wrote: Show quoted text

> Many thanks for looking at this. For the logs I went with the latest > stock DBI/DBD::ODBC instead of using the code where I had commented > out the autocommit. I'll regenerate the logs with the autocommit > skipped.

Cheers, look forward to seeing them. Martin -- Martin J. Evans Wetherby, UK

Download pass2_dbd.log
invalid/octet-stream 1.8k

Message body not shown because it is not plain text.

Download pass2_SimbaImpalaODBC_driver.log
invalid/octet-stream 15.1k

Message body not shown because it is not plain text.

Download pass2_unixodbc.log
invalid/octet-stream 10.8k

Message body not shown because it is not plain text.

Download pass2_server.log
invalid/octet-stream 2.7k

Message body not shown because it is not plain text.

Sat May 16 04:42:26 2015 bohica [...] ntlworld.com - Correspondence added

On Fri May 15 13:37:13 2015, JSLAGEL wrote: Show quoted text

> Here's the next pass at the logs. I commented out the call to > SQLSetConnectAttr() in the post_connect function of dbdimp.c. The > logs show that the subsequent SQLGetInfo() calls all seem to fail. I > checked on the server side and I do see the connection come in and it > executes a "SHOW DATABASES" (I've included this log too).

These logs make little sense unless unixODBC is being bypassed and some calls are going straight to the driver - hence the invalid handles and the SQLError calls in the unixODBC log with no other calls logged. Show quoted text

> Oh, I should also say I can connect using isql packaged with > unixODBC64 and everything seems to function correctly.

Firstly, isql opens an ODBC 2 connection and DBD::ODBC opens an ODBC 3 one. Secondly, isql does not do anything other than allocate 2 handles and call SQLConnect. I don't think it makes any SQLSetConnectAttr calls or SQLGetInfo calls at all. DBD::ODBC moved to ODBC 3 probably more than 10 years ago. It is next to impossible to support ODBC 2 drivers now and still support ODBC 3 functionality in ODBC 3 drivers. Show quoted text

> And lastly, I got pinged back on the impala-users mailing list and was > told there's an option, EnableSimulatedTransactions=1, which should > help with the AutoCommit. Unfortunately that didn't seem to help the > problem. I'm concerned that the driver log messages keep saying > Invalid Connection handle on the subsequent SQLGetInfo() calls... > > -Joe

Can you try running a small connect script in Perl again (with the autocommit still commented out) but first set and export PERL_DL_NONLAZY=1 I think what is happening is the dynamic linker is resolving SQLxxx symbols in libunixodbc.so until the ODBC driver is loaded and then SQLxxx symbols are pointing directly to the driver. unixODBC and your driver contain the same SQLxxx entry points and it is important the linker resolves them in unixodbc first. I also note you have a non unicode build of DBD::ODBC but changing that right now might add to the complications. I haven't got the driver you are using, does it say it supports ODBC 3? Can you point me at some documentation for the driver? Martin -- Martin J. Evans Wetherby, UK

Mon May 18 17:22:48 2015 slagelwa [...] yahoo.com - Correspondence added

Subject:	Re: [rt.cpan.org #104416] Problems using Impala ODBC library with Perl
Date:	Mon, 18 May 2015 21:22:25 +0000 (UTC)
To:	"bug-DBD-ODBC [...] rt.cpan.org" <bug-DBD-ODBC [...] rt.cpan.org>
From:	Joe S <slagelwa [...] yahoo.com>

Martin, Using PERL_DL_NONLAZY=1.....works! And with or without having the autocommit commented out. So apparently that was a non-issue. (And for the record setting the Impala ODBC parameter EnableSimulatedTransactions=1 wasn't necessary). The source for the ODBC drivers I'm using is at: http://www.cloudera.com/content/cloudera/en/downloads/connectors/impala/odbc/impala-odbc-v2-5-26.html and according to its documentation it compiles with ODBC 3.80 along with unicode and 32/64 support. As to the rest of our environment, we're testing on a 64bit CentOS 5 system. We installed the 64bit versions for RHEL5 Impala drivers. And instead of using the stock Perl where using Perl 5.14.2 built with the following options: config_args='-Dprefix=/tools' hint=recommended, useposix=true, d_sigaction=define useithreads=define, usemultiplicity=define useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=undef use64bitint=define, use64bitall=define, uselongd For the ODBC manager we've installed the stock CentOS 5 unixODBC64 and unixODBC64-devel, versions 2.2.14. We then added a link /usr/lib64/libodbc.so => libodbc.so.2.0.0 as the CentOS rpms have a link /usr/lib64/libodbc64.so => libodbc.so.2.0.0, but not the former and DBD::ODBC wasn't picking up odbc64. LD_LIBRARY_PATH is left unassigned, and we edited the cloudera.impalaodbc.ini file to use DriverManagerEncoding=UTF-16 and ODBCInstLib=libodbcinst.so as suggested by the comments in the template. Don't know if a description of our environment helps. -Joe From: Martin J Evans via RT <bug-DBD-ODBC@rt.cpan.org> To: slagelwa@yahoo.com Sent: Saturday, May 16, 2015 1:42 AM Subject: [rt.cpan.org #104416] Problems using Impala ODBC library with Perl <URL: https://rt.cpan.org/Ticket/Display.html?id=104416 > On Fri May 15 13:37:13 2015, JSLAGEL wrote: Show quoted text

> Here's the next pass at the logs. I commented out the call to > SQLSetConnectAttr() in the post_connect function of dbdimp.c. The > logs show that the subsequent SQLGetInfo() calls all seem to fail. I > checked on the server side and I do see the connection come in and it > executes a "SHOW DATABASES" (I've included this log too).

These logs make little sense unless unixODBC is being bypassed and some calls are going straight to the driver - hence the invalid handles and the SQLError calls in the unixODBC log with no other calls logged. Show quoted text

> Oh, I should also say I can connect using isql packaged with > unixODBC64 and everything seems to function correctly.

Firstly, isql opens an ODBC 2 connection and DBD::ODBC opens an ODBC 3 one. Secondly, isql does not do anything other than allocate 2 handles and call SQLConnect. I don't think it makes any SQLSetConnectAttr calls or SQLGetInfo calls at all. DBD::ODBC moved to ODBC 3 probably more than 10 years ago. It is next to impossible to support ODBC 2 drivers now and still support ODBC 3 functionality in ODBC 3 drivers. Show quoted text

> And lastly, I got pinged back on the impala-users mailing list and was > told there's an option, EnableSimulatedTransactions=1, which should > help with the AutoCommit. Unfortunately that didn't seem to help the > problem. I'm concerned that the driver log messages keep saying > Invalid Connection handle on the subsequent SQLGetInfo() calls... > > -Joe

Can you try running a small connect script in Perl again (with the autocommit still commented out) but first set and export PERL_DL_NONLAZY=1 I think what is happening is the dynamic linker is resolving SQLxxx symbols in libunixodbc.so until the ODBC driver is loaded and then SQLxxx symbols are pointing directly to the driver. unixODBC and your driver contain the same SQLxxx entry points and it is important the linker resolves them in unixodbc first. I also note you have a non unicode build of DBD::ODBC but changing that right now might add to the complications. I haven't got the driver you are using, does it say it supports ODBC 3? Can you point me at some documentation for the driver? Martin -- Martin J. Evans Wetherby, UK

Tue May 19 05:28:40 2015 bohica [...] ntlworld.com - Correspondence added

On Mon May 18 17:22:48 2015, JSLAGEL wrote: Show quoted text

> Martin, > Using PERL_DL_NONLAZY=1.....works! And with or without having the > autocommit commented out. So apparently that was a non-issue. (And > for the record setting the Impala ODBC parameter > EnableSimulatedTransactions=1 wasn't necessary).

That is good to hear. What I think it means if that something in the chain is linked with the dynamic linker set to resolve symbols when they are used rather than straight away. unixODBC loads the ODBC driver and I think there is an option (something like RTLD_GROUP) that needs adding to the call. I can probably find it if you need it. Show quoted text

> The source for the ODBC drivers I'm using is at: > http://www.cloudera.com/content/cloudera/en/downloads/connectors/impala/odbc/impala- > odbc-v2-5-26.html > and according to its documentation it compiles with ODBC 3.80 along > with unicode and 32/64 support.

That's good. Bear in mind unicode in ODBC is a bit strange. To use unicode properly, you need to build DBD::ODBC with the -u switch. You can tell if your DBD::ODBC was compiled this way by checking https://metacpan.org/pod/DBD::ODBC#odbc_has_unicode Show quoted text

> As to the rest of our environment, we're testing on a 64bit CentOS 5 > system. We installed the 64bit versions for RHEL5 Impala drivers. > And instead of using the stock Perl where using Perl 5.14.2 built with > the following options: > config_args='-Dprefix=/tools' > hint=recommended, useposix=true, d_sigaction=define > useithreads=define, usemultiplicity=define > useperlio=define, d_sfio=undef, uselargefiles=define, > usesocks=undef > use64bitint=define, use64bitall=define, uselongd > For the ODBC manager we've installed the stock CentOS 5 unixODBC64 and > unixODBC64-devel, versions 2.2.14. We then added a link > /usr/lib64/libodbc.so => libodbc.so.2.0.0 as the CentOS rpms have a > link /usr/lib64/libodbc64.so => libodbc.so.2.0.0, but not the former > and DBD::ODBC wasn't picking up odbc64.

Some day I need to get around to doing this automatically by testing odbc_config. Each distribution seems to do it slightly differently. Show quoted text

> LD_LIBRARY_PATH is left > unassigned, and we edited the cloudera.impalaodbc.ini file to use > DriverManagerEncoding=UTF-16 and ODBCInstLib=libodbcinst.so as > suggested by the comments in the template.

I'm not sure whether that DriverManagerEncoding is going to do the right thing for you. I think you'll need to rebuild DBD::ODBC with -u switch or DBD::ODBC will look like an ANSI app to unixODBC. Show quoted text

> Don't know if a description of our environment helps.

Yes, useful to know. Do you mind if I close this RT now? Martin -- Martin J. Evans Wetherby, UK

Tue Jun 16 08:51:11 2015 bohica [...] ntlworld.com - Status changed from 'open' to 'resolved'