Re: Problem with DBD::Oracle with mod_perl
PH == Perrin Harkins [EMAIL PROTECTED] writes: PH Don't open a connection during startup. If you do, it will be shared when PH Apache forks, and sharing a database handle is bad for the same reasons PH sharig a file handle is. Open a connection in the child process instead. Speaking of which, what does one do when Apache::DBI is loaded, and the parent process needs to pull some config info out of the database that all clients also use? That is, how can I force Apache::DBI to close that handle prior to the forking of children? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
Re: Problem with DBD::Oracle with mod_perl
From: Benjamin Trott [EMAIL PROTECTED] PH Don't open a connection during startup. PH Open a connection in the child process instead. BT I will second this. I've done this (unintentionally) when using MySQL, and BT you get a lot of weird errors about statement handles being active, etc. I haven't read the entire thread, so forgive me if I'm restating. But there are reasons for sharing DB connections (and reasons not to). In the case of MySQL and getting messages about active statement handles, that's because you're disconnecting the DBH. If you are in a shared connection enviroment with MySQL, you are winning. Since you don't have to worry about things like transactions and rolling back somebody elses changes. All you need to do is finish() your statement handled when you are finished with them: $sth-finish; This will prevent the errors you are seeing and will allow you to keep the shared DB connection. A shared connection is usefull for efficiency, since creating a connection may be expensive. From: Rasoul Hajikhani [EMAIL PROTECTED] I am sorry but this topic is confusing me... Are you saying that persistent DB connection objects are bad? It sounds like that, doesn't it? I am about to switch to Apache::DBI for that very reason. I don't want to create a new db handle with every request. Should I stop, or am I missing something here? Nope, you've got it. If you don't have transactions (anything else?) to worry about, I'd say to use Apache::DBI. Remember, from the Apache::DBI docs: Do NOT change anything in your scripts. The usage of this module is absolutely transparent ! --- Rodney Broom Programmer: Desert.Net
Re: Problem with DBD::Oracle with mod_perl
Rodney Broom [EMAIL PROTECTED] said something to this effect on 08/23/2001: From: Rasoul Hajikhani [EMAIL PROTECTED] I am sorry but this topic is confusing me... Are you saying that persistent DB connection objects are bad? It sounds like that, doesn't it? This is only when one handle is shared among many children. Sharing an open handle in the same child (i.e., by successive requests) is what you want to achieve. Since each child services requests serially, each handle is being used onlt once at a time, but the handle does not disconnect at the end of the request. (darren) -- Occam's Razor: The explanation requiring the fewest assumptions is probably the correct one.
Re: Problem with DBD::Oracle with mod_perl
PH Don't open a connection during startup. PH Open a connection in the child process instead. BT I will second this. I've done this (unintentionally) when using MySQL, and BT you get a lot of weird errors about statement handles being active, etc. I haven't read the entire thread, so forgive me if I'm restating. But there are reasons for sharing DB connections (and reasons not to). In the case of MySQL and getting messages about active statement handles, that's because you're disconnecting the DBH. If you are in a shared connection enviroment with MySQL, you are winning. Since you don't have to worry about things like transactions and rolling back somebody elses changes. All you need to do is finish() your statement handled when you are finished with them: $sth-finish; This will prevent the errors you are seeing and will allow you to keep the shared DB connection. A shared connection is usefull for efficiency, since creating a connection may be expensive. No, I called finish on all of my statement handles; that is not the reason for the errors I got. The behavior I am talking about *only occurs* for me when I open a connection in the Apache parent. Then this same DB connection is shared across all of the Apache children, and Strange Things occur. I am sorry but this topic is confusing me... Are you saying that persistent DB connection objects are bad? It sounds like that, doesn't it? Well, I'm sorry if it sounds like that, but that's not what I'm saying. :) I am merely echoing Perrin's statement that opening a shared connection in the parent is bad, because this connection is then shared between *all* of the children. You don't want this. You want one connection per child, not one connection shared between all Apache children. The latter *will* cause problems. I am about to switch to Apache::DBI for that very reason. I don't want to create a new db handle with every request. Should I stop, or am I missing something here? Nope, you've got it. If you don't have transactions (anything else?) to worry about, I'd say to use Apache::DBI. I would absolutely encourage anyone to use Apache::DBI. The case I am talking about is a very particular one: when you open a shared connection in the parent. You don't want this connection to be an Apache::DBI connection, so instead you should use the magic 6th arg to DBI::connect to force DBI to handle it exclusively. bye, Ben
Re: Problem with DBD::Oracle with mod_perl
Nope, you've got it. If you don't have transactions (anything else?) to worry about, I'd say to use Apache::DBI. Apache::DBI doesn't have a problem with transactions. If you're doing strange things like changing isolation levels on particular requests you can get into trouble, but that's an unusual case and you can always write your own cleanup code for it. - Perrin
Re: Problem with DBD::Oracle with mod_perl
From: Perrin Harkins [EMAIL PROTECTED] Apache::DBI doesn't have a problem with transactions. Ah, OK. What about when a shared connection is rolled back in one process, will it effect other running processes with the same handle? --- Rodney Broom Programmer: Desert.Net
Re: Problem with DBD::Oracle with mod_perl
Apache::DBI doesn't have a problem with transactions. Ah, OK. What about when a shared connection is rolled back in one process, will it effect other running processes with the same handle? Database handles are never shared between processes. If you missed this, you should re-read the Apache::DBI docs. - Perrin
Re: Problem with DBD::Oracle with mod_perl
PH Don't open a connection during startup. If you do, it will be shared when
PH Apache forks, and sharing a database handle is bad for the same reasons
PH sharig a file handle is. Open a connection in the child process instead.
I will second this. I've done this (unintentionally) when using MySQL, and
you get a lot of weird errors about statement handles being active, etc.
Speaking of which, what does one do when Apache::DBI is loaded, and
the parent process needs to pull some config info out of the database
that all clients also use? That is, how can I force Apache::DBI to
close that handle prior to the forking of children?
By using the magic 6th arg ($connect_meth in the DBI::connect source) to
DBI::connect. In my DB wrapper I have a connect method that internally calls
DBI::connect; before doing so, it checks to see if it is being called while
Apache is starting up in the parent process, and if so, it uses this special
arg.
my @args = (dbi:$db-{driver}:$dbname, $user, $pass,
{ RaiseError = 1 });
push @args, (undef, 'connect')
if $Apache::Server::Starting or $Visius::httpd_conf::Loading;
$self-{handle} = DBI-connect(@args);
The 'connect' is that magic argument that forces DBI to use its standard
connect method, rather than Apache::DBI::connect, when creating the new db
handle. This means that it is essentially a use-once DB handle, and that it
will not be reused by Apache::DBI.
Does this help?
bye,
Ben
Re: Problem with DBD::Oracle with mod_perl
Benjamin Trott wrote:
PH Don't open a connection during startup. If you do, it will be shared when
PH Apache forks, and sharing a database handle is bad for the same reasons
PH sharig a file handle is. Open a connection in the child process instead.
I will second this. I've done this (unintentionally) when using MySQL, and
you get a lot of weird errors about statement handles being active, etc.
Speaking of which, what does one do when Apache::DBI is loaded, and
the parent process needs to pull some config info out of the database
that all clients also use? That is, how can I force Apache::DBI to
close that handle prior to the forking of children?
By using the magic 6th arg ($connect_meth in the DBI::connect source) to
DBI::connect. In my DB wrapper I have a connect method that internally calls
DBI::connect; before doing so, it checks to see if it is being called while
Apache is starting up in the parent process, and if so, it uses this special
arg.
my @args = (dbi:$db-{driver}:$dbname, $user, $pass,
{ RaiseError = 1 });
push @args, (undef, 'connect')
if $Apache::Server::Starting or $Visius::httpd_conf::Loading;
$self-{handle} = DBI-connect(@args);
The 'connect' is that magic argument that forces DBI to use its standard
connect method, rather than Apache::DBI::connect, when creating the new db
handle. This means that it is essentially a use-once DB handle, and that it
will not be reused by Apache::DBI.
Does this help?
bye,
Ben
I am sorry but this topic is confusing me... Are you saying that
persistent DB connection objects are bad? I am about to switch to
Apache::DBI for that very reason. I don't want to create a new db handle
with every request. Should I stop, or am I missing something here?
RFC
-r
Re: Problem with DBD::Oracle with mod_perl
BT == Benjamin Trott [EMAIL PROTECTED] writes: that all clients also use? That is, how can I force Apache::DBI to close that handle prior to the forking of children? BT By using the magic 6th arg ($connect_meth in the DBI::connect source) to BT DBI::connect. In my DB wrapper I have a connect method that internally calls Duh... the source! What a concept ;-) Looking at Apache::DBI::connect, it special cases startup connections, so it just works automagically. How 'bout that! -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
RE: Problem with DBD::Oracle with mod_perl
We've seen this happen before. Unfortunately, I don't have a fix for you but I here's where we left off our chase... 1. ORA-03113: end-of-file on communication channel (for unknown reason, maybe a network blip?) 2. We have some code that will catch this error and call DBI-connect again. 3. Apache::DBI intercepts the connect(), looks in it's hash and sees that it already has a connection. It pings the handle, fails, and deletes the entry from the hash. That's the last refcount on the dbh, so DESTROY gets called. 4. The DESTROY method yields a DBI handle cleared whilst still active warning.. Issuing rollback() for database handle being DESTROY'd without explicit disconnect() at /usr/local/lib/perl5/site_perl/5.6.0/Apache/DBI.pm line 119. DBI handle cleared whilst still active at /usr/local/lib/perl5/site_perl/5.6.0/Apache/DBI.pm line 119. dbih_clearcom (h 0x82a3934, com 0x84ae49c): FLAGS 0x211: COMSET Warn AutoCommit TYPE 1 PARENT undef KIDS 0 (0 active) IMP_DATA undef in 'DBD::Oracle::dr' 5. Apache::DBI calls the real DBI-connect. This fails due to a ORA-12154: TNS:could not resolve service name (DBD ERROR: OCIServerAttach). If we run with DBI-trace(2) and $Apache::DBI::Debug = 2, we see ORA-01041: internal error. hostdef extension doesn't exist (DBD ERROR: OCITransRollback) appear in between the ORA-03113 and the ORA-12154 errors. We were running Perl 5.6.0, DBI 1.14, Apache::DBI 0.88, DBD::Oracle 1.06, and the Oracle 8.06 client against an 8.0.6.3.0 db. Make sure you let me know if you figure it out ;-) - Kyle -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Alex Povolotsky Sent: Wednesday, August 22, 2001 4:59 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Problem with DBD::Oracle with mod_perl Hello! I'm getting constant troubles with DBD::Oracle and mod_perl. DBD::Oracle 1.08, DBI 1.19, mod_perl 1.26 on Apache 1.3.20, SunOS netra 5.8 Generic_108528-09 sun4u sparc SUNW,UltraAX-i2 gcc 2.95.3 20010315 (release) This is perl, v5.6.1 built for sun4-solaris # perl -V:usemymalloc usemymalloc='n'; After some time of work (about hundred of requests), I get DBD::Oracle::db prepare failed: ORA-03113: end-of-file on communication channel (DBD: error possibly near * indicator at char 1 in '*select slogan_text from slogans') at /usr/local/www/lib/SQL.pm line 221. and all Oracle-using perl programs within Apache stops to work until I restart Apache. With two clients fetching a page both at one time, I'm 100% getting this in less than a minute. I have read all READMEs I've found around, and I couldn't reproduce this error by standalone perl scripts. Any help, anyone? Alex.
Re: Problem with DBD::Oracle with mod_perl
On Thu, Aug 23, 2001 at 11:19:22AM -0700, Kyle Oppenheim wrote:
3. Apache::DBI intercepts the connect(), looks in it's hash and sees that it
already has a connection. It pings the handle, fails, and deletes the entry
from the hash. That's the last refcount on the dbh, so DESTROY gets called.
4. The DESTROY method yields a DBI handle cleared whilst still active warning..
Apache::DBI should probably do an explicit eval { $dbh-disconnect } if the ping fails.
Tim.
RE: Problem with DBD::Oracle with mod_perl
On Wed, Aug 22, 2001 at 09:42:59AM -0400, Perrin Harkins wrote: Are you using Apache::DBI? Are you opening a connection in the parent process (in startup.pl or equivalent)? Yes, yes. Don't open a connection during startup. If you do, it will be shared when Apache forks, and sharing a database handle is bad for the same reasons sharig a file handle is. Open a connection in the child process instead. You can use connect_on_init() from Apache::DBI if you like. I misunderstood you. I was using connect_on_init. With or without Apache::DBI, it fails I've SEEN this. It SUCKED. Then I figured it out. And *IF* it's the same thing, then: ORACLE has to be reconfigured, to allow more connections. By default, there is a PROCESS max (200), because Oracle spawns a new process per connection. And then there is a WHOLE different operating mode, called MTS. You have to modify init.ora and activate MTS. The relevant section from MY Oracle config (8.1.5 or 8.1.6, I forget): # # This parameter turns on MTS mts_servers = 1 # min value mts_max_dispatchers = 5 # max value mts_dispatchers = (PROTOCOL=TCP)(DISPATCHERS=2) sessions = 1500 HTH L8r, Rob #!/usr/bin/perl -w use Disclaimer qw/:standard/;
Re: Problem with DBD::Oracle with mod_perl
Are you using Apache::DBI? Are you opening a connection in the parent process (in startup.pl or equivalent)? Yes, yes. Don't open a connection during startup. If you do, it will be shared when Apache forks, and sharing a database handle is bad for the same reasons sharig a file handle is. Open a connection in the child process instead. You can use connect_on_init() from Apache::DBI if you like. - Perrin
Re: Problem with DBD::Oracle with mod_perl
On Wed, Aug 22, 2001 at 09:42:59AM -0400, Perrin Harkins wrote: Are you using Apache::DBI? Are you opening a connection in the parent process (in startup.pl or equivalent)? Yes, yes. Don't open a connection during startup. If you do, it will be shared when Apache forks, and sharing a database handle is bad for the same reasons sharig a file handle is. Open a connection in the child process instead. You can use connect_on_init() from Apache::DBI if you like. I misunderstood you. I was using connect_on_init. With or without Apache::DBI, it fails Alex.
Re: Problem with DBD::Oracle with mod_perl
After some time of work (about hundred of requests), I get DBD::Oracle::db prepare failed: ORA-03113: end-of-file on communication channel (DBD: error possibly near * indicator at char 1 in '*select slogan_text from slogans') at /usr/local/www/lib/SQL.pm line 221. and all Oracle-using perl programs within Apache stops to work until I restart Apache. With two clients fetching a page both at one time, I'm 100% getting this in less than a minute. Are you using Apache::DBI? Are you opening a connection in the parent process (in startup.pl or equivalent)? - Perrin
Re: Problem with DBD::Oracle with mod_perl
On Wed, Aug 22, 2001 at 09:07:59AM -0400, Perrin Harkins wrote: (DBD: error possibly near * indicator at char 1 in '*select slogan_text from slogans') at /usr/local/www/lib/SQL.pm line 221. Are you using Apache::DBI? Are you opening a connection in the parent process (in startup.pl or equivalent)? Yes, yes. ... whithout it - runs... a little longer. Alex.
