php Codeigniter 的 insert_batch() 有数千个插入，缺少记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18015852/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Codeigniter's insert_batch() with thousands of inserts has missing records
提问by sotoz
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.
我正在使用insert_batch()将 10000 多行批量插入数据库中的表中。我正在做一些测试，我注意到有时所有 10.000 多行都被正确插入，但在某些情况下，我错过了表格总数中的 100 多行。
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.
我拥有的记录中的字段数据没问题，因为我在每次测试中都使用相同的数据，而且大多数时候我都没有问题。例如，我尝试了 20 次将相同的数据插入到我的数据库中，并且在 19 次时所有行都将被正确插入，但在这一次我将错过 100 行或更多行。
The function for the insert_batch()follows:
功能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(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?
insert_batch()在这种情况下我该如何调试？
I have made some changes on the DB_active_rec.phpto 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.
我DB_active_rec.php在 insert_batch 期间做了一些更改以进行一些日志记录，到目前为止我无法成功重现问题以查看出了什么问题。但是就问题一开始出现2-3次并且我没有对我的逻辑进行重大更改来修复它而言，我不能就这样离开它，因为我不相信codeigniter的insert_batch()生产功能。
I'm also adding codeigniter's insert_batch() function:
我还添加了 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)
{
//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.
最后一个log_message()与批次中的总插入量也显示了问题，因为当我的插入量少于预期时，我也会在那里获得非预期数量的插入量。
I have to think something else for inserting thousands of rows into my database w/ or w/o codeigniter.
我必须考虑其他方法才能将数千行插入到我的数据库中（带或不带 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.
有人对这种问题有任何线索吗？可能与硬盘或系统内存在性能不足有关？这是一台带有 1GB 内存的旧电脑。
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 语句，其中包含由 codeigniter 的insert_batch()函数生成的 9 行
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.
那是一个完整的插入。
回答by Narf
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_packetor something else, but the problem with it is that it sets a limit in bytes and not a number of rows.
insert_batch()试图完全避免您的问题 - 尝试插入大于 MySQL 一次配置为处理的数据。我不确定 MySQL 对此的选项是max_allowed_packet还是其他的，但它的问题在于它设置了字节限制而不是行数。
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.
如果您要编辑 DB_active_rec.php、mysql_driver.php 或任何适当的...尝试更改for()循环中的 100 计数。50应该是一个更安全的选择。
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.
除此之外，仅供参考 -affected_rows()如果您通过 插入超过 100 行insert_batch()，则不会返回正确的值，因此将其用作成功/错误检查是不可靠的。那是因为insert_batch()一次按 100 条记录插入您的数据，而affected_rows()只会返回最后一次查询的数据。
回答by Anand agrawal
The solution of this problem is, you need to go in directory /system/database/and open file DB_query_builder.phpand update
这个问题的解决方法是，你需要进入目录/system/database/并打开文件DB_query_builder.php并更新
public function insert_batch($table, $set = NULL, $escape = NULL, $batch_size = 1000)
public function insert_batch($table, $set = NULL, $escape = NULL, $batch_size = 1000)
you can set the size of your requirement.
您可以设置您的要求的大小。
