how do I close an SQL session?

Raffie_s

Hi,

I was testing some stuf on my PHP site with inserting and editing records of a SQL database.
Just when I wanted to insert another record in the SQL database tru php
I recieved a error message, that there where to many connections.
I checked in phpmyadmin under 'show processes' and holey smoke:rolleyes:
there was so manny connections.
and there where all sleeping:bemused:
so I went to kill them while there where sleeping hehehehe
ok annyway
how do I prevent this?
can't I close the SQL database after I edit the SQL tru php?
and who is user ODBC??

oh yeah
to open a database I use the command:

mysql_pconnect
should I use the command:
mysql_connect ??

Ralph

PhiSYS

pconnect is persistent, connect is not. Try mysql_connect(), it may work for you.

In addition you can close the connection with mysql_close($conn);

$conn = mysql_connect(...);

mysql_close($conn);

or if you don't use a connection resource

mysql_connect(...);

mysql_close();

Roger_Ramjet

Never use pconnect unless that is the only way to do what needs to be done - very rare. This one comes up quite often so search the forum for other threads where the ins and outs of it are discussed, you will see that persistent connections have no advantages but cause many problems.

To answer your other question, the only way I can see to close persistent connections is to restart the server, or to explicitly kill the Apache child process that is holding it open.

Roger_Ramjet

Originally posted by PhiSYS
pconnect is persistent, connect is not. Try mysql_connect(), it may work for you.

In addition you can close the connection with mysql_close($conn);
$conn = mysql_pconnect(...);

mysql_close($conn);
or if you don't use a connection resource
mysql_pconnect(...);

mysql_close();
[/B]

Wrong. From the manual

Note: mysql_close() will not close persistent links created by mysql_pconnect().

If you read the manual and user notes here and here you will see why pconnect is a BAD idea.

PhiSYS

Sorry, I never use persistent connections, I thought they work exactly as non-persistent.
So sorry.

Sxooter

No need to apologize. This is a common (sense) misinterpretation of the behavioiur of PHP.

pconnect is to db work as mysql's tinyint is to autoincrements. Generally a bad idea that causes no end of trouble for newbies.

Keep pounding out answers man, we all work as a team...

csn

I don't think there's anything wrong with using pconnects as long as you keep a leash on them (i.e. set finite connection limits in php.ini that your system's memory can handle). One of my servers has been pretty loaded lately - httpd is often at 90+% CPU use. I switched to pconnects to try to reduce load - it appears to have had a minor effect. I don't know what the costs are of process creation/destruction, but might as well eliminate that. But the heart of the problem is figuring out where in the PHP scripts the CPU is getting hit - but I don't know of any easy way to do that (apparently Zend Platform has this functionality). Best I've figured is do 'ps --sort=pcpu axu' to find processes with high CPU use, then lookup the process' id in httpd's server-status and see what URL it's calling.

Sxooter

csn, the real danger from pconnect comes from the fact that most databases default to allowing fewer connections than the apache server's default number of children.

For instance, postgresql comes with a default setting of 100 connections, whlie apache generally is set for 150 children, so if every child open a connection, you run out.

So, in order to use persistant connections reliably, you need to configure apache to have fewer children than your database has connections. And most people just start using pconnects without knowing that, and the failure winds up occuring days or weeks later in production...

csn

You'd just get a "can't connect to database error" in that case. But setting the number of httpd and postgres connections to the same number is a good idea. Far worse is when unlimited persistant connections are allowed and they use up all the memory and crash the server.

Sxooter

The thing that makes it harder for the beginning sysadmin to get the setup right is that the max persistant connections listed in phpl.ini is PER backend, per unique connection string So, setting it to 1, the lowest setting, allows an application that opens handles on 3 unique database connection strings to open max apache children * 3 connections at a time.

So, often, it's a good idea to have apache be half or less max children than the database has max connections setup.

csn

Yep. Apparently they're per child too (in the comments):

If anyone ever wonders why the number of idle db process (open connections) seems to grow even though you are using persistent connections, here's why:

"You are probably using a multi-process web server such as Apache. Since database connections cannot be shared among different processes a new one is created if the request happen to come to a different web server child process."

http://us3.php.net/manual/en/features.persistent-connections.php

Sxooter

yeah, child = apache backend. You mighta missed that. 🙂

csn

Oh yeah 😉 - only "unique connection string" stuck in my mind.

Hey Sxooter, I got a question for you here (or there):
http://slashdot.org/comments.pl?sid=136559&cid=11409289&pid=11409289&threshold=-1&mode=thread&commentsort=0&op=Change