diff options
Diffstat (limited to 'Docs/manual.texi')
| -rw-r--r-- | Docs/manual.texi | 768 |
1 files changed, 421 insertions, 347 deletions
diff --git a/Docs/manual.texi b/Docs/manual.texi index 8216f9ad09a..62b9bdb2134 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -121,6 +121,7 @@ distribution for that version. * Tutorial:: @strong{MySQL} Tutorial * Server:: @strong{MySQL} Server * Replication:: Replication +* Fulltext Search:: Fulltext Search * Performance:: Getting maximum performance from @strong{MySQL} * MySQL Benchmarks:: The @strong{MySQL} benchmark suite * Tools:: @strong{MySQL} Utilities @@ -410,6 +411,7 @@ MySQL Language Reference * SET OPTION:: @code{SET OPTION} syntax * SET TRANSACTION:: @code{SET TRANSACTION} syntax * GRANT:: @code{GRANT} and @code{REVOKE} syntax +* HANDLER:: @code{HANDLER} syntax * CREATE INDEX:: @code{CREATE INDEX} syntax * DROP INDEX:: @code{DROP INDEX} syntax * Comments:: Comment syntax @@ -497,7 +499,7 @@ MySQL Table Types * HEAP:: HEAP tables * BDB:: BDB or Berkeley_db tables * GEMINI:: GEMINI tables -* INNOBASE:: INNOBASE tables +* INNODB:: INNODB tables MyISAM Tables @@ -527,12 +529,12 @@ GEMINI Tables * GEMINI features:: * GEMINI TODO:: -INNOBASE Tables +INNODB Tables -* INNOBASE overview:: -* INNOBASE start:: INNOBASE startup options -* Using INNOBASE tables:: Using INNOBASE tables -* INNOBASE restrictions:: Some restrictions on @code{INNOBASE} tables: +* INNODB overview:: +* INNODB start:: INNODB startup options +* Using INNODB tables:: Using INNODB tables +* INNODB restrictions:: Some restrictions on @code{INNODB} tables: MySQL Tutorial @@ -602,6 +604,13 @@ Replication in MySQL * Replication FAQ:: Frequently Asked Questions about replication * Replication Problems:: Troubleshooting Replication. +MySQL Full-text Search + +* Fulltext Search:: +* Fulltext Fine-tuning:: +* Fulltext Features to Appear in MySQL 4.0:: +* Fulltext TODO:: + Getting Maximum Performance from MySQL * Optimize Basics:: Optimization overview @@ -884,15 +893,8 @@ How MySQL Compares to @code{mSQL} MySQL Internals * MySQL threads:: MySQL threads -* MySQL full-text search:: MySQL full-text search * MySQL test suite:: MySQL test suite -MySQL Full-text Search - -* Fulltext Fine-tuning:: -* Fulltext features to appear in MySQL 4.0:: -* Fulltext TODO:: - Credits * Developers:: @@ -2209,7 +2211,7 @@ The Berkeley DB code is very stable, but we are still improving the interface between @strong{MySQL} and BDB tables, so it will take some time before this is as tested as the other table types. -@item Innobase Tables -- Alpha +@item Innodb Tables -- Alpha This is a very recent addition to @code{MySQL} and is not very tested yet. @item Automatic recovery of MyISAM tables - Beta @@ -4129,12 +4131,12 @@ phone back within 48 hours to discuss @code{MySQL} related issues. @end itemize @cindex support, BDB Tables -@cindex support, INNOBASE Tables +@cindex support, INNODB Tables @cindex support, GEMINI Tables @node Table handler support, , Telephone support, Support @subsection Support for other table handlers -To get support for @code{BDB} tables, @code{INNOBASE} tables or +To get support for @code{BDB} tables, @code{INNODB} tables or @code{GEMINI} tables you have to pay an additional 30% on the standard support price for each of the table handlers you would like to have support for. @@ -5072,7 +5074,7 @@ extra options that you may want to use: @itemize @bullet @item --with-berkeley-db -@item --with-innobase +@item --with-innodb @item --with-raid @item --with-libwrap @item --with-named-z-lib (This is done for some of the binaries) @@ -7430,7 +7432,18 @@ go around this limit. If you see a dead @code{mysqld} daemon process with @code{ps}, this usually means that you have found a bug in @strong{MySQL} or you have a corrupted -table. @xref{Crashing}. +table. @xref{Crashing}. + +To get a core dump on Linux if mysqld dies with a SIGSEGV +signal, you can start mysqld with the @code{--core-file} option. Note +that you also probably need to raise the @code{core file size} by adding +@code{ulimit -c 1000000} to @code{safe_mysqld} or starting @code{safe_mysqld} +with @code{--core-file-sizes=1000000}. @xref{safe_mysqld}. + +@c the stuff below is really out of date - hardly anybody uses it anymore + +If you are using LinuxThreads and @code{mysqladmin shutdown} doesn't work, +you must upgrade to LinuxThreads Version 0.7.1 or newer. To get a core dump on Linux if mysqld dies with a SIGSEGV signal, you can start mysqld with the @code{--core-file} option. Note that you also probably @@ -9255,7 +9268,7 @@ above can always mail them to the developer's mailing list at @email{internals@@lists.mysql.com}. RPM distributions prior to @strong{MySQL} Version 3.22 are user-contributed. -Beginning with Version 3.22, some RPMs are generated by us at +Beginning with Version 3.22, the RPMs are generated by us at @strong{MySQL AB}. If you want to compile a debug version of @strong{MySQL}, you should add @@ -9807,8 +9820,8 @@ yourself with the different BDB specific startup options. @xref{BDB start}. If you are using Gemini tables, refer to the Gemini-specific startup options. @xref{GEMINI start}. -If you are using Innobase tables, refer to the Innobase-specific startup -options. @xref{INNOBASE start}. +If you are using Innodb tables, refer to the Innodb-specific startup +options. @xref{INNODB start}. @node Automatic start, Command-line options, Starting server, Post-installation @subsection Starting and Stopping MySQL Automatically @@ -11220,7 +11233,7 @@ issue. For those of our users who are concerned with or have wondered about transactions vis-a-vis @strong{MySQL}, there is a ``@strong{MySQL} way'' as we have outlined above. For those where safety is more important than speed, we recommend them to use the @code{BDB}, -@code{GEMINI} or @code{INNOBASE} tables for all their critical +@code{GEMINI} or @code{INNODB} tables for all their critical data. @xref{Table types}. One final note: We are currently working on a safe replication schema @@ -11448,11 +11461,11 @@ Entry level SQL92. ODBC levels 0-2. @cindex updating, tables @cindex @code{BDB} tables @cindex @code{GEMINI} tables -@cindex @code{INNOBASE} tables +@cindex @code{INNODB} tables The following mostly applies only for @code{ISAM}, @code{MyISAM}, and @code{HEAP} tables. If you only use transaction-safe tables (@code{BDB}, -@code{GEMINI} or @code{INNOBASE} tables) in an an update, you can do +@code{GEMINI} or @code{INNODB} tables) in an an update, you can do @code{COMMIT} and @code{ROLLBACK} also with @strong{MySQL}. @xref{COMMIT}. @@ -13641,6 +13654,7 @@ to restart @code{mysqld} with @code{--skip-grant-tables} to run * SET OPTION:: @code{SET OPTION} syntax * SET TRANSACTION:: @code{SET TRANSACTION} syntax * GRANT:: @code{GRANT} and @code{REVOKE} syntax +* HANDLER:: @code{HANDLER} syntax * CREATE INDEX:: @code{CREATE INDEX} syntax * DROP INDEX:: @code{DROP INDEX} syntax * Comments:: Comment syntax @@ -15550,7 +15564,7 @@ In @strong{MySQL} Version 3.23.23 or later, you can also create special @code{MyISAM} table type supports @code{FULLTEXT} indexes. They can be created only from @code{VARCHAR} and @code{TEXT} columns. Indexing always happens over the entire column and partial indexing is not -supported. See @ref{MySQL full-text search} for details. +supported. See @ref{Fulltext Search} for details. @cindex multi-column indexes @cindex indexes, multi-column @@ -16293,7 +16307,7 @@ For @code{MATCH ... AGAINST()} to work, a @strong{FULLTEXT} index must be created first. @xref{CREATE TABLE, , @code{CREATE TABLE}}. @code{MATCH ... AGAINST()} is available in @strong{MySQL} Version 3.23.23 or later. For details and usage examples -@pxref{MySQL full-text search}. +@pxref{Fulltext Search}. @end table @findex casts @@ -18469,7 +18483,7 @@ reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT table_options: - TYPE = @{BDB | HEAP | ISAM | INNOBASE | MERGE | MYISAM @} + TYPE = @{BDB | HEAP | ISAM | INNODB | MERGE | MYISAM @} or AUTO_INCREMENT = # or AVG_ROW_LENGTH = # or CHECKSUM = @{0 | 1@} @@ -18678,7 +18692,7 @@ In @strong{MySQL} Version 3.23.23 or later, you can also create special @code{MyISAM} table type supports @code{FULLTEXT} indexes. They can be created only from @code{VARCHAR} and @code{TEXT} columns. Indexing always happens over the entire column, partial indexing is not -supported. See @ref{MySQL full-text search} for details of operation. +supported. See @ref{Fulltext Search} for details of operation. @item The @code{FOREIGN KEY}, @code{CHECK}, and @code{REFERENCES} clauses don't @@ -18711,7 +18725,7 @@ The different table types are: @item GEMINI @tab Transaction-safe tables with row-level locking @xref{GEMINI}. @item HEAP @tab The data for this table is only stored in memory. @xref{HEAP}. @item ISAM @tab The original table handler. @xref{ISAM}. -@item INNOBASE @tab Transaction-safe tables with row locking. @xref{INNOBASE}. +@item INNODB @tab Transaction-safe tables with row locking. @xref{INNODB}. @item MERGE @tab A collection of MyISAM tables used as one table. @xref{MERGE}. @item MyISAM @tab The new binary portable table handler that is replacing ISAM. @xref{MyISAM}. @end multitable @@ -21124,7 +21138,7 @@ The following columns are returned: @item @code{Comment} @tab The comment used when creating the table (or some information why @strong{MySQL} couldn't access the table information). @end multitable -@code{INNOBASE} tables will report the free space in the tablespace +@code{INNODB} tables will report the free space in the tablespace in the table comment. @node SHOW STATUS, SHOW VARIABLES, SHOW TABLE STATUS, SHOW @@ -21330,7 +21344,7 @@ differ somewhat: | flush_time | 0 | | have_bdb | YES | | have_gemini | NO | -| have_innobase | YES | +| have_innodb | YES | | have_raid | YES | | have_ssl | NO | | init_file | | @@ -21517,9 +21531,9 @@ if @code{--skip-bdb} is used. @item @code{have_gemini} @code{YES} if @code{mysqld} supports Gemini tables. @code{DISABLED} if @code{--skip-gemini} is used. -@item @code{have_innobase} -@code{YES} if @code{mysqld} supports Innobase tables. @code{DISABLED} -if @code{--skip-innobase} is used. +@item @code{have_innodb} +@code{YES} if @code{mysqld} supports Innodb tables. @code{DISABLED} +if @code{--skip-innodb} is used. @item @code{have_raid} @code{YES} if @code{mysqld} supports the @code{RAID} option. @item @code{have_ssl} @@ -22278,7 +22292,7 @@ as soon as you execute an update, @strong{MySQL} will store the update on disk. If you are using transactions safe tables (like @code{BDB}, -@code{INNOBASE} or @code{GEMINI}), you can put @strong{MySQL} into +@code{INNODB} or @code{GEMINI}), you can put @strong{MySQL} into non-@code{autocommit} mode with the following command: @example @@ -22603,7 +22617,7 @@ You can set the default isolation level for @code{mysqld} with @findex GRANT @findex REVOKE -@node GRANT, CREATE INDEX, SET TRANSACTION, Reference +@node GRANT, HANDLER, SET TRANSACTION, Reference @section @code{GRANT} and @code{REVOKE} Syntax @example @@ -22831,11 +22845,52 @@ dropped only with explicit @code{REVOKE} commands or by manipulating the @strong{MySQL} grant tables. @end itemize +@findex HANDLER +@node HANDLER, CREATE INDEX, GRANT, Reference +@section @code{HANDLER} Syntax + +@example +HANDLER table OPEN [ AS alias ] +HANDLER table READ index @{ = | >= | <= | < @} (value1, value2, ... ) [ WHERE ... ] [LIMIT ... ] +HANDLER table READ index @{ FIRST | NEXT | PREV | LAST @} [ WHERE ... ] [LIMIT ... ] +HANDLER table READ @{ FIRST | NEXT @} [ WHERE ... ] [LIMIT ... ] +HANDLER table CLOSE +@end example + +The @code{HANDLER} statement provides direct access to @strong{MySQL} table +interface, bypassing SQL optimizer. Thus, it is faster then SELECT. + +The first form of @code{HANDLER} statement opens a table, making +in accessible via the following @code{HANDLER ... READ} routines. + +The second form fetches one (or, specified by @code{LIMIT} clause) row +where the index specified complies to the condition and @code{WHERE} +condition is met. If the index consists of several parts (spans over +several columns) the values are specified in comma-separated list, +providing values only for few first columns is possible. + +The third form fetches one (or, specified by @code{LIMIT} clause) row +from the table in index order, matching @code{WHERE} condition. + +The fourth form (without index specification) fetches one (or, specified +by @code{LIMIT} clause) row from the table in natural row order (as stored +in data file) matching @code{WHERE} condition. It is faster than +@code{HANDLER table READ index} when full table scan is desired. + +The last form closes the table, opened with @code{HANDLER ... OPEN}. + +@code{HANDLER} is somewhat low-level statement, for example it does not +provide consistency. That is @code{HANDLER ... OPEN} does @strong{not} +takes a snapshot of the table, and does @strong{not} locks the table. The +above means, that after @code{HANDLER ... OPEN} table data can be +modified (by this or other thread) and these modifications may appear only +partially in @code{HANDLER ... NEXT} or @code{HANDLER ... PREV} scans. + @cindex indexes @cindex indexes, multi-part @cindex multi-part index @findex CREATE INDEX -@node CREATE INDEX, DROP INDEX, GRANT, Reference +@node CREATE INDEX, DROP INDEX, HANDLER, Reference @section @code{CREATE INDEX} Syntax @example @@ -22883,7 +22938,7 @@ For more information about how @strong{MySQL} uses indexes, see @code{FULLTEXT} indexes can index only @code{VARCHAR} and @code{TEXT} columns, and only in @code{MyISAM} tables. @code{FULLTEXT} indexes are available in @strong{MySQL} Version 3.23.23 and later. -@ref{MySQL full-text search}. +@ref{Fulltext Search}. @findex DROP INDEX @node DROP INDEX, Comments, CREATE INDEX, Reference @@ -23105,7 +23160,7 @@ used them. @cindex @code{GEMINI} table type @cindex @code{HEAP} table type @cindex @code{ISAM} table type -@cindex @code{INNOBASE} table type +@cindex @code{INNODB} table type @cindex @code{MERGE} table type @cindex MySQL table types @cindex @code{MyISAM} table type @@ -23116,7 +23171,7 @@ used them. As of @strong{MySQL} Version 3.23.6, you can choose between three basic table formats (@code{ISAM}, @code{HEAP} and @code{MyISAM}. Newer @strong{MySQL} may support additional table type (@code{BDB}, -@code{GEMINI} or @code{INNOBASE}), depending on how you compile it. +@code{GEMINI} or @code{INNODB}), depending on how you compile it. When you create a new table, you can tell @strong{MySQL} which table type it should use for the table. @strong{MySQL} will always create a @@ -23131,7 +23186,7 @@ You can convert tables between different types with the @code{ALTER TABLE} statement. @xref{ALTER TABLE, , @code{ALTER TABLE}}. Note that @strong{MySQL} supports two different kinds of -tables. Transaction-safe tables (@code{BDB}, @code{INNOBASE} or +tables. Transaction-safe tables (@code{BDB}, @code{INNODB} or @code{GEMINI}) and not transaction-safe tables (@code{HEAP}, @code{ISAM}, @code{MERGE}, and @code{MyISAM}). @@ -23174,7 +23229,7 @@ of both worlds. * HEAP:: HEAP tables * BDB:: BDB or Berkeley_db tables * GEMINI:: GEMINI tables -* INNOBASE:: INNOBASE tables +* INNODB:: INNODB tables @end menu @node MyISAM, MERGE, Table types, Table types @@ -24084,7 +24139,7 @@ not trivial). @end itemize @cindex tables, @code{GEMINI} -@node GEMINI, INNOBASE, BDB, Table types +@node GEMINI, INNODB, BDB, Table types @section GEMINI Tables @menu @@ -24165,168 +24220,168 @@ limited by @code{gemini_connection_limit}. The default is 100 users. NuSphere is working on removing these limitations. -@node INNOBASE, , GEMINI, Table types -@section INNOBASE Tables +@node INNODB, , GEMINI, Table types +@section INNODB Tables @menu -* INNOBASE overview:: -* INNOBASE start:: INNOBASE startup options -* Using INNOBASE tables:: Using INNOBASE tables -* INNOBASE restrictions:: Some restrictions on @code{INNOBASE} tables: +* INNODB overview:: +* INNODB start:: INNODB startup options +* Using INNODB tables:: Using INNODB tables +* INNODB restrictions:: Some restrictions on @code{INNODB} tables: @end menu -@node INNOBASE overview, INNOBASE start, INNOBASE, INNOBASE -@subsection INNOBASE Tables overview +@node INNODB overview, INNODB start, INNODB, INNODB +@subsection INNODB Tables overview -Innobase tables are included in the @strong{MySQL} source distribution +Innodb tables are included in the @strong{MySQL} source distribution starting from 3.23.34 and will be activated in the @strong{MySQL}-max binary. If you have downloaded a binary version of @strong{MySQL} that includes -support for Innobase, simply follow the instructions for +support for Innodb, simply follow the instructions for installing a binary version of @strong{MySQL}. @xref{Installing binary}. -To compile @strong{MySQL} with Innobase support, download @strong{MySQL} +To compile @strong{MySQL} with Innodb support, download @strong{MySQL} 3.23.34 or newer and configure @code{MySQL} with the -@code{--with-innobase} option. @xref{Installing source}. +@code{--with-innodb} option. @xref{Installing source}. @example cd /path/to/source/of/mysql-3.23.34 -./configure --with-innobase +./configure --with-innodb @end example -Innobase provides @strong{MySQL} with a transaction safe table handler with -commit, rollback, and crash recovery capabilities. Innobase does +Innodb provides @strong{MySQL} with a transaction safe table handler with +commit, rollback, and crash recovery capabilities. Innodb does locking on row level, and also provides an Oracle-style consistent non-locking read in @code{SELECTS}, which increases transaction -concurrency. There is neither need for lock escalation in Innobase, -because row level locks in Innobase fit in very small space. +concurrency. There is neither need for lock escalation in Innodb, +because row level locks in Innodb fit in very small space. -Innobase is a table handler that is under the GNU GPL License Version 2 -(of June 1991). In the source distribution of @strong{MySQL}, Innobase +Innodb is a table handler that is under the GNU GPL License Version 2 +(of June 1991). In the source distribution of @strong{MySQL}, Innodb appears as a subdirectory. -@node INNOBASE start, Using INNOBASE tables, INNOBASE overview, INNOBASE -@subsection INNOBASE startup options +@node INNODB start, Using INNODB tables, INNODB overview, INNODB +@subsection INNODB startup options -To use Innobase tables you must specify configuration parameters +To use Innodb tables you must specify configuration parameters in the @strong{MySQL} configuration file in the @code{[mysqld]} section of the configuration file. Below is an example of possible configuration -parameters in my.cnf for Innobase: +parameters in my.cnf for Innodb: @example -innobase_data_home_dir = /usr/local/mysql/var -innobase_log_group_home_dir = /usr/local/mysql/var -innobase_log_arch_dir = /usr/local/mysql/var -innobase_data_file_path = ibdata1:25M;ibdata2:37M;ibdata3:100M;ibdata4:300M -set-variable = innobase_mirrored_log_groups=1 -set-variable = innobase_log_files_in_group=3 -set-variable = innobase_log_file_size=5M -set-variable = innobase_log_buffer_size=8M -innobase_flush_log_at_trx_commit=1 -innobase_log_archive=0 -set-variable = innobase_buffer_pool_size=16M -set-variable = innobase_additional_mem_pool_size=2M -set-variable = innobase_file_io_threads=4 -set-variable = innobase_lock_wait_timeout=50 +innodb_data_home_dir = /usr/local/mysql/var +innodb_log_group_home_dir = /usr/local/mysql/var +innodb_log_arch_dir = /usr/local/mysql/var +innodb_data_file_path = ibdata1:25M;ibdata2:37M;ibdata3:100M;ibdata4:300M +set-variable = innodb_mirrored_log_groups=1 +set-variable = innodb_log_files_in_group=3 +set-variable = innodb_log_file_size=5M +set-variable = innodb_log_buffer_size=8M +innodb_flush_log_at_trx_commit=1 +innodb_log_archive=0 +set-variable = innodb_buffer_pool_size=16M +set-variable = innodb_additional_mem_pool_size=2M +set-variable = innodb_file_io_threads=4 +set-variable = innodb_lock_wait_timeout=50 @end example The meanings of the configuration parameters are the following: @multitable @columnfractions .30 .70 -@item @code{innobase_data_home_dir} @tab -The common part of the directory path for all innobase data files. -@item @code{innobase_data_file_path} @tab +@item @code{innodb_data_home_dir} @tab +The common part of the directory path for all innodb data files. +@item @code{innodb_data_file_path} @tab Paths to individual data files and their sizes. The full directory path -to each data file is acquired by concatenating innobase_data_home_dir to +to each data file is acquired by concatenating innodb_data_home_dir to the paths specified here. The file sizes are specified in megabytes, hence the 'M' after the size specification above. Do not set a file size bigger than 4000M, and on most operating systems not bigger than 2000M. -innobase_mirrored_log_groups Number of identical copies of log groups we +innodb_mirrored_log_groups Number of identical copies of log groups we keep for the database. Currently this should be set to 1. -@item @code{innobase_log_group_home_dir} @tab -Directory path to Innobase log files. -@item @code{innobase_log_files_in_group} @tab -Number of log files in the log group. Innobase writes to the files in a +@item @code{innodb_log_group_home_dir} @tab +Directory path to Innodb log files. +@item @code{innodb_log_files_in_group} @tab +Number of log files in the log group. Innodb writes to the files in a circular fashion. Value 3 is recommended here. -@item @code{innobase_log_file_size} @tab +@item @code{innodb_log_file_size} @tab Size of each log file in a log group in megabytes. Sensible values range from 1M to the size of the buffer pool specified below. The bigger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk i/o. But bigger log files also mean that recovery will be slower in case of a crash. File size restriction as for a data file. -@item @code{innobase_log_buffer_size} @tab -The size of the buffer which Innobase uses to write log to the log files +@item @code{innodb_log_buffer_size} @tab +The size of the buffer which Innodb uses to write log to the log files on disk. Sensible values range from 1M to half the combined size of log files. A big log buffer allows large transactions to run without a need to write the log to disk until the transaction commit. Thus, if you have big transactions, making the log buffer big will save disk i/o. -@item @code{innobase_flush_log_at_trx_commit} @tab +@item @code{innodb_flush_log_at_trx_commit} @tab Normally this is set to 1, meaning that at a transaction commit the log is flushed to disk, and the modifications made by the transaction become permanent, and survive a database crash. If you are willing to compromise this safety, and you are running small transactions, you may set this to 0 to reduce disk i/o to the logs. -@item @code{innobase_log_arch_dir} @tab +@item @code{innodb_log_arch_dir} @tab The directory where fully written log files would be archived if we used log archiving. The value of this parameter should currently be set the -same as @code{innobase_log_group_home_dir}. -@item @code{innobase_log_archive} @tab +same as @code{innodb_log_group_home_dir}. +@item @code{innodb_log_archive} @tab This value should currently be set to 0. As recovery from a backup is done by @strong{MySQL} using its own log files, there is currently no need -to archive Innobase log files. -@item @code{innobase_buffer_pool_size} @tab -The size of the memory buffer Innobase uses to cache data and indexes of +to archive Innodb log files. +@item @code{innodb_buffer_pool_size} @tab +The size of the memory buffer Innodb uses to cache data and indexes of its tables. The bigger you set this the less disk i/o is needed to access data in tables. On a dedicated database server you may set this parameter up to 90 % of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system. -@item @code{innobase_additional_mem_pool_size} @tab -Size of a memory pool Innobase uses to store data dictionary information +@item @code{innodb_additional_mem_pool_size} @tab +Size of a memory pool Innodb uses to store data dictionary information and other internal data structures. A sensible value for this might be 2M, but the more tables you have in your application the more you will -need to allocate here. If Innobase runs out of memory in this pool, it +need to allocate here. If Innodb runs out of memory in this pool, it will start to allocate memory from the operating system, and write warning messages to the @strong{MySQL} error log. -@item @code{innobase_file_io_threads} @tab -Number of file i/o threads in Innobase. Normally, this should be 4, but +@item @code{innodb_file_io_threads} @tab +Number of file i/o threads in Innodb. Normally, this should be 4, but on Windows NT disk i/o may benefit from a larger number. -@item @code{innobase_lock_wait_timeout} @tab -Timeout in seconds an Innobase transaction may wait for a lock before -being rolled back. Innobase automatically detects transaction deadlocks +@item @code{innodb_lock_wait_timeout} @tab +Timeout in seconds an Innodb transaction may wait for a lock before +being rolled back. Innodb automatically detects transaction deadlocks in its own lock table and rolls back the transaction. If you use @code{LOCK TABLES} command, or other transaction safe table handlers -than Innobase in the same transaction, then a deadlock may arise which -Innobase cannot notice. In cases like this the timeout is useful to +than Innodb in the same transaction, then a deadlock may arise which +Innodb cannot notice. In cases like this the timeout is useful to resolve the situation. @end multitable -@node Using INNOBASE tables, INNOBASE restrictions, INNOBASE start, INNOBASE -@subsection Using INNOBASE tables +@node Using INNODB tables, INNODB restrictions, INNODB start, INNODB +@subsection Using INNODB tables -Technically, Innobase is a database backend placed under @strong{MySQL}. -Innobase has its own buffer pool for caching data and indexes in main -memory. Innobase stores its tables and indexes in a tablespace, which +Technically, Innodb is a database backend placed under @strong{MySQL}. +Innodb has its own buffer pool for caching data and indexes in main +memory. Innodb stores its tables and indexes in a tablespace, which may consist of several files. This is different from, for example, @code{MyISAM} tables where each table is stored as a separate file. -To create a table in the Innobase format you must specify -@code{TYPE = INNOBASE} in the table creation SQL command: +To create a table in the Innodb format you must specify +@code{TYPE = INNODB} in the table creation SQL command: @example -CREATE TABLE CUSTOMERS (A INT, B CHAR (20), INDEX (A)) TYPE = INNOBASE; +CREATE TABLE CUSTOMERS (A INT, B CHAR (20), INDEX (A)) TYPE = INNODB; @end example A consistent non-locking read is the default locking behavior when you -do a @code{SELECT} from an Innobase table. For a searched update and an +do a @code{SELECT} from an Innodb table. For a searched update and an insert row level exclusive locking is performed. -You can query the amount of free space in the Innobase tablespace (= +You can query the amount of free space in the Innodb tablespace (= data files you specified in my.cnf) by issuing the table status command of @strong{MySQL} for any table you have created with @code{TYPE = -INNOBASE}. Then the amount of free space in the tablespace appears in +INNODB}. Then the amount of free space in the tablespace appears in the table comment section in the output of SHOW. An example: @example @@ -24334,45 +24389,45 @@ SHOW TABLE STATUS FROM TEST LIKE 'CUSTOMER' @end example if you have created a table of name CUSTOMER in a database you have named -TEST. Note that the statistics SHOW gives about Innobase tables +TEST. Note that the statistics SHOW gives about Innodb tables are only approximate: they are used in SQL optimization. Table and index reserved sizes in bytes are accurate, though. -NOTE: DROP DATABASE does not currently work for Innobase tables! +NOTE: DROP DATABASE does not currently work for Innodb tables! You must drop the tables individually. Note that in addition to your tables, the rollback segment uses space from the tablespace. -Since Innobase is a multiversioned database, it must keep information +Since Innodb is a multiversioned database, it must keep information of old versions of rows in the tablespace. This information is stored in a data structure called a rollback segment, like in Oracle. In contrast to Oracle, you do not need to configure the rollback segment in any way in -Innobase. If you issue SELECTs, which by default do a consistent read in -Innobase, remember to commit your transaction regularly. Otherwise +Innodb. If you issue SELECTs, which by default do a consistent read in +Innodb, remember to commit your transaction regularly. Otherwise the rollback segment will grow because it has to preserve the information -needed for further consistent reads in your transaction: in Innobase +needed for further consistent reads in your transaction: in Innodb all consistent reads within one transaction will see the same timepoint snapshot of the database: the reads are also 'consistent' with respect to each other. -Some Innobase errors: If you run out of file space in the tablespace, +Some Innodb errors: If you run out of file space in the tablespace, you will get the @strong{MySQL} 'Table is full' error. If you want to make your tablespace bigger, you have to shut down @strong{MySQL} and add a new datafile specification to @file{my.conf}, to the -@code{innobase_data_file_path} parameter. +@code{innodb_data_file_path} parameter. A transaction deadlock or a timeout in a lock wait will give 'Table handler error 1000000'. -Contact information of Innobase Oy, producer of the Innobase engine: +Contact information of Innobase Oy, producer of the Innodb engine: Website: @uref{http://www.innobase.fi}. @email{Heikki.Tuuri@@innobase.inet.fi} @example phone: 358-9-6969 3250 (office) 358-40-5617367 (mobile) -Innobase Oy Inc. +Innodb Oy Inc. World Trade Center Helsinki Aleksanterinkatu 17 P.O.Box 800 @@ -24380,8 +24435,8 @@ P.O.Box 800 Finland @end example -@node INNOBASE restrictions, , Using INNOBASE tables, INNOBASE -@subsection Some restrictions on @code{INNOBASE} tables: +@node INNODB restrictions, , Using INNODB tables, INNODB +@subsection Some restrictions on @code{INNODB} tables: @itemize @bullet @item @@ -24392,9 +24447,9 @@ rows, one by one, which isn't that fast. @item The maximum blob size is 8000 bytes. @item -Before dropping a database with @code{INNOBASE} tables one has to drop +Before dropping a database with @code{INNODB} tables one has to drop the individual tables first. If one doesn't do that, the space in the -Innobase table space will not be reclaimed. +Innodb table space will not be reclaimed. @end itemize @cindex tutorial @@ -25336,7 +25391,7 @@ this may be different than what you are used to.) @item Claws @tab Gwen @tab cat @tab m @tab 1994-03-17 @tab @item Buffy @tab Harold @tab dog @tab f @tab 1989-05-13 @tab @item Fang @tab Benny @tab dog @tab m @tab 1990-08-27 @tab -@item Bowser @tab Diane @tab dog @tab m @tab 1998-08-31 @tab 1995-07-29 +@item Bowser @tab Diane @tab dog @tab m @tab 1989-08-31 @tab 1995-07-29 @item Chirpy @tab Gwen @tab bird @tab f @tab 1998-09-11 @tab @item Whistler @tab Gwen @tab bird @tab @tab 1997-12-09 @tab @item Slim @tab Benny @tab snake @tab m @tab 1996-04-29 @tab @@ -27129,7 +27184,7 @@ tables}. @cindex increasing, speed @cindex speed, increasing @cindex databases, replicating -@node Replication, Performance, Server, Top +@node Replication, Fulltext Search, Server, Top @chapter Replication in MySQL @menu @@ -28108,10 +28163,208 @@ Once you have collected the evidence on the phantom problem, try hard to isolate it into a separate test case first. Then report the problem to @email{bugs@@lists.mysql.com} with as much info as possible. +@cindex searching, full-text +@cindex full-text search +@cindex FULLTEXT +@node Fulltext Search, Performance, Replication, Top +@chapter MySQL Full-text Search + +Since Version 3.23.23, @strong{MySQL} has support for full-text indexing +and searching. Full-text indexes in @strong{MySQL} are an index of type +@code{FULLTEXT}. @code{FULLTEXT} indexes can be created from @code{VARCHAR} +and @code{TEXT} columns at @code{CREATE TABLE} time or added later with +@code{ALTER TABLE} or @code{CREATE INDEX}. For large datasets, adding +@code{FULLTEXT} index with @code{ALTER TABLE} (or @code{CREATE INDEX}) would +be much faster than inserting rows into the empty table with a @code{FULLTEXT} +index. + +Full-text search is performed with the @code{MATCH} function. + +@example +mysql> CREATE TABLE t (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); +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'); +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 | ++---------------------------+-------------------------------+ +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 +@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} +clause (see example above) the rows returned are automatically sorted with +relevance decreasing. Relevance is a non-negative floating-point number. +Zero relevance means no similarity. Relevance is computed based on the +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. + +Every correct word in the collection and in the query is weighted, +according to its significance in the query or collection. This way, a +word that is present in many documents will have lower weight (and may +even have a zero weight), because it has lower semantic value in this +particular collection. Otherwise, if the word is rare, it will receive a +higher weight. The weights of the words are then combined to compute the +relevance of the row. + +Such a technique works best with large collections (in fact, it was +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. + +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}. + +@menu +* Fulltext Fine-tuning:: +* Fulltext Features to Appear in MySQL 4.0:: +* Fulltext TODO:: +@end menu + +@node Fulltext Fine-tuning, Fulltext Features to Appear in MySQL 4.0, , Fulltext Search +@section Fine-tuning MySQL Full-text Search + +Unfortunately, full-text search has no user-tunable parameters yet, +although adding some is very high on the TODO. However, if you have a +@strong{MySQL} source distribution (@xref{Installing source}.), you can +somewhat alter the full-text search behavior. + +Note that full-text search was carefully tuned for the best searching +effectiveness. Modifying the default behavior will, in most cases, +only make the search results worse. Do not alter the @strong{MySQL} sources +unless you know what you are doing! + +@itemize + +@item +Minimal length of word to be indexed is defined in +@code{myisam/ftdefs.h} file by the line +@example +#define MIN_WORD_LEN 4 +@end example +Change it to the value you prefer, recompile @strong{MySQL}, and rebuild +your @code{FULLTEXT} indexes. + +@item +The stopword list is defined in @code{myisam/ft_static.c} +Modify it to your taste, recompile @strong{MySQL} and rebuild +your @code{FULLTEXT} indexes. + +@item +The 50% threshold is caused by the particular weighting scheme chosen. To +disable it, change the following line in @code{myisam/ftdefs.h}: +@example +#define GWS_IN_USE GWS_PROB +@end example +to +@example +#define GWS_IN_USE GWS_FREQ +@end example +and recompile @strong{MySQL}. +There is no need to rebuild the indexes in this case. + +@end itemize + +@node Fulltext Features to Appear in MySQL 4.0, Fulltext TODO, Fulltext Fine-tuning, Fulltext Search +@section New Features of Full-text Search to Appear in MySQL 4.0 + +This section includes a list of the fulltext features that are already +implemented in the 4.0 tree. It explains +@strong{More functions for full-text search} entry of @ref{TODO MySQL 4.0}. + +@itemize @bullet +@item @code{REPAIR TABLE} with @code{FULLTEXT} indexes, +@code{ALTER TABLE} with @code{FULLTEXT} indexes, and +@code{OPTIMIZE TABLE} with @code{FULLTEXT} indexes are now +up to 100 times faster. + +@item @code{MATCH ... AGAINST} now supports the following +@strong{boolean operators}: + +@itemize @bullet +@item @code{+}word means the that word @strong{must} be present in every +row returned. +@item @code{-}word means the that word @strong{must not} be present in every +row returned. +@item @code{<} and @code{>} can be used to decrease and increase word +weight in the query. +@item @code{~} can be used to assign a @strong{negative} weight to a noise +word. +@item @code{*} is a truncation operator. +@end itemize + +Boolean search utilizes a more simplistic way of calculating the relevance, +that does not have a 50% threshold. + +@item Searches are now up to 2 times faster due to optimized search algorithm. + +@item Utility program @code{ft_dump} added for low-level @code{FULLTEXT} +index operations (querying/dumping/statistics). + +@end itemize + +@node Fulltext TODO, , Fulltext Features to Appear in MySQL 4.0, Fulltext Search +@section Full-text Search TODO + +@itemize @bullet +@item Make all operations with @code{FULLTEXT} index @strong{faster}. +@item Support for braces @code{()} in boolean full-text search. +@item Support for "always-index words". They could be any strings +the user wants to treat as words, examples are "C++", "AS/400", "TCP/IP", etc. +@item Support for full-text search in @code{MERGE} tables. +@item Support for multi-byte charsets. +@item Make stopword list to depend of the language of the data. +@item Stemming (dependent of the language of the data, of course). +@item Generic user-supplyable UDF (?) preparser. +@item Make the model more flexible (by adding some adjustable +parameters to @code{FULLTEXT} in @code{CREATE/ALTER TABLE}). +@end itemize @cindex performance, maximizing @cindex optimization -@node Performance, MySQL Benchmarks, Replication, Top +@node Performance, MySQL Benchmarks, Fulltext Search, Top @chapter Getting Maximum Performance from MySQL Optimization is a complicated task because it ultimately requires @@ -28322,8 +28575,9 @@ get bigger. Running with @code{--log-bin} makes @strong{[MySQL} 1 % slower. @item -Compiling without frame pointers @code{-fomit-frame-pointer} with gcc makes -@strong{MySQL} 1% faster. +Compiling on Linux-x86 using gcc without frame pointers +@code{-fomit-frame-pointer} or @code{-fomit-frame-pointer -ffixed-ebp} +@code{mysqld} 1-4% faster. @end itemize The @strong{MySQL}-Linux distribution provided by @strong{MySQL AB} used @@ -31297,8 +31551,8 @@ mysqldump --all-databases > all_databases.sql @code{mysqlhotcopy} is a perl script that uses @code{LOCK TABLES}, @code{FLUSH TABLES} and @code{cp} or @code{scp} to quickly make a backup of a database. It's the fastest way to make a backup of the database, -but it can only be run on the same machine where the database directories -are. +of single tables but it can only be run on the same machine where the +database directories are. @example mysqlhotcopy db_name [/path/to/new_directory] @@ -40553,11 +40807,10 @@ This is a relatively low traffic list, in comparison with @menu * MySQL threads:: MySQL threads -* MySQL full-text search:: MySQL full-text search * MySQL test suite:: MySQL test suite @end menu -@node MySQL threads, MySQL full-text search, MySQL internals, MySQL internals +@node MySQL threads, MySQL test suite, , MySQL internals @section MySQL Threads The @strong{MySQL} server creates the following threads: @@ -40604,208 +40857,9 @@ started to read and apply updates from the master. @code{mysqladmin processlist} only shows the connection, @code{INSERT DELAYED}, and replication threads. -@cindex searching, full-text -@cindex full-text search -@cindex FULLTEXT -@node MySQL full-text search, MySQL test suite, MySQL threads, MySQL internals -@section MySQL Full-text Search - -Since Version 3.23.23, @strong{MySQL} has support for full-text indexing -and searching. Full-text indexes in @strong{MySQL} are an index of type -@code{FULLTEXT}. @code{FULLTEXT} indexes can be created from @code{VARCHAR} -and @code{TEXT} columns at @code{CREATE TABLE} time or added later with -@code{ALTER TABLE} or @code{CREATE INDEX}. For large datasets, adding -@code{FULLTEXT} index with @code{ALTER TABLE} (or @code{CREATE INDEX}) would -be much faster than inserting rows into the empty table with a @code{FULLTEXT} -index. - -Full-text search is performed with the @code{MATCH} function. - -@example -mysql> CREATE TABLE t (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); -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'); -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 | -+---------------------------+-------------------------------+ -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 -@strong{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} -clause (see example above) the rows returned are automatically sorted with -relevance decreasing. Relevance is a non-negative floating-point number. -Zero relevance means no similarity. Relevance is computed based on the -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. - -Every correct word in the collection and in the query is weighted, -according to its significance in the query or collection. This way, a -word that is present in many documents will have lower weight (and may -even have a zero weight), because it has lower semantic value in this -particular collection. Otherwise, if the word is rare, it will receive a -higher weight. The weights of the words are then combined to compute the -relevance of the row. - -Such a technique works best with large collections (in fact, it was -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. - -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}. - -@menu -* Fulltext Fine-tuning:: -* Fulltext features to appear in MySQL 4.0:: -* Fulltext TODO:: -@end menu - -@node Fulltext Fine-tuning, Fulltext features to appear in MySQL 4.0, MySQL full-text search, MySQL full-text search -@subsection Fine-tuning MySQL Full-text Search - -Unfortunately, full-text search has no user-tunable parameters yet, -although adding some is very high on the TODO. However, if you have a -@strong{MySQL} source distribution (@xref{Installing source}.), you can -somewhat alter the full-text search behavior. - -Note that full-text search was carefully tuned for the best searching -effectiveness. Modifying the default behavior will, in most cases, -only make the search results worse. Do not alter the @strong{MySQL} sources -unless you know what you are doing! - -@itemize - -@item -Minimal length of word to be indexed is defined in -@code{myisam/ftdefs.h} file by the line -@example -#define MIN_WORD_LEN 4 -@end example -Change it to the value you prefer, recompile @strong{MySQL}, and rebuild -your @code{FULLTEXT} indexes. - -@item -The stopword list is defined in @code{myisam/ft_static.c} -Modify it to your taste, recompile @strong{MySQL} and rebuild -your @code{FULLTEXT} indexes. - -@item -The 50% threshold is caused by the particular weighting scheme chosen. To -disable it, change the following line in @code{myisam/ftdefs.h}: -@example -#define GWS_IN_USE GWS_PROB -@end example -to -@example -#define GWS_IN_USE GWS_FREQ -@end example -and recompile @strong{MySQL}. -There is no need to rebuild the indexes in this case. - -@end itemize - -@node Fulltext features to appear in MySQL 4.0, Fulltext TODO, Fulltext Fine-tuning, MySQL full-text search -@subsection New Features of Full-text Search to Appear in MySQL 4.0 - -This section includes a list of the fulltext features that are already -implemented in the 4.0 tree. It explains -@strong{More functions for full-text search} entry of @ref{TODO MySQL 4.0}. - -@itemize @bullet -@item @code{REPAIR TABLE} with @code{FULLTEXT} indexes, -@code{ALTER TABLE} with @code{FULLTEXT} indexes, and -@code{OPTIMIZE TABLE} with @code{FULLTEXT} indexes are now -up to 100 times faster. - -@item @code{MATCH ... AGAINST} now supports the following -@strong{boolean operators}: - -@itemize @bullet -@item @code{+}word means the that word @strong{must} be present in every -row returned. -@item @code{-}word means the that word @strong{must not} be present in every -row returned. -@item @code{<} and @code{>} can be used to decrease and increase word -weight in the query. -@item @code{~} can be used to assign a @strong{negative} weight to a noise -word. -@item @code{*} is a truncation operator. -@end itemize - -Boolean search utilizes a more simplistic way of calculating the relevance, -that does not have a 50% threshold. - -@item Searches are now up to 2 times faster due to optimized search algorithm. - -@item Utility program @code{ft_dump} added for low-level @code{FULLTEXT} -index operations (querying/dumping/statistics). - -@end itemize - -@node Fulltext TODO, , Fulltext features to appear in MySQL 4.0, MySQL full-text search -@subsection Full-text Search TODO - -@itemize @bullet -@item Make all operations with @code{FULLTEXT} index @strong{faster}. -@item Support for braces @code{()} in boolean fulltext search. -@item Support for "always-index words". They could be any strings -the user wants to treat as words, examples are "C++", "AS/400", "TCP/IP", etc. -@item Support for fulltext search in @code{MERGE} tables. -@item Support for multi-byte charsets. -@item Make stopword list to depend of the language of the data. -@item Stemming (dependent of the language of the data, of course). -@item Generic user-suppied UDF (?) preparser. -@item Make the model more flexible (by adding some adjustable -parameters to @code{FULLTEXT} in @code{CREATE/ALTER TABLE}). -@end itemize - @cindex mysqltest, MySQL Test Suite @cindex testing mysqld, mysqltest -@node MySQL test suite, , MySQL full-text search, MySQL internals +@node MySQL test suite, , MySQL threads, MySQL internals @section MySQL Test Suite Until recently, our main full-coverage test suite was based on proprietary @@ -42773,7 +42827,7 @@ A new ISAM library which is tuned for SQL and supports large files. @item @strong{BerkeleyDB} or @strong{BDB} Uses the Berkeley DB library from Sleepycat Software to implement transaction-safe tables. -@item @strong{Innobase} +@item @strong{Innodb} A transaction-safe table handler that supports row level locking, and many Oracle-like features. @c change "three" to "four" above when uncommenting this @@ -42849,6 +42903,11 @@ not yet 100% confident in this code. @appendixsubsec Changes in release 3.23.37 @itemize @bullet @item +Changed @code{INNOBASE} to @code{INNODB} (because the @code{INNOBASE} +name was already used). Note that all @code{configure} options and +@code{mysqld} start options are now using @code{innodb} instead of +@code{innobase}. +@item Fixed bug when using indexes on @code{CHAR(255) NULL} columns. @item Slave thread will now be started even if @code{master-host} is not set, as @@ -48061,7 +48120,7 @@ the @code{.MYD} file. Better replication. @item More functions for full-text search. -@xref{Fulltext features to appear in MySQL 4.0}. +@xref{Fulltext Features to Appear in MySQL 4.0}. @item Character set casts and syntax for handling multiple character sets. @item @@ -48185,6 +48244,19 @@ Check why MIT-pthreads @code{ctime()} doesn't work on some FreeBSD systems. Add an @code{IMAGE} option to @code{LOAD DATA INFILE} to not update @code{TIMESTAMP} and @code{AUTO_INCREMENT} fields. @item +Added @code{LOAD DATE INFILE.. UPDATE} syntax. +@itemize @bullet +@item +For tables with primary keys, if the data contains the primary key, +entries matching that primary key are updated from the remainder of the +columns. However, columns MISSING from the incoming data feed are not +touched. +@item +For tables tables with primary keys that are missing some part of the key +in the incoming data stream, or that have no primary key, the feed is +treated as a @code{LOAD DATA INFILE ... REPLACE INTO} is now. +@end itemize +@item Make @code{LOAD DATA INFILE} understand syntax like: @example LOAD DATA INFILE 'file_name.txt' INTO TABLE tbl_name @@ -48699,8 +48771,10 @@ setting the @code{DBI_TRACE} environment variable. On some operating systems, the error log will contain a stack trace if @code{mysqld} dies unexpectedly. You can use this to find out where (and maybe why) @code{mysqld} died. @xref{Error log}. To get a stack trace, -you should NOT compile @code{mysqld} with the -@code{-fomit-frame-pointer} option to gcc. +you should NOT compile @code{mysqld} with the @code{-fomit-frame-pointer} +option to gcc. On Linux-x86 you can use +@code{-fomit-frame-pointer -ffixed-ebp} to get both speed and a reasonable +accurate stack trace. @xref{Compiling for debugging}. If the error file contains something like the following: |