Archive/Retention rules Not Creating

[Micky Arter]

Hi,

We are having an issue where we cannot add any archiving/retention rules via the GUI. Could you please advise how we can get this issue resolved. If we create a rule and click add nothing happens. we have tried various different browsers.

thanks

[Janos SUTO]

Locate the query() function in system/database/mysql.php, and add the following line to it:

syslog(LOG_INFO, "sql=$sql");

Then try to add a rule, then observe the actual sql query in the mail log. Finally go to the mysql console, and issue the very same command (substitue the bound parameters of course), and check if it's executed properly.

[Micky Arter]

Hi, Sorry for the late response I have added the line and check the logs but am seeing the following:

Jul 6 10:54:56 scan002 piler[25986]: connection from 192.168.50.75

Jul 6 10:54:56 scan002 piler[25986]: connection from 192.168.50.75

Jul 6 10:55:04 scan002 piler-webui[2102]: sql=

Jul 6 10:55:31 scan002 piler-webui[2102]: sql=

Jul 6 10:55:31 scan002 piler[2823]: reloaded config: /usr/local/etc/piler.conf

It looks to me that it not building the SQL string. I have not been able to try the direct SQL input as I am not familiar with you database table/field names.

[Janos SUTO]

OK, is a remote session possible via teamviewer, etc? If so, then find me on Skype (janos.suto)

[Janos SUTO]

There was a missing column in the rule table. Fixed.

[Jose M. Albarran]

Hi, I'm have just done a fresh installation in Ubuntu 16.04 LTS, and I'm having the same issue. Please, could you tell me the table/column to check if it's already created?

Thanks for the tool!!

[Janos SUTO]

Check the archiving_rule and the retention_rule tables. Anyway what version do you use? Try the master branch if doesn't work with 1.1.1.

[Jose M. Albarran]

Thanks! Sorry, I forgot: I'm using versión 1.1.1. I will compare the table structure with master branch.

Thanks!

[Jose M. Albarran]

Hi, I'm having the same issue, and I'm not able to debug it

I have added a log, and I see the INSERT. If I test the INSERT standalone, it works, but inside webui not.

See (log extract - INSERT works, but no INSERT (records=0) done

Aug 26 14:54:05 irulan piler-webui[3979]: SQL: INSERT INTO folder_rule (domain,from,to,subject,body,_size,size,attachment_name,attachment_type,_attachment_size,attachment_size,spam,folder_id) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?) Aug 26 14:54:05 irulan piler-webui[3979]: SQL DATA: Array ( [0] => [1] => [2] => [e-mail] [3] => [4] => [5] => > [6] => [7] => [8] => [9] => > [10] => [11] => 0 [12] => 96 ) Aug 26 14:54:05 irulan piler-webui[3979]: SQL Return records=0

==== and, for reference, this is the code I'm using for log/debug (function query in mysql.php) ===== public function query($sql, $arr = array()) { // JMA: Added log syslog(LOG_DEBUG, "SQL: $sql"); syslog(LOG_DEBUG, "SQL DATA: " . print_r($arr,TRUE));

  $query = new stdClass();

  $query->error = 1;
  $query->errmsg = "Error";
  $query->query = $sql;

  $time_start = microtime(true);

  $i = 0;
  $data = array();

  $s = $this->link->prepare($sql);
  if(!$s) { return $query; }

  try {
     $s->execute($arr);
  }
  catch(PDOException $exception) {
     // JMA: Added log
     syslog(LOG_ERR, "SQL Exception=$exception"); 
  }
  $this->affected = $s->rowCount();

  // JMA: Added log
  syslog(LOG_DEBUG, "SQL Return records=$this->affected");

  $R = $s->fetchAll();

  while(list ($k, $v) = each($R)){
     $data[$i] = $v;
     $i++;
  }

  $query->row      = isset($data[0]) ? $data[0] : array();

[Martin Henne]

Hi, i have the same problem. Ubuntu 16.04. LTS fresh install then

sudo -i

apt update
apt upgrade

dpkg-reconfigure dash 
dpkg-reconfigure locales
update-locale LANG=de_DE.UTF-8

apt install ntp sphinxsearch tcpd curl php-ldap php-gd php-memcache php-pdo php-mysql php-curl tre-agrep libzip-dev sysstat catdoc poppler-utils unrtf tnef memcached php-memcached gcc make openssl libssl-dev libtre5 libtre-dev libmysqlclient-dev

a2enmod rewrite
service apache2 restart 

groupadd piler
useradd -g piler -m -s /bin/sh -d /var/piler piler
usermod -L piler
chmod 755 /var/piler

wget https://bitbucket.org/jsuto/piler/downloads/piler-1.2.0.tar.gz
tar zxvf piler-1.2.0.tar.gz 
cd piler-1.2.0/

./configure --localstatedir=/var --with-database=mysql
make
su -c 'make install'
ldconfig

make postinstall 

/etc/init.d/rc.piler start
/etc/init.d/rc.searchd start

update-rc.d rc.searchd enable
update-rc.d rc.piler enable

creating any kind of rule is not possible. Nothing is saved. The mysql debug shows:

Dec 11 18:45:41 mailarchive piler-webui[2938]: sql=INSERT INTO archiving_rule (`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`) VALUES(?,?,?,?,?,?,?,?,?,?,?), arr=desired-domain.com;;;;>;;;;>;;-1
Dec 11 18:45:41 mailarchive piler-webui[2938]: sql=INSERT INTO archiving_rule (`from`,`to`,`subject`,`body`,`_size`,`size`,`attachment_name`,`attachment_type`,`_attachment_size`,`attachment_size`,`spam`) VALUES(?,?,?,?,?,?,?,?,?,?,?)
Dec 11 18:45:41 mailarchive piler-webui[2938]: sql=SELECT * FROM archiving_rule ORDER BY id, arr=
Dec 11 18:45:41 mailarchive piler-webui[2938]: sql=SELECT * FROM archiving_rule ORDER BY id
Dec 11 18:45:41 mailarchive piler-webui[2938]: sql=SELECT * FROM customer_settings WHERE domain=(SELECT mapped FROM domain WHERE domain=?), arr=local
Dec 11 18:45:41 mailarchive piler-webui[2938]: sql=SELECT * FROM customer_settings WHERE domain=(SELECT mapped FROM domain WHERE domain=?)

Any idea?

[Janos SUTO]

The bug mentioned above is fixed by now, it should work. Try to drop the archiving_rule table, recreate it (see util/db-mysql.sql for the schema), and try adding a new archiving rule. Let me know how it goes.

[Martin Henne]

Thanks for your fast reply. I deleted the whole piler table and reimported the sql according to procedure you described in "cleaning piler". Nothing changed. I am using piler-1.2.0.tar.gz.

[Janos SUTO]

OK, I'll do a fresh install, and see it for myself.

[Martin Henne]

adding the following to mysql.cnf fixed it.

#!
[mysqld]
sql-mode    = 

Deleting still not possible!

[Martin Henne]

Deleting is possible. It is just a tricky UI-Design on the confirmation page. The confirmation link looks more like a heading than a button ...

[Janos SUTO]

I managed to find the fix: fix model/policy/archiving.php, and locate the add_new_rule() function. Then fix $data['attachment_size'] to be (int)$data['attachment_size'] and $data['size'] to be (int)$data['size'], and check again.

[Martin Henne]

Yes, you fixed it. Thanks a lot. Great job :-)

[Martin Henne]

The problem remains with the retention page! Adding (int) there doesn't fix the bug!

[Janos SUTO]

Probably (int) is required at the 'day' option. Check out this commit: https://bitbucket.org/jsuto/piler/commits/eb4613593255a00e55fb5fc52f4d3b9d7a29da79

[Martin Henne]

Yes. It's working without the mysql.cnf mod now. This topic solved :-) But i have to keep the mysql.cnf mod at the moment because of another bug in the reporting pages ...

[Janos SUTO]

If there's another bug, then create a new issue. We'll fix it.