List:Commits« Previous MessageNext Message »
From:jon.stephens Date:April 1 2010 2:30pm
Subject:svn commit - mysqldoc@docsrva: r19812 - trunk/refman-6.0
View as plain text  
Author: jstephens
Date: 2010-04-01 16:30:57 +0200 (Thu, 01 Apr 2010)
New Revision: 19812

Log:

New section was added at wrong level



Modified:
   trunk/refman-6.0/replication-notes.xml


Modified: trunk/refman-6.0/replication-notes.xml
===================================================================
--- trunk/refman-6.0/replication-notes.xml	2010-04-01 13:26:38 UTC (rev 19811)
+++ trunk/refman-6.0/replication-notes.xml	2010-04-01 14:30:57 UTC (rev 19812)
Changed blocks: 1, Lines Added: 664, Lines Deleted: 450; 51628 bytes

@@ -474,560 +474,774 @@
 
     </section>
 
-    <section id="replication-features-different-data-types">
+    <section id="replication-features-differing-tables">
 
-      <title>Replication of Columns Having Different Data Types</title>
+      <title>Replication with Differing Table Definitions on Master and Slave</title>
 
       <indexterm>
-        <primary>attribute promotion</primary>
-        <secondary>replication</secondary>
-      </indexterm>
-
-      <indexterm>
         <primary>replication</primary>
-        <secondary>attribute promotion</secondary>
+        <secondary>with differing tables on master and slave</secondary>
       </indexterm>
 
-      <indexterm>
-        <primary>attribute demotion</primary>
-        <secondary>replication</secondary>
-      </indexterm>
-
-      <indexterm>
-        <primary>replication</primary>
-        <secondary>attribute demotion</secondary>
-      </indexterm>
-
       <para>
-        Corresponding columns on the master&apos;s and the slave&apos;s
-        copies of the same table ideally should have the same data type.
-        However, beginning with MySQL 5.1.21, this is not always
-        strictly enforced, as long as certain conditions are met.
+        Source and target tables for replication do not have to be
+        identical. A table on the master can have more or fewer columns
+        than the slave's copy of the table. In addition, corresponding
+        table columns on the master and the slave can use different data
+        types, subject to certain conditions.
       </para>
 
       <para>
-        All other things being equal, it is always possible to replicate
-        from a column of a given data type to another column of the same
-        type and same size or width, where applicable, or larger. For
-        example, you can replicate from a <literal>CHAR(10)</literal>
-        column to another <literal>CHAR(10)</literal>, or from a
-        <literal>CHAR(10)</literal> column to a
-        <literal>CHAR(25)</literal> column without any problems. In
-        certain cases, it also possible to replicate from a column
-        having one data type (on the master) to a column having a
-        different data type (on the slave); when the data type of the
-        master&apos;s version of the column is promoted to a type that
-        is the same size or larger on the slave, this is known as
-        <firstterm>attribute promotion</firstterm>.
-      </para>
+        In all cases where the source and target tables do not have
+        identical definitions, the following must be true in order for
+        replication to work:
 
-      <para>
-        Attribute promotion can be used with both statement-based and
-        row-based replication, and is not dependent on the storage
-        engine used by either the master or the slave. However, the
-        choice of logging format does have an effect on the type
-        conversions that are allowed; the particulars are discussed
-        later in this section.
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              You must be using row-based replication. (Using
+              <literal>MIXED</literal> for the binary logging format
+              does not work.)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              The database and table names must be the same on both the
+              master and the slave.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+        Additional conditions are discussed, with examples, in the
+        following two sections.
       </para>
 
-      <important>
+      <section id="replication-features-more-columns">
+
+        <title>Replication with More Columns on Master or Slave</title>
+
         <para>
-          Whether you use statement-based or row-based replication, the
-          slave&apos;s copy of the table cannot contain more columns
-          than the master&apos;s copy if you wish to employ attribute
-          promotion.
-        </para>
-      </important>
+          You can replicate a table from the master to the slave such
+          that the master and slave copies of the table have differing
+          numbers of columns, subject to the following conditions:
 
-      <formalpara>
+          <itemizedlist>
 
-        <title>Statement-based replication</title>
+            <listitem>
+              <para>
+                Columns common to both versions of the table must be
+                defined in the same order on the master and the slave.
+              </para>
+            </listitem>
 
-        <para>
-          When using statement-based replication, a simple rule of thumb
-          to follow is, <quote>If the statement run on the master would
-          also execute successfully on the slave, it should also
-          replicate successfully</quote>. In other words, if the
-          statement uses a value that is compatible with the type of a
-          given column on the slave, the statement can be replicated.
-          For example, you can insert any value that fits in a
-          <literal>TINYINT</literal> column into a
-          <literal>BIGINT</literal> column as well; it follows that,
-          even if you change the type of a <literal>TINYINT</literal>
-          column in the slave&apos;s copy of a table to
-          <literal>BIGINT</literal>, any insert into that column on the
-          master that succeeds should also succeed on the slave, since
-          it is impossible to have a legal <literal>TINYINT</literal>
-          value that is large enough to exceed a
-          <literal>BIGINT</literal> column.
-        </para>
+            <listitem>
+              <para>
+                Columns common to both versions of the table must be
+                defined before any additional columns.
+              </para>
+            </listitem>
 
-      </formalpara>
+            <listitem>
+              <para>
+                Each <quote>extra</quote> column in the version of the
+                table having more columns must have a default value.
+              </para>
 
-      <formalpara id="replication-features-attribute-promotion">
+              <note>
+                <para>
+                  A column's default value is determined by a number of
+                  factors, including its type, whether it is defined
+                  with a <literal>DEFAULT</literal> option, whether it
+                  is declared as <literal>NULL</literal>, and the server
+                  SQL mode in effect at the time of its creation; for
+                  more information, see
+                  <xref linkend="data-type-defaults"/>).
+                </para>
+              </note>
+            </listitem>
 
-        <title>Row-based replication: attribute promotion and demotion</title>
+          </itemizedlist>
 
-        <para>
-          Formerly, due to the fact that in row-based replication
-          changes rather than statements are replicated, and that these
-          changes are transmitted using formats that do not always map
-          directly to MySQL server column datatypes, you could not
-          replicate between different subtypes of the same general type
-          (for example, from <literal>TINYINT</literal> to
-          <literal>BIGINT</literal>, both <literal>INT</literal>
-          subtypes). However, beginning with MySQL 6.0.14, MySQL
-          Replication supports attribute promotion and demotion between
-          smaller data types and larger types. It is also possible to
-          specify whether or not to allow lossy (truncated) or non-lossy
-          conversions of demoted column values, as explained later in
-          this section.
+          In addition, when the slave's copy of the table has more
+          columns than the master's copy, each column common to the
+          tables must use the same data type in both tables.
         </para>
 
-      </formalpara>
+        <formalpara>
 
-      <formalpara>
+          <title>Examples</title>
 
-        <title>Lossy and non-lossy conversions</title>
+          <para>
+            The following examples illustrate some valid and invalid
+            table definitions:
 
-        <para>
-          In the event that the target type cannot represent the value
-          being inserted, a decision must be made on how to handle the
-          conversion. If we allow the conversion but truncate (or
-          otherwise modify) the source value to achieve a
-          <quote>fit</quote> in the target column, we make what is known
-          as a <firstterm>lossy conversion</firstterm>. A conversion
-          which does not require truncation or similar modifications to
-          fit the source column value in the target column is a
-          <firstterm>non-lossy</firstterm> conversion.
-        </para>
+            <itemizedlist>
 
-      </formalpara>
+              <listitem>
+                <formalpara>
 
-      <formalpara>
+                  <title>More columns on the master</title>
 
-        <title>Type conversion modes (<literal>slave_type_conversions</literal>
-          variable)</title>
+                  <para>
+                    The following table definitions are valid and
+                    replicate correctly:
 
-        <para>
-          The setting of the <literal>slave_type_conversions</literal>
-          global server variable controls the type conversion mode used
-          on the slave. This variable takes a set of values from the
-          following table, which shows the effects of each mode on the
-          slave&apos;s type-conversion behavior:
-        </para>
+<programlisting>
+master&gt; <userinput>CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);</userinput>
+slave&gt;  <userinput>CREATE TABLE t1 (c1 INT, c2 INT);</userinput>
+</programlisting>
 
-      </formalpara>
+                    The following table definitions would raise Error
+                    1532
+                    (<literal role="error">ER_BINLOG_ROW_RBR_TO_SBR</literal>)
+                    because the definitions of the columns common to
+                    both versions of the table are in a different order
+                    on the slave than they are on the master:
 
-      <informaltable>
-        <tgroup cols="2">
-          <colspec colwidth="25*"/>
-          <colspec colwidth="75*"/>
-          <thead>
-            <row>
-              <entry>Mode</entry>
-              <entry>Effect</entry>
-            </row>
-          </thead>
-          <tbody>
-            <row>
-              <entry><literal>ALL_LOSSY</literal></entry>
-              <entry><para>
-                  In this mode, type conversions that would mean loss of
-                  information are allowed.
-                </para>
+<programlisting>
+master&gt; <userinput>CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);</userinput>
+slave&gt;  <userinput>CREATE TABLE t1 (c2 INT, c1 INT);</userinput>
+</programlisting>
 
+                    The following table definitions would also raise
+                    Error 1532 because the definition of the extra
+                    column on the master appears before the definitions
+                    of the columns common to both versions of the table:
 
+<programlisting>
+master&gt; <userinput>CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);</userinput>
+slave&gt;  <userinput>CREATE TABLE t1 (c1 INT, c2 INT);</userinput>
+</programlisting>
+                  </para>
 
-                <para>
-                  This does not imply that non-lossy conversions are
-                  allowed, merely that only cases requiring either lossy
-                  conversions or no conversion at all are allowed; for
-                  example, enabling <emphasis>only</emphasis> this mode
-                  allows an <literal>INT</literal> column to be
-                  converted to <literal>TINYINT</literal> (a lossy
-                  conversion), but not a <literal>TINYINT</literal>
-                  column to an <literal>INT</literal> column
-                  (non-lossy). Attempting the latter conversion in this
-                  case would cause replication to stop with an error on
-                  the slave.
-                </para></entry>
-            </row>
-            <row>
-              <entry><literal>ALL_NON_LOSSY</literal></entry>
-              <entry><para>
-                  This mode permits conversions that do not require
-                  truncation or other special handling of the source
-                  value; that is, it allows conversions where the source
-                  type has a wider range than the source type.
-                </para>
+                </formalpara>
+              </listitem>
 
+              <listitem>
+                <formalpara>
 
+                  <title>More columns on the slave</title>
 
-                <para>
-                  Setting this mode has no bearing on whether lossy
-                  conversions are allowed; this is controlled with the
-                  <literal>ALL_LOSSY</literal> mode. If only
-                  <literal>ALL_NON_LOSSY</literal> is set, but not
-                  <literal>ALL_LOSSY</literal>, then attempting a
-                  conversion that would result in the loss of data (such
-                  as <literal>INT</literal> to
-                  <literal>TINYINT</literal>, or
-                  <literal>CHAR(25)</literal> to
-                  <literal>VARCHAR(20)</literal>) causes the slave to
-                  stop with an error.
-                </para></entry>
-            </row>
-            <row>
-              <entry><literal>ALL_LOSSY,ALL_NON_LOSSY</literal></entry>
-              <entry><para>
-                  When this mode is set, all supported type conversions
-                  are allowed, whether or not they are lossy
-                  conversions.
-                </para></entry>
-            </row>
-            <row>
-              <entry>[<emphasis>empty</emphasis>]</entry>
-              <entry><para>
-                  When <literal>slave_type_conversions</literal> is not
-                  set, no attribute promotion or demotion is permitted;
-                  this means that all columns in the source and target
-                  tables must be of the same types.
-                </para>
+                  <para>
+                    The following table definitions are valid and
+                    replicate correctly:
 
+<programlisting>
+master&gt; <userinput>CREATE TABLE t1 (c1 INT, c2 INT);</userinput>
+slave&gt;  <userinput>CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);</userinput>
+                  </programlisting>
 
+                    The following definitions raise Error 1532 because
+                    the columns common to both versions of the table are
+                    not defined in the same order on both the master and
+                    the slave:
 
-                <para>
-                  This mode is the default.
-                </para></entry>
-            </row>
-          </tbody>
-        </tgroup>
-      </informaltable>
+<programlisting>
+master&gt; <userinput>CREATE TABLE t1 (c1 INT, c2 INT);</userinput>
+slave&gt;  <userinput>CREATE TABLE t1 (c2 INT, c1 INT, c3 INT);</userinput>
+</programlisting>
 
-      <para>
-        Changing the type conversion mode requires restarting the slave
-        with the new <literal>slave_type_conversions</literal> setting.
-      </para>
+                    The following table definitions also raise Error
+                    1532 because the definition for the extra column in
+                    the slave's version of the table appears before the
+                    definitions for the columns which are common to both
+                    versions of the table:
 
-      <formalpara>
+<programlisting>
+master&gt; <userinput>CREATE TABLE t1 (c1 INT, c2 INT);</userinput>
+slave&gt;  <userinput>CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);</userinput>
+</programlisting>
 
-        <title>Supported conversions</title>
+                    The following table definitions fail because the
+                    slave's version of the table has additional columns
+                    compared to the master's version, and the two
+                    versions of the table use different data types for
+                    the common column <literal>c2</literal>:
 
+<programlisting>
+master&gt; <userinput>CREATE TABLE t1 (c1 INT, c2 BIGINT);</userinput>
+slave&gt;  <userinput>CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);</userinput>
+</programlisting>
+                  </para>
+
+                </formalpara>
+              </listitem>
+
+            </itemizedlist>
+          </para>
+
+        </formalpara>
+
+      </section>
+
+      <section id="replication-features-different-data-types">
+
+        <title>Replication of Columns Having Different Data Types</title>
+
+        <indexterm>
+          <primary>attribute promotion</primary>
+          <secondary>replication</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>replication</primary>
+          <secondary>attribute promotion</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>attribute demotion</primary>
+          <secondary>replication</secondary>
+        </indexterm>
+
+        <indexterm>
+          <primary>replication</primary>
+          <secondary>attribute demotion</secondary>
+        </indexterm>
+
         <para>
-          Supported conversions between different but similar data types
-          are shown in the following list:
+          Corresponding columns on the master&apos;s and the
+          slave&apos;s copies of the same table ideally should have the
+          same data type. However, beginning with MySQL 5.1.21, this is
+          not always strictly enforced, as long as certain conditions
+          are met.
         </para>
 
-      </formalpara>
+        <para>
+          All other things being equal, it is always possible to
+          replicate from a column of a given data type to another column
+          of the same type and same size or width, where applicable, or
+          larger. For example, you can replicate from a
+          <literal>CHAR(10)</literal> column to another
+          <literal>CHAR(10)</literal>, or from a
+          <literal>CHAR(10)</literal> column to a
+          <literal>CHAR(25)</literal> column without any problems. In
+          certain cases, it also possible to replicate from a column
+          having one data type (on the master) to a column having a
+          different data type (on the slave); when the data type of the
+          master&apos;s version of the column is promoted to a type that
+          is the same size or larger on the slave, this is known as
+          <firstterm>attribute promotion</firstterm>.
+        </para>
 
-      <itemizedlist>
+        <para>
+          Attribute promotion can be used with both statement-based and
+          row-based replication, and is not dependent on the storage
+          engine used by either the master or the slave. However, the
+          choice of logging format does have an effect on the type
+          conversions that are allowed; the particulars are discussed
+          later in this section.
+        </para>
 
-        <listitem>
+        <important>
           <para>
-            Between any of the integer types
-            <literal role="type">TINYINT</literal>,
-            <literal role="type">SMALLINT</literal>,
-            <literal role="type">MEDIUMINT</literal>,
-            <literal role="type">INT</literal>, and
-            <literal role="type">BIGINT</literal>.
+            Whether you use statement-based or row-based replication,
+            the slave&apos;s copy of the table cannot contain more
+            columns than the master&apos;s copy if you wish to employ
+            attribute promotion.
           </para>
+        </important>
 
-          <para>
-            This includes conversions between the signed and unsigned
-            versions of these types.
-          </para>
+        <formalpara>
 
-          <para>
-            Lossy conversions are made by truncating the source value to
-            the maximum (or minimum) allowed by the target column. For
-            insuring non-lossy conversions when going from unsigned to
-            signed types, the target column must be large enough to
-            accomodate the range of values in the source column. For
-            example, you can demote <literal>TINYINT UNSIGNED</literal>
-            non-lossily to <literal>SMALLINT</literal>, but not to
-            <literal>TINYINT</literal>.
-          </para>
-        </listitem>
+          <title>Statement-based replication</title>
 
-        <listitem>
           <para>
-            Between any of the decimal types
-            <literal role="type">DECIMAL</literal>,
-            <literal role="type">FLOAT</literal>,
-            <literal role="type">DOUBLE</literal>, and
-            <literal role="type">NUMERIC</literal>.
+            When using statement-based replication, a simple rule of
+            thumb to follow is, <quote>If the statement run on the
+            master would also execute successfully on the slave, it
+            should also replicate successfully</quote>. In other words,
+            if the statement uses a value that is compatible with the
+            type of a given column on the slave, the statement can be
+            replicated. For example, you can insert any value that fits
+            in a <literal>TINYINT</literal> column into a
+            <literal>BIGINT</literal> column as well; it follows that,
+            even if you change the type of a <literal>TINYINT</literal>
+            column in the slave&apos;s copy of a table to
+            <literal>BIGINT</literal>, any insert into that column on
+            the master that succeeds should also succeed on the slave,
+            since it is impossible to have a legal
+            <literal>TINYINT</literal> value that is large enough to
+            exceed a <literal>BIGINT</literal> column.
           </para>
 
+        </formalpara>
+
+        <formalpara id="replication-features-attribute-promotion">
+
+          <title>Row-based replication: attribute promotion and demotion</title>
+
           <para>
-            <literal>FLOAT</literal> to <literal>DOUBLE</literal> is a
-            non-lossy conversion; <literal>DOUBLE</literal> to
-            <literal>FLOAT</literal> can only be handled lossily. A
-            conversion from
-            <literal>DECIMAL(<replaceable>M</replaceable>,<replaceable>D</replaceable>)</literal>
-            to
-            <literal>DECIMAL(<replaceable>M'</replaceable>,<replaceable>D'</replaceable>)</literal>
-            where <literal><replaceable>M'</replaceable> =&gt;
-            <replaceable>M</replaceable></literal> and
-            <literal><replaceable>D'</replaceable> =&gt;
-            <replaceable>D</replaceable></literal> is non-lossy; for any
-            case where <literal><replaceable>M'</replaceable> &lt;
-            <replaceable>M</replaceable></literal>,
-            <literal><replaceable>D'</replaceable> &lt;
-            <replaceable>D</replaceable></literal>, or both, only a
-            lossy conversion can be made.
+            Formerly, due to the fact that in row-based replication
+            changes rather than statements are replicated, and that
+            these changes are transmitted using formats that do not
+            always map directly to MySQL server column datatypes, you
+            could not replicate between different subtypes of the same
+            general type (for example, from <literal>TINYINT</literal>
+            to <literal>BIGINT</literal>, both <literal>INT</literal>
+            subtypes). However, beginning with MySQL 6.0.14, MySQL
+            Replication supports attribute promotion and demotion
+            between smaller data types and larger types. It is also
+            possible to specify whether or not to allow lossy
+            (truncated) or non-lossy conversions of demoted column
+            values, as explained later in this section.
           </para>
 
+        </formalpara>
+
+        <formalpara>
+
+          <title>Lossy and non-lossy conversions</title>
+
           <para>
-            For any of the decimal types, if a value to be stored cannot
-            be fit in the target type, the value is rounded down
-            according to the rounding rules defined for the server
-            elsewhere in the documentation. See
-            <xref linkend="precision-math-rounding"/>, for information
-            about how this is done for decimal types.
+            In the event that the target type cannot represent the value
+            being inserted, a decision must be made on how to handle the
+            conversion. If we allow the conversion but truncate (or
+            otherwise modify) the source value to achieve a
+            <quote>fit</quote> in the target column, we make what is
+            known as a <firstterm>lossy conversion</firstterm>. A
+            conversion which does not require truncation or similar
+            modifications to fit the source column value in the target
+            column is a <firstterm>non-lossy</firstterm> conversion.
           </para>
-        </listitem>
 
-        <listitem>
+        </formalpara>
+
+        <formalpara>
+
+          <title>Type conversion modes (<literal>slave_type_conversions</literal>
+            variable)</title>
+
           <para>
-            Between any of the string types
-            <literal role="type">CHAR</literal>,
-            <literal role="type">VARCHAR</literal>, and
-            <literal role="type">TEXT</literal>, including conversions
-            between different widths.
+            The setting of the <literal>slave_type_conversions</literal>
+            global server variable controls the type conversion mode
+            used on the slave. This variable takes a set of values from
+            the following table, which shows the effects of each mode on
+            the slave&apos;s type-conversion behavior:
           </para>
 
+        </formalpara>
+
+        <informaltable>
+          <tgroup cols="2">
+            <colspec colwidth="25*"/>
+            <colspec colwidth="75*"/>
+            <thead>
+              <row>
+                <entry>Mode</entry>
+                <entry>Effect</entry>
+              </row>
+            </thead>
+            <tbody>
+              <row>
+                <entry><literal>ALL_LOSSY</literal></entry>
+                <entry><para>
+                    In this mode, type conversions that would mean loss
+                    of information are allowed.
+                  </para>
+
+
+
+                  <para>
+                    This does not imply that non-lossy conversions are
+                    allowed, merely that only cases requiring either
+                    lossy conversions or no conversion at all are
+                    allowed; for example, enabling
+                    <emphasis>only</emphasis> this mode allows an
+                    <literal>INT</literal> column to be converted to
+                    <literal>TINYINT</literal> (a lossy conversion), but
+                    not a <literal>TINYINT</literal> column to an
+                    <literal>INT</literal> column (non-lossy).
+                    Attempting the latter conversion in this case would
+                    cause replication to stop with an error on the
+                    slave.
+                  </para></entry>
+              </row>
+              <row>
+                <entry><literal>ALL_NON_LOSSY</literal></entry>
+                <entry><para>
+                    This mode permits conversions that do not require
+                    truncation or other special handling of the source
+                    value; that is, it allows conversions where the
+                    source type has a wider range than the source type.
+                  </para>
+
+
+
+                  <para>
+                    Setting this mode has no bearing on whether lossy
+                    conversions are allowed; this is controlled with the
+                    <literal>ALL_LOSSY</literal> mode. If only
+                    <literal>ALL_NON_LOSSY</literal> is set, but not
+                    <literal>ALL_LOSSY</literal>, then attempting a
+                    conversion that would result in the loss of data
+                    (such as <literal>INT</literal> to
+                    <literal>TINYINT</literal>, or
+                    <literal>CHAR(25)</literal> to
+                    <literal>VARCHAR(20)</literal>) causes the slave to
+                    stop with an error.
+                  </para></entry>
+              </row>
+              <row>
+                <entry><literal>ALL_LOSSY,ALL_NON_LOSSY</literal></entry>
+                <entry><para>
+                    When this mode is set, all supported type
+                    conversions are allowed, whether or not they are
+                    lossy conversions.
+                  </para></entry>
+              </row>
+              <row>
+                <entry>[<emphasis>empty</emphasis>]</entry>
+                <entry><para>
+                    When <literal>slave_type_conversions</literal> is
+                    not set, no attribute promotion or demotion is
+                    permitted; this means that all columns in the source
+                    and target tables must be of the same types.
+                  </para>
+
+
+
+                  <para>
+                    This mode is the default.
+                  </para></entry>
+              </row>
+            </tbody>
+          </tgroup>
+        </informaltable>
+
+        <para>
+          Changing the type conversion mode requires restarting the
+          slave with the new <literal>slave_type_conversions</literal>
+          setting.
+        </para>
+
+        <formalpara>
+
+          <title>Supported conversions</title>
+
           <para>
-            Conversion of a <literal>CHAR</literal>,
-            <literal>VARCHAR</literal>, or <literal>TEXT</literal> to a
-            <literal>CHAR</literal>, <literal>VARCHAR</literal>, or
-            <literal>TEXT</literal> column the same size or larger is
-            never lossy. Lossy conversion is handled by inserting only
-            the first <replaceable>N</replaceable> characters of the
-            string on the slave, where <replaceable>N</replaceable> is
-            the width of the target column.
+            Supported conversions between different but similar data
+            types are shown in the following list:
           </para>
 
-          <important>
+        </formalpara>
+
+        <itemizedlist>
+
+          <listitem>
             <para>
-              Replication between columns using different character sets
-              is not supported.
+              Between any of the integer types
+              <literal role="type">TINYINT</literal>,
+              <literal role="type">SMALLINT</literal>,
+              <literal role="type">MEDIUMINT</literal>,
+              <literal role="type">INT</literal>, and
+              <literal role="type">BIGINT</literal>.
             </para>
-          </important>
-        </listitem>
 
-        <listitem>
-          <para>
-            Between any of the binary data types
-            <literal role="type">BINARY</literal>,
-            <literal role="type">VARBINARY</literal>, and
-            <literal role="type">BLOB</literal>, including conversions
-            between different widths.
-          </para>
+            <para>
+              This includes conversions between the signed and unsigned
+              versions of these types.
+            </para>
 
-          <para>
-            Conversion of a <literal>BINARY</literal>,
-            <literal>VARBINARY</literal>, or <literal>BLOB</literal> to
-            a <literal>BINARY</literal>, <literal>VARBINARY</literal>,
-            or <literal>BLOB</literal> column the same size or larger is
-            never lossy. Lossy conversion is handled by inserting only
-            the first <replaceable>N</replaceable> bytes of the string
-            on the slave, where <replaceable>N</replaceable> is the
-            width of the target column.
-          </para>
-        </listitem>
+            <para>
+              Lossy conversions are made by truncating the source value
+              to the maximum (or minimum) allowed by the target column.
+              For insuring non-lossy conversions when going from
+              unsigned to signed types, the target column must be large
+              enough to accomodate the range of values in the source
+              column. For example, you can demote <literal>TINYINT
+              UNSIGNED</literal> non-lossily to
+              <literal>SMALLINT</literal>, but not to
+              <literal>TINYINT</literal>.
+            </para>
+          </listitem>
 
-        <listitem>
-          <para>
-            Between any 2 <literal role="type">BIT</literal> columns of
-            any 2 sizes.
-          </para>
+          <listitem>
+            <para>
+              Between any of the decimal types
+              <literal role="type">DECIMAL</literal>,
+              <literal role="type">FLOAT</literal>,
+              <literal role="type">DOUBLE</literal>, and
+              <literal role="type">NUMERIC</literal>.
+            </para>
 
-          <para>
-            When inserting a value from a
-            <literal>BIT(<replaceable>M</replaceable>)</literal> column
-            into a <literal>BIT(<replaceable>M'</replaceable>)</literal>
-            column, where <literal><replaceable>M'</replaceable> &gt;
-            <replaceable>M</replaceable></literal>, the most significant
-            bits of the
-            <literal>BIT(<replaceable>M'</replaceable>)</literal>
-            columns are cleared (set to zero) and the
-            <replaceable>M</replaceable> bits of the
-            <literal>BIT(<replaceable>M</replaceable>)</literal> value
-            are set as the least significant bits of the
-            <literal>BIT(<replaceable>M'</replaceable>)</literal>
-            column.
-          </para>
+            <para>
+              <literal>FLOAT</literal> to <literal>DOUBLE</literal> is a
+              non-lossy conversion; <literal>DOUBLE</literal> to
+              <literal>FLOAT</literal> can only be handled lossily. A
+              conversion from
+              <literal>DECIMAL(<replaceable>M</replaceable>,<replaceable>D</replaceable>)</literal>
+              to
+              <literal>DECIMAL(<replaceable>M'</replaceable>,<replaceable>D'</replaceable>)</literal>
+              where <literal><replaceable>M'</replaceable> =&gt;
+              <replaceable>M</replaceable></literal> and
+              <literal><replaceable>D'</replaceable> =&gt;
+              <replaceable>D</replaceable></literal> is non-lossy; for
+              any case where <literal><replaceable>M'</replaceable> &lt;
+              <replaceable>M</replaceable></literal>,
+              <literal><replaceable>D'</replaceable> &lt;
+              <replaceable>D</replaceable></literal>, or both, only a
+              lossy conversion can be made.
+            </para>
 
-          <para>
-            When inserting a value from a source
-            <literal>BIT(<replaceable>M</replaceable>)</literal> column
-            into a target
-            <literal>BIT(<replaceable>M'</replaceable>)</literal>
-            column, where <literal><replaceable>M'</replaceable> &lt;
-            <replaceable>M</replaceable></literal>, the maximum possible
-            value for the
-            <literal>BIT(<replaceable>M'</replaceable>)</literal> column
-            is assigned; in other words, an <quote>all-set</quote> value
-            is assigned to the target column.
-          </para>
-        </listitem>
+            <para>
+              For any of the decimal types, if a value to be stored
+              cannot be fit in the target type, the value is rounded
+              down according to the rounding rules defined for the
+              server elsewhere in the documentation. See
+              <xref linkend="precision-math-rounding"/>, for information
+              about how this is done for decimal types.
+            </para>
+          </listitem>
 
-      </itemizedlist>
+          <listitem>
+            <para>
+              Between any of the string types
+              <literal role="type">CHAR</literal>,
+              <literal role="type">VARCHAR</literal>, and
+              <literal role="type">TEXT</literal>, including conversions
+              between different widths.
+            </para>
 
-      <para>
-        Conversions between types not in the previous list are not
-        permitted.
-      </para>
+            <para>
+              Conversion of a <literal>CHAR</literal>,
+              <literal>VARCHAR</literal>, or <literal>TEXT</literal> to
+              a <literal>CHAR</literal>, <literal>VARCHAR</literal>, or
+              <literal>TEXT</literal> column the same size or larger is
+              never lossy. Lossy conversion is handled by inserting only
+              the first <replaceable>N</replaceable> characters of the
+              string on the slave, where <replaceable>N</replaceable> is
+              the width of the target column.
+            </para>
 
-      <formalpara id="replication-features-type-conversions-old">
+            <important>
+              <para>
+                Replication between columns using different character
+                sets is not supported.
+              </para>
+            </important>
+          </listitem>
 
-        <title>Replication type conversions in MySQL 6.0.14 and earlier</title>
+          <listitem>
+            <para>
+              Between any of the binary data types
+              <literal role="type">BINARY</literal>,
+              <literal role="type">VARBINARY</literal>, and
+              <literal role="type">BLOB</literal>, including conversions
+              between different widths.
+            </para>
 
-        <para>
-          Prior to MySQL 6.0.14, with row-based binary logging, you
-          could not replicate between different <literal>INT</literal>
-          subtypes, such as from <literal>TINYINT</literal> to
-          <literal>BIGINT</literal>, because changes to columns of these
-          types were represented differently from one another in the
-          binary log when using row-based logging. (However, you could
-          replicate from <literal>BLOB</literal> to
-          <literal>TEXT</literal> using row-based replication because
-          changes to <literal>BLOB</literal> and <literal>TEXT</literal>
-          columns were represented using the same format in the binary
-          log.)
-        </para>
+            <para>
+              Conversion of a <literal>BINARY</literal>,
+              <literal>VARBINARY</literal>, or <literal>BLOB</literal>
+              to a <literal>BINARY</literal>,
+              <literal>VARBINARY</literal>, or <literal>BLOB</literal>
+              column the same size or larger is never lossy. Lossy
+              conversion is handled by inserting only the first
+              <replaceable>N</replaceable> bytes of the string on the
+              slave, where <replaceable>N</replaceable> is the width of
+              the target column.
+            </para>
+          </listitem>
 
-      </formalpara>
+          <listitem>
+            <para>
+              Between any 2 <literal role="type">BIT</literal> columns
+              of any 2 sizes.
+            </para>
 
-      <para>
-        Supported conversions for attribute promotion when using
-        row-based replication prior to MySQL 6.0.14 are shown in the
-        following table:
-      </para>
+            <para>
+              When inserting a value from a
+              <literal>BIT(<replaceable>M</replaceable>)</literal>
+              column into a
+              <literal>BIT(<replaceable>M'</replaceable>)</literal>
+              column, where <literal><replaceable>M'</replaceable> &gt;
+              <replaceable>M</replaceable></literal>, the most
+              significant bits of the
+              <literal>BIT(<replaceable>M'</replaceable>)</literal>
+              columns are cleared (set to zero) and the
+              <replaceable>M</replaceable> bits of the
+              <literal>BIT(<replaceable>M</replaceable>)</literal> value
+              are set as the least significant bits of the
+              <literal>BIT(<replaceable>M'</replaceable>)</literal>
+              column.
+            </para>
 
-      <informaltable>
-        <tgroup cols="2">
-          <colspec colwidth="50*"/>
-          <colspec colwidth="50*"/>
-          <thead>
-            <row>
-              <entry>From (Master)</entry>
-              <entry>To (Slave)</entry>
-            </row>
-          </thead>
-          <tbody>
-            <row>
-              <entry><literal role="type">BINARY</literal></entry>
-              <entry><literal role="type">CHAR</literal></entry>
-            </row>
-            <row>
-              <entry><literal role="type">BLOB</literal></entry>
-              <entry><literal role="type">TEXT</literal></entry>
-            </row>
-            <row>
-              <entry><literal role="type">CHAR</literal></entry>
-              <entry><literal role="type">BINARY</literal></entry>
-            </row>
-            <row>
-              <entry><literal role="type">DECIMAL</literal></entry>
-              <entry><literal role="type">NUMERIC</literal></entry>
-            </row>
-            <row>
-              <entry><literal role="type">NUMERIC</literal></entry>
-              <entry><literal role="type">DECIMAL</literal></entry>
-            </row>
-            <row>
-              <entry><literal role="type">TEXT</literal></entry>
-              <entry><literal role="type">BLOB</literal></entry>
-            </row>
-            <row>
-              <entry><literal role="type">VARBINARY</literal></entry>
-              <entry><literal role="type">VARCHAR</literal></entry>
-            </row>
-            <row>
-              <entry><literal role="type">VARCHAR</literal></entry>
-              <entry><literal role="type">VARBINARY</literal></entry>
-            </row>
-          </tbody>
-        </tgroup>
-      </informaltable>
+            <para>
+              When inserting a value from a source
+              <literal>BIT(<replaceable>M</replaceable>)</literal>
+              column into a target
+              <literal>BIT(<replaceable>M'</replaceable>)</literal>
+              column, where <literal><replaceable>M'</replaceable> &lt;
+              <replaceable>M</replaceable></literal>, the maximum
+              possible value for the
+              <literal>BIT(<replaceable>M'</replaceable>)</literal>
+              column is assigned; in other words, an
+              <quote>all-set</quote> value is assigned to the target
+              column.
+            </para>
+          </listitem>
 
-      <note>
-        <para>
-          In all cases, the size or width of the column on the slave
-          must be equal to or greater than that of the column on the
-          master. For example, you could replicate from a
-          <literal>CHAR(10)</literal> column on the master to a column
-          that used <literal>BINARY(10)</literal> or
-          <literal>BINARY(25)</literal> on the slave, but you could not
-          replicate from a <literal>CHAR(10)</literal> column on the
-          master to <literal>BINARY(5)</literal> column on the slave.
-        </para>
+        </itemizedlist>
 
         <para>
-          For <literal role="type">DECIMAL</literal> and
-          <literal role="type">NUMERIC</literal> columns, both the
-          mantissa (<emphasis>M</emphasis>) and the number of decimals
-          (<emphasis>D</emphasis>) must be the same size or larger on
-          the slave as compared with the master. For example,
-          replication from a <literal>NUMERIC(5,4)</literal> to a
-          <literal>DECIMAL(6,4)</literal> worked, but not from a
-          <literal>NUMERIC(5,4)</literal> to a
-          <literal>DECIMAL(5,3)</literal>.
+          Conversions between types not in the previous list are not
+          permitted.
         </para>
-      </note>
 
-      <para>
-        Prior to MySQL 6.0.14, MySQL replication did not support
-        attribute promotion of any of the following data types to or
-        from any other data type when using row-based replication:
-      </para>
+        <formalpara id="replication-features-type-conversions-old">
 
-      <itemizedlist>
+          <title>Replication type conversions in MySQL 6.0.14 and earlier</title>
 
-        <listitem>
           <para>
-            <literal role="type">INT</literal> (including
-            <literal>TINYINT</literal>, <literal>SMALLINT</literal>,
-            <literal>MEDIUMINT</literal>, <literal>BIGINT</literal>).
+            Prior to MySQL 6.0.14, with row-based binary logging, you
+            could not replicate between different <literal>INT</literal>
+            subtypes, such as from <literal>TINYINT</literal> to
+            <literal>BIGINT</literal>, because changes to columns of
+            these types were represented differently from one another in
+            the binary log when using row-based logging. (However, you
+            could replicate from <literal>BLOB</literal> to
+            <literal>TEXT</literal> using row-based replication because
+            changes to <literal>BLOB</literal> and
+            <literal>TEXT</literal> columns were represented using the
+            same format in the binary log.)
           </para>
 
-          <para>
-            Promotion between <literal role="type">INT</literal>
-            subtypes &mdash; for example, from
-            <literal>SMALLINT</literal> to <literal>BIGINT</literal>
-            &mdash; was also not supported prior to MySQL 6.0.14.
-          </para>
-        </listitem>
+        </formalpara>
 
-        <listitem>
-          <para>
-            <literal role="type">SET</literal> or
-            <literal role="type">ENUM</literal>.
-          </para>
-        </listitem>
+        <para>
+          Supported conversions for attribute promotion when using
+          row-based replication prior to MySQL 6.0.14 are shown in the
+          following table:
+        </para>
 
-        <listitem>
+        <informaltable>
+          <tgroup cols="2">
+            <colspec colwidth="50*"/>
+            <colspec colwidth="50*"/>
+            <thead>
+              <row>
+                <entry>From (Master)</entry>
+                <entry>To (Slave)</entry>
+              </row>
+            </thead>
+            <tbody>
+              <row>
+                <entry><literal role="type">BINARY</literal></entry>
+                <entry><literal role="type">CHAR</literal></entry>
+              </row>
+              <row>
+                <entry><literal role="type">BLOB</literal></entry>
+                <entry><literal role="type">TEXT</literal></entry>
+              </row>
+              <row>
+                <entry><literal role="type">CHAR</literal></entry>
+                <entry><literal role="type">BINARY</literal></entry>
+              </row>
+              <row>
+                <entry><literal role="type">DECIMAL</literal></entry>
+                <entry><literal role="type">NUMERIC</literal></entry>
+              </row>
+              <row>
+                <entry><literal role="type">NUMERIC</literal></entry>
+                <entry><literal role="type">DECIMAL</literal></entry>
+              </row>
+              <row>
+                <entry><literal role="type">TEXT</literal></entry>
+                <entry><literal role="type">BLOB</literal></entry>
+              </row>
+              <row>
+                <entry><literal role="type">VARBINARY</literal></entry>
+                <entry><literal role="type">VARCHAR</literal></entry>
+              </row>
+              <row>
+                <entry><literal role="type">VARCHAR</literal></entry>
+                <entry><literal role="type">VARBINARY</literal></entry>
+              </row>
+            </tbody>
+          </tgroup>
+        </informaltable>
+
+        <note>
           <para>
-            <literal role="type">FLOAT</literal> or
-            <literal role="type">DOUBLE</literal>.
+            In all cases, the size or width of the column on the slave
+            must be equal to or greater than that of the column on the
+            master. For example, you could replicate from a
+            <literal>CHAR(10)</literal> column on the master to a column
+            that used <literal>BINARY(10)</literal> or
+            <literal>BINARY(25)</literal> on the slave, but you could
+            not replicate from a <literal>CHAR(10)</literal> column on
+            the master to <literal>BINARY(5)</literal> column on the
+            slave.
           </para>
-        </listitem>
 
-        <listitem>
           <para>
-            All of the data types relating to dates, times, or both:
-            <literal role="type">DATE</literal>,
-            <literal role="type">TIME</literal>,
-            <literal role="type">DATETIME</literal>,
-            <literal role="type">TIMESTAMP</literal>, and
-            <literal role="type">YEAR</literal>.
+            For <literal role="type">DECIMAL</literal> and
+            <literal role="type">NUMERIC</literal> columns, both the
+            mantissa (<emphasis>M</emphasis>) and the number of decimals
+            (<emphasis>D</emphasis>) must be the same size or larger on
+            the slave as compared with the master. For example,
+            replication from a <literal>NUMERIC(5,4)</literal> to a
+            <literal>DECIMAL(6,4)</literal> worked, but not from a
+            <literal>NUMERIC(5,4)</literal> to a
+            <literal>DECIMAL(5,3)</literal>.
           </para>
-        </listitem>
+        </note>
 
-      </itemizedlist>
+        <para>
+          Prior to MySQL 6.0.14, MySQL replication did not support
+          attribute promotion of any of the following data types to or
+          from any other data type when using row-based replication:
+        </para>
 
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              <literal role="type">INT</literal> (including
+              <literal>TINYINT</literal>, <literal>SMALLINT</literal>,
+              <literal>MEDIUMINT</literal>, <literal>BIGINT</literal>).
+            </para>
+
+            <para>
+              Promotion between <literal role="type">INT</literal>
+              subtypes &mdash; for example, from
+              <literal>SMALLINT</literal> to <literal>BIGINT</literal>
+              &mdash; was also not supported prior to MySQL 6.0.14.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal role="type">SET</literal> or
+              <literal role="type">ENUM</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal role="type">FLOAT</literal> or
+              <literal role="type">DOUBLE</literal>.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              All of the data types relating to dates, times, or both:
+              <literal role="type">DATE</literal>,
+              <literal role="type">TIME</literal>,
+              <literal role="type">DATETIME</literal>,
+              <literal role="type">TIMESTAMP</literal>, and
+              <literal role="type">YEAR</literal>.
+            </para>
+          </listitem>
+
+        </itemizedlist>
+
+      </section>
+
     </section>
 
     <section id="replication-features-directory">


Thread
svn commit - mysqldoc@docsrva: r19812 - trunk/refman-6.0jon.stephens1 Apr