Thursday, 8 August 2019

Connection lost during long time process to save records in database sqlite3 codeigniter

I have a heavy process which produce lots of records (>1000000) that should be save to the sqlite3 database in codeigniter.

The setting to connect to database in runtime is like this

 $config['hostname'] = '';
    $config['username'] = '';
    $config['password'] = '';
    $config['database'] = './application/database/' . $database_name;
    $config['dbdriver'] = 'sqlite3';
    $config['dbprefix'] = '';
    $config['pconnect'] = false;
    $config['db_debug'] = true;
    $config['autoinit'] = false;
    $config['cache_on'] = false;
    $config['cachedir'] = '';
    $config['stricton'] = false;
    $config['char_set'] = 'utf8';
    $config['dbcollat'] = 'utf8_general_ci';
    $config['save_queries'] = true;
    $config['failover'] = array();

my code in CI_Controller is

 $counter = 0;
    $this->load->model('db_model');
    for ($j = 0; $j < $MAX_Y; $j++) {
        for ($i = 0; $i < $MAX_X; $i++) {

            // some process .....
            // prepare $values

            if ($counter > 20000) {
                $this->db_model->insert($values, $config);
                $counter = 0;
                unset($values);
                $values = array();
            } else {
                $counter++;
            }

        }
    }
    if ($counter > 0) {
        $this->db_model->insert($values, $config);
    }

my code in db_model is

class Db_model extends CI_Model
{
 public function __construct()
    {
        parent::__construct();
        set_time_limit(10000);
    }

public function insert($data, $config)
{
    $DB = null;
    $DB = $this->load->database($config, TRUE);
    $DB->initialize();
//        $DB->busyTimeout(72000000);
        $DB->trans_start();
        $DB->insert_batch('tbl_name', $data);
        $DB->trans_complete();
        $DB->close();

    }

}

During the saving records into table in sqlite3 database suddenly it stop writing the rest of records which is remain and table does not have all records which should have.

I think some thing is wrong with connection time out, and i should change some thing related to connection time out. but i did not find a good answer for this issue after couple of weeks.



from Connection lost during long time process to save records in database sqlite3 codeigniter

No comments:

Post a Comment