max_allowed_packet error using MAMP

Am running drupal on MAMP (on a Macbook Pro using OSX 10.7.3). It seems to run ok apart from the fact that I keep on getting the error:

Cannot set variable `max_allowed_packet` to `536870912` in your database configuration!
Probably you do not have proper privileges on the database server.

I have created a my.cnf file in the MAMP/Library directory (a copy of my-medium.cnf.  The only change I have made in this is to comment out the line

log-bin=mysql-bin

to avoid multiple error messages like the above.   The max_allowed packet as defined in the file is the default value and not 536870912.

I have disabled the update status module as I understand this can cause this problem.

Can anyone help me to solve it? By the way, I have run update.php
bogormanAsked:
Who is Participating?
 
Tomas Helgi JohannssonConnect With a Mentor Commented:
Another thing you could do.
Look at the startMysql.sh batch file in the \MAMP\bin\ and
add the option "--max_allowed_packet=536870912" (change from 1MB (which is the default to 512MB).

And look at the process. (You should also look at the process before the change to see before and after change)

# /bin/sh
/Applications/MAMP/Library/bin/mysqld_safe --port=3306 --max_allowed_packet=536870912 --socket=/Applications/MAMP/tmp/mysql/mysql.sock --lower_case_table_names=0 --pid-file=/Applications/MAMP/tmp/mysql/mysql.pid --log-error=/Applications/MAMP/logs/mysql_error_log &

as mentioned in this forum
http://forum.mamp.info/viewtopic.php?f=2&t=14115

Regards,
    Tomas Helgi
0
 
Meir RivkinFull stack Software EngineerCommented:
what MySQL version you have?
0
 
Meir RivkinFull stack Software EngineerCommented:
from http://dev.mysql.com about max_allowed_packet:

As of MySQL 5.1.31, the session value of this variable is read only. Before 5.1.31, setting the session value is permitted but has no effect.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
bogormanAuthor Commented:
When I run MAMP and then phpMyAdmin, under MySQL it lists:

Server version: 5.5.9

I know that in the later versions one cannot set this value. Also  if I select the Variables tab it shows 1,048,576 as the value of max_allowed_packet.  Strange as the error shows 536870912.   I have searched in Finder for this value (either 536870912 or 512 (MByte equivalent) and I cannot find any file with these as settings for max_allowed_packet, so it looks as though it is not being set by any of the files on my disk.  In my.cnf it is set as 1M (under [mysqld] and 16M under [mysqldump] - the values in my-medium.cnf.
0
 
Meir RivkinFull stack Software EngineerCommented:
it's weird, I've never encountered this kind of error before.
my best offer for you is using the MySql forum to post a question there, I'm sure you'll get a better help.

http://forums.mysql.com

cheers
0
 
bogormanAuthor Commented:
Thanks, sedgwick. Sorry you weren't able to help.
0
 
bogormanAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for bogorman's comment #37741487

for the following reason:

No one solved the problem.
0
 
Tomas Helgi JohannssonCommented:
Hi!

Have you tried setting max_allowed_packet to greater than 512MB in my.cnf
restart the MySQL server and see what happens ?
Usually in coded there is a check to see if your db has this value set high and if not
then it is set.
So if you have it already set high then you should get rid of this message.

Hope this helps.

Regards,
    Tomas Helgi
0
 
Tomas Helgi JohannssonCommented:
See my  previous comment.

Regards,
    Tomas Helgi
0
 
bogormanAuthor Commented:
Hi Tomas,

Thanks. Sorry. I did not see your message before assigning the points to me. Can I change this?

Anyway, have done as you suggested. I have also discovered that the Variables tab in phpMyAdmin shows the value of max_allowed_packet. Unfortunately after closing MAMP and restarting it the value has reverted to the 1M value.  I have also tried running:

SET GLOBAL max_allowed_packet=512*1024*1024;

This sets the value but as soon as I restart MAMP it has reverted to 1M. It just does not retain the value.

I have more than one user defined via phpMyAdmin. Could this affect it?

Brian
0
 
Tomas Helgi JohannssonCommented:
Hi!

After little googling and looking at the MAMP documentation
http://documentation.mamp.info/en/mamp/how-tos/set-the-mysql-default-storage-engine
I see that there could be another my.cnf file located
/Applications/MAMP/conf/
which is the file MAMP uses by default.
Edit that my.cnf file and restart the MAMP.

Regards,
    Tomas Helgi
0
 
bogormanAuthor Commented:
Hi Tomas,

Think I have solved it at last.

I noticed that on the Privileges page of phpMyAdmin there is a link:

"Note: phpMyAdmin gets the users' privileges directly from MySQL's privilege tables. The content of these tables may differ from the privileges the server uses, if they have been changed manually. In this case, you should reload the privileges before you continue."

I clicked on this and the problem was solved!   Think, in trying to sort out the Privileges error I must have changed some setting in Privileges and did not notice the link.  Anyway, the error has  gone.   Thanks so much for trying to help.

Brian
0
 
bogormanAuthor Commented:
Hi Tomas,
Thanks for your most helpful suggestions. I hesitate to make any other changes as it all works now but am very grateful for the work you have done on it.
Can I reassign the points?
Brian
0
 
Tomas Helgi JohannssonCommented:
Yes, as I objected the closing request the question is as I belive still open.

Regards,
     Tomas Helgi
0
 
bogormanAuthor Commented:
Thanks again, Tomas.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.