lepus监控myqsl redis的报错解决

lepus部署及mysql和redis的监控配置详情请见前两篇文章

mysql错误解决

mysql监控错误信息

2017-08-02 09:37:20 [WARNING] check mysql 192.168.1.222:10121 failure: sleep 3 seconds and check again../include/functions.py:45: Warning: Out of range value for column 'max_connect_errors' at row 1  curs.execute(sql,param)

./include/functions.py:47: Warning: Out of range value for column 'pid' at row 1  curs.execute(sql,param) ./include/functions.py:45: Warning: Out of range value for column 'connections' at row 1  curs.execute(sql,param)./include/functions.py:45: Warning: Out of range value for column 'max_connect_errors' at row 1  curs.execute(sql,param)./include/functions.py:45: Warning: Out of range value for column 'max_connect_errors' at row 1  curs.execute(sql,param)

http://blog.csdn.net/wlzjsj/article/details/76572805

源码阅读

mysql监控的源码是check_mysql.py

贴出根据出错信息的部分代码

def mysql_exec(sql,param):    try:        conn=MySQLdb.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=5,charset='utf8')        conn.select_db(dbname)        curs = conn.cursor()        if param <> '':            curs.execute(sql,param)        else:            #print "sql:" + str(sql)            #print "parm:" + str(param)            curs.execute(sql)        conn.commit()        curs.close()        conn.close()    except Exception,e:       print "mysql execute: " + str(e)       #import traceback       #traceback.print_exc()

注：#注释的是我的调试代码

打出错误信息后，

./include/functions.py:47: Warning: Out of range value for column 'pid' at row 1  curs.execute(sql,param)sql:insert into mysql_processlist(server_id,host,port,tags,pid,p_user,p_host,p_db,command,time,status,info) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)parm:(282L, u'192.xx.xx.xx', u'3306', u'46_3306', 4091675221L, u'common', u'192.xx.xx.xx:8647', u'dbname', u'Query', 2L, u'updating', u"DELETE FROM xxx WHERE EndDateTime<'2017-08-01T00:00:00' LIMIT 2500")

可见第一个错误pid的超出范围是mysql_processlist表的pid字段出的问题，我们看下这个表结构：

mysql> desc mysql_processlist;+-------------+--------------+------+-----+-------------------+----------------+| Field       | Type         | Null | Key | Default           | Extra          |+-------------+--------------+------+-----+-------------------+----------------+| id          | int(11)      | NO   | PRI | NULL              | auto_increment || server_id   | smallint(4)  | YES  | MUL | NULL              |                || host        | varchar(30)  | NO   |     | NULL              |                || port        | varchar(10)  | NO   |     | NULL              |                || tags        | varchar(50)  | NO   | MUL |                   |                || pid         | int(10)      | YES  |     | NULL              |                || p_user      | varchar(50)  | YES  |     | NULL              |                || p_host      | varchar(50)  | YES  |     | NULL              |                || p_db        | varchar(30)  | YES  |     | NULL              |                || command     | varchar(30)  | YES  |     | NULL              |                || time        | varchar(200) | NO   |     | 0                 |                || status      | varchar(50)  | YES  |     | NULL              |                || info        | text         | YES  |     | NULL              |                || create_time | timestamp    | YES  | MUL | CURRENT_TIMESTAMP |                |+-------------+--------------+------+-----+-------------------+----------------+14 rows in set (0.01 sec)

http://blog.csdn.net/wlzjsj/article/details/76572805
pid是int（10），我们知道int的长度是从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。

我们存储的位数已经超过了最大存储位，这里参考下mysql自带的information_schema.PROCESSLIST表，可以看出ID字段是以biginnt(4)存储的，bigint范围是从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。

我们需要把pid修改为bigint(4).

解决

mysql> alter table mysql_processlist modify pid bigint(4);

那接下来看其它错误，有了第一个思路，接下来基本可以判断都是表结构的问题了，代码里分别做了param为空和不为空的2种判断进行sql插入，所以需要修改2个地方，param不为空时调试结果：

sql:insert into mysql_status(server_id,host,port,tags,connect,role,uptime,version,max_connections,max_connect_errors,open_files_limit,table_open_cache,max_tmp_tables,max_heap_table_size,max_allowed_packet,open_files,open_tables,threads_connected,threads_running,threads_waits,threads_created,threads_cached,connections,aborted_clients,aborted_connects,connections_persecond,bytes_received_persecond,bytes_sent_persecond,com_select_persecond,com_insert_persecond,com_update_persecond,com_delete_persecond,com_commit_persecond,com_rollback_persecond,questions_persecond,queries_persecond,transaction_persecond,created_tmp_tables_persecond,created_tmp_disk_tables_persecond,created_tmp_files_persecond,table_locks_immediate_persecond,table_locks_waited_persecond,key_buffer_size,sort_buffer_size,join_buffer_size,key_blocks_not_flushed,key_blocks_unused,key_blocks_used,key_read_requests_persecond,key_reads_persecond,key_write_requests_persecond,key_writes_persecond,innodb_version,innodb_buffer_pool_instances,innodb_buffer_pool_size,innodb_doublewrite,innodb_file_per_table,innodb_flush_log_at_trx_commit,innodb_flush_method,innodb_force_recovery,innodb_io_capacity,innodb_read_io_threads,innodb_write_io_threads,innodb_buffer_pool_pages_total,innodb_buffer_pool_pages_data,innodb_buffer_pool_pages_dirty,innodb_buffer_pool_pages_flushed,innodb_buffer_pool_pages_free,innodb_buffer_pool_pages_misc,innodb_page_size,innodb_pages_created,innodb_pages_read,innodb_pages_written,innodb_row_lock_current_waits,innodb_buffer_pool_pages_flushed_persecond,innodb_buffer_pool_read_requests_persecond,innodb_buffer_pool_reads_persecond,innodb_buffer_pool_write_requests_persecond,innodb_rows_read_persecond,innodb_rows_inserted_persecond,innodb_rows_updated_persecond,innodb_rows_deleted_persecond,query_cache_hitrate,thread_cache_hitrate,key_buffer_read_rate,key_buffer_write_rate,key_blocks_used_rate,created_tmp_disk_tables_rate,connections_usage_rate,open_files_usage_rate,open_tables_usage_rate) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);parm:(278L, u'192.xx.xx.xx', u'3306', u'2x_3306', 1, 'master', u'8788268', u'5.5.28-log', u'2768', u'1000000', u'16384', u'1024', u'32', u'16777216', u'16777216', u'374', u'1024', u'260', u'3', 1L, u'7367813', u'122', u'3389257780', u'19583460', u'147622', 400, 158, 1388, 560, 17, 22, 0, 0, 0, 1633, 1633, 0, 4, 1, 0, 613, 0, u'536870912', u'8388608', u'4194304', u'0', u'0', u'428684', 0, 0, 0, 0, u'1.1.8', u'1', u'17179869184', u'ON', u'OFF', u'2', u'', u'0', u'200', u'4', u'4', 1048576, 1001590, 58340, 122667533, 92, 46894, 16384, 5966915, 10902064, 122667533, 0, 28, 558362, 0, 416, 279904, 19, 16, 0, '     0.00', '     1.00', '     1.00', '     0.62', '     1.00', '     0.02', '     0.09', '     0.02', '     1.00')./include/functions.py:47: Warning: Out of range value for column 'max_connect_errors' at row 1  curs.execute(sql,param)./include/functions.py:47: Warning: Out of range value for column 'connections' at row 1  curs.execute(sql,param)

解决：

mysql> alter table mysql_status modify max_connect_errors int(10);mysql> alter table mysql_status modify connections  bigint(4);

param为空时调试结果：

sql:insert into mysql_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_statusparam:--------./include/functions.py:47: Warning: Out of range value for column 'max_connect_errors' at row 1  curs.execute(sql,param)./include/functions.py:47: Warning: Out of range value for column 'max_connect_errors' at row 1  curs.execute(sql,param)./include/functions.py:47: Warning: Out of range value for column 'connections' at row 1  curs.execute(sql,param)./include/functions.py:47: Warning: Out of range value for column 'max_connect_errors' at row 1  curs.execute(sql,param)./include/functions.py:47: Warning: Out of range value for column 'max_connect_errors' at row 1  curs.execute(sql,param)./include/functions.py:47: Warning: Out of range value for column 'max_connect_errors' at row 1  curs.execute(sql,param)

手动运行sql：

mysql> insert into mysql_status_history SELECT *,LEFT(REPLACE(REPLACE(REPLACE(create_time,'-',''),' ',''),':',''),12) from mysql_status    -> ;Query OK, 10 rows affected, 12 warnings (0.00 sec)Records: 10  Duplicates: 0  Warnings: 12mysql> show warnings;+---------+------+--------------------------------------------------------------+| Level   | Code | Message                                                      |+---------+------+--------------------------------------------------------------+| Warning | 1264 | Out of range value for column 'max_connect_errors' at row 1  || Warning | 1264 | Out of range value for column 'max_connect_errors' at row 2  || Warning | 1264 | Out of range value for column 'max_connect_errors' at row 3  || Warning | 1264 | Out of range value for column 'max_connect_errors' at row 4  || Warning | 1264 | Out of range value for column 'max_connect_errors' at row 5  || Warning | 1264 | Out of range value for column 'max_connect_errors' at row 6  || Warning | 1264 | Out of range value for column 'max_connect_errors' at row 7  || Warning | 1264 | Out of range value for column 'connections' at row 7         || Warning | 1264 | Out of range value for column 'max_connect_errors' at row 8  || Warning | 1264 | Out of range value for column 'max_connect_errors' at row 9  || Warning | 1264 | Out of range value for column 'max_connect_errors' at row 10 || Warning | 1264 | Out of range value for column 'connections' at row 10        |+---------+------+--------------------------------------------------------------+12 rows in set (0.00 sec)

解决：

mysql> alter table mysql_status_history modify max_connect_errors int(10);Query OK, 51708 rows affected (4.82 sec)Records: 51708  Duplicates: 0  Warnings: 0mysql> alter table mysql_status_history modify  connections  bigint(4);Query OK, 51708 rows affected (3.74 sec)Records: 51708  Duplicates: 0  Warnings: 0

http://blog.csdn.net/wlzjsj/article/details/76572805

Redis错误信息

redis的错误信息很相似，基本不用怎么判断了。这里源码的调试就不贴出了：

root@s0110-gz:/usr/local/lepus# python check_redis.py2017-08-02 11:00:58 [INFO] check redis controller started../include/functions.py:45: Warning: Out of range value for column 'rdb_changes_since_last_save' at row 1  curs.execute(sql,param)./include/functions.py:45: Warning: Out of range value for column 'rdb_changes_since_last_save' at row 1  curs.execute(sql,param)./include/functions.py:45: Warning: Out of range value for column 'rdb_changes_since_last_save' at row 1  curs.execute(sql,param)./include/functions.py:45: Warning: Out of range value for column 'keyspace_hits' at row 1  curs.execute(sql,param)./include/functions.py:45: Warning: Out of range value for column 'rdb_changes_since_last_save' at row 1  curs.execute(sql,param)./include/functions.py:45: Warning: Out of range value for column 'rdb_changes_since_last_save' at row 1  curs.execute(sql,param)./include/functions.py:45: Warning: Out of range value for column 'rdb_changes_since_last_save' at row 1  curs.execute(sql,param)./include/functions.py:45: Warning: Out of range value for column 'rdb_changes_since_last_save' at row 1  curs.execute(sql,param)./include/functions.py:45: Warning: Out of range value for column 'rdb_changes_since_last_save' at row 1  curs.execute(sql,param)2017-08-02 11:00:59 [INFO] check redis controller finished.

解决

mysql> alter table redis_status modify rdb_changes_since_last_save int(10);Query OK, 15 rows affected (0.01 sec)Records: 15  Duplicates: 0  Warnings: 0mysql> alter table redis_status modify keyspace_hits bigint(4);Query OK, 15 rows affected (0.02 sec)Records: 15  Duplicates: 0  Warnings: 0mysql> alter table redis_status_history modify keyspace_hits bigint(4);Query OK, 187 rows affected (0.02 sec)Records: 187  Duplicates: 0  Warnings: 0mysql> alter table redis_status_history modify rdb_changes_since_last_save int(10);Query OK, 187 rows affected (0.02 sec)Records: 187  Duplicates: 0  Warnings: 0

http://blog.csdn.net/wlzjsj/article/details/76572805

剩余问题：

2017-08-02 11:28:36 [WARNING] check mysql 192.xx.x.xx:xx failure: -1 error totally whack2017-08-02 11:28:36 [WARNING] check mysql 192.xx.xx.xx:xx failure: sleep 3 seconds and check again.2017-08-02 11:28:36 [WARNING] check mysql 192.xx.xx.xx:xx failure: -1 error totally whack2017-08-02 11:28:36 [WARNING] check mysql 192.xx.1.xx:xxx failure: sleep 3 seconds and check again.2017-08-02 11:28:36 [WARNING] check mysql 192.x8.1.xxx:3306 failure: -1 error totally whack2017-08-02 11:28:36 [WARNING] check mysql 192.1xx.1.xx:3xx failure: sleep 3 seconds and check again.