Настройка MySQL для запросов в цикле

Я использую скрипт в PHP для вставки больших данных в цикле в MySQL
каждый цикл выбирает 10000 строк из одной таблицы и вставляет эти строки в другую таблицу

Но в соответствии с моей текущей конфигурацией, MySQL отключается после первого цикла запросов

означает, что он гаснет после вставки первых 10000 строк

Я использую Zend Server в Ubuntu и MySQL

я пытался изменить my.cnf и php.ini
но безуспешно

Спасибо заранее тем, кто предоставит решение.

<?php
include('db.php');
$conn = mysql_connect($SERVER, $USER, $PASSWORD) or die('cant connect to server'.mysql_error());
mysql_select_db($DATABASE);


//ps_orders
// truncate table ps_orders;
//select * from ps_customer order by id_customer desc limit 0,2;
//select * from ps_cart order by date_add desc limit 0,1
// to fecth cart from sss database
//where id_customer >56020"$conn1 = mysql_connect($SERVER,$NEW_USER,$NEW_PASSWORD);
mysql_select_db($DATABASE_NewDB,$conn1);
$sql="truncate table `ps_orders`";
if(mysql_query($sql,$conn1))
{
echo "table truncated";
}
else
{
mysql_errno();
}

$page =0;
$total_rows = 0 ;
while(true)
{
$limit1 = ($page*9600)+1;
$limit2 = ($page*9600)+9600;
$queryContactID="SELECT id_order, reference, id_shop_group, id_shop, id_carrier, id_lang, id_customer, id_cart, id_currency, id_address_delivery, id_address_invoice, current_state, secure_key, payment, conversion_rate, module, recyclable, gift, gift_message, shipping_number, total_discounts, total_discounts_tax_incl, total_discounts_tax_excl, total_paid, total_paid_tax_incl, total_paid_tax_excl, total_paid_real, total_products, total_products_wt, total_shipping, total_shipping_tax_incl, total_shipping_tax_excl, carrier_tax_rate, total_wrapping, total_wrapping_tax_incl, total_wrapping_tax_excl, invoice_number, delivery_number, invoice_date, delivery_date, valid, date_add, date_upd, courier_complete from $DATABASE.ps_orders limit $limit1,$limit2";
$result = mysql_query($queryContactID) or die('query fetch sss address id error'.mysql_error());
if(mysql_num_rows($result)>0){
$carrier_query_arr = array();
while($row = mysql_fetch_object($result)){
$id_order = $row->id_order;
$reference = $row->reference;
$id_shop_group = $row->id_shop_group;
$id_shop = $row->id_shop;
$id_carrier = $row->id_carrier;
$id_lang = $row->id_lang;
$id_customer = $row->id_customer;
$id_cart  =$row->id_cart;
$id_currency  =$row->id_currency;
$id_address_delivery  =$row->id_address_delivery;
$id_address_invoice  = $row->id_address_invoice;
$current_state =$row->current_state;
$secure_key =$row->secure_key ;
$payment =$row->payment;
$conversion_rate =$row->conversion_rate;
$module =$row->module;
$recyclable =$row->recyclable;
$gift =$row->gift;
$gift_message =$row->gift_message;
$shipping_number =$row->shipping_number;
$total_discounts =addslashes(trim($row->total_discounts));
$total_discounts_tax_incl =addslashes(trim($row->total_discounts_tax_incl));
$total_discounts_tax_excl =addslashes(trim($row->total_discounts_tax_excl));
$total_paid =addslashes(trim($row->total_paid));
$total_paid_tax_incl =addslashes(trim($row->total_paid_tax_incl));
$total_paid_tax_excl =addslashes(trim($row->total_paid_tax_excl));
$total_paid_real =addslashes(trim($row->total_paid_real));
$total_products =addslashes(trim($row->total_products));
$total_products_wt =addslashes(trim($row->total_products_wt));
$total_shipping =addslashes(trim($row->total_shipping));
$total_shipping_tax_incl =addslashes(trim($row->total_shipping_tax_incl));
$total_shipping_tax_excl =addslashes(trim($row->total_shipping_tax_excl));
$carrier_tax_rate =addslashes(trim($row->carrier_tax_rate));
$total_wrapping =addslashes(trim($row->total_wrapping));
$total_wrapping_tax_incl =addslashes(trim($row->total_wrapping_tax_incl));
$total_wrapping_tax_excl =addslashes(trim($row->total_wrapping_tax_excl));
$invoice_number =addslashes(trim($row->invoice_number));
$delivery_number =addslashes(trim($row->delivery_number));
$invoice_date =addslashes(trim($row->invoice_date));
$delivery_date =addslashes(trim($row->delivery_date));
$valid =addslashes(trim($row->valid));
$date_add =addslashes(trim($row->date_add));
$date_upd =addslashes(trim($row->date_upd));
$courier_complete_count = addslashes(trim($row->courier_complete));
$cart_query_arr[] = "('".$id_order."','".$reference."','".$id_shop_group."','".$id_shop."','".$id_carrier."','".$id_lang."','".$id_customer."','".$id_cart."','".$id_currency."','".$id_address_delivery."','".$id_address_invoice."','".$current_state."','".$secure_key."','".$payment."','".$conversion_rate."','".$module."','".$recyclable."','".$gift."','".$gift_message."','".$shipping_number."','".$total_discounts."','".$total_discounts_tax_incl."','".$total_discounts_tax_excl."','".$total_paid."','".$total_paid_tax_incl."','".$total_paid_tax_excl."','".$total_paid_real."','".$total_products."','".$total_products_wt."','".$total_shipping."','".$total_shipping_tax_incl."','".$total_shipping_tax_excl."','".$carrier_tax_rate."','".$total_wrapping."','".$total_wrapping_tax_incl."','".$total_wrapping_tax_excl."','".$invoice_number."','".$delivery_number."','".$invoice_date."','".$delivery_date."','".$valid."','".$date_add."','".$date_upd."')";
++$total_rows;
}
$cart_single_insert = "INSERT into $DATABASE_NewDB.ps_orders(id_order, reference, id_shop_group, id_shop, id_carrier, id_lang, id_customer, id_cart, id_currency, id_address_delivery, id_address_invoice, current_state, secure_key, payment, conversion_rate, module, recyclable, gift, gift_message, shipping_number, total_discounts, total_discounts_tax_incl, total_discounts_tax_excl, total_paid, total_paid_tax_incl, total_paid_tax_excl, total_paid_real, total_products, total_products_wt, total_shipping, total_shipping_tax_incl, total_shipping_tax_excl, carrier_tax_rate, total_wrapping, total_wrapping_tax_incl, total_wrapping_tax_excl, invoice_number, delivery_number, invoice_date, delivery_date, valid, date_add, date_upd) values ";
$email_value = implode(',',$cart_query_arr);
$cart_single_insert .= ' '.$email_value;
$cart_query_arr = array();
mysql_query($cart_single_insert,$conn1);
++$page;
}
else
{
echo "queries are done";
break;
}
}
// first while loop ends here

//    $i=0;


//    if(mysql_num_rows($result)>0){
//        $j=0;

//        $total_address = 0;

//        $carrier_query_arr = array();
//        /* Start of While 1 */
//        while($row = mysql_fetch_object($result)){

//     $id_order = $row->id_order;
//     $reference = $row->reference;
//     $id_shop_group = $row->id_shop_group;
//     $id_shop = $row->id_shop;
//     $id_carrier = $row->id_carrier;
//     $id_lang = $row->id_lang;
//     $id_customer = $row->id_customer;
//     $id_cart  =$row->id_cart;
//     $id_currency  =$row->id_currency;
//     $id_address_delivery  =$row->id_address_delivery;
//     $id_address_invoice  = $row->id_address_invoice;
//     $current_state =$row->current_state;
//     $secure_key =$row->secure_key ;
//     $payment =$row->payment;
//     $conversion_rate =$row->conversion_rate;
//     $module =$row->module;
//     $recyclable =$row->recyclable;
//     $gift =$row->gift;
//     $gift_message =$row->gift_message;
//     $shipping_number =$row->shipping_number;
//     $total_discounts =addslashes(trim($row->total_discounts));
//     $total_discounts_tax_incl =addslashes(trim($row->total_discounts_tax_incl));
//     $total_discounts_tax_excl =addslashes(trim($row->total_discounts_tax_excl));
//     $total_paid =addslashes(trim($row->total_paid));
//     $total_paid_tax_incl =addslashes(trim($row->total_paid_tax_incl));
//     $total_paid_tax_excl =addslashes(trim($row->total_paid_tax_excl));
//     $total_paid_real =addslashes(trim($row->total_paid_real));
//     $total_products =addslashes(trim($row->total_products));
//     $total_products_wt =addslashes(trim($row->total_products_wt));
//     $total_shipping =addslashes(trim($row->total_shipping));
//     $total_shipping_tax_incl =addslashes(trim($row->total_shipping_tax_incl));
//     $total_shipping_tax_excl =addslashes(trim($row->total_shipping_tax_excl));
//     $carrier_tax_rate =addslashes(trim($row->carrier_tax_rate));
//     $total_wrapping =addslashes(trim($row->total_wrapping));
//     $total_wrapping_tax_incl =addslashes(trim($row->total_wrapping_tax_incl));
//     $total_wrapping_tax_excl =addslashes(trim($row->total_wrapping_tax_excl));
//     $invoice_number =addslashes(trim($row->invoice_number));
//     $delivery_number =addslashes(trim($row->delivery_number));
//     $invoice_date =addslashes(trim($row->invoice_date));
//     $delivery_date =addslashes(trim($row->delivery_date));
//     $valid =addslashes(trim($row->valid));
//     $date_add =addslashes(trim($row->date_add));
//     $date_upd =addslashes(trim($row->date_upd));
//     $courier_complete_count = addslashes(trim($row->courier_complete));

//     $cart_query_arr[] = "('".$id_order."','".$reference."','".$id_shop_group."','".$id_shop."','".$id_carrier."','".$id_lang."','".$id_customer."','".$id_cart."','".$id_currency."','".$id_address_delivery."','".$id_address_invoice."','".$current_state."','".$secure_key."','".$payment."','".$conversion_rate."','".$module."','".$recyclable."','".$gift."','".$gift_message."','".$shipping_number."','".$total_discounts."','".$total_discounts_tax_incl."','".$total_discounts_tax_excl."','".$total_paid."','".$total_paid_tax_incl."','".$total_paid_tax_excl."','".$total_paid_real."','".$total_products."','".$total_products_wt."','".$total_shipping."','".$total_shipping_tax_incl."','".$total_shipping_tax_excl."','".$carrier_tax_rate."','".$total_wrapping."','".$total_wrapping_tax_incl."','".$total_wrapping_tax_excl."','".$invoice_number."','".$delivery_number."','".$invoice_date."','".$delivery_date."','".$valid."','".$date_add."','".$date_upd."')";

//     $j++;
//     ++$total_address;

//     if($total_address > 5000){
//                $total_address = 0;

//     $cart_single_insert = "INSERT into $DATABASE_NewDB.ps_orders(id_order, reference, id_shop_group, id_shop, id_carrier, id_lang, id_customer, id_cart, id_currency, id_address_delivery, id_address_invoice, current_state, secure_key, payment, conversion_rate, module, recyclable, gift, gift_message, shipping_number, total_discounts, total_discounts_tax_incl, total_discounts_tax_excl, total_paid, total_paid_tax_incl, total_paid_tax_excl, total_paid_real, total_products, total_products_wt, total_shipping, total_shipping_tax_incl, total_shipping_tax_excl, carrier_tax_rate, total_wrapping, total_wrapping_tax_incl, total_wrapping_tax_excl, invoice_number, delivery_number, invoice_date, delivery_date, valid, date_add, date_upd) values ";
//     $email_value = implode(',',$cart_query_arr);
//     $cart_single_insert .= ' '.$email_value;

//     $cart_query_arr = array();
//     mysql_query($cart_single_insert,$conn1);
// }
//            print "Total records ".$j;
//        }
//    }/* End of While (1) */
?>

что я пробовал до сих пор:
в php.ini у меня есть изменения max_execution_time на 60000

-1

Решение

Вы можете вставить несколько строк одновременно. Попробуйте вставить так:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Итерируйте свой цикл и создайте запрос, подобный этому, и вставляйте после каждых 2К или 4К.

Для получения дополнительной информации: http://dev.mysql.com/doc/refman/5.6/en/insert.html

0

Другие решения

Других решений пока нет …

По вопросам рекламы [email protected]