So I have a Trac install and wanted to be able to open tickets with an email. No problem. Some nice person has written a python script to do so. I download it and go about installing it and configuring it on CentOS 4. After configuring it I need to setup an MTA to feed it. I choose Postfix 2.2.10 since it was already on the machine. To setup Postfix with the script you just put a line in your aliases file that pipes the incoming email to the trac2email script.
Since the Trac files and db's are owned by the web server user apache I need the Trac script to be run as the apache user. Postfix makes this really simple. It's best described by the group that makes the script.
You can run a delivery command as any user just by placing the aliases in a separate file and chowning that aliases file to the user you want the delivery to run as. Then you run postalias /path/to/aliases, which will create /path/to/aliases.db, and you then just add hash:/path/to/aliases to the alias_maps config variable in main.cf. The only caveat is that the user who owns the separate aliases file needs to have write perms to the directory that it's stored in.
So I make an aliase file and put in the following line.
tickets: "|/usr/bin/email2trac [--project=]"
Then chown it to the apache user:group and run my postalias command on it. I put the hash line in the alias_maps config and restarted postfix. That's when the problem started. When sending an email to the alias I was getting the error "db.commit() pysqlite2.dbapi2.OperationalError: database or disk is full".
I check if the disk is full and it is not even near full. I know the SQLite db does not have a set limit so I'm totally perplexed by this error message. I decide to run an strace on the script and see what it's actually doing. To do this you can just put "strace -o /tmp/trace" right after the | in the pipe command. I send another email and look at the trace file. I find the interesting error when the script tries to write to the SQLite db. The error is "write(3, ...)= -1 EFBIG (File too large) --- SIGXFSZ (File size limit exceeded)". (3,...) is the sqlite.db file in the trace.
File to large tells me there is a limit somewhere and I need to find out how to bump it up. Making a new Trac site and running the same script the email goes through fine. Other existing Trac sites on the same server work fine with the emal2trac script also. It's now down to just this one site that does not work.
Finally I start zeroing in on the SQLite db size. Most of the other sites have smaller db's which are like 10 to 40 megs. The Trac site with the problem has a 60meg db. Now why can the script write to a 40meg db but not a 60meg db?
I crack open the Postfix mail.cf file and start looking at different size limits in there for different things. I know I"m looking for something possibly in the over 40meg range. The one that sticks out is the mailbox_size_limit setting. It's set to its default of 51200000 bytes. That's 52meg. I change it to 0 which is unlimited and save and exit. Restart Postfix. Send another test email and BAM! Mail goes right through.
Somehow in Postfix mailbox_size_limit is not just the mailbox size limit. It also seems to be connected to any file the pipe process opens. If a file is opened bigger than the limit set it will not be able to write to the opened file. This process limit by Postfix makes it look to SQLite like the disk is full. I only tested this with 2.2.10 of postfix. It might be different later versions.
Del.icio.us! | Digg Me! | Reddit!
What is a SheevaPlug? It is a network appliance that is fully enclosed in an AC power plug or AC adapter. It has a gigabit Ethernet port, SD memory slot, usb port, and mini-usbport. You can plug it right into the wall just like an ac adapter. All for $99.
It comes with 512 Meg ram and 512 Meg of flash based disk space. It runs a ARM processor and comes with Ubuntu Linux installed.
More info about the SheevaPlug can be found here.
I used this little plug computer to setup a backup mail server. You could use it for just about anything you like since it runs a full Linux distribution. Here is the basic setup I used to get everything going.
Connect to the SheevaPlug from a linux box using the usb cable that came in the box. This connection will be your serial console (USBtoSerial). To get the usb serial thing going I had to insert a few modules on my Linux box. Everything worked fine after that. I have also read that you can just plug it in and it will get a DHCP address off the network if you have a DHCP server. Then you can just ssh in from there.
sudo modprobe usbserial sudo modprobe ftdi_sio vendor=0x9e88 product=0x9e8f
Install the simple call up (cu) program to connect to the emulated serial port ttyUSB1. Then connect.
sudo apt-get install cu cu -s 115200 -l /dev/ttyUSB1
Login as root with the default password.
Login: root Pass: nosoup4u
Change your root password
passwd
Add a new user.
adduser newuser
Use visudo and insert the line below to give your new user full sudo privs.
# add user in visudo newuser ALL=(ALL) ALL
Then turn on the setuid bit on the sudo program so you can actually use sudo. Sudo will not let you sudo to root if you don't do this.
chmod u+s /usr/bin/sudo
Edit /etc/network/interfaces. Comment out DHCP line. Put in static ip info.
# iface eth0 inet dhcp auto eth0 iface eth0 inet static address 192.168.1.60 netmask 255.255.255.0 network 192.168.1.0 broadcast 192.168.1.255 gateway 192.168.1.1
Restart networking.
/etc/init.d/networking restart
Edit /etc/resolv.conf and put in your ISP's DNS servers
domain yourdomain.org search yourdomain.org nameserver 192.168.1.1
The Ubuntu 9.04 that comes with the plug computer puts it's apt-get cache dir on a temporary file system. It's missing a dir so apt-get will not work until you make the dir. This line is in the /etc/rc.local file but it does not seem to execute on boot for some reason.
mkdir -p /var/cache/apt/archives/partial/
Install the Postfix SMTP server. This is the main reason for this box. You will have to put in your Posfix configs here. If you need examples see the menu on this site in the Postfix section. You can also search this site for the word Postfix.
apt-get install postfix apt-get clean all # put in your config files /etc/init.d/postfix start
I use procmail as my MDA so I'll install it.
apt-get install procmail apt-get clean all
Install ddclient so we can update the dynamically changing ip of our server.
apt-get install ddclient apt-get clean all
Edit /etc/default/ddclient file. Change run_daemon to true and daemon interval to what you want.
run_daemon="true" daemon_interval="1800"
Then copy over your /etc/ddclient.conf file. An example one is below. This example uses a custom domain in the example. The backup mail server.
daemon=1800 # check every 600 seconds syslog=yes # log update msgs to syslog mail=root # mail all msgs to root mail-failure=root # mail failed update msgs to root pid=/var/run/ddclient.pid # record PID in file. cache=/var/run/ddclient.cache # Cache file ssl=yes # use ssl-support. use=web login=dynloginname # default login password=xxxxxx # default password protocol=dyndns2 server=members.dyndns.org custom=yes, backupmail.yourdomain.org
Install OpenNTPd. For some reason OpenNTPd needs a date near the real date to work. So we will set one close to the real date. Then update the hardware clock.
date 012618002009 hwclock -w apt-get install openntpd
Edit the /etc/default/openntpd file and put in the -s option. This is so OpenNTPd will set the time on start.
DAEMON_OPTS="-s"
Restart OpenNTPd.
/etc/init.d/openntpd restart
Install pflogsumm because I like mail stats. Install mailx because the mail package is to big and mailx does what we need. This will make a symlink to mail.
apt-get install pflogsumm mailx
Run crontab -e and put this line in cron for pflogsum to run stats each night.
# crontab -e 15 0 * * * /usr/sbin/pflogsumm -d yesterday /var/log/mail.log 2>&1 |/usr/bin/mail -s "`uname -n` daily mail stats" root
The Linux build for the SheevaPlug does not have iptables by default. You could put an updated kernel in if you want it but let's keep this simple. We just want to filter hosts that connect to sshd. So we can do it with tcp wrappers. We will allow only hosts who's DNS PTR records (reverse look up) resolve to my.isp.org. Edit the /etc/hosts.allow file and put this in changing it for your ISP. Remember to allow your local hosts to connect ot sshd also.
sshd : .my.isp.org : allow sshd : 192.168.1. : allow sshd : localhost : allow sshd : ALL : deny
Last but not least. Remember to forward port 22 and 25 on your ISP's router to the internal static ip you gave your SheevaPlug.
Just reboot and test. I can say it works great for me. Cheap, silent, very low power, Linux. What more could you ask for?
Del.icio.us! | Digg Me! | Reddit!
SQLite is an embedded open source relational database (db). It is very portable, easy to use, compact, efficient, and reliable. Being an embedded database it becomes part of the program that hosts it. It is embedded in many popular programs used today. Most use the SQLite C API to interface with the SQLite db. Many scripting/programming languages use the API like the Perl module DBI::SQLite, PHP's data objects with the SQLite driver, or just straight C programs. Not only can these languages (and many more) use the SQLite C API to access the SQLite db but most OS's have a statically linked native binary that can also be controlled completely from the command line. How cool is that?
SQLite can be installed by going to the SQLite website and downloading a copy and installing it. They have binaries for most major platforms (Windows, MAC, or Linux). If your using a OS with a package manager then there is a good chance you can just install it by using your package manager.
There are 2 versions of SQLite. Version 2 and version 3. Version 2 is (as you may have guessed) is the older version. SQLite 3 databases are not compatible with SQLite 2 databases. Use SQLite 3 if at all possible. If your going to install the SQLite command line version then here is something to remember. The package for SQLite 2 is just called "SQLite". The package for SQLite version 3 is called "SQLite3". These are also the names for the command line binaries. You access a version 2 db with the "SQLite" binary (program). You access a version 3 db with the "SQLite3" binary.
Here are some examples of installing SQLite version 3 on some Linux and BSD's.
sudo apt-get install SQLite3
yum install SQLite3
pkg_add -v ftp://ftp.openbsd.org/pub/OpenBSD/4.4/packages/i386/SQLite3-3.5.9p0.tgz
You get the idea. Just install the SQLite3 package for whatever OS you have. If your OS does not have a package just go to the SQLite website then download and install their binary.
The following commands will be using the statically linked command-line program. This is what was installed in the install section above. You can completely control an SQLite database with this program and it's special built in shell. Some commands in the following examples will be invoked from a Linux shell and and from the SQLite shell. The SQLite shell will consider any command with . (period) in front of it a SQLite command. You can get a complete list of valid SQLite commands by typing .help at the SQLite shell. The commands that don't begin with a period are just regular SQL commands. They should work with most databases.
The examples below are done from a Linux machine with version 3.5.9 of SQLite.
Below when you see user@host:~$ it means execute the command from the shell. When you see sqlite> it means from a SQLite prompt (shell) after selecting a db.
This command will not make the db (the file test.db) until you create something in it (Ex: table or view) or until you run a few select commands from the SQLite prompt (like .database). This is so you can change things like page size or character encoding before the database structure is written to the file. After executing the command you will see a SQLite prompt. Make a table or a view to have the file actually written to disk.
If you already have a database file called test.db then you can select and access it with the same command. In summary if the db file does not exist this command will create it and if it does exist then it will select it to be accessed.
user@host:~$ sqlite3 test.db
Before we start creating and looking at data we are going to change a few default settings to make the ouput of the commands look better. We will turn on column mode (.mode col). There are many other modes other than col. See .help for more output modes. We will also turn on column headers (.headers on). Both stay on until you exit the SQLite shell or change them to something else.
sqlite> .mode col sqlite> .headers on
Let's make a table called test. After the table is made the database file will exist on disk. The table named test and have 2 columns. One called ids and one called value. The ids column is an integer primary key, SQLite will make this an auto increment column. Which means if there is no number then SQLite will generate a number for the column on the first insert. After the first insert it will auto increment the numbers from there. All of this is done with the SQL create command.
sqlite> create table test (ids integer primary key, value text);
Let's create a view. Views are like virtual tables. People also call them derived tables. This is because their contents are derived from other tables. Views look like regular tables, but they are not. Views are dynamically generated. A view is made up of relational expressions and takes other tables to produce a new table.
The following example will create a view called testview and will select everything from the test table we just created. It's a silly example of what views are good for but it will work. Most views are good for queries you keep using over and over again. You create the view to use as a temporary table and then query the view just like a table. The view eliminates the need to keep using that same redundant query.
Creating views like creating tables is done with the SQL create command.
sqlite> create view testview AS select * from test;
Indexes designed to speed up queries under certain conditions. If our test table had 100,000 entries and we wanted to find a small group of entries the database would have to scan all 100,000 entries looking for our small group. If we make an index of all these entries then we will be able to scan all the entries much faster.
The bad thing is that indexes increase the size of the database because they keep a copy of all the columns they index. One other bad thing is that if you insert, update or delete a record the database has to modify the record and the index of that table. This means that indexes can slow down inserts, updates, deletes, etc. If used wisely indexes can give you a huge performance boost. You will just have to test them for yourself. Here is how to make an index for our table test on column value.
Creating indexes like creating tables and views is done with the SQL create command.
sqlite> create index testindex on test (value);
To see all the tables and views in the database we use the SQLite .tables command. You can also use the SQL like operator to find tables and views with a pattern. Using the % symbol in the example below we show all the tables and views beginning with the letter t.
sqlite> .tables test testview
sqlite> .tables t% test
To view any indexes a table has we can use the SQLite .indices command. To see them for our test table we would do the following.
sqlite> .indices test testindex
To see database schema information we use the SQLite .schema command. This give you the create command that was used to create the tables. If you created an index on any columns then this will also show up.
sqlite> .schema test CREATE TABLE test (ids integer primary key, value text); CREATE INDEX testindex on test (value);
Now let's put in some data in the db with some SQL insert statements.
sqlite> insert into test (value) values('value1');
sqlite> insert into test (value) values('value2');
sqlite> insert into test (value) values('value3');
sqlite> insert into test (value) values('value4');
Let's use the SQL select command to look at the data we inserted. Remember we turned on column mode (.mode col) and column headers (.headers on) at the beginning of this page so it's easier to read the output.
sqlite> select * from test; ids value ---------- ---------- 1 value1 2 value2 3 value3 4 value4
To see the databases that are currently open use the SQLite .databases command. It will show the main and temp databases and where they are on the system.
sqlite> .databases seq name file --- --------------- ------------------------------------ 0 main /home/user/test.db 1 temp /var/tmp/etilqs_oCUXLLbZO4TjUNI
You can export all of your database objects to a file for backup using the SQLite .output and .dump commands. These commands will output all of the data from the database. The first .output line directs any output from now on to the file /tmp/test.sql. The second line .dump will export all of the data in the objects in the database. If the file is already there it will overwrite that file. The last command will set the output back to the screen like we had it.
sqlite> .output /tmp/test.sql sqlite> .dump sqlite> .output stdout
To look at what the file looks like open a new shell and cat that file.
user@host:~$ cat /tmp/test.sql BEGIN TRANSACTION; CREATE TABLE test (ids integer primary key, value text); INSERT INTO "test" VALUES(1,'value1'); INSERT INTO "test" VALUES(2,'value2'); INSERT INTO "test" VALUES(3,'value3'); INSERT INTO "test" VALUES(4,'value4'); CREATE VIEW testview AS select * from test; CREATE INDEX testindex on test (value); COMMIT;
The file contains all the information you need to re-create your database. From creating the tables to inserting all the data. This is used mostly to backup databases or to export the data from one database type to another. For backing up purposes it is usually easier to just copy the SQLite3 db file to another location.
If you would like to import the data you exported using the SQLite .dump command it's as easy as using the SQLite .read command. In the example below the first command makes a new database from the system command line (shell). The second command imports the data we exported in the previous example. The last command shows all the data in the new database.
user@host:~$ sqlite3 testrecover.db sqlite> .read /tmp/test.sql sqlite> select * from test; ids value ---------- ---------- 1 value1 2 value2 3 value3 4 value4
If you want to import data that is in csv (comma separated value) format into a table you can do it using the SQLite .import command. The csv file has to use separators that SQLite understands. To see the current separators you can use the .show command. SQLite uses the pipe | as the default separator.
sqlite> .show
echo: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:
To change the separator type use the .separator command. This will change it for output mode and for .import.
sqlite> .separator ,
sqlite> .show
echo: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: ","
width:
Now that the separator is changed to a comma we can import data from an external csv file. We will import more data into our current table (test) with the .import command. Make sure your csv file sections match up with your tables and that you have the same number of columns. Also, note that when you export a file now it will be exported with a comma as the separator.
Here is what the test.csv file looks like.
5,value5 6,value6 7,value7
Here is the import from the csv file and what the test table looks like after import.
sqlite> .import /tmp/test.csv test sqlite> select * from test; ids value ---------- ---------- 1 value1 2 value2 3 value3 4 value4 5 value5 6 value6 7 value7
To remove any index, table, trigger, or view you have made use the SQL drop command. The syntax is the word drop then what you want to drop and then the name of the thing you want to drop. Below is and example of dropping one of each kind.
drop index indexname drop table tablename drop trigger triggername drop view viewname
To exit out of the SQLite prompt use the SQLite .exit or .quit command. Both work the same way.
sqlite> .exit user@host:~$
The following commands will be using the statically linked SQLite command-line program. This is what was installed in the install section above. You can completely control an SQLite database with this program right from the Linux shell. No need to invoke the SQLite shell we introduced above. Being able to access SQLite databases from a shell lets you incorporate SQLite into your shell scripts without the need for external libraries.
We will use the same test.db database that we made in the examples above for this demo.
The SQLite binary is for version 3 is called SQLite3. You can use this program from the command line just like any other UNIX program. If you want to look at the options the binary has just issue the --help option.
user@host:~$ sqlite3 --help Usage: SQLite3 [OPTIONS] FILENAME [SQL] FILENAME is the name of an SQLite database. A new database is created if the file does not previously exist. OPTIONS include: -init filename read/process named file -echo print commands before execution -[no]header turn headers on or off -bail stop after hitting an error -interactive force interactive I/O -batch force batch I/O -column set output mode to 'column' -csv set output mode to 'csv' -html set output mode to HTML -line set output mode to 'line' -list set output mode to 'list' -separator 'x' set output field separator (|) -nullvalue 'text' set text string for NULL values -version show SQLite version
You can see syntax you need to use and the different options available.
NOTE THE ABOVE: If the database does not exist it will be created on any command given. Even on commands that fail.
You can execute SQL statements right from the shell. Let's look at the data in our test database test.db. We are going to turn on headers and also turn on column mode so the ouput looks nice.
user@host:~$ sqlite3 -header -column test.db 'select * from test' ids value ---------- ---------- 1 value1 2 value2 3 value3 4 value4 5 value5 6 value6 7 value7
You can execute any SQL statement against a database like this.
You can execute SQLite commands the same way you execute SQL commands. The example below uses the .schema SQLite command on the test.db file.
user@host:~$ sqlite3 -header -column test.db '.schema' CREATE TABLE test (ids integer primary key, value text); CREATE VIEW testview AS select * from test; CREATE INDEX testindex on test (value);
You can export (backup) your database using the command-line program and .dump. It works the same way it does from the SQLite shell. But we are going to want the output to go to a file and not the screen so we will redirect it to a file called dbbackup.
user@host:~$ sqlite3 test.db '.dump' > dbbackup
You could also compress the output before writing it disk by running .dump's ouput through gzip, bzip2, or whatever. The following is an example using bzip. It is highly recommended that you compress your dumps.
user@host:~$ sqlite3 test.db '.dump' | gzip -c > dbbackup.gz
If you have used the SQLite .dump command we mentioned earlier to create a file with SQL statements you can input those statements into your database from the command line. This can be done with any SQL statements not just the ones from the .dump command. The following example will use a file called statements.sql and import them into our test.db file.
user@host:~$ sqlite3 test.db < statements.sql
To import that gzip compressed dump (backup) of SQL statements from above.
user@host:~$ gunzip -c dbbackup.gz | SQLite3 test.db
As a speed note. If your importing a lot of insert statements wrap those in a transaction to get a major speedup.
Here are some great links from the SQLite website to help you further.
SQLite understands most of the standard SQL language. But there is some syntax differences sometimes. There are also some SQL commands that SQLite has added that are unique. These are commands like VACUUM or PRAGMA. Make sure your look at the PRAGMA page as it is used to tweak settings in your SQLite database.
http://www.SQLite.org/lang.html
Core Functions, Aggregate functions, and Date time functions are all functions you can use with SQLite. You can also have an application define additional functions that are written in C and have them added to the database engine using the SQLite3_create_function() API.
http://www.sqlite.org/lang_corefunc.html
http://www.sqlite.org/lang_aggfunc.html
http://www.sqlite.org/lang_datefunc.html
Del.icio.us! | Digg Me! | Reddit!
