libraries/server_status_monitor.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * functions for displaying server status sub item: monitor
   5  *
   6  * @usedby  server_status_monitor.php
   7  *
   8  * @package PhpMyAdmin
   9  */
  10 use PMA\libraries\ServerStatusData;
  11 use PMA\libraries\Util;
  12 use PMA\libraries\Sanitize;
  13
  14 require_once 'libraries/sysinfo.lib.php';
  15
  16 /**
  17  * Prints html with monitor
  18  *
  19  * @param ServerStatusData $ServerStatusData Server status data
  20  *
  21  * @return string
  22  */
  23 function PMA_getHtmlForMonitor($ServerStatusData)
  24 {
  25     $retval  = PMA_getHtmlForTabLinks();
  26
  27     $retval .= PMA_getHtmlForSettingsDialog();
  28
  29     $retval .= PMA_getHtmlForInstructionsDialog();
  30
  31     $retval .= PMA_getHtmlForAddChartDialog();
  32
  33     $retval .= PMA_getHtmlForAnalyseDialog();
  34
  35     $retval .= '<table class="clearfloat" id="chartGrid"></table>';
  36     $retval .= '<div id="logTable">';
  37     $retval .= '<br/>';
  38     $retval .= '</div>';
  39
  40     $retval .= '<script type="text/javascript">';
  41     $retval .= 'variableNames = [ ';
  42     $i = 0;
  43     foreach ($ServerStatusData->status as $name=>$value) {
  44         if (is_numeric($value)) {
  45             if ($i++ > 0) {
  46                 $retval .= ", ";
  47             }
  48             $retval .= Sanitize::formatJsVal($name);
  49         }
  50     }
  51     $retval .= '];';
  52     $retval .= '</script>';
  53
  54     return $retval;
  55 }
  56
  57 /**
  58  * Returns html for Analyse Dialog
  59  *
  60  * @return string
  61  */
  62 function PMA_getHtmlForAnalyseDialog()
  63 {
  64     $retval  = '<div id="logAnalyseDialog" title="';
  65     $retval .= __('Log statistics') . '" style="display:none;">';
  66     $retval .= '<p>' . __('Selected time range:');
  67     $retval .= '<input type="text" name="dateStart"'
  68         . ' class="datetimefield" value="" /> - ';
  69     $retval .= '<input type="text" name="dateEnd" class="datetimefield" value="" />';
  70     $retval .= '</p>';
  71     $retval .= '<input type="checkbox" id="limitTypes"'
  72         . ' value="1" checked="checked" />';
  73     $retval .= '<label for="limitTypes">';
  74     $retval .= __('Only retrieve SELECT,INSERT,UPDATE and DELETE Statements');
  75     $retval .= '</label>';
  76     $retval .= '<br/>';
  77     $retval .= '<input type="checkbox" id="removeVariables"'
  78         . ' value="1" checked="checked" />';
  79     $retval .= '<label for="removeVariables">';
  80     $retval .= __('Remove variable data in INSERT statements for better grouping');
  81     $retval .= '</label>';
  82     $retval .= '<p>';
  83     $retval .= __(
  84         'Choose from which log you want the statistics to be generated from.'
  85     );
  86     $retval .= '</p>';
  87     $retval .= '<p>';
  88     $retval .= __('Results are grouped by query text.');
  89     $retval .= '</p>';
  90     $retval .= '</div>';
  91     $retval .= '<div id="queryAnalyzerDialog" title="';
  92     $retval .= __('Query analyzer') . '" style="display:none;">';
  93     $retval .= '<textarea id="sqlquery"> </textarea>';
  94     $retval .= '<p></p>';
  95     $retval .= '<div class="placeHolder"></div>';
  96     $retval .= '</div>';
  97
  98     return $retval;
  99 }
 100
 101 /**
 102  * Returns html for Instructions Dialog
 103  *
 104  * @return string
 105  */
 106 function PMA_getHtmlForInstructionsDialog()
 107 {
 108     $retval  = '<div id="monitorInstructionsDialog" title="';
 109     $retval .= __('Monitor Instructions') . '" style="display:none;">';
 110     $retval .= __(
 111         'The phpMyAdmin Monitor can assist you in optimizing the server'
 112         . ' configuration and track down time intensive queries. For the latter you'
 113         . ' will need to set log_output to \'TABLE\' and have either the'
 114         . ' slow_query_log or general_log enabled. Note however, that the'
 115         . ' general_log produces a lot of data and increases server load'
 116         . ' by up to 15%.'
 117     );
 118
 119     $retval .= '<p></p>';
 120     $retval .= '<img class="ajaxIcon" src="';
 121     $retval .= $GLOBALS['pmaThemeImage'] . 'ajax_clock_small.gif"';
 122     $retval .= ' alt="' . __('Loading…') . '" />';
 123     $retval .= '<div class="ajaxContent"></div>';
 124     $retval .= '<div class="monitorUse" style="display:none;">';
 125     $retval .= '<p></p>';
 126     $retval .= '<strong>';
 127     $retval .= __('Using the monitor:');
 128     $retval .= '</strong><p>';
 129     $retval .= __(
 130         'Your browser will refresh all displayed charts in a regular interval.'
 131         . ' You may add charts and change the refresh rate under \'Settings\','
 132         . ' or remove any chart using the cog icon on each respective chart.'
 133     );
 134     $retval .= '</p><p>';
 135     $retval .= __(
 136         'To display queries from the logs, select the relevant time span on any'
 137         . ' chart by holding down the left mouse button and panning over the'
 138         . ' chart. Once confirmed, this will load a table of grouped queries,'
 139         . ' there you may click on any occurring SELECT statements to further'
 140         . ' analyze them.'
 141     );
 142     $retval .= '</p>';
 143     $retval .= '<p>';
 144     $retval .= PMA\libraries\Util::getImage('s_attention.png');
 145     $retval .= '<strong>';
 146     $retval .= __('Please note:');
 147     $retval .= '</strong><br />';
 148     $retval .= __(
 149         'Enabling the general_log may increase the server load by'
 150         . ' 5-15%. Also be aware that generating statistics from the logs is a'
 151         . ' load intensive task, so it is advisable to select only a small time'
 152         . ' span and to disable the general_log and empty its table once'
 153         . ' monitoring is not required any more.'
 154     );
 155     $retval .= '</p>';
 156     $retval .= '</div>';
 157     $retval .= '</div>';
 158
 159     return $retval;
 160 }
 161
 162 /**
 163  * Returns html for addChartDialog
 164  *
 165  * @return string
 166  */
 167 function PMA_getHtmlForAddChartDialog()
 168 {
 169     $retval  = '<div id="addChartDialog" title="'
 170         . __('Add chart') . '" style="display:none;">';
 171     $retval .= '<div id="tabGridVariables">';
 172     $retval .= '<p><input type="text" name="chartTitle" value="'
 173         . __('Chart Title') . '" /></p>';
 174     $retval .= '<input type="radio" name="chartType"'
 175         . ' value="preset" id="chartPreset" />';
 176     $retval .= '<label for="chartPreset">' . __('Preset chart') . '</label>';
 177     $retval .= '<select name="presetCharts"></select><br/>';
 178     $retval .= '<input type="radio" name="chartType" value="variable" '
 179         . 'id="chartStatusVar" checked="checked" />';
 180     $retval .= '<label for="chartStatusVar">';
 181     $retval .= __('Status variable(s)');
 182     $retval .= '</label><br/>';
 183     $retval .= '<div id="chartVariableSettings">';
 184     $retval .= '<label for="chartSeries">' . __('Select series:') . '</label><br />';
 185     $retval .= '<select id="chartSeries" name="varChartList" size="1">';
 186     $retval .= '<option>' . __('Commonly monitored') . '</option>';
 187     $retval .= '<option>Processes</option>';
 188     $retval .= '<option>Questions</option>';
 189     $retval .= '<option>Connections</option>';
 190     $retval .= '<option>Bytes_sent</option>';
 191     $retval .= '<option>Bytes_received</option>';
 192     $retval .= '<option>Threads_connected</option>';
 193     $retval .= '<option>Created_tmp_disk_tables</option>';
 194     $retval .= '<option>Handler_read_first</option>';
 195     $retval .= '<option>Innodb_buffer_pool_wait_free</option>';
 196     $retval .= '<option>Key_reads</option>';
 197     $retval .= '<option>Open_tables</option>';
 198     $retval .= '<option>Select_full_join</option>';
 199     $retval .= '<option>Slow_queries</option>';
 200     $retval .= '</select><br />';
 201     $retval .= '<label for="variableInput">';
 202     $retval .= __('or type variable name:');
 203     $retval .= ' </label>';
 204     $retval .= '<input type="text" name="variableInput" id="variableInput" />';
 205     $retval .= '<p></p>';
 206     $retval .= '<input type="checkbox" name="differentialValue"'
 207         . ' id="differentialValue" value="differential" checked="checked" />';
 208     $retval .= '<label for="differentialValue">';
 209     $retval .= __('Display as differential value');
 210     $retval .= '</label><br />';
 211     $retval .= '<input type="checkbox" id="useDivisor"'
 212         . ' name="useDivisor" value="1" />';
 213     $retval .= '<label for="useDivisor">' . __('Apply a divisor') . '</label>';
 214     $retval .= '<span class="divisorInput" style="display:none;">';
 215     $retval .= '<input type="text" name="valueDivisor" size="4" value="1" />';
 216     $retval .= '(<a href="#kibDivisor">' . __('KiB') . '</a>, ';
 217     $retval .= '<a href="#mibDivisor">' . __('MiB') . '</a>)';
 218     $retval .= '</span><br />';
 219     $retval .= '<input type="checkbox" id="useUnit" name="useUnit" value="1" />';
 220     $retval .= '<label for="useUnit">';
 221     $retval .= __('Append unit to data values');
 222     $retval .= '</label>';
 223     $retval .= '<span class="unitInput" style="display:none;">';
 224     $retval .= '<input type="text" name="valueUnit" size="4" value="" />';
 225     $retval .= '</span>';
 226     $retval .= '<p>';
 227     $retval .= '<a href="#submitAddSeries"><b>' . __('Add this series') . '</b></a>';
 228     $retval .= '<span id="clearSeriesLink" style="display:none;">';
 229     $retval .= ' | <a href="#submitClearSeries">' . __('Clear series') . '</a>';
 230     $retval .= '</span>';
 231     $retval .= '</p>';
 232     $retval .= __('Series in chart:');
 233     $retval .= '<br/>';
 234     $retval .= '<span id="seriesPreview">';
 235     $retval .= '<i>' . __('None') . '</i>';
 236     $retval .= '</span>';
 237     $retval .= '</div>';
 238     $retval .= '</div>';
 239     $retval .= '</div>';
 240
 241     return $retval;
 242 }
 243
 244 /**
 245  * Returns html with Tab Links
 246  *
 247  * @return string
 248  */
 249 function PMA_getHtmlForTabLinks()
 250 {
 251     $retval  = '<div class="tabLinks">';
 252     $retval .= '<a href="#pauseCharts">';
 253     $retval .= PMA\libraries\Util::getImage('play.png') . __('Start Monitor');
 254     $retval .= '</a>';
 255     $retval .= '<a href="#settingsPopup" class="popupLink">';
 256     $retval .= PMA\libraries\Util::getImage('s_cog.png') .  __('Settings');
 257     $retval .= '</a>';
 258     $retval .= '<a href="#monitorInstructionsDialog">';
 259     $retval .= Util::getImage('b_help.png') . __('Instructions/Setup');
 260     $retval .= '<a href="#endChartEditMode" style="display:none;">';
 261     $retval .= Util::getImage('s_okay.png');
 262     $retval .= __('Done dragging (rearranging) charts');
 263     $retval .= '</a>';
 264     $retval .= '</div>';
 265
 266     return $retval;
 267 }
 268
 269 /**
 270  * Returns html with Settings dialog
 271  *
 272  * @return string
 273  */
 274 function PMA_getHtmlForSettingsDialog()
 275 {
 276     $retval  = '<div class="popupContent settingsPopup">';
 277     $retval .= '<a href="#addNewChart">';
 278     $retval .= PMA\libraries\Util::getImage('b_chart.png') . __('Add chart');
 279     $retval .= '</a>';
 280     $retval .= '<a href="#rearrangeCharts">';
 281     $retval .= PMA\libraries\Util::getImage('b_tblops.png')
 282         . __('Enable charts dragging');
 283     $retval .= '</a>';
 284     $retval .= '<div class="clearfloat paddingtop"></div>';
 285     $retval .= '<div class="floatleft">';
 286     $retval .= __('Refresh rate') . '<br />';
 287     $retval .= ServerStatusData::getHtmlForRefreshList(
 288         'gridChartRefresh',
 289         5,
 290         Array(2, 3, 4, 5, 10, 20, 40, 60, 120, 300, 600, 1200)
 291     );
 292     $retval .= '<br />';
 293     $retval .= '</div>';
 294     $retval .= '<div class="floatleft">';
 295     $retval .= __('Chart columns');
 296     $retval .= '<br />';
 297     $retval .= '<select name="chartColumns">';
 298     $retval .= '<option>1</option>';
 299     $retval .= '<option>2</option>';
 300     $retval .= '<option>3</option>';
 301     $retval .= '<option>4</option>';
 302     $retval .= '<option>5</option>';
 303     $retval .= '<option>6</option>';
 304     $retval .= '</select>';
 305     $retval .= '</div>';
 306     $retval .= '<div class="clearfloat paddingtop">';
 307     $retval .= '<b>' . __('Chart arrangement') . '</b> ';
 308     $retval .= PMA\libraries\Util::showHint(
 309         __(
 310             'The arrangement of the charts is stored to the browsers local storage. '
 311             . 'You may want to export it if you have a complicated set up.'
 312         )
 313     );
 314     $retval .= '<br/>';
 315     $retval .= '<a class="ajax" href="#importMonitorConfig">';
 316     $retval .= __('Import');
 317     $retval .= '</a>';
 318     $retval .= '&nbsp;&nbsp;';
 319     $retval .= '<a class="disableAjax" href="#exportMonitorConfig">';
 320     $retval .= __('Export');
 321     $retval .= '</a>';
 322     $retval .= '&nbsp;&nbsp;';
 323     $retval .= '<a href="#clearMonitorConfig">';
 324     $retval .= __('Reset to default');
 325     $retval .= '</a>';
 326     $retval .= '</div>';
 327     $retval .= '</div>';
 328
 329     return $retval;
 330 }
 331
 332
 333 /**
 334  * Define some data and links needed on the client side
 335  *
 336  * @param ServerStatusData $ServerStatusData Server status data
 337  *
 338  * @return string
 339  */
 340 function PMA_getHtmlForClientSideDataAndLinks($ServerStatusData)
 341 {
 342     /**
 343      * Define some data needed on the client side
 344      */
 345     $input = '<input type="hidden" name="%s" value="%s" />';
 346     $form  = '<form id="js_data" class="hide">';
 347     $form .= sprintf($input, 'server_time', microtime(true) * 1000);
 348     $form .= sprintf($input, 'server_os', PMA_getSysInfoOs());
 349     $form .= sprintf($input, 'is_superuser', $GLOBALS['dbi']->isSuperuser());
 350     $form .= sprintf($input, 'server_db_isLocal', $ServerStatusData->db_isLocal);
 351     $form .= '</form>';
 352     /**
 353      * Define some links used on client side
 354      */
 355     $links  = '<div id="profiling_docu" class="hide">';
 356     $links .= PMA\libraries\Util::showMySQLDocu('general-thread-states');
 357     $links .= '</div>';
 358     $links .= '<div id="explain_docu" class="hide">';
 359     $links .= PMA\libraries\Util::showMySQLDocu('explain-output');
 360     $links .= '</div>';
 361
 362     return $form . $links;
 363 }
 364
 365 /***************************Ajax request function***********************************/
 366
 367 /**
 368  * Returns JSon for real-time charting data
 369  *
 370  * @return array
 371  */
 372 function PMA_getJsonForChartingData()
 373 {
 374     $ret = json_decode($_REQUEST['requiredData'], true);
 375     $statusVars = array();
 376     $serverVars = array();
 377     $sysinfo = $cpuload = $memory = 0;
 378
 379     /* Accumulate all required variables and data */
 380     list($serverVars, $statusVars, $ret) = PMA_getJsonForChartingDataGet(
 381         $ret, $serverVars, $statusVars, $sysinfo, $cpuload, $memory
 382     );
 383
 384     // Retrieve all required status variables
 385     if (count($statusVars)) {
 386         $statusVarValues = $GLOBALS['dbi']->fetchResult(
 387             "SHOW GLOBAL STATUS WHERE Variable_name='"
 388             . implode("' OR Variable_name='", $statusVars) . "'",
 389             0,
 390             1
 391         );
 392     } else {
 393         $statusVarValues = array();
 394     }
 395
 396     // Retrieve all required server variables
 397     if (count($serverVars)) {
 398         $serverVarValues = $GLOBALS['dbi']->fetchResult(
 399             "SHOW GLOBAL VARIABLES WHERE Variable_name='"
 400             . implode("' OR Variable_name='", $serverVars) . "'",
 401             0,
 402             1
 403         );
 404     } else {
 405         $serverVarValues = array();
 406     }
 407
 408     // ...and now assign them
 409     $ret = PMA_getJsonForChartingDataSet($ret, $statusVarValues, $serverVarValues);
 410
 411     $ret['x'] = microtime(true) * 1000;
 412     return $ret;
 413 }
 414
 415 /**
 416  * Assign the variables for real-time charting data
 417  *
 418  * @param array $ret             Real-time charting data
 419  * @param array $statusVarValues Status variable values
 420  * @param array $serverVarValues Server variable values
 421  *
 422  * @return array
 423  */
 424 function PMA_getJsonForChartingDataSet($ret, $statusVarValues, $serverVarValues)
 425 {
 426     foreach ($ret as $chart_id => $chartNodes) {
 427         foreach ($chartNodes as $node_id => $nodeDataPoints) {
 428             foreach ($nodeDataPoints as $point_id => $dataPoint) {
 429                 switch ($dataPoint['type']) {
 430                 case 'statusvar':
 431                     $ret[$chart_id][$node_id][$point_id]['value']
 432                         = $statusVarValues[$dataPoint['name']];
 433                     break;
 434                 case 'servervar':
 435                     $ret[$chart_id][$node_id][$point_id]['value']
 436                         = $serverVarValues[$dataPoint['name']];
 437                     break;
 438                 }
 439             }
 440         }
 441     }
 442     return $ret;
 443 }
 444
 445 /**
 446  * Get called to get JSON for charting data
 447  *
 448  * @param array $ret        Real-time charting data
 449  * @param array $serverVars Server variable values
 450  * @param array $statusVars Status variable values
 451  * @param mixed $sysinfo    System info
 452  * @param mixed $cpuload    CPU load
 453  * @param mixed $memory     Memory
 454  *
 455  * @return array
 456  */
 457 function PMA_getJsonForChartingDataGet(
 458     $ret, $serverVars, $statusVars, $sysinfo, $cpuload, $memory
 459 ) {
 460     // For each chart
 461     foreach ($ret as $chart_id => $chartNodes) {
 462         // For each data series
 463         foreach ($chartNodes as $node_id => $nodeDataPoints) {
 464             // For each data point in the series (usually just 1)
 465             foreach ($nodeDataPoints as $point_id => $dataPoint) {
 466                 list($serverVars, $statusVars, $ret[$chart_id][$node_id][$point_id])
 467                     = PMA_getJsonForChartingDataSwitch(
 468                         $dataPoint['type'], $dataPoint['name'], $serverVars,
 469                         $statusVars, $ret[$chart_id][$node_id][$point_id],
 470                         $sysinfo, $cpuload, $memory
 471                     );
 472             } /* foreach */
 473         } /* foreach */
 474     }
 475     return array($serverVars, $statusVars, $ret);
 476 }
 477
 478 /**
 479  * Switch called to get JSON for charting data
 480  *
 481  * @param string $type       Type
 482  * @param string $pName      Name
 483  * @param array  $serverVars Server variable values
 484  * @param array  $statusVars Status variable values
 485  * @param array  $ret        Real-time charting data
 486  * @param mixed  $sysinfo    System info
 487  * @param mixed  $cpuload    CPU load
 488  * @param mixed  $memory     Memory
 489  *
 490  * @return array
 491  */
 492 function PMA_getJsonForChartingDataSwitch(
 493     $type, $pName, $serverVars, $statusVars, $ret,
 494     $sysinfo, $cpuload, $memory
 495 ) {
 496     switch ($type) {
 497     /* We only collect the status and server variables here to
 498      * read them all in one query,
 499      * and only afterwards assign them.
 500      * Also do some white list filtering on the names
 501     */
 502     case 'servervar':
 503         if (!preg_match('/[^a-zA-Z_]+/', $pName)) {
 504             $serverVars[] = $pName;
 505         }
 506         break;
 507
 508     case 'statusvar':
 509         if (!preg_match('/[^a-zA-Z_]+/', $pName)) {
 510             $statusVars[] = $pName;
 511         }
 512         break;
 513
 514     case 'proc':
 515         $result = $GLOBALS['dbi']->query('SHOW PROCESSLIST');
 516         $ret['value'] = $GLOBALS['dbi']->numRows($result);
 517         break;
 518
 519     case 'cpu':
 520         if (!$sysinfo) {
 521             include_once 'libraries/sysinfo.lib.php';
 522             $sysinfo = PMA_getSysInfo();
 523         }
 524         if (!$cpuload) {
 525             $cpuload = $sysinfo->loadavg();
 526         }
 527
 528         if (PMA_getSysInfoOs() == 'Linux') {
 529             $ret['idle'] = $cpuload['idle'];
 530             $ret['busy'] = $cpuload['busy'];
 531         } else {
 532             $ret['value'] = $cpuload['loadavg'];
 533         }
 534
 535         break;
 536
 537     case 'memory':
 538         if (!$sysinfo) {
 539             include_once 'libraries/sysinfo.lib.php';
 540             $sysinfo = PMA_getSysInfo();
 541         }
 542         if (!$memory) {
 543             $memory = $sysinfo->memory();
 544         }
 545
 546         $ret['value'] = isset($memory[$pName]) ? $memory[$pName] : 0;
 547         break;
 548     }
 549
 550     return array($serverVars, $statusVars, $ret);
 551 }
 552
 553 /**
 554  * Returns JSon for log data with type: slow
 555  *
 556  * @param int $start Unix Time: Start time for query
 557  * @param int $end   Unix Time: End time for query
 558  *
 559  * @return array
 560  */
 561 function PMA_getJsonForLogDataTypeSlow($start, $end)
 562 {
 563     $query  = 'SELECT start_time, user_host, ';
 564     $query .= 'Sec_to_Time(Sum(Time_to_Sec(query_time))) as query_time, ';
 565     $query .= 'Sec_to_Time(Sum(Time_to_Sec(lock_time))) as lock_time, ';
 566     $query .= 'SUM(rows_sent) AS rows_sent, ';
 567     $query .= 'SUM(rows_examined) AS rows_examined, db, sql_text, ';
 568     $query .= 'COUNT(sql_text) AS \'#\' ';
 569     $query .= 'FROM `mysql`.`slow_log` ';
 570     $query .= 'WHERE start_time > FROM_UNIXTIME(' . $start . ') ';
 571     $query .= 'AND start_time < FROM_UNIXTIME(' . $end . ') GROUP BY sql_text';
 572
 573     $result = $GLOBALS['dbi']->tryQuery($query);
 574
 575     $return = array('rows' => array(), 'sum' => array());
 576
 577     while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
 578         $type = mb_strtolower(
 579             mb_substr(
 580                 $row['sql_text'],
 581                 0,
 582                 mb_strpos($row['sql_text'], ' ')
 583             )
 584         );
 585
 586         switch($type) {
 587         case 'insert':
 588         case 'update':
 589             //Cut off big inserts and updates, but append byte count instead
 590             if (mb_strlen($row['sql_text']) > 220) {
 591                 $implode_sql_text = implode(
 592                     ' ',
 593                     PMA\libraries\Util::formatByteDown(
 594                         mb_strlen($row['sql_text']), 2, 2
 595                     )
 596                 );
 597                 $row['sql_text'] = mb_substr($row['sql_text'], 0, 200)
 598                     . '... [' . $implode_sql_text . ']';
 599             }
 600             break;
 601         default:
 602             break;
 603         }
 604
 605         if (! isset($return['sum'][$type])) {
 606             $return['sum'][$type] = 0;
 607         }
 608         $return['sum'][$type] += $row['#'];
 609         $return['rows'][] = $row;
 610     }
 611
 612     $return['sum']['TOTAL'] = array_sum($return['sum']);
 613     $return['numRows'] = count($return['rows']);
 614
 615     $GLOBALS['dbi']->freeResult($result);
 616     return $return;
 617 }
 618
 619 /**
 620  * Returns JSon for log data with type: general
 621  *
 622  * @param int $start Unix Time: Start time for query
 623  * @param int $end   Unix Time: End time for query
 624  *
 625  * @return array
 626  */
 627 function PMA_getJsonForLogDataTypeGeneral($start, $end)
 628 {
 629     $limitTypes = '';
 630     if (isset($_REQUEST['limitTypes']) && $_REQUEST['limitTypes']) {
 631         $limitTypes
 632             = 'AND argument REGEXP \'^(INSERT|SELECT|UPDATE|DELETE)\' ';
 633     }
 634
 635     $query = 'SELECT TIME(event_time) as event_time, user_host, thread_id, ';
 636     $query .= 'server_id, argument, count(argument) as \'#\' ';
 637     $query .= 'FROM `mysql`.`general_log` ';
 638     $query .= 'WHERE command_type=\'Query\' ';
 639     $query .= 'AND event_time > FROM_UNIXTIME(' . $start . ') ';
 640     $query .= 'AND event_time < FROM_UNIXTIME(' . $end . ') ';
 641     $query .= $limitTypes . 'GROUP by argument'; // HAVING count > 1';
 642
 643     $result = $GLOBALS['dbi']->tryQuery($query);
 644
 645     $return = array('rows' => array(), 'sum' => array());
 646     $insertTables = array();
 647     $insertTablesFirst = -1;
 648     $i = 0;
 649     $removeVars = isset($_REQUEST['removeVariables'])
 650         && $_REQUEST['removeVariables'];
 651
 652     while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
 653         preg_match('/^(\w+)\s/', $row['argument'], $match);
 654         $type = mb_strtolower($match[1]);
 655
 656         if (! isset($return['sum'][$type])) {
 657             $return['sum'][$type] = 0;
 658         }
 659         $return['sum'][$type] += $row['#'];
 660
 661         switch($type) {
 662         /** @noinspection PhpMissingBreakStatementInspection */
 663         case 'insert':
 664             // Group inserts if selected
 665             if ($removeVars
 666                 && preg_match(
 667                     '/^INSERT INTO (`|\'|"|)([^\s\\1]+)\\1/i',
 668                     $row['argument'], $matches
 669                 )
 670             ) {
 671                 $insertTables[$matches[2]]++;
 672                 if ($insertTables[$matches[2]] > 1) {
 673                     $return['rows'][$insertTablesFirst]['#']
 674                         = $insertTables[$matches[2]];
 675
 676                     // Add a ... to the end of this query to indicate that
 677                     // there's been other queries
 678                     $temp = $return['rows'][$insertTablesFirst]['argument'];
 679                     $return['rows'][$insertTablesFirst]['argument']
 680                         .= PMA_getSuspensionPoints(
 681                             $temp[strlen($temp) - 1]
 682                         );
 683
 684                     // Group this value, thus do not add to the result list
 685                     continue 2;
 686                 } else {
 687                     $insertTablesFirst = $i;
 688                     $insertTables[$matches[2]] += $row['#'] - 1;
 689                 }
 690             }
 691             // No break here
 692
 693         case 'update':
 694             // Cut off big inserts and updates,
 695             // but append byte count therefor
 696             if (mb_strlen($row['argument']) > 220) {
 697                 $row['argument'] = mb_substr($row['argument'], 0, 200)
 698                     . '... ['
 699                     .  implode(
 700                         ' ',
 701                         PMA\libraries\Util::formatByteDown(
 702                             mb_strlen($row['argument']),
 703                             2,
 704                             2
 705                         )
 706                     )
 707                     . ']';
 708             }
 709             break;
 710
 711         default:
 712             break;
 713         }
 714
 715         $return['rows'][] = $row;
 716         $i++;
 717     }
 718
 719     $return['sum']['TOTAL'] = array_sum($return['sum']);
 720     $return['numRows'] = count($return['rows']);
 721
 722     $GLOBALS['dbi']->freeResult($result);
 723
 724     return $return;
 725 }
 726
 727 /**
 728  * Return suspension points if needed
 729  *
 730  * @param string $lastChar Last char
 731  *
 732  * @return null|string Return suspension points if needed
 733  */
 734 function PMA_getSuspensionPoints($lastChar)
 735 {
 736     if ($lastChar != '.') {
 737         return '<br/>...';
 738     }
 739
 740     return null;
 741 }
 742 /**
 743  * Returns JSon for logging vars
 744  *
 745  * @return array
 746  */
 747 function PMA_getJsonForLoggingVars()
 748 {
 749     if (isset($_REQUEST['varName']) && isset($_REQUEST['varValue'])) {
 750         $value = $GLOBALS['dbi']->escapeString($_REQUEST['varValue']);
 751         if (! is_numeric($value)) {
 752             $value="'" . $value . "'";
 753         }
 754
 755         if (! preg_match("/[^a-zA-Z0-9_]+/", $_REQUEST['varName'])) {
 756             $GLOBALS['dbi']->query(
 757                 'SET GLOBAL ' . $_REQUEST['varName'] . ' = ' . $value
 758             );
 759         }
 760
 761     }
 762
 763     $loggingVars = $GLOBALS['dbi']->fetchResult(
 764         'SHOW GLOBAL VARIABLES WHERE Variable_name IN'
 765         . ' ("general_log","slow_query_log","long_query_time","log_output")',
 766         0,
 767         1
 768     );
 769     return $loggingVars;
 770 }
 771
 772 /**
 773  * Returns JSon for query_analyzer
 774  *
 775  * @return array
 776  */
 777 function PMA_getJsonForQueryAnalyzer()
 778 {
 779     $return = array();
 780
 781     if (strlen($_REQUEST['database']) > 0) {
 782         $GLOBALS['dbi']->selectDb($_REQUEST['database']);
 783     }
 784
 785     if ($profiling = PMA\libraries\Util::profilingSupported()) {
 786         $GLOBALS['dbi']->query('SET PROFILING=1;');
 787     }
 788
 789     // Do not cache query
 790     $query = preg_replace(
 791         '/^(\s*SELECT)/i',
 792         '\\1 SQL_NO_CACHE',
 793         $_REQUEST['query']
 794     );
 795
 796     $GLOBALS['dbi']->tryQuery($query);
 797     $return['affectedRows'] = $GLOBALS['cached_affected_rows'];
 798
 799     $result = $GLOBALS['dbi']->tryQuery('EXPLAIN ' . $query);
 800     while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
 801         $return['explain'][] = $row;
 802     }
 803
 804     // In case an error happened
 805     $return['error'] = $GLOBALS['dbi']->getError();
 806
 807     $GLOBALS['dbi']->freeResult($result);
 808
 809     if ($profiling) {
 810         $return['profiling'] = array();
 811         $result = $GLOBALS['dbi']->tryQuery(
 812             'SELECT seq,state,duration FROM INFORMATION_SCHEMA.PROFILING'
 813             . ' WHERE QUERY_ID=1 ORDER BY seq'
 814         );
 815         while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
 816             $return['profiling'][]= $row;
 817         }
 818         $GLOBALS['dbi']->freeResult($result);
 819     }
 820     return $return;
 821 }
 822