ERROR:2027 (HY000): Malformed packet

MySQL报错2027：Malformed packet问题解析及解决方案

最新推荐文章于 2023-09-18 17:30:50 发布

原创最新推荐文章于 2023-09-18 17:30:50 发布 · 3.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #mysql bug

本文详细介绍了在执行MySQL存储过程时遇到的'ERROR:2027 (HY000): Malformed packet'错误。通过分析涉及的函数，如init_rr_cache和Protocol_classic::get_command，发现错误可能源自read_rnd_buff_size设置过小。解决方法是调整该参数的大小，以避免包格式错误。

分享bug:

表象：
ERROR:2027 (HY000): Malformed packet

操作内容：
存储过程：
CREATE DEFINER=`root`@`%` PROCEDURE `DELETED_TASK_NUMBER`()
BEGIN
declare nowDate char(35);
declare phonenumber char(25);
declare phonesum int(15);
declare DONE char(5);
declare continue handler for not found set DONE = FALSE;
SET SQL_SAFE_UPDATES = 0;
set nowDate = concat('crm_task_number_',date_format(now(),'%Y%m'));
select count(*) into phonesum from a where task_id in (select task_id from b where is_deleted = 1) ;
select phonesum;
set @create_number_table = CONCAT('create table if not exists ',nowDate,' like a');
prepare create_table from @create_number_table;
execute create_table;
deallocate prepare create_table;
while (phonesum >=0 ) DO
   set @insert_number_table = CONCAT('insert into ',nowDate,' (select * from a where task_id in (select task_id from a where is_deleted = 1) order by number_id limit 100000)');
   prepare insert_table from @insert_number_table;
   execute insert_table;
   deallocate prepare insert_table;
   select sleep(5);
delete from crm_task_number where task_id in (select task_id from a where is_deleted = 1) order by number_id limit 100000;
set phonesum=phonesum -100000;
end while;
END

涉及到的函数：
#init_rr_cache:临时文件初始化缓存
static int init_rr_cache(THD *thd, READ_RECORD *info)
{
uint rec_cache_size;
DBUG_ENTER("init_rr_cache");

READ_RECORD info_copy= *info;
info->struct_length= 3+MAX_REFLENGTH;
info->reclength= ALIGN_SIZE(info->table->s->reclength+1);
if (info->reclength < info->struct_length)
info->reclength= ALIGN_SIZE(info->struct_length);

info->error_offset= info->table->s->reclength;
info->cache_records= (thd->variables.read_rnd_buff_size /
(info->reclength+info->struct_length));
rec_cache_size= info->cache_records*info->reclength;
info->rec_cache_size= info->cache_records*info->ref_length;

if (info->cache_records <= 2 ||
!(info->cache=(uchar*) my_malloc(key_memory_READ_RECORD_cache,
rec_cache_size+info->cache_records*
info->struct_length,
MYF(0))))
{
*info= info_copy;
DBUG_RETURN(1);
}
DBUG_PRINT("info",("Allocated buffert for %d records",info->cache_records));
info->read_positions=info->cache+rec_cache_size;
info->cache_pos=info->cache_end=info->cache;
DBUG_RETURN(0);
} /* init_rr_cache */

#当info->cache_records <= 2 报错

#
int Protocol_classic::get_command(COM_DATA *com_data, enum_server_command *cmd)
{
// read packet from the network
if(int rc= read_packet())
return rc;

/*
'packet_length' contains length of data, as it was stored in packet
header. In case of malformed header, my_net_read returns zero.
If packet_length is not zero, my_net_read ensures that the returned
number of bytes was actually read from network.
There is also an extra safety measure in my_net_read:
it sets packet[packet_length]= 0, but only for non-zero packets.
*/
if (packet_length == 0) /* safety */
{
/* Initialize with COM_SLEEP packet */
raw_packet[0]= (uchar) COM_SLEEP;
packet_length= 1;
}
/* Do not rely on my_net_read, extra safety against programming errors. */
raw_packet[packet_length]= '\0'; /* safety */

*cmd= (enum enum_server_command) raw_packet[0];

if (*cmd >= COM_END)
*cmd= COM_END; // Wrong command

DBUG_ASSERT(packet_length);
// Skip 'command'
packet_length--;
raw_packet++;

return parse_packet(com_data, *cmd);
}

#函数：parse_packet

bool Protocol_classic::parse_packet(union COM_DATA *data,
enum_server_command cmd)
{
switch(cmd)
{
case COM_INIT_DB:
{
data->com_init_db.db_name= reinterpret_cast<const char*>(raw_packet);
data->com_init_db.length= packet_length;
break;
}
case COM_REFRESH:
{
if (packet_length < 1)
goto malformed;
data->com_refresh.options= raw_packet[0];
break;
}
case COM_SHUTDOWN:
{
data->com_shutdown.level= packet_length == 0 ?
SHUTDOWN_DEFAULT : (enum mysql_enum_shutdown_level) raw_packet[0];
break;
}
case COM_PROCESS_KILL:
{
if (packet_length < 4)
goto malformed;
data->com_kill.id= (ulong) uint4korr(raw_packet);
break;
}
case COM_SET_OPTION:
{
if (packet_length < 2)
goto malformed;
data->com_set_option.opt_command= uint2korr(raw_packet);
break;
}
case COM_STMT_EXECUTE:
{
if (packet_length < 9)
goto malformed;
data->com_stmt_execute.stmt_id= uint4korr(raw_packet);
data->com_stmt_execute.flags= (ulong) raw_packet[4];
/* stmt_id + 5 bytes of flags */
/*
FIXME: params have to be parsed into an array/structure
by protocol too
*/
data->com_stmt_execute.params= raw_packet + 9;
data->com_stmt_execute.params_length= packet_length - 9;
break;
}
case COM_STMT_FETCH:
{
if (packet_length < 8)
goto malformed;
data->com_stmt_fetch.stmt_id= uint4korr(raw_packet);
data->com_stmt_fetch.num_rows= uint4korr(raw_packet + 4);
break;
}
case COM_STMT_SEND_LONG_DATA:
{
#ifndef EMBEDDED_LIBRARY
if (packet_length < MYSQL_LONG_DATA_HEADER)
goto malformed;
#endif
data->com_stmt_send_long_data.stmt_id= uint4korr(raw_packet);
data->com_stmt_send_long_data.param_number= uint2korr(raw_packet + 4);
data->com_stmt_send_long_data.longdata= raw_packet + 6;
data->com_stmt_send_long_data.length= packet_length - 6;
break;
}
case COM_STMT_PREPARE:
{
data->com_stmt_prepare.query= reinterpret_cast<const char*>(raw_packet);
data->com_stmt_prepare.length= packet_length;
break;
}
case COM_STMT_CLOSE:
{
if (packet_length < 4)
goto malformed;

data->com_stmt_close.stmt_id= uint4korr(raw_packet);
break;
}
case COM_STMT_RESET:
{
if (packet_length < 4)
goto malformed;

data->com_stmt_reset.stmt_id= uint4korr(raw_packet);
break;
}
case COM_QUERY:
{
data->com_query.query= reinterpret_cast<const char*>(raw_packet);
data->com_query.length= packet_length;
break;
}
case COM_FIELD_LIST:
{
/*
We have name + wildcard in packet, separated by endzero
*/
data->com_field_list.table_name= raw_packet;
uint len= data->com_field_list.table_name_length=
strend((char *)raw_packet) - (char *)raw_packet;
if (len >= packet_length || len > NAME_LEN)
goto malformed;
data->com_field_list.query= raw_packet + len + 1;
data->com_field_list.query_length= packet_length - len;
break;
}
default:
break;
}