Codeigniter insert_batch () with thousands of inserts has missing entries

Question

Im using insert_batch() to bulk input 10,000+ rows into a table in the database. I do some tests and I noticed that sometimes all 10.000+ rows are inserted correctly, but in some cases I skip over 100 rows in the total number of tables.

The field data in the records that I have is fine, since I use the same data for each of my tests, and most of the time I have no problems. For example, I tried to insert the same data 20 times into my database and 19 times all rows will be inserted correctly, but during this time I will miss 100 or possibly more rows.

The function for 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 a case?

I made some changes to DB_active_rec.php to do some logging during insert_batch, and so far I cannot successfully reproduce the problem to see what is going wrong. But since the problem arose 2-3 times at first, and I didn’t make major changes in my logic to fix it, I can’t leave it the way I don’t trust the codeigniters insert_batch() function for production.

I also add the codeigniter 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 full inserts from the package also shows the problem, because when I have fewer attachments than expected, I also get the unintended number of inserts.

I need to think about something else to insert thousands of rows into my database without entering any code.

Does anyone have a clue to solving this problem? Maybe this is due to the hard drive or system memory during a lack of performance? This is an old PC with 1 GB of RAM.

EDIT: As requested, I put here an example 9-line INSERT statement that is created using the codeigniter 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) 

It was a filled in box.

Answer 1

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

If you are editing DB_active_rec.php, mysql_driver.php or whatever works ... try changing 100 samples in a for() loop. 50 should be a safer choice.

In addition, FYI - affected_rows() will not return the correct value if you insert more than 100 lines through insert_batch() , so it is not reliable to use it as a success / error check. This is because insert_batch() inserts your data 100 records at a time, and affected_rows() only returns data for the last request.

Answer 2

The solution to this problem: you need to go to the /system/database/ directory and open the DB_query_builder.php file and update

public function insert_batch($table, $set = NULL, $escape = NULL, $batch_size = 1000)

You can set the size of your requirement.