Wrapper on check_nt!UPTIME

#!/bin/bash

HOSTADDRESS=$1
MINWARN=$2 # in hours
MINCRIT=$3 # in hours

SECONDS=`/usr/local/nagios/libexec/check_nt -H $HOSTADDRESS -p 12489 -s $ECRET$ -v COUNTER -l "\\System\\System Up Time"`
#### IS:           6817
#### SHOULD BE:    WARNING: uptime: 1:53 < warning|'uptime'=6817000;172800000;3600000;

HOURS=$(( $SECONDS / 60 / 60 ))
SECONDSINHOURS=$(( $HOURS * 60 * 60 ))
REMAININGSECONDS=$(( $SECONDS - $SECONDSINHOURS ))
MINUTES=$(( $REMAININGSECONDS / 60 ))
FORMEDUPTIME="${HOURS}:${MINUTES}"

if [[ $HOURS -lt $MINCRIT ]]; then
    echo "CRITICAL: System started ${FORMEDUPTIME}h ago."
    exit 2
fi

if [[ $HOURS -lt $MINWARN ]]; then
    echo "WARNING: System started ${FORMEDUPTIME}h ago."
    exit 1
fi

echo "OK. Uptime $FORMEDUPTIME.|'uptime'=${SECONDS}000;7200000;3600000;"

Multisite Drupal

postgres=# CREATE DATABASE example_eu OWNER drupal6 ENCODING 'UTF8';
cp ./drupal/sites/default ./drupal/sites/www.example.eu
vim ./drupal/sites/www.example.eu/dbconfig.php
vim /etc/apache2/sites-available/example.eu
a2ensite example.eu
firefox https://www.example.eu/

Cutting mailq

Removing selected mail messages from postfix's mail queue based on sender on recipients:

mailq | tail +2 | grep -v ’^ *(’ | awk  ´BEGIN { RS = "" }
  # $7=sender, $8=recipient1, $9=recipient2
  { if ($8 == "user@example.com" && $9 == "")
    print $1 }
´ | tr -d ’*!’ | postsuper -d -

Print folded book in Evice

To print folded "book" in Evince its necessary to define what pages and in what order to print on both sides.

If the number of pages is not divided by 4, increase it (e.g. 62 increase to 64).

Then generate two series of page numbers:

#!/usr/bin/perl -w

use strict;

my $pages=64;
my $modifier=0;

for (my $i=1; $i<=$pages/4; $i++) {
  my $left_page  = $pages-$modifier;
  my $right_page = $modifier + 1;
  print "$left_page,";
  print "$right_page,";
  $modifier+=2;
}

print "\n";

$modifier=1;

for (my $i=1; $i<=$pages/4; $i++) {
  my $right_page = $modifier + 1;
  my $left_page  = $pages-$modifier;
  print "$right_page,";
  print "$left_page,";
  $modifier+=2;
}

print "\n";
exit 0;

Paste it in the print form (without the last comma).

List of MySQL grants

The command line shows list of all grants on all databases, including root@locahosts. It also shows encrypted passwords as they exists in mysql.user table.

mysql -Bse "SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user" | mysql -Bs | sed 's/$/;/g'

Data transfer from PostgreSQL to MySQL

Based on a tip from codesmell.org blog I eventually transferred data from PostgreSQL to MySQL. My client - what is unusual - decided to stay at MySQL as homogeneous environment and drop PostgreSQL.

The main problem is to

• preserve encoding
• keep all quoted characters
• change boolean from T/F to 1/0, and
• auto_increment what was serialized.

Transfering schema is planned for future post. The main guidelines are:

• change type inet to text
• remove serial and nextval
• remove evrything after "::" in field description using this command:

echo "default 'USD'::bpchar" | awk '{ i=index($0, ":"); print i; print substr($0, 0, i); }'

Exporting data

The first step is to compose string of the definition of every table for export to CSV:

table_name(first_column,second_column...)

Script parses all tables definitions and composes the desired string:
users(id,created,login,name,email,description)
ads(id,user_id,subject,text)

PostgreSQL versions

To parse PostgreSQL data I needed psql client in the same version as server. After download proper version I compiled and installed only client and necessary libraries. Create short script in source directory to automate it:

#!/bin/bashmake -C src/bin installmake -C src/bin/psql installmake -C src/include installmake -C src/interfaces installmake -C doc install

Then run ./configure --prefix=$HOME/local

Client in ~/local/bin/psql is ready to use. Execute it using full path or supplement $PATH:
export PATH=$HOME/local/bin:$PATH

Actual function:

function definitions {
while read table; do
if [[ $table != '' ]]; then
# weird empty lines
definition="$table("        # start the string with table name
while read column_def; do
column=`echo "$column_def" | awk '{ print $1; }'`
definition="${definition}${column}" # add column name
definition="$definition,"  # add comma
done < <($PGSQLCONN -t -c "\d $table" | awk '{ print $1; }')
echo "$definition)" | sed 's/,,)/)/' # add closing bracket and remove double commas
fi
done < <($PGSQLCONN -t -c "\dt" | grep -v "pg_" | awk '{ print $3; }') > $TABLE_DEFS
# for the while loop use all tables list from command psql> \dt

Some tables depend on data from other. To put FOREIGN KEY with reference to user's ids, I needed to put users table first. Identify all such tables and move them on top of imported tables.

tmpf="/tmp/tmp_table_definitions.txt"
grep -E "^user" $TABLE_DEFINITIONS > $PRIORITY_DEFINITIONS
grep -E "^ad" $TABLE_DEFINITIONS >> $PRIORITY_DEFINITIONS
grep -E -v "(^user|^ad)" $TABLE_DEFINITIONS > $tmpf
mv $PRIORITY_DEFINITIONS $TABLE_DEFINITIONScat $tmpf >> $TABLE_DEFINITIONS

Generation of a list table from table definition.

function tables {
DIR="$PROJHOME/csv"rm -rf $DIRmkdir $DIRtmpf="/tmp/tmp.csv"
# Ensure there's enough disk space on /tmp
rm $LISTA_TABEL 2>/dev/null
for definition in `cat $TABLE_DEFINITIONS`; do
echo "$definition" | \
awk 'BEGIN { FS = "(" } ; { print $1; }' >> $TABLE_LIST
done}

Dump of the data from PostgreSQL:

function dump {
### Data dump from PostgreSQL to CSV
for definition in `cat $TABLE_DEFINITIONS`; do
tabela=`echo $definition | \
awk 'BEGIN { FS = "(" } ; { print $1; }'`
file="${DIR}/${tabela}.csv"
nice ~/local/bin/psql --host=ip_address \
--user=account_name \
-c "COPY $definition TO STDOUT" db_name > $file
rows=`wc -l $file`
echo ">>File of the table $table has $rows rows"
done
}

Statements of table creation:

function create {
$MYSQLCONN < $RECREATE_TABLE
}

RECREATE_FILE contains complete database definition. Its based on PostgreSQL dump of schema with modifications to meet MySQL requirements.

Pouring the data into MySQL:

function tip {
tmpf="/tmp/tmp.csv"
for table in `cat $TABLE_LIST`; do
csv_file="$PROJHOME/csv/${table}.csv"
nice cat "$csv_file" | nice sed 's/\tt\t/\t1\t/g' | \
nice sed 's/\tf\t/\t0\t/g' > "$tmpf"
mv "$tmpf" "$csv_file"
# echo "TRUNCATE TABLE $tabela;" | $MYSQLCONN
echo "LOAD DATA LOCAL INFILE '$csv_file' INTO TABLE $table;" | \
$MYSQLCONN
done
}

Adding auto_increment behaviour to id columns:

function auto_increment {
$MYSQLCONN < $AUTO_INCREMENT

Where AUTO_INCREMENT file looks like:

ALTER TABLE user_activity MODIFY COLUMN `id` INTEGER NOT NULL AUTO_INCREMENT;
ALTER TABLE add_statistics MODIFY COLUMN `id` INTEGER NOT NULL AUTO_INCREMENT;
ALTER TABLE search_history MODIFY COLUMN `id` INTEGER NOT NULL AUTO_INCREMENT;

I added the above statements for all tables with id.

basename ... is bad if I can use something simpler

From graybot on irc.freenode.net#bash:

for f in *.wav; do lame "$f" "${f%.wav}.mp3"; done