You are viewing a plain text version of this content. The canonical link for it is here.
Posted to rivet-dev@tcl.apache.org by Clif Flynt <cl...@cflynt.com> on 2010/10/29 05:45:26 UTC
Persistent DB connection
Hi,
I think I'm missing something simple.
I may be getting bit by threads.
The system I'm building is a database backed web application.
Nothing particularly complex.
I open a connection to the database using tdbc - this creates
a new command that I put in the global scope as '::db1'.
A few minutes later, I hit a web page, and the command is gone.
It's simple enough to re-create it, but I'm getting bored with
adding the 'is it still here' check to my code.
Is there a way to open a persistent link? Or am I getting caught by
a new thread being assigned to a session and the new thread has never
heard of any ::db1 command.
Maybe I should drop the tdbc idea and move to dio... My primary
reason for using tdbc is that I'm not convinced that we won't be
switching backends before we get out of prototype mode and tdbc seemed
like the simplest way to be server-neutral.
Thanks for insights,
Clif
--
... Clif Flynt ... http://www.cwflynt.com ... clif@cflynt.com ...
.. Tcl/Tk: A Developer's Guide (2nd edition) - Morgan Kauffman ..
.. 17'th Annual Tcl/Tk Conference: 2010, Oak Brook, IL USA ..
............. http://www.tcl.tk/community/tcl2010/ ............
---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org
Re: Persistent DB connection
Posted by Damon Courtney <da...@tclhome.com>.
TDBC is a lot smarter than that. It verifies the connection every time you attempt to use it for something. My helper looks something like this:
proc getDbConnection {args} {
if {[info commands ::db] ne ""} {
## Already have a connection.
if {![catch {::db configure {*}$args} error]} {
return
}
::db close
}
tdbc::mysql::connection create ::db {*}$args
}
Every request calls this proc which attempts to configure the connection each time. I assume that the arguments passed in are legal, so if the connection fails to configure for some reason, it will just destroy it and make a new one. This is usually because the db connection has been live for a long time and has lost connection to the server somehow.
The other nice thing about this setup is that you can have multiple sites using the same children, and the DB proc here will simply reconfigure the db connection to a new user / host / database with each request if needed. Which is almost as expensive as just creating a new connection every time, so I prefer to use separate virtual interps per host, but hey, it works! 0-]
D
On Oct 29, 2010, at 11:13 AM, Massimo Manghi wrote:
> I don't know which backend you are using, but I had a few problems working with mysqltcl. As one might expect, if a child process stays idle too long the backend drops the connection, and it happened every night on the computer where I was developing the application.
>
> mysqltcl has a 'status' method and I thought I could have information about the dbms connection from it, but actually it didn't say anything useful (what it returned was even wrong and misleading). This was a few years ago and this support in mysqltcl might have improved now.
>
> The only way I found to get around it was closing the connection once the request had been served and reopening it every time. Shamefully expensive.
>
> -- Massimo
>
> On 10/29/2010 06:57 AM, Clif Flynt wrote:
>>
>> Thanks. I was suspecting something like this.
>>
>> Eventually, all of the children will have a ::db1 procedure, and
>> all will be happy.
>>
>> I was afraid that I was causing some unnecessary thrashing, but I
>> guess it's necessary after all.
>>
>> Happy Tcl'ing,
>> Clif
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
> For additional commands, e-mail: rivet-dev-help@tcl.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org
Re: Persistent DB connection
Posted by Massimo Manghi <ma...@unipr.it>.
I don't know which backend you are using, but I had a few problems
working with mysqltcl. As one might expect, if a child process stays
idle too long the backend drops the connection, and it happened every
night on the computer where I was developing the application.
mysqltcl has a 'status' method and I thought I could have information
about the dbms connection from it, but actually it didn't say anything
useful (what it returned was even wrong and misleading). This was a few
years ago and this support in mysqltcl might have improved now.
The only way I found to get around it was closing the connection once
the request had been served and reopening it every time. Shamefully
expensive.
-- Massimo
On 10/29/2010 06:57 AM, Clif Flynt wrote:
>
> Thanks. I was suspecting something like this.
>
> Eventually, all of the children will have a ::db1 procedure, and
> all will be happy.
>
> I was afraid that I was causing some unnecessary thrashing, but I
> guess it's necessary after all.
>
> Happy Tcl'ing,
> Clif
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org
Re: Persistent DB connection
Posted by Clif Flynt <cl...@cflynt.com>.
On Thu, Oct 28, 2010 at 10:57:20PM -0500, Damon Courtney wrote:
> You need to write a helper function to create the DB connection if it
> doesn't already exist and then return it if it does. The reason you're
> hitting this is because every time you request a page, you get a random
> child process from Apache. You don't know which one you're going to
> get, and it's unlikely you'll get the same one over and over again.
> Once you create the DB handle the first time in the global scope, it
> will remain in that Apache child, but you may get a different child
> next time.
Thanks. I was suspecting something like this.
Eventually, all of the children will have a ::db1 procedure, and
all will be happy.
I was afraid that I was causing some unnecessary thrashing, but I
guess it's necessary after all.
Happy Tcl'ing,
Clif
--
... Clif Flynt ... http://www.cwflynt.com ... clif@cflynt.com ...
.. Tcl/Tk: A Developer's Guide (2nd edition) - Morgan Kauffman ..
.. 17'th Annual Tcl/Tk Conference: 2010, Oak Brook, IL USA ..
............. http://www.tcl.tk/community/tcl2010/ ............
---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org
Re: Persistent DB connection
Posted by Damon Courtney <da...@tclhome.com>.
You need to write a helper function to create the DB connection if it doesn't already exist and then return it if it does. The reason you're hitting this is because every time you request a page, you get a random child process from Apache. You don't know which one you're going to get, and it's unlikely you'll get the same one over and over again. Once you create the DB handle the first time in the global scope, it will remain in that Apache child, but you may get a different child next time.
Damon
On Oct 28, 2010, at 10:45 PM, Clif Flynt wrote:
> Hi,
> I think I'm missing something simple.
>
> I may be getting bit by threads.
>
> The system I'm building is a database backed web application.
> Nothing particularly complex.
>
> I open a connection to the database using tdbc - this creates
> a new command that I put in the global scope as '::db1'.
>
> A few minutes later, I hit a web page, and the command is gone.
>
> It's simple enough to re-create it, but I'm getting bored with
> adding the 'is it still here' check to my code.
>
> Is there a way to open a persistent link? Or am I getting caught by
> a new thread being assigned to a session and the new thread has never
> heard of any ::db1 command.
>
> Maybe I should drop the tdbc idea and move to dio... My primary
> reason for using tdbc is that I'm not convinced that we won't be
> switching backends before we get out of prototype mode and tdbc seemed
> like the simplest way to be server-neutral.
>
> Thanks for insights,
> Clif
>
> --
> ... Clif Flynt ... http://www.cwflynt.com ... clif@cflynt.com ...
> .. Tcl/Tk: A Developer's Guide (2nd edition) - Morgan Kauffman ..
> .. 17'th Annual Tcl/Tk Conference: 2010, Oak Brook, IL USA ..
> ............. http://www.tcl.tk/community/tcl2010/ ............
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
> For additional commands, e-mail: rivet-dev-help@tcl.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: rivet-dev-unsubscribe@tcl.apache.org
For additional commands, e-mail: rivet-dev-help@tcl.apache.org