CODEWITHSUNDEEP
phpsqldatabasetimeout
Josh Sherick
Josh Sherick

Reputation: 2161

"MySQL server has gone away" error, I don't think it is a timeout

I am trying to import an SQL dump via PHP. I have the following code. 

    @mysql_connect("localhost", "root", "root") or die("Cannot connect to DB!");
    @mysql_select_db("great_database") or die("Cannot select DB!");
    $query = file_get_contents('defaultDatabasePartOne.sql', FILE_USE_INCLUDE_PATH);
    debug($query);
    $result = mysql_query($query);      
    debug($result);

    $error = mysql_error();
    debug($error);

I know it is correctly loading my file, because it debugs $query, which is the file. I removed comments from the file, but it still has line breaks, this wouldn't be a problem, would it?

I know that this error can have to do with a timeout, but I ran SET GLOBAL wait_timeout =99999999999 and it didn't change anything so i think that might not be it? I am not experienced in SQL how can I fix this?

Here is the example for @chris:

CREATE TABLE `access_tokens` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `token` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` int(11) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

Upvotes: 1

Views: 409

Answers (2)

Marc B
Marc B

Reputation: 360602

How big is that dump? You could be exceeding the max_allowed_packet limit (the max size a query can be going client->server).

As well, remember that a .sql file produced by mysqldump contains MULTIPLE queries. The PHP mysql drivers do not allow more than a single query statement to be executed via a single query() call. You'll never be able to load a dump this way without parseing the dump file into individual queries.

Upvotes: 2

user1934587390
user1934587390

Reputation: 461

Found this:

Heres the function i used to do this operation: Thought i'd post it just for those who need it in the future.

  • $url is the path to the sql file
  • $nowhost is dbhost
  • $nowdatabase is db
  • $nowuser is db user
  • $nowpass is db pass

Code:

function parse_mysql_dump($url,$nowhost,$nowdatabase,$nowuser,$nowpass){
    $link = mysql_connect($nowhost, $nowuser, $nowpass);
        if (!$link) {
           die('Not connected : ' . mysql_error());
        }

        // make foo the current db
        $db_selected = mysql_select_db($nowdatabase, $link);
        if (!$db_selected) {
           die ('Can\'t use foo : ' . mysql_error());
        }
   $file_content = file($url);
   foreach($file_content as $sql_line){
     if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
     //echo $sql_line . '<br>';
       mysql_query($sql_line);
     }
   }
  }

Upvotes: 2

Related Questions