diff options
Diffstat (limited to 'storage/sphinx/mysql-test')
| -rw-r--r-- | storage/sphinx/mysql-test/sphinx/my.cnf | 29 | ||||
| -rw-r--r-- | storage/sphinx/mysql-test/sphinx/sphinx.result | 97 | ||||
| -rw-r--r-- | storage/sphinx/mysql-test/sphinx/sphinx.test | 56 | ||||
| -rw-r--r-- | storage/sphinx/mysql-test/sphinx/suite.opt | 1 | ||||
| -rw-r--r-- | storage/sphinx/mysql-test/sphinx/suite.pm | 153 | ||||
| -rw-r--r-- | storage/sphinx/mysql-test/sphinx/testdata.xml | 44 | ||||
| -rw-r--r-- | storage/sphinx/mysql-test/sphinx/union-5539.result | 16 | ||||
| -rw-r--r-- | storage/sphinx/mysql-test/sphinx/union-5539.test | 11 |
8 files changed, 407 insertions, 0 deletions
diff --git a/storage/sphinx/mysql-test/sphinx/my.cnf b/storage/sphinx/mysql-test/sphinx/my.cnf new file mode 100644 index 00000000..f60380b7 --- /dev/null +++ b/storage/sphinx/mysql-test/sphinx/my.cnf @@ -0,0 +1,29 @@ +!include include/default_my.cnf + +[source src1] +type = xmlpipe2 +xmlpipe_command = cat @ENV.MTR_SUITE_DIR/testdata.xml + +[index test1] +source = src1 +docinfo = extern +charset_type = utf-8 +path = @ENV.MYSQLTEST_VARDIR/searchd/test1 + +[indexer] +mem_limit = 32M + +[searchd] +read_timeout = 5 +max_children = 30 +seamless_rotate = 1 +preopen_indexes = 0 +unlink_old = 1 +log = @ENV.MYSQLTEST_VARDIR/searchd/sphinx-searchd.log +query_log = @ENV.MYSQLTEST_VARDIR/searchd/sphinx-query.log +#log-error = @ENV.MYSQLTEST_VARDIR/searchd/sphinx.log +pid_file = @ENV.MYSQLTEST_VARDIR/run/searchd.pid +listen = @ENV.SPHINXSEARCH_PORT + +[ENV] +SPHINXSEARCH_PORT = @OPT.port diff --git a/storage/sphinx/mysql-test/sphinx/sphinx.result b/storage/sphinx/mysql-test/sphinx/sphinx.result new file mode 100644 index 00000000..c462d0cc --- /dev/null +++ b/storage/sphinx/mysql-test/sphinx/sphinx.result @@ -0,0 +1,97 @@ +create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:SPHINXSEARCH_PORT/*"; +select * from ts where q='test'; +id w q +1 2 test +2 2 test +4 1 test +5 1 test +drop table ts; +create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:SPHINXSEARCH_PORT/*"; +select * from ts where q='test;filter=gid,1;mode=extended'; +id w q +1 2379 test;filter=gid,1;mode=extended +2 2379 test;filter=gid,1;mode=extended +5 1412 test;filter=gid,1;mode=extended +select * from ts where q='test|one;mode=extended'; +id w q +1 3579 test|one;mode=extended +2 2439 test|one;mode=extended +4 1456 test|one;mode=extended +5 1456 test|one;mode=extended +select * from ts where q='test;offset=1;limit=1'; +id w q +2 2 test;offset=1;limit=1 +alter table ts connection="sphinx://127.0.0.1:SPHINXSEARCH_PORT/test1"; +select id, w from ts where q='one'; +id w +1 2 +drop table ts; +create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, gid int not null, _sph_count int not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:SPHINXSEARCH_PORT/test1"; +select * from ts; +id w q gid _sph_count +select * from ts where q=''; +id w q gid _sph_count +1 1 1 0 +2 1 1 0 +3 1 2 0 +4 1 2 0 +5 1 1 0 +select * from ts where q=';groupby=attr:gid'; +id w q gid _sph_count +3 1 ;groupby=attr:gid 2 2 +1 1 ;groupby=attr:gid 1 3 +explain select * from ts where q=';groupby=attr:gid'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE ts ref q q 257 const 3 Using where with pushed condition +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='index_condition_pushdown=off'; +explain select * from ts where q=';groupby=attr:gid'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE ts ref q q 257 const 3 Using where with pushed condition +SET optimizer_switch=@save_optimizer_switch; +drop table ts; +show status like "sphinx_total%"; +Variable_name Value +Sphinx_total 2 +Sphinx_total_found 2 +show status like "sphinx_word%"; +Variable_name Value +Sphinx_word_count 0 +Sphinx_words +create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:SPHINXSEARCH_PORT/*"; +select * from ts where q=';filter=meta.foo_count,100'; +id w q +1 1 ;filter=meta.foo_count,100 +select * from ts where q='test;filter=meta.sub.int,7'; +id w q +5 1 test;filter=meta.sub.int,7 +select * from ts where q=';filter=meta.sub.list[0],4'; +id w q +select * from ts where q=';filter=meta.sub.list[1],4'; +id w q +5 1 ;filter=meta.sub.list[1],4 +select * from ts where q='test;range=meta.foo_count,100,500'; +id w q +1 2 test;range=meta.foo_count,100,500 +5 1 test;range=meta.foo_count,100,500 +drop table ts; +# +# MDEV-19205: Sphinx unable to connect using a host name +# +create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://localhost:SPHINXSEARCH_PORT/*"; +select * from ts where q=';filter=meta.foo_count,100'; +id w q +1 1 ;filter=meta.foo_count,100 +select * from ts where q='test;filter=meta.sub.int,7'; +id w q +5 1 test;filter=meta.sub.int,7 +select * from ts where q=';filter=meta.sub.list[0],4'; +id w q +select * from ts where q=';filter=meta.sub.list[1],4'; +id w q +5 1 ;filter=meta.sub.list[1],4 +select * from ts where q='test;range=meta.foo_count,100,500'; +id w q +1 2 test;range=meta.foo_count,100,500 +5 1 test;range=meta.foo_count,100,500 +drop table ts; diff --git a/storage/sphinx/mysql-test/sphinx/sphinx.test b/storage/sphinx/mysql-test/sphinx/sphinx.test new file mode 100644 index 00000000..b733a3fc --- /dev/null +++ b/storage/sphinx/mysql-test/sphinx/sphinx.test @@ -0,0 +1,56 @@ + +--replace_result $SPHINXSEARCH_PORT SPHINXSEARCH_PORT +eval create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:$SPHINXSEARCH_PORT/*"; +select * from ts where q='test'; +drop table ts; + +--replace_result $SPHINXSEARCH_PORT SPHINXSEARCH_PORT +eval create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:$SPHINXSEARCH_PORT/*"; +select * from ts where q='test;filter=gid,1;mode=extended'; +select * from ts where q='test|one;mode=extended'; +select * from ts where q='test;offset=1;limit=1'; +--replace_result $SPHINXSEARCH_PORT SPHINXSEARCH_PORT +eval alter table ts connection="sphinx://127.0.0.1:$SPHINXSEARCH_PORT/test1"; +select id, w from ts where q='one'; +drop table ts; + +--replace_result $SPHINXSEARCH_PORT SPHINXSEARCH_PORT +eval create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, gid int not null, _sph_count int not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:$SPHINXSEARCH_PORT/test1"; +select * from ts; +select * from ts where q=''; +select * from ts where q=';groupby=attr:gid'; +explain select * from ts where q=';groupby=attr:gid'; +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='index_condition_pushdown=off'; +explain select * from ts where q=';groupby=attr:gid'; +SET optimizer_switch=@save_optimizer_switch; +drop table ts; + +# +# Don't show sphinx error as this is different between sphinx versions +# show status like "sphinx_error%"; + +show status like "sphinx_total%"; +show status like "sphinx_word%"; + +--replace_result $SPHINXSEARCH_PORT SPHINXSEARCH_PORT +eval create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://127.0.0.1:$SPHINXSEARCH_PORT/*"; +select * from ts where q=';filter=meta.foo_count,100'; +select * from ts where q='test;filter=meta.sub.int,7'; +select * from ts where q=';filter=meta.sub.list[0],4'; +select * from ts where q=';filter=meta.sub.list[1],4'; +select * from ts where q='test;range=meta.foo_count,100,500'; +drop table ts; + +--echo # +--echo # MDEV-19205: Sphinx unable to connect using a host name +--echo # + +--replace_result $SPHINXSEARCH_PORT SPHINXSEARCH_PORT +eval create table ts ( id bigint unsigned not null, w int not null, q varchar(255) not null, index(q) ) engine=sphinx connection="sphinx://localhost:$SPHINXSEARCH_PORT/*"; +select * from ts where q=';filter=meta.foo_count,100'; +select * from ts where q='test;filter=meta.sub.int,7'; +select * from ts where q=';filter=meta.sub.list[0],4'; +select * from ts where q=';filter=meta.sub.list[1],4'; +select * from ts where q='test;range=meta.foo_count,100,500'; +drop table ts; diff --git a/storage/sphinx/mysql-test/sphinx/suite.opt b/storage/sphinx/mysql-test/sphinx/suite.opt new file mode 100644 index 00000000..7b425b04 --- /dev/null +++ b/storage/sphinx/mysql-test/sphinx/suite.opt @@ -0,0 +1 @@ +--plugin-load-add=$HA_SPHINX_SO --sphinx diff --git a/storage/sphinx/mysql-test/sphinx/suite.pm b/storage/sphinx/mysql-test/sphinx/suite.pm new file mode 100644 index 00000000..e44a8e62 --- /dev/null +++ b/storage/sphinx/mysql-test/sphinx/suite.pm @@ -0,0 +1,153 @@ +package My::Suite::Sphinx; + +use My::SafeProcess; +use My::File::Path; +use mtr_report; + +@ISA = qw(My::Suite); + +############# initialization ###################### +sub locate_sphinx_binary { + my ($name)= @_; + my $res; + my @list= map "$_/$name", split /:/, $ENV{PATH}; + my $env_override= $ENV{"SPHINXSEARCH_\U$name"}; + @list= ($env_override) if $env_override; + for (@list) { return $_ if -x $_; } +} + +# Look for Sphinx binaries +my $exe_sphinx_indexer = &locate_sphinx_binary('indexer'); +return "'indexer' binary not found" unless $exe_sphinx_indexer; + +my $exe_sphinx_searchd = &locate_sphinx_binary('searchd'); +return "'searchd' binary not found" unless $exe_sphinx_searchd; + +my $sphinx_config= "$::opt_vardir/my_sphinx.conf"; + +# Check for Sphinx engine + +return "SphinxSE not found" unless $ENV{HA_SPHINX_SO} or $::mysqld_variables{'sphinx'} eq "ON"; + +{ + local $_ = `"$exe_sphinx_searchd" --help`; + mtr_verbose("tool: $exe_sphinx_searchd\n$_"); + my $ver = sprintf "%04d.%04d.%04d", (/([0-9]+)\.([0-9]+)(?:\.([0-9]+))?/); + return "Sphinx 2.0.4 or later is needed (found $ver) " unless $ver ge '0002.0000.0004'; +} + +############# action methods ###################### + +sub write_sphinx_conf { + my ($config) = @_; # My::Config + my $res; + + foreach my $group ($config->groups()) { + my $name= $group->{name}; + # Only the ones relevant to Sphinx search. + next unless ($name eq 'indexer' or $name eq 'searchd' or + $name =~ /^(source|index) \w+$/); + $res .= "$name\n{\n"; + foreach my $option ($group->options()) { + $res .= $option->name(); + my $value= $option->value(); + if (defined $value) { + $res .= "=$value"; + } + $res .= "\n"; + } + $res .= "}\n\n"; + } + $res; +} + +sub searchd_start { + my ($sphinx, $test) = @_; # My::Config::Group, My::Test + + return unless $exe_sphinx_indexer and $exe_sphinx_searchd; + return if $sphinx->{proc}; # Already started + + # First we must run the indexer to create the data. + my $sphinx_data_dir= "$::opt_vardir/" . $sphinx->name(); + mkpath($sphinx_data_dir); + my $sphinx_log= $sphinx->value('#log-error'); + my $sphinx_config= "$::opt_vardir/my_sphinx.conf"; + my $cmd= "\"$exe_sphinx_indexer\" --config \"$sphinx_config\" test1 > \"$sphinx_log\" 2>&1"; + &::mtr_verbose("cmd: $cmd"); + system $cmd; + + # Then start the searchd daemon. + my $args; + &::mtr_init_args(\$args); + &::mtr_add_arg($args, "--config"); + &::mtr_add_arg($args, $sphinx_config); + &::mtr_add_arg($args, "--console"); + &::mtr_add_arg($args, "--pidfile"); + + $sphinx->{'proc'}= My::SafeProcess->new + ( + name => 'sphinx-' . $sphinx->name(), + path => $exe_sphinx_searchd, + args => \$args, + output => $sphinx_log, + error => $sphinx_log, + append => 1, + nocore => 1, + ); + &::mtr_verbose("Started $sphinx->{proc}"); +} + +sub wait_exp_backoff { + my $timeout= shift; # Seconds + my $start_wait= shift; # Seconds + my $scale_factor= shift; + + $searchd_status= "$exe_sphinx_searchd --status" . + " --config $sphinx_config > /dev/null 2>&1"; + + my $scale= $start_wait; + my $total_sleep= 0; + while (1) { + my $status = system($searchd_status); + if (not $status) { + return 0; + } + if ($total_sleep >= $timeout) { + last; + } + + &::mtr_milli_sleep($scale * 1000); + $total_sleep+= $scale; + $scale*= $scale_factor; + } + + &::mtr_warning("Getting a response from searchd timed out"); + return 1 +} + +sub searchd_wait { + my ($sphinx) = @_; # My::Config::Group + + return wait_exp_backoff(30, 0.1, 2) +} + +############# declaration methods ###################### + +sub config_files() { + ( 'my_sphinx.conf' => \&write_sphinx_conf ) +} + +sub servers { + ( qr/^searchd$/ => { + SORT => 400, + START => \&searchd_start, + WAIT => \&searchd_wait, + } + ) +} + +sub is_default { 0 } + +############# return an object ###################### +bless { }; + diff --git a/storage/sphinx/mysql-test/sphinx/testdata.xml b/storage/sphinx/mysql-test/sphinx/testdata.xml new file mode 100644 index 00000000..7ef05bce --- /dev/null +++ b/storage/sphinx/mysql-test/sphinx/testdata.xml @@ -0,0 +1,44 @@ +<?xml version="1.0" encoding="utf-8"?> +<sphinx:docset> + +<sphinx:schema> +<sphinx:field name="title"/> +<sphinx:field name="content"/> +<sphinx:attr name="gid" type="int"/> +<sphinx:attr name="meta" type="json"/> +</sphinx:schema> + +<sphinx:document id="1"> +<title>test one</title> +<content>this is my test document number one. also checking search within phrases.</content> +<gid>1</gid> +<meta>{ "foo_count": 100 }</meta> +</sphinx:document> + +<sphinx:document id="2"> +<title>test two</title> +<content>this is my test document number two</content> +<gid>1</gid> +</sphinx:document> + +<sphinx:document id="3"> +<title>another doc</title> +<content>this is another group</content> +<gid>2</gid> +</sphinx:document> + +<sphinx:document id="4"> +<title>doc number four</title> +<content>this is to test groups</content> +<gid>2</gid> +</sphinx:document> + +<sphinx:document id="5"> +<title>doc number five</title> +<content>this is to test json filtering</content> +<gid>1</gid> +<meta>{ "foo_count": 200, "sub": { "list": [ 3, 4 ], "int": 7 } }</meta> +</sphinx:document> + +</sphinx:docset> + diff --git a/storage/sphinx/mysql-test/sphinx/union-5539.result b/storage/sphinx/mysql-test/sphinx/union-5539.result new file mode 100644 index 00000000..945e0141 --- /dev/null +++ b/storage/sphinx/mysql-test/sphinx/union-5539.result @@ -0,0 +1,16 @@ +create table ts (id bigint unsigned not null, w int not null, query varchar(255) not null, index(query)) engine=sphinx connection="sphinx://127.0.0.1:PORT/*"; +SELECT a.* FROM (SELECT * FROM ts si WHERE si.query=';mode=extended2;limit=1000000;maxmatches=500') AS a UNION SELECT b.* FROM (SELECT * FROM ts si WHERE si.query='@* 123nothingtofind123;mode=extended2;limit=1000000;maxmatches=500') AS b; +id w query +1 1 ;mode=extended2;limit=1000000;maxmatches=500 +2 1 ;mode=extended2;limit=1000000;maxmatches=500 +3 1 ;mode=extended2;limit=1000000;maxmatches=500 +4 1 ;mode=extended2;limit=1000000;maxmatches=500 +5 1 ;mode=extended2;limit=1000000;maxmatches=500 +SELECT a.* FROM (SELECT * FROM ts si WHERE si.query='@* 123nothingtofind123;mode=extended2;limit=1000000;maxmatches=500') AS a UNION SELECT b.* FROM (SELECT * FROM ts si WHERE si.query=';mode=extended2;limit=1000000;maxmatches=500') AS b; +id w query +1 1 ;mode=extended2;limit=1000000;maxmatches=500 +2 1 ;mode=extended2;limit=1000000;maxmatches=500 +3 1 ;mode=extended2;limit=1000000;maxmatches=500 +4 1 ;mode=extended2;limit=1000000;maxmatches=500 +5 1 ;mode=extended2;limit=1000000;maxmatches=500 +drop table ts; diff --git a/storage/sphinx/mysql-test/sphinx/union-5539.test b/storage/sphinx/mysql-test/sphinx/union-5539.test new file mode 100644 index 00000000..94cc2c02 --- /dev/null +++ b/storage/sphinx/mysql-test/sphinx/union-5539.test @@ -0,0 +1,11 @@ +# +# MDEV-5539 Empty results in UNION with Sphinx engine +# +--replace_result $SPHINXSEARCH_PORT PORT +eval create table ts (id bigint unsigned not null, w int not null, query varchar(255) not null, index(query)) engine=sphinx connection="sphinx://127.0.0.1:$SPHINXSEARCH_PORT/*"; +let $q1=SELECT * FROM ts si WHERE si.query=';mode=extended2;limit=1000000;maxmatches=500'; +let $q2=SELECT * FROM ts si WHERE si.query='@* 123nothingtofind123;mode=extended2;limit=1000000;maxmatches=500'; +eval SELECT a.* FROM ($q1) AS a UNION SELECT b.* FROM ($q2) AS b; +eval SELECT a.* FROM ($q2) AS a UNION SELECT b.* FROM ($q1) AS b; +drop table ts; + |