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
