0

While importing a full database on a remote server : mysql -h foo < dump_file.sql, it returns ERROR 1153 ... Got a packet bigger than 'max_allowed_packet' bytes. But I don't have admin rights on that server to change my.cnf settings. The limit is 16Mb

I tried to optimize the dump like following, without result :

mysqldump --skip-extended-insert --net_buffer_length=10000 DATABASE_NAME > dump_file.sql

Would it be helpful to use LOAD DATA INFILE Is there any possible syntax to use it without specifying INTO TABLE ... ?

LOAD DATA INFILE dump_file.sql

Or any other way to turn around that issue ?

EDIT : previous question asks :

Where do I change max_allowed_packet to be able to import the dump? Is there anything else I should set?

when I merely know where to change max_allowed_packet, but I have not enough rights to do it.

So I specifically asked how to do it with CLI only, and if it would be helpful or not to use LOAD DATA INFILE in my case, which is not at all in the other question. Whatever, duplicated or not, I got an answer who solved the issue.

Kojo
  • 315
  • 1
  • 10
  • 22
  • @l0b0 Kind of dupe yes, but I had read this answer already and it gives an admin solution, this is why I specified *no admin access* in the title. Indeed, it looks like I can't do this without changing the server configuration, but I asked because I want/need to be sure :) – Kojo Sep 04 '14 at 12:39
  • Well, if they say ["you have to change it for both the client and the daemon mysqld server"](https://stackoverflow.com/a/722656/96588) that's pretty clear... – l0b0 Sep 04 '14 at 14:11
  • 1
    Yes @l0b0, thank you. But @Giles option `--max_allowed_packet=16M` added to the other set of options `--skip-extended-insert --net_buffer_length=10000` did the job. I managed to solve that issue without changing the server configuration ;-) Very happy end for my current dev – Kojo Sep 04 '14 at 14:56

1 Answers1

4

mysqldump also allows a parameter for this - try adding --max_allowed_packet=16M to your dump command.

Giles
  • 1,597
  • 11
  • 15