How to change max_allowed_packet size

Question

How to change max_allowed_packet size

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:

n 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?

mysql

3 years ago by vishaljlf39

2 Answers

Login / Signup to Answer the Question.

Login Signup

**aashaykumar** · Answer 1 · 2021-08-04T13:21:58

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.

**sandhya6gczb** · Answer 2 · 2021-08-05T13:52:29

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;

C TUTORIAL

C PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

C++ TUTORIAL

C++ PROGRAMS

INTERVIEW TESTS

EXECUTE CODE

PYTHON TUTORIAL

PYTHON HOW TOS

INTERVIEW TESTS

EXECUTE CODE

JAVA TUTORIAL

JAVA CODE EXAMPLES

SPRING TUTORIAL

MORE IN JAVA

COMPUTER ARCHITECTURE

COMPUTER NETWORK

OPERATING SYSTEM

DBMS & SQL

PL/SQL

MongoDB

EXECUTE SQL

ANDROID DEVELOPMENT

GO LANGUAGE

LINUX

DOCKER

HTML TAGS (A to Z)

CSS REFERENCES

SASS/SCSS

KOTLIN

GAME DEVELOPMENT

PHP

GIT GUIDE

JAVASCRIPT

ADVANCED DSA

How to change max_allowed_packet size

2 Answers

Login / Signup to Answer the Question.