Re: perl is not working after migrating the server to AIX 7.2

[Fennell, Brian]

When I get errors from CPAN I usually switch to whet or curl to get the package 
them make ; make all and make test ; make install.  You could also try cpanm 
and see if it gives you better error messages - if you are building binary 
packages you
May need a c compiler - easy with Linux - may be harder with AIX might have to 
pay IBM then try to find the right packages on the support web site could take 
days or weeks - you may be able to find a prebuilt gcc or clang but you may 
still need IBM libraries and support. Linux has things like apt-get and yum or 
dnf - not sure of the AIX equivalent - most Perl packages / modules build 
nicely but some are so hard you are better off with OS packages of prebuilt 
binaries.  Also you didn't say if your database was DB2 or other.  You also 
didn't say if your version of Perl changed when you upgraded the OS.  It may 
matter

Get Outlook for iOS

From: MK, Karthick via dbi-users 
Sent: Wednesday, February 16, 2022 8:08 PM
To: dbi-users@perl.org
Cc: Khan, Haris; Rana, Piyush; Pallapothu, Saikalyan
Subject: [EXTERNAL] perl is not working after migrating the server to AIX 7.2

Hello Team,

We have migrated our AIX server from 6.1 to 7.2 . After the migration the perl 
is upgraded with   v5.28.1 but perl scripts are not working . We are getting 
below error message. Could you please take a look of below and help us to fix 
this issue.

Can't locate 
DBI.pm
 in @INC (you may need to install the DBI module) (@INC contains: 
/usr/opt/perl5/lib64/site_perl/5.28.1/aix-thread-multi-64all 
/usr/opt/perl5/lib64/site_perl/5.28.1 
/usr/opt/perl5/lib64/5.28.1/aix-thread-multi-64all /usr/opt/perl5/lib64/5.28.1 
/usr/opt/perl5/lib64/site_perl /home/ddba04/scripts/perl_modules) at 
/home/ddba04/scripts/perl_modules/dbi_cmds.pm
 line 4.
BEGIN failed--compilation aborted at 
/home/ddba04/scripts/perl_modules/dbi_cmds.pm
 line 4.
Compilation failed in require at 
/home/db2edw01/scripts/applMonitor.pl
 line 5.


Also we tried to install bundle the given link. But it got reported error

https://metacpan.org/release/DBI



Here is the error

perl -MCPAN -e 'install Bundle::DBI'

 
CPAN.pm
 requires configuration, but most of it can be done automatically.

 If you answer 'no' below, you will enter an interactive dialog for each

 configuration option instead.

 Would you like to configure as much as possible automatically? [yes] yes

 Autoconfiguration complete.

 You can re-run configuration any time with 'o conf init' in the CPAN shell

 commit: wrote 
'/.cpan/CPAN/MyConfig.pm'

 Fetching with LWP:

 
http://www.cpan.org/authors/01mailrc.txt.gz

 Reading '/.cpan/sources/authors/01mailrc.txt.gz'

 Fetching with LWP:

 
DONE

 Reading '/.cpan/sources/modules/02packages.details.txt.gz'

 
http://www.cpan.org/modules/02packages.details.txt.gz

 Database was generated on Tue, 15 Feb 2022 14:29:03 GMT

 New 
CPAN.pm
 version (v2.29) available.

 .

 [Currently running version is v2.20]

 reload cpan

 install CPAN

 You might want to try

 to both upgrade 
CPAN.pm
 and run the new version without leaving

 ..Memory fault(coredump)

Thanks and Regards,
Karthick MK
Email : karthick...@accenture.com
Mobile : 95661 20992




This message is for the designated recipient only and may contain privileged, 
proprietary, or 

RE: [EXTERNAL] Re: select decimal column from mysql

[Fennell, Brian]

Before someone points out that my description of perl variables is incomplete:

Here are the extra details for the very curious:

https://perldoc.perl.org/perlguts#Datatypes

https://metacpan.org/pod/Scalar::Util

https://perldoc.perl.org/Scalar::Util#dualvar

https://perldoc.perl.org/Scalar::Util#isdual

https://perldoc.perl.org/Scalar::Util#isvstring

https://perldoc.perl.org/Scalar::Util#looks_like_number

https://metacpan.org/release/NWCLARK/perl-5.8.8/view/pod/perlapi.pod

https://metacpan.org/pod/POSIX::1003::Math

https://www.perl.com/pub/2012/06/perlunicook-further-resources.html/

https://perldoc.perl.org/perlapi

https://perldoc.perl.org/perlembed

https://perldoc.perl.org/Devel::Peek

https://www.nntp.perl.org/group/perl.perl5.porters/2015/11/msg232974.html




-Original Message-
From: Fennell, Brian
Sent: Monday, December 20, 2021 10:14 AM
To: john miky ; dbi-users@perl.org
Subject: RE: [EXTERNAL] Re: select decimal column from mysql

Variables in perl are “untyped” or better yet “dynamically typed”.
Every variable is a string but will behave like a int or float in the right 
context.
Conversions from string to int to float and back to string happen automatically 
and silently.
This may be surprising and confusing if you are coming from java.

Example:

perl -e '$v = "123" ; print $v . "\n" ; $v += 2 ; print $v . "\n"; $v /= 2.0 ; 
print $v . "\n"; print length($v) . "\n"; $v = "(" . $v . ")"; print $v . "\n"'
123
125
62.5
4
(62.5)



From: john miky 
Sent: Monday, December 20, 2021 1:28 AM
To: dbi-users@perl.org
Subject: [EXTERNAL] Re: select decimal column from mysql

sorry, please ignore this stupid question, 0.015 is > 0

On Mon, Dec 20, 2021 at 2:19 PM john miky <mailto:gb2...@gmail.com> wrote:
hmm. it is perl's "problem", not dbi.

On Mon, Dec 20, 2021 at 1:20 PM john miky <mailto:gb2...@gmail.com> wrote:
hi,

I select a decimal column, from mysql with dbi, the value 0.015 for example,  
my ($v) = $dbh->selectrow_array("select price from test limit 1") it is greater 
than 0 in perl.

What's the proper way to get a float value ?



The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: [EXTERNAL] Re: select decimal column from mysql

[Fennell, Brian]

Variables in perl are “untyped” or better yet “dynamically typed”.
Every variable is a string but will behave like a int or float in the right 
context.
Conversions from string to int to float and back to string happen automatically 
and silently.
This may be surprising and confusing if you are coming from java.

Example:

perl -e '$v = "123" ; print $v . "\n" ; $v += 2 ; print $v . "\n"; $v /= 2.0 ; 
print $v . "\n"; print length($v) . "\n"; $v = "(" . $v . ")"; print $v . "\n"'
123
125
62.5
4
(62.5)



From: john miky 
Sent: Monday, December 20, 2021 1:28 AM
To: dbi-users@perl.org
Subject: [EXTERNAL] Re: select decimal column from mysql

sorry, please ignore this stupid question, 0.015 is > 0

On Mon, Dec 20, 2021 at 2:19 PM john miky  wrote:
hmm. it is perl's "problem", not dbi.

On Mon, Dec 20, 2021 at 1:20 PM john miky  wrote:
hi,

I select a decimal column, from mysql with dbi,
the value 0.015 for example,  my ($v) = $dbh->selectrow_array("select price 
from test limit 1")
it is greater than 0 in perl.

What's the proper way to get a float value ?



The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Strange issue with inserting varchar2()

[Fennell, Brian]

One more

https://stackoverflow.com/questions/1454952/dummys-guide-to-unicode




The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Strange issue with inserting varchar2()

[Fennell, Brian]

Here are a few articles if you really want to dig in

https://stackoverflow.com/questions/5290182/how-many-bytes-does-one-unicode-character-take

https://docs.oracle.com/database/121/NLSPG/ch2charset.htm#NLSPG1037

https://metacpan.org/pod/Text::Iconv

https://metacpan.org/pod/distribution/perl/pod/perlunifaq.pod

https://metacpan.org/pod/distribution/perl/pod/perlunifaq.pod



-Original Message-
From: Fennell, Brian 
Sent: Thursday, May 27, 2021 8:10 PM
To: Bruce Johnson ; dbi users 
Subject: [EXTERNAL] RE: Strange issue with inserting varchar2()

UTF-8 has a variable number of bytes per character.  Some encodings have 1 byte 
per character always.  Some encodings have two bytes per character always.
Assuming that the number of bytes is the same as the number of characters 
sometimes leads to wrong answers.
When you are using perl as a client to oracle you may have characters in more 
than one encoding so you have to be very careful with the byte count and 
character count.
The perl libraries which access Oracle database are built with a specific 
Oracle C Library implementation, and the rules for character encoding and 
character byte count are sometimes different from one version to another.
In addition the perl rules for character counting and byte counting are 
different for different versions of perl.
In addition the Oracle Database has its own idea of character encoding and byte 
count.  And this also changes with version.
So you are threading more than one needle at the same time - it can get (and 
usually does get) very complicated very quickly.
The usual solution is to try to get all the layers to use the same encoding 
(and therefore the same number of bytes per character).  UTF-8 (with Unicode 
Integer to Character Mapping and UTF-8 Byte Encoding of the integers) for all 
layers usually works, but if you don't have control over all the layers you may 
not be able to choose this.
Another choice is to find out what character encoding the database is using and 
try to configure all other layers to use the same encoding.
It is also helpful to know that the English letters A-Z and a-z and the digits 
0-9 are often encoded the same way for many different encodings so sometimes 
you can cheat by forcing all of your data to be "pure 7 bit ascii" and hoping 
that all the layers will be "close enough".
The OS also may have its own rules about character encoding so even the same 
version of perl on Windows and Linux may have different encoding rules.

The last time I tried to figure this out for a real life example I simply gave 
up trying to understand it and had to roll back the version of perl AND the DBI 
/ DBD libraries (and therefore the version of Oracle C Client Libraries) to 
known working.

To be absolutely 100 percent sure you get it right you need to understand the 
OS, perl, oracle client and oracle server rules inside and out - and frankly 
this is very hard.
Even if you understand it all some layer may simply not be able to do what you 
want it to do.

When the perl programming language was first created the idea was that a 
character was a byte and a byte was a character - things have changes since 
then - more than once.
The same is true of the Oracle Database and client libraries - (and Windows and 
Linux) - the growing pains left a lot of strange artifacts behind in all.

Another trick is make all buffers 4 times are big as you think you really need 
them and then have the database concat the data together on the server side - 
do in the end you are only working with one layer.
Last time I checked 4 bytes per character was the worse case scenario.  It 
might be 8 by now.

There are also "lossless" ways of encoding "illegal characters", such as UTF-8 
encode first then Mime Q-Encode (quoted printable) or using XML / XHTML 
entities like this -  or UTF-8 encode then percent-encode.
This can be used work around special characters when you are dealing with 
mostly English with some European characters and an encoding that is either 
7-bit-ascii compatible or can easily be converted to and from (losslessly) 
7-bit-ascii (such as EBCIDIC with some creativity).
And there is always good old hexadecimal.

It all depends on your use-cases, how much control you have over your 
environment and how much time you can spend being clever.




-Original Message-
From: Bruce Johnson 
Sent: Thursday, May 27, 2021 7:36 PM
To: dbi users 
Subject: [EXTERNAL] Strange issue with inserting varchar2()

I'm working on an app that processes (among other thing) long sections of text, 
and I’m running into odd multi-byte characters in some of the entries.

The column in question is a varchar2(4000) so I am truncating the input to 4000 
bytes.

(via use “bytes; $string=substr($orig,0,4000); ” in the section where I 
actually truncate the string.)

When I do the insert I get an  ORA-12899: value too large for column 
"AWARD"."PRECEPT_NOMINA

RE: Strange issue with inserting varchar2()

[Fennell, Brian]

UTF-8 has a variable number of bytes per character.  Some encodings have 1 byte 
per character always.  Some encodings have two bytes per character always.
Assuming that the number of bytes is the same as the number of characters 
sometimes leads to wrong answers.
When you are using perl as a client to oracle you may have characters in more 
than one encoding so you have to be very careful with the byte count and 
character count.
The perl libraries which access Oracle database are built with a specific 
Oracle C Library implementation, and the rules for character encoding and 
character byte count are sometimes different from one version to another.
In addition the perl rules for character counting and byte counting are 
different for different versions of perl.
In addition the Oracle Database has its own idea of character encoding and byte 
count.  And this also changes with version.
So you are threading more than one needle at the same time - it can get (and 
usually does get) very complicated very quickly.
The usual solution is to try to get all the layers to use the same encoding 
(and therefore the same number of bytes per character).  UTF-8 (with Unicode 
Integer to Character Mapping and UTF-8 Byte Encoding of the integers) for all 
layers usually works,
but if you don't have control over all the layers you may not be able to choose 
this.
Another choice is to find out what character encoding the database is using and 
try to configure all other layers
to use the same encoding.
It is also helpful to know that the English letters A-Z and a-z and the digits 
0-9 are often encoded the same way for many different encodings so sometimes 
you can cheat by forcing all of your data to be "pure 7 bit ascii" and hoping 
that all the layers will be "close enough".
The OS also may have its own rules about character encoding so even the same 
version of perl on Windows and Linux may have different encoding rules.

The last time I tried to figure this out for a real life example I simply gave 
up trying to understand it and had to roll back the version of perl AND the DBI 
/ DBD libraries (and therefore the version of Oracle C Client Libraries) to 
known working.

To be absolutely 100 percent sure you get it right you need to understand the 
OS, perl, oracle client and oracle server rules inside and out - and frankly 
this is very hard.
Even if you understand it all some layer may simply not be able to do what you 
want it to do.

When the perl programming language was first created the idea was that a 
character was a byte and a byte was a character - things have changes since 
then - more than once.
The same is true of the Oracle Database and client libraries - (and Windows and 
Linux) - the growing pains left a lot of strange artifacts behind in all.

Another trick is make all buffers 4 times are big as you think you really need 
them and then have the database concat the data together on the server side - 
do in the end you are only working with one layer.
Last time I checked 4 bytes per character was the worse case scenario.  It 
might be 8 by now.

There are also "lossless" ways of encoding "illegal characters", such as UTF-8 
encode first then Mime Q-Encode (quoted printable) or using XML / XHTML 
entities like this -  or UTF-8 encode then percent-encode.
This can be used work around special characters when you are dealing with 
mostly English with some European characters and an encoding that is either 
7-bit-ascii compatible or can easily be converted to and from (losslessly) 
7-bit-ascii (such as EBCIDIC with some creativity).
And there is always good old hexadecimal.

It all depends on your use-cases, how much control you have over your 
environment and how much time you can spend being clever.




-Original Message-
From: Bruce Johnson 
Sent: Thursday, May 27, 2021 7:36 PM
To: dbi users 
Subject: [EXTERNAL] Strange issue with inserting varchar2()

I'm working on an app that processes (among other thing) long sections of text, 
and I’m running into odd multi-byte characters in some of the entries.

The column in question is a varchar2(4000) so I am truncating the input to 4000 
bytes.

(via use “bytes; $string=substr($orig,0,4000); ” in the section where I 
actually truncate the string.)

When I do the insert I get an  ORA-12899: value too large for column 
"AWARD"."PRECEPT_NOMINATIONS"."ONCOLOGY_COMMENTS" (actual: 4054, maximum: 4000) 
error

When I check the actual length of the offending string in bytes on the perl 
side it is 4000 *bytes* long

Original Oncology comment is 4804 chars (4824 bytes) long and when truncated in 
character mode to 4000 chars it’s 4018 bytes long, when truncated in byte mode, 
it’s 4000 chars and bytes.

What’s confusing me is that the size of the string being reported by oracle in 
the error doesn’t match any of the values that I see in perl. I’m not sure this 
is a DBI issue or not.

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information 

Re: [EXTERNAL] Re: DBI.pm - Memoryfault(coredump)

[Fennell, Brian]

I think the following links should cover all the pieces needed for 
Perl/JDBC/Oracle

https://www.oracle.com/database/technologies/appdev/jdbc.html
https://metacpan.org/pod/distribution/DBD-JDBC/JDBC.pod
https://sdkman.io/
Perl can launch the java server using qx:
https://perlmaven.com/qx


This is possibly a tangent:
The last time I dug into this I couldn't figure out how to build DBD::Oracle 
from source using a Git source (in place of CPAN)
There is very brief mention the cpanm support for git here
https://metacpan.org/pod/distribution/App-cpanminus/bin/cpanm
(search the page for "git" )
There is better documentation (sort of) hidden in the closed issues here - 
several uri formats can be used:
https://metacpan.org/pod/distribution/App-cpanminus/bin/cpanm
https://github.com/miyagawa/cpanminus/issues/296
https://github.com/miyagawa/cpanminus/commit/cf9a72038123a2f721508f17ec8d314cae03245a
https://github.com/miyagawa/cpanminus/pull/165
https://github.com/miyagawa/cpanminus/commit/8d82465cc0dde2bd35cd7b454f06f7fba9f205f8
https://github.com/miyagawa/cpanminus/pull/230
https://github.com/miyagawa/cpanminus/commit/c588cd7f5fa3fa6c9dcc30d1f4c958c4d45680c1

I found two github repos with incidents - the second seems most active
https://github.com/sergadin/dbd-oracle
https://github.com/gitpan/DBD-Oracle



This is also helpful:
https://metacpan.org/pod/DBI#trace
https://metacpan.org/pod/DBI#TRACING







The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

Re: [EXTERNAL] Re: DBI.pm - Memoryfault(coredump)

[Fennell, Brian]

Also consider switching to the JDBC module and using the Oracle JDBC driver.

The JDBC driver requires launching a Java server which perl accesses via 
sockets which is available from CPAN but is not simply a drop in replacement 
for other DBI / DBD modules.

I had to downgrade both perl and Oracle PM in order to get some legacy perl / 
Oracle working.

The "Correct fix" requires rebuilding the Perl/Oracle module from source, an 
Oracle license, Oracle C Libraries and dot-h files and "sample" makefiles, and 
lots of time and patience and skill debugging buffer overflows in C (using 
something like electric fence) and extensive knowledge of Perl internals and 
Oracle C libraries. The Perl/Oracle module was not being actively maintained 
the last time I checked. I was not able to invest the time needed.

There is a fork of the perl/oracle module (or more than one) on GitHub / GitLab 
which can be build using cpanm (not cpan) which supports git. I forget the 
details beyond that.



The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

Re: DBD::Oracle 1.80 & Oracle client 19c Segfaults on Destroy

[Fennell, Brian]

Another idea . . .

You could also wrap Oracle SQLPlus in perl using qx

How-to SQLPLUS and XML here:

https://asktom.oracle.com/pls/apex/f?p=100:11:0P11_QUESTION_ID:3512822500346787661

That and a little perl text parsing could get you a long way - split , grep, 
map, s and tr.

This pure-perl module can parse the XML:
https://metacpan.org/pod/XML::SAX::PurePerl

It would be great to have an Oracle DBD driver using pure-perl and sqlplus 
only.   No C complier needed, no buffer overflows to chase down, no jvm and no 
"other" languages (except SQL).

(no time for this kind of perl-uber-hacking myself but anyone looking for a fun 
project . . . )

Another hack is to use perl with qx with jisql (and JDBC drivers) - I have used 
this to access Microsoft SQLServer from perl with little work.

https://github.com/stdunbar/jisql

Brian Fennell




The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: [EXTERNAL] Re: DBD::Oracle 1.80 & Oracle client 19c Segfaults on Destroy

[Fennell, Brian]

FYI

The Oracle DBD driver is hard to build, it requires Oracle C Libraries and 
Header files.  It also requires Oracle Development files described by Oracle as 
Examples, but which are really templates for building any C client.  All Oracle 
C Clients are hard to build.  If I recall sometimes a library has to be 
included on the compiler / linker command line three times because of circular 
references of one library to another which cannot be resolved on one pass.  
Ambiguity in wide characters "bytes per character" can make for buffer 
overflows which require a lot of time to hunt down and fix.   Even if you are 
not using wide characters.

The CPAN version of the DBD driver (the last time I checked) is abandonware - 
someone keeps a patched fork on GIT.  You can use cpanm (cpanminus) to build 
directly from git but not cpan.  Docs in the cpanm man page.

Lost mindshare from Perl to python and ruby have contributed to the problem 
remaining unresolved (all bugs are shallow given enough eyeballs - this doesn't 
work so well when the number of eyeballs drops greatly).I ended up 
downgrading both perl 5 and Oracle DBD to workaround a problem we had with a 
Perl/Oracle bug causing buffer overflows and seg-faults. I just couldn't afford 
the hours to fix it properly.  Perl 5 internal structures had changed so much 
that older (working) versions of DBD no longer compiled against newer perl 5s.

One workaround it to consider using the perl JDBC driver to access the Oracle 
JDBC library instead of trying to get the Oracle C Library to compile bug-free. 
 You will need to bootstrap a jvm with the bridge in order to use it.  Calling 
out from perl to a python "select to CSV" or "select to xml" or "select to 
JSON" might be another work-around - depending on your requirements - might be 
faster than booting up a JVM.

Just my experience.




The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: [EXTERNAL] PERL - DBI MODULE

[Fennell, Brian]

DBI is an interface.  It isn’t really meant to be used without some kind of a 
database / DBD driver behind the interface.

The following are simple and do not require a lot of other things to be 
installed:

https://metacpan.org/pod/DBD::Mock
https://metacpan.org/pod/DBD::Mem
https://metacpan.org/pod/DBD::CSV
https://metacpan.org/pod/DBD::File
https://metacpan.org/pod/DBD::DBM

Also read this page for common functionality:

https://metacpan.org/pod/DBI

You should also familiarize yourself with CPAN and CPANM

https://metacpan.org/pod/distribution/App-cpanminus/lib/App/cpanminus/fatscript.pm

https://metacpan.org/pod/CPAN

If you decide you want to use a real database – mysql and MariaDB are free (as 
in libre):

https://metacpan.org/pod/DBD::MariaDB
https://metacpan.org/pod/DBD::mysql

And if you prefer to avoid compiling a driver from source you can try the JDBC 
bridge, but you may need more help from this list for that

https://metacpan.org/pod/DBD::JDBC

You can use the Metacpan search interface for more DBD modules.

And here are a few tutorials:

This uses DBD::CSV

https://www.perl.com/pub/1999/10/DBI.html/

This uses DBD::mysql

https://www.perltutorial.org/perl-dbi/



From: Pramod Mv 
Sent: Wednesday, June 3, 2020 12:35 AM
To: dbi-users@perl.org
Subject: [EXTERNAL] PERL - DBI MODULE

Hello team

   Could you please let me know how to use the DBI module without a 
DBD module installed ? . can you suggest any alternative.


--
Regards,
Pramod M V



The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: [EXTERNAL] PERL - DBI MODULE

[Fennell, Brian]

For further reading:

https://metacpan.org/pod/DBI::DBD

These links may help as well:

(You can compare perl to other programing languages you may already know)

https://rosettacode.org/wiki/Table_creation
https://rosettacode.org/wiki/Table_creation/Postal_addresses
https://rosettacode.org/wiki/SQL-based_authentication
https://rosettacode.org/wiki/Parametrized_SQL_statement

And perhaps these:

https://hyperpolyglot.org/db

https://ss64.com/databases.html





The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Perl script excessively executing statement

[Fennell, Brian]

John,
The part of the story that troubles me is this:
"it was working fine - nothing changed - then it broke".
It is the middle part - something must have changed.
If I were in your shoes I would be looking for the "what changed".
I had a piece of perl / Oracle code break when we upgraded the OS.  When the OS 
changed the version of Perl 5 changed, the version of the Oracle module had to 
change along with the version of perl changing, because there were changes in 
perl to that made certain "only once" internal variables part of a structure 
which could be instantiated once per thread.  The Oracle C code for the version 
of the module which worked, relied on there only being one, with the old naming 
convention.  The new version of the Oracle Perl Module had a bug related to 
buffering n-byte charter sets (which the OS, perl, C, Oracle Client and Oracle 
Server all had to agree on in order to keep certain buffers from over flowing 
and causing a seg-fault core dump.  Finding the buffer overflow bug we like 
trying to find a needle in a haystack - I didn't have the time to re-ramp up on 
C-buffer overflow kung-fu and the free and very-not-free tools which are needed 
to hunt down the bug.  The version on CPAN was abandon-ware, but the few people 
who wanted to adopt it couldn't get CPAN to break control of the owner - so 
there were non-official forks in GIT - rebuilding from them was non-trivial.  I 
tried rolling back the version of the Oracle Perl Module to the one that had 
been working but it was not incompatible with the new version of perl 5 which 
came with the OS.  Finally I had to roll back the version of perl 5, building 
from source and making a second perl install on my box - then change all the 
code which ran perl-scripts from perl-scripts from perl-scipts (with system and 
qx) to make sure none of the legacy code "fell off" the new version of perl 5.  
Then it all worked again - the n-byte character bug was not exposed, the buffer 
overflow did not occur and the legacy code wen back to working, but the OS 
could be upgraded (a security concern).
But - I had to ask "what changed" - then hunt down the root cause.
Because "nothing changed" is never the root cause.
Brian Fennell


-Original Message-
From: JohnD Blackburn 
Sent: Tuesday, February 18, 2020 2:57 AM
To: dbi-users@perl.org
Subject: RE: Perl script excessively executing statement

After reviewing the log4perl output from DBIx when running this script I found 
the following perl module being executed:

/usr/local/lib64/perl5/DBD/Oracle.pm

Which contains the following subroutine:

sub execute_for_fetch {
   my ($sth, $fetch_tuple_sub, $tuple_status) = @_;
   my $row_count = 0;
   my $err_total = 0;
   my $tuple_count="0E0";
   my $tuple_batch_status;
   my $dbh = $sth->{Database};
   my $batch_size =($dbh->{'ora_array_chunk_size'}||= 1000);
   if(defined($tuple_status)) {
   @$tuple_status = ();
   $tuple_batch_status = [ ];
   }

   my $finished;
   while (1) {
   my @tuple_batch;
   for (my $i = 0; $i < $batch_size; $i++) {
   $finished = $fetch_tuple_sub->();
   push @tuple_batch, [@{$finished || last}];

   }
   last unless @tuple_batch;

   my $err_count = 0;
   my $res = ora_execute_array($sth,
   \@tuple_batch,
   scalar(@tuple_batch),
   $tuple_batch_status,
   $err_count );

   if (defined($res)) { #no error
$row_count += $res;
   }
   else {
$row_count = undef;
   }

   $err_total += $err_count;

   $tuple_count+=@tuple_batch;
   push @$tuple_status, @$tuple_batch_status
if defined($tuple_status);

   last if !$finished;

   }
   #error check here
   return $sth->set_err($DBI::stderr, "executing $tuple_count generated 
$err_total errors")
  if $err_total;

   return wantarray
? ($tuple_count, defined $row_count ? $row_count : undef)
: $tuple_count;

}



Is that "while(1)" loop a potential contender for causing this?




-Original Message-
From: JohnD Blackburn 
Sent: Tuesday, 18 February 2020 8:51 AM
To: Steven Lembark ; dbi-users@perl.org
Subject: RE: Perl script excessively executing statement

The problem is not about how well the SQL runs.  It runs in less than a second, 
and it should only be getting executed 12 times an hour.

What I'm trying to do is to figure out how the SQL statement got executed 1000 
times more than it should have done (which brought the db to its knees for 50 
minutes with nothing being able to connect).  If there is something in DBI that 
is automatically re-executing for some unknown reason, I need to be able 

RE: Perl script excessively executing statement

[Fennell, Brian]

John,
One way for you to execute sql thousands of times from perl is to do it in your 
loop.

The other way is for the Oracle server to do it for you in its loop.

Sometimes an Oracle Upgrade will cause the Oracle Optimizer to change - this 
can cause SQL that "worked fine" to start behaving differently.

A SQL join is translated to one or more nested loops by the Oracle Optimizer - 
SQL is an algebraic notation but computers don't really "do" algebra, they do 
nested loops.
Which table is selected first and which is second (and third and so-on).  If 
you have functions they may execute SQL once per row, but this may be in the 
outer loop (seldom) or in the inner loop (often).
The Oracle SQL Optimizer plan can also change when the table statistics are 
recalculated - which doesn't require an Oracle upgrade.

EXPLAIN PLAN is your way to see which table gets looped over in which order as 
the table statistics and the Oracle optimizer are RIGHT NOW.

Some more hints:

EXPLAIN PLAN
https://grokbase.com/t/perl/dbi-users/094mcz7m5w/an-explain-plan-for-oracle-queries-with-placeholders
https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf

DBI DEBUG
https://www.easysoft.com/developer/languages/perl/dbi-debugging.html
https://www.perlmonks.org/bare/?node_id=90692DBI-trace()
https://www.effectiveperlprogramming.com/2010/04/use-dbi_trace-to-follow-dbis-work/

The error you are getting may be a result of a client/server incompatibility 
(if the server version changed and your client version did not) - you could try 
an alternate Oracle DBD plug-in / class.

Alternate Oracle DBD plugin
https://github.com/perl5-dbi/DBD-Oracle
Can be installed with cpanm using a git url (but not with cpan)
https://metacpan.org/pod/cpanm
you will need oracle C libraries to build - it won't be easy

Alternate Oracle DBD plugin - Oracle JDBC
https://metacpan.org/pod/JDBC
https://metacpan.org/pod/distribution/DBD-JDBC/JDBC.pod#Starting-the-server
you will need a jvm installed - and oracle's jdbc jars - it might be easier (or 
not)
you can get a free jvm here:
https://sdkman.io/





From: JohnD Blackburn 
Sent: Wednesday, February 12, 2020 12:56 AM
To: dbi-users@perl.org
Subject: [EXTERNAL] Perl script excessively executing statement

Hi all,

I have a perl script in my monitoring system that has been working for months 
without an issue.

Basically, it connects to an Oracle 12c database, prepares a statement, then it 
executes the statement, then it has a while loop to process the returned rows.

So under normal conditions the statement is executed once every 5 minutes.

Now on Friday last week, it did something really strange which I cannot account 
for the behaviour.

According to the DBA, the statement in the script was executed 12610 times over 
a 50 minute period causing the database to become non-responsive.  The DBAs 
also stated that the script only connected to the database once at the 
beginning of the 50 minute period.  Average execution time of the statement was 
0.26 seconds.

According to the log for my script, the script only executed once at the 
beginning of the 50 minute period, and then after that, returned to executing 
every 5 minutes.

Since that incident, the statememt in question has only executed the expected 
12 times per hour.

I have yet to find a satisfactory reason the SQL statement from this perl 
script executed so many times in the 50 minute period.

Script is running on an Oracle Linux 7.7 server with;
* oracle 12c client installed
* perl 5.16.3
* perl-DBI 1.627-4 ( from Oracle Linix Latest yum repository)
* perl-DBD-ODBC 1.50.-3 (from EPEL)
* DBD::Oracle 1.80 (from CPAN)

Oracle 12 database is on a remote server.

Anyone have any ideas why the SQL statement would have been executed 12000+ 
times in a 50minute period, when the script and its schedule should not have 
executed the SQL any more frequiently than 12 times an hour?

Regards,
John





The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Perl script excessively executing statement

[Fennell, Brian]

If you are trying to execute one SQL statement against the database for each 
loop iteration – you are doing it wrong.

You are adding a round trip for every row which will be slow for the client and 
waist time and other resources on the server.

Look on the net for other approaches using NVL and “in” and DECODE.   Learn 
about Oracle inline-views, and clever uses of joins –
Learn the difference between “union all” and “union” and how to use DUAL and 
UNION ALL to create a dynamic-view that can be joined with other tables.

Try to figure out how you can do the whole thing in a single SQL statement, 
then just process the results one row at time.

Learn about how to bind variables instead of doing dynamically generated SQL.  
Learn about how oracle implements UPSERT (insert + update).

Hunt down relevant articles on “Ask Tom”.
You will have to think harder but your code will be faster and your DBA 
happier.  And you will be a wiser professional.

Ask the DBA for help if you are having a hard time figuring it out or share 
your problem and your solution here and ask for alternatives (you left out most 
of your code – security concerns?).



From: JohnD Blackburn 
Sent: Thursday, February 13, 2020 1:59 AM
To: Christopher Jones ; dbi-users@perl.org
Subject: [EXTERNAL] RE: Perl script excessively executing statement

The DBA pulled info from some cache that showed the SQL statement from the 
script was executed 12610 times.

So if I were to add an “or die $!” statement after the fetchrow_array(), that 
might fix things?  (or prevent it from trying to continue if there are errors?)
Or catch errors after the execute statement?




From: Christopher Jones 
mailto:christopher.jo...@oracle.com>>
Sent: Thursday, 13 February 2020 12:56 PM
To: dbi-users@perl.org; JohnD Blackburn 
mailto:johnd.blackb...@au.abb.com>>
Subject: Re: Perl script excessively executing statement

CAUTION: This email originated from outside of the organization. Do not click 
links or open attachments unless you recognize the sender and know the content 
is safe.



On 13/2/20 11:13 am, JohnD Blackburn wrote:
Is that a behavior of DBI or DBD::Oracle?

Maybe your script, if you are blindly looping when it gets an error?  Overall, 
I don't think there is enough information to point directly at a cause.  
Presumably the DBA meant that a SQL statement (of some kind) was e



xecuted 12610 times; not that your script was invoked that number of times.

My script says prepare or die, so any retries would have had to come directly 
from the DBD::Oracle module

Script basically says:

use DBD::Oracle;
my $dbh = DBI->connect("dbi:Oracle:$dbSID", $user, $passwd, {AutoCommit => 0 });
my $statement = execute();
while ( my ($var1, $var2, $var3, $var4) = $arraySelect->fetchrow_array() ) {

}
$arraySelect->finish();
$dbh->disconnect();

I don’t work much with the DBD::Oracle module or perl and what I have is just 
reworked from scripts others have written.

Is there parameters for the DBD::Oracle functions that can affect their 
behavior? If this is behavior of the execute function, I really need to be able 
to reign it in to limit its impact if it ever does it again.  Not knowing why 
the issue triggered in the 1st place, I don’t know how to reproduce it to test 
if any mitigations are sufficient.

You can set sqlnet.ora parameters to bound the time taken for connection and 
statement execution.  Refer to 
https://cx-oracle.readthedocs.io/en/latest/user_guide/ha.html#network-configuration
 and 
https://oracle.github.io/node-oracledb/doc/api.html#connectionha
 since the sqlnet.ora settings will be the same for DBD::Oracle -  the network 
layer is common across all the 

RE: Translate between DBI and SQL

[Fennell, Brian]

P.S.
This may also help:
http://www.dispersiondesign.com/articles/perl/perl_escape_characters

Short answer:
Use single-quoted strings whenever possible - they have the fewest characters 
to escape - only backslash and single quote - and you can escape both by 
preceding with a backslash.
Use dot operator and double-quoted strings for special characters and new-lines.

Use the dot and dot-equals operators for multi-line sql.

something like this:
   my($my_sql_string);
   $my_sql_string =  'SELECT \'column name with a space\' ' . "\n";
   $my_sql_string .= 'FROM table_b' ;

Ref the docs for anything really fancy (like non-english / non-printable-ascii 
Unicode or unusual character encodings).

Brian Fennell





The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Translate between DBI and SQL

[Fennell, Brian]

P.P.S.

My last answer used single-quote for a column with a space in it in PostgreSQL 
- this is wrong, it should have been a double-quote for the name of the column 
with a space and a single quote for a quoted string literal in PostgreSQL.

Better example:

(Adapted from answer to question here

https://dba.stackexchange.com/questions/118059/quoting-columns-with-spaces-in-postgresql
)


$my_perl_sql_string= 'SELECT' . "\n";
$my_perl_sql_string.= '   tab."This IS My Column EXACTLY" AS col' . "\n";
$my_perl_sql_string.= 'FROM "My TabLE Name Contains Spaces Too!" tab' . "\n";
$my_perl_sql_string.= 'WHERE tab."ANOTHER UGLY COLUMN name" = 
\'MyFilterString\''

I don't know if PostgreSQL requires a trailing semi-colon when used in DBI/DBD 
or not.
I know that Oracle SQL requires a trailing semicolon in sqlplus but not in 
DBI/DBD.
I don't have a PostgreSQL database to try it out on.

Try it both ways, and the one that works is the one to use.

Brian Fennell




The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Translate between DBI and SQL

[Fennell, Brian]

Mike,
If you have a complete example of what you are starting with and what you want 
to end up with your question would be clearer.
If I understand you correctly:
You start with raw SQL such as you might enter into a Postgres command line 
tool (for example psql).
What you want is the equivalent Perl source code to define the identical SQL as 
a string literal.

Please look at the docs here:

https://metacpan.org/pod/perlop
https://metacpan.org/pod/perlop#Quote-and-Quote-like-Operators
https://metacpan.org/pod/perlop#Gory-details-of-parsing-quoted-constructs
https://metacpan.org/pod/perlop#Additive-Operators(Especially the “.” 
Operator)
https://metacpan.org/pod/perlop#Assignment-Operators(Especially the 
“.=” Assignment Operator)
https://metacpan.org/pod/perlfunc#ord-EXPR
https://metacpan.org/pod/perlfunc#chr-NUMBER

And see if that helps.






The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Generic way of fetching a list of databases

[Fennell, Brian]

If you like you can review the "data_sources" subroutine defined here:

https://metacpan.org/pod/DBI

If you find the completely Generic way to list databases Lacking - here are few 
particular solutions:

In Particular for PostgreSQL:
This lists databases:
SELECT datname as database
FROM pg_database
WHERE datistemplate = false;

This lists tables in the current database
SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;


https://dba.stackexchange.com/questions/1285/how-do-i-list-all-databases-and-tables-using-psql

https://dba.stackexchange.com/a/1304


In Particular for Mysql:

show databases

OR

SELECT SCHEMA_NAME AS Database
FROM INFORMATION_SCHEMA.SCHEMATA

https://stackoverflow.com/questions/4366905/perl-dbi-dbdmysql-get-the-databases-name-from-mysql-server

In Particular for Oracle (what mysql and postgress call "Database" Oracle calls 
"Schema"):

https://stackoverflow.com/a/298771

SELECT USERNAME as SCHEMA FROM ALL_USERS ORDER BY USERNAME;

Or

SELECT USERNAME as SCHEMA FROM DBA_USERS ORDER BY USERNAME;

Or

SELECT DISTINCT OWNER as SCHEMA from ALL_OBJECTS order by OWNER;

Or

SELECT DISTINCT OWNER as SCHEMA from DBA_OBJECTS order by OWNER;

https://dba.stackexchange.com/questions/27725/how-to-see-list-of-databases-in-oracle

https://stackoverflow.com/questions/8739203/oracle-query-to-fetch-column-names

https://stackoverflow.com/questions/4833459/oracle-sql-query-for-listing-all-schemas-in-a-db

https://ss64.com/orad/ALL_OBJECTS.html

https://ss64.com/orad/DBA_OBJECTS.html


In particular for SQLServer (SQLServer has both databases and schemas - schemas 
are like Oracle schemas, databases are groups of schemas).

SELECT name FROM master.sys.databases

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

SELECT CATALOG_NAME AS DataBaseName  FROM INFORMATION_SCHEMA.SCHEMATA


https://stackoverflow.com/questions/147659/get-list-of-databases-from-sql-server

https://stackoverflow.com/questions/3719623/how-do-i-obtain-a-list-of-all-schemas-in-a-sql-server-database

https://stackoverflow.com/questions/873393/sql-server-query-to-find-all-current-database-names

https://stackoverflow.com/questions/5323740/difference-between-database-and-schema







The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Generic way of fetching a list of databases

[Fennell, Brian]

Closest thing that I know of is INFORMATION_SCHEMA and the 
INFORMATION_SCHEMA.TABLES pseudo table.
INFORMATION_SCHEMA is a standard across many databases.  One Database that 
doesn’t comply is Oracle (but it can be installed by the DBA as an Add-on) for 
Oracle select from ALL_TABLES (or DBA_TABLES if you have administrative 
privileges or “read any data dictionary” privileges).
Every database that I have ever worked with has some way to list the tables as 
if there were a “table of tables” somewhere.
If you are working with a database where cannot select from 
INFORMATION_SCHEMA.TABLES than just search google/bing for the name of that 
database and “INFORMATION_SCHEMA.TABLES” to find the equivalent for your 
odd-ball database.
Put the whole thing in a subroutine that returns the same kind of data 
structure as a SELECT, adding perhaps an if/the/else structure to handle the 
different kinds and you should at least be able to keep all the messiness in 
one subroutine.


From: Scott Webster Wood via dbi-users 
Sent: Friday, November 16, 2018 10:26 AM
To: dbi-users@perl.org; Daniel Kasak 
Subject: Re: Generic way of fetching a list of databases

convert to 'rest'ful JSON?

 Barack-O-phobia: The fear of politicians who think (more) government is 
the solution to every problem.


On Thursday, November 15, 2018, 9:09:44 PM EST, Daniel Kasak 
mailto:d.j.kasak...@gmail.com>> wrote:


Hi all.

I have a project that has to support pretty much every major database around, 
plus a number of more esoteric "big data" platforms as well. Until now, I've 
had a bunch of methods, implementing fetching databases, schemas, tables, etc, 
per database, with database-specific queries ( eg against information_schema ).

Some of the newer databases I'm trying to support have very little 
documentation, and in some cases no apparent way of fetching in the schema via 
SQL. I've had a conversation with one of the tech support people for a DB 
product who said that there were generic ODBC functions we can call for this 
kind of thing. Is this the case?

I've done quite a bit of search, but can't find any docs that mention fetching 
*databases* - either in ODBC docs or in Perl/DBI docs. The closest I've found 
that *might* have worked was DBI's tables() method:
 
https://metacpan.org/pod/DBI#tables
  ... but:

- this doesn't work in cases where there is a separation between hierarchies at 
the database level ( eg postgres only lists schemas and tables in the current 
database )
- this isn't returning *anything* for me with any of the ODBC drivers I've tried

So is it possible to retrieve a list of databases in a generic way? Failing 
that, assuming that there *is* some ODBC call ( as suggested by one DB vendor ) 
that I can use, is there some way of calling it from Perl with DBD::ODBC?

Thanks :)

Dan



The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Tim Bunce - RE: DBD-DBI Insert Binary Files and using Perl modules in cPanel environment

[Fennell, Brian]

Correction:

MYSQL uses "BIGINT" (or another type with "INT" in its name) where Oracle uses 
NUMBER.

https://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm#BABHHAJC

The rest of what I said still stands (I think).

YMMV





The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: Tim Bunce - RE: DBD-DBI Insert Binary Files and using Perl modules in cPanel environment

[Fennell, Brian]

If you encode your binary data as pure ascii, in and out, any database will do 
just fine.

Encodings to consider are mime-64 (https://metacpan.org/pod/MIME::Base64) and 
q-encoding (https://en.wikipedia.org/wiki/Quoted-printable ) - both are 
lossless and can store any binary data.  Hexadecimal representation of the 
binary data also works but is less efficient.  You can also consider 
compressing the data before converting to a pure-ascii representation to help 
address space concerns.  XML with "character entities" (  encoding) is 
another option - or some mix of these.  To store large runs of text in the 
database you can break the text into segments.  If the "binary files" are 
mostly text with some "binary" characters you can use q-encoding fileid, line, 
segment (three fields of type "NUMBER" storing an integer).   If the data is 
truly binary (no discernable structure) you can use fileid, block, segment 
(three NUMBER fields).  You can then use VARCHAR with a size of 256 or 512 or 
1024 (etc) to store the encoded binary data as characters.  Databases which 
offer "BLOB" (Binary Large OBject) or "CLOB" (Character Large OBject) 
capability often do so in a way that trades efficiency for flexibility and 
standards compliance.  The method I suggest should not need anything beyond 
standard capabilities that every database, mysql included, offers.  It may be 
less efficient than choosing a specific database and exploiting the specific 
capabilities of that database.

If you stick to LCD (Least Common Denominator) for your database interactions 
then you can even make the database type configurable at run-time.

I once stored arbitrary text (log) files in an Oracle database which had a 
maximum VARCHAR size of 2048.  My code broke lines up into segments of no more 
than 512 characters and I q-encoded any non-ascii characters.  The source 
character encoding was a one-byte-to-a-character-always (Windows 1252) encoding 
so the worst case was expanding one character to three ( "=3d" represented "=" 
, while "=FC" represented "LATIN SMALL LETTER U WITH DIAERESIS")
I didn't have to worry about the database character set as long as it was a 
superset of ascii.  I could store files of any size and with any length lines.  
I chose simplicity over fighting with the non-standard BLOB or CLOB apis.

If the files had been really binary I could have used 
https://metacpan.org/pod/MIME::Base64 .

This is the perl code I used for MIME Q-Encoding:

sub mimeq
{
my($in)  = @_;
my($out);

  $out =  $in;
  $out =~ s/(\W)/sprintf('=%02x', ord($1))/eg ;

  return $out;

}

sub unmimeq
{
my($in)  = @_;
my($out);

  $out =  $in;
  $out =~ s/=([\da-fA-F][\da-fA-F])/chr hex $1/eg ;

  return $out;

}

And a variation I sometimes use:

sub mimeq-permissive
{
my($in)  = @_;
my($out);

  $out =  $in;
  $out =~ 
s{([^]\-\s|#'a-zA-Z0-9,+.?()*,_.:;%@&/[])}{sprintf("=%02x",ord($1))}eg ;

  return $out;

}

I agree that using a filename or a URL/URI in the database and storing the 
actual file data someplace else, pointed to by the database, is another option, 
and one that is often used.

If your "binary data" isn't very big and you really want to, you can just use 
mime-64 and a VARCHAR and go happily about your business.

The choice is yours.

Perl - there is more than one way to do it.





The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

RE: debugging and stepping into ->do

[Fennell, Brian]

Jeff,

Sorry if I was telling you something you already knew.  I do that sometimes – I 
am a computer geek and sometimes my social skills are lacking.

All the best in your XS hacking.

It looks to me that this is actually documented behavior (if you know that the 
documentation is on the DBI module page, and not the DBD::mysql module page):
- if you need an exception you can test for “undef” (or false) and do your own 
die
- If you need the actual error number / error string you can use $h->errstr 
and/or $h->err

https://metacpan.org/pod/DBI

[ . . . ]

err

$rv = $h->err;

Returns the native database engine error code from the last driver method 
called. The code is typically an integer but you should not assume that.

The DBI resets $h->err to undef before almost all DBI method calls, so the 
value only has a short lifespan. Also, for most drivers, the statement handles 
share the same error variable as the parent database handle, so calling a 
method on one handle may reset the error on the related handles.

(Methods which don't reset err before being called include err() and errstr(), 
obviously, state(), rows(), func(), trace(), trace_msg(), ping(), and the tied 
hash attribute FETCH() and STORE() methods.)

If you need to test for specific error conditions and have your program be 
portable to different database engines, then you'll need to determine what the 
corresponding error codes are for all those engines and test for all of them.

The DBI uses the value of $DBI::stderr as the err value for internal errors. 
Drivers should also do likewise. The default value for $DBI::stderr is 
20.

A driver may return 0 from err() to indicate a warning condition after a method 
call. Similarly, a driver may return an empty string to indicate a 'success 
with information' condition. In both these cases the value is false but not 
undef. The errstr() and state() methods may be used to retrieve extra 
information in these cases.

See "set_err" for more information.


errstr

$str = $h->errstr;

Returns the native database engine error message from the last DBI method 
called. This has the same lifespan issues as the "err" method described above.

The returned string may contain multiple messages separated by newline 
characters.

The errstr() method should not be used to test for errors, use err() for that, 
because drivers may return 'success with information' or warning messages via 
errstr() for methods that have not 'failed'.

See "set_err" for more information.

[ . . . ] 

RaiseError

Type: boolean, inherited

The RaiseError attribute can be used to force errors to raise exceptions rather 
than simply return error codes in the normal way. It is "off" by default. When 
set "on", any method which results in an error will cause the DBI to 
effectively do a die("$class $method failed: $DBI::errstr"), where $class is 
the driver class and $method is the name of the method that failed. E.g.,

DBD::Oracle::db prepare failed: ... error text here ...

If you turn RaiseError on then you'd normally turn PrintError off. If 
PrintError is also on, then the PrintError is done first (naturally).

[ . . . ]

do

$rows = $dbh->do($statement)   or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr)   or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr, @bind_values) or die ...

Prepare and execute a single statement. Returns the number of rows affected or 
undef on error. A return value of -1 means the number of rows is not known, not 
applicable, or not available.

This method is typically most useful for non-SELECT statements that either 
cannot be prepared in advance (due to a limitation of the driver) or do not 
need to be executed repeatedly. It should not be used for SELECT statements 
because it does not return a statement handle (so you can't fetch any data).

The default do method is logically similar to:

sub do {
my($dbh, $statement, $attr, @bind_values) = @_;
my $sth = $dbh->prepare($statement, $attr) or return undef;
$sth->execute(@bind_values) or return undef;
my $rows = $sth->rows;
($rows == 0) ? "0E0" : $rows; # always return true if no error
}

[ . . . ]

In other news – 

You may also be interested in the mysql UPSERT syntax “ON DUPLICATE KEY”

https://stackoverflow.com/questions/6107752/how-to-perform-an-upsert-so-that-i-can-use-both-new-and-old-values-in-update-par

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

That way you can have mysql handle the exceptional cases, which may be more 
efficient.

RE: (Fwd) Perl with Oracle 12c

[Fennell, Brian]

DBD::Oracle is hard to build from source  . . . just went thru this.
If you don't build from source it may not be compatible with your Oracle shared 
libraries, OS Shared libraries or Oracle Server.
Consider carefully Oracle client and server characterset (and Perl 
characterset, and OS characterset) - two flavors of Oracle "text" objects need 
to be considered - "CHAR" and "WCHAR" - for 8 combinations.
UTF-8 end-to-end would be nice, but your environment may not allow (ours 
didn't).
Read the general and the platform specific Troubleshooting guide here:
https://metacpan.org/release/DBD-Oracle

If you have to report a bug (or want to see is an error you are getting is a 
known bug) . . .
Bug tracker is here:
https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-Oracle
And here:
https://github.com/pythian/DBD-Oracle/issues

I found this one instructive:

install DBD::Oracle from CPAN - various problems #47
https://github.com/pythian/DBD-Oracle/issues/47

You will need not only the client installed but the Oracle "demo" DVD-ROM files 
which include the "sample" makefiles (which are required prerequisites for 
building DBD::Oracle - making them more "code" than "sample").
Also there is no maintainer for the DBD::Oracle module - or at least the 
maintainer is not active.  There is a "co-maintainer" but he basically 
maintains a fork in git now, so the official CPAN module is old.  Wasn't able 
to find "build from git" instructions.  I guess it is "git clone 
https://github.com/pythian/DBD-Oracle.git; followed by "perl Makefile.PL ; make 
; make test ; make install" (the usual incantation from the MakeMaker 
documentation).  If you reach out to him via CPAN he might respond.
I found a bug in the latest version (memory leak exposed when reading a very 
large number of rows, something to do with Unicode/Characterset conversion and 
buffer size), had to downgrade the perl AND the DBD::Oracle to work-around.
Prerequisites are gcc, gnu make, and Oracle Client that matches your database 
(or older client, but not too old).  Look for a ftp CPAN mirror ( 
http://mirrors.cpan.org/ ) if you want to download an older version to work 
around a bug.
If you are not already familiar - read up on 
https://metacpan.org/pod/ExtUtils::MakeMaker especially the section "Default 
Makefile Behaviour" (and other sections) - which is the hidden documentation to 
"how CPAN modules really work".
Seriously consider the DBD::JDBC module with Oracle JDBC driver (you will need 
a java process to act as a proxy).
If you want to build the DBD::Oracle module try to build it on a test box 
similar to your production box and post results here if you run into errors 
(compile errors or runtime errors).
Allocate time in your project for trial and error.  No "point and shoot" 
solution.  OS type and version matters if you are looking for a how-to on the 
internet.  Read all the README and INSTALL files.  Carefully.  Twice.

RE: Hunting down (possible) memory leak in DBD::Oracle

[Fennell, Brian]

Thanks, Chris,

I looked at the two kde bugs you pointed out.  Neither of them was an "invalid 
write".
Also the software is actually failing here - not "working but getting valgrind 
errors" as was the case with the kde bugs.

In my case I have "failing and getting valgrind errors".  I can run the same 
case on an older version of perl and an older version of "DBD::Oracle" module 
(with the exact same oracle database) and get no errors.

Valgrind documentation describes an "invalid write" as follows:

http://valgrind.org/docs/manual/mc-manual.html#mc-manual.badrw

For example:

Invalid read of size 4
   at 0x40F6BBCC: (within /usr/lib/libpng.so.2.1.0.9)
   by 0x40F6B804: (within /usr/lib/libpng.so.2.1.0.9)
   by 0x40B07FF4: read_png_image(QImageIO *) (kernel/qpngio.cpp:326)
   by 0x40AC751B: QImageIO::read() (kernel/qimage.cpp:3621)
 Address 0xB0E0 is not stack'd, malloc'd or free'd

This happens when your program reads or writes memory at a place which Memcheck 
reckons it shouldn't. In this example, the program did a 4-byte read at address 
0xB0E0, somewhere within the system-supplied library libpng.so.2.1.0.9, 
which was called from somewhere else in the same library, called from line 326 
of qpngio.cpp, and so on.

Memcheck tries to establish what the illegal address might relate to, since 
that's often useful. So, if it points into a block of memory which has already 
been freed, you'll be informed of this, and also where the block was freed. 
Likewise, if it should turn out to be just off the end of a heap block, a 
common result of off-by-one-errors in array subscripting, you'll be informed of 
this fact, and also where the block was allocated. If you use the 
--read-var-info option Memcheck will run more slowly but may give a more 
detailed description of any illegal address.

In this example, Memcheck can't identify the address. Actually the address is 
on the stack, but, for some reason, this is not a valid stack address -- it is 
below the stack pointer and that isn't allowed. In this particular case it's 
probably caused by GCC generating invalid code, a known bug in some ancient 
versions of GCC.

Note that Memcheck only tells you that your program is about to access memory 
at an illegal address. It can't stop the access from happening. So, if your 
program makes an access which normally would result in a segmentation fault, 
you program will still suffer the same fate -- but you will get a message from 
Memcheck immediately prior to this. In this particular example, reading junk on 
the stack is non-fatal, and the program stays alive.



-Original Message-
From: Christopher Jones [mailto:christopher.jo...@oracle.com] 
Sent: Monday, January 15, 2018 6:56 PM
To: dbi-users@perl.org
Subject: Re: Hunting down (possible) memory leak in DBD::Oracle



On 16/1/18 9:17 am, Fennell, Brian wrote:

> $ egrep -B1 -A20 -i 'invalid write' 
> /copy/sandbox/feeds/data/search4_1/valgrind-log.txt | head -22 
> ==19402== ==19402== Invalid write of size 4
> ==19402==at 0xBD747E6: __intel_ssse3_rep_memcpy (in 
> /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
> ==19402==by 0xBD6CE95: _intel_fast_memcpy.P (in 
> /db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)

Valgrind doesn't do the greatest on binaries built with the Intel compiler:

https://bugs.kde.org/show_bug.cgi?id=286769
https://bugs.kde.org/show_bug.cgi?id=139776

Chris

--
http://twitter.com/ghrd

RE: Hunting down (possible) memory leak in DBD::Oracle

[Fennell, Brian]

I got this case running with valgrind - 
Valgrind reported -
8 invalid writes, 8 invalid writes, 2239 accesses to uninitialized values.
All invalid writes have a stack trace leading back to 
XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662) and ora_st_fetch 
(oci8.c:4032)

Details follow:

export DATE_MANIP=DM5;
export ORACLE_HOME=/db/app/oracle/product/12.1.0/client_1 ;
export LD_LIBRARY_PATH=$ORACLE_HOME/lib ;
export 
PATH="$ORACLE_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin";
export NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1";
export NLS_NCHAR="AL16UTF16" ;
export NLS_NCHAR_CHARACTERSET="AL16UTF16" ;
export LANGUAGE=en_US:en:C;
export LANG=C;
export LC_ALL=C;
export LC_CTYPE=C;
/bin/rm -rf/feeds/data/search4_1/ProductAttributes-dbi-trace.txt\
   /feeds/data/search4_1/getTableData-debug-log.txt \
   /feeds/data/search4_1/valgrind-log.txt ;
/usr/local/bin/valgrind \
--tool=memcheck \
--leak-check=yes \
--track-origins=yes \
--leak-check=full \
--show-leak-kinds=all \
--num-callers=100 \
--error-limit=no \
--log-file=/feeds/data/search4_1/valgrind-log.txt \
/usr/local/bin/perl \
  [ . . . ]


$ wc -l /copy/sandbox/feeds/data/search4_1/valgrind-log.txt
696325 /copy/sandbox/feeds/data/search4_1/valgrind-log.txt

$ du -h /copy/sandbox/feeds/data/search4_1/valgrind-log.txt
53M /copy/sandbox/feeds/data/search4_1/valgrind-log.txt

$ egrep -c -i 'invalid write' 
/copy/sandbox/feeds/data/search4_1/valgrind-log.txt
8

$ egrep -c -i 'invalid read' /copy/sandbox/feeds/data/search4_1/valgrind-log.txt
8

$ egrep  -c -i 'uninitialised value' 
/copy/sandbox/feeds/data/search4_1/valgrind-log.txt
2239

$ egrep -n -A20  -i 'invalid write' 
/copy/sandbox/feeds/data/search4_1/valgrind-log.txt | egrep 
'(oci8\.c|Oracle\.xsi)'
51575-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032)
51576-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array 
(Oracle.xsi:662)
51596-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032)
51597-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array 
(Oracle.xsi:662)
51614-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032)
51615-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array 
(Oracle.xsi:662)
51670-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032)
51671-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array 
(Oracle.xsi:662)
51710-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032)
51711-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array 
(Oracle.xsi:662)
51731-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032)
51732-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array 
(Oracle.xsi:662)
51771-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032)
51772-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array 
(Oracle.xsi:662)
51830-==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032)
51831-==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array 
(Oracle.xsi:662)

$ egrep -B1 -A20 -i 'invalid write' 
/copy/sandbox/feeds/data/search4_1/valgrind-log.txt | head -22
==19402==
==19402== Invalid write of size 4
==19402==at 0xBD747E6: __intel_ssse3_rep_memcpy (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xBD6CE95: _intel_fast_memcpy.P (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xCF70D36: kpccclr (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xCF705D7: kpccs2c (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xCF6ED9E: ttccfpg (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xCF6C799: ttcfour (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xCF3B0AE: kpufcpf (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xCF392F5: kpufch0 (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xCF37C34: kpufch (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xB14F1CA: OCIStmtFetch2 (in 
/db/app/oracle/product/12.1.0/client_1/lib/libclntsh.so.12.1)
==19402==by 0xAA0F269: ora_st_fetch (oci8.c:4032)
==19402==by 0xA9EE595: XS_DBD__Oracle__st_fetchrow_array (Oracle.xsi:662)
==19402==by 0x6D33069: XS_DBI_dispatch (DBI.xs:3797)
==19402==by 0x4EF240E: Perl_pp_entersub (in 
/usr/lib64/perl5/CORE/libperl.so)
==19402==by 0x4EEAB85: Perl_runops_standard (in 
/usr/lib64/perl5/CORE/libperl.so)
==19402==by 0x4E87984: perl_run (in /usr/lib64/perl5/CORE/libperl.so)
==19402==by 0x400D98: ??? (in /usr/bin/perl)
==19402==by 0x6174C04: (below main) (in /usr/lib64/libc-2.17.so)
==19402==  Address 0x14ec4275f is 4,095,739,679 bytes inside a block of size 
4,095,741,856 in arena "client"
==19402==

RE: Hunting down (possible) memory leak in DBD::Oracle

[Fennell, Brian]

John,

Thanks for your incites.  I tried what you said.  

I read up on NVARCHAR2 v VARCHAR2 - interesting.  I also see that Oracle has a 
way (more than one way) to specify if a VARCHAR2 should contain bytes or 
characters - further while a VARCHAR2(11 byte) and a VARCHAR2(11 char) are 
different (the second can have as many as 4 times as many bytes in it as the 
first) VARCHAR2(4000 byte) and VARCHAR(4000 char) are not different - 4000 
bytes is the max for either - no matter the size of a character.

Unicode makes everything harder.  "CHAR" may mean "BYTE" "2 BYTES" or "4 
BYTES".  And client and server have to agree.

I tried this:  I uses SUBSTRB(field,1,) to truncate the actual bytes coming 
from oracle back to the client.  Results are a bit odd.
For 3999 no change.  For 2000 all errors disappear.   For 3000 - some cases 
that used to error now succeed - but some cases that used to succeed now fail.

I also played with "matching up" the inner select and the outer - which isn't 
really very valuable - the client never sees the data from the inner select - 
and I only added the outer select so I could select by row number (which I 
couldn't do on the inner select since "rownum" is a pseudo column).  The outer 
select is basically the interface that OCI sees.

Specifically cast(SUBSTRB(field,1,2000) as VARCHAR(4000)) errors out exactly 
the same way as no substrb().  

For the 2000 byte case I used cast(SUBSTRB(field,1,2000) as VARCHAR(2000 byte)).

For the 3000 byte case I used cast(SUBSTRB(field,1,3000) as VARCHAR(3000 byte)).

I don't think truncating to 2000 bytes is a solution, but I suppose I could try 
breaking the field into 2 2000 byte strings (or 4 1000 bytes strings) and 
recombine them in some other part of the code.  I would need to consider the 
best way to do that - perhaps a output column naming convention.

I ran all of my variations on the old server and the new server and nothing 
failed on the old server.  Too much output to send it all to the list - if 
anyone wants something specific I can send it.

My money is still on a wild pointer or similar.

I looked thru the DBI log and found the following various field rc error codes 
(sorted):

    field #3 with rc=12851(UNKNOWN RC=12851))
    field #3 with rc=12854(UNKNOWN RC=12854))
    field #3 with rc=20041(UNKNOWN RC=20041))
    field #3 with rc=25934(UNKNOWN RC=25934))
    field #3 with rc=26962(UNKNOWN RC=26962))
    field #3 with rc=48(UNKNOWN RC=48))
    field #3 with rc=83(UNKNOWN RC=83))
    field #4 with rc=1280(UNKNOWN RC=1280))
    field #4 with rc=12870(UNKNOWN RC=12870))
    field #4 with rc=14128(UNKNOWN RC=14128))
    field #4 with rc=17230(UNKNOWN RC=17230))
    field #4 with rc=18688(UNKNOWN RC=18688))
    field #4 with rc=24919(UNKNOWN RC=24919))
    field #4 with rc=25196(UNKNOWN RC=25196))
    field #4 with rc=25926(UNKNOWN RC=25926))
    field #4 with rc=26691(UNKNOWN RC=26691))

I am not really sure which goes with what case - but I am really not thinking 
that the codes are real oracle error codes - but junk data which is a symptom 
of a problem somewhere else.  I could modify the tests to clear out the log and 
capture the "UNKNOWN RC" codes for each, but I would rather to more digging on 
the original case I reported to this list.

Brian Fennell

RE: Hunting down (possible) memory leak in DBD::Oracle

[Fennell, Brian]

And, also with the log level set to 15 here are the LAST 200 lines

3abd340 (field=0): '1127646'
field #2 with rc=0(OK)

3abd340 (field=1): '1268251'
field #3 with rc=0(OK)

3abd340 (field=2): 'a...'
field #4 with rc=0(OK)

3abd340 (field=3): '1'
field #5 with rc=1405(NULL)

3abd340 (field=4): undef
field #6 with rc=0(OK)

3abd340 (field=5): 'en_US'
<- fetchrow_array= ( '1127646' '1268251' 'aa' '1' undef 'en_US' 
) [6 items] row858 at /dirname/scriptname.pl line 196
-> fetchrow_array for DBD::Oracle::st 
(AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010
dbd_st_fetch 6 fields...
dbd_st_fetched 6 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)

3abd340 (field=0): '1093644'
field #2 with rc=0(OK)

3abd340 (field=1): '1268251'
field #3 with rc=0(OK)

3abd340 (field=2): 'sampl...'
field #4 with rc=0(OK)

3abd340 (field=3): '1'
field #5 with rc=1405(NULL)

3abd340 (field=4): undef
field #6 with rc=0(OK)

3abd340 (field=5): 'en_US'
<- fetchrow_array= ( '1093644' '1268251' 'sampledata' '1' undef 'en_US' 
) [6 items] row859 at /dirname/scriptname.pl line 196
-> fetchrow_array for DBD::Oracle::st 
(AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010
dbd_st_fetch 6 fields...
dbd_st_fetched 6 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)

3abd340 (field=0): '1093997'
field #2 with rc=0(OK)

3abd340 (field=1): '1268252'
field #3 with rc=0(OK)

3abd340 (field=2): 'sampl...'
field #4 with rc=0(OK)

3abd340 (field=3): '1'
field #5 with rc=1405(NULL)

3abd340 (field=4): undef
field #6 with rc=0(OK)

3abd340 (field=5): 'en_US'
<- fetchrow_array= ( '1093997' '1268252' 'sampledata' '1' undef 'en_US' 
) [6 items] row860 at /dirname/scriptname.pl line 196
-> fetchrow_array for DBD::Oracle::st 
(AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010
dbd_st_fetch 6 fields...
dbd_st_fetched 6 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)

3abd340 (field=0): '1093904'
field #2 with rc=0(OK)

3abd340 (field=1): '1268252'
field #3 with rc=0(OK)

3abd340 (field=2): 'sampl...'
field #4 with rc=0(OK)

3abd340 (field=3): '1'
field #5 with rc=1405(NULL)

3abd340 (field=4): undef
field #6 with rc=0(OK)

3abd340 (field=5): 'en_US'
<- fetchrow_array= ( '1093904' '1268252' 'samplecc' '1' undef 'en_US' ) 
[6 items] row861 at /dirname/scriptname.pl line 196
-> fetchrow_array for DBD::Oracle::st 
(AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010
dbd_st_fetch 6 fields...
dbd_st_fetched 6 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)

3abd340 (field=0): '1127647'
field #2 with rc=0(OK)

3abd340 (field=1): '1268253'
field #3 with rc=0(OK)

3abd340 (field=2): 'd...'
field #4 with rc=0(OK)

3abd340 (field=3): '0'
field #5 with rc=1405(NULL)

3abd340 (field=4): undef
field #6 with rc=0(OK)

3abd340 (field=5): 'en_US'
<- fetchrow_array= ( '1127647' '1268253' 'd' '0' undef 'en_US' 
) [6 items] row862 at /dirname/scriptname.pl line 196
-> fetchrow_array for DBD::Oracle::st 
(AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010
dbd_st_fetch 6 fields...
dbd_st_fetched 6 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)

3abd340 (field=0): '1127648'
field #2 with rc=0(OK)

3abd340 (field=1): '1268253'
field #3 with rc=0(OK)

3abd340 (field=2): 'a...'
field #4 with rc=0(OK)

3abd340 (field=3): '0'
field #5 with rc=1405(NULL)

3abd340 (field=4): undef
field #6 with rc=0(OK)

3abd340 (field=5): 'en_US'
<- fetchrow_array= ( '1127648' '1268253' 'aa' '0' undef 'en_US' 
) [6 items] row863 at /dirname/scriptname.pl line 196
-> fetchrow_array for DBD::Oracle::st 
(AAA::DBI::Connection::st=HASH(0x3abd310)~0x39f75f0) thr#24d4010
dbd_st_fetch 6 fields...
dbd_st_fetched 6 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)

3abd340 (field=0): '1105426'
field #2 with rc=0(OK)

3abd340 (field=1): '1268253'
field #3 with rc=0(OK)

3abd340 (field=2): 

RE: Hunting down (possible) memory leak in DBD::Oracle

[Fennell, Brian]

With the log level set to 15 here are the first 200 lines of log

AAA::DBI::Connection::db=HASH(0x3abce00) trace level set to 0x0/15 (DBI @ 
0x0/0) in DBI 1.637-ithread (pid 12594)
-> STORE for DBD::Oracle::db 
(AAA::DBI::Connection::db=HASH(0x3abce00)~INNER 'RowCacheSize' 2097152) 
thr#24d4010
<- STORE= ( 1 ) [1 items] at /dirname/scriptname.pl line 78
-> prepare for DBD::Oracle::db 
(AAA::DBI::Connection::db=HASH(0x3abcef0)~0x3abce00 'SELECT [ yada yada yada 
]') thr#24d4010
New 'AAA::DBI::Connection::st' (for DBD::Oracle::st, 
parent=AAA::DBI::Connection::db=HASH(0x3abce00), id=undef)

dbih_setup_handle(AAA::DBI::Connection::st=HASH(0x3abd310)=>AAA::DBI::Connection::st=HASH(0x39f75f0),
 DBD::Oracle::st, 268ae18, Null!)
dbih_make_com(AAA::DBI::Connection::db=HASH(0x3abce00), 3abdfc0, 
DBD::Oracle::st, 464, 0) thr#24d4010
dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), Err, 
AAA::DBI::Connection::db=HASH(0x3abce00)) SCALAR(0x268acc8) (already defined)
dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), State, 
AAA::DBI::Connection::db=HASH(0x3abce00)) SCALAR(0x268ad88) (already defined)
dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), Errstr, 
AAA::DBI::Connection::db=HASH(0x3abce00)) SCALAR(0x268ad28) (already defined)
dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), TraceLevel, 
AAA::DBI::Connection::db=HASH(0x3abce00)) 15 (already defined)
dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), 
FetchHashKeyName, AAA::DBI::Connection::db=HASH(0x3abce00)) 'NAME' (already 
defined)
dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), HandleSetErr, 
AAA::DBI::Connection::db=HASH(0x3abce00)) undef (not defined)
dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), HandleError, 
AAA::DBI::Connection::db=HASH(0x3abce00)) undef (not defined)
dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), ReadOnly, 
AAA::DBI::Connection::db=HASH(0x3abce00)) undef (not defined)
dbih_setup_attrib(AAA::DBI::Connection::st=HASH(0x39f75f0), Profile, 
AAA::DBI::Connection::db=HASH(0x3abce00)) undef (not defined)
OCIHandleAlloc(3b2c0f0,3be0cc8,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIStmtPrepare(3ba3190,3b535c8,'SELECT [ yada yada yada 
]',513,1,0)=SUCCESS

OCIAttrGet(3ba3190,OCI_HTYPE_STMT,3be0cdc,0,OCI_ATTR_STMT_TYPE,3b535c8)=SUCCESS
dbd_st_prepare'd sql SELECT ( auto_lob1, check_sql1)
dbd_describe SELECT (EXPLICIT, lb 80)...

OCIStmtExecute(3b5b028,3ba3190,3b535c8,0,0,0,0,mode=DESCRIBE_ONLY,16)=SUCCESS

OCIAttrGet(3ba3190,OCI_HTYPE_STMT,7ffd7b72baa4,0,OCI_ATTR_PARAM_COUNT,3b535c8)=SUCCESS
OCIParamGet(3ba3190,4,3b535c8,3be10e0,1,OCI_HTYPE_STMT)=SUCCESS

OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1110,0,OCI_ATTR_DATA_TYPE,3b535c8)=SUCCESS

OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1112,0,OCI_ATTR_DATA_SIZE,3b535c8)=SUCCESS

OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1128,0,OCI_ATTR_CHAR_USED,3b535c8)=SUCCESS

OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be112a,0,OCI_ATTR_CHAR_SIZE,3b535c8)=SUCCESS

OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be112c,0,OCI_ATTR_CHARSET_ID,3b535c8)=SUCCESS

OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be112e,0,OCI_ATTR_CHARSET_FORM,3b535c8)=SUCCESS

OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1114,0,OCI_ATTR_PRECISION,3b535c8)=SUCCESS

OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1116,0,OCI_ATTR_SCALE,3b535c8)=SUCCESS

OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1117,0,OCI_ATTR_IS_NULL,3b535c8)=SUCCESS

OCIAttrGet(3ba2e90,OCI_DTYPE_PARAM,3be1118,7ffd7b72baac,OCI_ATTR_NAME,3b535c8)=SUCCESS
Describe col #1 type=2(NVARCHAR2)
Described col  1: dbtype 2(NVARCHAR2), scale -127, prec 0, nullok 1, name 
ROW_NUMBER
  : dbsize 22, char_used 0, char_size 0, csid 0, csform 0(0), 
disize 171
fbh 1: 'ROW_NUMBER' NULLable, otype   2->  5, dbsize 22/172, 
p0.s-127
OCIParamGet(3ba3190,4,3b535c8,3be1188,2,OCI_HTYPE_STMT)=SUCCESS

OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11b8,0,OCI_ATTR_DATA_TYPE,3b535c8)=SUCCESS

OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11ba,0,OCI_ATTR_DATA_SIZE,3b535c8)=SUCCESS

OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11d0,0,OCI_ATTR_CHAR_USED,3b535c8)=SUCCESS

OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11d2,0,OCI_ATTR_CHAR_SIZE,3b535c8)=SUCCESS

OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11d4,0,OCI_ATTR_CHARSET_ID,3b535c8)=SUCCESS

OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11d6,0,OCI_ATTR_CHARSET_FORM,3b535c8)=SUCCESS

OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11bc,0,OCI_ATTR_PRECISION,3b535c8)=SUCCESS

OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11be,0,OCI_ATTR_SCALE,3b535c8)=SUCCESS

OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11bf,0,OCI_ATTR_IS_NULL,3b535c8)=SUCCESS

OCIAttrGet(3ba07a0,OCI_DTYPE_PARAM,3be11c0,7ffd7b72baac,OCI_ATTR_NAME,3b535c8)=SUCCESS
Describe col #2 type=2(NVARCHAR2)
Described col  2: dbtype 

RE: Hunting down (possible) memory leak in DBD::Oracle

[Fennell, Brian]

John,

Thanks for the ideas to change the cache params - I will try that!

Here is the SQL and the field types:

SELECT
d.ROW_NUMBER,
d.f1,
d.f2,
d.f3,
d.f4,
d.f5
FROM
(
SELECT /*+ FULL(A) PARALLEL(A 6) */
rownum ROW_NUMBER,
A.field1 f1 ,
A.field2 f2,
A.field3 f3,
A.field4 f4,
B.field5 f5
FROM
tableA A,
tableB B
WHERE
B.field6  IN  ( 'TOK3', 'TOK4', 'TOK5' )
AND B.field7  LIKE'A%'
AND B.field8  IN  ('TOK1', 'TOK2')
AND B.fkfield1=   A.field1
ORDER BY
2, 3, 4, 5, 6
  ) d
WHERE
d.row_number <  202
AND d.row_number >= 100

Field Types:

A.field1 NUMBER(12)
A.field2 VARCHAR2(20)
A.field3 NUMBER(15,3)
A.field4 VARCHAR2(4000)
B.field5 VARCHAR2(5)
B.field6 VARCHAR2(20)
B.field7 VARCHAR2(8)
B.field8 VARCHAR2(8)
B.fkfield1   NUMBER(12)

RE: Hunting down (possible) memory leak in DBD::Oracle

[Fennell, Brian]

Pluta,

Looks like it is worth a try - when I looked at the project before it looked 
like it was for installing a "per user" perl.  Does it work for root / all 
users on a box as well?

Brian

RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **

[Fennell, Brian]

Good question - I have asked DBA and am waiting for a reply.

-Original Message-
From: Howard, Chris [mailto:howa...@prpa.org] 
Sent: Monday, December 18, 2017 11:36 AM
To: Fennell, Brian <fenne...@radial.com>; dbi-users@perl.org
Subject: RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **

Fennell,

Are you seeing anything on the database side, in the alert log, etc.?


Howard


-Original Message-
From: Fennell, Brian [mailto:fenne...@radial.com] 
Sent: Monday, December 18, 2017 8:30 AM
To: Howard, Chris <howa...@prpa.org>; dbi-users@perl.org
Subject: RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **

Thanks for the reply, Howard,

I am using the exact same database - a test database that has copies of the 
production data put into it once a day - besides that it doesn't change much 
(if at all) during the day.

The SQL I am using doesn't leave out a "bad guy" - I thought of that and 
actually had an off-by-one gap in some of my early tests.  Closed that hole by 
changing a ">" to a ">=".

Here is the SQL (with the original table and field names changed to allow for 
sharing

SELECT
d.ROW_NUMBER,
d.f1,
d.f2,
d.f3,
d.f4,
d.f5
FROM
(
SELECT /*+ FULL(A) PARALLEL(A 6) */
rownum ROW_NUMBER,
A.field1 f1 ,
A.field2 f2,
A.field3 f3,
A.field4 f4,
B.field5 f5
FROM
tableA A,
tableB B
WHERE
B.field6  IN  ( 'TOK3', 'TOK4', 'TOK5' )
AND B.field7  LIKE'A%'
AND B.field8  IN  ('TOK1', 'TOK2')
AND B.fkfield1=   A.field1
ORDER BY
1, 2, 3, 4, 5
  ) d
WHERE
d.row_number <  202
AND d.row_number >= 100

-Original Message-
From: Howard, Chris [mailto:howa...@prpa.org] 
Sent: Monday, December 18, 2017 9:21 AM
To: Fennell, Brian <fenne...@radial.com>; dbi-users@perl.org
Subject: RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **

Same database... do you mean the actual very same data source?

What is the Oracle error? 

To eliminate problems based on data (implicit conversions, that kind of thing) 
can you do a run from row 500,000 to 1,500,000 ?  (I think you have this 
covered, but maybe that row right at the breaking spot is somehow a bad guy.)




-Original Message-
From: Fennell, Brian [mailto:fenne...@radial.com]
Sent: Saturday, December 16, 2017 3:19 PM
To: dbi-users@perl.org
Subject: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **

Dear DBI people - 

I am trying to port some old perl code to a new box.  (see Details below) 
Needless to say the original box and code works fine, but the new box (and old 
code) does not.
Specifically what I am seeing is that when I select slightly over a million 
records from a specific join of two tables (to be dumped one row at a time into 
a TSV file) we get strange ORA-N errors that don't really make any sense in 
this context.
The Same database and same table works fine on the original box with the large 
number of records.  2 million records always causes errors but two groups of 
1million (divided up by ROWNUM - the EXACT same rows) causes no errors.  I am 
using a test database with little activity do I am reasonably certain that the 
queries deal with the same rows.
So I am thinking the problem is data volume and not any specific piece of data 
(originally I thought it might be an odd string/data related error, but I am 
starting to think it is a memory leak of some kind).
The error always happens inside of fetchrow_array - and "$dbh->trace( 4 , 
$filename )" shows that the error originates inside the DBD::Oracle module 
while reading field 3 of 6.  
Researching the ORA-NN error gives a perfectly sane description that makes 
no sense at all in the context of reading a specific field.  

We are going thru an Audit and tightening up security so there are some things 
(like REAL hostnames and REAL column/table names) that I cannot share - but I 
will try to share as much as I can.

The Host I am calling "prod" below is the only one NOT exhibiting this issue.

Things I want to try - 

1) recompile the DBD::Oracle module on Host "sandbox" with "perl Makefile.PL 
-g" and then use Valgrind.  I haven't used Valgrind before, but I guess it is 
time to learn.
2) Anything else this list suggests. 

Details:

Host: prod
OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
Perl: 5.8.8 built for x86_64-linux
DBI: 1.53
DBD::Oracle: 1.19
Oracle: 10.2.0.1.0

Host: dev
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Perl: 5.16.1  built for x86_64-linux
DBI: 1.631
DBD::Oracle: 1.74
Oracle: 11.2.0.3.0

Host: prodnew
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Perl: 5.16.1  built f

RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **

[Fennell, Brian]

Ok . . . I am already benefiting from the support from this list : - ) 

I noticed that I was actually ordering my query BY rownum (which doesn't make 
much sense . . . and perhaps oracle's optimizer recognized this and ignored the 
pseudo column.)

Just to be sure, I change the query and then reran my tests - the results, 
however, did not change

New SQL

SELECT
d.ROW_NUMBER,
d.f1,
d.f2,
d.f3,
d.f4,
d.f5
FROM
(
SELECT /*+ FULL(A) PARALLEL(A 6) */
rownum ROW_NUMBER,
A.field1 f1 ,
A.field2 f2,
A.field3 f3,
A.field4 f4,
B.field5 f5
FROM
tableA A,
tableB B
WHERE
B.field6  IN  ( 'TOK3', 'TOK4', 'TOK5' )
AND B.field7  LIKE'A%'
AND B.field8  IN  ('TOK1', 'TOK2')
AND B.fkfield1=   A.field1
ORDER BY
2, 3, 4, 5, 6
  ) d
WHERE
d.row_number <  202
AND d.row_number >= 100

Here is a summary of the test results (I have a test harness which uses ssh to 
run the exact same tests on more than one host).

INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 200   0 ) ) OUTPUT: ( 
ORACLE_ERROR_COUNT = 2 )
INPUT: ( ( HOSTNAME = prod) ( RANGE = 200   0 ) ) OUTPUT: ( 
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 100   0 ) ) OUTPUT: ( 
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = prod) ( RANGE = 100   0 ) ) OUTPUT: ( 
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 200 100 ) ) OUTPUT: ( 
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = prod) ( RANGE = 200 100 ) ) OUTPUT: ( 
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 202 100 ) ) OUTPUT: ( 
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = prod) ( RANGE = 202 100 ) ) OUTPUT: ( 
ORACLE_ERROR_COUNT = 0 )
INPUT: ( ( HOSTNAME = sandbox ) ( RANGE = 203 100 ) ) OUTPUT: ( 
ORACLE_ERROR_COUNT = 2 )
INPUT: ( ( HOSTNAME = prod) ( RANGE = 203 100 ) ) OUTPUT: ( 
ORACLE_ERROR_COUNT = 0 )

RE: Hunting down (possible) memory leak in DBD::Oracle

[Fennell, Brian]

John,
Thanks so much for your reply!

I have put off this work for a few years and now the pressure is on - the 
original box and OS are so old that the DBA and System Engineer and the 
Operations manager have all ganged up on me.

I suppose I could try and work around by downgrading both the perl and the 
DBD::Oracle to the same version we use in production, but it would be nice to 
actually fix the bug if I can.

I tried just downgrading the DBD::Oracle, but changes in perl 5 to support 
MULTIPLICITY made that look like more than just a little work - spend two days 
on it and then backed off.  

I am a polyglot programmer so I can program in C and Perl (and about a dozen 
other languages).  I have done enough time with C that it doesn't scare me.  
Valgrind is new to me, but make and gcc and ld are not.
I have started to read the Valgrind docs and it seems to make sense - it 
basically emulates all the CPU instructions with injected instrumentation - I 
assume it works for Intel and Red Hat if it works at all 
(and it seems to have a long history and good open source support community).  
Perhaps I am fooling myself, but I figure it is worth a try.

I have negotiated support from both DBA and System Engineering (the Red Hat OS 
guys) so if I am going to fix this now is the time.

The only other option I can think of is to try to get the old code working with 
the DBD::JDBC driver (which would mean adding a JVM running in parallel and 
additional overhead - so I would rather not).

1) The error changes depending on the data - which is why I think it is a 
buffer overrun or a wild pointer - but it is  always in "field N of N" - 
Current I can reproduce with ORA-01403
2) I will re-try at level 15 and post the results - current at 4 (or perhaps 5) 
here is a section from the log (which suggests to me it is happing in the C 
code and not in the Perl

-> fetchrow_array for DBD::Oracle::st 
(GSI::DBI::Connection::st=HASH(0x29353c8)~0x286f6b0) thr#134c010
dbd_st_fetch 6 fields...
dbd_st_fetched 6 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)
field #2 with rc=0(OK)
field #3 with rc=0(OK)
field #4 with rc=1405(NULL)
field #5 with rc=0(OK)
field #6 with rc=0(OK)
-> fetchrow_array for DBD::Oracle::st 
(GSI::DBI::Connection::st=HASH(0x29353c8)~0x286f6b0) thr#134c010
dbd_st_fetch 6 fields...
dbd_st_fetched 6 fields with status of 0(SUCCESS)
field #1 with rc=0(OK)
field #2 with rc=0(OK)
field #3 with rc=0(OK)
field #4 with rc=14135(UNKNOWN RC=14135))
OCIErrorGet after ORA-14135 error on field 4 of 6, ora_type 2 (er1:ok): 
-1, 1403: ORA-01403: no data found

-- HandleSetErr err=1403, errstr='ORA-01403: no data found (DBD ERROR: 
ORA-14135 error on field 4 of 6, ora_type 2)', state=undef, undef
field #5 with rc=0(OK)
field #6 with rc=0(OK)
1   -> FETCH for DBD::Oracle::st 
(GSI::DBI::Connection::st=HASH(0x286f6b0)~INNER 'ParamValues') thr#134c010

3) I think the most exotic thing in these tables is a VARCHAR2 but I will check 
and post the results.
4) I looks like it is in the XS to me (see answer to 2) - but I suppose it 
could be elsewhere - like a loopback-perl-ref that should be weak but is not.
5) I think I have what I need, DBA installed Oracle 12 OCI client and "dot.so" 
libraries but currently I am concerned that I am using "ins_rdbms.mk" when I 
should be using "demo.mk" or similar - I am getting a Warning (see details 
below) when I run Makefile.PL - I asked DBA to look into installing the 
"demo.mk" file and consider opening up a Oracle METALINK support ticket to see 
if another customer had already solved this with Oracle's help.

Details:

# /usr/local/bin/perl Makefile.PL -g
Using DBI 1.637 (for perl 5.016003 on x86_64-linux-thread-multi) installed in 
/usr/local/lib64/perl5/auto/DBI/

Configuring DBD::Oracle for perl 5.016003 on linux (x86_64-linux-thread-multi)

Remember to actually *READ* the README file! Especially if you have any 
problems.

Installing on a linux, Ver#3.10
Using Oracle in /db/app/oracle/product/12.1.0/client_1
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
Oracle version 12.1.0.2 (12.1)
Found /db/app/oracle/product/12.1.0/client_1/rdbms/lib/ins_rdbms.mk
Using /db/app/oracle/product/12.1.0/client_1/rdbms/lib/ins_rdbms.mk
Your LD_LIBRARY_PATH env var is set to 
'/db/app/oracle/product/12.1.0/client_1/lib:/db/app/oracle/product/12.1.0/client_1'
Reading /db/app/oracle/product/12.1.0/client_1/rdbms/lib/ins_rdbms.mk
Reading /db/app/oracle/product/12.1.0/client_1/rdbms/lib/env_rdbms.mk
WARNING: Oracle /db/app/oracle/product/12.1.0/client_1/rdbms/lib/ins_rdbms.mk 
doesn't define a 'build' rule.

WARNING: I will now try to guess how to build and link DBD::Oracle for you.
 This kind of guess work is very error prone and Oracle-version 
sensitive.
 It is possible that it won't be supported in future versions of 
DBD::Oracle.
 

RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **

[Fennell, Brian]

Thanks for the reply, Howard,

I am using the exact same database - a test database that has copies of the 
production data put into it once a day - besides that it doesn't change much 
(if at all) during the day.

The SQL I am using doesn't leave out a "bad guy" - I thought of that and 
actually had an off-by-one gap in some of my early tests.  Closed that hole by 
changing a ">" to a ">=".

Here is the SQL (with the original table and field names changed to allow for 
sharing

SELECT
d.ROW_NUMBER,
d.f1,
d.f2,
d.f3,
d.f4,
d.f5
FROM
(
SELECT /*+ FULL(A) PARALLEL(A 6) */
rownum ROW_NUMBER,
A.field1 f1 ,
A.field2 f2,
A.field3 f3,
A.field4 f4,
B.field5 f5
FROM
tableA A,
tableB B
WHERE
B.field6  IN  ( 'TOK3', 'TOK4', 'TOK5' )
AND B.field7  LIKE'A%'
AND B.field8  IN  ('TOK1', 'TOK2')
AND B.fkfield1=   A.field1
ORDER BY
1, 2, 3, 4, 5
  ) d
WHERE
d.row_number <  202
AND d.row_number >= 100

-Original Message-
From: Howard, Chris [mailto:howa...@prpa.org] 
Sent: Monday, December 18, 2017 9:21 AM
To: Fennell, Brian <fenne...@radial.com>; dbi-users@perl.org
Subject: RE: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **

Same database... do you mean the actual very same data source?

What is the Oracle error? 

To eliminate problems based on data (implicit conversions, that kind of thing) 
can you do a run from row 500,000 to 1,500,000 ?  (I think you have this 
covered, but maybe that row right at the breaking spot is somehow a bad guy.)




-Original Message-
From: Fennell, Brian [mailto:fenne...@radial.com]
Sent: Saturday, December 16, 2017 3:19 PM
To: dbi-users@perl.org
Subject: Hunting down (possible) memory leak in DBD::Oracle ** EXTERNAL **

Dear DBI people - 

I am trying to port some old perl code to a new box.  (see Details below) 
Needless to say the original box and code works fine, but the new box (and old 
code) does not.
Specifically what I am seeing is that when I select slightly over a million 
records from a specific join of two tables (to be dumped one row at a time into 
a TSV file) we get strange ORA-N errors that don't really make any sense in 
this context.
The Same database and same table works fine on the original box with the large 
number of records.  2 million records always causes errors but two groups of 
1million (divided up by ROWNUM - the EXACT same rows) causes no errors.  I am 
using a test database with little activity do I am reasonably certain that the 
queries deal with the same rows.
So I am thinking the problem is data volume and not any specific piece of data 
(originally I thought it might be an odd string/data related error, but I am 
starting to think it is a memory leak of some kind).
The error always happens inside of fetchrow_array - and "$dbh->trace( 4 , 
$filename )" shows that the error originates inside the DBD::Oracle module 
while reading field 3 of 6.  
Researching the ORA-NN error gives a perfectly sane description that makes 
no sense at all in the context of reading a specific field.  

We are going thru an Audit and tightening up security so there are some things 
(like REAL hostnames and REAL column/table names) that I cannot share - but I 
will try to share as much as I can.

The Host I am calling "prod" below is the only one NOT exhibiting this issue.

Things I want to try - 

1) recompile the DBD::Oracle module on Host "sandbox" with "perl Makefile.PL 
-g" and then use Valgrind.  I haven't used Valgrind before, but I guess it is 
time to learn.
2) Anything else this list suggests. 

Details:

Host: prod
OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
Perl: 5.8.8 built for x86_64-linux
DBI: 1.53
DBD::Oracle: 1.19
Oracle: 10.2.0.1.0

Host: dev
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Perl: 5.16.1  built for x86_64-linux
DBI: 1.631
DBD::Oracle: 1.74
Oracle: 11.2.0.3.0

Host: prodnew
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Perl: 5.16.1  built for x86_64-linux
DBI: 1.631
DBD::Oracle: 1.70
Oracle: 11.2.0.1.0

Host: sandbox
OS: CentOS Linux release 7.4.1708 (Core)
Perl: 5.16.3  built for x86_64-linux-thread-multi
DBI: 1.637
DBD::Oracle: 1.74
Oracle: 12.1.0.2.0

--
Brian Fennell, Software Engineer | Radial
O: 610 491 7308 | M: 484 354 1699
fenne...@radial.com

The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you 

Hunting down (possible) memory leak in DBD::Oracle

[Fennell, Brian]

Dear DBI people - 

I am trying to port some old perl code to a new box.  (see Details below) 
Needless to say the original box and code works fine, but the new box (and old 
code) does not.
Specifically what I am seeing is that when I select slightly over a million 
records from a specific join of two tables (to be dumped one row at a time into 
a TSV file) we get strange ORA-N errors that don't really make any sense in 
this context.
The Same database and same table works fine on the original box with the large 
number of records.  2 million records always causes errors but two groups of 
1million (divided up by ROWNUM - the EXACT same rows) causes no errors.  I am 
using a test database with little activity do I am reasonably certain that the 
queries deal with the same rows.
So I am thinking the problem is data volume and not any specific piece of data 
(originally I thought it might be an odd string/data related error, but I am 
starting to think it is a memory leak of some kind).
The error always happens inside of fetchrow_array - and "$dbh->trace( 4 , 
$filename )" shows that the error originates inside the DBD::Oracle module 
while reading field 3 of 6.  
Researching the ORA-NN error gives a perfectly sane description that makes 
no sense at all in the context of reading a specific field.  

We are going thru an Audit and tightening up security so there are some things 
(like REAL hostnames and REAL column/table names) that I cannot share - but I 
will try to share as much as I can.

The Host I am calling "prod" below is the only one NOT exhibiting this issue.

Things I want to try - 

1) recompile the DBD::Oracle module on Host "sandbox" with "perl Makefile.PL 
-g" and then use Valgrind.  I haven't used Valgrind before, but I guess it is 
time to learn.
2) Anything else this list suggests. 

Details:

Host: prod
OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
Perl: 5.8.8 built for x86_64-linux
DBI: 1.53
DBD::Oracle: 1.19
Oracle: 10.2.0.1.0

Host: dev
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Perl: 5.16.1  built for x86_64-linux
DBI: 1.631
DBD::Oracle: 1.74
Oracle: 11.2.0.3.0

Host: prodnew
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Perl: 5.16.1  built for x86_64-linux
DBI: 1.631
DBD::Oracle: 1.70
Oracle: 11.2.0.1.0

Host: sandbox
OS: CentOS Linux release 7.4.1708 (Core)
Perl: 5.16.3  built for x86_64-linux-thread-multi
DBI: 1.637
DBD::Oracle: 1.74
Oracle: 12.1.0.2.0

--
Brian Fennell, Software Engineer | Radial
O: 610 491 7308 | M: 484 354 1699
fenne...@radial.com

The information contained in this electronic mail transmission is intended only 
for the use of the individual or entity named in this transmission. If you are 
not the intended recipient of this transmission, you are hereby notified that 
any disclosure, copying or distribution of the contents of this transmission is 
strictly prohibited and that you should delete the contents of this 
transmission from your system immediately. Any comments or statements contained 
in this transmission do not necessarily reflect the views or position of Radial 
or its subsidiaries and/or affiliates.

 >++[>++>++>++>+++>+++>++>+++>+++><-]>-->++>+>>>+>-->--><>.>.>.>.>.>.>.>.