Author: paul
Date: 2006-01-10 21:16:47 +0100 (Tue, 10 Jan 2006)
New Revision: 758
Log:
r6067@frost: paul | 2006-01-10 14:15:27 -0600
General revisions.
Modified:
trunk/
trunk/refman-4.1/innodb.xml
trunk/refman-5.0/innodb.xml
trunk/refman-5.1/innodb.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6058
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:1994
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6067
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:1994
Modified: trunk/refman-4.1/innodb.xml
===================================================================
--- trunk/refman-4.1/innodb.xml 2006-01-10 18:03:14 UTC (rev 757)
+++ trunk/refman-4.1/innodb.xml 2006-01-10 20:16:47 UTC (rev 758)
@@ -1295,7 +1295,7 @@
replication setup you should use
<literal>innodb_flush_log_at_trx_commit=1</literal>,
<literal>sync-binlog=1</literal>, and
- <literal>innodb_safe_binlog</literal> in your master
+ <literal>innodb_safe_binlog</literal> in your master server
<filename>my.cnf</filename> file.
</para>
</listitem>
@@ -1373,9 +1373,10 @@
</para>
<para>
- This option turns off next-key locking in
- <literal>InnoDB</literal> searches and index scans. Default
- value for this option is false.
+ This variable controls next-key locking in
+ <literal>InnoDB</literal> searches and index scans. By
+ default, this variable is 0 (disabled), which means that
+ next-key locking is enabled.
</para>
<para>
@@ -1390,11 +1391,11 @@
record. If a user has a shared or exclusive lock on record
<emphasis>R</emphasis> in an index, another user cannot insert
a new index record immediately before <emphasis>R</emphasis>
- in the order of the index. This option causes
+ in the order of the index. Enabling this variable causes
<literal>InnoDB</literal> not to use next-key locking in
searches or index scans. Next-key locking is still used to
ensure foreign key constraints and duplicate key checking.
- Note that using this option may cause phantom problems:
+ Note that enabling this variable may cause phantom problems:
Suppose that you want to read and lock all children from the
<literal>child</literal> table with an identifier value larger
than 100, with the intention of updating some column in the
@@ -1408,17 +1409,17 @@
<para>
Suppose that there is an index on the <literal>id</literal>
column. The query scans that index starting from the first
- record where id is bigger than 100. If the locks set on the
- index records do not lock out inserts made in the gaps, a new
- row is meanwhile inserted to the table. If you execute the
- same <literal>SELECT</literal> within the same transaction,
- you see a new row in the result set returned by the query.
- This also means, that if new items are added to the database,
- <literal>InnoDB</literal> does not guarantee serializability
- instead conflict serializability is still guaranteed.
- Therefore, if this option is used <literal>InnoDB</literal>
- guarantees at most isolation level <literal>READ
- COMMITTED</literal>. This option is available as of MySQL
+ record where <literal>id</literal> is larger than 100. If the
+ locks set on the index records do not lock out inserts made in
+ the gaps, another client can insert a new row into the table.
+ If you execute the same <literal>SELECT</literal> within the
+ same transaction, you see a new row in the result set returned
+ by the query. This also means that if new items are added to
+ the database, <literal>InnoDB</literal> does not guarantee
+ serializability Therefore, if this variable is enabled
+ <literal>InnoDB</literal> guarantees at most isolation level
+ <literal>READ COMMITTED</literal>. (Conflict serializability
+ is still guaranteed.) This variable is available as of MySQL
4.1.4.
</para>
</listitem>
@@ -1698,26 +1699,27 @@
that the directories you have specified for
<literal>InnoDB</literal> data files and log files exist and that
the MySQL server has access rights to those directories.
- <literal>InnoDB</literal> cannot create directories, only files.
+ <literal>InnoDB</literal> does create directories, only files.
Check also that you have enough disk space for the data and log
files.
</para>
<para>
It is best to run the MySQL server <command>mysqld</command> from
- the command prompt when you create an <literal>InnoDB</literal>
- database, not from the <command>mysqld_safe</command> wrapper or
- as a Windows service. When you run from a command prompt you see
- what <command>mysqld</command> prints and what is happening. On
- Unix, just invoke <command>mysqld</command>. On Windows, use the
+ the command prompt when you first start the server with
+ <literal>InnoDB</literal> enabled, not from the
+ <command>mysqld_safe</command> wrapper or as a Windows service.
+ When you run from a command prompt you see what
+ <command>mysqld</command> prints and what is happening. On Unix,
+ just invoke <command>mysqld</command>. On Windows, use the
<option>--console</option> option.
</para>
<para>
When you start the MySQL server after initially configuring
<literal>InnoDB</literal> in your option file,
- <literal>InnoDB</literal> creates your data files and log files.
- <literal>InnoDB</literal> prints something like the following:
+ <literal>InnoDB</literal> creates your data files and log files,
+ and prints something like this:
</para>
<programlisting>
@@ -1747,11 +1749,11 @@
</programlisting>
<para>
- A new <literal>InnoDB</literal> database has been created. You can
- connect to the MySQL server with the usual MySQL client programs
- like <command>mysql</command>. When you shut down the MySQL server
- with <command>mysqladmin shutdown</command>, the output is like
- the following:
+ At this point <literal>InnoDB</literal> has initialized its
+ tablespace and log files. You can connect to the MySQL server with
+ the usual MySQL client programs like <command>mysql</command>.
+ When you shut down the MySQL server with <command>mysqladmin
+ shutdown</command>, the output is like this:
</para>
<programlisting>
@@ -1763,12 +1765,12 @@
<para>
You can look at the data file and log directories and you see the
- files created. The log directory also contains a small file named
- <filename>ib_arch_log_0000000000</filename>. That file resulted
- from the database creation, after which <literal>InnoDB</literal>
- switched off log archiving. When MySQL is started again, the data
- files and log files have been created, so the output is much
- briefer:
+ files created there. The log directory also contains a small file
+ named <filename>ib_arch_log_0000000000</filename>. That file
+ resulted from the database creation, after which
+ <literal>InnoDB</literal> switched off log archiving. When MySQL
+ is started again, the data files and log files have been created
+ already, so the output is much briefer:
</para>
<programlisting>
@@ -1779,10 +1781,10 @@
<para>
Starting from MySQL 4.1.1, you can add the option
<literal>innodb_file_per_table</literal> to
- <filename>my.cnf</filename>, and make <literal>InnoDB</literal> to
- store each table into its own <filename>.ibd</filename> file in a
- database directory of MySQL. See
- <xref linkend="multiple-tablespaces"/>.
+ <filename>my.cnf</filename> to make <literal>InnoDB</literal>
+ store each table to its own <filename>.ibd</filename> file in the
+ same MySQL database directory where the <filename>.frm</filename>
+ file is created. See <xref linkend="multiple-tablespaces"/>.
</para>
<section id="error-creating-innodb">
@@ -1790,8 +1792,9 @@
<title>&title-error-creating-innodb;</title>
<para>
- If <literal>InnoDB</literal> prints an operating system error in
- a file operation, usually the problem is one of the following:
+ If <literal>InnoDB</literal> prints an operating system error
+ during a file operation, usually the problem has one of the
+ following causes:
</para>
<itemizedlist>
@@ -1812,10 +1815,10 @@
<listitem>
<para>
- <command>mysqld</command> cannot not read the proper
+ <command>mysqld</command> cannot read the proper
<filename>my.cnf</filename> or <filename>my.ini</filename>
- option file, and consequently does not see the options you
- specified.
+ option file, and consequently does not see the options that
+ you specified.
</para>
</listitem>
@@ -1828,15 +1831,16 @@
<listitem>
<para>
You have created a subdirectory whose name is equal to a
- data file you specified.
+ data file that you specified, so the name cannot be used as
+ a filename.
</para>
</listitem>
<listitem>
<para>
- There is a syntax error in
+ There is a syntax error in the
<literal>innodb_data_home_dir</literal> or
- <literal>innodb_data_file_path</literal>.
+ <literal>innodb_data_file_path</literal> value.
</para>
</listitem>
@@ -1847,14 +1851,14 @@
to initialize its tablespace or its log files, you should delete
all files created by <literal>InnoDB</literal>. This means all
<filename>ibdata</filename> files and all
- <filename>ib_logfile</filename>s. In case you created some
- <literal>InnoDB</literal> tables, delete the corresponding
- <filename>.frm</filename> files for these tables (and any
- <filename>.ibd</filename> files if you are using multiple
- tablespaces) from the MySQL database directories as well. Then
- you can try the <literal>InnoDB</literal> database creation
- again. It is best to start the MySQL server from a command
- prompt so that you see what is happening.
+ <filename>ib_logfile</filename> files. In case you have already
+ created some <literal>InnoDB</literal> tables, delete the
+ corresponding <filename>.frm</filename> files for these tables
+ (and any <filename>.ibd</filename> files if you are using
+ multiple tablespaces) from the MySQL database directories as
+ well. Then you can try the <literal>InnoDB</literal> database
+ creation again. It is best to start the MySQL server from a
+ command prompt so that you see what is happening.
</para>
</section>
@@ -1866,11 +1870,10 @@
<title>&title-using-innodb-tables;</title>
<para>
- Suppose that you have started the MySQL client with the command
- <literal>mysql test</literal>. To create an
- <literal>InnoDB</literal> table, you must specify an
- <literal>ENGINE = InnoDB</literal> or <literal>TYPE =
- InnoDB</literal> option in the table creation SQL statement:
+ To create an <literal>InnoDB</literal> table, you must specify an
+ <literal>ENGINE = InnoDB</literal> option (or <literal>TYPE =
+ InnoDB</literal>, but this is deprecated) in the <literal>CREATE
+ TABLE</literal> statement:
</para>
<programlisting>
@@ -1879,18 +1882,20 @@
</programlisting>
<para>
- The SQL statement creates a table and an index on column
+ The statement creates a table and an index on column
<literal>a</literal> in the <literal>InnoDB</literal> tablespace
that consists of the data files that you specified in
<filename>my.cnf</filename>. In addition, MySQL creates a file
<filename>customers.frm</filename> in the
<filename>test</filename> directory under the MySQL database
- directory. Internally, <literal>InnoDB</literal> adds to its own
- data dictionary an entry for table
- <literal>'test/customers'</literal>. This means you can create a
- table of the same name <literal>customers</literal> in some other
- database, and the table names do not collide inside
- <literal>InnoDB</literal>.
+ directory. Internally, <literal>InnoDB</literal> adds an entry for
+ the table to its own data dictionary. The entry includes the
+ database name. For example, if <literal>test</literal> is the
+ database in which the <literal>customers</literal> table is
+ created, the entry is for <literal>'test/customers'</literal>.
+ This means you can create a table of the same name
+ <literal>customers</literal> in some other database, and the table
+ names do not collide inside <literal>InnoDB</literal>.
</para>
<para>
@@ -1907,7 +1912,7 @@
</programlisting>
<para>
- Note that the statistics <literal>SHOW</literal> gives about
+ Note that the statistics <literal>SHOW</literal> displays for
<literal>InnoDB</literal> tables are only approximate. They are
used in SQL optimization. Table and index reserved sizes in bytes
are accurate, though.
@@ -2014,7 +2019,7 @@
If you have <literal>UNIQUE</literal> constraints on secondary
keys, starting from MySQL 3.23.52, you can speed up a table
import by turning off the uniqueness checks temporarily during
- the import session:
+ the import operation:
</para>
<programlisting>
@@ -2048,19 +2053,19 @@
size of the <literal>InnoDB</literal> buffer pool to reduce disk
I/O. Do not use more than 80% of the physical memory, though.
You can also increase the sizes of the <literal>InnoDB</literal>
- log files and the log files.
+ log files.
</para>
<para>
Make sure that you do not fill up the tablespace:
<literal>InnoDB</literal> tables require a lot more disk space
than <literal>MyISAM</literal> tables. If an <literal>ALTER
- TABLE</literal> runs out of space, it starts a rollback, and
- that can take hours if it is disk-bound. For inserts,
- <literal>InnoDB</literal> uses the insert buffer to merge
- secondary index records to indexes in batches. That saves a lot
- of disk I/O. In rollback, no such mechanism is used, and the
- rollback can take 30 times longer than the insertion.
+ TABLE</literal> operation runs out of space, it starts a
+ rollback, and that can take hours if it is disk-bound. For
+ inserts, <literal>InnoDB</literal> uses the insert buffer to
+ merge secondary index records to indexes in batches. That saves
+ a lot of disk I/O. For rollback, no such mechanism is used, and
+ the rollback can take 30 times longer than the insertion.
</para>
<para>
@@ -2078,12 +2083,12 @@
<title>&title-innodb-auto-increment-column;</title>
<para>
- If you specify an <literal>AUTO_INCREMENT</literal> column for a
- table, the <literal>InnoDB</literal> table handle in the data
- dictionary contains a special counter called the auto-increment
- counter that is used in assigning new values for the column. The
- auto-increment counter is stored only in main memory, not on
- disk.
+ If you specify an <literal>AUTO_INCREMENT</literal> column for
+ an <literal>InnoDB</literal> table, the table handle in the
+ <literal>InnoDB</literal> data dictionary contains a special
+ counter called the auto-increment counter that is used in
+ assigning new values for the column. This counter is stored only
+ in main memory, not on disk.
</para>
<para>
@@ -2091,8 +2096,8 @@
initialize the auto-increment counter for a table
<literal>T</literal> that contains an
<literal>AUTO_INCREMENT</literal> column named
- <literal>ai_col</literal>: After a server startup, when a user
- first does an insert to a table <literal>T</literal>,
+ <literal>ai_col</literal>: After a server startup, for the first
+ insert into a table <literal>T</literal>,
<literal>InnoDB</literal> executes the equivalent of this
statement:
</para>
@@ -2102,16 +2107,17 @@
</programlisting>
<para>
- The value retrieved by the statement is incremented by one and
- assigned to the column and the auto-increment counter of the
- table. If the table is empty, the value <literal>1</literal> is
- assigned. If the auto-increment counter is not initialized and
- the user invokes a <literal>SHOW TABLE STATUS</literal>
+ <literal>InnoDB</literal> increments by one the value retrieved
+ by the statement and assigns it to the column and to the
+ auto-increment counter for the table. If the table is empty,
+ <literal>InnoDB</literal> uses the value <literal>1</literal>.
+ If a user invokes a <literal>SHOW TABLE STATUS</literal>
statement that displays output for the table
- <literal>T</literal>, the counter is initialized (but not
- incremented) and stored for use by later inserts. Note that in
- this initialization we do a normal exclusive-locking read on the
- table and the lock lasts to the end of the transaction.
+ <literal>T</literal> and the auto-increment counter has not been
+ initialized, <literal>InnoDB</literal> initializes but does not
+ increment the value and stores it for use by later inserts. Note
+ that this initialization uses a normal exclusive-locking read on
+ the table and the lock lasts to the end of the transaction.
</para>
<para>
@@ -2121,7 +2127,24 @@
</para>
<para>
- Note that if the user specifies <literal>NULL</literal> or
+ After the auto-increment counter has been initialized, if a user
+ does not explicitly specify a value for an
+ <literal>AUTO_INCREMENT</literal> column,
+ <literal>InnoDB</literal> increments the counter by one and
+ assigns the new value to the column. If the user inserts a row
+ that explicitly specifies the column value, and the value is
+ bigger than the current counter value, the counter is set to the
+ specified column value.
+ </para>
+
+ <para>
+ You may see gaps in the sequence of values assigned to the
+ <literal>AUTO_INCREMENT</literal> column if you roll back
+ transactions that have generated numbers using the counter.
+ </para>
+
+ <para>
+ If a user specifies <literal>NULL</literal> or
<literal>0</literal> for the <literal>AUTO_INCREMENT</literal>
column in an <literal>INSERT</literal>,
<literal>InnoDB</literal> treats the row as if the value had not
@@ -2129,17 +2152,15 @@
</para>
<para>
- After the auto-increment counter has been initialized, if a user
- inserts a row that explicitly specifies the column value, and
- the value is bigger than the current counter value, the counter
- is set to the specified column value. If the user does not
- explicitly specify a value, <literal>InnoDB</literal> increments
- the counter by one and assigns the new value to the column.
+ The behavior of the auto-increment mechanism is not defined if a
+ user assigns a negative value to the column or if the value
+ becomes bigger than the maximum integer that can be stored in
+ the specified integer type.
</para>
<para>
When accessing the auto-increment counter,
- <literal>InnoDB</literal> uses a special table level
+ <literal>InnoDB</literal> uses a special table-level
<literal>AUTO-INC</literal> lock that it keeps to the end of the
current SQL statement, not to the end of the transaction. The
special lock release strategy was introduced to improve
@@ -2150,19 +2171,14 @@
</para>
<para>
- Note that you may see gaps in the sequence of values assigned to
- the <literal>AUTO_INCREMENT</literal> column if you roll back
- transactions that have gotten numbers from the counter.
+ <literal>InnoDB</literal> uses the in-memory auto-increment
+ counter as long as he server runs. When the server is stopped
+ and restarted, <literal>InnoDB</literal> reinitializes the
+ counter for each table for the first <literal>INSERT</literal>
+ to the table, as described earlier.
</para>
<para>
- The behavior of the auto-increment mechanism is not defined if a
- user assigns a negative value to the column or if the value
- becomes bigger than the maximum integer that can be stored in
- the specified integer type.
- </para>
-
- <para>
Beginning with MySQL 4.1.12, <literal>InnoDB</literal> supports
the <literal>AUTO_INCREMENT =
<replaceable>n</replaceable></literal> table option in
@@ -2218,8 +2234,8 @@
<listitem>
<para>
- Both tables must be <literal>InnoDB</literal> type and they
- must not be temporary tables.
+ Both tables must be <literal>InnoDB</literal> tables and
+ they must not be <literal>TEMPORARY</literal> tables.
</para>
</listitem>
@@ -2253,11 +2269,11 @@
<listitem>
<para>
- If the
- <literal>CONSTRAINT<replaceable>symbol</replaceable></literal>
- is given, it must be unique in the database. If it is not
- given, <literal>InnoDB</literal> creates the name
- automatically.
+ If the <literal>CONSTRAINT
+ <replaceable>symbol</replaceable></literal> clause is given,
+ the <replaceable>symbol</replaceable> value must be unique
+ in the database. If the clause is not given,
+ <literal>InnoDB</literal> creates the name automatically.
</para>
</listitem>
@@ -2266,15 +2282,15 @@
<para>
<literal>InnoDB</literal> rejects any <literal>INSERT</literal>
or <literal>UPDATE</literal> operation that attempts to create a
- foreign key value in a child table without a matching candidate
- key value in the parent table. The action
+ foreign key value in a child table if there is no a matching
+ candidate key value in the parent table. The action
<literal>InnoDB</literal> takes for any
<literal>UPDATE</literal> or <literal>DELETE</literal> operation
that attempts to update or delete a candidate key value in the
parent table that has some matching rows in the child table is
dependent on the <emphasis>referential action</emphasis>
specified using <literal>ON UPDATE</literal> and <literal>ON
- DETETE</literal> subclauses of the <literal>FOREIGN
+ DELETE</literal> subclauses of the <literal>FOREIGN
KEY</literal> clause. When the user attempts to delete or update
a row from a parent table, and there are one or more matching
rows in the child table, <literal>InnoDB</literal> supports five
@@ -2292,8 +2308,8 @@
<literal>CASCADE</literal>: Delete or update the row from
the parent table and automatically delete or update the
matching rows in the child table. <literal>ON DELETE
- CASCADE</literal> is available starting from MySQL 3.23.50
- and <literal>ON UPDATE CASCADE</literal> is available
+ CASCADE</literal> is supported starting from MySQL 3.23.50
+ and <literal>ON UPDATE CASCADE</literal> is supported
starting from 4.0.8. Between two tables, you should not
define several <literal>ON UPDATE CASCADE</literal> clauses
that act on the same column in the parent table or in the
@@ -2305,12 +2321,12 @@
<para>
<literal>SET NULL</literal>: Delete or update the row from
the parent table and set the foreign key column or columns
- in the child table to <literal>NULL</literal>. This is only
- valid if the foreign key columns do not have the
- <literal>NOT NULL</literal> qualifier specified. <literal>ON
- DELETE SET NULL</literal> is available starting from MySQL
- 3.23.50 and <literal>ON UPDATE SET NULL</literal> is
- available starting from 4.0.8.
+ in the child table to <literal>NULL</literal>. This is valid
+ only if the foreign key columns do not have the <literal>NOT
+ NULL</literal> qualifier specified. <literal>ON DELETE SET
+ NULL</literal> is available starting from MySQL 3.23.50 and
+ <literal>ON UPDATE SET NULL</literal> is available starting
+ from 4.0.8.
</para>
</listitem>
@@ -2353,23 +2369,14 @@
</itemizedlist>
<para>
- <literal>InnoDB</literal> supports the same options when the
- candidate key in the parent table is updated. With
- <literal>CASCADE</literal>, the foreign key column or columns in
- the child table are set to the new values of the candidate key
- in the parent table. In the same way, the updates cascade if
- updated columns in the child table reference foreign keys in
- another table.
- </para>
-
- <para>
Note that <literal>InnoDB</literal> supports foreign key
- references within a table and in these cases child table really
- means dependent records within the table.
+ references within a table. In these cases, <quote>child table
+ records</quote> really refers to dependent records within the
+ same table.
</para>
<para>
- <literal>InnoDB</literal> needs indexes on foreign keys and
+ <literal>InnoDB</literal> requires indexes on foreign keys and
referenced keys so that foreign key checks can be fast and not
require a table scan. Starting with MySQL 4.1.2, the index on
the foreign key is created automatically. In older versions, the
@@ -2392,11 +2399,11 @@
<para>
If MySQL reports an error number 1005 from a <literal>CREATE
TABLE</literal> statement, and the error message refers to errno
- 150, this means that the table creation failed because a foreign
- key constraint was not correctly formed. Similarly, if an
- <literal>ALTER TABLE</literal> fails and it refers to errno 150,
- that means a foreign key definition would be incorrectly formed
- for the altered table. Starting from MySQL 4.0.13, you can use
+ 150, table creation failed because a foreign key constraint was
+ not correctly formed. Similarly, if an <literal>ALTER
+ TABLE</literal> fails and it refers to errno 150, that means a
+ foreign key definition would be incorrectly formed for the
+ altered table. Starting from MySQL 4.0.13, you can use
<literal>SHOW INNODB STATUS</literal> to display a detailed
explanation of the latest <literal>InnoDB</literal> foreign key
error in the server.
@@ -2409,11 +2416,16 @@
</para>
<para>
+ <emphasis role="bold">Note</emphasis>: Currently, triggers are
+ not activated by cascaded foreign key actions.
+ </para>
+
+ <para>
<emphasis role="bold">Deviation from SQL standards</emphasis>:
- If in the parent table there are several rows that have the same
- referenced key value, then <literal>InnoDB</literal> acts in
- foreign key checks as if the other parent rows with the same key
- value do not exist. For example, if you have defined a
+ If there are several rows in the parent table that have the same
+ referenced key value, <literal>InnoDB</literal> acts in foreign
+ key checks as if the other parent rows with the same key value
+ do not exist. For example, if you have defined a
<literal>RESTRICT</literal> type constraint, and there is a
child row with several parent rows, <literal>InnoDB</literal>
does not allow the deletion of any of those parent rows.
@@ -2463,16 +2475,16 @@
</para>
<para>
- A simple example that relates <literal>parent</literal> and
- <literal>child</literal> tables through a single-column foreign
- key:
+ Here is a simple example that relates <literal>parent</literal>
+ and <literal>child</literal> tables through a single-column
+ foreign key:
</para>
<programlisting>
-CREATE TABLE parent(id INT NOT NULL,
+CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
-CREATE TABLE child(id INT, parent_id INT,
+CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
@@ -2516,7 +2528,7 @@
</para>
<programlisting>
-ALTER TABLE yourtablename
+ALTER TABLE <replaceable>yourtablename</replaceable>
ADD [CONSTRAINT <replaceable>symbol</replaceable>] FOREIGN KEY [<replaceable>id</replaceable>] (<replaceable>index_col_name</replaceable>, ...)
REFERENCES <replaceable>tbl_name</replaceable> (<replaceable>index_col_name</replaceable>, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
@@ -2557,10 +2569,11 @@
<literal>CONSTRAINT</literal> name when you created the foreign
key, you can refer to that name to drop the foreign key. (A
constraint name can be given as of MySQL 4.0.18.) Otherwise, the
- <literal>fk_symbol</literal> value is internally generated by
- <literal>InnoDB</literal> when the foreign key is created. To
- find out the symbol when you want to drop a foreign key, use the
- <literal>SHOW CREATE TABLE</literal> statement. For example:
+ <replaceable>fk_symbol</replaceable> value is internally
+ generated by <literal>InnoDB</literal> when the foreign key is
+ created. To find out the symbol when you want to drop a foreign
+ key, use the <literal>SHOW CREATE TABLE</literal> statement. For
+ example:
</para>
<programlisting>
@@ -2584,23 +2597,24 @@
) TYPE=InnoDB CHARSET=latin1
1 row in set (0.01 sec)
-mysql> <userinput>ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;</userinput>
+mysql> <userinput>ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;</userinput>
</programlisting>
<para>
You cannot add a foreign key and drop a foreign key in separate
clauses of a single <literal>ALTER TABLE</literal> statement.
- You must use separate statements.
+ Separate statements are required.
</para>
<para>
Starting from MySQL 3.23.50, the <literal>InnoDB</literal>
- parser allows you to use backticks around table and column names
- in a <literal>FOREIGN KEY ... REFERENCES ...</literal> clause.
- Starting from MySQL 4.0.5, the <literal>InnoDB</literal> parser
- also takes into account the
- <literal>lower_case_table_names</literal> system variable
- setting.
+ parser allows table and column identifiers in a <literal>FOREIGN
+ KEY … REFERENCES …</literal> clause to be quoted
+ within backticks. (Alternatively, double quotes can be used if
+ the <literal>ANSI_QUOTES</literal> SQL mode is enabled.) The
+ <literal>InnoDB</literal> parser also takes into account the
+ setting of the <literal>lower_case_table_names</literal> system
+ variable.
</para>
<para>
@@ -2631,14 +2645,14 @@
</programlisting>
<para>
- From this version, <command>mysqldump</command> also produces
- correct definitions of tables to the dump file, and does not
- forget about the foreign keys.
+ <command>mysqldump</command> also produces correct definitions
+ of tables to the dump file, and does not forget about the
+ foreign keys.
</para>
<para>
- You can display the foreign key constraints for a table like
- this:
+ You can also display the foreign key constraints for a table
+ like this:
</para>
<programlisting>
Modified: trunk/refman-5.0/innodb.xml
===================================================================
--- trunk/refman-5.0/innodb.xml 2006-01-10 18:03:14 UTC (rev 757)
+++ trunk/refman-5.0/innodb.xml 2006-01-10 20:16:47 UTC (rev 758)
@@ -1295,7 +1295,7 @@
replication setup you should use
<literal>innodb_flush_log_at_trx_commit=1</literal>,
<literal>sync-binlog=1</literal>, and, before MySQL 5.0.3,
- <literal>innodb_safe_binlog</literal> in your master
+ <literal>innodb_safe_binlog</literal> in your master server
<filename>my.cnf</filename> file.
(<literal>innodb_safe_binlog</literal> is not needed from
5.0.3 on.)
@@ -1308,9 +1308,10 @@
</para>
<para>
- This option turns off next-key locking in
- <literal>InnoDB</literal> searches and index scans. Default
- value for this option is false.
+ This variable controls next-key locking in
+ <literal>InnoDB</literal> searches and index scans. By
+ default, this variable is 0 (disabled), which means that
+ next-key locking is enabled.
</para>
<para>
@@ -1325,11 +1326,11 @@
record. If a user has a shared or exclusive lock on record
<emphasis>R</emphasis> in an index, another user cannot insert
a new index record immediately before <emphasis>R</emphasis>
- in the order of the index. This option causes
+ in the order of the index. Enabling this variable causes
<literal>InnoDB</literal> not to use next-key locking in
searches or index scans. Next-key locking is still used to
ensure foreign key constraints and duplicate key checking.
- Note that using this option may cause phantom problems:
+ Note that enabling this variable may cause phantom problems:
Suppose that you want to read and lock all children from the
<literal>child</literal> table with an identifier value larger
than 100, with the intention of updating some column in the
@@ -1345,37 +1346,38 @@
column. The query scans that index starting from the first
record where <literal>id</literal> is greater than 100. If the
locks set on the index records do not lock out inserts made in
- the gaps, a new row is meanwhile inserted into the table. If
- you execute the same <literal>SELECT</literal> within the same
- transaction, you see a new row in the result set returned by
- the query. This also means, that if new items are added to the
- database, <literal>InnoDB</literal> does not guarantee
- serializability; however, conflict serializability is still
- guaranteed. Therefore, if this option is used
+ the gaps, another client can insert a new row into the table.
+ If you execute the same <literal>SELECT</literal> within the
+ same transaction, you see a new row in the result set returned
+ by the query. This also means that if new items are added to
+ the database, <literal>InnoDB</literal> does not guarantee
+ serializability. Therefore, if this variable is enabled
<literal>InnoDB</literal> guarantees at most isolation level
- <literal>READ COMMITTED</literal>.
+ <literal>READ COMMITTED</literal>. (Conflict serializability
+ is still guaranteed.)
</para>
<para>
Starting from MySQL 5.0.2, this option is even more unsafe.
<literal>InnoDB</literal> in an <literal>UPDATE</literal> or a
<literal>DELETE</literal> only locks rows that it updates or
- deletes. This greatly reduces the probability of deadlocks but
- they can happen. Note that this option still does not allow
- operations such as <literal>UPDATE</literal> to overtake like
- operations (such as another <literal>UPDATE</literal>) even in
- the case when they affect different rows. Consider the
- following example:
+ deletes. This greatly reduces the probability of deadlocks,
+ but they can happen. Note that enabling this variable still
+ does not allow operations such as <literal>UPDATE</literal> to
+ overtake other similar operations (such as another
+ <literal>UPDATE</literal>) even in the case when they affect
+ different rows. Consider the following example, beginning with
+ this table:
</para>
<programlisting>
-CREATE TABLE A(A INT NOT NULL, B INT);
+CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB;
INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
</programlisting>
<para>
- If one connection executes a query:
+ Suppose that one client executes these statements:
</para>
<programlisting>
@@ -1384,8 +1386,8 @@
</programlisting>
<para>
- and the other connection executes, following the first one,
- another query:
+ Then suppose that another client executes these statements
+ following those of the first client:
</para>
<programlisting>
@@ -1394,19 +1396,23 @@
</programlisting>
<para>
- Then query two has to wait for a commit or rollback of query
- one, because query one has an exclusive lock to row (2,3), and
- query two while scanning rows also tries to take an exclusive
- lock to the same row (2,3), which it cannot have. This is
- because query two first takes an exclusive lock on a row and
- then determines whether this row belongs to the result set,
- and if not then releases the unnecessary lock, when the
+ In this case, the second <literal>UPDATE</literal> must wait
+ for a commit or rollback of the first
+ <literal>UPDATE</literal>. The first <literal>UPDATE</literal>
+ has an exclusive lock on row (2,3), and the second
+ <literal>UPDATE</literal> while scanning rows also tries to
+ acquire an exclusive lock for the same row, which it cannot
+ have. This is because <literal>UPDATE</literal> two first
+ acquires an exclusive lock on a row and then determines
+ whether the row belongs to the result set. If not, it releases
+ the unnecessary lock, when the
<literal>innodb_locks_unsafe_for_binlog</literal> variable is
enabled.
</para>
<para>
- Therefore, query one is executed as follows:
+ Therefore, <literal>InnoDB</literal> executes
+ <literal>UPDATE</literal> one as follows:
</para>
<programlisting>
@@ -1423,7 +1429,8 @@
</programlisting>
<para>
- and then query two is executed as follows:
+ <literal>InnoDB</literal> executes <literal>UPDATE</literal>
+ two as follows:
</para>
<programlisting>
@@ -1743,26 +1750,27 @@
that the directories you have specified for
<literal>InnoDB</literal> data files and log files exist and that
the MySQL server has access rights to those directories.
- <literal>InnoDB</literal> cannot create directories, only files.
+ <literal>InnoDB</literal> does create directories, only files.
Check also that you have enough disk space for the data and log
files.
</para>
<para>
It is best to run the MySQL server <command>mysqld</command> from
- the command prompt when you create an <literal>InnoDB</literal>
- database, not from the <command>mysqld_safe</command> wrapper or
- as a Windows service. When you run from a command prompt you see
- what <command>mysqld</command> prints and what is happening. On
- Unix, just invoke <command>mysqld</command>. On Windows, use the
+ the command prompt when you first start the server with
+ <literal>InnoDB</literal> enabled, not from the
+ <command>mysqld_safe</command> wrapper or as a Windows service.
+ When you run from a command prompt you see what
+ <command>mysqld</command> prints and what is happening. On Unix,
+ just invoke <command>mysqld</command>. On Windows, use the
<option>--console</option> option.
</para>
<para>
When you start the MySQL server after initially configuring
<literal>InnoDB</literal> in your option file,
- <literal>InnoDB</literal> creates your data files and log files.
- <literal>InnoDB</literal> prints something like the following:
+ <literal>InnoDB</literal> creates your data files and log files,
+ and prints something like this:
</para>
<programlisting>
@@ -1792,11 +1800,11 @@
</programlisting>
<para>
- A new <literal>InnoDB</literal> database has been created. You can
- connect to the MySQL server with the usual MySQL client programs
- like <command>mysql</command>. When you shut down the MySQL server
- with <command>mysqladmin shutdown</command>, the output is like
- the following:
+ At this point <literal>InnoDB</literal> has initialized its
+ tablespace and log files. You can connect to the MySQL server with
+ the usual MySQL client programs like <command>mysql</command>.
+ When you shut down the MySQL server with <command>mysqladmin
+ shutdown</command>, the output is like this:
</para>
<programlisting>
@@ -1808,12 +1816,12 @@
<para>
You can look at the data file and log directories and you see the
- files created. The log directory also contains a small file named
- <filename>ib_arch_log_0000000000</filename>. That file resulted
- from the database creation, after which <literal>InnoDB</literal>
- switched off log archiving. When MySQL is started again, the data
- files and log files have been created, so the output is much
- briefer:
+ files created there. The log directory also contains a small file
+ named <filename>ib_arch_log_0000000000</filename>. That file
+ resulted from the database creation, after which
+ <literal>InnoDB</literal> switched off log archiving. When MySQL
+ is started again, the data files and log files have been created
+ already, so the output is much briefer:
</para>
<programlisting>
@@ -1822,11 +1830,11 @@
</programlisting>
<para>
- You can add the option <literal>innodb_file_per_table</literal> to
- <filename>my.cnf</filename>, and make <literal>InnoDB</literal> to
- store each table into its own <filename>.ibd</filename> file in a
- database directory of MySQL. See
- <xref linkend="multiple-tablespaces"/>.
+ If you add the <literal>innodb_file_per_table</literal> option to
+ <filename>my.cnf</filename>, <literal>InnoDB</literal> stores each
+ table in its own <filename>.ibd</filename> file in the same MySQL
+ database directory where the <filename>.frm</filename> file is
+ created. See <xref linkend="multiple-tablespaces"/>.
</para>
<section id="error-creating-innodb">
@@ -1834,8 +1842,9 @@
<title>&title-error-creating-innodb;</title>
<para>
- If <literal>InnoDB</literal> prints an operating system error in
- a file operation, usually the problem is one of the following:
+ If <literal>InnoDB</literal> prints an operating system error
+ during a file operation, usually the problem has one of the
+ following causes:
</para>
<itemizedlist>
@@ -1856,10 +1865,10 @@
<listitem>
<para>
- <command>mysqld</command> cannot not read the proper
+ <command>mysqld</command> cannot read the proper
<filename>my.cnf</filename> or <filename>my.ini</filename>
- option file, and consequently does not see the options you
- specified.
+ option file, and consequently does not see the options that
+ you specified.
</para>
</listitem>
@@ -1872,15 +1881,16 @@
<listitem>
<para>
You have created a subdirectory whose name is equal to a
- data file you specified.
+ data file that you specified, so the name cannot be used as
+ a filename.
</para>
</listitem>
<listitem>
<para>
- There is a syntax error in
+ There is a syntax error in the
<literal>innodb_data_home_dir</literal> or
- <literal>innodb_data_file_path</literal>.
+ <literal>innodb_data_file_path</literal> value.
</para>
</listitem>
@@ -1891,14 +1901,14 @@
to initialize its tablespace or its log files, you should delete
all files created by <literal>InnoDB</literal>. This means all
<filename>ibdata</filename> files and all
- <filename>ib_logfile</filename>s. In case you created some
- <literal>InnoDB</literal> tables, delete the corresponding
- <filename>.frm</filename> files for these tables (and any
- <filename>.ibd</filename> files if you are using multiple
- tablespaces) from the MySQL database directories as well. Then
- you can try the <literal>InnoDB</literal> database creation
- again. It is best to start the MySQL server from a command
- prompt so that you see what is happening.
+ <filename>ib_logfile</filename> files. In case you have already
+ created some <literal>InnoDB</literal> tables, delete the
+ corresponding <filename>.frm</filename> files for these tables
+ (and any <filename>.ibd</filename> files if you are using
+ multiple tablespaces) from the MySQL database directories as
+ well. Then you can try the <literal>InnoDB</literal> database
+ creation again. It is best to start the MySQL server from a
+ command prompt so that you see what is happening.
</para>
</section>
@@ -1910,11 +1920,10 @@
<title>&title-using-innodb-tables;</title>
<para>
- Suppose that you have started the MySQL client with the command
- <literal>mysql test</literal>. To create an
- <literal>InnoDB</literal> table, you must specify an
- <literal>ENGINE = InnoDB</literal> or <literal>TYPE =
- InnoDB</literal> option in the table creation SQL statement:
+ To create an <literal>InnoDB</literal> table, you must specify an
+ <literal>ENGINE = InnoDB</literal> option (or <literal>TYPE =
+ InnoDB</literal>, but this is deprecated) in the <literal>CREATE
+ TABLE</literal> statement:
</para>
<programlisting>
@@ -1923,18 +1932,20 @@
</programlisting>
<para>
- The SQL statement creates a table and an index on column
+ The statement creates a table and an index on column
<literal>a</literal> in the <literal>InnoDB</literal> tablespace
that consists of the data files that you specified in
<filename>my.cnf</filename>. In addition, MySQL creates a file
<filename>customers.frm</filename> in the
<filename>test</filename> directory under the MySQL database
- directory. Internally, <literal>InnoDB</literal> adds to its own
- data dictionary an entry for table
- <literal>'test/customers'</literal>. This means you can create a
- table of the same name <literal>customers</literal> in some other
- database, and the table names do not collide inside
- <literal>InnoDB</literal>.
+ directory. Internally, <literal>InnoDB</literal> adds an entry for
+ the table to its own data dictionary. The entry includes the
+ database name. For example, if <literal>test</literal> is the
+ database in which the <literal>customers</literal> table is
+ created, the entry is for <literal>'test/customers'</literal>.
+ This means you can create a table of the same name
+ <literal>customers</literal> in some other database, and the table
+ names do not collide inside <literal>InnoDB</literal>.
</para>
<para>
@@ -1951,7 +1962,7 @@
</programlisting>
<para>
- Note that the statistics <literal>SHOW</literal> gives about
+ Note that the statistics <literal>SHOW</literal> displays for
<literal>InnoDB</literal> tables are only approximate. They are
used in SQL optimization. Table and index reserved sizes in bytes
are accurate, though.
@@ -2053,7 +2064,7 @@
<para>
If you have <literal>UNIQUE</literal> constraints on secondary
keys, you can speed up a table import by turning off the
- uniqueness checks temporarily during the import session:
+ uniqueness checks temporarily during the import operation:
</para>
<programlisting>
@@ -2087,19 +2098,19 @@
size of the <literal>InnoDB</literal> buffer pool to reduce disk
I/O. Do not use more than 80% of the physical memory, though.
You can also increase the sizes of the <literal>InnoDB</literal>
- log files and the log files.
+ log files.
</para>
<para>
Make sure that you do not fill up the tablespace:
<literal>InnoDB</literal> tables require a lot more disk space
than <literal>MyISAM</literal> tables. If an <literal>ALTER
- TABLE</literal> runs out of space, it starts a rollback, and
- that can take hours if it is disk-bound. For inserts,
- <literal>InnoDB</literal> uses the insert buffer to merge
- secondary index records to indexes in batches. That saves a lot
- of disk I/O. In rollback, no such mechanism is used, and the
- rollback can take 30 times longer than the insertion.
+ TABLE</literal> operation runs out of space, it starts a
+ rollback, and that can take hours if it is disk-bound. For
+ inserts, <literal>InnoDB</literal> uses the insert buffer to
+ merge secondary index records to indexes in batches. That saves
+ a lot of disk I/O. For rollback, no such mechanism is used, and
+ the rollback can take 30 times longer than the insertion.
</para>
<para>
@@ -2117,12 +2128,12 @@
<title>&title-innodb-auto-increment-column;</title>
<para>
- If you specify an <literal>AUTO_INCREMENT</literal> column for a
- table, the <literal>InnoDB</literal> table handle in the data
- dictionary contains a special counter called the auto-increment
- counter that is used in assigning new values for the column. The
- auto-increment counter is stored only in main memory, not on
- disk.
+ If you specify an <literal>AUTO_INCREMENT</literal> column for
+ an <literal>InnoDB</literal> table, the table handle in the
+ <literal>InnoDB</literal> data dictionary contains a special
+ counter called the auto-increment counter that is used in
+ assigning new values for the column. This counter is stored only
+ in main memory, not on disk.
</para>
<para>
@@ -2130,8 +2141,8 @@
initialize the auto-increment counter for a table
<literal>T</literal> that contains an
<literal>AUTO_INCREMENT</literal> column named
- <literal>ai_col</literal>: After a server startup, when a user
- first does an insert to a table <literal>T</literal>,
+ <literal>ai_col</literal>: After a server startup, for the first
+ insert into a table <literal>T</literal>,
<literal>InnoDB</literal> executes the equivalent of this
statement:
</para>
@@ -2141,16 +2152,17 @@
</programlisting>
<para>
- The value retrieved by the statement is incremented by one and
- assigned to the column and the auto-increment counter of the
- table. If the table is empty, the value <literal>1</literal> is
- assigned. If the auto-increment counter is not initialized and
- the user invokes a <literal>SHOW TABLE STATUS</literal>
+ <literal>InnoDB</literal> increments by one the value retrieved
+ by the statement and assigns it to the column and to the
+ auto-increment counter for the table. If the table is empty,
+ <literal>InnoDB</literal> uses the value <literal>1</literal>.
+ If a user invokes a <literal>SHOW TABLE STATUS</literal>
statement that displays output for the table
- <literal>T</literal>, the counter is initialized (but not
- incremented) and stored for use by later inserts. Note that in
- this initialization we do a normal exclusive-locking read on the
- table and the lock lasts to the end of the transaction.
+ <literal>T</literal> and the auto-increment counter has not been
+ initialized, <literal>InnoDB</literal> initializes but does not
+ increment the value and stores it for use by later inserts. Note
+ that this initialization uses a normal exclusive-locking read on
+ the table and the lock lasts to the end of the transaction.
</para>
<para>
@@ -2160,7 +2172,24 @@
</para>
<para>
- Note that if the user specifies <literal>NULL</literal> or
+ After the auto-increment counter has been initialized, if a user
+ does not explicitly specify a value for an
+ <literal>AUTO_INCREMENT</literal> column,
+ <literal>InnoDB</literal> increments the counter by one and
+ assigns the new value to the column. If the user inserts a row
+ that explicitly specifies the column value, and the value is
+ bigger than the current counter value, the counter is set to the
+ specified column value.
+ </para>
+
+ <para>
+ You may see gaps in the sequence of values assigned to the
+ <literal>AUTO_INCREMENT</literal> column if you roll back
+ transactions that have generated numbers using the counter.
+ </para>
+
+ <para>
+ If a user specifies <literal>NULL</literal> or
<literal>0</literal> for the <literal>AUTO_INCREMENT</literal>
column in an <literal>INSERT</literal>,
<literal>InnoDB</literal> treats the row as if the value had not
@@ -2168,17 +2197,15 @@
</para>
<para>
- After the auto-increment counter has been initialized, if a user
- inserts a row that explicitly specifies the column value, and
- the value is bigger than the current counter value, the counter
- is set to the specified column value. If the user does not
- explicitly specify a value, <literal>InnoDB</literal> increments
- the counter by one and assigns the new value to the column.
+ The behavior of the auto-increment mechanism is not defined if a
+ user assigns a negative value to the column or if the value
+ becomes bigger than the maximum integer that can be stored in
+ the specified integer type.
</para>
<para>
When accessing the auto-increment counter,
- <literal>InnoDB</literal> uses a special table level
+ <literal>InnoDB</literal> uses a special table-level
<literal>AUTO-INC</literal> lock that it keeps to the end of the
current SQL statement, not to the end of the transaction. The
special lock release strategy was introduced to improve
@@ -2189,19 +2216,14 @@
</para>
<para>
- Note that you may see gaps in the sequence of values assigned to
- the <literal>AUTO_INCREMENT</literal> column if you roll back
- transactions that have gotten numbers from the counter.
+ <literal>InnoDB</literal> uses the in-memory auto-increment
+ counter as long as he server runs. When the server is stopped
+ and restarted, <literal>InnoDB</literal> reinitializes the
+ counter for each table for the first <literal>INSERT</literal>
+ to the table, as described earlier.
</para>
<para>
- The behavior of the auto-increment mechanism is not defined if a
- user assigns a negative value to the column or if the value
- becomes bigger than the maximum integer that can be stored in
- the specified integer type.
- </para>
-
- <para>
Beginning with MySQL 5.0.3, <literal>InnoDB</literal> supports
the <literal>AUTO_INCREMENT =
<replaceable>n</replaceable></literal> table option in
@@ -2253,8 +2275,8 @@
<listitem>
<para>
- Both tables must be <literal>InnoDB</literal> type and they
- must not be temporary tables.
+ Both tables must be <literal>InnoDB</literal> tables and
+ they must not be <literal>TEMPORARY</literal> tables.
</para>
</listitem>
@@ -2288,11 +2310,11 @@
<listitem>
<para>
- If the
- <literal>CONSTRAINT<replaceable>symbol</replaceable></literal>
- is given, it must be unique in the database. If it is not
- given, <literal>InnoDB</literal> creates the name
- automatically.
+ If the <literal>CONSTRAINT
+ <replaceable>symbol</replaceable></literal> clause is given,
+ the <replaceable>symbol</replaceable> value must be unique
+ in the database. If the clause is not given,
+ <literal>InnoDB</literal> creates the name automatically.
</para>
</listitem>
@@ -2301,15 +2323,15 @@
<para>
<literal>InnoDB</literal> rejects any <literal>INSERT</literal>
or <literal>UPDATE</literal> operation that attempts to create a
- foreign key value in a child table without a matching candidate
- key value in the parent table. The action
+ foreign key value in a child table if there is no a matching
+ candidate key value in the parent table. The action
<literal>InnoDB</literal> takes for any
<literal>UPDATE</literal> or <literal>DELETE</literal> operation
that attempts to update or delete a candidate key value in the
parent table that has some matching rows in the child table is
dependent on the <emphasis>referential action</emphasis>
specified using <literal>ON UPDATE</literal> and <literal>ON
- DETETE</literal> subclauses of the <literal>FOREIGN
+ DELETE</literal> subclauses of the <literal>FOREIGN
KEY</literal> clause. When the user attempts to delete or update
a row from a parent table, and there are one or more matching
rows in the child table, <literal>InnoDB</literal> supports five
@@ -2328,7 +2350,7 @@
the parent table and automatically delete or update the
matching rows in the child table. Both <literal>ON DELETE
CASCADE</literal> and <literal>ON UPDATE CASCADE</literal>
- are available. Between two tables, you should not define
+ are supported. Between two tables, you should not define
several <literal>ON UPDATE CASCADE</literal> clauses that
act on the same column in the parent table or in the child
table.
@@ -2339,11 +2361,11 @@
<para>
<literal>SET NULL</literal>: Delete or update the row from
the parent table and set the foreign key column or columns
- in the child table to <literal>NULL</literal>. This is only
- valid if the foreign key columns do not have the
- <literal>NOT NULL</literal> qualifier specified. Both
- <literal>ON DELETE SET NULL</literal> and <literal>ON UPDATE
- SET NULL</literal> clauses are supported.
+ in the child table to <literal>NULL</literal>. This is valid
+ only if the foreign key columns do not have the <literal>NOT
+ NULL</literal> qualifier specified. Both <literal>ON DELETE
+ SET NULL</literal> and <literal>ON UPDATE SET NULL</literal>
+ clauses are supported.
</para>
</listitem>
@@ -2386,23 +2408,14 @@
</itemizedlist>
<para>
- <literal>InnoDB</literal> supports the same options when the
- candidate key in the parent table is updated. With
- <literal>CASCADE</literal>, the foreign key column or columns in
- the child table are set to the new values of the candidate key
- in the parent table. In the same way, the updates cascade if
- updated columns in the child table reference foreign keys in
- another table.
- </para>
-
- <para>
Note that <literal>InnoDB</literal> supports foreign key
- references within a table and in these cases child table really
- means dependent records within the table.
+ references within a table. In these cases, <quote>child table
+ records</quote> really refers to dependent records within the
+ same table.
</para>
<para>
- <literal>InnoDB</literal> needs indexes on foreign keys and
+ <literal>InnoDB</literal> requires indexes on foreign keys and
referenced keys so that foreign key checks can be fast and not
require a table scan. The index on the foreign key is created
automatically. This is in contrast to some older versions, in
@@ -2425,11 +2438,11 @@
<para>
If MySQL reports an error number 1005 from a <literal>CREATE
TABLE</literal> statement, and the error message refers to errno
- 150, this means that the table creation failed because a foreign
- key constraint was not correctly formed. Similarly, if an
- <literal>ALTER TABLE</literal> fails and it refers to errno 150,
- that means a foreign key definition would be incorrectly formed
- for the altered table. You can use <literal>SHOW ENGINE INNODB
+ 150, table creation failed because a foreign key constraint was
+ not correctly formed. Similarly, if an <literal>ALTER
+ TABLE</literal> fails and it refers to errno 150, that means a
+ foreign key definition would be incorrectly formed for the
+ altered table. You can use <literal>SHOW ENGINE INNODB
STATUS</literal> to display a detailed explanation of the most
recent <literal>InnoDB</literal> foreign key error in the
server.
@@ -2443,11 +2456,16 @@
</para>
<para>
+ <emphasis role="bold">Note</emphasis>: Currently, triggers are
+ not activated by cascaded foreign key actions.
+ </para>
+
+ <para>
<emphasis role="bold">Deviation from SQL standards</emphasis>:
- If in the parent table there are several rows that have the same
- referenced key value, then <literal>InnoDB</literal> acts in
- foreign key checks as if the other parent rows with the same key
- value do not exist. For example, if you have defined a
+ If there are several rows in the parent table that have the same
+ referenced key value, <literal>InnoDB</literal> acts in foreign
+ key checks as if the other parent rows with the same key value
+ do not exist. For example, if you have defined a
<literal>RESTRICT</literal> type constraint, and there is a
child row with several parent rows, <literal>InnoDB</literal>
does not allow the deletion of any of those parent rows.
@@ -2496,21 +2514,16 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: Currently, triggers are
- not activated by cascaded foreign key actions.
+ Here is a simple example that relates <literal>parent</literal>
+ and <literal>child</literal> tables through a single-column
+ foreign key:
</para>
- <para>
- A simple example that relates <literal>parent</literal> and
- <literal>child</literal> tables through a single-column foreign
- key:
- </para>
-
<programlisting>
-CREATE TABLE parent(id INT NOT NULL,
+CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
-CREATE TABLE child(id INT, parent_id INT,
+CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
@@ -2553,7 +2566,7 @@
</para>
<programlisting>
-ALTER TABLE yourtablename
+ALTER TABLE <replaceable>yourtablename</replaceable>
ADD [CONSTRAINT <replaceable>symbol</replaceable>] FOREIGN KEY [<replaceable>id</replaceable>] (<replaceable>index_col_name</replaceable>, ...)
REFERENCES <replaceable>tbl_name</replaceable> (<replaceable>index_col_name</replaceable>, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
@@ -2593,11 +2606,11 @@
If the <literal>FOREIGN KEY</literal> clause included a
<literal>CONSTRAINT</literal> name when you created the foreign
key, you can refer to that name to drop the foreign key.
- Otherwise, the <literal>fk_symbol</literal> value is internally
- generated by <literal>InnoDB</literal> when the foreign key is
- created. To find out the symbol when you want to drop a foreign
- key, use the <literal>SHOW CREATE TABLE</literal> statement. For
- example:
+ Otherwise, the <replaceable>fk_symbol</replaceable> value is
+ internally generated by <literal>InnoDB</literal> when the
+ foreign key is created. To find out the symbol value when you
+ want to drop a foreign key, use the <literal>SHOW CREATE
+ TABLE</literal> statement. For example:
</para>
<programlisting>
@@ -2621,45 +2634,26 @@
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)
-mysql> <userinput>ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;</userinput>
+mysql> <userinput>ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;</userinput>
</programlisting>
<para>
You cannot add a foreign key and drop a foreign key in separate
clauses of a single <literal>ALTER TABLE</literal> statement.
- You must use separate statements.
+ Separate statements are required.
</para>
<para>
- The <literal>InnoDB</literal> parser allows you to use backticks
- around table and column names in a <literal>FOREIGN KEY ...
- REFERENCES ...</literal> clause. The <literal>InnoDB</literal>
- parser also takes into account the setting of the
- <literal>lower_case_table_names</literal> system variable.
+ The <literal>InnoDB</literal> parser allows table and column
+ identifiers in a <literal>FOREIGN KEY … REFERENCES
+ …</literal> clause to be quoted within backticks.
+ (Alternatively, double quotes can be used if the
+ <literal>ANSI_QUOTES</literal> SQL mode is enabled.) The
+ <literal>InnoDB</literal> parser also takes into account the
+ setting of the <literal>lower_case_table_names</literal> system
+ variable.
</para>
- <remark>
- Does this still apply in 5.0?
- </remark>
-
-<!--
- <para>
- Before MySQL 3.23.50, <literal>ALTER TABLE</literal> or
- <literal>CREATE INDEX</literal> should not be used in connection
- with tables that have foreign key constraints or that are referenced
- in foreign key constraints: Any <literal>ALTER TABLE</literal>
- removes all foreign key constraints defined for the table. You
- should not use <literal>ALTER TABLE</literal> with the referenced
- table, either. Instead, use <literal>DROP TABLE</literal> and
- <literal>CREATE TABLE</literal> to modify the schema. When MySQL
- does an <literal>ALTER TABLE</literal> it may internally use
- <literal>RENAME TABLE</literal>, and that confuses the foreign key
- constraints that refer to the table. In MySQL, a <literal>CREATE
- INDEX</literal> statement is processed as an <literal>ALTER
- TABLE</literal>, so the same considerations apply.
- </para>
--->
-
<para>
<literal>InnoDB</literal> returns a table's foreign key
definitions as part of the output of the <literal>SHOW CREATE
@@ -2671,14 +2665,14 @@
</programlisting>
<para>
- From this version, <command>mysqldump</command> also produces
- correct definitions of tables to the dump file, and does not
- forget about the foreign keys.
+ <command>mysqldump</command> also produces correct definitions
+ of tables to the dump file, and does not forget about the
+ foreign keys.
</para>
<para>
- You can display the foreign key constraints for a table like
- this:
+ You can also display the foreign key constraints for a table
+ like this:
</para>
<programlisting>
Modified: trunk/refman-5.1/innodb.xml
===================================================================
--- trunk/refman-5.1/innodb.xml 2006-01-10 18:03:14 UTC (rev 757)
+++ trunk/refman-5.1/innodb.xml 2006-01-10 20:16:47 UTC (rev 758)
@@ -1290,7 +1290,7 @@
For the greatest possible durability and consistency in a
replication setup you should use
<literal>innodb_flush_log_at_trx_commit=1</literal> and
- <literal>sync-binlog=1</literal> in your master
+ <literal>sync-binlog=1</literal> in your master server
<filename>my.cnf</filename> file.
</para>
</listitem>
@@ -1301,9 +1301,10 @@
</para>
<para>
- This option turns off next-key locking in
- <literal>InnoDB</literal> searches and index scans. Default
- value for this option is false.
+ This variable controls next-key locking in
+ <literal>InnoDB</literal> searches and index scans. By
+ default, this variable is 0 (disabled), which means that
+ next-key locking is enabled.
</para>
<para>
@@ -1318,11 +1319,11 @@
record. If a user has a shared or exclusive lock on record
<emphasis>R</emphasis> in an index, another user cannot insert
a new index record immediately before <emphasis>R</emphasis>
- in the order of the index. This option causes
+ in the order of the index. Enabling this variable causes
<literal>InnoDB</literal> not to use next-key locking in
searches or index scans. Next-key locking is still used to
ensure foreign key constraints and duplicate key checking.
- Note that using this option may cause phantom problems:
+ Note that enabling this variable may cause phantom problems:
Suppose that you want to read and lock all children from the
<literal>child</literal> table with an identifier value larger
than 100, with the intention of updating some column in the
@@ -1338,36 +1339,38 @@
column. The query scans that index starting from the first
record where <literal>id</literal> is greater than 100. If the
locks set on the index records do not lock out inserts made in
- the gaps, a new row is meanwhile inserted into the table. If
- you execute the same <literal>SELECT</literal> within the same
- transaction, you see a new row in the result set returned by
- the query. This also means, that if new items are added to the
- database, <literal>InnoDB</literal> does not guarantee
- serializability; however, conflict serializability is still
- guaranteed. Therefore, if this option is used
+ the gaps, another client can insert a new row into the table.
+ If you execute the same <literal>SELECT</literal> within the
+ same transaction, you see a new row in the result set returned
+ by the query. This also means that if new items are added to
+ the database, <literal>InnoDB</literal> does not guarantee
+ serializability. Therefore, if this variable is enabled,
<literal>InnoDB</literal> guarantees at most isolation level
- <literal>READ COMMITTED</literal>.
+ <literal>READ COMMITTED</literal>. (Conflict serializability
+ is still guaranteed.)
</para>
<para>
- This option is even more unsafe. <literal>InnoDB</literal> in
- an <literal>UPDATE</literal> or a <literal>DELETE</literal>
- only locks rows that it updates or deletes. This greatly
- reduces the probability of deadlocks but they can happen. Note
- that this option still does not allow operations such as
- <literal>UPDATE</literal> to overtake like operations (such as
- another <literal>UPDATE</literal>) even in the case when they
- affect different rows. Consider the following example:
+ Enabling this variable has an additional effect:
+ <literal>InnoDB</literal> in an <literal>UPDATE</literal> or a
+ <literal>DELETE</literal> only locks rows that it updates or
+ deletes. This greatly reduces the probability of deadlocks,
+ but they can happen. Note that enabling this variable still
+ does not allow operations such as <literal>UPDATE</literal> to
+ overtake other similar operations (such as another
+ <literal>UPDATE</literal>) even in the case when they affect
+ different rows. Consider the following example, beginning with
+ this table:
</para>
<programlisting>
-CREATE TABLE A(A INT NOT NULL, B INT);
+CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB;
INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
</programlisting>
<para>
- If one connection executes a query:
+ Suppose that one client executes these statements:
</para>
<programlisting>
@@ -1376,8 +1379,8 @@
</programlisting>
<para>
- and the other connection executes, following the first one,
- another query:
+ Then suppose that another client executes these statements
+ following those of the first client:
</para>
<programlisting>
@@ -1386,19 +1389,23 @@
</programlisting>
<para>
- Then query two has to wait for a commit or rollback of query
- one, because query one has an exclusive lock to row (2,3), and
- query two while scanning rows also tries to take an exclusive
- lock to the same row (2,3), which it cannot have. This is
- because query two first takes an exclusive lock on a row and
- then determines whether this row belongs to the result set,
- and if not then releases the unnecessary lock, when the
+ In this case, the second <literal>UPDATE</literal> must wait
+ for a commit or rollback of the first
+ <literal>UPDATE</literal>. The first <literal>UPDATE</literal>
+ has an exclusive lock on row (2,3), and the second
+ <literal>UPDATE</literal> while scanning rows also tries to
+ acquire an exclusive lock for the same row, which it cannot
+ have. This is because <literal>UPDATE</literal> two first
+ acquires an exclusive lock on a row and then determines
+ whether the row belongs to the result set. If not, it releases
+ the unnecessary lock, when the
<literal>innodb_locks_unsafe_for_binlog</literal> variable is
enabled.
</para>
<para>
- Therefore, query one is executed as follows:
+ Therefore, <literal>InnoDB</literal> executes
+ <literal>UPDATE</literal> one as follows:
</para>
<programlisting>
@@ -1415,7 +1422,8 @@
</programlisting>
<para>
- and then query two is executed as follows:
+ <literal>InnoDB</literal> executes <literal>UPDATE</literal>
+ two as follows:
</para>
<programlisting>
@@ -1718,26 +1726,27 @@
that the directories you have specified for
<literal>InnoDB</literal> data files and log files exist and that
the MySQL server has access rights to those directories.
- <literal>InnoDB</literal> cannot create directories, only files.
+ <literal>InnoDB</literal> does create directories, only files.
Check also that you have enough disk space for the data and log
files.
</para>
<para>
It is best to run the MySQL server <command>mysqld</command> from
- the command prompt when you create an <literal>InnoDB</literal>
- database, not from the <command>mysqld_safe</command> wrapper or
- as a Windows service. When you run from a command prompt you see
- what <command>mysqld</command> prints and what is happening. On
- Unix, just invoke <command>mysqld</command>. On Windows, use the
+ the command prompt when you first start the server with
+ <literal>InnoDB</literal> enabled, not from the
+ <command>mysqld_safe</command> wrapper or as a Windows service.
+ When you run from a command prompt you see what
+ <command>mysqld</command> prints and what is happening. On Unix,
+ just invoke <command>mysqld</command>. On Windows, use the
<option>--console</option> option.
</para>
<para>
When you start the MySQL server after initially configuring
<literal>InnoDB</literal> in your option file,
- <literal>InnoDB</literal> creates your data files and log files.
- <literal>InnoDB</literal> prints something like the following:
+ <literal>InnoDB</literal> creates your data files and log files,
+ and prints something like this:
</para>
<programlisting>
@@ -1767,11 +1776,11 @@
</programlisting>
<para>
- A new <literal>InnoDB</literal> database has been created. You can
- connect to the MySQL server with the usual MySQL client programs
- like <command>mysql</command>. When you shut down the MySQL server
- with <command>mysqladmin shutdown</command>, the output is like
- the following:
+ At this point <literal>InnoDB</literal> has initialized its
+ tablespace and log files. You can connect to the MySQL server with
+ the usual MySQL client programs like <command>mysql</command>.
+ When you shut down the MySQL server with <command>mysqladmin
+ shutdown</command>, the output is like this:
</para>
<programlisting>
@@ -1783,12 +1792,12 @@
<para>
You can look at the data file and log directories and you see the
- files created. The log directory also contains a small file named
- <filename>ib_arch_log_0000000000</filename>. That file resulted
- from the database creation, after which <literal>InnoDB</literal>
- switched off log archiving. When MySQL is started again, the data
- files and log files have been created, so the output is much
- briefer:
+ files created there. The log directory also contains a small file
+ named <filename>ib_arch_log_0000000000</filename>. That file
+ resulted from the database creation, after which
+ <literal>InnoDB</literal> switched off log archiving. When MySQL
+ is started again, the data files and log files have been created
+ already, so the output is much briefer:
</para>
<programlisting>
@@ -1797,11 +1806,11 @@
</programlisting>
<para>
- You can add the option <literal>innodb_file_per_table</literal> to
- <filename>my.cnf</filename>, and make <literal>InnoDB</literal> to
- store each table into its own <filename>.ibd</filename> file in a
- database directory of MySQL. See
- <xref linkend="multiple-tablespaces"/>.
+ If you add the <literal>innodb_file_per_table</literal> option to
+ <filename>my.cnf</filename>, <literal>InnoDB</literal> stores each
+ table in its own <filename>.ibd</filename> file in the same MySQL
+ database directory where the <filename>.frm</filename> file is
+ created. See <xref linkend="multiple-tablespaces"/>.
</para>
<section id="error-creating-innodb">
@@ -1809,8 +1818,9 @@
<title>&title-error-creating-innodb;</title>
<para>
- If <literal>InnoDB</literal> prints an operating system error in
- a file operation, usually the problem is one of the following:
+ If <literal>InnoDB</literal> prints an operating system error
+ during a file operation, usually the problem has one of the
+ following causes:
</para>
<itemizedlist>
@@ -1831,10 +1841,10 @@
<listitem>
<para>
- <command>mysqld</command> cannot not read the proper
+ <command>mysqld</command> cannot read the proper
<filename>my.cnf</filename> or <filename>my.ini</filename>
- option file, and consequently does not see the options you
- specified.
+ option file, and consequently does not see the options that
+ you specified.
</para>
</listitem>
@@ -1847,15 +1857,16 @@
<listitem>
<para>
You have created a subdirectory whose name is equal to a
- data file you specified.
+ data file that you specified, so the name cannot be used as
+ a filename.
</para>
</listitem>
<listitem>
<para>
- There is a syntax error in
+ There is a syntax error in the
<literal>innodb_data_home_dir</literal> or
- <literal>innodb_data_file_path</literal>.
+ <literal>innodb_data_file_path</literal> value.
</para>
</listitem>
@@ -1866,14 +1877,14 @@
to initialize its tablespace or its log files, you should delete
all files created by <literal>InnoDB</literal>. This means all
<filename>ibdata</filename> files and all
- <filename>ib_logfile</filename>s. In case you created some
- <literal>InnoDB</literal> tables, delete the corresponding
- <filename>.frm</filename> files for these tables (and any
- <filename>.ibd</filename> files if you are using multiple
- tablespaces) from the MySQL database directories as well. Then
- you can try the <literal>InnoDB</literal> database creation
- again. It is best to start the MySQL server from a command
- prompt so that you see what is happening.
+ <filename>ib_logfile</filename> files. In case you have already
+ created some <literal>InnoDB</literal> tables, delete the
+ corresponding <filename>.frm</filename> files for these tables
+ (and any <filename>.ibd</filename> files if you are using
+ multiple tablespaces) from the MySQL database directories as
+ well. Then you can try the <literal>InnoDB</literal> database
+ creation again. It is best to start the MySQL server from a
+ command prompt so that you see what is happening.
</para>
</section>
@@ -1885,11 +1896,10 @@
<title>&title-using-innodb-tables;</title>
<para>
- Suppose that you have started the MySQL client with the command
- <literal>mysql test</literal>. To create an
- <literal>InnoDB</literal> table, you must specify an
- <literal>ENGINE = InnoDB</literal> or <literal>TYPE =
- InnoDB</literal> option in the table creation SQL statement:
+ To create an <literal>InnoDB</literal> table, you must specify an
+ <literal>ENGINE = InnoDB</literal> option (or <literal>TYPE =
+ InnoDB</literal>, but this is deprecated) in the <literal>CREATE
+ TABLE</literal> statement:
</para>
<programlisting>
@@ -1898,18 +1908,20 @@
</programlisting>
<para>
- The SQL statement creates a table and an index on column
+ The statement creates a table and an index on column
<literal>a</literal> in the <literal>InnoDB</literal> tablespace
that consists of the data files that you specified in
<filename>my.cnf</filename>. In addition, MySQL creates a file
<filename>customers.frm</filename> in the
<filename>test</filename> directory under the MySQL database
- directory. Internally, <literal>InnoDB</literal> adds to its own
- data dictionary an entry for table
- <literal>'test/customers'</literal>. This means you can create a
- table of the same name <literal>customers</literal> in some other
- database, and the table names do not collide inside
- <literal>InnoDB</literal>.
+ directory. Internally, <literal>InnoDB</literal> adds an entry for
+ the table to its own data dictionary. The entry includes the
+ database name. For example, if <literal>test</literal> is the
+ database in which the <literal>customers</literal> table is
+ created, the entry is for <literal>'test/customers'</literal>.
+ This means you can create a table of the same name
+ <literal>customers</literal> in some other database, and the table
+ names do not collide inside <literal>InnoDB</literal>.
</para>
<para>
@@ -1926,7 +1938,7 @@
</programlisting>
<para>
- Note that the statistics <literal>SHOW</literal> gives about
+ Note that the statistics <literal>SHOW</literal> displays for
<literal>InnoDB</literal> tables are only approximate. They are
used in SQL optimization. Table and index reserved sizes in bytes
are accurate, though.
@@ -2028,7 +2040,7 @@
<para>
If you have <literal>UNIQUE</literal> constraints on secondary
keys, you can speed up a table import by turning off the
- uniqueness checks temporarily during the import session:
+ uniqueness checks temporarily during the import operation:
</para>
<programlisting>
@@ -2062,19 +2074,19 @@
size of the <literal>InnoDB</literal> buffer pool to reduce disk
I/O. Do not use more than 80% of the physical memory, though.
You can also increase the sizes of the <literal>InnoDB</literal>
- log files and the log files.
+ log files.
</para>
<para>
Make sure that you do not fill up the tablespace:
<literal>InnoDB</literal> tables require a lot more disk space
than <literal>MyISAM</literal> tables. If an <literal>ALTER
- TABLE</literal> runs out of space, it starts a rollback, and
- that can take hours if it is disk-bound. For inserts,
- <literal>InnoDB</literal> uses the insert buffer to merge
- secondary index records to indexes in batches. That saves a lot
- of disk I/O. In rollback, no such mechanism is used, and the
- rollback can take 30 times longer than the insertion.
+ TABLE</literal> operation runs out of space, it starts a
+ rollback, and that can take hours if it is disk-bound. For
+ inserts, <literal>InnoDB</literal> uses the insert buffer to
+ merge secondary index records to indexes in batches. That saves
+ a lot of disk I/O. For rollback, no such mechanism is used, and
+ the rollback can take 30 times longer than the insertion.
</para>
<para>
@@ -2092,12 +2104,12 @@
<title>&title-innodb-auto-increment-column;</title>
<para>
- If you specify an <literal>AUTO_INCREMENT</literal> column for a
- table, the <literal>InnoDB</literal> table handle in the data
- dictionary contains a special counter called the auto-increment
- counter that is used in assigning new values for the column. The
- auto-increment counter is stored only in main memory, not on
- disk.
+ If you specify an <literal>AUTO_INCREMENT</literal> column for
+ an <literal>InnoDB</literal> table, the table handle in the
+ <literal>InnoDB</literal> data dictionary contains a special
+ counter called the auto-increment counter that is used in
+ assigning new values for the column. This counter is stored only
+ in main memory, not on disk.
</para>
<para>
@@ -2105,8 +2117,8 @@
initialize the auto-increment counter for a table
<literal>T</literal> that contains an
<literal>AUTO_INCREMENT</literal> column named
- <literal>ai_col</literal>: After a server startup, when a user
- first does an insert to a table <literal>T</literal>,
+ <literal>ai_col</literal>: After a server startup, for the first
+ insert into a table <literal>T</literal>,
<literal>InnoDB</literal> executes the equivalent of this
statement:
</para>
@@ -2116,16 +2128,17 @@
</programlisting>
<para>
- The value retrieved by the statement is incremented by one and
- assigned to the column and the auto-increment counter of the
- table. If the table is empty, the value <literal>1</literal> is
- assigned. If the auto-increment counter is not initialized and
- the user invokes a <literal>SHOW TABLE STATUS</literal>
+ <literal>InnoDB</literal> increments by one the value retrieved
+ by the statement and assigns it to the column and to the
+ auto-increment counter for the table. If the table is empty,
+ <literal>InnoDB</literal> uses the value <literal>1</literal>.
+ If a user invokes a <literal>SHOW TABLE STATUS</literal>
statement that displays output for the table
- <literal>T</literal>, the counter is initialized (but not
- incremented) and stored for use by later inserts. Note that in
- this initialization we do a normal exclusive-locking read on the
- table and the lock lasts to the end of the transaction.
+ <literal>T</literal> and the auto-increment counter has not been
+ initialized, <literal>InnoDB</literal> initializes but does not
+ increment the value and stores it for use by later inserts. Note
+ that this initialization uses a normal exclusive-locking read on
+ the table and the lock lasts to the end of the transaction.
</para>
<para>
@@ -2135,7 +2148,24 @@
</para>
<para>
- Note that if the user specifies <literal>NULL</literal> or
+ After the auto-increment counter has been initialized, if a user
+ does not explicitly specify a value for an
+ <literal>AUTO_INCREMENT</literal> column,
+ <literal>InnoDB</literal> increments the counter by one and
+ assigns the new value to the column. If the user inserts a row
+ that explicitly specifies the column value, and the value is
+ bigger than the current counter value, the counter is set to the
+ specified column value.
+ </para>
+
+ <para>
+ You may see gaps in the sequence of values assigned to the
+ <literal>AUTO_INCREMENT</literal> column if you roll back
+ transactions that have generated numbers using the counter.
+ </para>
+
+ <para>
+ If a user specifies <literal>NULL</literal> or
<literal>0</literal> for the <literal>AUTO_INCREMENT</literal>
column in an <literal>INSERT</literal>,
<literal>InnoDB</literal> treats the row as if the value had not
@@ -2143,17 +2173,15 @@
</para>
<para>
- After the auto-increment counter has been initialized, if a user
- inserts a row that explicitly specifies the column value, and
- the value is bigger than the current counter value, the counter
- is set to the specified column value. If the user does not
- explicitly specify a value, <literal>InnoDB</literal> increments
- the counter by one and assigns the new value to the column.
+ The behavior of the auto-increment mechanism is not defined if a
+ user assigns a negative value to the column or if the value
+ becomes bigger than the maximum integer that can be stored in
+ the specified integer type.
</para>
<para>
When accessing the auto-increment counter,
- <literal>InnoDB</literal> uses a special table level
+ <literal>InnoDB</literal> uses a special table-level
<literal>AUTO-INC</literal> lock that it keeps to the end of the
current SQL statement, not to the end of the transaction. The
special lock release strategy was introduced to improve
@@ -2164,19 +2192,14 @@
</para>
<para>
- Note that you may see gaps in the sequence of values assigned to
- the <literal>AUTO_INCREMENT</literal> column if you roll back
- transactions that have gotten numbers from the counter.
+ <literal>InnoDB</literal> uses the in-memory auto-increment
+ counter as long as he server runs. When the server is stopped
+ and restarted, <literal>InnoDB</literal> reinitializes the
+ counter for each table for the first <literal>INSERT</literal>
+ to the table, as described earlier.
</para>
<para>
- The behavior of the auto-increment mechanism is not defined if a
- user assigns a negative value to the column or if the value
- becomes bigger than the maximum integer that can be stored in
- the specified integer type.
- </para>
-
- <para>
<literal>InnoDB</literal> supports the <literal>AUTO_INCREMENT =
<replaceable>n</replaceable></literal> table option in
<literal>CREATE TABLE</literal> and <literal>ALTER
@@ -2227,8 +2250,8 @@
<listitem>
<para>
- Both tables must be <literal>InnoDB</literal> type and they
- must not be temporary tables.
+ Both tables must be <literal>InnoDB</literal> tables and
+ they must not be <literal>TEMPORARY</literal> tables.
</para>
</listitem>
@@ -2262,11 +2285,11 @@
<listitem>
<para>
- If the
- <literal>CONSTRAINT<replaceable>symbol</replaceable></literal>
- is given, it must be unique in the database. If it is not
- given, <literal>InnoDB</literal> creates the name
- automatically.
+ If the <literal>CONSTRAINT
+ <replaceable>symbol</replaceable></literal> clause is given,
+ the <replaceable>symbol</replaceable> value must be unique
+ in the database. If the clause is not given,
+ <literal>InnoDB</literal> creates the name automatically.
</para>
</listitem>
@@ -2275,15 +2298,15 @@
<para>
<literal>InnoDB</literal> rejects any <literal>INSERT</literal>
or <literal>UPDATE</literal> operation that attempts to create a
- foreign key value in a child table without a matching candidate
- key value in the parent table. The action
+ foreign key value in a child table if there is no a matching
+ candidate key value in the parent table. The action
<literal>InnoDB</literal> takes for any
<literal>UPDATE</literal> or <literal>DELETE</literal> operation
that attempts to update or delete a candidate key value in the
parent table that has some matching rows in the child table is
dependent on the <emphasis>referential action</emphasis>
specified using <literal>ON UPDATE</literal> and <literal>ON
- DETETE</literal> subclauses of the <literal>FOREIGN
+ DELETE</literal> subclauses of the <literal>FOREIGN
KEY</literal> clause. When the user attempts to delete or update
a row from a parent table, and there are one or more matching
rows in the child table, <literal>InnoDB</literal> supports five
@@ -2302,7 +2325,7 @@
the parent table and automatically delete or update the
matching rows in the child table. Both <literal>ON DELETE
CASCADE</literal> and <literal>ON UPDATE CASCADE</literal>
- are available. Between two tables, you should not define
+ are supported. Between two tables, you should not define
several <literal>ON UPDATE CASCADE</literal> clauses that
act on the same column in the parent table or in the child
table.
@@ -2313,11 +2336,11 @@
<para>
<literal>SET NULL</literal>: Delete or update the row from
the parent table and set the foreign key column or columns
- in the child table to <literal>NULL</literal>. This is only
- valid if the foreign key columns do not have the
- <literal>NOT NULL</literal> qualifier specified. Both
- <literal>ON DELETE SET NULL</literal> and <literal>ON UPDATE
- SET NULL</literal> clauses are supported.
+ in the child table to <literal>NULL</literal>. This is valid
+ only if the foreign key columns do not have the <literal>NOT
+ NULL</literal> qualifier specified. Both <literal>ON DELETE
+ SET NULL</literal> and <literal>ON UPDATE SET NULL</literal>
+ clauses are supported.
</para>
</listitem>
@@ -2360,23 +2383,14 @@
</itemizedlist>
<para>
- <literal>InnoDB</literal> supports the same options when the
- candidate key in the parent table is updated. With
- <literal>CASCADE</literal>, the foreign key column or columns in
- the child table are set to the new values of the candidate key
- in the parent table. In the same way, the updates cascade if
- updated columns in the child table reference foreign keys in
- another table.
- </para>
-
- <para>
Note that <literal>InnoDB</literal> supports foreign key
- references within a table and in these cases child table really
- means dependent records within the table.
+ references within a table. In these cases, <quote>child table
+ records</quote> really refers to dependent records within the
+ same table.
</para>
<para>
- <literal>InnoDB</literal> needs indexes on foreign keys and
+ <literal>InnoDB</literal> requires indexes on foreign keys and
referenced keys so that foreign key checks can be fast and not
require a table scan. The index on the foreign key is created
automatically. This is in contrast to some older versions, in
@@ -2399,11 +2413,11 @@
<para>
If MySQL reports an error number 1005 from a <literal>CREATE
TABLE</literal> statement, and the error message refers to errno
- 150, this means that the table creation failed because a foreign
- key constraint was not correctly formed. Similarly, if an
- <literal>ALTER TABLE</literal> fails and it refers to errno 150,
- that means a foreign key definition would be incorrectly formed
- for the altered table. You can use <literal>SHOW ENGINE INNODB
+ 150, table creation failed because a foreign key constraint was
+ not correctly formed. Similarly, if an <literal>ALTER
+ TABLE</literal> fails and it refers to errno 150, that means a
+ foreign key definition would be incorrectly formed for the
+ altered table. You can use <literal>SHOW ENGINE INNODB
STATUS</literal> to display a detailed explanation of the most
recent <literal>InnoDB</literal> foreign key error in the
server.
@@ -2417,11 +2431,16 @@
</para>
<para>
+ <emphasis role="bold">Note</emphasis>: Currently, triggers are
+ not activated by cascaded foreign key actions.
+ </para>
+
+ <para>
<emphasis role="bold">Deviation from SQL standards</emphasis>:
- If in the parent table there are several rows that have the same
- referenced key value, then <literal>InnoDB</literal> acts in
- foreign key checks as if the other parent rows with the same key
- value do not exist. For example, if you have defined a
+ If there are several rows in the parent table that have the same
+ referenced key value, <literal>InnoDB</literal> acts in foreign
+ key checks as if the other parent rows with the same key value
+ do not exist. For example, if you have defined a
<literal>RESTRICT</literal> type constraint, and there is a
child row with several parent rows, <literal>InnoDB</literal>
does not allow the deletion of any of those parent rows.
@@ -2470,21 +2489,16 @@
</para>
<para>
- <emphasis role="bold">Note</emphasis>: Currently, triggers are
- not activated by cascaded foreign key actions.
+ Here is a simple example that relates <literal>parent</literal>
+ and <literal>child</literal> tables through a single-column
+ foreign key:
</para>
- <para>
- A simple example that relates <literal>parent</literal> and
- <literal>child</literal> tables through a single-column foreign
- key:
- </para>
-
<programlisting>
-CREATE TABLE parent(id INT NOT NULL,
+CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
-CREATE TABLE child(id INT, parent_id INT,
+CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
@@ -2527,7 +2541,7 @@
</para>
<programlisting>
-ALTER TABLE yourtablename
+ALTER TABLE <replaceable>yourtablename</replaceable>
ADD [CONSTRAINT <replaceable>symbol</replaceable>] FOREIGN KEY [<replaceable>id</replaceable>] (<replaceable>index_col_name</replaceable>, ...)
REFERENCES <replaceable>tbl_name</replaceable> (<replaceable>index_col_name</replaceable>, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
@@ -2567,11 +2581,11 @@
If the <literal>FOREIGN KEY</literal> clause included a
<literal>CONSTRAINT</literal> name when you created the foreign
key, you can refer to that name to drop the foreign key.
- Otherwise, the <literal>fk_symbol</literal> value is internally
- generated by <literal>InnoDB</literal> when the foreign key is
- created. To find out the symbol when you want to drop a foreign
- key, use the <literal>SHOW CREATE TABLE</literal> statement. For
- example:
+ Otherwise, the <replaceable>fk_symbol</replaceable> value is
+ internally generated by <literal>InnoDB</literal> when the
+ foreign key is created. To find out the symbol value when you
+ want to drop a foreign key, use the <literal>SHOW CREATE
+ TABLE</literal> statement. For example:
</para>
<programlisting>
@@ -2595,45 +2609,26 @@
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)
-mysql> <userinput>ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;</userinput>
+mysql> <userinput>ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;</userinput>
</programlisting>
<para>
You cannot add a foreign key and drop a foreign key in separate
clauses of a single <literal>ALTER TABLE</literal> statement.
- You must use separate statements.
+ Separate statements are required.
</para>
<para>
- The <literal>InnoDB</literal> parser allows you to use backticks
- around table and column names in a <literal>FOREIGN KEY ...
- REFERENCES ...</literal> clause. The <literal>InnoDB</literal>
- parser also takes into account the setting of the
- <literal>lower_case_table_names</literal> system variable.
+ The <literal>InnoDB</literal> parser allows table and column
+ identifiers in a <literal>FOREIGN KEY … REFERENCES
+ …</literal> clause to be quoted within backticks.
+ (Alternatively, double quotes can be used if the
+ <literal>ANSI_QUOTES</literal> SQL mode is enabled.) The
+ <literal>InnoDB</literal> parser also takes into account the
+ setting of the <literal>lower_case_table_names</literal> system
+ variable.
</para>
- <remark role="todo">
- Does this still apply in 5.1?
- </remark>
-
-<!--
- <para>
- Before MySQL 3.23.50, <literal>ALTER TABLE</literal> or
- <literal>CREATE INDEX</literal> should not be used in connection
- with tables that have foreign key constraints or that are referenced
- in foreign key constraints: Any <literal>ALTER TABLE</literal>
- removes all foreign key constraints defined for the table. You
- should not use <literal>ALTER TABLE</literal> with the referenced
- table, either. Instead, use <literal>DROP TABLE</literal> and
- <literal>CREATE TABLE</literal> to modify the schema. When MySQL
- does an <literal>ALTER TABLE</literal> it may internally use
- <literal>RENAME TABLE</literal>, and that confuses the foreign key
- constraints that refer to the table. In MySQL, a <literal>CREATE
- INDEX</literal> statement is processed as an <literal>ALTER
- TABLE</literal>, so the same considerations apply.
- </para>
--->
-
<para>
<literal>InnoDB</literal> returns a table's foreign key
definitions as part of the output of the <literal>SHOW CREATE
@@ -2645,14 +2640,14 @@
</programlisting>
<para>
- From this version, <command>mysqldump</command> also produces
- correct definitions of tables to the dump file, and does not
- forget about the foreign keys.
+ <command>mysqldump</command> also produces correct definitions
+ of tables to the dump file, and does not forget about the
+ foreign keys.
</para>
<para>
- You can display the foreign key constraints for a table like
- this:
+ You can also display the foreign key constraints for a table
+ like this:
</para>
<programlisting>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r758 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 10 Jan |
