页面无法显示查询数据

[wangfan618]

大佬，按文档部署遇到以下问题。
运维监控主机环境：centos7.8 mysql5.7.40 php5.4.16
被监控mysql主机环境：centos7.8 mysql5.7.25 percona-toolkit-3.0.12 pt-query-digest 3.0.12

目前，运维监控主机的Mysql sql_db库中mysql_slow_query_review_history，mysql_slow_query_review 表，显示已采集到被监控主机mysql-slow相关记录，但在直接 select sample,db_max from mysql_slow_query_review_history where checksum=${checksum} limit 1 查询报错 ，参考以前issues，检查对于表字段checksum已是varchar类型。 最头疼是，前端页面也查询，展示不到记录。
看http 日志报以下错误。
[Tue Feb 21 09:49:48.523692 2023] [:error] [pid 10716] [client X.X.X.X:52279] PHP Notice: Undefined index: action in /var/www/html/slowquery/slowquery.php on line 4, referer: http://X.X.X.X:/slowquery/slowquery.php
[Tue Feb 21 09:49:48.525082 2023] [:error] [pid 10716] [client X.X.X.X:52279] PHP Notice: Undefined index: dbname in /var/www/html/slowquery/slowquery.php on line 61, referer: http://X.X.X.X:/slowquery/slowquery.php
[Tue Feb 21 09:49:48.525151 2023] [:error] [pid 10716] [client X.X.X.X:52279] PHP Notice: A session had already been started - ignoring session_start() in /var/www/html/slowquery/slowquery.php on line 79, referer: http://10.38.1.200/slowquery/slowquery.php
[Tue Feb 21 09:49:48.525344 2023] [:error] [pid 10716] [client X.X.X.X::52279] PHP Notice: A session had already been started - ignoring session_start() in /var/www/html/slowquery/slowquery.php on line 109, referer: http://10.38.1.200/slowquery/slowquery.php
[Tue Feb 21 09:49:48.526400 2023] [:error] [pid 10716] [client X.X.X.X::52279] PHP Notice: Undefined index: page in /var/www/html/slowquery/slowquery.php on line 113, referer: http://X.X.X.X:/slowquery/slowquery.php
[Tue Feb 21 09:49:48.530640 2023] [:error] [pid 10716] [client X.X.X.X::52279] PHP Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /var/www/html/slowquery/slowquery.php on line 152, referer: http://10.38.1.200/slowquery/slowquery.php

[hcymysql]

`SELECT
r.checksum,
r.fingerprint,
h.db_max,
h.user_max,
r.last_seen,
SUM(h.ts_cnt) AS ts_cnt,
ROUND(MIN(h.Query_time_min), 3) AS Query_time_min,
ROUND(MAX(h.Query_time_max), 3) AS Query_time_max,
ROUND(
SUM(h.Query_time_sum) / SUM(h.ts_cnt),
3
) AS Query_time_avg,
r.sample
FROM
mysql_slow_query_review AS r
JOIN mysql_slow_query_review_history AS h
ON r.checksum = h.checksum
WHERE h.db_max = 'yourDB'
AND r.last_seen >= SUBDATE(NOW(), INTERVAL 31 DAY)
GROUP BY r.checksum
ORDER BY r.last_seen DESC,
ts_cnt DESC
LIMIT 10 ;

`

你执行这条SQL，有数据返回结果吗?

[wangfan618]

直接在数据库里查询 报‘Unknown column 'h.db_max' in 'field list'’

[hcymysql]

直接在数据库里查询 报‘Unknown column 'h.db_max' in 'field list'’

表结构不对引起的。

进入到slowquery/slowquery_table_schema目录下

导入dbinfo_table_schema.sql和slowquery_table_schema.sql表结构文件到你的运维管理机MySQL里。

例：

mysql -uroot -p123456 sql_db < ./dbinfo_table_schema.sql

mysql -uroot -p123456 sql_db < ./slowquery_table_schema.sql 

[hcymysql]

CREATE TABLE mysql_slow_query_review (
  checksum varchar(200) NOT NULL,
  fingerprint text NOT NULL,
  sample text NOT NULL,
  first_seen datetime DEFAULT NULL,
  last_seen datetime DEFAULT NULL,
  reviewed_by varchar(20) DEFAULT NULL,
  reviewed_on datetime DEFAULT NULL,
  comments text DEFAULT NULL,
  PRIMARY KEY (checksum),
  KEY idx_last_seen (last_seen) USING BTREE
) ENGINE=InnoDB;

CREATE TABLE mysql_slow_query_review_history (
  serverid_max int(4) NOT NULL,
  db_max varchar(100) DEFAULT NULL,
  user_max varchar(100) DEFAULT NULL,
  checksum varchar(200) NOT NULL,
  sample text NOT NULL,
  ts_min datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  ts_max datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  ts_cnt float DEFAULT NULL,
  Query_time_sum float DEFAULT NULL,
  Query_time_min float DEFAULT NULL,
  Query_time_max float DEFAULT NULL,
  Query_time_pct_95 float DEFAULT NULL,
  Query_time_stddev float DEFAULT NULL,
  Query_time_median float DEFAULT NULL,
  Lock_time_sum float DEFAULT NULL,
  Lock_time_min float DEFAULT NULL,
  Lock_time_max float DEFAULT NULL,
  Lock_time_pct_95 float DEFAULT NULL,
  Lock_time_stddev float DEFAULT NULL,
  Lock_time_median float DEFAULT NULL,
  Rows_sent_sum float DEFAULT NULL,
  Rows_sent_min float DEFAULT NULL,
  Rows_sent_max float DEFAULT NULL,
  Rows_sent_pct_95 float DEFAULT NULL,
  Rows_sent_stddev float DEFAULT NULL,
  Rows_sent_median float DEFAULT NULL,
  Rows_examined_sum float DEFAULT NULL,
  Rows_examined_min float DEFAULT NULL,
  Rows_examined_max float DEFAULT NULL,
  Rows_examined_pct_95 float DEFAULT NULL,
  Rows_examined_stddev float DEFAULT NULL,
  Rows_examined_median float DEFAULT NULL,
  Rows_affected_sum float DEFAULT NULL,
  Rows_affected_min float DEFAULT NULL,
  Rows_affected_max float DEFAULT NULL,
  Rows_affected_pct_95 float DEFAULT NULL,
  Rows_affected_stddev float DEFAULT NULL,
  Rows_affected_median float DEFAULT NULL,
  Rows_read_sum float DEFAULT NULL,
  Rows_read_min float DEFAULT NULL,
  Rows_read_max float DEFAULT NULL,
  Rows_read_pct_95 float DEFAULT NULL,
  Rows_read_stddev float DEFAULT NULL,
  Rows_read_median float DEFAULT NULL,
  Merge_passes_sum float DEFAULT NULL,
  Merge_passes_min float DEFAULT NULL,
  Merge_passes_max float DEFAULT NULL,
  Merge_passes_pct_95 float DEFAULT NULL,
  Merge_passes_stddev float DEFAULT NULL,
  Merge_passes_median float DEFAULT NULL,
  InnoDB_IO_r_ops_min float DEFAULT NULL,
  InnoDB_IO_r_ops_max float DEFAULT NULL,
  InnoDB_IO_r_ops_pct_95 float DEFAULT NULL,
  InnoDB_IO_r_ops_stddev float DEFAULT NULL,
  InnoDB_IO_r_ops_median float DEFAULT NULL,
  InnoDB_IO_r_bytes_min float DEFAULT NULL,
  InnoDB_IO_r_bytes_max float DEFAULT NULL,
  InnoDB_IO_r_bytes_pct_95 float DEFAULT NULL,
  InnoDB_IO_r_bytes_stddev float DEFAULT NULL,
  InnoDB_IO_r_bytes_median float DEFAULT NULL,
  InnoDB_IO_r_wait_min float DEFAULT NULL,
  InnoDB_IO_r_wait_max float DEFAULT NULL,
  InnoDB_IO_r_wait_pct_95 float DEFAULT NULL,
  InnoDB_IO_r_wait_stddev float DEFAULT NULL,
  InnoDB_IO_r_wait_median float DEFAULT NULL,
  InnoDB_rec_lock_wait_min float DEFAULT NULL,
  InnoDB_rec_lock_wait_max float DEFAULT NULL,
  InnoDB_rec_lock_wait_pct_95 float DEFAULT NULL,
  InnoDB_rec_lock_wait_stddev float DEFAULT NULL,
  InnoDB_rec_lock_wait_median float DEFAULT NULL,
  InnoDB_queue_wait_min float DEFAULT NULL,
  InnoDB_queue_wait_max float DEFAULT NULL,
  InnoDB_queue_wait_pct_95 float DEFAULT NULL,
  InnoDB_queue_wait_stddev float DEFAULT NULL,
  InnoDB_queue_wait_median float DEFAULT NULL,
  InnoDB_pages_distinct_min float DEFAULT NULL,
  InnoDB_pages_distinct_max float DEFAULT NULL,
  InnoDB_pages_distinct_pct_95 float DEFAULT NULL,
  InnoDB_pages_distinct_stddev float DEFAULT NULL,
  InnoDB_pages_distinct_median float DEFAULT NULL,
  QC_Hit_cnt float DEFAULT NULL,
  QC_Hit_sum float DEFAULT NULL,
  Full_scan_cnt float DEFAULT NULL,
  Full_scan_sum float DEFAULT NULL,
  Full_join_cnt float DEFAULT NULL,
  Full_join_sum float DEFAULT NULL,
  Tmp_table_cnt float DEFAULT NULL,
  Tmp_table_sum float DEFAULT NULL,
  Tmp_table_on_disk_cnt float DEFAULT NULL,
  Tmp_table_on_disk_sum float DEFAULT NULL,
  Filesort_cnt float DEFAULT NULL,
  Filesort_sum float DEFAULT NULL,
  Filesort_on_disk_cnt float DEFAULT NULL,
  Filesort_on_disk_sum float DEFAULT NULL,
  PRIMARY KEY (checksum,ts_min,ts_max),
  KEY idx_serverid_max (serverid_max) USING BTREE,
  KEY idx_query_time_max (Query_time_max) USING BTREE,
  KEY idx_db_ts (db_max,ts_max)
) ENGINE=InnoDB;

[wangfan618]

感谢大佬，已经可以正常查询！