Setting max_allowed_packet

  -  
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: Setting max_allowed_packet

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 set global max_allowed_packet=33554432 followed by show variables lượt thích "max_allowed_packet" - it gives me 33554432 as expected.

But when I restart the MySQL vps it magically goes back lớn 1048576. What am I doing wrong here?

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


*

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 và you are done.

See the documentation for further information.

Xem thêm: 5 Cách Chỉnh Tăng Giảm Độ Sáng Màn Hình Win 10 Máy Bàn Pc, Laptop


*

*

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

However, if you bởi not change it in the my.ini file (as dragon112 suggested), the value will reset when the vps 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;

*

*

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
YOURIP

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 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, EnterYou could find an entry like "MySQL56", right click on it, select propertiesYou could see sth like "D:/Program Files/MySQL/MySQL vps 5.6/binmysqld" --defaults-file="D:ProgramDataMySQLMySQL server 5.6my.ini" MySQL56

I got this answer from http://bugs.mysql.com/bug.php?id=68516


Following all instructions, this is what I did và worked:

mysql> SELECT CONNECTION_ID();//This is my ID for this session.+-----------------+| CONNECTION_ID() |+-----------------+| đôi mươi |+-----------------+1 row in mix (0.00 sec)mysql> select
max_allowed_packet |+---------------------+| NULL |+---------------------+1 row in phối (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 mix (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 mix (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.

Xem thêm: Đăng Ký Tên Miền Tiếng Việt Miễn Phí, Hướng Dãn Đăng Ký Tên Miền Tiếng Việt

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 set (0.00 sec)Conclusion, after phối GLOBAL max_allowed_packet=1073741824, the hệ thống will have the new max_allowed_packet until it is restarted, as someone stated previously.