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.

Functional Programming in Haskell

Wow, I have submitted my assignment for the Functional Programming in Haskell course.

I am really tired.

The period allotted to the assignment is six weeks. I have put in over 60 hours and taken two days leave. Yeah, I know, they said about 25 hours should do it, but it takes as long as it takes.

I have known that Functional Programming was the missing element of my experience for a long while. I asked for and was given this course by work - yay work! It is an expensive course and I could not have afforded the 7 days holiday it would have taken without their support.

These seven weeks started much earlier: This year I have written some R code, which is a sorta functional language. I tried to do some prestudy: I bought the course book and read chapter 1 twice (once on holiday). I read through the prestudy material. Very little of this cohered, but it was just enough to enable me to keep my balance during the course.

The course was great! The lecturer served on the Haskell committee. The SoftEng department at Oxford is smart in every sense. The lunches at Kellogg College were great. The other students were really bright. I just about kept up, contributing more than most and not being wrong as often as some. On Thursday I caught myself gurning during the lecture: it was all so elegant!

Immediately the course ended, I had hopes of Friday afternoon but it was not until the evening, I started organising myself. Reading the questions repeatedly, I tried chipping away at them.

Things soon went pretty horribly wrong: I could not be sure I understood the meaning of the questions, what level of completeness was expected or what shape the answers were meant to have. The assignment centred upon producers, which we had hardly touched upon and which did not feature in the course documents, consumers being the main focus. So I continued to erect a structure of yak hair: we would need a test harness, Latex processing, bibliography.

I did manage to get an answer, thought it was right, was elated. Read in Bird that my answer was wrong (though, infuriatingly the answer was 'left as an exercise'), was pretty disconsolate. Researched further and discovered that the answer was that the number of trees that can be generated from n leaves is the Catalan number for n-1.

Managed to write the generator and tests. Yay!

In between the ups and downs I had managed to pick off list, length etc.

Managed to do some pretty solid work around higher order functions, though was running out of time. I managed to finish Part I, but now only had a week for Part II and Part III.

Part II did fall out pretty easily, but it was still probably 12 hours. The tests were in place when I started to embellish with nice-to-haves, which helped.

Part II

If all tests pass then the page is written to the file system.

output :: IO ()
output = do
writeFile "fpr.xhtml" (charSequToString (showXHTMLPage testPage))
The resulting xhtml  file can be seen at [output] and is shown valid at [W3C Validator].

Part III (the essay - mine ended up as something like Discuss the Influence of FP on Java) got significantly squeezed, as I kept returning to polish Part II.

At 11.30pm I spotted a mistake in Part I - in the length function and its test.

Submitted the thing at 12.40am. Have printed it out but resisted the temptation to read it. Am feeling like a zombie.

After the deadline had passed I added a missing line break to the output, just to show that this is for me, not just for the marker.

Specify InnoDb as default table type in JDBC

One of the recurrent gotchas with MySQL is that the default table type is MyISAM, whereas you almost definitely need InnoDB, if you want transactions, indexing or anything else that would make MySQL a useful database.

Set default table type for all databases

In /etc/mysql/my.cnf add: [mysqld]
default-storage-engine=InnoDB But this requires that you have root access to the server and that you want all tables to use InnoDB.

Specify default on JDBC url

database.url=jdbc\:mysql\://localhost\:3306/chassis_studies?autoReconnect=true&sessionVariables=storage_engine=InnoDB You could of course use a real database!

cvs to github

Following How to export revision history from mercurial or git to cvs?

On hanuman

I created an id file git_authors mapping cvs ids to github name, email format for all contributors:

timp=Tim Pizey<timp@paneris.org>

then create a repository on github (melati in this example, I already have uploaded my ssh public key for this machine)

git cvsimport -d /usr/cvsroot -C melati -r cvs -k -A ../../git_authors melati
cd melati
echo A jdbc to java object relational mapping system. 1999-2011 > README.txt
git add README.txt
git commit -m "Initial" README.txt
git remote add origin git@github.com:timp21337/melati.git
git push -u origin master

See https://github.com/timp21337/melati.