Solved

max_allowed_packet error using MAMP

Posted on 2012-03-19
15
1,235 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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