Open Archive is long

[geckeon]

Hello,
There could be made faster to open Archive ?
Because I have several millions of lines and it makes time.
It's possible to improve this or maybe create a task to delete log upper than 7 days, because truncate is steep.
There could be made possible to choose by default the numbers of online display in Syslog or Archive perhaps by using users setting. And in this setting i don't understand what is "Salt".
Thx for help.
Geckeon.

[gadamo]

Hi,

The slowness in opening Archive, is a known issue and tracked down to be caused due to limitations in Yii, the framework used to develop the web interface. There is no pretty way to fix this, Yii will always do a count() upon opening the table to get the total number of records. We are looking into ways to counter that.

Occasionally trimming the archive is something we have already looked into and we have something in the works for this to be configurable from the web interface (within System Configuration). This functionality is implemented in pull request #30, which introduces 4 new keys in the System Configuration that control the "rotation".

To test Archive trimming, you can check-out the latest master. Make sure you a) import echofish-events.sql and b) create the appropriate keys in sysconf table as shown bellow:

insert into sysconf (id,val) VALUES 
('archive_activated','yes'),
('whitelist_archived','no'),
('archive_rotate','yes'),
('archive_delete_use_mem','no'),
('archive_delete_days',7),
('archive_delete_limit', 0)
ON DUPLICATE KEY UPDATE id=VALUES(id);

These values will configure echofish to only keep up to 7days worth of logs in Archive.

[gadamo]

The salt is random data used to encrypt/decrypt passwords for each user. Make sure you insert some random string there.

Literature: https://en.wikipedia.org/wiki/Salt_%28cryptography%29

[geckeon]

Thx for the explanation.

[gadamo]

@geckeon Have you checked out the new changes by any chance? Please let us know how this went, to close this one. We will keep you posted on work around the slowness issue.

[geckeon]

The archiving don't work. I add the new event and sysconf but nothing append, i always have all log in Archive.

[gadamo]

If i understood correctly... your archive remains empty (?). Check archive_activated, it should be set to 'yes'.

To test archiving and archive trimming, make sure you have the following set, in Settings => System Configuration:

    archive_activated   yes
    archive_delete_days 7
    archive_delete_limit    0
    archive_delete_use_mem  no
    archive_rotate  yes
    whitelist_archived  no

The e_rotate_archive event is scheduled to run daily and will trim entries older than archive_delete_days.

After configuring the settings above, please let us know the results by performing the following tests:

1. Check the min/max timestamps for entries in your archive:

    SELECT MIN(received_ts),MAX(received_ts),NOW() from archive
   

2. Make sure that a) mysql's event scheduler is ON and b) the event status is ENABLED:

   SELECT @@EVENT_SCHEDULER;
   SHOW EVENTS WHERE name='e_rotate_archive' \G;
   

Note: we are currently testing this feature as well in a testing environment, with archive_delete_days=2, so we should also have some results by tomorrow.

[geckeon]

When i send this request i have an answer.
SELECT id FROM archive WHERE received_ts < NOW() - INTERVAL 1 DAY;
But nothing append in archive table with the rotation event.
I deleted all ligne in archive_unparse and syslog table to be sure but no change.

[gadamo]

Did you mean to write "happened" instead of "append"? A bit confused here: If i understand correctly you DO receive new entries in the archive table (i.e. your archive table has records), but your older entries are NOT rotated. Right?

In our test environment, we also noticed that the event did not work. We pinned the problem to a syntax error in the SQL code for the event (echofish-events.sql, Line 57 missing 'DAY' in the end of the query).

Any installations that used the faulty event will have a temp table 'archive_ids' left-over from the broken event execution.

A fix is on the way, as we discuss how this is going to be handled for other users, with a clean procedure that will clear left-overs and such. In the process, we are going to make this event call a procedure that will contain all the code, which provides better grounds for testing (events only support scheduled background execution, while a procedure can be called at any time, even from the mysql CLI, to test).

Stay tuned,

[geckeon]

You understood well my request despite my bad english :)
I await your return.

[gadamo]

I will provide a quick fix for you, with only the required addition in Line 57 of echofish-events.sql and a 'DROP TABLE' in the end, until this is fixed in master:

1. Make sure you remove temp table 'archive_ids':

   drop table archive_ids;

2. Save this in a file, e.g. rotate-tempfix.sql:

   SET FOREIGN_KEY_CHECKS=0;
   SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
   SET time_zone = "+00:00";
   SET NAMES utf8 COLLATE 'utf8_unicode_ci';
   
   /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
   /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
   /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
   /*!40101 SET NAMES utf8 */;
   DELIMITER //
   
   DROP EVENT IF EXISTS e_rotate_archive//
   CREATE EVENT e_rotate_archive
   ON SCHEDULE EVERY 1 DAY COMMENT 'ROTATE OLD ARCHIVE ENTRIES' DO
   BEGIN
   IF (SELECT count(*) FROM sysconf WHERE id='archive_rotate' and val='yes')>0 THEN
   SET @archive_days=IFNULL((SELECT val FROM sysconf WHERE id='archive_delete_days'),7);
   SET @archive_limit=IFNULL((SELECT val FROM sysconf WHERE id='archive_delete_limit'),0);
   SET @use_mem=IFNULL((SELECT val FROM sysconf WHERE id='archive_delete_use_mem'),'no');
   IF @archive_days>0 THEN
     IF @use_mem != 'yes' THEN
       CREATE TEMPORARY TABLE archive_ids (id BIGINT UNSIGNED NOT NULL PRIMARY KEY);
     ELSE
       CREATE TEMPORARY TABLE archive_ids (id BIGINT UNSIGNED NOT NULL PRIMARY KEY) ENGINE=MEMORY;
     END IF;
   
     SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
     START TRANSACTION;
     IF @archive_limit > 0 THEN
         PREPARE choose_archive_ids FROM 'INSERT INTO archive_ids SELECT id FROM `archive` WHERE received_ts < NOW() - INTERVAL ? DAY LIMIT ?';
         EXECUTE choose_archive_ids USING @archive_days, @archive_limit;
     ELSE
         PREPARE choose_archive_ids FROM 'INSERT INTO archive_ids SELECT id FROM `archive` WHERE received_ts < NOW() - INTERVAL ? DAY';
         EXECUTE choose_archive_ids USING @archive_days;
     END IF;
     DEALLOCATE PREPARE choose_archive_ids;
     -- Ignore ID's from entries that exist on archive_unparse
     DELETE t1.* FROM archive_ids as t1 LEFT JOIN archive_unparse AS t2 ON t1.id=t2.id WHERE t2.id IS NOT NULL;
     -- Ignore ID's from entries that exist on syslog
     DELETE t1.* FROM archive_ids as t1 LEFT JOIN syslog AS t2 ON t1.id=t2.id WHERE t2.id IS NOT NULL;
     -- Ignore ID's from entries that exist on abuser_evidense
     DELETE t1.* FROM archive_ids as t1 LEFT JOIN abuser_evidence AS t2 ON t1.id=t2.archive_id WHERE t2.archive_id IS NOT NULL;
     DELETE t1.* FROM `archive` AS t1 LEFT JOIN archive_ids AS t2 ON t1.id=t2.id WHERE t2.id IS NOT NULL;
     COMMIT;
         DROP TABLE IF EXISTS archive_ids;
   END IF;
   END IF;  
   END
   //

3. Import the updated code:

   mysql -u root ETS_echofish < rotate-tempfix.sql
   #rm rotate-tempfix.sql

We are discussing other enhancements too for the rotation, but this should make your rotation working today.

This issue is left open until we provide a fix on master.

[gadamo]

@geckeon Please pull the latest changes from the master branch. The latest changes from @proditis, merged on #31, fix the issue.

After pulling the latest changes, import the affected files:

mysql -u root ETS_echofish <  schema/echofish-procedures.sql
mysql -u root ETS_echofish <  schema/echofish-events.sql

With those in place, you can manually test the procedure any time:

CALL eproc_rotate_archive();
SHOW WARNINGS;

Note: Those entries that have not yet been acknowledged (thus deleted from syslog table), will not be deleted from archive table either. Same applies for entries that are in-use for abuser evidence (in case you use this).

[geckeon]

When I call this procedure, I have this warning :
Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

[gadamo]

What product (mysql/mariadb) and which version are you running exactly? Can you post your /etc/my.cnf file to compare with ours? This might help spot what actually causes the problem. In the meantime we will look into it.