libraries/server_status_monitor.lib.php

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