| Bug #34714 | mysql insert stops without errors | ||
|---|---|---|---|
| Submitted: | 21 Feb 2008 4:05 | Modified: | 27 Mar 2008 11:42 |
| Reporter: | totoy mola | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.0.22 | OS: | Windows (MS 2003 Server, IIS, PHP 5) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | MySQL, timeout | ||
[21 Feb 2008 4:06]
totoy mola
this could be a bug with mysql running on IIS. i apologize if i'm wrong. please help.
[21 Feb 2008 10:28]
MySQL Verification Team
Thank you for the bug report. Could you please test connecting with the remote server with mysql.exe client and inserting the data from the csv file and see if the same behavior happens and error/warnings messages are showed. Thanks in advance.
[11 Mar 2008 1:32]
totoy mola
the problem could actually be with the "printing" of the results on the page. i don't know why it happens. i just commented the lines that "echo" text on the page and now i'm getting all 600,000 records. i tried it 3 times and so far so good. not sure if this problem will persist.
[19 Mar 2008 12:47]
Susanne Ebrecht
Does this mean the error was just in your script? Can we close this bug here?
[27 Mar 2008 7:32]
totoy mola
yes i think we can close this now. this is not an error with my script. i suspect the server can't handle mysql updates while echoing output to the page at the same time.
[27 Mar 2008 11:42]
Susanne Ebrecht
Closed this after getting ok for closing from the reporter.
Description: Hi. I don't know why my script stops (or maybe times out) during the process. No error messages. It works fine on my local server but not on the remote server. The script (1) downloads a 50mb zip from an ftp, (2) unzips the content of the zip file, (3) opens the csv file (extracted from zip), (4) assigns the values to temporary variables, (5) inserts these values to a mysql table, then loops back to (4) until feof. The csv has over 400,000 rows.. the process (5) stops at random row sometimes at 17,000.. sometimes 25,000.. 50,000.. 210,000.. etc. But when i run this process on my local server, it completes adding 414,200 rows. Any ideas? I have attached my code. <? //...removed some code here... 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'); //...removed some code here... $fh = fopen("download/books_all.csv", "r"); $i = 0; $fields = array(0=>'sku', 3=>'price', 4=>'isbn', 5=>'format', 6=>'category'); $values = ''; $sql = "insert into tboverstock (".join(',', $fields).") values "; while($row=fgetcsv($fh)) { // Progress indicator //echo "$i: $row[4]<br />"; // Execute batch query every 1000 rows if (++$i % 2000 == 0) { mysql_query($sql . rtrim($valueList, ",")) or die(mysql_error()); echo $sql . $valueList."<br /><br />"; $valueList=""; } // Manipulate and validate data as needed if (strlen($row[4]) != 13) continue; // ignore non 13 number ISBNs // Build query foreach ($fields as $k=>$field) { $values .= "'".mysql_real_escape_string(addslashes($row[$k]))."',"; } $valueList .= "(".rtrim($values,",")."),"; $values = ""; // reset for next line } ?> My remote server's environment: W2K3 Server, IIS6, PHP 5.2.1, mySQL 5.0.22 Please help! Thanks! You can view more information here: http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Windows/Q_... How to repeat: I made two version of the script which are available here: www.bookdealfinder.com/updates/overstock.php and www.bookdealfinder.com/updates/overstock2.php The first one does individual inserts of records from the csv to mysql. The second one does a batch insert (1,000 rows per insert). Both version of the script work perfectly well on my local computer (xampp - apache, php, mysql package).