an3l
commented
5 years ago After successfully installation [1] sudo apt-get install sphinxsearch and installed sphinx daemon (which can be start/stopped by sudo service sphinxsearch start []), we have following:
/etc/sphinxsearch$ ls
example.sql sphinx.conf.dist sphinx.conf.sample sphinx-min.conf.distAccording to the [2] we don't have sphinx.conf but rather sphinx.conf.dist it is ok.
According to the [2] sample data should be here mysql -u test </usr/share/doc/sphinxsearch/example-conf/example.sql (Debian/Ubuntu) but after testing think should be here mysql -u test < /etc/sphinxsearch/example.sql not ok ->change documentation. Yes there are documents and tags tables.
In order to enable sphinx SE we need to change config file (even with mysqld group it works, maybe searchd should be used?)
[mysqld]
# Sphinx enable
plugin_dir=/home/anel/workspace/server/storage/sphinx
plugin_load=ha_sphinxMariaDB [(none)]> show engines;
+------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| SPHINX | YES | Sphinx storage engine 2.2.6-release | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
+------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+To see the tables documents and tags
MariaDB [test]> show create table documents;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| documents | CREATE TABLE `documents` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`group_id` int(11) NOT NULL,
`group_id2` int(11) NOT NULL,
`date_added` datetime NOT NULL,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)MariaDB [test]> show create table tags;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tags | CREATE TABLE `tags` (
`docid` int(11) NOT NULL,
`tagid` int(11) NOT NULL,
UNIQUE KEY `docid` (`docid`,`tagid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)What is weird here is that SE is InnoDB.
Let's try to recreate example from [4]
MariaDB [test]> CREATE TABLE t1 ( id BIGINT UNSIGNED NOT NULL, weight INTEGER NOT NULL, query VARCHAR(3072) NOT NULL, group_id INTEGER, INDEX(query) ) ENGINE=SPHINX CONNECTION="sphinx://localhost:9312/test";
Query OK, 0 rows affected (0.001 sec)
MariaDB [test]> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL,
`weight` int(11) NOT NULL,
`query` varchar(3072) NOT NULL,
`group_id` int(11) DEFAULT NULL,
KEY `query` (`query`)
) ENGINE=SPHINX DEFAULT CHARSET=latin1 CONNECTION='sphinx://localhost:9312/test' |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)As we can see we used sphinx se and works.
Also altering connection works:
MariaDB [test]> ALTER TABLE t1 CONNECTION="sphinx://localhost:3306/test";
Query OK, 0 rows affected (0.001 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL,
`weight` int(11) NOT NULL,
`query` varchar(3072) NOT NULL,
`group_id` int(11) DEFAULT NULL,
KEY `query` (`query`)
) ENGINE=SPHINX DEFAULT CHARSET=latin1 CONNECTION='sphinx://localhost:3306/test' |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)What is not working is following example, what I need to find out-additional configuration is needed -todo!
MariaDB [test]> SELECT * FROM t1 WHERE query='test it;mode=any';
ERROR 1429 (HY000): Unable to connect to foreign data source: failed to resolve searchd host (name=localhost)Observation
- There is no
./mysql-test/suite/sphinxdirectory as stated in[7]
In order to test PR 532 https://github.com/MariaDB/server/pull/532 and duplicate PR 940 https://github.com/MariaDB/server/pull/940 try to do the same (contributor followed guidelines in
[4])Links and commits of interest [1 - mariadb-install] [2 - mariadb-configure] [3 - sphink-official] [4 - mariadb-about-basic-usage] [5 - mariadb-home] [6 - MDEV-disabled-test] [7 - Golubchik-patch]