Author: paul
Date: 2006-01-11 05:43:25 +0100 (Wed, 11 Jan 2006)
New Revision: 759
Log:
r6069@frost: paul | 2006-01-10 22:42:30 -0600
General revisions.
Modified:
trunk/
trunk/refman-4.1/database-administration.xml
trunk/refman-4.1/innodb.xml
trunk/refman-5.0/database-administration.xml
trunk/refman-5.0/innodb.xml
trunk/refman-5.1/database-administration.xml
trunk/refman-5.1/innodb.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6067
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:1994
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6069
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:1994
Modified: trunk/refman-4.1/database-administration.xml
===================================================================
--- trunk/refman-4.1/database-administration.xml 2006-01-10 20:16:47 UTC (rev 758)
+++ trunk/refman-4.1/database-administration.xml 2006-01-11 04:43:25 UTC (rev 759)
@@ -16536,8 +16536,8 @@
</para>
<para>
- In cases of operating system crashes or power failures, we can
- assume the MySQL disk data is available after a restart. The
+ For cases of operating system crashes or power failures, we can
+ assume that MySQL's disk data is available after a restart. The
<literal>InnoDB</literal> data files might not contain
consistent data due to the crash, but <literal>InnoDB</literal>
reads its logs and finds in them the list of pending committed
Modified: trunk/refman-4.1/innodb.xml
===================================================================
--- trunk/refman-4.1/innodb.xml 2006-01-10 20:16:47 UTC (rev 758)
+++ trunk/refman-4.1/innodb.xml 2006-01-11 04:43:25 UTC (rev 759)
@@ -746,42 +746,41 @@
<para>
<literal>innodb_file_per_table</literal> affects only table
- creation. If you start the server with this option, new tables
- are created using <filename>.ibd</filename> files, but you can
- still access tables that exist in the shared tablespace. If you
- remove the option, new tables are created in the shared
+ creation, not access to existing tables. If you start the server
+ with this option, new tables are created using
+ <filename>.ibd</filename> files, but you can still access tables
+ that exist in the shared tablespace. If you remove the option
+ and restart the server, new tables are created in the shared
tablespace, but you can still access any tables that were
created using multiple tablespaces.
</para>
<para>
- <literal>InnoDB</literal> always needs the shared tablespace.
- The <filename>.ibd</filename> files are not sufficient for
- <literal>InnoDB</literal> to operate. The shared tablespace
- consists of the familiar <filename>ibdata</filename> files where
- <literal>InnoDB</literal> puts its internal data dictionary and
- undo logs.
+ <literal>InnoDB</literal> always needs the shared tablespace
+ because it puts its internal data dictionary and undo logs
+ there. The <filename>.ibd</filename> files are not sufficient
+ for <literal>InnoDB</literal> to operate.
</para>
<para>
<emphasis role="bold">Note</emphasis>: You cannot freely move
<filename>.ibd</filename> files between database directories as
you can with <literal>MyISAM</literal> table files. This is
- because the table definition is stored in the
- <literal>InnoDB</literal> shared tablespace, and because
- <literal>InnoDB</literal> must preserve the consistency of
- transaction IDs and log sequence numbers.
+ because the table definition that is stored in the
+ <literal>InnoDB</literal> shared tablespace includes the
+ database name, and because <literal>InnoDB</literal> must
+ preserve the consistency of transaction IDs and log sequence
+ numbers.
</para>
<para>
- Within a given MySQL installation, you can move an
- <filename>.ibd</filename> file and the associated table from one
- database to another with a <literal>RENAME TABLE</literal>
- statement:
+ To move an <filename>.ibd</filename> file and the associated
+ table from one database to another, use a <literal>RENAME
+ TABLE</literal> statement:
</para>
<programlisting>
-RENAME TABLE <replaceable>old_db_name.tbl_name</replaceable> TO <replaceable>new_db_name.tbl_name</replaceable>;
+RENAME TABLE <replaceable>db1.tbl_name</replaceable> TO <replaceable>db2.tbl_name</replaceable>;
</programlisting>
<indexterm type="function">
@@ -2009,10 +2008,10 @@
afterward. The fastest way to alter a table to
<literal>InnoDB</literal> is to do the inserts directly to an
<literal>InnoDB</literal> table. That is, use <literal>ALTER
- TABLE ... TYPE=INNODB</literal>, or create an empty
+ TABLE … TYPE=INNODB</literal>, or create an empty
<literal>InnoDB</literal> table with identical definitions and
- insert the rows with <literal>INSERT INTO ... SELECT * FROM
- ...</literal>.
+ insert the rows with <literal>INSERT INTO … SELECT * FROM
+ …</literal>.
</para>
<para>
@@ -2746,8 +2745,10 @@
the <literal>InnoDB</literal> tablespace and the log files, as
well as the <filename>.frm</filename> files of the
<literal>InnoDB</literal> tables, and move the copies to the
- slave. For the proper procedure to do this, see
- <xref linkend="moving"/>.
+ slave. If the <literal>innodb_file_per_table</literal> variable
+ is enabled, you must also copy the <filename>.ibd</filename>
+ files as well. For the proper procedure to do this, see
+ <xref linkend="backing-up"/>.
</para>
<para>
@@ -2778,7 +2779,9 @@
TABLE <replaceable>tbl_name</replaceable> FROM
MASTER</literal>, and then use <literal>ALTER
TABLE</literal> to alter the master table back to the
- <literal>InnoDB</literal> type afterward.
+ <literal>InnoDB</literal> type afterward. However, this
+ should not be done for tables that have foreign key
+ definitions because the definitions will be lost.
</para>
</listitem>
@@ -2831,15 +2834,10 @@
<para>
Transactions that fail on the master do not affect replication
at all. MySQL replication is based on the binary log where MySQL
- writes SQL statements that modify data. A slave reads the binary
- log of the master and executes the same SQL statements. However,
- statements that occur within a transaction are not written to
- the binary log until the transaction commits, at which point all
- statements in the transaction are written at once. If a
- statement fails, for example, because of a foreign key
- violation, or if a transaction is rolled back, no SQL statements
- are written to the binary log, and the transaction is not
- executed on the slave at all.
+ writes SQL statements that modify data. A transaction that fails
+ (for example, because of a foreign key violation, or because it
+ is is rolled back) is not written to the binary log, so it is
+ not sent to slaves.
</para>
</section>
@@ -2864,27 +2862,26 @@
the tablespace definition. Then <literal>InnoDB</literal>
increases the size of that file automatically in 8MB increments
when it runs out of space. Starting with MySQL 4.0.24 and 4.1.5,
- the increment size can be configured by setting the value of the
+ the increment size can be changed by setting the value of the
<literal>innodb_autoextend_increment</literal> system variable,
- which is measured in MB, and has a default value of 8.
+ which is measured in MB.
</para>
<para>
Alternatively, you can increase the size of your tablespace by
adding another data file. To do this, you have to shut down the
- MySQL server, edit the <filename>my.cnf</filename> file to add a
- new data file to the end of
- <literal>innodb_data_file_path</literal>, and start the server
- again.
+ MySQL server, change the tablespace configuration to add a new
+ data file to the end of <literal>innodb_data_file_path</literal>,
+ and start the server again.
</para>
<para>
If your last data file was defined with the keyword
- <literal>autoextend</literal>, the procedure to edit
- <filename>my.cnf</filename> must take into account the size to
- which the last data file has grown. Obtain the size of the data
- file, round it down to the closest multiple of 1024 × 1024
- bytes (= 1MB), and specify the rounded size explicitly in
+ <literal>autoextend</literal>, the procedure for reconfiguring the
+ tablespace must take into account the size to which the last data
+ file has grown. Obtain the size of the data file, round it down to
+ the closest multiple of 1024 × 1024 bytes (= 1MB), and
+ specify the rounded size explicitly in
<literal>innodb_data_file_path</literal>. Then you can add another
data file. Remember that only the last data file in the
<literal>innodb_data_file_path</literal> can be specified as
@@ -2903,8 +2900,9 @@
<para>
Suppose that this data file, over time, has grown to 988MB. Here
- is the configuration line after adding another auto-extending data
- file.
+ is the configuration line after modifying the original data file
+ to not be auto-extending and adding another auto-extending data
+ file:
</para>
<programlisting>
@@ -2913,9 +2911,9 @@
</programlisting>
<para>
- When you add a new file to the tablespace, make sure that it does
- not exist. <literal>InnoDB</literal> creates and initializes the
- file when you restart the server.
+ When you add a new file to the tablespace configuration, make sure
+ that it does not exist. <literal>InnoDB</literal> will create and
+ initialize the file when you restart the server.
</para>
<para>
@@ -2967,14 +2965,15 @@
<para>
If you want to change the number or the size of your
<literal>InnoDB</literal> log files, you have to stop the MySQL
- server and make sure that it shuts down without errors. Then copy
- the old log files into a safe place just in case something went
- wrong in the shutdown and you need them to recover the tablespace.
- Delete the old log files from the log file directory, edit
- <filename>my.cnf</filename> to change the log file configuration,
- and start the MySQL server again. <command>mysqld</command> sees
- that no log files exist at startup and tells you that it is
- creating new ones.
+ server and make sure that it shuts down without errors (to ensure
+ that there is no information for outstanding transactions in the
+ logs). Then copy the old log files into a safe place just in case
+ something went wrong in the shutdown and you need them to recover
+ the tablespace. Delete the old log files from the log file
+ directory, edit <filename>my.cnf</filename> to change the log file
+ configuration, and start the MySQL server again.
+ <command>mysqld</command> sees that no log files exist at startup
+ and tells you that it is creating new ones.
</para>
</section>
@@ -2984,7 +2983,7 @@
<title>&title-backing-up;</title>
<para>
- The key to safe database management is taking regular backups.
+ The key to safe database management is making regular backups.
</para>
<para>
@@ -2993,8 +2992,8 @@
is running. <command>InnoDB Hot Backup</command> does not require
you to shut down your database and it does not set any locks or
disturb your normal database processing. <command>InnoDB Hot
- Backup</command> is a non-free (commercial) add-on tool whose
- annual license fee is €390 per computer on which the MySQL
+ Backup</command> is a non-free (commercial) add-on tool with an
+ annual license fee of €390 per computer on which the MySQL
server is run. See the
<ulink url="http://www.innodb.com/order.html"><command>InnoDB Hot
Backup</command> home page</ulink> for detailed information and
@@ -3054,8 +3053,8 @@
</para>
<para>
- In addition to taking binary backups as just described, you should
- also regularly take dumps of your tables with
+ In addition to making binary backups as just described, you should
+ also regularly make dumps of your tables with
<command>mysqldump</command>. The reason for this is that a binary
file might be corrupted without you noticing it. Dumped tables are
stored into text files that are human-readable, so spotting table
@@ -3063,7 +3062,7 @@
chance for serious data corruption is smaller.
<command>mysqldump</command> also has a
<option>--single-transaction</option> option that you can use to
- take a consistent snapshot without locking out other clients.
+ make a consistent snapshot without locking out other clients.
</para>
<para>
@@ -3597,7 +3596,7 @@
<para>
Thus, intention locks do not block anything except full table
- requests (for example, <literal>LOCK TABLES ...
+ requests (for example, <literal>LOCK TABLES …
WRITE</literal>). The main purpose of
<replaceable>IX</replaceable> and <replaceable>IS</replaceable>
locks is to show that someone is locking a row, or going to lock
@@ -3695,8 +3694,8 @@
<para>
In <literal>InnoDB</literal>, all user activity occurs inside a
transaction. If the autocommit mode is enabled, each SQL
- statement forms a single transaction on its own. MySQL always
- starts a new connection with autocommit enabled.
+ statement forms a single transaction on its own. By default,
+ MySQL starts new connections with autocommit enabled.
</para>
<para>
@@ -3704,13 +3703,13 @@
AUTOCOMMIT = 0</literal>, then we can consider that a user
always has a transaction open. A SQL <literal>COMMIT</literal>
or <literal>ROLLBACK</literal> statement ends the current
- transaction and a new one starts. Both statements release all
- <literal>InnoDB</literal> locks that were set during the current
- transaction. A <literal>COMMIT</literal> means that the changes
- made in the current transaction are made permanent and become
- visible to other users. A <literal>ROLLBACK</literal> statement,
- on the other hand, cancels all modifications made by the current
- transaction.
+ transaction and a new one starts. A <literal>COMMIT</literal>
+ means that the changes made in the current transaction are made
+ permanent and become visible to other users. A
+ <literal>ROLLBACK</literal> statement, on the other hand,
+ cancels all modifications made by the current transaction. Both
+ statements release all <literal>InnoDB</literal> locks that were
+ set during the current transaction.
</para>
<para>
@@ -3735,9 +3734,9 @@
isolation levels described by the SQL standard. You can set the
default isolation level for all connections by using the
<option>--transaction-isolation</option> option on the command
- line or in option files. For example, you can set the option in
- the <literal>[mysqld]</literal> section of
- <filename>my.cnf</filename> like this:
+ line or in an option file. For example, you can set the option
+ in the <literal>[mysqld]</literal> section of an option file
+ like this:
</para>
<programlisting>
@@ -3747,8 +3746,8 @@
</programlisting>
<para>
- A user can change the isolation level of a single session or all
- new incoming connections with the <literal>SET
+ A user can change the isolation level for a single session or
+ for all new incoming connections with the <literal>SET
TRANSACTION</literal> statement. Its syntax is as follows:
</para>
@@ -3769,9 +3768,9 @@
(not started) transaction. If you use the
<literal>GLOBAL</literal> keyword, the statement sets the
default transaction level globally for all new connections
- created from that point on (but not existing connections). You
- need the <literal>SUPER</literal> privilege to do this. Using
- the <literal>SESSION</literal> keyword sets the default
+ created from that point on (but not for existing connections).
+ You need the <literal>SUPER</literal> privilege to do this.
+ Using the <literal>SESSION</literal> keyword sets the default
transaction level for all future transactions performed on the
current connection.
</para>
@@ -3790,8 +3789,10 @@
</para>
<para>
- You can query the global and session transaction isolation
- levels with these statements:
+ You can determine the global and session transaction isolation
+ levels by checking the value of the
+ <literal>tx_isolation</literal> system variable with these
+ statements:
</para>
<programlisting>
@@ -3839,10 +3840,10 @@
<para>
A somewhat Oracle-like isolation level. All <literal>SELECT
- ... FOR UPDATE</literal> and <literal>SELECT ... LOCK IN
- SHARE MODE</literal> statements lock only the index records,
- not the gaps before them, and thus allow the free insertion
- of new records next to locked records.
+ … FOR UPDATE</literal> and <literal>SELECT …
+ LOCK IN SHARE MODE</literal> statements lock only the index
+ records, not the gaps before them, and thus allow the free
+ insertion of new records next to locked records.
<literal>UPDATE</literal> and <literal>DELETE</literal>
statements using a unique index with a unique search
condition lock only the index record found, not the gap
@@ -3869,8 +3870,8 @@
<para>
This is the default isolation level of
- <literal>InnoDB</literal>. <literal>SELECT ... FOR
- UPDATE</literal>, <literal>SELECT ... LOCK IN SHARE
+ <literal>InnoDB</literal>. <literal>SELECT … FOR
+ UPDATE</literal>, <literal>SELECT … LOCK IN SHARE
MODE</literal>, <literal>UPDATE</literal>, and
<literal>DELETE</literal> statements that use a unique index
with a unique search condition lock only the index record
@@ -3901,8 +3902,8 @@
<para>
This level is like <literal>REPEATABLE READ</literal>, but
<literal>InnoDB</literal> implicitly commits all plain
- <literal>SELECT</literal> statements to <literal>SELECT ...
- LOCK IN SHARE MODE</literal>.
+ <literal>SELECT</literal> statements to <literal>SELECT
+ … LOCK IN SHARE MODE</literal>.
</para>
</listitem>
@@ -4037,7 +4038,7 @@
</programlisting>
<para>
- A <literal>SELECT ... FOR UPDATE</literal> reads the latest
+ A <literal>SELECT … FOR UPDATE</literal> reads the latest
available data, setting exclusive locks on each row it reads.
Thus, it sets the same locks a searched SQL
<literal>UPDATE</literal> would set on the rows.
@@ -4045,9 +4046,9 @@
<para>
The preceding description is merely an example of how
- <literal>SELECT ... FOR UPDATE</literal> works. In MySQL, the
- specific task of generating a unique identifier actually can be
- accomplished using only a single access to the table:
+ <literal>SELECT … FOR UPDATE</literal> works. In MySQL,
+ the specific task of generating a unique identifier actually can
+ be accomplished using only a single access to the table:
</para>
<programlisting>
@@ -4250,9 +4251,9 @@
<listitem>
<para>
- <literal>SELECT ... FROM</literal> is a consistent read,
- reading a snapshot of the database and setting no locks
- unless the transaction isolation level is set to
+ <literal>SELECT … FROM</literal> is a consistent
+ read, reading a snapshot of the database and setting no
+ locks unless the transaction isolation level is set to
<literal>SERIALIZABLE</literal>. For
<literal>SERIALIZABLE</literal> level, this sets shared
next-key locks on the index records it encounters.
@@ -4261,26 +4262,26 @@
<listitem>
<para>
- <literal>SELECT ... FROM ... LOCK IN SHARE MODE</literal>
- sets shared next-key locks on all index records the read
- encounters.
+ <literal>SELECT … FROM … LOCK IN SHARE
+ MODE</literal> sets shared next-key locks on all index
+ records the read encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FROM ... FOR UPDATE</literal> sets
- exclusive next-key locks on all index records the read
+ <literal>SELECT … FROM … FOR UPDATE</literal>
+ sets exclusive next-key locks on all index records the read
encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>INSERT INTO ... VALUES (...)</literal> sets an
- exclusive lock on the inserted row. Note that this lock is
- not a next-key lock and does not prevent other users from
- inserting to the gap before the inserted row. If a
+ <literal>INSERT INTO … VALUES (…)</literal>
+ sets an exclusive lock on the inserted row. Note that this
+ lock is not a next-key lock and does not prevent other users
+ from inserting to the gap before the inserted row. If a
duplicate-key error occurs, a shared lock on the duplicate
index record is set.
</para>
@@ -4316,23 +4317,23 @@
<listitem>
<para>
- <literal>INSERT INTO T SELECT ... FROM S WHERE ...</literal>
- sets an exclusive (non-next-key) lock on each row inserted
- into <literal>T</literal>. It does the search on
- <literal>S</literal> as a consistent read, but sets shared
- next-key locks on <literal>S</literal> if MySQL binary
- logging is turned on. <literal>InnoDB</literal> has to set
- locks in the latter case: In roll-forward recovery from a
- backup, every SQL statement has to be executed in exactly
- the same way it was done originally.
+ <literal>INSERT INTO T SELECT … FROM S WHERE
+ …</literal> sets an exclusive (non-next-key) lock on
+ each row inserted into <literal>T</literal>. It does the
+ search on <literal>S</literal> as a consistent read, but
+ sets shared next-key locks on <literal>S</literal> if MySQL
+ binary logging is turned on. <literal>InnoDB</literal> has
+ to set locks in the latter case: In roll-forward recovery
+ from a backup, every SQL statement has to be executed in
+ exactly the same way it was done originally.
</para>
</listitem>
<listitem>
<para>
- <literal>CREATE TABLE ... SELECT ...</literal> performs the
- <literal>SELECT</literal> as a consistent read or with
- shared locks, as in the previous item.
+ <literal>CREATE TABLE … SELECT …</literal>
+ performs the <literal>SELECT</literal> as a consistent read
+ or with shared locks, as in the previous item.
</para>
</listitem>
@@ -4346,15 +4347,16 @@
<listitem>
<para>
- <literal>UPDATE ... WHERE ...</literal> sets an exclusive
- next-key lock on every record the search encounters.
+ <literal>UPDATE … WHERE …</literal> sets an
+ exclusive next-key lock on every record the search
+ encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>DELETE FROM ... WHERE ...</literal> sets an
- exclusive next-key lock on every record the search
+ <literal>DELETE FROM … WHERE …</literal> sets
+ an exclusive next-key lock on every record the search
encounters.
</para>
</listitem>
@@ -4583,8 +4585,8 @@
<listitem>
<para>
- If you are using locking reads (<literal>SELECT ... FOR
- UPDATE</literal> or <literal>... LOCK IN SHARE
+ If you are using locking reads (<literal>SELECT … FOR
+ UPDATE</literal> or <literal>… LOCK IN SHARE
MODE</literal>), try using a lower isolation level such as
<literal>READ COMMITTED</literal>.
</para>
Modified: trunk/refman-5.0/database-administration.xml
===================================================================
--- trunk/refman-5.0/database-administration.xml 2006-01-10 20:16:47 UTC (rev 758)
+++ trunk/refman-5.0/database-administration.xml 2006-01-11 04:43:25 UTC (rev 759)
@@ -18749,7 +18749,7 @@
<para>
For cases of operating system crashes or power failures, we can
- assume that MySQLś disk data is available after a restart. The
+ assume that MySQL's disk data is available after a restart. The
<literal>InnoDB</literal> data files might not contain
consistent data due to the crash, but <literal>InnoDB</literal>
reads its logs and finds in them the list of pending committed
Modified: trunk/refman-5.0/innodb.xml
===================================================================
--- trunk/refman-5.0/innodb.xml 2006-01-10 20:16:47 UTC (rev 758)
+++ trunk/refman-5.0/innodb.xml 2006-01-11 04:43:25 UTC (rev 759)
@@ -633,42 +633,41 @@
<para>
<literal>innodb_file_per_table</literal> affects only table
- creation. If you start the server with this option, new tables
- are created using <filename>.ibd</filename> files, but you can
- still access tables that exist in the shared tablespace. If you
- remove the option, new tables are created in the shared
+ creation, not access to existing tables. If you start the server
+ with this option, new tables are created using
+ <filename>.ibd</filename> files, but you can still access tables
+ that exist in the shared tablespace. If you remove the option
+ and restart the server, new tables are created in the shared
tablespace, but you can still access any tables that were
created using multiple tablespaces.
</para>
<para>
- <literal>InnoDB</literal> always needs the shared tablespace.
- The <filename>.ibd</filename> files are not sufficient for
- <literal>InnoDB</literal> to operate. The shared tablespace
- consists of the familiar <filename>ibdata</filename> files where
- <literal>InnoDB</literal> puts its internal data dictionary and
- undo logs.
+ <literal>InnoDB</literal> always needs the shared tablespace
+ because it puts its internal data dictionary and undo logs
+ there. The <filename>.ibd</filename> files are not sufficient
+ for <literal>InnoDB</literal> to operate.
</para>
<para>
<emphasis role="bold">Note</emphasis>: You cannot freely move
<filename>.ibd</filename> files between database directories as
you can with <literal>MyISAM</literal> table files. This is
- because the table definition is stored in the
- <literal>InnoDB</literal> shared tablespace, and because
- <literal>InnoDB</literal> must preserve the consistency of
- transaction IDs and log sequence numbers.
+ because the table definition that is stored in the
+ <literal>InnoDB</literal> shared tablespace includes the
+ database name, and because <literal>InnoDB</literal> must
+ preserve the consistency of transaction IDs and log sequence
+ numbers.
</para>
<para>
- Within a given MySQL installation, you can move an
- <filename>.ibd</filename> file and the associated table from one
- database to another with a <literal>RENAME TABLE</literal>
- statement:
+ To move an <filename>.ibd</filename> file and the associated
+ table from one database to another, use a <literal>RENAME
+ TABLE</literal> statement:
</para>
<programlisting>
-RENAME TABLE <replaceable>old_db_name.tbl_name</replaceable> TO <replaceable>new_db_name.tbl_name</replaceable>;
+RENAME TABLE <replaceable>db1.tbl_name</replaceable> TO <replaceable>db2.tbl_name</replaceable>;
</programlisting>
<indexterm type="function">
@@ -2055,10 +2054,10 @@
afterward. The fastest way to alter a table to
<literal>InnoDB</literal> is to do the inserts directly to an
<literal>InnoDB</literal> table. That is, use <literal>ALTER
- TABLE ... ENGINE=INNODB</literal>, or create an empty
+ TABLE … ENGINE=INNODB</literal>, or create an empty
<literal>InnoDB</literal> table with identical definitions and
- insert the rows with <literal>INSERT INTO ... SELECT * FROM
- ...</literal>.
+ insert the rows with <literal>INSERT INTO … SELECT * FROM
+ …</literal>.
</para>
<para>
@@ -2760,8 +2759,10 @@
the <literal>InnoDB</literal> tablespace and the log files, as
well as the <filename>.frm</filename> files of the
<literal>InnoDB</literal> tables, and move the copies to the
- slave. For the proper procedure to do this, see
- <xref linkend="moving"/>.
+ slave. If the <literal>innodb_file_per_table</literal> variable
+ is enabled, you must also copy the <filename>.ibd</filename>
+ files as well. For the proper procedure to do this, see
+ <xref linkend="backing-up"/>.
</para>
<para>
@@ -2775,10 +2776,10 @@
</para>
<para>
- One minor limitation in <literal>InnoDB</literal> replication is
- that <literal>LOAD TABLE FROM MASTER</literal> does not work for
- <literal>InnoDB</literal> type tables. There are two possible
- workarounds:
+ You cannot set up replication for <literal>InnoDB</literal>
+ using the <literal>LOAD TABLE FROM MASTER</literal> statement,
+ which works only for <literal>MyISAM</literal> tables. There are
+ two possible workarounds:
</para>
<itemizedlist>
@@ -2798,6 +2799,9 @@
<replaceable>tbl_name</replaceable> FROM MASTER</literal>,
and then use <literal>ALTER TABLE</literal> to convert the
master table back to <literal>InnoDB</literal> afterward.
+ However, this should not be done for tables that have
+ foreign key definitions because the definitions will be
+ lost.
</para>
</listitem>
@@ -2806,15 +2810,10 @@
<para>
Transactions that fail on the master do not affect replication
at all. MySQL replication is based on the binary log where MySQL
- writes SQL statements that modify data. A slave reads the binary
- log of the master and executes the same SQL statements. However,
- statements that occur within a transaction are not written to
- the binary log until the transaction commits, at which point all
- statements in the transaction are written at once. If a
- statement fails, for example, because of a foreign key
- violation, or if a transaction is rolled back, no SQL statements
- are written to the binary log, and the transaction is not
- executed on the slave at all.
+ writes SQL statements that modify data. A transaction that fails
+ (for example, because of a foreign key violation, or because it
+ is is rolled back) is not written to the binary log, so it is
+ not sent to slaves.
</para>
</section>
@@ -2838,28 +2837,27 @@
<literal>autoextend</literal> attribute for the last data file in
the tablespace definition. Then <literal>InnoDB</literal>
increases the size of that file automatically in 8MB increments
- when it runs out of space. The increment size can be configured by
+ when it runs out of space. The increment size can be changed by
setting the value of the
<literal>innodb_autoextend_increment</literal> system variable,
- which is measured in MB, and has a default value of 8.
+ which is measured in MB.
</para>
<para>
Alternatively, you can increase the size of your tablespace by
adding another data file. To do this, you have to shut down the
- MySQL server, edit the <filename>my.cnf</filename> file to add a
- new data file to the end of
- <literal>innodb_data_file_path</literal>, and start the server
- again.
+ MySQL server, change the tablespace configuration to add a new
+ data file to the end of <literal>innodb_data_file_path</literal>,
+ and start the server again.
</para>
<para>
If your last data file was defined with the keyword
- <literal>autoextend</literal>, the procedure to edit
- <filename>my.cnf</filename> must take into account the size to
- which the last data file has grown. Obtain the size of the data
- file, round it down to the closest multiple of 1024 × 1024
- bytes (= 1MB), and specify the rounded size explicitly in
+ <literal>autoextend</literal>, the procedure for reconfiguring the
+ tablespace must take into account the size to which the last data
+ file has grown. Obtain the size of the data file, round it down to
+ the closest multiple of 1024 × 1024 bytes (= 1MB), and
+ specify the rounded size explicitly in
<literal>innodb_data_file_path</literal>. Then you can add another
data file. Remember that only the last data file in the
<literal>innodb_data_file_path</literal> can be specified as
@@ -2878,8 +2876,9 @@
<para>
Suppose that this data file, over time, has grown to 988MB. Here
- is the configuration line after adding another auto-extending data
- file.
+ is the configuration line after modifying the original data file
+ to not be auto-extending and adding another auto-extending data
+ file:
</para>
<programlisting>
@@ -2888,9 +2887,9 @@
</programlisting>
<para>
- When you add a new file to the tablespace, make sure that it does
- not exist. <literal>InnoDB</literal> creates and initializes the
- file when you restart the server.
+ When you add a new file to the tablespace configuration, make sure
+ that it does not exist. <literal>InnoDB</literal> will create and
+ initialize the file when you restart the server.
</para>
<para>
@@ -2942,14 +2941,15 @@
<para>
If you want to change the number or the size of your
<literal>InnoDB</literal> log files, you have to stop the MySQL
- server and make sure that it shuts down without errors. Then copy
- the old log files into a safe place just in case something went
- wrong in the shutdown and you need them to recover the tablespace.
- Delete the old log files from the log file directory, edit
- <filename>my.cnf</filename> to change the log file configuration,
- and start the MySQL server again. <command>mysqld</command> sees
- that no log files exist at startup and tells you that it is
- creating new ones.
+ server and make sure that it shuts down without errors (to ensure
+ that there is no information for outstanding transactions in the
+ logs). Then copy the old log files into a safe place just in case
+ something went wrong in the shutdown and you need them to recover
+ the tablespace. Delete the old log files from the log file
+ directory, edit <filename>my.cnf</filename> to change the log file
+ configuration, and start the MySQL server again.
+ <command>mysqld</command> sees that no log files exist at startup
+ and tells you that it is creating new ones.
</para>
</section>
@@ -2959,7 +2959,7 @@
<title>&title-backing-up;</title>
<para>
- The key to safe database management is taking regular backups.
+ The key to safe database management is making regular backups.
</para>
<para>
@@ -2968,8 +2968,8 @@
is running. <command>InnoDB Hot Backup</command> does not require
you to shut down your database and it does not set any locks or
disturb your normal database processing. <command>InnoDB Hot
- Backup</command> is a non-free (commercial) add-on tool whose
- annual license fee is €390 per computer on which the MySQL
+ Backup</command> is a non-free (commercial) add-on tool with an
+ annual license fee of €390 per computer on which the MySQL
server is run. See the
<ulink url="http://www.innodb.com/order.html"><command>InnoDB Hot
Backup</command> home page</ulink> for detailed information and
@@ -3029,8 +3029,8 @@
</para>
<para>
- In addition to taking binary backups as just described, you should
- also regularly take dumps of your tables with
+ In addition to making binary backups as just described, you should
+ also regularly make dumps of your tables with
<command>mysqldump</command>. The reason for this is that a binary
file might be corrupted without you noticing it. Dumped tables are
stored into text files that are human-readable, so spotting table
@@ -3038,7 +3038,7 @@
chance for serious data corruption is smaller.
<command>mysqldump</command> also has a
<option>--single-transaction</option> option that you can use to
- take a consistent snapshot without locking out other clients.
+ make a consistent snapshot without locking out other clients.
</para>
<para>
@@ -3561,7 +3561,7 @@
<para>
Thus, intention locks do not block anything except full table
- requests (for example, <literal>LOCK TABLES ...
+ requests (for example, <literal>LOCK TABLES …
WRITE</literal>). The main purpose of
<replaceable>IX</replaceable> and <replaceable>IS</replaceable>
locks is to show that someone is locking a row, or going to lock
@@ -3659,8 +3659,8 @@
<para>
In <literal>InnoDB</literal>, all user activity occurs inside a
transaction. If the autocommit mode is enabled, each SQL
- statement forms a single transaction on its own. MySQL always
- starts a new connection with autocommit enabled.
+ statement forms a single transaction on its own. By default,
+ MySQL starts new connections with autocommit enabled.
</para>
<para>
@@ -3668,13 +3668,13 @@
AUTOCOMMIT = 0</literal>, then we can consider that a user
always has a transaction open. A SQL <literal>COMMIT</literal>
or <literal>ROLLBACK</literal> statement ends the current
- transaction and a new one starts. Both statements release all
- <literal>InnoDB</literal> locks that were set during the current
- transaction. A <literal>COMMIT</literal> means that the changes
- made in the current transaction are made permanent and become
- visible to other users. A <literal>ROLLBACK</literal> statement,
- on the other hand, cancels all modifications made by the current
- transaction.
+ transaction and a new one starts. A <literal>COMMIT</literal>
+ means that the changes made in the current transaction are made
+ permanent and become visible to other users. A
+ <literal>ROLLBACK</literal> statement, on the other hand,
+ cancels all modifications made by the current transaction. Both
+ statements release all <literal>InnoDB</literal> locks that were
+ set during the current transaction.
</para>
<para>
@@ -3698,9 +3698,9 @@
transaction isolation levels described by the SQL standard. You
can set the default isolation level for all connections by using
the <option>--transaction-isolation</option> option on the
- command line or in option files. For example, you can set the
- option in the <literal>[mysqld]</literal> section of
- <filename>my.cnf</filename> like this:
+ command line or in an option file. For example, you can set the
+ option in the <literal>[mysqld]</literal> section of an option
+ file like this:
</para>
<programlisting>
@@ -3710,8 +3710,8 @@
</programlisting>
<para>
- A user can change the isolation level of a single session or all
- new incoming connections with the <literal>SET
+ A user can change the isolation level for a single session or
+ for all new incoming connections with the <literal>SET
TRANSACTION</literal> statement. Its syntax is as follows:
</para>
@@ -3732,9 +3732,9 @@
(not started) transaction. If you use the
<literal>GLOBAL</literal> keyword, the statement sets the
default transaction level globally for all new connections
- created from that point on (but not existing connections). You
- need the <literal>SUPER</literal> privilege to do this. Using
- the <literal>SESSION</literal> keyword sets the default
+ created from that point on (but not for existing connections).
+ You need the <literal>SUPER</literal> privilege to do this.
+ Using the <literal>SESSION</literal> keyword sets the default
transaction level for all future transactions performed on the
current connection.
</para>
@@ -3746,8 +3746,10 @@
</para>
<para>
- You can query the global and session transaction isolation
- levels with these statements:
+ You can determine the global and session transaction isolation
+ levels by checking the value of the
+ <literal>tx_isolation</literal> system variable with these
+ statements:
</para>
<programlisting>
@@ -3795,10 +3797,10 @@
<para>
A somewhat Oracle-like isolation level. All <literal>SELECT
- ... FOR UPDATE</literal> and <literal>SELECT ... LOCK IN
- SHARE MODE</literal> statements lock only the index records,
- not the gaps before them, and thus allow the free insertion
- of new records next to locked records.
+ … FOR UPDATE</literal> and <literal>SELECT …
+ LOCK IN SHARE MODE</literal> statements lock only the index
+ records, not the gaps before them, and thus allow the free
+ insertion of new records next to locked records.
<literal>UPDATE</literal> and <literal>DELETE</literal>
statements using a unique index with a unique search
condition lock only the index record found, not the gap
@@ -3825,8 +3827,8 @@
<para>
This is the default isolation level of
- <literal>InnoDB</literal>. <literal>SELECT ... FOR
- UPDATE</literal>, <literal>SELECT ... LOCK IN SHARE
+ <literal>InnoDB</literal>. <literal>SELECT … FOR
+ UPDATE</literal>, <literal>SELECT … LOCK IN SHARE
MODE</literal>, <literal>UPDATE</literal>, and
<literal>DELETE</literal> statements that use a unique index
with a unique search condition lock only the index record
@@ -3857,8 +3859,8 @@
<para>
This level is like <literal>REPEATABLE READ</literal>, but
<literal>InnoDB</literal> implicitly commits all plain
- <literal>SELECT</literal> statements to <literal>SELECT ...
- LOCK IN SHARE MODE</literal>.
+ <literal>SELECT</literal> statements to <literal>SELECT
+ … LOCK IN SHARE MODE</literal>.
</para>
</listitem>
@@ -3993,7 +3995,7 @@
</programlisting>
<para>
- A <literal>SELECT ... FOR UPDATE</literal> reads the latest
+ A <literal>SELECT … FOR UPDATE</literal> reads the latest
available data, setting exclusive locks on each row it reads.
Thus, it sets the same locks a searched SQL
<literal>UPDATE</literal> would set on the rows.
@@ -4001,9 +4003,9 @@
<para>
The preceding description is merely an example of how
- <literal>SELECT ... FOR UPDATE</literal> works. In MySQL, the
- specific task of generating a unique identifier actually can be
- accomplished using only a single access to the table:
+ <literal>SELECT … FOR UPDATE</literal> works. In MySQL,
+ the specific task of generating a unique identifier actually can
+ be accomplished using only a single access to the table:
</para>
<programlisting>
@@ -4206,9 +4208,9 @@
<listitem>
<para>
- <literal>SELECT ... FROM</literal> is a consistent read,
- reading a snapshot of the database and setting no locks
- unless the transaction isolation level is set to
+ <literal>SELECT … FROM</literal> is a consistent
+ read, reading a snapshot of the database and setting no
+ locks unless the transaction isolation level is set to
<literal>SERIALIZABLE</literal>. For
<literal>SERIALIZABLE</literal> level, this sets shared
next-key locks on the index records it encounters.
@@ -4217,26 +4219,26 @@
<listitem>
<para>
- <literal>SELECT ... FROM ... LOCK IN SHARE MODE</literal>
- sets shared next-key locks on all index records the read
- encounters.
+ <literal>SELECT … FROM … LOCK IN SHARE
+ MODE</literal> sets shared next-key locks on all index
+ records the read encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FROM ... FOR UPDATE</literal> sets
- exclusive next-key locks on all index records the read
+ <literal>SELECT … FROM … FOR UPDATE</literal>
+ sets exclusive next-key locks on all index records the read
encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>INSERT INTO ... VALUES (...)</literal> sets an
- exclusive lock on the inserted row. Note that this lock is
- not a next-key lock and does not prevent other users from
- inserting to the gap before the inserted row. If a
+ <literal>INSERT INTO … VALUES (…)</literal>
+ sets an exclusive lock on the inserted row. Note that this
+ lock is not a next-key lock and does not prevent other users
+ from inserting to the gap before the inserted row. If a
duplicate-key error occurs, a shared lock on the duplicate
index record is set.
</para>
@@ -4265,23 +4267,23 @@
<listitem>
<para>
- <literal>INSERT INTO T SELECT ... FROM S WHERE ...</literal>
- sets an exclusive (non-next-key) lock on each row inserted
- into <literal>T</literal>. It does the search on
- <literal>S</literal> as a consistent read, but sets shared
- next-key locks on <literal>S</literal> if MySQL binary
- logging is turned on. <literal>InnoDB</literal> has to set
- locks in the latter case: In roll-forward recovery from a
- backup, every SQL statement has to be executed in exactly
- the same way it was done originally.
+ <literal>INSERT INTO T SELECT … FROM S WHERE
+ …</literal> sets an exclusive (non-next-key) lock on
+ each row inserted into <literal>T</literal>. It does the
+ search on <literal>S</literal> as a consistent read, but
+ sets shared next-key locks on <literal>S</literal> if MySQL
+ binary logging is turned on. <literal>InnoDB</literal> has
+ to set locks in the latter case: In roll-forward recovery
+ from a backup, every SQL statement has to be executed in
+ exactly the same way it was done originally.
</para>
</listitem>
<listitem>
<para>
- <literal>CREATE TABLE ... SELECT ...</literal> performs the
- <literal>SELECT</literal> as a consistent read or with
- shared locks, as in the previous item.
+ <literal>CREATE TABLE … SELECT …</literal>
+ performs the <literal>SELECT</literal> as a consistent read
+ or with shared locks, as in the previous item.
</para>
</listitem>
@@ -4295,15 +4297,16 @@
<listitem>
<para>
- <literal>UPDATE ... WHERE ...</literal> sets an exclusive
- next-key lock on every record the search encounters.
+ <literal>UPDATE … WHERE …</literal> sets an
+ exclusive next-key lock on every record the search
+ encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>DELETE FROM ... WHERE ...</literal> sets an
- exclusive next-key lock on every record the search
+ <literal>DELETE FROM … WHERE …</literal> sets
+ an exclusive next-key lock on every record the search
encounters.
</para>
</listitem>
@@ -4533,8 +4536,8 @@
<listitem>
<para>
- If you are using locking reads (<literal>SELECT ... FOR
- UPDATE</literal> or <literal>... LOCK IN SHARE
+ If you are using locking reads (<literal>SELECT … FOR
+ UPDATE</literal> or <literal>… LOCK IN SHARE
MODE</literal>), try using a lower isolation level such as
<literal>READ COMMITTED</literal>.
</para>
Modified: trunk/refman-5.1/database-administration.xml
===================================================================
--- trunk/refman-5.1/database-administration.xml 2006-01-10 20:16:47 UTC (rev 758)
+++ trunk/refman-5.1/database-administration.xml 2006-01-11 04:43:25 UTC (rev 759)
@@ -18698,7 +18698,7 @@
<para>
For cases of operating system crashes or power failures, we can
- assume that MySQLś disk data is available after a restart. The
+ assume that MySQL's disk data is available after a restart. The
<literal>InnoDB</literal> data files might not contain
consistent data due to the crash, but <literal>InnoDB</literal>
reads its logs and finds in them the list of pending committed
Modified: trunk/refman-5.1/innodb.xml
===================================================================
--- trunk/refman-5.1/innodb.xml 2006-01-10 20:16:47 UTC (rev 758)
+++ trunk/refman-5.1/innodb.xml 2006-01-11 04:43:25 UTC (rev 759)
@@ -633,42 +633,41 @@
<para>
<literal>innodb_file_per_table</literal> affects only table
- creation. If you start the server with this option, new tables
- are created using <filename>.ibd</filename> files, but you can
- still access tables that exist in the shared tablespace. If you
- remove the option, new tables are created in the shared
+ creation, not access to existing tables. If you start the server
+ with this option, new tables are created using
+ <filename>.ibd</filename> files, but you can still access tables
+ that exist in the shared tablespace. If you remove the option
+ and restart the server, new tables are created in the shared
tablespace, but you can still access any tables that were
created using multiple tablespaces.
</para>
<para>
- <literal>InnoDB</literal> always needs the shared tablespace.
- The <filename>.ibd</filename> files are not sufficient for
- <literal>InnoDB</literal> to operate. The shared tablespace
- consists of the familiar <filename>ibdata</filename> files where
- <literal>InnoDB</literal> puts its internal data dictionary and
- undo logs.
+ <literal>InnoDB</literal> always needs the shared tablespace
+ because it puts its internal data dictionary and undo logs
+ there. The <filename>.ibd</filename> files are not sufficient
+ for <literal>InnoDB</literal> to operate.
</para>
<para>
<emphasis role="bold">Note</emphasis>: You cannot freely move
<filename>.ibd</filename> files between database directories as
you can with <literal>MyISAM</literal> table files. This is
- because the table definition is stored in the
- <literal>InnoDB</literal> shared tablespace, and because
- <literal>InnoDB</literal> must preserve the consistency of
- transaction IDs and log sequence numbers.
+ because the table definition that is stored in the
+ <literal>InnoDB</literal> shared tablespace includes the
+ database name, and because <literal>InnoDB</literal> must
+ preserve the consistency of transaction IDs and log sequence
+ numbers.
</para>
<para>
- Within a given MySQL installation, you can move an
- <filename>.ibd</filename> file and the associated table from one
- database to another with a <literal>RENAME TABLE</literal>
- statement:
+ To move an <filename>.ibd</filename> file and the associated
+ table from one database to another, use a <literal>RENAME
+ TABLE</literal> statement:
</para>
<programlisting>
-RENAME TABLE <replaceable>old_db_name.tbl_name</replaceable> TO <replaceable>new_db_name.tbl_name</replaceable>;
+RENAME TABLE <replaceable>db1.tbl_name</replaceable> TO <replaceable>db2.tbl_name</replaceable>;
</programlisting>
<indexterm type="function">
@@ -2031,10 +2030,10 @@
afterward. The fastest way to alter a table to
<literal>InnoDB</literal> is to do the inserts directly to an
<literal>InnoDB</literal> table. That is, use <literal>ALTER
- TABLE ... ENGINE=INNODB</literal>, or create an empty
+ TABLE … ENGINE=INNODB</literal>, or create an empty
<literal>InnoDB</literal> table with identical definitions and
- insert the rows with <literal>INSERT INTO ... SELECT * FROM
- ...</literal>.
+ insert the rows with <literal>INSERT INTO … SELECT * FROM
+ …</literal>.
</para>
<para>
@@ -2735,8 +2734,10 @@
the <literal>InnoDB</literal> tablespace and the log files, as
well as the <filename>.frm</filename> files of the
<literal>InnoDB</literal> tables, and move the copies to the
- slave. For the proper procedure to do this, see
- <xref linkend="moving"/>.
+ slave. If the <literal>innodb_file_per_table</literal> variable
+ is enabled, you must also copy the <filename>.ibd</filename>
+ files as well. For the proper procedure to do this, see
+ <xref linkend="backing-up"/>.
</para>
<para>
@@ -2750,10 +2751,10 @@
</para>
<para>
- One minor limitation in <literal>InnoDB</literal> replication is
- that <literal>LOAD TABLE FROM MASTER</literal> does not work for
- <literal>InnoDB</literal> type tables. There are two possible
- workarounds:
+ You cannot set up replication for <literal>InnoDB</literal>
+ using the <literal>LOAD TABLE FROM MASTER</literal> statement,
+ which works only for <literal>MyISAM</literal> tables. There are
+ two possible workarounds:
</para>
<itemizedlist>
@@ -2773,6 +2774,9 @@
<replaceable>tbl_name</replaceable> FROM MASTER</literal>,
and then use <literal>ALTER TABLE</literal> to convert the
master table back to <literal>InnoDB</literal> afterward.
+ However, this should not be done for tables that have
+ foreign key definitions because the definitions will be
+ lost.
</para>
</listitem>
@@ -2781,15 +2785,10 @@
<para>
Transactions that fail on the master do not affect replication
at all. MySQL replication is based on the binary log where MySQL
- writes SQL statements that modify data. A slave reads the binary
- log of the master and executes the same SQL statements. However,
- statements that occur within a transaction are not written to
- the binary log until the transaction commits, at which point all
- statements in the transaction are written at once. If a
- statement fails, for example, because of a foreign key
- violation, or if a transaction is rolled back, no SQL statements
- are written to the binary log, and the transaction is not
- executed on the slave at all.
+ writes SQL statements that modify data. A transaction that fails
+ (for example, because of a foreign key violation, or because it
+ is is rolled back) is not written to the binary log, so it is
+ not sent to slaves.
</para>
</section>
@@ -2813,28 +2812,27 @@
<literal>autoextend</literal> attribute for the last data file in
the tablespace definition. Then <literal>InnoDB</literal>
increases the size of that file automatically in 8MB increments
- when it runs out of space. The increment size can be configured by
+ when it runs out of space. The increment size can be changed by
setting the value of the
<literal>innodb_autoextend_increment</literal> system variable,
- which is measured in MB, and has a default value of 8.
+ which is measured in MB.
</para>
<para>
Alternatively, you can increase the size of your tablespace by
adding another data file. To do this, you have to shut down the
- MySQL server, edit the <filename>my.cnf</filename> file to add a
- new data file to the end of
- <literal>innodb_data_file_path</literal>, and start the server
- again.
+ MySQL server, change the tablespace configuration to add a new
+ data file to the end of <literal>innodb_data_file_path</literal>,
+ and start the server again.
</para>
<para>
If your last data file was defined with the keyword
- <literal>autoextend</literal>, the procedure to edit
- <filename>my.cnf</filename> must take into account the size to
- which the last data file has grown. Obtain the size of the data
- file, round it down to the closest multiple of 1024 × 1024
- bytes (= 1MB), and specify the rounded size explicitly in
+ <literal>autoextend</literal>, the procedure for reconfiguring the
+ tablespace must take into account the size to which the last data
+ file has grown. Obtain the size of the data file, round it down to
+ the closest multiple of 1024 × 1024 bytes (= 1MB), and
+ specify the rounded size explicitly in
<literal>innodb_data_file_path</literal>. Then you can add another
data file. Remember that only the last data file in the
<literal>innodb_data_file_path</literal> can be specified as
@@ -2853,8 +2851,9 @@
<para>
Suppose that this data file, over time, has grown to 988MB. Here
- is the configuration line after adding another auto-extending data
- file.
+ is the configuration line after modifying the original data file
+ to not be auto-extending and adding another auto-extending data
+ file:
</para>
<programlisting>
@@ -2863,9 +2862,9 @@
</programlisting>
<para>
- When you add a new file to the tablespace, make sure that it does
- not exist. <literal>InnoDB</literal> creates and initializes the
- file when you restart the server.
+ When you add a new file to the tablespace configuration, make sure
+ that it does not exist. <literal>InnoDB</literal> will create and
+ initialize the file when you restart the server.
</para>
<para>
@@ -2917,14 +2916,15 @@
<para>
If you want to change the number or the size of your
<literal>InnoDB</literal> log files, you have to stop the MySQL
- server and make sure that it shuts down without errors. Then copy
- the old log files into a safe place just in case something went
- wrong in the shutdown and you need them to recover the tablespace.
- Delete the old log files from the log file directory, edit
- <filename>my.cnf</filename> to change the log file configuration,
- and start the MySQL server again. <command>mysqld</command> sees
- that no log files exist at startup and tells you that it is
- creating new ones.
+ server and make sure that it shuts down without errors (to ensure
+ that there is no information for outstanding transactions in the
+ logs). Then copy the old log files into a safe place just in case
+ something went wrong in the shutdown and you need them to recover
+ the tablespace. Delete the old log files from the log file
+ directory, edit <filename>my.cnf</filename> to change the log file
+ configuration, and start the MySQL server again.
+ <command>mysqld</command> sees that no log files exist at startup
+ and tells you that it is creating new ones.
</para>
</section>
@@ -2934,7 +2934,7 @@
<title>&title-backing-up;</title>
<para>
- The key to safe database management is taking regular backups.
+ The key to safe database management is making regular backups.
</para>
<para>
@@ -2943,8 +2943,8 @@
is running. <command>InnoDB Hot Backup</command> does not require
you to shut down your database and it does not set any locks or
disturb your normal database processing. <command>InnoDB Hot
- Backup</command> is a non-free (commercial) add-on tool whose
- annual license fee is €390 per computer on which the MySQL
+ Backup</command> is a non-free (commercial) add-on tool with an
+ annual license fee of €390 per computer on which the MySQL
server is run. See the
<ulink url="http://www.innodb.com/order.html"><command>InnoDB Hot
Backup</command> home page</ulink> for detailed information and
@@ -3004,8 +3004,8 @@
</para>
<para>
- In addition to taking binary backups as just described, you should
- also regularly take dumps of your tables with
+ In addition to making binary backups as just described, you should
+ also regularly make dumps of your tables with
<command>mysqldump</command>. The reason for this is that a binary
file might be corrupted without you noticing it. Dumped tables are
stored into text files that are human-readable, so spotting table
@@ -3013,7 +3013,7 @@
chance for serious data corruption is smaller.
<command>mysqldump</command> also has a
<option>--single-transaction</option> option that you can use to
- take a consistent snapshot without locking out other clients.
+ make a consistent snapshot without locking out other clients.
</para>
<para>
@@ -3536,7 +3536,7 @@
<para>
Thus, intention locks do not block anything except full table
- requests (for example, <literal>LOCK TABLES ...
+ requests (for example, <literal>LOCK TABLES …
WRITE</literal>). The main purpose of
<replaceable>IX</replaceable> and <replaceable>IS</replaceable>
locks is to show that someone is locking a row, or going to lock
@@ -3634,8 +3634,8 @@
<para>
In <literal>InnoDB</literal>, all user activity occurs inside a
transaction. If the autocommit mode is enabled, each SQL
- statement forms a single transaction on its own. MySQL always
- starts a new connection with autocommit enabled.
+ statement forms a single transaction on its own. By default,
+ MySQL starts new connections with autocommit enabled.
</para>
<para>
@@ -3643,13 +3643,13 @@
AUTOCOMMIT = 0</literal>, then we can consider that a user
always has a transaction open. A SQL <literal>COMMIT</literal>
or <literal>ROLLBACK</literal> statement ends the current
- transaction and a new one starts. Both statements release all
- <literal>InnoDB</literal> locks that were set during the current
- transaction. A <literal>COMMIT</literal> means that the changes
- made in the current transaction are made permanent and become
- visible to other users. A <literal>ROLLBACK</literal> statement,
- on the other hand, cancels all modifications made by the current
- transaction.
+ transaction and a new one starts. A <literal>COMMIT</literal>
+ means that the changes made in the current transaction are made
+ permanent and become visible to other users. A
+ <literal>ROLLBACK</literal> statement, on the other hand,
+ cancels all modifications made by the current transaction. Both
+ statements release all <literal>InnoDB</literal> locks that were
+ set during the current transaction.
</para>
<para>
@@ -3673,9 +3673,9 @@
transaction isolation levels described by the SQL standard. You
can set the default isolation level for all connections by using
the <option>--transaction-isolation</option> option on the
- command line or in option files. For example, you can set the
- option in the <literal>[mysqld]</literal> section of
- <filename>my.cnf</filename> like this:
+ command line or in an option file. For example, you can set the
+ option in the <literal>[mysqld]</literal> section of an option
+ file like this:
</para>
<programlisting>
@@ -3685,8 +3685,8 @@
</programlisting>
<para>
- A user can change the isolation level of a single session or all
- new incoming connections with the <literal>SET
+ A user can change the isolation level for a single session or
+ for all new incoming connections with the <literal>SET
TRANSACTION</literal> statement. Its syntax is as follows:
</para>
@@ -3707,9 +3707,9 @@
(not started) transaction. If you use the
<literal>GLOBAL</literal> keyword, the statement sets the
default transaction level globally for all new connections
- created from that point on (but not existing connections). You
- need the <literal>SUPER</literal> privilege to do this. Using
- the <literal>SESSION</literal> keyword sets the default
+ created from that point on (but not for existing connections).
+ You need the <literal>SUPER</literal> privilege to do this.
+ Using the <literal>SESSION</literal> keyword sets the default
transaction level for all future transactions performed on the
current connection.
</para>
@@ -3721,8 +3721,10 @@
</para>
<para>
- You can query the global and session transaction isolation
- levels with these statements:
+ You can determine the global and session transaction isolation
+ levels by checking the value of the
+ <literal>tx_isolation</literal> system variable with these
+ statements:
</para>
<programlisting>
@@ -3770,10 +3772,10 @@
<para>
A somewhat Oracle-like isolation level. All <literal>SELECT
- ... FOR UPDATE</literal> and <literal>SELECT ... LOCK IN
- SHARE MODE</literal> statements lock only the index records,
- not the gaps before them, and thus allow the free insertion
- of new records next to locked records.
+ … FOR UPDATE</literal> and <literal>SELECT …
+ LOCK IN SHARE MODE</literal> statements lock only the index
+ records, not the gaps before them, and thus allow the free
+ insertion of new records next to locked records.
<literal>UPDATE</literal> and <literal>DELETE</literal>
statements using a unique index with a unique search
condition lock only the index record found, not the gap
@@ -3800,8 +3802,8 @@
<para>
This is the default isolation level of
- <literal>InnoDB</literal>. <literal>SELECT ... FOR
- UPDATE</literal>, <literal>SELECT ... LOCK IN SHARE
+ <literal>InnoDB</literal>. <literal>SELECT … FOR
+ UPDATE</literal>, <literal>SELECT … LOCK IN SHARE
MODE</literal>, <literal>UPDATE</literal>, and
<literal>DELETE</literal> statements that use a unique index
with a unique search condition lock only the index record
@@ -3832,8 +3834,8 @@
<para>
This level is like <literal>REPEATABLE READ</literal>, but
<literal>InnoDB</literal> implicitly commits all plain
- <literal>SELECT</literal> statements to <literal>SELECT ...
- LOCK IN SHARE MODE</literal>.
+ <literal>SELECT</literal> statements to <literal>SELECT
+ … LOCK IN SHARE MODE</literal>.
</para>
</listitem>
@@ -3968,7 +3970,7 @@
</programlisting>
<para>
- A <literal>SELECT ... FOR UPDATE</literal> reads the latest
+ A <literal>SELECT … FOR UPDATE</literal> reads the latest
available data, setting exclusive locks on each row it reads.
Thus, it sets the same locks a searched SQL
<literal>UPDATE</literal> would set on the rows.
@@ -3976,9 +3978,9 @@
<para>
The preceding description is merely an example of how
- <literal>SELECT ... FOR UPDATE</literal> works. In MySQL, the
- specific task of generating a unique identifier actually can be
- accomplished using only a single access to the table:
+ <literal>SELECT … FOR UPDATE</literal> works. In MySQL,
+ the specific task of generating a unique identifier actually can
+ be accomplished using only a single access to the table:
</para>
<programlisting>
@@ -4181,9 +4183,9 @@
<listitem>
<para>
- <literal>SELECT ... FROM</literal> is a consistent read,
- reading a snapshot of the database and setting no locks
- unless the transaction isolation level is set to
+ <literal>SELECT … FROM</literal> is a consistent
+ read, reading a snapshot of the database and setting no
+ locks unless the transaction isolation level is set to
<literal>SERIALIZABLE</literal>. For
<literal>SERIALIZABLE</literal> level, this sets shared
next-key locks on the index records it encounters.
@@ -4192,26 +4194,26 @@
<listitem>
<para>
- <literal>SELECT ... FROM ... LOCK IN SHARE MODE</literal>
- sets shared next-key locks on all index records the read
- encounters.
+ <literal>SELECT … FROM … LOCK IN SHARE
+ MODE</literal> sets shared next-key locks on all index
+ records the read encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>SELECT ... FROM ... FOR UPDATE</literal> sets
- exclusive next-key locks on all index records the read
+ <literal>SELECT … FROM … FOR UPDATE</literal>
+ sets exclusive next-key locks on all index records the read
encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>INSERT INTO ... VALUES (...)</literal> sets an
- exclusive lock on the inserted row. Note that this lock is
- not a next-key lock and does not prevent other users from
- inserting to the gap before the inserted row. If a
+ <literal>INSERT INTO … VALUES (…)</literal>
+ sets an exclusive lock on the inserted row. Note that this
+ lock is not a next-key lock and does not prevent other users
+ from inserting to the gap before the inserted row. If a
duplicate-key error occurs, a shared lock on the duplicate
index record is set.
</para>
@@ -4240,23 +4242,23 @@
<listitem>
<para>
- <literal>INSERT INTO T SELECT ... FROM S WHERE ...</literal>
- sets an exclusive (non-next-key) lock on each row inserted
- into <literal>T</literal>. It does the search on
- <literal>S</literal> as a consistent read, but sets shared
- next-key locks on <literal>S</literal> if MySQL binary
- logging is turned on. <literal>InnoDB</literal> has to set
- locks in the latter case: In roll-forward recovery from a
- backup, every SQL statement has to be executed in exactly
- the same way it was done originally.
+ <literal>INSERT INTO T SELECT … FROM S WHERE
+ …</literal> sets an exclusive (non-next-key) lock on
+ each row inserted into <literal>T</literal>. It does the
+ search on <literal>S</literal> as a consistent read, but
+ sets shared next-key locks on <literal>S</literal> if MySQL
+ binary logging is turned on. <literal>InnoDB</literal> has
+ to set locks in the latter case: In roll-forward recovery
+ from a backup, every SQL statement has to be executed in
+ exactly the same way it was done originally.
</para>
</listitem>
<listitem>
<para>
- <literal>CREATE TABLE ... SELECT ...</literal> performs the
- <literal>SELECT</literal> as a consistent read or with
- shared locks, as in the previous item.
+ <literal>CREATE TABLE … SELECT …</literal>
+ performs the <literal>SELECT</literal> as a consistent read
+ or with shared locks, as in the previous item.
</para>
</listitem>
@@ -4270,15 +4272,16 @@
<listitem>
<para>
- <literal>UPDATE ... WHERE ...</literal> sets an exclusive
- next-key lock on every record the search encounters.
+ <literal>UPDATE … WHERE …</literal> sets an
+ exclusive next-key lock on every record the search
+ encounters.
</para>
</listitem>
<listitem>
<para>
- <literal>DELETE FROM ... WHERE ...</literal> sets an
- exclusive next-key lock on every record the search
+ <literal>DELETE FROM … WHERE …</literal> sets
+ an exclusive next-key lock on every record the search
encounters.
</para>
</listitem>
@@ -4493,8 +4496,8 @@
<listitem>
<para>
- If you are using locking reads (<literal>SELECT ... FOR
- UPDATE</literal> or <literal>... LOCK IN SHARE
+ If you are using locking reads (<literal>SELECT … FOR
+ UPDATE</literal> or <literal>… LOCK IN SHARE
MODE</literal>), try using a lower isolation level such as
<literal>READ COMMITTED</literal>.
</para>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r759 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 11 Jan |
