MySQL max_allowed_packets

kklosson

When the AwareIM control panel starts, as near as I can tell it sets the max_allowed_packets variable for MySQL to 16K. My application has some reports that require this setting to be much higher. So if I restart the control panel, I have to reset this variable on the MySQL server. It would be nice if I didn't have to do this.

Support, is there any way you can give as a setting somewhere to set that when the control panel starts - like in the settings or something?

Rennur

# SERVER SECTION

----------------------------------------------------------------------

#

The following options will be read by the MySQL Server. Make sure that

you have installed the server correctly (see above) so it reads this

file.

#
[mysqld]

max_allowed_packet=500M

I have set mine to
max_allowed_packet=500M

C:\Program Files\MySQL\MySQL Server 5.x\my.ini

kklosson

I have set this value in the file my-default.ini in the root folder:

Set Max Allwed Packet

max_allowed_packet=128M

It never takes.

Rennur

Change in the my.ini file under [mysqld].

kklosson

Thanks. I don't know what you mean by "under [mysqld]. There is no "my.ini" file in the directory tree.

kklosson

So why does this variable change whenever I restart the control panel?

kklosson

So can anyone answer the above question?

hpl123

Kklosson,
I can´t answer your question about AIM resetting your MySQL packet setting at startup but I think setting this setting in the MySQL config file will work. I myself have other settings there related to AIM that isn´t affected when starting up, restarting etc. the server.

Regarding the config file, this differs a bit on different versions of MySQL, see this post for info:
http://stackoverflow.com/questions/4292769/what-is-the-location-of-mysql-client-my-cnf-in-windows

kklosson

Thanks but this setting is only applied with the service is started. Anytime I restart the control panel this setting goes back to 16K. The control panel startup must be doing something...

ACDC

It seems odd that Aware is enforcing this setting on a reload. This could be a problem, I wonder why they are doing it, maybe support can advise us on this. In the meantime if you have this setting in your my.ini file, if you stop and start the Mysql Service you can update the setting... but then you have to do this on every reset of the aware server

kklosson

It's easy enough to reset the variable, I just see no reason for AwareIM to push a value to it. It goes to my original post... Either give us a setting to push or leave it alone. I would like Support to confirm whether this is actually an issue they can fiddle with.

Rennur

So why does this variable change whenever I restart the control panel?

It seems that the AIM server always resets the value to 16mb. Mine is set to 1GB and I have checked the settings using MySQL tools to confirm this.

I'm now getting an error saying

Message is Unable to persist process context Packet for query is too large (31721605 > 16776192). You can change this value on the server by setting the max_allowed_packet' variable. Sending failure reply

Everyone should check the Server output on startup to see if AIM sets the value as 16mb.

aware_support

The problem should be fixed in build 2027

hpl123

Great this is fixed.
You guys that know, what should we ideally set this to for our apps? and also is it dependant on app type i.e. app with big reports use more, apps with no reports use default etc. etc.?

Thanks

kklosson

You will know when you need it because your user will get an error pointing right to it. I am currently using 128000000. I get the error when generating a very complex report.

kklosson

Still getting an error in Build 2029. In this case, attempting to connect to an AWS MySQL instance. Screenshot says it all, I believe. Exact error reads:

Process status: Aware IM Server: Unable to set required value to 'max_allowed_packet' database variable due to the following exception Access denied; you need (at least one of) the SUPER privilege(s) for this operation Make sure that it is at least 10M and start the server again.

aware_support

Aware IM WILL set this value to 16M unless the value has been set already to something greater than 16M.
There were no changes in this area in build 2029

Rennur

See if you can set the value using MySql Workbench:

kklosson

Turned out I had to set this parameter in the RDS console.