How to change max allowed packet size

Question

I am having a problem with BLOB fields in my MySQL database - when uploading files larger than approx 1MB I get an error Packets larger than max allowed packet are not allowed   Here is what i ve tried   In MySQL Query Browser I ran a show variables like  max allowed packet  which gave me 1048576    Then I execute the query set global max allowed packet 33554432 followed by show variables like  max allowed packet  - it gives me 33554432 as expected   But when I restart the MySQL server it magically goes back to 1048576  What am I doing wrong here   Bonus question  is it possible to compress a BLOB field

User · Answer

Set the max allowed packet size using MySql Workbench and restart the server

User · Answer

I think some would also want to know how to find the my ini file on your PC  For windows users  I think the best way is as follows    Win R shortcut for  run    type services msc  Enter You could find an entry like  MySQL56   right click on it  select properties You could see sth like  D  Program Files MySQL MySQL Server 5 6 bin mysqld  --defaults-file  D  ProgramData MySQL MySQL Server 5 6 my ini  MySQL56   I got this answer from http   bugs mysql com bug php id 68516

User · Answer

in MYSQL 5 7  max allowed packet is at most 1G  if you want to set it to 4G  it would failed without error and warning

User · Answer

Following all instructions  this is what I did and worked   mysql gt  SELECT CONNECTION ID     This is my ID for this session   -----------------    CONNECTION ID      -----------------                 20    -----------------  1 row in set  0 00 sec   mysql gt  select  max allowed packet   Mysql do not found  max allowed packet  ---------------------     max allowed packet    ---------------------    NULL                   ---------------------  1 row in set  0 00 sec   mysql gt  Select   global max allowed packet    That is better    I have max allowed packet 32M inside my ini  -----------------------------      global max allowed packet    -----------------------------                       33554432    -----------------------------  1 row in set  0 00 sec   mysql gt    SET GLOBAL max allowed packet 1073741824      Now I m changing the value  Query OK  0 rows affected  0 00 sec   mysql gt  select  max allowed packet    Mysql not found  max allowed packet  ---------------------     max allowed packet    ---------------------    NULL                   ---------------------  1 row in set  0 00 sec   mysql gt  Select   global max allowed packet   The new value  And I still have max allowed packet 32M inisde my ini  -----------------------------      global max allowed packet    -----------------------------                     1073741824    -----------------------------  1 row in set  0 00 sec    So  as we can see  the max allowed packet has been changed outside from my ini   Lets leave the session and check again    mysql gt  exit Bye  C  Windows System32 gt mysql -uroot -pPassword Warning  Using a password on the command line interface can be insecure  Welcome to the MySQL monitor   Commands end with   or  g  Your MySQL connection id is 21 Server version  5 6 26-log MySQL Community Server  GPL   Copyright  c  2000  2015  Oracle and or its affiliates  All rights reserved   Oracle is a registered trademark of Oracle Corporation and or its affiliates  Other names may be trademarks of their respective owners   Type  help   or   h  for help  Type   c  to clear the current input statement   mysql gt  SELECT CONNECTION ID     This is my ID for this session   -----------------    CONNECTION ID      -----------------                 21    -----------------  1 row in set  0 00 sec   mysql gt  Select   global max allowed packet   The new value still here and And I still have max allowed packet 32M inisde my ini  -----------------------------      global max allowed packet    -----------------------------                     1073741824    -----------------------------  1 row in set  0 00 sec   Now I will stop the server 2016-02-03 10 28 30 - Server is stopped  mysql gt  SELECT CONNECTION ID    ERROR 2013  HY000   Lost connection to MySQL server during query   Now I will start the server 2016-02-03 10 31 54 - Server is running   C  Windows System32 gt mysql -uroot -pPassword Warning  Using a password on the command line interface can be insecure  Welcome to the MySQL monitor   Commands end with   or  g  Your MySQL connection id is 9 Server version  5 6 26-log MySQL Community Server  GPL   Copyright  c  2000  2015  Oracle and or its affiliates  All rights reserved   Oracle is a registered trademark of Oracle Corporation and or its affiliates  Other names may be trademarks of their respective owners   Type  help   or   h  for help  Type   c  to clear the current input statement   mysql gt  SELECT CONNECTION ID     -----------------    CONNECTION ID      -----------------                  9    -----------------  1 row in set  0 00 sec   mysql gt  Select   global max allowed packet   The previous new value has gone  Now I see what I have inside my ini again   -----------------------------      global max allowed packet    -----------------------------                       33554432    -----------------------------  1 row in set  0 00 sec    Conclusion  after SET GLOBAL max allowed packet 1073741824  the server will have the new max allowed packet until it is restarted  as someone stated previously

User · Answer

Change in the my ini or    my cnf file by including the single line under  mysqld  or  client  section in your file   max allowed packet 500M   then restart the MySQL service and you are done   See the documentation for further information

User · Answer

For anyone running MySQL on Amazon RDS service  this change is done via parameter groups  You need to create a new PG or use an existing one  other than the default  which is read-only    You should search for the max allowed packet parameter  change its value  and then hit save   Back in your MySQL instance  if you created a new PG  you should attach the PG to your instance  you may need a reboot    If you changed a PG that was already attached to your instance  changes will be applied without reboot  to all your instances that have that PG attached

User · Answer

For those running wamp mysql server  Wamp tray Icon -  MySql -  my ini   wampmysqld  port          3306 socket         tmp mysql sock key buffer size   16M max allowed packet   16M           -- gt  changing this wont solve sort buffer size   512K   Scroll down to the end until u find   mysqld  port 3306 explicit defaults for timestamp   TRUE   Add the line of packet size in between   mysqld  port 3306 max allowed packet   16M explicit defaults for timestamp   TRUE   Check whether it worked with this query  Select   global max allowed packet

User · Answer

The max allowed packet variable can be set globally by running a query   However  if you do not change it in the my ini file  as dragon112 suggested   the value will reset when the server restarts  even if you set it globally   To change the max allowed packet for everyone to 1GB until the server restarts    SET GLOBAL max allowed packet 1073741824

User · Answer

Many of the answerers spotted the issue and already gave the solution   I just want to suggest another solution  which is changing the Glogal variable value from within the tool Mysql Workbench  That is ofcourse IF you use Workbench running locally on server  or via SSH connection   You just connect to your instance and go on menu      Server -  Options File -  Networking -  max allowed packed   You set the desired value and then you need to restart MySql Service

User · Answer

If you want upload big size image or data in database  Just change the data type to  BIG BLOB

User · Answer

set global max allowed packet 10000000000

User · Answer

If getting this error while performing a backup  max allowed packet can be set in the my cnf particularly for mysqldump    mysqldump  max allowed packet 512M   I kept getting this error while performing a mysqldump and I did not understand because I had this set in my cnf under the  mysqld  section  Once I figured out I could set it for  mysqldump  and I set the value  my backups completed without issue

User · Answer

One of my junior developers was having a problem modifying this for me so I thought I would expand this in greater detail for linux users   1  open terminal  2  ssh root YOURIP  3  enter root password  4  nano  etc mysql my cnf   if command is not recognized do this first or try vi then repeat  yum install nano    5  add the line  max allowed packet 256M  obviously adjust size for whatever you need  under the  MYSQLD  section  He made a mistake of putting it at the bottom of the file first so it did not work     6  Control   O  save   then ENTER  confirm  then Control   X  exit file   7  service mysqld restart  8  You can check the change in the variables section on phpmyadmin

User · Answer

This error come because of your data contain larger then set value   Just write down the max allowed packed 500M or you can calculate that 500 1024k and use that instead of 500M if you want   Now just restart the MySQL

[mysql] How to change max_allowed_packet size

Examples related to mysql