--- Begin Message ---
Package: mysql-5.1-server
Version: mysql-server-5.1
Severity: grave
Justification: renders package unusable
Tried to build MySQL-5.1 from source, as I need some minor tweaks in
myisam/ftdefs.h. Building failed. What I did was:
| $ apt-get source mysql-server-5.1
| $ cd mysql-5.1-5.1.49
| $ vi storage/mysisam/ftdefs.h
| [Changing misc_word_char(X) into something which is still valid:]
| #define misc_word_char(X) ((X)=='\'' || (X)=='-' || (X)=='.' ||
(X)=='%')
| [/Changing]
| $ dpkg-buildpackage -b -rfakeroot
| [...]
| Completed: Failed 4/711 tests, 99.44% were successful.
| Failing test(s): main.fulltext main.mysql main.mysql_upgrade
binlog.binlog_stm_blackhole
| The log files in var/log may give you some hint of what went wrong.
At least _I_ am not able to derive anything from the test suite logs. I
am attaching the four logfiles which seem to correspond to the failed
tests, but I cannot find any hint to an error in there. If you need
anything else, please do contact me. The complete log directory is much
too big to include it completely.
-- System Information:
Debian Release: 6.0
APT prefers squeeze-updates
APT policy: (500, 'squeeze-updates'), (500, 'stable')
Architecture: i386 (i686)
Kernel: Linux 2.6.28.7 (SMP w/2 CPU cores)
Locale: LANG=de_AT.UTF-8, LC_CTYPE=de_AT.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
drop table if exists t1,t2,t3;
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
('Full-text indexes', 'are called collections'),
('Only MyISAM tables','support collections'),
('Function MATCH ... AGAINST()','is used to do a search'),
('Full-text search in MySQL', 'implements vector space model');
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name
Collation Cardinality Sub_part Packed Null Index_type
Comment
t1 1 a 1 a NULL NULL NULL NULL YES
FULLTEXT
t1 1 a 2 b NULL NULL NULL NULL YES
FULLTEXT
select * from t1 where MATCH(a,b) AGAINST ("collections");
a b
Full-text indexes are called collections
Only MyISAM tables support collections
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
id select_type table type possible_keys key key_len ref
rows filtered Extra
1 SIMPLE t1 fulltext a a 0 1
100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from
`test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against
('collections'))
select * from t1 where MATCH(a,b) AGAINST ("indexes");
a b
Full-text indexes are called collections
select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
a b
Full-text indexes are called collections
Only MyISAM tables support collections
select * from t1 where MATCH(a,b) AGAINST ("only");
a b
select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
a b
Only MyISAM tables support collections
Full-text indexes are called collections
MySQL has now support for full-text search
select * from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
a b
Full-text indexes are called collections
Only MyISAM tables support collections
select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY
EXPANSION);
a b
Full-text indexes are called collections
Only MyISAM tables support collections
MySQL has now support for full-text search
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE);
a b
Full-text indexes are called collections
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE
WITH QUERY EXPANSION);
a b
Full-text indexes are called collections
Only MyISAM tables support collections
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY
EXPANSION);
ERROR 42000: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'WITH
QUERY EXPANSION)' at line 1
explain select * from t1 where MATCH(a,b) AGAINST ("collections");
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext a a 0 1
Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0;
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext a a 0 1
Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1;
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext a a 0 1
Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0;
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using
where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1;
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext a a 0 1
Using where
explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections");
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext a a 0 1
Using where
explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections");
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext a a 0 1
Using where
explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections");
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using
where
explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections");
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext a a 0 1
Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like
'%ll%';
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext a a 0 1
Using where
select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN
MODE);
a b
MySQL has now support for full-text search
explain extended select * from t1 where MATCH(a,b) AGAINST("support
-collections" IN BOOLEAN MODE);
id select_type table type possible_keys key key_len ref
rows filtered Extra
1 SIMPLE t1 fulltext a a 0 1
100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from
`test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('support
-collections' in boolean mode))
select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN
MODE);
a b
MySQL has now support for full-text search
Full-text indexes are called collections
Only MyISAM tables support collections
select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN
MODE);
a b
Full-text indexes are called collections
Only MyISAM tables support collections
select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE);
a b
MySQL has now support for full-text search
Function MATCH ... AGAINST() is used to do a search
Full-text search in MySQL implements vector space model
select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN
MODE);
a b
Only MyISAM tables support collections
select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
a b
MySQL has now support for full-text search
Function MATCH ... AGAINST() is used to do a search
Full-text search in MySQL implements vector space model
select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN
BOOLEAN MODE);
a b
MySQL has now support for full-text search
Full-text search in MySQL implements vector space model
select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN
BOOLEAN MODE);
a b
Function MATCH ... AGAINST() is used to do a search
select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from
t1;
a b x
MySQL has now support for full-text search 1
Full-text indexes are called collections 1
Only MyISAM tables support collections 2
Function MATCH ... AGAINST() is used to do a search 0
Full-text search in MySQL implements vector space model 0
select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from
t1;
a b x
MySQL has now support for full-text search 1
Full-text indexes are called collections 1
Only MyISAM tables support collections 2
Function MATCH ... AGAINST() is used to do a search 0
Full-text search in MySQL implements vector space model 0
select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
a b
Full-text indexes are called collections
select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
a b
select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
a b
MySQL has now support for full-text search
select * from t1 where MATCH a,b AGAINST ('"now support"' IN BOOLEAN MODE);
a b
MySQL has now support for full-text search
select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' IN
BOOLEAN MODE);
a b
MySQL has now support for full-text search
select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN
BOOLEAN MODE);
a b
select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN
BOOLEAN MODE);
a b
MySQL has now support for full-text search
select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE);
a b
select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE);
a b
select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN
BOOLEAN MODE);
a b
select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*'
IN BOOLEAN MODE);
a b
select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN
BOOLEAN MODE);
a b
Full-text indexes are called collections
select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE);
a b
Full-text search in MySQL implements vector space model
select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE);
a b
Full-text search in MySQL implements vector space model
select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE);
a b
MySQL has now support for full-text search
Function MATCH ... AGAINST() is used to do a search
select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select *
from t1 where MATCH(a,b) AGAINST ("indexes");
a b
Full-text indexes are called collections
Only MyISAM tables support collections
Full-text indexes are called collections
delete from t1 where a like "MySQL%";
update t1 set a='some test foobar' where MATCH a,b AGAINST ('model');
delete from t1 where MATCH(a,b) AGAINST ("indexes");
select * from t1;
a b
Only MyISAM tables support collections
Function MATCH ... AGAINST() is used to do a search
some test foobar implements vector space model
drop table t1;
create table t1 (a varchar(200) not null, fulltext (a));
insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10");
select * from t1 where match a against ("+aaa* +bbb*" in boolean mode);
a
aaa30 bbb10
aaa20 bbb15
aaa10 bbb20
select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode);
a
aaa30 bbb10
aaa20 bbb15
select * from t1 where match a against ("+aaa* +ccc*" in boolean mode);
a
select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode);
a
aaa10 bbb20
select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode);
a
aaa30 bbb10
aaa20 bbb15
select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode);
a
aaa30 bbb10
aaa20 bbb15
drop table t1;
CREATE TABLE t1 (
id int(11),
ticket int(11),
KEY ti (id),
KEY tit (ticket)
);
INSERT INTO t1 VALUES (2,3),(1,2);
CREATE TABLE t2 (
ticket int(11),
inhalt text,
KEY tig (ticket),
fulltext index tix (inhalt)
);
INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar');
select t1.id FROM t2 as ttxt,t1,t1 as ticket2
WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and
match(ttxt.inhalt) against ('foobar');
id
select ticket2.id FROM t2 as ttxt,t2 INNER JOIN t1 as ticket2 ON
ticket2.id = t2.ticket
WHERE ticket2.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar');
id
INSERT INTO t1 VALUES (3,3);
select ticket2.id FROM t2 as ttxt,t2
INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket
WHERE ticket2.id = ticket2.ticket and
match(ttxt.inhalt) against ('foobar');
id
3
show keys from t2;
Table Non_unique Key_name Seq_in_index Column_name
Collation Cardinality Sub_part Packed Null Index_type
Comment
t2 1 tig 1 ticket A NULL NULL NULL YES
BTREE
t2 1 tix 1 inhalt NULL NULL NULL NULL YES
FULLTEXT
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`ticket` int(11) DEFAULT NULL,
`inhalt` text,
KEY `tig` (`ticket`),
FULLTEXT KEY `tix` (`inhalt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t2 where MATCH inhalt AGAINST (NULL);
ticket inhalt
select * from t2 where MATCH inhalt AGAINST ('foobar');
ticket inhalt
3 foobar
select * from t2 having MATCH inhalt AGAINST ('foobar');
ticket inhalt
3 foobar
CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i));
ERROR HY000: Column 't' cannot be part of FULLTEXT index
CREATE TABLE t3 (t int(11),i text,
j varchar(200) CHARACTER SET latin2,
fulltext tix (i,j));
ERROR HY000: Column 'j' cannot be part of FULLTEXT index
CREATE TABLE t3 (
ticket int(11),
inhalt text,
KEY tig (ticket),
fulltext index tix (inhalt)
);
select * from t2 where MATCH inhalt AGAINST (t2.inhalt);
ERROR HY000: Incorrect arguments to AGAINST
select * from t2 where MATCH ticket AGAINST ('foobar');
ERROR HY000: Can't find FULLTEXT index matching the column list
select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar');
ERROR HY000: Incorrect arguments to MATCH
drop table t1,t2,t3;
CREATE TABLE t1 (
id int(11) auto_increment,
title varchar(100) default '',
PRIMARY KEY (id),
KEY ind5 (title)
) ENGINE=MyISAM;
CREATE FULLTEXT INDEX ft1 ON t1(title);
insert into t1 (title) values ('this is a test');
select * from t1 where match title against ('test' in boolean mode);
id title
1 this is a test
update t1 set title='this is A test' where id=1;
check table t1;
Table Op Msg_type Msg_text
test.t1 check status OK
update t1 set title='this test once revealed a bug' where id=1;
select * from t1;
id title
1 this test once revealed a bug
update t1 set title=NULL where id=1;
drop table t1;
CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE=MyISAM;
insert into t1 values (1,"I wonder why the fulltext index doesnt work?");
SELECT * from t1 where MATCH (b) AGAINST ('apples');
a b
insert into t1 values (2,"fullaaa fullzzz");
select * from t1 where match b against ('full*' in boolean mode);
a b
2 fullaaa fullzzz
1 I wonder why the fulltext index doesnt work?
drop table t1;
CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text
NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so
funny'),(4,'MySQL Tutorial');
select 8 from t1;
8
8
8
8
8
drop table t1;
create table t1 (a text, fulltext key (a));
insert into t1 values ('aaaa');
repair table t1;
Table Op Msg_type Msg_text
test.t1 repair status OK
select * from t1 where match (a) against ('aaaa');
a
drop table t1;
create table t1 ( ref_mag text not null, fulltext (ref_mag));
insert into t1 values ('test');
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
ref_mag
test
alter table t1 change ref_mag ref_mag char (255) not null;
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
ref_mag
test
drop table t1;
create table t1 (t1_id int(11) primary key, name varchar(32));
insert into t1 values (1, 'data1');
insert into t1 values (2, 'data2');
create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32));
insert into t2 values (1, 1, 'xxfoo');
insert into t2 values (2, 1, 'xxbar');
insert into t2 values (3, 1, 'xxbuz');
select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name)
against('xxfoo' in boolean mode);
t1_id name t2_id name
1 data1 1 xxfoo
select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
t2_id t1_id name
drop table t1,t2;
create table t1 (a text, fulltext key (a));
insert into t1 select "xxxx yyyy zzzz";
drop table t1;
SET NAMES latin1;
CREATE TABLE t1 (t text character set utf8 not null, fulltext(t));
INSERT t1 VALUES ('Mit freundlichem Grüß'), ('aus Osnabrück');
SET NAMES koi8r;
INSERT t1 VALUES ("üÔÏ ÍÙ - ÏÐÉÌËÉ"),("ïÔÌÅÚØ, ÇÎÉÄÁ!"),
("îÅ ×ÌÅÚÁÊ, ÕÂØÅÔ!"),("É ÂÕÄÅÔ ÐÒÁ×!");
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('ïðéìëé');
t collation(t)
üÔÏ ÍÙ - ÏÐÉÌËÉ utf8_general_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('ðÒá*' IN BOOLEAN MODE);
t collation(t)
É ÂÕÄÅÔ ÐÒÁ×! utf8_general_ci
SELECT * FROM t1 WHERE MATCH t AGAINST ('ÜÔÏ' IN BOOLEAN MODE);
t
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück');
t collation(t)
SET NAMES latin1;
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück');
t collation(t)
aus Osnabrück utf8_general_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
t collation(t)
SELECT t, collation(t),FORMAT(MATCH t AGAINST ('Osnabruck'),6) FROM t1 WHERE
MATCH t AGAINST ('Osnabruck');
t collation(t) FORMAT(MATCH t AGAINST ('Osnabruck'),6)
aus Osnabrück utf8_general_ci 1.591140
alter table t1 modify t varchar(200) collate latin1_german2_ci not null;
Warnings:
Warning 1366 Incorrect string value: '\xD0\xAD\xD1\x82\xD0\xBE...' for
column 't' at row 3
Warning 1366 Incorrect string value: '\xD0\x9E\xD1\x82\xD0\xBB...' for
column 't' at row 4
Warning 1366 Incorrect string value: '\xD0\x9D\xD0\xB5 \xD0...' for column
't' at row 5
Warning 1366 Incorrect string value: '\xD0\xB8 \xD0\xB1\xD1...' for column
't' at row 6
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück');
t collation(t)
aus Osnabrück latin1_german2_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
t collation(t)
aus Osnabrück latin1_german2_ci
DROP TABLE t1;
CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) DEFAULT CHARSET=utf8;
insert into t1 (s) values ('pära para para'),('para para para');
select * from t1 where match(s) against('para' in boolean mode);
s
pära para para
para para para
select * from t1 where match(s) against('par*' in boolean mode);
s
pära para para
para para para
DROP TABLE t1;
CREATE TABLE t1 (h text, FULLTEXT (h));
INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing');
REPAIR TABLE t1;
Table Op Msg_type Msg_text
test.t1 repair status OK
select count(*) from t1;
count(*)
1
drop table t1;
CREATE TABLE t1 ( a TEXT, FULLTEXT (a) );
INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance');
SELECT MATCH(a) AGAINST ('nosuchword') FROM t1;
MATCH(a) AGAINST ('nosuchword')
0
DROP TABLE t1;
create table t1 (a int primary key, b text, fulltext(b));
create table t2 (a int, b text);
insert t1 values (1, "aaaa"), (2, "bbbb");
insert t2 values (10, "aaaa"), (2, "cccc");
replace t1 select * from t2;
drop table t1, t2;
CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
FULLTEXT (t));
SET NAMES latin1;
INSERT INTO t1 VALUES('Mit freundlichem Grüß aus Osnabrück');
SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabrück"' IN BOOLEAN MODE);
COUNT(*)
1
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a));
INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
SET myisam_repair_threads=2;
REPAIR TABLE t1;
Table Op Msg_type Msg_text
test.t1 repair status OK
SET myisam_repair_threads=@@global.myisam_repair_threads;
INSERT INTO t1 VALUES('testword\'\'');
SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE);
a
testword''
SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE);
a
testword''
INSERT INTO t1 VALUES('test\'s');
SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE);
a
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` varchar(10000) DEFAULT NULL,
FULLTEXT KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a));
INSERT INTO t1 VALUES('test'),('test1'),('test');
PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1
WHERE MATCH(a) AGAINST('test1 test')";
EXECUTE stmt;
a FORMAT(MATCH(a) AGAINST('test1 test'),6)
test1 0.685267
EXECUTE stmt;
a FORMAT(MATCH(a) AGAINST('test1 test'),6)
test1 0.685267
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a));
SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test');
a
ALTER TABLE t1 DISABLE KEYS;
SELECT * FROM t1 WHERE MATCH(a) AGAINST('test');
ERROR HY000: Can't find FULLTEXT index matching the column list
DROP TABLE t1;
CREATE TABLE t1(a TEXT);
INSERT INTO t1 VALUES(' aaaaa aaaa');
SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE);
a
aaaaa aaaa
DROP TABLE t1;
CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a));
INSERT INTO t1 VALUES('Offside'),('City Of God');
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE);
a
City Of God
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE);
a
City Of God
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE);
a
City Of God
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b));
INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1),
('test', 1),('test', 2),('test', 3),('test', 4);
EXPLAIN SELECT * FROM t1
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext b,a a 0 1
Using where
EXPLAIN SELECT * FROM t1 USE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext a a 0 1
Using where
EXPLAIN SELECT * FROM t1 FORCE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 fulltext a a 0 1
Using where
EXPLAIN SELECT * FROM t1 IGNORE INDEX(a)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 ref b b 5 const 4 Using
where
EXPLAIN SELECT * FROM t1 USE INDEX(b)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 ref b b 5 const 4 Using
where
EXPLAIN SELECT * FROM t1 FORCE INDEX(b)
WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1;
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE t1 ref b b 5 const 4 Using
where
DROP TABLE t1;
CREATE TABLE t1(a CHAR(10));
INSERT INTO t1 VALUES('aaa15');
SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1;
MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE)
1
SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1;
MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE)
2
DROP TABLE t1;
CREATE TABLE t1(a TEXT);
SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN
MODE);
ERROR HY000: Incorrect arguments to AGAINST
DROP TABLE t1;
CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a));
INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd');
SELECT * FROM t1 WHERE MATCH(a) AGAINST('+awrd bwrd* +cwrd*' IN BOOLEAN MODE);
a
awrd bwrd cwrd
awrd bwrd cwrd
awrd bwrd cwrd
DROP TABLE t1;
CREATE TABLE t1 (col text, FULLTEXT KEY full_text (col));
PREPARE s FROM
"SELECT MATCH (col) AGAINST('findme') FROM t1 ORDER BY MATCH (col)
AGAINST('findme')"
;
EXECUTE s;
MATCH (col) AGAINST('findme')
DEALLOCATE PREPARE s;
DROP TABLE t1;
#
# Bug #49250 : spatial btree index corruption and crash
# Part two : fulltext syntax check
#
CREATE TABLE t1(col1 TEXT,
FULLTEXT INDEX USING BTREE (col1));
ERROR 42000: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'USING BTREE (col1))' at line 2
CREATE TABLE t2(col1 TEXT);
CREATE FULLTEXT INDEX USING BTREE ON t2(col);
ERROR 42000: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'USING BTREE ON t2(col)' at line 1
ALTER TABLE t2 ADD FULLTEXT INDEX USING BTREE (col1);
ERROR 42000: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'USING BTREE (col1)' at line 1
DROP TABLE t2;
End of 5.0 tests
#
# Bug #47930: MATCH IN BOOLEAN MODE returns too many results
# inside subquery
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1), (2);
CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2));
INSERT INTO t2 VALUES (1,'Scargill');
CREATE TABLE t3 (a int, b int);
INSERT INTO t3 VALUES (1,1), (2,1);
# t2 should use full text index
EXPLAIN
SELECT count(*) FROM t1 WHERE
not exists(
SELECT 1 FROM t2, t3
WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
);
id select_type table type possible_keys key key_len ref
rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using
where
2 DEPENDENT SUBQUERY t2 fulltext b2 b2 0
1 Using where
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL
2 Using where
# should return 0
SELECT count(*) FROM t1 WHERE
not exists(
SELECT 1 FROM t2, t3
WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
);
count(*)
0
# should return 0
SELECT count(*) FROM t1 WHERE
not exists(
SELECT 1 FROM t2 IGNORE INDEX (b2), t3
WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE)
);
count(*)
0
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a));
INSERT INTO t1 VALUES
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),
('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('awrd'),('cwrd'),
('awrd');
SELECT COUNT(*) FROM t1 WHERE MATCH(a) AGAINST("+awrd bwrd* +cwrd*" IN BOOLEAN
MODE);
COUNT(*)
0
DROP TABLE t1;
#
# Bug #49445: Assertion failed: 0, file .\item_row.cc, line 55 with
# fulltext search and row op
#
CREATE TABLE t1(a CHAR(1),FULLTEXT(a));
SELECT 1 FROM t1 WHERE MATCH(a) AGAINST ('') AND ROW(a,a) > ROW(1,1);
1
DROP TABLE t1;
#
# BUG#51866 - crash with repair by sort and fulltext keys
#
CREATE TABLE t1(a CHAR(4), FULLTEXT(a));
INSERT INTO t1 VALUES('aaaa');
SET myisam_sort_buffer_size=4;
Warnings:
Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '4'
REPAIR TABLE t1;
Table Op Msg_type Msg_text
test.t1 repair status OK
SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size;
DROP TABLE t1;
End of 5.1 tests
drop table if exists t1;
create table t1(a int);
insert into t1 values(1);
ERROR at line 9: DELIMITER must be followed by a 'delimiter' character or string
Test default delimiter ;
a
1
Test delimiter without arg
Test delimiter :
a
1
Test delimiter :
a
1
Test delimiter :;
a
1
Test delimiter //
a
1
Test delimiter MySQL
a
1
Test delimiter delimiter
a
1
Tables_in_test
t1
t2
t3
Tables_in_test
t1
_
Test delimiter : from command line
a
1
_
Test delimiter :; from command line
a
1
_
Test 'go' command(vertical output) G
*************************** 1. row ***************************
a: 1
_
Test 'go' command g
a
1
drop table t1;
create table t1(a int);
lock tables t1 write;
database()
test
unlock tables;
drop table t1;
+----------------------+------------+--------+
| concat('>',col1,'<') | col2 | col3 |
+----------------------+------------+--------+
| >a < | b | 123421 |
| >a < | 0123456789 | 4 |
| >abcd< | | 4 |
+----------------------+------------+--------+
+-------------------+
| __tañgè Ñãmé |
+-------------------+
| John Doe |
+-------------------+
+-------------------+
| John Doe |
+-------------------+
| __tañgè Ñãmé |
+-------------------+
+------+------+---------------------------+
| i | j | k |
+------+------+---------------------------+
| 1 | NULL | NULL |
| NULL | NULL | <-----------------------> |
| NULL | NULL | <----- |
| NULL | NULL | Τη γλώσσα |
| NULL | NULL | ᛖᚴ ᚷᛖᛏ |
+------+------+---------------------------+
i j k
NULL 1 NULL
Field Type Null Key Default Extra
i int(11) YES NULL
j int(11) NO NULL
k int(11) YES NULL
+------+---+------+
| i | j | k |
+------+---+------+
| NULL | 1 | NULL |
+------+---+------+
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | YES | | NULL | |
| j | int(11) | NO | | NULL | |
| k | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
i s1
1 x
2 NULL
3
+------+------+
| i | s1 |
+------+------+
| 1 | x |
| 2 | NULL |
| 3 | |
+------+------+
unhex('zz')
NULL
+-------------+
| unhex('zz') |
+-------------+
| NULL |
+-------------+
create table t1(a int, b varchar(255), c int);
Field Type Null Key Default Extra
a int(11) YES NULL
b varchar(255) YES NULL
c int(11) YES NULL
Field Type Null Key Default Extra
a int(11) YES NULL
b varchar(255) YES NULL
c int(11) YES NULL
drop table t1;
1
1
ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
ERROR at line 1: USE must be followed by a database name
\
\\
';
';
create table t17583 (a int);
insert into t17583 (a) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
insert into t17583 select a from t17583;
insert into t17583 select a from t17583;
insert into t17583 select a from t17583;
insert into t17583 select a from t17583;
insert into t17583 select a from t17583;
insert into t17583 select a from t17583;
insert into t17583 select a from t17583;
select count(*) from t17583;
count(*)
1280
drop table t17583;
Test connect without db- or host-name => reconnect
Test connect with dbname only => new dbname, old hostname
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'connecttest' at line 1
Test connect with _invalid_ dbname only => new invalid dbname, old hostname
ERROR 1049 (42000) at line 1: Unknown database 'invalid'
ERROR 1049 (42000) at line 1: Unknown database 'invalid'
Test connect with dbname + hostname
Test connect with dbname + _invalid_ hostname
mtr.global_suppressions OK
mtr.test_suppressions OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log
Error : You can't use locks with log tables.
status : OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.servers OK
mysql.slow_log
Error : You can't use locks with log tables.
status : OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
drop table if exists t1,t2;
CREATE TABLE t1 (
Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
) ENGINE=blackhole;
INSERT INTO t1 VALUES (9410,9412);
select period from t1;
period
select * from t1;
Period Varor_period
select t1.* from t1;
Period Varor_period
CREATE TABLE t2 (
auto int NOT NULL auto_increment,
fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
fld3 char(30) DEFAULT '' NOT NULL,
fld4 char(35) DEFAULT '' NOT NULL,
fld5 char(35) DEFAULT '' NOT NULL,
fld6 char(4) DEFAULT '' NOT NULL,
primary key (auto)
) ENGINE=blackhole;
INSERT INTO t2 VALUES (1192,068305,00,'Colombo','hardware','colicky','');
INSERT INTO t2 VALUES
(1193,000000,00,'nondecreasing','implant','thrillingly','');
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
fld3
select fld3 from t2 where fld3 like "%cultivation" ;
fld3
select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
fld3 companynr
select fld3,companynr from t2 where companynr = 58 order by fld3;
fld3 companynr
select fld3 from t2 order by fld3 desc limit 10;
fld3
select fld3 from t2 order by fld3 desc limit 5;
fld3
select fld3 from t2 order by fld3 desc limit 5,5;
fld3
select t2.fld3 from t2 where fld3 = 'honeysuckle';
fld3
select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
fld3
select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
fld3
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
fld3
select t2.fld3 from t2 where fld3 LIKE 'h%le';
fld3
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
fld3
select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
fld3
select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order
by fld3;
fld3
select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order
by fld3;
fld1 fld3
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
('Full-text indexes', 'are called collections'),
('Only MyISAM tables','support collections'),
('Function MATCH ... AGAINST()','is used to do a search'),
('Full-text search in MySQL', 'implements vector space model');
SHOW INDEX FROM t1;
Table Non_unique Key_name Seq_in_index Column_name
Collation Cardinality Sub_part Packed Null Index_type
Comment
t1 1 a 1 a NULL NULL NULL NULL YES
FULLTEXT
t1 1 a 2 b NULL NULL NULL NULL YES
FULLTEXT
select * from t1 where MATCH(a,b) AGAINST ("collections");
a b
Full-text indexes are called collections
Only MyISAM tables support collections
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
id select_type table type possible_keys key key_len ref
rows filtered Extra
1 SIMPLE t1 fulltext a a 0 1
100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from
`test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against
('collections'))
select * from t1 where MATCH(a,b) AGAINST ("indexes");
a b
Full-text indexes are called collections
select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
a b
Full-text indexes are called collections
Only MyISAM tables support collections
select * from t1 where MATCH(a,b) AGAINST ("only");
a b
reset master;
drop table t1,t2;
create table t1 (a int) engine=blackhole;
delete from t1 where a=10;
update t1 set a=11 where a=15;
insert into t1 values(1);
insert ignore into t1 values(1);
replace into t1 values(100);
create table t2 (a varchar(200)) engine=blackhole;
load data infile '../../std_data/words.dat' into table t2;
alter table t1 add b int;
alter table t1 drop b;
create table t3 like t1;
insert into t1 select * from t3;
replace into t1 select * from t3;
select * from t1;
a
select * from t2;
a
select * from t3;
a
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; drop table
t1,t2
master-bin.000001 # Query # # use `test`; create
table t1 (a int) engine=blackhole
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; delete from
t1 where a=10
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; update t1
set a=11 where a=15
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; insert into
t1 values(1)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; insert
ignore into t1 values(1)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; replace
into t1 values(100)
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # use `test`; create
table t2 (a varchar(200)) engine=blackhole
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Begin_load_query # #
;file_id=#;block_len=#
master-bin.000001 # Execute_load_query # # use
`test`; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE `t2` FIELDS
TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n'
(`a`) ;file_id=#
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # use `test`; alter table
t1 add b int
master-bin.000001 # Query # # use `test`; alter table
t1 drop b
master-bin.000001 # Query # # use `test`; create
table t3 like t1
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; insert into
t1 select * from t3
master-bin.000001 # Query # # COMMIT
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Query # # use `test`; replace
into t1 select * from t3
master-bin.000001 # Query # # COMMIT
drop table t1,t2,t3;
CREATE TABLE t1(a INT) ENGINE=BLACKHOLE;
INSERT DELAYED INTO t1 VALUES(1);
DROP TABLE t1;
CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE;
DELETE FROM t1 WHERE a=10;
ALTER TABLE t1 ADD INDEX(a);
DELETE FROM t1 WHERE a=10;
ALTER TABLE t1 DROP INDEX a;
ALTER TABLE t1 ADD UNIQUE INDEX(a);
DELETE FROM t1 WHERE a=10;
ALTER TABLE t1 DROP INDEX a;
ALTER TABLE t1 ADD PRIMARY KEY(a);
DELETE FROM t1 WHERE a=10;
DROP TABLE t1;
reset master;
create table t1 (a int) engine=blackhole;
set autocommit=0;
start transaction;
insert into t1 values(1);
commit;
start transaction;
insert into t1 values(2);
rollback;
set autocommit=1;
drop table if exists t1;
reset master;
create table t1 (a int auto_increment, primary key (a)) engine=blackhole;
insert into t1 values (11), (NULL), (NULL), (NULL);
set insert_id= 3;
insert into t1 values (NULL), (33), (NULL);
set insert_id= 5;
insert into t1 values (55), (NULL);
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query 1 # use `test`; create
table t1 (a int auto_increment, primary key (a)) engine=blackhole
master-bin.000001 # Query 1 # BEGIN
master-bin.000001 # Intvar 1 # INSERT_ID=1
master-bin.000001 # Query 1 # use `test`; insert into
t1 values (11), (NULL), (NULL), (NULL)
master-bin.000001 # Query 1 # COMMIT
master-bin.000001 # Query 1 # BEGIN
master-bin.000001 # Intvar 1 # INSERT_ID=3
master-bin.000001 # Query 1 # use `test`; insert into
t1 values (NULL), (33), (NULL)
master-bin.000001 # Query 1 # COMMIT
master-bin.000001 # Query 1 # BEGIN
master-bin.000001 # Intvar 1 # INSERT_ID=5
master-bin.000001 # Query 1 # use `test`; insert into
t1 values (55), (NULL)
master-bin.000001 # Query 1 # COMMIT
drop table t1;
--- End Message ---
