31

I noticed there is no close function for PDO. Should I close the connection or is it unnecessary for PDO?

PeeHaa
  • 71,436
  • 58
  • 190
  • 262
Rujikin
  • 730
  • 2
  • 10
  • 17

4 Answers4

44

Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

http://php.net/manual/en/pdo.connections.php

So the answer is no, you don't need to do anything unless you need to explicitly close the connection during the script execution for whatever reason, in which case just set your PDO object to null.

Tchoupi
  • 14,560
  • 5
  • 37
  • 71
Stegrex
  • 4,004
  • 1
  • 17
  • 19
  • 3
    It would be great practice to close the connection though. Other languages aren't so generous when it comes to memory leaks. – Ryan Mar 16 '13 at 02:41
  • @RPM That's very true. I agree its also a good idea to do in the case that the script is written badly and runs in an infinite loop or otherwise takes too long. If the connection's not being used, no need to keep it on. – Stegrex Mar 16 '13 at 02:45
  • 1
    @RPM It's pointless to close it unless you have a specific reason to do so. Also, if your script runs too long, it will be killed and the connection will be closed. – Brad Oct 28 '13 at 00:36
  • 8
    @Brad, it's not pointless to close a connection. Every connection occupies resources in the database server. Most servers are configured with a maximum number of concurrent connections. If you close connections proactively, you free up that slot for another thread, and you get overall better throughput. – Bill Karwin Nov 03 '13 at 13:29
  • 5
    @BillKarwin If you are writing scripts that end immediately anyway, it is pointless. I completely agree that if you are writing anything long-running that doesn't need data after an initial query, then you should close it. – Brad Nov 03 '13 at 14:47
  • @Brad, yes, not all scripts are created equal, and some do some work on formatting and HTML output after they have done all their database work. But there are some sites that have such high traffic, that even reducing 20 milliseconds from the length of a db connection makes a big difference, when you multiply by thousands of requests per second. – Bill Karwin Nov 03 '13 at 18:11
  • At this link: https://phpdelusions.net/pdo#prepared , why is it said: "The connection has to be made only once! No connects in every function. No connects in every class constructor. Otherwise, multiple connections will be created, which will eventually kill your database server. Thus, a sole PDO instance has to be created and then used through whole script execution." Checking with: show status where `variable_name` = 'Threads_connected'; I can see there are always only ever 2 connections open at any one time (presumably my sql app and my site). – BSUK Dec 05 '17 at 17:30
  • This is not entirely correct. There are cases where you should close a connection, for example when using pcntl threads. – John Jan 13 '19 at 02:47
  • @John - Or at least don't share a connection between threads; it will mess up badly. – Rick James May 24 '23 at 21:51
12

This question is depending a bit on the type of project and the type of connection.

In almost all of my projects I never manually close the connection. In PHP the connection (unless it is a persistent connection) will only be open during the request. So manually closing it is pretty useless anyway.

When looking at my projects where there was no persistent connection it would have been very hard to know when to manually close the connection either way. Once a project gets larger than a couple of files (and the individual components have no idea about eachother like they should) it becomes very hard to know when the connection will still be needed.

And opening the connection again when needed is waay more expensive than just leaving it open during the request.

Something though when working with persistent connection there will be situations where you will want to manually close the connection.

So to answer your question:

I noticed there is no close function for PDO.

You can nullify the object reference (and all references to the object) to manually close the connection in PHP.

Should I close the connection or is it unnecessary for PDO?

In most situations it is not necessary.

PeeHaa
  • 71,436
  • 58
  • 190
  • 262
  • If I explicitly set a `PDO` objecto to NULL (`$pdo = NULL`) and I created the PDO object with `PDO::ATTR_PERSISTENT => true` (persistent connection), will it close the connection or will PHP ignore the instruction because the connection is persistent? – tonix Nov 23 '18 at 16:44
  • @tonix obviously it will. – Your Common Sense May 25 '23 at 07:57
5

From the PDO's connection page

Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object.

If you don't do this explicitly, PHP will automatically close the connection when your script ends.

EDIT

I'd rather use persistent connection. Though, it's a good practice to close all connections at the end of the script.

Community
  • 1
  • 1
hjpotter92
  • 78,589
  • 36
  • 144
  • 183
  • 1
    Would you say it's a good idea to always close your PDO connection? – Benjamin Gruenbaum Mar 16 '13 at 02:05
  • 1
    It's a great idea to close you PDO connection after you're finished with it. – Ryan Mar 16 '13 at 02:42
  • 6
    @RPM "It's a great idea to close you PDO connection after you're finished with it" [citation needed] – PeeHaa Oct 27 '13 at 23:51
  • I don't understand what is meant to say here. What should be a takeaway - a PHP man suggestion or your opinion? How persistent connections are related to this question? What is recommended to close? A persistent connection? – Your Common Sense May 25 '23 at 02:27
0

What I get from this is [roughly]

  • Use ATTR_PERSISTENT only if you have a thousand connections/second and are using SSL.
  • Otherwise, trash left behind by one connection could mess up the next. Apparently, this can happen if one connection crashes ungracefully.

The issues are

  • TEMPORARY tables
  • Local @variables
  • Unfinished transactions
  • (maybe) default database (USE)
  • SSL
  • and perhaps more.

I would like to see a clear specification of what is or is not done with those issues in every type of connection pooling (PDO, MariaDB, various Proxies, etc.) Quotes from man pages are not specific enough for me to trust.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • But how this answer is even related to the question? It doesn't even mention a persistent connection. Why this answer is here and not [there](https://stackoverflow.com/q/3332074/285587)? – Your Common Sense May 25 '23 at 02:29
  • @YourCommonSense - It was mentioned in another answer and comment. – Rick James May 25 '23 at 02:34
  • But Stack Overflow is not a forum? And answers are supposed to provide information related to the initial question, not to argue with some remarks in other answers? This answer looks completely off the track. – Your Common Sense May 25 '23 at 02:39