How to make MySQL / SELECT interface work for anything else but the cli tool?

General Discussions - English
1

I’m testing manticoresearch if it fits our needs but already stumbling on the “easy” parts. The SQL (mysql) part is not very intuitive:

GUI tool (datagrip)

  • can connect but not run any commands, always some error

mysql shell

  • always syntaxerror

low-code tools / pipelining

  • syntax errors

it really seems the cli tool is the only thing that actually works an one can use the SQL interface. But that isn’t a very useful tool in context of actual usage in a real application or for testing and then especially bulk insertion of data in way not needing extensive coding.

it seems in the end one is forced to use the official manticoresearch client anyway making the whole sql interface a bit pointless?

or what am I missing here?

2

all mysql libraries work fine with ManticoreSearch , however different tool could issue some specific queries and as SphinxQL is not fully compatible MySQL interface, could fail with error.

You could try to set mysql_version_string = 5.0.37 at the searchd section of the config as described at our documentation to prevent send of specific queries.

3

mysql shell

  • always syntaxerror

Default Linux/Windows mysql/mariadb clients should work fine. If they don’t - please provide more details:

  • client version
  • your query
  • the output

low-code tools / pipelining

what tools do you mean?

it seems in the end one is forced to use the official manticoresearch client anyway making the whole sql interface a bit pointless?

No, most likely you just did smth wrong. Most Manticore users use Linux mysql client and it works fine with Manticore.

4

Hi Sergey,

i think the shell (which is different from the commandline client) does some “clever” stuff that breaks things just like the “GUI tools” do.
(with shell I meant this one: https://dev.mysql.com/doc/mysql-shell/8.0/en/).

The cli client does work fine so we can forget about the shell, i don’t really mind that one.

Data Grip:

Create table statements fail. I tried with examples from the documentation:

CREATE TABLE forum(title text, content text, author_id int, forum_id int, post_date timestamp);

The error is to me meaningless: "Found input string: “”

When I create the table with mysql cli client and then “Show Tables;” works in data grip.

low-code tool I mean KNIME which has a mysql connector but here I suspect they are also doing “clever” things in the background which really only works with mysql. the idea is to have an easy way to populate a db with test data.

5

(with shell I meant this one: https://dev.mysql.com/doc/mysql-shell/8.0/en/).

OK, I see.

Data Grip:

Here’s an issue about it SELECT DATABASE(); · Issue #1063 · manticoresoftware/manticoresearch · GitHub

the idea is to have an easy way to populate a db with test data.

What can be easier than mysql -P9306 -h0 < data.sql ?

6

I’ve never used mysqlsh before. I can confirm it’s not working with Manticore:

image
image1832×238 94.6 KB

The queries it attempts to make that Manticore doesn’t understand are:

select schema() # error=Sphinx expr: syntax error, unexpected '(', expecting $end near ')'

SET SESSION session_track_system_variables = '<empty>, sql_mode'; # error=sphinxql: syntax error, unexpected identifier, expecting '=' near 'session_track_system_variables = '<empty>, sql_mode';'

select concat(@@version, ' ', @@version_comment) # error=sphinxql: syntax error, unexpected @@sysvar near '@@version, ' ', @@version_comment)'

mysql-shell support can be added using Manticore Buddy like we recently added mysqldump support.

7

mysqlsh is not for sending queries to your mysql databases. The mysqlsh program is for configuring the server mostly for use in cluster configurations. You want mysql workbench or the mysql command line that Sergey already provided. mysql -u username -p