Connections
Connections
Connections
The MySQL server supports the use of different
transport layers for connections. Connections use TCP/IP, Unix
domain sockets or Windows named pipes.
The hostname localhost has a special
meaning. It is bound to the use of Unix domain sockets. It is not
possible to open a TCP/IP connection using the hostname
localhost you must use 127.0.0.1 instead.
Example #1 Special meaning of localhost
<?php
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
echo $mysqli->host_info . "\n";
$mysqli = new mysqli("127.0.0.1", "user", "password", "database", 3306);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
echo
$mysqli->host_info . "\n";
?>The above example will output:
Localhost via UNIX socket 127.0.0.1 via TCP/IP
Connection parameter
defaults
Depending on the connection function used, assorted
parameters can be omitted. If a parameter is not provided, then the
extension attempts to use the default values that are set in the
PHP configuration file.
Example #2 Setting defaults
mysqli.default_host=192.168.2.27 mysqli.default_user=root mysqli.default_pw="" mysqli.default_port=3306 mysqli.default_socket=/tmp/mysql.sock
The resulting parameter values are then passed to
the client library that is used by the extension. If the client
library detects empty or unset parameters, then it may default to
the library built-in values.
Built-in connection library
defaults
If the host value is unset or empty, then the
client library will default to a Unix socket connection on
localhost. If socket is unset or empty, and a Unix socket
connection is requested, then a connection to the default socket on
/tmp/mysql.sock is attempted.
On Windows systems, the host name . is
interpreted by the client library as an attempt to open a Windows
named pipe based connection. In this case the socket parameter is
interpreted as the pipe name. If not given or empty, then the
socket (pipe name) defaults to \\.\pipe\MySQL.
If neither a Unix domain socket based not a Windows
named pipe based connection is to be established and the port
parameter value is unset, the library will default to port
3306.
The mysqlnd library and the MySQL Client Library
(libmysqlclient) implement the same logic for determining
defaults.
Connection options
Connection options are available to, for example,
set init commands which are executed upon connect, or for
requesting use of a certain charset. Connection options must be set
before a network connection is established.
For setting a connection option, the connect
operation has to be performed in three steps: creating a connection
handle with mysqli_init(), setting the requested
options using mysqli_options(), and establishing the
network connection with mysqli_real_connect().
Connection pooling
The mysqli extension supports persistent database
connections, which are a special kind of pooled connections. By
default, every database connection opened by a script is either
explicitly closed by the user during runtime or released
automatically at the end of the script. A persistent connection is
not. Instead it is put into a pool for later reuse, if a connection
to the same server using the same username, password, socket, port
and default database is opened. Reuse saves connection
overhead.
Every PHP process is using its own mysqli
connection pool. Depending on the web server deployment model, a
PHP process may serve one or multiple requests. Therefore, a pooled
connection may be used by one or more scripts subsequently.
Persistent connection
If a unused persistent connection for a given
combination of host, username, password, socket, port and default
database can not be found in the connection pool, then mysqli opens
a new connection. The use of persistent connections can be enabled
and disabled using the PHP directive mysqli.allow_persistent. The total number of connections
opened by a script can be limited with mysqli.max_links. The maximum number of persistent
connections per PHP process can be restricted with mysqli.max_persistent. Please note, that the web server
may spawn many PHP processes.
A common complain about persistent connections is
that their state is not reset before reuse. For example, open and
unfinished transactions are not automatically rolled back. But
also, authorization changes which happened in the time between
putting the connection into the pool and reusing it are not
reflected. This may be seen as an unwanted side-effect. On the
contrary, the name persistent may be understood as a
promise that the state is persisted.
The mysqli extension supports both interpretations
of a persistent connection: state persisted, and state reset before
reuse. The default is reset. Before a persistent connection is
reused, the mysqli extension implicitly calls mysqli_change_user() to reset the state. The
persistent connection appears to the user as if it was just opened.
No artifacts from previous usages are visible.
The mysqli_change_user() function is an expensive
operation. For best performance, users may want to recompile the
extension with the compile flag
MYSQLI_NO_CHANGE_USER_ON_PCONNECT
being set.
It is left to the user to choose between safe
behavior and best performance. Both are valid optimization goals.
For ease of use, the safe behavior has been made the default at the
expense of maximum performance.
See also