upgrade 1.23 to 1.24

[Ronald Top]

Hi Janos,

The first step gives me: [root@sys-piler01 piler-0.1.24]# mysql -u piler -p piler < util/db-upgrade-0.1.23-vs-0.1.24.sql Enter password: ERROR 1060 (42S21) at line 1: Duplicate column name 'ldap_id' [root@sys-piler01 piler-0.1.24]#

Can you help?

Thanks! Ronald

[Janos SUTO]

I think there must be a glitch somewhere. I installed the database tables from 0.1.23 release, then performed the database scheme upgrade, ie. mysql -u piler -p piler < util/db-upgrade-0.1.23-vs-0.1.24.sql, and it returned no error.

[Ronald Top]

Strange, I executed all statements from the script and the ones that are failing are:

MariaDB [piler]> alter table retention_rule drop index from; ERROR 1091 (42000): Can't DROP 'from'; check that column/key exists MariaDB [piler]> create unique index entry on retention_rule (domain,from,to,subject,_size,size,attachment_type,_attachment_size,attachment_size,spam); ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes MariaDB [piler]>

All other run find (except the ones that try to add a column, coz these are already part of the respective table)

Do I need the index? Guess it will slow down the DB when it’s not there ..

Any ideas?

Grtz

[Janos SUTO]

Well, I used a different blend of mysql, but it shouldn't matter.

I recommend you to drop the retention table, then recreate it according to piler-0.1.24/util/db-mysql.sql, and continue with the next sql modification in the upgrade file.

[Ronald Top]

Hi Janos,

OK, did so, table ‘retention_rule’ has been dropped and recreated.

Now, I can start with the searchd stuff, however, at which point do I need to upgrade piler itself? Is that after altering searchd?

At least, I assume at some point you have to run ./configure bla from the piler-1.24 source right (although this is not mentioned in the documentation)?

And is the config-site.php the only file I need to safeguard?

Grtz! Ronald

[Janos SUTO]

After finishing the schema upgrade to 0.1.24, then download the master branch, and execute mysql -u piler -p piler < util/db-upgrade-0.1.24-vs-0.1.25.sql

Then you can run ./configure ... which is actually mentioned in the installation docs (http://www.mailpiler.org/en/installation.html)

Regarding the gui you can overwrite anything, and don't worry about config-site.php, because the webui directory (in the piler source tree) doesn't have such a file.

[Ronald Top]

OK, so steps from 1.23 to master branche are: Upgrade db schema to 1.24 (done) Service stop searchd Alter searchd to delta-delta-delta main according documentation Service start searchd Service stop mailpiler Download masterbranch execute mysql -u piler -p piler < util/db-upgrade-0.1.24-vs-0.1.25.sql ./configure …. Make && make install Service start mailpiler

Right?

[Ronald Top]

Hi Janos,

Upgrade is done, I have 163 messages in the archive, After the upgrade I sent mails to a specific domain that is included in the domains list in piler. And I see an increment in messages processed. OK, guess so far, so good.

But how can I retrieve them? I just keep getting ‘Empty search result’. Details:

Am I missing something?

Grtz! Ronald

[Janos SUTO]

Go to the settings page (on the right upper corner), and check your email addresses. The gui shows you only those messages that you either sent or received.

If you cannot find anything by just hitting the search buttom, then try to login as auditor and perform a search.

[Ronald Top]

Hi Janos,

I see that my archive grows (214 messages now, really not too big, guess the archive_only_mydomains works!), but I cannot retrieve any mails. I get no hits. I created a user with an email address (also visible in the settings), but as I said no hits. Tried as an auditor, still no luck. I have no idea how to troubleshoot this. Do you have an idea? Maybe logs that contain some information?

I do have another question as well, suppose I have a user with multiple email addresses (some aliases, with other smtp domains), how is that handled when authenticating with ldap? The case above is with a piler user, didn’t tried out the ldap auth yet.

Any ideas?

Grtz,

Ronald

[Janos SUTO]

Show me the search query that is logged when you hit the search button? Look at the mail.log file, and search for "webui"

When a user is authenticated against an ldap database, then the gui queries all of his email addresses. So it knows which emails he's authorized to read.

[Ronald Top]

Hi Janos,

In which log file is the query logged?

Grtz, Ronald

[Ronald Top]

/var/log/maillog

sphinx query: 'SELECT id FROM main WHERE MATCH('@to ronaldXtopXofficeunlimitedXnl') ORDER BY sent DESC LIMIT 0,1000 OPTION max_matches=1000' in 0.00 s, 0 hits

Seems there’s something wrong from with the WHERE clause. The left operator is missing?

And if I filter on mydomains, what is logged? All messages to and from those domain I guess?

Grtz, Ronald

[Ronald Top]

sphinx query: 'SELECT id FROM main WHERE MATCH('@to ronaldXtopXofficeunlimitedXnl') ORDER BY sent DESC LIMIT 0,1000 OPTION max_matches=1000' in 0.00 s, 0 hits

And:

MariaDB [piler]> select * from main; ERROR 1146 (42S02): Table 'piler.main' doesn't exist MariaDB [piler]>

Looking in db-mysql.sql the table ‘main’ does not exist and is not created from that script.

[Ronald Top]

Querying against v_messages does give results (although I somehow cannot simply replace main with v_messages in the query. That does not work either).

[Janos SUTO]

I think the problem is that you try to select from an index called "main".

Please edit config-site.php, and set the following:

$config['SPHINX_MAIN_INDEX'] = 'main1,dailydelta1,delta1';

Then run the query again. Btw. this query is used by searchd not by mysql. To try it for yourself, connect to searchd by "mysql -h 127.0.0.1 -P 9306", then execute the query.

[Ronald Top]

BANG! Workz! Great!

I like it this way better than via OVA’s. Now I learn some more!

I let you know about the next steps!

Thanks again!

[Ronald Top]

Is there a way to index 'known format' attachments like pdf or docx??

[Janos SUTO]

By default the parser extracts textual data from pdf and docx as well. In order to do that both libzip and the pdf2text utility (poppler package) must be installed.

When you run "configure" then it prints a summary what utilities it has found and will use. To test it just get an EML format email with a pdf or docx attachment, then run pilertest on it, and verify if it can find the attachment text.