ThinkingSphinx::SphinxError: Unknown MySQL error

[airled]

Hi. I have some issues with a new project. Specs:

• Ubuntu: 19.10 Linux 5.3.0-19-generic
• Postgresql: psql (12.0 (Ubuntu 12.0-2.pgdg19.10+1), server 11.5 (Ubuntu 11.5-3.pgdg19.10+1))
• Sphinx: 2.2.11-id64-release
• ThinkingSphinx: 4.4.1
• Mysql2: 0.5.2
• Rails: 6.0.0
• Ruby 2.6.5

Thinking sphinx config:

default: &default
  morphology:  stem_enru, Soundex, Metaphone
  min_infix_len: 1
  index_exact_words: 1
  expand_keywords: 1
  html_strip: 1

  indexed_models:
    - Section

development:
  <<: *default
  mysql41: 9312
  mem_limit: 200M
  pid_file: tmp/pids/searchd.pid

Sphinx config:

indexer
{
  mem_limit = 200M
}

searchd
{
  listen = 127.0.0.1:9312:mysql41
  log = /home/air/qq2dev/log/development.searchd.log
  query_log = /home/air/qq2dev/log/development.searchd.query.log
  pid_file = tmp/pids/searchd.pid
  workers = threads
  binlog_path = /home/air/qq2dev/tmp/binlog/development
}

index section_core
{
  type = rt
  path = /home/air/qq2dev/db/sphinx/development/section_core
  docinfo = extern
  morphology = stem_enru, Soundex, Metaphone
  min_infix_len = 1
  expand_keywords = 1
  html_strip = 1
  index_exact_words = 1
  rt_field = slug
  rt_attr_uint = sphinx_deleted
  rt_attr_bigint = sphinx_internal_id
  rt_attr_string = sphinx_internal_class
}

index section
{
  type = distributed
  local = section_core
}

Index file indices/section_index.rb:

ThinkingSphinx::Index.define :section, with: :real_time do
  indexes slug
end

Section model:

class Section < ApplicationRecord
  ...
  after_save ThinkingSphinx::RealTime.callback_for(:section)
end

No exceptions on running be rake ts:rebuild if my database is empty. If I run Section.search('test') I get:

irb(main):001:0> Section.search('test')
  Sphinx Query (6.7ms)  SELECT * FROM `section_core` WHERE MATCH('test') AND `sphinx_deleted` = 0 LIMIT 0, 20
Traceback (most recent call last):
ThinkingSphinx::SphinxError (Unknown MySQL error)

If I try to create new Section entity:

irb(main):002:0> Section.create!(name: 'test', slug: 'test', subtitle: 'test')
   (0.6ms)  BEGIN
  Section Exists? (0.9ms)  SELECT 1 AS one FROM "sections" WHERE "sections"."slug" = $1 LIMIT $2  [["slug", "test"], ["LIMIT", 1]]
  Section Create (1.0ms)  INSERT INTO "sections" ("slug", "created_at", "updated_at") VALUES ($1, $2, $3) RETURNING "id"  [["slug", "test"], ["created_at", "2019-11-11 10:35:06.788649"], ["updated_at", "2019-11-11 10:35:06.788649"]]
  Sphinx Query (6.2ms)  REPLACE INTO section_core (id, `slug`, `sphinx_internal_id`, `sphinx_internal_class`, `sphinx_deleted`) VALUES (10, 'test', 5, 'Section', 0)
   (0.6ms)  ROLLBACK
Traceback (most recent call last):
        2: from (irb):2
        1: from (irb):2:in `rescue in irb_binding'
ThinkingSphinx::SphinxError (Unknown MySQL error)

Extended traceback:

ThinkingSphinx::SphinxError: Unknown MySQL error

Caused by:
Mysql2::Error: Unknown MySQL error

Caused by:
Innertube::Pool::BadResource: Innertube::Pool::BadResource

Caused by:
ThinkingSphinx::QueryExecutionError: Unknown MySQL error - REPLACE INTO section_core (id, `slug`, `sphinx_internal_id`, `sphinx_internal_class`, `sphinx_deleted`) VALUES (2, 'quests', 1, 'Section', 0)

Caused by:
ThinkingSphinx::SphinxError: Unknown MySQL error

Caused by:
Mysql2::Error: Unknown MySQL error

I tried to reinstall postgresql and sphinxsearch packets but no luck. What can it be?

[airled]

New information: I tried to run this project on other machine with Ubuntu 16.04 and Postgresql psql (12.0 (Ubuntu 12.0-2.pgdg16.04+1), server 11.5 (Ubuntu 11.5-3.pgdg16.04+1)) with sphinx Sphinx 2.2.9-id64-release (rel22-r5006) and building and updating indices works absolutely fine there.

[atomical]

I experienced this same problem recently. Rebuilding Ruby and reinstalling all gems worked for me.

[airled]

@atomical Thank you for your answer. I reinstalled ruby with gems but with no luck. Same error.

[pat]

Can you confirm which version of the MySQL library files you have installed? If it's v8, you'll need to downgrade to v5.7 or earlier, because Sphinx 2.2.11 doesn't understand MySQL v8.

[airled]

Can you confirm which version of the MySQL library files you have installed? If it's v8, you'll need to downgrade to v5.7 or earlier, because Sphinx 2.2.11 doesn't understand MySQL v8.

You are right. I have this libs:

libmysqlclient-dev/eoan,now 8.0.17-0ubuntu2 amd64 [installed]
libmysqlclient21/eoan,now 8.0.17-0ubuntu2 amd64 [installed,automatic]
mysql-common/eoan,now 5.8+1.0.5ubuntu2 all [installed,automatic]

Thank you. I close the issue.