/**
* Execute command
*
* @param InputInterface $input Input instance
* @param OutputInterface $output Output instance
*
* @return int|null|void
*/
public function execute(InputInterface $input, OutputInterface $output)
{
$this->elevateProcess($input, $output);
$debugLogLocation = $this->getApplication()->getConfigValue('db', 'debug_log_dir');
$debugLogDir = dirname($debugLogLocation);
$output->writeln('<h2>Starting MySQL general query log</h2>');
// Create directory if not exists
if (!is_dir($debugLogDir)) {
if (!mkdir($debugLogDir, 0777, true)) {
$output->writeln('<p-error>Could not create "' . $debugLogDir . '" directory</p-error>');
throw new \CliTools\Exception\StopException(1);
}
}
if (!empty($debugLogLocation)) {
$debugLogLocation .= 'mysql_' . getmypid() . '.log';
$query = 'SET GLOBAL general_log_file = ' . DatabaseConnection::quote($debugLogLocation);
DatabaseConnection::exec($query);
}
// Fetch log file
$query = 'SHOW VARIABLES LIKE \'general_log_file\'';
$logFileRow = DatabaseConnection::getRow($query);
if (!empty($logFileRow['Value'])) {
// Enable general log
$output->writeln('<p>Enabling general log</p>');
$query = 'SET GLOBAL general_log = \'ON\'';
DatabaseConnection::exec($query);
// Setup teardown cleanup
$tearDownFunc = function () use($output) {
// Disable general log
$output->writeln('<p>Disabling general log</p>');
$query = 'SET GLOBAL general_log = \'OFF\'';
DatabaseConnection::exec($query);
};
$this->getApplication()->registerTearDown($tearDownFunc);
// Read grep value
$grep = null;
if ($input->hasArgument('grep')) {
$grep = $input->getArgument('grep');
}
// Tail logfile
$logList = array($logFileRow['Value']);
$optionList = array('-n 0');
$this->showLog($logList, $input, $output, $grep, $optionList);
return 0;
} else {
$output->writeln('<p-error>MySQL general_log_file not set</p-error>');
return 1;
}
}
/**
* Cleanup MySQL
*
* @return string
*/
protected function cleanupMysql()
{
try {
// ############################
// Clear general log
// ############################
// Disable general log
$query = 'SET GLOBAL general_log = \'OFF\'';
DatabaseConnection::exec($query);
// Fetch log file
$query = 'SHOW VARIABLES LIKE \'general_log_file\'';
$logFileRow = DatabaseConnection::getRow($query);
if (!empty($logFileRow['Value'])) {
$command = new CommandBuilder('rm');
$command->addArgument('-f')->addArgumentSeparator()->addArgument($logFileRow['Value'])->executeInteractive();
}
} catch (\Exception $e) {
// do nothing if no mysql is running
}
}
/**
* Execute command
*
* @param InputInterface $input Input instance
* @param OutputInterface $output Output instance
*
* @return int|null|void
*/
public function execute(InputInterface $input, OutputInterface $output)
{
// Get list of databases
$databaseList = DatabaseConnection::databaseList();
if (!empty($databaseList)) {
// ########################
// Fetch statistics
// ########################
$databaseRowList = array();
foreach ($databaseList as $database) {
// Get all tables
$query = 'SELECT COUNT(*) AS count
FROM information_schema.tables
WHERE TABLE_SCHEMA = ' . DatabaseConnection::quote($database) . '
AND TABLE_TYPE = \'BASE TABLE\'';
$tableCount = DatabaseConnection::getOne($query);
// Get all views
$query = 'SELECT COUNT(*) AS count
FROM information_schema.tables
WHERE TABLE_SCHEMA = ' . DatabaseConnection::quote($database) . '
AND TABLE_TYPE LIKE \'%VIEW\'';
$viewCount = DatabaseConnection::getOne($query);
// Get size of database
$query = 'SELECT SUM(data_length) AS data_size,
SUM(index_length) AS index_size,
SUM(data_length + index_length) AS total_size
FROM information_schema.tables
WHERE TABLE_SCHEMA = ' . DatabaseConnection::quote($database);
$statsRow = DatabaseConnection::getRow($query);
$databaseRowList[$database] = array('name' => $database, 'table_count' => $tableCount, 'view_count' => $viewCount, 'data_size' => $statsRow['data_size'], 'index_size' => $statsRow['index_size'], 'total_size' => $statsRow['total_size']);
}
// ########################
// Sorting
// ########################
// Sort: default by name (natural sort)
uasort($databaseRowList, function ($a, $b) {
return strnatcmp($a['name'], $b['name']);
});
// Sort: by table names
if ($input->getOption('sort-name')) {
uasort($databaseRowList, function ($a, $b) {
return $a['table_count'] < $b['table_count'];
});
}
// Sort: by data size
if ($input->getOption('sort-data')) {
uasort($databaseRowList, function ($a, $b) {
return $a['data_size'] < $b['data_size'];
});
}
// Sort: by index size
if ($input->getOption('sort-index')) {
uasort($databaseRowList, function ($a, $b) {
return $a['index_size'] < $b['index_size'];
});
}
// Sort: by total size
if ($input->getOption('sort-total')) {
uasort($databaseRowList, function ($a, $b) {
return $a['total_size'] < $b['total_size'];
});
}
// ########################
// Stats
// ########################
$statsRow = array('name' => '', 'table_count' => 0, 'view_count' => 0, 'data_size' => 0, 'index_size' => 0, 'total_size' => 0);
$databaseCount = count($databaseRowList);
foreach ($databaseRowList as $databaseRow) {
$statsRow['table_count'] += $databaseRow['table_count'];
$statsRow['view_count'] += $databaseRow['view_count'];
$statsRow['data_size'] += $databaseRow['data_size'];
$statsRow['index_size'] += $databaseRow['index_size'];
$statsRow['total_size'] += $databaseRow['total_size'];
}
// ########################
// Output
// ########################
/** @var \Symfony\Component\Console\Helper\Table $table */
$table = new Table($output);
$table->setHeaders(array('Database', 'Tables', 'Views', 'Data', 'Index', 'Total'));
foreach ($databaseRowList as $databaseRow) {
$databaseRow['table_count'] = FormatUtility::number($databaseRow['table_count']);
$databaseRow['view_count'] = FormatUtility::number($databaseRow['view_count']);
$databaseRow['data_size'] = FormatUtility::bytes($databaseRow['data_size']);
$databaseRow['index_size'] = FormatUtility::bytes($databaseRow['index_size']);
$databaseRow['total_size'] = FormatUtility::bytes($databaseRow['total_size']);
$table->addRow(array_values($databaseRow));
}
// Stats: average
if ($databaseCount >= 1) {
$table->addRow(new TableSeparator());
$statsAvgRow = array();
$statsAvgRow['name'] = 'Average';
$statsAvgRow['table_count'] = FormatUtility::number($statsRow['table_count'] / $databaseCount);
$statsAvgRow['view_count'] = FormatUtility::number($statsRow['view_count'] / $databaseCount);
$statsAvgRow['data_size'] = FormatUtility::bytes($statsRow['data_size'] / $databaseCount);
$statsAvgRow['index_size'] = FormatUtility::bytes($statsRow['index_size'] / $databaseCount);
$statsAvgRow['total_size'] = FormatUtility::bytes($statsRow['total_size'] / $databaseCount);
$table->addRow(array_values($statsAvgRow));
}
// Stats: total
$statsTotalRow['name'] = 'Total';
$statsTotalRow['table_count'] = FormatUtility::number($statsRow['table_count']);
$statsTotalRow['view_count'] = FormatUtility::number($statsRow['view_count']);
$statsTotalRow['data_size'] = FormatUtility::bytes($statsRow['data_size']);
$statsTotalRow['index_size'] = FormatUtility::bytes($statsRow['index_size']);
$statsTotalRow['total_size'] = FormatUtility::bytes($statsRow['total_size']);
$table->addRow(array_values($statsTotalRow));
$table->render();
} else {
$output->writeln('<p-error>No databases found</p-error>');
}
return 0;
}
/**
* Execute command
*
* @param InputInterface $input Input instance
* @param OutputInterface $output Output instance
*
* @return int|null|void
*/
public function execute(InputInterface $input, OutputInterface $output)
{
$this->elevateProcess($input, $output);
$slowLogQueryTime = 1;
$logNonIndexedQueries = false;
// Slow log threshold
if ($input->getOption('time')) {
$slowLogQueryTime = $input->getOption('time');
}
// Also show not using indexes queries
if ($input->getOption('no-index')) {
$logNonIndexedQueries = true;
}
$debugLogLocation = $this->getApplication()->getConfigValue('db', 'debug_log_dir');
$debugLogDir = dirname($debugLogLocation);
$output->writeln('<h2>Starting MySQL slow query log</h2>');
// Create directory if not exists
if (!is_dir($debugLogDir)) {
if (!mkdir($debugLogDir, 0777, true)) {
$output->writeln('<p-error>Could not create "' . $debugLogDir . '" directory</p-error>');
throw new \CliTools\Exception\StopException(1);
}
}
if (!empty($debugLogLocation)) {
$debugLogLocation .= 'mysql_' . getmypid() . '.log';
$query = 'SET GLOBAL slow_query_log_file = ' . DatabaseConnection::quote($debugLogLocation);
DatabaseConnection::exec($query);
}
// Fetch log file
$query = 'SHOW VARIABLES LIKE \'slow_query_log_file\'';
$logFileRow = DatabaseConnection::getRow($query);
if (!empty($logFileRow['Value'])) {
// Enable slow log
$output->writeln('<p>Enabling slow log</p>');
$query = 'SET GLOBAL slow_query_log = \'ON\'';
DatabaseConnection::exec($query);
// Enable slow log
$output->writeln('<p>Set long_query_time to ' . (int) abs($slowLogQueryTime) . ' seconds</p>');
$query = 'SET GLOBAL long_query_time = ' . (int) abs($slowLogQueryTime);
DatabaseConnection::exec($query);
// Enable log queries without indexes log
if ($logNonIndexedQueries) {
$output->writeln('<p>Enabling logging of queries without using indexes</p>');
$query = 'SET GLOBAL log_queries_not_using_indexes = \'ON\'';
DatabaseConnection::exec($query);
} else {
$output->writeln('<p>Disabling logging of queries without using indexes</p>');
$query = 'SET GLOBAL log_queries_not_using_indexes = \'OFF\'';
DatabaseConnection::exec($query);
}
// Setup teardown cleanup
$tearDownFunc = function () use($output, $logNonIndexedQueries) {
// Disable general log
$output->writeln('<p>Disable slow log</p>');
$query = 'SET GLOBAL slow_query_log = \'OFF\'';
DatabaseConnection::exec($query);
if ($logNonIndexedQueries) {
// Disable log queries without indexes log
$query = 'SET GLOBAL log_queries_not_using_indexes = \'OFF\'';
DatabaseConnection::exec($query);
}
};
$this->getApplication()->registerTearDown($tearDownFunc);
// Read grep value
$grep = null;
if ($input->hasArgument('grep')) {
$grep = $input->getArgument('grep');
}
// Tail logfile
$logList = array($logFileRow['Value']);
$optionList = array('-n 0');
$this->showLog($logList, $input, $output, $grep, $optionList);
return 0;
} else {
$output->writeln('<p-error>MySQL general_log_file not set</p-error>');
return 1;
}
}
