Hi, I’m trying to access manticore using sql server linked server.
This is what I have done so far:
Installed manticore 3.0.2-190531
Successfully using manticore with mysql command line and HeidiSQL
Set mysql_version_string=5.0.37 in order to connect using odbc datasource
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.
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=;’
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;
I suppose that’s why I don’t see databases in SSMS.
However I managed to execute simple query using:
select id from [linked_server_name]...MyIndex
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.
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