lepus监控myqsl redis的报错解决

最新推荐文章于 2023-07-11 20:55:31 发布

_lynnwu

最新推荐文章于 2023-07-11 20:55:31 发布

阅读量2.4k

点赞数 2

分类专栏： ----mysql监控

本文链接：https://blog.csdn.net/wlzjsj/article/details/76572805

版权

----mysql监控专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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_status
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 '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: 12

mysql> 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: 0

mysql> 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.py
2017-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: 0

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

mysql> 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 whack
2017-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 whack
2017-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 whack
2017-08-02 11:28:36 [WARNING] check mysql 192.1xx.1.xx:3xx failure: sleep 3 seconds and check again.