Codeigniter的insert_batch（）带有上千个插入，缺少记录 [英] Codeigniter's insert_batch() with thousands of inserts has missing records
问题描述
我使用 insert_batch（）在数据库表中批量插入10000行以上。我正在做一些测试，我注意到有时所有10.000+行正在正确插入，但在某些情况下，我错过100多行在我的表的总数。
记录中的字段数据我已经确定，因为我对每个测试使用相同的数据，大多数时候我没有问题。例如，我试过20次插入相同的数据到我的数据库，在19次，所有的行将被正确插入，但在这一次，我会错过100或可能更多的行。
insert_batch（）的功能如下：
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 ）。'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数据已成功插入
return TRUE;
} else {
log_message（'error'，$ this-> campaign_id。'::无法将sms插入数据库'）;
log_message（'error'，$ this-> ci-> db-> _error_message（））;
return FALSE; //没有正确插入短信
}
}
insert_batch（）这样的场合？
我已经对 DB_active_rec .php 在insert_batch期间做一些日志记录，到目前为止我无法成功地再现问题，看看是什么问题。但是，尽管问题出现了2-3次，在开始，我没有重大更改我的逻辑，以解决它，我不能离开这样，因为我不信任codeigniter的 insert_batch ）
我也在添加codeigniter的insert_batch（）函数：
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）
{
//无有效数据数组。在键和值不匹配的情况下折叠
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];
}
//批量这个宝贝
for（$ i = 0，$ total = count（$ this-> ar_set）; $ i< $ total; $ i $ $ i + 100）
{
$ sql = $ this-> _insert_batch（$ this-> _protect_identifiers（$ table，TRUE，NULL，FALSE） > 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'，从批处理插入总数：。$ countz）;
return TRUE;
}
最后 log_message（）与批次的总插入也显示问题，因为当我有更少的插入比预期，我得到的非预期数量的插入，以及。
我必须考虑另外一些东西，用于在数据库中插入几千行或者w / o代码符号。
<任何人都有这样的问题的任何线索？也许它与硬盘驱动器或系统的内存有关的缺乏性能？
编辑：根据请求，我在这里插入一个示例INSERT语句，其中有9行，由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， -02 02:08:34'，0，'30697000000140'，0，'fd'，0，'sotos'，1，'9d5a0572f5bb2807e33571c3cbf8bd09'，0），（'279'，1， ：08：34'，0，'30697000000142'，0，'fd'，0，'sotos'，1，'ab99174d88f7d19850fde010a1518854'，0），（'279'，1，'2013-08-02 02:08： 349，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）
/ p>
insert_batch（）尝试避免您的问题 - 大于MySQL的数据被配置为一次处理。我不知道MySQL的选项是 max_allowed_packet 还是别的什么，但它的问题是它设置了一个字节限制，而不是一个行数。 / p>
如果你将编辑DB_active_rec.php，mysql_driver.php或任何适当的...尝试改变 for（） / code> loop。
除此之外，FYI - affected_rows（）不会返回正确的值，如果通过 insert_batch（）插入超过100行，因此将其用作成功/错误检查是不可靠的。这是因为 insert_batch（）一次将您的数据插入100条记录，而 affected_rows（）最后一个查询。
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.
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.
这篇关于Codeigniter的insert_batch（）带有上千个插入，缺少记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
