0

I am running the following PHP script that is called using a standard $.ajax call via jquery. This PHP script inserts multiple records into a database via a Stored Procedure. This procedure is a simple INSERT INTO.

The PHP script is 

include_once '../Connections/define_connections.php';
$mysqli = new mysqli(DBHOST,DBUSER,DBPASS,DBNAME);
$mysqli->set_charset("utf8");

for ($q=0;$q<50000;$q++){
    $varma  = $mysqli->prepare("CALL insert_qq(?,?,?)");
    $varma->bind_param('dds',$v1,$v2,$v3);
    $v1 = '1';
    $v2 = '2';
    $v3 = '3';
    $varma->execute();
};
$mysqli->close();

The Mysql Procedure is

DELIMITER $$

CREATE DEFINER=`loopmap`@`%` PROCEDURE `insert_qq`(in latitude double,in longitude double,
in placename varchar(100))
BEGIN

insert into GEO_Structure values (default,default,default,default,latitude,longitude,
default,default,default,default,default,default,default,default,default,
default,default,default,default,default,default,default,default,default,default,default,
placename,default,default,default);

END

The problem is that this code works ok when the number of loops is circa 10K, but when it is above that number, for example 50K as shown here some problems occur. Each record is inserted twice, indicating possibly that the stored procedure is called twice per loop.

This yields in this case 100K records in my database. I can also verify that something is wrong because firebug returns a red error ( but it is not specifying anything, nor any code, nor any description on the error encountered).

Interestingly, as I mentioned, when the loop goes up to 10K each record is inserted correctly (the stored procedure is called just once per loop) and firebug does not throw any error (it returns success). It would appear that there is a critical number of loops where this starts failing.

Francisco Juretig
  • 33
  • 4
  • OMG what are you doing there? – steven May 28 '13 at 05:55
  • Can you make chunks of data and insert it one by one? Would be a solution, I guess – Nemoden May 28 '13 at 05:56
  • Is it necessary to prepare the query at each iteration? – Orangepill May 28 '13 at 05:59
  • shouldn't ->bind_param() be after the $v3 variable? you can but it depends on what you are doing and if it is neccessary... also agree with @Orangepill – Jurijs Nesterovs May 28 '13 at 06:00
  • Maybe [this good answer](http://stackoverflow.com/a/3952302/387573) will help you – crackmigg May 28 '13 at 06:06
  • you don't need prepare statements in loop, do that outside loop and only binding and executing should be inside. – Robert May 28 '13 at 06:08
  • Interesting problem. You can divide insertion into parts say you have to insert num records,then divide into parts of num / 10K par part. Then, call each part. This may help you. I also want to know how you are going to solve. Try to debug using these functions. http://php.net/manual/en/book.errorfunc.php and http://php.net/manual/en/mysqli.error.php and php.net/manual/en/function.set-error-handler.php – web2students.com May 28 '13 at 06:11
  • BTW. seems that $v1, $v2, $v3 should be set before calling bind_param() ;) – Kamilos May 28 '13 at 07:23
  • Could you post the code of your mysql procedure and the table. PHP seems ok but for the comments above. But that does not explain why your records are interted twice. – Frédéric Clausset May 28 '13 at 07:29
  • The Mysql procedure is – Francisco Juretig May 28 '13 at 14:39
  • Even if $v1,$v2,$v3 are set before, the problem keeps appearing. Also even if the prepare is moved out from the loop the problem keeps appearing. I cannot cut the problem into chunks of 10K because the true real code has around 100Million records. I could do a LOAD...INFILE but it is not an option for me because the user is not authorized to have FILE permission. (This is a toy example of a much more complex code where a 100Million record file is read and inserted accordingly) – Francisco Juretig May 28 '13 at 14:46

0 Answers0