Error: MySQL server has gone away

I’m getting a MySQL error about once in every 10,000 times of using my Yii application. Looking at the error, it seems to me that if my table (“game”) is not available, I would get the exception EVERY time the app is run.

Ideas? Here’s the error (newlines added for readability)





[error] [system.db.CDbCommand] Error in querying

SQL: SELECT * FROM `game` `t` WHERE `t`.`app_url`=:yp0 LIMIT 1


2010/12/21 20:10:06 [error] [exception.CDbException] exception

'CDbException' with message 'CDbCommand failed to execute the SQL

statement: SQLSTATE[HY000]: General error: 2006 MySQL server has gone

away' in /home/tim/src/yii/framework/db/CDbCommand.php:387


Stack trace:

#0 /home/tim/src/yii/framework/db/CDbCommand.php(291):

CDbCommand->queryInternal('fetch', 2, Array)


#1 /home/tim/src/yii/framework/db/ar/CActiveRecord.php(1231):

CDbCommand->queryRow()


#2 /home/tim/src/yii/framework/db/ar/CActiveRecord.php(1366):

CActiveRecord->query(Object(CDbCriteria))


#3 etc. (stack trace of my code).




Try to change its host from "localhost" to "127.0.0.1"

Dont ask me why but it works

Its a problem not with Yii’s architeture but with PDO when using it on a persistent connection with a mysql server

I’ll try changing the MySQL host from “localhost” to “127.0.0.1” – thanks for the tip!!

You’re welcome, Emily

Spoke too soon.

The error continues–though it’s not as frequent.

It happens both for large and for small tables. Occurs about once in 20,000 page loads. There’s no indication that it occurs during peak times for the server; seems to happen at any time of day.

Anyone have a clue about the mysterious "MySQL server has gone away" problem? Free virtual beer for the winner!

I still get this error. I tried installing the pcntl library as suggested in another thread on this forum. Didn’t work.

The techs at my data center spent most of today trying to figure out the issue, and in the end, concluded this:

“It’s possible that the db layer in your app is writing the query to the mysql socket incorrectly.”

Can anyone comment on this?

:mellow:

Did you try to increase




[mysqld]

....

wait_timeout = <huge number>

connect_timeout = <also a huge number>

...



in your my.cnf ?

cheers

There are many possible reasons for this error, check the docs - http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

The main point is that the connection timeout expires and you get that error…

Thanks for the replies guys! I’ll talk this over with my techs.

:mellow:

What I suggest is to instead of look for the error, which will take a long time and you might never find out, is to handle the error differently.

You probably already has something like this in your components :




'errorHandler'=>array(

   		'errorAction' => '//error/handler'

),

then in the action that handles the error find out somehow that the error is the "mysql server has gone away" error and redirect the user to the page he/she was supposed to go

Thanks for the idea Gustavo. I’ll give that some thought.

–Emily

I am also encountering this problem. My console application that is supposed to run always gets killed due to this error. I try to respawn the console application like below:




class ListenerCommand extends CConsoleCommand {

	public function run($args) {

		try {

                        // Running this method that gets data from the database is where the error occurs

			SettingsModel::getAppSettings(1);


                        // Code loop for listening for xmpp messages

			while(true) {

			    ... 

			}

		} catch (Exception $e) {

			print date() . ": Exception encountered: " . $e->getMessage() . "\n";

			print date() . ": Respawning listener...\n";

                        // Try to respawn the console application.

			$this->run($args);

		}

	}

}



The SettingsModel::getAppSettings() method is like this:




public static function getAppSettings($id) {

        return self::model()->findByPk($id);

}



However, the console app just keeps respawning and the "MySQL server has gone away" error occurs until the console app is completely killed by the php out of memory error.

Is there a way to properly re-establish the connection of the console app to the mysql server?

Hello all. i strongly believe that someone out there might be facing the same problem "MYSQL as gone away."

In my own case, i was developing a yii application and in one of my controllers, i have a function that stores zip file in to MYSQL. In my column, ‘file’, that DATA_TYPE = BLOB.

However, i noticed that if i upload any file below 1MB, uploading and storing my zip file in database would be successful but once its more than 1MB, i get "MYSQL as gone away.". This error has nothing to do with YII framework but MYSQL max_allowed_packet.

Below are the solutions i adopted and worked.

Using PHPMYADMIN -

  • Connect to your phpmyadmin database from the browser.

  • select and open phpmyadmin from the list of available database

  • Run this query - SET GLOBAL max_allowed_packet=1024102416;

If you are using MYSQL Administrator, follow the below steps:

  • locate Mysql my.ini file : C:\Program Files\MySQL\MySQL Server 4.1\my.ini"

  • under [mysqld] set this variable

max_allowed_packet=1024102416;

Hope this help someone.

Thanks

I fixed it by adding


mysql.connect_timeout = -1

in


/var/php5/cli/php.ini

In case this error is in a "console application", it could be because of a persistent connection left open by the app. I was getting this error with my gearman workers, wherein after exactly 8 hours the MYSQL server would go away and I was left stranded. The solution that worked for me was to extend all the workers with a base class, and define the following code in the base class:

protected function init() {

    //run a default command to check for the database connection 


    //if it fails then reconnect to the database


    Yii::app()-&gt;db-&gt;setActive(false);


    try {


        echo &quot;*******************************************&#092;n&quot;;


        echo &quot;&#092;ntrying to do a dummy command on the database&#092;n&quot;;


        echo &quot;*******************************************&#092;n&quot;;


        Yii::app()-&gt;db-&gt;createCommand('select 1')-&gt;execute();


    } catch (exception &#036;e) {


        echo &quot;*******************************************&#092;n&quot;;


        echo &quot;got exception -- &quot;.&#036;e-&gt;getMessage().&quot;&#092;n&quot;;


        echo &quot;*******************************************&#092;n&quot;;


        Yii::app()-&gt;db-&gt;setActive(false);


        Yii::app()-&gt;db-&gt;setActive(true);





        //try reconnecting again....if this fails, then we know that the mysql server won't work


        Yii::app()-&gt;db-&gt;createCommand('select 1')-&gt;execute();


    }


}

and call $this->init() at the beginning of each worker function. This way whenever the worker gets a new job, you would re-establish a new MYSQL connection, and eliminate the possibility of mysql connection timeout.

Great, Solved, but may i ask why :unsure:

Hello people.

We have same error too.

We did it by that way.

Our local server is XAMPP

[list=1]

[*]Go to C:\xampp\mysql\bin

[*]Open my.ini

[*]find the row - max_allowed_packet = 1M

[*]change it like - max_allowed_packet = 10M

[/list]

Best Regards www.iPage.am

I was having trouble with uploading on GoDaddy and processing several thousand XML files in a ZIP. By the time it got to the few files it needed to save it would throw an obscure error similar to: the table *** for active record class cannot be found in the database

Ran across this post and tried changing my DB to 127.0.0.1 and it worked. Thanks for posting, not sure how it is related but I am sure there is something going on…