Tweaking is the motto – what an easy release PECL/mysqlnd_ms 1.4 will be! The first tweak for the next stable version of the mysqlnd replication and load balancing plugin solves pitfalls around charsets. String escaping now works on lazy connection handles (default) prior to establishing a connection to MySQL. A new server_charset setting has been introduced for this. The way it works also prevents you from the risk of using a different charset for escaping than used later on for your connection.
Lazy connections and server_charset
PECL/mysqlnd_ms is a load balancer. A users connection handle can point to different nodes of a replication cluster over time. For example, if using MySQL Replication, the connection handle may point to the master for running writes and, later on, to one of the slaves for reads. At the very moment a user opens a connection handle, the load balancer does not yet know which cluster node needs to be queried first.
/* Load balanced following "myapp" section rules from the plugins config file */
$mysqli = new mysqli("myapp", "username", "password", "database");
$pdo = new PDO('mysql:host=myapp;dbname=database', 'username', 'password');
$mysql = mysql_connect("myapp", "username", "password");
Thus, the plugin delays opening a MySQL connection to any configured cluster node (master or slave) until a node has been selected for statement execution. The plugin calls this lazy connections. Instead of openening a connection to a default node or even opening connections to all nodes, it waits and sees.
/* Nothing but a handle, no MySQL connection opened so far*/
$mysqli = new mysqli("myapp", "username", "password", "database");
/* Escaping on a lazy connection is not possible - will emit a warning */
$mysqli->real_escape("What charset to use?");
/* A node is picked and a connection will be openend */
$mysqli->query("SELECT 'connection will be opened now' AS _msg FROM DUAL");
While lazy connections potentially help to keep the number of connections opened as low as possible, there is a problem. Which charset to use for string escaping prior to opening a connection to MySQL? PECL/mysqlnd_ms 1.4 will search for a server_charset setting in its configuration file and use it. If it is not there, it will bark at you, pretty much like all previous stable releases. A warning will be thrown that reads like (mysqlnd_ms) string escaping doesn't work without established connection in the current series and is a bit more verbose in the 1.4 series, like (mysqlnd_ms) string escaping doesn't work without established connection. Possible solution is to add server_charset to your configuration
Setting server_charset removes the warning. PECL/mysqlnd_ms 1.4 will use the server_charset to do the string escaping. At any time thereafter the user can change the charset using an API call for string escaping with a different charset. SQL statements shall not be used to change charsets as they are not monitored by the plugin. However, upon establishing a connection to any MySQL server, the connection will be set to server_charset again.
No need to take care of the server configuration
Enforcing the configured server_charset whenever a connection is opened free’s the administrator from the need to set the same default charset on all servers. When using version 1.3 or older you should make sure that servers use the same charset. This prevents tapping into the pitfall of escaping a string using the charset of the first server contacted, then switching the connection to another server/connection with a different charset and accidently using a wrongly escaped string.
More tweaks and improvements considered for version 1.4 are listed at http://wiki.php.net/pecl/mysqlnd_ms. Please, do not understand the list as a firm promise that everything will be implemented. Feel free to add ideas or tasks to the wiki page.
Happy hacking!
