php mysql amxbans csbans 1.6 expired bans remove

[Murz]

hello, maybe someone have scrip for auto remove expired bans. I found som old daemon code for amxbans 4.0

but it does not work

Спойлер: mv_bans.pl

Content-type: text/html


#    AMXBans, managing bans for Half-Life modifications
#    Copyright (C) 2003, 2004  Ronald Renes / Niek Albers
#
#               web             : http://www.xs4all.nl/~yomama/amxbans/
#               mail    : [email protected]
#               ICQ             : 104115504
#
#               This file is part of AMXBans.
#
#    AMXBans is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 2 of the License, or
#    (at your option) any later version.
#
#    AMXBans is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with AMXBans; if not, write to the Free Software
#    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
#

use strict;
use DBI;

my $dsn = {
     host     => '127.0.0.1',
    username => '',
    password => '',
    database => '',
};

main();

sub main {
   my $dbh = connect_db($dsn);

   my $sth = $dbh->prepare(
       q/ SELECT *
           FROM amx_bans
           WHERE ban_created + ban_length*60 < UNIX_TIMESTAMP()
           AND ban_length != 0 /
   );

   my $rv = $sth->execute();

   while ( my $r = $sth->fetchrow_hashref ) {
       my $sql = q/INSERT INTO amx_banhistory
           (player_ip, player_id,   player_nick,
           admin_ip,  admin_id,    admin_nick,
           ban_type,  ban_reason,  ban_created,   ban_length,
           server_ip, server_name, unban_created, unban_reason,
           unban_admin_nick)
           VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,'Bantime expired','amxbans')/;

       my $sth = $dbh->prepare($sql);
       my $rv  = $sth->execute(
           $r->{player_ip},  $r->{player_id},   $r->{player_nick},
           $r->{admin_ip},   $r->{admin_id},    $r->{admin_nick},
           $r->{ban_type},   $r->{ban_reason},  $r->{ban_created},
           $r->{ban_length}, $r->{server_ip},   $r->{server_name},
           time()
         )
         or die $DBI::errstr;

       $sth = $dbh->prepare(q/DELETE FROM amx_bans WHERE bid = ?/);
       $sth->execute( $r->{bid} );

   }

}

sub connect_db {
   my ($dsn) = @_;

   my @dsnlist = (
       'dbi:' . 'mysql' . ':dbname=' . $dsn->{database} . ';host=' .
         $dsn->{host},
       $dsn->{username}
   );
   return DBI->connect( @dsnlist, $dsn->{password} ) or die $DBI::errstr;

}

I am also use another code with cron and it works, but he is for remove expired admins so maybe its possible add function remove expired bans to

Спойлер: code

<?php $mysqli = new mysqli("127.0.0.1", "user", "paswword", "db name");
 
$mysqli->query("DELETE FROM `amx_amxadmins` WHERE `expired` < '".time()."' AND `days` != 0");
$mysqli->query("DELETE FROM `amx_admins_servers` WHERE `admin_id` NOT IN (SELECT a.id FROM `amx_amxadmins` a)");
?>

 

[fantom]

Murz, why do you need this?

 

[Murz]

Murz, why do you need this?

hosting sql problems. I want to Prune DB one time per week.

 

[Shel]

Which type of sql problems exactly do you have?

 

[Murz]

Which type of sql problems exactly do you have?

hosting size 300 MB

 

[Shel]

so, and ur bans db size is more than 300mb or what?

 

[Murz]

hosting providers limit that size
and yes
forum + stats + bans more than 300mb

 

[fantom]

Murz, removing 1-10 admins you will empty bytes, or kilobytes. Removing forum's data will empty more size

 

[Murz]

Murz, removing 1-10 admins you will empty bytes, or kilobytes. Removing forum's data will empty more size

expired bans max ban time in my servers 1 day

 

[Shel]

As fantom said bans db can't take too much space, you should try to remove your log's from forum and stats.

 

[Shel]

btw if you can, upload some dump (bans) for example without admins data and we gonna try to make some query.

 

[Murz]

fantom, Shel, you are right exported amx_bans.sql file and its only 457kb
I'll search in forum and stats logs and db where it disappears size
thanks for suggestions

 

[BlackSignature]

Murz to prune expired bans, you can try

$mysqli->query("DELETE FROM `amx_bans` WHERE `expired` != 0");

or

$mysqli->query("DELETE FROM `amx_bans` WHERE `ban_length` != 0 AND `ban_created` + (`ban_length` * 60) < unix_timestamp()");