phpMyAdmin import file size 2M limit

Question

I was using phpmyadmin (Version information: 4.0.10deb1) on php 7.0.7 & nginx 1.4.6 . When I was trying to import a csv file to one of tables, I saw the max size allowed indicated on the phpmyadmin screen is 2,048KiB . Then I changed settings in php.ini (both /etc/php/7.0/fpm/php.ini & /etc/php/7.0/cli/php.ini):

upload_max_filesize = 150M
post_max_size = 150M
memory_limit = -1
max_execution_time = 5000
max_input_time = 5000

changed setting in /etc/nginx/nginx.conf:

client_max_body_size 150M;

and restarted nginx:

service nginx restart

but nothing changed. And the import would fail. How could I fix this issue? Thanks.

Czarism

Possible duplicate of Import file size limit in PHPMyAdmin – Mudstone

@John Mccandles, Search for solution before posting a question. You can go through link provided by Will. – Byssus

see here youtube.com/watch?v=LKEZPVujTgI – Shantae

Answer 1

I checked with my DigitalOcean technical support and found out the reason: I restarted Nginx, but haven't restarted php-fpm which is the PHP process for Nginx.

After I tried service php7.0-fpm restart, phpMyAdmin is showing (Max: 150MiB) for importing limit now. And the importing works!

Czarism

Save my day - just need to restart fpm service(before that you need to make changes in php.ini file) – Theca

Answer 2

You must change the mysql server settings too my.ini or my.cnf file by including the single line under [mysqld] section:

max_allowed_packet=500M

Then restart the MySQL server. In case of 500M is not enough use another value.

Mackay

Answer 3

In my case, after a long search, I found a new world for me which saves too much time and effort (i.e SSH)

Uploading via SCP then importing via SSH.

Advanages:

1- Very fast comparing to the tradional method.

2- No size limit and no need to edit any files.

Disadvantages:

Nothing but the fact that it is not visual process so it needs typing some commands, It will take you sometime to get familiar with it, you might need to keep searching for a while but honestly at the end you will know it is worth it.

• So, first upload the db file somewhere in your server (Doesn't really matter where to upload it).

  scp path to/your file.sql user@server:/path-to-save-the-file (enter password when prompt) (capnel user, appears at the top of your cpanel file manager e.g home3/user/public_html/..)

Please note the following points:

a. in some cases you will need a ssh key so look it up if that's your case.
b. If you are uploading folder and its content you need to add -r flag(i.e. scp -r    ).
c. in my case i was uploading to aws cloud so -i flag is required in this case.

• Now, connect to your server via ssh (I have windows 10 machine, so from powershell, bash or cmd)

  ssh user@server (enter password when prompt)

in my case for aws it was was ( ssh -i key.pem ec2-user@Public IPv4 DNS )

cd /path-where-you- have-saved-the-file

then..

mysql -u username -p databsename < your-file.sql (replace username and database name) (enter db user password when prompt).

Beaubeauchamp

Answer 4

Simple Solution In yout .ini file, change post_max_size DONE!

Haimes

Answer 5

#1 Increase limits like you did in .ini file

upload_max_filesize = 150M
post_max_size = 150M

#2 Restart php, if its a module restart nginx/apache if fpm restart it separately:

service php7.0-fpm restart

#3 Make sure config loaded with making file with or do it with console like php -i | grep upload_max_filesize

#4 If its not loaded check if there multiple config files loaded with step 3 (php -i | less) and change variables in another configs as well. Then do step 2.

Unlimber