Codeigniter's insert_batch() with thousands of inserts has missing records

Question

I’m using insert_batch() to mass insert 10000+ rows into a table in a database. I’m making some tests and I have noticed that sometimes all the 10.000+ rows are getting inserted correctly but in some occasions I miss 100+ rows in my table’s total count.

The field data in the records I have are ok as I'm using the same data for each of my tests and most of the times I have no problem. For example I tried 20 times to insert the same data into my database and at 19 times all rows will be inserted correctly but in this one time I will miss 100 or maybe more rows.

The function for the insert_batch() follows:

protected function save_sms_to_database() {
    //insert_Batch
    $datestring = "%Y-%m-%d %h:%m:%s";
    $time = time();
    $datetime = mdate($datestring, $time);

    $this->date_sent = $datetime;

    foreach ($this->destinations as $k => $v) {
        $sms_data[$k] = array(
            'campaign_id' => $this->campaign_id,
            'sender_id' => $this->from,
            'destination' => $v,
            'token' => md5(time() . 'smstoken' . rand(1, 99999999999)),
            'message' => $this->body,
            'unicode' => $this->unicode,
            'long' => $this->longsms,
            'credit_cost' => $this->eachMsgCreditCost,
            'date_sent' => $this->date_sent,
            'deleted' => 0,
            'status' => 1,
            'scheduled' => $this->scheduled,
        );
    }

    $this->ci->db->insert_batch('outgoingSMS', $sms_data);
    if ($this->ci->db->affected_rows() > 0) {
        // outgoingSMS data were successfully inserted      
        return TRUE;
    } else {
        log_message('error', $this->campaign_id.' :: Could not insert sms into database');
        log_message('error', $this->ci->db->_error_message());
        return FALSE; // sms was not inserted correctly
    }
}

How can I debug insert_batch() for such an occasion?

I have made some changes on the DB_active_rec.php to do some logging during the insert_batch and so far I can’t successfully reproduce the problem to see what is going wrong. But as far as the problem appeared 2-3 times at the beginning and I did not major changes to my logic to fix it, I can’t leave it like this as I don’t trust codeigniter’s insert_batch() function for production.

I'm also adding codeigniter's insert_batch() function:

    public function insert_batch($table = '', $set = NULL)
{
        $countz = 0;
    if ( ! is_null($set))
    {
        $this->set_insert_batch($set);
    }

    if (count($this->ar_set) == 0)
    {
        if ($this->db_debug)
        {
            //No valid data array.  Folds in cases where keys and values did not match up
            return $this->display_error('db_must_use_set');
        }
        return FALSE;
    }

    if ($table == '')
    {
        if ( ! isset($this->ar_from[0]))
        {
            if ($this->db_debug)
            {
                return $this->display_error('db_must_set_table');
            }
            return FALSE;
        }

        $table = $this->ar_from[0];
    }

    // Batch this baby
    for ($i = 0, $total = count($this->ar_set); $i < $total; $i = $i + 100)
    {

        $sql = $this->_insert_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), $this->ar_keys, array_slice($this->ar_set, $i, 100));

        //echo $sql;

        $this->query($sql);
                    $countz = $countz + $this->affected_rows();
    }

    $this->_reset_write();
            log_message('info', "Total inserts from batch:".$countz);

    return TRUE;
}

The last log_message() with the total inserts from batch also shows the problem as when I have less inserts than expected I get the non-expected number of inserts there as well.

I have to think something else for inserting thousands of rows into my database w/ or w/o codeigniter.

anyone has any clue for this kind of problem? Maybe it has something to do with the hard drive or the memory of the system during to lack of performance? It's an old PC with 1gb of ram.

EDIT: As requested I'm putting here an example INSERT statement with 9 rows that is being produced by codeigniter's insert_batch() function

INSERT INTO `outgoingSMS` (`campaign_id`, `credit_cost`, `date_sent`, `deleted`, `destination`, `long`, `message`, `scheduled`, `sender_id`, `status`, `token`, `unicode`) VALUES ('279',1,'2013-08-02 02:08:34',0,'14141415151515',0,'fd',0,'sotos',1,'4d270f6cc2fb32fb47f81e8e15412a36',0), ('279',1,'2013-08-02 02:08:34',0,'30697000000140',0,'fd',0,'sotos',1,'9d5a0572f5bb2807e33571c3cbf8bd09',0), ('279',1,'2013-08-02 02:08:34',0,'30697000000142',0,'fd',0,'sotos',1,'ab99174d88f7d19850fde010a1518854',0), ('279',1,'2013-08-02 02:08:34',0,'30697000000147',0,'fd',0,'sotos',1,'95c48b96397b21ddbe17ad8ed026221e',0), ('279',1,'2013-08-02 02:08:34',0,'306972233469',0,'fd',0,'sotos',1,'6c55bc3181be50d8a99f0ddba1e783bf',0), ('279',1,'2013-08-02 02:08:34',0,'306972233470',0,'fd',0,'sotos',1,'d9cae1cbe7eaecb9c0726dce5f872e1c',0), ('279',1,'2013-08-02 02:08:34',0,'306972233474',0,'fd',0,'sotos',1,'579c34fa7778ac2e329afe894339a43d',0), ('279',1,'2013-08-02 02:08:34',0,'306972233475',0,'fd',0,'sotos',1,'77d68c23422bb11558cf6fa9718b73d2',0), ('279',1,'2013-08-02 02:08:34',0,'30697444333',0,'fd',0,'sotos',1,'a7fd63b8b053b04bc9f83dcd4cf1df55',0)

That was a completed insert.

Answer 1

insert_batch() tries to avoid exactly your problem - trying to insert data larger than MySQL is configured to process at a time. I'm not sure if MySQL's option for that was max_allowed_packet or something else, but the problem with it is that it sets a limit in bytes and not a number of rows.

If you'll be editing DB_active_rec.php, mysql_driver.php or whatever appropriate ... try changing that 100 count in the for() loop. 50 should be a safer choice.

Other than that, FYI - affected_rows() won't return the correct value if you're inserting more than 100 rows via insert_batch(), so it's not reliable to use it as a success/error check. That's because insert_batch() inserts your data by 100 records at a time, while affected_rows() would only return data for the last query.