Networking, and hence sshd, does not start until after login on Ubuntu

I updated my Ubuntu system and restarted it, as prompted, and left the office.

When I got home I could not ssh into my work machine.

This was because I had not logged into the machine and so networking and hence sshd had not started.

To get networking started before login add the following to /etc/network/interfaces

auto eth0
iface eth0 inet dhcp

Setup Postfix to use Gmail as a smarthost

smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd
smtp_sasl_security_options = noanonymous
smtp_sasl_tls_security_options = noanonymous
html_directory = /usr/share/doc/postfix/html

 emacs /etc/postfix/sasl_passwd # create password file

smtp.gmail.com GMAIL_USER_NAME@gmail.com:GMAIL_USER_PASSWORD

 postmap hash:/etc/postfix/sasl_passwd
 /etc/init.d/postfix reload
 sudo apt-get install mailutils 

Send me the DHCP allocated IP address on boot

Occasionally I reboot, occasionally I get allocated a new IP address.

Create /etc/init.d/sendIpToTimP :

#!/bin/sh                                                                                                                                      
ifconfig eth0 \ 
 |grep "inet addr" \
 |cut -f2 -d":" \
 |cut -f1 -d" " \ 
 | mail timp@paneris.org -s "Work IP address" 

 update-rc.d sendIpToTimP defaults

Configuring MySQL

The defaults that MySQL comes with are probably not what you want.

We got caught by this at work and followed the instructions given in How to calculate a good InnoDB log file size

Edit /etc/mysql/my.cnf to reflect:

query_cache_limit = 4M
query_cache_size  = 32M
table_cache       = 768

innodb_open_files = 512

innodb_buffer_pool_size = 512M

innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 8
innodb_concurrency_tickets = 500
innodb_lock_wait_timeout=200
innodb_autoinc_lock_mode= 2
innodb_commit_concurrency=4
innodb_flush_log_at_trx_commit=2
innodb_support_xa=false
innodb_checksums=0
innodb_doublewrite=0
innodb_max_dirty_pages_pct=15

Discover the location of datadir typically datadir = /var/lib/mysql

service mysql stop
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0_old
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1_old
service mysql start

Jenkins, Github and Blogger make sharing workable

I was refactoring a project, which would entail deleting a little R script which I could see myself wanting to use again.

Step one: create a new repository on GitHub

Step two: Checkout the repository locally, add the code, test, commit.

Step three: Create a new project in your Jenkins CI installation (if you do not have Jenkins, go sign up for a free account at CloudBees)

Write up at Blogger - here we are!

MySQL VIEW error "Table doesn't exist"

mysql> select table_name
    -> from information_schema.tables
    -> where table_type='VIEW';
+---------------+
| table_name    |
+---------------+
| ClinicalDrugs |
| Studies       |
| StudySites    |
| pkAnalytes    |
| pkSamples     |
| pkdetails     |
+---------------+
6 rows in set (0.00 sec)
mysql> select * from chassisDb.pkDetails;
ERROR 1146 (42S02): Table 'chassisDb.pkDetails' doesn't exist

The listing gives the named view but we are told it does not exist. I was also able to provoke

mysql> select * from viewname;
ERROR 1356 (HY000): View 'dbname.viewname' references invalid table(s) or 
column(s) or function(s) or definer/invoker of view lack rights to use them

If you try to access the view from JDBC then you will see

java.sql.SQLException: View 'chassisDb.Studies' references invalid table(s) or 
column(s) or function(s) or definer/invoker of view lack rights to use them
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)

This is caused by MySQL's weird, newish, security defaults for the CREATE VIEW command. 

create view Studies as 
select * from Entry, Study where Study.EntryId = Entry.Id;

is translated, after defaults are applied, to

CREATE VIEW SQL SECURITY DEFINER Studies AS 
SELECT  Entry.Id, Study.Id FROM Entry, Study WHERE Study.EntryId = Entry.Id;

If you now try to use this view as any user other than the definer of the view you will get the error above. What you wanted, and have to write explicitly to avoid the default, is

CREATE VIEW SQL SECURITY INVOKER Studies AS 
SELECT  Entry.Id, Study.Id FROM Entry, Study WHERE Study.EntryId = Entry.Id;

This page in the hope that it helps!

Simple MySQL reporting database

We have created an XML based repository of clinical trials study data.

To report upon this we create an isomorphic MySQL database using HyperJAXB. Our main configuration of hyperjaxb is to use an information preserving naming convention plugin.

The builds are under Continuous Integration. The database is created during the course of the Maven build, after this views are created and the database dumped.

mysqldump chassisDb  --complete-insert --skip-opt --add-drop-table  > $file

The dump file is then copied to an Amazon Web Services T1 instance, which only has MySQL installed. Two users exist: reader and uploader.

These two users have usernames and passwords configured in .my.cnf in their home directories containing

[client]
user=user
password=password

uploader has a cron job configured to update the database from any uploaded files:

mysql chassisDb < chassisDb_20120119.sql 

The database can now be accessed using the Pentaho reporting tool.