In PHP API, the persistent connection between MySQL and Mysqli is different

Verification of retained re-test

PHP API, MySQL with Mysqli's persistent connection area ...

A long time ago, I was also because of the work of the bug, the PHP MySQL client connection to drive Mysqlnd and libmysql the difference between PHP and MySQL communication that point, this time again encountered a contact with them, The difference between mysqli and MySQL persistent links. Write this article, with a good one months, one is I am too lazy, the other is the work is also relatively busy. Only recently can we make time to do these things. Every time you make a summary, you should read the source code carefully, understand the meaning, test verification, to confirm these details. Each step takes a long time and cannot be interrupted. Once interrupted, it takes a long time to brush up on the context. Also deliberately forced themselves to write this summary, change their inertia.
I and my little friends in full swing in the development, testing occurred "MySQL server too many connections" error, a little troubleshooting, found that the PHP background process has established a large number of links, but not closed. The server environment is approximately as follows php5.3.x, mysqli API, Mysqlnd driver. The code situation is this:

//Background process a
/*
configuration information
' MySQL ' =>array (
' driver ' = ' mysqli ',
//' Driver ' = ' PDO ',
//' Driver ' = ' mysql ',
' host ' = ' 192.168.111.111 ',
' user ' = ' root ',
' Port ' =>3306,
' dbname ' and ' = ' DBName ',
' socket ' = ', '
' pass ' = ' pass ',
' persist ' =>true,///below mentioned Oh, this is the configuration of persistent link
),
*/
$ Config=yaf_registry::get (' config ');
$driver = afx_db_factory::D bdriver ($config [' MySQL '] [' Driver '])//mysql mysqli
$driver::d ebug ($config [' Debug ']); Note here
$driver->setconfig ($config [' MySQL ']);//note here
Afx_module::instance ()->setadapter ($driver); Note here, where uncomfortable, pay attention to see where.

$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); /hibernate 0.05 seconds
+ + $idle _count;
if ($idle _count>= $idle _max)
{
$idle _count=0;
Afx_db_factory::p ing ();
}
continue;
}
$idle _count=0;
$Role->setid ($data [' Attacker '] [' role_id ']);
$Property = $Role->getmodule (' property ');
$Mounts = $Role->getmodule (' Mounts ');
//......
unset ($Property, $Mounts/*.....*/);
}

From this background process code, you can see that the "$Property" variable and the "$Mounts" variable are frequently created and destroyed. The GetModule method for the Role object is written in this way

GetModule Methods for Role objects
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;
}
}
Property class
Class Property extends Afx_module_abstract
{
Public function __construct ($mRole)
{
$this->__mrole = $mRole;
}
}

As you can see, the GetModule method simply simulates a singleton, and new returns the object, and they all inherit the Afx_module_abstract class. The Afx_module_abstract class is about the following code:

Abstract class Afx_module_abstract
{
Public Function Setadapter ($_adapter)
{
$this->_adapter = $_adapter;
}
}

Class Afx_module_abstract in the key code, as related to the Db, Setadapter a method, back to "background process a", Setadapter method is to Afx_db_factory::D bdriver ($config [' The return of MySQL ' [' Driver ']) is passed in as a parameter. Keep looking at 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::D b_mysqli)
{
Switch ($type)
{
Case self::D b_mysql:
$driver = Afx_db_mysql_adapter::instance ();
Break
Case self::D b_mysqli:
$driver = Afx_db_mysqli_adapter::instance (); Here we go.
Break
Case self::D b_pdo:
$driver = Afx_db_pdo_adapter::instance ();
Break
Default
Break
}
return $driver;
}
}

I know it's a factory class, keep looking at the real DB adapter part 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 (); Here is a singleton mode, why does the new one become a MySQL link?
}
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; This is done for persistent links and supports persistent links
}
$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 code above, we've already enabled long links, why do we have so many links built up so often? In order to reproduce this problem, I am in the local development environment for testing, no matter how can not reproduce, compared to the next environment, my development environment is Windows7, php5.3.x, MySQL, Libmysql, with the server inconsistencies, The problem is likely to be on MySQL and Mysqli's API, or Libmysql and mysqlnd issues. To this end, I carefully opened the PHP source code (5.3.x the latest), finally Kung Fu, found the cause of these problems.

907-916 lines in the file ext\mysql\php_mysql.c
Mysql_connect, Mysql_pconnect call it, the difference is whether the persistent link identity is persistent to 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;
Note the following if the code inside
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) + +;
}

From the Mysql_pconnect code, you can see that when PHP expands the MySQL API and MySQL server to establish a TCP link, the link is immediately stored in the Persistent_list, the next time the link is established, will be first from Persistent_ The list finds out if there is a link to the IP, PORT, USER, PASS, client_flags, it is used, does not exist, and is new.
PHP's mysqli extension not only used a persistent_list to store the link, but also used a free_link to store the currently idle TCP link. When looking, it also determines whether to exist in an idle Free_link linked list before using this TCP link. After Mysqli_closez or Rshutdown, the link is then push to free_links. (Mysqli will find the same ip,port, USER, PASS, DBNAME, socket as the same logo, unlike MySQL, without the client, more DBNAME with the socket, and IP also includes long connection identification "P")

File Ext\mysqli\mysqli_nonapi.c 172 rows or so mysqli_common_connect create a TCP link (mysqli_connect function call)
do {
if (zend_ptr_stack_num_elements (&plist->free_links)) {
Mysql->mysql = Zend_ptr_stack_pop (&plist->free_links); The direct pop comes out, and the next mysqli_connect of the same script is called again, and it's not found.

Myg (num_inactive_persistent)--;
/* Reset Variables */

#ifndef mysqli_no_change_user_on_pconnect
if (!mysqli_change_user_silent (Mysql->mysql, username, passwd, dbname, Passwd_len) {//(let you look at it again) notice here mysqli_change_user_silent
#else
if (!mysql_ping (Mysql->mysql)) {
#endif
#ifdef mysqli_use_mysqlnd
Mysqlnd_restart_psession (mysql->mysql);
#endif
}
//File Ext \MYSQLI\MYSQLI_API.C 585-615 Line
/* {{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); It's on push back, and it's going to work again next time.

Myg (num_active_persistent)--;
Myg (num_inactive_persistent) + +;
}
}
Mysql->persistent = FALSE;
}
Mysql->mysql = NULL;

Php_clear_mysql (MySQL);
}
/* }}} */

Why would mysqli do this? Why is the same long connection not reusable in the same script?
In the C function mysqli_common_connect see there is a mysqli_change_user_silent call, such as the code, mysqli_change_user_silent corresponding to this libmysql mysql_ Change_user or Mysqlnd mysqlnd_change_user_ex, they are all called the C API Mysql_change_user to clean up some temporary session variables of the current TCP link, incomplete commit rollback instructions, lock table directives, temporary table unlock and so on (these instructions are all MySQL server itself decided to complete, not PHP mysqli to determine the sent SQL instructions and then make a response decision), see the manual description of the mysqli Extension and persistent Connections. This design is for this new feature, and MySQL extension does not support this feature.
From the shallow understanding of these code, you can understand the mysqli and MySQL persistent link differences, this problem, may be more difficult to understand, I later searched, but also found a reason for this cause of doubts, we see this case, it may be very easy to understand. Mysqli Persistent Connect doesn ' t work respondents are not specific to the MYSQLI bottom-level implementation, which is actually the reason. The code is as follows:

<?php
$links = Array ();
for ($i = 0; $i < $i + +) {
$links [] = Mysqli_connect (' p:192.168.1.40 ', ' USER ', ' PWD ', ' DB ', 3306);
}
Sleep (15);

View the process list as a result:

Netstat-an grep 192.168.1.40:3306
TCP 0 0 192.168.1.6:52441 192.168.1.40:3306 established
TCP 0 0 192.168.1.6:5 2454 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 ESTABLISHED

This way of looking at the code, it is much clearer, verify that my understanding is also relatively simple, so a change to see the

for ($i = 0; $i < $i + +) {
$links [$i] = Mysqli_connect (' p:192.168.1.40 ', ' USER ', ' PWD ', ' DB ', 3306);
Var_dump (mysqli_thread_id ($links [$i]); If you are worried about being close, this is a new TCP link, then you can print the thread ID and see if it is the same ID.
Mysqli_close ($links [$i])
}
/*
The results are as follows:
[Email protected]:/home/cfc4n# netstat-antp grep 3306grep-v "PHP-FPM"
TCP 0 0 192.168.61.150:55148 192.168.71.88:3306 established 5100/PHP5
[Email protected]:/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 being close and this is a new TCP link, then you can print the thread ID and see if it's the same ID, it's clear. (although I didn't reply to this post, it doesn't prove that I'm bad.) This is the case when the CLI mode is above. When in FPM mode, each page request is processed by a single FPM child process. This subprocess will be responsible for maintaining the long link between PHP and MySQL server, so when you visit this page multiple times to confirm that it is the same thread ID, it may be distributed separately to other FPM subprocess processes, resulting in a different result. Eventually, however, each FPM child process maintains these TCP links separately.
Overall, the difference between mysqli expansion and MySQL expansion is the following
Persistent link creation method, Mysqli is added "P:" Two characters in front of host, MySQL uses mysql_pconnect function;.
Mysqli established persistent link must be after mysqli_close, the following code will be reused, or rshotdown after the next request is reused; MySQL's long connection, can be reused immediately
mysqli when a persistent link is established, the last session variable, ROLLBACK TRANSACTION, table unlock, release lock, etc. are automatically cleaned up; MySQL won't.
Mysqli determine if the same persistent link is identified as Ip,port, USER, PASS, DBNAME, Socket;mysql is IP, PORT, USER, PASS, Client_flags
Well, know this reason, then our article at the beginning of the question to solve, you must first think of in a similar property class, __destruct destructor Add a Mysqli_close method, when destroyed, call the close function, Push the persistent link into the free_links. If you think so, I can only congratulate you, the answer is wrong, the best solution is not to let it create so many times. Colleague Dietoad a solution to the most real single case of DB adapter, and, optionally, whether to create a new link. 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::D b_mysqli, $create = FALSE)//new $create parameter
{
$driver = NULL;
Switch ($type)
{
Case self::D b_mysql:
$driver = Afx_db_mysql_adapter::instance ($create);
Break
Case self::D b_mysqli:
$driver = Afx_db_mysqli_adapter::instance ($create);
Break
Case self::D b_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 (); The new $create parameter is judged
}
if (! self::$__instance instanceof Afx_db_mysqli_adapter)
{
Self::$__instance = new self ();
}
return self::$__instance;
}
}

It seems that the development environment is as important as the running environment, otherwise you will not encounter these problems. But it would be a pity if we hadn't had such an interesting question.

The persistent connection between MySQL and mysqli in the PHP API