Auto clear log (or limit) of alert sensor.

[YellowSky]

Hi everyone,
i use some alert sensor to inform me:
The level of water inside tank
The rain alert from viewtopic.php?f=38&t=5903
...

But one problem is the size of log.

I would like to know if it is possible to clear log automatically. Or limit entry number of these log.

Thank you

[YellowSky]

I always look after this json command.
Is there anybody to help me?

[Egregius]

Why not delete it from database directly?

[YellowSky]

Of course it is an alternative (how to do that with domoticz running?) but the most common use for me is to perform this action each dayfor selected "sensor"

[Egregius]

I run this script every night:

Code: Select all

<?php
$db = new SQLite3('/volume1/@appstore/domoticz/var/domoticz.db');
$tables = array( 'LightingLog',
                'MultiMeter',
                'MultiMeter_Calendar',
                'Meter',
                'Meter_Calendar',
                'Percentage',
                'Percentage_Calendar',
                'Rain',
                'Rain_Calendar',
                'Temperature',
                'Temperature_Calendar',
                'UV',
                'UV_Calendar',
                'Wind',
                'Wind_Calendar');
foreach($tables as $table){
    $query=$db->exec("DELETE FROM $table WHERE DeviceRowID not in (select ID from DeviceStatus where Used = 1)");
    if ($query){
        $rows = $db->changes();
        if($rows>0)
            echo $rows." rows removed from $table<br/>";
    }
}
$query=$db->exec("DELETE FROM LightingLog WHERE DeviceRowID in (6,9,10)");
    if ($query){
        $rows = $db->changes();
        if($rows>0)
            echo $rows." rows removed from LightingLog<br/>";
    }
$sql = 'VACUUM;';
if(!$result = $db->exec($sql))
    die('There was an error running the query [' . $db->error . ']'); 

First part: remove all data from devices that are not used. Yes, also unused devices store data in the database.
Second part: remove all data from devices with idx 6,9 or 10 as I don't care about them.
Last part: vacuum the database to free unused space.

This script runs already a year, never had a problem with it.

[YellowSky]

Wow, thank you a lot.
I guess domoticz has to be stop before to perform that?

[Egregius]

I don't stop it.

[YellowSky]

Ok, thanks.
In my case, it does not work (unable to open it) (i think because php don't have the permission to access to the domoticz.db )

[mayyam]

I run this script every night:

Code: Select all

<?php
$db = new SQLite3('/volume1/@appstore/domoticz/var/domoticz.db');
$tables = array( 'LightingLog',
                'MultiMeter',
                'MultiMeter_Calendar',
                'Meter',
                'Meter_Calendar',
                'Percentage',
                'Percentage_Calendar',
                'Rain',
                'Rain_Calendar',
                'Temperature',
                'Temperature_Calendar',
                'UV',
                'UV_Calendar',
                'Wind',
                'Wind_Calendar');
foreach($tables as $table){
    $query=$db->exec("DELETE FROM $table WHERE DeviceRowID not in (select ID from DeviceStatus where Used = 1)");
    if ($query){
        $rows = $db->changes();
        if($rows>0)
            echo $rows." rows removed from $table<br/>";
    }
}
$query=$db->exec("DELETE FROM LightingLog WHERE DeviceRowID in (6,9,10)");
    if ($query){
        $rows = $db->changes();
        if($rows>0)
            echo $rows." rows removed from LightingLog<br/>";
    }
$sql = 'VACUUM;';
if(!$result = $db->exec($sql))
    die('There was an error running the query [' . $db->error . ']');

First part: remove all data from devices that are not used. Yes, also unused devices store data in the database.
Second part: remove all data from devices with idx 6,9 or 10 as I don't care about them.
Last part: vacuum the database to free unused space.

This script runs already a year, never had a problem with it.

I'm getting errors:

pi@domoticz:~ $ /home/pi/domoticz/scripts/php/clean_log_database.php
/home/pi/domoticz/scripts/php/clean_log_database.php: line 1: ?php: No such file or directory
/home/pi/domoticz/scripts/php/clean_log_database.php: line 2: syntax error near unexpected token `('
/home/pi/domoticz/scripts/php/clean_log_database.php: line 2: `$db = new SQLite3('/home/pi/domoticz/domoticz.db');'

[Egregius]

Are you sure you have php and php-cli installed?

There are different ways to run a php script.

a) script starts with just <?php: host it on a webserver and use curl.
b) start the script by /usr/bin/php /path/to/script.php
c) start the script with #!/usr/bin/php before the <?php

[mayyam]

Thanks for the asnwer.
Yes, i have the apache and php installed:

pi@domoticz:~ $ apachectl -V
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1. Set the 'ServerName' directive globally to suppress this message
Server version: Apache/2.4.10 (Raspbian)
Server built: Sep 17 2016 16:40:43
Server's Module Magic Number: 20120211:37
Server loaded: APR 1.5.1, APR-UTIL 1.5.4
Compiled using: APR 1.5.1, APR-UTIL 1.5.4
Architecture: 32-bit
Server MPM: prefork
threaded: no
forked: yes (variable process count)
Server compiled with....
-D APR_HAS_SENDFILE
-D APR_HAS_MMAP
-D APR_HAVE_IPV6 (IPv4-mapped addresses enabled)
-D APR_USE_SYSVSEM_SERIALIZE
-D APR_USE_PTHREAD_SERIALIZE
-D SINGLE_LISTEN_UNSERIALIZED_ACCEPT
-D APR_HAS_OTHER_CHILD
-D AP_HAVE_RELIABLE_PIPED_LOGS
-D DYNAMIC_MODULE_LIMIT=256
-D HTTPD_ROOT="/etc/apache2"
-D SUEXEC_BIN="/usr/lib/apache2/suexec"
-D DEFAULT_PIDLOG="/var/run/apache2.pid"
-D DEFAULT_SCOREBOARD="logs/apache_runtime_status"
-D DEFAULT_ERRORLOG="logs/error_log"
-D AP_TYPES_CONFIG_FILE="mime.types"
-D SERVER_CONFIG_FILE="apache2.conf"

pi@domoticz:~ $ php -v
PHP 5.6.29-0+deb8u1 (cli) (built: Dec 17 2016 06:04:43)
Copyright (c) 1997-2016 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies

pi@domoticz:~/domoticz $ sudo apt-get install php5-cli
Reading package lists... Done
Building dependency tree
Reading state information... Done
php5-cli is already the newest version.
php5-cli set to manually installed.

pi@domoticz:~ $ sqlite3
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Using second method:

pi@domoticz:~ $ sudo /usr/bin/php /home/pi/domoticz/scripts/php/clean_log_database.php
PHP Fatal error: Class 'SQLite3' not found in /home/pi/domoticz/scripts/php/clean_log_database.php on line 2

Using Third method (where first line= #!/usr/bin/php<?php):

sudo /home/pi/domoticz/scripts/php/clean_log_database.php
sudo: unable to execute /home/pi/domoticz/scripts/php/clean_log_database.php: No such file or directory

Using Third method (where first line= #!/usr/bin/php <?php):

pi@domoticz:~ $ sudo /home/pi/domoticz/scripts/php/clean_log_database.php
Could not open input file: <?php

First method i don't understand - just host on the webserwer results in blank page.
And there is the error in apche error.log:

[Thu Mar 16 12:36:05.184927 2017] [:error] [pid 745] [client 192.168.12.20:59203] PHP Fatal error: Class 'SQLite3' not found in /var/www/html/clean_log_database.php on line 2

And Domoticz directory looks like:

pi@domoticz:~/domoticz/scripts/php $ cd /home/pi/domoticz
pi@domoticz:~/domoticz $ ls
backups Config domocookie.txt domoticz domoticz.db domoticz.db-shm domoticz.db-wal domoticz.sh History.txt License.txt python-broadlink scripts server_cert.pem tmp updatebeta updaterelease www

EDIT:
The problem was:

pi@domoticz:~/domoticz $ sudo apache2ctl configtest
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1. Set the 'ServerName' directive globally to suppress this message
Syntax OK

I had to:
sudo nano /etc/apache2/apache2.conf
insert the line - ServerName myserver.mydomain.com - with my name of the server.
Then - apachectl configtest

pi@domoticz:~/domoticz $ apachectl configtest
Syntax OK

Then - sudo service apache2 restart
Then - sudo /usr/bin/php /home/pi/domoticz/scripts/php/clean_log_database.php

102 rows removed from LightingLog<br/>92 rows removed from MultiMeter_Calendar<br/>91 rows removed from Meter<br/>288 rows removed from Rain<br/>58 rows removed from Rain_Calendar<br/>92 rows removed from UV<br/>55 rows removed from UV_Calendar<br/>32552 rows removed from LightingLog<br/>pi@domoticz:~/domoticz $

Now its working
Bless You @Egregius

[waltervl]

What is wrong with the log history settings? Better use these than manual srcew up your database.....
https://www.domoticz.com/wiki/Applicati ... og_History

[waltervl]

In what table are those camera log rows? If they are in Lightinglog then they will be purged after the light/switch setting days, default 30 days. So you can decrease that setting and check what changed.