常见的errorlog报错

本文汇总了一些 mysql errorlog 有关的 case，仅供日后参考。

场景1：[Warning] Aborted connection

[Warning] Aborted connection xx to db: 'db' user: 'xxx' host: 'hostname' (Got an error reading communication packets)
[Warning] Aborted connection xx to db:'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets)
[Warning] Aborted connection 109 to db:'xxx' user: 'yy' host: '10.10.20.10' (Got an error writing communication packets)
[Warning] Got an error writing communication packets

解决方案：

• 1.client 会话链接未正常关闭，程序没有调用mysql_close()
• 2.空闲时间超过 wait_timeout 或 interactive_timeout 设置的秒数

set global interactive_timeout=20; 
set global wait_timeout=20;

• 3.查询数据包大小超过max_allowed_packet数值，造成链接中断
• 4.其他网络或者硬件层面的问题。比如网络连接超过 connect_timout

场景2：[Warning] InnoDB: Cannot add field

• 症状：查看 mysql_error.log 看到大量如下 warning

[Warning] InnoDB: Cannot add field `WBS_NAME` in table `i6000_cmdb`.`ci_t6031926` because after adding it, 
the row size is 8127 which is greater than maximum allowed size (8126) for a record on index leaf page.

• 原因：关于 Row Format，InnoDB默认使用Dynamic格式，MyISAM默认使用Compact格式。如果使用MyISAM引擎，需要手动设置Row Format为Redundant或Dynamic。上述报错的表正是采用了 Compact格式

#.测试数据
CREATE TABLE xxx (id int(11), name varchar(20) ) ENGINE=InnoDB ROW_FORMAT=COMPACT;

#.确认信息
SELECT table_schema,table_name,engine,row_format,table_rows,create_options 
FROM information_schema.tables 
where table_schema not in ('mysql','information_schema','performance_schema');

#.解决方案
ALTER TABLE xxx ROW_FORMAT=DYNAMIC;