SQL Error (2027): Malformed packet

Question

I have a mysql database (Version 5.6.23-log running on windows) that is set up for use with Teamcity. When running a command against it using HeidiSQL I am getting an error:

SQL Error (2027): Malformed packet

This error only occurs if the result set is more than a certain number of rows (using LIMIT 14561 rows works, 14562 gives this error).

I believe this error is not due to Heidi since TeamCity is failing to start when running the same command.

I assume it is something to do with the data size becoming too large and something failing but I can't find any way to identify more specifically what the problem is and thus how to fix it.

If anybody can tell me what I can do to prevent this problem or at the very least how I might be able to better diagnose this problem I'd be most grateful.

Answer 1

My guess would be that you have TEXT/BLOB data and that the row data in the 14562 row is bigger than your current MySQL Packet, which is sized by max_allowed_packet.

MySQL Perspective

You need to reserve more space for max_allowed_packet

• Jul 29, 2013 : Max_packet_allowed setting for Windows 7
• Jul 03, 2013 : What max_allowed_packet is big enough, and why do I need to change it?
• Jun 12, 2012 : MySQL Error Reading Communication Packets

Your InnoDB Redo Logs (sized by innodb_log_file_size) may need to be increased as well. I first learned of it (in relation to the MySQL Packet) from ServerFault. I refer to that ServerFault post in some of my posts:

• Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error
• Aug 01, 2011 : How does max_allowed_packet affect the backup and restore of a database?

I would max out the max_allowed_packet to 1G and resize your redo logs to 1.5G

[mysqld]
max_allowed_packet = 1G
innodb_log_file_size = 1536M

Next, run this to purge all transactions out of the redo logs:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Then, restart mysql (which is required).

Note to everyone running MySQL 5.5 and prior

MySQL 5.6 will handle resizing logs for you when restarting. Previous versions of MySQL require that you do that manually. Your steps after changing my.cnf and setting innodb_fast_shutdown to 0, you would do the following

service mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
service mysql start

HeidiSQL Perspective

One very common complaint is Error 2027, particularly with using LOAD DATA INFILE

Some have suggested using older versions of libmysql.dll

• HeidiSQL 8.1 released
• sql error(2007) Malformed packet

Some suspect a character set issue : Error Code: 2027 Malformed packet

GIVE IT A TRY !!!

Answer 2

None of the above advices were helpful in my case. Turns out it was a bug in MySQL and not a wrong configuration as suggested in the other answers. Changing max_allowed_packet etc had no effect whatsoever.

Then I found this: https://bugs.mysql.com/bug.php?id=77298

"This is duplicate of internal bug Bug#20895852 which is fixed. Noted in 5.6.25, 5.7.8, 5.8.0 changelogs."

For small values of the read_rnd_buffer_size system variable, internal caching of temporary results could fail and cause query execution failure.

I went from 5.6.22 to 5.6.26 and the issue went away without any configuration changes.

All I did was

brew upgrade mysql

and restart the server in a new terminal tab using

mysql.server restart

That was all.

Answer 3

Same issue haunted my team for a while, and there's very little useful information about it on the web. We spent a lot of time troubleshooting the issue - and FINALLY found solution that solved it (at least - for our team)!

We found that Django sets "charset" option to "utf8" for database connections by default. During troubleshooting we used two separate database connection objects: one created for us by Django, and another - created manually using direct _mysql.connect() command. When we executed same exact query using both connection objects - the one created by Django resulted in "django.db.utils.OperationalError: (2027, 'Malformed packet')" (which is exactly what we were getting in our API), but the second connection (manual) - worked without any issues. Further comparison of the two connection objects (we actually had to use Python debugger "pdb" and set breakpoint within django.db.backends.mysql.base.py for that) - revealed that Django creates connection by passing "charset":"utf8", while manual _mysql connection - uses "latin1". As soon as we added "charset":"latin1" to DATABASES["default"]["OPTIONS"] within our settings.py - this error disappeared.

To summarize, solution (for us) was to explicitly set "charset":"latin1" within "OPTIONS" config section of DATABASES configuration - for every database alias. I can not say for sure that this will work for everyone who experiences this error - but it certainly works for us.

In general, any MySQL connection that uses "utf8" charset would perform the same way (generate this error); replacing that with "latin1" should solve it.

Answer 4

In the end I found that if I dumped the data out of one database and into another on a different machine that things seemed to work. I thus decided to do a compare of the two config files and update values that were different. In doing so I updated the following:

• tmp_table_size from 5M to 19M
• myisam_sort_buffer_size from 8M to 30M
• read_buffer_size from 0 to 59K
• read_rnd_buffer_size from 0 to 256K
• sort_buffer_size from 0 to 256K
• query_cache_type from 1 to 0

Doing this fixed my issues. I'm sure not all of them are needed so I would be happy for somebody to explain why this fixed my problem in a better answer (and will gladly transfer acceptance to them in that case).