distal-attribute
distal-attribute
distal-attribute
distal-attribute

Error #1153

jamsession posted 5 years ago in General
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.
ansgar posted 5 years ago
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.
jamsession posted 5 years ago
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
ansgar posted 5 years ago
You can try and go to Tools > User manager and check "Server privileges" > Super.
jamsession posted 5 years ago
That didn't work; it said that I did not have privileges.

- j
ansgar posted 5 years ago
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

jamsession posted 5 years ago
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 unhappy
jamsession posted 5 years ago
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
ansgar posted 5 years ago
> 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.
jamsession posted 5 years ago
I am the administrator (no joke) and I do have all privileges, on the server.

So, how do I convey this to Heidisql?

- j
jamsession posted 5 years ago
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
jamsession posted 5 years ago
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

jamsession posted 5 years ago
My mistake, Heidisql displayed an Error #1045 message.

- j
jamsession posted 5 years ago
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
jamsession posted 5 years ago
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
jamsession posted 5 years ago
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 unhappy
ansgar posted 5 years ago
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!
jamsession posted 5 years ago
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

ansgar posted 5 years ago
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 ?
jamsession posted 5 years ago
1 file is 37 mb and another is about 150 mb.

- j
ansgar posted 5 years ago
Well, not too big for loading into some lightweight text editor. Did you try to edit your files ?
jamsession posted 5 years ago
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
ansgar posted 5 years ago
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...
jamsession posted 5 years ago
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

jamsession posted 5 years ago
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
ansgar posted 5 years ago
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.

Please login to leave a reply, or register at first.