App isn't querying db on server, but works in development

I've got a very small app that uses ajax for a live preview. On my
development machine (using sqlite3), it works just fine. Here is a
sample of the log with a known good query:

development.log

Any ideas?

Any ideas?

The default in production mode is not to log SQL queries, so the lack
of queries in the log is likely a red herring. The request is getting
processed and rendering something - did you try using something like
firebug to see in what way the response differs from what you expect ?

Fred

And of course, you can configure MySQL to log queries for you to
examine separately.

You might also review whether you have some database-specific
code (e.g. a `conditions => "non-standard-SQL" `) anywhere.

Very interesting. I had no idea SQL queries didn't show up in the
production log.

Here's my code for the suspected offending method. Is there something
in that query that wouldn't work with MySQL but OK with SQLite3?

def get_results
  if request.xhr?
    if params['search_text'].strip.length > 0
    terms = params['search_text'].split.collect do |word|
      "%#{word.downcase}%"
    end
    @posts = Post.find(
    :all,
    :conditions => [
      ( ["(LOWER(question || answer) LIKE ?)"] * terms.size ).join(" AND
"), * terms.flatten
    ]
    )
  end
  render :partial => "search"
  else
  redirect_to :action => "index"
  end

P.S. You guys are great.

Is the || in the query a string concatenation operator, as in the SQL
standard and PostgreSQL? If so, then that's your problem. MySQL chose
to implement string concatenation in a nonstandard way (as the concat()
function) rather than following the standard. Just another arbitrary
mySQL stupidity. I'm glad I no longer use it. :slight_smile:

MySQL supports || for string concatenation and PostgreSQL has CONCAT() too.
I guess you just could not resist to comment on product you neither
know nor use.

Regards,
Rimantas

I used mySQL for about 5 years and know it well. That's why I don't use
it anymore. :slight_smile: (I guess you couldn't resist flaming when you didn't
have accurate info to share...)

set sql_mode='ANSI';

Query OK, 0 rows affected (0.00 sec)

It kind of depends on what the db adaptor turns this into :slight_smile:

Can you check your MySQL query log to see what this produces?
Or for that matter check the error log, as well.

Sorry for the delay, I've been out of town.

Here's the query from the terminal on my development machine, with a
known good term (fire):

Processing PostsController#get_results (for 127.0.0.1 at 2009-09-21
20:36:34) [POST]
  Parameters: {"authenticity_token"=>"q5E5x7rV3Xvz8EPJ/WyQcFA5xcCgZN
+6CLWdW19rJ6A=", "search_text"=>"fire"}
  Post Load (0.3ms) SELECT * FROM `posts` WHERE ((LOWER(question ||
answer) LIKE '%fire%'))
Rendered posts/_search (0.6ms)
Completed in 19ms (View: 3, DB: 1) | 200 OK [http://localhost/posts/
get_results]

And the same entry from the development.log:

Processing PostsController#get_results (for 127.0.0.1 at 2009-09-21
20:36:34) [POST]
  Parameters: {"authenticity_token"=>"q5E5x7rV3Xvz8EPJ/WyQcFA5xcCgZN
+6CLWdW19rJ6A=", "search_text"=>"fire"}
  ^[[4;36;1mPost Load (0.3ms)^[[0m ^[[0;1mSELECT * FROM `posts`
WHERE ((LOWER(question || answer) LIKE '%fire%')) ^[[0m
Rendered posts/_search (0.6ms)
Completed in 19ms (View: 3, DB: 1) | 200 OK [http://localhost/posts/
get_results]

Thanx,

Eric

I thought I'd revisit this and say that I ended up getting this
working, with a change in query.

I tried running the query in MySQL and it still didn’t work. But, when
I took out the “or answer” part (|| answer), it did. A friend of mine
told me I had my || in the wrong order for MySQL (though strangely
enough, not for SQLite3).

All I needed to do was to change my SQL query in the controller from
this:

(LOWER(question || answer) LIKE ?)

To this:

(LOWER(question)) || (LOWER(answer)) LIKE ?

And voila! It worked. Thanks to Hassan to get me to think outside the
box to find a way to troubleshoot.

Eric

I hate to rain on your parade but learning how to debug SQL is not
thinking outside the box.