'; $retval .= '
'; $retval .= '
'; $retval .= '
'; $retval .= ''; return $retval; } /** * Returns html for Analyse Dialog * * @return string */ public static function getHtmlForAnalyseDialog() { $retval = '
'; $retval .= '

' . __('Selected time range:'); $retval .= ' - '; $retval .= ''; $retval .= '

'; $retval .= ''; $retval .= ''; $retval .= '
'; $retval .= ''; $retval .= ''; $retval .= '

'; $retval .= __( 'Choose from which log you want the statistics to be generated from.' ); $retval .= '

'; $retval .= '

'; $retval .= __('Results are grouped by query text.'); $retval .= '

'; $retval .= '
'; $retval .= '
'; $retval .= ''; $retval .= '

'; $retval .= '
'; $retval .= '
'; return $retval; } /** * Returns html for Instructions Dialog * * @return string */ public static function getHtmlForInstructionsDialog() { $retval = '
'; $retval .= __( 'The phpMyAdmin Monitor can assist you in optimizing the server' . ' configuration and track down time intensive queries. For the latter you' . ' will need to set log_output to \'TABLE\' and have either the' . ' slow_query_log or general_log enabled. Note however, that the' . ' general_log produces a lot of data and increases server load' . ' by up to 15%.' ); $retval .= '

'; $retval .= ''; $retval .= '
'; $retval .= '
'; $retval .= '

'; $retval .= ''; $retval .= __('Using the monitor:'); $retval .= '

'; $retval .= __( 'Your browser will refresh all displayed charts in a regular interval.' . ' You may add charts and change the refresh rate under \'Settings\',' . ' or remove any chart using the cog icon on each respective chart.' ); $retval .= '

'; $retval .= __( 'To display queries from the logs, select the relevant time span on any' . ' chart by holding down the left mouse button and panning over the' . ' chart. Once confirmed, this will load a table of grouped queries,' . ' there you may click on any occurring SELECT statements to further' . ' analyze them.' ); $retval .= '

'; $retval .= '

'; $retval .= Util::getImage('s_attention'); $retval .= ''; $retval .= __('Please note:'); $retval .= '
'; $retval .= __( 'Enabling the general_log may increase the server load by' . ' 5-15%. Also be aware that generating statistics from the logs is a' . ' load intensive task, so it is advisable to select only a small time' . ' span and to disable the general_log and empty its table once' . ' monitoring is not required any more.' ); $retval .= '

'; $retval .= '
'; $retval .= '
'; return $retval; } /** * Returns html for addChartDialog * * @return string */ public static function getHtmlForAddChartDialog() { $retval = '
'; $retval .= '
'; $retval .= '

'; $retval .= ''; $retval .= ''; $retval .= '
'; $retval .= ''; $retval .= '
'; $retval .= '
'; $retval .= '
'; $retval .= '
'; $retval .= ''; $retval .= ''; $retval .= '

'; $retval .= ''; $retval .= '
'; $retval .= ''; $retval .= ''; $retval .= ''; $retval .= ''; $retval .= '(' . __('KiB') . ', '; $retval .= '' . __('MiB') . ')'; $retval .= '
'; $retval .= ''; $retval .= ''; $retval .= ''; $retval .= ''; $retval .= ''; $retval .= '

'; $retval .= '' . __('Add this series') . ''; $retval .= ''; $retval .= ' | ' . __('Clear series') . ''; $retval .= ''; $retval .= '

'; $retval .= __('Series in chart:'); $retval .= '
'; $retval .= ''; $retval .= '' . __('None') . ''; $retval .= ''; $retval .= '
'; $retval .= '
'; $retval .= '
'; return $retval; } /** * Returns html with Tab Links * * @return string */ public static function getHtmlForTabLinks() { $retval = '
'; $retval .= ''; $retval .= Util::getImage('play') . __('Start Monitor'); $retval .= ''; $retval .= ''; $retval .= Util::getImage('s_cog') . __('Settings'); $retval .= ''; $retval .= ''; $retval .= Util::getImage('b_help') . __('Instructions/Setup'); $retval .= ''; $retval .= Util::getImage('s_okay'); $retval .= __('Done dragging (rearranging) charts'); $retval .= ''; $retval .= '
'; return $retval; } /** * Returns html with Settings dialog * * @return string */ public static function getHtmlForSettingsDialog() { $retval = '
'; $retval .= ''; $retval .= Util::getImage('b_chart') . __('Add chart'); $retval .= ''; $retval .= ''; $retval .= Util::getImage('b_tblops') . __('Enable charts dragging'); $retval .= ''; $retval .= '
'; $retval .= '
'; $retval .= __('Refresh rate') . '
'; $retval .= Data::getHtmlForRefreshList( 'gridChartRefresh', 5, Array(2, 3, 4, 5, 10, 20, 40, 60, 120, 300, 600, 1200) ); $retval .= '
'; $retval .= '
'; $retval .= '
'; $retval .= __('Chart columns'); $retval .= '
'; $retval .= ''; $retval .= '
'; $retval .= '
'; $retval .= '' . __('Chart arrangement') . ' '; $retval .= Util::showHint( __( 'The arrangement of the charts is stored to the browsers local storage. ' . 'You may want to export it if you have a complicated set up.' ) ); $retval .= '
'; $retval .= ''; $retval .= __('Import'); $retval .= ''; $retval .= '  '; $retval .= ''; $retval .= __('Export'); $retval .= ''; $retval .= '  '; $retval .= ''; $retval .= __('Reset to default'); $retval .= ''; $retval .= '
'; $retval .= '
'; return $retval; } /** * Define some data and links needed on the client side * * @param Data $serverStatusData Server status data * * @return string */ public static function getHtmlForClientSideDataAndLinks(Data $serverStatusData) { /** * Define some data needed on the client side */ $input = ''; $form = '
'; $form .= sprintf($input, 'server_time', microtime(true) * 1000); $form .= sprintf($input, 'server_os', SysInfo::getOs()); $form .= sprintf($input, 'is_superuser', $GLOBALS['dbi']->isSuperuser()); $form .= sprintf($input, 'server_db_isLocal', $serverStatusData->db_isLocal); $form .= '
'; /** * Define some links used on client side */ $links = '
'; $links .= Util::showMySQLDocu('general-thread-states'); $links .= '
'; $links .= '
'; $links .= Util::showMySQLDocu('explain-output'); $links .= '
'; return $form . $links; } /***************************Ajax request function***********************************/ /** * Returns JSon for real-time charting data * * @return array */ public static function getJsonForChartingData() { $ret = json_decode($_POST['requiredData'], true); $statusVars = array(); $serverVars = array(); $sysinfo = $cpuload = $memory = 0; /* Accumulate all required variables and data */ list($serverVars, $statusVars, $ret) = self::getJsonForChartingDataGet( $ret, $serverVars, $statusVars, $sysinfo, $cpuload, $memory ); // Retrieve all required status variables if (count($statusVars)) { $statusVarValues = $GLOBALS['dbi']->fetchResult( "SHOW GLOBAL STATUS WHERE Variable_name='" . implode("' OR Variable_name='", $statusVars) . "'", 0, 1 ); } else { $statusVarValues = array(); } // Retrieve all required server variables if (count($serverVars)) { $serverVarValues = $GLOBALS['dbi']->fetchResult( "SHOW GLOBAL VARIABLES WHERE Variable_name='" . implode("' OR Variable_name='", $serverVars) . "'", 0, 1 ); } else { $serverVarValues = array(); } // ...and now assign them $ret = self::getJsonForChartingDataSet($ret, $statusVarValues, $serverVarValues); $ret['x'] = microtime(true) * 1000; return $ret; } /** * Assign the variables for real-time charting data * * @param array $ret Real-time charting data * @param array $statusVarValues Status variable values * @param array $serverVarValues Server variable values * * @return array */ public static function getJsonForChartingDataSet(array $ret, array $statusVarValues, array $serverVarValues) { foreach ($ret as $chart_id => $chartNodes) { foreach ($chartNodes as $node_id => $nodeDataPoints) { foreach ($nodeDataPoints as $point_id => $dataPoint) { switch ($dataPoint['type']) { case 'statusvar': $ret[$chart_id][$node_id][$point_id]['value'] = $statusVarValues[$dataPoint['name']]; break; case 'servervar': $ret[$chart_id][$node_id][$point_id]['value'] = $serverVarValues[$dataPoint['name']]; break; } } } } return $ret; } /** * Get called to get JSON for charting data * * @param array $ret Real-time charting data * @param array $serverVars Server variable values * @param array $statusVars Status variable values * @param mixed $sysinfo System info * @param mixed $cpuload CPU load * @param mixed $memory Memory * * @return array */ public static function getJsonForChartingDataGet( array $ret, array $serverVars, array $statusVars, $sysinfo, $cpuload, $memory ) { // For each chart foreach ($ret as $chart_id => $chartNodes) { // For each data series foreach ($chartNodes as $node_id => $nodeDataPoints) { // For each data point in the series (usually just 1) foreach ($nodeDataPoints as $point_id => $dataPoint) { list($serverVars, $statusVars, $ret[$chart_id][$node_id][$point_id]) = self::getJsonForChartingDataSwitch( $dataPoint['type'], $dataPoint['name'], $serverVars, $statusVars, $ret[$chart_id][$node_id][$point_id], $sysinfo, $cpuload, $memory ); } /* foreach */ } /* foreach */ } return array($serverVars, $statusVars, $ret); } /** * Switch called to get JSON for charting data * * @param string $type Type * @param string $pName Name * @param array $serverVars Server variable values * @param array $statusVars Status variable values * @param array $ret Real-time charting data * @param mixed $sysinfo System info * @param mixed $cpuload CPU load * @param mixed $memory Memory * * @return array */ public static function getJsonForChartingDataSwitch( $type, $pName, array $serverVars, array $statusVars, array $ret, $sysinfo, $cpuload, $memory ) { switch ($type) { /* We only collect the status and server variables here to * read them all in one query, * and only afterwards assign them. * Also do some white list filtering on the names */ case 'servervar': if (!preg_match('/[^a-zA-Z_]+/', $pName)) { $serverVars[] = $pName; } break; case 'statusvar': if (!preg_match('/[^a-zA-Z_]+/', $pName)) { $statusVars[] = $pName; } break; case 'proc': $result = $GLOBALS['dbi']->query('SHOW PROCESSLIST'); $ret['value'] = $GLOBALS['dbi']->numRows($result); break; case 'cpu': if (!$sysinfo) { $sysinfo = SysInfo::get(); } if (!$cpuload) { $cpuload = $sysinfo->loadavg(); } if (SysInfo::getOs() == 'Linux') { $ret['idle'] = $cpuload['idle']; $ret['busy'] = $cpuload['busy']; } else { $ret['value'] = $cpuload['loadavg']; } break; case 'memory': if (!$sysinfo) { $sysinfo = SysInfo::get(); } if (!$memory) { $memory = $sysinfo->memory(); } $ret['value'] = isset($memory[$pName]) ? $memory[$pName] : 0; break; } return array($serverVars, $statusVars, $ret); } /** * Returns JSon for log data with type: slow * * @param int $start Unix Time: Start time for query * @param int $end Unix Time: End time for query * * @return array */ public static function getJsonForLogDataTypeSlow($start, $end) { $query = 'SELECT start_time, user_host, '; $query .= 'Sec_to_Time(Sum(Time_to_Sec(query_time))) as query_time, '; $query .= 'Sec_to_Time(Sum(Time_to_Sec(lock_time))) as lock_time, '; $query .= 'SUM(rows_sent) AS rows_sent, '; $query .= 'SUM(rows_examined) AS rows_examined, db, sql_text, '; $query .= 'COUNT(sql_text) AS \'#\' '; $query .= 'FROM `mysql`.`slow_log` '; $query .= 'WHERE start_time > FROM_UNIXTIME(' . $start . ') '; $query .= 'AND start_time < FROM_UNIXTIME(' . $end . ') GROUP BY sql_text'; $result = $GLOBALS['dbi']->tryQuery($query); $return = array('rows' => array(), 'sum' => array()); while ($row = $GLOBALS['dbi']->fetchAssoc($result)) { $type = mb_strtolower( mb_substr( $row['sql_text'], 0, mb_strpos($row['sql_text'], ' ') ) ); switch($type) { case 'insert': case 'update': //Cut off big inserts and updates, but append byte count instead if (mb_strlen($row['sql_text']) > 220) { $implode_sql_text = implode( ' ', Util::formatByteDown( mb_strlen($row['sql_text']), 2, 2 ) ); $row['sql_text'] = mb_substr($row['sql_text'], 0, 200) . '... [' . $implode_sql_text . ']'; } break; default: break; } if (! isset($return['sum'][$type])) { $return['sum'][$type] = 0; } $return['sum'][$type] += $row['#']; $return['rows'][] = $row; } $return['sum']['TOTAL'] = array_sum($return['sum']); $return['numRows'] = count($return['rows']); $GLOBALS['dbi']->freeResult($result); return $return; } /** * Returns JSon for log data with type: general * * @param int $start Unix Time: Start time for query * @param int $end Unix Time: End time for query * * @return array */ public static function getJsonForLogDataTypeGeneral($start, $end) { $limitTypes = ''; if (isset($_POST['limitTypes']) && $_POST['limitTypes']) { $limitTypes = 'AND argument REGEXP \'^(INSERT|SELECT|UPDATE|DELETE)\' '; } $query = 'SELECT TIME(event_time) as event_time, user_host, thread_id, '; $query .= 'server_id, argument, count(argument) as \'#\' '; $query .= 'FROM `mysql`.`general_log` '; $query .= 'WHERE command_type=\'Query\' '; $query .= 'AND event_time > FROM_UNIXTIME(' . $start . ') '; $query .= 'AND event_time < FROM_UNIXTIME(' . $end . ') '; $query .= $limitTypes . 'GROUP by argument'; // HAVING count > 1'; $result = $GLOBALS['dbi']->tryQuery($query); $return = array('rows' => array(), 'sum' => array()); $insertTables = array(); $insertTablesFirst = -1; $i = 0; $removeVars = isset($_POST['removeVariables']) && $_POST['removeVariables']; while ($row = $GLOBALS['dbi']->fetchAssoc($result)) { preg_match('/^(\w+)\s/', $row['argument'], $match); $type = mb_strtolower($match[1]); if (! isset($return['sum'][$type])) { $return['sum'][$type] = 0; } $return['sum'][$type] += $row['#']; switch($type) { /** @noinspection PhpMissingBreakStatementInspection */ case 'insert': // Group inserts if selected if ($removeVars && preg_match( '/^INSERT INTO (`|\'|"|)([^\s\\1]+)\\1/i', $row['argument'], $matches ) ) { $insertTables[$matches[2]]++; if ($insertTables[$matches[2]] > 1) { $return['rows'][$insertTablesFirst]['#'] = $insertTables[$matches[2]]; // Add a ... to the end of this query to indicate that // there's been other queries $temp = $return['rows'][$insertTablesFirst]['argument']; $return['rows'][$insertTablesFirst]['argument'] .= self::getSuspensionPoints( $temp[strlen($temp) - 1] ); // Group this value, thus do not add to the result list continue 2; } else { $insertTablesFirst = $i; $insertTables[$matches[2]] += $row['#'] - 1; } } // No break here case 'update': // Cut off big inserts and updates, // but append byte count therefor if (mb_strlen($row['argument']) > 220) { $row['argument'] = mb_substr($row['argument'], 0, 200) . '... [' . implode( ' ', Util::formatByteDown( mb_strlen($row['argument']), 2, 2 ) ) . ']'; } break; default: break; } $return['rows'][] = $row; $i++; } $return['sum']['TOTAL'] = array_sum($return['sum']); $return['numRows'] = count($return['rows']); $GLOBALS['dbi']->freeResult($result); return $return; } /** * Return suspension points if needed * * @param string $lastChar Last char * * @return null|string Return suspension points if needed */ public static function getSuspensionPoints($lastChar) { if ($lastChar != '.') { return '
...'; } return null; } /** * Returns JSon for logging vars * * @return array */ public static function getJsonForLoggingVars() { if (isset($_POST['varName']) && isset($_POST['varValue'])) { $value = $GLOBALS['dbi']->escapeString($_POST['varValue']); if (! is_numeric($value)) { $value="'" . $value . "'"; } if (! preg_match("/[^a-zA-Z0-9_]+/", $_POST['varName'])) { $GLOBALS['dbi']->query( 'SET GLOBAL ' . $_POST['varName'] . ' = ' . $value ); } } $loggingVars = $GLOBALS['dbi']->fetchResult( 'SHOW GLOBAL VARIABLES WHERE Variable_name IN' . ' ("general_log","slow_query_log","long_query_time","log_output")', 0, 1 ); return $loggingVars; } /** * Returns JSon for query_analyzer * * @return array */ public static function getJsonForQueryAnalyzer() { $return = array(); if (strlen($_POST['database']) > 0) { $GLOBALS['dbi']->selectDb($_POST['database']); } if ($profiling = Util::profilingSupported()) { $GLOBALS['dbi']->query('SET PROFILING=1;'); } // Do not cache query $query = preg_replace( '/^(\s*SELECT)/i', '\\1 SQL_NO_CACHE', $_POST['query'] ); $GLOBALS['dbi']->tryQuery($query); $return['affectedRows'] = $GLOBALS['cached_affected_rows']; $result = $GLOBALS['dbi']->tryQuery('EXPLAIN ' . $query); while ($row = $GLOBALS['dbi']->fetchAssoc($result)) { $return['explain'][] = $row; } // In case an error happened $return['error'] = $GLOBALS['dbi']->getError(); $GLOBALS['dbi']->freeResult($result); if ($profiling) { $return['profiling'] = array(); $result = $GLOBALS['dbi']->tryQuery( 'SELECT seq,state,duration FROM INFORMATION_SCHEMA.PROFILING' . ' WHERE QUERY_ID=1 ORDER BY seq' ); while ($row = $GLOBALS['dbi']->fetchAssoc($result)) { $return['profiling'][]= $row; } $GLOBALS['dbi']->freeResult($result); } return $return; } }