Looping "mysql_query" only works for small number of loops?

Question

The code below is a portion of a php file I use to update a mySQLdatabase.

I can see in FF Firebug console that the POST data is all correct in the form that POSTs to this php file.

The problem is that if $i is greater that 3 I get the following error:

The connection was reset
The connection to the server was reset while the page was loading."

Does this indicate that I have to write a custom php.ini? If so, what values do I need to increase?

I realize my looping mysql_query code may also be incorrect, but it does work with $i values of 1 to 3. Could someone suggest the correct coding if this is the case?

        for ($i=1; $i<=$rr_years; $i++){
            $idrr = GetSQLValueString($_POST['id' . $i], "int");
            $associated_horse = GetSQLValueString($_POST['associated_horse' . $i], "int");
            $year = GetSQLValueString($_POST['year' . $i], "text");
            $age = GetSQLValueString($_POST['age' . $i], "int");
            $starts = GetSQLValueString($_POST['starts' . $i], "int");
            $first = GetSQLValueString($_POST['first' . $i], "int");
            $first_sw = GetSQLValueString($_POST['first_sw' . $i], "int");
            $second = GetSQLValueString($_POST['second' . $i], "int");
            $second_sp = GetSQLValueString($_POST['second_sp' . $i], "int");
            $third = GetSQLValueString($_POST['third' . $i], "int");
            $third_sp = GetSQLValueString($_POST['third_sp' . $i], "int");
            $age_notes = GetSQLValueString($_POST['age_notes' . $i], "text");
            $age_text = GetSQLValueString($_POST['age_text' . $i], "text");
            $earned = GetSQLValueString($_POST['earned' . $i], "text");


        mysql_select_db($database_HDAdave, $HDAdave);
        mysql_query("UPDATE race_records SET
            associated_horse = $associated_horse, 
            year = $year, 
            age = $age, 
            starts = $starts, 
            first = $first, 
            first_sw = $first_sw, 
            second = $second, 
            second_sp = $second_sp, 
            third = $third, 
            third_sp = $third_sp, 
            age_notes = $age_notes,
            age_text = $age_text,
            earned = $earned


            WHERE rr_id = $idrr", $HDAdave) or die(mysql_error());

        }

Thanks for any help you can offer.

Well, how long (in seconds) does one iteration take? If it takes too long you might run in a timeout defined by your webserver. — vstm, Dec 22 '11 at 06:27
Maybe one second. Date is small amount of text (mostly less than 200 characters). — user1028866, Dec 22 '11 at 06:32
I've always seen that function associated to DW; don't know where you took it, but if you ask DW's help to query the database it creates a code snippet containing that function. It should actually be a Macromedia custom code. Anyway, see my answer — Damien Pirsy, Dec 22 '11 at 06:45
I've met this exact same problem with Firebird. My code worked with MySQL/MySQLi/PDO MySQL, but the deprecated mysql_* functions and problems with its PDO made me change to Firebird. With PDO Firebird, the exact same code didn't go beyond 2~5 FOR iteractions (randomly), showing the "connection was reset" error (instead of throwing some Apache/PHP/Firebird error). I tried every suggestion, reconfigured everything, restarted the machine more than I'd like, watched the log files and changed my code until it broke. Finally, I quit and changed to PostgreSQL and the original code worked perfectly! — Cyberknight, Jan 11 '15 at 04:41

score 0 · Answer 1 · answered Dec 22 '11 at 06:31

0

The error text The connection to the server was reset while the page was loading. tells me that the error is between your web server and your client (browser), not between the webserver and the DB.

Have you looked at the DB and see if the records are properly updated?

As possible solutions:

increase php's max_execution_time
increase php's max_input_time
if the above are not working, split the insert on the client side with javascript into smaller chunks and send them individually

Also, an InnoDB storage engine might help you here, because it has row-level locking and it might decrease the execution at DB level.

Also, look in apache's error log and see if there are other errors there.

answered Dec 22 '11 at 06:31

Tudor Constantin

26,330
7
49
72

How do I inspect the apache's error logs? This is on a remote server. – user1028866 Dec 22 '11 at 07:20
that depends on your hosting provider - do you have access with ssh? if not, they should provide access to it through the interface – Tudor Constantin Dec 22 '11 at 07:35
I'll have to educate myself on this and look at what's available to me. Thanks for your help. I'll re-comment if I find something going down this avenue. – user1028866 Dec 22 '11 at 07:46
max_input_time is set to -1, max_execution_time is set to 30. Connection error comes up in about 15 sec. – user1028866 Dec 22 '11 at 18:35

score 0 · Accepted Answer · edited May 23 '17 at 11:48

0

This is more of personal taste maybe rather than real performance issue, but GetSQLValueString() (for those wondering, it's a Macromedia DW's custom function generated when using Dreamweaver's help for database. In this SO question it's shown and explained) sometimes is "too much", and a simple mysql_real_escape_string() or a check for value being an INT is enough.

Also, I'd place the connection and the selection of the database outside the loop. Additionally, but it's not needed usually, you can free mysql results after each passage:

// connect to DB here.
mysql_select_db($database_HDAdave, $HDAdave);

for ($i=1; $i<=$rr_years; $i++){
            $idrr = intval($_POST['id' . $i]);
            $associated_horse = intval($_POST['associated_horse' . $i]);
            $year = mysql_real_escape_string($_POST['year' . $i]);
            $age = intval($_POST['age' . $i]);
            $starts = intval($_POST['starts' . $i]);
            $first = intval($_POST['first' . $i]);
            $first_sw = intval($_POST['first_sw' . $i]);
            $second = intval($_POST['second' . $i]);
            $second_sp = intval($_POST['second_sp' . $i]);
            $third = intval($_POST['third' . $i]);
            $third_sp = intval($_POST['third_sp' . $i]);
            $age_notes = mysql_real_escape_string($_POST['age_notes' . $i]);
            $age_text = mysql_real_escape_string($_POST['age_text' . $i]);
            $earned = mysql_real_escape_string($_POST['earned' . $i]);

        mysql_query("UPDATE `race_records` SET
            `associated_horse` = $associated_horse, 
            `year` = '$year', 
            `age` = $age, 
            `starts` = $starts, 
            `first` = $first, 
            `first_sw` = $first_sw, 
            `second` = $second, 
            `second_sp` = $second_sp, 
            `third` = $third, 
            `third_sp` = $third_sp, 
            `age_notes` = '$age_notes',
            `age_text` = '$age_text',
            `earned` = '$earned'
            WHERE `rr_id` = $idrr", $HDAdave) or die(mysql_error());
        // not needed:
        mysql_free_result($HDAdave);
        }
?>

edited May 23 '17 at 11:48

Community

1
1

answered Dec 22 '11 at 06:40

Damien Pirsy

25,319
8
70
77

I must have picked up the DW helper code back in the day when I was learning on DW. I'm trying your suggestions and will let you know how it turns out. Having some initial field mismatch errors slowing me down. Thanks for the help. – user1028866 Dec 22 '11 at 07:03
OK, I have followed your suggestions, but haven't yet included a php.ini file. In FF I'm getting the same error messages. In IE I'm getting something else that I can't figure out the reason for. It says "Unknown column 'THREE' in 'field list'". I can't figure out why it's looking for a column "THREE" in my table. The "THREE" is actually the value of $_POST['age_text3']. I know this should help me debug, but I'm not seeing the light! – user1028866 Dec 22 '11 at 07:16
EDIT: $_POST['age_text1'] = "THREE" – user1028866 Dec 22 '11 at 07:30
I am now getting the same error in FF and IE: "Unknown column 'THREE' in 'field list'" – user1028866 Dec 22 '11 at 07:41
Thats' a mysql error, the browsers has nothing do to with it apart from displaying it. I don't know your DB structure, anyway see my updated answer, I added backticks to the column names to avoid misunderstandings – Damien Pirsy Dec 22 '11 at 08:19
OK, I'm back to the update working for $i =1, 2, 3. But for $i=4 I get the "The connection was reset The connection to the server was reset while the page was loading." – user1028866 Dec 22 '11 at 08:44
I'm trying to figure out if the reason for the error is due to the content of $age_notes . $i and not due to the coding. I'm looking at the record that has four entries. I appreciate your help on the coding and advice about the DW helper code. Thanks – user1028866 Dec 23 '11 at 04:02
I have been trouble shooting this in FF but just noticed that it is working properly now in IE! Still fails in FF. – user1028866 Dec 23 '11 at 18:41
@user1028866 Browsers should play any role in this; your code is client side...unless your problem is in the html form that sends the $_POST values, but that's another story :) – Damien Pirsy Dec 23 '11 at 20:50
I'll look for problem in my form. Thanks. – user1028866 Dec 24 '11 at 02:51

Looping "mysql_query" only works for small number of loops?

2 Answers2