[Emily Dickinson]

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).

[Gustavo]

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

[Emily Dickinson]

I'll try changing the MySQL host from "localhost" to "127.0.0.1" -- thanks for the tip!!

[Emily Dickinson]

Emily Dickinson, on 22 December 2010 - 02:38 AM, said:

In my config file, I changed the MySQL host from "localhost" to "127.0.0.1" -- and the error has gone away. Thanks!

[Gustavo]

You're welcome, Emily

[Emily Dickinson]

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!

[Emily Dickinson]

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?

[jeanluca]

Emily Dickinson, on 01 May 2011 - 01:06 AM, said:

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?

Did you try to increase

[mysqld]
....
wait_timeout = <huge number>
connect_timeout = <also a huge number>
...

in your my.cnf ?

cheers

[Maurizio Domba Cerin]

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

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

[Emily Dickinson]

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

[Gustavo]

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

[Emily Dickinson]

Thanks for the idea Gustavo. I'll give that some thought.
--Emily

[Blackleg Sanji]

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?

[spatan]

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=1024*1024*16;

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=1024*1024*16;


Hope this help someone.
Thanks

[Rahul Prasad]

I fixed it by adding

mysql.connect_timeout = -1

in

/var/php5/cli/php.ini

[Kapil Kaushik]

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()->db->setActive(false);
try {
echo "*******************************************\n";
echo "\ntrying to do a dummy command on the database\n";
echo "*******************************************\n";
Yii::app()->db->createCommand('select 1')->execute();
} catch (exception $e) {
echo "*******************************************\n";
echo "got exception -- ".$e->getMessage()."\n";
echo "*******************************************\n";
Yii::app()->db->setActive(false);
Yii::app()->db->setActive(true);

//try reconnecting again....if this fails, then we know that the mysql server won't work
Yii::app()->db->createCommand('select 1')->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.

[MaziTizeh]

Gustavo, on 21 December 2010 - 04:55 PM, said:

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

Great, Solved, but may i ask why

[iSystems]

Hello people.

We have same error too.
We did it by that way.

Our local server is XAMPP...

• Go to C:\xampp\mysql\bin
  
• Open my.ini
  
• find the row - max_allowed_packet = 1M
  
• change it like - max_allowed_packet = 10M

Best Regards www.iPage.am

[aknatn]

Gustavo, on 21 December 2010 - 04:55 PM, said:

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 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...