What is maximum query size for mysql?

Question

What is maximum query size for mysql? Some times the server stops working when I fire a too long query.

http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html — AllTooSir, May 02 '13 at 09:51

score 227 · Answer 1 · edited Jan 14 '21 at 03:55

227

You can check your current server setting with:

SHOW VARIABLES LIKE 'max_allowed_packet';

This gives you the answer in bytes. for e.g max_allowed_packet=1048576 or 1 MiB

edited Jan 14 '21 at 03:55

Joundill

answered May 02 '13 at 09:50

kiriloff

If it's in bytes, then the 1mb from answer is about 8 times smaller. – Lukas Salich Oct 05 '17 at 10:36
7

It seems that this answer implies that the maximum query length is only limited by the maximum packet size that can be sent to the server. This basically means that there is no upper limit to query lengths as long as you're able to send it to the server. – jlh Apr 06 '18 at 09:03

score 39 · Answer 2 · edited May 02 '13 at 10:48

39

To increase the max_allowed_packet, open

my.ini/my.cnf under [mysqld] section.

Once the change is done you would have to restart the server.

edited May 02 '13 at 10:48

Yadav Chetan

answered May 02 '13 at 10:40

3

Using "128MB", "1GB", etc is allowed. The value does not have to be in bytes (but it is supported). Max value is 1GB. If you set it to more than 1GB, mysql will still start normally, but it will not accept anything over 1GB. – Nikola Miljković Nov 22 '21 at 08:19

2 Answers2