MySQL protocol

Manticore Search implements an SQL interface using MySQL protocol, which allows any MySQL client, library or connector to be used for connecting to Manticore Search and work with it as if it would be MySQL server, not Manticore.

However the SQL dialect is different. It implements only a subset of SQL commands or functions available in MySQL. In addition, there are clauses and functions that are specific to Manticore Search. The most eloquent example is the MATCH() clause which allows setting the full-text search.

Manticore Search doesn’t support server-side prepared statements. Client-side prepared statements can be used with Manticore. It must be noted that Manticore implements the multi value (MVA) data type for which there is no equivalent in MySQL or libraries implementing prepared statements. In these cases, the MVA values will need to be crafted in the raw query.

Some MySQL clients/connectors demand values for user/password and/or database name. Since Manticore Search does not have the concept of database and there is no user access control yet implemented, these can be set arbitrarily as Manticore will simply ignore the values.

Configuration

The default port for the SQL interface is 9306 and it’s enabled by default.

In the searchd section of the configuration file the MySQL port can be defined by listen directive like this:

  1. searchd {
  2. ...
  3.    listen = 127.0.0.1:9306:mysql
  4. ...
  5. }

Because Manticore doesn’t have yet user authentication implemented make sure the MySQL port can’t be accessed by anyone outside your network.

VIP connection

A separate MySQL port can be used to perform ‘VIP’ connections. A connection to this port bypasses the thread pool and always forcibly creates a new dedicated thread. That’s useful for managing in case of a severe overload when the server would either stall or not let you connect via a regular port.

  1. searchd {
  2. ...
  3.    listen = 127.0.0.1:9306:mysql
  4.    listen = 127.0.0.1:9307:mysql_vip
  5. ...
  6. }

Connecting via standard MySQL client

The easiest way to connect to Manticore is by using a standard MySQL client:

  1. mysql -P9306 -h0

Secured MySQL connection

The MySQL protocol supports SSL encryption. The secured connections can be made on the same mysql listening port.

Compressed MySQL connection

Compression can be used with MySQL Connections and available to clients by default. The client just need to specify the connection to use compression.

An example with the MySQL client:

  1. mysql -P9306 -h0 -C

Compression can be used in both secured and non-secured connections.

Notes on MySQL connectors

The official MySQL connectors can be used to connect to Manticore Search, however they might require certain settings passed in the DSN string as the connector can try running certain SQL commands not implemented yet in Manticore.

JDBC Connector 6.x and above require Manticore Search 2.8.2 or greater and the DSN string should contain the following options:

  1. jdbc:mysql://IP:PORT/DB/?characterEncoding=utf8&maxAllowedPacket=512000&serverTimezone=XXX

By default Manticore Search will report it’s own version to the connector, however this may cause some troubles. To overcome that mysql_version_string directive in searchd section of the configuration should be set to a version lower than 5.1.1:

  1. searchd {
  2. ...
  3.    mysql_version_string = 5.0.37
  4. ...
  5. }

.NET MySQL connector uses connection pools by default. To correctly get the statistics of SHOW META, queries along with SHOW META command should be sent as a single multistatement (SELECT ...;SHOW META). If pooling is enabled option Allow Batch=True is required to be added to the connection string to allow multistatements:

  1. Server=127.0.0.1;Port=9306;Database=somevalue;Uid=somevalue;Pwd=;Allow Batch=True;

Notes on ODBC connectivity

Manticore can be accessed using ODBC. It’s recommended to set charset=UTF8 in the ODBC string. Some ODBC drivers will not like the reported version by the Manticore server as they will see it as a very old MySQL server. This can be overridden with mysql_version_string option.

Comment syntax

Manticore SQL over MySQL supports C-style comment syntax. Everything from an opening /* sequence to a closing */ sequence is ignored. Comments can span multiple lines, can not nest, and should not get logged. MySQL specific /*! ... */ comments are also currently ignored. (As the comments support was rather added for better compatibility with mysqldump produced dumps, rather than improving general query interoperability between Manticore and MySQL.)

  1. SELECT /*! SQL_CALC_FOUND_ROWS */ col1 FROM table1 WHERE ...

HTTP

You can connect to Manticore Search over HTTP/HTTPS.

Configuration

By default Manticore listens for HTTP, HTTPS and binary requests on ports 9308 and 9312.

In section “searchd” of your configuration file the HTTP port can be defined with directive listen like this:

Both lines are valid and equal by meaning (except for the port number), they both define listeners that will serve all api/http/https protocols. There are no special requirements and any HTTP client can be used to connect to Manticore.

  • HTTP

HTTP

  1. searchd {
  2. ...
  3.    listen = 127.0.0.1:9308
  4.    listen = 127.0.0.1:9312:http
  5. ...
  6. }

All HTTP endpoints respond with application/json content type. Most endpoints use JSON payload for requests, however there are some exceptions that use NDJSON or simple URL encoded payload.

There is no user authentication implemented at the moment, so make sure the HTTP interface is not reachable by anyone outside your network. Since Manticore acts like any other web server, you can use a reverse proxy like Nginx to add HTTP authentication or caching.

The HTTP protocol also supports SSL encryption: If you specify :https instead of :http only secured connections will be accepted. Otherwise in case of no valid key/certificate provided, but the client trying to connect via https - the connection will be dropped. If you make not HTTPS, but an HTTP request to 9443 it will respond with HTTP code 400.

  • HTTPS

HTTPS

  1. searchd {
  2. ...
  3.    listen = 127.0.0.1:9308
  4.    listen = 127.0.0.1:9443:https
  5. ...
  6. }

VIP connection

Separate HTTP interface can be used to perform ‘VIP’ connections. A connection in this case bypasses a thread pool and always forcibly creates a new dedicated thread. That’s useful for managing Manticore Search in case of a severe overload when the server would either stall or not let you connect via a regular port otherwise.

Read more about listen in this section.

  • VIP

VIP

  1. searchd {
  2. ...
  3.    listen = 127.0.0.1:9308
  4.    listen = 127.0.0.1:9318:_vip
  5. ...
  6. }

SQL over HTTP

Endpoints /sql and /cli allow running SQL queries via HTTP.

  • /sql endpoint accepts only SELECT statements and returns the response in HTTP JSON format. The query parameter should be URL-encoded.
  • The /sql?mode=raw endpoint accepts any SQL query and returns the response in raw format, similar to what you would receive via mysql. The query parameter should also be URL-encoded.
  • The /cli endpoint accepts any SQL query and returns the response in raw format, similar to what you would receive via mysql. Unlike the /sql and /sql?mode=raw endpoints, the query parameter should not be URL-encoded. This endpoint is intended for manual actions using a browser or command line HTTP clients such as curl. It is not recommended to use the /cli endpoint in scripts.

/sql

/sql accepts an SQL SELECT query via HTTP JSON interface.

Query payload must be URL encoded, otherwise query statements with = (filtering or setting options) will result in an error.

It returns a JSON response which contains hits information and execution time. The response has the same format as json/search endpoint. Note, that /sql endpoint supports only single search requests. If you are looking for processing a multi-query see below.

  • HTTP

HTTP

  1. POST /sql -d "query=select%20id%2Csubject%2Cauthor_id%20%20from%20forum%20where%20match%28%27%40subject%20php%20manticore%27%29%20group%20by%20author_id%20order%20by%20id%20desc%20limit%200%2C5"

Response

  1. {
  2.   "took": 0,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 2,
  6.     "total_relation": "eq",
  7.     "hits": [
  8.       {
  9.         "_id": "2",
  10.         "_score": 2356,
  11.         "_source": {
  12.           "subject": "php manticore",
  13.           "author_id": 12
  14.         }
  15.       },
  16.       {
  17.         "_id": "1",
  18.         "_score": 2356,
  19.         "_source": {
  20.           "subject": "php manticore",
  21.           "author_id": 11
  22.         }
  23.       }
  24.     ]
  25.   }
  26. }

/sql?mode=raw

/sql endpoint also has a special mode “raw”, which allows to send any valid sphinxql queries including multi-queries. The returned value is a json array of one or more result sets.

  • HTTP

HTTP

  1. POST /sql?mode=raw -d "query=desc%20test"

Response

  1. [
  2.   {
  3.     "columns": [
  4.       {
  5.         "Field": {
  6.           "type": "string"
  7.         }
  8.       },
  9.       {
  10.         "Type": {
  11.           "type": "string"
  12.         }
  13.       },
  14.       {
  15.         "Properties": {
  16.           "type": "string"
  17.         }
  18.       }
  19.     ],
  20.     "data": [
  21.       {
  22.         "Field": "id",
  23.         "Type": "bigint",
  24.         "Properties": ""
  25.       },
  26.       {
  27.         "Field": "title",
  28.         "Type": "text",
  29.         "Properties": "indexed"
  30.       },
  31.       {
  32.         "Field": "gid",
  33.         "Type": "uint",
  34.         "Properties": ""
  35.       },
  36.       {
  37.         "Field": "title",
  38.         "Type": "string",
  39.         "Properties": ""
  40.       },
  41.       {
  42.         "Field": "j",
  43.         "Type": "json",
  44.         "Properties": ""
  45.       },
  46.       {
  47.         "Field": "new1",
  48.         "Type": "uint",
  49.         "Properties": ""
  50.       }
  51.     ],
  52.     "total": 6,
  53.     "error": "",
  54.     "warning": ""
  55.   }
  56. ]

/cli

While the /sql endpoint is useful to control Manticore programmatically from your application, there’s also endpoint /cli which makes it eaiser to maintain a Manticore instance via curl or your browser manually. It accepts POST and GET HTTP methods. Everything after /cli? is taken by Manticore as is even if you don’t escape it manually via curl or let the browser encode it automaticaly. The + sign is not decoded to space as well, eliminating the necessity of encoding it. The response format is the same as in the /sql?mode=raw.

  • HTTP
  • Browser

HTTP Browser

  1. POST /cli -d "select id,1+2 as a, packedfactors() from test where match('tes*') option ranker=expr('1')"

using /cli in browser

Response

  1. [
  2.   {
  3.     "columns": [
  4.       {
  5.         "id": {
  6.           "type": "long long"
  7.         }
  8.       },
  9.       {
  10.         "a": {
  11.           "type": "long"
  12.         }
  13.       },
  14.       {
  15.         "packedfactors()": {
  16.           "type": "string"
  17.         }
  18.       }
  19.     ],
  20.     "data": [
  21.       {
  22.         "id": 1,
  23.         "a": 3,
  24.         "packedfactors()": "bm25=616, bm25a=0.69689077, field_mask=1, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.25595802, min_idf=0.25595802, max_idf=0.25595802, sum_idf=0.25595802, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.25595802, atc=0.000000), word0=(tf=1, idf=0.25595802)"
  25.       },
  26.       {
  27.         "id": 2,
  28.         "a": 3,
  29.         "packedfactors()": "bm25=616, bm25a=0.69689077, field_mask=1, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.25595802, min_idf=0.25595802, max_idf=0.25595802, sum_idf=0.25595802, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.25595802, atc=0.000000), word0=(tf=1, idf=0.25595802)"
  30.       },
  31.       {
  32.         "id": 8,
  33.         "a": 3,
  34.         "packedfactors()": "bm25=616, bm25a=0.69689077, field_mask=1, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.25595802, min_idf=0.25595802, max_idf=0.25595802, sum_idf=0.25595802, min_hit_pos=2, min_best_span_pos=2, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.25595802, atc=0.000000), word0=(tf=1, idf=0.25595802)"
  35.       }
  36.     ],
  37.     "total": 3,
  38.     "error": "",
  39.     "warning": ""
  40.   }
  41. ]

Keep-alive

HTTP keep-alive is also supported, which makes working via the HTTP JSON interface stateful as long as the client supports keep-alive too. For example, using the new /cli endpoint you can call SHOW META after SELECT and it will work the same way it works via mysql.

HTTP

You can connect to Manticore Search over HTTP/HTTPS.

Configuration

By default Manticore listens for HTTP, HTTPS and binary requests on ports 9308 and 9312.

In section “searchd” of your configuration file the HTTP port can be defined with directive listen like this:

Both lines are valid and equal by meaning (except for the port number), they both define listeners that will serve all api/http/https protocols. There are no special requirements and any HTTP client can be used to connect to Manticore.

  • HTTP

HTTP

  1. searchd {
  2. ...
  3.    listen = 127.0.0.1:9308
  4.    listen = 127.0.0.1:9312:http
  5. ...
  6. }

All HTTP endpoints respond with application/json content type. Most endpoints use JSON payload for requests, however there are some exceptions that use NDJSON or simple URL encoded payload.

There is no user authentication implemented at the moment, so make sure the HTTP interface is not reachable by anyone outside your network. Since Manticore acts like any other web server, you can use a reverse proxy like Nginx to add HTTP authentication or caching.

The HTTP protocol also supports SSL encryption: If you specify :https instead of :http only secured connections will be accepted. Otherwise in case of no valid key/certificate provided, but the client trying to connect via https - the connection will be dropped. If you make not HTTPS, but an HTTP request to 9443 it will respond with HTTP code 400.

  • HTTPS

HTTPS

  1. searchd {
  2. ...
  3.    listen = 127.0.0.1:9308
  4.    listen = 127.0.0.1:9443:https
  5. ...
  6. }

VIP connection

Separate HTTP interface can be used to perform ‘VIP’ connections. A connection in this case bypasses a thread pool and always forcibly creates a new dedicated thread. That’s useful for managing Manticore Search in case of a severe overload when the server would either stall or not let you connect via a regular port otherwise.

Read more about listen in this section.

  • VIP

VIP

  1. searchd {
  2. ...
  3.    listen = 127.0.0.1:9308
  4.    listen = 127.0.0.1:9318:_vip
  5. ...
  6. }

SQL over HTTP

Endpoints /sql and /cli allow running SQL queries via HTTP.

  • /sql endpoint accepts only SELECT statements and returns the response in HTTP JSON format. The query parameter should be URL-encoded.
  • The /sql?mode=raw endpoint accepts any SQL query and returns the response in raw format, similar to what you would receive via mysql. The query parameter should also be URL-encoded.
  • The /cli endpoint accepts any SQL query and returns the response in raw format, similar to what you would receive via mysql. Unlike the /sql and /sql?mode=raw endpoints, the query parameter should not be URL-encoded. This endpoint is intended for manual actions using a browser or command line HTTP clients such as curl. It is not recommended to use the /cli endpoint in scripts.

/sql

/sql accepts an SQL SELECT query via HTTP JSON interface.

Query payload must be URL encoded, otherwise query statements with = (filtering or setting options) will result in an error.

It returns a JSON response which contains hits information and execution time. The response has the same format as json/search endpoint. Note, that /sql endpoint supports only single search requests. If you are looking for processing a multi-query see below.

  • HTTP

HTTP

  1. POST /sql -d "query=select%20id%2Csubject%2Cauthor_id%20%20from%20forum%20where%20match%28%27%40subject%20php%20manticore%27%29%20group%20by%20author_id%20order%20by%20id%20desc%20limit%200%2C5"

Response

  1. {
  2.   "took": 0,
  3.   "timed_out": false,
  4.   "hits": {
  5.     "total": 2,
  6.     "total_relation": "eq",
  7.     "hits": [
  8.       {
  9.         "_id": "2",
  10.         "_score": 2356,
  11.         "_source": {
  12.           "subject": "php manticore",
  13.           "author_id": 12
  14.         }
  15.       },
  16.       {
  17.         "_id": "1",
  18.         "_score": 2356,
  19.         "_source": {
  20.           "subject": "php manticore",
  21.           "author_id": 11
  22.         }
  23.       }
  24.     ]
  25.   }
  26. }

/sql?mode=raw

/sql endpoint also has a special mode “raw”, which allows to send any valid sphinxql queries including multi-queries. The returned value is a json array of one or more result sets.

  • HTTP

HTTP

  1. POST /sql?mode=raw -d "query=desc%20test"

Response

  1. [
  2.   {
  3.     "columns": [
  4.       {
  5.         "Field": {
  6.           "type": "string"
  7.         }
  8.       },
  9.       {
  10.         "Type": {
  11.           "type": "string"
  12.         }
  13.       },
  14.       {
  15.         "Properties": {
  16.           "type": "string"
  17.         }
  18.       }
  19.     ],
  20.     "data": [
  21.       {
  22.         "Field": "id",
  23.         "Type": "bigint",
  24.         "Properties": ""
  25.       },
  26.       {
  27.         "Field": "title",
  28.         "Type": "text",
  29.         "Properties": "indexed"
  30.       },
  31.       {
  32.         "Field": "gid",
  33.         "Type": "uint",
  34.         "Properties": ""
  35.       },
  36.       {
  37.         "Field": "title",
  38.         "Type": "string",
  39.         "Properties": ""
  40.       },
  41.       {
  42.         "Field": "j",
  43.         "Type": "json",
  44.         "Properties": ""
  45.       },
  46.       {
  47.         "Field": "new1",
  48.         "Type": "uint",
  49.         "Properties": ""
  50.       }
  51.     ],
  52.     "total": 6,
  53.     "error": "",
  54.     "warning": ""
  55.   }
  56. ]

/cli

While the /sql endpoint is useful to control Manticore programmatically from your application, there’s also endpoint /cli which makes it eaiser to maintain a Manticore instance via curl or your browser manually. It accepts POST and GET HTTP methods. Everything after /cli? is taken by Manticore as is even if you don’t escape it manually via curl or let the browser encode it automaticaly. The + sign is not decoded to space as well, eliminating the necessity of encoding it. The response format is the same as in the /sql?mode=raw.

  • HTTP
  • Browser

HTTP Browser

  1. POST /cli -d "select id,1+2 as a, packedfactors() from test where match('tes*') option ranker=expr('1')"

using /cli in browser

Response

  1. [
  2.   {
  3.     "columns": [
  4.       {
  5.         "id": {
  6.           "type": "long long"
  7.         }
  8.       },
  9.       {
  10.         "a": {
  11.           "type": "long"
  12.         }
  13.       },
  14.       {
  15.         "packedfactors()": {
  16.           "type": "string"
  17.         }
  18.       }
  19.     ],
  20.     "data": [
  21.       {
  22.         "id": 1,
  23.         "a": 3,
  24.         "packedfactors()": "bm25=616, bm25a=0.69689077, field_mask=1, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.25595802, min_idf=0.25595802, max_idf=0.25595802, sum_idf=0.25595802, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.25595802, atc=0.000000), word0=(tf=1, idf=0.25595802)"
  25.       },
  26.       {
  27.         "id": 2,
  28.         "a": 3,
  29.         "packedfactors()": "bm25=616, bm25a=0.69689077, field_mask=1, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.25595802, min_idf=0.25595802, max_idf=0.25595802, sum_idf=0.25595802, min_hit_pos=1, min_best_span_pos=1, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.25595802, atc=0.000000), word0=(tf=1, idf=0.25595802)"
  30.       },
  31.       {
  32.         "id": 8,
  33.         "a": 3,
  34.         "packedfactors()": "bm25=616, bm25a=0.69689077, field_mask=1, doc_word_count=1, field0=(lcs=1, hit_count=1, word_count=1, tf_idf=0.25595802, min_idf=0.25595802, max_idf=0.25595802, sum_idf=0.25595802, min_hit_pos=2, min_best_span_pos=2, exact_hit=0, max_window_hits=1, min_gaps=0, exact_order=1, lccs=1, wlccs=0.25595802, atc=0.000000), word0=(tf=1, idf=0.25595802)"
  35.       }
  36.     ],
  37.     "total": 3,
  38.     "error": "",
  39.     "warning": ""
  40.   }
  41. ]

Keep-alive

HTTP keep-alive is also supported, which makes working via the HTTP JSON interface stateful as long as the client supports keep-alive too. For example, using the new /cli endpoint you can call SHOW META after SELECT and it will work the same way it works via mysql.