Rails - Sep 2009 - 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
-----------------------
Processing PostsController#get_results (for 127.0.0.1 at 2009-09-11
10:59:06) [POST]
  Parameters: {"authenticity_token"=>"SOME-CODE-HERE=",
"search_text"=>"?", "_"=>""}
   [4;36;1mPost Load (0.0ms) [0m    [0;1mSELECT * FROM "posts" WHERE
((LOWER(question || answer) LIKE ''%?%''))  [0m
Rendered posts/_search (15.0ms)
Completed in 15ms (View: 15, DB: 0) | 200 OK [http://localhost/posts/
get_results]

But on my server (using MySQL), it doesn''t produce a result. My gems
and my application are frozen to Rails 2.3.4. The database is running
and working, as I am able to view entires using GET queries. But live
preview isn''t working, and there isn''t any SQL queries in the
production log:

production.log
--------------------
Processing PostsController#get_results (for 555.555.55.55 at
2009-09-11 11:23:38) [POST]
  Parameters: {"authenticity_token"=>"SOME-CODE-HERE=",
"search_text"=>"fire", "_"=>""}
Completed in 5ms (View: 0, DB: 0) | 200 OK [http://example.com/faq/
posts/get_results]
Processing

Any idea what is going on here?

Thanx in advance,

Eric

Any ideas?

On Sep 11, 1:25 pm, "Eric J. Gruber"
<e...-XGWyyMbmuCAI8iZMlb555w@public.gmane.org>
wrote:
> 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
> -----------------------
> Processing PostsController#get_results (for 127.0.0.1 at 2009-09-11
> 10:59:06) [POST]
>   Parameters:
{"authenticity_token"=>"SOME-CODE-HERE=",
> "search_text"=>"?", "_"=>""}
>    [4;36;1mPost Load (0.0ms) [0m    [0;1mSELECT * FROM "posts"
WHERE
> ((LOWER(question || answer) LIKE ''%?%''))  [0m
> Rendered posts/_search (15.0ms)
> Completed in 15ms (View: 15, DB: 0) | 200 OK [http://localhost/posts/
> get_results]
>
> But on my server (using MySQL), it doesn''t produce a result. My
gems
> and my application are frozen to Rails 2.3.4. The database is running
> and working, as I am able to view entires using GET queries. But live
> preview isn''t working, and there isn''t any SQL queries in
the
> production log:
>
> production.log
> --------------------
> Processing PostsController#get_results (for555.555.55.55at
> 2009-09-11 11:23:38) [POST]
>   Parameters:
{"authenticity_token"=>"SOME-CODE-HERE=",
> "search_text"=>"fire",
"_"=>""}
> Completed in 5ms (View: 0, DB: 0) | 200 OK [http://example.com/faq/
> posts/get_results]
> Processing
>
> Any idea what is going on here?
>
> Thanx in advance,
>
> Eric

On Sep 12, 10:31 pm, Eric Gruber
<e...-XGWyyMbmuCAI8iZMlb555w@public.gmane.org>
wrote:
> 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
> On Sep 11, 1:25 pm, "Eric J. Gruber"
<e...-XGWyyMbmuCAI8iZMlb555w@public.gmane.org> wrote:
>
>
>
> > 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
> > -----------------------
> > Processing PostsController#get_results (for 127.0.0.1 at 2009-09-11
> > 10:59:06) [POST]
> >   Parameters:
{"authenticity_token"=>"SOME-CODE-HERE=",
> > "search_text"=>"?",
"_"=>""}
> >    [4;36;1mPost Load (0.0ms) [0m    [0;1mSELECT * FROM
"posts" WHERE
> > ((LOWER(question || answer) LIKE ''%?%''))  [0m
> > Rendered posts/_search (15.0ms)
> > Completed in 15ms (View: 15, DB: 0) | 200 OK [http://localhost/posts/
> > get_results]
>
> > But on my server (using MySQL), it doesn''t produce a result.
My gems
> > and my application are frozen to Rails 2.3.4. The database is running
> > and working, as I am able to view entires using GET queries. But live
> > preview isn''t working, and there isn''t any SQL
queries in the
> > production log:
>
> > production.log
> > --------------------
> > Processing PostsController#get_results (for555.555.55.55at
> > 2009-09-11 11:23:38) [POST]
> >   Parameters:
{"authenticity_token"=>"SOME-CODE-HERE=",
> > "search_text"=>"fire",
"_"=>""}
> > Completed in 5ms (View: 0, DB: 0) | 200 OK [http://example.com/faq/
> > posts/get_results]
> > Processing
>
> > Any idea what is going on here?
>
> > Thanx in advance,
>
> > Eric

On Sat, Sep 12, 2009 at 4:11 PM, Frederick Cheung
<frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> The default in production mode is not to log SQL queries, so the lack
> of queries in the log is likely a red herring.
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.

-- 
Hassan Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
twitter: @hassan

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.

On Sep 12, 11:02 pm, Hassan Schroeder
<hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Sat, Sep 12, 2009 at 4:11 PM, Frederick Cheung
>
> <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > The default in production mode is not to log SQL queries, so the lack
> > of queries in the log is likely a red herring.
>
> 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.
>
> --
> Hassan Schroeder ------------------------
hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
> twitter: @hassan

Eric Gruber wrote:
> 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?
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. :)

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

> 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. :)
 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
--
http://rimantas.com/

Rimantas Liubertas wrote:
>> 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. :)
> 
>  MySQL supports || for string concatenation 
Not according to the manual. The only meaning given for || is logical 
or.
> and PostgreSQL has CONCAT() 
Not according to the manual. || is the only string concatenator listed.
> too.
> I guess you just could not resist to comment on product you neither
> know nor use.
I used mySQL for about 5 years and know it well.  That''s why I
don''t use
it anymore. :)  (I guess you couldn''t resist flaming when you
didn''t
have accurate info to share...)
> 
> Regards,
> Rimantas
> --
> http://rimantas.com/
Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

> I used mySQL for about 5 years and know it well.  That''s why I
don''t use
> it anymore. :)  (I guess you couldn''t resist flaming when you
didn''t
> have accurate info to share...)
mysql> set sql_mode=''ANSI'';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ''abc'' || ''def'' ||
''ghj'';
+-------------------------+
| ''abc'' || ''def'' || ''ghj''
|
+-------------------------+
| abcdefghj               |
+-------------------------+
1 row in set (0.00 sec)


Regards,
Rimantas
--
http://rimantas.com/

On Mon, Sep 14, 2009 at 9:32 AM, Eric Gruber
<eric-XGWyyMbmuCAI8iZMlb555w@public.gmane.org> wrote:
> 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?
>                :conditions => [
>                  ( ["(LOWER(question || answer) LIKE ?)"] *
terms.size ).join(" AND
> "), * terms.flatten
>                ]
It kind of depends on what the db adaptor turns this into :-)

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

-- 
Hassan Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
twitter: @hassan

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


On Sep 14, 11:42 pm, Hassan Schroeder
<hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Mon, Sep 14, 2009 at 9:32 AM, Eric Gruber
<e...-XGWyyMbmuCAI8iZMlb555w@public.gmane.org> wrote:
> > 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?
> >                :conditions => [
> >                  ( ["(LOWER(question || answer) LIKE ?)"] *
terms.size ).join(" AND
> > "), * terms.flatten
> >                ]
>
> It kind of depends on what the db adaptor turns this into :-)
>
> Can you check your MySQL query log to see what this produces?
> Or for that matter check the error log, as well.
>
> --
> Hassan Schroeder ------------------------
hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
> twitter: @hassan

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

On Sep 21, 8:42 pm, Eric Gruber
<e...-XGWyyMbmuCAI8iZMlb555w@public.gmane.org>
wrote:
> 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
>
> On Sep 14, 11:42 pm, Hassan Schroeder
<hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>
> > On Mon, Sep 14, 2009 at 9:32 AM, Eric Gruber
<e...-XGWyyMbmuCAI8iZMlb555w@public.gmane.org> wrote:
> > > 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?
> > >                :conditions => [
> > >                  ( ["(LOWER(question || answer) LIKE
?)"] * terms.size ).join(" AND
> > > "), * terms.flatten
> > >                ]
>
> > It kind of depends on what the db adaptor turns this into :-)
>
> > Can you check your MySQL query log to see what this produces?
> > Or for that matter check the error log, as well.
>
> > --
> > Hassan Schroeder ------------------------
hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
> > twitter: @hassan

On Wed, Sep 30, 2009 at 1:41 PM, Eric Gruber
<eric-XGWyyMbmuCAI8iZMlb555w@public.gmane.org>
wrote:
> 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.

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


-- 
Greg Donald
http://destiney.com/