Resolved fail2ban issue

[Dork]

Server operating system version

CentOS 7

Plesk version and microupdate number

18.0.49 Update #2,

sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "delete from bans where timeofban < strftime('%s', 'now', '-7 days')"
has no effects
sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "vacuum"
doesn't work.
am I doing something wrong?

 

[othmaqsa]

sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "delete from bans where timeofban < strftime('%s', 'now', '-7 days')"
has no effects
sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "vacuum"
doesn't work.
am I doing something wrong?

Take a look here, it may help you:

Question - Fail2ban sqlite database huge

We had a client's server run out of disk space last night. It looks like fail2ban had made several copies of its database for some reason until it ran out: -rw------- 1 root root 1232522240 May 4 09:18 fail2ban.sqlite3 -rw------- 1 root root 1232522240 May 3 21:03...

talk.plesk.com

 

[Dork]

Take a look here, it may help you:

Question - Fail2ban sqlite database huge

We had a client's server run out of disk space last night. It looks like fail2ban had made several copies of its database for some reason until it ran out: -rw------- 1 root root 1232522240 May 4 09:18 fail2ban.sqlite3 -rw------- 1 root root 1232522240 May 3 21:03...

talk.plesk.com

Sorry - but excactly that doesn't work (see my question)

 

[othmaqsa]

Sorry - but excactly that doesn't work (see my question)

you can try this:

sudo /etc/init.d/fail2ban stop
sudo rm -rf /var/lib/fail2ban
sudo /etc/init.d/fail2ban start
sudo reboot

 

[Dork]

you can try this:

sudo /etc/init.d/fail2ban stop
sudo rm -rf /var/lib/fail2ban
sudo /etc/init.d/fail2ban start
sudo reboot

Please - read m question again.
But thanks for your effort to help me out.

 

[othmaqsa]

I have read your question. You have a problem with Fail2ban, right ?

 

[Kaspar]

sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "delete from bans where timeofban < strftime('%s', 'now', '-7 days')"
has no effects
sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "vacuum"
doesn't work.
am I doing something wrong?

Your query seems right, although I haven't tested it. Note that you'll need the run the vacuum command directly after you've ran the query.

What is it you're trying to achieve exactly?

 

[Dork]

Your query seems right, although I haven't tested it. Note that you'll need the run the vacuum command directly after you've ran the query.

What is it you're trying to achieve exactly?

I`m on a virtual Server (Virtuozo) and the limit if numiptent is 2000.
Vey often the server is under havy attack (ssh, postfix) and the numiptent increases (> 2000).
In this case I have to delete the database table and the logfile by hand.
So I want to write a script that read the numiptent and start the "vacuum" if numiptent is > 1900.
The script should start via cronjob.

 

[Dork]

I don't need advice for writing the script - but as long as at least
sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "vacuum;"
doesn't work, as long it's useless to write the script

 

[Kaspar]

I see. What I actually meant to ask was what do you expect the query to do?

The query DELETE FROM bans WHERE timeofban < STRFTIME('%s', 'now', '-7 days') deletes all records (bans) older than 7 days. Do you actually have fail2ban configured with a ban period longer than seven days? If you have shorter ban period there won't be anything in the database to delete when you run that query.

 

[Dork]

I see. What I actually meant to ask was what do you expect the query to do?

The query DELETE FROM bans WHERE timeofban < STRFTIME('%s', 'now', '-7 days') deletes all records (bans) older than 7 days. Do you actually have fail2ban configured with a ban period longer than seven days? If you have shorter ban period there won't be anything in the database to delete when you run that query.

Yes - I know
but

sqlite3 /var/lib/fail2ban/fail2ban.sqlite3 "vacuum;"

should work independently of the other query - am I rght?

 

[Kaspar]

What do you expect vacuum to do on it own?

 

[Dork]

What do you expect vacuum to do on it own?

It should delete all records of the db table (like flush)

 

[Kaspar]

No, that's not what vacuum is used for. From How SQLite VACUUM Optimizes Your Database

The SQLite VACUUM command​

The VACUUM command does not change the content of the database except the rowid values. If you use INTEGER PRIMARY KEY column, the VACUUM does not change the values of that column. However, if you use unaliased rowid, the VACUUM command will reset the rowid values. Besides changing the rowid values, the VACUUM command also builds the index from scratch.

It is a good practice to perform the VACUUM command periodically, especially when you delete large tables or indexes from a database.

It is important to note that the VACCUM command requires storage to hold the original file and also the copy. Also, the VACUUM command requires exclusive access to the database file. In other words, the VACUUM command will not run successfully if the database has a pending SQL statement or an open transaction.

 

[Dork]

No, that's not what vacuum is used for. From How SQLite VACUUM Optimizes Your Database

OK - then I was in a wrong idea of the vacuum command; thanks for pointing it out
The database file will be minimized as all of the unused space is reclaimed back to the operating system - is that right?

 

[Kaspar]

Yes, that's correct.

 

[Dork]

Yes, that's correct.

So I wil use a customized "delete" command as a part of my script and the "vacuum" command as an addition (in order to optimize the db).
Thanks for your help!

Please mark the thread as resolved.