Can't access manticore using mssql linked server


#1

Hi, I’m trying to access manticore using sql server linked server.
This is what I have done so far:

  1. Installed manticore 3.0.2-190531

  2. Successfully using manticore with mysql command line and HeidiSQL

  3. Set mysql_version_string=5.0.37 in order to connect using odbc datasource

  4. Created 64bit ODBC system datasource using both MySQL ODBC 8.0 ANSI and MySQL ODBC 8.0 Unicode driver. I used 127.0.0.1 and port 9306. Connection test is successful.

  5. Created linked server using
    EXEC master.dbo.sp_addlinkedserver
    @server = N’ManticoreUnicodeSearch’,
    @srvproduct=N’manticore’,
    @provider=N’MSDASQL’,
    @datasrc=N’manticoreUnicode’,
    @provstr=N’Driver={MySQL ODBC 8.0 Unicode Driver};
    Server=127.0.0.1;Port=9306,charset=UTF8;User=;Password=;’

  6. Adjusted provider parameters as suggested here: http://sql-articles.com/articles/dba/creating-linked-server-to-mysql-from-sql-server/

  7. This is what I get:
    image

It seems to me that something isn’t working since I don’t get any data in catalogs.
Any ideas what is wrong?

Thanks.


#2

could you look at your searchd.log there might be already some connection error logged?

I also might suggest to use regular mysql client connect to daemon and switch verbose2 log level then connect with your linked server and post all mysql commands from searchd.log here last command at log might cause linked server to break connection

Here is commands to enable verbosity level2 at Manticore daemon

$>mysql -h0 -P 9306
mysql>SET GLOBAL query_log_format=sphinxql;
mysql>SET GLOBAL LOG_LEVEL=debugv;

#3

I tried your suggestions, but I can’t get any info in searchd.log.

mysql> SET GLOBAL query_log_format=sphinxql;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL LOG_LEVEL=debugv;
Query OK, 0 rows affected (0.00 sec)

This is searchd.log content:

[Mon Jul 15 09:44:53.175 2019] [9552] listening on all interfaces, port=9312
[Mon Jul 15 09:44:53.176 2019] [9552] listening on all interfaces, port=9306
[Mon Jul 15 09:44:53.187 2019] [9552] WARNING: failed to get MAC address, using random number 1445749771
[Mon Jul 15 09:44:53.190 2019] [9552] prereading 2 indexes
[Mon Jul 15 09:44:53.295 2019] [9552] prereaded 2 indexes in 0.105 sec
[Mon Jul 15 09:44:53.295 2019] [9552] accepting connections

Connecting and querying from mysql client still works. Also, I use windows 10 and both manticore and mysql are running on single machine.

And one more information. I can’t see databases when I create ODBC datasource but test connection is successful:

image

I don’t know if that’s ok.


#4

I’ve made some progress with this:

  1. When I expand linked server node in SSMS I get this error in query.log:

/* Mon Jul 15 15:48:04.342 2019 conn 2 */ select database() # error=Sphinx expr: syntax error, unexpected '(', expecting $end near '()'

I suppose that’s why I don’t see databases in SSMS.

  1. However I managed to execute simple query using:

select id from [linked_server_name]...MyIndex

  1. Unfortunately when I tried to retrieve string attribute from db I get this error:

    Msg 7347, Level 16, State 1, Line 3
    OLE DB provider ‘MSDASQL’ for linked server ‘MANTICORE’ returned data that does not match expected data length for column ‘[MANTICORE]…[MyIndex].my_string_attr’. The (maximum) expected data length is 170, while the returned data length is 28.

Any ideas?


#5

seems select databases syntax is not supported by searchd
it might be better to create ticket at github for this statement to be informed when it be fixed


#6

Comment about “select database()” error:

Solution for “data that does not match expected data length” error: