0

I am using windows server 2003, using iis6.

How to change the mysql packet size?? I cant find any my.cnf files in my server. Please help

I tried to copy and paste from mysql sample file, when i rename it, i rename to ".my.cnf", but got errors. Anyone can help me?

2 Answers 2

3

It's my.ini on windows.

...and it should be structured like:

[mysqld]
max_allowed_packet=16M
1
  • Short and to the point. I like it. +1 Oct 23, 2009 at 9:43
1

On linux, well CentOS at least, you would edit /etc/my.cnf and add the same text as randy melder says. There is likely to already be [mysqld] in my.cnf so you just need to add the second line: max_allowed_packet=16M

It's important that this line is somewhere under [mysqld] not another [] header in this file as the setting won't be picked up.

You would then need to restart the MySQL server (mysqld) as otherwise it won't know about your change since it only reads /etc/my.cnf when it starts up. Restarting mysqld forces it to re-read my.cnf and pick up your change.

To restart mysqld:

/etc/init.d/mysqld restart

Credit: http://theos.in/desktop-linux/tip-that-matters/how-do-i-restart-mysql-server/

Why would you want to increase your packet size? One top reason is if you are importing a large MySQL database in the form of a plain text file, e.g. using this command

mysql -u username -p -h localhost databasename < data.sql

Credit: http://theos.in/desktop-linux/tip-that-matters/how-do-i-restart-mysql-server/

Reasons for this are for example if you are moving databases between servers or restoring a database from a backup. Or if your file size is too large for another method, e.g. import via phpmyadmin

These mysql database export dump plain text files can get large in the order of several 10s of Mb and the default packet size setting can be quite small relatively in comparison.

With that default setting you might not be able to do the import and you would therefore be presented with the error message: "ERROR 1153 (08S01) at line 53: Got a packet bigger than 'max_allowed_packet' bytes".

So setting max_allowed_packet as described in the answer above can cure that problem, provided your setting is larger than the file you want to import.

3
  • I should add that: If the path is not found /etc/init.d/mysqld' in that /etc/init.d/mysqld restart` doesn't run. You can find out the path of your mysqld by doing whereis mysqld. In my CentOS 7 setup, the path is /usr/libexec/mysqld so the command would be: /usr/libexec/mysqld restart You may not need to be root or a sudoer for this command. I suggest trying with and without. If you have a VM, you can shutdown and restart it to be sure that mysqld is restarted to pickup the new max_allowed_packet value. Mar 21, 2018 at 11:44
  • Also, in one scenario I used max_allowed_packet=512M so that a Drupal drush cc all can run without a database gone away error. Mar 21, 2018 at 11:45
  • In some scenarios you may also need to set your PHP memory size to be large enough (this is unrelated to the original problem here) but I thought worth mentioning. e.g. in /etc/php.ini edit so that memory_limit = -1 (this advice assumes you are using PHP with the database, which wouldn't apply if you are not using PHP) Mar 21, 2018 at 11:48

You must log in to answer this question.