0

I'm trying to dump a large database (126 MB) with extended inserts in MAMP (localhost on my computer) and I get errors whatever I do. First I tried to dump it via terminal with 

/applications/MAMP/library/bin/mysql -u root -p databasename < /path/file.sql

but I got this error (line 44 is where there is the first INSERT INTO)

ERROR 2006 (HY000) at line 44: MySQL server has gone away

so I copied my-large.cnf into /applications/MAMP/conf/ renamed it to my.conf, set the new values for max_allowed_packet

[mysqldump]
quick
max_allowed_packet = 32M

and also put the line skip-character-set-client-handshake after [mysqld]

# The MySQL server
[mysqld]
skip-character-set-client-handshake

saved the file, restarted the server, tried again to dump the database with the command line and still got the same error.

I also tried to import it with MY MAMP DUMP but I get an error message after few seconds: An error occurred while processing SQL file.

I then tried with bigdump, where I set $max_query_lines = 6000; but the script doesn't even seem to run (yes, I put the file and the script in the same directory and yes, the mysql server is running).

I really don't know what else to do, what could be the problem?

Foxsquirrel
  • 373
  • 1
  • 3
  • 8
  • Besides `max_allowed_packet` search for `wait_timeout` in your cnf and set it to 300 secs or something.Also use mysqldump directly – Mihai Feb 03 '15 at 14:35
  • there is no `wait_timeout` in the file, can I add it after `max_allowed_packet`? – Foxsquirrel Feb 03 '15 at 14:41
  • Yes add it in [mysqld] `wait_timeout=300` maybe even increase max_allowed to 64 – Mihai Feb 03 '15 at 14:42
  • Thanks Mihai. I added the line and tried with mysqldump (which I never did) but I got a new error: `unknown variable 'wait_timeout=500'`. So I removed the line again and wow! Apparently the dump succeded in a blink! `-- Dump completed on 2015-02-03 15:53:49`... wait, no, I go to see the database via phpmyadmin and the database is empty :( – Foxsquirrel Feb 03 '15 at 14:57
  • Those variables have nothing to do with your db being empty.wait_timeout is what it says,a query will stop after this amount of time.There is something more.At least do you have a db dump? – Mihai Feb 03 '15 at 15:00
  • Uhm... I have a very big sql file with tables that I want to upload in order to interact with it via php, if that's what you mean with "db dump". Did I understand correctly your question? – Foxsquirrel Feb 03 '15 at 15:03
  • Look here but its the same advice http://stackoverflow.com/questions/12425287/mysql-server-has-gone-away-when-importing-large-sql-file Also did you refresh you phpmyadmin? – Mihai Feb 03 '15 at 15:04
  • Thanks Mihai, but when I added the line the program didn't even run because I got this error `mysqldump: unknown variable 'wait_timeout=500'`. I'm trying again. – Foxsquirrel Feb 03 '15 at 15:07
  • Make sure its under [mysqld] part in the cnf file – Mihai Feb 03 '15 at 15:10
  • 1
    Thanks a lot mihai! I managed to dump the database adding that line and using MY MAMP DUMP utility to upload the file (still didn't manage to make it work via command line, I can't understand why, but it doesn't matter anymore). Thanks thanks thanks! – Foxsquirrel Feb 03 '15 at 16:04

0 Answers0