Codeigniter Cron job which consume more time then insert to database

Question

I have a cron job which call the following codeigniter controller
But it is time costly
Which calculate and operate more operations (on average need 5 minutes)
Then insert values on database

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Mycron extends CI_Controller {

    public function cron_function()
    {
        // simulate time cost operations
        sleep(300); // 5 mint = 5 *60 = 300 sec

        $this->load->database();    // line1
        $this->db->reconnect();     // line2

        $this           -> db -> set    ( 'source_id',  '11');
        $this           -> db -> set    ( 'title',  'TTL');
        $query = $this  -> db -> insert ( 'my_table' );

        echo 'END ...'; 
    }
}

My issue
Without both lines line1/line2
When database try to connect after consumed time on operationsthe result will be

SQL Error: 2006: MySQL server has gone away

Then when try to apply the fix RE_CONNECT to database by using 2 lines
We face

A PHP Error was encountered
Severity: Warning
Message: Cannot modify header information - headers already sent by 
(output started at .../system/database/drivers/mysqli/mysqli_driver.php:392)
Filename: core/Common.php
Line Number: 568
Backtrace:

A PHP Error was encountered
Severity: Error
Message: Call to a member function real_escape_string() on boolean
Filename: mysqli/mysqli_driver.php
Line Number: 392
Backtrace:

check the `wait_timeout` variable in your `my.cnf' – Kisaragi Jan 04 '17 at 16:52 — Kisaragi, Jan 04 '17 at 16:52

score 0 · Answer 1 · edited May 23 '17 at 11:53

0

I would suggest creating a second method in cron controller responsible only for inserting data into database.

Then, after time costly operation is finished, you could post the generated data with, say, curl to the second method, thus going around MySQL timeout limitation.

edited May 23 '17 at 11:53

Community

1
1

answered Jan 05 '17 at 09:08

Vaviloff

16,282
6
48
56

score 0 · Answer 2 · answered Jan 05 '17 at 09:57

I disabled auto-loading of database library
Old code

$autoload['libraries'] = array('database');

to be

$autoload['libraries'] = array();

And load it manually before calling DB statement

$this->load->database();

So the database connection will open after time of costly operation will be finished

Then this time (of costly operation) will NOT considered as a database synchronous operation time
Opposite of auto-loading of database library which start timer when page loaded

also remove `$this->db->reconnect();` statement – Ahmed Nabil Jan 05 '17 at 09:58 — Ahmed Nabil, Jan 05 '17 at 09:58

score 0 · Answer 3 · answered Jun 28 '22 at 08:37

0

You must use insert_batch() which is way to faster than insert().

Check https://www.codeigniter.com/userguide3/database/query_builder.html?highlight=update_batch#inserting-data for detailed info.

answered Jun 28 '22 at 08:37

Indesh TH

1

Codeigniter Cron job which consume more time then insert to database

3 Answers3