Executing mysqli_stmt->execute() causes "MySQL server has gone away" error

Question

Okay. This is a weird one. First, here's the error:

Fatal error: Uncaught mysqli_sql_exception: MySQL server has gone away in database.php:84 Stack trace: #0 database.php(84): mysqli_stmt->execute()

According to other StackOverflow articles such as this and this, that error, MySQL server has gone away means that either:

• The mysqli object is being reused without being closed first (nope, definitely not)
• The connection timeout variable was too small on the MySQL server's end
• The maximum packet size variable is too small

However, I have set the timeout and the maximum packet size variable to their maximum values, and the query is simply selecting from an empty table. There's no reason why either of those should be a problem. I also verified from Python-- the server can be connected to and queries should be able to be executed. It even works fine from phpMyAdmin.

On the PHP documentation for mysqli_stmt::prepare, it says this regarding the error:

• I'm using mysqlnd on Linux, and it's not supposed to give this error when the statement is longer than max_allowed_packet
• I have already mentioned how I have set max_allowed_packet to the variable's maximum value.

If you would like for me to give more information such as my SQL server or PHP configuration, let me know what you need.

One article I read said to use mysqli->ping() to check how the connection's doing, and it appears to be fine until I call mysqli_stmt->execute().

I'm fairly certain it's a problem with my implementation-- I tried reinstalling the web server and the MySQL server, switching PHP versions, and I even tried switching hosts. But despite my attempts to fix this, I continue to get the error.

Here is the code:

<?php
    ini_set('mysql.connect_timeout', 3000);
    ini_set('default_socket_timeout', 3000);

    /* define the database class */
    class Database {
        public $host = 'localhost';
        public $name = '';
        public $user = '';
        public $pass = '';

        private $mysqli;

        /* constructor function, inits the database connection */
        function __construct($chost, $cname, $cuser, $cpass) {
            $this->host = $chost;
            $this->name = $cname;
            $this->user = $cuser;
            $this->pass = $cpass;

            mysqli_report(MYSQLI_REPORT_ALL);
            $this->mysqli = new mysqli($this->getHost(), $this->getUsername(), $this->getPassword(), $this->getName());
        }

        /* closes the connection to the database */
        function close() {
            return $this->getMySQLi()->close();
        }

        /* returns a query object for the given parameters */
        function query($query, $type='', ...$params) {
            $statement = $this->getMySQLi()->prepare($query);


            if(strlen($type) != 0) {
                // bind parameters to query
                $statement->bind_param($type, ...$params);
            }

            /*
             * stackoverflow readers: this the debug code
             * I mentioned to check the connection
             *
             */
            if ($this->getMySQLi()->ping()) {
                printf ("Our connection is ok!\n");
            } else {
                printf ("Error: %s\n", $this->getMySQLi()->error);
            }

            return new Query($statement);
        }

        /* getter functions */

        function getMySQLi() {
            return $this->mysqli;
        }

        function getHost() {
            return $this->host;
        }

        function getName() {
            return $this->name;
        }

        function getUsername() {
            return $this->user;
        }

        function getPassword() {
            return $this->pass;
        }
    }

    /* define the query class */
    class Query {
        private $statement;
        private $result;

        /* constructor, sets variables and stuff */
        function __construct($statement) {
            $this->statement = $statement;
        }

        /* executes the statement */
        function execute() {
            $status = $this->getStatement()->execute();
            $this->result = $this->getStatement()->get_result();

            return $status;
        }

        /* closes the statement */
        function close() {
            return $this->getStatement()->close();
        }

        /* returns the number of results */
        function countRows() {
            return $this->getResult()->num_rows;
        }

        /* getter functions */

        /* returns the statement object */
        function getStatement() {
            return $this->statement;
        }

        /* returns the result object */
        function getResult() {
            return $this->result;
        }

        function getRow() {
            return $this->getResult()->fetch_assoc();
        }

        /* returns the result in an array */
        function getRows() {
            $rows = array();
            while($row = $this->getRow()) {
                $rows[] = $row;
            }

            return $rows;
        }
    }
?>

So. My question is, is there a problem with my implementation? How can the problem be mitigated? Is it a problem with the SQL server or PHP?

Edit: Here's how I'm using the Database class (getConnection() simply returns a new Database instance)

function getUsers() {
    $query = getConnection()->query('SELECT * FROM `users`');
    $query->execute();
    return $query->getRows();
}

Answer 1

I think I now know what is causing the problem! I wish I'm right.

function getUsers() {
    // here you are creating a Database instance,
    // but you've left it in the air, because you just retrieved `query` from it.
    $query = getConnection()->query('SELECT * FROM `users`');
    // at this point, there are already NO Database instance,
    // because you have no reference of it.
    // thus what I've read about resources being garbage collected
    // will now apply here.
    $query->execute();
    // this will fail, indeed, Mysql has gone away!
    return $query->getRows();
}

You should, at least save the connection to another variable.

$conn = getConnection();
$query = $conn->query('SELECT * FROM `user`');
$query->execute();
return $query->getRows();

But as a proper solution you must keep a single connection alive through the whole script execution and use it for all queries.