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