PHP API, MYSQL and MYSQLI persistent connection difference

Long long ago, I studied the difference between mysqlnd and libmysql, the connection driver of PHP mysql client, because of a bug in my work. It took me more than a month to write this article. First, I was too lazy, and second, I was busy with my work. I’ve only been able to get around to these things lately. Each time you do a summary, read the source code carefully, understand the meaning, test validation, to confirm these details. Each of these steps takes a long time and can’t be interrupted. Once interrupted, it takes a long time to review the context. Also deliberately forced myself to write this summary, to change their inertia.

The error “mysql Server too many Connections” occurred during the development and testing of my friends and I in full swing. After a slight investigation, WE found that the PHP background process had established a large number of links instead of closing them. The server environment is about the following PHP5.3. x, mysqli API, mysqlnd driver. The code looks like this:

// Background process A
/ *
Configuration information
‘mysql’=>array(
‘driver’=>’mysqli’,
// ‘driver’=>’pdo’,
// ‘driver’=>’mysql’,
‘host’ = > ‘192.168.111.111’,
‘user’=>’root’,
‘port’=>3306,
‘dbname’=>’dbname’,
‘socket’=>”,
‘pass’=>’pass’,
‘persist’=>true, // as mentioned below, this is the configuration for persistent links
),
* /
$config=Yaf_Registry::get(‘config’);
$driver = Afx_Db_Factory::DbDriver($config[‘mysql’][‘driver’]); //mysql mysqli
$driver::debug($config[‘debug’]); // Notice here
$driver->setConfig($config[‘mysql’]); // Notice here
Afx_Module::Instance()->setAdapter($driver); // Pay attention here. Pay attention to where you feel uncomfortable.
$queue=Afx_Queue::Instance();
$combat = new CombatEngine();
$Role = new Role(1,true);
$idle_max=isset($config[‘idle_max’])? $config[‘idle_max’]:1000;
while(true)
{
$data = $queue->pop(MTypes::ECTYPE_COMBAT_QUEUE, 1);
if(! $data){
usleep(50000); // Sleep for 0.05 seconds
++$idle_count;
if($idle_count>=$idle_max)
{
$idle_count=0;
Afx_Db_Factory::ping();
}
continue;
}
$idle_count=0;
$Role->setId($data[‘attacker’][‘role_id’]);
$Property = $Role->getModule(‘Property’);
$Mounts = $Role->getModule(‘Mounts’);
/ /…
unset($Property, $Mounts/*….. * /);
}

You can see the “$Property” variable and the “$Mounts” variable are frequently created and destroyed in this daemon code. The ROLE object’s getModule method is written like this

Class ROLE extends Afx_Module_Abstract {public function getModule ($member_class) {$property_name  = '__m' . ucfirst($member_class); if (! isset($this->$property_name)) { $this->$property_name = new $member_class($this); } return $this->$property_name; Class Property extends Afx_Module_Abstract {public function __construct ($mRole) {$this->__mRole = $mRole; }}Copy the code

As you can see, the getModule method just emulates the singleton by returning a new object, and they all inherit from the Afx_Module_Abstract class. The approximate code for the Afx_Module_Abstract class is as follows:

abstract class Afx_Module_Abstract
{
public function setAdapter ($_adapter)
{
$this->_adapter = $_adapter;
}
}

The key code in the Afx_Module_Abstract class is as above, related to DB, setAdapter method, back to “background process A”, The setAdapter method passes the return of Afx_Db_Factory::DbDriver($config[‘ mysql ‘][‘ driver ‘]) as an argument. Continue with the code for the Afx_Db_Factory class

class Afx_Db_Factory
{
const DB_MYSQL = ‘mysql’;
const DB_MYSQLI = ‘mysqli’;
const DB_PDO = ‘pdo’;
public static function DbDriver ($type = self::DB_MYSQLI)
{
switch ($type)
{
case self::DB_MYSQL:
$driver = Afx_Db_Mysql_Adapter::Instance();
break;
case self::DB_MYSQLI:
$driver = Afx_Db_Mysqli_Adapter::Instance(); // This is it
break;
case self::DB_PDO:
$driver = Afx_Db_Pdo_Adapter::Instance();
break;
default:
break;
}
return $driver;
}
}

This is a factory class. Continue to look at the real DB Adapter part of the code

class Afx_Db_Mysqli_Adapter implements Afx_Db_Adapter
{
public static function Instance ()
{
if (! self::$__instance instanceof Afx_Db_Mysqli_Adapter)
{
self::$__instance = new self(); // This is a singleton. Why is it a link to mysql?
}
return self::$__instance;
}
public function setConfig ($config)
{
$this->__host = $config[‘host’];
/ /…
$this->__user = $config[‘user’];
$this->__persist = $config[‘persist’];
if ($this->__persist == TRUE)
{
$this->__host = ‘p:’ . $this->__host; // Persistent links are supported
}
$this->__config = $config;
}
private function __init ()
{
$this->__link = mysqli_init();
$this->__link->set_opt(MYSQLI_OPT_CONNECT_TIMEOUT, $this->__timeout);
$this->__link->real_connect($this->__host, $this->__user, $this->__pass, $this->__dbname, $this->__port, $this->__socket);
if ($this->__link->errno == 0)
{
$this->__link->set_charset($this->__charset);
} else
{
throw new Afx_Db_Exception($this->__link->error, $this->__link->errno);
}
}
}

As you can see from the above code, we have enabled long links. Why do we create so many links so frequently? In order to simulate and reproduce this problem, I tried to test the local development environment, but I could not reproduce it anyway. Compared the environment, my development environment is Windows7, PHP5.3. x, mysql, libmysql, and the server is not consistent, the problem is likely to occur in mysql and mysqli API. Or libmysql and mysqlnd. So I carefully opened the PHP source code (the latest 5.3.x) and finally found the cause of these problems.

// in line 907-916 of ext\mysql\php_mysql.c
//mysql_connect and mysql_pconnect call persistent false or true
static void php_mysql_do_connect(INTERNAL_FUNCTION_PARAMETERS, int persistent)
{
/* hash it up */
Z_TYPE(new_le) = le_plink;
new_le.ptr = mysql;
// Notice the code in if below
if (zend_hash_update(&EG(persistent_list), hashed_details, hashed_details_length+1, (void *) &new_le, sizeof(zend_rsrc_list_entry), NULL)==FAILURE) {
free(mysql);
efree(hashed_details);
MYSQL_DO_CONNECT_RETURN_FALSE();
}
MySG(num_persistent)++;
MySG(num_links)++;
}

[mysql_pconnect] persistent_list [mysql_pconnect] [mysql_pconnect] Persistent_list looks for links to IP, PORT, USER, PASS, and CLIENT_FLAGS.

The MYSqli extension of PHP uses not only a persistent_list to store links, but also a free_link to store currently idle TCP links. When the search is performed, it also determines whether the link exists in the free free_link list. If it does, the TCP link will be used. This link is pushed into free_links only after mysqli_closez or RSHUTDOWN. (Mysqli will look for the same IP address, PORT, USER, PASS, DBNAME, SOCKET as the same id. Unlike mysql, there is no CLIENT, DBNAME and SOCKET, and IP also includes the long connection identifier “P”)

Mysqli_common_connect creates a TCP connection (when mysqli_connect is called)
do {
if (zend_ptr_stack_num_elements(&plist->free_links)) {
mysql->mysql = zend_ptr_stack_pop(&plist->free_links); // Pop it out, and the next mysqli_connect call from the same script will not find it
MyG(num_inactive_persistent)–;
/* reset variables */
#ifndef MYSQLI_NO_CHANGE_USER_ON_PCONNECT
if (! Mysqli_change_user_silent (mysqli_change_user_silent(mysql->mysql, username, passwd, dbname, passwd_len)) { Notice here mysqli_change_user_silent
#else
if (! mysql_ping(mysql->mysql)) {
#endif
#ifdef MYSQLI_USE_MYSQLND
mysqlnd_restart_psession(mysql->mysql);
#endif
}
// ext\mysqli\mysqli_api.c lines 585-615
/* {{{ php_mysqli_close */
void php_mysqli_close(MY_MYSQL * mysql, int close_type, int resource_status TSRMLS_DC)
{
if (resource_status > MYSQLI_STATUS_INITIALIZED) {
MyG(num_links)–;
}
if (! mysql->persistent) {
mysqli_close(mysql->mysql, close_type);
} else {
zend_rsrc_list_entry *le;
if (zend_hash_find(&EG(persistent_list), mysql->hash_key, strlen(mysql->hash_key) + 1, (void **)&le) == SUCCESS) {
if (Z_TYPE_P(le) == php_le_pmysqli()) {
mysqli_plist_entry *plist = (mysqli_plist_entry *) le->ptr;
#if defined(MYSQLI_USE_MYSQLND)
mysqlnd_end_psession(mysql->mysql);
#endif
zend_ptr_stack_push(&plist->free_links, mysql->mysql); // This is push back, we can use it again next time
MyG(num_active_persistent)–;
MyG(num_inactive_persistent)++;
}
}
mysql->persistent = FALSE;
}
mysql->mysql = NULL;
php_clear_mysql(mysql);
}
/ * * /}}}

Why does MYSQLI do this? Why can’t the same long connection be reused in the same script?

Mysqli_change_user_silent = mysqli_common_connect = mysqli_common_connect Mysqli_change_user_silent corresponds to mysql_change_user of libmysql or mysqlnd_change_user_ex of mysqlnd, Mysql_change_user (mysql_change_user, mysql_change_user, mysql_change_user, mysql_change_user, mysql_change_user, mysql_change_user, mysql_change_user, mysql_change_user, mysql_change_user, mysql_change_user, mysql_change_user, mysql_change_user, mysql_change_user) It is not PHP’s mysqli that determines The sent SQL instructions and makes a response decision), see The manual for The description of mysqli Extension and Persistent Connections. This is designed for this new feature, which mysql extensions do not support.

From the shallow understanding of these code, you can understand the difference between mysqli and mysql persistent link, this problem, may be difficult to understand, I later searched, also found a confusion because of this reason, we look at this case, may be very easy to understand. The Mysqli persistent connect doesn’t work is not specific to the underlying implementation of Mysqli. The code is as follows:

$links = array();
for ($i = 0; $i < 15; $i++) {
$links [] = mysqli_connect (‘ p: 192.168.1.40 ‘, ‘USER’, ‘the PWD’, ‘DB’, 3306);
}
sleep(15);

The process list looks like this:

Netstat - an | grep 192.168.1.40: TCP 3306 0 0 192.168.1.6:52441 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52454 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52445 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52443 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52446 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52449 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52452 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52442 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52450 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52448 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52440 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52447 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52444 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52451 192.168.1.40:3306 ESTABLISHED TCP 0 0 192.168.1.6:52453 192.168.1.40:3306 ESTABLISHEDCopy the code

So if YOU look at the code this way, it’s a lot clearer, and it’s a lot easier to verify that I got it right, so you can see it

for ($i = 0; $i < 15; $i++) {
$links ($I) = mysqli_connect (‘ p: 192.168.1.40 ‘, ‘USER’, ‘the PWD’, ‘DB’, 3306);
var_dump(mysqli_thread_id($links[$i])); // If you are worried about closing a new TCP connection, you can print the thread ID to see if it is the same ID
mysqli_close($links[$i])
}
/ *
The results are as follows:
root@cnxct:/home/cfc4n# netstat -antp |grep 3306|grep -v “php-fpm”
TCP 00 192.168.61.150:55148 192.168.71.88:3306 ESTABLISHED 5100/php5
root@cnxct:/var/www# /usr/bin/php5 4.php
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
int(224218)
* /

If you’re worried about closing a new TCP connection, you can print the thread ID to see if it’s the same ID. (I didn’t reply to this post, but that doesn’t make me bad.) This is the CLI mode. In FPM mode, each page request is handled by a single FPM child process. This subprocess is responsible for maintaining long links between PHP and mysql Server. If you visit this page multiple times to check whether the thread ID is the same, it may be distributed to other FPM subprocesses, resulting in different results. But eventually, each FPM subprocess maintains these TCP links separately.

In general, the difference between mysqli extension and mysql extension is the following several

Mysqli = ‘p:’; Mysql uses mysql_pconnect; .
The persistent link established by mysqli must be released after mysqli_close before it can be reused by the next request, or after RSHOTDOWN. Mysql long connection, can be reused immediately
Persistent links created by PDO can be reused without being closed.
When mysqli establishes a persistent link, it will automatically clean up the last session variable, roll back the transaction, unlock the table, release the lock, etc. Mysql will not.
Mysqli checks whether the persistent link identifier is IP, PORT, USER, PASS, DBNAME, SOCKET. Mysql is IP, PORT, USER, PASS, CLIENT_FLAGS

Add a mysqli_close method to the __destruct destructor of a Property class and call the mysqli_close function when it is destroyed. Push persistent links into free_links. If you think so, I can only congratulate you, wrong, the best solution is not to let it be created so many times at all. Colleague Dietoad gave a solution, the most true singleton for DB ADAPTER, and, optionally, whether to create new links. The following code:

// DB FACTORY
class Afx_Db_Factory
{
const DB_MYSQL = ‘mysql’;
const DB_MYSQLI = ‘mysqli’;
const DB_PDO = ‘pdo’;
static $drivers = array(
‘mysql’=>array(),’mysqli’=>array(),’pdo’=>array()
);
Public static function DbDriver ($type = self::DB_MYSQLI, $create = FALSE
{
$driver = NULL;
switch ($type)
{
case self::DB_MYSQL:
$driver = Afx_Db_Mysql_Adapter::Instance($create);
break;
case self::DB_MYSQLI:
$driver = Afx_Db_Mysqli_Adapter::Instance($create);
break;
case self::DB_PDO:
$driver = Afx_Db_Pdo_Adapter::Instance($create);
break;
default:
break;
}
self::$drivers[$type][] = $driver;
return $driver;
}
}
//mysqli adapter
class Afx_Db_Mysqli_Adapter implements Afx_Db_Adapter
{
public static function Instance ($create = FALSE)
{
if ($create)
{
return new self(); // Add the $create argument
}
if (! self::$__instance instanceof Afx_Db_Mysqli_Adapter)
{
self::$__instance = new self();
}
return self::$__instance;
}
}

It seems so important that the development environment is consistent with the runtime environment, otherwise these problems would not have occurred. But wouldn’t it be a shame not to have such an interesting problem? (Photo: Winter)

via http://www.cnxct.com/some-differences-between-mysql-and-mysqli-of-persistent-connection/

To admire the

PHP API, MYSQL and MYSQLI persistent connection difference

Related Posts

Graphic Design patterns: The application of identity scenarios

Windows 10 system installation Oracle11g database and client client method

A way to implement distributed locking using django_cache