PHP mysqli reconnect problem

Question 1

I am having trouble using the mysqli class in PHP and I haven't been able to find the answer anywhere.

In my script a class creates a mysqli connection that it uses throughout it's functions. Afterward, this script forks. The connection is used by the children as well, but I'm running into the problem of the connection being closed (MYSQL Server Has Gone Away) in the parent when the children die.

Before I switched to mysqli (was just using mysql) I simply called mysql_ping to assure that the db connection was there before performing the query in the parent process. Mysqli has a similar ping function BUT it doesn't actually reconnect if the connection is gone. I tried using the mysqli.reconnect=ON global setting with no luck (using php.ini and ini_set).

The php mysql_connect function allows you to grab an already-existing connection, so if I was using mysql instead of mysqli, I could simply reuse the connection in the child right after the process forked. BUT mysqli doesn't seem to have any such functionality...

The only thing I was able to do was call mysqli->ping() and if that returned false then reconnect to the database in the parent. This is terribly inefficient, and I would much rather figure out how to do it correctly with mysqli (and no need for manual reconnects) that having to change back to mysql..

Any suggestions?

Question 2

The doc for mysqli_ping() says that if you set the global option mysqli.reconnect to 1 (in your php.ini) then mysqli_ping() will reconnect when it detects the connection has gone away.

Update: Since I answered this question in 2009, PHP has mostly moved on to use the mysqlnd driver by default instead of libmysql. As mentioned in the comment below, mysqlnd does not support the mysqli_ping() function, and the PHP developers did this intentionally, according to their "Won't Fix" answer in https://bugs.php.net/bug.php?id=52561

So there appears to be no solution for automatic reconnect in PHP anymore.

Question 3

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

Question 4

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Question 5

Looking Back on this question now a year later I can see exactly what i did wrong. in the form i didn't give it any action, i left it at <form action="" method="post">. It should have been <form action="#" method="post">. basic stuff up by a beginner.

Question 6

To be sure you see all PHP errors, add this code on top of your script:

error_reporting(E_ALL);
ini_set('display_errors', 1);

You must correct your calls to mysqli_real_escape_string. According to the documentation, there must be two parameters, and the first parameter must be a MySQL link. In your case that link would be $mysqli.

Also, replace:

if($row==1){

with:

if($result->num_row==1){

You are misunderstanding what $result->num_rows is: it contains the TOTAL number of rows returned by the query whose result is stored in $result. So, it is useless to check the value of $result->num_rows inside the loop where you retrieve all records returned by the query.

I removed the constant MYSQLI_USE_RESULT from your query() because the documentation for mysqli_query says:
If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result().

New code:

<?php
    $mysqli = new mysqli('localhost', 'root', 'password', 'aiesec');

    /* check connection */
    if (mysqli_connect_errno()) {
        printf("Connect failed: %sn", mysqli_connect_error());
        exit();
    }

    // cleanup POST variables
    $myusername = mysqli_real_escape_string($mysqli, stripslashes(trim($_POST['myusername'])));
    $mypassword = mysqli_real_escape_string($mysqli, stripslashes(trim($_POST['mypassword'])));

    // If result matched $myusername and $mypassword, table row must be 1 row
    $sql = "SELECT * FROM members WHERE username='$myusername' and password='$mypassword'"; 
    $result = mysqli->query($sql);
     if($mysqli->errno<>0)
        die("Errormessage: %sn", $mysqli->error);
    echo $result->num_rows;
    if($result->num_rows==1){
        echo "correct username and pass";
        // Register $myusername, $mypassword and redirect to file "login_success.php"
       // session_register("myusername");
        //session_register("mypassword");
        //header("location:login_success.php");
    }
    else {
        echo "Wrong Username or Password";
    }
    mysqli_close();     
?>

PHP mysqli reconnect problem

Answers

php,mysqli,fork,reconnect