Export Roundcube Contacts to CSV from Database

In this installment I’ll show you how to export roundcube contacts to a CSV file straight from the Roundcube database. Perhaps a customer has moved to a different server, and no longer has access to their roundcube mail. You can restore the Roundcube database, and get the contacts manually.

First, you have to gain access to the MySQL database. I’ll leave that for another time. But lets assume you have it, and you want to export roundcube contacts to a CSV file so that you can import it into another Roundcube installation, or even many other compatible programs.

The first thing you have to do is create a header for the CSV file. CSV means Comma Separated Values. It’s just a bunch of stuff with comma’s in the between the values. That’s it. The first line in a CSV file is the header that contains the names of the columns presented in the following lines. We’re going to use Mozilla Thunderbird’s format for compatibility with Roundcube and other programs.

echo "First Name,Last Name,Display Name,Nickname,Primary Email,Secondary Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes," > email.address.contacts.csv

Copy and paste that line, and then you have the header. Now we need to get the content. This is the fun part!


Open up MySQL and select the roundcube database:

root@tidbits [~]# mysql roundcube
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1068
Server version: 5.5.39-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [roundcube]>

Now we’ll find the user_id of the email address we want to get the the contacts for.

MariaDB [roundcube]> select user_id from users where username='user@domain.com';
+---------+
| user_id |
+---------+
| 59 |
+---------+
1 row in set (0.00 sec)

There it is, ID 59. So now, we’ll export the contacts. Make sure to copy the line exactly, because the sed statements are very important:

mysql roundcube -qbse "select name,email from contacts where user_id=59;" |sed  's/\t/,/g' | sed 's/^/,,/g' >>  email.address.contacts.csv

Now, it’s complete. You can examine the results:

less  email.address.contacts.csv

Because you’ve added the correct headers to export roundcube contacts from the database, and placed the data correctly, you can import the CSV file into any mail program that accepts Thunderbird exports, including Roundcube itself. Enjoy!

Using a Linux Laptop as a Wireless Bridge

Is it possible to use Linux for a Wireless Bridge? You bet! Last night one of our USB wireless adapters kicked the bucket. Being down one, I decided to see if I could come up with a creative solution rather than plunk down another $20 that I may or may not have right now. One of the things in my inventory is a Linux laptop (Remember Project 50/50?) that has no problem connecting to my WiFi.

I suspected that there would be a way to route Ethernet traffic from my desktop PC (running Windows 7) through the Ethernet on the laptop, and through its WiFi connection. The laptop already has CrunchBang Linux running on it (a Debian derivative.) Thanks to IPTables, this was a simple task.

First, I ran an Ethernet cable between the Ethernet port on my desktop PC and the laptop. Because of Modern Technology I didn’t even have to use a crossover cable. I configured the PC with the IP of 192.168.2.2, Subnet Mask 255.255.255.0, Gateway of 192.168.1.1 and DNS of 8.8.8.8 and 8.8.4.4.

Then, with the WiFi already connected via the Linux GUI, I ran the following shell script as root:

ifconfig eth0 up
ifconfig eth0 192.168.2.1
echo "1" > /proc/sys/net/ipv4/ip_forward
iptables -t nat -A POSTROUTING -o wlan0 -s 192.168.2.0/24 -j MASQUERADE

Voila! We have a bridged connection. The only trouble was that Network-Manager kept putting eth0 down because it was not configured. This was solved by running

sudo nm-connection-editor

and then selecting “Wired conneciton 1” and then “Edit…” and then unchecking “Connect Automatically.” This solved that problem.

Since this is only a temporary solution, I haven’t set it up to automatically do the routing every time the system boots. But, it works for now and is a simple, elegant solution. Plus it saved me $20!

I hope this works well for you. If you have any questions, please don’t hesitate to comment below! 🙂

 

 

 

libgcc_s.so.1 must be installed for pthread_cancel to work — cPanel Apache error

The other day I ran into the error “libgcc_s.so.1 must be installed for pthread_cancel to work.” Apache on this cPanel server would produce “Internal Server 500” for any web page. When I checked the /usr/local/apache/logs/error_log, I found the following entry:

[Thu Jul 17 03:51:41 2014] [error] [client 12.34.56.78] libgcc_s.so.1 must be installed for pthread_cancel to work
[Thu Jul 17 03:51:41 2014] [error] [client 12.34.56.78] Premature end of script headers: index.php
[Thu Jul 17 03:51:42 2014] [error] [client 12.34.56.78] libgcc_s.so.1 must be installed for pthread_cancel to work
[Thu Jul 17 03:51:42 2014] [error] [client 12.34.56.78] Premature end of script headers: index.php
[Thu Jul 17 03:51:42 2014] [error] [client 12.34.56.78] libgcc_s.so.1 must be installed for pthread_cancel to work

The fix was only a Google search away. cPanel’s own forum had the fix here. I’ve consolidated the fix into a short shell script that you can just copy and paste. But first, understand what it does. In WHM, you can go to Home » Service Configuration » Apache Configuration » Memory Usage Restrictions and setup Resource Limits (RLimitMEM and RLimitCPU) to, well, limit CPU and memory resources.

The problem comes when sometimes, it doesn’t work as intended. So, you have to remove it. The easiest way is to edit /usr/local/apache/conf/httpd.conf, remove or comment out the RLimit lines, distill it and restart Apache. That’s what this script does, using sed to make the edit. It’s faster and it’s more precise, and it’s reversible (since I only commented out the lines rather than remove them.)

sed -i 's/^RLimit/#RLimit/g' /usr/local/apache/conf/httpd.conf
/usr/local/cpanel/bin/apache_conf_distiller --update
service httpd restart

You only need to log in to a shell prompt as root, and paste in the script. Make sure to press Enter after the “service httpd restart” to make sure Apache restarts. This should eliminate the error “libgcc_s.so.1 must be installed for pthread_cancel to work”.

Did this fix work for you? Share it with your friends using the link below, and leave a comment and let me know!