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

[John Scoles]

Well it will be in either one of two .c files  dbdimp.c or oci8.c  The XS side 
of things Oracle.xs is not used very much.


The level 15 debug will get deep inside the c to see where it is happening. The 
trace you gave is a little high level


you are right ORA-01403 dose not make much sense here.


If could be running out of buffer.  Give some of the caching params a tweak


https://metacpan.org/pod/DBD::Oracle#RowCacheSize


if you can try give  fetchrow_hashref a try as see if the error happens there 
as well.


Cheers

John

DBD::Oracle - Oracle database driver for the DBI module 
...
metacpan.org
Oracle database driver for the DBI module ... NAME; VERSION; SYNOPSIS; 
DESCRIPTION; CONSTANTS; DBI CLASS METHODS. connect. OS authentication






From: Fennell, Brian 
Sent: December 18, 2017 11:25 AM
To: John Scoles; dbi-users@perl.org
Subject: RE: Hunting down (possible) memory leak in DBD::Oracle

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