server_status_monitor.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Server status monitor feature
   5  *
   6  * @package PhpMyAdmin
   7  */
   8
   9 require_once 'libraries/common.inc.php';
  10 require_once 'libraries/server_common.inc.php';
  11 require_once 'libraries/ServerStatusData.class.php';
  12 require_once 'libraries/server_status_monitor.lib.php';
  13 if (PMA_DRIZZLE) {
  14     $server_master_status = false;
  15     $server_slave_status = false;
  16 } else {
  17     include_once 'libraries/replication.inc.php';
  18     include_once 'libraries/replication_gui.lib.php';
  19 }
  20
  21 /**
  22  * Ajax request
  23  */
  24 if (isset($_REQUEST['ajax_request']) && $_REQUEST['ajax_request'] == true) {
  25     // Send with correct charset
  26     header('Content-Type: text/html; charset=UTF-8');
  27
  28     // real-time charting data
  29     if (isset($_REQUEST['chart_data'])) {
  30         switch($_REQUEST['type']) {
  31         case 'chartgrid': // Data for the monitor
  32             $ret = json_decode($_REQUEST['requiredData'], true);
  33             $statusVars = array();
  34             $serverVars = array();
  35             $sysinfo = $cpuload = $memory = 0;
  36             $pName = '';
  37
  38             /* Accumulate all required variables and data */
  39             // For each chart
  40             foreach ($ret as $chart_id => $chartNodes) {
  41                 // For each data series
  42                 foreach ($chartNodes as $node_id => $nodeDataPoints) {
  43                     // For each data point in the series (usually just 1)
  44                     foreach ($nodeDataPoints as $point_id => $dataPoint) {
  45                         $pName = $dataPoint['name'];
  46
  47                         switch ($dataPoint['type']) {
  48                         /* We only collect the status and server variables here to
  49                          * read them all in one query,
  50                          * and only afterwards assign them.
  51                          * Also do some white list filtering on the names
  52                         */
  53                         case 'servervar':
  54                             if (! preg_match('/[^a-zA-Z_]+/', $pName)) {
  55                                 $serverVars[] = $pName;
  56                             }
  57                             break;
  58
  59                         case 'statusvar':
  60                             if (! preg_match('/[^a-zA-Z_]+/', $pName)) {
  61                                 $statusVars[] = $pName;
  62                             }
  63                             break;
  64
  65                         case 'proc':
  66                             $result = $GLOBALS['dbi']->query('SHOW PROCESSLIST');
  67                             $ret[$chart_id][$node_id][$point_id]['value']
  68                                 = $GLOBALS['dbi']->numRows($result);
  69                             break;
  70
  71                         case 'cpu':
  72                             if (!$sysinfo) {
  73                                 include_once 'libraries/sysinfo.lib.php';
  74                                 $sysinfo = PMA_getSysInfo();
  75                             }
  76                             if (!$cpuload) {
  77                                 $cpuload = $sysinfo->loadavg();
  78                             }
  79
  80                             if (PMA_getSysInfoOs() == 'Linux') {
  81                                 $ret[$chart_id][$node_id][$point_id]['idle']
  82                                     = $cpuload['idle'];
  83                                 $ret[$chart_id][$node_id][$point_id]['busy']
  84                                     = $cpuload['busy'];
  85                             } else {
  86                                 $ret[$chart_id][$node_id][$point_id]['value']
  87                                     = $cpuload['loadavg'];
  88                             }
  89
  90                             break;
  91
  92                         case 'memory':
  93                             if (!$sysinfo) {
  94                                 include_once 'libraries/sysinfo.lib.php';
  95                                 $sysinfo = PMA_getSysInfo();
  96                             }
  97                             if (!$memory) {
  98                                 $memory  = $sysinfo->memory();
  99                             }
 100
 101                             $ret[$chart_id][$node_id][$point_id]['value']
 102                                 = $memory[$pName];
 103                             break;
 104                         } /* switch */
 105                     } /* foreach */
 106                 } /* foreach */
 107             } /* foreach */
 108
 109             // Retrieve all required status variables
 110             if (count($statusVars)) {
 111                 $statusVarValues = $GLOBALS['dbi']->fetchResult(
 112                     "SHOW GLOBAL STATUS WHERE Variable_name='"
 113                     . implode("' OR Variable_name='", $statusVars) . "'",
 114                     0,
 115                     1
 116                 );
 117             } else {
 118                 $statusVarValues = array();
 119             }
 120
 121             // Retrieve all required server variables
 122             if (count($serverVars)) {
 123                 $serverVarValues = $GLOBALS['dbi']->fetchResult(
 124                     "SHOW GLOBAL VARIABLES WHERE Variable_name='"
 125                     . implode("' OR Variable_name='", $serverVars) . "'",
 126                     0,
 127                     1
 128                 );
 129             } else {
 130                 $serverVarValues = array();
 131             }
 132
 133             // ...and now assign them
 134             foreach ($ret as $chart_id => $chartNodes) {
 135                 foreach ($chartNodes as $node_id => $nodeDataPoints) {
 136                     foreach ($nodeDataPoints as $point_id => $dataPoint) {
 137                         switch($dataPoint['type']) {
 138                         case 'statusvar':
 139                             $ret[$chart_id][$node_id][$point_id]['value']
 140                                 = $statusVarValues[$dataPoint['name']];
 141                             break;
 142                         case 'servervar':
 143                             $ret[$chart_id][$node_id][$point_id]['value']
 144                                 = $serverVarValues[$dataPoint['name']];
 145                             break;
 146                         }
 147                     }
 148                 }
 149             }
 150
 151             $ret['x'] = microtime(true) * 1000;
 152
 153             PMA_Response::getInstance()->addJSON('message', $ret);
 154             exit;
 155         }
 156     }
 157
 158     if (isset($_REQUEST['log_data'])) {
 159         if (PMA_MYSQL_INT_VERSION < 50106) {
 160             // Table logging is only available since 5.1.6
 161             exit('""');
 162         }
 163
 164         $start = intval($_REQUEST['time_start']);
 165         $end = intval($_REQUEST['time_end']);
 166
 167         if ($_REQUEST['type'] == 'slow') {
 168             $q = 'SELECT start_time, user_host, ';
 169             $q .= 'Sec_to_Time(Sum(Time_to_Sec(query_time))) as query_time, ';
 170             $q .= 'Sec_to_Time(Sum(Time_to_Sec(lock_time))) as lock_time, ';
 171             $q .= 'SUM(rows_sent) AS rows_sent, ';
 172             $q .= 'SUM(rows_examined) AS rows_examined, db, sql_text, ';
 173             $q .= 'COUNT(sql_text) AS \'#\' ';
 174             $q .= 'FROM `mysql`.`slow_log` ';
 175             $q .= 'WHERE start_time > FROM_UNIXTIME(' . $start . ') ';
 176             $q .= 'AND start_time < FROM_UNIXTIME(' . $end . ') GROUP BY sql_text';
 177
 178             $result = $GLOBALS['dbi']->tryQuery($q);
 179
 180             $return = array('rows' => array(), 'sum' => array());
 181             $type = '';
 182
 183             while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
 184                 $type = strtolower(
 185                     substr($row['sql_text'], 0, strpos($row['sql_text'], ' '))
 186                 );
 187
 188                 switch($type) {
 189                 case 'insert':
 190                 case 'update':
 191                     //Cut off big inserts and updates, but append byte count instead
 192                     if (strlen($row['sql_text']) > 220) {
 193                         $implode_sql_text = implode(
 194                             ' ',
 195                             PMA_Util::formatByteDown(
 196                                 strlen($row['sql_text']), 2, 2
 197                             )
 198                         );
 199                         $row['sql_text'] = substr($row['sql_text'], 0, 200)
 200                             . '... [' . $implode_sql_text . ']';
 201                     }
 202                     break;
 203                 default:
 204                     break;
 205                 }
 206
 207                 if (! isset($return['sum'][$type])) {
 208                     $return['sum'][$type] = 0;
 209                 }
 210                 $return['sum'][$type] += $row['#'];
 211                 $return['rows'][] = $row;
 212             }
 213
 214             $return['sum']['TOTAL'] = array_sum($return['sum']);
 215             $return['numRows'] = count($return['rows']);
 216
 217             $GLOBALS['dbi']->freeResult($result);
 218
 219             PMA_Response::getInstance()->addJSON('message', $return);
 220             exit;
 221         }
 222
 223         if ($_REQUEST['type'] == 'general') {
 224             $limitTypes = '';
 225             if (isset($_REQUEST['limitTypes']) && $_REQUEST['limitTypes']) {
 226                 $limitTypes
 227                     = 'AND argument REGEXP \'^(INSERT|SELECT|UPDATE|DELETE)\' ';
 228             }
 229
 230             $q = 'SELECT TIME(event_time) as event_time, user_host, thread_id, ';
 231             $q .= 'server_id, argument, count(argument) as \'#\' ';
 232             $q .= 'FROM `mysql`.`general_log` ';
 233             $q .= 'WHERE command_type=\'Query\' ';
 234             $q .= 'AND event_time > FROM_UNIXTIME(' . $start . ') ';
 235             $q .= 'AND event_time < FROM_UNIXTIME(' . $end . ') ';
 236             $q .= $limitTypes . 'GROUP by argument'; // HAVING count > 1';
 237
 238             $result = $GLOBALS['dbi']->tryQuery($q);
 239
 240             $return = array('rows' => array(), 'sum' => array());
 241             $type = '';
 242             $insertTables = array();
 243             $insertTablesFirst = -1;
 244             $i = 0;
 245             $removeVars = isset($_REQUEST['removeVariables'])
 246                 && $_REQUEST['removeVariables'];
 247
 248             while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
 249                 preg_match('/^(\w+)\s/', $row['argument'], $match);
 250                 $type = strtolower($match[1]);
 251
 252                 if (! isset($return['sum'][$type])) {
 253                     $return['sum'][$type] = 0;
 254                 }
 255                 $return['sum'][$type] += $row['#'];
 256
 257                 switch($type) {
 258                 case 'insert':
 259                     // Group inserts if selected
 260                     if ($removeVars
 261                         && preg_match(
 262                             '/^INSERT INTO (`|\'|"|)([^\s\\1]+)\\1/i',
 263                             $row['argument'], $matches
 264                         )
 265                     ) {
 266                         $insertTables[$matches[2]]++;
 267                         if ($insertTables[$matches[2]] > 1) {
 268                             $return['rows'][$insertTablesFirst]['#']
 269                                 = $insertTables[$matches[2]];
 270
 271                             // Add a ... to the end of this query to indicate that
 272                             // there's been other queries
 273                             $temp = $return['rows'][$insertTablesFirst]['argument'];
 274                             if ($temp[strlen($temp) - 1] != '.') {
 275                                 $return['rows'][$insertTablesFirst]['argument']
 276                                     .= '<br/>...';
 277                             }
 278
 279                             // Group this value, thus do not add to the result list
 280                             continue 2;
 281                         } else {
 282                             $insertTablesFirst = $i;
 283                             $insertTables[$matches[2]] += $row['#'] - 1;
 284                         }
 285                     }
 286                     // No break here
 287
 288                 case 'update':
 289                     // Cut off big inserts and updates,
 290                     // but append byte count therefor
 291                     if (strlen($row['argument']) > 220) {
 292                         $row['argument'] = substr($row['argument'], 0, 200)
 293                             . '... ['
 294                             .  implode(
 295                                 ' ',
 296                                 PMA_Util::formatByteDown(
 297                                     strlen($row['argument']),
 298                                     2,
 299                                     2
 300                                 )
 301                             )
 302                             . ']';
 303                     }
 304                     break;
 305
 306                 default:
 307                     break;
 308                 }
 309
 310                 $return['rows'][] = $row;
 311                 $i++;
 312             }
 313
 314             $return['sum']['TOTAL'] = array_sum($return['sum']);
 315             $return['numRows'] = count($return['rows']);
 316
 317             $GLOBALS['dbi']->freeResult($result);
 318
 319             PMA_Response::getInstance()->addJSON('message', $return);
 320             exit;
 321         }
 322     }
 323
 324     if (isset($_REQUEST['logging_vars'])) {
 325         if (isset($_REQUEST['varName']) && isset($_REQUEST['varValue'])) {
 326             $value = PMA_Util::sqlAddSlashes($_REQUEST['varValue']);
 327             if (! is_numeric($value)) {
 328                 $value="'" . $value . "'";
 329             }
 330
 331             if (! preg_match("/[^a-zA-Z0-9_]+/", $_REQUEST['varName'])) {
 332                 $GLOBALS['dbi']->query(
 333                     'SET GLOBAL ' . $_REQUEST['varName'] . ' = ' . $value
 334                 );
 335             }
 336
 337         }
 338
 339         $loggingVars = $GLOBALS['dbi']->fetchResult(
 340             'SHOW GLOBAL VARIABLES WHERE Variable_name IN'
 341             . ' ("general_log","slow_query_log","long_query_time","log_output")',
 342             0,
 343             1
 344         );
 345         PMA_Response::getInstance()->addJSON('message', $loggingVars);
 346         exit;
 347     }
 348
 349     if (isset($_REQUEST['query_analyzer'])) {
 350         $return = array();
 351
 352         if (strlen($_REQUEST['database'])) {
 353             $GLOBALS['dbi']->selectDb($_REQUEST['database']);
 354         }
 355
 356         if ($profiling = PMA_Util::profilingSupported()) {
 357             $GLOBALS['dbi']->query('SET PROFILING=1;');
 358         }
 359
 360         // Do not cache query
 361         $query = preg_replace(
 362             '/^(\s*SELECT)/i',
 363             '\\1 SQL_NO_CACHE',
 364             $_REQUEST['query']
 365         );
 366
 367         $result = $GLOBALS['dbi']->tryQuery($query);
 368         $return['affectedRows'] = $GLOBALS['cached_affected_rows'];
 369
 370         $result = $GLOBALS['dbi']->tryQuery('EXPLAIN ' . $query);
 371         while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
 372             $return['explain'][] = $row;
 373         }
 374
 375         // In case an error happened
 376         $return['error'] = $GLOBALS['dbi']->getError();
 377
 378         $GLOBALS['dbi']->freeResult($result);
 379
 380         if ($profiling) {
 381             $return['profiling'] = array();
 382             $result = $GLOBALS['dbi']->tryQuery(
 383                 'SELECT seq,state,duration FROM INFORMATION_SCHEMA.PROFILING'
 384                 . ' WHERE QUERY_ID=1 ORDER BY seq'
 385             );
 386             while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
 387                 $return['profiling'][]= $row;
 388             }
 389             $GLOBALS['dbi']->freeResult($result);
 390         }
 391
 392         PMA_Response::getInstance()->addJSON('message', $return);
 393         exit;
 394     }
 395 }
 396
 397 /**
 398  * JS Includes
 399  */
 400 $header   = $response->getHeader();
 401 $scripts  = $header->getScripts();
 402 $scripts->addFile('jquery/jquery.tablesorter.js');
 403 $scripts->addFile('jquery/jquery.json-2.4.js');
 404 $scripts->addFile('jquery/jquery.sortableTable.js');
 405 $scripts->addFile('jquery/jquery-ui-timepicker-addon.js');
 406 /* < IE 9 doesn't support canvas natively */
 407 if (PMA_USR_BROWSER_AGENT == 'IE' && PMA_USR_BROWSER_VER < 9) {
 408     $scripts->addFile('jqplot/excanvas.js');
 409 }
 410 $scripts->addFile('canvg/canvg.js');
 411 // for charting
 412 $scripts->addFile('jqplot/jquery.jqplot.js');
 413 $scripts->addFile('jqplot/plugins/jqplot.pieRenderer.js');
 414 $scripts->addFile('jqplot/plugins/jqplot.canvasTextRenderer.js');
 415 $scripts->addFile('jqplot/plugins/jqplot.canvasAxisLabelRenderer.js');
 416 $scripts->addFile('jqplot/plugins/jqplot.dateAxisRenderer.js');
 417 $scripts->addFile('jqplot/plugins/jqplot.highlighter.js');
 418 $scripts->addFile('jqplot/plugins/jqplot.cursor.js');
 419 $scripts->addFile('jqplot/plugins/jqplot.byteFormatter.js');
 420 $scripts->addFile('date.js');
 421
 422 $scripts->addFile('server_status_monitor.js');
 423 $scripts->addFile('server_status_sorter.js');
 424
 425
 426 /**
 427  * start output
 428  */
 429 $ServerStatusData = new PMA_ServerStatusData();
 430
 431 /**
 432  * Output
 433  */
 434 $response->addHTML('<div>');
 435 $response->addHTML($ServerStatusData->getMenuHtml());
 436 $response->addHTML(PMA_getHtmlForMonitor($ServerStatusData));
 437 $response->addHTML(PMA_getHtmlForClientSideDataAndLinks($ServerStatusData));
 438 $response->addHTML('</div>');
 439 exit;
 440
 441 ?>