Error #1153

While importing a table, my HeidiSQL stopped and displayed the following message:

"Error while reading file: E:\...\phpbb2_privmsgs.sql:

SQL Error (1153): Got a packet bigger than 'max_allowed_packet' bytes"

How do I fix this?

- j

* Note: I abbreviated the above filepath.

That happens when max_allowed_packet is lower than an INSERT you are about to execute. You can adjust that variable using HeidiSQL's "Host" > "Variables" tab:
- find the "max_allowed_packet" item and doubleclick it
- increase the value by factor 2 or 5
- check "Global"
- disconnect and reconnect to that server
- try to import the file again - should work

You may not have the privilege to adjust variables but let's try that before diving into more complicated stuff.

When I clicked OK, an error message said:

SQL Error (1227): Access denied; you need the SUPER privilege for this operation.

Since I'm in charge, then how do I grant myself SUPER privileges?

- j

You can try and go to Tools > User manager and check "Server privileges" > Super.

That didn't work; it said that I did not have privileges.

- j

Then you should go and either
- edit your dump file and split large INSERTs into 2 or 3 single ones
- or recreate the backup file

That sounds like a lot of unnecessary work - especially given that there is a user manager which enables privileges.

Why is the user manager there, if it can't be accessed?

How do I access the user manager?

- j

Does this problem have something to do with:




Syntax:
SHOW PRIVILEGES

SHOW PRIVILEGES shows the list of system privileges that the MySQL
server supports. The exact list of privileges depends on the version of
your server.

URL: http://dev.mysql.com/doc/refman/5.0/en/show-privileges.html




?

- j

> Why is the user manager there, if it can't be accessed?

Hehe, of course it can be accessed by users which have the required privileges. You could ask the administrator of that server to give you the privilege.

I am the administrator (no joke) and I do have all privileges, on the server.

So, how do I convey this to Heidisql?

- j

Is this the answer?




mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+




- j

When I tried:




GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'




Heidisql displayed and Error #1045 message.

Yet, Heidisql would still let me export, etc., as if my password had not changed.

So, how do I resolve this?

- j

My mistake, Heidisql displayed an Error #1045 message.

- j

Incidentally, in place of 'root', I had, instead, inserted my usual user name.

After all, I was not logged in as 'root'; I use a different username.

- j

Trying to move things along, I changed my password and re-entered:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'


...on the query panel, but, again, Heidisql displayed an Error #1045 message.

So, I seem to be going around in circles, here.

So, what is the fasted and simplest way for me to access Heidisql's user manager?

...and please keep in mind that I am the administrator.

- j

When I switched *.* for the name of the database, in the above GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'; , Heidisql displayed an Error #1142 message.

How do I, the administrator, resolve this?

- j

If you have all access to the servers files and you do not have an account with all privileges you can start the server ignoring any user privs:

\path\to\mysqld.exe --skip-grant-tables

Start without grant tables. This gives all users FULL ACCESS to all tables!

Thanks for the idea, but I can't.

Initially, I thought it would only effect my instance of Heidisql, but my host's tech says otherwise.

He told me:

"This is not a desirable nor permanent solution as this essentially bypasses all authentication mechanisms and lets anyone connect to the server without auth. This feature is really only intended for use when a lost MySQL root password needs to be reset."

So, I can't.

How about helping me to create a permissions table and helping me to set the SUPER permission, for me, instead?

- j

You should go and edit your SQL file, split large INSERTs into two or three. Now that I see the whole seesaw I think that's the best solution. Of course I don't know how large your file is but probably it's not too big ?

1 file is 37 mb and another is about 150 mb.

- j

Well, not too big for loading into some lightweight text editor. Did you try to edit your files ?

I have a theory.

There might be a syntax error or something like that corrupting both files and that might explain the conflict with Heidisql's factory settings.

So, during the course of the next few weeks, I'm going to go over those files with as much of a 'fine-tooth comb' as I can and see if I can resolve the situation that way.

Now, about the other matter, being the administrator, I think I should know more about handling Heidisql's user manager.

So, I want to create a permissions table and assign to myself SUPER privileges.

I don't intend to reset any settings, but might want to assign privileges, from time to time.

I suspect that I could create the table and, maybe, establish my privileges via cPanel or phpMyAdmin, but I would prefer to learn how to do it via Heidisql.

So, any idea as to how I could go about doing that?

- j

Well you don't have the Super privilege and you cannot assign that yourself, can you? That may be funny but quite possible, I would test that. But I'm really unsure if I understand all this discussion and have the feeling I'm giving you wrong advices...

Why are you repeatedly second-guessing me?

Although I have changed my mind about changing factory settings, I have figured out that I should establish a privileges table and assign to myself what Heidisql calls Super privileges.

So, any idea as to how I could go about doing that, via Heidisql?

- j

If I, the administrator, can't assign Super privileges, then who can?

There's nobody else above me.

So, if not me, then who?

- j

If I was second-guessing you I'm sorry. As said I have the feeling I don't get what you really want now.

> assign to myself what Heidisql calls Super privileges

Not HeidiSQL calls this "super", it's your MySQL server.

> So, if not me, then who?

Man, please look into your mysql.user table - it contains all existing users and you will find a column "Super_priv", which must be set to "Y" to assign super privileges. But that's the same as the user manager does so maybe you will get the same error when trying to change data there.