3

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.

  • So it sounds like it works with smaller datasets, could it be how TeamCity is setup? I ran across this in research: [stackoverflow.com/questions/331755/… – SQLHound May 27 '15 at 16:04
  • I don't think it would be to do with team city since I get an error when using HeidiSQL (which is a SQL client) to do the query and that is not touching any Teamcity code at all. It is possible it is something to do with the data that teamcity stores but I think it more likely that there is some setting to do with maximum recordset size or something like that that I am missing (or a bug that only happens when a large dataset is returned). The linked question doesn't seem to help at all - its more about port conflicts on the web server part than db problems. – Chris May 27 '15 at 16:41
4

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

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:

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

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

GIVE IT A TRY !!!

  • I would also make sure you are using the latest version of HeidiSQL, especially using the latest libmysql.dll. You may need to rollback to previous version of HeidiSQL. – RolandoMySQLDBA May 27 '15 at 17:29
  • That looks like loads of useful information to be getting on with. One thing I should have noted is that it is not the specific 14562nd row that is causing the problem. If I order it differently it is still the same number of rows that causes a problem. I'll look through the links you've provided though, they look really helpful. :) – Chris May 28 '15 at 8:07
  • As an additional note if I use LIMIT 14561 then it works but if I use LIMIT 14561,1 it fails. This suggests its the amount of data in memory that is causing the problem . Likewise removing the order by clause allows me to fetch more rows and changing the order by to a larger field (eg a VARCHAR(255) instead of a bigint) then I can fetch less data. It really seems like it is hitting some size limit but I've upped the max_allowed_packet to 1G (and no single blob data should be that big anyway). Is there somewhere else that memory is limited in the server per request or something? – Chris May 28 '15 at 9:20
  • In case you are interested I found a solution. Sadly none of the things you suggested seemed to help. On looking the datatables combined were less than 10M on disk. A comparison of config files between two servers allowed me to find some config values to update that seems to have fixed it though I will admit to not having a clue why. :) Thanks for the help though. – Chris May 28 '15 at 10:28
  • Don't you need an extra step when changing innodb_log_file_size? – Rick James Jun 7 '15 at 14:59
1

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.

  • Good find! I've taken the liberty of trying to update your answer with the actual bug explanation from that report. Given that read_rnd_buffer_size was one of the values I updated I am pretty sure that this is the bug I was running into. :) – Chris Aug 19 '15 at 21:18
0

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).

  • 2
    ZERO for read_buffer_size, etc??? Yikes. – Rick James Jun 7 '15 at 15:04
  • I have to admit I assumed those zeros were some kind of "use default value" type zeros. Am I to take it that they actually said "use no buffers"? In my defence I didn't set these up! ;-) – Chris Jun 7 '15 at 16:23
  • 2
    The manual is unclear on the issue. It does state a "minimum" value. Perhaps 0 is replaced by the minimum. You can discover the active value by doing SHOW VARIABLES LIKE '%buffer_size';. – Rick James Jun 7 '15 at 16:40
  • Thanks Rick. I'm not going to touch the server again now its working though! Curious though I am possibly breaking it in the name of SCIENCE! is not on my todo list today. :) – Chris Jun 8 '15 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.