Lost connection to MySQL server during query exception

Question

I am getting the below error while executing the prepared statement

(2013) Lost connection to MySQL server during queryexception
I have checked almost all the questions posted on this topic before posting but could not find the answer. so please do not close.

I am new to PHP and MYSQL so please correct if I have made any mistake
MY Code:-

<?php
class sanitize_insert{
protected $prepared_stmt;
protected $db_sqli;
public function prepare_sanitized_insert($created_by)
{
    if(!is_integer($created_by))
    {
        throw new InvalidArgException("Invalid argument(s) type. Expected integer(s)"); //to be defined
    }
    
    
    
    $query = "insert into requests_v(user_id,property_id,request_type,description,to_user_id,created_on,last_update_date,created_by,last_updated_by) values (?,?,?,?,?,now(),now(),8,8);";
            if(!is_resource($this->db_sqli))
    {
        $this->db_sqli = mysqli_connect('host','user','password','dbname');
    }
    if(!$this->prepared_stmt = $this->dbh->prepare($query))
    {
        return false;   
    }
    $this->prepared_stmt->bind_param('iiisi', $user_id, $property_id, $req_type, $desc,$to_id);
    //$result = $this->db_sqli->execute($this->prepared_stmt);
    return true;
}

public function execute_insert()
{
    if(!is_object($this->prepared_stmt))
    {
        return false;
    }
    if(!is_resource($this->db_sqli))
    {
        $this->db_sqli = mysqli_connect('host','user','password','dbname');
    }
    $result = $this->prepared_stmt->execute();
    return $result;
}


}

When I execute the prepared statement inside the method 'prepare_sanitized_insert', it gets executed without any error but when I execute it inside the method "execute_insert" it fails with the error :-

(2013) Lost connection to MySQL server during query

var_dump of the prepared statement just before the execution
object(mysqli_stmt)#4 (10) { ["affected_rows"]=> int(0) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(5) ["field_count"]=> int(0) ["errno"]=> int(2013) ["error"]=> string(44) "Lost connection to MySQL server during query" ["error_list"]=> array(1) { [0]=> array(3) { ["errno"]=> int(2013) ["sqlstate"]=> string(5) "HY000" ["error"]=> string(44) "Lost connection to MySQL server during query" } } ["sqlstate"]=> string(5) "HY000" ["id"]=> int(1) }

Could someone please help?

Did you execute prepare_sanitized_insert() before execute_insert()? — 01e
Yes I executed prepare_sanitized_insert() before execute_insert(). — Saurabh Jain
Why are you using mysqli_connect to assign db_sqli in prepare_sanitized_insert(), and db_factory::get_db_sqli() in execute_insert()? Can you log/echo in that if statement to see if it's trying to reassign the variable? — aynber
Why do you have a mysqli_connect() call inside the prepare function, yet use get_db_sqli() in the execute call? Seems like you're using multiple DIFFERENT methods of connecting to the db. I doubt that a statement prepared on one mysql connection is usable via another separate connection. — Marc B
Sorry for the confusion!!! Earlier I was using a factory class to get the my_sqli object. I have edited the code please have a look. Also i am updating the var_dump of the prepared statement. — Saurabh Jain

moskito-x moskito-x · Accepted Answer · 2013-09-17T22:25:43

you mixed up object-oriented style and procedural style .

object-oriented style
Note the new

$this->db_sqli = new mysqli('host','user','password','dbname');

if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

$query = "insert into requests_v(user_id, ...) values (?,?,?,?,?,now(), ...)";

wrong

if(!$this->prepared_stmt = $this->dbh->prepare($query)) {

correct

if ($this->prepared_stmt = $this->db_sqli->prepare($query)) {

    $this->prepared_stmt->bind_param('iiisi', $user_id, $property_id, ..., ...);

Prozedural style (The procedural style was used in the question)
Note without new

$this->db_sqli = mysqli_connect('host','user','password','dbname');
...
$query = "insert into requests_v(user_id, ...) values (?,?,?,?,?,now(), ...)";

wrong

if (!$this->prepared_stmt = $this->db_sqli->prepare($query)) {

correct

if (!$this->prepared_stmt = mysqli_prepare($this->db_sqli, $query)) {

wrong

$this->prepared_stmt->bind_param('iiisi', $user_id, $property_id, ...,...);

correct

mysqli_stmt_bind_param($this->prepared_stmt,'iiisi',$user_id, ..., ...,...);

wrong

$result = $this->prepared_stmt->execute();

correct

$result = mysqli_stmt_execute($this->prepared_stmt);

You have to decide for one of the two object-oriented style or procedural style .

you could use a constructor (also mentioned @Saber Haj Rabiee)
object-oriented style

 class sanitize_insert{

   protected $prepared_stmt;
   protected $db_sqli;
   public    $OK = TRUE;

  public function __construct($host, $user, $pass, $db)
  { 
    $this->db_sqli = new mysqli($host, $user, $pass, $db); 
    if (mysqli_connect_errno()) {
      printf("Connect failed: %s\n", mysqli_connect_error());
      $this->OK = FALSE;
    }
  }

call it like

 $sanitizeclass = new sanitize_insert($host, $user, $pass, $db);
 if ($sanitizeclass->OK) {
  ....
 }

Lost connection to MySQL server during query exception

1 Answers