Hello!

I was wondering if it is dangerous to use persistent MySQL connections on shared servers. Is it possible that somebody can "grab" my connection and use it himself? Any other things?

Regards,
Sebastian

PS: The title should read "Persistent MySQL Connections"

If PHP was built as a CGI program on your server, it should not pose a problem. Under this config, every time a page is requested an instance of the PHP interpreter is first created then destroyed. So, any connection made to the database is closed after each page request.

If PHP is built as a module, there is a danger of your script spawning a lot of children processes eating up server resources. Each database has a limited number of simulataneous connections. This is set by your host. Any request made above this limit won't connect. If your script happens to have a bug like an infinite loop or you get heavy traffic, the entire server will most likely crash. Under this config, I recommend you ask your host whether they prefer you use non-persistent or persistent connections. It's possible they may have set up their servers to handle persistent ones okay.


Only if he knows your database username and password.

According to phpinfo(), "mysql.max_persistent" is set to "Unlimited".

You should always use a persistent connection. It uses an already opened connection, so you don't have to worry about opening another MySQL connection.

If you don't want to worry about opening another MySQL connection, use mysql_ping() and on failure, connect.

That's wrong.

You should only really use persistent connections if opening a connection on each page is particularly slow for some reason or has a big overhead.

http://www.php.net/manual/en/function.mysql-pconnect.php



So I'm wrong? It says right there, from the php.net manual, that it will use an already opened connection.

I may admit, that "always" is quite strong... and that a persistent connection will not work as a CGI wrapper.

Where does it say 'always use pconnect'? All that does is tell you what purpose it serves.

http://uk2.php.net/manual/en/features.pers...connections.php

There it explains that they are good if there's a lot of overhead in creating connections. If there isn't, there's little point in using pconnect since the problems it can cause will probably outweigh the benefits. If there's no problem creating connections on your server you might just as well use mysql_connect.

...don't forget mysql_ping()!

It can slow down the connection on any first attempts (as it waits for a reply) but otherwise isn't all that bad. In the long run, it's your own choice and how you wrote the script. Since I wrote a script that incorporates something like 4 files in to one and can't necessarily be certain that one file won't open a NEW mysql connection after one is already open, I use ping in my own mysql connecting function so that I'm not wasting resources. It does take slightly longer since it is pinging, but considering how fast I try to get my pages to load anyway, I'm not too worried in this case.

There are numerous potential issues, especially on a shared server. If they haven't set an inactivity timeout for mysql connections then neither Apache nor MYSQL will ever close that connection.

Also, if a realistic upper limit has not been set on the number of mysql_pconnections PHP can open, during high-traffic periods it is possible for PHP to take up all available connection slots to the MySQL server with mysql_pconnect's. If this happens then no other connections will be permitted. Standard mysql_connect statements will fail. If combined with the other issue I mentioned the only solution left would be to restart the severs.

I agree with Rikki here. If, for example, MySQL was running on a different server to Apache/PHP then yes, you may see a good performance increase in using mysql_pconnect. But for most setups there isn't really much need... and on shoddy shared hosting environments, it can cause a number of problems.

A script that 'accidently' opens new connections (which aren't required) when one already exists in the same execution is poorly written IMO.

When we're talking about connections that continue we mean between server processes, not between files or a single page load of a php script.

I *THINK* (but am not sure) that if you try to open an identical connection to one that is already open (i.e. same username, password, db) then PHP will just return the reference to the original connection resource.

Not sure whether this is default behavior, or a configurable option, but it is behavior I have noticed in the past.

Yes, perhaps...that's the problem with scope creep, I suppose. I can only plan so far ahead, but then the client wants this, that, and the other thing which weren't even CLOSE to the original specifications. Rather than replan and rewrite from scratch, I ended up trying to add modules that didn't really act, ummm...modular. Had I created a session for ALL visitors and required the database on ALL pages, then the problem would be gone. Unfortunately, that isn't the case...but at each new stumble in the road, I learn from past experiences. It might be poorly adapted/patched together, but I am glad there is a function such as mysql_ping so I can avoid recreating a new connection.

Wombat, I believe that's a configuration option and I would have no idea where it could be set.

If I use sessions, I can create a connection on session start and destroy it on session end, correct?

You're going to have to create some type of connection every time you view a page.

Starting with PHP 4's Zend Engine, you don't have to destroy resources unless you are using mysqlp_connection()... which in that case you shouldn't.

Sebastian, I fail to understand your question about sessions and connections... They are not interconnected, so please be more specific.

Transverse Styles: You can't destroy a persistent connection, it can only time out.

I thought that when using PHP sessions, I don't have to use persistent connections, but simply create a MySQL connection which I carry over to the other pages.

No, it won't work as mysql_connect returns a *reference* to a connection resource. Not sure how PHP handles references when they are stored in a session, but somehow I expect they wont work.

A session is basically a server-side cookie. It can hold information that you give to it, but that is all. I suppose you could give it the resource ID of the mysql connection, but unfortunately (as far as I'm aware) you can't reopen a specific resource ID using PHP's mysql functions.

I hope that clarifies a little bit for you.

OK, thanks. So the only possiblity would be either reopening the MySQL connection on every page load or using persistent MySQL connections which I would rather not do.

That is correct.

I hope this topic hasn't confused people

Opening a MySQL connection on every page is fine under most circumstances. IPB for example does it (by default) as does virtually every other script. It's only if your server setup means you need to keep a persistant connection that you need to use mysql_pconnect. That still requires you to call mysql_pconnect on every page you want to use MySQL in but behind the scenes, Apache will reuse a connection.

Correct, from the manual(new_link is the fourth parameter for mysql_connect):

...so in effect, me using mysql_ping is completely useless? $%#@^%@#%$#

Thanks, Michael.

Yes, it seems that way. That behaviour can be quite annoying at times though, I spent a couple of hours trying to work out why both connections had the same resource ID when writing a forum convertor script a while ago.

That kind of makes me wonder what the purpose of mysql_ping() is now...seems redundant, and therefore bloats the codebase of PHP (although it technically is bloated anyway for convenience).

I recently did work for a large website and we decided to bring their mySQL server to a seperate dedicated server. Their site was also run off of a dedicated server. Since mySQL connections had to go across the LAN, the slowed down a bit. Using mySQL pconnect helped in this case as it reduced the connection time.

In some cases it can be useful