Below is the list of changes that have just been committed into a local
mysqldoc repository of jon. When jon does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.3015 05/07/14 21:56:46 jon@stripped +1 -0
Edits/fixes for RefMan-5.0 version of
Storage Engines chapter.
refman-5.0/storage-engines.xml
1.6 05/07/14 21:56:45 jon@stripped +420 -421
Edits/fixes for RefMan-5.0 version.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: jon
# Host: gigan.
# Root: /home/jon/bk/mysqldoc
--- 1.5/refman-5.0/storage-engines.xml 2005-07-09 01:47:07 +10:00
+++ 1.6/refman-5.0/storage-engines.xml 2005-07-14 21:56:45 +10:00
@@ -140,75 +140,72 @@
<itemizedlist>
<listitem><para>
- The original storage engine was <literal>ISAM</literal>, which
- managed non-transactional tables. This engine has been replaced by
- <literal>MyISAM</literal>, and has been removed altogether in MySQL
- 5.0. The <literal>ISAM</literal> storage engine is also not supported
- by embedded MySQL. It is mentioned here for historical purposes only.
+ <literal>MyISAM</literal> manages non-transactional tables. It
+ provides high-speed storage and retrieval, as well as fulltext
+ searching capabilities. <literal>MyISAM</literal> is supported in all
+ MySQL configurations, and is the default storage engine unless you
+ have configured MySQL to use a different one by default.
</para></listitem>
<listitem><para>
- In MySQL 3.23.0, the <literal>MyISAM</literal> and
- <literal>HEAP</literal> storage engines were introduced.
- <literal>MyISAM</literal> is an improved replacement for
- <literal>ISAM</literal>. The <literal>HEAP</literal> storage engine
- provides in-memory tables. The <literal>MERGE</literal> storage
- engine was added in MySQL 3.23.25. It allows a collection of
- identical <literal>MyISAM</literal> tables to be handled as a single
- table. All three of these storage engines handle non-transactional
- tables, and all are included in MySQL by default. Note that the
- <literal>HEAP</literal> storage engine has been renamed the
- <literal>MEMORY</literal> engine.
- </para></listitem>
+ The <literal>MEMORY</literal> storage engine provides in-memory
+ tables. The <literal>MERGE</literal> storage engine allows a
+ collection of identical <literal>MyISAM</literal> tables to be
+ handled as a single table. Like <literal>MyISAM</literal>, the
+ <literal>MEMORY</literal> and <literal>MERGE</literal> storage
+ engines handle non-transactional tables, and both are also
+ included in MySQL by default.
+ </para>
+
+ <para><emphasis role="bold">Note</emphasis>: The
+ <literal>MEMORY</literal> storage engine was formerly known as the
+ <literal>HEAP</literal> engine.
+ </para>
+ </listitem>
<listitem><para>
The <literal>InnoDB</literal> and <literal>BDB</literal> storage
- engines that handle transaction-safe tables were introduced in later
- versions of MySQL 3.23. Both are available in source distributions as
- of MySQL 3.23.34a. <literal>BDB</literal> is included in MySQL-Max
- binary distributions on those operating systems that support it.
- <literal>InnoDB</literal> also is included in MySQL-Max binary
- distributions for MySQL 3.23. Beginning with MySQL 4.0,
- <literal>InnoDB</literal> is included by default in all MySQL binary
- distributions. In source distributions, you can enable or disable
- either engine by configuring MySQL as you like.
+ engines provide transaction-safe tables. <literal>BDB</literal> is
+ included in MySQL-Max binary distributions on those operating systems
+ that support it. <literal>InnoDB</literal> also included by default
+ in all MySQL 5.0 binary distributions. In source distributions, you
+ can enable or disable either engine by configuring MySQL as you like.
</para></listitem>
<listitem><para>
- The <literal>EXAMPLE</literal> storage engine was added in MySQL
- 4.1.3. It is a ``stub'' engine that does nothing. You can create
- tables with this engine, but no data can be stored into them or
- retrieved from them. The purpose of this engine is to serve as an
- example in the MySQL source code that illustrates how to begin
- writing new storage engines. As such, it is primarily of interest to
- developers.
+ The <literal>EXAMPLE</literal> storage engine is a "stub"
+ engine that does nothing. You can create tables with this engine,
+ but no data can be stored into them or retrieved from them. The
+ purpose of this engine is to serve as an example in the MySQL source
+ code that illustrates how to begin writing new storage engines. As
+ such, it is primarily of interest to developers.
</para></listitem>
<listitem><para>
<literal>NDB Cluster</literal> is the storage engine used by MySQL
Cluster to implement tables that are partitioned over many computers.
- It is available in source code distributions as of MySQL 4.1.2 and
- binary distributions as of MySQL-Max 4.1.3.
+ It is available in MySQL-Max 5.0 binary distributions. This storage
+ engine is currently supported on Linux, Solaris, and Mac OS X only.
+ We intend to add support for this engine on other platforms,
+ including Windows, in future MySQL releases.
</para></listitem>
<listitem><para>
- The <literal>ARCHIVE</literal> storage engine was added in MySQL
- 4.1.3. It is used for storing large amounts of data without indexes
- in a very small footprint.
+ The <literal>ARCHIVE</literal> storage engine is used for storing
+ large amounts of data without indexes with a very small footprint.
</para></listitem>
<listitem><para>
- The <literal>CSV</literal> storage engine was added in MySQL 4.1.4.
- This engine stores data in text files using comma-separated-values
- format.
+ The <literal>CSV</literal> storage engine stores data in text files
+ using comma-separated-values format.
</para></listitem>
<listitem><para>
The <literal>FEDERATED</literal> storage engine was added in MySQL
5.0.3. This engine stores data in a remote database. In this release,
- it works with MySQL only, using the MySQL C Client API. Future
- releases will be able to connect to other data sources using other
- driver or client connection methods.
+ it works with MySQL only, using the MySQL C Client API. In future
+ releases, it will be able to connect to other data sources using
+ other driver or client connection methods.
</para></listitem>
</itemizedlist>
@@ -268,15 +265,16 @@
See <xref linkend="create-table"/> and <xref linkend="alter-table"/>.
</para>
-<!-- We should consider removing this or making a new section 'What happens when a -->
-
-<!-- table is created'. Currently it seems a bit out of place and not that -->
-
-<!-- helpful. -->
-
-<!-- TODO: this behavior now may be "using the default storage engine" -->
-
-<!-- rather than "using MyISAM", but when did that change? -->
+<!--
+ We should consider removing this or making a new section 'What
+ happens when a table is created'. Currently it seems a bit out of
+ place and not that helpful.
+-->
+
+<!--
+ TODO: this behavior now may be "using the default storage engine"
+ rather than "using MyISAM", but when did that change?
+-->
<para>
If you try to use a storage engine that is not compiled in or that is
@@ -292,7 +290,7 @@
<para>
This automatic substitution of the <literal>MyISAM</literal> table
type when an unavailable type is specified can be confusing for new
- MySQL users. In MySQL 4.1 and up, a warning is generated when a table
+ MySQL users. In MySQL 5.0, a warning is generated when a table
type is automatically changed.
</para>
@@ -347,11 +345,9 @@
</itemizedlist>
<para>
- Note that to use the <literal>InnoDB</literal> storage engine in MySQL
- 3.23, you must configure at least the
- <literal>innodb_data_file_path</literal> startup option. In 4.0 and
- up, <literal>InnoDB</literal> uses default configuration values if you
- specify none. See <xref linkend="innodb-configuration"/>.
+ In MySQL 5.0, <literal>InnoDB</literal> uses default configuration
+ values if you specify none. See
+ <xref linkend="innodb-configuration"/>.
</para>
<para>
@@ -400,9 +396,10 @@
</indexterm>
<para>
- <literal>MyISAM</literal> is the default storage engine as of MySQL
- 3.23. It is based on the <literal>ISAM</literal> code but has many
- useful extensions.
+ <literal>MyISAM</literal> is the default storage engine. It is based
+ on the older <literal>ISAM</literal> code but has many useful
+ extensions. (Note that MySQL 5.0 does not support
+ <literal>ISAM</literal>.)
</para>
<para>
@@ -410,39 +407,46 @@
files. The files have names that begin with the table name and have
an extension to indicate the file type. An <filename>.frm</filename>
file stores the table definition. The data file has an
- <filename>.MYD</filename> (MYData) extension. The index file has an
- <filename>.MYI</filename> (MYIndex) extension,
+ <filename>.MYD</filename> (<literal>MYData</literal>) extension. The
+ index file has an <filename>.MYI</filename>
+ (<literal>MYIndex</literal>) extension.
</para>
<para>
To specify explicitly that you want a <literal>MyISAM</literal>
- table, indicate that with an <literal>ENGINE</literal> or
- <literal>TYPE</literal> table option:
+ table, indicate that with an <literal>ENGINE</literal> table option:
</para>
<programlisting>
CREATE TABLE t (i INT) ENGINE = MYISAM;
-CREATE TABLE t (i INT) TYPE = MYISAM;
</programlisting>
<para>
- Normally, the <literal>ENGINE</literal> or <literal>TYPE</literal>
- option is unnecessary; <literal>MyISAM</literal> is the default
- storage engine unless the default has been changed.
+ (<emphasis role="bold">Note</emphasis>: Older versions of MySQL used
+ <literal>TYPE</literal> rather than <literal>ENGINE</literal> (for
+ example: <literal>TYPE = MYISAM</literal>). MySQL 5.0 supports this
+ syntax for backwards compatibility but <literal>TABLE</literal> is
+ now deprecated and <literal>ENGINE</literal> is the preferred
+ usage.)
+ </para>
+
+ <para>
+ Normally, the <literal>ENGINE</literal> option is unnecessary;
+ <literal>MyISAM</literal> is the default storage engine unless the
+ default has been changed.
</para>
<para>
You can check or repair <literal>MyISAM</literal> tables with the
<command>myisamchk</command> utility. See
- <xref linkend="crash-recovery"/>. You can compress
+ <xref linkend="crash-recovery"/>. You can also compress
<literal>MyISAM</literal> tables with <command>myisampack</command>
to take up much less space. See <xref linkend="myisampack"/>.
</para>
<para>
- The following characteristics of the <literal>MyISAM</literal>
- storage engine are improvements over the older
- <literal>ISAM</literal> engine:
+ The following are some characteristics of the
+ <literal>MyISAM</literal> storage engine:
</para>
<itemizedlist>
@@ -479,16 +483,15 @@
</para></listitem>
<listitem><para>
- The maximum number of indexes per table is 64 (32 before MySQL
- 4.1.2). This can be changed by recompiling. The maximum number of
- columns per index is 16.
+ The maximum number of indexes per <literal>MyISAM</literal> table in
+ MySQL 5.0 is 64. This can be changed by recompiling. The maximum
+ number of columns per index is 16.
</para></listitem>
<listitem><para>
- The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This
- can be changed by recompiling. For the case of a key longer than 250
- bytes, a larger key block size than the default of 1024 bytes is
- used.
+ The maximum key length is 1000 bytes. This can also be changed by
+ recompiling. For the case of a key longer than 250 bytes, a larger
+ key block size than the default of 1024 bytes is used.
</para></listitem>
<listitem><para>
@@ -507,14 +510,6 @@
</para></listitem>
<listitem><para>
- Index files are usually much smaller with <literal>MyISAM</literal>
- than with <literal>ISAM</literal>. This means that
- <literal>MyISAM</literal> normally uses less system resources than
- <literal>ISAM</literal>, but needs more CPU time when inserting data
- into a compressed index.
- </para></listitem>
-
- <listitem><para>
When records are inserted in sorted order (as when you are using an
<literal>AUTO_INCREMENT</literal> column), the index tree is split
so that the high node only contains one key. This improves space
@@ -524,10 +519,10 @@
<listitem><para>
Internal handling of one <literal>AUTO_INCREMENT</literal> column
per table. <literal>MyISAM</literal> automatically updates this
- column for <literal>INSERT/UPDATE</literal>. This makes
- <literal>AUTO_INCREMENT</literal> columns faster (at least 10%).
- Values at the top of the sequence are not reused after being deleted
- as they are with <literal>ISAM</literal>. (When an
+ column for <literal>INSERT</literal>and <literal>UPDATE</literal>
+ operations. This makes <literal>AUTO_INCREMENT</literal> columns
+ faster (at least 10%). Values at the top of the sequence are not
+ reused after being deleted. (When an
<literal>AUTO_INCREMENT</literal> column is defined as the last
column of a multiple-column index, reuse of values deleted from the
top of a sequence does occur.) The <literal>AUTO_INCREMENT</literal>
@@ -536,9 +531,9 @@
</para></listitem>
<listitem><para>
- If a table doesn't have free blocks in the middle of the data file,
- you can <literal>INSERT</literal> new rows into it at the same time
- that other threads are reading from the table. (These are known as
+ If a table has no free blocks in the middle of the data file, you
+ can <literal>INSERT</literal> new rows into it at the same time that
+ other threads are reading from the table. (These are known as
concurrent inserts.) A free block can occur as a result of deleting
rows or an update of a dynamic length row with more data than its
current contents. When all free blocks are used up (filled in),
@@ -553,8 +548,8 @@
</para></listitem>
<listitem><para>
- As of MySQL 4.1, each character column can have a different
- character set.
+ Each character column can have a different character set. See
+ <xref linkend="charset"/>.
</para></listitem>
<listitem><para>
@@ -563,32 +558,30 @@
<command>mysqld</command> is started with the
<literal>--myisam-recover</literal> option,
<literal>MyISAM</literal> tables are automatically checked when
- opened and repaired if the table wasn't closed properly.
+ opened, and are repaired if the table wasn't closed properly.
</para></listitem>
<listitem><para>
- <command>myisamchk</command> marks tables as checked if you run it
- with the <literal>--update-state</literal> option.
+ <command>myisamchk</command> marks tables as checked if run using
+ the <literal>--update-state</literal> option.
<command>myisamchk --fast</command> checks only those tables that
don't have this mark.
</para></listitem>
<listitem><para>
<command>myisamchk --analyze</command> stores statistics for key
- parts, not only for whole keys as in <literal>ISAM</literal>.
+ parts, as well as for entire keys.
</para></listitem>
<listitem><para>
<command>myisampack</command> can pack <literal>BLOB</literal> and
- <literal>VARCHAR</literal> columns; <command>pack_isam</command>
- cannot.
+ <literal>VARCHAR</literal> columns.
</para></listitem>
</itemizedlist>
<para>
- <literal>MyISAM</literal> also supports the following features, which
- MySQL will be able to use in the near future:
+ <literal>MyISAM</literal> also supports the following features:
</para>
<itemizedlist>
@@ -612,8 +605,8 @@
<listitem><para>
A hashed computed index can be used for <literal>UNIQUE</literal>.
This allows you to have <literal>UNIQUE</literal> on any combination
- of columns in a table. (You can't search on a
- <literal>UNIQUE</literal> computed index, however.)
+ of columns in a table. (However, you cannot search on a
+ <literal>UNIQUE</literal> computed index.)
</para></listitem>
</itemizedlist>
@@ -689,8 +682,8 @@
<para>
Used to help MySQL to decide when to use the slow but safe key
cache index creation method. <emphasis role="bold">Note</emphasis>:
- This parameter is given in megabytes before MySQL 4.0.3, and in
- bytes as of 4.0.3. This flag was removed in MySQL 5.0.6.
+ This was parameter was given in bytes before MySQL 5.0.6, when it
+ was removed.
</para></listitem>
<listitem><para>
@@ -700,8 +693,8 @@
<para>
Don't use the fast sort index method to create an index if the
temporary file would become larger than this.
- <emphasis role="bold">Note</emphasis>: This parameter is given in
- megabytes before MySQL 4.0.3, and in bytes as of 4.0.3.
+ <emphasis role="bold">Note</emphasis>: In MySQL 5.0, this parameter
+ is given in bytes.
</para></listitem>
<listitem><para>
@@ -842,13 +835,13 @@
or <literal>DYNAMIC</literal> with the <literal>ROW_FORMAT</literal>
table option. This causes <literal>CHAR</literal> and
<literal>VARCHAR</literal> columns to become <literal>CHAR</literal>
- for <literal>FIXED</literal> format or <literal>VARCHAR</literal>
+ for <literal>FIXED</literal> format, or <literal>VARCHAR</literal>
for <literal>DYNAMIC</literal> format.
</para>
<para>
- In the future, you will be able to compress or decompress tables by
- specifying <literal>ROW_FORMAT={COMPRESSED | DEFAULT}</literal> to
+ You can compress or decompress tables by specifying
+ <literal>ROW_FORMAT={COMPRESSED | DEFAULT}</literal> with
<literal>ALTER TABLE</literal>. See <xref linkend="create-table"/>.
</para>
@@ -1000,7 +993,7 @@
update a row with information that extends the row length, the row
becomes fragmented. In this case, you may have to run
<literal>OPTIMIZE TABLE</literal> or <command>myisamchk
- -r</command> from time to time to get better performance. Use
+ -r</command> from time to time to improve performance. Use
<command>myisamchk -ei</command> to obtain table statistics.
</para></listitem>
@@ -1028,8 +1021,8 @@
There is a penalty of 6 bytes for each link. A dynamic record is
linked whenever an update causes an enlargement of the record.
Each new link is at least 20 bytes, so the next enlargement
- probably goes in the same link. If not, there is another link. You
- may check how many links there are with <command>myisamchk
+ probably goes in the same link. If not, another link is created.
+ You can find the number of links using <command>myisamchk
-ed</command>. All links may be removed with <command>myisamchk
-r</command>.
</para></listitem>
@@ -1066,16 +1059,9 @@
</para>
<para>
- All MySQL distributions as of version 3.23.19 include
- <command>myisampack</command> by default. (This version is when
- MySQL was placed under the GPL.) For earlier versions,
- <command>myisampack</command> was included only with licenses or
- support agreements, but the server still can read tables that were
- compressed with <command>myisampack</command>. Compressed tables
- can be uncompressed with <command>myisamchk</command>. (For the
- <literal>ISAM</literal> storage engine, compressed tables can be
- created with <command>pack_isam</command> and uncompressed with
- <command>isamchk</command>.)
+ All MySQL distributions include <command>myisampack</command> by
+ default. Compressed tablescan be uncompressed with
+ <command>myisamchk</command>.
</para>
<para>
@@ -1086,12 +1072,12 @@
<listitem><para>
Compressed tables take very little disk space. This minimizes disk
- usage, which is very nice when using slow disks (such as CD-ROMs).
+ usage, which is helpful when using slow disks (such as CD-ROMs).
</para></listitem>
<listitem><para>
Each record is compressed separately, so there is very little
- access overhead. The header for a record is fixed (1-3 bytes)
+ access overhead. The header for a record takes up 1-3 bytes
depending on the biggest record in the table. Each column is
compressed differently. There is usually a different Huffman tree
for each column. Some of the compression types are:
@@ -1126,7 +1112,8 @@
</para></listitem>
<listitem><para>
- A column may use a combination of the preceding compressions.
+ A column may use any combination of the preceding compression
+ types.
</para></listitem>
</itemizedlist></listitem>
@@ -1159,7 +1146,7 @@
Even though the <literal>MyISAM</literal> table format is very
reliable (all changes to a table made by an SQL statement are
written before the statement returns), you can still get corrupted
- tables if some of the following things happen:
+ tables if any of the following events occur:
</para>
<itemizedlist>
@@ -1175,7 +1162,7 @@
</para></listitem>
<listitem><para>
- Hardware errors.
+ Hardware failures.
</para></listitem>
<listitem><para>
@@ -1191,7 +1178,7 @@
</itemizedlist>
<para>
- Typical symptoms for a corrupt table are:
+ Typical symptoms of a corrupt table are:
</para>
<itemizedlist>
@@ -1211,8 +1198,8 @@
</itemizedlist>
<para>
- You can check whether a <literal>MyISAM</literal> table is okay
- with the <literal>CHECK TABLE</literal> statement. You can repair a
+ You can check the health of a <literal>MyISAM</literal> table using
+ the <literal>CHECK TABLE</literal> statement, and repair a
corrupted <literal>MyISAM</literal> table with <literal>REPAIR
TABLE</literal>. When <command>mysqld</command> is not running, you
can also check or repair a table with the
@@ -1229,7 +1216,7 @@
<literal>restarted mysqld</literal> message in the error log. If
there is such a message, it is likely that table corruption is a
result of the server dying. Otherwise, corruption may have occurred
- during normal operation, which is a bug. You should try to create a
+ during normal operation. This is a bug. You should try to create a
reproducible test case that demonstrates the problem. See
<xref linkend="crashing"/> and
<xref linkend="reproduceable-test-case"/>.
@@ -1256,7 +1243,7 @@
<para>
This warning doesn't necessarily mean that the table is corrupted,
- but you should at least check the table to verify that it's okay.
+ but you should at least check the table.
</para>
<para>
@@ -1302,9 +1289,9 @@
<itemizedlist>
<listitem><para>
- The <literal>MyISAM</literal> tables are copied without a
- preceding <literal>LOCK TABLES</literal> and <literal>FLUSH
- TABLES</literal>.
+ The <literal>MyISAM</literal> tables are copied without first
+ issuing <literal>LOCK TABLES</literal> and <literal>FLUSH
+ TABLES</literal>.
</para></listitem>
<listitem><para>
@@ -1320,8 +1307,8 @@
</para></listitem>
<listitem><para>
- Many <command>mysqld</command> servers are using the table and one
- server performed a <literal>REPAIR TABLE</literal> or
+ Multiple <command>mysqld</command> servers are using the table and
+ one server performed a <literal>REPAIR TABLE</literal> or
<literal>CHECK TABLE</literal> on the table while it was in use by
another server. In this setup, it is safe to use <literal>CHECK
TABLE</literal>, although you might get the warning from other
@@ -1379,19 +1366,14 @@
<!-- description_for_help_topic MERGE -->
<para>
- The <literal>MERGE</literal> storage engine was introduced in MySQL
- 3.23.25. It is also known as the <literal>MRG_MyISAM</literal>
- engine.
- </para>
-
- <para>
- A <literal>MERGE</literal> table is a collection of identical
+ The <literal>MERGE</literal> storage engine, also known as the
+ <literal>MRG_MyISAM</literal> engine, is a collection of identical
<literal>MyISAM</literal> tables that can be used as one.
- ``Identical'' means that all tables have identical column and index
- information. You can't merge tables in which the columns are listed
- in a different order, don't have exactly the same columns, or have
- the indexes in different order. However, any or all of the tables can
- be compressed with <command>myisampack</command>. See
+ "Identical" means that all tables have identical column and
+ index information. You cannot merge tables in which the columns are
+ listed in a different order, don't have exactly the same columns, or
+ have the indexes in different order. However, any or all of the
+ tables can be compressed with <command>myisampack</command>. See
<xref linkend="myisampack"/>. Differences in table options such as
<literal>AVG_ROW_LENGTH</literal>, <literal>MAX_ROWS</literal>, or
<literal>PACK_KEYS</literal> do not matter.
@@ -1405,16 +1387,14 @@
and have an extension to indicate the file type. An
<filename>.frm</filename> file stores the table definition, and an
<filename>.MRG</filename> file contains the names of the tables that
- should be used as one. (Originally, all used tables had to be in the
- same database as the <literal>MERGE</literal> table itself. This
- restriction has been lifted as of MySQL 4.1.1.)
+ should be used as one. The tables do not have to be in the
+ same database as the <literal>MERGE</literal> table itself.
</para>
<para>
You can use <literal>SELECT</literal>, <literal>DELETE</literal>,
- <literal>UPDATE</literal>, and (as of MySQL 4.0)
- <literal>INSERT</literal> on the collection of tables. For the
- moment, you must have <literal>SELECT</literal>,
+ <literal>UPDATE</literal>, and <literal>INSERT</literal> on the
+ collection of tables. You must have <literal>SELECT</literal>,
<literal>UPDATE</literal>, and <literal>DELETE</literal> privileges
on the tables that you map to a <literal>MERGE</literal> table.
</para>
@@ -1427,16 +1407,16 @@
<para>
When you create a <literal>MERGE</literal> table, you must specify a
- <literal>UNION=(list-of-tables)</literal> clause that indicates which
- tables you want to use as one. You can optionally specify an
- <literal>INSERT_METHOD</literal> option if you want inserts for the
- <literal>MERGE</literal> table to happen in the first or last table
- of the <literal>UNION</literal> list. Use a value of
- <literal>FIRST</literal> or <literal>LAST</literal> to have inserts
- go to the first or last table. If you don't specify any
- <literal>INSERT_METHOD</literal> option or specify it with a value of
- <literal>NO</literal>, attempts to insert records into the
- <literal>MERGE</literal> table result in an error.
+ <literal>UNION=(<replaceable>list-of-tables</replaceable>)</literal>
+ clause that indicates which tables you want to use as one. You can
+ optionally specify an <literal>INSERT_METHOD</literal> option if you
+ want inserts for the <literal>MERGE</literal> table to take place in
+ the first or last table of the <literal>UNION</literal> list. Use a
+ value of <literal>FIRST</literal> or <literal>LAST</literal> to cause
+ inserts to be made in the first or last table, respectively. If you
+ do not specify an <literal>INSERT_METHOD</literal> option or if you
+ specify it with a value of <literal>NO</literal>, attempts to insert
+ records into the <literal>MERGE</literal> table result in an error.
</para>
<para>
@@ -1471,8 +1451,8 @@
</para>
<para>
- After creating the <literal>MERGE</literal> table, you can do things
- like this:
+ After creating the <literal>MERGE</literal> table, you can issue
+ queries that operate on the group of tables as a whole:
</para>
<programlisting>
@@ -1502,13 +1482,15 @@
<para>
To remap a <literal>MERGE</literal> table to a different collection
- of <literal>MyISAM</literal> tables, you can do one of the following:
+ of <literal>MyISAM</literal> tables, you can perform one of the
+ following:
</para>
<itemizedlist>
<listitem><para>
- <literal>DROP</literal> the table and re-create it.
+ <literal>DROP</literal> the <literal>MERGE</literal> table
+ and re-create it.
</para></listitem>
<listitem><para>
@@ -1544,24 +1526,20 @@
criteria, and then put individual tables on different disks. A
<literal>MERGE</literal> table on this could be much faster than
using the big table.
-
-<!-- (You can also use a RAID table to get the same -->
- </para>
-
-<!-- kind of benefits.) --></listitem>
+ </para></listitem>
<listitem><para>
- Do more efficient searches. If you know exactly what you are looking
- for, you can search in just one of the split tables for some queries
- and use a <literal>MERGE</literal> table for others. You can even
- have many different <literal>MERGE</literal> tables that use
+ Perform more efficient searches. If you know exactly what you are
+ looking for, you can search in just one of the split tables for some
+ queries and use a <literal>MERGE</literal> table for others. You can
+ even have many different <literal>MERGE</literal> tables that use
overlapping sets of tables.
</para></listitem>
<listitem><para>
- Do more efficient repairs. It's easier to repair the individual
+ Perform more efficient repairs. It is easier to repair individual
tables that are mapped to a <literal>MERGE</literal> table than to
- repair a single really big table.
+ repair a single large table.
</para></listitem>
<listitem><para>
@@ -1591,7 +1569,7 @@
You can create an alias or synonym for a <literal>MyISAM</literal>
table by defining a <literal>MERGE</literal> table that maps to that
single table. There should be no really notable performance impact
- of doing this (only a couple of indirect calls and
+ from doing this (only a couple of indirect calls and
<literal>memcpy()</literal> calls for each read).
</para></listitem>
@@ -1620,12 +1598,12 @@
Key reads are slower. When you read a key, the
<literal>MERGE</literal> storage engine needs to issue a read on all
underlying tables to check which one most closely matches the given
- key. If you then do a ``read-next,'' the <literal>MERGE</literal>
+ key. If you then do a read-next, the <literal>MERGE</literal>
storage engine needs to search the read buffers to find the next
- key. Only when one key buffer is used up, the storage engine needs
- to read the next key block. This makes <literal>MERGE</literal> keys
- much slower on <literal>eq_ref</literal> searches, but not much
- slower on <literal>ref</literal> searches. See
+ key. Only when one key buffer is used up does the storage engine
+ need to read the next key block. This makes <literal>MERGE</literal>
+ keys much slower on <literal>eq_ref</literal> searches, but not much
+ slower on <literal>ref</literal> searches. See
<xref linkend="explain"/> for more information about
<literal>eq_ref</literal> and <literal>ref</literal>.
</para></listitem>
@@ -1637,7 +1615,7 @@
<title id='title-merge-table-problems'>&title-merge-table-problems;</title>
<para>
- The following are the known problems with <literal>MERGE</literal>
+ The following are known problems with <literal>MERGE</literal>
tables:
</para>
@@ -1648,31 +1626,26 @@
<literal>MERGE</literal> table to another table type, the mapping
to the underlying tables is lost. Instead, the rows from the
underlying <literal>MyISAM</literal> tables are copied into the
- altered table, which then is assigned the new type.
+ altered table, which is then assigned the new type.
</para></listitem>
<listitem><para>
- Before MySQL 4.1.1, all underlying tables and the
- <literal>MERGE</literal> table itself had to be in the same
- database.
+ <literal>REPLACE</literal> does not work.
</para></listitem>
<listitem><para>
- <literal>REPLACE</literal> doesn't work.
- </para></listitem>
-
- <listitem><para>
- You can't use <literal>DROP TABLE</literal>, <literal>ALTER
+ You cannot use <literal>DROP TABLE</literal>, <literal>ALTER
TABLE</literal>, <literal>DELETE FROM</literal> without a
<literal>WHERE</literal> clause, <literal>REPAIR TABLE</literal>,
<literal>TRUNCATE TABLE</literal>, <literal>OPTIMIZE
TABLE</literal>, or <literal>ANALYZE TABLE</literal> on any of the
- tables that are mapped into a <literal>MERGE</literal> table that
- is ``open.'' If you do this, the <literal>MERGE</literal> table may
- still refer to the original table and you get unexpected results.
- The easiest way to work around this deficiency is to issue a
- <literal>FLUSH TABLES</literal> statement to ensure that no
- <literal>MERGE</literal> tables remain ``open.''
+ tables that are mapped into an open <literal>MERGE</literal> table.
+ If you do so, the <literal>MERGE</literal> table may still refer to
+ the original table, which yields unexpected results. The easiest
+ way to work around this deficiency is to issue a <literal>FLUSH
+ TABLES</literal> statement prior to performing any of these
+ operations to ensure that no <literal>MERGE</literal> tables remain
+ open.
</para></listitem>
<listitem><para>
@@ -1687,50 +1660,35 @@
</para></listitem>
<listitem><para>
- Before MySQL 3.23.49, <literal>DELETE FROM
- <replaceable>merge_table</replaceable></literal> used without a
- <literal>WHERE</literal> clause only clears the mapping for the
- table. That is, it incorrectly empties the
- <filename>.MRG</filename> file rather than deleting records from
- the mapped tables.
- </para></listitem>
-
- <listitem><para>
- Using <literal>RENAME TABLE</literal> on an active
- <literal>MERGE</literal> table may corrupt the table. This is fixed
- in MySQL 4.1.x.
- </para></listitem>
-
- <listitem><para>
When you create a <literal>MERGE</literal> table, there is no check
- whether the underlying tables exist and have identical structure.
- When the <literal>MERGE</literal> table is used, MySQL does a quick
- check that the record length for all mapped tables is equal, but
+ to insure that the underlying tables exist and have identical
+ structures. When the <literal>MERGE</literal> table is used, MySQL
+ checks that the record length for all mapped tables is equal, but
this is not foolproof. If you create a <literal>MERGE</literal>
table from dissimilar <literal>MyISAM</literal> tables, you are
very likely to run into strange problems.
</para></listitem>
<listitem><para>
- Index order in the <literal>MERGE</literal> table and its
+ The order of indexes in the <literal>MERGE</literal> table and its
underlying tables should be the same. If you use <literal>ALTER
TABLE</literal> to add a <literal>UNIQUE</literal> index to a table
used in a <literal>MERGE</literal> table, and then use
<literal>ALTER TABLE</literal> to add a non-unique index on the
- <literal>MERGE</literal> table, the index order is different for
- the tables if there was an old non-unique index in the underlying
- table. (This is because <literal>ALTER TABLE</literal> puts
- <literal>UNIQUE</literal> indexes before non-unique indexes to be
- able to detect duplicate keys as early as possible.) Consequently,
- queries may return unexpected results.
+ <literal>MERGE</literal> table, the index ordering is different for
+ the tables if there was already a non-unique index in the
+ underlying table. (This is because <literal>ALTER TABLE</literal>
+ puts <literal>UNIQUE</literal> indexes before non-unique indexes to
+ facilitate rapid detection of duplicate keys.) Consequently,
+ queries on tables with such indexes may return unexpected results.
</para></listitem>
<listitem><para>
<literal>DROP TABLE</literal> on a table that is in use by a
<literal>MERGE</literal> table does not work on Windows because the
- <literal>MERGE</literal> storage engine does the table mapping
- hidden from the upper layer of MySQL. Because Windows doesn't allow
- you to delete files that are open, you first must flush all
+ <literal>MERGE</literal> storage engine's table mapping is hidden
+ from the upper layer of MySQL. Since Windows does not allow the
+ deletion of open files, you first must flush all
<literal>MERGE</literal> tables (with <literal>FLUSH
TABLES</literal>) or drop the <literal>MERGE</literal> table before
dropping the table.
@@ -1746,9 +1704,11 @@
<title id='title-memory-storage-engine'>&title-memory-storage-engine;</title>
-<!-- TODO: parts of this may be true only for hash indexes. Now that -->
-
-<!-- MEMORY supports BTREE indexes, some statements may need to be qualified. -->
+<!--
+ TODO: Parts of this may be true only for hash indexes. Now that
+ MEMORY supports BTREE indexes, some statements may need to be
+ qualified.
+-->
<indexterm type="concept">
<primary><literal>HEAP</literal> storage engine</primary>
@@ -1778,11 +1738,11 @@
<para>
The <literal>MEMORY</literal> storage engine creates tables with
- contents that are stored in memory. Before MySQL 4.1,
- <literal>MEMORY</literal> tables are called <literal>HEAP</literal>
- tables. As of 4.1, <literal>MEMORY</literal> is the preferred term,
- and <literal>HEAP</literal> is a synonym for
- <literal>MEMORY</literal>.
+ contents that are stored in memory. These were formerly known as
+ <literal>HEAP</literal> tables. In MySQL 5.0,
+ <literal>MEMORY</literal> is the preferred term, although
+ <literal>HEAP</literal> remains supported for backwards
+ compatibility.
</para>
<para>
@@ -1794,32 +1754,31 @@
<para>
To specify explicitly that you want a <literal>MEMORY</literal>
- table, indicate that with an <literal>ENGINE</literal> or
- <literal>TYPE</literal> table option:
+ table, indicate that with an <literal>ENGINE</literal> option:
</para>
<programlisting>
CREATE TABLE t (i INT) ENGINE = MEMORY;
-CREATE TABLE t (i INT) TYPE = HEAP;
</programlisting>
<para>
- <literal>MEMORY</literal> tables are stored in memory and use hash
- indexes by default. This makes them very fast, and very useful for
- creating temporary tables. However, when the server shuts down, all
- data stored in <literal>MEMORY</literal> tables is lost. The tables
- continue to exist because their definitions are stored in the
- <filename>.frm</filename> files on disk, but their contents are empty
- when the server restarts.
+ As indicated by their name, <literal>MEMORY</literal> tables are
+ stored in memory and use hash indexes by default. This makes them
+ very fast, and very useful for creating temporary tables. However,
+ when the server shuts down, all data stored in
+ <literal>MEMORY</literal> tables is lost. The tables themselves
+ continue to exist because their definitions are stored in
+ <filename>.frm</filename> files on disk, but they are empty when the
+ server restarts.
</para>
<para>
- Here is an example that shows how you might create, use, and remove a
+ This example shows how you might create, use, and remove a
<literal>MEMORY</literal> table:
</para>
<programlisting>
-mysql> CREATE TABLE test TYPE=MEMORY
+mysql> CREATE TABLE test ENGINE=MEMORY
-> SELECT ip,SUM(downloads) AS down
-> FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
@@ -1834,16 +1793,16 @@
<listitem><para>
Space for <literal>MEMORY</literal> tables is allocated in small
- blocks. The tables use 100% dynamic hashing (on inserting). No
- overflow areas and no extra key space are needed. There is no extra
- space needed for free lists. Deleted rows are put in a linked list
- and are reused when you insert new data into the table.
- <literal>MEMORY</literal> tables also don't have problems with
- deletes plus inserts, which is common with hashed tables.
+ blocks. Tables use 100% dynamic hashing for inserts. No overflow
+ area or extra key space is needed. No extra space is needed for free
+ lists. Deleted rows are put in a linked list and are reused when you
+ insert new data into the table. <literal>MEMORY</literal> tables
+ also have none of the problems commonly associated with deletes plus
+ inserts in hashed tables.
</para></listitem>
<listitem><para>
- <literal>MEMORY</literal> tables allow up to 32 indexes per table
+ <literal>MEMORY</literal> tables can have up to 32 indexes per table
and 16 columns per index. Previously, the maximum key length
supported by this storage engine was 255 bytes; as of MySQL 5.0.8,
<literal>MEMORY</literal> tables support a maximum key length of 500
@@ -1851,12 +1810,10 @@
</para></listitem>
<listitem><para>
- Before MySQL 4.1, the <literal>MEMORY</literal> storage engine
- implements only hash indexes. From MySQL 4.1 on, hash indexes are
- still the default, but you can specify explicitly that a
- <literal>MEMORY</literal> table index should be a
- <literal>HASH</literal> or <literal>BTREE</literal> by adding a
- <literal>USING</literal> clause:
+ In MySQL 5.0, the <literal>MEMORY</literal> storage engine
+ implements both <literal>HASH</literal> and <literal>BTREE</literal>
+ indexes. You can specify one or the other for a given index by
+ adding a <literal>USING</literal> clause as shown here:
</para>
<programlisting>
@@ -1879,7 +1836,7 @@
</para></listitem>
<listitem><para>
- As of MySQL 4.1, you can use <literal>INSERT DELAYED</literal> with
+ In MySQL 5.0, you can use <literal>INSERT DELAYED</literal> with
<literal>MEMORY</literal> tables. See
<xref linkend="insert-delayed"/>.
</para></listitem>
@@ -1888,7 +1845,7 @@
If you have a hash index on a <literal>MEMORY</literal> table that
has a high degree of key duplication (many index entries containing
the same value), updates to the table that affect key values and all
- deletes are significantly slower. The degree of slowdown is
+ deletes are significantly slower. The degree of this slowdown is
proportional to the degree of duplication (or, inversely
proportional to the index cardinality). You can use a
<literal>BTREE</literal> index to avoid this problem.
@@ -1904,13 +1861,9 @@
</para></listitem>
<listitem><para>
- <literal>MEMORY</literal> doesn't support
- <literal>AUTO_INCREMENT</literal> columns before MySQL 4.1.0.
- </para></listitem>
-
- <listitem><para>
- Prior to MySQL 4.0.2, <literal>MEMORY</literal> doesn't support
- indexes on columns that can contain <literal>NULL</literal> values.
+ <literal>MEMORY</literal> in MySQL 5.0 includes support for both
+ <literal>AUTO_INCREMENT</literal> columns and indexes on columns
+ that can contain <literal>NULL</literal> values.
</para></listitem>
<listitem><para>
@@ -1948,15 +1901,16 @@
</itemizedlist></listitem>
<listitem><para>
- The server needs enough extra memory to maintain all
+ The server needs sufficient memory to maintain all
<literal>MEMORY</literal> tables that are in use at the same time.
</para></listitem>
<listitem><para>
- To free memory used by a <literal>MEMORY</literal> table if you no
+ To free memory used by a <literal>MEMORY</literal> table when you no
longer require its contents, you should execute
- <literal>DELETE</literal> or <literal>TRUNCATE TABLE</literal>, or
- else remove the table with <literal>DROP TABLE</literal>.
+ <literal>DELETE FROM</literal> or <literal>TRUNCATE TABLE</literal>,
+ or remove the table altogether (using <literal>DROP
+ TABLE</literal>).
</para></listitem>
<listitem><para>
@@ -1964,8 +1918,9 @@
MySQL server starts, you can use the <literal>--init-file</literal>
option. For example, you can put statements such as <literal>INSERT
INTO ... SELECT</literal> or <literal>LOAD DATA INFILE</literal>
- into the file to load the table from some persistent data source.
- See <xref linkend="server-options"/>.
+ into this file in order to load the table from a persistent data
+ source. See <xref linkend="server-options"/> and
+ <xref linkend="load-data"/>.
</para></listitem>
<listitem><para>
@@ -1973,16 +1928,16 @@
<literal>MEMORY</literal> tables become empty when it is shut down
and restarted. However, a slave is not aware that these tables have
become empty, so it returns out-of-date content if you select data
- from them. Beginning with MySQL 4.0.18, when a
- <literal>MEMORY</literal> table is used on the master for the first
- time since the master's startup, a <literal>DELETE FROM</literal>
- statement is written to the master's binary log automatically, thus
- synchronizing the slave to the master again. Note that even with
- this strategy, the slave still has out-of-date data in the table
- during the interval between the master's restart and its first use
- of the table. But if you use the <literal>--init-file</literal>
- option to populate the <literal>MEMORY</literal> table on the master
- at startup, it ensures that the failing time interval is zero.
+ from them. In MySQL 5.0, when a <literal>MEMORY</literal> table is
+ used on the master for the first time since the master was started,
+ a <literal>DELETE FROM</literal> statement is written to the
+ master's binary log automatically, thus synchronizing the slave to
+ the master again. Note that even with this strategy, the slave still
+ has outdated data in the table during the interval between the
+ master's restart and its first use of the table. However, if you use
+ the <literal>--init-file</literal> option to populate the
+ <literal>MEMORY</literal> table on the master at startup, it ensures
+ that this time interval is zero.
</para></listitem>
<listitem><para>
@@ -2042,8 +1997,7 @@
transactional storage engine. This storage engine typically is called
<literal>BDB</literal> for short. Support for the
<literal>BDB</literal> storage engine is included in MySQL source
- distributions starting from version 3.23.34a and is activated in
- MySQL-Max binary distributions.
+ distributions is activated in MySQL-Max binary distributions.
</para>
<para>
@@ -2051,25 +2005,23 @@
crashes and are also capable of <literal>COMMIT</literal> and
<literal>ROLLBACK</literal> operations on transactions. The MySQL
source distribution comes with a <literal>BDB</literal> distribution
- that has a couple of small patches to make it work more smoothly with
- MySQL. You can't use a non-patched <literal>BDB</literal> version
- with MySQL.
+ that is patched to make it work with MySQL. You cannot use a
+ non-patched version of <literal>BDB</literal> with MySQL.
</para>
-
+<!-- Is this still true in 5.0? -->
<para>
- We at MySQL AB are working in close cooperation with Sleepycat to
- keep the quality of the MySQL/BDB interface high. (Even though
- Berkeley DB is in itself very tested and reliable, the MySQL
- interface is still considered gamma quality. We are improving and
- optimizing it.)
+ We at MySQL AB work in close cooperation with Sleepycat to keep the
+ quality of the MySQL/BDB interface high. (Even though Berkeley DB is
+ in itself very tested and reliable, the MySQL interface is still
+ considered gamma quality. We continue to improve and optimize it.)
</para>
<para>
When it comes to support for any problems involving
<literal>BDB</literal> tables, we are committed to helping our users
- locate the problem and create a reproducible test case. Any such test
- case is forwarded to Sleepycat, which in turn helps us find and fix
- the problem. As this is a two-stage operation, any problems with
+ locate the problem and create reproducible test cases. Any such test
+ case is forwarded to Sleepycat, who in turn help us find and fix the
+ problem. As this is a two-stage operation, any problems with
<literal>BDB</literal> tables may take a little longer for us to fix
than for other storage engines. However, we anticipate no significant
difficulties with this procedure because the Berkeley DB code itself
@@ -2111,17 +2063,21 @@
<listitem><para>
SCO OpenServer
- </para></listitem>
-
- <listitem><para>
+ </para></listitem>
+
+ <listitem><para>
SCO UnixWare 7.1.x
+ </para></listitem>
+
+ <listitem><para>
+ Windows NT/2000/XP
</para></listitem>
</itemizedlist>
<para>
- <literal>BDB</literal> does not work with the following operating
- systems:
+ <literal>BDB</literal> does <emphasis>not</emphasis> work with the
+ following operating systems:
</para>
<itemizedlist>
@@ -2189,18 +2145,18 @@
<literal>BDB</literal> support by running
<command>configure</command> with the
<literal>--with-berkeley-db</literal> option in addition to any
- other options that you normally use. Download a distribution for
- MySQL 3.23.34 or newer, change location into its top-level
- directory, and run this command:
+ other options that you normally use. Download a MySQL 5.0
+ distribution, change location into its top-level directory, and run
+ this command:
</para>
<programlisting>
-shell> ./configure --with-berkeley-db [other-options]
+shell> ./configure --with-berkeley-db [<replaceable>other-options</replaceable>]
</programlisting>
<para>
For more information, see <xref linkend="installing-binary"/>,
- <xref linkend="mysqld-max"/>, and See
+ <xref linkend="mysqld-max"/>, and
<xref linkend="installing-source"/>.
</para>
@@ -2249,7 +2205,7 @@
</para>
<para>
- Don't start Berkeley DB in recover mode.
+ Do not start Berkeley DB in recover mode.
</para></listitem>
<listitem><para>
@@ -2257,7 +2213,7 @@
</para>
<para>
- Don't synchronously flush the <literal>BDB</literal> logs.
+ Do not synchronously flush the <literal>BDB</literal> logs.
</para></listitem>
<listitem><para>
@@ -2265,7 +2221,7 @@
</para>
<para>
- Start Berkeley DB in multi-process mode. (Don't use
+ Start Berkeley DB in multi-process mode. (Do not use
<literal>DB_PRIVATE</literal> when initializing Berkeley DB.)
</para></listitem>
@@ -2290,7 +2246,12 @@
<para>
See <xref linkend="server-options"/>.
</para>
-
+<!--
+ Seems a bit odd to have a single-item list here... This bsaically
+ just repeats what's show about 2 paras below, so I'm commenting it
+ out for now
+-->
+<!--
<para>
The following system variable affects the behavior of
<literal>BDB</literal> tables:
@@ -2303,15 +2264,12 @@
</para>
<para>
- The maximum number of locks you can have active on a
+ The maximum number of locks that can be active on a
<literal>BDB</literal> table.
</para></listitem>
</itemizedlist>
-
- <para>
- See <xref linkend="server-system-variables"/>.
- </para>
+-->
<para>
If you use the <literal>--skip-bdb</literal> option, MySQL does not
@@ -2325,7 +2283,7 @@
<para>
Normally, you should start <command>mysqld</command> without the
<literal>--bdb-no-recover</literal> option if you intend to use
- <literal>BDB</literal> tables. However, this may give you problems
+ <literal>BDB</literal> tables. However, this may cause problems
when you try to start <command>mysqld</command> if the
<literal>BDB</literal> log files are corrupted. See
<xref linkend="starting-server"/>.
@@ -2345,12 +2303,17 @@
Got error 12 from ...
</programlisting>
- <para>
- You may also want to change the <literal>binlog_cache_size</literal>
- and <literal>max_binlog_cache_size</literal> variables if you are
- using large multiple-statement transactions. See
- <xref linkend="binary-log"/>.
- </para>
+ <para>
+ You may also want to change the
+ <literal>binlog_cache_size</literal> and
+ <literal>max_binlog_cache_size</literal> variables if you are
+ using large multiple-statement transactions. See
+ <xref linkend="binary-log"/>.
+ </para>
+
+ <para>
+ See also <xref linkend="server-system-variables"/>.
+ </para>
</section>
@@ -2423,9 +2386,9 @@
<itemizedlist>
<listitem><para>
- <literal>BDB</literal> tables can have up to 31 indexes per table,
- 16 columns per index, and a maximum key size of 1024 bytes (500
- bytes before MySQL 4.0).
+ In MySQL 5.0, <literal>BDB</literal> tables can have up to 31
+ indexes per table, 16 columns per index, and a maximum key size of
+ 1024 bytes.
</para></listitem>
<listitem><para>
@@ -2434,7 +2397,9 @@
identified. If you don't create one explicitly, MySQL creates and
maintains a hidden <literal>PRIMARY KEY</literal> for you. The
hidden key has a length of five bytes and is incremented for each
- insert attempt.
+ insert attempt. This key does not appear in the output of
+ <literal>SHOW CREATE TABLE</literal> or
+ <literal>DESCRIBE</literal>.
</para></listitem>
<listitem><para>
@@ -2507,13 +2472,16 @@
page level.
</para></listitem>
- <listitem><para>
- <literal>LOCK TABLES</literal> works on <literal>BDB</literal>
- tables as with other tables. If you don't use <literal>LOCK
- TABLE</literal>, MySQL issues an internal multiple-write lock on
- the table (a lock that doesn't block other writers) to ensure that
- the table is properly locked if another thread issues a table lock.
- </para></listitem>
+ <listitem>
+ <para>
+ <literal>LOCK TABLES</literal> works on <literal>BDB</literal>
+ tables as with other tables. If you do not use <literal>LOCK
+ TABLES</literal>, MySQL issues an internal multiple-write lock
+ on the table (a lock that does not block other writers) to
+ ensure that the table is properly locked if another thread
+ issues a table lock.
+ </para>
+ </listitem>
<listitem><para>
To be able to roll back transactions, the <literal>BDB</literal>
@@ -2549,11 +2517,11 @@
</para></listitem>
<listitem><para>
- If you get full disk with a <literal>BDB</literal> table, you get
+ If you get a full disk with a <literal>BDB</literal> table, you get
an error (probably error 28) and the transaction should roll back.
- This contrasts with <literal>MyISAM</literal> and
- <literal>ISAM</literal> tables, for which <command>mysqld</command>
- waits for enough free disk before continuing.
+ This contrasts with <literal>MyISAM</literal> tables, for which
+ <command>mysqld</command> waits for sufficient free disk space
+ before continuing.
</para></listitem>
</itemizedlist>
@@ -2567,25 +2535,50 @@
<itemizedlist>
<listitem><para>
- It's very slow to open many <literal>BDB</literal> tables at the
- same time. If you are going to use <literal>BDB</literal> tables,
+ Opening many <literal>BDB</literal> tables at the same time may be
+ quite slow. If you are going to use <literal>BDB</literal> tables,
you should not have a very large table cache (for example, with a
size larger than 256) and you should use the
<literal>--no-auto-rehash</literal> option when you use the
- <command>mysql</command> client. We plan to partly fix this in 4.0.
+ <command>mysql</command> client.
</para></listitem>
<listitem><para>
- <literal>SHOW TABLE STATUS</literal> doesn't yet provide very much
- information for <literal>BDB</literal> tables.
- </para></listitem>
+ <literal>SHOW TABLE STATUS</literal> does not yet provide some
+ information for <literal>BDB</literal> tables:
+ </para>
+
+<programlisting>
+mysql> SHOW TABLE STATUS LIKE 'bdbtest'\G
+*************************** 1. row ***************************
+ Name: bdbtest
+ Engine: BerkeleyDB
+ Version: 10
+ Row_format: Dynamic
+ Rows: 154
+ Avg_row_length: 0
+ Data_length: 0
+Max_data_length: 0
+ Index_length: 0
+ Data_free: 0
+ Auto_increment: NULL
+ Create_time: NULL
+ Update_time: NULL
+ Check_time: NULL
+ Collation: latin1_swedish_ci
+ Checksum: NULL
+ Create_options:
+ Comment:
+</programlisting>
+
+ </listitem>
<listitem><para>
Optimize performance.
</para></listitem>
<listitem><para>
- Change to not use page locks at all for table scanning operations.
+ Change to use no page locks for table scanning operations.
</para></listitem>
</itemizedlist>
@@ -2607,8 +2600,8 @@
Each <literal>BDB</literal> table stores in the
<filename>.db</filename> file the path to the file as it was
created. This was done to be able to detect locks in a multi-user
- environment that supports symlinks. However, the consequence is
- that <literal>BDB</literal> table files cannot be moved from one
+ environment that supports symlinks. As a consequence of this, it is
+ not possible to move <literal>BDB</literal> table files from one
database directory to another.
</para></listitem>
@@ -2643,7 +2636,7 @@
<listitem><para>
If the following error occurs when you start
- <command>mysqld</command>, it means that the new
+ <command>mysqld</command> after upgrading, it means that the new
<literal>BDB</literal> version doesn't support the old log file
format:
</para>
@@ -2706,17 +2699,16 @@
</indexterm>
<para>
- The <literal>EXAMPLE</literal> storage engine was added in MySQL
- 4.1.3. It is a ``stub'' engine that does nothing. Its purpose is to
- serve as an example in the MySQL source code that illustrates how to
- begin writing new storage engines. As such, it is primarily of
- interest to developers.
+ The <literal>EXAMPLE</literal> storage engine is a stub engine that
+ does nothing. Its purpose is to serve as an example in the MySQL
+ source code that illustrates how to begin writing new storage
+ engines. As such, it is primarily of interest to developers.
</para>
<para>
To examine the source for the <literal>EXAMPLE</literal> engine, look
- in the <filename>sql/examples</filename> directory of a source
- distribution for MySQL 4.1.3 or newer.
+ in the <filename>sql/examples</filename> directory of a MySQL 5.0
+ source distribution.
</para>
<para>
@@ -2769,9 +2761,9 @@
</indexterm>
<para>
- The <literal>FEDERATED</literal> storage engine was added in MySQL
- 5.0.3. It is a storage engine that accesses data in tables of remote
- databases rather than in local tables.
+ The <literal>FEDERATED</literal> storage engine is available
+ beginning with MySQL 5.0.3. It is a storage engine that accesses data
+ in tables of remote databases rather than in local tables.
</para>
<para>
@@ -2870,11 +2862,12 @@
<title id='title-federated-use'>&title-federated-use;</title>
-<!-- TODO: Stuff we could cover: -->
-
-<!-- - If federated table is replicated, the the slave hosts must be able to -->
-
-<!-- use the account in the COMMENT to connect to the remote server. -->
+<!--
+ TODO: Stuff we could cover:
+ - If federated table is replicated, the the slave hosts must be
+ able to use the account in the COMMENT to connect to the remote
+ server.
+-->
<para>
The procedure for using <literal>FEDERATED</literal> tables is very
@@ -2911,10 +2904,8 @@
</para>
<para>
- Next, create a <literal>FEDERATED</literal> table for accessing the
- remote table. The server where you create the
- <literal>FEDERATED</literal> table is the ``client server.'' On this
- server, create the table as follows:
+ Next, create a <literal>FEDERATED</literal> table on the local
+ server for accessing the remote table:
</para>
<programlisting>
@@ -2932,12 +2923,12 @@
</programlisting>
<para>
- The structure of this table must be exactly the same as the remote
- table, except that the <literal>ENGINE</literal> table option should
- be <literal>FEDERATED</literal> and the <literal>COMMENT</literal>
- table option is a connection string that indicates to the
- <literal>FEDERATED</literal> engine how to connect to the remote
- server.
+ The structure of this table must be exactly the same as that of the
+ remote table, except that the <literal>ENGINE</literal> table option
+ should be <literal>FEDERATED</literal> and the
+ <literal>COMMENT</literal> table option is a connection string that
+ indicates to the <literal>FEDERATED</literal> engine how to connect
+ to the remote server.
</para>
<para>
@@ -2948,11 +2939,11 @@
<para>
The remote host information indicates the remote server to which
- your ``client'' server connects, and the database and table
- information indicates which remote table to use as the ``data
- file.'' In the example, the remote server is indicated to be running
- as <literal>remote_host</literal> on port 9306, so you want to start
- that server so that it is indeed listening to port 9306.
+ your local server connects, and the database and table
+ information indicates which remote table to use as the data file. In
+ this example, the remote server is indicated to be running as
+ <literal>remote_host</literal> on port 9306, so you want to start
+ that server so that it listens to port 9306.
</para>
<para>
@@ -2966,8 +2957,8 @@
<para>
Only <literal>mysql</literal> is supported as the
- <replaceable>scheme</replaceable> at this point, and the password
- and port number are optional.
+ <replaceable>scheme</replaceable> at this point; the password and
+ port number are optional.
</para>
<para>
@@ -2982,18 +2973,19 @@
<para>
The use of <literal>COMMENT</literal> for specifying the connection
- string is non-optimal and will likely change in MySQL 5.1. Keep this
- in mind when you use <literal>FEDERATED</literal> tables, because it
- means you'll need to make some modifications when that happens.
+ string is non-optimal and is likely to change in MySQL 5.1. Keep
+ this in mind when you use <literal>FEDERATED</literal> tables,
+ because it means that modifications are likely to be required when
+ that happens.
</para>
<para>
- Also, because a password is stored in the connection string as plain
- text, it can be seen by any user who can use <literal>SHOW CREATE
- TABLE</literal> or <literal>SHOW TABLE STATUS</literal> for the
- <literal>FEDERATED</literal> table, or query the
- <literal>TABLES</literal> table of
- <literal>INFORMATION_SCHEMA</literal>.
+ Because any password used is stored in the connection string as
+ plain text, it can be seen by any user who can use <literal>SHOW
+ CREATE TABLE</literal> or <literal>SHOW TABLE STATUS</literal> for
+ the <literal>FEDERATED</literal> table, or query the
+ <literal>TABLES</literal> table in the
+ <literal>INFORMATION_SCHEMA</literal> database.
</para>
</section>
@@ -3004,7 +2996,7 @@
<para>
What the <literal>FEDERATED</literal> storage engine does and
- doesn't support:
+ does not support:
</para>
<itemizedlist>
@@ -3023,10 +3015,14 @@
<listitem><para>
It is possible for one <literal>FEDERATED</literal> table to point
- to another, but you must be careful not to create a loop. You know
+ to another, but you must be careful not to create a loop.
+<!-- TODO: Colourful but not very professional. Rewrite or cut. -->
+<!--
+ You know
and have heard the screeching of audio feedback? You know what you
see visually when you place two mirrors in front of each other, how
the reflection continues for eternity? Well, need we say more?!
+-->
</para></listitem>
<listitem><para>
@@ -3036,9 +3032,9 @@
<listitem><para>
There is no way for the <literal>FEDERATED</literal> engine to know
if the remote table has changed. The reason for this is that this
- table has to work like a data file that would never be written to
- by anything other than the database. The integrity of the data in
- the local table could be breached if there was any change to the
+ table must work like a data file that would never be written to by
+ anything other than the database. The integrity of the data in the
+ local table could be breached if there was any change to the
remote database.
</para></listitem>
@@ -3048,15 +3044,18 @@
<literal>UPDATE</literal>, <literal>DELETE</literal>, and indexes.
It does not support <literal>ALTER TABLE</literal>, <literal>DROP
TABLE</literal>, or any other Data Definition Language statements.
- The first implementation does not use Prepared statements. It
+ The current implementation does not use Prepared statements.
+<!--
+ It
remains to be seen whether the limited subset of the client API for
the server supports this capability.
+-->
</para></listitem>
<listitem><para>
The implementation uses <literal>SELECT</literal>,
- <literal>INSERT</literal>, <literal>UPDATE</literal>,
- <literal>DELETE</literal> and not <literal>HANDLER</literal>.
+ <literal>INSERT</literal>, <literal>UPDATE</literal>, and
+ <literal>DELETE</literal>, but not <literal>HANDLER</literal>.
</para></listitem>
<listitem><para>
@@ -3093,9 +3092,9 @@
</indexterm>
<para>
- The <literal>ARCHIVE</literal> storage engine was added in MySQL
- 4.1.3. It is used for storing large amounts of data without indexes
- in a very small footprint.
+ The <literal>ARCHIVE</literal> storage engine is used for storing
+ large amounts of data without indexes with a relatively small
+ footprint.
</para>
<para>
@@ -3117,11 +3116,12 @@
<para>
The <literal>ARCHIVE</literal> engine supports only
- <literal>INSERT</literal> and <literal>SELECT</literal>. (No deletes,
- replaces, or updates.) A <literal>SELECT</literal> performs a
- complete table scan. Records are compressed as they are inserted. Use
- of <literal>OPTIMIZE TABLE</literal> can analyze the table and pack
- it into a smaller format.
+ <literal>INSERT</literal> and <literal>SELECT</literal>. This means
+ that you cannot perform <literal>DELETE</literal>,
+ <literal>REPLACE</literal>, or <literal>update</literal> queries. A
+ <literal>SELECT</literal> performs a complete table scan. Records are
+ compressed as they are inserted. <literal>OPTIMIZE
+ TABLE</literal> can be used to compress the table.
</para>
<para>
@@ -3148,9 +3148,8 @@
</indexterm>
<para>
- The <literal>CSV</literal> storage engine was added in MySQL 4.1.4.
- This engine stores data in text files using comma-separated-values
- format.
+ The <literal>CSV</literal> storage engine stores data in text files
+ using comma-separated values format.
</para>
<para>
@@ -3189,8 +3188,8 @@
<para>
If you examine the <filename>test.CSV</filename> file in the database
- directory after executing the preceding statements, its contents look
- like this:
+ directory created by executing the preceding statements, its contents
+ should look like this:
</para>
<programlisting>
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (jon:1.3015) | jon | 14 Jul |
