8

I have to change max_allowed_packet size in MySQL using phpmyadmin, but I don't know how to do it. When I try set global max_allowed_packet=10M in phpmyadmin it give this error

#1227 - Access denied; you need the SUPER privilege for this operation

I can't get SUPER privilege, because server is not in my control.

So, How can I change it?

user1390378
  • 403
  • 2
  • 7
  • 20

3 Answers3

8

You will have to set this in MySQL as well .. Generally found here: 

/etc/mysql/my.cnf

Example:

max_allowed_packet      = 16M

If the server is not in your control, you are going to have to ask for access to said file.

Zak
  • 6,976
  • 2
  • 26
  • 48
4

You can change variables from the "Server variables and settings" page, which is accessible via "Variables" at the top or at [server]/phpmyadmin/server_variables.php

Look up "max_allowed_packet", and hit Edit - default is 4194304 (4MB, in bytes).

user4560259
  • 59
  • 3
  • If you make your answer more organized and fancy it would be a flawless answer! Thank you very much for your help – Grinnex. Jun 15 '20 at 19:26
3

You cannot.

To change it dynamically, as with the SET you tried, you need the SUPER privilege, there is no way around it. And this is a good thing, because 1. the setting is global, which means it affects all connections, and 2. it might jeopardize the server (it makes it easier to DoS a server, for example).

To set it permanently, you need access to the MySQL configuration file and be able to restart the service, as Zak advises.

The real question is, however, why do you need such a high limit. Unless you are trying to import a large dump, having a need for such a limit almost always suggests something was wrongly designed in the first place. If you are importing a dump, try to import smaller bits at a time.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • I have a BLOB field in my table and I am trying to save a 10MB file in it and I am getting this max_allowed_packet error. So how I can save? – user1390378 Jul 10 '13 at 01:13
  • You might want to reconsider storing such large files in the database altogether (please check [this question](http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay) for some advantages of storing them as plain files). Other than that, there is no direct workaround. The only (ugly) hack I can think of is splitting your files in smaller chunks and store each chunk in a separate table with 3 columns: a foreign key to your main entity, a sequence number, and a blob. – RandomSeed Jul 10 '13 at 16:40
  • I would like to suggest that you install WAMP, LAMP, MAMP, or whichever you need onto your own computer. Do all of the work on your own computer, and then export/import the information to the server. In this way you can control how large of a file you can upload and the admin of the server can then handle importing what you've created. – Mark Manning Jul 22 '15 at 20:41