Troubles connection to the MYSQL interface of Manticore Search

[mindreframer]

The MyXQL process is killed immediately, without any error logs or similar... It works with node.js or the Mysql terminal without issues. Not sure what the source for this problem is, it is really weird.

Docs:

• https://manual.manticoresearch.com/Starting_the_server/Docker#Configuring-Manticore-Search-with-Docker

Reproduction:

$ docker run -e EXTRA=1 --name manticore  -p 127.0.0.1:9306:9306  -d manticoresearch/manticore

Elixir example:

iex(4)> MyXQL.start_link(port: 9306, hostname: "127.0.0.1")
{:ok, #PID<0.547.0>}
** (EXIT from #PID<0.546.0>) shell process exited with reason: killed

Interactive Elixir (1.15.7) - press Ctrl+C to exit (type h() ENTER for help)
iex(5)>

Node.js example:

import mysql from 'mysql';
const connection = mysql.createConnection({
  host: 'localhost',
  port: 9306,
});

connection.connect();

connection.query("Drop TABLE if exists tbl", function (error, results, fields) {
  if (error) throw error;
  console.log(results);
})

connection.query("CREATE TABLE if not exists tbl(id bigint, content text)", function (error, results, fields) {
  if (error) throw error;
  console.log(results);
})

connection.query("insert into tbl(id, content) values (1, 'hello')", function (error, results, fields) {
  if (error) throw error;
  console.log(results);
})

connection.query("select * from tbl", function (error, results, fields) {
  if (error) throw error;
  console.log(results);
})

connection.end();

[greg-rychlewski]

If you check the docker logs for manticore can you see any errors? It might give some clues to what happened.

[mindreframer]

@greg-rychlewski Hi, I could not see any errors with default configuration.

BUT... I looked a bit in the docs, and here is the setup to quickly reproduce the issue:

• https://github.com/mindreframer/manticore-search-elixir-bug

Not sure what the actual problem is, but this line might be relevant:

DEBUG: conn 192.168.215.1:48352(14), sock=19: bailing on failed MySQL header, invalid size read 4(-1)

[mindreframer]

Maybe the issue is with Manticore (https://github.com/manticoresoftware/manticoresearch), I just find it curious that the Node.js Mysql driver gets a proper connection.

[josevalim]

Most drivers just write bindings to the C code. Elixir implements the MySQL protocol. Given Elixir can connect to a MySQL database, it is most likely to be a manticore issue indeed. You could use Wireshark and compare how they are communicating.

[greg-rychlewski]

When I tested locally, I was able to get some error messages. Manticore was complaining about some of the server flags being set.

Are you able to try with my branch here: https://github.com/greg-rychlewski/myxql/commit/0490a303c4f88325234f6e60ebfeded9f65b57ba

You can change your dep to {:myxql, git: "https://github.com/greg-rychlewski/myxql.git", branch: "test_manticore"}

Also it seems manticore does not like the binary protocol so I had to send my queries with query_type: :text option set, like this MyXQL.query(p, "select 1", [], query_type: :text)

[josevalim]

Yeah, the text messages will be limited and it means you cannot use Ecto.

[mindreframer]

@greg-rychlewski Thanks! I can execute queries and basic CRUD stuff seems to work on your branch!

@josevalim thanks for chiming in! For this particular use case I was not planning to use Ecto, but use the REST api for queries. The SQL interface is interesting, because it allows more control for DDL + admin queries (and most examples in the docs use SQL).

I'll open an issue on manticore repo and see what they suggest. @greg-rychlewski Is there a way to control the protocol client_capability_flags via configs? If not, I would stick to your fork for now.

[greg-rychlewski]

We don't have any general way to set the flags. Just a couple of config options that affect a couple of them.

@josevalim I could create a PR to expose these flags as an option if you think it's a good idea?

[josevalim]

Sounds good to me although @wojtekmach likely has opinions on the better API!

[mindreframer]

Sounds good to me although @wojtekmach likely has opinions on the better API!

Thanks, that would be awesome! I guess it could come in handy for similar Mysql-like databases, that don't quite follow the official MySQL protocol like https://vitess.io/ or https://github.com/dolthub/dolt.

[wojtekmach]

This might be a MyXQL bug. During handshake, the first packet is sent by the server and it contains its capabilities. The client should respond with a "handshake response" packet with its capabilities. I am not sure if it's well defined in the spec but maybe what is happening is MyXQL insists on sending capabilities that the server does not support (i.e. it did not send them in the initial handshake) and manticore understandably so rejects that.

[greg-rychlewski]

Oh so they are being compared but MyXQL will error and disconnect if they are not matched:

defp ensure_capabilities(capability_flags, names) do
    Enum.reduce_while(names, :ok, fn name, _acc ->
      if has_capability_flag?(capability_flags, name) do
        {:cont, :ok}
      else
        {:halt, {:error, {:server_missing_capability, name}}}
      end
    end)
  end

[greg-rychlewski]

Maybe the client flags should be filtered instead of erroring. It looks to me like that's what the spec is saying:

https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase.html

Capability Negotiation To permit an old client to connect to newer servers, the Protocol::Handshake contains

the MySQL Server version the server's Capabilities Flags

The client should only announce the capabilities in the Protocol::HandshakeResponse: that it has in common with the server.

They can agree on: use of status flags use of SQL states for error codes authentication methods SSL Support Compression

[wojtekmach]

@greg-rychlewski perfect, thank you for providing the reference.

@mindreframer if using Greg's branch fixes your issue, I believe you can close the upstream issue on manticore given the error was on our side.

[mindreframer]

@wojtekmach Sounds great, I'll close it! I just was not sure, what the actual root cause was.