0

I'm trying to import a database using Ubuntu Terminal (~260MB) into MySQL and I believe it's timing out and only importing half of the data and dropping me to prompt because of it's size. I've tried:

  1. php.ini
    • max_execution_time = 600
    • post_max_size=512M
    • upload_max_filesize=512M
    • memory_limit=512M
  2. config.inc.php
    • $cfg['ExecTimeLimit'] = 0;
  3. my.cnf
    • max_allowed_packet=512M

Command used: mysql -u root database_name < dump.sql

edit: As others have mentioned, PHP configs were striked because they aren't relevant as I'm using the terminal.

kneeki
  • 2,444
  • 4
  • 17
  • 27
  • 7
    If you are running this from the terminal, it has nothing to do with php and php's limits. – jeroen Jan 04 '16 at 21:16
  • there are some options you can add to the command line that should help you find the problem `--debug`, `--debug-check`, `--verbose` –  Jan 04 '16 at 21:28
  • Using `--verbose` I saw that the import appears to complete successfully. All of the SQL commands flash by on the screen and it ends without error. – kneeki Jan 05 '16 at 00:42

1 Answers1

3

PHP is completely uninvolved in the command you're running so those php configs you listed in your question would have no impact. You can try making tweaks in your my.cnf (often found in the /etc directory). I suggest adding this:

max_allowed_packet=64M

Don't forget to restart mysql for changes in my.cnf to take effect.

Asaph
  • 159,146
  • 25
  • 197
  • 199
  • adding that line to `/etc/alternatives/my.cnf` prevents MySQL from restarting properly. No error, just hangs until I CTRL-C. – kneeki Jan 04 '16 at 22:06
  • Did you add it under the [mysql] section? – Asaph Jan 04 '16 at 22:07
  • I must be in the wrong file. The contents of `my.cnf` is such: http://pastebin.com/nRm9zjav – kneeki Jan 04 '16 at 22:12
  • Yeah, that file looks like something that gets pre-processed to produce the final my.cnf file. Do you have a my.cnf in /etc or /etc/mysql? – Asaph Jan 04 '16 at 22:15
  • Look for `my.cnf` in one of the locations listed here: http://stackoverflow.com/questions/2482234/how-to-know-mysql-my-cnf-location – Asaph Jan 04 '16 at 22:45
  • Looks like `/etc/mysql/conf.d/mysql.cnf` has the [mysql] section. Unfortunately, after adding the line and restarting MySQL I have the same issue loading the database. – kneeki Jan 05 '16 at 00:07
  • Are you sure you're editing the my.cnf that is actually used by MySQL? Because that doesn't look like one of the paths listed here: http://stackoverflow.com/a/2485758/166339 – Asaph Jan 05 '16 at 03:07
  • Run this SQL query to see if your `my.cnf` edit is actually taking effect: `show variables like 'max_allowed_packet';` – Asaph Jan 05 '16 at 03:11
  • Your query results show max_allowed_packet=16777216 bytes which is 16.777 megs. So that means the my.cnf edit to increase it to 64 megs is not taking effect. You must still be editing the wrong my.cnf file. – Asaph Jan 05 '16 at 03:40
  • I've tried editing every file shown here: http://pastebin.com/1ezYFRHx and none of them seem to reflect the change after restarting mysql. *sigh* – kneeki Jan 05 '16 at 06:24
  • Ok, let's try setting that value via SQL. Run this query: `set global max_allowed_packet = 67108864;` then quit the current connection and establish a new connection. That should definitely stick until the next time MySQL is restarted. – Asaph Jan 05 '16 at 06:33
  • Setting that variable was successful, but it didn't resolve importing the database. I'm beginning to think that the problem isn't the mysql config anymore, but the database dump itself. – kneeki Jan 05 '16 at 06:58