Unknown MySQL error - Githubissues

sdbeck commented 6 years ago

I am developing a new web database app (Rails 5.2, mysql 5.6.38, libmysql - mysqlnd 5.0.12-dev, thinking-sphinx 4.0.0), and just deployed Sphinx and Thinking-Sphinx. All of the configurations work, the database is indexed with no problems.

But when I run a search (Grant.search 'agriculture'), I get the following error:

Sphinx Query (2.0ms) SELECT * FROM grant_core WHERE MATCH('agriculture') AND sphinx_deleted = 0 LIMIT 0, 20 Completed 500 Internal Server Error in 130ms (ActiveRecord: 93.9ms)

ThinkingSphinx::SphinxError (Unknown MySQL error):

mysql2 (0.5.1) lib/mysql2/client.rb:90:in `connect'

I interpret this as sphinx is not connecting to the MySQL server. My mysql server is running on a separate machine. I'm assuming this is simply a config problem, but I haven't been able to find it.

Any thoughts?

pat commented 6 years ago

I'm quite sure this error is actually unrelated to your own MySQL server - it's because Sphinx uses the MySQL protocol (hence Thinking Sphinx's reliance on the mysql2 gem), so it's actually Thinking Sphinx complaining that it can't connect to Sphinx.

Have you run rake ts:start to get the Sphinx daemon running?

aafaq-hassan-confiz commented 6 years ago

I am also facing this issue after upgrading mysql 5.7 to mysql 8.0.11. After installing mysql2 gem again, indexing is performed successfully, but upon searching, just this error 'ThinkingSphinx::SphinxError (Unknown MySQL error)' is being thrown without any backtrace.

pat commented 6 years ago

Can you confirm if the searchd process is running on your server?

Also, you may need to reinstall Sphinx if it was compiled on your machine and was bound to the previous installation of MySQL.

aafaq-hassan-confiz commented 6 years ago

searchd process is running, following is output of 'ps aux | grep searchd' command:

aafaqhassan 36787 0.1 0.1 4323404 10040 ?? S 4:31PM 0:00.42 /usr/local/bin/searchd --pidfile --config /Users/aafaqhassan/Projects/simplicant_rails_app/config/development.sphinx.conf

I had already reinstalled sphinx using following commands:

brew uninstall sphinx brew install sphinx --with-mysql

pat commented 6 years ago

Is there anything possibly related in the Sphinx log? Also, what's the output of rake ts:rebuild?

aafaq-hassan-confiz commented 6 years ago

Following is sphinx log upon performing searching:

Sphinx Query (4.5ms) SELECT * FROM job_core, job_delta WHERE MATCH('GDPR Compliance') AND company_id = 1150 AND is_deleted = 0 AND jobs_id IN (5153, 5198, 5255, 5257, 5301, 5330, 5331, 5332, 5333, 5334, 5335, 5336, 5350, 5356, 5363, 5367, 5376, 5154, 5381, 5388, 5389, 5390, 5393, 5395, 5399, 6228, 6231, 6385, 6387, 6390, 6392, 6393, 6394, 6395, 6396, 6474, 6483, 6484, 6485, 6486, 6489, 6490, 6491, 6492, 6493, 6494, 6508, 6512, 6515, 6521, 6522, 6523, 6526, 6529, 6533, 6534, 6536, 6542, 6543, 6544, 6552, 6556, 6578, 6579, 6580, 6583, 6584, 6586, 6590, 6592, 6577, 6591, 6585) AND sphinx_deleted = 0 ORDER BY jobs_status_id DESC, jobs_id DESC LIMIT 0, 50 Completed 500 Internal Server Error in 35ms (ActiveRecord: 4.4ms)

ThinkingSphinx::SphinxError (Unknown MySQL error)

Following is output of 'rbenv exec bundle exec bin/rails ts:rebuild'

using config file '/Users/aafaqhassan/Projects/simplicant_rails_app/config/development.sphinx.conf'... stop: successfully sent SIGTERM to pid 32704 Stopped searchd daemon (pid: 32704). Generating configuration to /Users/aafaqhassan/Projects/simplicant_rails_app/config/development.sphinx.conf Generating configuration to /Users/aafaqhassan/Projects/simplicant_rails_app/config/development.sphinx.conf Sphinx 2.2.11-id64-release (95ae9a6) Copyright (c) 2001-2016, Andrew Aksyonoff Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/Users/aafaqhassan/Projects/simplicant_rails_app/config/development.sphinx.conf'... indexing index 'app_core'... collected 189 docs, 0.3 MB sorted 0.0 Mhits, 100.0% done total 189 docs, 288313 bytes total 0.901 sec, 319702 bytes/sec, 209.57 docs/sec indexing index 'app_delta'... collected 0 docs, 0.0 MB total 0 docs, 0 bytes total 0.006 sec, 0 bytes/sec, 0.00 docs/sec indexing index 'company_core'... collected 1150 docs, 0.1 MB sorted 0.0 Mhits, 100.0% done total 1150 docs, 71046 bytes total 0.042 sec, 1663606 bytes/sec, 26928.30 docs/sec indexing index 'company_delta'... collected 0 docs, 0.0 MB total 0 docs, 0 bytes total 0.008 sec, 0 bytes/sec, 0.00 docs/sec indexing index 'job_core'... collected 4415 docs, 0.1 MB collected 2588 attr values sorted 0.0 Mvalues, 100.0% done sorted 0.0 Mhits, 100.0% done total 4415 docs, 104579 bytes total 0.151 sec, 688472 bytes/sec, 29065.17 docs/sec indexing index 'job_delta'... collected 0 docs, 0.0 MB collected 0 attr values sorted 0.0 Mvalues, 100.0% done total 0 docs, 0 bytes total 0.009 sec, 0 bytes/sec, 0.00 docs/sec indexing index 'lead_core'... collected 7 docs, 0.0 MB sorted 0.0 Mhits, 100.0% done total 7 docs, 309 bytes total 0.011 sec, 26734 bytes/sec, 605.64 docs/sec indexing index 'lead_delta'... collected 0 docs, 0.0 MB total 0 docs, 0 bytes total 0.009 sec, 0 bytes/sec, 0.00 docs/sec indexing index 'user_core'... collected 1502 docs, 0.1 MB sorted 0.0 Mhits, 100.0% done total 1502 docs, 52861 bytes total 0.041 sec, 1288978 bytes/sec, 36625.21 docs/sec indexing index 'user_delta'... collected 0 docs, 0.0 MB total 0 docs, 0 bytes total 0.009 sec, 0 bytes/sec, 0.00 docs/sec skipping non-plain index 'app'... skipping non-plain index 'company'... skipping non-plain index 'job'... skipping non-plain index 'lead'... skipping non-plain index 'user'... total 17616 reads, 0.036 sec, 0.1 kb/call avg, 0.0 msec/call avg total 97 writes, 0.007 sec, 24.0 kb/call avg, 0.0 msec/call avg Sphinx 2.2.11-id64-release (95ae9a6) Copyright (c) 2001-2016, Andrew Aksyonoff Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/Users/aafaqhassan/Projects/simplicant_rails_app/config/development.sphinx.conf'... listening on 127.0.0.1:3314 precaching index 'app_core' precaching index 'app_delta'
precaching index 'company_core'
precaching index 'company_delta'
precaching index 'job_core'
precaching index 'job_delta'
precaching index 'lead_core'
precaching index 'lead_delta'
precaching index 'user_core'
precaching index 'user_delta'
precached 10 indexes in 0.017 sec
Started searchd successfully (pid: 33567).

pat commented 6 years ago

Sorry, it's a bit confusing I know, but I'm wondering if there's anything in the sphinx logs - in a development environment, they're located in log/development.searchd.log.

Thanks for all of that other information - though frustratingly, there doesn't seem to be any clues in that rake output to the problem :|

aafaq-hassan-confiz commented 6 years ago

Log entries for today from log/development.searchd.log:

[Fri Jun 22 16:31:29.768 2018] [-1] caught SIGTERM, shutting down [Fri Jun 22 16:31:29.901 2018] [-1] shutdown complete [Fri Jun 22 16:31:29.904 2018] [-1] watchdog: main process 33567 forked ok [Fri Jun 22 16:31:29.904 2018] [-1] watchdog: main process 33567 exited cleanly (exit code 0), shutting down [Fri Jun 22 16:31:51.471 2018] [-1] watchdog: main process 36787 forked ok [Fri Jun 22 16:31:51.474 2018] [-1] listening on 127.0.0.1:3314 [Fri Jun 22 16:31:51.525 2018] [-1] binlog: replaying log /Users/aafaqhassan/Projects/simplicant_rails_app/tmp/binlog/development/binlog.001 [Fri Jun 22 16:31:51.525 2018] [-1] binlog: replay stats: 0 rows in 0 commits; 0 updates, 0 reconfigure; 0 indexes [Fri Jun 22 16:31:51.525 2018] [-1] binlog: finished replaying /Users/aafaqhassan/Projects/simplicant_rails_app/tmp/binlog/development/binlog.001; 0.0 MB in 0.000 sec [Fri Jun 22 16:31:51.525 2018] [-1] binlog: finished replaying total 1 in 0.000 sec [Fri Jun 22 16:31:51.526 2018] [-1] accepting connections

sdbeck commented 6 years ago

like aafaq-hassan, my searchd was running. I stopped & restarted the daemon with rake, and still have the same issue.

logs from development.searchd.log this morning: [Fri Jun 22 08:10:43.007 2018] [-1] caught SIGTERM, shutting down [Fri Jun 22 08:10:43.125 2018] [-1] shutdown complete [Fri Jun 22 08:10:43.126 2018] [-1] watchdog: main process 34675 forked ok [Fri Jun 22 08:10:43.126 2018] [-1] watchdog: main process 34675 exited cleanly (exit code 0), shutting down [Fri Jun 22 08:10:47.969 2018] [-1] watchdog: main process 37265 forked ok [Fri Jun 22 08:10:47.970 2018] [-1] listening on 127.0.0.1:9306 [Fri Jun 22 08:10:48.136 2018] [-1] binlog: replaying log /Users/stephenbeck/Sites/grants_db/tmp/binlog/development/binlog.001 [Fri Jun 22 08:10:48.136 2018] [-1] binlog: replay stats: 0 rows in 0 commits; 0 updates, 0 reconfigure; 0 indexes [Fri Jun 22 08:10:48.136 2018] [-1] binlog: finished replaying /Users/stephenbeck/Sites/grants_db/tmp/binlog/development/binlog.001; 0.0 MB in 0.000 sec [Fri Jun 22 08:10:48.136 2018] [-1] binlog: finished replaying total 1 in 0.000 sec [Fri Jun 22 08:10:48.143 2018] [-1] accepting connections

and here is the output from ts:rebuild:

using config file '/Users/stephenbeck/Sites/grants_db/config/development.sphinx.conf'... stop: successfully sent SIGTERM to pid 37265 Stopped searchd daemon (pid: 37265). Generating configuration to /Users/stephenbeck/Sites/grants_db/config/development.sphinx.conf Generating configuration to /Users/stephenbeck/Sites/grants_db/config/development.sphinx.conf Sphinx 2.2.11-id64-release (95ae9a6) Copyright (c) 2001-2016, Andrew Aksyonoff Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/Users/stephenbeck/Sites/grants_db/config/development.sphinx.conf'... indexing index 'grant_core'... collected 49452 docs, 65.3 MB sorted 9.7 Mhits, 100.0% done total 49452 docs, 65254649 bytes total 8.669 sec, 7526527 bytes/sec, 5703.83 docs/sec skipping non-plain index 'grant'... total 49461 reads, 0.084 sec, 0.6 kb/call avg, 0.0 msec/call avg total 75 writes, 0.072 sec, 818.5 kb/call avg, 0.9 msec/call avg Sphinx 2.2.11-id64-release (95ae9a6) Copyright (c) 2001-2016, Andrew Aksyonoff Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/Users/stephenbeck/Sites/grants_db/config/development.sphinx.conf'... listening on 127.0.0.1:9306 precaching index 'grant_core' precached 1 indexes in 0.007 sec
Started searchd successfully (pid: 37339).

Rob-Schwartz commented 6 years ago

Also getting this error. We're upgrading to Rails 5.2, from 5.0.
Updated the TS gem to 4.0.0 on Rails 5.0 first, got it all working there, mysql2 gem is 0.4.9., sphinx version 2.1.6 (b/c EngineYard only supports 2.1.6 so far). Worked local and deployed.

Upgrading to Rails 5.2 then gives this same error, same info in the log file.
Joiner gem was still 0.3.4 and ts:rebuild would not work. Then updated Joiner gem to 0.4.0 and ts:rebuild started working, I think, but then now get this same error reported here. So then updated Riddle from 2.3.0 to 2.3.1 and am still getting the error. What versions of these dependencies should I be on?
Here's what I've got right now: Builder - 3.2.3 Innertube - 1.1.0 Joiner - 0.4.0 Riddle - 2.3.1

Thanks - Rob

pat commented 6 years ago

Wish I could reproduce this issue!

@Rob-Schwartz: I think that Sphinx version isn't quite accurate, as there's no v3.1.6 yet (v3.0.x are the latest releases). Perhaps you meant v2.1.6?

What versions of Ruby is everyone running? And @aafaq-hassan-confiz: which version of Rails are you using?

aafaq-hassan-confiz commented 6 years ago

Sphinx 2.2.11 thinking sphinx 3.4.2 mysql2 0.5.1 MySQL Server 8.0.11 Rails 5.2 Ruby 2.5.1 macOS High Sierra 10.13.5

pat commented 6 years ago

Thanks for those details @aafaq-hassan-confiz. I've just tested locally with an almost identical setup (MySQL Server is 14.14/5.7.21), and search queries are working fine for me.

Can people share the contents of their config/thinking_sphinx.yml files? Also, are you seeing this problem happen on every search query, or only some? If not all, is it always the same queries?

pat commented 6 years ago

Also: not sure if it's related, but which databases are you all using? MySQL? PostgreSQL? @Rob-Schwartz: are you using real-time indices? (Seems others are)

aafaq-hassan-confiz commented 6 years ago

It is with MySQL and happening on every search query. @pat: please test with MySQL 8.0.11. config/thinking_sphinx.yml file content is below:

development: pid_file: '/Users/aafaqhassan/Projects/simplicant_rails_app/tmp/pids/searchd_development.pid' indices_location: '/Users/aafaqhassan/Projects/simplicant_rails_app/db/sphinx/development/' configuration_file: '/Users/aafaqhassan/Projects/simplicant_rails_app/config/development.sphinx.conf' mysql41: 3314 bin_path: '/usr/local/bin' charsettable: '0..9, A..Z->a..z, , a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F' blend_chars: '.' mem_limit: 1024M max_filter_values: 10000 html_strip: true min_word_len: 3 min_prefix_len: 3 delayed_job_queue: 'sphinx_dj'

production: pid_file: /Users/aafaqhassan/Projects/simplicant_rails_app/tmp/pids/searchd_development.pid indices_location: /Users/aafaqhassan/Projects/simplicant_rails_app/db/sphinx/development/ configuration_file: '/Users/aafaqhassan/Projects/simplicant_rails_app/config/development.sphinx.conf' mysql41: 3314 bin_path: '/usr/local/bin' charsettable: '0..9, A..Z->a..z, , a..z, U+410..U+42F->U+430..U+44F, U+430..U+44F' blend_chars: '.' mem_limit: 1024M max_filter_values: 10000 html_strip: true min_word_len: 3 min_prefix_len: 3 delayed_job_queue: 'sphinx_dj'

Rob-Schwartz commented 6 years ago

More info: sphinx 2.1.6 (you are correct - not 3.1.6) thinking-sphinx (4.0.0) mysql2 gem: 0.4.10 & 0.5.1 (tried both) MySQL - Server version: 5.6.38 Homebrew Rails 5.2.0 ruby 2.3.0p0 (2015-12-25 revision 53290) [x86_64-darwin15] MacOS High Sierra 10.13.5

config/thinking_sphinx.yml for local machine:

DEFAULTS: &DEFAULTS
  #blank address should allow remote connections
  address:
  #port: 9312
  mysql41: 9312
  mem_limit: 128M
  morphology: stem_en
  min_prefix_len: 3
  enable_star: true
  configuration_file: <%= Rails.root %>/config/environments/<%= Rails.env %>.sphinx.conf
  searchd_log_fle: <%= Rails.root %>/log/searchd.log
  query_log_fle: <%= Rails.root %>/log/searchd.query.log
  indices_location: <%= Rails.root%>/db/sphinx
  utf8: false
  delayed_job_queue: sphinx_delta

development:
  <<: *DEFAULTS

test:
  <<: *DEFAULTS

production:
  <<: *DEFAULTS

Using MySQL. No real time indices.
We have four indices setup and all of them give the same error, for any/all queries - even a simple .search('rob').

ExternalData.search('rob')
  Sphinx Query (3.9ms)  SELECT * FROM `external_data_core`, `external_data_delta` WHERE MATCH('rob') AND `sphinx_deleted` = 0 LIMIT 0, 20

ThinkingSphinx::SphinxError: Unknown MySQL error
  thinking-sphinx (4.0.0) lib/thinking_sphinx/errors.rb:26:in `new_from_mysql'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/connection/mri.rb:18:in `rescue in client'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/connection/mri.rb:16:in `client'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/connection/client.rb:28:in `close!'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/connection.rb:28:in `block in pool'
  innertube (1.1.0) lib/innertube.rb:88:in `delete_element'
  innertube (1.1.0) lib/innertube.rb:140:in `rescue in take'
  innertube (1.1.0) lib/innertube.rb:148:in `take'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/connection.rb:36:in `take'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/search/batch_inquirer.rb:18:in `results'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/middlewares/inquirer.rb:11:in `block in call'
  activesupport (5.2.0) lib/active_support/notifications.rb:168:in `block in instrument'
  activesupport (5.2.0) lib/active_support/notifications/instrumenter.rb:23:in `instrument'
  activesupport (5.2.0) lib/active_support/notifications.rb:168:in `instrument'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/logger.rb:5:in `log'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/middlewares/inquirer.rb:10:in `call'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/middlewares/geographer.rb:13:in `call'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/middlewares/sphinxql.rb:16:in `call'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/middlewares/valid_options.rb:9:in `call'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/middlewares/stale_id_filter.rb:12:in `call'
  middleware (0.1.0) lib/middleware/runner.rb:31:in `call'
  middleware (0.1.0) lib/middleware/builder.rb:102:in `call'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/search.rb:94:in `populate'
  thinking-sphinx (4.0.0) lib/thinking_sphinx/search.rb:144:in `method_missing'
...

Rob-Schwartz commented 6 years ago

I reverted the mysql2 gem back down to 0.4.9 and it started working again! I then put it up to 0.4.10, 0.5.0, or 0.5.1 and it re-breaks. I'll continue to research this but have a viable solution for Rails 5.2.

aafaq-hassan-confiz commented 6 years ago

Following line in "lib/thinking_sphinx/connection/mri.rb" is throwing exception:

Mysql2::Client.new({:host=>"127.0.0.1", :port=>3314, :reconnect=>true, :flags => Mysql2::Client::MULTI_STATEMENTS, :connect_timeout => 5})

Above code was working with mysql 5.7 and returning Mysql2::Client instance, but with mysql 8.0.11, it is just says ''Mysql2::Error (Unknown MySQL error)'' error.

pat commented 6 years ago

@aafaq-hassan-confiz, @sdbeck: I'm getting the same error now when I have the latest MySQL available in Homebrew (8.0.11) installed. I'll debug further when I have the time (though I'm going to be mostly offline for the rest of the week). Downgrading MySQL (and then re-installing Sphinx and your mysql2 gem) should get things working again - but certainly, I'd like to get it working with the latest MySQL too. My current guess is that the mysql2 library is detecting the MySQL database version and behaving in a way that Sphinx can't handle (i.e. the SET @@SESSION... query that shows up in my Rails log/console).

@Rob-Schwartz: Great to hear you've got things working for you - I'm guessing the mysql2 v0.5.x gem is not compatible with Sphinx 2.1.x - so if you're upgrading one, you'll need to upgrade the other.

pat commented 6 years ago

Ah, that SET @@SESSION... line is to the MySQL database, not to Sphinx.

Further debugging hasn't gotten me far, though I've managed to find a stack trace, and the error's coming from within the mysql2 C bindings - it's not from the Ruby code in either mysql2 or Thinking Sphinx.

Right now, I think MySQL v8 is not compatible with Sphinx - and indeed, because Sphinx binds against MySQL when installing, the only way around this is to use an older version of MySQL. Hopefully future releases of MySQL or Sphinx fix this (I've tried Sphinx v3.0.3, which is the latest, and that doesn't work either).

sdbeck commented 6 years ago

Thanks for digging deep into this. I'll stick with kludging Ransack for my immediate needs. Will wait for a Sphinx update.

jnweaver commented 6 years ago

In case it's helpful to anyone:

I have a separate mysql5.7 install. Running thinking-sphinx against mysql2 versions installed with only that mysql5.7 on my laptop worked fine.

In setting up Thinking Sphinx though, I installed sphinx via brew brew install --with-mysql which of course installed mysql 8. Installing mysql2 gems after this caused Thinking Sphinx to fail with the Unknown Mysql Error because those gems were getting built against the Mysql 8 client lib /usr/local/opt/mysql/lib/libmysqlclient.21.dylib FWIW, trying to install the mysql2 gem while setting --with-mysql-config or --with-mysql-dir and pointing at my Mysql 5 install did not help — the mysql2 gem always built against the MySQL 8 client library. Uninstalling MySQL 8 from my laptop and re-installing the mysql2 gem got thinking sphinx working again.

sodabrew commented 6 years ago

I wonder if the issue is this change in default password algorithm in the MySQL 8 C library?

https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

pat commented 6 years ago

@sodabrew it could be, I guess? Though Sphinx doesn't pay any attention to usernames or passwords, and TS generally doesn't pass that information through - hasn't been a problem in the past. While debugging I have tried adding usernames and passwords just in case, but it didn't make any difference.

Some other debugging notes:

I also tried adding a database name to the connection details (Sphinx has no concept of separate databases within a connection/daemon). Didn't make a difference.
The error number being returned by MySQL is 2000 - which seems to be a generic client error, hence a lack of information from mysql2.
Over the past year or so some people unsatisfied with Sphinx have forked it into a new project Manticore, which is under more active development (though Sphinx is also being actively developed). The standard Sphinx features that TS expects remain the same (indeed, the binary names are the same as well), so TS works fine with Manticore. The latest Manticore release, though, has the same issue, so that's sadly not a workaround.

manticoresearch commented 6 years ago

Hi

It's gonna be fixed in Manticore within few weeks. Here's the issue https://github.com/manticoresoftware/manticoresearch/issues/89

pat commented 6 years ago

Further updates to this:

It's now fixed in Manticore's master branch (so I'd expect the release after 2.7.1 to include it). I've tested this locally.
I've logged an issue on the Sphinx bug tracker, so hopefully they'll get a fix in place soon too.

al3rez commented 6 years ago

@jnweaver how did you fix this? I have installed mysql@5.7 using brew and built mysql2 gem with --with-mysql-dir option but I get _mysql_server_init not found when I try to build Sphinx index.

al3rez commented 6 years ago

here's my error:

 ThinkingSphinx::SphinxError (Unknown MySQL error - SELECT *, weight() + weight_boost + (20 - (stem_length * 2)) + (tld_boost_en ) as wFROM `domain_core`, `domain_delta` WHERE MATCH('@stem *ishu*') AND `status` = 0 AND `is_deleted` = 0 AND `sphinx_deleted` = 0 ORDER BY `w` desc LIMIT 0, 100 OPTIONranker=bm25; SHOW META):

mysql version: 8.0.11 mysql2 gem version: 0.5.2 rails version: rails 5.0.7

@pat

pat commented 6 years ago

@azbshiri if you're back on MySQL 5.7, you'll likely need to compile Sphinx from source rather than Homebrew (unless there's a way to tell Homebrew to use a specific version of MySQL as the dependency?)

This is how I compile (using OpenSSL installed by Homebrew) once the Sphinx code is downloaded:

# configure
export LDFLAGS="-L/usr/local/opt/openssl/lib"
export CPPFLAGS="-I/usr/local/opt/openssl/include"
./configure --with-mysql --with-pgsql --enable-id64
# compile
make -j4
# install
make install

al3rez commented 6 years ago

@pat we're not back on mysql@5.7 I just notice mysql 8.0.x have some major changes so I tried downgrade to see if it's works or not. is that resolves the Unknown MySQL error issue?

pat commented 6 years ago

Yes, downgrading to MySQL 5.7 will fix that issue.

al3rez commented 6 years ago

by the source code you meant this right? https://github.com/sphinxsearch/sphinx @pat

pat commented 6 years ago

There's also specific releases at http://sphinxsearch.com/downloads/archive/ - I'd recommend 2.2.11.

manticoresearch commented 6 years ago

It's gonna be fixed in Manticore within few weeks. Done in release 2.7.2 (https://manticoresearch.com/downloads/)

pat commented 6 years ago

Sphinx v3.1.1 has been released, and I can confirm that it's fine with MySQL v8 on my machine - if others want to verify, that'd be great! :)

pat commented 5 years ago

I'm going to close this issue, given the underlying problem is fixed in both Sphinx and Manticore. If anyone finds things aren't working for them though, please do comment further.

nir0 commented 5 years ago

got this error as well. it might be related to my upgrade from macbook to macbook air with data transferred

pat commented 5 years ago

@nir0 did upgrading Sphinx or Manticore or downgrading MySQL fix the problem for you?

nir0 commented 5 years ago

@pat thinking-sphinx 4.2.0

➜  core git:(staging) ✗ searchd --version
Sphinx 2.3.3-id64-dev (409f2c2)
➜  core git:(staging) ✗ mysql --version
mysql  Ver 8.0.16 for osx10.14 on x86_64 (Homebrew)
➜  core git:(staging) ✗ mysqld --version
/usr/local/Cellar/mysql/8.0.16/bin/mysqld  Ver 8.0.16 for osx10.14 on x86_64 (Homebrew)

ThinkingSphinx::SphinxError: Unknown MySQL error when running rake ts:rebuild

pat commented 5 years ago

I think it's only Sphinx v3.1.1 that supports MySQL v8, so you'll want to grab the latest binaries for that instead: http://sphinxsearch.com/downloads/current/

nir0 commented 5 years ago

thanks i didn't spot that this development build got version 2 instead of 3. i use brew to install everything, that's odd that i've got no option to install sphinx v3

pat commented 5 years ago

Homebrew doesn't support Sphinx v3 (perhaps because the source for the v3 releases isn't public, and it's unclear if it will ever be), so brew isn't going to be helpful in this case 😔

adriannuta commented 5 years ago

For Manticore Search there is now a tap https://github.com/manticoresoftware/homebrew-tap and a PR to official brew was submitted (please note mysql@5.7 was used at compiling).

modullar commented 5 years ago

The disaster is, that brew install sphinx installs mysql8 automatically. so MySQL 8 is not uninstallable unless sphinx gets uninstalled. Is it possible to configure sphinx with MySQL 5.7 upon installing it?

ivinco-sergey commented 5 years ago

Sphinx fork's brew https://github.com/manticoresoftware/homebrew-tap depends on mysql@5.7, not just mysql (which defaults to 8 in homebrew):

➜  ~ brew info manticoresearch
manticoresoftware/tap/manticoresearch: stable 3.1.2 (bottled), HEAD
Open source text search engine
https://www.manticoresearch.com
Conflicts with:
  sphinx (because manticore,sphinx install the same binaries.)
/usr/local/Cellar/manticoresearch/3.1.2 (70 files, 30.3MB) *
  Built from source on 2019-08-23 at 13:38:38
From: https://github.com/manticoresoftware/homebrew-tap/blob/master/Formula/manticoresearch.rb
==> Dependencies
Build: cmake ✔, icu4c ✔, libpq ✔, mysql@5.7 ✘, unixodbc ✔
Required: openssl ✔

Not speaking about that:

brew install sphinx installs ancient Sphinx 2.2.11 with tons of bugs and security issues
Manticore Search supports mysql 8 out of the box.

pat commented 5 years ago

I've just submitted a PR to Homebrew to lock the MySQL dependency in the Sphinx formula to 5.7.

However, I believe Homebrew has removed the ability to pass through the flag for PostgreSQL support (thus, it's not useful if you're using SQL-backed indices on a PostgreSQL database) … so, rather than relying on Sphinx via Homebrew, I'd either be installing Sphinx 3.1.1 manually, or using Manticore instead (as noted by others here). Manticore has all of Sphinx's features (plus others), and Thinking Sphinx works fine with it (it's part of the CI setup).

sodabrew commented 5 years ago

I would be glad to make Sphinx and/or Manticore part of the CI matrix for this gem. Would someone knowledgeable on these tools provide a PR for unit tests? I imagine some of the tests are not going to be relevant, but I don't know which!

pat commented 5 years ago

@sodabrew the offer's greatly appreciated, but I think my wording wasn't quite clear in the last message - Manticore is in TS' CI matrix: https://github.com/pat/thinking-sphinx/blob/develop/.travis.yml#L30-L35 😄

manticoresearch commented 4 years ago

For Manticore Search there is now a tap https://github.com/manticoresoftware/homebrew-tap and a PR to official brew was submitted (please note mysql@5.7 was used at compiling).

You can now install Manticore Search w/o the tap, just: brew install manticoresearch

pat / thinking-sphinx

Unknown MySQL error #1101