LOAD DATA LOCAL INFILE large file?

Question

I'm importing .CSV files into mySQL using LOAD DATA LOCAL INFILE, and it's working for all my test files (which are relativity small).

The problem occurs when I try using the real file I want to work with, which is 97MB in size. Is there a limit to the file size I can upload? If there is, how can I overcome it? My code for the query is below:

// Import csv data in table in respective rows.

    $sql = "LOAD DATA LOCAL INFILE '$csvFileForLoad' INTO TABLE $tableName 
            FIELDS TERMINATED BY '~'
            LINES TERMINATED BY '\\r\\n'
            IGNORE 1 LINES
            (@ignore, name, age, gender)";


// Execute query

    if (mysqli_query($con,$sql)) {
      echo "Table populated successfully.\n";
    } else {
      echo "Error populating table: " . mysqli_error($con) . ".\n";
    }

Thanks!

There is a filesize limit in php. Look [here](https://www.drupal.org/node/97193) — Jens, Jul 09 '14 at 11:22
and maximum execution time [http://php.net/manual/tr/function.set-time-limit.php] — Oras, Jul 09 '14 at 11:28

score 1 · Accepted Answer · answered Jul 09 '14 at 11:36

1

Have a look inside your php.ini - There should be a setting to tweak in there

upload_max_filesize = 10M
post_max_size = 10M

Also check your vhosts and htaccess files as they may have something like

php_value upload_max_filesize 10M
php_value post_max_size 10M

Don't forget to restart your webserver ;-)

HTHs! Thanks, //P

answered Jul 09 '14 at 11:36

YFP

331
3
8

I can't seem to find php.ini in my phpMyAdmin files. Any idea where it may be hiding? – Jul 09 '14 at 12:21
It is generally included under your /etc/ directory on most *nix based systems but check your php.ini - afraid this will take a bit of commandline work ;-) or talking to your provider – YFP Jul 09 '14 at 12:22
Awesome, found it! Now... How do I restart the server without loosing any of the data? (I'm new to this s sorry for all the questions!) – Jul 09 '14 at 12:43
Np Buddy - to restarting the webserver depends on what distro and Webserver you are using try - sudo service apache restart That should sort the webserver bit out ;-) – YFP Jul 09 '14 at 12:47

LOAD DATA LOCAL INFILE large file?

1 Answers1