How to solve General error: 2006 MySQL server has gone away

by

I would venture to say the problem is with wait_timeout. It is set to 30 seconds on my shared host and on my localhost is set for 28800.

I found that I can change it for the session, so you can issue the query: SET session wait_timeout=28800

UPDATE The OP determined that he also needed to change the variable interactive_timeout as well. This may or may not be needed for everyone.

The code below shows the setting before and after the change to verify that it has been changed.

So, set wait_timeout=28800 (and interactive_timeout = 28800) at the beginning of your query and see if it completes.

Remember to insert your own db credentials in place of DB_SERVER, DB_USER, DB_PASS, DB_NAME

UPDATE Also, if this does work, you want to be clear on what you are doing by setting wait_timeout higher. Setting it to 28800 is 8 hours and is a lot.

The following is from this site. It recommends setting wait_timeout to 300 – which I will try and report back with my results (after a few weeks).

wait_timeout variable represents the amount of time that MySQL will
wait before killing an idle connection. The default wait_timeout
variable is 28800 seconds, which is 8 hours. That’s a lot.

I’ve read in different forums/blogs that putting wait_timeout too low
(e.g. 30, 60, 90) can result in MySQL has gone away error messages. So
you’ll have to decide for your configuration.

<?php

$db = new db();

$results = $db->query("SHOW VARIABLES LIKE '%timeout%'", TRUE);
echo "<pre>";
var_dump($results);
echo "</pre>";

$results = $db->query("SET session wait_timeout=28800", FALSE);
// UPDATE - this is also needed
$results = $db->query("SET session interactive_timeout=28800", FALSE);

$results = $db->query("SHOW VARIABLES LIKE '%timeout%'", TRUE);
echo "<pre>";
var_dump($results);
echo "</pre>";


class db {

    public $mysqli;

    public function __construct() {
        $this->mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
        if (mysqli_connect_errno()) {
            exit();
        }
    }

    public function __destruct() {
        $this->disconnect();
        unset($this->mysqli);
    }

    public function disconnect() {
        $this->mysqli->close();
    }

    function query($q, $resultset) {

        /* create a prepared statement */
        if (!($stmt = $this->mysqli->prepare($q))) {
            echo("Sql Error: " . $q . ' Sql error #: ' . $this->mysqli->errno . ' - ' . $this->mysqli->error);
            return false;
        }

        /* execute query */
        $stmt->execute();

        if ($stmt->errno) {
            echo("Sql Error: " . $q . ' Sql error #: ' . $stmt->errno . ' - ' . $stmt->error);
            return false;
        }
        if ($resultset) {
            $result = $stmt->get_result();
            for ($set = array(); $row = $result->fetch_assoc();) {
            $set[] = $row;
            }
            $stmt->close();
            return $set;
        }
    }
}

More Related Contents:

  1. PDOException SQLSTATE[HY000] [2002] No such file or directory
  2. PHP PDO: charset, set names?
  3. How to get numeric types from MySQL using PDO?
  4. How can I use PDO to fetch a results array in PHP?
  5. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  6. Use bound parameter multiple times
  7. What is the difference between MySQL, MySQLi and PDO? [closed]
  8. MySQL check if a table exists without throwing an exception
  9. how safe are PDO prepared statements
  10. How to install pdo driver in php docker image?
  11. Replacing mysql_* functions with PDO and prepared statements
  12. MySQL retrieve variable from Stored Procedure in PHP PDO
  13. Understanding pdo mysql transactions
  14. Calling stored procedure with Out parameter using PDO
  15. When *not* to use prepared statements?
  16. How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
  17. Setting a connect timeout with PDO
  18. In PHP with PDO, how to check the final SQL parametrized query? [duplicate]
  19. What is the best way to insert multiple rows in PHP PDO MYSQL?
  20. PDO MySQL: Insert multiple rows in one query
  21. How to bind LIKE values using the PDO extension?
  22. PDO prepared statement fetch() returning double results
  23. Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  24. PHP 7 RC3: How to install missing MySQL PDO
  25. How to escape single-quote (apostrophe) in string using php
  26. PHP Laravel: No connection could be made because the target machine actively refused it
  27. PDO bindParam into one statement?
  28. Insert value list does not match column list: 1136 Column count doesn’t match value count
  29. What is the PDO equivalent of function mysql_real_escape_string?
  30. Installing PDO driver on MySQL Linux server

Leave a Comment