diff options
| -rw-r--r-- | Docs/manual.texi | 118 | ||||
| -rw-r--r-- | innobase/include/ut0dbg.h | 6 | ||||
| -rw-r--r-- | innobase/os/os0file.c | 49 | ||||
| -rw-r--r-- | myisammrg/mymrgdef.h | 1 | ||||
| -rw-r--r-- | myisammrg/myrg_rkey.c | 11 | ||||
| -rw-r--r-- | myisammrg/myrg_rnext.c | 45 | ||||
| -rw-r--r-- | myisammrg/myrg_rprev.c | 13 | ||||
| -rw-r--r-- | mysql-test/r/merge.result | 3 | ||||
| -rw-r--r-- | mysql-test/r/rpl000001.result | 6 | ||||
| -rw-r--r-- | mysql-test/t/merge.test | 13 | ||||
| -rw-r--r-- | mysql-test/t/rpl000001.test | 8 |
11 files changed, 153 insertions, 120 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi index b5337f456a8..76cf02a40ff 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -29435,42 +29435,36 @@ index. Full-text search is performed with the @code{MATCH} function. @example -mysql> CREATE TABLE t (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); +mysql> CREATE TABLE articles ( + -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, + -> title VARCHAR(200), + -> body TEXT, + -> FULLTEXT (title,body) + -> ); Query OK, 0 rows affected (0.00 sec) -mysql> INSERT INTO t 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'); +mysql> INSERT INTO articles VALUES + -> (0,'MySQL Tutorial', 'DBMS stands for DataBase Management ...'), + -> (0,'How To Use MySQL Efficiently', 'After you went through a ...'), + -> (0,'Optimizing MySQL','In this tutorial we will show how to ...'), + -> (0,'1001 MySQL Trick','1. Never run mysqld as root. 2. Normalize ...'), + -> (0,'MySQL vs. YourSQL', 'In the following database comparison we ...'), + -> (0,'MySQL Security', 'When configured properly, MySQL could be ...'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 -mysql> SELECT * FROM t WHERE MATCH (a,b) AGAINST ('MySQL'); -+---------------------------+-------------------------------+ -| a | b | -+---------------------------+-------------------------------+ -| MySQL has now support | for full-text search | -| Full-text search in MySQL | implements vector-space-model | -+---------------------------+-------------------------------+ +mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database'); ++----+-------------------+---------------------------------------------+ +| id | title | body | ++----+-------------------+---------------------------------------------+ +| 5 | MySQL vs. YourSQL | In the following database comparison we ... | +| 1 | MySQL Tutorial | DBMS stands for DataBase Management ... | ++----+-------------------+---------------------------------------------+ 2 rows in set (0.00 sec) - -mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t; -+------------------------------+-------------------------------+--------+ -| a | b | x | -+------------------------------+-------------------------------+--------+ -| MySQL has now support | for full-text search | 0.3834 | -| Full-text indexes | are called collections | 0.3834 | -| Only MyISAM tables | support collections | 0.7668 | -| Function MATCH ... AGAINST() | is used to do a search | 0 | -| Full-text search in MySQL | implements vector space model | 0 | -+------------------------------+-------------------------------+--------+ -5 rows in set (0.00 sec) @end example The function @code{MATCH} matches a natural language query @code{AGAINST} -a text collection (which is simply the columns that are covered by a +a text collection (which is simply the set of columns covered by a @code{FULLTEXT} index). For every row in a table it returns relevance - a similarity measure between the text in that row (in the columns that are part of the collection) and the query. When it is used in a @code{WHERE} @@ -29481,10 +29475,51 @@ number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word. -MySQL uses a very simple parser to split text into words. A ``word'' is -any sequence of letters, numbers, @samp{'}, and @samp{_}. Any ``word'' -that is present in the stopword list or just too short (3 characters -or less) is ignored. +The above is a basic example of using @code{MATCH} function. Rows are +returned with relevance decreasing. + +@example +mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles; ++----+-----------------------------------------+ +| id | MATCH (title,body) AGAINST ('Tutorial') | ++----+-----------------------------------------+ +| 1 | 0.64840710366884 | +| 2 | 0 | +| 3 | 0.66266459031789 | +| 4 | 0 | +| 5 | 0 | +| 6 | 0 | ++----+-----------------------------------------+ +5 rows in set (0.00 sec) +@end example + +This example shows how to retrieve the relevances. As neither @code{WHERE} +nor @code{ORDER BY} clauses are present, returned rows are not ordered. + +@example +mysql> SELECT id, body, MATCH (title,body) AGAINST ( + -> 'Security implications of running MySQL as root') AS score + -> FROM articles WHERE MATCH (title,body) AGAINST + -> ('Security implications of running MySQL as root'); ++----+-----------------------------------------------+-----------------+ +| id | body | score | ++----+-----------------------------------------------+-----------------+ +| 4 | 1. Never run mysqld as root. 2. Normalize ... | 1.5055546709332 | +| 6 | When configured properly, MySQL could be ... | 1.31140957288 | ++----+-----------------------------------------------+-----------------+ +2 rows in set (0.00 sec) +@end example + +This is more complex example - the query returns the relevance and still +sorts the rows with relevance decreasing. To achieve it one should specify +@code{MATCH} twice. Note, that this will cause no additional overhead, as +@strong{MySQL} optimizer will notice that these two @code{MATCH} calls are +identical and will call full-text search code only once. + +@strong{MySQL} uses a very simple parser to split text into words. A +``word'' is any sequence of letters, numbers, @samp{'}, and @samp{_}. Any +``word'' that is present in the stopword list or just too short (3 +characters or less) is ignored. Every correct word in the collection and in the query is weighted, according to its significance in the query or collection. This way, a @@ -29499,17 +29534,22 @@ carefully tuned this way). For very small tables, word distribution does not reflect adequately their semantical value, and this model may sometimes produce bizarre results. -For example, search for the word "search" will produce no results in the -above example. Word "search" is present in more than half of rows, and -as such, is effectively treated as a stopword (that is, with semantical value -zero). It is, really, the desired behavior - a natural language query -should not return every other row in 1GB table. +@example +mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL'); +Empty set (0.00 sec) +@end example + +Search for the word @code{MySQL} produces no results in the above example. +Word @code{MySQL} is present in more than half of rows, and as such, is +effectively treated as a stopword (that is, with semantical value zero). +It is, really, the desired behavior - a natural language query should not +return every second row in 1GB table. A word that matches half of rows in a table is less likely to locate relevant documents. In fact, it will most likely find plenty of irrelevant documents. We all know this happens far too often when we are trying to find something on the Internet with a search engine. It is with this reasoning that such rows -have been assigned a low semantical value in @strong{a particular dataset}. +have been assigned a low semantical value in @strong{this particular dataset}. @menu * Fulltext Fine-tuning:: @@ -44373,6 +44413,9 @@ Fixed problem with @code{MERGE} tables and big tables (> 4G) when using @appendixsubsec Changes in release 3.23.38 @itemize @bullet @item +Fixed a bug when @code{SELECT} from @code{MERGE} table +sometimes results in incorrectly ordered rows. +@item Fixed a bug in @code{REPLACE()} when using the ujis character set. @item Applied Sleepycat BDB patches 3.2.9.1 and 3.2.9.2. @@ -50234,7 +50277,6 @@ Start the @code{mysqld} server with a trace log in @file{/tmp/mysqld.trace} On Windows you should also use the @code{--standalone} flag to not start @code{mysqld} as a service. - Note that the trace file will get very @emph{BIG}! If you want to have a smaller trace file, you can use something like: diff --git a/innobase/include/ut0dbg.h b/innobase/include/ut0dbg.h index 751609b244e..657d1bf95b2 100644 --- a/innobase/include/ut0dbg.h +++ b/innobase/include/ut0dbg.h @@ -27,12 +27,12 @@ extern ulint* ut_dbg_null_ptr; \ if (!((ulint)(EXPR) + ut_dbg_zero)) {\ fprintf(stderr,\ - "Innobase: Assertion failure in thread %lu in file %s line %lu\n",\ + "InnoDB: Assertion failure in thread %lu in file %s line %lu\n",\ os_thread_get_curr_id(), IB__FILE__, (ulint)__LINE__);\ fprintf(stderr,\ - "Innobase: we intentionally generate a memory trap.\n");\ + "InnoDB: We intentionally generate a memory trap.\n");\ fprintf(stderr,\ - "Innobase: Send a bug report to mysql@lists.mysql.com\n");\ + "InnoDB: Send a detailed bug report to mysql@lists.mysql.com\n");\ ut_dbg_stop_threads = TRUE;\ dbg_i = *(ut_dbg_null_ptr);\ if (dbg_i) {\ diff --git a/innobase/os/os0file.c b/innobase/os/os0file.c index 065d9b6f553..96f8f332066 100644 --- a/innobase/os/os0file.c +++ b/innobase/os/os0file.c @@ -120,6 +120,12 @@ os_file_get_last_error(void) err = (ulint) GetLastError(); + if (err != ERROR_FILE_EXISTS) { + fprintf(stderr, + "InnoDB: operating system error number %li in a file operation.\n", + (long) err); + } + if (err == ERROR_FILE_NOT_FOUND) { return(OS_FILE_NOT_FOUND); } else if (err == ERROR_DISK_FULL) { @@ -132,6 +138,12 @@ os_file_get_last_error(void) #else err = (ulint) errno; + if (err != EEXIST) { + fprintf(stderr, + "InnoDB: operating system error number %i in a file operation.\n", + errno); + } + if (err == ENOSPC ) { return(OS_FILE_DISK_FULL); #ifdef POSIX_ASYNC_IO @@ -173,7 +185,7 @@ os_file_handle_error( "InnoDB: Encountered a problem with file %s.\n", name); } - fprintf(stderr, + fprintf(stderr, "InnoDB: Cannot continue operation.\n" "InnoDB: Disk is full. Try to clean the disk to free space.\n" "InnoDB: Delete possible created file and restart.\n"); @@ -184,7 +196,9 @@ os_file_handle_error( return(TRUE); } else { - ut_error; + fprintf(stderr, "InnoDB: Cannot continue operation.\n"); + + exit(1); } return(FALSE); @@ -300,12 +314,11 @@ try_again: UT_NOT_USED(purpose); /* On Linux opening a file in the O_SYNC mode seems to be much - more efficient than calling an explicit fsync or fdatasync after - each write */ + more efficient for small writes than calling an explicit fsync or + fdatasync after each write, but on Solaris O_SYNC and O_DSYNC is + extremely slow in large block writes to a big file. Therefore we + do not use these options, but use explicit fdatasync. */ -#ifdef O_SYNC - create_flag = create_flag | O_SYNC; -#endif if (create_mode == OS_FILE_CREATE) { file = open(name, create_flag, S_IRUSR | S_IWUSR | S_IRGRP | S_IWGRP | S_IROTH | S_IWOTH); @@ -425,10 +438,13 @@ os_file_set_size( byte* buf; try_again: - buf = ut_malloc(UNIV_PAGE_SIZE * 64); + /* We use a very big 16 MB buffer in writing because Linux is + extremely slow in fdatasync on 1 MB writes */ + + buf = ut_malloc(UNIV_PAGE_SIZE * 1024); /* Write buffer full of zeros */ - for (i = 0; i < UNIV_PAGE_SIZE * 64; i++) { + for (i = 0; i < UNIV_PAGE_SIZE * 1024; i++) { buf[i] = '\0'; } @@ -440,10 +456,10 @@ try_again: UT_NOT_USED(size_high); #endif while (offset < low) { - if (low - offset < UNIV_PAGE_SIZE * 64) { + if (low - offset < UNIV_PAGE_SIZE * 1024) { n_bytes = low - offset; } else { - n_bytes = UNIV_PAGE_SIZE * 64; + n_bytes = UNIV_PAGE_SIZE * 1024; } ret = os_file_write(name, file, buf, offset, 0, n_bytes); @@ -459,6 +475,8 @@ try_again: ret = os_file_flush(file); + fsync(file); + if (ret) { return(TRUE); } @@ -499,14 +517,7 @@ os_file_flush( #else int ret; -#ifdef O_SYNC - /* We open all files with the O_SYNC option, which means there - should be no need for fsync or fdatasync. In practice such a need - may be because on a Linux Xeon computer "donna" the OS seemed to be - fooled to believe that 500 disk writes/second are possible. */ - - ret = 0; -#elif defined(HAVE_FDATASYNC) +#ifdef HAVE_FDATASYNC ret = fdatasync(file); #else ret = fsync(file); diff --git a/myisammrg/mymrgdef.h b/myisammrg/mymrgdef.h index 564900614e2..aae1d07cd64 100644 --- a/myisammrg/mymrgdef.h +++ b/myisammrg/mymrgdef.h @@ -29,4 +29,3 @@ extern pthread_mutex_t THR_LOCK_open; #endif int _myrg_init_queue(MYRG_INFO *info,int inx,enum ha_rkey_function search_flag); -int _myrg_finish_scan(MYRG_INFO *info, int inx, enum ha_rkey_function type); diff --git a/myisammrg/myrg_rkey.c b/myisammrg/myrg_rkey.c index 6560380622a..cd2c73c8ec2 100644 --- a/myisammrg/myrg_rkey.c +++ b/myisammrg/myrg_rkey.c @@ -44,7 +44,6 @@ int myrg_rkey(MYRG_INFO *info,byte *record,int inx, const byte *key, MYRG_TABLE *table; MI_INFO *mi; int err; - byte *buf=((search_flag == HA_READ_KEY_EXACT) ? record: 0); LINT_INIT(key_buff); LINT_INIT(pack_key_length); @@ -57,14 +56,14 @@ int myrg_rkey(MYRG_INFO *info,byte *record,int inx, const byte *key, if (table == info->open_tables) { - err=mi_rkey(mi,buf,inx,key,key_len,search_flag); + err=mi_rkey(mi,0,inx,key,key_len,search_flag); key_buff=(byte*) mi->lastkey+mi->s->base.max_key_length; pack_key_length=mi->last_rkey_length; } else { mi->use_packed_key=1; - err=mi_rkey(mi,buf,inx,key_buff,pack_key_length,search_flag); + err=mi_rkey(mi,0,inx,key_buff,pack_key_length,search_flag); mi->use_packed_key=0; } info->last_used_table=table+1; @@ -78,12 +77,6 @@ int myrg_rkey(MYRG_INFO *info,byte *record,int inx, const byte *key, /* adding to queue */ queue_insert(&(info->by_key),(byte *)table); - /* if looking for KEY_EXACT, return first matched now */ - if (buf) - { - info->current_table=table; - return 0; - } } if (!info->by_key.elements) diff --git a/myisammrg/myrg_rnext.c b/myisammrg/myrg_rnext.c index d5bc9c54339..5a3fbdfb299 100644 --- a/myisammrg/myrg_rnext.c +++ b/myisammrg/myrg_rnext.c @@ -29,7 +29,11 @@ int myrg_rnext(MYRG_INFO *info, byte *buf, int inx) if ((err=mi_rnext(info->current_table->table,NULL,inx))) { if (err == HA_ERR_END_OF_FILE) + { queue_remove(&(info->by_key),0); + if (!info->by_key.elements) + return HA_ERR_END_OF_FILE; + } else return err; } @@ -40,48 +44,7 @@ int myrg_rnext(MYRG_INFO *info, byte *buf, int inx) queue_replaced(&(info->by_key)); } - /* next, let's finish myrg_rkey's initial scan */ - if ((err=_myrg_finish_scan(info, inx, HA_READ_KEY_OR_NEXT))) - return err; - - if (!info->by_key.elements) - return HA_ERR_END_OF_FILE; - /* now, mymerge's read_next is as simple as one queue_top */ mi=(info->current_table=(MYRG_TABLE *)queue_top(&(info->by_key)))->table; return mi_rrnd(mi,buf,mi->lastpos); } - - -/* let's finish myrg_rkey's initial scan */ - -int _myrg_finish_scan(MYRG_INFO *info, int inx, enum ha_rkey_function type) -{ - int err; - MYRG_TABLE *table=info->last_used_table; - if (table < info->end_table) - { - MI_INFO *mi= table[-1].table; - byte *key_buff=(byte*) mi->lastkey+mi->s->base.max_key_length; - uint pack_key_length= mi->last_rkey_length; - - for (; table < info->end_table ; table++) - { - mi=table->table; - mi->use_packed_key=1; - err=mi_rkey(mi,NULL,inx,key_buff,pack_key_length,type); - mi->use_packed_key=0; - if (err) - { - if (err == HA_ERR_KEY_NOT_FOUND) /* If end of file */ - continue; - return err; - } - /* Found here, adding to queue */ - queue_insert(&(info->by_key),(byte *) table); - } - /* All tables are now used */ - info->last_used_table=table; - } - return 0; -} diff --git a/myisammrg/myrg_rprev.c b/myisammrg/myrg_rprev.c index c63c73b4200..d8089e80498 100644 --- a/myisammrg/myrg_rprev.c +++ b/myisammrg/myrg_rprev.c @@ -29,7 +29,11 @@ int myrg_rprev(MYRG_INFO *info, byte *buf, int inx) if ((err=mi_rprev(info->current_table->table,NULL,inx))) { if (err == HA_ERR_END_OF_FILE) + { queue_remove(&(info->by_key),0); + if (!info->by_key.elements) + return HA_ERR_END_OF_FILE; + } else return err; } @@ -40,16 +44,7 @@ int myrg_rprev(MYRG_INFO *info, byte *buf, int inx) queue_replaced(&(info->by_key)); } - /* next, let's finish myrg_rkey's initial scan */ - if ((err=_myrg_finish_scan(info, inx, HA_READ_KEY_OR_PREV))) - return err; - - if (!info->by_key.elements) - return HA_ERR_END_OF_FILE; - /* now, mymerge's read_prev is as simple as one queue_top */ mi=(info->current_table=(MYRG_TABLE *)queue_top(&(info->by_key)))->table; return mi_rrnd(mi,buf,mi->lastpos); } - - diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 9e8e1b81687..653e25af799 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -127,3 +127,6 @@ t3 CREATE TABLE `t3` ( `othr` int(11) NOT NULL default '0' ) TYPE=MRG_MyISAM UNION=(t1,t2) a +a b +1 1 +1 2 diff --git a/mysql-test/r/rpl000001.result b/mysql-test/r/rpl000001.result index a80fb618457..ebccb1e1e6b 100644 --- a/mysql-test/r/rpl000001.result +++ b/mysql-test/r/rpl000001.result @@ -7,5 +7,11 @@ sum(length(word)) 0 count(*) 10 +select_priv user +N blafasel2 +select_priv user +Y blafasel2 n 3456 +select_priv user +Y blafasel2 diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 0b14ba388ce..619ec1d58af 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -96,3 +96,16 @@ drop table t3,t2,t1; create table t1 (a int not null) type=merge; select * from t1; drop table t1; + +# +# Bug found by Monty. +# + +drop table if exists t3, t2, t1; +create table t1 (a int not null, b int not null, key(a,b)); +create table t2 (a int not null, b int not null, key(a,b)); +create table t3 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2); +insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6); +insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6); +flush tables; +select * from t3 where a=1 order by b limit 2; diff --git a/mysql-test/t/rpl000001.test b/mysql-test/t/rpl000001.test index 7989a679a70..99b5c3bfcae 100644 --- a/mysql-test/t/rpl000001.test +++ b/mysql-test/t/rpl000001.test @@ -71,10 +71,18 @@ connection master1; drop table t1; create table t1 (n int); insert into t1 values(3456); +use mysql; +insert into user (Host, User, Password) + VALUES ("10.10.10.%", "blafasel2", "blafasel2"); +select select_priv,user from mysql.user where user = 'blafasel2'; +update user set Select_priv = "Y" where User="blafasel2"; +select select_priv,user from mysql.user where user = 'blafasel2'; +use test; save_master_pos; connection slave; sync_with_master; select n from t1; +select select_priv,user from mysql.user where user = 'blafasel2'; connection master1; drop table t1; save_master_pos; |