Member Avatar for sam023

hello friends..
i have a excel file which has more than 20000 rows..!!`when i upload the file i got this error ' MySQL server has gone away '

even i increase the maximum excution time to 3000..and
and memory limit to 512.!!

i just want to know how many rows it can insert or read..so tht i can break the excel sheet according to it..

Edited by sam023 because: n/a
  • 3 Contributors
  • 4 Replies
  • 2K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by cwarn23

Dani AI

Generated

the "MySQL server has gone away" you see with 20k+ rows is almost always a packet-size or timeout issue on the MySQL side, not just PHP. Check and raise, as needed, max_allowed_packet (to handle big multi-row inserts) and wait_timeout/net_write_timeout (to prevent long operations from being cut off). Also avoid giant single INSERTs; smaller batches or a bulk loader are safer. Closing/reopening the connection in a loop (as suggested by ) is usually unnecessary if you tune these limits. is right that FTP helps with upload size, but this error happens after the upload, during import.

For Excel, export to CSV first and stream it. That lets you both count rows quickly and load efficiently. A simple way to estimate throughput is to time a bulk load and use the affected row count. Example (run from CLI, using mysqli or PDO; ensure LOCAL INFILE is enabled and your CSV matches the table schema):

$csv = '/path/upload.csv';   // header in first row
$db  = new mysqli('localhost','user','pass','db');
$start = microtime(true);
$sql = "LOAD DATA LOCAL INFILE '{$csv}' INTO TABLE mytable
        FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES";
$db->query($sql);
printf("Inserted %d rows in %.2fs\n", $db->affected_rows, microtime(true)-$start);

Need a hard row cap per run? Add a timer in your processing loop and stop after N seconds, commit, and resume from the last offset. As a rule of thumb: use the CLI, prepared statements or LOAD DATA, wrap inserts in transactions, and commit every 500–2000 rows to keep packets small and progress steady.

Recommended Answers

All 4 Replies

Member Avatar for cwarn23

Although you increased the memory limit and execution time I bet you didn't do the same for mysql. To do such a processing job you would need to change the memory limit and execution time for both mysql and php. Sounds like you only did it for php. Not sure how to do it for mysql though. I am guessing the memory limit for mysql would be 32MB so if you closed the connection and reopened the connection every so often in the loop then that should solve your problem. For example:

<?php
$link=mysql_connect('localhost','root','');
mysql_select_db('database',$link);

for ($i=0;$i<100000;$i++) { //change condition
if (($i/500)==round($i/500)) {
    mysql_close($link);
    sleep(2); //let cpu decrease
    $link=mysql_connect('localhost','root','');
    mysql_select_db('database',$link);
    }
//do stuff in here
}

The above is a rough idea of what it should look like and hope it helps.

Member Avatar for Manuz

I assume that you problem in file uploading through html file upload method.
If so,,
Try to upload huge files through ftp methods.There are codes for the ftp methods.This will be a better solution for your problem.

Member Avatar for sam023

Although you increased the memory limit and execution time I bet you didn't do the same for mysql. To do such a processing job you would need to change the memory limit and execution time for both mysql and php. Sounds like you only did it for php. Not sure how to do it for mysql though. I am guessing the memory limit for mysql would be 32MB so if you closed the connection and reopened the connection every so often in the loop then that should solve your problem. For example:

<?php
$link=mysql_connect('localhost','root','');
mysql_select_db('database',$link);

for ($i=0;$i<100000;$i++) { //change condition
if (($i/500)==round($i/500)) {
    mysql_close($link);
    sleep(2); //let cpu decrease
    $link=mysql_connect('localhost','root','');
    mysql_select_db('database',$link);
    }
//do stuff in here
}

The above is a rough idea of what it should look like and hope it helps.

Yes u r right.. i didnt increase memory limit for mysql.. nor i found any option to increase memory limit in my.ini

secondly, i want to know how many rows has been read in predfined time..!! how can i do that..??

Member Avatar for cwarn23

Well the answer is you can send to mysql 32MB at a time. That is 33,554,432 characters/letters/digits. So make sure the length of all your mysql query's do not go beyond 32MB and re-establish the mysql connection just before 32MB has been breached.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.