mysqli prepare statement error "MySQL server has gone away"

Question

I'm struggling to make the jump form Procedural to Object Orientated style so if my code is untidy or flawed please be nice - here I'm passing a couple of posts via jQuery to a class to update a record when the user checks a checkbox:

Here is the database connection

class db {
    
    private $host ;      
    private $username;
    private $password;
    private $dbname;
                
    protected function conn()
    {
        $this->host = "localhost";
        $this->username = "root";
        $this->password = "";
        $this->dbname = "mytest";
    
        $db = new mysqli($this->host, $this->username,  $this->password, $this->dbname);
        
        if($db->connect_errno > 0){
                die('Unable to connect to database [' . $db->connect_error . ']');
        }
        
        return $db;
    
    }
            
}

Here is the update class

class updOrders extends db {

public $pid;
public $proc;

public function __construct()
{
$this->pid = isset($_POST['pid']) ? $_POST['pid'] : 0;
$this->proc = isset($_POST['proc']) ? $_POST['proc'] : 1;
   
// $stmt = $this->conn()->query("UPDATE tblorderhdr SET completed = ".$this->proc." WHERE orderid = ".$this->pid);

$stmt = $this->conn()->prepare("UPDATE tblorderhdr SET completed = ? WHERE orderid = ?");
$stmt->bind_param('ii', $this->proc, $this->pid);

 $stmt->execute();

if($stmt->error)
    {
        $err = $stmt->error ;
    } else {
        $err = 'ok';
    }
    
/* close statement */
$stmt->close();

 echo json_encode($err);   

}
    
}

$test = new  updOrders;

When I comment out the prepare statement and run the query directly (commented out) it updates, when I try and run it as a prepare statement it returns an error "MySQL server has gone away".

Your error message suggests MySQL is timing out, although it seems to be local, I would confirm that PHP can communicate with the database. — Absorbing, Oct 11 '18 at 15:26
I can run a SELECT statement through PHP and the UPDATE query runs as a standard query with the variable written into the SQL string (as per the commented code) - only falls over with the prepare statement. — Tricky, Oct 11 '18 at 15:40

score 4 · Answer 1 · edited May 27 '21 at 01:36

4

I have looked at your code and I found this.

$stmt = $this->conn()->prepare("UPDATE tblorderhdr SET completed = ? WHERE orderid = ?");

I wrote nearly the same. But I saw you separated the connection from the prepare function.

$db = $this->conn();

$stmt = $db->prepare("UPDATE tblorderhdr SET completed = ? WHERE orderid = ?");

I don't know either why, but it works now.

edited May 27 '21 at 01:36

Nic3500

8,144
10
29
40

answered May 26 '21 at 17:44

László Szatmári

41
2

score 1 · Answer 2 · edited Sep 01 '21 at 10:02

1

It would appear that the problem lies within the connection to the database. Here is the (relevant bit of the) updated code:

$db = $this->conn();

$stmt = $db->prepare("UPDATE tblorderhdr SET completed = ? WHERE orderid = ?");
$stmt->bind_param('ii', $this->proc, $this->pid);

$stmt->execute();

edited Sep 01 '21 at 10:02

Dharman

30,962
25
85
135

answered Oct 12 '18 at 08:56

Tricky

43
6

score 0 · Answer 3 · answered Sep 01 '21 at 09:58

When you call $this->conn(), you are creating a new connection object of class mysqli. When no more variables point to the object, PHP will trigger its destructor. The destructor for mysqli will close the connection. This means that if you do not save the return value of this method into a variable, there will be no more references pointing to the object and the connection will be closed.

To fix this, simply save the object and reuse it. Don't connect each time.

$conn = $this->conn();
$stmt = $conn->prepare("UPDATE tblorderhdr SET completed = ? WHERE orderid = ?");

On a side note, creating a class like the one you have db is absolutely pointless. This class doesn't do anything useful. You haven't added any extra functionality to mysqli. You never need to save the credentials in properties. The whole class can be replaced with this code:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'password', 'test');
$mysqli->set_charset('utf8mb4'); // always set the charset

Then your classes will expect the mysqli object as a dependency.

class updOrders {

public $pid;
public $proc;

public function __construct(mysqli $conn) {
}

mysqli prepare statement error "MySQL server has gone away"

3 Answers3

Linked