Solved

max_allowed_packet error using MAMP

Posted on 2012-03-19
15
1,276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

707 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