Bug #77298 ERROR 2027 (HY000): Malformed packet while selecting data
Submitted: 10 Jun 2015 12:25 Modified: 10 Jun 2015 13:23
Reporter: Surender Sarwa Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.6.23 OS:Linux (3.14.27-25.47.amzn1.x86_64)
Assigned to: CPU Architecture:Any
Tags: Mysql select with limit

[10 Jun 2015 12:25] Surender Sarwa
Description:
Hi,

I am using MySQL 5.6.23 on Amazon server 3.14.27 (linux).

From past few days I am getting a very strange error. I couldn't find any resolution for this error and some posts shows that this is a bug and has been resolved in 5.5.

Below query is causing issues.

SELECT PA.categoryName AS catname, PF.productid, PA.ProductCatID, PA.SubCategoryName, 
PA.subcatid, COUNT(DISTINCT(PA.ProductId)) AS COUNT
 ,CASE WHEN PA.Product_Group_ID='' THEN PA.ProductID ELSE PA.Product_Group_ID END  AS 'Product_Group_ID', PA.color
FROM product_filters_cat6 PA 
INNER JOIN fc_allproductdata_cat6 PF ON (PF.ProductID=PA.ProductID AND PF.IsPremiumProduct=1 )
WHERE 1=1  
GROUP BY PA.subcatid 
LIMIT 41;

1 queries executed, 0 success, 1 errors, 0 warnings

Query: SELECT PA.categoryName AS catname, PF.productid, PA.ProductCatID, PA.SubCategoryName, PA.subcatid, COUNT(DISTINCT(PA.ProductId))...

Below is error on SQLYog
Error Code: 2000
Unknown MySQL error

Below is error on MySQL Command prompt
ERROR 2027 (HY000): Malformed packet

Execution Time : 0.316 sec
Transfer Time  : 0.001 sec
Total Time     : 0.317 sec

However if I change the limit to 40 then this is working fine.

SELECT PA.categoryName AS catname, PF.productid, PA.ProductCatID, PA.SubCategoryName, 
PA.subcatid, COUNT(DISTINCT(PA.ProductId)) AS COUNT
 ,CASE WHEN PA.Product_Group_ID='' THEN PA.ProductID ELSE PA.Product_Group_ID END  AS 'Product_Group_ID', PA.color
FROM product_filters_cat6 PA 
INNER JOIN fc_allproductdata_cat6 PF ON (PF.ProductID=PA.ProductID AND PF.IsPremiumProduct=1 )
WHERE 1=1  
GROUP BY PA.subcatid 
LIMIT 40;

14 rows in set (0.15 sec)

Please suggest where I am going wrong.

Below is my my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

server-id=922434

join_buffer_size=128K
read_rnd_buffer_size=128K
#query_cache_type = 1

max_heap_table_size=25M
lower_case_table_names=1
log_bin_trust_function_creators=1
skip_name_resolve

# skip_slave_start
slave_type_conversions="ALL_LOSSY,ALL_NON_LOSSY,ALL_SIGNED,ALL_UNSIGNED"
relay_log=mysql-relay
relay_log_index=relay-log-index

binlog_format=mixed

replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

max_connections=10000
#query_cache_size=500M
#query_cache_limit = 30M
#query_cache_type=1

table_open_cache=64
tmp_table_size=30M
thread_cache_size=9

innodb_flush_log_at_trx_commit=1
#innodb_buffer_pool_instances=5

# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000

# It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.
innodb_open_files=3000

innodb_file_per_table=1

event_scheduler=1

innodb_buffer_pool_size=10G
innodb_additional_mem_pool_size=64M
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_dump_now=1
innodb_buffer_pool_load_abort=0
innodb_buffer_pool_load_at_startup=1
innodb_doublewrite=1
innodb_fast_shutdown=1
innodb_io_capacity=500
innodb_io_capacity_max=2000
innodb_max_dirty_pages_pct=75
innodb_open_files=500
#innodb_read_io_threads=20
#innodb_write_io_threads=20
#innodb_thread_concurrency=9
innodb_table_locks=0

max_allowed_packet=256M
max_connect_errors=10000
table_definition_cache=256

# Specify the maximum size of a row-based binary log event, in bytes.
# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size=8K

# If the value of this variable is greater than 0, a replication slave synchronizes its master.info file to disk.
# (using fdatasync()) after every sync_master_info events.
sync_master_info=10000

# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
# (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log=10000

# If the value of this variable is greater than 0, a replication slave synchronizes its relay-log.info file to disk.
# (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

How to repeat:
Create below tables

CREATE TABLE `fc_allproductdata_cat6` (
  `Productid` int(11) NOT NULL,
  `ProductJSON` longtext NOT NULL,
  `ProductName` varchar(2000) NOT NULL,
  `Discount` decimal(18,2) unsigned NOT NULL DEFAULT '0.00',
  `DiscountPrice` decimal(18,2) NOT NULL,
  `stock` int(11) DEFAULT '1',
  `ranks` int(11) unsigned DEFAULT NULL,
  `Prank` int(11) unsigned DEFAULT NULL,
  `OrderNo` int(11) unsigned DEFAULT NULL,
  `PerUnitCost` decimal(18,2) unsigned DEFAULT NULL,
  `bestseller` int(11) DEFAULT '0',
  `createddate` datetime DEFAULT NULL,
  `URating` decimal(10,2) DEFAULT NULL,
  `IsPremiumProduct` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`Productid`),
  KEY `Discountprice` (`DiscountPrice`),
  KEY `Prank` (`Prank`),
  KEY `bestseller` (`bestseller`),
  KEY `ProductName` (`ProductName`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create Table

CREATE TABLE `product_filters_cat6` (
  `ProductID` int(11) NOT NULL,
  `discount` decimal(18,2) NOT NULL DEFAULT '0.00',
  `DiscountedPrice` decimal(18,2) NOT NULL,
  `Type1DataID` int(11) DEFAULT NULL,
  `Type2DataID` int(11) DEFAULT NULL,
  `Type3DataID` int(11) DEFAULT NULL,
  `Type4DataID` int(11) DEFAULT NULL,
  `Type5DataID` int(11) DEFAULT NULL,
  `Type6DataID` int(11) DEFAULT NULL,
  `Type7DataID` int(11) DEFAULT NULL,
  `Type8DataID` int(11) DEFAULT NULL,
  `Type9DataID` int(11) DEFAULT NULL,
  `Type10DataID` int(11) DEFAULT NULL,
  `productinfoid` int(11) NOT NULL,
  `SubCatID` int(11) NOT NULL,
  `BrandID` int(11) NOT NULL,
  `Color` varchar(100) DEFAULT NULL,
  `AgeFrom` decimal(18,2) DEFAULT NULL,
  `AgeTo` decimal(18,2) DEFAULT NULL,
  `MRP` decimal(18,2) NOT NULL,
  `IsActive` bit(1) DEFAULT NULL,
  `Product_Group_ID` varchar(50) DEFAULT '0',
  `BrandName` varchar(500) NOT NULL,
  `SubCategoryName` varchar(500) NOT NULL,
  `ProductCatID` int(11) NOT NULL,
  `OnSale` int(11) DEFAULT NULL,
  `CurrentStock` int(11) NOT NULL,
  `SiteType` int(11) NOT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `CategoryName` varchar(500) NOT NULL,
  `stock` int(11) NOT NULL,
  `brandJSon` varchar(1000) DEFAULT NULL,
  `subcategoryJSon` varchar(1000) DEFAULT NULL,
  `DiscountedPriceJSon` varchar(500) DEFAULT NULL,
  `DiscountJSon` varchar(500) DEFAULT NULL,
  `AgeFromJSon` varchar(500) DEFAULT NULL,
  `AgeToJSon` varchar(500) DEFAULT NULL,
  `Type1DataIDJSon` varchar(1000) DEFAULT NULL,
  `Type2DataIDJSon` varchar(1000) DEFAULT NULL,
  `Type3DataIDJSon` varchar(1000) DEFAULT NULL,
  `Type4DataIDJSon` varchar(1000) DEFAULT NULL,
  `Type5DataIDJSon` varchar(1000) DEFAULT NULL,
  `Type6DataIDJSon` varchar(1000) DEFAULT NULL,
  `Type7DataIDJSon` varchar(1000) DEFAULT NULL,
  `Type8DataIDJSon` varchar(1000) DEFAULT NULL,
  `Type9DataIDJSon` varchar(1000) DEFAULT NULL,
  `Type10DataIDJSon` varchar(1000) DEFAULT NULL,
  `offertype` varchar(100) DEFAULT NULL,
  `Size` varchar(500) DEFAULT NULL,
  `rating` varchar(500) DEFAULT NULL,
  `CouponOffer` varchar(20) DEFAULT NULL,
  `CouponOfferTitle` varchar(2000) DEFAULT NULL,
  `BestScore` decimal(10,2) DEFAULT NULL,
  `NewArrivals` tinyint(4) DEFAULT NULL,
  `ColorName` varchar(200) DEFAULT NULL,
  `ColorID` int(10) DEFAULT NULL,
  `MeasurementName` varchar(200) DEFAULT NULL,
  `MeasurementID` int(10) DEFAULT NULL,
  `MaterialName` varchar(200) DEFAULT NULL,
  `MaterialID` int(10) DEFAULT NULL,
  `SkillName` varchar(200) DEFAULT NULL,
  `SkillID` int(10) DEFAULT NULL,
  `Discount_ID` varchar(10) DEFAULT NULL,
  `Price_ID` varchar(10) DEFAULT NULL,
  `Age_ID` varchar(10) DEFAULT NULL,
  KEY `IDX_ProductID` (`ProductID`) USING BTREE,
  KEY `IDX_BrandID` (`BrandID`) USING BTREE,
  KEY `IDX_SubCatID` (`SubCatID`) USING BTREE,
  KEY `IDX_Product_Group_ID` (`Product_Group_ID`) USING BTREE,
  KEY `IDX_discount` (`discount`) USING BTREE,
  KEY `IDX_CurrentStock` (`CurrentStock`) USING BTREE,
  KEY `IDX_AgeFrom` (`AgeFrom`) USING BTREE,
  KEY `IDX_AgeTo` (`AgeTo`) USING BTREE,
  KEY `Color` (`Color`),
  KEY `DiscountedPrice` (`DiscountedPrice`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

There are around a million records in both the tables
[10 Jun 2015 12:58] MySQL Verification Team
Thank you for the report.
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.

Also, see Bug #76996
[10 Jun 2015 13:23] Surender Sarwa
Dear Umesh,

Many thanks for the fix. Issue resolved after setting 
read_rnd_buffer_size=256K