List:Commits« Previous MessageNext Message »
From:paul Date:January 23 2006 9:26pm
Subject:svn commit - mysqldoc@docsrva: r1004 - in trunk: . refman-4.1 refman-5.0 refman-5.1
View as plain text  
Author: paul
Date: 2006-01-23 22:26:35 +0100 (Mon, 23 Jan 2006)
New Revision: 1004

Log:
 r2447@kite-hub:  paul | 2006-01-23 14:18:33 -0600
 Move section.


Modified:
   trunk/
   trunk/refman-4.1/database-administration.xml
   trunk/refman-5.0/database-administration.xml
   trunk/refman-5.1/database-administration.xml


Property changes on: trunk
___________________________________________________________________
Name: svk:merge
   - b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6574
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2446
   + b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6574
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2447

Modified: trunk/refman-4.1/database-administration.xml
===================================================================
--- trunk/refman-4.1/database-administration.xml	2006-01-23 21:26:18 UTC (rev 1003)
+++ trunk/refman-4.1/database-administration.xml	2006-01-23 21:26:35 UTC (rev 1004)
@@ -1977,483 +1977,6 @@
 
       </refsection>
 
-      <refsection id="server-sql-mode">
-
-        <title>&title-server-sql-mode;</title>
-
-        <para>
-          The MySQL server can operate in different SQL modes, and (as
-          of MySQL 4.1) can apply these modes differentially for
-          different clients. This enables each application to tailor the
-          server's operating mode to its own requirements.
-        </para>
-
-        <para>
-          Modes define what SQL syntax MySQL should support and what
-          kind of data validation checks it should perform. This makes
-          it easier to use MySQL in different environments and to use
-          MySQL together with other database servers.
-        </para>
-
-        <para>
-          You can set the default SQL mode by starting
-          <command>mysqld</command> with the
-          <option>--sql-mode="<replaceable>modes</replaceable>"</option>
-          option. The value also can be empty
-          (<option>--sql-mode=""</option>) if you want to reset it.
-        </para>
-
-        <para>
-          Beginning with MySQL 4.1, you can also change the SQL mode
-          after startup time by setting the <literal>sql_mode</literal>
-          variable with a <literal>SET [SESSION|GLOBAL]
-          sql_mode='<replaceable>modes</replaceable>'</literal>
-          statement. Setting the <literal>GLOBAL</literal> variable
-          requires the <literal>SUPER</literal> privilege and affects
-          the operation of all clients that connect from that time on.
-          Setting the <literal>SESSION</literal> variable affects only
-          the current client. Any client can change its session
-          <literal>sql_mode</literal> value.
-        </para>
-
-        <para>
-          <replaceable>modes</replaceable> is a list of different modes
-          separated by comma (&lsquo;<literal>,</literal>&rsquo;)
-          characters. You can retrieve the current mode by issuing a
-          <literal>SELECT @@sql_mode</literal> statement. The default
-          value is empty (no modes set).
-        </para>
-
-        <indexterm>
-          <primary>ANSI SQL mode</primary>
-        </indexterm>
-
-        <para>
-          The most important <literal>sql_mode</literal> value is
-          <literal>ANSI</literal>, which changes syntax and behavior to
-          be more conformant to standard SQL. This mode is available
-          beginning in MySQL 4.1.1
-        </para>
-
-        <para>
-          The following list describes all the supported modes:
-        </para>
-
-        <itemizedlist>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ANSI_QUOTES SQL mode</primary>
-              </indexterm>
-
-              <literal>ANSI_QUOTES</literal>
-            </para>
-
-            <para>
-              Treat &lsquo;<literal>"</literal>&rsquo; as an identifier
-              quote character (like the
-              &lsquo;<literal>`</literal>&rsquo; quote character) and
-              not as a string quote character. You can still use
-              &lsquo;<literal>`</literal>&rsquo; to quote identifiers in
-              ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
-              you cannot use double quotes to quote a literal string,
-              because it is interpreted as an identifier. (New in MySQL
-              4.0.0)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>IGNORE_SPACE SQL mode</primary>
-              </indexterm>
-
-              <literal>IGNORE_SPACE</literal>
-            </para>
-
-            <para>
-              Allow spaces between a function name and the
-              &lsquo;<literal>(</literal>&rsquo; character. This forces
-              all function names to be treated as reserved words. As a
-              result, if you want to access any database, table, or
-              column name that is a reserved word, you must quote it.
-              For example, because there is a <literal>USER()</literal>
-              function, the name of the <literal>user</literal> table in
-              the <literal>mysql</literal> database and the
-              <literal>User</literal> column in that table become
-              reserved, so you must quote them:
-            </para>
-
-<programlisting>
-SELECT "User" FROM mysql."user";
-</programlisting>
-
-            <para>
-              (New in MySQL 4.0.0)
-            </para>
-
-            <para>
-              See <xref linkend="myisam-start"/>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal>
-            </para>
-
-            <para>
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
-              of <literal>AUTO_INCREMENT</literal> columns. Normally,
-              you generate the next sequence number for the column by
-              inserting either <literal>NULL</literal> or
-              <literal>0</literal> into it.
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
-              behavior for <literal>0</literal> so that only
-              <literal>NULL</literal> generates the next sequence
-              number. (New in MySQL 4.1.1)
-            </para>
-
-            <para>
-              This mode can be useful if <literal>0</literal> has been
-              stored in a table's <literal>AUTO_INCREMENT</literal>
-              column. (Storing <literal>0</literal> is not a recommended
-              practice, by the way.) For example, if you dump the table
-              with <command>mysqldump</command> and then reload it,
-              MySQL normally generates new sequence numbers when it
-              encounters the <literal>0</literal> values, resulting in a
-              table with contents different from the one that was
-              dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
-              before reloading the dump file solves this problem. As of
-              MySQL 4.1.1, <command>mysqldump</command> automatically
-              includes a statement in the dump output that enables
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
-              problem..
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_DIR_IN_CREATE SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_DIR_IN_CREATE</literal>
-            </para>
-
-            <para>
-              When creating a table, ignore all <literal>INDEX
-              DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
-              directives. This option is useful on slave replication
-              servers. (New in MySQL 4.0.15)
-            </para>
-          </listitem>
-
-<!--
-        <listitem>
-          <remark role="todo">
-            [js]  Does not appear to be available in 4.1?
-          </remark>
-          
-          <para>
-            <literal>NO_ENGINE_SUBSTITUTION</literal>
-          </para>
-
-          <para>
-            Prevents automatic substitution of storage engine when the
-            requested storage engine is disabled or not compiled in.
-          </para>
-        </listitem>
--->
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_FIELD_OPTIONS SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_FIELD_OPTIONS</literal>
-            </para>
-
-            <para>
-              do not print MySQL-specific column options in the output
-              of <literal>SHOW CREATE TABLE</literal>. This mode is used
-              by <command>mysqldump</command> in portability mode. (New
-              in MySQL 4.1.1)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_KEY_OPTIONS SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_KEY_OPTIONS</literal>
-            </para>
-
-            <para>
-              do not print MySQL-specific index options in the output of
-              <literal>SHOW CREATE TABLE</literal>. This mode is used by
-              <command>mysqldump</command> in portability mode. (New in
-              MySQL 4.1.1)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_TABLE_OPTIONS SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_TABLE_OPTIONS</literal>
-            </para>
-
-            <para>
-              do not print MySQL-specific table options (such as
-              <literal>ENGINE</literal>) in the output of <literal>SHOW
-              CREATE TABLE</literal>. This mode is used by
-              <command>mysqldump</command> in portability mode. (New in
-              MySQL 4.1.1)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_UNSIGNED_SUBTRACTION</literal>
-            </para>
-
-            <para>
-              In subtraction operations, do not mark the result as
-              <literal>UNSIGNED</literal> if one of the operands is
-              unsigned. Note that this makes <literal>UNSIGNED
-              BIGINT</literal> not 100% usable in all contexts. See
-              <xref linkend="cast-functions"/>. (New in MySQL 4.0.2)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
-              </indexterm>
-
-              <literal>ONLY_FULL_GROUP_BY</literal>
-            </para>
-
-            <para>
-              Do not allow queries that in the <literal>GROUP
-              BY</literal> part refer to a not selected column. (New in
-              MySQL 4.0.0)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>PIPES_AS_CONCAT SQL mode</primary>
-              </indexterm>
-
-              <literal>PIPES_AS_CONCAT</literal>
-            </para>
-
-            <para>
-              Treat <literal>||</literal> as a string concatenation
-              operator (same as <literal>CONCAT()</literal>) rather than
-              as a synonym for <literal>OR</literal>. (New in MySQL
-              4.0.0)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>REAL_AS_FLOAT SQL mode</primary>
-              </indexterm>
-
-              <literal>REAL_AS_FLOAT</literal>
-            </para>
-
-            <para>
-              Treat <literal>REAL</literal> as a synonym for
-              <literal>FLOAT</literal> rather than as a synonym for
-              <literal>DOUBLE</literal>. (New in MySQL 4.0.0)
-            </para>
-          </listitem>
-
-        </itemizedlist>
-
-        <para>
-          The following special modes are provided as shorthand for
-          combinations of mode values from the preceding list. All are
-          available as of MySQL 4.1.1.
-        </para>
-
-        <para>
-          The descriptions include all mode values that are available in
-          the most recent version of MySQL. For older versions, a
-          combination mode does not include individual mode values that
-          are not available except in newer versions.
-        </para>
-
-        <itemizedlist>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ANSI SQL mode</primary>
-              </indexterm>
-
-              <literal>ANSI</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>REAL_AS_FLOAT</literal>,
-              <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>. Before MySQL 4.1.11,
-              <literal>ANSI</literal> also includes
-              <literal>ONLY_FULL_GROUP_BY</literal>. See
-              <xref linkend="ansi-mode"/>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>DB2 SQL mode</primary>
-              </indexterm>
-
-              <literal>DB2</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MAXDB SQL mode</primary>
-              </indexterm>
-
-              <literal>MAXDB</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MSSQL SQL mode</primary>
-              </indexterm>
-
-              <literal>MSSQL</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MYSQL323 SQL mode</primary>
-              </indexterm>
-
-              <literal>MYSQL323</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MYSQL40 SQL mode</primary>
-              </indexterm>
-
-              <literal>MYSQL40</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ORACLE SQL mode</primary>
-              </indexterm>
-
-              <literal>ORACLE</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>POSTGRESQL SQL mode</primary>
-              </indexterm>
-
-              <literal>POSTGRESQL</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-        </itemizedlist>
-
-      </refsection>
-
       <refsection id="server-system-variables">
 
         <title>&title-server-system-variables;</title>
@@ -7597,6 +7120,483 @@
 
       </refsection>
 
+      <refsection id="server-sql-mode">
+
+        <title>&title-server-sql-mode;</title>
+
+        <para>
+          The MySQL server can operate in different SQL modes, and (as
+          of MySQL 4.1) can apply these modes differentially for
+          different clients. This enables each application to tailor the
+          server's operating mode to its own requirements.
+        </para>
+
+        <para>
+          Modes define what SQL syntax MySQL should support and what
+          kind of data validation checks it should perform. This makes
+          it easier to use MySQL in different environments and to use
+          MySQL together with other database servers.
+        </para>
+
+        <para>
+          You can set the default SQL mode by starting
+          <command>mysqld</command> with the
+          <option>--sql-mode="<replaceable>modes</replaceable>"</option>
+          option. The value also can be empty
+          (<option>--sql-mode=""</option>) if you want to reset it.
+        </para>
+
+        <para>
+          Beginning with MySQL 4.1, you can also change the SQL mode
+          after startup time by setting the <literal>sql_mode</literal>
+          variable with a <literal>SET [SESSION|GLOBAL]
+          sql_mode='<replaceable>modes</replaceable>'</literal>
+          statement. Setting the <literal>GLOBAL</literal> variable
+          requires the <literal>SUPER</literal> privilege and affects
+          the operation of all clients that connect from that time on.
+          Setting the <literal>SESSION</literal> variable affects only
+          the current client. Any client can change its session
+          <literal>sql_mode</literal> value.
+        </para>
+
+        <para>
+          <replaceable>modes</replaceable> is a list of different modes
+          separated by comma (&lsquo;<literal>,</literal>&rsquo;)
+          characters. You can retrieve the current mode by issuing a
+          <literal>SELECT @@sql_mode</literal> statement. The default
+          value is empty (no modes set).
+        </para>
+
+        <indexterm>
+          <primary>ANSI SQL mode</primary>
+        </indexterm>
+
+        <para>
+          The most important <literal>sql_mode</literal> value is
+          <literal>ANSI</literal>, which changes syntax and behavior to
+          be more conformant to standard SQL. This mode is available
+          beginning in MySQL 4.1.1
+        </para>
+
+        <para>
+          The following list describes all the supported modes:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ANSI_QUOTES SQL mode</primary>
+              </indexterm>
+
+              <literal>ANSI_QUOTES</literal>
+            </para>
+
+            <para>
+              Treat &lsquo;<literal>"</literal>&rsquo; as an identifier
+              quote character (like the
+              &lsquo;<literal>`</literal>&rsquo; quote character) and
+              not as a string quote character. You can still use
+              &lsquo;<literal>`</literal>&rsquo; to quote identifiers in
+              ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
+              you cannot use double quotes to quote a literal string,
+              because it is interpreted as an identifier. (New in MySQL
+              4.0.0)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>IGNORE_SPACE SQL mode</primary>
+              </indexterm>
+
+              <literal>IGNORE_SPACE</literal>
+            </para>
+
+            <para>
+              Allow spaces between a function name and the
+              &lsquo;<literal>(</literal>&rsquo; character. This forces
+              all function names to be treated as reserved words. As a
+              result, if you want to access any database, table, or
+              column name that is a reserved word, you must quote it.
+              For example, because there is a <literal>USER()</literal>
+              function, the name of the <literal>user</literal> table in
+              the <literal>mysql</literal> database and the
+              <literal>User</literal> column in that table become
+              reserved, so you must quote them:
+            </para>
+
+<programlisting>
+SELECT "User" FROM mysql."user";
+</programlisting>
+
+            <para>
+              (New in MySQL 4.0.0)
+            </para>
+
+            <para>
+              See <xref linkend="myisam-start"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+            </para>
+
+            <para>
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
+              of <literal>AUTO_INCREMENT</literal> columns. Normally,
+              you generate the next sequence number for the column by
+              inserting either <literal>NULL</literal> or
+              <literal>0</literal> into it.
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
+              behavior for <literal>0</literal> so that only
+              <literal>NULL</literal> generates the next sequence
+              number. (New in MySQL 4.1.1)
+            </para>
+
+            <para>
+              This mode can be useful if <literal>0</literal> has been
+              stored in a table's <literal>AUTO_INCREMENT</literal>
+              column. (Storing <literal>0</literal> is not a recommended
+              practice, by the way.) For example, if you dump the table
+              with <command>mysqldump</command> and then reload it,
+              MySQL normally generates new sequence numbers when it
+              encounters the <literal>0</literal> values, resulting in a
+              table with contents different from the one that was
+              dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+              before reloading the dump file solves this problem. As of
+              MySQL 4.1.1, <command>mysqldump</command> automatically
+              includes a statement in the dump output that enables
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
+              problem..
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_DIR_IN_CREATE SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_DIR_IN_CREATE</literal>
+            </para>
+
+            <para>
+              When creating a table, ignore all <literal>INDEX
+              DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
+              directives. This option is useful on slave replication
+              servers. (New in MySQL 4.0.15)
+            </para>
+          </listitem>
+
+<!--
+        <listitem>
+          <remark role="todo">
+            [js]  Does not appear to be available in 4.1?
+          </remark>
+          
+          <para>
+            <literal>NO_ENGINE_SUBSTITUTION</literal>
+          </para>
+
+          <para>
+            Prevents automatic substitution of storage engine when the
+            requested storage engine is disabled or not compiled in.
+          </para>
+        </listitem>
+-->
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_FIELD_OPTIONS SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_FIELD_OPTIONS</literal>
+            </para>
+
+            <para>
+              do not print MySQL-specific column options in the output
+              of <literal>SHOW CREATE TABLE</literal>. This mode is used
+              by <command>mysqldump</command> in portability mode. (New
+              in MySQL 4.1.1)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_KEY_OPTIONS SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_KEY_OPTIONS</literal>
+            </para>
+
+            <para>
+              do not print MySQL-specific index options in the output of
+              <literal>SHOW CREATE TABLE</literal>. This mode is used by
+              <command>mysqldump</command> in portability mode. (New in
+              MySQL 4.1.1)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_TABLE_OPTIONS SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_TABLE_OPTIONS</literal>
+            </para>
+
+            <para>
+              do not print MySQL-specific table options (such as
+              <literal>ENGINE</literal>) in the output of <literal>SHOW
+              CREATE TABLE</literal>. This mode is used by
+              <command>mysqldump</command> in portability mode. (New in
+              MySQL 4.1.1)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_UNSIGNED_SUBTRACTION</literal>
+            </para>
+
+            <para>
+              In subtraction operations, do not mark the result as
+              <literal>UNSIGNED</literal> if one of the operands is
+              unsigned. Note that this makes <literal>UNSIGNED
+              BIGINT</literal> not 100% usable in all contexts. See
+              <xref linkend="cast-functions"/>. (New in MySQL 4.0.2)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
+              </indexterm>
+
+              <literal>ONLY_FULL_GROUP_BY</literal>
+            </para>
+
+            <para>
+              Do not allow queries that in the <literal>GROUP
+              BY</literal> part refer to a not selected column. (New in
+              MySQL 4.0.0)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>PIPES_AS_CONCAT SQL mode</primary>
+              </indexterm>
+
+              <literal>PIPES_AS_CONCAT</literal>
+            </para>
+
+            <para>
+              Treat <literal>||</literal> as a string concatenation
+              operator (same as <literal>CONCAT()</literal>) rather than
+              as a synonym for <literal>OR</literal>. (New in MySQL
+              4.0.0)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>REAL_AS_FLOAT SQL mode</primary>
+              </indexterm>
+
+              <literal>REAL_AS_FLOAT</literal>
+            </para>
+
+            <para>
+              Treat <literal>REAL</literal> as a synonym for
+              <literal>FLOAT</literal> rather than as a synonym for
+              <literal>DOUBLE</literal>. (New in MySQL 4.0.0)
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          The following special modes are provided as shorthand for
+          combinations of mode values from the preceding list. All are
+          available as of MySQL 4.1.1.
+        </para>
+
+        <para>
+          The descriptions include all mode values that are available in
+          the most recent version of MySQL. For older versions, a
+          combination mode does not include individual mode values that
+          are not available except in newer versions.
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ANSI SQL mode</primary>
+              </indexterm>
+
+              <literal>ANSI</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>REAL_AS_FLOAT</literal>,
+              <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>. Before MySQL 4.1.11,
+              <literal>ANSI</literal> also includes
+              <literal>ONLY_FULL_GROUP_BY</literal>. See
+              <xref linkend="ansi-mode"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>DB2 SQL mode</primary>
+              </indexterm>
+
+              <literal>DB2</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MAXDB SQL mode</primary>
+              </indexterm>
+
+              <literal>MAXDB</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MSSQL SQL mode</primary>
+              </indexterm>
+
+              <literal>MSSQL</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MYSQL323 SQL mode</primary>
+              </indexterm>
+
+              <literal>MYSQL323</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MYSQL40 SQL mode</primary>
+              </indexterm>
+
+              <literal>MYSQL40</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ORACLE SQL mode</primary>
+              </indexterm>
+
+              <literal>ORACLE</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>POSTGRESQL SQL mode</primary>
+              </indexterm>
+
+              <literal>POSTGRESQL</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </refsection>
+
       <refsection id="server-shutdown">
 
         <title>&title-server-shutdown;</title>

Modified: trunk/refman-5.0/database-administration.xml
===================================================================
--- trunk/refman-5.0/database-administration.xml	2006-01-23 21:26:18 UTC (rev 1003)
+++ trunk/refman-5.0/database-administration.xml	2006-01-23 21:26:35 UTC (rev 1004)
@@ -1941,832 +1941,6 @@
 
       </refsection>
 
-      <refsection id="server-sql-mode">
-
-        <title>&title-server-sql-mode;</title>
-
-        <para>
-          The MySQL server can operate in different SQL modes, and can
-          apply these modes differently for different clients. This
-          enables each application to tailor the server's operating mode
-          to its own requirements.
-        </para>
-
-        <para>
-          Modes define what SQL syntax MySQL should support and what
-          kind of data validation checks it should perform. This makes
-          it easier to use MySQL in different environments and to use
-          MySQL together with other database servers.
-        </para>
-
-        <para>
-          You can set the default SQL mode by starting
-          <command>mysqld</command> with the
-          <option>--sql-mode="<replaceable>modes</replaceable>"</option>
-          option. The value also can be empty
-          (<option>--sql-mode=""</option>) if you want to reset it.
-        </para>
-
-        <para>
-          You can also change the SQL mode after startup time by setting
-          the <literal>sql_mode</literal> variable using a <literal>SET
-          [SESSION|GLOBAL]
-          sql_mode='<replaceable>modes</replaceable>'</literal>
-          statement. Setting the <literal>GLOBAL</literal> variable
-          requires the <literal>SUPER</literal> privilege and affects
-          the operation of all clients that connect from that time on.
-          Setting the <literal>SESSION</literal> variable affects only
-          the current client. Any client can change its own session
-          <literal>sql_mode</literal> value at any time.
-        </para>
-
-        <para>
-          <replaceable>modes</replaceable> is a list of different modes
-          separated by comma (&lsquo;<literal>,</literal>&rsquo;)
-          characters. You can retrieve the current mode by issuing a
-          <literal>SELECT @@sql_mode</literal> statement. The default
-          value is empty (no modes set).
-        </para>
-
-        <para>
-          The most important <literal>sql_mode</literal> values are
-          probably these:
-        </para>
-
-        <itemizedlist>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ANSI SQL mode</primary>
-              </indexterm>
-
-              <literal>ANSI</literal>
-            </para>
-
-            <para>
-              Change syntax and behavior to be more conformant to
-              standard SQL.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>STRICT_TRANS_TABLES SQL mode</primary>
-              </indexterm>
-
-              <literal>STRICT_TRANS_TABLES</literal>
-            </para>
-
-            <para>
-              If a value could not be inserted as given into a
-              transactional table, abort the statement. For a
-              non-transactional table, abort the statement if the value
-              occurs in a single-row statement or the first row of a
-              multiple-row statement. More detail is given later in this
-              section. (Implemented in MySQL 5.0.2)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>TRADITIONAL SQL mode</primary>
-              </indexterm>
-
-              <literal>TRADITIONAL</literal>
-            </para>
-
-            <para>
-              Make MySQL behave like a <quote>traditional</quote> SQL
-              database system. A simple description of this mode is
-              <quote>give an error instead of a warning</quote> when
-              inserting an incorrect value into a column.
-              <emphasis role="bold">Note</emphasis>: The
-              <literal>INSERT</literal>/<literal>UPDATE</literal> aborts
-              as soon as the error is noticed. This may not be what you
-              want if you are using a non-transactional storage engine,
-              because data changes made prior to the error are not be
-              rolled back, resulting in a <quote>partially done</quote>
-              update. (Added in MySQL 5.0.2)
-            </para>
-          </listitem>
-
-        </itemizedlist>
-
-        <indexterm>
-          <primary>STRICT SQL mode</primary>
-        </indexterm>
-
-        <para>
-          When this manual refers to <quote>strict mode,</quote> it
-          means a mode where at least one of
-          <literal>STRICT_TRANS_TABLES</literal> or
-          <literal>STRICT_ALL_TABLES</literal> is enabled.
-        </para>
-
-        <para>
-          The following list describes all supported modes:
-        </para>
-
-        <itemizedlist>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ALLOW_INVALID_DATES SQL mode</primary>
-              </indexterm>
-
-              <literal>ALLOW_INVALID_DATES</literal>
-            </para>
-
-            <para>
-              Don't do full checking of dates. Check only that the month
-              is in the range from 1 to 12 and the day is in the range
-              from 1 to 31. This is very convenient for Web applications
-              where you obtain year, month, and day in three different
-              fields and you want to store exactly what the user
-              inserted (without date validation). This mode applies to
-              <literal>DATE</literal> and <literal>DATETIME</literal>
-              columns. It does not apply <literal>TIMESTAMP</literal>
-              columns, which always require a valid date.
-            </para>
-
-            <para>
-              This mode is implemented in MySQL 5.0.2. Before 5.0.2,
-              this was the default MySQL date-handling mode. As of
-              5.0.2, the server requires that month and day values be
-              legal, and not merely in the range 1 to 12 and 1 to 31,
-              respectively. With strict mode disabled, invalid dates
-              such as <literal>'2004-04-31'</literal> are converted to
-              <literal>'0000-00-00'</literal> and a warning is
-              generated. With strict mode enabled, invalid dates
-              generate an error. To allow such dates, enable
-              <literal>ALLOW_INVALID_DATES</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ANSI_QUOTES SQL mode</primary>
-              </indexterm>
-
-              <literal>ANSI_QUOTES</literal>
-            </para>
-
-            <para>
-              Treat &lsquo;<literal>"</literal>&rsquo; as an identifier
-              quote character (like the
-              &lsquo;<literal>`</literal>&rsquo; quote character) and
-              not as a string quote character. You can still use
-              &lsquo;<literal>`</literal>&rsquo; to quote identifiers in
-              ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
-              you cannot use double quotes to quote a literal string,
-              because it is interpreted as an identifier.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ERROR_FOR_DIVISION_BY_ZERO SQL mode</primary>
-              </indexterm>
-
-              <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>
-            </para>
-
-            <para>
-              Produce an error in strict mode (otherwise a warning) when
-              we encounter a division by zero (or
-              <literal>MOD(X,0)</literal>) during an
-              <literal>INSERT</literal> or <literal>UPDATE</literal>. If
-              this mode is not given, MySQL instead returns
-              <literal>NULL</literal> for divisions by zero. If used in
-              <literal>INSERT IGNORE</literal> or <literal>UPDATE
-              IGNORE</literal>, MySQL generates a warning for divisions
-              by zero, but the result of the operation is
-              <literal>NULL</literal>. (Implemented in MySQL 5.0.2)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>HIGH_NOT_PRECEDENCE SQL mode</primary>
-              </indexterm>
-
-              <literal>HIGH_NOT_PRECEDENCE</literal>
-            </para>
-
-            <para>
-              From MySQL 5.0.2 on, the precedence of the
-              <literal>NOT</literal> operator is such that expressions
-              such as <literal>NOT a BETWEEN b AND c</literal> are
-              parsed as <literal>NOT (a BETWEEN b AND c)</literal>.
-              Before MySQL 5.0.2, the expression is parsed as
-              <literal>(NOT a) BETWEEN b AND c</literal>. The old
-              higher-precedence behavior can be obtained by enabling the
-              <literal>HIGH_NOT_PRECEDENCE</literal> SQL mode. (Added in
-              MySQL 5.0.2)
-            </para>
-
-<programlisting>
-mysql&gt; <userinput>SET sql_mode = '';</userinput>
-mysql&gt; <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
-        -&gt; 0
-mysql&gt; <userinput>SET sql_mode = 'broken_not';</userinput>
-mysql&gt; <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
-        -&gt; 1
-</programlisting>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>IGNORE_SPACE SQL mode</primary>
-              </indexterm>
-
-              <literal>IGNORE_SPACE</literal>
-            </para>
-
-            <para>
-              Allow spaces between a function name and the
-              &lsquo;<literal>(</literal>&rsquo; character. This forces
-              all function names to be treated as reserved words. As a
-              result, if you want to access any database, table, or
-              column name that is a reserved word, you must quote it.
-              For example, because there is a <literal>USER()</literal>
-              function, the name of the <literal>user</literal> table in
-              the <literal>mysql</literal> database and the
-              <literal>User</literal> column in that table become
-              reserved, so you must quote them:
-            </para>
-
-<programlisting>
-SELECT "User" FROM mysql."user";
-</programlisting>
-
-            <para>
-              The <literal>IGNORE_SPACE</literal> SQL mode applies to
-              built-in functions, not to stored routines. it is always
-              allowable to have spaces after a routine name, regardless
-              of whether <literal>IGNORE_SPACE</literal> is enabled.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_AUTO_CREATE_USER SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_AUTO_CREATE_USER</literal>
-            </para>
-
-            <para>
-              Prevent <literal>GRANT</literal> from automatically
-              creating new users if it would otherwise do so, unless a
-              non-empty password also is specified. (Added in MySQL
-              5.0.2)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal>
-            </para>
-
-            <para>
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
-              of <literal>AUTO_INCREMENT</literal> columns. Normally,
-              you generate the next sequence number for the column by
-              inserting either <literal>NULL</literal> or
-              <literal>0</literal> into it.
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
-              behavior for <literal>0</literal> so that only
-              <literal>NULL</literal> generates the next sequence
-              number.
-            </para>
-
-            <para>
-              This mode can be useful if <literal>0</literal> has been
-              stored in a table's <literal>AUTO_INCREMENT</literal>
-              column. (Storing <literal>0</literal> is not a recommended
-              practice, by the way.) For example, if you dump the table
-              with <command>mysqldump</command> and then reload it,
-              MySQL normally generates new sequence numbers when it
-              encounters the <literal>0</literal> values, resulting in a
-              table with contents different from the one that was
-              dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
-              before reloading the dump file solves this problem.
-              <command>mysqldump</command> now automatically includes in
-              its output a statement that enables
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
-              problem.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_BACKSLASH_ESCAPES SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_BACKSLASH_ESCAPES</literal>
-            </para>
-
-            <para>
-              Disable the use of the backslash character
-              (&lsquo;<literal>\</literal>&rsquo;) as an escape
-              character within strings. With this mode enabled,
-              backslash becomes any ordinary character like any other.
-              (Implemented in MySQL 5.0.1)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_DIR_IN_CREATE SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_DIR_IN_CREATE</literal>
-            </para>
-
-            <para>
-              When creating a table, ignore all <literal>INDEX
-              DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
-              directives. This option is useful on slave replication
-              servers.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <literal>NO_ENGINE_SUBSTITUTION</literal>
-            </para>
-
-            <para>
-              Prevents automatic substitution of storage engine when the
-              requested storage engine is disabled or not compiled in.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_FIELD_OPTIONS SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_FIELD_OPTIONS</literal>
-            </para>
-
-            <para>
-              Don't print MySQL-specific column options in the output of
-              <literal>SHOW CREATE TABLE</literal>. This mode is used by
-              <command>mysqldump</command> in portability mode.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_KEY_OPTIONS SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_KEY_OPTIONS</literal>
-            </para>
-
-            <para>
-              Don't print MySQL-specific index options in the output of
-              <literal>SHOW CREATE TABLE</literal>. This mode is used by
-              <command>mysqldump</command> in portability mode.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_TABLE_OPTIONS SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_TABLE_OPTIONS</literal>
-            </para>
-
-            <para>
-              Don't print MySQL-specific table options (such as
-              <literal>ENGINE</literal>) in the output of <literal>SHOW
-              CREATE TABLE</literal>. This mode is used by
-              <command>mysqldump</command> in portability mode.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_UNSIGNED_SUBTRACTION</literal>
-            </para>
-
-            <para>
-              In subtraction operations, don't mark the result as
-              <literal>UNSIGNED</literal> if one of the operands is
-              unsigned. Note that this makes <literal>UNSIGNED
-              BIGINT</literal> not 100% usable in all contexts. See
-              <xref linkend="cast-functions"/>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_ZERO_DATE SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_ZERO_DATE</literal>
-            </para>
-
-            <para>
-              In strict mode, don't allow
-              <literal>'0000-00-00'</literal> as a valid date. You can
-              still insert zero dates with the <literal>IGNORE</literal>
-              option. When not in strict mode, the date is accepted but
-              a warning is generated. (Added in MySQL 5.0.2)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_ZERO_IN_DATE SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_ZERO_IN_DATE</literal>
-            </para>
-
-            <para>
-              In strict mode, don't accept dates where the month or day
-              part is 0. If used with the <literal>IGNORE</literal>
-              option, we insert a <literal>'0000-00-00'</literal> date
-              for any such date. When not in strict mode, the date is
-              accepted but a warning is generated. (Added in MySQL
-              5.0.2)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
-              </indexterm>
-
-              <literal>ONLY_FULL_GROUP_BY</literal>
-            </para>
-
-            <para>
-              Do not allow queries that in the <literal>GROUP
-              BY</literal> part refer to a column that is not selected.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>PIPES_AS_CONCAT SQL mode</primary>
-              </indexterm>
-
-              <literal>PIPES_AS_CONCAT</literal>
-            </para>
-
-            <para>
-              Treat <literal>||</literal> as a string concatenation
-              operator (same as <literal>CONCAT()</literal>) rather than
-              as a synonym for <literal>OR</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>REAL_AS_FLOAT SQL mode</primary>
-              </indexterm>
-
-              <literal>REAL_AS_FLOAT</literal>
-            </para>
-
-            <para>
-              Treat <literal>REAL</literal> as a synonym for
-              <literal>FLOAT</literal> rather than as a synonym for
-              <literal>DOUBLE</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>STRICT_ALL_TABLES SQL mode</primary>
-              </indexterm>
-
-              <literal>STRICT_ALL_TABLES</literal>
-            </para>
-
-            <para>
-              Enable strict mode for all storage engines. Invalid data
-              values are rejected. Additional detail follows. (Added in
-              MySQL 5.0.2)
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>STRICT_TRANS_TABLES SQL mode</primary>
-              </indexterm>
-
-              <literal>STRICT_TRANS_TABLES</literal>
-            </para>
-
-            <para>
-              Enable strict mode for transactional storage engines, and
-              when possible for non-transactional storage engines.
-              Additional details follow. (Implemented in MySQL 5.0.2)
-            </para>
-          </listitem>
-
-        </itemizedlist>
-
-        <para>
-          Strict mode controls how MySQL handles input values that are
-          invalid or missing. A value can be invalid for several
-          reasons. For example, it might have the wrong data type for
-          the column, or it might be out of range. A value is missing
-          when a new row to be inserted does not contain a value for a
-          column that has no explicit <literal>DEFAULT</literal> clause
-          in its definition.
-        </para>
-
-        <para>
-          For transactional tables, an error occurs for invalid or
-          missing values in a statement when either of the
-          <literal>STRICT_ALL_TABLES</literal> or
-          <literal>STRICT_TRANS_TABLES</literal> modes are enabled. The
-          statement is aborted and rolled back.
-        </para>
-
-        <para>
-          For non-transactional tables, the behavior is the same for
-          either mode, if the bad value occurs in the first row to be
-          inserted or updated. The statement is aborted and the table
-          remains unchanged. If the statement inserts or modifies
-          multiple rows and the bad value occurs in the second or later
-          row, the result depends on which strict option is enabled:
-        </para>
-
-        <itemizedlist>
-
-          <listitem>
-            <para>
-              For <literal>STRICT_ALL_TABLES</literal>, MySQL returns an
-              error and ignores the rest of the rows. However, in this
-              case, the earlier rows still have been inserted or
-              updated. This means that you might get a partial update,
-              which might not be what you want. To avoid this, it's best
-              to use single-row statements because these can be aborted
-              without changing the table.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              For <literal>STRICT_TRANS_TABLES</literal>, MySQL converts
-              an invalid value to the closest valid value for the column
-              and insert the adjusted value. If a value is missing,
-              MySQL inserts the implicit default value for the column
-              data type. In either case, MySQL generates a warning
-              rather than an error and continues processing the
-              statement. Implicit defaults are described in
-              <xref linkend="create-table"/>.
-            </para>
-          </listitem>
-
-        </itemizedlist>
-
-        <para>
-          Strict mode disallows invalid date values such as
-          <literal>'2004-04-31'</literal>. It does not disallow dates
-          with zero parts such as <literal>2004-04-00'</literal> or
-          <quote>zero</quote> dates. To disallow these as well, enable
-          the <literal>NO_ZERO_IN_DATE</literal> and
-          <literal>NO_ZERO_DATE</literal> SQL modes in addition to
-          strict mode.
-        </para>
-
-        <para>
-          If you are not using strict mode (that is, neither
-          <literal>STRICT_TRANS_TABLES</literal> nor
-          <literal>STRICT_ALL_TABLES</literal> is enabled), MySQL
-          inserts adjusted values for invalid or missing values and
-          produces warnings. In strict mode, you can produce this
-          behavior by using <literal>INSERT IGNORE</literal> or
-          <literal>UPDATE IGNORE</literal>. See
-          <xref linkend="show-warnings"/>.
-        </para>
-
-        <para>
-          The following special modes are provided as shorthand for
-          combinations of mode values from the preceding list. All are
-          available in MySQL &current-series; beginning with version
-          5.0.0, except for <literal>TRADITIONAL</literal>, which was
-          implemented in MySQL 5.0.2.
-        </para>
-
-        <para>
-          The descriptions include all mode values that are available in
-          the most recent version of MySQL. For older versions, a
-          combination mode does not include individual mode values that
-          are not available except in newer versions.
-        </para>
-
-        <itemizedlist>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ANSI SQL mode</primary>
-              </indexterm>
-
-              <literal>ANSI</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>REAL_AS_FLOAT</literal>,
-              <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>. Before MySQL 5.0.3,
-              <literal>ANSI</literal> also includes
-              <literal>ONLY_FULL_GROUP_BY</literal>. See
-              <xref linkend="ansi-mode"/>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>DB2 SQL mode</primary>
-              </indexterm>
-
-              <literal>DB2</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MAXDB SQL mode</primary>
-              </indexterm>
-
-              <literal>MAXDB</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>,
-              <literal>NO_AUTO_CREATE_USER</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MSSQL SQL mode</primary>
-              </indexterm>
-
-              <literal>MSSQL</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MYSQL323 SQL mode</primary>
-              </indexterm>
-
-              <literal>MYSQL323</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
-              <literal>HIGH_NOT_PRECEDENCE</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MYSQL40 SQL mode</primary>
-              </indexterm>
-
-              <literal>MYSQL40</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
-              <literal>HIGH_NOT_PRECEDENCE</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ORACLE SQL mode</primary>
-              </indexterm>
-
-              <literal>ORACLE</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>,
-              <literal>NO_AUTO_CREATE_USER</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>POSTGRESQL SQL mode</primary>
-              </indexterm>
-
-              <literal>POSTGRESQL</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>TRADITIONAL SQL mode</primary>
-              </indexterm>
-
-              <literal>TRADITIONAL</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>STRICT_TRANS_TABLES</literal>,
-              <literal>STRICT_ALL_TABLES</literal>,
-              <literal>NO_ZERO_IN_DATE</literal>,
-              <literal>NO_ZERO_DATE</literal>,
-              <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>,
-              <literal>NO_AUTO_CREATE_USER</literal>.
-            </para>
-          </listitem>
-
-        </itemizedlist>
-
-      </refsection>
-
       <refsection id="server-system-variables">
 
         <title>&title-server-system-variables;</title>
@@ -8809,6 +7983,832 @@
 
       </refsection>
 
+      <refsection id="server-sql-mode">
+
+        <title>&title-server-sql-mode;</title>
+
+        <para>
+          The MySQL server can operate in different SQL modes, and can
+          apply these modes differently for different clients. This
+          enables each application to tailor the server's operating mode
+          to its own requirements.
+        </para>
+
+        <para>
+          Modes define what SQL syntax MySQL should support and what
+          kind of data validation checks it should perform. This makes
+          it easier to use MySQL in different environments and to use
+          MySQL together with other database servers.
+        </para>
+
+        <para>
+          You can set the default SQL mode by starting
+          <command>mysqld</command> with the
+          <option>--sql-mode="<replaceable>modes</replaceable>"</option>
+          option. The value also can be empty
+          (<option>--sql-mode=""</option>) if you want to reset it.
+        </para>
+
+        <para>
+          You can also change the SQL mode after startup time by setting
+          the <literal>sql_mode</literal> variable using a <literal>SET
+          [SESSION|GLOBAL]
+          sql_mode='<replaceable>modes</replaceable>'</literal>
+          statement. Setting the <literal>GLOBAL</literal> variable
+          requires the <literal>SUPER</literal> privilege and affects
+          the operation of all clients that connect from that time on.
+          Setting the <literal>SESSION</literal> variable affects only
+          the current client. Any client can change its own session
+          <literal>sql_mode</literal> value at any time.
+        </para>
+
+        <para>
+          <replaceable>modes</replaceable> is a list of different modes
+          separated by comma (&lsquo;<literal>,</literal>&rsquo;)
+          characters. You can retrieve the current mode by issuing a
+          <literal>SELECT @@sql_mode</literal> statement. The default
+          value is empty (no modes set).
+        </para>
+
+        <para>
+          The most important <literal>sql_mode</literal> values are
+          probably these:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ANSI SQL mode</primary>
+              </indexterm>
+
+              <literal>ANSI</literal>
+            </para>
+
+            <para>
+              Change syntax and behavior to be more conformant to
+              standard SQL.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>STRICT_TRANS_TABLES SQL mode</primary>
+              </indexterm>
+
+              <literal>STRICT_TRANS_TABLES</literal>
+            </para>
+
+            <para>
+              If a value could not be inserted as given into a
+              transactional table, abort the statement. For a
+              non-transactional table, abort the statement if the value
+              occurs in a single-row statement or the first row of a
+              multiple-row statement. More detail is given later in this
+              section. (Implemented in MySQL 5.0.2)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>TRADITIONAL SQL mode</primary>
+              </indexterm>
+
+              <literal>TRADITIONAL</literal>
+            </para>
+
+            <para>
+              Make MySQL behave like a <quote>traditional</quote> SQL
+              database system. A simple description of this mode is
+              <quote>give an error instead of a warning</quote> when
+              inserting an incorrect value into a column.
+              <emphasis role="bold">Note</emphasis>: The
+              <literal>INSERT</literal>/<literal>UPDATE</literal> aborts
+              as soon as the error is noticed. This may not be what you
+              want if you are using a non-transactional storage engine,
+              because data changes made prior to the error are not be
+              rolled back, resulting in a <quote>partially done</quote>
+              update. (Added in MySQL 5.0.2)
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <indexterm>
+          <primary>STRICT SQL mode</primary>
+        </indexterm>
+
+        <para>
+          When this manual refers to <quote>strict mode,</quote> it
+          means a mode where at least one of
+          <literal>STRICT_TRANS_TABLES</literal> or
+          <literal>STRICT_ALL_TABLES</literal> is enabled.
+        </para>
+
+        <para>
+          The following list describes all supported modes:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ALLOW_INVALID_DATES SQL mode</primary>
+              </indexterm>
+
+              <literal>ALLOW_INVALID_DATES</literal>
+            </para>
+
+            <para>
+              Don't do full checking of dates. Check only that the month
+              is in the range from 1 to 12 and the day is in the range
+              from 1 to 31. This is very convenient for Web applications
+              where you obtain year, month, and day in three different
+              fields and you want to store exactly what the user
+              inserted (without date validation). This mode applies to
+              <literal>DATE</literal> and <literal>DATETIME</literal>
+              columns. It does not apply <literal>TIMESTAMP</literal>
+              columns, which always require a valid date.
+            </para>
+
+            <para>
+              This mode is implemented in MySQL 5.0.2. Before 5.0.2,
+              this was the default MySQL date-handling mode. As of
+              5.0.2, the server requires that month and day values be
+              legal, and not merely in the range 1 to 12 and 1 to 31,
+              respectively. With strict mode disabled, invalid dates
+              such as <literal>'2004-04-31'</literal> are converted to
+              <literal>'0000-00-00'</literal> and a warning is
+              generated. With strict mode enabled, invalid dates
+              generate an error. To allow such dates, enable
+              <literal>ALLOW_INVALID_DATES</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ANSI_QUOTES SQL mode</primary>
+              </indexterm>
+
+              <literal>ANSI_QUOTES</literal>
+            </para>
+
+            <para>
+              Treat &lsquo;<literal>"</literal>&rsquo; as an identifier
+              quote character (like the
+              &lsquo;<literal>`</literal>&rsquo; quote character) and
+              not as a string quote character. You can still use
+              &lsquo;<literal>`</literal>&rsquo; to quote identifiers in
+              ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
+              you cannot use double quotes to quote a literal string,
+              because it is interpreted as an identifier.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ERROR_FOR_DIVISION_BY_ZERO SQL mode</primary>
+              </indexterm>
+
+              <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>
+            </para>
+
+            <para>
+              Produce an error in strict mode (otherwise a warning) when
+              we encounter a division by zero (or
+              <literal>MOD(X,0)</literal>) during an
+              <literal>INSERT</literal> or <literal>UPDATE</literal>. If
+              this mode is not given, MySQL instead returns
+              <literal>NULL</literal> for divisions by zero. If used in
+              <literal>INSERT IGNORE</literal> or <literal>UPDATE
+              IGNORE</literal>, MySQL generates a warning for divisions
+              by zero, but the result of the operation is
+              <literal>NULL</literal>. (Implemented in MySQL 5.0.2)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>HIGH_NOT_PRECEDENCE SQL mode</primary>
+              </indexterm>
+
+              <literal>HIGH_NOT_PRECEDENCE</literal>
+            </para>
+
+            <para>
+              From MySQL 5.0.2 on, the precedence of the
+              <literal>NOT</literal> operator is such that expressions
+              such as <literal>NOT a BETWEEN b AND c</literal> are
+              parsed as <literal>NOT (a BETWEEN b AND c)</literal>.
+              Before MySQL 5.0.2, the expression is parsed as
+              <literal>(NOT a) BETWEEN b AND c</literal>. The old
+              higher-precedence behavior can be obtained by enabling the
+              <literal>HIGH_NOT_PRECEDENCE</literal> SQL mode. (Added in
+              MySQL 5.0.2)
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>SET sql_mode = '';</userinput>
+mysql&gt; <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
+        -&gt; 0
+mysql&gt; <userinput>SET sql_mode = 'broken_not';</userinput>
+mysql&gt; <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
+        -&gt; 1
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>IGNORE_SPACE SQL mode</primary>
+              </indexterm>
+
+              <literal>IGNORE_SPACE</literal>
+            </para>
+
+            <para>
+              Allow spaces between a function name and the
+              &lsquo;<literal>(</literal>&rsquo; character. This forces
+              all function names to be treated as reserved words. As a
+              result, if you want to access any database, table, or
+              column name that is a reserved word, you must quote it.
+              For example, because there is a <literal>USER()</literal>
+              function, the name of the <literal>user</literal> table in
+              the <literal>mysql</literal> database and the
+              <literal>User</literal> column in that table become
+              reserved, so you must quote them:
+            </para>
+
+<programlisting>
+SELECT "User" FROM mysql."user";
+</programlisting>
+
+            <para>
+              The <literal>IGNORE_SPACE</literal> SQL mode applies to
+              built-in functions, not to stored routines. it is always
+              allowable to have spaces after a routine name, regardless
+              of whether <literal>IGNORE_SPACE</literal> is enabled.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_AUTO_CREATE_USER SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_AUTO_CREATE_USER</literal>
+            </para>
+
+            <para>
+              Prevent <literal>GRANT</literal> from automatically
+              creating new users if it would otherwise do so, unless a
+              non-empty password also is specified. (Added in MySQL
+              5.0.2)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+            </para>
+
+            <para>
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
+              of <literal>AUTO_INCREMENT</literal> columns. Normally,
+              you generate the next sequence number for the column by
+              inserting either <literal>NULL</literal> or
+              <literal>0</literal> into it.
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
+              behavior for <literal>0</literal> so that only
+              <literal>NULL</literal> generates the next sequence
+              number.
+            </para>
+
+            <para>
+              This mode can be useful if <literal>0</literal> has been
+              stored in a table's <literal>AUTO_INCREMENT</literal>
+              column. (Storing <literal>0</literal> is not a recommended
+              practice, by the way.) For example, if you dump the table
+              with <command>mysqldump</command> and then reload it,
+              MySQL normally generates new sequence numbers when it
+              encounters the <literal>0</literal> values, resulting in a
+              table with contents different from the one that was
+              dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+              before reloading the dump file solves this problem.
+              <command>mysqldump</command> now automatically includes in
+              its output a statement that enables
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
+              problem.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_BACKSLASH_ESCAPES SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_BACKSLASH_ESCAPES</literal>
+            </para>
+
+            <para>
+              Disable the use of the backslash character
+              (&lsquo;<literal>\</literal>&rsquo;) as an escape
+              character within strings. With this mode enabled,
+              backslash becomes any ordinary character like any other.
+              (Implemented in MySQL 5.0.1)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_DIR_IN_CREATE SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_DIR_IN_CREATE</literal>
+            </para>
+
+            <para>
+              When creating a table, ignore all <literal>INDEX
+              DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
+              directives. This option is useful on slave replication
+              servers.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>NO_ENGINE_SUBSTITUTION</literal>
+            </para>
+
+            <para>
+              Prevents automatic substitution of storage engine when the
+              requested storage engine is disabled or not compiled in.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_FIELD_OPTIONS SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_FIELD_OPTIONS</literal>
+            </para>
+
+            <para>
+              Don't print MySQL-specific column options in the output of
+              <literal>SHOW CREATE TABLE</literal>. This mode is used by
+              <command>mysqldump</command> in portability mode.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_KEY_OPTIONS SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_KEY_OPTIONS</literal>
+            </para>
+
+            <para>
+              Don't print MySQL-specific index options in the output of
+              <literal>SHOW CREATE TABLE</literal>. This mode is used by
+              <command>mysqldump</command> in portability mode.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_TABLE_OPTIONS SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_TABLE_OPTIONS</literal>
+            </para>
+
+            <para>
+              Don't print MySQL-specific table options (such as
+              <literal>ENGINE</literal>) in the output of <literal>SHOW
+              CREATE TABLE</literal>. This mode is used by
+              <command>mysqldump</command> in portability mode.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_UNSIGNED_SUBTRACTION</literal>
+            </para>
+
+            <para>
+              In subtraction operations, don't mark the result as
+              <literal>UNSIGNED</literal> if one of the operands is
+              unsigned. Note that this makes <literal>UNSIGNED
+              BIGINT</literal> not 100% usable in all contexts. See
+              <xref linkend="cast-functions"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_ZERO_DATE SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_ZERO_DATE</literal>
+            </para>
+
+            <para>
+              In strict mode, don't allow
+              <literal>'0000-00-00'</literal> as a valid date. You can
+              still insert zero dates with the <literal>IGNORE</literal>
+              option. When not in strict mode, the date is accepted but
+              a warning is generated. (Added in MySQL 5.0.2)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_ZERO_IN_DATE SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_ZERO_IN_DATE</literal>
+            </para>
+
+            <para>
+              In strict mode, don't accept dates where the month or day
+              part is 0. If used with the <literal>IGNORE</literal>
+              option, we insert a <literal>'0000-00-00'</literal> date
+              for any such date. When not in strict mode, the date is
+              accepted but a warning is generated. (Added in MySQL
+              5.0.2)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
+              </indexterm>
+
+              <literal>ONLY_FULL_GROUP_BY</literal>
+            </para>
+
+            <para>
+              Do not allow queries that in the <literal>GROUP
+              BY</literal> part refer to a column that is not selected.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>PIPES_AS_CONCAT SQL mode</primary>
+              </indexterm>
+
+              <literal>PIPES_AS_CONCAT</literal>
+            </para>
+
+            <para>
+              Treat <literal>||</literal> as a string concatenation
+              operator (same as <literal>CONCAT()</literal>) rather than
+              as a synonym for <literal>OR</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>REAL_AS_FLOAT SQL mode</primary>
+              </indexterm>
+
+              <literal>REAL_AS_FLOAT</literal>
+            </para>
+
+            <para>
+              Treat <literal>REAL</literal> as a synonym for
+              <literal>FLOAT</literal> rather than as a synonym for
+              <literal>DOUBLE</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>STRICT_ALL_TABLES SQL mode</primary>
+              </indexterm>
+
+              <literal>STRICT_ALL_TABLES</literal>
+            </para>
+
+            <para>
+              Enable strict mode for all storage engines. Invalid data
+              values are rejected. Additional detail follows. (Added in
+              MySQL 5.0.2)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>STRICT_TRANS_TABLES SQL mode</primary>
+              </indexterm>
+
+              <literal>STRICT_TRANS_TABLES</literal>
+            </para>
+
+            <para>
+              Enable strict mode for transactional storage engines, and
+              when possible for non-transactional storage engines.
+              Additional details follow. (Implemented in MySQL 5.0.2)
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          Strict mode controls how MySQL handles input values that are
+          invalid or missing. A value can be invalid for several
+          reasons. For example, it might have the wrong data type for
+          the column, or it might be out of range. A value is missing
+          when a new row to be inserted does not contain a value for a
+          column that has no explicit <literal>DEFAULT</literal> clause
+          in its definition.
+        </para>
+
+        <para>
+          For transactional tables, an error occurs for invalid or
+          missing values in a statement when either of the
+          <literal>STRICT_ALL_TABLES</literal> or
+          <literal>STRICT_TRANS_TABLES</literal> modes are enabled. The
+          statement is aborted and rolled back.
+        </para>
+
+        <para>
+          For non-transactional tables, the behavior is the same for
+          either mode, if the bad value occurs in the first row to be
+          inserted or updated. The statement is aborted and the table
+          remains unchanged. If the statement inserts or modifies
+          multiple rows and the bad value occurs in the second or later
+          row, the result depends on which strict option is enabled:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              For <literal>STRICT_ALL_TABLES</literal>, MySQL returns an
+              error and ignores the rest of the rows. However, in this
+              case, the earlier rows still have been inserted or
+              updated. This means that you might get a partial update,
+              which might not be what you want. To avoid this, it's best
+              to use single-row statements because these can be aborted
+              without changing the table.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              For <literal>STRICT_TRANS_TABLES</literal>, MySQL converts
+              an invalid value to the closest valid value for the column
+              and insert the adjusted value. If a value is missing,
+              MySQL inserts the implicit default value for the column
+              data type. In either case, MySQL generates a warning
+              rather than an error and continues processing the
+              statement. Implicit defaults are described in
+              <xref linkend="create-table"/>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          Strict mode disallows invalid date values such as
+          <literal>'2004-04-31'</literal>. It does not disallow dates
+          with zero parts such as <literal>2004-04-00'</literal> or
+          <quote>zero</quote> dates. To disallow these as well, enable
+          the <literal>NO_ZERO_IN_DATE</literal> and
+          <literal>NO_ZERO_DATE</literal> SQL modes in addition to
+          strict mode.
+        </para>
+
+        <para>
+          If you are not using strict mode (that is, neither
+          <literal>STRICT_TRANS_TABLES</literal> nor
+          <literal>STRICT_ALL_TABLES</literal> is enabled), MySQL
+          inserts adjusted values for invalid or missing values and
+          produces warnings. In strict mode, you can produce this
+          behavior by using <literal>INSERT IGNORE</literal> or
+          <literal>UPDATE IGNORE</literal>. See
+          <xref linkend="show-warnings"/>.
+        </para>
+
+        <para>
+          The following special modes are provided as shorthand for
+          combinations of mode values from the preceding list. All are
+          available in MySQL &current-series; beginning with version
+          5.0.0, except for <literal>TRADITIONAL</literal>, which was
+          implemented in MySQL 5.0.2.
+        </para>
+
+        <para>
+          The descriptions include all mode values that are available in
+          the most recent version of MySQL. For older versions, a
+          combination mode does not include individual mode values that
+          are not available except in newer versions.
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ANSI SQL mode</primary>
+              </indexterm>
+
+              <literal>ANSI</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>REAL_AS_FLOAT</literal>,
+              <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>. Before MySQL 5.0.3,
+              <literal>ANSI</literal> also includes
+              <literal>ONLY_FULL_GROUP_BY</literal>. See
+              <xref linkend="ansi-mode"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>DB2 SQL mode</primary>
+              </indexterm>
+
+              <literal>DB2</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MAXDB SQL mode</primary>
+              </indexterm>
+
+              <literal>MAXDB</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>,
+              <literal>NO_AUTO_CREATE_USER</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MSSQL SQL mode</primary>
+              </indexterm>
+
+              <literal>MSSQL</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MYSQL323 SQL mode</primary>
+              </indexterm>
+
+              <literal>MYSQL323</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
+              <literal>HIGH_NOT_PRECEDENCE</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MYSQL40 SQL mode</primary>
+              </indexterm>
+
+              <literal>MYSQL40</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
+              <literal>HIGH_NOT_PRECEDENCE</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ORACLE SQL mode</primary>
+              </indexterm>
+
+              <literal>ORACLE</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>,
+              <literal>NO_AUTO_CREATE_USER</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>POSTGRESQL SQL mode</primary>
+              </indexterm>
+
+              <literal>POSTGRESQL</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>TRADITIONAL SQL mode</primary>
+              </indexterm>
+
+              <literal>TRADITIONAL</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>STRICT_TRANS_TABLES</literal>,
+              <literal>STRICT_ALL_TABLES</literal>,
+              <literal>NO_ZERO_IN_DATE</literal>,
+              <literal>NO_ZERO_DATE</literal>,
+              <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>,
+              <literal>NO_AUTO_CREATE_USER</literal>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </refsection>
+
       <refsection id="server-shutdown">
 
         <title>&title-server-shutdown;</title>

Modified: trunk/refman-5.1/database-administration.xml
===================================================================
--- trunk/refman-5.1/database-administration.xml	2006-01-23 21:26:18 UTC (rev 1003)
+++ trunk/refman-5.1/database-administration.xml	2006-01-23 21:26:35 UTC (rev 1004)
@@ -1933,820 +1933,6 @@
 
       </refsection>
 
-      <refsection id="server-sql-mode">
-
-        <title>&title-server-sql-mode;</title>
-
-        <para>
-          The MySQL server can operate in different SQL modes, and can
-          apply these modes differently for different clients. This
-          enables each application to tailor the server's operating mode
-          to its own requirements.
-        </para>
-
-        <para>
-          Modes define what SQL syntax MySQL should support and what
-          kind of data validation checks it should perform. This makes
-          it easier to use MySQL in different environments and to use
-          MySQL together with other database servers.
-        </para>
-
-        <para>
-          You can set the default SQL mode by starting
-          <command>mysqld</command> with the
-          <option>--sql-mode="<replaceable>modes</replaceable>"</option>
-          option. The value also can be empty
-          (<option>--sql-mode=""</option>) if you want to reset it.
-        </para>
-
-        <para>
-          You can also change the SQL mode after startup time by setting
-          the <literal>sql_mode</literal> variable using a <literal>SET
-          [SESSION|GLOBAL]
-          sql_mode='<replaceable>modes</replaceable>'</literal>
-          statement. Setting the <literal>GLOBAL</literal> variable
-          requires the <literal>SUPER</literal> privilege and affects
-          the operation of all clients that connect from that time on.
-          Setting the <literal>SESSION</literal> variable affects only
-          the current client. Any client can change its own session
-          <literal>sql_mode</literal> value at any time.
-        </para>
-
-        <para>
-          <replaceable>modes</replaceable> is a list of different modes
-          separated by comma (&lsquo;<literal>,</literal>&rsquo;)
-          characters. You can retrieve the current mode by issuing a
-          <literal>SELECT @@sql_mode</literal> statement. The default
-          value is empty (no modes set).
-        </para>
-
-        <para>
-          The most important <literal>sql_mode</literal> values are
-          probably these:
-        </para>
-
-        <itemizedlist>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ANSI SQL mode</primary>
-              </indexterm>
-
-              <literal>ANSI</literal>
-            </para>
-
-            <para>
-              Change syntax and behavior to be more conformant to
-              standard SQL.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>STRICT_TRANS_TABLES SQL mode</primary>
-              </indexterm>
-
-              <literal>STRICT_TRANS_TABLES</literal>
-            </para>
-
-            <para>
-              If a value could not be inserted as given into a
-              transactional table, abort the statement. For a
-              non-transactional table, abort the statement if the value
-              occurs in a single-row statement or the first row of a
-              multiple-row statement. More detail is given later in this
-              section.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>TRADITIONAL SQL mode</primary>
-              </indexterm>
-
-              <literal>TRADITIONAL</literal>
-            </para>
-
-            <para>
-              Make MySQL behave like a <quote>traditional</quote> SQL
-              database system. A simple description of this mode is
-              <quote>give an error instead of a warning</quote> when
-              inserting an incorrect value into a column.
-              <emphasis role="bold">Note</emphasis>: The
-              <literal>INSERT</literal>/<literal>UPDATE</literal> aborts
-              as soon as the error is noticed. This may not be what you
-              want if you are using a non-transactional storage engine,
-              because data changes made prior to the error are not be
-              rolled back, resulting in a <quote>partially done</quote>
-              update.
-            </para>
-          </listitem>
-
-        </itemizedlist>
-
-        <indexterm>
-          <primary>STRICT SQL mode</primary>
-        </indexterm>
-
-        <para>
-          When this manual refers to <quote>strict mode,</quote> it
-          means a mode where at least one of
-          <literal>STRICT_TRANS_TABLES</literal> or
-          <literal>STRICT_ALL_TABLES</literal> is enabled.
-        </para>
-
-        <para>
-          The following list describes all supported modes:
-        </para>
-
-        <itemizedlist>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ALLOW_INVALID_DATES SQL mode</primary>
-              </indexterm>
-
-              <literal>ALLOW_INVALID_DATES</literal>
-            </para>
-
-            <para>
-              Don't do full checking of dates. Check only that the month
-              is in the range from 1 to 12 and the day is in the range
-              from 1 to 31. This is very convenient for Web applications
-              where you obtain year, month, and day in three different
-              fields and you want to store exactly what the user
-              inserted (without date validation). This mode applies to
-              <literal>DATE</literal> and <literal>DATETIME</literal>
-              columns. It does not apply <literal>TIMESTAMP</literal>
-              columns, which always require a valid date.
-            </para>
-
-            <para>
-              The server requires that month and day values be legal,
-              and not merely in the range 1 to 12 and 1 to 31,
-              respectively. With strict mode disabled, invalid dates
-              such as <literal>'2004-04-31'</literal> are converted to
-              <literal>'0000-00-00'</literal> and a warning is
-              generated. With strict mode enabled, invalid dates
-              generate an error. To allow such dates, enable
-              <literal>ALLOW_INVALID_DATES</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ANSI_QUOTES SQL mode</primary>
-              </indexterm>
-
-              <literal>ANSI_QUOTES</literal>
-            </para>
-
-            <para>
-              Treat &lsquo;<literal>"</literal>&rsquo; as an identifier
-              quote character (like the
-              &lsquo;<literal>`</literal>&rsquo; quote character) and
-              not as a string quote character. You can still use
-              &lsquo;<literal>`</literal>&rsquo; to quote identifiers in
-              ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
-              you cannot use double quotes to quote a literal string,
-              because it is interpreted as an identifier.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ERROR_FOR_DIVISION_BY_ZERO SQL mode</primary>
-              </indexterm>
-
-              <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>
-            </para>
-
-            <para>
-              Produce an error in strict mode (otherwise a warning) when
-              we encounter a division by zero (or
-              <literal>MOD(X,0)</literal>) during an
-              <literal>INSERT</literal> or <literal>UPDATE</literal>. If
-              this mode is not given, MySQL instead returns
-              <literal>NULL</literal> for divisions by zero. If used in
-              <literal>INSERT IGNORE</literal> or <literal>UPDATE
-              IGNORE</literal>, MySQL generates a warning for divisions
-              by zero, but the result of the operation is
-              <literal>NULL</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>HIGH_NOT_PRECEDENCE SQL mode</primary>
-              </indexterm>
-
-              <literal>HIGH_NOT_PRECEDENCE</literal>
-            </para>
-
-            <para>
-              The precedence of the <literal>NOT</literal> operator is
-              such that expressions such as <literal>NOT a BETWEEN b AND
-              c</literal> are parsed as <literal>NOT (a BETWEEN b AND
-              c)</literal>. In some older versions of MySQL, the
-              expression was parsed as <literal>(NOT a) BETWEEN b AND
-              c</literal>. The old higher-precedence behavior can be
-              obtained by enabling the
-              <literal>HIGH_NOT_PRECEDENCE</literal> SQL mode.
-            </para>
-
-<programlisting>
-mysql&gt; <userinput>SET sql_mode = '';</userinput>
-mysql&gt; <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
-        -&gt; 0
-mysql&gt; <userinput>SET sql_mode = 'broken_not';</userinput>
-mysql&gt; <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
-        -&gt; 1
-</programlisting>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>IGNORE_SPACE SQL mode</primary>
-              </indexterm>
-
-              <literal>IGNORE_SPACE</literal>
-            </para>
-
-            <para>
-              Allow spaces between a function name and the
-              &lsquo;<literal>(</literal>&rsquo; character. This forces
-              all function names to be treated as reserved words. As a
-              result, if you want to access any database, table, or
-              column name that is a reserved word, you must quote it.
-              For example, because there is a <literal>USER()</literal>
-              function, the name of the <literal>user</literal> table in
-              the <literal>mysql</literal> database and the
-              <literal>User</literal> column in that table become
-              reserved, so you must quote them:
-            </para>
-
-<programlisting>
-SELECT "User" FROM mysql."user";
-</programlisting>
-
-            <para>
-              The <literal>IGNORE_SPACE</literal> SQL mode applies to
-              built-in functions, not to stored routines. it is always
-              allowable to have spaces after a routine name, regardless
-              of whether <literal>IGNORE_SPACE</literal> is enabled.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_AUTO_CREATE_USER SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_AUTO_CREATE_USER</literal>
-            </para>
-
-            <para>
-              Prevent <literal>GRANT</literal> from automatically
-              creating new users if it would otherwise do so, unless a
-              non-empty password also is specified.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal>
-            </para>
-
-            <para>
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
-              of <literal>AUTO_INCREMENT</literal> columns. Normally,
-              you generate the next sequence number for the column by
-              inserting either <literal>NULL</literal> or
-              <literal>0</literal> into it.
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
-              behavior for <literal>0</literal> so that only
-              <literal>NULL</literal> generates the next sequence
-              number.
-            </para>
-
-            <para>
-              This mode can be useful if <literal>0</literal> has been
-              stored in a table's <literal>AUTO_INCREMENT</literal>
-              column. (Storing <literal>0</literal> is not a recommended
-              practice, by the way.) For example, if you dump the table
-              with <command>mysqldump</command> and then reload it,
-              MySQL normally generates new sequence numbers when it
-              encounters the <literal>0</literal> values, resulting in a
-              table with contents different from the one that was
-              dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
-              before reloading the dump file solves this problem.
-              <command>mysqldump</command> now automatically includes in
-              its output a statement that enables
-              <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
-              problem.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_BACKSLASH_ESCAPES SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_BACKSLASH_ESCAPES</literal>
-            </para>
-
-            <para>
-              Disable the use of the backslash character
-              (&lsquo;<literal>\</literal>&rsquo;) as an escape
-              character within strings. With this mode enabled,
-              backslash becomes any ordinary character like any other.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_DIR_IN_CREATE SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_DIR_IN_CREATE</literal>
-            </para>
-
-            <para>
-              When creating a table, ignore all <literal>INDEX
-              DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
-              directives. This option is useful on slave replication
-              servers.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <literal>NO_ENGINE_SUBSTITUTION</literal>
-            </para>
-
-            <para>
-              Prevents automatic substitution of storage engine when the
-              requested storage engine is disabled or not compiled in.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_FIELD_OPTIONS SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_FIELD_OPTIONS</literal>
-            </para>
-
-            <para>
-              Don't print MySQL-specific column options in the output of
-              <literal>SHOW CREATE TABLE</literal>. This mode is used by
-              <command>mysqldump</command> in portability mode.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_KEY_OPTIONS SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_KEY_OPTIONS</literal>
-            </para>
-
-            <para>
-              Don't print MySQL-specific index options in the output of
-              <literal>SHOW CREATE TABLE</literal>. This mode is used by
-              <command>mysqldump</command> in portability mode.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_TABLE_OPTIONS SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_TABLE_OPTIONS</literal>
-            </para>
-
-            <para>
-              Don't print MySQL-specific table options (such as
-              <literal>ENGINE</literal>) in the output of <literal>SHOW
-              CREATE TABLE</literal>. This mode is used by
-              <command>mysqldump</command> in portability mode.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_UNSIGNED_SUBTRACTION</literal>
-            </para>
-
-            <para>
-              In subtraction operations, don't mark the result as
-              <literal>UNSIGNED</literal> if one of the operands is
-              unsigned. Note that this makes <literal>UNSIGNED
-              BIGINT</literal> not 100% usable in all contexts. See
-              <xref linkend="cast-functions"/>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_ZERO_DATE SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_ZERO_DATE</literal>
-            </para>
-
-            <para>
-              In strict mode, don't allow
-              <literal>'0000-00-00'</literal> as a valid date. You can
-              still insert zero dates with the <literal>IGNORE</literal>
-              option. When not in strict mode, the date is accepted but
-              a warning is generated.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>NO_ZERO_IN_DATE SQL mode</primary>
-              </indexterm>
-
-              <literal>NO_ZERO_IN_DATE</literal>
-            </para>
-
-            <para>
-              In strict mode, don't accept dates where the month or day
-              part is 0. If used with the <literal>IGNORE</literal>
-              option, we insert a <literal>'0000-00-00'</literal> date
-              for any such date. When not in strict mode, the date is
-              accepted but a warning is generated.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
-              </indexterm>
-
-              <literal>ONLY_FULL_GROUP_BY</literal>
-            </para>
-
-            <para>
-              Do not allow queries that in the <literal>GROUP
-              BY</literal> part refer to a column that is not selected.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>PIPES_AS_CONCAT SQL mode</primary>
-              </indexterm>
-
-              <literal>PIPES_AS_CONCAT</literal>
-            </para>
-
-            <para>
-              Treat <literal>||</literal> as a string concatenation
-              operator (same as <literal>CONCAT()</literal>) rather than
-              as a synonym for <literal>OR</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>REAL_AS_FLOAT SQL mode</primary>
-              </indexterm>
-
-              <literal>REAL_AS_FLOAT</literal>
-            </para>
-
-            <para>
-              Treat <literal>REAL</literal> as a synonym for
-              <literal>FLOAT</literal> rather than as a synonym for
-              <literal>DOUBLE</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>STRICT_ALL_TABLES SQL mode</primary>
-              </indexterm>
-
-              <literal>STRICT_ALL_TABLES</literal>
-            </para>
-
-            <para>
-              Enable strict mode for all storage engines. Invalid data
-              values are rejected. Additional detail follows.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>STRICT_TRANS_TABLES SQL mode</primary>
-              </indexterm>
-
-              <literal>STRICT_TRANS_TABLES</literal>
-            </para>
-
-            <para>
-              Enable strict mode for transactional storage engines, and
-              when possible for non-transactional storage engines.
-              Additional details follow.
-            </para>
-          </listitem>
-
-        </itemizedlist>
-
-        <para>
-          Strict mode controls how MySQL handles input values that are
-          invalid or missing. A value can be invalid for several
-          reasons. For example, it might have the wrong data type for
-          the column, or it might be out of range. A value is missing
-          when a new row to be inserted does not contain a value for a
-          column that has no explicit <literal>DEFAULT</literal> clause
-          in its definition.
-        </para>
-
-        <para>
-          For transactional tables, an error occurs for invalid or
-          missing values in a statement when either of the
-          <literal>STRICT_ALL_TABLES</literal> or
-          <literal>STRICT_TRANS_TABLES</literal> modes are enabled. The
-          statement is aborted and rolled back.
-        </para>
-
-        <para>
-          For non-transactional tables, the behavior is the same for
-          either mode, if the bad value occurs in the first row to be
-          inserted or updated. The statement is aborted and the table
-          remains unchanged. If the statement inserts or modifies
-          multiple rows and the bad value occurs in the second or later
-          row, the result depends on which strict option is enabled:
-        </para>
-
-        <itemizedlist>
-
-          <listitem>
-            <para>
-              For <literal>STRICT_ALL_TABLES</literal>, MySQL returns an
-              error and ignores the rest of the rows. However, in this
-              case, the earlier rows still have been inserted or
-              updated. This means that you might get a partial update,
-              which might not be what you want. To avoid this, it's best
-              to use single-row statements because these can be aborted
-              without changing the table.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              For <literal>STRICT_TRANS_TABLES</literal>, MySQL converts
-              an invalid value to the closest valid value for the column
-              and insert the adjusted value. If a value is missing,
-              MySQL inserts the implicit default value for the column
-              data type. In either case, MySQL generates a warning
-              rather than an error and continues processing the
-              statement. Implicit defaults are described in
-              <xref linkend="create-table"/>.
-            </para>
-          </listitem>
-
-        </itemizedlist>
-
-        <para>
-          Strict mode disallows invalid date values such as
-          <literal>'2004-04-31'</literal>. It does not disallow dates
-          with zero parts such as <literal>2004-04-00'</literal> or
-          <quote>zero</quote> dates. To disallow these as well, enable
-          the <literal>NO_ZERO_IN_DATE</literal> and
-          <literal>NO_ZERO_DATE</literal> SQL modes in addition to
-          strict mode.
-        </para>
-
-        <para>
-          If you are not using strict mode (that is, neither
-          <literal>STRICT_TRANS_TABLES</literal> nor
-          <literal>STRICT_ALL_TABLES</literal> is enabled), MySQL
-          inserts adjusted values for invalid or missing values and
-          produces warnings. In strict mode, you can produce this
-          behavior by using <literal>INSERT IGNORE</literal> or
-          <literal>UPDATE IGNORE</literal>. See
-          <xref linkend="show-warnings"/>.
-        </para>
-
-        <para>
-          The following special modes are provided as shorthand for
-          combinations of mode values from the preceding list.
-        </para>
-
-        <para>
-          The descriptions include all mode values that are available in
-          the most recent version of MySQL. For older versions, a
-          combination mode does not include individual mode values that
-          are not available except in newer versions.
-        </para>
-
-        <itemizedlist>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ANSI SQL mode</primary>
-              </indexterm>
-
-              <literal>ANSI</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>REAL_AS_FLOAT</literal>,
-              <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>. See
-              <xref linkend="ansi-mode"/>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>DB2 SQL mode</primary>
-              </indexterm>
-
-              <literal>DB2</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MAXDB SQL mode</primary>
-              </indexterm>
-
-              <literal>MAXDB</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>,
-              <literal>NO_AUTO_CREATE_USER</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MSSQL SQL mode</primary>
-              </indexterm>
-
-              <literal>MSSQL</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MYSQL323 SQL mode</primary>
-              </indexterm>
-
-              <literal>MYSQL323</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
-              <literal>HIGH_NOT_PRECEDENCE</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>MYSQL40 SQL mode</primary>
-              </indexterm>
-
-              <literal>MYSQL40</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
-              <literal>HIGH_NOT_PRECEDENCE</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>ORACLE SQL mode</primary>
-              </indexterm>
-
-              <literal>ORACLE</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>,
-              <literal>NO_AUTO_CREATE_USER</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>POSTGRESQL SQL mode</primary>
-              </indexterm>
-
-              <literal>POSTGRESQL</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
-              <literal>ANSI_QUOTES</literal>,
-              <literal>IGNORE_SPACE</literal>,
-              <literal>NO_KEY_OPTIONS</literal>,
-              <literal>NO_TABLE_OPTIONS</literal>,
-              <literal>NO_FIELD_OPTIONS</literal>.
-            </para>
-          </listitem>
-
-          <listitem>
-            <para>
-              <indexterm>
-                <primary>TRADITIONAL SQL mode</primary>
-              </indexterm>
-
-              <literal>TRADITIONAL</literal>
-            </para>
-
-            <para>
-              Equivalent to <literal>STRICT_TRANS_TABLES</literal>,
-              <literal>STRICT_ALL_TABLES</literal>,
-              <literal>NO_ZERO_IN_DATE</literal>,
-              <literal>NO_ZERO_DATE</literal>,
-              <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>,
-              <literal>NO_AUTO_CREATE_USER</literal>.
-            </para>
-          </listitem>
-
-        </itemizedlist>
-
-      </refsection>
-
       <refsection id="server-system-variables">
 
         <title>&title-server-system-variables;</title>
@@ -8854,6 +8040,820 @@
 
       </refsection>
 
+      <refsection id="server-sql-mode">
+
+        <title>&title-server-sql-mode;</title>
+
+        <para>
+          The MySQL server can operate in different SQL modes, and can
+          apply these modes differently for different clients. This
+          enables each application to tailor the server's operating mode
+          to its own requirements.
+        </para>
+
+        <para>
+          Modes define what SQL syntax MySQL should support and what
+          kind of data validation checks it should perform. This makes
+          it easier to use MySQL in different environments and to use
+          MySQL together with other database servers.
+        </para>
+
+        <para>
+          You can set the default SQL mode by starting
+          <command>mysqld</command> with the
+          <option>--sql-mode="<replaceable>modes</replaceable>"</option>
+          option. The value also can be empty
+          (<option>--sql-mode=""</option>) if you want to reset it.
+        </para>
+
+        <para>
+          You can also change the SQL mode after startup time by setting
+          the <literal>sql_mode</literal> variable using a <literal>SET
+          [SESSION|GLOBAL]
+          sql_mode='<replaceable>modes</replaceable>'</literal>
+          statement. Setting the <literal>GLOBAL</literal> variable
+          requires the <literal>SUPER</literal> privilege and affects
+          the operation of all clients that connect from that time on.
+          Setting the <literal>SESSION</literal> variable affects only
+          the current client. Any client can change its own session
+          <literal>sql_mode</literal> value at any time.
+        </para>
+
+        <para>
+          <replaceable>modes</replaceable> is a list of different modes
+          separated by comma (&lsquo;<literal>,</literal>&rsquo;)
+          characters. You can retrieve the current mode by issuing a
+          <literal>SELECT @@sql_mode</literal> statement. The default
+          value is empty (no modes set).
+        </para>
+
+        <para>
+          The most important <literal>sql_mode</literal> values are
+          probably these:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ANSI SQL mode</primary>
+              </indexterm>
+
+              <literal>ANSI</literal>
+            </para>
+
+            <para>
+              Change syntax and behavior to be more conformant to
+              standard SQL.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>STRICT_TRANS_TABLES SQL mode</primary>
+              </indexterm>
+
+              <literal>STRICT_TRANS_TABLES</literal>
+            </para>
+
+            <para>
+              If a value could not be inserted as given into a
+              transactional table, abort the statement. For a
+              non-transactional table, abort the statement if the value
+              occurs in a single-row statement or the first row of a
+              multiple-row statement. More detail is given later in this
+              section.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>TRADITIONAL SQL mode</primary>
+              </indexterm>
+
+              <literal>TRADITIONAL</literal>
+            </para>
+
+            <para>
+              Make MySQL behave like a <quote>traditional</quote> SQL
+              database system. A simple description of this mode is
+              <quote>give an error instead of a warning</quote> when
+              inserting an incorrect value into a column.
+              <emphasis role="bold">Note</emphasis>: The
+              <literal>INSERT</literal>/<literal>UPDATE</literal> aborts
+              as soon as the error is noticed. This may not be what you
+              want if you are using a non-transactional storage engine,
+              because data changes made prior to the error are not be
+              rolled back, resulting in a <quote>partially done</quote>
+              update.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <indexterm>
+          <primary>STRICT SQL mode</primary>
+        </indexterm>
+
+        <para>
+          When this manual refers to <quote>strict mode,</quote> it
+          means a mode where at least one of
+          <literal>STRICT_TRANS_TABLES</literal> or
+          <literal>STRICT_ALL_TABLES</literal> is enabled.
+        </para>
+
+        <para>
+          The following list describes all supported modes:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ALLOW_INVALID_DATES SQL mode</primary>
+              </indexterm>
+
+              <literal>ALLOW_INVALID_DATES</literal>
+            </para>
+
+            <para>
+              Don't do full checking of dates. Check only that the month
+              is in the range from 1 to 12 and the day is in the range
+              from 1 to 31. This is very convenient for Web applications
+              where you obtain year, month, and day in three different
+              fields and you want to store exactly what the user
+              inserted (without date validation). This mode applies to
+              <literal>DATE</literal> and <literal>DATETIME</literal>
+              columns. It does not apply <literal>TIMESTAMP</literal>
+              columns, which always require a valid date.
+            </para>
+
+            <para>
+              The server requires that month and day values be legal,
+              and not merely in the range 1 to 12 and 1 to 31,
+              respectively. With strict mode disabled, invalid dates
+              such as <literal>'2004-04-31'</literal> are converted to
+              <literal>'0000-00-00'</literal> and a warning is
+              generated. With strict mode enabled, invalid dates
+              generate an error. To allow such dates, enable
+              <literal>ALLOW_INVALID_DATES</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ANSI_QUOTES SQL mode</primary>
+              </indexterm>
+
+              <literal>ANSI_QUOTES</literal>
+            </para>
+
+            <para>
+              Treat &lsquo;<literal>"</literal>&rsquo; as an identifier
+              quote character (like the
+              &lsquo;<literal>`</literal>&rsquo; quote character) and
+              not as a string quote character. You can still use
+              &lsquo;<literal>`</literal>&rsquo; to quote identifiers in
+              ANSI mode. With <literal>ANSI_QUOTES</literal> enabled,
+              you cannot use double quotes to quote a literal string,
+              because it is interpreted as an identifier.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ERROR_FOR_DIVISION_BY_ZERO SQL mode</primary>
+              </indexterm>
+
+              <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>
+            </para>
+
+            <para>
+              Produce an error in strict mode (otherwise a warning) when
+              we encounter a division by zero (or
+              <literal>MOD(X,0)</literal>) during an
+              <literal>INSERT</literal> or <literal>UPDATE</literal>. If
+              this mode is not given, MySQL instead returns
+              <literal>NULL</literal> for divisions by zero. If used in
+              <literal>INSERT IGNORE</literal> or <literal>UPDATE
+              IGNORE</literal>, MySQL generates a warning for divisions
+              by zero, but the result of the operation is
+              <literal>NULL</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>HIGH_NOT_PRECEDENCE SQL mode</primary>
+              </indexterm>
+
+              <literal>HIGH_NOT_PRECEDENCE</literal>
+            </para>
+
+            <para>
+              The precedence of the <literal>NOT</literal> operator is
+              such that expressions such as <literal>NOT a BETWEEN b AND
+              c</literal> are parsed as <literal>NOT (a BETWEEN b AND
+              c)</literal>. In some older versions of MySQL, the
+              expression was parsed as <literal>(NOT a) BETWEEN b AND
+              c</literal>. The old higher-precedence behavior can be
+              obtained by enabling the
+              <literal>HIGH_NOT_PRECEDENCE</literal> SQL mode.
+            </para>
+
+<programlisting>
+mysql&gt; <userinput>SET sql_mode = '';</userinput>
+mysql&gt; <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
+        -&gt; 0
+mysql&gt; <userinput>SET sql_mode = 'broken_not';</userinput>
+mysql&gt; <userinput>SELECT NOT 1 BETWEEN -5 AND 5;</userinput>
+        -&gt; 1
+</programlisting>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>IGNORE_SPACE SQL mode</primary>
+              </indexterm>
+
+              <literal>IGNORE_SPACE</literal>
+            </para>
+
+            <para>
+              Allow spaces between a function name and the
+              &lsquo;<literal>(</literal>&rsquo; character. This forces
+              all function names to be treated as reserved words. As a
+              result, if you want to access any database, table, or
+              column name that is a reserved word, you must quote it.
+              For example, because there is a <literal>USER()</literal>
+              function, the name of the <literal>user</literal> table in
+              the <literal>mysql</literal> database and the
+              <literal>User</literal> column in that table become
+              reserved, so you must quote them:
+            </para>
+
+<programlisting>
+SELECT "User" FROM mysql."user";
+</programlisting>
+
+            <para>
+              The <literal>IGNORE_SPACE</literal> SQL mode applies to
+              built-in functions, not to stored routines. it is always
+              allowable to have spaces after a routine name, regardless
+              of whether <literal>IGNORE_SPACE</literal> is enabled.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_AUTO_CREATE_USER SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_AUTO_CREATE_USER</literal>
+            </para>
+
+            <para>
+              Prevent <literal>GRANT</literal> from automatically
+              creating new users if it would otherwise do so, unless a
+              non-empty password also is specified.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_AUTO_VALUE_ON_ZERO SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+            </para>
+
+            <para>
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal> affects handling
+              of <literal>AUTO_INCREMENT</literal> columns. Normally,
+              you generate the next sequence number for the column by
+              inserting either <literal>NULL</literal> or
+              <literal>0</literal> into it.
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal> suppresses this
+              behavior for <literal>0</literal> so that only
+              <literal>NULL</literal> generates the next sequence
+              number.
+            </para>
+
+            <para>
+              This mode can be useful if <literal>0</literal> has been
+              stored in a table's <literal>AUTO_INCREMENT</literal>
+              column. (Storing <literal>0</literal> is not a recommended
+              practice, by the way.) For example, if you dump the table
+              with <command>mysqldump</command> and then reload it,
+              MySQL normally generates new sequence numbers when it
+              encounters the <literal>0</literal> values, resulting in a
+              table with contents different from the one that was
+              dumped. Enabling <literal>NO_AUTO_VALUE_ON_ZERO</literal>
+              before reloading the dump file solves this problem.
+              <command>mysqldump</command> now automatically includes in
+              its output a statement that enables
+              <literal>NO_AUTO_VALUE_ON_ZERO</literal>, to avoid this
+              problem.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_BACKSLASH_ESCAPES SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_BACKSLASH_ESCAPES</literal>
+            </para>
+
+            <para>
+              Disable the use of the backslash character
+              (&lsquo;<literal>\</literal>&rsquo;) as an escape
+              character within strings. With this mode enabled,
+              backslash becomes any ordinary character like any other.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_DIR_IN_CREATE SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_DIR_IN_CREATE</literal>
+            </para>
+
+            <para>
+              When creating a table, ignore all <literal>INDEX
+              DIRECTORY</literal> and <literal>DATA DIRECTORY</literal>
+              directives. This option is useful on slave replication
+              servers.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>NO_ENGINE_SUBSTITUTION</literal>
+            </para>
+
+            <para>
+              Prevents automatic substitution of storage engine when the
+              requested storage engine is disabled or not compiled in.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_FIELD_OPTIONS SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_FIELD_OPTIONS</literal>
+            </para>
+
+            <para>
+              Don't print MySQL-specific column options in the output of
+              <literal>SHOW CREATE TABLE</literal>. This mode is used by
+              <command>mysqldump</command> in portability mode.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_KEY_OPTIONS SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_KEY_OPTIONS</literal>
+            </para>
+
+            <para>
+              Don't print MySQL-specific index options in the output of
+              <literal>SHOW CREATE TABLE</literal>. This mode is used by
+              <command>mysqldump</command> in portability mode.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_TABLE_OPTIONS SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_TABLE_OPTIONS</literal>
+            </para>
+
+            <para>
+              Don't print MySQL-specific table options (such as
+              <literal>ENGINE</literal>) in the output of <literal>SHOW
+              CREATE TABLE</literal>. This mode is used by
+              <command>mysqldump</command> in portability mode.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_UNSIGNED_SUBTRACTION SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_UNSIGNED_SUBTRACTION</literal>
+            </para>
+
+            <para>
+              In subtraction operations, don't mark the result as
+              <literal>UNSIGNED</literal> if one of the operands is
+              unsigned. Note that this makes <literal>UNSIGNED
+              BIGINT</literal> not 100% usable in all contexts. See
+              <xref linkend="cast-functions"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_ZERO_DATE SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_ZERO_DATE</literal>
+            </para>
+
+            <para>
+              In strict mode, don't allow
+              <literal>'0000-00-00'</literal> as a valid date. You can
+              still insert zero dates with the <literal>IGNORE</literal>
+              option. When not in strict mode, the date is accepted but
+              a warning is generated.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>NO_ZERO_IN_DATE SQL mode</primary>
+              </indexterm>
+
+              <literal>NO_ZERO_IN_DATE</literal>
+            </para>
+
+            <para>
+              In strict mode, don't accept dates where the month or day
+              part is 0. If used with the <literal>IGNORE</literal>
+              option, we insert a <literal>'0000-00-00'</literal> date
+              for any such date. When not in strict mode, the date is
+              accepted but a warning is generated.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ONLY_FULL_GROUP_BY SQL mode</primary>
+              </indexterm>
+
+              <literal>ONLY_FULL_GROUP_BY</literal>
+            </para>
+
+            <para>
+              Do not allow queries that in the <literal>GROUP
+              BY</literal> part refer to a column that is not selected.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>PIPES_AS_CONCAT SQL mode</primary>
+              </indexterm>
+
+              <literal>PIPES_AS_CONCAT</literal>
+            </para>
+
+            <para>
+              Treat <literal>||</literal> as a string concatenation
+              operator (same as <literal>CONCAT()</literal>) rather than
+              as a synonym for <literal>OR</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>REAL_AS_FLOAT SQL mode</primary>
+              </indexterm>
+
+              <literal>REAL_AS_FLOAT</literal>
+            </para>
+
+            <para>
+              Treat <literal>REAL</literal> as a synonym for
+              <literal>FLOAT</literal> rather than as a synonym for
+              <literal>DOUBLE</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>STRICT_ALL_TABLES SQL mode</primary>
+              </indexterm>
+
+              <literal>STRICT_ALL_TABLES</literal>
+            </para>
+
+            <para>
+              Enable strict mode for all storage engines. Invalid data
+              values are rejected. Additional detail follows.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>STRICT_TRANS_TABLES SQL mode</primary>
+              </indexterm>
+
+              <literal>STRICT_TRANS_TABLES</literal>
+            </para>
+
+            <para>
+              Enable strict mode for transactional storage engines, and
+              when possible for non-transactional storage engines.
+              Additional details follow.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          Strict mode controls how MySQL handles input values that are
+          invalid or missing. A value can be invalid for several
+          reasons. For example, it might have the wrong data type for
+          the column, or it might be out of range. A value is missing
+          when a new row to be inserted does not contain a value for a
+          column that has no explicit <literal>DEFAULT</literal> clause
+          in its definition.
+        </para>
+
+        <para>
+          For transactional tables, an error occurs for invalid or
+          missing values in a statement when either of the
+          <literal>STRICT_ALL_TABLES</literal> or
+          <literal>STRICT_TRANS_TABLES</literal> modes are enabled. The
+          statement is aborted and rolled back.
+        </para>
+
+        <para>
+          For non-transactional tables, the behavior is the same for
+          either mode, if the bad value occurs in the first row to be
+          inserted or updated. The statement is aborted and the table
+          remains unchanged. If the statement inserts or modifies
+          multiple rows and the bad value occurs in the second or later
+          row, the result depends on which strict option is enabled:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              For <literal>STRICT_ALL_TABLES</literal>, MySQL returns an
+              error and ignores the rest of the rows. However, in this
+              case, the earlier rows still have been inserted or
+              updated. This means that you might get a partial update,
+              which might not be what you want. To avoid this, it's best
+              to use single-row statements because these can be aborted
+              without changing the table.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              For <literal>STRICT_TRANS_TABLES</literal>, MySQL converts
+              an invalid value to the closest valid value for the column
+              and insert the adjusted value. If a value is missing,
+              MySQL inserts the implicit default value for the column
+              data type. In either case, MySQL generates a warning
+              rather than an error and continues processing the
+              statement. Implicit defaults are described in
+              <xref linkend="create-table"/>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          Strict mode disallows invalid date values such as
+          <literal>'2004-04-31'</literal>. It does not disallow dates
+          with zero parts such as <literal>2004-04-00'</literal> or
+          <quote>zero</quote> dates. To disallow these as well, enable
+          the <literal>NO_ZERO_IN_DATE</literal> and
+          <literal>NO_ZERO_DATE</literal> SQL modes in addition to
+          strict mode.
+        </para>
+
+        <para>
+          If you are not using strict mode (that is, neither
+          <literal>STRICT_TRANS_TABLES</literal> nor
+          <literal>STRICT_ALL_TABLES</literal> is enabled), MySQL
+          inserts adjusted values for invalid or missing values and
+          produces warnings. In strict mode, you can produce this
+          behavior by using <literal>INSERT IGNORE</literal> or
+          <literal>UPDATE IGNORE</literal>. See
+          <xref linkend="show-warnings"/>.
+        </para>
+
+        <para>
+          The following special modes are provided as shorthand for
+          combinations of mode values from the preceding list.
+        </para>
+
+        <para>
+          The descriptions include all mode values that are available in
+          the most recent version of MySQL. For older versions, a
+          combination mode does not include individual mode values that
+          are not available except in newer versions.
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ANSI SQL mode</primary>
+              </indexterm>
+
+              <literal>ANSI</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>REAL_AS_FLOAT</literal>,
+              <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>. See
+              <xref linkend="ansi-mode"/>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>DB2 SQL mode</primary>
+              </indexterm>
+
+              <literal>DB2</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MAXDB SQL mode</primary>
+              </indexterm>
+
+              <literal>MAXDB</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>,
+              <literal>NO_AUTO_CREATE_USER</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MSSQL SQL mode</primary>
+              </indexterm>
+
+              <literal>MSSQL</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MYSQL323 SQL mode</primary>
+              </indexterm>
+
+              <literal>MYSQL323</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
+              <literal>HIGH_NOT_PRECEDENCE</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>MYSQL40 SQL mode</primary>
+              </indexterm>
+
+              <literal>MYSQL40</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>NO_FIELD_OPTIONS</literal>,
+              <literal>HIGH_NOT_PRECEDENCE</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>ORACLE SQL mode</primary>
+              </indexterm>
+
+              <literal>ORACLE</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>,
+              <literal>NO_AUTO_CREATE_USER</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>POSTGRESQL SQL mode</primary>
+              </indexterm>
+
+              <literal>POSTGRESQL</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>PIPES_AS_CONCAT</literal>,
+              <literal>ANSI_QUOTES</literal>,
+              <literal>IGNORE_SPACE</literal>,
+              <literal>NO_KEY_OPTIONS</literal>,
+              <literal>NO_TABLE_OPTIONS</literal>,
+              <literal>NO_FIELD_OPTIONS</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <indexterm>
+                <primary>TRADITIONAL SQL mode</primary>
+              </indexterm>
+
+              <literal>TRADITIONAL</literal>
+            </para>
+
+            <para>
+              Equivalent to <literal>STRICT_TRANS_TABLES</literal>,
+              <literal>STRICT_ALL_TABLES</literal>,
+              <literal>NO_ZERO_IN_DATE</literal>,
+              <literal>NO_ZERO_DATE</literal>,
+              <literal>ERROR_FOR_DIVISION_BY_ZERO</literal>,
+              <literal>NO_AUTO_CREATE_USER</literal>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </refsection>
+
       <refsection id="server-shutdown">
 
         <title>&title-server-shutdown;</title>

Thread
svn commit - mysqldoc@docsrva: r1004 - in trunk: . refman-4.1 refman-5.0 refman-5.1paul23 Jan