tags:

views:

56

answers:

3
Q: 

PHP Constant open mysql connection safety?

I have a website where at the top of each page I include a page that opens a mysql_connect connection, so I don't have to open up the connection each time I need to modify or insert stuff into the database. Is this safe, or should I open and close each connection once I'm done using it?

require_once("../include/mysqlconn.php");

$sql = "SELECT * FROM blah";
$query = mysql_query($sql);
A: 

Personally, I create a new connection each time I make a query, then close it. If you have multiple databases, you can clash if you leave it open.

Jason  2010-10-24 17:51:29
That's a really bad practice as it may slow down your server. Opening connections (be it via TCP or UNIX socket) has some overhead. If you have multiple databases or multiple servers, name the connection link variables accordingly. In any case keeping the connection open during execution of just one script is fine.
mojuba  2010-10-24 17:57:08
How much overhead are we talking about?
Jason  2010-10-24 18:04:53
If you run just one simple query that returns a few rows, it may be, say 2 packets vs 7-8 exchanged between the DB server and the application, and that's for each request. But then of course it depends on the overall load of your application server.
mojuba  2010-10-24 18:13:29
Not so much overhead but it's still bad practice. Your reason to open if for the every query is just ridiculous.
Col. Shrapnel  2010-10-24 18:19:49
Interesting. I've taught myself to program, so I did not know too much about overheard. I like the peace of mind of not having an open database connection. Thanks for the input, I'll have to change it because I open and close alot. haha.
Jason  2010-10-24 18:21:44
@Jason - shame you're not coding .NET otherwise we'd all be giving you a pat on the back and telling you what a great job of self teaching you'd done - http://www.developer.com/net/article.php/3729831/Using-Connection-Pooling-in-ADONET.htm or http://aspalliance.com/1099. I just guess connection pooling in apache/mysql works differently so connections with page vs. method level affinity are the way to go for scalability here :P
f00  2010-10-24 21:04:51
I was going for scalability. In the long run I want people to be able to have plugins, and I am a little eerie about leaving a connection open so that a developer can willy-nilly query a password or something. I guess, upon reflection, if a plugin developer wants a database entry, he will get it.
Jason  2010-10-25 01:02:07
@Jason - in .NET "connection open" and "fetch connection from pool" are synonymous as are "connection close" and "return connection to pool" so your approach of opening/closing or rather fetching/returning connections before and after each db call is actually BEST PRACTICE for developing high performance, highly scalable enterprise level applications in MS technologies. As far as developing similar apps in LAMP this approach is bad practice due to lack of proper connection pooling mechanism, although 3rd party conenction pooling extensions are available - so might be worth investigating...
f00  2010-10-25 08:29:20
A: 

This is OK as the connection will be closed automatically at the end of each script execution (actually all resources are freed at that point).

Another possibility is to use mysql_pconnect() which does connection caching for you and may speed up your site a bit.

In either case there is nothing to worry about security wise, as long as the password is kept in a place not accessible from outside.

mojuba  2010-10-24 17:54:29
The only sensible answer out of 3 so far but still -1 for mentioning pconnect.
Col. Shrapnel  2010-10-24 17:57:59
Care to explain what's wrong with pconnect?
mojuba  2010-10-24 18:00:18
Care to explain what's good with pconnect? Have you ever used it? Measure that "speed up"? Ever experienced Too many connections error?
Col. Shrapnel  2010-10-24 18:03:33
Um, unless the connection is secured via SSL/TLS, the authentication information is being sent in plain text.
Gumbo  2010-10-24 18:07:10
pconnect() saves you the overhead of opening and closing connections. If you get "Too many connections" AND switching to connect() solves the problem, it means only one thing: it's a result of leaving some database locks unreleased. Otherwise both connect() and pconnect() use roughly the same number of channels for the same site. Honestly, I doubt you know what you are talking about. Sorry.
mojuba  2010-10-24 18:08:41
@Gumbo if mysqld runs on the server as apache, which is the case in the vast majority of cases, then no need to encrypt your connection with mysql.
mojuba  2010-10-24 18:10:19
connect() and pconnect() cannot use the same number of channels because some pconnects will be always hang out unemployed. "Connection caching" means that. Just google for too many connections.
Col. Shrapnel  2010-10-24 18:17:45
Col., the whole point of pconnect() keeping the channels open is reusing the channels without reopening them. With careful programming (such as never leaving locks) pconnect() gives you some performance gain. Don't use it though if you are unsure.
mojuba  2010-10-24 22:16:03
A: 

well closing it is safer. you can also include a page that closes the connection to mysql at the end of every page you open the connection.

burntblark  2010-10-24 17:55:25
Connections are always closed when the script is done being executed, no matter what.
Vincent Savard  2010-10-24 17:57:08

related questions

Remove Quotes and Commas from a String in MySQL
What's the best way of converting a mysql database to a sqlite one?
How do you manage databases in development, test, and production?
Multiple foreign keys?
Add 1 to a field (MySQL)
Issues using MS Access as a front-end to a MySQL database back-end?
Bigger than a char but smaller than a blob
How do I retrieve my MySQL username and password?
Long-time LAMP Developer moving to VB.NET
How do I Concatenate entire result sets in MySQL?
Full complete MySQL database replication? Ideas? What do people do?
SQL: Distribution of table in time
SQLite vs MySQL
How do I create a new Ruby on Rails application using MySQL instead of SQLite?
Multiple Updates in MySQL
MySQL/Apache Error in PHP MySQL query
What all do I need to escape when sending a (My)SQL query?
Auto Generate Database Diagram MySQL
Mechanisms for tracking DB schema changes
How big can a MySQL database get before performance starts to degrade.
Python and MySQL
SQL Server 2005 implementation of MySQL REPLACE INTO?
How to export data from SQL Server 2005 to MySQL
Throw Error In MySQL Trigger
Binary Data in MySQL