PHP Codeigniter 3 MySQL server has gone away

Question

I am running a Cron Job for a PHP script in Codeigniter to convert uploaded videos. Everything executes without any problems, but once it gets to inserting the data into the database I receive this error:

Error Number: 2006 MySQL server has gone away

The process basically converts the video, if that was successful create the thumbnail and poster from the video and then insert the video data into the database. I have tried adding $this->db->reconnect(); in my model function. Here is the function:

function add_video($data)
{
  //BECASUE THE CONVERSION TAKES SO LONG WE NEED TO RECONNECT TO THE DATABASE AFTER EACH EXEC
  $this->db->reconnect(); 

  $this->db->insert('video_uploads', $data);
}

This did not work I still recieved the error. If I add $this->db->reconnect(); after each exec I receive these errors:

Message: mysql_ping() expects parameter 1 to be resource, boolean given

Message: mysql_real_escape_string() expects parameter 2 to be resource, boolean given

Just as an FYI I am using FFMPEG to convert my videos.

How can I keep the connection alive after all of the executions have finished so I can insert the data into the database?

EDIT: here is what Codeigniter's $this->db->reconnect(); functions looks like:

public function reconnect()
{
    if (mysql_ping($this->conn_id) === FALSE)
    {
        $this->conn_id = FALSE;
    }
}

Check this [link](http://stackoverflow.com/questions/1884111/sudden-mysql-server-has-gone-away-error-in-php-site) — Saty, Jul 24 '15 at 13:18
If the conversion is taking so long then are you able to avoid connecting until after the video conversion and then perform the insert? If not then maybe you can manually issue a disconnect, convert the video, fresh connect, and perform the insert. — MonkeyZeus, Jul 24 '15 at 13:46
Thanks @Saty I added `$this->load->database();` before `$this->db->reconnect();` this seems to be working. If you want to write that as the answer I will accept it. — iamthestreets, Jul 24 '15 at 14:24

score 2 · Accepted Answer · edited Mar 05 '20 at 23:45

2

To avoid this error you can write

$this->load->database();

before you call

$this->db->reconnect();

edited Mar 05 '20 at 23:45

Ade

2,961
4
30
47

answered Jul 24 '15 at 14:58

Saty

22,443
7
33
51

1

please correct your answer first line should be $this->load->database(); – Shehzad Bilal Sep 05 '16 at 13:51
@Saty - Where do we call this function $this->db->database(); before you call $this->db->reconnect(); i couldn't find in model or control – Tariq Husain Jun 13 '18 at 08:05
@ShehzadBilal can you look at my comment – Tariq Husain Jun 13 '18 at 08:06
I have fixed that line above – Ade Mar 05 '20 at 23:46

score 1 · Answer 2 · answered May 20 '17 at 05:20

For me it was a setting in the database file where I migrated servers and hadn't setup mysql completely. In mysql I use ssl to connect to the server.

Once I commented out the following all worked well.

$db['default']['options'] =  array(
    PDO::MYSQL_ATTR_SSL_KEY  => $_SERVER['DOCUMENT_ROOT'].'/application/third_party/client-key.p$
    PDO::MYSQL_ATTR_SSL_CERT => $_SERVER['DOCUMENT_ROOT'].'/application/third_party/client-cert.$
    PDO::MYSQL_ATTR_SSL_CA   => $_SERVER['DOCUMENT_ROOT'].'/application/third_party/ca-cert.pem'
  );

I started by disabling libraries and other autoloaded files until I deduced that it was not a model or controller that was causing the issue. once I disabled the database library I didnt receive the 500 server error or the mysql error. After checking my connection settings I realized what was causing the issue.

That was the problem for me too. – sixty-nine May 17 '19 at 09:38 — sixty-nine, May 17 '19 at 09:38

score 1 · Answer 3 · answered May 26 '20 at 14:40

In some cases $this->db->reconnect(); throwing an error. I'm not sure why its going to an error. I'll update the answer when I know about it. But for now to resolve that kind of issue you can actually do below:

Replace:

$this->db->reconnect();

With:

$this->db->close();
$this->db->initialize();

score 0 · Answer 4 · edited May 23 '17 at 11:55

0

It may be easier to check if the connection and re-establish it if needed.

See PHP:mysqli_ping for info on that

For reference

edited May 23 '17 at 11:55

Community

1
1

answered Jul 24 '15 at 13:19

Vinie

2,983
1
18
29

I believe Codeigniter is already doing this. I edited my post at the bottom with what the `$this->db->reconnect();` function is. – iamthestreets Jul 24 '15 at 13:39

score 0 · Answer 5 · answered Nov 07 '15 at 11:42

0

I was having a similar issue for a different reason - I was hammering the database hard with > 12,000 select/updates. the mysql server just gave up and 'went on holiday'

anyway, a little time off is all it needed, in the loop i added:

if ($this->db->conn_id->ping() === FALSE)
{
    sleep(1);
    $this->db->reconnect();
}

answered Nov 07 '15 at 11:42

pgee70

3,707
4
35
41

1

What does the sleep do here? The reconnect() itself does not resolve the issue, until the sleep is added. The conn_id->ping() is not in the Codeigniter docs. Is this part of the DB_driver? – imparante Jan 21 '18 at 23:13

PHP Codeigniter 3 MySQL server has gone away

5 Answers5