CODEWITHSUNDEEP
phpmysqlpdoyii2
rkm
rkm

Reputation: 3131

Yii2 MySQL server has gone away

I'm developing Yii2 application. There is a console script that executes quite long and it fails with error: MySQL server has gone away.

According to logs it throws an exception after 6-7 seconds without communication to the database (it does some job and if needed should update a table).

I added PDO timeout in db configuration:

'attributes' => [
    PDO::ATTR_TIMEOUT => 600,
],

Also I checked MySQL timeout variables, but they seem to be good:

mysql> show variables like '%timeout%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| connect_timeout            | 10     |
| delayed_insert_timeout     | 300    |
| innodb_lock_wait_timeout   | 50     |
| innodb_rollback_on_timeout | OFF    |
| interactive_timeout        | 259200 |
| net_read_timeout           | 30     |
| net_write_timeout          | 60     |
| slave_net_timeout          | 3600   |
| table_lock_wait_timeout    | 50     |
| wait_timeout               | 259200 |
+----------------------------+--------+
10 rows in set (0.00 sec)

I tried to reconnect in the controller like this:

Yii::$app->db->close();
Yii::$app->db->open();

But it didn't help. Do you have any ideas what I'm doing wrong?

Thanks

Upvotes: 4

Views: 8459

Answers (3)

akshaypjoshi
akshaypjoshi

Reputation: 1295

You need to handle it manually using try {...}catch(){...} statement.

try {
        \Yii::$app->db->createCommand("DO 1")->execute();
    } catch (Exception $e) {
        \Yii::$app->db->close();
        \Yii::$app->db->open();
    }

Upvotes: 2

Ruslan Novikov
Ruslan Novikov

Reputation: 1527

I solved by adding in the action

Yii::$app->db->createCommand('SET SESSION wait_timeout = 28800;')->execute();

Upvotes: 6

Maharjan
Maharjan

Reputation: 176

i solved by adding 

max_allowed_packet = 128M
wait_timeout=3600
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

in my.cnf of mysql

Upvotes: 2

Related Questions