Re: Weird issues using DBI + mod_perl
Just a follow up…it was embarrassingly stupid. You know how you can stare at an error in a line dozens of times and not see it? Like the ‘#' commenting out "use Apache::DBI” in your startup.pl file ... On Aug 16, 2019, at 2:47 PM, Andreas Mock mailto:[email protected]>> wrote: Hi Bruce, I'm just guessing, but this sounds much like reusing or double using of Oracle db handles, context handles or statement handles. You have to have a look at the bookkeeping of the handles per request. Also forking of the childs may byte you. You must ensure that db handles are ONLY opened in the child process doing the work and get closed properly. With DBI in use you can use the variable DBI_TRACE to get extensive log. Google for that in combination with mod_perl. Just some hints. Best regards Andreas -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: Weird issues using DBI + mod_perl
I'll second that. Try being hyper-paranoid about handles -- explicitly finish them at the earliest opportunity. Close everything before a fork. I have had too many lapses of sanitary etiquette bite me in weird ways. On 20190816, Andreas Mock wrote: Hi Bruce, I'm just guessing, but this sounds much like reusing or double using of Oracle db handles, context handles or statement handles. You have to have a look at the bookkeeping of the handles per request. Also forking of the childs may byte you. You must ensure that db handles are ONLY opened in the child process doing the work and get closed properly. With DBI in use you can use the variable DBI_TRACE to get extensive log. Google for that in combination with mod_perl. Just some hints. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman & wood chipper / [email protected] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o
Re: Weird issues using DBI + mod_perl
Also make sure you set AutoInactiveDestroy, which would be default if not for back compat, otherwise a fork that doesn't even use the handle may close your connections. Making sure connections are specific to one process can be tedious, especially when you are not controlling the processes, one easier way to manage it is with DBIx::Connector (https://metacpan.org/pod/DBIx::Connector), the DBIx::Connector object can be shared between any forks and whenever ->run or ->txn or ->dbh is called it will make sure to get or create a connection specific to that process. -Dan On Fri, Aug 16, 2019 at 8:34 PM Andreas Mock wrote: > Hi Bruce, > > I'm just guessing, but this sounds much like reusing or double using of > Oracle db handles, context handles or statement handles. > > You have to have a look at the bookkeeping of the handles per request. > Also forking of the childs may byte you. > You must ensure that db handles are ONLY opened in the child process doing > the work and get closed properly. > > With DBI in use you can use the variable DBI_TRACE to get extensive log. > Google for that in combination with mod_perl. > > Just some hints. > > Best regards > Andreas > > > -Ursprüngliche Nachricht- > Von: Bruce Johnson > Gesendet: Freitag, 16. August 2019 23:36 > An: dbi users ; mod_perl list > > Betreff: Weird issues using DBI + mod_perl > > So I’ve built a site using mod_perl in CGI mode, with DBI and Oracle as my > database back end. > > Everything works fine for a while but then I get intermittent weird errors > that start happening for every page load, which is resulting in > non-functional pages and error messages “Something happened contact your > system administrator” > > "DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD > ERROR: OCIStmtExecute) [for Statement "select to_char(add_months(sysdate, > 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual”]” at > /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 267, line > 522.\n > > There are no bound variables in the query... > > Another one: [error] DBD::Oracle::st execute failed: ORA-01007: variable > not in select list (DBD ERROR: OCIStmtExecute) [for Statement "select > to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, > 24), 'J') from dual"] at > /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 267, line > 522.\n > > Other errors that happen are : > > DBD::Oracle::st execute failed: ORA-01007: variable not in select list > (DBD ERROR: OCIStmtExecute) [for Statement "select pid, cn, email, afflist, > bldg_id, start_task from login_info where cookie =?" with ParamValues: > :p1='NS9DdciuH7XPeSVygRyUjoviZ’] > > DBD::Oracle::db prepare failed: ORA-01002: fetch out of sequence (DBD > ERROR: OCIStmtExecute/Describe) [for Statement "select > to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, > 24), 'J') from dual"] at > /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 266, line > 522.\n > > DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication > protocol error (DBD ERROR: OCIStmtExecute) [for Statement "select pid, cn, > email, afflist, bldg_id, start_task from login_info where cookie =?" with > ParamValues: :p1='v2bpY8jnoPw5yf0x71I4wZPx6'] at > /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 307 > > Most of these errors (that cause the DBI stuff to break) are happening in > this defaults module, which IS referenced more than once per page load as > the pages have ajax calls to other scripts that reference this module. > > restarting httpd fixes the problem, for a while. This isn’t a very heavily > loaded site, right now it’s the middle of the afternoon and I'm seeing > 'accesses per minute' kind of traffic. > > And clues as to where I should start to look? > > -- > Bruce Johnson > University of Arizona > College of Pharmacy > Information Technology Group > > Institutions do not have opinions, merely customs >
AW: Weird issues using DBI + mod_perl
Hi Bruce, I'm just guessing, but this sounds much like reusing or double using of Oracle db handles, context handles or statement handles. You have to have a look at the bookkeeping of the handles per request. Also forking of the childs may byte you. You must ensure that db handles are ONLY opened in the child process doing the work and get closed properly. With DBI in use you can use the variable DBI_TRACE to get extensive log. Google for that in combination with mod_perl. Just some hints. Best regards Andreas -Ursprüngliche Nachricht- Von: Bruce Johnson Gesendet: Freitag, 16. August 2019 23:36 An: dbi users ; mod_perl list Betreff: Weird issues using DBI + mod_perl So I’ve built a site using mod_perl in CGI mode, with DBI and Oracle as my database back end. Everything works fine for a while but then I get intermittent weird errors that start happening for every page load, which is resulting in non-functional pages and error messages “Something happened contact your system administrator” "DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: OCIStmtExecute) [for Statement "select to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual”]” at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 267, line 522.\n There are no bound variables in the query... Another one: [error] DBD::Oracle::st execute failed: ORA-01007: variable not in select list (DBD ERROR: OCIStmtExecute) [for Statement "select to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual"] at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 267, line 522.\n Other errors that happen are : DBD::Oracle::st execute failed: ORA-01007: variable not in select list (DBD ERROR: OCIStmtExecute) [for Statement "select pid, cn, email, afflist, bldg_id, start_task from login_info where cookie =?" with ParamValues: :p1='NS9DdciuH7XPeSVygRyUjoviZ’] DBD::Oracle::db prepare failed: ORA-01002: fetch out of sequence (DBD ERROR: OCIStmtExecute/Describe) [for Statement "select to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual"] at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 266, line 522.\n DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtExecute) [for Statement "select pid, cn, email, afflist, bldg_id, start_task from login_info where cookie =?" with ParamValues: :p1='v2bpY8jnoPw5yf0x71I4wZPx6'] at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 307 Most of these errors (that cause the DBI stuff to break) are happening in this defaults module, which IS referenced more than once per page load as the pages have ajax calls to other scripts that reference this module. restarting httpd fixes the problem, for a while. This isn’t a very heavily loaded site, right now it’s the middle of the afternoon and I'm seeing 'accesses per minute' kind of traffic. And clues as to where I should start to look? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Weird issues using DBI + mod_perl
So I’ve built a site using mod_perl in CGI mode, with DBI and Oracle as my database back end. Everything works fine for a while but then I get intermittent weird errors that start happening for every page load, which is resulting in non-functional pages and error messages “Something happened contact your system administrator” "DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: OCIStmtExecute) [for Statement "select to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual”]” at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 267, line 522.\n There are no bound variables in the query... Another one: [error] DBD::Oracle::st execute failed: ORA-01007: variable not in select list (DBD ERROR: OCIStmtExecute) [for Statement "select to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual"] at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 267, line 522.\n Other errors that happen are : DBD::Oracle::st execute failed: ORA-01007: variable not in select list (DBD ERROR: OCIStmtExecute) [for Statement "select pid, cn, email, afflist, bldg_id, start_task from login_info where cookie =?" with ParamValues: :p1='NS9DdciuH7XPeSVygRyUjoviZ’] DBD::Oracle::db prepare failed: ORA-01002: fetch out of sequence (DBD ERROR: OCIStmtExecute/Describe) [for Statement "select to_char(add_months(sysdate, 24), 'MM/DD/'), to_char(add_months(sysdate, 24), 'J') from dual"] at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 266, line 522.\n DBD::Oracle::st execute failed: ORA-03106: fatal two-task communication protocol error (DBD ERROR: OCIStmtExecute) [for Statement "select pid, cn, email, afflist, bldg_id, start_task from login_info where cookie =?" with ParamValues: :p1='v2bpY8jnoPw5yf0x71I4wZPx6'] at /home/allwebfiles/perl/LocalModules/Cal4Defaults.pm line 307 Most of these errors (that cause the DBI stuff to break) are happening in this defaults module, which IS referenced more than once per page load as the pages have ajax calls to other scripts that reference this module. restarting httpd fixes the problem, for a while. This isn’t a very heavily loaded site, right now it’s the middle of the afternoon and I'm seeing 'accesses per minute' kind of traffic. And clues as to where I should start to look? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
