DeutschEnglish

Submenu

 - - - By CrazyStat - - -

20. September 2017

How to find out the real name belonging to an email address for 1 cent through paypal

Filed under: Security,Uncategorized — Christopher Kramer @ 18:36

Paypal allows you to transfer money to your friends and family. To do so, you just need the email or phone number of the recipient. I just did this and noticed something interesting: I just gave the email address of the recipient, but in the list of transactions, paypal shows the full real name of my friend as recipient, and also sent me a mail that contains the full name. This means, you can easily find out the real name assigned to some mail address that is used as a paypal account by just sending a cent to the address. Of course the recipient will notice it, but probably will not complain.

This is especially interesting as paypal itself says in the footer of every email that scam mails will usually not include your real name. But scammers could first send you a cent to find out your real name and then greet you with your real name in the scam mail.

It seems to be a good idea to use an email address for paypal that you use nowhere else. This way, it is not possible to find out your real name through your normal mail address. Additionally, this has the advantage that you can ignore any phishing mail that you receive in the name of paypal, but through your normal mail address and not your paypal mail address.

Recommendation

Try my Open Source PHP visitor analytics script CrazyStat.

13. June 2017

Putty: Automatically start an SSH tunnel without a window (headless)

Filed under: Windows — Tags: , , , , , , , — Christopher Kramer @ 21:23

You use Putty to tunnel some port through SSH to a foreign network but manually starting it again and again annoys you and the window gets in the way? Here is your solution. It requires admin rights and is tested on Windows 7 Professional only. Please let us know in the comments whether it works the same in your windows version.

  1. Set up your SSH tunnel in putty and save it as a session (e.g. with the name mytunnel). Make sure to use keys for authentication and that the key does not require a passphrase. Without a window there is no way to enter the passphrase of course ;-). If you worry about security, best restrict the rights of the user that you connect to as much as possible.
  2. Press WINDOWS + R and enter Taskschd.msc to start the Windows Task Scheduler. Accept the UAC prompt, if any.
  3. In the tree on the left, select the folder Task Scheduler Library.
  4. On the right, click Create task…
  5. Enter a name like SSH Tunnel.
  6. Choose Run whether user is logged on or not (this is important to make sure the window does not pop up).
  7. Click the checkbox Do not store the password. The process will only have access to local resources.
  8. Switch to the Triggers tab.
  9. Click the New… button
  10. Select a trigger that suits your needs. I chose Daily,  recur every 1 days, Repeat task every 30 minutes for the duration of 1 day and stop all running tasks at the end of repetition interval.  Click OK. This will restart the SSH tunnel every 30 minutes, so you might be left without connection for at most 30 minutes. You could add another trigger at startup or login if you need that.
  11. Switch to the Actions tab.
  12. Click the New… button.
  13. Choose Start a program as action.
  14. Use the Browse button to find plink.exe, which belongs to putty, you can download it here.
  15. Next to Add arguments, enter the name of your putty session (e.g. mytunnel).
  16. Click OK.
  17. You can adjust more settings like in the Conditions tab, unselect Start the task only if the computer is on AC power to make sure your tunnel also starts when your laptop is operating on battery, if necessary.
  18. Click OK.
  19. Right click your task and select Run to see if it works as expected. No window should come up but your ssh tunnel should be working.

Let me know if this saved your day and what you are using it for. I am using this to create an SSH tunnel for Sophos Endpoint Security and Control to fetch its updates from a server that is only accessible from an internal network.

9. June 2017

Automatically run WP-CLI as the correct user

Filed under: Linux,Server Administration,Wordpress — Tags: , , , , , , — Christopher Kramer @ 19:04

You are root, your php runs with a different user for each site / customer e.g. using PHP-FPM. And these users don’t have a shell assigned. So how can you easily run wp-cli with the correct user to avoid permission denied-problems?

This is how: Define an alias for wp in your shell config, e.g ~/.bash_profile like this:

alias wp='sudo -u `stat -c '%U' .` -s -- php /usr/local/wp-cli.phar --path=`pwd` '

This will first run stat -c %U . to get the owner of the current folder. Then it will pass this to sudo to execute the command as this user. You might need to adjust the path to the phar-file.

Relogin so the alias takes effect and have fun! 🙂

Now when you are in the folder of some site and run wp-cli with the wp command, it will always automatically run with the user that is the owner of the wordpress-folder, which should be the user assigned to this site.

This is tested on a Debian Wheezy system. On FreeBSD, you would need to adjust it slightly:

alias wp='sudo -u `stat -f '%Su' .` -s -- php /usr/local/wp-cli.phar --path=`pwd` '

Let me know if this saved your day or you still have some problems.

26. March 2017

Zimbra: fix corrupt index open_conversation in an mboxgroup MySQL-DB

Filed under: DBMS,Linux,Server Administration — Tags: , , , , , , — Christopher Kramer @ 20:50

Just wanted to upgrade a Zimbra server from 8.7.3 to 8.7.5. The upgrade always asks you whether to check database integrity. Even it was only a minor upgrade, I chose yes to be on the safe side. And it turned out the MySQL DB was indeed corrupt.

I had seen corrupt zimbra dbs a lot and the “MySQL crash recovery” guide in the zimbra wiki always helped out. But not this time.

I tried the crash recovery as explained in the wiki. When doing dumps, four mboxgroup-databases always failed because the index open_conversation of the table open_conversation was corrupt. As the guide explains, I increased innodb_force_recovery step by step from 1 to the maximum 6, but the error did not go away.

So here is what helped:

  1. Try to create the dumps as explained in the crash recovery guide. You will get errors like this:
    Dumped mboxgroup8
    mysqldump: Error 1712: Index open_conversation is corrupted when dumping table `open_conversation` at row: 0
    Dumped mboxgroup9

    This means that mboxgroup9 (not 8!) is corrupt. Write down all the mboxgroup numbers where an error appeared.

  2. Remove  innodb_force_recovery from the my.cnf if you inserted it
  3. Login as zimbra
    su zimbra
  4. Restart the mysql server
    mysql.server restart
  5. Load the MySQL account data into shell variables
    source ~/bin/zmshutil ; zmsetvars
  6. Log into MySQL using the root account
    mysql -u root --password=$mysql_root_password
  7. Open the first database that is corrupt:
    USE mboxgroup9;
  8. Repair the corrupt open_converstation table:
    OPTIMIZE TABLE open_conversation;

    Note: If this fails, check if you really removed innodb_force_recovery from the my.cnf!

  9. Go back to step 7 and open the next database that is corrupt until all have been repaired.
    Now exit the MySQL prompt:

    exit;
  10. You can now continue with the crash recovery, it should now create all dumps correctly. But if the open_conversation tables where the only corruption problem, you could also just stop here as this should have fixed the corruption. In my case, I jus started the upgrade again and let it verify message store database integrity again, and this time it completed with “No errors found”. 🙂
  11. Clean up the MySQL dumps
     rm -R /tmp/mysql.db.list /tmp/mysql.sql/

Please let me know if this helped you or if you have some additions.

26. February 2017

Postgrey: let nagios/icinga tell you what domains might need whitelisting

Filed under: Linux,Server Administration — Tags: , , , , , — Christopher Kramer @ 16:58

The problem: greylisting significantly delays mail from big mail providers with many servers

I still think greylisting with postgrey is very effective against spam. But now and then, mail from some senders get delayed for hours. This especially is the case for big mail service providers like Amazon SES, Microsoft Exchange Online Protection or Mailchimp. These providers have lots of servers and IP-addresses and the first attempt to deliver an email usually comes from a different server than the second. Thus, postgrey will not find a matching triplet and block the second attempt again. This will continue until one of the mail servers tried twice and postgrey found a matching tripled. This might take hours and thus cause significant delay of mails.

Therefore, if you want to use postgrey without delaying lots of mail for several hours, you need to whitelist these big email services with many servers. As new services come up and others go, you need some way to find out which servers try to send mail to your server and frequently get denied because no triplet is found.

The solution: Check your mail logs

To get an idea of what is happening and which servers might need whitelisting, I wrote a small command that analyzes your mail.log-files. Here it is:

grep -ohP 'reason=new, client_name=[^,.]+\.[^,.]+\.\K[^,.]+\.[^,]+' /var/log/mail.log* | sort | uniq -c | sort -nr | less

So what does this do?

It scans your /var/log/mail.log* files  for occurrences containing reason=new, which is what postgrey logs when no triplet was found. It then takes the domain of the server that is trying to send the mail to you, stripping the first subdomain, as this might vary from server to server that the mail service provider uses. It then sorts, counts and orders the result.

The result of this command might look like this (stripped after some lines):

    579 protection.outlook.com
    503 eu-west-1.amazonses.com
    113 facebook.com
     54 mcsv.net
     52 rsgsv.net
     48 mcdlv.net
     28 amazonses.com
     17 asianet.co.th

You should check especially all the lines that have a lot of occurrences. They might be domains from big email service providers that need whitelisting for the reasons explained above. For example, the mail from protection.outlook.com and amazonses.com is mail sent by customers of these mail providers that usually is not spam. Especially, the mail servers of these providers do retry delivery lots of times, so greylisting would not block spam anyway, it would just cause significant delays. So these domains should be whitelisted.

But the domains in this list might also only be domains that are used by an ISP for dynamic IP addresses that are actually sending spam. For example, asianet.co.th seems to be a big ISP from Thailand and multiple dynamic IP-addresses from this ISP tried to send spam, resulting in 17 occurrences where no triplet was found. This is mail that you want to be blocked/delayed.

Once you decided which domains you want to whitelist, you can do so by adding them in your whitelist-file (on Debian this is usually /etc/postgrey/whitelist_clients).

The entries should look like this:

/.*\.protection\.outlook\.com$/
/.*\.amazonses\.com$/
/.*\.facebook\.com$/
/.*\.booking\.com$/
/.*\.mcsv\.net$/
/.*\.rsgsv\.net$/
/.*\.mcdlv\.net$/
/.*\.mandrillapp\.com$/

Note: You should escape all dots with a backslash, as done in the example. Otherwise, a dot matches any character, so protection.outlook.com would also whitelist protectionAoutlook.com, which might be a domain that spammers register on purpose to get through servers that use inaccurate whitelists.

Automation with nagios/icinga

You could run the above command from time to time to check if new services need whitelisting. But it would be more easy, if you get notified when a new domain pops up in this list that you have not checked yet, right?

Therefore, I wrote a small nagios/icinga plugin that checks if any new domains appear on this list. It is a bit quick and dirty but does its job.

The main part is a bash script, that you could place in /usr/lib/nagios/plugins/check_postgrey_whitelist :

#!/bin/bash
log='/var/log/mail.log.1'
ignoreFile='/etc/nagios-plugins/postgrey_no_whitelist'

hosts=`grep -ohP 'reason=(new|early-retry[^,]*), client_name=[^,.]+\.[^,.]+\.\K[^,.]+(\.co|\.com)\.?[^,]+' $log | sort | uniq -c | awk '$1>=15{print $2}' | sort`;
noWhitelist=`cat $ignoreFile | sort`;

diff=`comm -23 <(echo "$hosts") <(echo "$noWhitelist") | sed ':a;N;$!ba;s/\n/ /g'`;

OK=0;
WARNING=1;
CRITICAL=2;
UNKNOWN=3;

if [ -z "$diff" ]; then
        echo "Okay, the top domains are whitelisted or ignored";
        exit $OK;
else
        echo "Whitelist or ignore these senders: $diff";
        exit $WARNING;
fi

This script ignores all domains listed in /etc/nagios-plugins/postgrey_no_whitelist  (one per line), assuming you checked that you do not want to whitelist these. So create this file as well and put the domains in that you checked and do not want to whitelist:

asianet.co.th
example.com

Now we need to define the command for this plugin:

Create the configuration where your other plugins are defined, e.g. /etc/nagios-plugins/config/postgrey_whitelist.cfg

define command {
        command_name    check_postgrey_whitelist
        command_line    /usr/lib/nagios/plugins/check_postgrey_whitelist
}

Now you need to create a service for your localhost that runs this command.

In your localhost service definition, e.g. /etc/icinga/objects/localhost_icinga.cfg , add this:

define service{
        use                             generic-service
        host_name                       localhost
        service_description             Postgrey Whitelist
        check_command                   check_postgrey_whitelist
}

Finally, make sure that nagios/icinga has read-access to your mail-logfile. You can adjust which file to check in the script above, I chose mail.log.1. So you might chown this file to the nagios user like this:

chown nagios:adm mail.log.1

Of course logrotation needs to know that new rotated files should have this owner. On a debian system, you can configure logrotate to do so in /etc/logrotate.d/rsyslog. Adjust it similar to this one:

/var/log/mail.info
/var/log/mail.warn
/var/log/mail.err
/var/log/daemon.log
/var/log/kern.log
/var/log/auth.log
/var/log/user.log
/var/log/lpr.log
/var/log/cron.log
/var/log/debug
/var/log/messages
{
        rotate 4
        weekly
        missingok
        notifempty
        compress
        delaycompress
        sharedscripts
        postrotate
                invoke-rc.d rsyslog rotate > /dev/null
        endscript
}

/var/log/mail.log
{
        rotate 4
        weekly
        missingok
        notifempty
        compress
        delaycompress
        sharedscripts
        create 440 nagios adm
        postrotate
                invoke-rc.d rsyslog rotate > /dev/null
        endscript
}

So I removed the mail.log from the first logrotate definition and created a new one below which is exactly the same but contains an extra line create 440 nagios adm to tell logrotate which user and permission to assign the logfiles to.

You might need to adjust some stuff depending on your distro, but I hope this helps somebody to set this up.

Update 06.03.2017: Adjusted script so it does not consider second-level domains like co.uk or com.br as single senders anymore.

14. December 2016

Microsoft and AOL sending DMARC aggregate reports (RUA) without DKIM signature

Filed under: Server Administration — Tags: , , , , , , , — Christopher Kramer @ 19:18

Just noticed that Microsoft and AOL are sending their DMARC aggregate reports without a DKIM signature. This alone is not uncommon, lots of small mail providers don’t use DKIM for their DMARC aggregate report mails. But the domains that Microsoft and AOL use as sender of their aggregate report mails have DMARC policies themselves. AOL even sets p=reject in their policy, which means that a mail server checking DMARC policies would reject aggregate reports sent by AOL if SPF fails, e.g. because the mail was relayed in between.

This is their DMARC policies:

_dmarc.aol.com.         2083    IN      TXT     
     "v=DMARC1\; p=reject\; pct=100\; rua=mailto:d@rua.agari.com\; ruf=mailto:d@ruf.agari.com\;"

_dmarc.microsoft.com.   391     IN      TXT     
     "v=DMARC1\; p=quarantine\; pct=100\; rua=mailto:d@rua.agari.com\; ruf=mailto:d@ruf.agari.com\; fo=1"

And this are the SPF records:

aol.com.                3190    IN      TXT     
     "v=spf1 ptr:mx.aol.com include:spf.constantcontact.com include:aspmx.sailthru.com include:mail.zendesk.com ~all"

abuse.aol.com.          227     IN      TXT     
     "v=spf1 ip4:204.29.186.192/26 ip4:204.29.187.0/27 -all"

microsoft.com.          1683    IN      TXT     
 "v=spf1 include:_spf-a.microsoft.com include:_spf-b.microsoft.com include:_spf-c.microsoft.com include:_spf-ssg-a.microsoft.com include:spf-a.hotmail.com ip4:147.243.128.24 ip4:147.243.128.26 ip4:147.243.1.153 ip4:147.243.1.47 ip4:147.243.1.48 -all"

AOL is sending its aggregate reports from abuse_dmarc@abuse.aol.com, and the SPF records of this subdomain has a strict “-all” (which aol.com doesn’t). Microsoft sends the reports from dmarcrep@microsoft.com, and microsoft.com also uses “-all”.

Google for example sends its aggregate reports with DKIM signature, so DMARC evaluation runs smooth. SPF is relaxed “~all” at google.com, but DMARC nowadays is “p=reject”, which I was a little surprised to see.

So in conclusion, if you really want to get your DMARC aggregate reports, you should not strictly follow the DMARC policies of the others, otherwise you might miss some reports…

And if you set up a mail server yourself that sends DMARC reports, don’t forget to sign those mails with DKIM as well.

15. November 2016

Typo3 Mask – Javascript and CSS includes of extensions missing

Filed under: JavaScript,Typo3 — Tags: , , , , , , — Christopher Kramer @ 11:36

I was using Mask 2.1.1 with Typo3 7.6.11 for a site as described in the Mask manual with this typoscript:

page < temp.mask.page
page.10.file.stdWrap.cObject.default.value = fileadmin/templates/index.html
page {
        includeCSS.styles = fileadmin/templates/style.css
}

The problem with this was that the CSS and JS files includes from extensions where missing, even though the static templates of this where included. The reason is that this typoscript overwrites the page.include* stuff defined by the templates.

A workaround

To solve this, I copied the page.include* stuff defined by the extensions in a temporary variable and restored it after loading the mask object into page:

tempJSFooterlibs < page.includeJSFooterlibs
tempJSFooter < page.includeJSFooter
tempCSS < page.includeCSS

page < temp.mask.page
page.10.file.stdWrap.cObject.default.value = fileadmin/templates/index.html
page {
        includeJSFooterlibs < tempJSFooterlibs
        includeJSFooter < tempJSFooter
        includeCSS < tempCSS

        includeCSS.styles = fileadmin/templates/style.css
}

There are more includes you might need to consider if your extensions use these:

page.includeJS
page.includeJSlibs
page.jsInline
page.jsFooterInline

Hope this helps someone with the same problem! Please let me know if there is an easier solution.

Update 06.07.2017: The clean way

So the workaround described above works, but it has a disadvantage: You might install a plugin that defines some other page.* property that gets lost. For example, I just had this problem with mindshape_cookie_hint: It defines page.9877 which gets lost when you overwrite page with temp.mask.page. It took quite some time until I found the reason for this problem. And then I found another way which is much simpler than the one described before:

page = PAGE
page.10 < temp.mask.page.10

Just don’t copy the whole temp.mask.page, only temp.mask.page.10. This will preserve all the stuff defined by plugins. The solution is so simple and easy!

So I think this is not really a bug in mask, it is rather a mistake in the documentation.

Please let me know in the comments if this saved your day or if you have suggestions.

17. August 2016

Linux Software Raid: Enlarge Raid Array of Hetzner EX41

Filed under: Linux,Server Administration — Tags: , , , , , , , , — Christopher Kramer @ 21:15

Recently, I ordered an EX41 server at Hetzner, which by default comes with two 4 TB HDDs in a software RAID1. I chose the minimal Debian Jessie Image. From Hetzner’s EX40, which has two 2 TB drives in a Software RAID1, I was used to find the whole drive formatted as one partition by Hetzner. But with the EX41, it turned out that the 4 TB were split: The root partition / was 2 TB of size and 1.7 TB were allocated to /home. As the application that should run on this server needs more than 2 TB space in one folder, I repartitioned the server. If somebody else has a similar issue, here is how it can be done:

  1. Edit /etc/fstab and remove or comment (by placing a # at the beginning) the line that mounts /home from /dev/md3
  2. Reboot into the Rescue system or some other system that is not on the same drive. (The Hetzner rescue system boots from network. On a local system, you would boot a linux from CD or USB.)
  3. Check the Raid status and setup:
    cat /proc/mdstat
    
    md3 : active raid1 sda4[0] sdb4[1]
          1777751872 blocks super 1.2 [2/2] [UU]
          bitmap: 0/14 pages [0KB], 65536KB chunk
    
    md2 : active raid1 sda3[0] sdb3[1]
          2111700992 blocks super 1.2 [2/2] [UU]
          bitmap: 0/16 pages [0KB], 65536KB chunk
    
    md1 : active raid1 sda2[0] sdb2[1]
          523712 blocks super 1.2 [2/2] [UU]
    
    md0 : active raid1 sda1[0] sdb1[1]
          16760832 blocks super 1.2 [2/2] [UU]

    So here md3 is the raid array that we want to get rid of together with its partitions sda4 and sdb4. md2 the raid array that we want to grow in size.

  4. First let’s get rid of the md3:
    mdadm --stop /dev/md3
    mdadm --zero-superblock /dev/sda4
    mdadm --zero-superblock /dev/sdb4
    parted /dev/sda rm 4
    parted /dev/sdb rm 4
  5. Resize the partitions of md2 (sda3 and sdb3):
    (Adjust the end if necessary)

    parted /dev/sda
    (parted) resize 3
    (END?) 4000GB
    (parted) quit
    
    parted /dev/sdb
    (parted) resize 3
    (END?) 4000GB
    (parted) quit

    Note: Tomas pointed out in the comments that the resize-command was removed and replaced with resizepart in current versions of parted. So the command is now:

    parted /dev/sda
    (parted) resizepart 3 4000GB
    (parted) quit

    (then do the same for sdb)

  6. Let the raid array md2 grow:
    mdadm --grow /dev/md2 --size=max
  7. Check the raid status:
    cat /proc/mdstat
    Personalities : [raid1]
    md2 : active raid1 sda3[0] sdb3[1]
          3888815376 blocks super 1.2 [2/2] [UU]
          [===================>.]  resync = 97.8% (3807048128/3888815376) finish=13.3min speed=101952K/sec
          bitmap: 1/15 pages [4KB], 131072KB chunk
  8. Wait until the resync finished (took a few hours for 2 TB):
    cat /proc/mdstat                                                                                                                                        
    Personalities : [raid1]
    md2 : active raid1 sda3[0] sdb3[1]
          3888815376 blocks super 1.2 [2/2] [UU]
          bitmap: 0/15 pages [0KB], 131072KB chunk
  9. Check the filesystem:
    e2fsck -fv /dev/md2
  10. Resize the filesystem:
    resize2fs /dev/md2
  11. Reboot into your normal system and you are done:
    reboot
  12. Check the free disk space:
    df -h

    It should give the new size of / (3.6 TB) and a lot more free space 🙂

Hope this helps somebody with the same or similar issue.

 

25. July 2016

Roundcube: Multiple identities (mail_domains) automatically created by default when user is created

The problem

Roundcube is a popular webmailer written in PHP. Today I configured it on a host where each user has multiple identities, one for each domain: E.g., user bob has the three identities:
bob@example1.org, bob@example2.org and bob@example3.org

The same for all other users.

When the user logs in for the first time in roundcube, all three identities should be automatically created. There is a config variable mail_domain, which can be used like this:

$config['mail_domain'] = 'example1.org';

This will make sure that when user bob logs in, the created identity will be bob@example1.org . You can give an array here, but only one domain per IMAP host. So using mail_domain, it seems that you currently cannot define multiple domains that will create different identities.

The solution

The solution that I found uses the plugin virtuser_query. This plugin lets you query mail identities from an SQL database. But within the SQL query, you can also statically configure all the domains like this:

$config['virtuser_query'] = array('email' => 
'SELECT "%u@example1.org" UNION 
SELECT "%u@example2.org" UNION 
SELECT "%u@example3.org"',
'user' => '', 'host' => '', 'alias' => '');

The plugin replaces %u with the username, e.g. bob. So this query will always return three rows, one for each domain. It is not really querying the database, it is just a workaround to configure multiple default domains in roundcube, as mail_domain currently does not allow us to do this.

For the plugin to work, you need to enable it like this:

$config['plugins'] = array(...,'virtuser_query'); 
// note: there is an underscore after virtuser

In my case the virtuser_query plugin was already included in Roundcube as I have installed the Debian package roundcube-plugins . If it is not already part of your roundcube installation, you can get in here.

I hope this helps somebody to solve this issue faster 🙂
Please leave a comment if it helped you, if you have problems with this, or if you found another way.

15. July 2016

Request-Tracker4: Enabling fast fulltext search with Sphinxsearch on Debian Jessie

I just successfully made fulltext search in our request tracker super fast. This is a guide for Debian Jessie and request tracker set up to use MySQL (or MariaDB).

  • Don’t forget to backup your RT database:
    mysqldump -p -u root rtdb > rtdb.sql
  • Log out of request tracker. I had some strange issue because I did not 😉
  • Make sure you are using request-tracker4 version 4.2.12 from jessie-backports. If you don’t already, this is how you update:
    – add this to the end of your /etc/apt/sources.list:

    deb http://ftp.debian.org/debian jessie-backports main

    – run

    apt-get update

    – install request-tracker4 from jessie-backports:

    apt-get -t jessie-backports install request-tracker4

    – follow the update procedure, make sure your database is upgraded
    (We need the package from backports as jessie’s rt4-db-mysql package depends on mysql-client, whereas the backports version depends on virtual-mysql-client and thus can also be easily used with MariaDB.)

  • Second, make sure you are using MariaDB instead of MySQL. MariaDB is a drop-in replacement for MySQL. The big advantage of MariaDB here is that it comes with the SphinxSE storage engine that we will use for the fulltext search. SphinxSE is also available for MySQL, but you would need to compile it yourself whereas MariaDB includes SphinxSE by default. MariaDB has also a lot more advantages. Switching to MariaDB is very simple, no need to convert databases or configuration. I used aptitude and selected to install mariadb-client and maridb-server. Then I checked the dependency resolutions and choose the option to uninstall mysql-client, mysql-server and related packages. Then everything went smooth automatically, I just needed to enter the root password again, everything else was updated automatically.
  • Now check if your RT is still working well with MariaDB.
  • Now enable the SphinxSE storage engine in MariaDB:
    mysql -u root -p
    [enter password]
    INSTALL SONAME 'ha_sphinx';
    SHOW ENGINES;
    EXIT;
  • Now install the Sphinxsearch daemon:
    apt-get install sphinxsearch
  • Now set up indexed full text search in RT:
    rt-setup-fulltext-index --dba root --dba-password secret

    (Let me know if you find a way without passing the password as a parameter)
    This asks you what type of index you want to use. Enter “sphinx”. Then keep the defaults.

  • The command above gave you code for /etc/request-tracker4/RT_SiteConfig.pm – like this:
    Set( %FullTextSearch,
        Enable     => 1,
        Indexed    => 1,
        MaxMatches => '10000',
        Table      => 'AttachmentsIndex',
    );

    Add / adjust this in RT_SiteConfig.pm.

  • Then you need to configure the index in sphinx. The above tool also gave you a config file for this, but it does not work with the current sphinxsearch version of jessie. So here is what works. Create an /etc/sphinxsearch/sphinx.conf like this:
    source rt {
        type            = mysql
    
        sql_host        = localhost
        sql_db          = rtdb
        sql_user        = rtuser
        sql_pass        = RT_PASSWORD
    
        sql_query_pre   = SET NAMES utf8
        sql_query       = \
            SELECT a.id, t.Subject, a.content FROM Attachments a \
            JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType = 'RT::Ticket' \
            JOIN Tickets t ON txn.ObjectId = t.id \
            WHERE a.ContentType LIKE 'text/%' AND t.Status != 'deleted'
    
    #    sql_query_info  = SELECT * FROM Attachments WHERE id=$id
    }
    
    index rt {
        source                  = rt
        path                    = /var/lib/sphinxsearch/data/rt
        docinfo                 = extern
    #    charset_type            = utf-8
    }
    
    indexer {
        mem_limit               = 32M
    }
    
    searchd {
        listen                  = 3312
        log                     = /var/log/sphinxsearch/searchd.log
        query_log               = /var/log/sphinxsearch/query.log
        read_timeout            = 5
        max_children            = 30
        pid_file                = /var/run/sphinxsearch/searchd.pid
    #    max_matches             = 10000
        seamless_rotate         = 1
        preopen_indexes         = 0
        unlink_old              = 1
        # For sphinx >= 1.10:
        binlog_path             = /var/lib/sphinxsearch/data
    }

    Make sure to adjust the mysql database, user and password.
    Note that there are some differences to the config provided by rt-setup-fulltext-index. Especially, you need to use listen instead of port. If you don’t adjust this, sphinxsearch will start on a different port and RT will not find any results in fulltext searches! If your RT is not behind a firewall, better also set listen to 127.0.0.1:3312 . Also, I used the Debian style paths from the Debian sample. The fields commented out are not necessary anymore in current sphinxsearch.
    UPDATE 26.08.2016: I changed the SQL Query to also index the Subject of the tickets and index all ‘text/%’ types and not only ‘text/plain’. This change is based on a comment by Claes Merin in the Request Tracker Mailing List. Thanks a lot. Without this change, the fulltext search will only search within ticket content, but not within ticket titles… Also, before it would not index text/html attachments.

  • Now run the indexer to build the initial index. This will take some time, for a 6 GB Attachments Table, it took not more than 30 minutes. Maybe run this in a screen in case your ssh connection breaks:
    indexer rt
  • Enable the Sphinx daemon. To do so, adjust START to yes in /etc/default/sphinxsearch
  • Finally, start the sphinxsearch daemon:
    /etc/init.d/sphinxsearch start
  • Now you can log into RT and do a fulltext search by entering in the search field:
    fulltext:search_word
  • In my case, this made the fulltext searches run within a second instead of 5 minutes 🙂
  • Make sure the indexer is run regularily to update the index. To do so, enter
    crontab -e

    And add a line like this:

    13 * * * * /usr/bin/indexer rt --rotate

I hope this helps somebody to setup this a little faster.

 

 

 

Older Posts »