PHP 7.1.19 Released

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.

add a note add a note

User Contributed Notes

There are no user contributed notes for this page.
To Top