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
On Sep 12, 10:31 pm, Eric G. [email protected] 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 Sat, Sep 12, 2009 at 4:11 PM, Frederick C.
[email protected] 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 S. ------------------------ [email protected]
twitter: @hassan
Eric G. 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
[email protected]
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 L. 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
[email protected]
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/
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 S. [email protected]
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 S. [email protected]
On Mon, Sep 14, 2009 at 9:32 AM, Eric G. [email protected]
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 S. ------------------------ [email protected]
twitter: @hassan
On Wed, Sep 30, 2009 at 1:41 PM, Eric G. [email protected]
wrote:
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 D.
http://destiney.com/
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