Author: mcbrown
Date: 2006-08-15 08:31:40 +0200 (Tue, 15 Aug 2006)
New Revision: 3018
Log:
Splitting the 5.0 storage engines chapter into separate files
Added:
trunk/refman-5.0/se-archive.xml
trunk/refman-5.0/se-bdb.xml
trunk/refman-5.0/se-blackhole.xml
trunk/refman-5.0/se-csv.xml
trunk/refman-5.0/se-example.xml
trunk/refman-5.0/se-federated.xml
trunk/refman-5.0/se-memory.xml
trunk/refman-5.0/se-merge.xml
trunk/refman-5.0/se-myisam.xml
Modified:
trunk/refman-5.0/Makefile
trunk/refman-5.0/storage-engines.xml
Modified: trunk/refman-5.0/Makefile
===================================================================
--- trunk/refman-5.0/Makefile 2006-08-15 04:18:48 UTC (rev 3017)
+++ trunk/refman-5.0/Makefile 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 6197 bytes
@@ -28,7 +28,7 @@
MANUAL_SRCS_EXTRA = versions.ent ../refman-common/fixedchars.ent ../refman-common/urls.ent
-MANUAL_SRCS = $(MANUAL_SRCS_EXTRA) manual.xml apis.xml charset.xml client-utility-programs.xml connector-j.xml connector-mxj.xml connector-net.xml connector-odbc.xml connector-php.xml connectors.xml copyright.xml database-administration.xml data-types.xml errmsgs-client.xml errmsgs-server.xml error-handling.xml extending-mysql.xml functions.xml images/blackhole-1.png images/cluster-components-1.png images/cluster-upgrade-downgrade-table.png images/cnet-win-install-step1.png images/cnet-win-install-step2.png images/cnet-win-install-step3.png images/cnet-win-install-step4.png images/cnet-win-install-step6.png images/multi-comp-1.png images/myaccess-odbc.png images/myaccess.png images/myarchitecture.png images/myflowchart.png images/myodbc-excel-step1.png images/myodbc-excel-step2.png images/myodbc-excel-step3.png images/myodbc-excel-step4.png images/myodbc-excel-step5.png images/myodbc-macosx-install-step1.png images/myodbc-macosx-install-step2.png images/myodbc-macosx-instal!
l-step3.png images/myodbc-macosx-install-step4.png images/myodbc-macosx-install-step5.png images/myodbc-macosx-install-step6.png images/myodbc-macosx-odbcadmin-adddsn.png images/myodbc-macosx-odbcadmin-main.png images/myodbc-macosx-odbcadmin-sampledsn.png images/myodbc-macosx-odbcadmin-tracing.png images/myodbc-win-install-step1.png images/myodbc-win-install-step2.png images/myodbc-win-install-step3.png images/myodbc-win-install-step4.png images/myodbc-win-odbcadmin-adddsn.png images/myodbc-win-odbcadmin-advanced.png images/myodbc-win-odbcadmin-driverselect.png images/myodbc-win-odbcadmin-main.png images/myodbc-win-odbcadmin-options.png images/myodbc-win-odbcadmin-sampledsn.png images/myodbc-win-tracing.png images/replicas-groups-1-1.png images/replicas-groups-1-2.png information-schema.xml innodb.xml installing.xml introduction.xml language-structure.xml legalnotice.en.xml limits.xml mysql-cluster.xml news.xml optimization.xml porting.xml precision-math.xml preface.xml pro!
blems.xml ../refman-common/bug-reports.xml ../refman-common/cj!
k-faq.en
.xml ../refman-common/credits.xml ../refman-common/environment-variables.xml ../refman-common/gpl-license.xml ../refman-common/information-sources.xml ../refman-common/manual-conventions.en.xml ../refman-common/maxdb.en.xml ../refman-common/mysql-floss-license-exception.xml ../refman-common/news-5.0.xml ../refman-common/news-cluster.xml ../refman-common/news-connector-j.xml ../refman-common/news-connector-net.xml ../refman-common/news-myodbc.xml ../refman-common/regexp.xml ../refman-common/reserved-new-5.0.xml ../refman-common/what-is.en.xml ../refman-common/what-is-mysql-ab.en.xml replication.xml reservedwords.xml restrictions.xml spatial-extensions.xml sql-syntax.xml storage-engines.xml stored-procedures.xml triggers.xml tutorial.xml using-mysql-programs.xml views.xml
+MANUAL_SRCS = $(MANUAL_SRCS_EXTRA) manual.xml apis.xml charset.xml client-utility-programs.xml connector-j.xml connector-mxj.xml connector-net.xml connector-odbc.xml connector-php.xml connectors.xml copyright.xml database-administration.xml data-types.xml errmsgs-client.xml errmsgs-server.xml error-handling.xml extending-mysql.xml functions.xml images/blackhole-1.png images/cluster-components-1.png images/cluster-upgrade-downgrade-table.png images/cnet-win-install-step1.png images/cnet-win-install-step2.png images/cnet-win-install-step3.png images/cnet-win-install-step4.png images/cnet-win-install-step6.png images/multi-comp-1.png images/myaccess-odbc.png images/myaccess.png images/myarchitecture.png images/myflowchart.png images/myodbc-excel-step1.png images/myodbc-excel-step2.png images/myodbc-excel-step3.png images/myodbc-excel-step4.png images/myodbc-excel-step5.png images/myodbc-macosx-install-step1.png images/myodbc-macosx-install-step2.png images/myodbc-macosx-instal!
l-step3.png images/myodbc-macosx-install-step4.png images/myodbc-macosx-install-step5.png images/myodbc-macosx-install-step6.png images/myodbc-macosx-odbcadmin-adddsn.png images/myodbc-macosx-odbcadmin-main.png images/myodbc-macosx-odbcadmin-sampledsn.png images/myodbc-macosx-odbcadmin-tracing.png images/myodbc-win-install-step1.png images/myodbc-win-install-step2.png images/myodbc-win-install-step3.png images/myodbc-win-install-step4.png images/myodbc-win-odbcadmin-adddsn.png images/myodbc-win-odbcadmin-advanced.png images/myodbc-win-odbcadmin-driverselect.png images/myodbc-win-odbcadmin-main.png images/myodbc-win-odbcadmin-options.png images/myodbc-win-odbcadmin-sampledsn.png images/myodbc-win-tracing.png images/replicas-groups-1-1.png images/replicas-groups-1-2.png information-schema.xml innodb.xml installing.xml introduction.xml language-structure.xml legalnotice.en.xml limits.xml mysql-cluster.xml news.xml optimization.xml porting.xml precision-math.xml preface.xml pro!
blems.xml ../refman-common/bug-reports.xml ../refman-common/cj!
k-faq.en
.xml ../refman-common/credits.xml ../refman-common/environment-variables.xml ../refman-common/gpl-license.xml ../refman-common/information-sources.xml ../refman-common/manual-conventions.en.xml ../refman-common/maxdb.en.xml ../refman-common/mysql-floss-license-exception.xml ../refman-common/news-5.0.xml ../refman-common/news-cluster.xml ../refman-common/news-connector-j.xml ../refman-common/news-connector-net.xml ../refman-common/news-myodbc.xml ../refman-common/regexp.xml ../refman-common/reserved-new-5.0.xml ../refman-common/what-is.en.xml ../refman-common/what-is-mysql-ab.en.xml replication.xml reservedwords.xml restrictions.xml spatial-extensions.xml sql-syntax.xml storage-engines.xml se-archive.xml se-blackhole.xml se-example.xml se-memory.xml se-myisam.xml se-bdb.xml se-csv.xml se-federated.xml se-merge.xml stored-procedures.xml triggers.xml tutorial.xml using-mysql-programs.xml views.xml
manual-prepped.xml: $(MANUAL_SRCS)
manual-manprepped.xml: $(MANUAL_SRCS)
Added: trunk/refman-5.0/se-archive.xml
===================================================================
--- trunk/refman-5.0/se-archive.xml (rev 0)
+++ trunk/refman-5.0/se-archive.xml 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 148, Lines Deleted: 0; 6001 bytes
@@ -0,0 +1,148 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent">
+ %fixedchars.entities;
+ <!ENTITY % urls.entities SYSTEM "../refman-common/urls.ent">
+ %urls.entities;
+ <!ENTITY % versions.entities SYSTEM "versions.ent">
+ %versions.entities;
+]>
+ <section id="archive-storage-engine">
+
+ <title>The <literal>ARCHIVE</literal> Storage Engine</title>
+
+ <indexterm>
+ <primary><literal>ARCHIVE</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>storage engine</primary>
+ <secondary>ARCHIVE</secondary>
+ </indexterm>
+
+ <para>
+ The <literal>ARCHIVE</literal> storage engine is used for storing
+ large amounts of data without indexes in a very small footprint.
+ </para>
+
+ <para>
+ The <literal>ARCHIVE</literal> storage engine is included in MySQL
+ binary distributions. To enable this storage engine if you build
+ MySQL from source, invoke <command>configure</command> with the
+ <option>--with-archive-storage-engine</option> option.
+ </para>
+
+ <para>
+ To examine the source for the <literal>ARCHIVE</literal> engine,
+ look in the <filename>sql</filename> directory of a MySQL source
+ distribution.
+ </para>
+
+ <para>
+ You can check whether the <literal>ARCHIVE</literal> storage
+ engine is available with this statement:
+ </para>
+
+ <programlisting>
+mysql> <userinput>SHOW VARIABLES LIKE 'have_archive';</userinput>
+</programlisting>
+
+ <para>
+ When you create an <literal>ARCHIVE</literal> table, the server
+ creates a table format file in the database directory. The file
+ begins with the table name and has an <filename>.frm</filename>
+ extension. The storage engine creates other files, all having
+ names beginning with the table name. The data and metadata files
+ have extensions of <filename>.ARZ</filename> and
+ <filename>.ARM</filename>, respectively. An
+ <filename>.ARN</filename> file may appear during optimization
+ operations.
+ </para>
+
+ <para>
+ The <literal>ARCHIVE</literal> engine supports
+ <literal>INSERT</literal> and <literal>SELECT</literal>, but not
+ <literal>DELETE</literal>, <literal>REPLACE</literal>, or
+ <literal>UPDATE</literal>. It does support <literal>ORDER
+ BY</literal> operations, <literal>BLOB</literal> columns, and
+ basically all but spatial data types (see
+ <xref linkend="mysql-spatial-datatypes"/>). The
+ <literal>ARCHIVE</literal> engine uses row-level locking.
+ </para>
+
+ <para>
+ <emphasis role="bold">Storage:</emphasis> Rows are compressed as
+ they are inserted. The <literal>ARCHIVE</literal> engine uses
+ <literal>zlib</literal> lossless data compression (see
+ <ulink url="http://www.zlib.net/"/>). You can use
+ <literal>OPTIMIZE TABLE</literal> to analyze the table and pack it
+ into a smaller format (for a reason to use <literal>OPTIMIZE
+ TABLE</literal>, see later in this section). Beginning with MySQL
+ 5.0.15, the engine also supports <literal>CHECK TABLE</literal>.
+ There are several types of insertions that are used:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ An <literal>INSERT</literal> statement just pushes rows into a
+ compression buffer, and that buffer flushes as necessary. The
+ insertion into the buffer is protected by a lock. A
+ <literal>SELECT</literal> forces a flush to occur, unless the
+ only insertions that have come in were <literal>INSERT
+ DELAYED</literal> (those flush as necessary). See
+ <xref linkend="insert-delayed"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A bulk insert is visible only after it completes, unless other
+ inserts occur at the same time, in which case it can be seen
+ partially. A <literal>SELECT</literal> never causes a flush of
+ a bulk insert unless a normal insert occurs while it is
+ loading.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">Retrieval</emphasis>: On retrieval, rows are
+ uncompressed on demand; there is no row cache. A
+ <literal>SELECT</literal> operation performs a complete table
+ scan: When a <literal>SELECT</literal> occurs, it finds out how
+ many rows are currently available and reads that number of rows.
+ <literal>SELECT</literal> is performed as a consistent read. Note
+ that lots of <literal>SELECT</literal> statements during insertion
+ can deteriorate the compression, unless only bulk or delayed
+ inserts are used. To achieve better compression, you can use
+ <literal>OPTIMIZE TABLE</literal> or <literal>REPAIR
+ TABLE</literal>. The number of rows in <literal>ARCHIVE</literal>
+ tables reported by <literal>SHOW TABLE STATUS</literal> is always
+ accurate. See <xref linkend="optimize-table"/>,
+ <xref linkend="repair-table"/>, and
+ <xref linkend="show-table-status"/>.
+ </para>
+
+ <para>
+ <emphasis role="bold">Additional resources</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ A forum dedicated to the <literal>ARCHIVE</literal> storage
+ engine is available at
+ <ulink url="&base-url-forum-list;?112"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
\ No newline at end of file
Added: trunk/refman-5.0/se-bdb.xml
===================================================================
--- trunk/refman-5.0/se-bdb.xml (rev 0)
+++ trunk/refman-5.0/se-bdb.xml 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 867, Lines Deleted: 0; 28114 bytes
@@ -0,0 +1,867 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent">
+ %fixedchars.entities;
+ <!ENTITY % urls.entities SYSTEM "../refman-common/urls.ent">
+ %urls.entities;
+ <!ENTITY % versions.entities SYSTEM "versions.ent">
+ %versions.entities;
+]>
+ <section id="bdb-storage-engine">
+
+ <title>The <literal>BDB</literal> (<literal>BerkeleyDB</literal>) Storage
+ Engine</title>
+
+ <indexterm>
+ <primary><literal>BDB</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>BerkeleyDB</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary><literal>BDB</literal></secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary><literal>Berkeley DB</literal></secondary>
+ </indexterm>
+
+ <para>
+ Sleepycat Software has provided MySQL with the Berkeley DB
+ transactional storage engine. This storage engine typically is
+ called <literal>BDB</literal> for short. <literal>BDB</literal>
+ tables may have a greater chance of surviving crashes and are also
+ capable of <literal>COMMIT</literal> and
+ <literal>ROLLBACK</literal> operations on transactions.
+ </para>
+
+ <para>
+ Support for the <literal>BDB</literal> storage engine is included
+ in MySQL source distributions is activated in MySQL-Max binary
+ distributions. The MySQL source distribution comes with a
+ <literal>BDB</literal> distribution that is patched to make it
+ work with MySQL. You cannot use a non-patched version of
+ <literal>BDB</literal> with MySQL.
+ </para>
+
+ <remark>
+ Is this still true in 5.0?
+ </remark>
+
+ <para>
+ 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 reproducible test cases. 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 <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 is used in many applications other
+ than MySQL.
+ </para>
+
+ <para>
+ For general information about Berkeley DB, please visit the
+ Sleepycat Web site, <ulink url="http://www.sleepycat.com/"/>.
+ </para>
+
+ <section id="bdb-portability">
+
+ <title>Operating Systems Supported by <literal>BDB</literal></title>
+
+ <para>
+ Currently, we know that the <literal>BDB</literal> storage
+ engine works with the following operating systems:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Linux 2.x Intel
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Sun Solaris (SPARC and x86)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ FreeBSD 4.x/5.x (x86, sparc64)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ IBM AIX 4.3.x
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ SCO OpenServer
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ SCO UnixWare 7.1.x
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Windows NT/2000/XP
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The <literal>BDB</literal> storage engine does
+ <emphasis>not</emphasis> work with the following operating
+ systems:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Linux 2.x Alpha
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Linux 2.x AMD64
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Linux 2.x IA-64
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Linux 2.x s390
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Mac OS X
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">Note</emphasis>: The preceding lists are
+ not complete. We update them as we receive more information.
+ </para>
+
+ <para>
+ If you build MySQL from source with support for
+ <literal>BDB</literal> tables, but the following error occurs
+ when you start <command>mysqld</command>, it means that the
+ <literal>BDB</literal> storage engine is not supported for your
+ architecture:
+ </para>
+
+<programlisting>
+bdb: architecture lacks fast mutexes: applications cannot be threaded
+Can't init databases
+</programlisting>
+
+ <para>
+ In this case, you must rebuild MySQL without
+ <literal>BDB</literal> support or start the server with the
+ <option>--skip-bdb</option> option.
+ </para>
+
+ </section>
+
+ <section id="bdb-install">
+
+ <title>Installing <literal>BDB</literal></title>
+
+ <para>
+ If you have downloaded a binary version of MySQL that includes
+ support for Berkeley DB, simply follow the usual binary
+ distribution installation instructions. (MySQL-Max distributions
+ include <literal>BDB</literal> support.)
+ </para>
+
+ <para>
+ If you build MySQL from source, you can enable
+ <literal>BDB</literal> support by invoking
+ <command>configure</command> with the
+ <option>--with-berkeley-db</option> option in addition to any
+ other options that you normally use. Download a MySQL
+ ¤t-series; distribution, change location into its
+ top-level directory, and run this command:
+ </para>
+
+<programlisting>
+shell> <userinput>./configure --with-berkeley-db [<replaceable>other-options</replaceable>]</userinput>
+</programlisting>
+
+ <para>
+ For more information, see <xref linkend="mysqld-max"/>,
+ <xref linkend="installing-binary"/>, and
+ <xref linkend="installing-source"/>.
+ </para>
+
+ </section>
+
+ <section id="bdb-start">
+
+ <title><literal>BDB</literal> Startup Options</title>
+
+ <para>
+ The following options to <command>mysqld</command> can be used
+ to change the behavior of the <literal>BDB</literal> storage
+ engine. For more information, see
+ <xref linkend="server-options"/>.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>bdb-home option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>bdb-home option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--bdb-home=<replaceable>path</replaceable></option>
+ </para>
+
+ <para>
+ The base directory for <literal>BDB</literal> tables. This
+ should be the same directory that you use for
+ <option>--datadir</option>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>bdb-lock-detect option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>bdb-lock-detect option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--bdb-lock-detect=<replaceable>method</replaceable></option>
+ </para>
+
+ <para>
+ The <literal>BDB</literal> lock detection method. The option
+ value should be <literal>DEFAULT</literal>,
+ <literal>OLDEST</literal>, <literal>RANDOM</literal>, or
+ <literal>YOUNGEST</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>bdb-logdir option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>bdb-logdir option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--bdb-logdir=<replaceable>file_name</replaceable></option>
+ </para>
+
+ <para>
+ The <literal>BDB</literal> log file directory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>bdb-no-recover option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>bdb-no-recover option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--bdb-no-recover</option>
+ </para>
+
+ <para>
+ Do not start Berkeley DB in recover mode.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>bdb-no-sync option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>bdb-no-sync option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--bdb-no-sync</option>
+ </para>
+
+ <para>
+ Don't synchronously flush the <literal>BDB</literal> logs.
+ This option is deprecated; use
+ <option>--skip-sync-bdb-logs</option> instead (see the
+ description for <option>--sync-bdb-logs</option>).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>bdb-shared-data option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>bdb-shared-data option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--bdb-shared-data</option>
+ </para>
+
+ <para>
+ Start Berkeley DB in multi-process mode. (Do not use
+ <literal>DB_PRIVATE</literal> when initializing Berkeley
+ DB.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>bdb-tmpdir option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>bdb-tmpdir option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--bdb-tmpdir=<replaceable>path</replaceable></option>
+ </para>
+
+ <para>
+ The <literal>BDB</literal> temporary file directory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>skip-bdb option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>skip-bdb option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--skip-bdb</option>
+ </para>
+
+ <para>
+ Disable the <literal>BDB</literal> storage engine.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>sync-bdb-logs option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>sync-bdb-logs option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--sync-bdb-logs</option>
+ </para>
+
+ <para>
+ Synchronously flush the <literal>BDB</literal> logs. This
+ option is enabled by default. Use
+ <option>--skip-sync-bdb-logs</option> to disable it.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ If you use the <option>--skip-bdb</option> option, MySQL does
+ not initialize the Berkeley DB library and this saves a lot of
+ memory. However, if you use this option, you cannot use
+ <literal>BDB</literal> tables. If you try to create a
+ <literal>BDB</literal> table, MySQL uses the default storage
+ engine instead.
+ </para>
+
+ <para>
+ Normally, you should start <command>mysqld</command> without the
+ <option>--bdb-no-recover</option> option if you intend to use
+ <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"/>.
+ </para>
+
+ <para>
+ With the <literal>bdb_max_lock</literal> variable, you can
+ specify the maximum number of locks that can be active on a
+ <literal>BDB</literal> table. The default is 10,000. You should
+ increase this if errors such as the following occur when you
+ perform long transactions or when <command>mysqld</command> has
+ to examine many rows to execute a query:
+ </para>
+
+<programlisting>
+bdb: Lock table is out of available locks
+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>
+ See also <xref linkend="server-system-variables"/>.
+ </para>
+
+ </section>
+
+ <section id="bdb-characteristics">
+
+ <title>Characteristics of <literal>BDB</literal> Tables</title>
+
+ <para>
+ Each <literal>BDB</literal> table is stored on disk in two
+ 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 format, and a
+ <filename>.db</filename> file contains the table data and
+ indexes.
+ </para>
+
+ <para>
+ To specify explicitly that you want a <literal>BDB</literal>
+ table, indicate that with an <literal>ENGINE</literal> table
+ option:
+ </para>
+
+<programlisting>
+CREATE TABLE t (i INT) ENGINE = BDB;
+</programlisting>
+
+ <para>
+ The older term <literal>TYPE</literal> is supported as a synonym
+ for <literal>ENGINE</literal> for backward compatibility, but
+ <literal>ENGINE</literal> is the preferred term and
+ <literal>TYPE</literal> is deprecated.
+ </para>
+
+ <para>
+ <literal>BerkeleyDB</literal> is a synonym for
+ <literal>BDB</literal> in the <literal>ENGINE</literal> table
+ option.
+ </para>
+
+ <para>
+ The <literal>BDB</literal> storage engine provides transactional
+ tables. The way you use these tables depends on the autocommit
+ mode:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you are running with autocommit enabled (which is the
+ default), changes to <literal>BDB</literal> tables are
+ committed immediately and cannot be rolled back.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you are running with autocommit disabled, changes do not
+ become permanent until you execute a
+ <literal>COMMIT</literal> statement. Instead of committing,
+ you can execute <literal>ROLLBACK</literal> to forget the
+ changes.
+ </para>
+
+ <para>
+ You can start a transaction with the <literal>START
+ TRANSACTION</literal> or <literal>BEGIN</literal> statement
+ to suspend autocommit, or with <literal>SET
+ AUTOCOMMIT=0</literal> to disable autocommit explicitly.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ For more information about transactions, see
+ <xref linkend="commit"/>.
+ </para>
+
+ <para>
+ The <literal>BDB</literal> storage engine has the following
+ characteristics:
+ </para>
+
+ <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.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL requires a primary key in each <literal>BDB</literal>
+ table so that each row can be uniquely identified. If you
+ don't create one explicitly by declaring a <literal>PRIMARY
+ KEY</literal>, MySQL creates and maintains a hidden primary
+ key for you. The hidden key has a length of five bytes and
+ is incremented for each insert attempt. This key does not
+ appear in the output of <literal>SHOW CREATE TABLE</literal>
+ or <literal>DESCRIBE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The primary key is faster than any other index, because it
+ is stored together with the row data. The other indexes are
+ stored as the key data plus the primary key, so it's
+ important to keep the primary key as short as possible to
+ save disk space and get better speed.
+ </para>
+
+ <para>
+ This behavior is similar to that of
+ <literal>InnoDB</literal>, where shorter primary keys save
+ space not only in the primary index but in secondary indexes
+ as well.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If all columns that you access in a <literal>BDB</literal>
+ table are part of the same index or part of the primary key,
+ MySQL can execute the query without having to access the
+ actual row. In a <literal>MyISAM</literal> table, this can
+ be done only if the columns are part of the same index.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Sequential scanning is slower for <literal>BDB</literal>
+ tables than for <literal>MyISAM</literal> tables because the
+ data in <literal>BDB</literal> tables is stored in B-trees
+ and not in a separate data file.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Key values are not prefix- or suffix-compressed like key
+ values in <literal>MyISAM</literal> tables. In other words,
+ key information takes a little more space in
+ <literal>BDB</literal> tables compared to
+ <literal>MyISAM</literal> tables.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ There are often holes in the <literal>BDB</literal> table to
+ allow you to insert new rows in the middle of the index
+ tree. This makes <literal>BDB</literal> tables somewhat
+ larger than <literal>MyISAM</literal> tables.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>SELECT COUNT(*) FROM
+ <replaceable>tbl_name</replaceable></literal> is slow for
+ <literal>BDB</literal> tables, because no row count is
+ maintained in the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The optimizer needs to know the approximate number of rows
+ in the table. MySQL solves this by counting inserts and
+ maintaining this in a separate segment in each
+ <literal>BDB</literal> table. If you don't issue a lot of
+ <literal>DELETE</literal> or <literal>ROLLBACK</literal>
+ statements, this number should be accurate enough for the
+ MySQL optimizer. However, MySQL stores the number only on
+ close, so it may be incorrect if the server terminates
+ unexpectedly. It should not be fatal even if this number is
+ not 100% correct. You can update the row count by using
+ <literal>ANALYZE TABLE</literal> or <literal>OPTIMIZE
+ TABLE</literal>. See <xref linkend="analyze-table"/>, and
+ <xref linkend="optimize-table"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Internal locking in <literal>BDB</literal> tables is done at
+ the page level.
+ </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 support transaction rollback, the <literal>BDB</literal>
+ storage engine maintains log files. For maximum performance,
+ you can use the <option>--bdb-logdir</option> option to
+ place the <literal>BDB</literal> logs on a different disk
+ than the one where your databases are located.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL performs a checkpoint each time a new
+ <literal>BDB</literal> log file is started, and removes any
+ <literal>BDB</literal> log files that are not needed for
+ current transactions. You can also use <literal>FLUSH
+ LOGS</literal> at any time to checkpoint the Berkeley DB
+ tables.
+ </para>
+
+ <para>
+ For disaster recovery, you should use table backups plus
+ MySQL's binary log. See <xref linkend="backup"/>.
+ </para>
+
+ <para>
+ <emphasis role="bold">Warning:</emphasis> If you delete old
+ log files that are still in use, <literal>BDB</literal> is
+ not able to do recovery at all and you may lose data if
+ something goes wrong.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Applications must always be prepared to handle cases where
+ any change of a <literal>BDB</literal> table may cause an
+ automatic rollback and any read may fail with a deadlock
+ error.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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> tables, for which
+ <command>mysqld</command> waits for sufficient free disk
+ space before continuing.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="bdb-restrictions">
+
+ <title>Restrictions on <literal>BDB</literal> Tables</title>
+
+ <remark role="todo">
+ [JS] Move this to Restrictions appendix?
+ </remark>
+
+ <para>
+ The following list indicates restrictions that you must observe
+ when using <literal>BDB</literal> tables:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Each <literal>BDB</literal> table stores in its
+ <filename>.db</filename> file the path to the file as it was
+ created. This is done to enable detection of locks in a
+ multi-user 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>
+
+ <listitem>
+ <para>
+ When making backups of <literal>BDB</literal> tables, you
+ must either use <command>mysqldump</command> or else make a
+ backup that includes the files for each
+ <literal>BDB</literal> table (the <filename>.frm</filename>
+ and <filename>.db</filename> files) as well as the
+ <literal>BDB</literal> log files. The <literal>BDB</literal>
+ storage engine stores unfinished transactions in its log
+ files and requires them to be present when
+ <command>mysqld</command> starts. The <literal>BDB</literal>
+ logs are the files in the data directory with names of the
+ form
+ <filename>log.<replaceable>NNNNNNNNNN</replaceable></filename>
+ (ten digits).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If a column that allows <literal>NULL</literal> values has a
+ unique index, only a single <literal>NULL</literal> value is
+ allowed. This differs from other storage engines, which
+ allow multiple <literal>NULL</literal> values in unique
+ indexes.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="bdb-errors">
+
+ <title>Errors That May Occur When Using <literal>BDB</literal> Tables</title>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If the following error occurs when you start
+ <command>mysqld</command> after upgrading, it means that the
+ current version of <literal>BDB</literal> doesn't support
+ the old log file format:
+ </para>
+
+<programlisting>
+bdb: Ignoring log file: .../log.<replaceable>NNNNNNNNNN</replaceable>:
+unsupported log version #
+</programlisting>
+
+ <para>
+ In this case, you must delete all <literal>BDB</literal>
+ logs from your data directory (the files that have names of
+ the form
+ <filename>log.<replaceable>NNNNNNNNNN</replaceable></filename>)
+ and restart <command>mysqld</command>. We also recommend
+ that you then use <command>mysqldump --opt</command> to dump
+ your <literal>BDB</literal> tables, drop the tables, and
+ restore them from the dump file.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If autocommit mode is disabled and you drop a
+ <literal>BDB</literal> table that is referenced in another
+ transaction, you may get error messages of the following
+ form in your MySQL error log:
+ </para>
+
+<programlisting>
+001119 23:43:56 bdb: Missing log fileid entry
+001119 23:43:56 bdb: txn_abort: Log undo failed for LSN:
+ 1 3644744: Invalid
+</programlisting>
+
+ <para>
+ This is not fatal, but the fix is not trivial. Until the
+ problem is fixed, we recommend that you not drop
+ <literal>BDB</literal> tables except while autocommit mode
+ is enabled.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ </section>
Added: trunk/refman-5.0/se-blackhole.xml
===================================================================
--- trunk/refman-5.0/se-blackhole.xml (rev 0)
+++ trunk/refman-5.0/se-blackhole.xml 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 151, Lines Deleted: 0; 5535 bytes
@@ -0,0 +1,151 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent">
+ %fixedchars.entities;
+ <!ENTITY % urls.entities SYSTEM "../refman-common/urls.ent">
+ %urls.entities;
+ <!ENTITY % versions.entities SYSTEM "versions.ent">
+ %versions.entities;
+]>
+ <section id="blackhole-storage-engine">
+
+ <title>The <literal>BLACKHOLE</literal> Storage Engine</title>
+
+ <indexterm>
+ <primary><literal>BLACKHOLE</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary>BLACKHOLE</secondary>
+ </indexterm>
+
+ <para>
+ The <literal>BLACKHOLE</literal> storage engine acts as a
+ <quote>black hole</quote> that accepts data but throws it away and
+ does not store it. Retrievals always return an empty result:
+ </para>
+
+ <programlisting>
+mysql> <userinput>CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;</userinput>
+Query OK, 0 rows affected (0.03 sec)
+
+mysql> <userinput>INSERT INTO test VALUES(1,'record one'),(2,'record two');</userinput>
+Query OK, 2 rows affected (0.00 sec)
+Records: 2 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SELECT * FROM test;</userinput>
+Empty set (0.00 sec)
+</programlisting>
+
+ <para>
+ The <literal>BLACKHOLE</literal> storage engine is included in
+ MySQL-Max binary distributions. To enable this storage engine if
+ you build MySQL from source, invoke <command>configure</command>
+ with the <option>--with-blackhole-storage-engine</option> option.
+ </para>
+
+ <para>
+ To examine the source for the <literal>BLACKHOLE</literal> engine,
+ look in the <filename>sql</filename> directory of a MySQL source
+ distribution.
+ </para>
+
+ <para>
+ When you create a <literal>BLACKHOLE</literal> table, the server
+ creates a table format file in the database directory. The file
+ begins with the table name and has an <filename>.frm</filename>
+ extension. There are no other files associated with the table.
+ </para>
+
+ <para>
+ The <literal>BLACKHOLE</literal> storage engine supports all kinds
+ of indexes. That is, you can include index declarations in the
+ table definition.
+ </para>
+
+ <para>
+ You can check whether the <literal>BLACKHOLE</literal> storage
+ engine is available with this statement:
+ </para>
+
+ <programlisting>
+mysql> <userinput>SHOW VARIABLES LIKE 'have_blackhole_engine';</userinput>
+</programlisting>
+
+ <para>
+ Inserts into a <literal>BLACKHOLE</literal> table do not store any
+ data, but if the binary log is enabled, the SQL statements are
+ logged (and replicated to slave servers). This can be useful as a
+ repeater or filter mechanism. For example, suppose that your
+ application requires slave-side filtering rules, but transferring
+ all binary log data to the slave first results in too much
+ traffic. In such a case, it is possible to set up on the master
+ host a <quote>dummy</quote> slave process whose default storage
+ engine is <literal>BLACKHOLE</literal>, depicted as follows:
+ </para>
+
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/blackhole-1.png" format="PNG"/>
+ </imageobject>
+ <textobject>
+ <phrase lang="en">Replication using <literal>BLACKHOLE</literal>
+ for filtering</phrase>
+ </textobject>
+ </mediaobject>
+
+ <para>
+ The master writes to its binary log. The <quote>dummy</quote>
+ <command>mysqld</command> process acts as a slave, applying the
+ desired combination of <literal>replicate-do-*</literal> and
+ <literal>replicate-ignore-*</literal> rules, and writes a new,
+ filtered binary log of its own. (See
+ <xref linkend="replication-options"/>.) This filtered log is
+ provided to the slave.
+ </para>
+
+ <para>
+ The dummy process does not actually store any data, so there is
+ little processing overhead incurred by running the additional
+ <command>mysqld</command> process on the replication master host.
+ This type of setup can be repeated with additional replication
+ slaves.
+ </para>
+
+ <para>
+ Other possible uses for the <literal>BLACKHOLE</literal> storage
+ engine include:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Verification of dump file syntax.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Measurement of the overhead from binary logging, by comparing
+ performance using <literal>BLACKHOLE</literal> with and
+ without binary logging enabled.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>BLACKHOLE</literal> is essentially a
+ <quote>no-op</quote> storage engine, so it could be used for
+ finding performance bottlenecks not related to the storage
+ engine itself.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
\ No newline at end of file
Added: trunk/refman-5.0/se-csv.xml
===================================================================
--- trunk/refman-5.0/se-csv.xml (rev 0)
+++ trunk/refman-5.0/se-csv.xml 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 100, Lines Deleted: 0; 3508 bytes
@@ -0,0 +1,100 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent">
+ %fixedchars.entities;
+ <!ENTITY % urls.entities SYSTEM "../refman-common/urls.ent">
+ %urls.entities;
+ <!ENTITY % versions.entities SYSTEM "versions.ent">
+ %versions.entities;
+]>
+ <section id="csv-storage-engine">
+
+ <title>The <literal>CSV</literal> Storage Engine</title>
+
+ <indexterm>
+ <primary><literal>CSV</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary>CSV</secondary>
+ </indexterm>
+
+ <para>
+ The <literal>CSV</literal> storage engine stores data in text
+ files using comma-separated values format.
+ </para>
+
+ <para>
+ To enable this storage engine, use the
+ <option>--with-csv-storage-engine</option> option to
+ <command>configure</command> when you build MySQL.
+ </para>
+
+ <para>
+ The <literal>CSV</literal> storage engine is included in MySQL-Max
+ binary distributions. To enable this storage engine if you build
+ MySQL from source, invoke <command>configure</command> with the
+ <option>--with-csv-storage-engine</option> option.
+ </para>
+
+ <para>
+ To examine the source for the <literal>CSV</literal> engine, look
+ in the <filename>sql/examples</filename> directory of a MySQL
+ source distribution.
+ </para>
+
+ <para>
+ When you create a <literal>CSV</literal> table, the server creates
+ a table format file in the database directory. The file begins
+ with the table name and has an <filename>.frm</filename>
+ extension. The storage engine also creates a data file. Its name
+ begins with the table name and has a <filename>.CSV</filename>
+ extension. The data file is a plain text file. When you store data
+ into the table, the storage engine saves it into the data file in
+ comma-separated values format.
+ </para>
+
+ <programlisting>
+mysql> <userinput>CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV;</userinput>
+Query OK, 0 rows affected (0.12 sec)
+
+mysql> <userinput>INSERT INTO test VALUES(1,'record one'),(2,'record two');</userinput>
+Query OK, 2 rows affected (0.00 sec)
+Records: 2 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>SELECT * FROM test;</userinput>
++------+------------+
+| i | c |
++------+------------+
+| 1 | record one |
+| 2 | record two |
++------+------------+
+2 rows in set (0.00 sec)
+</programlisting>
+
+ <para>
+ If you examine the <filename>test.CSV</filename> file in the
+ database directory created by executing the preceding statements,
+ its contents should look like this:
+ </para>
+
+ <programlisting>
+"1","record one"
+"2","record two"
+</programlisting>
+
+ <para>
+ This format can be read, and even written, by spreadsheet
+ applications such as Microsoft Excel or StarOffice Calc.
+ </para>
+
+ <para>
+ The <literal>CSV</literal> storage engine does not support
+ indexing.
+ </para>
+
+ </section>
+
\ No newline at end of file
Added: trunk/refman-5.0/se-example.xml
===================================================================
--- trunk/refman-5.0/se-example.xml (rev 0)
+++ trunk/refman-5.0/se-example.xml 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 71, Lines Deleted: 0; 2732 bytes
@@ -0,0 +1,71 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent">
+ %fixedchars.entities;
+ <!ENTITY % urls.entities SYSTEM "../refman-common/urls.ent">
+ %urls.entities;
+ <!ENTITY % versions.entities SYSTEM "versions.ent">
+ %versions.entities;
+]>
+ <section id="example-storage-engine">
+
+ <title>The <literal>EXAMPLE</literal> Storage Engine</title>
+
+ <indexterm>
+ <primary><literal>EXAMPLE</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary>EXAMPLE</secondary>
+ </indexterm>
+
+ <para>
+ 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>
+ The <literal>EXAMPLE</literal> storage engine is included in
+ MySQL-Max binary distributions. To enable this storage engine if
+ you build MySQL from source, invoke <command>configure</command>
+ with the <option>--with-example-storage-engine</option> option.
+ </para>
+
+ <para>
+ To examine the source for the <literal>EXAMPLE</literal> engine,
+ look in the <filename>sql/examples</filename> directory of a MySQL
+ source distribution.
+ </para>
+
+ <para>
+ When you create an <literal>EXAMPLE</literal> table, the server
+ creates a table format file in the database directory. The file
+ begins with the table name and has an <filename>.frm</filename>
+ extension. No other files are created. No data can be stored into
+ the table. Retrievals return an empty result.
+ </para>
+
+ <programlisting>
+mysql> <userinput>CREATE TABLE test (i INT) ENGINE = EXAMPLE;</userinput>
+Query OK, 0 rows affected (0.78 sec)
+
+mysql> <userinput>INSERT INTO test VALUES(1),(2),(3);</userinput>
+ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option
+
+mysql> <userinput>SELECT * FROM test;</userinput>
+Empty set (0.31 sec)
+</programlisting>
+
+ <para>
+ The <literal>EXAMPLE</literal> storage engine does not support
+ indexing.
+ </para>
+
+ </section>
+
\ No newline at end of file
Added: trunk/refman-5.0/se-federated.xml
===================================================================
--- trunk/refman-5.0/se-federated.xml (rev 0)
+++ trunk/refman-5.0/se-federated.xml 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 391, Lines Deleted: 0; 13507 bytes
@@ -0,0 +1,391 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent">
+ %fixedchars.entities;
+ <!ENTITY % urls.entities SYSTEM "../refman-common/urls.ent">
+ %urls.entities;
+ <!ENTITY % versions.entities SYSTEM "versions.ent">
+ %versions.entities;
+]>
+ <section id="federated-storage-engine">
+
+ <title>The <literal>FEDERATED</literal> Storage Engine</title>
+
+ <indexterm>
+ <primary><literal>FEDERATED</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary>FEDERATED</secondary>
+ </indexterm>
+
+ <para>
+ 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>
+ The <literal>FEDERATED</literal> storage engine is included in
+ MySQL-Max binary distributions. To enable this storage engine if
+ you build MySQL from source, invoke <command>configure</command>
+ with the <option>--with-federated-storage-engine</option> option.
+ </para>
+
+ <para>
+ To examine the source for the <literal>FEDERATED</literal> engine,
+ look in the <filename>sql</filename> directory of a source
+ distribution for MySQL 5.0.3 or newer.
+ </para>
+
+ <para>
+ <emphasis role="bold">Additional resources</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ A forum dedicated to the <literal>FEDERATED</literal> storage
+ engine is available at
+ <ulink url="&base-url-forum-list;?105"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <section id="federated-description">
+
+ <title>Description of the <literal>FEDERATED</literal> Storage Engine</title>
+
+ <para>
+ When you create a <literal>FEDERATED</literal> table, the server
+ creates a table format file in the database directory. The file
+ begins with the table name and has an <filename>.frm</filename>
+ extension. No other files are created, because the actual data
+ is in a remote table. This differs from the way that storage
+ engines for local tables work.
+ </para>
+
+ <para>
+ For local database tables, data files are local. For example, if
+ you create a <literal>MyISAM</literal> table named
+ <literal>users</literal>, the <literal>MyISAM</literal> handler
+ creates a data file named <literal>users.MYD</literal>. A
+ handler for local tables reads, inserts, deletes, and updates
+ data in local data files, and rows are stored in a format
+ particular to the handler. To read rows, the handler must parse
+ data into columns. To write rows, column values must be
+ converted to the row format used by the handler and written to
+ the local data file.
+ </para>
+
+ <para>
+ With the MySQL <literal>FEDERATED</literal> storage engine,
+ there are no local data files for a table (for example, there is
+ no <filename>.MYD</filename> file). Instead, a remote database
+ stores the data that normally would be in the table. The local
+ server connects to a remote server, and uses the MySQL client
+ API to read, delete, update, and insert data in the remote
+ table. Data retrieval is initiated via a <literal>SELECT * FROM
+ <replaceable>tbl_name</replaceable></literal> SQL statement. To
+ read the result, rows are fetched one at a time by using the
+ <literal>mysql_fetch_row()</literal> C API function, and then
+ converting the columns in the <literal>SELECT</literal> result
+ set to the format that the <literal>FEDERATED</literal> handler
+ expects.
+ </para>
+
+ <para>
+ The flow of information is as follows:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ SQL calls issued locally
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL handler API (data in handler format)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL client API (data converted to SQL calls)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Remote database -> MySQL client API
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Convert result sets (if any) to handler format
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Handler API -> Result rows or rows-affected count to
+ local
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ </section>
+
+ <section id="federated-use">
+
+ <title>How to use <literal>FEDERATED</literal> Tables</title>
+
+ <remark role="todo">
+ Stuff we could cover: - If federated table is replicated, the
+ slave hosts must be able to use the account in the CONNECTION to
+ connect to the remote server.
+ </remark>
+
+ <para>
+ The procedure for using <literal>FEDERATED</literal> tables is
+ very simple. Normally, you have two servers running, either both
+ on the same host or on different hosts. (It is possible for a
+ <literal>FEDERATED</literal> table to use another table that is
+ managed by the same server, although there is little point in
+ doing so.)
+ </para>
+
+ <para>
+ First, you must have a table on the remote server that you want
+ to access by using a <literal>FEDERATED</literal> table. Suppose
+ that the remote table is in the <literal>federated</literal>
+ database and is defined like this:
+ </para>
+
+<programlisting>
+CREATE TABLE test_table (
+ id INT(20) NOT NULL AUTO_INCREMENT,
+ name VARCHAR(32) NOT NULL DEFAULT '',
+ other INT(20) NOT NULL DEFAULT '0',
+ PRIMARY KEY (id),
+ INDEX name (name),
+ INDEX other_key (other)
+)
+ENGINE=MyISAM
+DEFAULT CHARSET=latin1;
+</programlisting>
+
+ <para>
+ The example uses a <literal>MyISAM</literal> table, but the
+ table could use any storage engine.
+ </para>
+
+ <para>
+ Next, create a <literal>FEDERATED</literal> table on the local
+ server for accessing the remote table:
+ </para>
+
+<programlisting>
+CREATE TABLE federated_table (
+ id INT(20) NOT NULL AUTO_INCREMENT,
+ name VARCHAR(32) NOT NULL DEFAULT '',
+ other INT(20) NOT NULL DEFAULT '0',
+ PRIMARY KEY (id),
+ INDEX name (name),
+ INDEX other_key (other)
+)
+ENGINE=FEDERATED
+DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@remote_host:9306/federated/test_table';
+</programlisting>
+
+ <para>
+ (Before MySQL 5.0.13, use <literal>COMMENT</literal> rather than
+ <literal>CONNECTION</literal>.)
+ </para>
+
+ <para>
+ 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>CONNECTION</literal> table option is a connection
+ string that indicates to the <literal>FEDERATED</literal> engine
+ how to connect to the remote server.
+ </para>
+
+ <para>
+ The <literal>FEDERATED</literal> engine creates only the
+ <filename>test_table.frm</filename> file in the
+ <literal>federated</literal> database.
+ </para>
+
+ <para>
+ The remote host information indicates the remote server to which
+ your local server connects, and the database and table
+ information indicates which remote table to use as the data
+ source. In this example, the remote server is indicated to be
+ running as <literal>remote_host</literal> on port 9306, so there
+ must be a MySQL server running on the remote host and listening
+ to port 9306.
+ </para>
+
+ <para>
+ The general form of the connection string in the
+ <literal>CONNECTION</literal> option is as follows:
+ </para>
+
+<programlisting>
+<replaceable>scheme</replaceable>://<replaceable>user_name</replaceable>[:<replaceable>password</replaceable>]@<replaceable>host_name</replaceable>[:<replaceable>port_num</replaceable>]/<replaceable>db_name</replaceable>/<replaceable>tbl_name</replaceable>
+</programlisting>
+
+ <para>
+ Only <literal>mysql</literal> is supported as the
+ <replaceable>scheme</replaceable> value at this point; the
+ password and port number are optional.
+ </para>
+
+ <para>
+ Here are some example connection strings:
+ </para>
+
+<programlisting>
+CONNECTION='mysql://username:password@hostname:port/database/tablename'
+CONNECTION='mysql://username@hostname/database/tablename'
+CONNECTION='mysql://username:password@hostname/database/tablename'
+</programlisting>
+
+ <para>
+ The use of <literal>CONNECTION</literal> for specifying the
+ connection string is non-optimal and is likely to change in
+ future. Keep this in mind for applications that use
+ <literal>FEDERATED</literal> tables. Such applications are
+ likely to need modification if the format for specifying
+ connection information changes.
+ </para>
+
+ <para>
+ Because any password given in the connection string is stored 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>
+
+ <section id="federated-limitations">
+
+ <title>Limitations of the <literal>FEDERATED</literal> Storage Engine</title>
+
+ <para>
+ The following items indicate features that the
+ <literal>FEDERATED</literal> storage engine does and does not
+ support:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ In the first version, the remote server must be a MySQL
+ server. Support by <literal>FEDERATED</literal> for other
+ database engines may be added in the future.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The remote table that a <literal>FEDERATED</literal> table
+ points to <emphasis>must</emphasis> exist before you try to
+ access the table through the <literal>FEDERATED</literal>
+ table.
+ </para>
+ </listitem>
+
+ <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.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ There is no support for transactions.
+ </para>
+ </listitem>
+
+ <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 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>
+
+ <listitem>
+ <para>
+ The <literal>FEDERATED</literal> storage engine supports
+ <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>, and
+ indexes. It does not support <literal>ALTER TABLE</literal>,
+ or any Data Definition Language statements other than
+ <literal>DROP TABLE</literal>. The current implementation
+ does not use Prepared statements.
+
+ <remark>
+ It remains to be seen whether the limited subset of the
+ client API for the server supports this capability.
+ </remark>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Any <literal>DROP TABLE</literal> statement issued against a
+ FEDERATED table will only drop the local table, not the
+ remote table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The implementation uses <literal>SELECT</literal>,
+ <literal>INSERT</literal>, <literal>UPDATE</literal>, and
+ <literal>DELETE</literal>, but not
+ <literal>HANDLER</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>FEDERATED</literal> tables do not work with the
+ query cache.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Some of these limitations may be lifted in future versions of
+ the <literal>FEDERATED</literal> handler.
+ </para>
+
+ </section>
+
+ </section>
Added: trunk/refman-5.0/se-memory.xml
===================================================================
--- trunk/refman-5.0/se-memory.xml (rev 0)
+++ trunk/refman-5.0/se-memory.xml 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 337, Lines Deleted: 0; 11927 bytes
@@ -0,0 +1,337 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent">
+ %fixedchars.entities;
+ <!ENTITY % urls.entities SYSTEM "../refman-common/urls.ent">
+ %urls.entities;
+ <!ENTITY % versions.entities SYSTEM "versions.ent">
+ %versions.entities;
+]>
+ <section id="memory-storage-engine">
+
+ <title>The <literal>MEMORY</literal> (<literal>HEAP</literal>) Storage Engine</title>
+
+ <remark role="todo">
+ Parts of this may be true only for hash indexes. Now that MEMORY
+ supports BTREE indexes, some statements may need to be qualified.
+ </remark>
+
+ <indexterm>
+ <primary><literal>HEAP</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary><literal>MEMORY</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary><literal>MEMORY</literal></secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary><literal>HEAP</literal></secondary>
+ </indexterm>
+
+ <para>
+ The <literal>MEMORY</literal> storage engine creates tables with
+ contents that are stored in memory. Formerly, these were known as
+ <literal>HEAP</literal> tables. <literal>MEMORY</literal> is the
+ preferred term, although <literal>HEAP</literal> remains supported
+ for backward compatibility.
+ </para>
+
+ <para>
+ Each <literal>MEMORY</literal> table is associated with one disk
+ file. The filename begins with the table name and has an extension
+ of <filename>.frm</filename> to indicate that it stores the table
+ definition.
+ </para>
+
+ <para>
+ To specify explicitly that you want to create a
+ <literal>MEMORY</literal> table, indicate that with an
+ <literal>ENGINE</literal> table option:
+ </para>
+
+<programlisting>
+CREATE TABLE t (i INT) ENGINE = MEMORY;
+</programlisting>
+
+ <para>
+ The older term <literal>TYPE</literal> is supported as a synonym
+ for <literal>ENGINE</literal> for backward compatibility, but
+ <literal>ENGINE</literal> is the preferred term and
+ <literal>TYPE</literal> is deprecated.
+ </para>
+
+ <para>
+ As indicated by the name, <literal>MEMORY</literal> tables are
+ stored in memory. They use hash indexes by default, which makes
+ them very fast, and very useful for creating temporary tables.
+ However, when the server shuts down, all rows stored in
+ <literal>MEMORY</literal> tables are 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>
+ This example shows how you might create, use, and remove a
+ <literal>MEMORY</literal> table:
+ </para>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE test ENGINE=MEMORY</userinput>
+ -> <userinput>SELECT ip,SUM(downloads) AS down</userinput>
+ -> <userinput>FROM log_table GROUP BY ip;</userinput>
+mysql> <userinput>SELECT COUNT(ip),AVG(down) FROM test;</userinput>
+mysql> <userinput>DROP TABLE test;</userinput>
+</programlisting>
+
+ <para>
+ <literal>MEMORY</literal> tables have the following
+ characteristics:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Space for <literal>MEMORY</literal> tables is allocated in
+ small 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 can have up to 32 indexes per
+ table, 16 columns per index and a maximum key length of 500
+ bytes.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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>
+CREATE TABLE lookup
+ (id INT, INDEX USING HASH (id))
+ ENGINE = MEMORY;
+CREATE TABLE lookup
+ (id INT, INDEX USING BTREE (id))
+ ENGINE = MEMORY;
+</programlisting>
+
+ <para>
+ General characteristics of B-tree and hash indexes are
+ described in <xref linkend="mysql-indexes"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You can have non-unique keys in a <literal>MEMORY</literal>
+ table. (This is an uncommon feature for implementations of
+ hash indexes.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 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.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Columns that are indexed can contain <literal>NULL</literal>
+ values.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MEMORY</literal> tables use a fixed-length row
+ storage format.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MEMORY</literal> tables cannot contain
+ <literal>BLOB</literal> or <literal>TEXT</literal> columns.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MEMORY</literal> includes support for
+ <literal>AUTO_INCREMENT</literal> columns.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You can use <literal>INSERT DELAYED</literal> with
+ <literal>MEMORY</literal> tables. See
+ <xref linkend="insert-delayed"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MEMORY</literal> tables are shared among all clients
+ (just like any other non-<literal>TEMPORARY</literal> table).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MEMORY</literal> table contents are stored in memory,
+ which is a property that <literal>MEMORY</literal> tables
+ share with internal tables that the server creates on the fly
+ while processing queries. However, the two types of tables
+ differ in that <literal>MEMORY</literal> tables are not
+ subject to storage conversion, whereas internal tables are:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If an internal table becomes too large, the server
+ automatically converts it to an on-disk table. The size
+ limit is determined by the value of the
+ <literal>tmp_table_size</literal> system variable.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MEMORY</literal> tables are never converted to
+ disk tables. To ensure that you don't accidentally do
+ anything foolish, you can set the
+ <literal>max_heap_table_size</literal> system variable to
+ impose a maximum size on <literal>MEMORY</literal> tables.
+ For individual tables, you can also specify a
+ <literal>MAX_ROWS</literal> table option in the
+ <literal>CREATE TABLE</literal> statement.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 when
+ you no longer require its contents, you should execute
+ <literal>DELETE</literal> or <literal>TRUNCATE
+ TABLE</literal>, or remove the table altogether using
+ <literal>DROP TABLE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you want to populate a <literal>MEMORY</literal> table when
+ the MySQL server starts, you can use the
+ <option>--init-file</option> option. For example, you can put
+ statements such as <literal>INSERT INTO ... SELECT</literal>
+ or <literal>LOAD DATA INFILE</literal> into this file to load
+ the table from a persistent data source. See
+ <xref linkend="server-options"/>, and
+ <xref linkend="load-data"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you are using replication, the master server's
+ <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. When a <literal>MEMORY</literal>
+ table is used on the master for the first time since the
+ master was started, a <literal>DELETE</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
+ <option>--init-file</option> 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>
+ The memory needed for one row in a <literal>MEMORY</literal>
+ table is calculated using the following expression:
+ </para>
+
+<programlisting>
+SUM_OVER_ALL_BTREE_KEYS(<replaceable>max_length_of_key</replaceable> + sizeof(char*) × 4)
++ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
++ ALIGN(<replaceable>length_of_row</replaceable>+1, sizeof(char*))
+</programlisting>
+
+ <para>
+ <literal>ALIGN()</literal> represents a round-up factor to
+ cause the row length to be an exact multiple of the
+ <literal>char</literal> pointer size.
+ <literal>sizeof(char*)</literal> is 4 on 32-bit machines and 8
+ on 64-bit machines.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">Additional resources</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ A forum dedicated to the <literal>MEMORY</literal> storage
+ engine is available at
+ <ulink url="&base-url-forum-list;?92"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
Added: trunk/refman-5.0/se-merge.xml
===================================================================
--- trunk/refman-5.0/se-merge.xml (rev 0)
+++ trunk/refman-5.0/se-merge.xml 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 516, Lines Deleted: 0; 20130 bytes
@@ -0,0 +1,516 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent">
+ %fixedchars.entities;
+ <!ENTITY % urls.entities SYSTEM "../refman-common/urls.ent">
+ %urls.entities;
+ <!ENTITY % versions.entities SYSTEM "versions.ent">
+ %versions.entities;
+]>
+ <section id="merge-storage-engine">
+
+ <title>The <literal>MERGE</literal> Storage Engine</title>
+
+ <indexterm>
+ <primary><literal>MERGE</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary><literal>MERGE</literal></secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary>merging</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MERGE tables</primary>
+ <secondary>defined</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary>partitioning</secondary>
+ </indexterm>
+
+ <remark role="help-topic" condition="MERGE"/>
+
+ <remark role="help-description-begin"/>
+
+ <para>
+ 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.
+ <quote>Identical</quote> means that all tables have identical
+ column and index information. You cannot merge
+ <literal>MyISAM</literal> tables in which the columns are listed
+ in a different order, do not have exactly the same columns, or
+ have the indexes in different order. However, any or all of the
+ <literal>MyISAM</literal> 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.
+ </para>
+
+ <remark role="help-description-end"/>
+
+ <para>
+ When you create a <literal>MERGE</literal> table, MySQL creates
+ two files on disk. 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 format, and an
+ <filename>.MRG</filename> file contains the names of the tables
+ that 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 <literal>INSERT</literal> on
+ <literal>MERGE</literal> tables. You must have
+ <literal>SELECT</literal>, <literal>UPDATE</literal>, and
+ <literal>DELETE</literal> privileges on the
+ <literal>MyISAM</literal> tables that you map to a
+ <literal>MERGE</literal> table.
+ </para>
+
+ <para>
+ <emphasis role="bold">Note</emphasis>: The use of
+ <literal>MERGE</literal> tables entails the following security
+ issue: If a user has access to <literal>MyISAM</literal> table
+ <replaceable>t</replaceable>, that user can create a
+ <literal>MERGE</literal> table <replaceable>m</replaceable> that
+ accesses <replaceable>t</replaceable>. However, if the user's
+ privileges on <replaceable>t</replaceable> are subsequently
+ revoked, the user can continue to access
+ <replaceable>t</replaceable> by doing so through
+ <replaceable>m</replaceable>. If this behavior is undesirable, you
+ can start the server with the new <option>--skip-merge</option>
+ option to disable the <literal>MERGE</literal> storage engine.
+ This option is available as of MySQL 5.0.24.
+ </para>
+
+ <para>
+ If you <literal>DROP</literal> the <literal>MERGE</literal> table,
+ you are dropping only the <literal>MERGE</literal> specification.
+ The underlying tables are not affected.
+ </para>
+
+ <para>
+ To create a <literal>MERGE</literal> table, you must specify a
+ <literal>UNION=(<replaceable>list-of-tables</replaceable>)</literal>
+ clause that indicates which <literal>MyISAM</literal> 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 rows into the <literal>MERGE</literal> table result in
+ an error.
+ </para>
+
+ <para>
+ The following example shows how to create a
+ <literal>MERGE</literal> table:
+ </para>
+
+ <remark role="help-example"/>
+
+<programlisting>
+mysql> <userinput>CREATE TABLE t1 (</userinput>
+ -> <userinput>a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,</userinput>
+ -> <userinput>message CHAR(20)) ENGINE=MyISAM;</userinput>
+mysql> <userinput>CREATE TABLE t2 (</userinput>
+ -> <userinput>a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,</userinput>
+ -> <userinput>message CHAR(20)) ENGINE=MyISAM;</userinput>
+mysql> <userinput>INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');</userinput>
+mysql> <userinput>INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');</userinput>
+mysql> <userinput>CREATE TABLE total (</userinput>
+ -> <userinput>a INT NOT NULL AUTO_INCREMENT,</userinput>
+ -> <userinput>message CHAR(20), INDEX(a))</userinput>
+ -> <userinput>ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;</userinput>
+</programlisting>
+
+ <para>
+ The older term <literal>TYPE</literal> is supported as a synonym
+ for <literal>ENGINE</literal> for backward compatibility, but
+ <literal>ENGINE</literal> is the preferred term and
+ <literal>TYPE</literal> is deprecated.
+ </para>
+
+ <para>
+ Note that the <literal>a</literal> column is indexed as a
+ <literal>PRIMARY KEY</literal> in the underlying
+ <literal>MyISAM</literal> tables, but not in the
+ <literal>MERGE</literal> table. There it is indexed but not as a
+ <literal>PRIMARY KEY</literal> because a <literal>MERGE</literal>
+ table cannot enforce uniqueness over the set of underlying tables.
+ </para>
+
+ <para>
+ After creating the <literal>MERGE</literal> table, you can issue
+ queries that operate on the group of tables as a whole:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT * FROM total;</userinput>
++---+---------+
+| a | message |
++---+---------+
+| 1 | Testing |
+| 2 | table |
+| 3 | t1 |
+| 1 | Testing |
+| 2 | table |
+| 3 | t2 |
++---+---------+
+</programlisting>
+
+ <para>
+ To remap a <literal>MERGE</literal> table to a different
+ collection of <literal>MyISAM</literal> tables, you can use one of
+ the following methods:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>DROP</literal> the <literal>MERGE</literal> table and
+ re-create it.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Use <literal>ALTER TABLE <replaceable>tbl_name</replaceable>
+ UNION=(...)</literal> to change the list of underlying tables.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <literal>MERGE</literal> tables can help you solve the following
+ problems:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Easily manage a set of log tables. For example, you can put
+ data from different months into separate tables, compress some
+ of them with <command>myisampack</command>, and then create a
+ <literal>MERGE</literal> table to use them as one.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Obtain more speed. You can split a big read-only table based
+ on some 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.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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>
+ 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 large
+ table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Instantly map many tables as one. A <literal>MERGE</literal>
+ table need not maintain an index of its own because it uses
+ the indexes of the individual tables. As a result,
+ <literal>MERGE</literal> table collections are
+ <emphasis>very</emphasis> fast to create or remap. (Note that
+ you must still specify the index definitions when you create a
+ <literal>MERGE</literal> table, even though no indexes are
+ created.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you have a set of tables from which you create a large
+ table on demand, you should instead create a
+ <literal>MERGE</literal> table on them on demand. This is much
+ faster and saves a lot of disk space.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Exceed the file size limit for the operating system. Each
+ <literal>MyISAM</literal> table is bound by this limit, but a
+ collection of <literal>MyISAM</literal> tables is not.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 from
+ doing this (only a couple of indirect calls and
+ <literal>memcpy()</literal> calls for each read).
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The disadvantages of <literal>MERGE</literal> tables are:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ You can use only identical <literal>MyISAM</literal> tables
+ for a <literal>MERGE</literal> table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You cannot use a number of <literal>MyISAM</literal> features
+ in <literal>MERGE</literal> tables. For example, you cannot
+ create <literal>FULLTEXT</literal> indexes on
+ <literal>MERGE</literal> tables. (You can, of course, create
+ <literal>FULLTEXT</literal> indexes on the underlying
+ <literal>MyISAM</literal> tables, but you cannot search the
+ <literal>MERGE</literal> table with a full-text search.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If the <literal>MERGE</literal> table is non-temporary, all
+ underlying <literal>MyISAM</literal> tables must be
+ non-temporary, too. If the <literal>MERGE</literal> table is
+ temporary, the <literal>MyISAM</literal> tables can be any mix
+ of temporary and non-temporary.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>MERGE</literal> tables use more file descriptors. If
+ 10 clients are using a <literal>MERGE</literal> table that
+ maps to 10 tables, the server uses (10 × 10) + 10 file
+ descriptors. (10 data file descriptors for each of the 10
+ clients, and 10 index file descriptors shared among the
+ clients.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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. To read the next key, 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 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>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">Additional resources</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ A forum dedicated to the <literal>MERGE</literal> storage
+ engine is available at
+ <ulink url="&base-url-forum-list;?93"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <section id="merge-table-problems">
+
+ <title><literal>MERGE</literal> Table Problems</title>
+
+ <para>
+ The following are known problems with <literal>MERGE</literal>
+ tables:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If you use <literal>ALTER TABLE</literal> to change a
+ <literal>MERGE</literal> table to another storage engine,
+ 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 uses the
+ specified storage engine.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>REPLACE</literal> does not work.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You cannot use <literal>DROP TABLE</literal>, <literal>ALTER
+ TABLE</literal>, <literal>DELETE</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 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 ensure that no
+ <literal>MERGE</literal> tables remain open by issuing a
+ <literal>FLUSH TABLES</literal> statement prior to
+ performing any of those operations.
+ </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's table
+ mapping is hidden from the upper layer of MySQL. Windows
+ does not allow open files to be deleted, so 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.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A <literal>MERGE</literal> table cannot maintain uniqueness
+ constraints over the entire table. When you perform an
+ <literal>INSERT</literal>, the data goes into the first or
+ last <literal>MyISAM</literal> table (depending on the value
+ of the <literal>INSERT_METHOD</literal> option). MySQL
+ ensures that unique key values remain unique within that
+ <literal>MyISAM</literal> table, but not across all the
+ tables in the collection.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When you create or alter <literal>MERGE</literal> table,
+ there is no check to ensure that the underlying tables are
+ existing <literal>MyISAM</literal> tables and have identical
+ structures. When the <literal>MERGE</literal> table is used,
+ MySQL checks that the row 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>
+
+ <para>
+ Similarly, if you create a <literal>MERGE</literal> table
+ from non-<literal>MyISAM</literal> tables, or if you drop an
+ underlying table or alter it to be a
+ non-<literal>MyISAM</literal> table, no error for the
+ <literal>MERGE</literal> table occurs until later when you
+ attempt to use it.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 ordering is
+ different for the tables if there was already a non-unique
+ index in the underlying table. (This happens 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>
+ If you encounter an error message similar to <literal>ERROR
+ 1017 (HY000): Can't find file:
+ '<replaceable>mm</replaceable>.MRG' (errno: 2)</literal> it
+ generally indicates that some of the base tables are not
+ using the MyISAM storage engine. Confirm that all tables are
+ MyISAM.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ There is a limit of 2<superscript>32</superscript>
+ (~4.295E+09)) rows to a <literal>MERGE</literal> table, just
+ as there is with a <literal>MyISAM</literal>, it is
+ therefore not possible to merge multiple
+ <literal>MyISAM</literal> tables that exceed this
+ limitation. However, you build MySQL with the
+ <literal>--with-big-tables</literal> option then the row
+ limitation is increased to
+ (2<superscript>32</superscript>)<superscript>2</superscript>
+ (1.844E+19) rows. See <xref linkend="configure-options"/>.
+ Beginning with MySQL 5.0.4 all standard binaries are built
+ with this option.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ </section>
Added: trunk/refman-5.0/se-myisam.xml
===================================================================
--- trunk/refman-5.0/se-myisam.xml (rev 0)
+++ trunk/refman-5.0/se-myisam.xml 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 1208, Lines Deleted: 0; 41140 bytes
@@ -0,0 +1,1208 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
+"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd"
+[
+ <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent">
+ %fixedchars.entities;
+ <!ENTITY % urls.entities SYSTEM "../refman-common/urls.ent">
+ %urls.entities;
+ <!ENTITY % versions.entities SYSTEM "versions.ent">
+ %versions.entities;
+]>
+
+ <section id="myisam-storage-engine">
+
+ <title>The <literal>MyISAM</literal> Storage Engine</title>
+
+ <indexterm>
+ <primary><literal>MyISAM</literal> storage engine</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary><literal>MyISAM</literal></secondary>
+ </indexterm>
+
+ <para>
+ <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 ¤t-series; does
+ <emphasis>not</emphasis> support <literal>ISAM</literal>.)
+ </para>
+
+ <para>
+ Each <literal>MyISAM</literal> table is stored on disk in three
+ 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 format. The data
+ file has an <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> table
+ option:
+ </para>
+
+<programlisting>
+CREATE TABLE t (i INT) ENGINE = MYISAM;
+</programlisting>
+
+ <para>
+ The older term <literal>TYPE</literal> is supported as a synonym
+ for <literal>ENGINE</literal> for backward compatibility, but
+ <literal>ENGINE</literal> is the preferred term and
+ <literal>TYPE</literal> is deprecated.
+ </para>
+
+ <para>
+ Normally, it is unnecesary to use <literal>ENGINE</literal> to
+ specify the <literal>MyISAM</literal> storage engine.
+ <literal>MyISAM</literal> is the default engine unless the default
+ has been changed. To ensure that <literal>MyISAM</literal> is used
+ in situations where the default might have been changed, include
+ the <literal>ENGINE</literal> option explicitly.
+ </para>
+
+ <para>
+ You can check or repair <literal>MyISAM</literal> tables with the
+ <command>mysqlcheck</command> client or
+ <command>myisamchk</command> utility. You can also compress
+ <literal>MyISAM</literal> tables with
+ <command>myisampack</command> to take up much less space. See
+ <xref linkend="mysqlcheck"/>, <xref linkend="crash-recovery"/>,
+ and <xref linkend="myisampack"/>.
+ </para>
+
+ <para>
+ <literal>MyISAM</literal> tables have the following
+ characteristics:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ All data values are stored with the low byte first. This makes
+ the data machine and operating system independent. The only
+ requirements for binary portability are that the machine uses
+ two's-complement signed integers and IEEE floating-point
+ format. These requirements are widely used among mainstream
+ machines. Binary compatibility might not be applicable to
+ embedded systems, which sometimes have peculiar processors.
+ </para>
+
+ <para>
+ There is no significant speed penalty for storing data low
+ byte first; the bytes in a table row normally are unaligned
+ and it takes little more processing to read an unaligned byte
+ in order than in reverse order. Also, the code in the server
+ that fetches column values is not time critical compared to
+ other code.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ All numeric key values are stored with the high byte first to
+ allow better index compression.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Large files (up to 63-bit file length) are supported on
+ filesystems and operating systems that support large files.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ There is a limit of 2<superscript>32</superscript>
+ (~4.295E+09) rows in a <literal>MyISAM</literal> table. You
+ can increase this limitation if you build MySQL with the
+ <literal>--with-big-tables</literal> option then the row
+ limitation is increased to
+ (2<superscript>32</superscript>)<superscript>2</superscript>
+ (1.844E+19) rows. See <xref linkend="configure-options"/>.
+ Beginning with MySQL 5.0.4 all standard binaries are built
+ with this option.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The maximum number of indexes per <literal>MyISAM</literal>
+ table is 64. This can be changed by recompiling. Beginning
+ with MySQL 5.0.18, you can configure the build by invoking
+ <command>configure</command> with the
+ <option>--with-max-indexes=<replaceable>N</replaceable></option>
+ option, where <replaceable>N</replaceable> is the maximum
+ number of indexes to permit per <literal>MyISAM</literal>
+ table. <replaceable>N</replaceable> must be less thann or
+ equal to 128. Before MySQL 5.0.18, you must change the source.
+ </para>
+
+ <para>
+ The maximum number of columns per index is 16.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The maximum key length is 1000 bytes. This can also be changed
+ by changing the source and 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>
+ When rows 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 utilization in the index tree.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Internal handling of one <literal>AUTO_INCREMENT</literal>
+ column per table is supported. <literal>MyISAM</literal>
+ automatically updates this 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> value can be reset with
+ <literal>ALTER TABLE</literal> or
+ <command>myisamchk</command>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Dynamic-sized rows are much less fragmented when mixing
+ deletes with updates and inserts. This is done by
+ automatically combining adjacent deleted blocks and by
+ extending blocks if the next block is deleted.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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), future inserts become concurrent
+ again. See <xref linkend="concurrent-inserts"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You can put the data file and index file on different
+ directories to get more speed with the <literal>DATA
+ DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal>
+ table options to <literal>CREATE TABLE</literal>. See
+ <xref linkend="create-table"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>BLOB</literal> and <literal>TEXT</literal> columns
+ can be indexed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>NULL</literal> values are allowed in indexed columns.
+ This takes 0−1 bytes per key.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Each character column can have a different character set. See
+ <xref linkend="charset"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ There is a flag in the <literal>MyISAM</literal> index file
+ that indicates whether the table was closed correctly. If
+ <command>mysqld</command> is started with the
+ <option>--myisam-recover</option> option,
+ <literal>MyISAM</literal> tables are automatically checked
+ when 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 <option>--update-state</option> 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
+ portions of keys, as well as for entire keys.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <command>myisampack</command> can pack <literal>BLOB</literal>
+ and <literal>VARCHAR</literal> columns.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <literal>MyISAM</literal> also supports the following features:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Support for a true <literal>VARCHAR</literal> type; a
+ <literal>VARCHAR</literal> column starts with a length stored
+ in one or two bytes.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Tables with <literal>VARCHAR</literal> columns may have fixed
+ or dynamic row length.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The sum of the lengths of the <literal>VARCHAR</literal> and
+ <literal>CHAR</literal> columns in a table may be up to 64KB.
+ </para>
+ </listitem>
+
+ <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. (However, you cannot search on a
+ <literal>UNIQUE</literal> computed index.)
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">Additional resources</emphasis>
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ A forum dedicated to the <literal>MyISAM</literal> storage
+ engine is available at
+ <ulink url="&base-url-forum-list;?21"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <section id="myisam-start">
+
+ <title><literal>MyISAM</literal> Startup Options</title>
+
+ <para>
+ The following options to <command>mysqld</command> can be used
+ to change the behavior of <literal>MyISAM</literal> tables. For
+ additional information, see <xref linkend="server-options"/>.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>myisam-recover option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>myisam-recover option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--myisam-recover=<replaceable>mode</replaceable></option>
+ </para>
+
+ <para>
+ Set the mode for automatic recovery of crashed
+ <literal>MyISAM</literal> tables.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>delay-key-write option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>delay-key-write option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--delay-key-write=ALL</option>
+ </para>
+
+ <para>
+ Don't flush key buffers between writes for any
+ <literal>MyISAM</literal> table.
+ </para>
+
+ <para>
+ <emphasis role="bold">Note</emphasis>: If you do this, you
+ should not access <literal>MyISAM</literal> tables from
+ another program (such as from another MySQL server or with
+ <command>myisamchk</command>) when the tables are in use.
+ Doing so risks index corruption. Using
+ <option>--external-locking</option> does not eliminate this
+ risk.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The following system variables affect the behavior of
+ <literal>MyISAM</literal> tables. For additional information,
+ see <xref linkend="server-system-variables"/>.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>bulk_insert_buffer_size</literal>
+ </para>
+
+ <para>
+ The size of the tree cache used in bulk insert optimization.
+ <emphasis role="bold">Note</emphasis>: This is a limit
+ <emphasis>per thread</emphasis>!
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>myisam_max_extra_sort_file_size</literal>
+ </para>
+
+ <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 was
+ given in bytes before MySQL 5.0.6, when it was removed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>myisam_max_sort_file_size</literal>
+ </para>
+
+ <para>
+ The maximum size of the temporary file that MySQL is allowed
+ to use while re-creating a <literal>MyISAM</literal> index
+ (during <literal>REPAIR TABLE</literal>, <literal>ALTER
+ TABLE</literal>, or <literal>LOAD DATA INFILE</literal>). If
+ the file size would be larger than this value, the index is
+ created using the key cache instead, which is slower. The
+ value is given in bytes.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>myisam_sort_buffer_size</literal>
+ </para>
+
+ <para>
+ Set the size of the buffer used when recovering tables.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Automatic recovery is activated if you start
+ <command>mysqld</command> with the
+ <option>--myisam-recover</option> option. In this case, when the
+ server opens a <literal>MyISAM</literal> table, it checks
+ whether the table is marked as crashed or whether the open count
+ variable for the table is not 0 and you are running the server
+ with external locking disabled. If either of these conditions is
+ true, the following happens:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The server checks the table for errors.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If the server finds an error, it tries to do a fast table
+ repair (with sorting and without re-creating the data file).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If the repair fails because of an error in the data file
+ (for example, a duplicate-key error), the server tries
+ again, this time re-creating the data file.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If the repair still fails, the server tries once more with
+ the old repair option method (write row by row without
+ sorting). This method should be able to repair any type of
+ error and has low disk space requirements.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ If the recovery wouldn't be able to recover all rows from
+ previously completed statementas and you didn't specify
+ <literal>FORCE</literal> in the value of the
+ <option>--myisam-recover</option> option, automatic repair
+ aborts with an error message in the error log:
+ </para>
+
+<programlisting>
+Error: Couldn't repair table: test.g00pages
+</programlisting>
+
+ <para>
+ If you specify <literal>FORCE</literal>, a warning like this is
+ written instead:
+ </para>
+
+<programlisting>
+Warning: Found 344 of 354 rows when repairing ./test/g00pages
+</programlisting>
+
+ <para>
+ Note that if the automatic recovery value includes
+ <literal>BACKUP</literal>, the recovery process creates files
+ with names of the form
+ <filename><replaceable>tbl_name-datetime</replaceable>.BAK</filename>.
+ You should have a <command>cron</command> script that
+ automatically moves these files from the database directories to
+ backup media.
+ </para>
+
+ </section>
+
+ <section id="key-space">
+
+ <title>Space Needed for Keys</title>
+
+ <indexterm>
+ <primary>key space</primary>
+ <secondary>MyISAM</secondary>
+ </indexterm>
+
+ <para>
+ <literal>MyISAM</literal> tables use B-tree indexes. You can
+ roughly calculate the size for the index file as
+ <literal>(key_length+4)/0.67</literal>, summed over all keys.
+ This is for the worst case when all keys are inserted in sorted
+ order and the table doesn't have any compressed keys.
+ </para>
+
+ <para>
+ String indexes are space compressed. If the first index part is
+ a string, it is also prefix compressed. Space compression makes
+ the index file smaller than the worst-case figure if a string
+ column has a lot of trailing space or is a
+ <literal>VARCHAR</literal> column that is not always used to the
+ full length. Prefix compression is used on keys that start with
+ a string. Prefix compression helps if there are many strings
+ with an identical prefix.
+ </para>
+
+ <para>
+ In <literal>MyISAM</literal> tables, you can also prefix
+ compress numbers by specifying the
+ <literal>PACK_KEYS=1</literal> table option when you create the
+ table. Numbers are stored with the high byte first, so this
+ helps when you have many integer keys that have an identical
+ prefix.
+ </para>
+
+ </section>
+
+ <section id="myisam-table-formats">
+
+ <title><literal>MyISAM</literal> Table Storage Formats</title>
+
+ <para>
+ <literal>MyISAM</literal> supports three different storage
+ formats. Two of them, fixed and dynamic format, are chosen
+ automatically depending on the type of columns you are using.
+ The third, compressed format, can be created only with the
+ <command>myisampack</command> utility.
+ </para>
+
+ <para>
+ When you use <literal>CREATE TABLE</literal> or <literal>ALTER
+ TABLE</literal> for a table that has no <literal>BLOB</literal>
+ or <literal>TEXT</literal> columns, you can force the table
+ format to <literal>FIXED</literal> 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>DYNAMIC</literal> format.
+ </para>
+
+ <para>
+ You can decompress tables by specifying
+ <literal>ROW_FORMAT=DEFAULT</literal> with <literal>ALTER
+ TABLE</literal>.
+ </para>
+
+ <para>
+ See <xref linkend="create-table"/>, for information about
+ <literal>ROW_FORMAT</literal>.
+ </para>
+
+ <section id="static-format">
+
+ <title>Static (Fixed-Length) Table Characteristics</title>
+
+ <para>
+ Static format is the default for <literal>MyISAM</literal>
+ tables. It is used when the table contains no variable-length
+ columns (<literal>VARCHAR</literal>,
+ <literal>VARBINARY</literal>, <literal>BLOB</literal>, or
+ <literal>TEXT</literal>). Each row is stored using a fixed
+ number of bytes.
+ </para>
+
+ <para>
+ Of the three <literal>MyISAM</literal> storage formats, static
+ format is the simplest and most secure (least subject to
+ corruption). It is also the fastest of the on-disk formats due
+ to the ease with which rows in the data file can be found on
+ disk: To look up a row based on a row number in the index,
+ multiply the row number by the row length to calculate the row
+ position. Also, when scanning a table, it is very easy to read
+ a constant number of rows with each disk read operation.
+ </para>
+
+ <para>
+ The security is evidenced if your computer crashes while the
+ MySQL server is writing to a fixed-format
+ <literal>MyISAM</literal> file. In this case,
+ <command>myisamchk</command> can easily determine where each
+ row starts and ends, so it can usually reclaim all rows except
+ the partially written one. Note that <literal>MyISAM</literal>
+ table indexes can always be reconstructed based on the data
+ rows.
+ </para>
+
+ <para>
+ Static-format tables have these characteristics:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>CHAR</literal> columns are space-padded to the
+ column width. This is also true for
+ <literal>NUMERIC</literal> and <literal>DECIMAL</literal>
+ columns created before MySQL 5.0.3.
+ <literal>BINARY</literal> columns are space-padded to the
+ column width before MySQL 5.0.15. As of 5.0.15,
+ <literal>BINARY</literal> columns are padded with
+ <literal>0x00</literal> bytes.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Very quick.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Easy to cache.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Easy to reconstruct after a crash, because rows are
+ located in fixed positions.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Reorganization is unnecessary unless you delete a huge
+ number of rows and want to return free disk space to the
+ operating system. To do this, use <literal>OPTIMIZE
+ TABLE</literal> or <command>myisamchk -r</command>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Usually require more disk space than dynamic-format
+ tables.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="dynamic-format">
+
+ <title>Dynamic Table Characteristics</title>
+
+ <indexterm>
+ <primary>dynamic table characteristics</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary>dynamic</secondary>
+ </indexterm>
+
+ <para>
+ Dynamic storage format is used if a <literal>MyISAM</literal>
+ table contains any variable-length columns
+ (<literal>VARCHAR</literal>, <literal>VARBINARY</literal>,
+ <literal>BLOB</literal>, or <literal>TEXT</literal>), or if
+ the table was created with the
+ <literal>ROW_FORMAT=DYNAMIC</literal> table option.
+ </para>
+
+ <para>
+ Dynamic format is a little more complex than static format
+ because each row has a header that indicates how long it is. A
+ row can become fragmented (stored in non-contiguous pieces)
+ when it is made longer as a result of an update.
+ </para>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary>defragment</secondary>
+ </indexterm>
+
+ <para>
+ You can use <literal>OPTIMIZE TABLE</literal> or
+ <command>myisamchk -r</command> to defragment a table. If you
+ have fixed-length columns that you access or change frequently
+ in a table that also contains some variable-length columns, it
+ might be a good idea to move the variable-length columns to
+ other tables just to avoid fragmentation.
+ </para>
+
+ <para>
+ Dynamic-format tables have these characteristics:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ All string columns are dynamic except those with a length
+ less than four.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Each row is preceded by a bitmap that indicates which
+ columns contain the empty string (for string columns) or
+ zero (for numeric columns). Note that this does not
+ include columns that contain <literal>NULL</literal>
+ values. If a string column has a length of zero after
+ trailing space removal, or a numeric column has a value of
+ zero, it is marked in the bitmap and not saved to disk.
+ Non-empty strings are saved as a length byte plus the
+ string contents.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Much less disk space usually is required than for
+ fixed-length tables.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Each row uses only as much space as is required. However,
+ if a row becomes larger, it is split into as many pieces
+ as are required, resulting in row fragmentation. For
+ example, if you 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
+ improve performance. Use <command>myisamchk -ei</command>
+ to obtain table statistics.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ More difficult than static-format tables to reconstruct
+ after a crash, because rows may be fragmented into many
+ pieces and links (fragments) may be missing.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The expected row length for dynamic-sized rows is
+ calculated using the following expression:
+ </para>
+
+<programlisting>
+3
++ (<replaceable>number of columns</replaceable> + 7) / 8
++ (<replaceable>number of char columns</replaceable>)
++ (<replaceable>packed size of numeric columns</replaceable>)
++ (<replaceable>length of strings</replaceable>)
++ (<replaceable>number of NULL columns</replaceable> + 7) / 8
+</programlisting>
+
+ <para>
+ There is a penalty of 6 bytes for each link. A dynamic row
+ is linked whenever an update causes an enlargement of the
+ row. Each new link is at least 20 bytes, so the next
+ enlargement 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 <literal>OPTIMIZE TABLE</literal> or
+ <command>myisamchk -r</command>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="compressed-format">
+
+ <title>Compressed Table Characteristics</title>
+
+ <indexterm>
+ <primary>tables</primary>
+ <secondary>compressed format</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>compressed tables</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>MyISAM</primary>
+ <secondary>compressed tables</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary><command>myisampack</command></primary>
+ </indexterm>
+
+ <para>
+ Compressed storage format is a read-only format that is
+ generated with the <command>myisampack</command> tool.
+ Compressed tables can be uncompressed with
+ <command>myisamchk</command>.
+ </para>
+
+ <para>
+ Compressed tables have the following characteristics:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Compressed tables take very little disk space. This
+ minimizes disk usage, which is helpful when using slow
+ disks (such as CD-ROMs).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Each row is compressed separately, so there is very little
+ access overhead. The header for a row takes up one to
+ three bytes depending on the biggest row in the table.
+ Each column is compressed differently. There is usually a
+ different Huffman tree for each column. Some of the
+ compression types are:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Suffix space compression.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Prefix space compression.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Numbers with a value of zero are stored using one bit.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If values in an integer column have a small range, the
+ column is stored using the smallest possible type. For
+ example, a <literal>BIGINT</literal> column (eight
+ bytes) can be stored as a <literal>TINYINT</literal>
+ column (one byte) if all its values are in the range
+ from <literal>-128</literal> to
+ <literal>127</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If a column has only a small set of possible values,
+ the data type is converted to <literal>ENUM</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A column may use any combination of the preceding
+ compression types.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Can be used for fixed-length or dynamic-length rows.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <formalpara>
+
+ <title>Note</title>
+
+ <para>
+ While a compressed table is read-only, and you cannot
+ therefore update or add rows in the table, DDL (Data
+ Definition Language) operations are still valid. For
+ example, you may still use <literal>DROP</literal> to drop
+ the table, and <literal>TRUNCATE</literal> to empty the
+ table.
+ </para>
+
+ </formalpara>
+
+ </section>
+
+ </section>
+
+ <section id="myisam-table-problems">
+
+ <title><literal>MyISAM</literal> Table Problems</title>
+
+ <para>
+ The file format that MySQL uses to store data has been
+ extensively tested, but there are always circumstances that may
+ cause database tables to become corrupted. The following
+ discussion describes how this can happen and how to handle it.
+ </para>
+
+ <section id="corrupted-myisam-tables">
+
+ <title>Corrupted <literal>MyISAM</literal> Tables</title>
+
+ <para>
+ 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 any of the following events occur:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The <command>mysqld</command> process is killed in the
+ middle of a write.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ An unexpected computer shutdown occurs (for example, the
+ computer is turned off).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Hardware failures.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ You are using an external program (such as
+ <command>myisamchk</command>) to modify a table that is
+ being modified by the server at the same time.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A software bug in the MySQL or <literal>MyISAM</literal>
+ code.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Typical symptoms of a corrupt table are:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ You get the following error while selecting data from the
+ table:
+ </para>
+
+<programlisting>
+Incorrect key file for table: '...'. Try to repair it
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Queries don't find rows in the table or return incomplete
+ results.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ 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 <command>myisamchk</command>
+ command. See <xref linkend="check-table"/>,
+ <xref linkend="repair-table"/>, and
+ <xref linkend="myisamchk"/>.
+ </para>
+
+ <para>
+ If your tables become corrupted frequently, you should try to
+ determine why this is happening. The most important thing to
+ know is whether the table became corrupted as a result of a
+ server crash. You can verify this easily by looking for a
+ recent <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. This is
+ a bug. You should try to create a reproducible test case that
+ demonstrates the problem. See <xref linkend="crashing"/>, and
+ <xref linkend="reproducible-test-case"/>.
+ </para>
+
+ </section>
+
+ <section id="myisam-table-close">
+
+ <title>Problems from Tables Not Being Closed Properly</title>
+
+ <para>
+ Each <literal>MyISAM</literal> index file
+ (<filename>.MYI</filename> file) has a counter in the header
+ that can be used to check whether a table has been closed
+ properly. If you get the following warning from <literal>CHECK
+ TABLE</literal> or <command>myisamchk</command>, it means that
+ this counter has gone out of sync:
+ </para>
+
+<programlisting>
+clients are using or haven't closed the table properly
+</programlisting>
+
+ <para>
+ This warning doesn't necessarily mean that the table is
+ corrupted, but you should at least check the table.
+ </para>
+
+ <para>
+ The counter works as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ The first time a table is updated in MySQL, a counter in
+ the header of the index files is incremented.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The counter is not changed during further updates.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When the last instance of a table is closed (because a
+ <literal>FLUSH TABLES</literal> operation was performed or
+ because there is no room in the table cache), the counter
+ is decremented if the table has been updated at any point.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When you repair the table or check the table and it is
+ found to be okay, the counter is reset to zero.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ To avoid problems with interaction with other processes
+ that might check the table, the counter is not decremented
+ on close if it was zero.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, the counter can become incorrect only under
+ these conditions:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ A <literal>MyISAM</literal> table is copied without first
+ issuing <literal>LOCK TABLES</literal> and <literal>FLUSH
+ TABLES</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL has crashed between an update and the final close.
+ (Note that the table may still be okay, because MySQL
+ always issues writes for everything between each
+ statement.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A table was modified by <command>myisamchk
+ --recover</command> or <command>myisamchk
+ --update-state</command> at the same time that it was in
+ use by <command>mysqld</command>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 servers.
+ However, <literal>REPAIR TABLE</literal> should be avoided
+ because when one server replaces the data file with a new
+ one, this is not known to the other servers.
+ </para>
+
+ <para>
+ In general, it is a bad idea to share a data directory
+ among multiple servers. See
+ <xref linkend="multiple-servers"/>, for additional
+ discussion.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ </section>
+
+ </section>
Modified: trunk/refman-5.0/storage-engines.xml
===================================================================
--- trunk/refman-5.0/storage-engines.xml 2006-08-15 04:18:48 UTC (rev 3017)
+++ trunk/refman-5.0/storage-engines.xml 2006-08-15 06:31:40 UTC (rev 3018)
Changed blocks: 1, Lines Added: 11, Lines Deleted: 3685; 127126 bytes
@@ -393,3700 +393,26 @@
</itemizedlist>
- <section id="myisam-storage-engine">
- <title>The <literal>MyISAM</literal> Storage Engine</title>
-
- <indexterm>
- <primary><literal>MyISAM</literal> storage engine</primary>
- </indexterm>
-
- <indexterm>
- <primary>tables</primary>
- <secondary><literal>MyISAM</literal></secondary>
- </indexterm>
-
- <para>
- <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 ¤t-series; does
- <emphasis>not</emphasis> support <literal>ISAM</literal>.)
- </para>
-
- <para>
- Each <literal>MyISAM</literal> table is stored on disk in three
- 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 format. The data
- file has an <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> table
- option:
- </para>
-
-<programlisting>
-CREATE TABLE t (i INT) ENGINE = MYISAM;
-</programlisting>
-
- <para>
- The older term <literal>TYPE</literal> is supported as a synonym
- for <literal>ENGINE</literal> for backward compatibility, but
- <literal>ENGINE</literal> is the preferred term and
- <literal>TYPE</literal> is deprecated.
- </para>
-
- <para>
- Normally, it is unnecesary to use <literal>ENGINE</literal> to
- specify the <literal>MyISAM</literal> storage engine.
- <literal>MyISAM</literal> is the default engine unless the default
- has been changed. To ensure that <literal>MyISAM</literal> is used
- in situations where the default might have been changed, include
- the <literal>ENGINE</literal> option explicitly.
- </para>
-
- <para>
- You can check or repair <literal>MyISAM</literal> tables with the
- <command>mysqlcheck</command> client or
- <command>myisamchk</command> utility. You can also compress
- <literal>MyISAM</literal> tables with
- <command>myisampack</command> to take up much less space. See
- <xref linkend="mysqlcheck"/>, <xref linkend="crash-recovery"/>,
- and <xref linkend="myisampack"/>.
- </para>
-
- <para>
- <literal>MyISAM</literal> tables have the following
- characteristics:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- All data values are stored with the low byte first. This makes
- the data machine and operating system independent. The only
- requirements for binary portability are that the machine uses
- two's-complement signed integers and IEEE floating-point
- format. These requirements are widely used among mainstream
- machines. Binary compatibility might not be applicable to
- embedded systems, which sometimes have peculiar processors.
- </para>
-
- <para>
- There is no significant speed penalty for storing data low
- byte first; the bytes in a table row normally are unaligned
- and it takes little more processing to read an unaligned byte
- in order than in reverse order. Also, the code in the server
- that fetches column values is not time critical compared to
- other code.
- </para>
- </listitem>
-
- <listitem>
- <para>
- All numeric key values are stored with the high byte first to
- allow better index compression.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Large files (up to 63-bit file length) are supported on
- filesystems and operating systems that support large files.
- </para>
- </listitem>
-
- <listitem>
- <para>
- There is a limit of 2<superscript>32</superscript>
- (~4.295E+09) rows in a <literal>MyISAM</literal> table. You
- can increase this limitation if you build MySQL with the
- <literal>--with-big-tables</literal> option then the row
- limitation is increased to
- (2<superscript>32</superscript>)<superscript>2</superscript>
- (1.844E+19) rows. See <xref linkend="configure-options"/>.
- Beginning with MySQL 5.0.4 all standard binaries are built
- with this option.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The maximum number of indexes per <literal>MyISAM</literal>
- table is 64. This can be changed by recompiling. Beginning
- with MySQL 5.0.18, you can configure the build by invoking
- <command>configure</command> with the
- <option>--with-max-indexes=<replaceable>N</replaceable></option>
- option, where <replaceable>N</replaceable> is the maximum
- number of indexes to permit per <literal>MyISAM</literal>
- table. <replaceable>N</replaceable> must be less thann or
- equal to 128. Before MySQL 5.0.18, you must change the source.
- </para>
-
- <para>
- The maximum number of columns per index is 16.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The maximum key length is 1000 bytes. This can also be changed
- by changing the source and 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>
- When rows 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 utilization in the index tree.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Internal handling of one <literal>AUTO_INCREMENT</literal>
- column per table is supported. <literal>MyISAM</literal>
- automatically updates this 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> value can be reset with
- <literal>ALTER TABLE</literal> or
- <command>myisamchk</command>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Dynamic-sized rows are much less fragmented when mixing
- deletes with updates and inserts. This is done by
- automatically combining adjacent deleted blocks and by
- extending blocks if the next block is deleted.
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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), future inserts become concurrent
- again. See <xref linkend="concurrent-inserts"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You can put the data file and index file on different
- directories to get more speed with the <literal>DATA
- DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal>
- table options to <literal>CREATE TABLE</literal>. See
- <xref linkend="create-table"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>BLOB</literal> and <literal>TEXT</literal> columns
- can be indexed.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>NULL</literal> values are allowed in indexed columns.
- This takes 0−1 bytes per key.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Each character column can have a different character set. See
- <xref linkend="charset"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- There is a flag in the <literal>MyISAM</literal> index file
- that indicates whether the table was closed correctly. If
- <command>mysqld</command> is started with the
- <option>--myisam-recover</option> option,
- <literal>MyISAM</literal> tables are automatically checked
- when 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 <option>--update-state</option> 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
- portions of keys, as well as for entire keys.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <command>myisampack</command> can pack <literal>BLOB</literal>
- and <literal>VARCHAR</literal> columns.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <literal>MyISAM</literal> also supports the following features:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Support for a true <literal>VARCHAR</literal> type; a
- <literal>VARCHAR</literal> column starts with a length stored
- in one or two bytes.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Tables with <literal>VARCHAR</literal> columns may have fixed
- or dynamic row length.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The sum of the lengths of the <literal>VARCHAR</literal> and
- <literal>CHAR</literal> columns in a table may be up to 64KB.
- </para>
- </listitem>
-
- <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. (However, you cannot search on a
- <literal>UNIQUE</literal> computed index.)
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">Additional resources</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- A forum dedicated to the <literal>MyISAM</literal> storage
- engine is available at
- <ulink url="&base-url-forum-list;?21"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <section id="myisam-start">
-
- <title><literal>MyISAM</literal> Startup Options</title>
-
- <para>
- The following options to <command>mysqld</command> can be used
- to change the behavior of <literal>MyISAM</literal> tables. For
- additional information, see <xref linkend="server-options"/>.
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>myisam-recover option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>myisam-recover option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--myisam-recover=<replaceable>mode</replaceable></option>
- </para>
-
- <para>
- Set the mode for automatic recovery of crashed
- <literal>MyISAM</literal> tables.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>delay-key-write option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>delay-key-write option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--delay-key-write=ALL</option>
- </para>
-
- <para>
- Don't flush key buffers between writes for any
- <literal>MyISAM</literal> table.
- </para>
-
- <para>
- <emphasis role="bold">Note</emphasis>: If you do this, you
- should not access <literal>MyISAM</literal> tables from
- another program (such as from another MySQL server or with
- <command>myisamchk</command>) when the tables are in use.
- Doing so risks index corruption. Using
- <option>--external-locking</option> does not eliminate this
- risk.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The following system variables affect the behavior of
- <literal>MyISAM</literal> tables. For additional information,
- see <xref linkend="server-system-variables"/>.
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>bulk_insert_buffer_size</literal>
- </para>
-
- <para>
- The size of the tree cache used in bulk insert optimization.
- <emphasis role="bold">Note</emphasis>: This is a limit
- <emphasis>per thread</emphasis>!
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>myisam_max_extra_sort_file_size</literal>
- </para>
-
- <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 was
- given in bytes before MySQL 5.0.6, when it was removed.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>myisam_max_sort_file_size</literal>
- </para>
-
- <para>
- The maximum size of the temporary file that MySQL is allowed
- to use while re-creating a <literal>MyISAM</literal> index
- (during <literal>REPAIR TABLE</literal>, <literal>ALTER
- TABLE</literal>, or <literal>LOAD DATA INFILE</literal>). If
- the file size would be larger than this value, the index is
- created using the key cache instead, which is slower. The
- value is given in bytes.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>myisam_sort_buffer_size</literal>
- </para>
-
- <para>
- Set the size of the buffer used when recovering tables.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Automatic recovery is activated if you start
- <command>mysqld</command> with the
- <option>--myisam-recover</option> option. In this case, when the
- server opens a <literal>MyISAM</literal> table, it checks
- whether the table is marked as crashed or whether the open count
- variable for the table is not 0 and you are running the server
- with external locking disabled. If either of these conditions is
- true, the following happens:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- The server checks the table for errors.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If the server finds an error, it tries to do a fast table
- repair (with sorting and without re-creating the data file).
- </para>
- </listitem>
-
- <listitem>
- <para>
- If the repair fails because of an error in the data file
- (for example, a duplicate-key error), the server tries
- again, this time re-creating the data file.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If the repair still fails, the server tries once more with
- the old repair option method (write row by row without
- sorting). This method should be able to repair any type of
- error and has low disk space requirements.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- If the recovery wouldn't be able to recover all rows from
- previously completed statementas and you didn't specify
- <literal>FORCE</literal> in the value of the
- <option>--myisam-recover</option> option, automatic repair
- aborts with an error message in the error log:
- </para>
-
-<programlisting>
-Error: Couldn't repair table: test.g00pages
-</programlisting>
-
- <para>
- If you specify <literal>FORCE</literal>, a warning like this is
- written instead:
- </para>
-
-<programlisting>
-Warning: Found 344 of 354 rows when repairing ./test/g00pages
-</programlisting>
-
- <para>
- Note that if the automatic recovery value includes
- <literal>BACKUP</literal>, the recovery process creates files
- with names of the form
- <filename><replaceable>tbl_name-datetime</replaceable>.BAK</filename>.
- You should have a <command>cron</command> script that
- automatically moves these files from the database directories to
- backup media.
- </para>
-
- </section>
-
- <section id="key-space">
-
- <title>Space Needed for Keys</title>
-
- <indexterm>
- <primary>key space</primary>
- <secondary>MyISAM</secondary>
- </indexterm>
-
- <para>
- <literal>MyISAM</literal> tables use B-tree indexes. You can
- roughly calculate the size for the index file as
- <literal>(key_length+4)/0.67</literal>, summed over all keys.
- This is for the worst case when all keys are inserted in sorted
- order and the table doesn't have any compressed keys.
- </para>
-
- <para>
- String indexes are space compressed. If the first index part is
- a string, it is also prefix compressed. Space compression makes
- the index file smaller than the worst-case figure if a string
- column has a lot of trailing space or is a
- <literal>VARCHAR</literal> column that is not always used to the
- full length. Prefix compression is used on keys that start with
- a string. Prefix compression helps if there are many strings
- with an identical prefix.
- </para>
-
- <para>
- In <literal>MyISAM</literal> tables, you can also prefix
- compress numbers by specifying the
- <literal>PACK_KEYS=1</literal> table option when you create the
- table. Numbers are stored with the high byte first, so this
- helps when you have many integer keys that have an identical
- prefix.
- </para>
-
- </section>
-
- <section id="myisam-table-formats">
-
- <title><literal>MyISAM</literal> Table Storage Formats</title>
-
- <para>
- <literal>MyISAM</literal> supports three different storage
- formats. Two of them, fixed and dynamic format, are chosen
- automatically depending on the type of columns you are using.
- The third, compressed format, can be created only with the
- <command>myisampack</command> utility.
- </para>
-
- <para>
- When you use <literal>CREATE TABLE</literal> or <literal>ALTER
- TABLE</literal> for a table that has no <literal>BLOB</literal>
- or <literal>TEXT</literal> columns, you can force the table
- format to <literal>FIXED</literal> 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>DYNAMIC</literal> format.
- </para>
-
- <para>
- You can decompress tables by specifying
- <literal>ROW_FORMAT=DEFAULT</literal> with <literal>ALTER
- TABLE</literal>.
- </para>
-
- <para>
- See <xref linkend="create-table"/>, for information about
- <literal>ROW_FORMAT</literal>.
- </para>
-
- <section id="static-format">
-
- <title>Static (Fixed-Length) Table Characteristics</title>
-
- <para>
- Static format is the default for <literal>MyISAM</literal>
- tables. It is used when the table contains no variable-length
- columns (<literal>VARCHAR</literal>,
- <literal>VARBINARY</literal>, <literal>BLOB</literal>, or
- <literal>TEXT</literal>). Each row is stored using a fixed
- number of bytes.
- </para>
-
- <para>
- Of the three <literal>MyISAM</literal> storage formats, static
- format is the simplest and most secure (least subject to
- corruption). It is also the fastest of the on-disk formats due
- to the ease with which rows in the data file can be found on
- disk: To look up a row based on a row number in the index,
- multiply the row number by the row length to calculate the row
- position. Also, when scanning a table, it is very easy to read
- a constant number of rows with each disk read operation.
- </para>
-
- <para>
- The security is evidenced if your computer crashes while the
- MySQL server is writing to a fixed-format
- <literal>MyISAM</literal> file. In this case,
- <command>myisamchk</command> can easily determine where each
- row starts and ends, so it can usually reclaim all rows except
- the partially written one. Note that <literal>MyISAM</literal>
- table indexes can always be reconstructed based on the data
- rows.
- </para>
-
- <para>
- Static-format tables have these characteristics:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>CHAR</literal> columns are space-padded to the
- column width. This is also true for
- <literal>NUMERIC</literal> and <literal>DECIMAL</literal>
- columns created before MySQL 5.0.3.
- <literal>BINARY</literal> columns are space-padded to the
- column width before MySQL 5.0.15. As of 5.0.15,
- <literal>BINARY</literal> columns are padded with
- <literal>0x00</literal> bytes.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Very quick.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Easy to cache.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Easy to reconstruct after a crash, because rows are
- located in fixed positions.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Reorganization is unnecessary unless you delete a huge
- number of rows and want to return free disk space to the
- operating system. To do this, use <literal>OPTIMIZE
- TABLE</literal> or <command>myisamchk -r</command>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Usually require more disk space than dynamic-format
- tables.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="dynamic-format">
-
- <title>Dynamic Table Characteristics</title>
-
- <indexterm>
- <primary>dynamic table characteristics</primary>
- </indexterm>
-
- <indexterm>
- <primary>tables</primary>
- <secondary>dynamic</secondary>
- </indexterm>
-
- <para>
- Dynamic storage format is used if a <literal>MyISAM</literal>
- table contains any variable-length columns
- (<literal>VARCHAR</literal>, <literal>VARBINARY</literal>,
- <literal>BLOB</literal>, or <literal>TEXT</literal>), or if
- the table was created with the
- <literal>ROW_FORMAT=DYNAMIC</literal> table option.
- </para>
-
- <para>
- Dynamic format is a little more complex than static format
- because each row has a header that indicates how long it is. A
- row can become fragmented (stored in non-contiguous pieces)
- when it is made longer as a result of an update.
- </para>
-
- <indexterm>
- <primary>tables</primary>
- <secondary>defragment</secondary>
- </indexterm>
-
- <para>
- You can use <literal>OPTIMIZE TABLE</literal> or
- <command>myisamchk -r</command> to defragment a table. If you
- have fixed-length columns that you access or change frequently
- in a table that also contains some variable-length columns, it
- might be a good idea to move the variable-length columns to
- other tables just to avoid fragmentation.
- </para>
-
- <para>
- Dynamic-format tables have these characteristics:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- All string columns are dynamic except those with a length
- less than four.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Each row is preceded by a bitmap that indicates which
- columns contain the empty string (for string columns) or
- zero (for numeric columns). Note that this does not
- include columns that contain <literal>NULL</literal>
- values. If a string column has a length of zero after
- trailing space removal, or a numeric column has a value of
- zero, it is marked in the bitmap and not saved to disk.
- Non-empty strings are saved as a length byte plus the
- string contents.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Much less disk space usually is required than for
- fixed-length tables.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Each row uses only as much space as is required. However,
- if a row becomes larger, it is split into as many pieces
- as are required, resulting in row fragmentation. For
- example, if you 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
- improve performance. Use <command>myisamchk -ei</command>
- to obtain table statistics.
- </para>
- </listitem>
-
- <listitem>
- <para>
- More difficult than static-format tables to reconstruct
- after a crash, because rows may be fragmented into many
- pieces and links (fragments) may be missing.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The expected row length for dynamic-sized rows is
- calculated using the following expression:
- </para>
-
-<programlisting>
-3
-+ (<replaceable>number of columns</replaceable> + 7) / 8
-+ (<replaceable>number of char columns</replaceable>)
-+ (<replaceable>packed size of numeric columns</replaceable>)
-+ (<replaceable>length of strings</replaceable>)
-+ (<replaceable>number of NULL columns</replaceable> + 7) / 8
-</programlisting>
-
- <para>
- There is a penalty of 6 bytes for each link. A dynamic row
- is linked whenever an update causes an enlargement of the
- row. Each new link is at least 20 bytes, so the next
- enlargement 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 <literal>OPTIMIZE TABLE</literal> or
- <command>myisamchk -r</command>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="compressed-format">
-
- <title>Compressed Table Characteristics</title>
-
- <indexterm>
- <primary>tables</primary>
- <secondary>compressed format</secondary>
- </indexterm>
-
- <indexterm>
- <primary>compressed tables</primary>
- </indexterm>
-
- <indexterm>
- <primary>MyISAM</primary>
- <secondary>compressed tables</secondary>
- </indexterm>
-
- <indexterm>
- <primary><command>myisampack</command></primary>
- </indexterm>
-
- <para>
- Compressed storage format is a read-only format that is
- generated with the <command>myisampack</command> tool.
- Compressed tables can be uncompressed with
- <command>myisamchk</command>.
- </para>
-
- <para>
- Compressed tables have the following characteristics:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Compressed tables take very little disk space. This
- minimizes disk usage, which is helpful when using slow
- disks (such as CD-ROMs).
- </para>
- </listitem>
-
- <listitem>
- <para>
- Each row is compressed separately, so there is very little
- access overhead. The header for a row takes up one to
- three bytes depending on the biggest row in the table.
- Each column is compressed differently. There is usually a
- different Huffman tree for each column. Some of the
- compression types are:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Suffix space compression.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Prefix space compression.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Numbers with a value of zero are stored using one bit.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If values in an integer column have a small range, the
- column is stored using the smallest possible type. For
- example, a <literal>BIGINT</literal> column (eight
- bytes) can be stored as a <literal>TINYINT</literal>
- column (one byte) if all its values are in the range
- from <literal>-128</literal> to
- <literal>127</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If a column has only a small set of possible values,
- the data type is converted to <literal>ENUM</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A column may use any combination of the preceding
- compression types.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <para>
- Can be used for fixed-length or dynamic-length rows.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <formalpara>
-
- <title>Note</title>
-
- <para>
- While a compressed table is read-only, and you cannot
- therefore update or add rows in the table, DDL (Data
- Definition Language) operations are still valid. For
- example, you may still use <literal>DROP</literal> to drop
- the table, and <literal>TRUNCATE</literal> to empty the
- table.
- </para>
-
- </formalpara>
-
- </section>
-
- </section>
-
- <section id="myisam-table-problems">
-
- <title><literal>MyISAM</literal> Table Problems</title>
-
- <para>
- The file format that MySQL uses to store data has been
- extensively tested, but there are always circumstances that may
- cause database tables to become corrupted. The following
- discussion describes how this can happen and how to handle it.
- </para>
-
- <section id="corrupted-myisam-tables">
-
- <title>Corrupted <literal>MyISAM</literal> Tables</title>
-
- <para>
- 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 any of the following events occur:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- The <command>mysqld</command> process is killed in the
- middle of a write.
- </para>
- </listitem>
-
- <listitem>
- <para>
- An unexpected computer shutdown occurs (for example, the
- computer is turned off).
- </para>
- </listitem>
-
- <listitem>
- <para>
- Hardware failures.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You are using an external program (such as
- <command>myisamchk</command>) to modify a table that is
- being modified by the server at the same time.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A software bug in the MySQL or <literal>MyISAM</literal>
- code.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Typical symptoms of a corrupt table are:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- You get the following error while selecting data from the
- table:
- </para>
-
-<programlisting>
-Incorrect key file for table: '...'. Try to repair it
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- Queries don't find rows in the table or return incomplete
- results.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- 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 <command>myisamchk</command>
- command. See <xref linkend="check-table"/>,
- <xref linkend="repair-table"/>, and
- <xref linkend="myisamchk"/>.
- </para>
-
- <para>
- If your tables become corrupted frequently, you should try to
- determine why this is happening. The most important thing to
- know is whether the table became corrupted as a result of a
- server crash. You can verify this easily by looking for a
- recent <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. This is
- a bug. You should try to create a reproducible test case that
- demonstrates the problem. See <xref linkend="crashing"/>, and
- <xref linkend="reproducible-test-case"/>.
- </para>
-
- </section>
-
- <section id="myisam-table-close">
-
- <title>Problems from Tables Not Being Closed Properly</title>
-
- <para>
- Each <literal>MyISAM</literal> index file
- (<filename>.MYI</filename> file) has a counter in the header
- that can be used to check whether a table has been closed
- properly. If you get the following warning from <literal>CHECK
- TABLE</literal> or <command>myisamchk</command>, it means that
- this counter has gone out of sync:
- </para>
-
-<programlisting>
-clients are using or haven't closed the table properly
-</programlisting>
-
- <para>
- This warning doesn't necessarily mean that the table is
- corrupted, but you should at least check the table.
- </para>
-
- <para>
- The counter works as follows:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- The first time a table is updated in MySQL, a counter in
- the header of the index files is incremented.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The counter is not changed during further updates.
- </para>
- </listitem>
-
- <listitem>
- <para>
- When the last instance of a table is closed (because a
- <literal>FLUSH TABLES</literal> operation was performed or
- because there is no room in the table cache), the counter
- is decremented if the table has been updated at any point.
- </para>
- </listitem>
-
- <listitem>
- <para>
- When you repair the table or check the table and it is
- found to be okay, the counter is reset to zero.
- </para>
- </listitem>
-
- <listitem>
- <para>
- To avoid problems with interaction with other processes
- that might check the table, the counter is not decremented
- on close if it was zero.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- In other words, the counter can become incorrect only under
- these conditions:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- A <literal>MyISAM</literal> table is copied without first
- issuing <literal>LOCK TABLES</literal> and <literal>FLUSH
- TABLES</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL has crashed between an update and the final close.
- (Note that the table may still be okay, because MySQL
- always issues writes for everything between each
- statement.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- A table was modified by <command>myisamchk
- --recover</command> or <command>myisamchk
- --update-state</command> at the same time that it was in
- use by <command>mysqld</command>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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 servers.
- However, <literal>REPAIR TABLE</literal> should be avoided
- because when one server replaces the data file with a new
- one, this is not known to the other servers.
- </para>
-
- <para>
- In general, it is a bad idea to share a data directory
- among multiple servers. See
- <xref linkend="multiple-servers"/>, for additional
- discussion.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- </section>
-
- </section>
-
+ <xi:include href="se-myisam.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+
<xi:include href="innodb.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
- <section id="merge-storage-engine">
+ <xi:include href="se-merge.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+ <xi:include href="se-memory.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+ <xi:include href="se-bdb.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+ <xi:include href="se-example.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+ <xi:include href="se-federated.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+ <xi:include href="se-archive.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+ <xi:include href="se-csv.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+ <xi:include href="se-blackhole.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+
- <title>The <literal>MERGE</literal> Storage Engine</title>
- <indexterm>
- <primary><literal>MERGE</literal> storage engine</primary>
- </indexterm>
- <indexterm>
- <primary>tables</primary>
- <secondary><literal>MERGE</literal></secondary>
- </indexterm>
- <indexterm>
- <primary>tables</primary>
- <secondary>merging</secondary>
- </indexterm>
- <indexterm>
- <primary>MERGE tables</primary>
- <secondary>defined</secondary>
- </indexterm>
- <indexterm>
- <primary>tables</primary>
- <secondary>partitioning</secondary>
- </indexterm>
- <remark role="help-topic" condition="MERGE"/>
- <remark role="help-description-begin"/>
-
- <para>
- 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.
- <quote>Identical</quote> means that all tables have identical
- column and index information. You cannot merge
- <literal>MyISAM</literal> tables in which the columns are listed
- in a different order, do not have exactly the same columns, or
- have the indexes in different order. However, any or all of the
- <literal>MyISAM</literal> 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.
- </para>
-
- <remark role="help-description-end"/>
-
- <para>
- When you create a <literal>MERGE</literal> table, MySQL creates
- two files on disk. 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 format, and an
- <filename>.MRG</filename> file contains the names of the tables
- that 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 <literal>INSERT</literal> on
- <literal>MERGE</literal> tables. You must have
- <literal>SELECT</literal>, <literal>UPDATE</literal>, and
- <literal>DELETE</literal> privileges on the
- <literal>MyISAM</literal> tables that you map to a
- <literal>MERGE</literal> table.
- </para>
-
- <para>
- <emphasis role="bold">Note</emphasis>: The use of
- <literal>MERGE</literal> tables entails the following security
- issue: If a user has access to <literal>MyISAM</literal> table
- <replaceable>t</replaceable>, that user can create a
- <literal>MERGE</literal> table <replaceable>m</replaceable> that
- accesses <replaceable>t</replaceable>. However, if the user's
- privileges on <replaceable>t</replaceable> are subsequently
- revoked, the user can continue to access
- <replaceable>t</replaceable> by doing so through
- <replaceable>m</replaceable>. If this behavior is undesirable, you
- can start the server with the new <option>--skip-merge</option>
- option to disable the <literal>MERGE</literal> storage engine.
- This option is available as of MySQL 5.0.24.
- </para>
-
- <para>
- If you <literal>DROP</literal> the <literal>MERGE</literal> table,
- you are dropping only the <literal>MERGE</literal> specification.
- The underlying tables are not affected.
- </para>
-
- <para>
- To create a <literal>MERGE</literal> table, you must specify a
- <literal>UNION=(<replaceable>list-of-tables</replaceable>)</literal>
- clause that indicates which <literal>MyISAM</literal> 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 rows into the <literal>MERGE</literal> table result in
- an error.
- </para>
-
- <para>
- The following example shows how to create a
- <literal>MERGE</literal> table:
- </para>
-
- <remark role="help-example"/>
-
-<programlisting>
-mysql> <userinput>CREATE TABLE t1 (</userinput>
- -> <userinput>a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,</userinput>
- -> <userinput>message CHAR(20)) ENGINE=MyISAM;</userinput>
-mysql> <userinput>CREATE TABLE t2 (</userinput>
- -> <userinput>a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,</userinput>
- -> <userinput>message CHAR(20)) ENGINE=MyISAM;</userinput>
-mysql> <userinput>INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');</userinput>
-mysql> <userinput>INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');</userinput>
-mysql> <userinput>CREATE TABLE total (</userinput>
- -> <userinput>a INT NOT NULL AUTO_INCREMENT,</userinput>
- -> <userinput>message CHAR(20), INDEX(a))</userinput>
- -> <userinput>ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;</userinput>
-</programlisting>
-
- <para>
- The older term <literal>TYPE</literal> is supported as a synonym
- for <literal>ENGINE</literal> for backward compatibility, but
- <literal>ENGINE</literal> is the preferred term and
- <literal>TYPE</literal> is deprecated.
- </para>
-
- <para>
- Note that the <literal>a</literal> column is indexed as a
- <literal>PRIMARY KEY</literal> in the underlying
- <literal>MyISAM</literal> tables, but not in the
- <literal>MERGE</literal> table. There it is indexed but not as a
- <literal>PRIMARY KEY</literal> because a <literal>MERGE</literal>
- table cannot enforce uniqueness over the set of underlying tables.
- </para>
-
- <para>
- After creating the <literal>MERGE</literal> table, you can issue
- queries that operate on the group of tables as a whole:
- </para>
-
-<programlisting>
-mysql> <userinput>SELECT * FROM total;</userinput>
-+---+---------+
-| a | message |
-+---+---------+
-| 1 | Testing |
-| 2 | table |
-| 3 | t1 |
-| 1 | Testing |
-| 2 | table |
-| 3 | t2 |
-+---+---------+
-</programlisting>
-
- <para>
- To remap a <literal>MERGE</literal> table to a different
- collection of <literal>MyISAM</literal> tables, you can use one of
- the following methods:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>DROP</literal> the <literal>MERGE</literal> table and
- re-create it.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Use <literal>ALTER TABLE <replaceable>tbl_name</replaceable>
- UNION=(...)</literal> to change the list of underlying tables.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <literal>MERGE</literal> tables can help you solve the following
- problems:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Easily manage a set of log tables. For example, you can put
- data from different months into separate tables, compress some
- of them with <command>myisampack</command>, and then create a
- <literal>MERGE</literal> table to use them as one.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Obtain more speed. You can split a big read-only table based
- on some 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.
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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>
- 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 large
- table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Instantly map many tables as one. A <literal>MERGE</literal>
- table need not maintain an index of its own because it uses
- the indexes of the individual tables. As a result,
- <literal>MERGE</literal> table collections are
- <emphasis>very</emphasis> fast to create or remap. (Note that
- you must still specify the index definitions when you create a
- <literal>MERGE</literal> table, even though no indexes are
- created.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you have a set of tables from which you create a large
- table on demand, you should instead create a
- <literal>MERGE</literal> table on them on demand. This is much
- faster and saves a lot of disk space.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Exceed the file size limit for the operating system. Each
- <literal>MyISAM</literal> table is bound by this limit, but a
- collection of <literal>MyISAM</literal> tables is not.
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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 from
- doing this (only a couple of indirect calls and
- <literal>memcpy()</literal> calls for each read).
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The disadvantages of <literal>MERGE</literal> tables are:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- You can use only identical <literal>MyISAM</literal> tables
- for a <literal>MERGE</literal> table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You cannot use a number of <literal>MyISAM</literal> features
- in <literal>MERGE</literal> tables. For example, you cannot
- create <literal>FULLTEXT</literal> indexes on
- <literal>MERGE</literal> tables. (You can, of course, create
- <literal>FULLTEXT</literal> indexes on the underlying
- <literal>MyISAM</literal> tables, but you cannot search the
- <literal>MERGE</literal> table with a full-text search.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- If the <literal>MERGE</literal> table is non-temporary, all
- underlying <literal>MyISAM</literal> tables must be
- non-temporary, too. If the <literal>MERGE</literal> table is
- temporary, the <literal>MyISAM</literal> tables can be any mix
- of temporary and non-temporary.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>MERGE</literal> tables use more file descriptors. If
- 10 clients are using a <literal>MERGE</literal> table that
- maps to 10 tables, the server uses (10 × 10) + 10 file
- descriptors. (10 data file descriptors for each of the 10
- clients, and 10 index file descriptors shared among the
- clients.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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. To read the next key, 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 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>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">Additional resources</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- A forum dedicated to the <literal>MERGE</literal> storage
- engine is available at
- <ulink url="&base-url-forum-list;?93"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <section id="merge-table-problems">
-
- <title><literal>MERGE</literal> Table Problems</title>
-
- <para>
- The following are known problems with <literal>MERGE</literal>
- tables:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- If you use <literal>ALTER TABLE</literal> to change a
- <literal>MERGE</literal> table to another storage engine,
- 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 uses the
- specified storage engine.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>REPLACE</literal> does not work.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You cannot use <literal>DROP TABLE</literal>, <literal>ALTER
- TABLE</literal>, <literal>DELETE</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 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 ensure that no
- <literal>MERGE</literal> tables remain open by issuing a
- <literal>FLUSH TABLES</literal> statement prior to
- performing any of those operations.
- </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's table
- mapping is hidden from the upper layer of MySQL. Windows
- does not allow open files to be deleted, so 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.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A <literal>MERGE</literal> table cannot maintain uniqueness
- constraints over the entire table. When you perform an
- <literal>INSERT</literal>, the data goes into the first or
- last <literal>MyISAM</literal> table (depending on the value
- of the <literal>INSERT_METHOD</literal> option). MySQL
- ensures that unique key values remain unique within that
- <literal>MyISAM</literal> table, but not across all the
- tables in the collection.
- </para>
- </listitem>
-
- <listitem>
- <para>
- When you create or alter <literal>MERGE</literal> table,
- there is no check to ensure that the underlying tables are
- existing <literal>MyISAM</literal> tables and have identical
- structures. When the <literal>MERGE</literal> table is used,
- MySQL checks that the row 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>
-
- <para>
- Similarly, if you create a <literal>MERGE</literal> table
- from non-<literal>MyISAM</literal> tables, or if you drop an
- underlying table or alter it to be a
- non-<literal>MyISAM</literal> table, no error for the
- <literal>MERGE</literal> table occurs until later when you
- attempt to use it.
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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 ordering is
- different for the tables if there was already a non-unique
- index in the underlying table. (This happens 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>
- If you encounter an error message similar to <literal>ERROR
- 1017 (HY000): Can't find file:
- '<replaceable>mm</replaceable>.MRG' (errno: 2)</literal> it
- generally indicates that some of the base tables are not
- using the MyISAM storage engine. Confirm that all tables are
- MyISAM.
- </para>
- </listitem>
-
- <listitem>
- <para>
- There is a limit of 2<superscript>32</superscript>
- (~4.295E+09)) rows to a <literal>MERGE</literal> table, just
- as there is with a <literal>MyISAM</literal>, it is
- therefore not possible to merge multiple
- <literal>MyISAM</literal> tables that exceed this
- limitation. However, you build MySQL with the
- <literal>--with-big-tables</literal> option then the row
- limitation is increased to
- (2<superscript>32</superscript>)<superscript>2</superscript>
- (1.844E+19) rows. See <xref linkend="configure-options"/>.
- Beginning with MySQL 5.0.4 all standard binaries are built
- with this option.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- </section>
-
- <section id="memory-storage-engine">
-
- <title>The <literal>MEMORY</literal> (<literal>HEAP</literal>) Storage Engine</title>
-
- <remark role="todo">
- Parts of this may be true only for hash indexes. Now that MEMORY
- supports BTREE indexes, some statements may need to be qualified.
- </remark>
-
- <indexterm>
- <primary><literal>HEAP</literal> storage engine</primary>
- </indexterm>
-
- <indexterm>
- <primary><literal>MEMORY</literal> storage engine</primary>
- </indexterm>
-
- <indexterm>
- <primary>tables</primary>
- <secondary><literal>MEMORY</literal></secondary>
- </indexterm>
-
- <indexterm>
- <primary>tables</primary>
- <secondary><literal>HEAP</literal></secondary>
- </indexterm>
-
- <para>
- The <literal>MEMORY</literal> storage engine creates tables with
- contents that are stored in memory. Formerly, these were known as
- <literal>HEAP</literal> tables. <literal>MEMORY</literal> is the
- preferred term, although <literal>HEAP</literal> remains supported
- for backward compatibility.
- </para>
-
- <para>
- Each <literal>MEMORY</literal> table is associated with one disk
- file. The filename begins with the table name and has an extension
- of <filename>.frm</filename> to indicate that it stores the table
- definition.
- </para>
-
- <para>
- To specify explicitly that you want to create a
- <literal>MEMORY</literal> table, indicate that with an
- <literal>ENGINE</literal> table option:
- </para>
-
-<programlisting>
-CREATE TABLE t (i INT) ENGINE = MEMORY;
-</programlisting>
-
- <para>
- The older term <literal>TYPE</literal> is supported as a synonym
- for <literal>ENGINE</literal> for backward compatibility, but
- <literal>ENGINE</literal> is the preferred term and
- <literal>TYPE</literal> is deprecated.
- </para>
-
- <para>
- As indicated by the name, <literal>MEMORY</literal> tables are
- stored in memory. They use hash indexes by default, which makes
- them very fast, and very useful for creating temporary tables.
- However, when the server shuts down, all rows stored in
- <literal>MEMORY</literal> tables are 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>
- This example shows how you might create, use, and remove a
- <literal>MEMORY</literal> table:
- </para>
-
-<programlisting>
-mysql> <userinput>CREATE TABLE test ENGINE=MEMORY</userinput>
- -> <userinput>SELECT ip,SUM(downloads) AS down</userinput>
- -> <userinput>FROM log_table GROUP BY ip;</userinput>
-mysql> <userinput>SELECT COUNT(ip),AVG(down) FROM test;</userinput>
-mysql> <userinput>DROP TABLE test;</userinput>
-</programlisting>
-
- <para>
- <literal>MEMORY</literal> tables have the following
- characteristics:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Space for <literal>MEMORY</literal> tables is allocated in
- small 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 can have up to 32 indexes per
- table, 16 columns per index and a maximum key length of 500
- bytes.
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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>
-CREATE TABLE lookup
- (id INT, INDEX USING HASH (id))
- ENGINE = MEMORY;
-CREATE TABLE lookup
- (id INT, INDEX USING BTREE (id))
- ENGINE = MEMORY;
-</programlisting>
-
- <para>
- General characteristics of B-tree and hash indexes are
- described in <xref linkend="mysql-indexes"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You can have non-unique keys in a <literal>MEMORY</literal>
- table. (This is an uncommon feature for implementations of
- hash indexes.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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 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.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Columns that are indexed can contain <literal>NULL</literal>
- values.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>MEMORY</literal> tables use a fixed-length row
- storage format.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>MEMORY</literal> tables cannot contain
- <literal>BLOB</literal> or <literal>TEXT</literal> columns.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>MEMORY</literal> includes support for
- <literal>AUTO_INCREMENT</literal> columns.
- </para>
- </listitem>
-
- <listitem>
- <para>
- You can use <literal>INSERT DELAYED</literal> with
- <literal>MEMORY</literal> tables. See
- <xref linkend="insert-delayed"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>MEMORY</literal> tables are shared among all clients
- (just like any other non-<literal>TEMPORARY</literal> table).
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>MEMORY</literal> table contents are stored in memory,
- which is a property that <literal>MEMORY</literal> tables
- share with internal tables that the server creates on the fly
- while processing queries. However, the two types of tables
- differ in that <literal>MEMORY</literal> tables are not
- subject to storage conversion, whereas internal tables are:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- If an internal table becomes too large, the server
- automatically converts it to an on-disk table. The size
- limit is determined by the value of the
- <literal>tmp_table_size</literal> system variable.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>MEMORY</literal> tables are never converted to
- disk tables. To ensure that you don't accidentally do
- anything foolish, you can set the
- <literal>max_heap_table_size</literal> system variable to
- impose a maximum size on <literal>MEMORY</literal> tables.
- For individual tables, you can also specify a
- <literal>MAX_ROWS</literal> table option in the
- <literal>CREATE TABLE</literal> statement.
- </para>
- </listitem>
-
- </itemizedlist>
- </listitem>
-
- <listitem>
- <para>
- 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 when
- you no longer require its contents, you should execute
- <literal>DELETE</literal> or <literal>TRUNCATE
- TABLE</literal>, or remove the table altogether using
- <literal>DROP TABLE</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you want to populate a <literal>MEMORY</literal> table when
- the MySQL server starts, you can use the
- <option>--init-file</option> option. For example, you can put
- statements such as <literal>INSERT INTO ... SELECT</literal>
- or <literal>LOAD DATA INFILE</literal> into this file to load
- the table from a persistent data source. See
- <xref linkend="server-options"/>, and
- <xref linkend="load-data"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you are using replication, the master server's
- <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. When a <literal>MEMORY</literal>
- table is used on the master for the first time since the
- master was started, a <literal>DELETE</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
- <option>--init-file</option> 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>
- The memory needed for one row in a <literal>MEMORY</literal>
- table is calculated using the following expression:
- </para>
-
-<programlisting>
-SUM_OVER_ALL_BTREE_KEYS(<replaceable>max_length_of_key</replaceable> + sizeof(char*) × 4)
-+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
-+ ALIGN(<replaceable>length_of_row</replaceable>+1, sizeof(char*))
-</programlisting>
-
- <para>
- <literal>ALIGN()</literal> represents a round-up factor to
- cause the row length to be an exact multiple of the
- <literal>char</literal> pointer size.
- <literal>sizeof(char*)</literal> is 4 on 32-bit machines and 8
- on 64-bit machines.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">Additional resources</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- A forum dedicated to the <literal>MEMORY</literal> storage
- engine is available at
- <ulink url="&base-url-forum-list;?92"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="bdb-storage-engine">
-
- <title>The <literal>BDB</literal> (<literal>BerkeleyDB</literal>) Storage
- Engine</title>
-
- <indexterm>
- <primary><literal>BDB</literal> storage engine</primary>
- </indexterm>
-
- <indexterm>
- <primary><literal>BerkeleyDB</literal> storage engine</primary>
- </indexterm>
-
- <indexterm>
- <primary>tables</primary>
- <secondary><literal>BDB</literal></secondary>
- </indexterm>
-
- <indexterm>
- <primary>tables</primary>
- <secondary><literal>Berkeley DB</literal></secondary>
- </indexterm>
-
- <para>
- Sleepycat Software has provided MySQL with the Berkeley DB
- transactional storage engine. This storage engine typically is
- called <literal>BDB</literal> for short. <literal>BDB</literal>
- tables may have a greater chance of surviving crashes and are also
- capable of <literal>COMMIT</literal> and
- <literal>ROLLBACK</literal> operations on transactions.
- </para>
-
- <para>
- Support for the <literal>BDB</literal> storage engine is included
- in MySQL source distributions is activated in MySQL-Max binary
- distributions. The MySQL source distribution comes with a
- <literal>BDB</literal> distribution that is patched to make it
- work with MySQL. You cannot use a non-patched version of
- <literal>BDB</literal> with MySQL.
- </para>
-
- <remark>
- Is this still true in 5.0?
- </remark>
-
- <para>
- 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 reproducible test cases. 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 <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 is used in many applications other
- than MySQL.
- </para>
-
- <para>
- For general information about Berkeley DB, please visit the
- Sleepycat Web site, <ulink url="http://www.sleepycat.com/"/>.
- </para>
-
- <section id="bdb-portability">
-
- <title>Operating Systems Supported by <literal>BDB</literal></title>
-
- <para>
- Currently, we know that the <literal>BDB</literal> storage
- engine works with the following operating systems:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Linux 2.x Intel
- </para>
- </listitem>
-
- <listitem>
- <para>
- Sun Solaris (SPARC and x86)
- </para>
- </listitem>
-
- <listitem>
- <para>
- FreeBSD 4.x/5.x (x86, sparc64)
- </para>
- </listitem>
-
- <listitem>
- <para>
- IBM AIX 4.3.x
- </para>
- </listitem>
-
- <listitem>
- <para>
- SCO OpenServer
- </para>
- </listitem>
-
- <listitem>
- <para>
- SCO UnixWare 7.1.x
- </para>
- </listitem>
-
- <listitem>
- <para>
- Windows NT/2000/XP
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The <literal>BDB</literal> storage engine does
- <emphasis>not</emphasis> work with the following operating
- systems:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Linux 2.x Alpha
- </para>
- </listitem>
-
- <listitem>
- <para>
- Linux 2.x AMD64
- </para>
- </listitem>
-
- <listitem>
- <para>
- Linux 2.x IA-64
- </para>
- </listitem>
-
- <listitem>
- <para>
- Linux 2.x s390
- </para>
- </listitem>
-
- <listitem>
- <para>
- Mac OS X
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">Note</emphasis>: The preceding lists are
- not complete. We update them as we receive more information.
- </para>
-
- <para>
- If you build MySQL from source with support for
- <literal>BDB</literal> tables, but the following error occurs
- when you start <command>mysqld</command>, it means that the
- <literal>BDB</literal> storage engine is not supported for your
- architecture:
- </para>
-
-<programlisting>
-bdb: architecture lacks fast mutexes: applications cannot be threaded
-Can't init databases
-</programlisting>
-
- <para>
- In this case, you must rebuild MySQL without
- <literal>BDB</literal> support or start the server with the
- <option>--skip-bdb</option> option.
- </para>
-
- </section>
-
- <section id="bdb-install">
-
- <title>Installing <literal>BDB</literal></title>
-
- <para>
- If you have downloaded a binary version of MySQL that includes
- support for Berkeley DB, simply follow the usual binary
- distribution installation instructions. (MySQL-Max distributions
- include <literal>BDB</literal> support.)
- </para>
-
- <para>
- If you build MySQL from source, you can enable
- <literal>BDB</literal> support by invoking
- <command>configure</command> with the
- <option>--with-berkeley-db</option> option in addition to any
- other options that you normally use. Download a MySQL
- ¤t-series; distribution, change location into its
- top-level directory, and run this command:
- </para>
-
-<programlisting>
-shell> <userinput>./configure --with-berkeley-db [<replaceable>other-options</replaceable>]</userinput>
-</programlisting>
-
- <para>
- For more information, see <xref linkend="mysqld-max"/>,
- <xref linkend="installing-binary"/>, and
- <xref linkend="installing-source"/>.
- </para>
-
- </section>
-
- <section id="bdb-start">
-
- <title><literal>BDB</literal> Startup Options</title>
-
- <para>
- The following options to <command>mysqld</command> can be used
- to change the behavior of the <literal>BDB</literal> storage
- engine. For more information, see
- <xref linkend="server-options"/>.
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>bdb-home option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>bdb-home option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--bdb-home=<replaceable>path</replaceable></option>
- </para>
-
- <para>
- The base directory for <literal>BDB</literal> tables. This
- should be the same directory that you use for
- <option>--datadir</option>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>bdb-lock-detect option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>bdb-lock-detect option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--bdb-lock-detect=<replaceable>method</replaceable></option>
- </para>
-
- <para>
- The <literal>BDB</literal> lock detection method. The option
- value should be <literal>DEFAULT</literal>,
- <literal>OLDEST</literal>, <literal>RANDOM</literal>, or
- <literal>YOUNGEST</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>bdb-logdir option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>bdb-logdir option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--bdb-logdir=<replaceable>file_name</replaceable></option>
- </para>
-
- <para>
- The <literal>BDB</literal> log file directory.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>bdb-no-recover option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>bdb-no-recover option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--bdb-no-recover</option>
- </para>
-
- <para>
- Do not start Berkeley DB in recover mode.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>bdb-no-sync option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>bdb-no-sync option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--bdb-no-sync</option>
- </para>
-
- <para>
- Don't synchronously flush the <literal>BDB</literal> logs.
- This option is deprecated; use
- <option>--skip-sync-bdb-logs</option> instead (see the
- description for <option>--sync-bdb-logs</option>).
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>bdb-shared-data option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>bdb-shared-data option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--bdb-shared-data</option>
- </para>
-
- <para>
- Start Berkeley DB in multi-process mode. (Do not use
- <literal>DB_PRIVATE</literal> when initializing Berkeley
- DB.)
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>bdb-tmpdir option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>bdb-tmpdir option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--bdb-tmpdir=<replaceable>path</replaceable></option>
- </para>
-
- <para>
- The <literal>BDB</literal> temporary file directory.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>skip-bdb option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>skip-bdb option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--skip-bdb</option>
- </para>
-
- <para>
- Disable the <literal>BDB</literal> storage engine.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <indexterm>
- <primary>mysqld</primary>
- <secondary>sync-bdb-logs option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>sync-bdb-logs option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--sync-bdb-logs</option>
- </para>
-
- <para>
- Synchronously flush the <literal>BDB</literal> logs. This
- option is enabled by default. Use
- <option>--skip-sync-bdb-logs</option> to disable it.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- If you use the <option>--skip-bdb</option> option, MySQL does
- not initialize the Berkeley DB library and this saves a lot of
- memory. However, if you use this option, you cannot use
- <literal>BDB</literal> tables. If you try to create a
- <literal>BDB</literal> table, MySQL uses the default storage
- engine instead.
- </para>
-
- <para>
- Normally, you should start <command>mysqld</command> without the
- <option>--bdb-no-recover</option> option if you intend to use
- <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"/>.
- </para>
-
- <para>
- With the <literal>bdb_max_lock</literal> variable, you can
- specify the maximum number of locks that can be active on a
- <literal>BDB</literal> table. The default is 10,000. You should
- increase this if errors such as the following occur when you
- perform long transactions or when <command>mysqld</command> has
- to examine many rows to execute a query:
- </para>
-
-<programlisting>
-bdb: Lock table is out of available locks
-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>
- See also <xref linkend="server-system-variables"/>.
- </para>
-
- </section>
-
- <section id="bdb-characteristics">
-
- <title>Characteristics of <literal>BDB</literal> Tables</title>
-
- <para>
- Each <literal>BDB</literal> table is stored on disk in two
- 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 format, and a
- <filename>.db</filename> file contains the table data and
- indexes.
- </para>
-
- <para>
- To specify explicitly that you want a <literal>BDB</literal>
- table, indicate that with an <literal>ENGINE</literal> table
- option:
- </para>
-
-<programlisting>
-CREATE TABLE t (i INT) ENGINE = BDB;
-</programlisting>
-
- <para>
- The older term <literal>TYPE</literal> is supported as a synonym
- for <literal>ENGINE</literal> for backward compatibility, but
- <literal>ENGINE</literal> is the preferred term and
- <literal>TYPE</literal> is deprecated.
- </para>
-
- <para>
- <literal>BerkeleyDB</literal> is a synonym for
- <literal>BDB</literal> in the <literal>ENGINE</literal> table
- option.
- </para>
-
- <para>
- The <literal>BDB</literal> storage engine provides transactional
- tables. The way you use these tables depends on the autocommit
- mode:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- If you are running with autocommit enabled (which is the
- default), changes to <literal>BDB</literal> tables are
- committed immediately and cannot be rolled back.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If you are running with autocommit disabled, changes do not
- become permanent until you execute a
- <literal>COMMIT</literal> statement. Instead of committing,
- you can execute <literal>ROLLBACK</literal> to forget the
- changes.
- </para>
-
- <para>
- You can start a transaction with the <literal>START
- TRANSACTION</literal> or <literal>BEGIN</literal> statement
- to suspend autocommit, or with <literal>SET
- AUTOCOMMIT=0</literal> to disable autocommit explicitly.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- For more information about transactions, see
- <xref linkend="commit"/>.
- </para>
-
- <para>
- The <literal>BDB</literal> storage engine has the following
- characteristics:
- </para>
-
- <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.
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL requires a primary key in each <literal>BDB</literal>
- table so that each row can be uniquely identified. If you
- don't create one explicitly by declaring a <literal>PRIMARY
- KEY</literal>, MySQL creates and maintains a hidden primary
- key for you. The hidden key has a length of five bytes and
- is incremented for each insert attempt. This key does not
- appear in the output of <literal>SHOW CREATE TABLE</literal>
- or <literal>DESCRIBE</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The primary key is faster than any other index, because it
- is stored together with the row data. The other indexes are
- stored as the key data plus the primary key, so it's
- important to keep the primary key as short as possible to
- save disk space and get better speed.
- </para>
-
- <para>
- This behavior is similar to that of
- <literal>InnoDB</literal>, where shorter primary keys save
- space not only in the primary index but in secondary indexes
- as well.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If all columns that you access in a <literal>BDB</literal>
- table are part of the same index or part of the primary key,
- MySQL can execute the query without having to access the
- actual row. In a <literal>MyISAM</literal> table, this can
- be done only if the columns are part of the same index.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Sequential scanning is slower for <literal>BDB</literal>
- tables than for <literal>MyISAM</literal> tables because the
- data in <literal>BDB</literal> tables is stored in B-trees
- and not in a separate data file.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Key values are not prefix- or suffix-compressed like key
- values in <literal>MyISAM</literal> tables. In other words,
- key information takes a little more space in
- <literal>BDB</literal> tables compared to
- <literal>MyISAM</literal> tables.
- </para>
- </listitem>
-
- <listitem>
- <para>
- There are often holes in the <literal>BDB</literal> table to
- allow you to insert new rows in the middle of the index
- tree. This makes <literal>BDB</literal> tables somewhat
- larger than <literal>MyISAM</literal> tables.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>SELECT COUNT(*) FROM
- <replaceable>tbl_name</replaceable></literal> is slow for
- <literal>BDB</literal> tables, because no row count is
- maintained in the table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The optimizer needs to know the approximate number of rows
- in the table. MySQL solves this by counting inserts and
- maintaining this in a separate segment in each
- <literal>BDB</literal> table. If you don't issue a lot of
- <literal>DELETE</literal> or <literal>ROLLBACK</literal>
- statements, this number should be accurate enough for the
- MySQL optimizer. However, MySQL stores the number only on
- close, so it may be incorrect if the server terminates
- unexpectedly. It should not be fatal even if this number is
- not 100% correct. You can update the row count by using
- <literal>ANALYZE TABLE</literal> or <literal>OPTIMIZE
- TABLE</literal>. See <xref linkend="analyze-table"/>, and
- <xref linkend="optimize-table"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Internal locking in <literal>BDB</literal> tables is done at
- the page level.
- </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 support transaction rollback, the <literal>BDB</literal>
- storage engine maintains log files. For maximum performance,
- you can use the <option>--bdb-logdir</option> option to
- place the <literal>BDB</literal> logs on a different disk
- than the one where your databases are located.
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL performs a checkpoint each time a new
- <literal>BDB</literal> log file is started, and removes any
- <literal>BDB</literal> log files that are not needed for
- current transactions. You can also use <literal>FLUSH
- LOGS</literal> at any time to checkpoint the Berkeley DB
- tables.
- </para>
-
- <para>
- For disaster recovery, you should use table backups plus
- MySQL's binary log. See <xref linkend="backup"/>.
- </para>
-
- <para>
- <emphasis role="bold">Warning:</emphasis> If you delete old
- log files that are still in use, <literal>BDB</literal> is
- not able to do recovery at all and you may lose data if
- something goes wrong.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Applications must always be prepared to handle cases where
- any change of a <literal>BDB</literal> table may cause an
- automatic rollback and any read may fail with a deadlock
- error.
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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> tables, for which
- <command>mysqld</command> waits for sufficient free disk
- space before continuing.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="bdb-restrictions">
-
- <title>Restrictions on <literal>BDB</literal> Tables</title>
-
- <remark role="todo">
- [JS] Move this to Restrictions appendix?
- </remark>
-
- <para>
- The following list indicates restrictions that you must observe
- when using <literal>BDB</literal> tables:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Each <literal>BDB</literal> table stores in its
- <filename>.db</filename> file the path to the file as it was
- created. This is done to enable detection of locks in a
- multi-user 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>
-
- <listitem>
- <para>
- When making backups of <literal>BDB</literal> tables, you
- must either use <command>mysqldump</command> or else make a
- backup that includes the files for each
- <literal>BDB</literal> table (the <filename>.frm</filename>
- and <filename>.db</filename> files) as well as the
- <literal>BDB</literal> log files. The <literal>BDB</literal>
- storage engine stores unfinished transactions in its log
- files and requires them to be present when
- <command>mysqld</command> starts. The <literal>BDB</literal>
- logs are the files in the data directory with names of the
- form
- <filename>log.<replaceable>NNNNNNNNNN</replaceable></filename>
- (ten digits).
- </para>
- </listitem>
-
- <listitem>
- <para>
- If a column that allows <literal>NULL</literal> values has a
- unique index, only a single <literal>NULL</literal> value is
- allowed. This differs from other storage engines, which
- allow multiple <literal>NULL</literal> values in unique
- indexes.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="bdb-errors">
-
- <title>Errors That May Occur When Using <literal>BDB</literal> Tables</title>
-
- <itemizedlist>
-
- <listitem>
- <para>
- If the following error occurs when you start
- <command>mysqld</command> after upgrading, it means that the
- current version of <literal>BDB</literal> doesn't support
- the old log file format:
- </para>
-
-<programlisting>
-bdb: Ignoring log file: .../log.<replaceable>NNNNNNNNNN</replaceable>:
-unsupported log version #
-</programlisting>
-
- <para>
- In this case, you must delete all <literal>BDB</literal>
- logs from your data directory (the files that have names of
- the form
- <filename>log.<replaceable>NNNNNNNNNN</replaceable></filename>)
- and restart <command>mysqld</command>. We also recommend
- that you then use <command>mysqldump --opt</command> to dump
- your <literal>BDB</literal> tables, drop the tables, and
- restore them from the dump file.
- </para>
- </listitem>
-
- <listitem>
- <para>
- If autocommit mode is disabled and you drop a
- <literal>BDB</literal> table that is referenced in another
- transaction, you may get error messages of the following
- form in your MySQL error log:
- </para>
-
-<programlisting>
-001119 23:43:56 bdb: Missing log fileid entry
-001119 23:43:56 bdb: txn_abort: Log undo failed for LSN:
- 1 3644744: Invalid
-</programlisting>
-
- <para>
- This is not fatal, but the fix is not trivial. Until the
- problem is fixed, we recommend that you not drop
- <literal>BDB</literal> tables except while autocommit mode
- is enabled.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- </section>
-
- <section id="example-storage-engine">
-
- <title>The <literal>EXAMPLE</literal> Storage Engine</title>
-
- <indexterm>
- <primary><literal>EXAMPLE</literal> storage engine</primary>
- </indexterm>
-
- <indexterm>
- <primary>tables</primary>
- <secondary>EXAMPLE</secondary>
- </indexterm>
-
- <para>
- 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>
- The <literal>EXAMPLE</literal> storage engine is included in
- MySQL-Max binary distributions. To enable this storage engine if
- you build MySQL from source, invoke <command>configure</command>
- with the <option>--with-example-storage-engine</option> option.
- </para>
-
- <para>
- To examine the source for the <literal>EXAMPLE</literal> engine,
- look in the <filename>sql/examples</filename> directory of a MySQL
- source distribution.
- </para>
-
- <para>
- When you create an <literal>EXAMPLE</literal> table, the server
- creates a table format file in the database directory. The file
- begins with the table name and has an <filename>.frm</filename>
- extension. No other files are created. No data can be stored into
- the table. Retrievals return an empty result.
- </para>
-
-<programlisting>
-mysql> <userinput>CREATE TABLE test (i INT) ENGINE = EXAMPLE;</userinput>
-Query OK, 0 rows affected (0.78 sec)
-
-mysql> <userinput>INSERT INTO test VALUES(1),(2),(3);</userinput>
-ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option
-
-mysql> <userinput>SELECT * FROM test;</userinput>
-Empty set (0.31 sec)
-</programlisting>
-
- <para>
- The <literal>EXAMPLE</literal> storage engine does not support
- indexing.
- </para>
-
- </section>
-
- <section id="federated-storage-engine">
-
- <title>The <literal>FEDERATED</literal> Storage Engine</title>
-
- <indexterm>
- <primary><literal>FEDERATED</literal> storage engine</primary>
- </indexterm>
-
- <indexterm>
- <primary>tables</primary>
- <secondary>FEDERATED</secondary>
- </indexterm>
-
- <para>
- 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>
- The <literal>FEDERATED</literal> storage engine is included in
- MySQL-Max binary distributions. To enable this storage engine if
- you build MySQL from source, invoke <command>configure</command>
- with the <option>--with-federated-storage-engine</option> option.
- </para>
-
- <para>
- To examine the source for the <literal>FEDERATED</literal> engine,
- look in the <filename>sql</filename> directory of a source
- distribution for MySQL 5.0.3 or newer.
- </para>
-
- <para>
- <emphasis role="bold">Additional resources</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- A forum dedicated to the <literal>FEDERATED</literal> storage
- engine is available at
- <ulink url="&base-url-forum-list;?105"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <section id="federated-description">
-
- <title>Description of the <literal>FEDERATED</literal> Storage Engine</title>
-
- <para>
- When you create a <literal>FEDERATED</literal> table, the server
- creates a table format file in the database directory. The file
- begins with the table name and has an <filename>.frm</filename>
- extension. No other files are created, because the actual data
- is in a remote table. This differs from the way that storage
- engines for local tables work.
- </para>
-
- <para>
- For local database tables, data files are local. For example, if
- you create a <literal>MyISAM</literal> table named
- <literal>users</literal>, the <literal>MyISAM</literal> handler
- creates a data file named <literal>users.MYD</literal>. A
- handler for local tables reads, inserts, deletes, and updates
- data in local data files, and rows are stored in a format
- particular to the handler. To read rows, the handler must parse
- data into columns. To write rows, column values must be
- converted to the row format used by the handler and written to
- the local data file.
- </para>
-
- <para>
- With the MySQL <literal>FEDERATED</literal> storage engine,
- there are no local data files for a table (for example, there is
- no <filename>.MYD</filename> file). Instead, a remote database
- stores the data that normally would be in the table. The local
- server connects to a remote server, and uses the MySQL client
- API to read, delete, update, and insert data in the remote
- table. Data retrieval is initiated via a <literal>SELECT * FROM
- <replaceable>tbl_name</replaceable></literal> SQL statement. To
- read the result, rows are fetched one at a time by using the
- <literal>mysql_fetch_row()</literal> C API function, and then
- converting the columns in the <literal>SELECT</literal> result
- set to the format that the <literal>FEDERATED</literal> handler
- expects.
- </para>
-
- <para>
- The flow of information is as follows:
- </para>
-
- <orderedlist>
-
- <listitem>
- <para>
- SQL calls issued locally
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL handler API (data in handler format)
- </para>
- </listitem>
-
- <listitem>
- <para>
- MySQL client API (data converted to SQL calls)
- </para>
- </listitem>
-
- <listitem>
- <para>
- Remote database -> MySQL client API
- </para>
- </listitem>
-
- <listitem>
- <para>
- Convert result sets (if any) to handler format
- </para>
- </listitem>
-
- <listitem>
- <para>
- Handler API -> Result rows or rows-affected count to
- local
- </para>
- </listitem>
-
- </orderedlist>
-
- </section>
-
- <section id="federated-use">
-
- <title>How to use <literal>FEDERATED</literal> Tables</title>
-
- <remark role="todo">
- Stuff we could cover: - If federated table is replicated, the
- slave hosts must be able to use the account in the CONNECTION to
- connect to the remote server.
- </remark>
-
- <para>
- The procedure for using <literal>FEDERATED</literal> tables is
- very simple. Normally, you have two servers running, either both
- on the same host or on different hosts. (It is possible for a
- <literal>FEDERATED</literal> table to use another table that is
- managed by the same server, although there is little point in
- doing so.)
- </para>
-
- <para>
- First, you must have a table on the remote server that you want
- to access by using a <literal>FEDERATED</literal> table. Suppose
- that the remote table is in the <literal>federated</literal>
- database and is defined like this:
- </para>
-
-<programlisting>
-CREATE TABLE test_table (
- id INT(20) NOT NULL AUTO_INCREMENT,
- name VARCHAR(32) NOT NULL DEFAULT '',
- other INT(20) NOT NULL DEFAULT '0',
- PRIMARY KEY (id),
- INDEX name (name),
- INDEX other_key (other)
-)
-ENGINE=MyISAM
-DEFAULT CHARSET=latin1;
-</programlisting>
-
- <para>
- The example uses a <literal>MyISAM</literal> table, but the
- table could use any storage engine.
- </para>
-
- <para>
- Next, create a <literal>FEDERATED</literal> table on the local
- server for accessing the remote table:
- </para>
-
-<programlisting>
-CREATE TABLE federated_table (
- id INT(20) NOT NULL AUTO_INCREMENT,
- name VARCHAR(32) NOT NULL DEFAULT '',
- other INT(20) NOT NULL DEFAULT '0',
- PRIMARY KEY (id),
- INDEX name (name),
- INDEX other_key (other)
-)
-ENGINE=FEDERATED
-DEFAULT CHARSET=latin1
-CONNECTION='mysql://root@remote_host:9306/federated/test_table';
-</programlisting>
-
- <para>
- (Before MySQL 5.0.13, use <literal>COMMENT</literal> rather than
- <literal>CONNECTION</literal>.)
- </para>
-
- <para>
- 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>CONNECTION</literal> table option is a connection
- string that indicates to the <literal>FEDERATED</literal> engine
- how to connect to the remote server.
- </para>
-
- <para>
- The <literal>FEDERATED</literal> engine creates only the
- <filename>test_table.frm</filename> file in the
- <literal>federated</literal> database.
- </para>
-
- <para>
- The remote host information indicates the remote server to which
- your local server connects, and the database and table
- information indicates which remote table to use as the data
- source. In this example, the remote server is indicated to be
- running as <literal>remote_host</literal> on port 9306, so there
- must be a MySQL server running on the remote host and listening
- to port 9306.
- </para>
-
- <para>
- The general form of the connection string in the
- <literal>CONNECTION</literal> option is as follows:
- </para>
-
-<programlisting>
-<replaceable>scheme</replaceable>://<replaceable>user_name</replaceable>[:<replaceable>password</replaceable>]@<replaceable>host_name</replaceable>[:<replaceable>port_num</replaceable>]/<replaceable>db_name</replaceable>/<replaceable>tbl_name</replaceable>
-</programlisting>
-
- <para>
- Only <literal>mysql</literal> is supported as the
- <replaceable>scheme</replaceable> value at this point; the
- password and port number are optional.
- </para>
-
- <para>
- Here are some example connection strings:
- </para>
-
-<programlisting>
-CONNECTION='mysql://username:password@hostname:port/database/tablename'
-CONNECTION='mysql://username@hostname/database/tablename'
-CONNECTION='mysql://username:password@hostname/database/tablename'
-</programlisting>
-
- <para>
- The use of <literal>CONNECTION</literal> for specifying the
- connection string is non-optimal and is likely to change in
- future. Keep this in mind for applications that use
- <literal>FEDERATED</literal> tables. Such applications are
- likely to need modification if the format for specifying
- connection information changes.
- </para>
-
- <para>
- Because any password given in the connection string is stored 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>
-
- <section id="federated-limitations">
-
- <title>Limitations of the <literal>FEDERATED</literal> Storage Engine</title>
-
- <para>
- The following items indicate features that the
- <literal>FEDERATED</literal> storage engine does and does not
- support:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- In the first version, the remote server must be a MySQL
- server. Support by <literal>FEDERATED</literal> for other
- database engines may be added in the future.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The remote table that a <literal>FEDERATED</literal> table
- points to <emphasis>must</emphasis> exist before you try to
- access the table through the <literal>FEDERATED</literal>
- table.
- </para>
- </listitem>
-
- <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.
- </para>
- </listitem>
-
- <listitem>
- <para>
- There is no support for transactions.
- </para>
- </listitem>
-
- <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 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>
-
- <listitem>
- <para>
- The <literal>FEDERATED</literal> storage engine supports
- <literal>SELECT</literal>, <literal>INSERT</literal>,
- <literal>UPDATE</literal>, <literal>DELETE</literal>, and
- indexes. It does not support <literal>ALTER TABLE</literal>,
- or any Data Definition Language statements other than
- <literal>DROP TABLE</literal>. The current implementation
- does not use Prepared statements.
-
- <remark>
- It remains to be seen whether the limited subset of the
- client API for the server supports this capability.
- </remark>
- </para>
- </listitem>
-
- <listitem>
- <para>
- Any <literal>DROP TABLE</literal> statement issued against a
- FEDERATED table will only drop the local table, not the
- remote table.
- </para>
- </listitem>
-
- <listitem>
- <para>
- The implementation uses <literal>SELECT</literal>,
- <literal>INSERT</literal>, <literal>UPDATE</literal>, and
- <literal>DELETE</literal>, but not
- <literal>HANDLER</literal>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>FEDERATED</literal> tables do not work with the
- query cache.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- Some of these limitations may be lifted in future versions of
- the <literal>FEDERATED</literal> handler.
- </para>
-
- </section>
-
- </section>
-
- <section id="archive-storage-engine">
-
- <title>The <literal>ARCHIVE</literal> Storage Engine</title>
-
- <indexterm>
- <primary><literal>ARCHIVE</literal> storage engine</primary>
- </indexterm>
-
- <indexterm>
- <primary>storage engine</primary>
- <secondary>ARCHIVE</secondary>
- </indexterm>
-
- <para>
- The <literal>ARCHIVE</literal> storage engine is used for storing
- large amounts of data without indexes in a very small footprint.
- </para>
-
- <para>
- The <literal>ARCHIVE</literal> storage engine is included in MySQL
- binary distributions. To enable this storage engine if you build
- MySQL from source, invoke <command>configure</command> with the
- <option>--with-archive-storage-engine</option> option.
- </para>
-
- <para>
- To examine the source for the <literal>ARCHIVE</literal> engine,
- look in the <filename>sql</filename> directory of a MySQL source
- distribution.
- </para>
-
- <para>
- You can check whether the <literal>ARCHIVE</literal> storage
- engine is available with this statement:
- </para>
-
-<programlisting>
-mysql> <userinput>SHOW VARIABLES LIKE 'have_archive';</userinput>
-</programlisting>
-
- <para>
- When you create an <literal>ARCHIVE</literal> table, the server
- creates a table format file in the database directory. The file
- begins with the table name and has an <filename>.frm</filename>
- extension. The storage engine creates other files, all having
- names beginning with the table name. The data and metadata files
- have extensions of <filename>.ARZ</filename> and
- <filename>.ARM</filename>, respectively. An
- <filename>.ARN</filename> file may appear during optimization
- operations.
- </para>
-
- <para>
- The <literal>ARCHIVE</literal> engine supports
- <literal>INSERT</literal> and <literal>SELECT</literal>, but not
- <literal>DELETE</literal>, <literal>REPLACE</literal>, or
- <literal>UPDATE</literal>. It does support <literal>ORDER
- BY</literal> operations, <literal>BLOB</literal> columns, and
- basically all but spatial data types (see
- <xref linkend="mysql-spatial-datatypes"/>). The
- <literal>ARCHIVE</literal> engine uses row-level locking.
- </para>
-
- <para>
- <emphasis role="bold">Storage:</emphasis> Rows are compressed as
- they are inserted. The <literal>ARCHIVE</literal> engine uses
- <literal>zlib</literal> lossless data compression (see
- <ulink url="http://www.zlib.net/"/>). You can use
- <literal>OPTIMIZE TABLE</literal> to analyze the table and pack it
- into a smaller format (for a reason to use <literal>OPTIMIZE
- TABLE</literal>, see later in this section). Beginning with MySQL
- 5.0.15, the engine also supports <literal>CHECK TABLE</literal>.
- There are several types of insertions that are used:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- An <literal>INSERT</literal> statement just pushes rows into a
- compression buffer, and that buffer flushes as necessary. The
- insertion into the buffer is protected by a lock. A
- <literal>SELECT</literal> forces a flush to occur, unless the
- only insertions that have come in were <literal>INSERT
- DELAYED</literal> (those flush as necessary). See
- <xref linkend="insert-delayed"/>.
- </para>
- </listitem>
-
- <listitem>
- <para>
- A bulk insert is visible only after it completes, unless other
- inserts occur at the same time, in which case it can be seen
- partially. A <literal>SELECT</literal> never causes a flush of
- a bulk insert unless a normal insert occurs while it is
- loading.
- </para>
- </listitem>
-
- </itemizedlist>
-
- <para>
- <emphasis role="bold">Retrieval</emphasis>: On retrieval, rows are
- uncompressed on demand; there is no row cache. A
- <literal>SELECT</literal> operation performs a complete table
- scan: When a <literal>SELECT</literal> occurs, it finds out how
- many rows are currently available and reads that number of rows.
- <literal>SELECT</literal> is performed as a consistent read. Note
- that lots of <literal>SELECT</literal> statements during insertion
- can deteriorate the compression, unless only bulk or delayed
- inserts are used. To achieve better compression, you can use
- <literal>OPTIMIZE TABLE</literal> or <literal>REPAIR
- TABLE</literal>. The number of rows in <literal>ARCHIVE</literal>
- tables reported by <literal>SHOW TABLE STATUS</literal> is always
- accurate. See <xref linkend="optimize-table"/>,
- <xref linkend="repair-table"/>, and
- <xref linkend="show-table-status"/>.
- </para>
-
- <para>
- <emphasis role="bold">Additional resources</emphasis>
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- A forum dedicated to the <literal>ARCHIVE</literal> storage
- engine is available at
- <ulink url="&base-url-forum-list;?112"/>.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
- <section id="csv-storage-engine">
-
- <title>The <literal>CSV</literal> Storage Engine</title>
-
- <indexterm>
- <primary><literal>CSV</literal> storage engine</primary>
- </indexterm>
-
- <indexterm>
- <primary>tables</primary>
- <secondary>CSV</secondary>
- </indexterm>
-
- <para>
- The <literal>CSV</literal> storage engine stores data in text
- files using comma-separated values format.
- </para>
-
- <para>
- To enable this storage engine, use the
- <option>--with-csv-storage-engine</option> option to
- <command>configure</command> when you build MySQL.
- </para>
-
- <para>
- The <literal>CSV</literal> storage engine is included in MySQL-Max
- binary distributions. To enable this storage engine if you build
- MySQL from source, invoke <command>configure</command> with the
- <option>--with-csv-storage-engine</option> option.
- </para>
-
- <para>
- To examine the source for the <literal>CSV</literal> engine, look
- in the <filename>sql/examples</filename> directory of a MySQL
- source distribution.
- </para>
-
- <para>
- When you create a <literal>CSV</literal> table, the server creates
- a table format file in the database directory. The file begins
- with the table name and has an <filename>.frm</filename>
- extension. The storage engine also creates a data file. Its name
- begins with the table name and has a <filename>.CSV</filename>
- extension. The data file is a plain text file. When you store data
- into the table, the storage engine saves it into the data file in
- comma-separated values format.
- </para>
-
-<programlisting>
-mysql> <userinput>CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV;</userinput>
-Query OK, 0 rows affected (0.12 sec)
-
-mysql> <userinput>INSERT INTO test VALUES(1,'record one'),(2,'record two');</userinput>
-Query OK, 2 rows affected (0.00 sec)
-Records: 2 Duplicates: 0 Warnings: 0
-
-mysql> <userinput>SELECT * FROM test;</userinput>
-+------+------------+
-| i | c |
-+------+------------+
-| 1 | record one |
-| 2 | record two |
-+------+------------+
-2 rows in set (0.00 sec)
-</programlisting>
-
- <para>
- If you examine the <filename>test.CSV</filename> file in the
- database directory created by executing the preceding statements,
- its contents should look like this:
- </para>
-
-<programlisting>
-"1","record one"
-"2","record two"
-</programlisting>
-
- <para>
- This format can be read, and even written, by spreadsheet
- applications such as Microsoft Excel or StarOffice Calc.
- </para>
-
- <para>
- The <literal>CSV</literal> storage engine does not support
- indexing.
- </para>
-
- </section>
-
- <section id="blackhole-storage-engine">
-
- <title>The <literal>BLACKHOLE</literal> Storage Engine</title>
-
- <indexterm>
- <primary><literal>BLACKHOLE</literal> storage engine</primary>
- </indexterm>
-
- <indexterm>
- <primary>tables</primary>
- <secondary>BLACKHOLE</secondary>
- </indexterm>
-
- <para>
- The <literal>BLACKHOLE</literal> storage engine acts as a
- <quote>black hole</quote> that accepts data but throws it away and
- does not store it. Retrievals always return an empty result:
- </para>
-
-<programlisting>
-mysql> <userinput>CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;</userinput>
-Query OK, 0 rows affected (0.03 sec)
-
-mysql> <userinput>INSERT INTO test VALUES(1,'record one'),(2,'record two');</userinput>
-Query OK, 2 rows affected (0.00 sec)
-Records: 2 Duplicates: 0 Warnings: 0
-
-mysql> <userinput>SELECT * FROM test;</userinput>
-Empty set (0.00 sec)
-</programlisting>
-
- <para>
- The <literal>BLACKHOLE</literal> storage engine is included in
- MySQL-Max binary distributions. To enable this storage engine if
- you build MySQL from source, invoke <command>configure</command>
- with the <option>--with-blackhole-storage-engine</option> option.
- </para>
-
- <para>
- To examine the source for the <literal>BLACKHOLE</literal> engine,
- look in the <filename>sql</filename> directory of a MySQL source
- distribution.
- </para>
-
- <para>
- When you create a <literal>BLACKHOLE</literal> table, the server
- creates a table format file in the database directory. The file
- begins with the table name and has an <filename>.frm</filename>
- extension. There are no other files associated with the table.
- </para>
-
- <para>
- The <literal>BLACKHOLE</literal> storage engine supports all kinds
- of indexes. That is, you can include index declarations in the
- table definition.
- </para>
-
- <para>
- You can check whether the <literal>BLACKHOLE</literal> storage
- engine is available with this statement:
- </para>
-
-<programlisting>
-mysql> <userinput>SHOW VARIABLES LIKE 'have_blackhole_engine';</userinput>
-</programlisting>
-
- <para>
- Inserts into a <literal>BLACKHOLE</literal> table do not store any
- data, but if the binary log is enabled, the SQL statements are
- logged (and replicated to slave servers). This can be useful as a
- repeater or filter mechanism. For example, suppose that your
- application requires slave-side filtering rules, but transferring
- all binary log data to the slave first results in too much
- traffic. In such a case, it is possible to set up on the master
- host a <quote>dummy</quote> slave process whose default storage
- engine is <literal>BLACKHOLE</literal>, depicted as follows:
- </para>
-
- <mediaobject>
- <imageobject>
- <imagedata fileref="images/blackhole-1.png" format="PNG"/>
- </imageobject>
- <textobject>
- <phrase lang="en">Replication using <literal>BLACKHOLE</literal>
- for filtering</phrase>
- </textobject>
- </mediaobject>
-
- <para>
- The master writes to its binary log. The <quote>dummy</quote>
- <command>mysqld</command> process acts as a slave, applying the
- desired combination of <literal>replicate-do-*</literal> and
- <literal>replicate-ignore-*</literal> rules, and writes a new,
- filtered binary log of its own. (See
- <xref linkend="replication-options"/>.) This filtered log is
- provided to the slave.
- </para>
-
- <para>
- The dummy process does not actually store any data, so there is
- little processing overhead incurred by running the additional
- <command>mysqld</command> process on the replication master host.
- This type of setup can be repeated with additional replication
- slaves.
- </para>
-
- <para>
- Other possible uses for the <literal>BLACKHOLE</literal> storage
- engine include:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- Verification of dump file syntax.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Measurement of the overhead from binary logging, by comparing
- performance using <literal>BLACKHOLE</literal> with and
- without binary logging enabled.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>BLACKHOLE</literal> is essentially a
- <quote>no-op</quote> storage engine, so it could be used for
- finding performance bottlenecks not related to the storage
- engine itself.
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
</chapter>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r3018 - trunk/refman-5.0 | mcbrown | 15 Aug |
