how should i import a huge table in phpmyadmin

Question

I have a table with 300,000 rows. then when i import it, it will uploaded almost 200,000 rows then phpmyadmin kills the upload with this error:

Fatal error: Maximum execution time of 300 seconds exceeded in C:\xampp\phpMyAdmin\libraries\import\sql.php on line 246

Note: 'on line 246' is not unchanged. sometimes is 130 or 182.

It should be noted that i changed (increased) all the parameter in php.ini file. something such as:

post_max_size       = 750M
upload_max_filesize = 750M
max_execution_time  = 5000
max_input_time      = 5000
memory_limit        = 1000M

Still i could not upload my huge table .....! In fact, my problem is time limit. What suggestions do you have ?

@Vohuman i logged the sql file and don't find any appropriate variable for change. — Shafizadeh, May 25 '15 at 20:51
What exactly do you mean by "don't find any appropriate variable for change"? — Ram, May 25 '15 at 20:59
@Vohuman What exactly do you mean by "Log into mysql shell and source the sql file" ? — Shafizadeh, May 25 '15 at 20:59
This should answer your question: http://stackoverflow.com/questions/6163694/how-do-you-use-mysqls-source-command-to-import-large-files-in-windows — Ram, May 25 '15 at 21:02
@Sajad Normally I roll in things like this through the `mysql` command-line tool since it is much more reliable than `phpmyadmin`. If you can do that, give it a shot. — tadman, May 25 '15 at 21:05
@Vohuman aha, but i'm in local (my PC), i have not a server with linux OS , then how should i use command line ? — Shafizadeh, May 25 '15 at 21:05
You don't have to have a Linux OS installed. It seems you are using a XAMPP stack, this might help http://stackoverflow.com/questions/698914/how-can-i-access-the-mysql-command-line-with-xampp-for-windows — Ram, May 25 '15 at 21:13
@Vohuman you right...! tnx alot. just are you from iran ? can you speak persian ? — Shafizadeh, May 25 '15 at 21:14
@Vohuman wow !! vaghaan khoshHalam ke ye irani ro inja mibinam ! 87,014 reputation ??? damet garm baba !! movafagh bashi — Shafizadeh, May 25 '15 at 21:18

score 2 · Accepted Answer · answered May 26 '15 at 10:54

I could solve my problem with the help of @vohuman.

To get rid of the limitations of import (and even export), you should use command line, Or in the words of @vohuman:

@vohuman: 'Log into mysql shell and source the sql file'

So, to import a .sql file (for windows and xampp), you should do these:

step1: put your .sql file on C drive, then your file address will be: c:\filename.sql

step2: open CMD and type this:

c:\xampp\mysql\bin

step3: then type this line into command line to import your file:

mysql -u {username} -p {databasename} < c:\filename.sql

By default {username} is root.

If you use the password, you should use -p{passwordname} in above line. (between -p and passwordname isn't any space.)

Also you can use any other address. c:\filename.sql is for example.

step4: finally, check your database and uploaded file. (for example use phpmyadmin)

score 0 · Answer 2 · answered May 25 '15 at 20:52

The configuration is ok, but you have to notice that if you are running phpmyadmin with an alias conf file, inside the alias you can set the php variables right to the alias, and will not be affected with the directives that you set in the global php.ini, check the alias (if is the case)

Alias /phpmyadmin "/path/phpmyadmin4.4.6/"
<Directory "/path/phpmyadmin4.4.6/">
   Options Indexes FollowSymLinks MultiViews
   AllowOverride all
  <IfDefine APACHE24>
    Require local
  </IfDefine>
  <IfDefine !APACHE24>
    Order Deny,Allow
      Deny from all
      Allow from localhost ::1 127.0.0.1
    </IfDefine>
  php_admin_value upload_max_filesize 128M
  php_admin_value post_max_size 128M
  php_admin_value max_execution_time 360
  php_admin_value max_input_time 360
</Directory>

how should i import a huge table in phpmyadmin

2 Answers2