8

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.

John Mccandles
  • 1,181
  • 2
  • 16
  • 24
  • 1
    Possible duplicate of [Import file size limit in PHPMyAdmin](http://stackoverflow.com/questions/3958615/import-file-size-limit-in-phpmyadmin) – Will Jul 28 '16 at 04:40
  • @John Mccandles, Search for solution before posting a question. You can go through link provided by Will. – ersks Jul 04 '17 at 03:08

3 Answers3

16

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!

John Mccandles
  • 1,181
  • 2
  • 16
  • 24
  • Save my day - just need to restart fpm service(before that you need to make changes in php.ini file) – Dany Jul 20 '23 at 07:21
1

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.

Ivan Cachicatari
  • 4,212
  • 2
  • 21
  • 41
1

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).
shireef khatab
  • 977
  • 2
  • 13
  • 33