MySQL Insert into fails and I get no errors back - any ideas what to look ?

[Frank H. Shaw]

I have been going crazy and need some good ideas how to debug this probem?

 

I am using mysql and php and the problem is i get to about half way through my inserts and the insert fail - I am using auto increment and a select that goes through the whole table and inside the while loop I hold some veriables in the results set.

 

I am able to insert a large number of records but then when I get to the middle of my process i seem to have my insert fail with no error messages the script jsut stops.

 

I am using 5.0.5 dev of mysql if that helps and php 5.31 i strip my insert down to just inserting the one field and the auto increment should happen automatictly so it is not my data that is the problem but the auto increment field its self.

 

Here is the table structure that i am inserting in to

 

$mytabletemp = "CREATE TABLE ALEPPOEXTRA (

ememid int unsigned NOT NULL AUTO_INCREMENT,

MemNumE VARCHAR(10),

First VARCHAR(50),

Middle VARCHAR(50),

Last VARCHAR(50),

Suffix VARCHAR(5),

Address1 VARCHAR(50),

Address2 VARCHAR(50),

City VARCHAR(35),

State VARCHAR(2),

Zip VARCHAR(10),

Birthdate VARCHAR(10),

YearBirth VARCHAR(10),

DayBirth VARCHAR(10),

MonthBirth VARCHAR(10),

Birth VARCHAR(10),

PRIMARY KEY (ememid) )";

 

I have tried the int unsigned and just int i do not get any further in my inserts.

 

The select is this

 

$mycommand = "SELECT M.Memkey, A.Memkey, A.AddressAutoID, M.MemNum, M.StatusType, M.FirstName, M.MiddleName, M.LastName, M.Suffix, A.Address1, A.Address2, A.City, A.State, A.Zip, M.DOB FROM ALEPPOMEMBERS M, ALEPPOADDRESS A WHERE ( M.Memkey = A.AddressAutoID ) AND ( NOT M.Memkey = A.Memkey ) AND NOT M.StatusType = 'C'";

 

$retquery1 = mysql_query( $mycommand, $mysql_connection );

if (mysql_errno()) {

  echo "Could not Query or Error Occured while : " . mysql_errno() . ": " . mysql_error(). "\n";

  exit(0); 

}

 

while ($rowouter = mysql_fetch_row($retquery1)) {

 

 

$queryextra = "INSERT INTO ALEPPOEXTRA SET MemNumE = '$rowouter[0]'";

 

  $retvalA = mysql_query($queryextra, $mysql_connection);

    if (mysql_errno()) { // - fhs 02/19/2010

      echo "Could not Insert Aleppo Members and Address EXTRA into Aleppo table: " . mysql_errno() . ": " . mysql_error(). "\n";

      exit(0); 

    }  // - fhs 02/19/2010

 

 

 

}

 

I have strip a lot of stuff out of the above to boil the problem down to very basic things.

 

The touble is the failure seems to happen on 2436 or there abouts - the failure seems to not be the same each time I run the query and insert but only a few records apart how ever.

 

Any one have some ideas as to what i might look at to resolve this strange problem.

 

TAHNKS

 

Frank H. Shaw

 

 

[PFMaBiSmAd]

Approximately how long does the script run before this problem occurs and does it occur each time after about the same amount of time?

[Frank H. Shaw]

"Approximately how long does the script run before this problem occurs and does it occur each time after about the same amount of time?"

 

The script takes a few mins to run about the same length of time what MySQL system veriables should i look at if  you think it is a time out issues.

 

How can I get the value of the MYSQL variables using php and then set the variables to a high value in my php script?

 

A Example wood help?

 

And what variables should I look at and why please explain?

 

THANKS

 

Frank H. Shaw

 

 

[Frank H. Shaw]

THANKS for the reply

 

I have check the inputs and as you can see the insert into now has only one thing:

 

$queryextra = "INSERT INTO ALEPPOEXTRA SET MemNumE = '$rowouter[0]'";

 

So the MemNumE value is not realy important because I have even hard coded the value to be a empty string and i still get the same problem so the problem seems to lay in the way the auto increment is happening when you insert the record into the table.

 

I have figured that much out my self ?

 

But what things can i do to figure out why this problem is happening I have dump the phpinfo() and see the mysql.connect_timeout is set to 60 does that have any thing to do with the insert or insert and query and should it be increased and can I do it in php script?

 

THANKS

 

Frank H. Shaw

 

 

[Frank H. Shaw]

Good News one thinks

 

I put the following at the top of my script and problem went away?

 

ini_set('error_reporting', E_ALL);

ini_set('display_errors', TRUE);

ini_set('max_execution_time','6000');

ini_set('memory_limit','128M');

ini_set('mysql.connect_timeout','6000');

ini_set('mysql.trace_mode','On');

 

I do not at the monment know which of the above setting or all of them fixed the problem but the script has gone on further now and I ahve to fix a few things.

 

THANKS

 

Frank H. Shaw

 

[fenway]

Why not do this in a single SQL statement?