function _table_hours_perform($tablename)
{
if (!isset($GLOBALS["Q"])) {
$GLOBALS["Q"] = new mysql_squid_builder();
}
if (!preg_match("#ngixattck_([0-9]+)#", $tablename, $re)) {
writelogs_squid("NOT AN HOUR TABLE `{$tablename}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$q = new mysql_squid_builder();
$time = $q->TIME_FROM_HOUR_TEMP_TABLE($tablename);
if ($GLOBALS["VERBOSE"]) {
echo "{$tablename} - {$time} - " . date("Y-m-d", $time) . "\n";
}
$sql = "SELECT HOUR(zDate) as `hour`,COUNT(keyr) as hits,`ipaddr`,`familysite`,`hostname`,`country`,`servername`\n\tFROM `{$tablename}` GROUP BY `hour`,`ipaddr`,`familysite`,`hostname`,`country`,`servername`";
if ($GLOBALS["VERBOSE"]) {
echo $sql . "\n";
}
$results = $GLOBALS["Q"]->QUERY_SQL($sql);
if (!$GLOBALS["Q"]->ok) {
writelogs_squid("Fatal: {$GLOBALS["Q"]->mysql_error} on `{$tablename}`\n" . @implode("\n", $GLOBALS["REPAIR_MYSQL_TABLE"]), __FUNCTION__, __FILE__, __LINE__, "stats");
if (strpos(" {$GLOBALS["Q"]->mysql_error}", "is marked as crashed and should be repaired") > 0) {
$q1 = new mysql();
writelogs_squid("try to repair table `{$tablename}`", __FUNCTION__, __FILE__, __LINE__, "stats");
$q1->REPAIR_TABLE("squidlogs", $tablename);
writelogs_squid(@implode("\n", $GLOBALS["REPAIR_MYSQL_TABLE"]), __FUNCTION__, __FILE__, __LINE__, "stats");
}
return false;
}
if (mysql_num_rows($results) == 0) {
return true;
}
$timekey = date('Ymd', $time);
$tabledest = "ngixattckd_{$timekey}";
if (!$q->check_nginx_attacks_DAY($timekey)) {
return false;
}
$prefix = "INSERT IGNORE INTO {$tabledest} (zmd5,`hour`,`ipaddr`,`familysite`,`hostname`,`country`,`servername`,`hits`) VALUES ";
$d = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$zmd = array();
while (list($key, $value) = each($ligne)) {
$ligne[$key] = mysql_escape_string2($value);
$zmd[] = $value;
}
$zMD5 = md5(@implode("", $zmd));
$f[] = "('{$zMD5}','{$ligne["hour"]}','{$ligne["ipaddr"]}','{$ligne["familysite"]}','{$ligne["hostname"]}','{$ligne["country"]}','{$ligne["servername"]}','{$ligne["hits"]}')";
if (count($f) > 500) {
$q->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
if (!$q->ok) {
writelogs_squid("Fatal: {$q->mysql_error} on `{$tabledest}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
}
if (count($f) > 0) {
$q->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
if (!$q->ok) {
writelogs_squid("Fatal: {$q->mysql_error} on `{$tabledest}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
}
return true;
}
function _repair_database($database)
{
$q = new mysql();
$sql = "SHOW TABLES";
$results = $q->QUERY_SQL($sql, "squidlogs");
$unix = new unix();
$MYSQL_DATA_DIR = $unix->MYSQL_DATA_DIR();
$time1 = time();
$myisamchk = $unix->find_program("myisamchk");
$mysqlcheck = $unix->find_program("mysqlcheck");
$myisamchk = $unix->find_program("myisamchk");
$pgrep = $unix->find_program("pgrep");
exec("{$pgrep} -l -f \"{$myisamchk}\"", $results);
while (list($index, $line) = each($results)) {
if (preg_match("#pgrep#", $line)) {
continue;
}
if (preg_match("#^[0-9]+\\s+#", $line)) {
writelogs("{$line} already executed", @implode("\r\n", $results), __FUNCTION__, __FILE__, __LINE__);
return;
}
}
$mysqlcheck_logs = null;
$sql = "SHOW TABLES";
$results = $q->QUERY_SQL($sql, $database);
if (!$q->ok) {
system_admin_events("Maintenance on database {$database} failed {$q->mysql_error}", __FUNCTION__, __FILE__, __LINE__, "mysql-error");
return;
}
if (mysql_num_rows($results) == 0) {
system_admin_events("Maintenance on database {$database} aborting, no table stored", __FUNCTION__, __FILE__, __LINE__, "mysql-error");
return;
}
$user = $q->mysql_admin;
$ty[] = " --user={$user}";
if ($q->mysql_password != null) {
$ty[] = "--password={$q->mysql_password}";
}
$BLACKS["events_waits_current"] = true;
$BLACKS["events_waits_history"] = true;
$BLACKS["events_waits_history_long"] = true;
$BLACKS["cond_instances"] = true;
$BLACKS["events_waits_summary_by_instance"] = true;
$BLACKS["events_waits_summary_by_thread_by_event_name"] = true;
$BLACKS["events_waits_summary_global_by_event_name"] = true;
$BLACKS["file_instances"] = true;
$BLACKS["file_summary_by_event_name"] = true;
$BLACKS["file_summary_by_instance"] = true;
$BLACKS["mutex_instances"] = true;
$BLACKS["performance_timers"] = true;
$BLACKS["rwlock_instances"] = true;
$BLACKS["setup_consumers"] = true;
$BLACKS["setup_instruments"] = true;
$BLACKS["setup_timers"] = true;
$BLACKS["threads"] = true;
$BLACKS["schema"] = true;
$credentials = @implode(" ", $ty);
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$table = $ligne["Tables_in_{$database}"];
if (isset($BLACKS[$table])) {
continue;
}
$tt = time();
if (is_file($mysqlcheck)) {
exec("{$mysqlcheck}{$credentials} -r {$database} {$table} 2>&1", $mysqlcheck_array);
$mysqlcheck_logs = $mysqlcheck_logs . "\n{$mysqlcheck} on {$table}:\n" . @implode("\n", $mysqlcheck_array);
unset($mysqlcheck_array);
}
echo $table . "\n";
if (is_file($myisamchk)) {
shell_exec("{$myisamchk} --safe-recover --force {$MYSQL_DATA_DIR}/{$database}/{$table}");
} else {
$q->REPAIR_TABLE($database, $table);
}
$q->QUERY_SQL("OPTIMIZE table {$table}", "{$database}");
$time_duration = distanceOfTimeInWords($tt, time());
$p[] = "{$database}/{$table} {$time_duration}";
}
$t2 = time();
$time_duration = distanceOfTimeInWords($time1, $t2);
system_admin_events("Maintenance on database {$database} done: took {$time_duration}\nOperations has be proceed on \n" . @implode("\n", $p) . "\nmysqlchecks results:\n{$mysqlcheck_logs}", __FUNCTION__, __FILE__, __LINE__, "mysql");
}
function maintenance()
{
return null;
$unix = new unix();
$time = $unix->file_time_min("/etc/artica-postfix/mysql.optimize.time");
$time1 = time();
$myisamchk = $unix->find_program("myisamchk");
$mysqlcheck = $unix->find_program("mysqlcheck");
if (!$GLOBALS["VERBOSE"]) {
if ($time < 1440) {
$unix->events(__FILE__ . "::" . __FUNCTION__ . " {$time}Mn wait 1440Mn, aborting");
return;
}
}
$mysqlcheck_logs = "";
@unlink("/etc/artica-postfix/mysql.optimize.time");
@file_put_contents("/etc/artica-postfix/mysql.optimize.time", "#");
if (is_file($mysqlcheck)) {
exec("{$mysqlcheck} -A -1 2>&1", $mysqlcheck_array);
$mysqlcheck_logs = $mysqlcheck_logs . "\n" . @implode("\n", $mysqlcheck_array);
unset($mysqlcheck_array);
}
$q = new mysql();
$sql = "SHOW TABLES";
$results = $q->QUERY_SQL($sql, "artica_backup");
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$table = $ligne["Tables_in_artica_backup"];
$tt = time();
if (is_file($mysqlcheck)) {
exec("{$mysqlcheck} -r artica_backup {$table} 2>&1", $mysqlcheck_array);
$mysqlcheck_logs = $mysqlcheck_logs . "\n" . @implode("\n", $mysqlcheck_array);
unset($mysqlcheck_array);
}
echo $table . "\n";
if (is_file($myisamchk)) {
shell_exec("{$myisamchk} -r --safe-recover --force /var/lib/mysql/artica_backup/{$table}");
} else {
$q->REPAIR_TABLE("artica_backup", $table);
}
$q->QUERY_SQL("OPTIMIZE table {$table}", "artica_backup");
$time_duration = distanceOfTimeInWords($tt, time());
$p[] = "artica_backup/{$table} {$time_duration}";
}
$sql = "SHOW TABLES";
$results = $q->QUERY_SQL($sql, "artica_events");
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$table = $ligne["Tables_in_artica_events"];
$tt = time();
echo "Repair & optimize {$table}\n";
if (is_file($mysqlcheck)) {
exec("{$mysqlcheck} -r artica_events {$table} 2>&1", $mysqlcheck_array);
$mysqlcheck_logs = $mysqlcheck_logs . "\n" . @implode("\n", $mysqlcheck_array);
unset($mysqlcheck_array);
}
if (is_file($myisamchk)) {
shell_exec("{$myisamchk} -r --safe-recover --force /var/lib/mysql/artica_events/{$table}");
} else {
$q->REPAIR_TABLE("artica_events", $table);
}
$q->QUERY_SQL("OPTIMIZE table {$table}", "artica_events");
$time_duration = distanceOfTimeInWords($tt, time());
$p[] = "artica_events/{$table} {$time_duration}";
}
$t2 = time();
$time_duration = distanceOfTimeInWords($time1, $t2);
send_email_events("Maintenance on databases artica_backup & artica_events done {$time_duration}", "Operations has be proceed on \n" . @implode("\n", $p) . "\nmysqlchecks results:\n{$mysqlcheck_logs}", "system");
}
function _table_hours_perform($tablename)
{
if (!isset($GLOBALS["Q"])) {
$GLOBALS["Q"] = new mysql_squid_builder();
}
if (!preg_match("#squidhour_([0-9]+)#", $tablename, $re)) {
writelogs_squid("NOT AN HOUR TABLE `{$tablename}`", __FUNCTION__, __FILE__, __LINE__, "stats");
return;
}
$hour = $re[1];
$year = substr($hour, 0, 4);
$month = substr($hour, 4, 2);
$day = substr($hour, 6, 2);
$compressed = false;
$f = array();
$dansguardian_table = "dansguardian_events_{$year}{$month}{$day}";
$accounts = $GLOBALS["Q"]->ACCOUNTS_ISP();
if (!$GLOBALS["Q"]->Check_dansguardian_events_table($dansguardian_table)) {
return false;
}
if (!$GLOBALS["Q"]->FIELD_EXISTS("{$tablename}", "category")) {
$GLOBALS["Q"]->QUERY_SQL("ALTER TABLE `{$tablename}` ADD `category` VARCHAR( 60 ) NOT NULL ,ADD INDEX ( `category` )");
}
$sql = "SELECT COUNT(ID) as hits,SUM(QuerySize) as QuerySize,DATE_FORMAT(zDate,'%Y-%m-%d %H:00:00') as zDate,sitename,uri,TYPE,REASON,CLIENT,uid,remote_ip,country,cached,MAC,hostname,category FROM {$tablename} GROUP BY sitename,uri,TYPE,REASON,CLIENT,uid,remote_ip,country,cached,MAC,zDate,hostname,category";
if ($GLOBALS["VERBOSE"]) {
echo $sql . "\n";
}
$results = $GLOBALS["Q"]->QUERY_SQL($sql);
if (!$GLOBALS["Q"]->ok) {
stats_admin_events(1, "Fatal: {$GLOBALS["Q"]->mysql_error} on `{$tablename}`", @implode("\n", $GLOBALS["REPAIR_MYSQL_TABLE"]), __FILE__, __LINE__, "stats");
if (strpos(" {$GLOBALS["Q"]->mysql_error}", "is marked as crashed and should be repaired") > 0) {
$q1 = new mysql();
$q1->REPAIR_TABLE("squidlogs", $tablename);
stats_admin_events(2, "try to repair table `{$tablename}`", $GLOBALS["REPAIR_MYSQL_TABLE"], __FILE__, __LINE__, "stats");
}
return false;
}
$prefix = "INSERT IGNORE INTO {$dansguardian_table} (sitename,uri,TYPE,REASON,CLIENT,MAC,zDate,zMD5,uid,remote_ip,country,QuerySize,hits,cached,hostname,account,category) VALUES ";
$ip = new IP();
$SUM = mysql_num_rows($results);
$d = 0;
while ($ligne = @mysql_fetch_array($results, MYSQL_ASSOC)) {
$zmd = array();
while (list($key, $value) = each($ligne)) {
$ligne[$key] = mysql_escape_string2($value);
$zmd[] = $value;
}
$zMD5 = md5(@implode("", $zmd));
$accountclient = null;
if (isset($accounts[$ligne["CLIENT"]])) {
$accountclient = $accounts[$ligne["CLIENT"]];
}
$uid = $ligne["uid"];
if ($uid == null) {
$uid = $GLOBALS["Q"]->MacToUid($ligne["MAC"]);
if (is_numeric($uid)) {
$uid = null;
}
}
if ($uid == null) {
$uid = $GLOBALS["Q"]->IpToUid($ligne["CLIENT"]);
if (is_numeric($uid)) {
$uid = null;
}
}
$uid = mysql_escape_string2($uid);
$hostname = $ligne["hostname"];
if ($hostname == null) {
$hostname = $GLOBALS["Q"]->MacToHost($ligne["MAC"]);
if (is_numeric($uid)) {
$uid = null;
}
}
if ($hostname == null) {
$hostname = $GLOBALS["Q"]->IpToHost($ligne["CLIENT"]);
if (is_numeric($uid)) {
$uid = null;
}
}
if ($ip->isValid($hostname)) {
$hostname = null;
}
$hostname = mysql_escape_string2($hostname);
$d++;
$f[] = "('{$ligne["sitename"]}','{$ligne["uri"]}','{$ligne["TYPE"]}','{$ligne["REASON"]}','{$ligne["CLIENT"]}','{$ligne["MAC"]}','{$ligne["zDate"]}','{$zMD5}','{$uid}','{$ligne["remote_ip"]}','{$ligne["country"]}','{$ligne["QuerySize"]}','{$ligne["hits"]}','{$ligne["cached"]}','{$hostname}','{$accountclient}','{$ligne["category"]}')";
if (count($f) > 500) {
ToSyslog("{$dansguardian_table}: {$d}/{$SUM}");
$GLOBALS["Q"]->UncompressTable($dansguardian_table);
$GLOBALS["Q"]->QUERY_SQL($prefix . @implode(",", $f));
$f = array();
if (!$GLOBALS["Q"]->ok) {
stats_admin_events(1, "MySQL Error ", "{$GLOBALS["Q"]->mysql_error} on `{$dansguardian_table}`", __FILE__, __LINE__, "stats");
return;
}
}
}
if (count($f) > 0) {
$GLOBALS["Q"]->UncompressTable($dansguardian_table);
$GLOBALS["Q"]->QUERY_SQL($prefix . @implode(",", $f));
if (!$GLOBALS["Q"]->ok) {
stats_admin_events(1, "MySQL Error ", "{$GLOBALS["Q"]->mysql_error} on `{$dansguardian_table}`", __FILE__, __LINE__, "stats");
return;
}
$squid_stats_tools = new squid_stats_tools();
$squid_stats_tools->NoCategorize = true;
$squid_stats_tools->check_table_days();
}
stats_admin_events(2, "Success updating {$dansguardian_table} with {$d} events", __FILE__, __LINE__);
return true;
}
