MySQL won't update max_allowed_packet

Question

There are a ton of questions in SO about changing the max_allowed_packet variable for mysql. None of the advice given so far has helped me. I am on OSX 10.10.4 and have the following in ~/.my.cnf

[mysqld]
user = root
password = redacted
general_log=1
log=/var/log/mysql-query.log
max_allowed_packet = 32M

[mysqldump]
max_allowed_packet = 32M
user = root
password = redacted

[mysql]
max_allowed_packet = 32M

I stop the service and make sure it is stopped. Then I restart the service and query:

SHOW VARIABLES LIKE '%max_allowed_packet%';

Which gives me the same:

Variable_name              Value
max_allowed_packet         1048576
slave_max_allowed_packet   1073741824

I'm running MySQL 5.5.29 and there are no other config files in any of these places:

/etc/my.cnf 
/etc/mysql/my.cnf 
/usr/local/mysql/etc/my.cnf

Any help at getting my 1M limit increased will be greatly appreciated and upvoted.

Might be that Sequel Pro is simply lying http://playingtree.blogspot.ro/2011/08/packet-for-query-is-too-largesetting.html — Mihai, Jul 16 '15 at 11:18
Good find @Mihai! But no dice. Running `SHOW VARIABLES LIKE '%max_allowed_packet%'` through the shell also reports only 1M — Jannie Theunissen, Jul 16 '15 at 11:31
Might be that its not the good config file,I cant think of something else. — Mihai, Jul 16 '15 at 11:35
If I put something weird in that config file, the mysql instance won't start. So I assume everything is fine — Jannie Theunissen, Jul 16 '15 at 11:36
Is MySQL server really using ~/.my.cnf as configuration file? Maybe this file is ignored and file from another location is used as described here - http://stackoverflow.com/questions/10757169/mysql-my-cnf-location — Ondřej Šotek, Jul 17 '15 at 20:41

score 1 · Accepted Answer · answered Jul 20 '15 at 07:31

Wish I could wait for a less drastic solution, but I had to give up on the native package installed version. After making backups of all my databases I switched to a brewed version:

remove

sudo /usr/local/mysql/support-files/mysql.server stop
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm /Library/LaunchDaemons/com.mysql.mysql.plist
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /private/var/db/receipts/*mysql*
sudo rm -rf /var/db/receipts/com.mysql.*

install

brew install -v mysql
cp -v $(brew --prefix mysql)/support-files/my-default.cnf $(brew --prefix)/etc/my.cnf
cat >> $(brew --prefix)/etc/my.cnf <<'EOF'

# my changes
max_allowed_packet = 32M
innodb_file_per_table = 1
EOF

sed -i '' 's/^#[[:space:]]*\(innodb_buffer_pool_size\)/\1/' $(brew --prefix)/etc/my.cnf
brew services start mysql
$(brew --prefix mysql)/bin/mysql_secure_installation

MySQL won't update max_allowed_packet

1 Answers1

remove

install