Solved

max_allowed_packet error using MAMP

Posted on 2012-03-19
15
1,194 Views
Last Modified: 2012-03-20
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
0
Comment
Question by:bogorman
  • 7
  • 5
  • 3
15 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 37741322
what MySQL version you have?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 37741325
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
 

Author Comment

by:bogorman
ID: 37741487
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 42

Expert Comment

by:sedgwick
ID: 37741747
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
 

Author Comment

by:bogorman
ID: 37742046
Thanks, sedgwick. Sorry you weren't able to help.
0
 

Author Comment

by:bogorman
ID: 37742171
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37742162
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37742172
See my  previous comment.

Regards,
    Tomas Helgi
0
 

Author Comment

by:bogorman
ID: 37742942
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37743069
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
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 37743222
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
 

Author Comment

by:bogorman
ID: 37743275
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
 

Author Comment

by:bogorman
ID: 37743581
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
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 37743686
Yes, as I objected the closing request the question is as I belive still open.

Regards,
     Tomas Helgi
0
 

Author Closing Comment

by:bogorman
ID: 37744213
Thanks again, Tomas.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question