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