Error #1153
| User, date | Message |
|---|---|
|
Written by jamsession
3 years ago Category: General 113 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
3 years ago 3976 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
3 years ago 113 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
3 years ago 3976 posts since Fri, 07 Apr 06 |
You can try and go to Tools > User manager and check "Server privileges" > Super. |
|
Written by jamsession
3 years ago 113 posts since Sat, 22 Dec 07 |
That didn't work; it said that I did not have privileges. - j |
|
Written by ansgar
3 years ago 3976 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
3 years ago 113 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 
|
|
Written by jamsession
3 years ago 113 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
3 years ago 3976 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
3 years ago 113 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
3 years ago 113 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
3 years ago 113 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
3 years ago 113 posts since Sat, 22 Dec 07 |
My mistake, Heidisql displayed an Error #1045 message. - j |
|
Written by jamsession
3 years ago 113 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
3 years ago 113 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
3 years ago 113 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
|
|
Written by ansgar
3 years ago 3976 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
3 years ago 113 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
3 years ago 3976 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
3 years ago 113 posts since Sat, 22 Dec 07 |
1 file is 37 mb and another is about 150 mb. - j |
|
Written by ansgar
3 years ago 3976 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
3 years ago 113 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
3 years ago 3976 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
3 years ago 113 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
3 years ago 113 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
3 years ago 3976 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. |