Idle in transaction – DELETE waiting

We have a Linux server (Redhat Enterprise 6) running a multi-site installation of Mediawiki. In total, we have today 120 unique wiki’s, each having its one Postgresql 9.1 database.

Suddenly the server stopped responding. In Apache webserver, the total number concurrent apache processes went up to Apache’s max-client setting in httpd.conf, and people couldn’t log in or see any wiki-pages.

In the apache log, we did not see anything special. But, when we started to check which processes were running during a full stop on the server with the Unix command ps, we could see entries like:

A search on Internet found that also others experienced this, and the solution to our problem we found here:

* http://www.mail-archive.com/dspace-tech@lists.sourceforge.net/msg00109.html

* https://wiki.duraspace.org/display/DSPACE/Idle+In+Transaction+Problem#IdleInTransactionProblem-Workaround:Killing

We implemented a fix to our problem by adding a bash script to /etc/crontab, running each fifth minute.

The script will first check if there are more then three (3) processes with the description “idle in transaction”.

If so, then the pkill command will stop (“kill”) the oldest one.

fix-transactions.sh:

The script runs every fifth minute. Here is our entry in the /etc/crontab file.

*/5 * * * * root /usr/share/scripts/fix-transactions/fix-transactions.sh

 

Authenticate Postgresql client agains backend ldap

We have a Postgresql server open to many different people at our organization. In order to add a ldap authentication you can add these 3 lines to your current pg_hba.conf file:

where the first one will allow users that access the server from localhost (either from a shell or 127.0.0.1 connections). The second line is allowing a computer from the following IP adresse: 129.177.XXX.YYY.

Finally the last line will use ldap backend for local connections. That means a Linux user that logged in with SSH, will still be validated agains the ldap server, regardless of local password.

Remember this is only authentication, so you have to add the username to Postgresql database anyway (hint: use createuser command).

If you want to allow a user to connect remotely over web, remember to use SSL in the webserver, HTTPS, so that the password is not transmittet un-encrypted.

Note also:  This is also only LDAP, not LDAPS connections. So data going from the Postgresql server to the ldap is not encrypted. In short: Keep the distance between the postgresql server and the LDAP server short, or try to implement LDAPS, which I did, where I failed, so far…

Open Journal System missing email logs

I upgraded our Open Journal Systems from 2.2.2 to 2.4.2 and afterwards we saw the email logs were gone. This was related to:

http://pkp.sfu.ca/support/forum/viewtopic.php?f=8&t=9140

My solution was this:
Established a new Postgresql server in a virtual enviroment (vmware, test-machine), and import the old and the new (upgraded) database.

ojs_old: was the original 2.2.2 database
and
ojs_new: was the upgraded 2.4.2 one

In order to fix the assoc_id numbers, I used this bash script:

myscript.sh:

The output from this script is sent to STDOUT. I redirected it to another script, and ran the script on our Production server.

Upgrade Moodle 2.2.1 to 2.6.2+

I tried to upgrade Moodle 2.2.1 to 2.6.2+ and got this error message:

The solution was to run these sql’s at the Mysql database:

This issue seems to be related to: