Mixing techniques causes a memory leak

A co-worker solved an interesting problem this week.

A number of developers have been contributing to a Groovy script that exports a large amount of data from one of our databases. Although the conversion rules for the data can be complex, at its core, the script is pretty simple. Using groovy.sql.Sql, the script does a SELECT to get a number of insurance policies. Then it iterates through the result set and does a second SELECT to get some additional information about each policy. Here’s the pseudocode:

sql = Sql.newInstance(connectionProperties)
sql.eachRow(SELECT_POLICIES) { polRow ->
sql.eachRow(SELECT_DETAILS) { detailRow ->

Until the latest sprint, the script had been working fine. However, it recently began to cause OutOfMemory errors after writing about 60,000 rows, and none of the developers could figure out why. The main difference that we saw was that a new developer had modified one of the SQL statements, and instead of using string substitution to modify the inner query, he had used parameter markers — which is a technique that, really, we all should be adopting for a number of good reasons.

sql = Sql.newInstance(connectionProperties)
sql.eachRow(SELECT_POLICIES) { polRow ->
sql.eachRow(SELECT_DETAILS, [polRow.some_prop]) { detailRow ->

My co-worker solved the puzzle for us. With the addition of the parameter in the statement, the Groovy Sql class started compiling the inner query into a prepared statement. Prepared statements are generally faster because they are compiled and then cached and re-used, so you don’t incur the overhead of having to recompile on each use. However, with the substitution of a different policy number in the string on each iteration of the loop, making each string unique, on each iteration of the loop the Sql processor was preparing and caching a new statement. When the cache filled with about 60,000 instances, we ran out of heap memory.

The solution, of course, was to get rid of all the string substitution in our SQL statements and use parameters instead. The benefit of forcing all the queries to use prepared statements was that the script now runs about 30% faster. For a process that used to take 4.5 hours, that’s a significant improvement.

Lesson #1: Use prepared statements. Not only are they more efficient, but if you’re working with a web application, they are resistant to SQL injection.
Lesson #2: Don’t mix techniques. Practice code review and pair programming to encourage all of your team to develop shared best practices.

#groovy, #groovy-sql, #memory-leak

Catching a dynamic typing failure in Groovy

Earlier today, a colleague asked me to help troubleshoot a very weird error in a Grails page he was working on.

On this page, he has a select element where the user is asked to choose a US state. The states list comes from a domain class that is mapped to a database table that holds state names and their ISO-8601 codes:

2 AK Alaska
9 DE Delaware
10 CA California
16 ID Idaho
50 TN Tennessee

<g:select name="state" from="${State.list()}" optionKey="codeValue" optionValue="codeName" value="${params.state}"/>

In the controller action for this page, he posts [params: params] back on the request in the return statement. So, after hitting submit on the page, he would expect to see that the select element retains the same state that was chosen by the user. (value=”${params.state}”)

The weird behavior he described was that the page seemed to work as designed for most values that he selected from the drop-down. However, for the first states in the list (AK through DE), upon submitting the form, the selected state would change. For example, if he selected “AL Alabama” (with a codeValue == 2), after submit, the form would have “TN Tennessee” selected. (codeValue == 50).

After puzzling through this for a while, we finally came to understand what was happening.

Groovy is dynamically typed. If you don’t explicitly specify a data type, then Groovy treats all values as a “def” – an instance of java.lang.Object. When Groovy has to perform an operation on a “def”, it makes an educated guess about what type to cast the value to, and it is right so often that we tend to forget that typing is happening at all.

But consider: Values posted back to the server from a GSP form in an http request are all returned as text. Grails conveniently parses form values out of the request and injects them into the controller action as a List named “params”, but it doesn’t know how to type them if they’re not actually Strings, so if you don’t cast them yourself, you get whatever type Groovy thinks they should be. My colleague was taking the params.state value right off of the request and then rendering it unmodified back into the view.

Now, if params.state was being typed as an Integer, everything should work as expected. But what if Groovy was blindly typing the value as a String? On a hunch, I asked my colleague to take a look at the
Unicode Basic Latin character set.

Case 1: He selects “ID Idaho”. Idaho’s codeValue, “16”, is submitted back to the controller and then reloaded into the select upon rendering. This works as expected.

Case 2: He selects “AL Alabama”. Alabama’s codeValue, “2”, is submitted back to the controller and then reloaded into the select upon rendering. The select now has “TN Tennessee”, codeValue “50”, selected.

The decimal Unicode value for the character “2” is 50.

What’s happening here is that if a two-character String representation of a numeric value (e.g., “16”) is given to Groovy, Groovy is correctly deducing that it needs to be cast as Integer. If a one-character String representation of a numeric value (e.g., “2”) is given to Groovy with no other instructions, Groovy is making the guess that this is a byte, and it is operating on the decimal value of the byte.

Once we understood what was happening, the fix was simple.

<g:select name="state" from="${State.list()}" optionKey="codeValue" optionValue="codeName" value="${params.state as Integer}"/>

I think that the lesson to be learned here is that while dynamically typed languages are great, a smart developer should never rely on them to always do the right thing. You don’t have to go so far as to force static typing in Groovy, but do use typed values as a habit, whenever you have the choice.

#dynamically-typed, #grails, #groovy

Groovy Order of Operations Gotcha: Ternary Operator vs. Left Shift

The code below perplexed the heck out of me until a co-worker helped me understand the problem.

List items = []
items << row.policy_no
items << row.insured_name
items << row.effective_date ? row.effective_date.format('MM/dd/yyyy') : 'not available'
items.each { item ->
    println item

No matter what I did, I could not seem to get the value for effective_date to format.

What is happening here is that Groovy is evaluating the left shift operator (<<) before the ternary operator (?:).  I was able to get this code to do what I wanted simply by wrapping the expression in parentheses.

items << row.effective_date ? row.effective_date.format('MM/dd/yyyy') : 'not available' )

My first impression is that this would seem to be a bug, or at least undesirable behavior on Groovy’s part. But there is probably a good reason for it that I’m not seeing.

#groovy, #left-shift, #order-of-operations, #ternary-operator

DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501 and group permissions

Revised 13 Mar 2014

The Situation

A Groovy script that executes some SQL against a DB2 for LUW 9.7 database throws an exception:


IBM states that SQLCODE -551 means:

authorization-ID does not have the required authorization or privilege to perform operation operation on object object-name.

Initially, this seems to be a pretty straightforward problem. This is a database that was recently copied from another system. It is not unlikely that when it was restored, permissions were not granted to USERID to execute SCHEMA.GET_FACE_AMOUNT.

However, once the database administrator demonstrates that he can execute the GET_FACE_AMOUNT function while logged on as USERID, we’re left with a puzzle.

What We Learned

Our DB2 permissions were applied to user groups. The executing userid is a member of the ‘rw’ group, which should have read, write and execute permissions to the GET_FACE_AMOUNT function. As it turns out, however, DB2 does not apply group permissions to user defined functions.

It’s a much better practice to manage database permissions using roles. I’m not a DB2 DBA, so I can’t comment on exactly what the differences between groups and roles are. However, it seems like roles are a more robust way of defining permissions for a collection of users.

#db2, #db2-sql-error, #sql, #sql0551n, #sqlcode-551, #sqlstate-42501

Vagrant Up fails with “VBoxManage.exe: error: Code CO_E_SERVER_EXEC_FAILURE (0x80080005)”

I’m trying out Vagrant, and ran into a problem.  The vagrant up command failed: 

Bringing machine 'default' up with 'virtualbox' provider... 
[default] Setting the name of the VM... 
[default] Clearing any previously set forwarded ports... 
There was an error while executing `VBoxManage`, a CLI used by Vagrant for controlling VirtualBox. The command and stderr is shown below.

Command: ["list", "hostonlyifs"]

Stderr: VBoxManage.exe: error: Failed to create the VirtualBox object! 
VBoxManage.exe: error: Code CO_E_SERVER_EXEC_FAILURE (0x80080005) - Server execution failed (extended info not available) 
VBoxManage.exe: error: Most likely, the VirtualBox COM server is not running or failed to start.

A Google search revealed a lot of other people having the same problem, but not too many solutions, so I’ll document how I got past it.

This is using:

  • Vagrant 1.2.7
  • VirtualBox 4.2.16
  • Windows 7 Professional, 64-bit, SP 1

I could also reproduce the same error by running the vboxmanage list hostonlyifs command from C:\Program Files\Oracle\VirtualBox on a my command line — so this is a problem with VirtualBox and Windows, not with Vagrant itself.

Mitchell Hashimoto very helpfully pointed out that this might just be a permissions issue with Vagrant running under a different userid than VirtualBox, and that does seem to be the case.  I had started my command prompt using Run As Administrator.

Simply running from an un-elevated command line didn’t solve my problem, however. The fix was to grant some DCOM permissions on the VirtualBox Application component.

On Windows 7, that’s accomplished as follows:

  • Open Component Services from the Administrative Tools folder.
  • Drill down to Component Services > Computers > My Computer > DCOM Config.
  • Click through a couple of warnings about unregistered classes from other apps installed on my system.
  • Right click on VirtualBox Application.
  • Go to the Security tab.
  • Change Launch and Activation Permissions from Default to Customize

It’s hard to say whether any specific permission was needed or whether simply opening up the permissions for edit and then re-saving them did the trick, because once it started working, I returned my permissions back to the original defaults and it continued to work. Maybe something in my registry was corrupted and the DCOM Config editor fixed it.

#co_e_server_exec_failure, #dcom, #vagrant, #virtualbox, #windows

ACORD, enums and Groovy

ACORD publishes standards for information exchange by insurance and financial industries. As a part of their specifications, they provide lists (called lookups) of constant values (called type codes) for all kinds of items that insurance carriers, customers or users might need to communicate about.  For example, the lookup named OLI_LU_GENDER contains numeric values for genders. If two parties need to communicate about the rates for a policy to cover a male, age 35, non-smoker, then they can use an XML message that declares the applicant has:

<Gender tc="1"/>

where the tc=”1″ refers to the type code for ‘Male’.

As a developer writing web services that need to speak ACORD XML, it can be a tedious chore to have to keep referring to the ACORD manual to look up type codes. Fortunately, Java and Groovy make it easy to convert ACORD lookups into a library of enums.  With an IDE that does code completion, having the enums jar in your classpath is all you need to be able to work with ACORD lookups and type codes at a decent speed.  Simply start typing the name of a lookup (and most of the ones in the OLifE standard start with “OLI_LU_”) and the IDE prompts you with choices. Tab to select your lookup and hit “.” and then you’re prompted with a list of type codes. Easy!


    OLI_GENDER_MALE(1, 'Male'),
    OLI_GENDER_FEMALE(2, 'Female')

    private final Integer typeCode
    private final String text

    OLI_LU_GENDER(Integer typeCode, String text) {
        this.typeCode = typeCode
        this.text = text

    String getName() {
        return name()

    Integer getTypeCode() {
        return typeCode

    String getText() {
        return text

    static def findByName(String searchArg) {
        return this.find { it.name == searchArg }

    static def findByTypeCode(Integer searchArg) {
        return this.find { it.typeCode == searchArg }

    static def findByText(String searchArg) {
        return this.find { it.text == searchArg }

    static def findByTextIgnoreCase(String searchArg) {
        return this.find { it.text.equalsIgnoreCase(searchArg) }


I realize that the example above breaks Java and Groovy naming conventions for classes. However, for recognition and usability I think that it’s more important to stick to the ACORD naming conventions and use the original name of the lookup for the name of the enum.

Here are some example usages:

    String renderAcordXml() {
        def xml = new StreamingMarkupBuilder().bind { builder ->
            mkp.declareNamespace('xsd': 'http://www.w3.org/2001/XMLSchema',
                    '': 'http://ACORD.org/Standards/Life/2',
                    'xsi': 'http://www.w3.org/2001/XMLSchema-instance')
            'http://ACORD.org/Standards/Life/2 schema\\TXLife2.16.01.xsd'
            ) {
                TXLifeRequest() {
                    OLifE {
                        Party(id: INSURED_PARTY_ID) {
                            Person() {
                                Gender(tc: OLI_LU_GENDER.OLI_GENDER_MALE.typeCode, OLI_LU_GENDER.OLI_GENDER_MALE.text)
<g:select name="insuredGender"
             optionValue="text" />
InsuranceQuote quote = new InsuranceQuote()
quote.applicantGender = OLI_LU_GENDER.findByTypeCode(params.insuredGender)

I wish I could crowdsource the development of a complete enum jar for each ACORD standard, or at least share the one I’m creating, but the standards are not my intellectual property.  The best that I can do for you is to suggest that it should be possible, if you’re an ACORD member and have access to the standards documents, to write a script that can parse tabular lookup data out of the docs and write Groovy enum code from it.

#acord, #enums, #groovy

Take Charge of Windows Updates

It seems like every month that at least one of my laptop using co-workers gets blindsided by 15 – 30 minutes worth of pending updates when they need to shut down their laptop and leave in a hurry. Keeping your software up to date with the latest security fixes is important — read Krebs on Security or the F-Secure weblog if you think otherwise — but you don’t have to let Redmond’s Windows Update service bully you.

Black Tuesday: Expect It

Unless something critical comes up earlier, Microsoft releases updates for all of their products on the second Tuesday of every month. Typically, those updates will hit your computer on Wednesday or Thursday.  Plan for it.

If you’d like to be reminded of what’s coming, there are a number of news feeds or mailing lists that you can subscribe to that publish summaries of the expected updates.

Microsoft Security Bulletin Advance Notification: http://technet.microsoft.com/en-us/security/gg309152.aspx

The Shutdown Button

If your computer has updates waiting to be applied, then the default choice for the shutdown button becomes “Apply updates and shut down”, but you have other choices. If now is not a convenient time to apply updates:

  • On Windows 7 or Vista: Note that there’s a little arrow on the shut down button. If you click it, it will expand into a menu with other choices, including some that allow you to shut down without applying updates.

Windows 7 shutdown menu

  • If you’re one of those poor souls still stuck using Windows XP, the shut down prompt has a drop-down list which allows you to choose some other ways of shutting down without applying updates.

Windows XP Pro shut down

Pre-Emptive Updating

If you know updates are available, you don’t have to wait until Windows forces you to apply them. You can start the process yourself and let it run in the background while you continue working. Then you can reboot your computer at a more convenient time. (Do reboot it though, so that the updates can finish applying.)

To start applying updates:

  • On Windows 7, go to Start > Control Panel > System and Security > Windows Update to start applying updates.
  • On Windows XP, open Internet Explorer and go to http://www.update.microsoft.com/.

Now once the updating process finishes, Windows will start prompting you, with very annoying frequency, to restart your computer to complete the process. It may even try to force you to restart. No worries – you can silence that prompt by temporarily turning off the Windows service that manages the updates:

  • On Windows 7 or Vista, go to Start > Control Panel > System and Security > Administrative Tools. In the Administrative Tools, start the Services tool. Find the Windows Update service. Right click on it and select Stop.
  • On Windows XP, go to Start > Control Panel > Administrative Tools. In the Administrative Tools, start the Services tool. Find the Automatic Updates service. Right click on it and select Stop.

#black-tuesday, #windows-updates