sql_range_query with empty string

[ekosz]

While setting up Sphinx on my production server, this strange error came up when trying to index

ERROR: index 'benefit_core': sql_range_query: You have an error in your SQL 
syntax; check the manual that corresponds to your MySQL server version for the 
right syntax to use near '' at line 1

That empty string is giving me a headache. This doesn't happen on my local machine.

Does anyone seen this type of issue before or knows where it can originate?

benefit.rb

define_index do 
  # Fields
  indexes category
  indexes title
  indexes tags
  indexes description
  indexes brief_description
  indexes brand
  indexes short_description
  indexes long_description
  indexes benefit_description
  indexes address.city
  indexes address.state
  indexes address.street_1
  where sanitize_sql(["active = true and expiration > ?", Time.now]) 
  set_property :field_weights => {
    :title => 15,
    :tags => 10,
    :brand => 10,
    :description => 3
  }

end

Thinking-Sphinx - 1.4.4

Sphinx - 0.9.9

Thank you!

[pat]

Are you using the same version of Thinking Sphinx in development? Because I don't think sanitize_sql is available in 1.4.4 within a define_index block. Also: are you using MySQL in development as well?

Pat

On 01/07/2011, at 7:19 AM, ekosz wrote:

While setting up Sphinx on my production server, this strange error came up when trying to index

ERROR: index 'benefit_core': sql_range_query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

That empty string is giving me a headache. This doesn't happen on my local machine.

Does anyone seen this type of issue before or knows where it can originate?

benefit.rb

define_index do

Fields

 indexes category
 indexes title
 indexes tags
 indexes description
 indexes brief_description
 indexes brand
 indexes short_description
 indexes long_description
 indexes benefit_description
 indexes address.city
 indexes address.state
 indexes address.street_1
 where sanitize_sql(["active = true and expiration > ?", Time.now]) 
 set_property :field_weights => {
   :title => 15,
   :tags => 10,
   :brand => 10,
   :description => 3
 }

end

Thinking-Sphinx - 1.4.4

Sphinx - 0.9.9

Thank you!

Reply to this email directly or view it on GitHub: https://github.com/freelancing-god/thinking-sphinx/issues/248

[ekosz]

Yes on both accounts.

[pat]

Ah, my mistake, the feature is there in 1.4.4.

What about the following:

where sanitize_sql(['active = ? AND expiration > ?", true, Time.now])

[ekosz]

Ok, so it is defiantly that line. I tried that code out and had the same problem. I then changed the code to where "active = true AND expiration > #{Time.now.to_formatted_s(:db)}" and got a new error:

ERROR: index 'benefit_core': sql_range_query: You have an error in your SQL 
syntax; check the manual that corresponds to your MySQL server version for 
the right syntax to use near '22:35:18 GROUP BY `benefits`.`id`  ORDER BY 
NULL' at line 1 

[pat]

Try putting quotes around the date:

where "active = true AND expiration > '#{Time.now.to_formatted_s(:db)}'"

[ekosz]

It worked! Perfect. I guess the problem was with sanitize_sql in that version. Thank you for your help!

[pat]

No problem, great to have it all sorted :)