#1153 - Got a packet bigger than 'max_allowed_packet' bytes on database import

[GherkinDilds]

So I've given up on using tickets, response times take hours regardless of priority. Its been at least two weeks since I was actually able to begin setting up my VPS (first time hosting, no experience) - my site's still not up.

Anyways, I am trying to import my Drupal database from my localhost. I have created a database via Plesk but when I try to import it I get an error message which stops the import. My database is 1.5MB compressed so size is not an issue. The database is only partially imported. I have submitted a ticket, waiting for a usable reply.

#1153 - Got a packet bigger than 'max_allowed_packet' bytes on database import

Is this an issue my end or a server/database issue on JaguarPCs end? JPC tech support suggests I upload my database to "public_html"? Where is that located and is it secure?

If this is an issue on JPC's side I don't want to have to send my database to them every time just to import a database.

Has anyone encountered this before?

Cheers

[JPC-Martin]

Hello GherkinDilds,

We are sorry for the long delay , I have checked that your ticket has been already replied by Support tech after restoring the database.

Please confirm and update us in the related support ticket if you are still facing any issue so we look further and assist you accordingly.

[Ron]

Would someone please update this thread with the issue/solution when they have a few minutes?

[GherkinDilds]

Would someone please update this thread with the issue/solution when they have a few minutes?

Ron, from what I can tell. JPC increased the max_allowed packets slightly to 18M and then imported the database on my behalf.

[JPC-Les]

I sent a note to the techs to double check and see if we can post a confirmed solution.

[GherkinDilds]

Well the database was successfully imported by JPC, took about two days though. I was then able to import by Drupal website without much issue. I think the database import issue is fixed, though I don't exactly what JPC tech support did in order for the import to complete successfully. Details would be appreciated so that I can help myself in the future, if it is something I can do.

[JPC-Martin]

#1153 - Got a packet bigger than 'max_allowed_packet' bytes on database import

'max_allowed_packet' option specifies how large your MySQL Query/Data Packet can be, and NOT the Maximum File Size that MySQL can maintain.

A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client. When MySQL server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error 'following error: #1153 – Got a packet bigger than ‘max_allowed_packet’ bytes' and closes the connection.

MySQL server have max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:

Login to the server using SSH and connect to MySQL:
Mysql> mysqld --max_allowed_packet=16M

OR in other way you need to locate your mysql my.cnf config file and add or change the max allowed packet:
[mysqld]
max_allowed_packet = 50M

@ GherkinDilds: The tech who worked on your issue had increased value of 'max_allowed_packet' in /etc/my.cnf and restored the database.

[GherkinDilds]

Thanks for the reply, Martin.

I am new to the whole hosting thing. I am assuming the only way to access my.cnf is through SSH and not through FTP. I can't find a "/etc" directory when I go through FTP. Is this normal?

[JPC-Anoop]

Hi,

You have to SFTP to the server as root to access my.cnf. You can't access it via ftp or sftp using accounts other than root.

[Ron]

He says his sql file was 1.5M compressed... assuming a 9 TO 1 ratio a total fie size of about 15M ; he even dumped the file with --skip-extended-insert and the compressed size changed to 1.6m, but still failed. How could the problem be a single packet bigger than 16M? Unless he accidentally tried to import the old dump instead of the new dump.

Plus he said that his file specified 200M for max packet size. So I wonder why it just wasn't working for him.

Well glad you got it sorted.

[Ron]

PS On my shared server, the server reports 50M. According to MySQL manual, the memory is only allocated if needed. So unless we're just trying to protect for super-huge packets, could it be set arbitrarily high, not just to 18?

[GherkinDilds]

Yes, at least in the my.cnf for my localhost, max_allowed_packets was set to 200M, and the database file for import was 1.5M and 1.6M with extended-insert off.

[chining]

I get the same problem.

[JPC-Anoop]

Have you tried increasing the max_allowed_packet limit? If the issue still persist after increasing the limit, please open a ticket with support and we can take a look at it.