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

Error #1153

User, date Message
Written by jamsession
4 years ago
Category: General
120 posts since Sat, 22 Dec 07
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.
Written by ansgar
4 years ago
4940 posts since Fri, 07 Apr 06
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.
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by ansgar
4 years ago
4940 posts since Fri, 07 Apr 06
You can try and go to Tools > User manager and check "Server privileges" > Super.
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
That didn't work; it said that I did not have privileges.

- j
Written by ansgar
4 years ago
4940 posts since Fri, 07 Apr 06
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
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by ansgar
4 years ago
4940 posts since Fri, 07 Apr 06
> 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.
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
I am the administrator (no joke) and I do have all privileges, on the server.

So, how do I convey this to Heidisql?

- j
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
My mistake, Heidisql displayed an Error #1045 message.

- j
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by ansgar
4 years ago
4940 posts since Fri, 07 Apr 06
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!
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by ansgar
4 years ago
4940 posts since Fri, 07 Apr 06
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 ?
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
1 file is 37 mb and another is about 150 mb.

- j
Written by ansgar
4 years ago
4940 posts since Fri, 07 Apr 06
Well, not too big for loading into some lightweight text editor. Did you try to edit your files ?
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by ansgar
4 years ago
4940 posts since Fri, 07 Apr 06
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...
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by jamsession
4 years ago
120 posts since Sat, 22 Dec 07
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
Written by ansgar
4 years ago
4940 posts since Fri, 07 Apr 06
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.