Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

Mysql MultiStatement support not working under (Mountain) Lion #291

Closed
barttenbrinke opened this Issue · 20 comments

4 participants

@barttenbrinke

Hi,
Im using the mysql2 gem to query sphinx, like so:

sphinx = Mysql2::Client.new(:host => '127.0.0.1', :port => 9306, :database => 'noop', :username => 'noop', :flags => Mysql2::Client::MULTI_STATEMENTS)
sphinx_ql_query = "SELECT * FROM content_stemmer WHERE MATCH('test'); SHOW META;"
results = sphinx.query(sphinx_ql_query).collect{|x| x}
p sphinx.next_result 
false
 => false 

If I connect use mysql-cli, this query works just fine and I do get two results back. When I run this with an identical setup on my old laptop (Snow leopard), it DO get two results back.

I am testing this using the current master in my gemfile. Is there any way I can drill down to the core issue of this problem?

@brianmario
Owner

Want to give the latest beta gem a try? I just pushed a fix for a bug with the flags stuff.

@barttenbrinke

Tested with: 0.3.12b4 - Problem remains... I think its Mountain Lion specific.

@sodabrew
Collaborator

What versions of MySQL are at play?

@barttenbrinke

mysql Ver 14.14 Distrib 5.5.25a, for osx10.8 (i386) using EditLine wrapper
Mysql Percona.

@sodabrew
Collaborator

Ok, so that's your MySQL client version, and you're connecting to Sphinx using MySQL procotol? http://sphinxsearch.com/docs/1.10/sphinxql.html

Versions of Sphinx, and MySQL client that works on OS X Lion?

@barttenbrinke

Wow I feel really dumb. I was debugging a Mysql deadlock and somehow just copy pasted my mysql version string when the email came in.

I am connecting to Sphinx via the MySQL protocol. If I do this through mysql client:

    mysql root --port 9306 --protocol tcp --host localhost
    SELECT * FROM content_stemmer WHERE MATCH('test'); SHOW META;

I get two results sets. If I do the same thing through a rails console, I only get the first resultset.
Sphinx version is the one shipped via homebrew (Sphinx 2.0.4-release (r3135))

I was running the identical setup on Snow Leopard and that all worked, so my guess is that it has something to do with lion, but I could be wrong here. Would it be an idea if I built a repo for you that reproduces the problem?

@barttenbrinke

Steps to reproduce are here: https://github.com/barttenbrinke/mysql2_sphinx_issue
Repo includes an example Sphinx database with about 10 items in it.

@sodabrew
Collaborator
@barttenbrinke

Lion ships with libmysqlclient.18.dylib, while Show Leopard ships with libmysqlclient.16.dylib.

@sodabrew
Collaborator

Ping, any chance this is resolved with newer MySQL, mysql2 gem, or newer Sphinx in recent months?

@sodabrew
Collaborator

Close for lack of updates.

@sodabrew sodabrew closed this
@luckyjazzbo

The issue still remains.. any updates on this?

@sodabrew
Collaborator

I closed the ticket for lack of updates. If you're still seeing this problem, please post enough information to help triage and debug the issue!

@luckyjazzbo

looks like @barttenbrinke posted some info, even created small project to help reproduce the issue. What else can I do?

@luckyjazzbo

I'm using mac os mavericks (10.9.1), mysql2 0.3.14 and having the same issue.

2.0.0p247 :035 > client = Mysql2::Client.new(port: Sphinx::Config.port, host:Sphinx::Config.host, flags: Mysql2::Client::MULTI_STATEMENTS, username: "root", password: "", database: "dummy")
 => #<Mysql2::Client:0x007fe06a204638 @read_timeout=nil, @query_options={:as=>:hash, :async=>false, :cast_booleans=>false, :symbolize_keys=>false, :database_timezone=>:local, :application_timezone=>nil, :cache_rows=>true, :connect_flags=>2147525125, :cast=>true, :default_file=>nil, :default_group=>nil, :port=>9306, :host=>"127.0.0.1", :flags=>65536, :username=>"root", :password=>"", :database=>"dummy"}> 

2.0.0p247 :035 > client.query("select * from p_items; select * from p_item_candidates")
 => #<Mysql2::Result:0x007fe06a215ff0 @query_options={:as=>:hash, :async=>false, :cast_booleans=>false, :symbolize_keys=>false, :database_timezone=>:local, :application_timezone=>nil, :cache_rows=>true, :connect_flags=>2147525125, :cast=>true, :default_file=>nil, :default_group=>nil, :port=>9306, :host=>"127.0.0.1", :flags=>65536, :username=>"root", :password=>"", :database=>"dummy"}> 

2.0.0p247 :035 > client.more_results?
 => false 
@sodabrew sodabrew reopened this
@sodabrew
Collaborator

Using this sphinx: Server version: 2.1.4-release (rel21-r4421)
With this MySQL lib: mysql Ver 14.14 Distrib 5.5.37, for osx10.9 (i386) using readline 5.1
And with mysql2 from the current git master d0a5199
With the system provided Ruby: ruby 2.0.0p451 (2014-02-24 revision 45167) [universal.x86_64-darwin13]
On OS X 10.9.3 (build 13D45a public beta):

$ mysql -u foobar --port 9306 --protocol tcp --host localhost
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2.1.4-release (rel21-r4421)

mysql> SELECT * FROM content_stemmer WHERE MATCH('a'); SHOW META;
+--------+
| id     |
+--------+
| 920301 |
| 930301 |
+--------+
2 rows in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| total         | 2     |
| total_found   | 2     |
| time          | 0.001 |
| keyword[0]    | a     |
| docs[0]       | 2     |
| hits[0]       | 2     |
+---------------+-------+
6 rows in set (0.00 sec)

And in irb:

bundle exec irb
irb(main):001:0> require 'mysql2'
=> true
irb(main):002:0> sphinx = Mysql2::Client.new(:host => '127.0.0.1', :port => 9306, :database => 'noop', :username => 'noop', :flags => Mysql2::Client::MULTI_STATEMENTS)
=> #<Mysql2::Client:0x007fc5d3239358 @read_timeout=nil, @query_options={:as=>:hash, :async=>false, :cast_booleans=>false, :symbolize_keys=>false, :database_timezone=>:local, :application_timezone=>nil, :cache_rows=>true, :connect_flags=>2147525125, :cast=>true, :default_file=>nil, :default_group=>nil, :host=>"127.0.0.1", :port=>9306, :database=>"noop", :username=>"noop", :flags=>65536}>
irb(main):003:0> sphinx_ql_query = "SELECT * FROM content_stemmer WHERE MATCH('a'); SHOW META;"
=> "SELECT * FROM content_stemmer WHERE MATCH('a'); SHOW META;"
irb(main):004:0> results = sphinx.query(sphinx_ql_query).collect{|x| x}
=> [{"id"=>920301}, {"id"=>930301}]
irb(main):005:0> sphinx.next_result
=> true
irb(main):006:0> sphinx.store_result.collect{|x| x}
=> [{"Variable_name"=>"total", "Value"=>"2"}, {"Variable_name"=>"total_found", "Value"=>"2"}, {"Variable_name"=>"time", "Value"=>"0.000"}, {"Variable_name"=>"keyword[0]", "Value"=>"a"}, {"Variable_name"=>"docs[0]", "Value"=>"2"}, {"Variable_name"=>"hits[0]", "Value"=>"2"}]

So this may be a resolved issue now. It would be a nice-to-have to identify the affected versions, e.g. Sphinx 2.0.x, earlier Ruby, earlier MySQL, earlier mysql2 gem, etc.

Edit: tested with mysql2 versions 0.3.12, 13, 14, 15, master and same results. My guess is this was either a mysql client library issue, or a Sphinx issue. I don't have Sphinx 2.0.x handy to test with, but that would be my next target to version bisect.

@barttenbrinke

I'm quite sure it was a mysql dylib issue, which was shipped with osx? Or is it built by home-brew?

@sodabrew
Collaborator

OS X 10.9.3 beta. I'm using MacPorts. I don't see a system-provided libmysqlclient in /Library or /usr. Sorry it took me so long to follow your repro steps! Are you still seeing the problem, btw?

@sodabrew
Collaborator

Is this still an open problem?

@sodabrew
Collaborator

Closing for lack of updates. I hope that everything has been resolved in current release versions of Sphinx, OS X, etc.!

@sodabrew sodabrew closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.