Importing large files into mysql with phpmyadmin

By David Pratt / Tags: , / 196 Comments / Published: 18-01-10

I’m writing this here because it’s the third time I’ve had this problem, and the third time I’ve forgotten how to fix it! If you ever get hit with the error message:

Phpmyadmin error message

“You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit.”

When trying to import large SQL files into mysql using phpmyadmin, the phpmyadmin documentation offers a few solutions, but I find the easiest method to overcome this is…

Find the config.inc.php file located in the phpmyadmin directory. In my case it is located here:

C:\wamp\apps\phpmyadmin3.2.0.1\config.inc.php

Find the line with $cfg[‘UploadDir’] on it and update it to:

$cfg['UploadDir'] = 'upload';

Create a directory called ‘upload’ within the phpmyadmin directory.

C:\wamp\apps\phpmyadmin3.2.0.1\upload\

Then place the large sql file that you are trying to import into the new upload directory. Now when you go onto the db import page within phpmyadmin console you will notice a drop down present that wasn’t there before – it contains all of the sql files in the upload directory that you have just created. You can now select this and begin the import.

If you’re not using WAMP on Windows, then I’m sure you’ll be able to adapt this to your environment without too much trouble.

Category: Tech

Tags: ,

Posted: on January 18th, 2010 at 2:55 pm.

Feeds: RSS 2.0

196 Responses to “Importing large files into mysql with phpmyadmin”

Nagarajan July 11th, 2012 at 9:08 am

hi all,
can any one explain in detail how to solve this problem in xampp since i am an fresher in php..and i meet the same error…please..

Nagarajan July 11th, 2012 at 9:09 am

This problem occurred: “The directory you set for upload work cannot be reached”
what should I do?

Kevin July 12th, 2012 at 4:04 pm

many thanks!

kani July 26th, 2012 at 12:36 pm

i had some prblm while importing the database in phpmyadmin ,i think that prblm in storage engine

John August 7th, 2012 at 2:39 pm

thanks man, worked for xampp as well with adding what the above comments said.. simply adding the following to the xampp/phpMyAdmin/config.inc.php file

$cfg[‘UploadDir’] = ‘upload’;
$cfg[‘ExecTimeLimit’] = 0;

Robb September 4th, 2012 at 5:18 pm

@john thanks man that worked…

it worked thanks for suggestion

Michael Marshall September 4th, 2012 at 7:28 pm

This is a gravedig post but oh well.
I have tried what you said but I get a “Fatal Error” while uploading.
I am working with a 150mb+ .sql file here!

Kalim September 16th, 2012 at 5:46 pm

Great, its work in XAMP too. Thanks

orangorangan September 19th, 2012 at 11:18 am

ur tips is way more human than the documentation dude!

kiran September 20th, 2012 at 8:22 am

Thanks a lot !! :)

Bogdan October 1st, 2012 at 1:44 pm

great man …worked like a charm, thanks and keep up the good work :)

Syed Kamran October 4th, 2012 at 12:29 pm

Pure skills..

Evelyn October 9th, 2012 at 9:52 am

Great, that works very fine!
Thank you for sharing :)

Amber October 15th, 2012 at 7:47 pm

ummm . . wowww !! thank you so much !!

Jason October 16th, 2012 at 7:28 pm

Worked perfectly…..brilliant!!!!

Amitabh October 17th, 2012 at 5:52 pm

Many Many thanks for your valuable points

Dwain October 25th, 2012 at 8:07 pm

It worked. Thank you very much!

ariessandi October 27th, 2012 at 6:09 am

COOL………THANX MAN….
IS VERI USEFULL FOR ME..
THANXXXX

atik November 14th, 2012 at 1:02 am

thank you very much, realy usefull, it work 100%

Tamawy December 2nd, 2012 at 2:26 am

Thnx man, It works. But I am trying to upload a 27 mb sql file. It take too many time to perform. If there is any faster way tell me please. Thank you

rudy December 7th, 2012 at 12:47 pm

this is very useful information and I followed your instructions step by step.
in myPHPadmin the drop down radiobox appeared but still the system gives the same error message: “No data was received to import. Either no file name was submitted, or the file size exceeded the maximum size permitted by your PHP configuration. See FAQ 1.16.”

any tips?

and my filesize is only 2,7 MB

Urmila December 11th, 2012 at 1:01 pm

this method is really rocks……..

thank you so much!!!!!!

Chandra December 13th, 2012 at 12:54 pm

Hi, Nice solution . but when i try to upload a 200 MB sql file into the system then it is not working fine .. i also changed the max allowed packet data value to check if this will work but still it doesnt ..

Aussie December 15th, 2012 at 4:45 am

Works great, on wampserver 2.2 – save plenty of time, thanks mate

Deepika Patel January 24th, 2013 at 6:49 am

Thanks! it works..

roja March 15th, 2013 at 9:42 pm

HOW TO RESOLVE THIS PROBLEM…

When i try to upload very large file (about to 480MB) of file i got this error message…

plz anyone help me in this regard……

Error

SQL query: DocumentationEdit Edit

SELECT `comment`
FROM `phpmyadmin`.`pma_column_info`
WHERE db_name = ‘BharatCine’
AND table_name = ”
AND column_name = ‘(db_comment)’

MySQL said: Documentation
#1100 – Table ‘pma_column_info’ was not locked with LOCK TABLES

Irfan March 19th, 2013 at 8:41 am

Thanks its is very nice, easy and simple method.
Works perfect.

bauer March 22nd, 2013 at 12:54 pm

thanks a lot :)

Drew April 13th, 2013 at 11:08 am

This is pure gold. Works perfectly on MAMP when nothing else did. Thanks so much.

Kumaresh May 8th, 2013 at 4:06 am

God bless you !! I’m the DBA here @ http://www.20overs.com and I was trying to upload a 110 MB data file. Tried several things, none of them worked. This is my first stint at MySQL and phpMyAdmin. Your advice worked like a charm !! Thanks …
For those of you who received “The directory you set for upload work cannot be reached” error, try ‘/upload’ instead of ‘upload’ and use LOAD DATA INFILE ‘/upload/filename’ … to load the data into your table.

Rob May 9th, 2013 at 10:42 am

Works like a charm. Thanks mate!!

Nithin June 8th, 2013 at 7:49 pm

You can use command line to do the same thing. Just do the following command in the cmd line

C:/path till upload directory> mysql -h hostname -u username -p databasename > dbfile.sql

Remember you shud have your database, host and user created before doing this.

Thanks!

Regginald June 9th, 2013 at 12:41 pm

Ran into the error below when I tried it on XAMPP for Linux:

“Existing configuration file (./config.inc.php) is not readable.”

I fixed it as shown below:

1. Stop XAMPP/ LAMPP
sudo /opt/lampp/lampp stop
2. Navigate to the phpmyadmin folder
cd /opt/lampp/phpmyadmin
3 Change the permissions for the config file:
sudo chmod 644 config.inc.php
4 Start XAMPP/LAMMP
sudo /opt/lampp/lampp stop

Hope this helps someone also facing this error!

Thanks Dave :)

Akzo June 20th, 2013 at 11:38 pm

Thanks, worked in Mamp 2.1.3 on Mountain Lion 10.8.4 while creating a local version of a site.

Bookmarked :-)

Renato Aloi November 19th, 2013 at 12:18 am

For those NOT using Lammp, search for these paths:

/etc/phpmyadmin/config.inc.php
and
/usr/share/phpmyadmin/upload

Thanks for the article!
Renato

Nate February 19th, 2014 at 2:23 am

Half an hour spent trying other solutions and this took about a minute and worked straight away. Thank you!

Gaurav June 4th, 2014 at 2:15 pm

great work buddy

Niranjan July 10th, 2014 at 7:09 am

Thanks dear it works fine

jerbey January 28th, 2015 at 1:49 pm

i cant find $cfg[‘UploadDir’] in my config.inc.php im using xampp can you help me because im importing 500k rows of csv files…

swargam dinesh February 28th, 2015 at 9:04 am

Thank you
IT working great

Samreen March 10th, 2015 at 7:39 pm

I am trying to import a large file,i have changed post_max_size and upload_max_filesize to 128M,but wen i import it , it is showing Error:#1046 No database selected.
Kindly help me to solve this error.

Leave a reply