Why is max_allowed_packet not having an effect?

Question

I am trying to put a large amount of data into a database.

I have observed that when the data is a lot greater than 1Mb, I get a 500 - Mysql has gone away error.

When the data is closer to the 1Mb mark, I get an error stating that the max packet size has been reached.

When the data is less than or equal to 1Mb, the query succeeds every time.

I have had a look max_allowed_packet in my.ini (I'm running on a Windows machine), and set it to 16M and even increased it higher. I have made sure that I have restarted mysql too.

But the same behavior is happening. Is there another setting somewhere that I am missing? For your information, I am using PHP to run the queries.

Thanks.

Answer 1

Turns out that I needed to change it in two places. I discovered on phpMyAdmin, under settings and variables that max packets allowed was indeed still 1M, despite me changing it in the config.

With this new information, I was able to focus my searches and it led me to

SET @@global.max_allowed_packet=n

Which showed me exactly how to change the global settings!

Problem is, I think if the server resets, it goes back to what it was, I need to do some settings file. More complications that I don't need right now.

Answer 2

fopen a file handle, and then bind the resource to the parameter.

Answer 3

I had the similar problem and solved it by increasing upload_max_filesize and memory_limit on php.ini

Answer 4

You can set the max_allowed_packet as a global variable. You can set this variable dynamically, without restarting the MYSQL Service.

You can increase it up to 1 gigabyte. I'd recommend setting it to at least 2x the largest data string you expect to insert. That will give you some room to expand because of course next week you will have to handle a larger file than you expected.

See an example of setting the max_allowed_packet dynamically from PHP code here: http://www.devplace.nl/blog/storing-large-values-in-mysql-fields-with-php