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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
Tomas Helgi JohannssonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Content Management

From novice to tech pro — start learning today.