Yii Framework Forum: Error: MySQL server has gone away - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Error: MySQL server has gone away Rate Topic: -----

#1 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 21 December 2010 - 03:17 PM

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


0

#2 User is offline   Gustavo 

  • Master Member
  • Yii
  • Group: Moderators
  • Posts: 916
  • Joined: 27-July 10
  • Location:Curitiba - Brasil

Posted 21 December 2010 - 04:55 PM

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
--
Extensions:
translate modue - module to handle translations
multiActiveRecord - db selection in models
redisCache - redis cache component
mpCpanel - interact with cpanel api
mUploadify - use uploadify uploader in your application

Gustavo Salomé Silva
1

#3 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 22 December 2010 - 02:38 AM

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

#4 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 22 December 2010 - 02:51 PM

View PostEmily 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!

0

#5 User is offline   Gustavo 

  • Master Member
  • Yii
  • Group: Moderators
  • Posts: 916
  • Joined: 27-July 10
  • Location:Curitiba - Brasil

Posted 22 December 2010 - 02:52 PM

You're welcome, Emily
--
Extensions:
translate modue - module to handle translations
multiActiveRecord - db selection in models
redisCache - redis cache component
mpCpanel - interact with cpanel api
mUploadify - use uploadify uploader in your application

Gustavo Salomé Silva
0

#6 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 24 December 2010 - 12:01 PM

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!
0

#7 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 01 May 2011 - 01:06 AM

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:
0

#8 User is offline   jeanluca 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 59
  • Joined: 22-January 11
  • Location:Bunnik, Netherlands

Posted 01 May 2011 - 02:53 AM

View PostEmily 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?
:mellow:

Did you try to increase

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

in your my.cnf ?

cheers
0

#9 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,354
  • Joined: 12-October 09
  • Location:Croatia

Posted 01 May 2011 - 03:16 AM

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...
Find more about me.... btw. Do you know your WAN IP?
0

#10 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 01 May 2011 - 11:20 AM

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

#11 User is offline   Gustavo 

  • Master Member
  • Yii
  • Group: Moderators
  • Posts: 916
  • Joined: 27-July 10
  • Location:Curitiba - Brasil

Posted 01 May 2011 - 05:53 PM

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
--
Extensions:
translate modue - module to handle translations
multiActiveRecord - db selection in models
redisCache - redis cache component
mpCpanel - interact with cpanel api
mUploadify - use uploadify uploader in your application

Gustavo Salomé Silva
1

#12 User is offline   Emily Dickinson 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 201
  • Joined: 17-September 10
  • Location:Albuquerque, NM

Posted 01 May 2011 - 10:29 PM

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

#13 User is offline   Blackleg Sanji 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 26-March 12

Posted 09 May 2012 - 02:30 AM

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?
0

#14 User is offline   spatan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 05-December 11

Posted 23 April 2013 - 04:13 AM

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
0

#15 User is offline   Rahul Prasad 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 31-August 12

Posted 29 May 2013 - 02:10 AM

I fixed it by adding
mysql.connect_timeout = -1
in
/var/php5/cli/php.ini

0

#16 User is offline   Kapil Kaushik 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 11-July 12

Posted 21 June 2013 - 01:21 PM

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

#17 User is offline   MaziTizeh 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 61
  • Joined: 12-June 12
  • Location:U.A.E

Posted 18 October 2013 - 02:31 PM

View PostGustavo, 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 :unsure:
0

#18 User is offline   www.iPage.am 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 19-July 14

Posted 20 July 2014 - 01:52 AM

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
0

#19 User is offline   aknatn 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 28-January 11

Posted 09 November 2014 - 02:03 AM

View PostGustavo, 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...
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users