What is the persistent connection between MYSQL and MYSQLI in PHPAPI?

A long time ago, I also studied the difference between mysqlnd and libmysql, the connection driver of phpmysqlclient, and the communication between php and mysql, this time I encountered another thing that had something to do with them, the difference between mysqli and mysql persistent connections. Write this article... "/> <scripttype =" text/javascri a long time ago, I was also caused by work bugs, I studied the difference between mysqlnd and libmysql, the connection driver of php mysql client, and the communication between php and mysql. this time I met another thing that I had something to do with them, what is the difference between mysqli and mysql persistent connections. It took more than a month to write this article. One was that I was too lazy and the other was busy at work. Only recently can we free up time to do these things. Every time you make a summary, you should carefully read the source code, understand the meaning, test and verify to confirm these details. Each step takes a long time and cannot be interrupted. Once interrupted, it takes a long time to study the context. I also deliberately forced myself to write this summary and change my inertia.

I encountered the "mysql server too many ONS" error during the development and testing of my friends. I checked it a bit and found that a large number of links were established by the php background process, but not disabled. The server environment is about php5.3.x, mysqli API, and mysqlnd driver. The code is as follows:

// Background process A/* configuration information 'mysql' => array ('driver '=> 'mysqli', // 'driver' => 'pdo ', // 'driver '=> 'mysql', 'host' => '2017. 168.111.111 ', 'user' => 'root', 'port' => 3306, 'dbname' => 'dbname', 'socket' => '', 'pass' => 'pass', 'persist '=> true, // as mentioned below, this is the configuration of persistent links.), */$ config = Yaf_Registry :: get ('config'); $ driver = Afx_Db_Factory: DbDriver ($ config ['mysql'] ['driver ']); // mysql mysqli $ driver :: debug ($ config ['debug']); // note here $ driver-> se TConfig ($ config ['mysql']); // pay attention to the Afx_Module: Instance ()-> setAdapter ($ driver); // pay attention to the uncomfortable conditions, pay attention to where to look. $ 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 ('properties'); $ Mounts = $ Role-> getModule ('mounts '); //............ unset ($ Property, $ Mounts /*..... */);}

 

From this background process code, we can see that the "$ Property" variable and the "$ Mounts" variable are frequently created and destroyed. The getModule method of the ROLE object is written in this way.

// The getModule Method 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 Property extends Afx_Module_Abstract {public function _ construct ($ mRole) {$ this->__ mRole = $ mRole ;}}

 

It can be seen that the getModule method only simulates the singleton, and a new object is returned, and they all inherit the Afx_Module_Abstract class. The approximate code of 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. for DB-related code, set the setAdapter method to return to "background process A". The setAdapter method is to set Afx_Db_Factory :: dbDriver ($ config ['mysql'] ['driver ']) is returned as a parameter. Continue to read the Afx_Db_Factory class code.

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 (); // break; case self: DB_PDO: $ driver = Afx_Db_Pdo_Adapter: Instance (); break; default: break;} return $ driver ;}}

 

At first glance, we know that it is a factory class. continue to look at the real DB Adapter 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 the singleton mode. why is a new mysql connection generated?} 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; // The persistent link is processed here. 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 );}}}

 

From the code above, we can see that we have enabled persistent links. Why have we established so many links frequently? In order to simulate and reproduce this problem, I tested it in a local development environment and could not reproduce it anyway. In comparison, my development environments are Windows 7, php5.3.x, mysql, and libmysql, inconsistency with the server may occur in the APIs of mysql and mysqli, or libmysql and mysqlnd. To this end, I carefully opened the PHP source code (5.3.x latest) and finally found the cause of these problems.

 

// Call it in line 907-916 of ext \ mysql \ php_mysql.c // mysql_connect and mysql_pconnect. The difference is whether the persistent link ID is "false" or "truestatic void parameters (INTERNAL_FUNCTION_PARAMETERS, int persistent) {/* hash it up */Z_TYPE (new_le) = le_plink; new_le.ptr = mysql; // pay attention to the code 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); detail ();} mySG (num_persistent) ++; MySG (num_links) ++ ;}

 

From the code of mysql_pconnect, we can see that when php expands the mysql api to establish a TCP link with the mysql server, it will immediately store this link into the persistent_list, it first searches for links with the same IP address, PORT, USER, PASS, and CLIENT_FLAGS from persistent_list. If yes, it is used. If no connection exists, it is created.

The mysqli extension of php not only uses a persistent_list to store links, but also uses a free_link to store idle TCP links. When searching, the system also determines whether the link exists in the idle free_link linked list and uses the TCP link only if it exists. After mysqli_closez or RSHUTDOWN, the link is pushed to free_links. (Mysqli will search for the same IP address, PORT, USER, PASS, DBNAME, and SOCKET as the same identifier. Unlike mysql, if there is no CLIENT, DBNAME and SOCKET are added, and the IP address also includes the persistent connection identifier "p ")

// FILE ext \ mysqli \ mysqli_nonapi.c. mysqli_common_connect creates a TCP link (when the mysqli_connect function is called) do {if (zend_ptr_stack_num_elements (& plist-> free_links )) {mysql-> mysql = zend_ptr_stack_pop (& plist-> free_links); // pop it directly. when the next mysqli_connect call of the same script is made, MyG (num_inactive_persistent) cannot be found) --;/* reset variables */# ifndef MYSQLI_NO_CHANGE_USER_ON_PCONNECT if (! Mysqli_change_user_silent (mysql-> mysql, username, passwd, dbname, passwd_len) {// (when you want to see it again) note that 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 \ limit 585-615 lines/* {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 (success) mysqlnd_end_psession (mysql-> mysql); # define (& plist-> free_links, mysql-> mysql); // Here, push back, next time you can use MyG (num_active_persistent) --; MyG (num_inactive_persistent) ++ ;}} mysql-> persistent = FALSE;} mysql-> mysql = NULL; php_clear_mysql (mysql );}/*}}}*/

 

Why does MYSQLI do this? Why cannot I reuse the same persistent connection in the same script?
In the C function mysqli_common_connect, we can see a call to mysqli_change_user_silent. in the above code, mysqli_change_user_silent corresponds to mysql_change_user of libmysql or mysqlnd, all of them call mysql_change_user of the c api to clear some temporary session variables of the current TCP link, submit rollback commands and lock table commands that are not completely written, temporary table unlocking and so on (these commands are all determined by the mysql server, rather than the php mysqli to determine the sent SQL command and then make a response decision ), see The instructions in The manual for The mysqli Extension and Persistent Connections. This design aims at this new feature, which is not supported by mysql expansion.

From the perspective of understanding these codes, we can understand the difference between the persistent connections between mysql and mysql. this problem may be hard for everyone to understand. I searched for it later, I also found a doubt for this reason. it may be very easy to understand this case. The Mysqli persistent connect doesn' t work responder is not specific to the underlying implementation of mysqli, which is actually the reason. The code is as follows:

  
View the result in the process list:

netstat -an | grep 192.168.1.40:3306tcp        0      0 192.168.1.6:52441       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52454       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52445       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52443       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52446       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52449       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52452       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52442       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52450       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52448       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52440       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52447       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52444       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52451       192.168.1.40:3306       ESTABLISHEDtcp        0      0 192.168.1.6:52453       192.168.1.40:3306       ESTABLISHED
 
In this way, the code is much clearer, and it is easy to verify that my understanding is correct. This change shows that

For ($ I = 0; $ I <15; $ 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 closed, this is the newly created TCP link, then you can print the thread id to see if it is the same ID. the mysqli_close ($ links [$ I])}/* result is as follows: root @ cnxct: /home/cfc4n # netstat-antp | grep 3306 | grep-v "php-fpm" tcp 0 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 are worried about being closed, this is the newly created TCP link, you can print the thread id to see if it is the same ID. (Although I did not reply to this post, it cannot prove that I am very bad .) The above is the case in CLI mode. In FPM mode, each page request is processed by a single fpm sub-process. This sub-process will be responsible for maintaining the long link established between php and mysql server. Therefore, when you visit this page multiple times to confirm whether it is the same thread id, the results may be distributed to other fpm sub-processes separately, resulting in different results. But in the end, each fpm sub-process will maintain these TCP links separately.
In general, the difference between mysqli expansion and mysql expansion is the following:
 
 

  
  
Persistent connection establishment method. mysqli adds the "p:" character before the host. mysql uses the mysql_pconnect function ;.
  
  
Persistent connections established by mysqli can be reused by the next request only after mysqli_close, or after the following code is reused or RSHOTDOWN. mysql persistent connections can be reused immediately.
  
  
When mysqli creates a persistent link, the previous session variables, rollback transactions, table unlocking, and lock release operations are automatically cleared; mysql does not.
  
  
Mysqli determines whether the same persistent link ID is IP, PORT, USER, PASS, DBNAME, SOCKET; mysql is IP, PORT, USER, PASS, CLIENT_FLAGS
 
 
Well, if you know the reason, the problem we mentioned at the beginning of this article will be solved. you must first think of a class similar to Property, __add a mysqli_close method to the destruct destructor. when the method is destroyed, call the close function to push the persistent link to free_links. If you think so, I can only congratulate you. wrong answer. The best solution is not to allow it to be created so many times. Colleague dietoad gave a solution, which is the most genuine Singleton for the db adapter, and optional whether to create a new link. The following code:

// DB FACTORYclass 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) // add the $ create parameter {$ driver = NULL; switch ($ type) {case self :: DB_MYSQL: $ driver = Afx_Db_Mysql_Adapter: Instance ($ create); break; case sel F: 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 adapterclass Afx_Db_Mysqli_Adapter implements Afx_Db_Adapter {public static function Instance ($ create = FALSE) {if ($ create) {return new self (); // add $ create parameter judgment} if (! Self: :__ __ instance instanceof Afx_Db_Mysqli_Adapter) {self ::$ __ instance = new self ();} return self ::__ instance ;}}
 
It seems that the consistency between the development environment and the running environment is very important, otherwise these problems will not be encountered. However, if you haven't encountered such an interesting problem, it would be a pity.