Connection pooling and switching

The replication and load balancing plugin changes the semantics of a PHP
MySQL connection handle. The existing API of the PHP MySQL extensions
(mysqli,
mysql,
PDO_MYSQL) are not changed in
a way that functions are added or removed. But their behaviour
changes when using the plugin. Existing applications do not need to
be adapted to a new API. But they may need to be modified because of
the behaviour changes.

The plugin breaks the one-by-one relationship between a
mysqli,
mysql,
PDO_MYSQL connection
handle and a MySQL wire connection. If using the plugin a
mysqli,
mysql,
PDO_MYSQL connection
handle represents a local pool of connections to the configured
MySQL replication master and the MySQL replication slave servers.
The plugin redirects queries to the master and slave servers.
At some point in time one and the same PHP connection handle
may point to the MySQL master server. Later on, it may point
to one of the slave servers or still the master. Manipulating
and replacing the wire connection referenced by a PHP MySQL
connection handle is not a transparent operation.

Every MySQL connection has a state. The state of the connections in
the connection pool of the plugin can differ. Whenever the
plugin switches from one wire connection to another, the current state of
the user connection may change. The applications must be aware of this.

The following listshows what the connection state consists of.

Transaction status

Temporary tables

Table locks

Session system variables and session user variables

Session system variables and session user variables

Prepared statements

HANDLER variables

Locks acquired with GET_LOCK()

The plugins philosophy is to align the state of connections in the
pool only if the state is under full control of the plugin, or if it is
necessary for security reasons. Just a few actions that change the
state of the connection fall into this category. The plugin does broadcast
the following state changing client calls to all currently open
connections in the connection pool: change_user,
select_db, set_charset,
set_server_option, set_client_option,
autocommit.

The plugin does not proxy or
"remember" settings to apply them on connections
opened in the future. This is important to remember, if
using
lazy connections.
Lazy connections are connections which are not
opened before the client sends the first connection.
Use of lazy connections is the default plugin action.

Connection switches happen right before queries are run. The plugin does
not switch the current connection until the moment in time when
the next statement is executed.

Please, do not miss the MySQL reference manual chapter on
replication features and issues. Some restrictions you hit may not be related
to the PHP plugin but are properties of the MySQL replication system.