Tech Blog

For a recent project we wanted to use Railo encrypted DSN's passwords in the railo-server.xml.

The only issue was that the encryption of the passwords was not documented as to how it works. A bit of digging around in the source code showed me how it was done and I've now made a simple page that will generate these encrypted passwords.

Benefits: - Can use encrypted passwords - Don't need to be able to see the production DB when creating the config file

So I can now generate encrypted passwords for railo without hooking my laptop railo instance up to the production db's. Nice.

Getting Setup

Here is some documentation for reference and to help others:

1. Get the SDK from Amazon

• I got version 1.2.4 from here: http://aws.amazon.com/sdkforjava/
• Extract the lib/aws-java-sdk-1.2.4jar file and put it in your tomcat/lib folder

2. Get the HTTPComponents Client

• You will also need get the HTTPComponents Client from apache as it depends on this. http://hc.apache.org/downloads.cgi I downloaded the 4.1.1 release and this worked a treat
• Extract the 6 jars and drop them in your tomcat/lib folder.

3. Restart Tomcat and write some code

Comparison of techniques

I played with a few different techniques of using the AWS with tomcat and found the following:

1. CFHTTP direct API calls

2. Java AWS SDK from Railo

This worked well, but each request took on average of 1500ms from AU to US East AWS. This was disappointing, so I tried caching in the the application scope as below.

3. Java AWS SDK cached in App Scope

The first request took ~ 1500ms but then subsequent requests were about 300ms, which was 1/3 of the time of the raw CF code. With the added benefit of having full access to all the API's, this is definitely the way we'll be using it going forward.

Cheers, Mark

References

• AWS Java SDK reference
• List of endpoints for each Amazon service

• Makes it practical for students to practice Oral and Aural skills
• Is efficient and effective for teachers, as they can listen to each student individually at a time to suit them
• Can be used for homework assignments or “High Stakes Assessments”

Learnosity are creating the next generation of language and assessment technology for use in schools and education worldwide. The current product portfolio includes:

• High availability web based systems
• Cross platform software (Windows, Mac and Linux)
• iPhone/Android native applications
• Telephony/VOIP and SMS applications

We need someone who can:

• Use Javascript or Actionscript to create great user interfaces
• Develop highly scalable web applications using ColdFusion, PHP or similar
• Cut code with the best in the world

You will also need to be:

• keen to continue learning new technologies
• able to have a conversation with non technical people
• enthusiastic and ready to push the boundaries

You'll need:

• 1-3 years of programming experience
• Experience in at least one Client side language (Actionscript or Javascript/jQuery)
• Experience in at least one Server side language (eg PHP, Java, ColdFusion, etc)
• Understanding of Object Oriented design
• Understanding of XHTML and CSS

It would be good if you have:

• A degree in Computer Science, Engineering or similar.
• been working with open source tools
• Experience with Adobe AIR or Flex
• been playing around with iPhone/Android applications
• experience with some of Linux/VOIP/SIP/Asterisk/Jabber/XMPP

This is a full time role and you will be working in a casual workplace with flexible hours in the Sydney CBD. Salary commensurate with experience.

If this sounds like the job for you, email a covering letter explaining why you'll be great and your resume to mark@learnosity.com - no agencies please.

These are:

• Logging to DB instead of log files
• Runtime configuration of logging.

Logging to DB instead of log files

Coming from a web development background rather than a sysadmin background I'm far more comfortable manipulating and analysing data using SQL. So to be able to log all the queries or just the slow queries for an application to the db during application development or load testing is a huge benefit.

To enable logging to DB you can add the following to your my.cnf

The logs will be written to the 'slow_log' and 'general_log' tables in the mysql database.

Note - logging to tables has more overhead than logging to file, so would suggest using it primarily for development purposes.

Full details of the options are on the mysql manual on log tables

Runtime configuration of logging.

This allows you to turn on and off logging without restarting MySQL - which just saves a little bit of time and makes it much nicer for debugging problems.

To turn on the logging of all queries run:

And to turn them both off use:

If you also want to see queries not using indexes in the slow query log you can set the following variable:

Hope it helps, Mark

I found it in this excellent article on optimising MSSQL

The times that are returned from the queries are in micro seconds as documented on msdn.

Cheers, Mark

You can use it to clear the query cache - to ensure you are starting from a level playing field. Or if you are tuning queries in a application and want to ensure they are not cached run it before each run:

Cheers, Mark

However, you need to do a tiny bit of setup work before you can use the functions. You need to load the timezone info from your system into the mysql db.

Thankfully, the MySQL Developers have included a very handy script that will do just that from a zoneinfo files which are used on Linux and on Mac's.

Obviously change the mysql params or add a -p if you have a password or are connecting to a remote server.

Check out the MySQL developer site for full details on MySQL timezone support

Cheers, Mark

I needed to use a case function on a subquery value to return a tri-state value. I.e. yes, no or partial.

The cut down query below demonstrates how it works.

• First we drop the temporary table in case one has been left lying around.
• Then we select a query into the temporary table which include some complex subqueries.
• We can then query the temporary table to do further processing on the result, which in this case depending on the values of the class_count and class_count_assigned allows us to set a field in three states.
  • 'yes' if all users for a class are selected
  • 'partial' if some users are selected
  • 'no' if none are selected.

Mostly posted for my future reference but hope it helps out.

Cheers, Mark

I was returning a query with some bit fields into my class but the it was not returning true or false when I switched the data.

A bit of hunting and it turned out the that it was being returned as a binary type and after a few failed attempts to convert it to an integer or boolean on the PHP side I found this bug report on it.

So the moral of the story is if you are selecting BIT types from a mysql DB in PHP don't do this:

Instead cast the bit to an integer in MySQL like so:

Voila, it works like expected.

Cheers, Mark Lynch

We weren't intentionally doing these queries but they were coming from somewhere. A bit of detective work via google found a very enlightening article about this problem.

A quick read of this explained:

It turns out ColdFusion was asking Connector/J for the metadata on every field, which in turn triggered a SHOW FULL COLUMNS query for every varchar and text column returned.

It also went on to say that is had been fixed as of Connector/J 5.07.

On reading the release notes it mentions that the

Driver detects when it is running in a ColdFusion MX server (tested with version 7), and uses the configuration bundle coldFusion, which sets useDynamicCharsetInfo to false (see previous entry), and sets useLocalSessionState and autoReconnect to true.

However, from my testing it wasn't doing it as we were still seeing the problem. However, we are running the MultiServer version of ColdFusion which sits atop Jrun4.

So I added the parameter directly to the querystring:

This has stopped the problem, and given a significant performance gain. On one of the sites I was load testing it gave a 12% throughput increase and on another one which had less queries it gave a 4% increase.

Hope it helps.

Cheers, Mark