Connection::MRI#query_all doesn't split results properly

[timriley]

I'm using these versions of thinking sphinx and mysql2:

gem "mysql2", "0.3.12b4"
gem "thinking-sphinx", "~> 3.0.0"

I can successfully create an index, but when I tried a search, I got errors like this:

>> Post.search("coffee").first

NoMethodError: undefined method `inject' for nil:NilClass
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:49:in `call'
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:14:in `block in call'
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:13:in `each'
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:13:in `call'
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/geographer.rb:9:in `call'
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/sphinxql.rb:11:in `call'
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/stale_id_filter.rb:10:in `call'
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/middleware-0.1.0/lib/middleware/runner.rb:31:in `call'
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/middleware-0.1.0/lib/middleware/builder.rb:102:in `call'
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/search.rb:58:in `populate'
    from /Users/tim/.rbenv/versions/1.9.3-p194/lib/ruby/gems/1.9.1/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/search.rb:99:in `method_missing'

It turns out that Connection::MRI#query_all wasn't handling the two queries from BatchInquirer properly. BatchInquirer was calling append_query with context[:sphinxql].to_sql and Riddle::Query.meta properly, but when ThinkingSphinx::Middlewares::Inquirer#call ran batch.results, it was getting an array back with only one Mysql2::Result, instead of two. This meant that Inner.new(context).call batch.results[index], batch.results[index + 1] was passing nil as the second argument and causing the "undefined method" errors.

I was able to make things work again with this monkey-patch inside my app:

module ThinkingSphinx
  module Connection
    class MRI
      def query_all(*statements)
        statements.map { |statement| client.query(statement) }
      end
    end
  end
end

That's less efficient than your current approach, because it results in two queries, but at this point, at least it's giving me two results back, which means I can actually run searches with TS3 now.

Any ideas what might be happening?

[pat]

This one is a little baffling. We're using what is essentially 3.0.0 (all commits from last year) in production, and we're not seeing this issue. Works fine for me locally as well.

It sounds like you've debugged this pretty thoroughly, but just to confirm: you've been using the b4 release of mysql2 the whole time? 0.3.11 doesn't support multi-statement queries, hence why the beta release is necessary.

[timriley]

It's definitely strange. I can confirm I'm on 0.3.12b4. Here's a console session that demonstrates my issue even more clearly (it also shows that the MULTI_STATEMENTS flag is indeed set on the Mysql2::Client. This is with the stock ThinkingSphinx 3.0.0 code, with my monkey-patch disabled:

>> Mysql2::VERSION
=> "0.3.12b4"

>> connection = ThinkingSphinx::Connection.new
=> #<ThinkingSphinx::Connection::MRI:0x007fc0115ec9e0 @client=#<Mysql2::Client:0x007fc0115ec940>>

>> connection.client.query_options[:flags]
=> 65536

>> Mysql2::Client::MULTI_STATEMENTS
=> 65536

>> connection.query_all("SELECT * FROM `post_core` WHERE MATCH('coffee @sphinx_internal_class_name (Post|Author|City|Page|Article)') AND sphinx_deleted = 0 LIMIT 0, 20", "SHOW META")
=> [#<Mysql2::Result:0x007fc011700e30>]

As you can see in the last line, I've passed two queries to query_all, but only one result has come back.

[timriley]

I've tried the above with the mysql2 0.3.12b5 version and the results are the same.

[pat]

And yet on my machine, this is the difference (using the 3.0.0 gem, not a custom commit):

>> connection.query_all("SELECT * FROM post_core WHERE MATCH('sleep') LIMIT 0, 20", "SHOW META")
=> [#<Mysql2::Result:0x007ffb24713848>, #<Mysql2::Result:0x007ffb247137d0>]

I am using ruby 1.9.3p327, which is slightly newer... if that makes the difference, though, I'll be surprised. That said, I'm baffled as to what the cause might be anyway.

[timriley]

Upgraded my ruby AND my MySQL and I still can't get the same results as you. Not really sure what else I can do. Any suggestions?

[pat]

Is this a new app, or something with plenty of other gems in the mix? I wonder if something else is getting in the way.

[timriley]

I just tried it with a newly generated Rails 3.2.8 app. The Gemfile:

source 'https://rubygems.org'

gem 'rails', '3.2.8'
gem 'sqlite3'

group :assets do
  gem 'sass-rails',   '~> 3.2.3'
  gem 'coffee-rails', '~> 3.2.1'
  gem 'uglifier', '>= 1.0.3'
end

gem 'jquery-rails'

gem "mysql2", "0.3.12b5"
gem "thinking-sphinx", "~> 3.0.0"
gem "flying-sphinx", github: "flying-sphinx/flying-sphinx", branch: "ts3"

And the output of bundle show:

  * actionmailer (3.2.8)
  * actionpack (3.2.8)
  * activemodel (3.2.8)
  * activerecord (3.2.8)
  * activeresource (3.2.8)
  * activesupport (3.2.8)
  * arel (3.0.2)
  * builder (3.0.4)
  * bundler (1.2.3)
  * coffee-rails (3.2.2)
  * coffee-script (2.2.0)
  * coffee-script-source (1.4.0)
  * erubis (2.7.0)
  * execjs (1.4.0)
  * faraday (0.8.4)
  * faraday_middleware (0.9.0)
  * flying-sphinx (0.8.4 f87bbbd)
  * hashie (1.2.0)
  * hike (1.2.1)
  * i18n (0.6.1)
  * innertube (1.0.2)
  * journey (1.0.4)
  * jquery-rails (2.1.4)
  * json (1.7.6)
  * mail (2.4.4)
  * middleware (0.1.0)
  * mime-types (1.19)
  * multi_json (1.5.0)
  * multipart-post (1.1.5)
  * mysql2 (0.3.12b5)
  * polyglot (0.3.3)
  * rack (1.4.1)
  * rack-cache (1.2)
  * rack-ssl (1.3.2)
  * rack-test (0.6.2)
  * rails (3.2.8)
  * railties (3.2.8)
  * rake (10.0.3)
  * rash (0.3.2)
  * rdoc (3.12)
  * riddle (1.5.4)
  * sass (3.2.4)
  * sass-rails (3.2.5)
  * sprockets (2.1.3)
  * sqlite3 (1.3.6)
  * thinking-sphinx (3.0.0)
  * thor (0.16.0)
  * tilt (1.3.3)
  * treetop (1.4.12)
  * tzinfo (0.3.35)
  * uglifier (1.3.0)

And those same console commands revealed the same behaviour. Only one mysql result for the multiple-query string:

>> Mysql2::VERSION
=> "0.3.12b5"

>> connection = ThinkingSphinx::Connection.new
=> #<ThinkingSphinx::Connection::MRI:0x007f81d6b379a0 @client=#<Mysql2::Client:0x007f81d6b37860>>

>> connection.query_all("SELECT * FROM `post_core` WHERE MATCH('coffee @sphinx_internal_class_name (Post|Author|City|Page|Article)') AND sphinx_deleted = 0 LIMIT 0, 20", "SHOW META")
=> [#<Mysql2::Result:0x007f81d6b48458>]

I thought it might be useful to try it completely outside the Rails environment too, so I just fired up irb and ran the following:

>> require "rubygems"
=> false
>> require "mysql2"
=> true
>> require "thinking_sphinx"
=> true

>> Mysql2::VERSION
=> "0.3.12b5"

>> connection = ThinkingSphinx::Connection.new                                                                                                                             => #<ThinkingSphinx::Connection::MRI:0x007fd0b133cf50 @client=#<Mysql2::Client:0x007fd0b133caa0>>

>> connection.query_all("SELECT * FROM `post_core` WHERE MATCH('coffee @sphinx_internal_class_name (Post|Author|City|Page|Article)') AND sphinx_deleted = 0 LIMIT 0, 20", "SHOW META")
=> [#<Mysql2::Result:0x007fd0b136b4e0>]

Same result.

[pat]

Straight IRB for me works like it has been working already on my machine.

What version of MySQL do you have installed? And (with thanks to @radar looking over my shoulder thinking the issue through), what version of Sphinx are you using?

[timriley]

• MySQL: mysql Ver 14.14 Distrib 5.5.29, for osx10.8 (i386) using readline 5.1
• Sphinx: Sphinx 2.0.5-release (r3308)

[pat]

Curses, I was hoping it'd be the Sphinx version that would be different.

• mysql Ver 14.14 Distrib 5.5.27, for osx10.7 (i386) using readline 5.1
• Sphinx 2.0.5-release (r3308)

[pat]

@Sutto I know you've got this working locally - what version of OS X are you on, as well as MySQL and Sphinx?

[Sutto]

@pat OSX: 10.8.2 MySQL: 5.5.14 mysql-connector-c: 6.0.2 mysql2 (linked to Mysql, not mysql-connector-c - very important): 0.3.12b5 Sphinx: 2.0.6-release (r3473) Ruby: ruby-1.9.3-p194

[Sutto]

Oh, and myql --version: mysql Ver 14.14 Distrib 5.5.14, for osx10.7 (i386) using readline 5.1

[Sutto]

Can also check servers it's working on for versions if needed.

[pat]

Tim, can you give it a spin with Sphinx 2.0.6?

[timriley]

Sure thing. Still just one result returned.

[pat]

I'm running out of ideas. I wonder if it's something in the mysql2 gem - @brianmario, if you have any time, would certainly appreciate your wisdom here.

@Sutto: many thanks for your prompt responses, good to know it's been working smoothly for you.

[pat]

Tim, perhaps it's worth trying a multi-query call to a mysql database instead, using Mysql2?

[timriley]

Good idea, Pat. Here's something interesting: it actually works with a regular MySQL server! I opened the console for the same Rails app as all my other tests here, and ran something that more or less equates to what Thinking Sphinx's query_all does:

>> client = Mysql2::Client.new(host: "127.0.0.1", port: 3306, username: "root", flags: Mysql2::Client::MULTI_STATEMENTS)
=> #<Mysql2::Client:0x007fab9105bfd8>

>> results  = [client.query("SHOW DATABASES; SHOW DATABASES")]
=> [#<Mysql2::Result:0x007fab91246be0>]

>> results << client.store_result while client.next_result
=> nil

>> results
=> [#<Mysql2::Result:0x007fab91246be0>, #<Mysql2::Result:0x007fab9124dff8>]

I guess this would indicate the issue is with Thinking Sphinx, or somewhere in between my app and TS?

[pat]

How are you installing Sphinx? Compiling source, or some other means? And if the former, what commands are you running to configure/compile it?

[tscolari]

I'm having the same NoMethodError: undefined methodinject' for nil:NilClass` error for every search I try. I'm using Postgres here, and sphinx version 3.0.0.

1.9.3p374 :010 > Page.search('test')
 => #<ThinkingSphinx::Masks::PaginationMask:0x007fe7727784e8 @search=#<ThinkingSphinx::Masks::PaginationMask:0x007fe7727784e8 ...>> 
1.9.3p374 :011 > _.all
   (0.3ms)   SELECT DISTINCT type
 FROM pages

NoMethodError: undefined method `inject' for nil:NilClass
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:49:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:14:in `block in call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:13:in `each'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:13:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/geographer.rb:9:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/sphinxql.rb:11:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/stale_id_filter.rb:10:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/middleware-0.1.0/lib/middleware/runner.rb:31:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/middleware-0.1.0/lib/middleware/builder.rb:102:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/search.rb:58:in `populate'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/search.rb:99:in `method_missing'
    from (irb):11
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/railties-3.2.11/lib/rails/commands/console.rb:47:in `start'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/railties-3.2.11/lib/rails/commands/console.rb:8:in `start'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/railties-3.2.11/lib/rails/commands.rb:41:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in `<main>'
1.9.3p374 :012 > ThinkingSphinx.search 'test'
 => #<ThinkingSphinx::Masks::PaginationMask:0x007fe76ba9ec78 @search=#<ThinkingSphinx::Masks::PaginationMask:0x007fe76ba9ec78 ...>> 
1.9.3p374 :013 > _.all
NoMethodError: undefined method `inject' for nil:NilClass
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:49:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:14:in `block in call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:13:in `each'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/inquirer.rb:13:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/geographer.rb:9:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/sphinxql.rb:11:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/middlewares/stale_id_filter.rb:10:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/middleware-0.1.0/lib/middleware/runner.rb:31:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/middleware-0.1.0/lib/middleware/builder.rb:102:in `call'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/search.rb:58:in `populate'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/thinking-sphinx-3.0.0/lib/thinking_sphinx/search.rb:99:in `method_missing'
    from (irb):13
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/railties-3.2.11/lib/rails/commands/console.rb:47:in `start'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/railties-3.2.11/lib/rails/commands/console.rb:8:in `start'
    from /Users/tscolari/.rvm/gems/ruby-1.9.3-p374@portal/gems/railties-3.2.11/lib/rails/commands.rb:41:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in `<main>'

This all was working with sphinx 2. Any idea what could cause this?

[pat]

Hi Tiago

Just with the last question I asked @timriley: how did you install Sphinx? And if you're compiling by hand, what flags did you use?

[tscolari]

Hello, I installed it using homebrew, mysql and postgres too.

[pat]

Would it be possible for you to try compiling it from the source instead? It shouldn't be complex:

./configure --with-pgsql
make
sudo make install

Though I'd recommend uninstalling the brew version first, just to be sure.

[tscolari]

@pat thank you! That pointed me in the right direction.

Apparently in order to work with ThinkingSphinx, Sphinx must be compiled with mysql support, even when using it in postgresql. For some reason homebrew was compiling it --without-mysql. Using brew install sphinx --mysql worked for me.

[pat]

Ah, excellent! I'm a little surprised that's the case, but it's certainly very useful information. Tim, when you have a moment, it'd be great if you could confirm whether Tiago's solution worked for you as well.

[timriley]

That fixed it for me too!

[pat]

Brilliant!

I'll close this issue and make a note in the README.

[andreazaupa]

It work for me too! If you have both postgres and mysql installed, the sphinx recipe chose postgres.