What is the difference between setting the max_allowed_packet via the MySQL commandline using:

set global max_allowed_packet=100000000;

And setting it by editing the MySQL my.ini (Win) or my.cnf (Linux) config and putting the following code under [mysqld]:

set global max_allowed_packet = 1000000000

This sets max_allowed_packet it to 1 billion bytes or 1 GB.


This sets it to 104857600 bytes or 100 MiB.

Assuming your extra zero is unintended, the only difference between setting via command line and config is that the config file supports shorthand using JEDEC binary prefixes. Plus, the set global will be lost on server restart.

  • +1 Yes, you're correct -- that was a typo, I've updated the question to address it. One quick question, for the config and/or setting to take effect, does the server need to be restarted? Guessing the command sets it live, but not sure about the config. Thanks! – blunders Mar 8 '11 at 19:04
  • 1
    @blunders: config file changes always require a restart – hobodave Mar 8 '11 at 19:17
  • +1 for the clarification. – blunders Mar 8 '11 at 19:48

Another major difference is the fact that thesetting of max_allowed_packet will survive a mysql restart if you set the variable in my.cnf (or my.ini)

Additionally, if you set that variable globally, it would make sense to set the session variables as well, especially if the session needs it.

Ugh, I just read hobodave's answer. Clearer answer. I give him a +1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.