Monitoring number of SQL queries in MySQL

I have created a new application in Grails and surprisingly, in production environment it was slower than on my laptop. Production administrator found that my application generate too many SQL queries. The delay was caused by network latency. So, I had to find a way how to monitor number of SQL queries on MySQL server.

For monitoring of the current session, there is a handy command:

SHOW STATUS WHERE variable_name='Com_select'

For monitoring of all the sessions, there is a switch:

SHOW global STATUS WHERE variable_name='Com_select'

All variables could be listed by


Live fulltext search in Ruby on Rails

Some time ago I promised to create a small tutorial about live fulltext search. A fulltext search, that gives you results as you type.


  • Ruby on rails
  • ferret gem (gem install ferret)
  • acts_as_ferret gem (gem install acts_as_ferret)
  • auto_complete plugin (from the application root: ruby script/plugin install auto_complete)

What we will do

  1. Create an empty application – simple book database
  2. Add fulltext search capabilities
  3. Create the live search
    1. Create search pane partial – the one that will display the search box
    2. Create the search results partial – that will render the hints (search results)
    3. Modify controller to respond to the search pane

Create a book database application

We will create a small application for book management. It will store, list, update books and it will also provide the live search.
So, lets create the skeleton of the aplication:

# Create the rails application
rails books
# create database books
echo "create database books"  | mysql -u root -p
cd books

Configure database login and password in app/config/database.yml.

  adapter: mysql
  database: books
  username: root
  password: password
  host: localhost
  port: 3306

Create skeleton of the application. From root of the application run:

ruby script/generate scaffold Book title:string abstract:text

Create the books table

rake db:migrate

Start up the development server

ruby script/server

Now, browse to and type in some data.

Add fulltext search capabilities

Change the app/models/book.rb to support fulltext search

require "acts_as_ferret"
class Book < ActiveRecord::Base

You can check in the console, that the fulltext is enabled. Just start the console via
ruby script/console and put there


It should return a result set, similar to this:

=> #<ActsAsFerret::SearchResults:0x2540f54 @results=[#<Book id: 2, title: "Book secondo", abstract: "Book about book", created_at: "2008-07-07 23:16:38", updated_at: "2008-07-07 23:16:38">, #<Book id: 1, title: "First book", abstract: "This is a first book", created_at: "2008-07-07 23:16:23", updated_at: "2008-07-07 23:16:23">], @total_hits=2>

Create the live search

Finally, create the live search.

Create search pane partial

The search_pane will be used to display search box.

Create a partial _search_pane.html.erb in app/views/books and put there simple tag. The tag create Ajax Autocompleter that calls auto_complete_for_search_query method of the default controller (in our case it will be books)

<%= text_field_with_auto_complete :search, :query %>

Add javascript include and partial rendering to the books template app/views/layouts/books.html.erb.

Do not forget! The javascript include must be in the head of the template.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
<html xmlns="" xml:lang="en" lang="en">
  <meta http-equiv="content-type" content="text/html;charset=UTF-8" />
  <title>Books: <%= controller.action_name %></title>
  <!-- HERE -->
  <%= stylesheet_link_tag 'scaffold' %>
	<%= javascript_include_tag :defaults %> 
<!-- AND HERE -->
<%= render :partial=>"books/search_pane" %>
<p style="color: green"><%= flash[:notice] %></p>
<%= yield  %>

Create the search results partial

The search_results will format the results of the full text search and will “offer” the resulting records. Create a partial app/views/books/_search_results.html.erb and add there the formatting code:

	<% for book in @books %>
	<li><%= link_to h(book.title), :controller=>"books", :action=>"show", :id=>book %></li>
  	<% end %>

Modify controller

Add the following line at the beginning of the books_controller.

protect_from_forgery :only => [:create, :update, :destroy]

Create a method in books_controller that will search for the books

 def auto_complete_for_search_query
   @books = Book.find_by_contents(params["search"]["query"]+"*", {:limit => 5})
   render :partial => "search_results"

We do not want to generate the whole page layout, so it is necessary to specify it in the books controller:

layout 'books', :except => [:auto_complete_for_search_query]

And now, navigate to and start searching. As soon as you start typing into the search box, it shows results. Click on one of the proposed links to see what happens. Source code is here.

Bootstrapping Grails integration tests

It is quite easy to prepare test environment the same as the development one. Just use Bootstrap.

class BootstrappedTests extends GroovyTestCase {
  void testBootstrap() {
    new BootStrap().init(null) 
    assert MyDomain.count() == 5   // Perform your tests here

Custom iterative tag in Grails with named variable

I will show you how to create an iterative Grails tag that can contain another tag. The inner tag will use variable of the iterative tag. So, we are going to implement a tag that creates n links ’/show/1’, ’/show/2’, etc. with description ‘Article number 1’, ‘Article number 2’ etc.:

First of all, there is a nice and handy example for a simple iterative tag on the Grails site.


def repeat = { attrs, body ->
  def i = Integer.valueOf( attrs["times"] )
  def current = 0
  i.times {
    // pass the current iteration as the groovy default arg "it"
    // then pass the result to "out" to send it to the view
    out << body( ++current )


<g:repeat times="3">
  <p>Repeat this 3 times! Current repeat = ${it}</p>

Slight modification of the tag does not lead to the requested functionality:

<g:repeat times="3"> 
  <g:link action="show" id="${it}">Hello number  ${it}</g:link> 

It just generates the “Article number” string three times, because the it variable is not known here.
Thus it is necessary to change the tag definition. Lets add another parameter vars as a symbolic name of the current member of the collection.

def repeat = { attrs, body -> 
  def pars = [:]
  attrs.times?.toInteger().times { n -> 
    pars[attrs.var] = n
    out << body(pars) 

And now it is possible to use g:link inside the custom tag:

<g:repeat times="3" var="num"> 
  <g:link action="show" id="${num}">Hello number  ${num}</g:link> 

I am not sure if it is supported, but it is definitely working.

From Mysql to Oracle: Grails application migration

Today I have finished migration of our Grails prototype. Originally we did develop it for MySQL, but the final system have to work with Oracle. This post summarize troubles and differences I was facing.

User creation

Since I am not Oracle administrator, it took me some time to put together script that creates user and appropriate rights for the Grails application. Use the script for development only.

CREATE user grails IDENTIFIED BY grassword;
GRANT connect  TO habilion;
GRANT CREATE sequence TO habilion;
GRANT unlimited tablespace TO habilion;

Identifier length limitation

I usually do not use identifiers that are 30 characters long. But, there is a trick.

Imagine two classes: Author and Content. Author having multiple Contents.

Now, imagine there is several types of Content – Book, Newspaper, ElectronicNewspaper, ScientificArticle…
So, we can model the situation using following classes:

Class Content{
  static belongsTo = [ contentAuthor:Author ]
Class Book extends Content {}
Class Newspaper extends Content {}
Class ElectronicNewspaper extends Content {}
Class ScientificArticle extends Content {}
Class Author{
  static hasMany = [ contents: Content ]

And now comes the trick. The ‘content’ table in the database contains long columns:


As you can see, it is easy to hit the 30 characters limit. If you look deeper, you will see the solution. The column names are composed of class name of the Content descendants and variable name pointing to the master entity.
So, I renamed the contentAuthor to ca, created getter and setter for contentAuthor and checked all “new Book…” snippets. God bless MVC!

Column size

The second major issue was a String column. I had a string column of 10000 characters.

Class Content {
  String abstract
  static constraints = { abstract(maxSize:10000) }

It was translated to Long data type. Unfortunately there is a bug in Oracle JDBC driver that causes nasty stack traces when processing Long data type. There is a workaround on the Internet, but it is working only for some versions of Oracle. Moreover, there is just one Long column allowed per table. So, changed the size to 4000 characters and the column was created as VARCHAR2.

And thats it!

Update: Empty strings

One thing to mention is a fact that Oracle does not distinguish between null and an empty string. So, if you try to store an empty string to Oracle DB, it actually stores it as a null value.

The problem arises, if you have not null column and you try to store empty string ‘’. For Grails it is a not null value, so it does not apply defaults. For Oracle it is null and thus constraint violation…