I’m sure a lot of developers have run into the dreaded “Mysql has gone away” issue, especially when dealing with long running scripts such as those reserved for background or cron jobs. This is caused by the connection between mysql and php being dropped. What exactly is the best way to prevent that from happening?
I currently use a custom CDbConnection class with a setActive method straight out of here:
This worked great and has stopped my MySQL gone away issue. Unfortunately, I’ve been running into a really random issue where after inserting a new record to the database via CActiveRecord, yii fails to set the primary key value properly. You end up with a pk value of 0. This sucks especially when you need that value to insert another record to a related table. It also sucked because I could never reproduce the issue on my local dev machine. Well, I finally got around to looking at the issue more deeply and was finally able to reproduce it on my machine. It seems like my custom CDbConnection::setActive() method can be the likely cause. When you run the CActiveRecord::save() method, yii prepares the necessary sql and executes it via PDO. Immediately after this, yii uses PDO::lastInsertId() to grab the latest inserted ID and populates your models PK attribute. What happens though if for whatever reason, the initial insert command takes more than a few seconds to complete? This triggers the mysql ping action of my custom setActive() method, which only waits for a 2 second difference between the current timestamp and the last active timestamp. I noticed that when you do a PDO insert query, followed by a PDO select query, then finally the PDO::lastInsertId(), you end up with a last insert id value of 0.
I can’t say for certain if this is what’s happening on our live servers where the issue randomly occurs but it’s been the only way I have been able to reproduce it. I guess I could increase that 2 second check but doesn’t that defeat the purpose of keeping the connection alive as much as possible?