Changing Max_Allowed_Packet In Mysql

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.

Bạn đang xem: Changing max_allowed_packet in mysql

Here is what i"ve tried:

In MySQL Query Browser I ran a show variables lượt thích "max_allowed_packet" which gave me 1048576.

Then I execute the query mix 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 lớn 1048576. What am I doing wrong here?

Bonus question, is it possible to compress a BLOB field?


Introducing: Trending sort You can now choose khổng lồ sort by Trending, which boosts votes that have happened recently, helping khổng lồ surface more up-to-date answers.

Trending is based off of the highest score sort and falls back lớn it if no posts are trending.

Try it Dismiss
Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first)
Change in the my.ini or ~/.my.cnf tệp tin by including the single line under or section in your file:

max_allowed_packet=500Mthen restart the MySQL service and you are done.

Xem thêm: Top 8 Điện Thoại Cấu Hình Khủng Giá Rẻ 2020, Top 7 Điện Thoại Cấu Hình Khủng, Giá Rẻ 2021

See the documentation for further information.



The max_allowed_packet variable can be mix globally by running a query.

However, if you bởi vì not change it in the my.ini tệp tin (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 khổng lồ 1GB until the vps restarts:

SET GLOBAL max_allowed_packet=1073741824;



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:

open terminal

ssh root

enter root password

nano /etc/mysql/my.cnf (if command is not recognized vì this first or try vi then repeat: yum install nano )

Control + O (save) then Enter (confirm) then Control + X (exit file)

service mysqld restart

You can kiểm tra the change in the variables section on phpmyadmin

I think some would also want to lớn know how khổng lồ 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, EnterYou could find an entry lượt thích "MySQL56", right click on it, select propertiesYou could see sth lượt thích "D:/Program Files/MySQL/MySQL vps 5.6/binmysqld" --defaults-file="D:ProgramDataMySQLMySQL hệ thống 5.6my.ini" MySQL56

I got this answer from

Following all instructions, this is what I did & worked:

mysql> SELECT CONNECTION_ID();//This is my ID for this session.+-----------------+| CONNECTION_ID() |+-----------------+| trăng tròn |+-----------------+1 row in mix (0.00 sec)mysql> select
max_allowed_packet |+---------------------+| NULL |+---------------------+1 row in set (0.00 sec)mysql> 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> **SET GLOBAL max_allowed_packet=1073741824**; //Now I"m changing the value.Query OK, 0 rows affected (0.00 sec)mysql> select
max_allowed_packet |+---------------------+| NULL |+---------------------+1 row in phối (0.00 sec)mysql> 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 phối (0.00 sec)So, as we can see, the max_allowed_packet has been changed outside from my.ini.

Lets leave the session and kiểm tra again:

global.max_allowed_packet;//The new value still here & And I still have max_allowed_packet=32M inisde my.ini+-----------------------------+|
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 phối (0.00 sec)Conclusion, after set GLOBAL max_allowed_packet=1073741824, the vps will have the new max_allowed_packet until it is restarted, as someone stated previously.

Leave a Reply

Your email address will not be published. Required fields are marked *

  • Cách làm sữa ngô sánh mịn

  • Cách xóa gợi ý tìm kiếm trên messenger

  • Cách kết nối wifi bằng wps

  • Cách xin nghỉ hẳn học thêm

  • x