Skip to content

Instantly share code, notes, and snippets.

@withgod
Last active Nov 8, 2020
Embed
What would you like to do?
mysqlnd persistent connection problem
[ec2-user@test test]$ git diff
diff --git a/mysql3.php b/mysql3.php
index a4f6a8a..0e277f0 100644
--- a/mysql3.php
+++ b/mysql3.php
@@ -11,8 +11,8 @@ define('MY_TBL', 'sample');
function _check() {
$link = mysqli_init();
- $ret1 = mysqli_options($link, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE);
$ret2 = mysqli_real_connect($link, 'p:' . MY_HOST, MY_USER, MY_PASS);
+ $ret1 = mysqli_options($link, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE);
$ret3 = mysqli_select_db($link, MY_DB);
printf("ret1[%s]ret2[%s]ret3[%s]\n", $ret1, $ret2, $ret3);
$ret = mysqli_query($link, 'select id,name, age from ' . MY_DB);
[ec2-user@test test]$ php74 ./mysql3.php
1st time
ret1[1]ret2[1]ret3[1]
array(3) {
["id"]=>
int(1)
["name"]=>
string(4) "john"
["age"]=>
int(20)
}
2nd time
ret1[1]ret2[1]ret3[1]
array(3) {
["id"]=>
int(1)
["name"]=>
string(4) "john"
["age"]=>
int(20)
}
3rd time
ret1[1]ret2[1]ret3[1]
array(3) {
["id"]=>
int(1)
["name"]=>
string(4) "john"
["age"]=>
int(20)
}
[ec2-user@test test]$ php74 -v
PHP 7.4.12 (cli) (built: Oct 27 2020 15:01:52) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
[ec2-user@test ~]$ php74 --ri mysqli
mysqli
MysqlI Support => enabled
Client API library version => mysqlnd 7.4.12
Active Persistent Links => 0
Inactive Persistent Links => 0
Active Links => 0
Directive => Local Value => Master Value
mysqli.max_links => Unlimited => Unlimited
mysqli.max_persistent => Unlimited => Unlimited
mysqli.allow_persistent => On => On
mysqli.rollback_on_cached_plink => Off => Off
mysqli.default_host => no value => no value
mysqli.default_user => no value => no value
mysqli.default_pw => no value => no value
mysqli.default_port => 3306 => 3306
mysqli.default_socket => /var/lib/mysql/mysql.sock => /var/lib/mysql/mysql.sock
mysqli.reconnect => Off => Off
mysqli.allow_local_infile => Off => Off
[ec2-user@test test]$ php74 ./mysql3.php
1st time
ret1[1]ret2[1]ret3[1]
array(3) {
["id"]=>
int(1)
["name"]=>
string(4) "john"
["age"]=>
int(20)
}
2nd time
ret1[1]ret2[1]ret3[1]
array(3) {
["id"]=>
int(1)
["name"]=>
string(4) "john"
["age"]=>
int(20)
}
3rd time
ret1[1]ret2[1]ret3[1]
array(3) {
["id"]=>
string(1) "1"
["name"]=>
string(4) "john"
["age"]=>
string(2) "20"
}
<?php
header('Content-type: text/plain');
ini_set( 'display_errors', 1 );
ini_set('error_reporting', E_ALL);
define('MY_HOST', '127.0.0.1');
define('MY_USER', 'user');
define('MY_PASS', 'password');
define('MY_DB', 'sample');
define('MY_TBL', 'sample');
function _check() {
$link = mysqli_init();
$ret1 = mysqli_options($link, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE);
$ret2 = mysqli_real_connect($link, 'p:' . MY_HOST, MY_USER, MY_PASS);
$ret3 = mysqli_select_db($link, MY_DB);
printf("ret1[%s]ret2[%s]ret3[%s]\n", $ret1, $ret2, $ret3);
$ret = mysqli_query($link, 'select id,name, age from ' . MY_DB);
$row = mysqli_fetch_array($ret, MYSQLI_ASSOC);
var_dump($row);
}
echo '1st time' . PHP_EOL;
_check();
echo '2nd time' . PHP_EOL;
_check();
// kill connection. emulate wait_timeout exceeded
$cmd = sprintf('mysql -u%s -p%s -h%s -D%s -e"show processlist;"', MY_USER, MY_PASS, MY_HOST, MY_DB);
$cmd_ret = `$cmd | grep Sleep | awk '{print $1}'`;
$pids = array_filter(explode("\n", $cmd_ret), 'strlen');
#var_dump([$cmd_ret, $pids]);
foreach ($pids as $pid) {
$cmd = sprintf('mysql -u%s -p%s -h%s -D%s -e"kill %s"', MY_USER, MY_PASS, MY_HOST, MY_DB, $pid);
`$cmd`;
}
echo '3rd time' . PHP_EOL;
_check();
drop database if exists sample;
create database sample default character set utf8 collate utf8_general_ci;
create table sample.sample(
id int not null auto_increment,
name varchar(32) not null,
age int not null default 0,
primary key(id)
);
insert into sample.sample(name, age) values ('john', 20), ('jane', 21);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment