Skip to main content

Benjamin Oakes

Photo of Ben Oakes

Hi, I'm Ben Oakes and this is my geek blog. Currently, I'm a Ruby/JavaScript Developer at Continuity Control. Previously, I was a Software Developer at Hedgeye, a Research Assistant in the Early Social Cognition Lab at Yale University and a student at the University of Iowa. I also organize ICRuby and OpenHack Iowa City, and previously organized NewHaven.rb. I have an amazing wife named Danielle Oakes.

Filtering for the MySQL category. Clear

That’s not doing quite what you think…

by Ben

I recently helped an intern at Hedgeye work through a problem with a database query. Because I’m working in a separate timezone, I ended up making suggestions through a GitHub pull request. We discussed and decided that what I wrote was self-contained enough that I should re-post so it can help others.

:conditions => ["event_type != ?", 'LOGIN'||'LOGOUT'],

I don’t think this is doing quite what you think…

'LOGIN' || 'LOGOUT' # => 'LOGIN'

So this turns into:

where event_type != 'LOGIN'

I’m guessing you meant to do:

where event_type != 'LOGIN' or event_type != 'LOGOUT'

But, believe it or not, != is a MySQL proprietary extension to SQL. It would probably be best to use something that’s a part of ANSI SQL:

where event_type <> 'LOGIN' or event_type <> 'LOGOUT'
-- alternative:
where event_type not in ('LOGIN', 'LOGOUT')

Because these are literals (not user-provided values), there’s no point in sanitization using ?.

Conclusion:

:conditions => "event_type not in ('LOGIN', 'LOGOUT')",

database configuration does not specify adapter

by Ben

From an answer I wrote for StackOverflow:

Another possible cause:

In Rails 3.2.x, establish_connection has a default argument set from the environment:

From connection_specification.rb:

def self.establish_connection(spec = ENV["DATABASE_URL"])
  resolver = ConnectionSpecification::Resolver.new spec, configurations
  spec = resolver.spec

The way ConnectionSpecification::Resolver works depends on ENV['DATABASE_URL'] giving a nil if not set. (Normally, it would be something like postgres://...).

So, if you happen to have misconfigured DATABASE_URL such that ENV['DATABASE_URL'] == '', that will give you database configuration does not specify adapter.

MySQL console tip

by Ben

If you terminate statements with \G instead of ;, the MySQL console will print the result set vertically rather than as a horizontal table.

For example, instead of:

+----+--------+--------+
| id | foo_id | bar_id |
+----+--------+--------+
| 1  | 6      | 2      |
+----+--------+--------+

You will have:

*************************** 1. row ***************************
                         id: 186
                     foo_id: 6
                     bar_id: 2

It’s really useful for wide tables, or ones with text columns.

I’m always surprised that the MySQL docs don’t make it easier to find this — it’s one of the better tricks for using the database console.