How to store permanent set max_allowed_packet mysql variable

Question

I have MySQL version 5.5. I want increase database packet size permanently.How can i store permanently packet size in mysql .

score 1 · Accepted Answer · answered Oct 09 '14 at 06:00

1

Change in the my.ini file. Include the single line under [mysqld] in your file

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

See the documentation for the further information.....https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

answered Oct 09 '14 at 06:00

Programmer man

393
3
14

How can find my.ini file – Himanshu Deria Oct 09 '14 at 06:02
what are you using linux,xampp or wamp? – Programmer man Oct 09 '14 at 06:05
Check this path In MySQL 5.5 Windows the default path for my.ini is C:\ProgramData\MySQL\MySQL Server, but what are you using as a localserver – Programmer man Oct 09 '14 at 06:10
The name of your local server please, as in wamp or xampp? – Programmer man Oct 09 '14 at 06:11
C:\xampp\mysql\bin this my xampp location, the type file is Configuration_settings file name my ..or try the first one I sent.. MySQL 5.5 Windows the default path for my.ini is C:\ProgramData\MySQL\MySQL Server, but what are you using as a localserver – Programmer man Oct 09 '14 at 06:16
I add your query max_allowed_packet=500M in my my.ini file but cant save file get error Access Denied – Himanshu Deria Oct 09 '14 at 06:17
Please tick this answer and up vote it if you don't mind only if you found my help resoucesful...You need to run what ever you opening it with eg notepad, notepad++ as an administrator – Programmer man Oct 09 '14 at 06:18
https://www.youtube.com/watch?v=DE56Trxx0h8 also check this one http://www.guidingtech.com/16317/run-notepad-as-admin-edit-system-files-without-errors/ – Programmer man Oct 09 '14 at 06:19
I add your query in my.in file , but can't get result – Himanshu Deria Oct 09 '14 at 06:43
run this..show GLOBAL variables like 'max_allowed_packet%'; – Programmer man Oct 09 '14 at 06:48
and try setting a new one like SET GLOBAL max_allowed_packet=1073741824; or SET GLOBAL max_allowed_packet = numeric;...with a numeric number just like the above answer – Programmer man Oct 09 '14 at 06:50
When i restart my server that time previous packet data size show – Himanshu Deria Oct 09 '14 at 07:12
Something problem here when i upload Up to 1024 KB file it's working, but i upload 1 MB file that's time file not uploading – Himanshu Deria Oct 10 '14 at 04:54

score 0 · Answer 2 · edited Apr 13 '17 at 12:42

You have two values of max_allowed_packet in MySQL :

one on the client side : [mysql] section, [mysqldump], [client] and more. one on the server side : [mysqld] section. The value of max_allowed_packet you see with the command show variables like 'max_allowed_packet'; is the one on the server side.

In order to increase this value, you must increase both sides : in your server configuration file ([mysqld] section in your my.ini file) and in your client configuration file (whether your [client] or [mysql] section in your my.ini file).

This setting can be changed on the server side without restarting the server if you have the SUPER privilege with this command : mysql> SET GLOBAL max_allowed_packet = numeric;.

Don't forget to change the 'numeric' value by a numeric value. Don't forget to change your configuration file too otherwise this value will be reset at reboot.

Rehash from old answer: https://dba.stackexchange.com/questions/45087/max-allowed-packet-in-mysql

How to store permanent set max_allowed_packet mysql variable

2 Answers2