| List: | Commits | « Previous MessageNext Message » | |
| From: | jon | Date: | April 18 2008 4:44pm |
| Subject: | svn commit - mysqldoc@docsrva: r10529 - in trunk: it/refman-5.1 pt/refman-5.1 refman-4.1 refman-5.0 refman-5.1 refman-6.0 | ||
| View as plain text | |||
Author: jstephens Date: 2008-04-18 16:44:26 +0200 (Fri, 18 Apr 2008) New Revision: 10529 Log: Fixed typos and a couple of other issues in merge-table-problems (Thanks, Shane!) Modified: trunk/it/refman-5.1/se-merge.xml trunk/pt/refman-5.1/se-merge.xml trunk/refman-4.1/storage-engines.xml trunk/refman-5.0/se-merge.xml trunk/refman-5.1/se-merge.xml trunk/refman-6.0/se-merge.xml Modified: trunk/it/refman-5.1/se-merge.xml =================================================================== --- trunk/it/refman-5.1/se-merge.xml 2008-04-18 12:40:37 UTC (rev 10528) +++ trunk/it/refman-5.1/se-merge.xml 2008-04-18 14:44:26 UTC (rev 10529) Changed blocks: 3, Lines Added: 52, Lines Deleted: 17; 4237 bytes @@ -503,8 +503,39 @@ <listitem> <para> - <literal>REPLACE</literal> does not work. + <literal>REPLACE</literal> does not work as expected because + the <literal>MERGE</literal> engine cannot enforce uniqueness + over the set of underlying tables. The two key facts are: </para> + + <itemizedlist> + + <listitem> + <para> + <literal>REPLACE</literal> can detect unique key + violations only in the underlying table to which it is + going to write (which is determined by + <literal>INSERT_METHOD</literal>). This differs from + violations in the <literal>MERGE</literal> table itself. + </para> + </listitem> + + <listitem> + <para> + If <literal>REPLACE</literal> detects such a violation, it + will only change the corresponding row in the first + underlying table in which the row is present, whereas a + row with the same unique key value may be present in all + underlying tables. + </para> + </listitem> + + </itemizedlist> + + <para> + Similar considerations apply for <literal>INSERT ... ON + DUPLICATE KEY UPDATE</literal>. + </para> </listitem> <listitem> @@ -664,27 +695,29 @@ <listitem> <para> - If you encounter an error message similar to <literal>ERROR + If you encounter an error message similar to <errortext>ERROR 1017 (HY000): Can't find file: - '<replaceable>mm</replaceable>.MRG' (errno: 2)</literal> it + '<replaceable>mm</replaceable>.MRG' (errno: 2)</errortext> it generally indicates that some of the base tables are not using - the MyISAM storage engine. Confirm that all tables are MyISAM. + the <literal>MyISAM</literal> storage engine. Confirm that all + of these tables are <literal>MyISAM</literal>. </para> </listitem> <listitem> <para> - There is a limit of 2<superscript>32</superscript> - (~4.295E+09) rows to a <literal>MERGE</literal> table, just as - there is with a <literal>MyISAM</literal>, it is therefore not - possible to merge multiple <literal>MyISAM</literal> tables - that exceed this limitation. However, you build MySQL with the - <option>--with-big-tables</option> option then the row - limitation is increased to + The maximum number of rows in a <literal>MERGE</literal> table + is (2<superscript>32</superscript>)<superscript>2</superscript> - (1.844E+19) rows. See <xref linkend="configure-options"/>. - Beginning with MySQL 5.0.4 all standard binaries are built - with this option. + (~1.844E+19; the same as for a <literal>MyISAM</literal> + table), provided that the server was build using the + <option>--with-big-tables</option> option. (All standard MySQL + ¤t-series; standard binaries are built with this option; + for more information, see + <xref linkend="configure-options"/>.) It is not possible to + merge multiple <literal>MyISAM</literal> tables into a single + <literal>MERGE</literal> table that would have more than this + number of rows. </para> </listitem> @@ -720,9 +753,11 @@ TABLES is in effect. The following does <emphasis>not</emphasis> work: -<programlisting>CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...; - LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE; - ALTER TABLE m1 ... UNION=(t1,t2) ...;</programlisting> +<programlisting> +CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...; +LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE; +ALTER TABLE m1 ... UNION=(t1,t2) ...; +</programlisting> However, you can do this with a temporary <literal>MERGE</literal> table. Modified: trunk/pt/refman-5.1/se-merge.xml =================================================================== --- trunk/pt/refman-5.1/se-merge.xml 2008-04-18 12:40:37 UTC (rev 10528) +++ trunk/pt/refman-5.1/se-merge.xml 2008-04-18 14:44:26 UTC (rev 10529) Changed blocks: 3, Lines Added: 52, Lines Deleted: 17; 4237 bytes @@ -503,8 +503,39 @@ <listitem> <para> - <literal>REPLACE</literal> does not work. + <literal>REPLACE</literal> does not work as expected because + the <literal>MERGE</literal> engine cannot enforce uniqueness + over the set of underlying tables. The two key facts are: </para> + + <itemizedlist> + + <listitem> + <para> + <literal>REPLACE</literal> can detect unique key + violations only in the underlying table to which it is + going to write (which is determined by + <literal>INSERT_METHOD</literal>). This differs from + violations in the <literal>MERGE</literal> table itself. + </para> + </listitem> + + <listitem> + <para> + If <literal>REPLACE</literal> detects such a violation, it + will only change the corresponding row in the first + underlying table in which the row is present, whereas a + row with the same unique key value may be present in all + underlying tables. + </para> + </listitem> + + </itemizedlist> + + <para> + Similar considerations apply for <literal>INSERT ... ON + DUPLICATE KEY UPDATE</literal>. + </para> </listitem> <listitem> @@ -664,27 +695,29 @@ <listitem> <para> - If you encounter an error message similar to <literal>ERROR + If you encounter an error message similar to <errortext>ERROR 1017 (HY000): Can't find file: - '<replaceable>mm</replaceable>.MRG' (errno: 2)</literal> it + '<replaceable>mm</replaceable>.MRG' (errno: 2)</errortext> it generally indicates that some of the base tables are not using - the MyISAM storage engine. Confirm that all tables are MyISAM. + the <literal>MyISAM</literal> storage engine. Confirm that all + of these tables are <literal>MyISAM</literal>. </para> </listitem> <listitem> <para> - There is a limit of 2<superscript>32</superscript> - (~4.295E+09) rows to a <literal>MERGE</literal> table, just as - there is with a <literal>MyISAM</literal>, it is therefore not - possible to merge multiple <literal>MyISAM</literal> tables - that exceed this limitation. However, you build MySQL with the - <option>--with-big-tables</option> option then the row - limitation is increased to + The maximum number of rows in a <literal>MERGE</literal> table + is (2<superscript>32</superscript>)<superscript>2</superscript> - (1.844E+19) rows. See <xref linkend="configure-options"/>. - Beginning with MySQL 5.0.4 all standard binaries are built - with this option. + (~1.844E+19; the same as for a <literal>MyISAM</literal> + table), provided that the server was build using the + <option>--with-big-tables</option> option. (All standard MySQL + ¤t-series; standard binaries are built with this option; + for more information, see + <xref linkend="configure-options"/>.) It is not possible to + merge multiple <literal>MyISAM</literal> tables into a single + <literal>MERGE</literal> table that would have more than this + number of rows. </para> </listitem> @@ -720,9 +753,11 @@ TABLES is in effect. The following does <emphasis>not</emphasis> work: -<programlisting>CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...; - LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE; - ALTER TABLE m1 ... UNION=(t1,t2) ...;</programlisting> +<programlisting> +CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...; +LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE; +ALTER TABLE m1 ... UNION=(t1,t2) ...; +</programlisting> However, you can do this with a temporary <literal>MERGE</literal> table. Modified: trunk/refman-4.1/storage-engines.xml =================================================================== --- trunk/refman-4.1/storage-engines.xml 2008-04-18 12:40:37 UTC (rev 10528) +++ trunk/refman-4.1/storage-engines.xml 2008-04-18 14:44:26 UTC (rev 10529) Changed blocks: 2, Lines Added: 18, Lines Deleted: 7; 2229 bytes @@ -2186,7 +2186,7 @@ If <literal>REPLACE</literal> detects such a violation, it will only change the corresponding row in the first underlying table in which the row is present, whereas a - row with the same unique key value may present in all + row with the same unique key value may be present in all underlying tables. </para> </listitem> @@ -2369,17 +2369,28 @@ <listitem> <para> - If you encounter an error message similar to <literal>ERROR - 1017 (HY000): Can't find file: - '<replaceable>mm</replaceable>.MRG' (errno: 2)</literal> it - generally indicates that some of the base tables are not - using the MyISAM storage engine. Confirm that all tables are - MyISAM. + If you encounter an error message similar to + <errortext>ERROR 1017 (HY000): Can't find file: + '<replaceable>mm</replaceable>.MRG' (errno: 2)</errortext> + it generally indicates that some of the base tables are not + using the <literal>MyISAM</literal> storage engine. Confirm + that all of these tables are <literal>MyISAM</literal>. </para> </listitem> <listitem> <para> + The maximum number of rows in a <literal>MERGE</literal> + table is 2<superscript>32</superscript> (~4.295E+09; the + same as for a <literal>MyISAM</literal> table). It is not + possible to merge multiple <literal>MyISAM</literal> tables + into a single <literal>MERGE</literal> table that would have + more than this number of rows. + </para> + </listitem> + + <listitem> + <para> The <literal>MERGE</literal> storage engine does not support <literal>INSERT DELAYED</literal> statements. </para> Modified: trunk/refman-5.0/se-merge.xml =================================================================== --- trunk/refman-5.0/se-merge.xml 2008-04-18 12:40:37 UTC (rev 10528) +++ trunk/refman-5.0/se-merge.xml 2008-04-18 14:44:26 UTC (rev 10529) Changed blocks: 2, Lines Added: 22, Lines Deleted: 14; 2948 bytes @@ -556,7 +556,7 @@ If <literal>REPLACE</literal> detects such a violation, it will only change the corresponding row in the first underlying table in which the row is present, whereas a - row with the same unique key value may present in all + row with the same unique key value may be present in all underlying tables. </para> </listitem> @@ -717,28 +717,36 @@ <listitem> <para> - If you encounter an error message similar to <literal>ERROR + If you encounter an error message similar to <errortext>ERROR 1017 (HY000): Can't find file: - '<replaceable>mm</replaceable>.MRG' (errno: 2)</literal> it + '<replaceable>mm</replaceable>.MRG' (errno: 2)</errortext> it generally indicates that some of the base tables are not using - the MyISAM storage engine. Confirm that all tables are MyISAM. + the <literal>MyISAM</literal> storage engine. Confirm that all + of these tables are <literal>MyISAM</literal>. </para> </listitem> <listitem> <para> - There is a limit of 2<superscript>32</superscript> - (~4.295E+09)) rows to a <literal>MERGE</literal> table, just - as there is with a <literal>MyISAM</literal>, it is therefore - not possible to merge multiple <literal>MyISAM</literal> - tables that exceed this limitation. However, you build MySQL - with the <option>--with-big-tables</option> option then the - row limitation is increased to + The maximum number of rows in a <literal>MERGE</literal> table + is 2<superscript>32</superscript> (~4.295E+09; the same as for + a <literal>MyISAM</literal> table). It is not possible to + merge multiple <literal>MyISAM</literal> tables into a single + <literal>MERGE</literal> table that would have more than this + number of rows. However, if you build MySQL using the + <option>--with-big-tables</option> option, then the maximum + number of rows is increased to (2<superscript>32</superscript>)<superscript>2</superscript> - (1.844E+19) rows. See <xref linkend="configure-options"/>. - Beginning with MySQL 5.0.4 all standard binaries are built - with this option. + (1.844E+19); for more information, see + <xref linkend="configure-options"/>. </para> + + <note> + <para> + Beginning with MySQL 5.0.4, all standard binaries are built + with this option. + </para> + </note> </listitem> <listitem> Modified: trunk/refman-5.1/se-merge.xml =================================================================== --- trunk/refman-5.1/se-merge.xml 2008-04-18 12:40:37 UTC (rev 10528) +++ trunk/refman-5.1/se-merge.xml 2008-04-18 14:44:26 UTC (rev 10529) Changed blocks: 3, Lines Added: 21, Lines Deleted: 17; 3311 bytes @@ -539,7 +539,7 @@ If <literal>REPLACE</literal> detects such a violation, it will only change the corresponding row in the first underlying table in which the row is present, whereas a - row with the same unique key value may present in all + row with the same unique key value may be present in all underlying tables. </para> </listitem> @@ -709,27 +709,29 @@ <listitem> <para> - If you encounter an error message similar to <literal>ERROR + If you encounter an error message similar to <errortext>ERROR 1017 (HY000): Can't find file: - '<replaceable>mm</replaceable>.MRG' (errno: 2)</literal> it + '<replaceable>mm</replaceable>.MRG' (errno: 2)</errortext> it generally indicates that some of the base tables are not using - the MyISAM storage engine. Confirm that all tables are MyISAM. + the <literal>MyISAM</literal> storage engine. Confirm that all + of these tables are <literal>MyISAM</literal>. </para> </listitem> <listitem> <para> - There is a limit of 2<superscript>32</superscript> - (~4.295E+09) rows to a <literal>MERGE</literal> table, just as - there is with a <literal>MyISAM</literal>, it is therefore not - possible to merge multiple <literal>MyISAM</literal> tables - that exceed this limitation. However, you build MySQL with the - <option>--with-big-tables</option> option then the row - limitation is increased to + The maximum number of rows in a <literal>MERGE</literal> table + is (2<superscript>32</superscript>)<superscript>2</superscript> - (1.844E+19) rows. See <xref linkend="configure-options"/>. - Beginning with MySQL 5.0.4 all standard binaries are built - with this option. + (~1.844E+19; the same as for a <literal>MyISAM</literal> + table), provided that the server was build using the + <option>--with-big-tables</option> option. (All standard MySQL + ¤t-series; standard binaries are built with this option; + for more information, see + <xref linkend="configure-options"/>.) It is not possible to + merge multiple <literal>MyISAM</literal> tables into a single + <literal>MERGE</literal> table that would have more than this + number of rows. </para> </listitem> @@ -765,9 +767,11 @@ TABLES is in effect. The following does <emphasis>not</emphasis> work: -<programlisting>CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...; - LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE; - ALTER TABLE m1 ... UNION=(t1,t2) ...;</programlisting> +<programlisting> +CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...; +LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE; +ALTER TABLE m1 ... UNION=(t1,t2) ...; +</programlisting> However, you can do this with a temporary <literal>MERGE</literal> table. Modified: trunk/refman-6.0/se-merge.xml =================================================================== --- trunk/refman-6.0/se-merge.xml 2008-04-18 12:40:37 UTC (rev 10528) +++ trunk/refman-6.0/se-merge.xml 2008-04-18 14:44:26 UTC (rev 10529) Changed blocks: 3, Lines Added: 21, Lines Deleted: 17; 3311 bytes @@ -537,7 +537,7 @@ If <literal>REPLACE</literal> detects such a violation, it will only change the corresponding row in the first underlying table in which the row is present, whereas a - row with the same unique key value may present in all + row with the same unique key value may be present in all underlying tables. </para> </listitem> @@ -656,27 +656,29 @@ <listitem> <para> - If you encounter an error message similar to <literal>ERROR + If you encounter an error message similar to <errortext>ERROR 1017 (HY000): Can't find file: - '<replaceable>mm</replaceable>.MRG' (errno: 2)</literal> it + '<replaceable>mm</replaceable>.MRG' (errno: 2)</errortext> it generally indicates that some of the base tables are not using - the MyISAM storage engine. Confirm that all tables are MyISAM. + the <literal>MyISAM</literal> storage engine. Confirm that all + of these tables are <literal>MyISAM</literal>. </para> </listitem> <listitem> <para> - There is a limit of 2<superscript>32</superscript> - (~4.295E+09) rows to a <literal>MERGE</literal> table, just as - there is with a <literal>MyISAM</literal>, it is therefore not - possible to merge multiple <literal>MyISAM</literal> tables - that exceed this limitation. However, you build MySQL with the - <option>--with-big-tables</option> option then the row - limitation is increased to + The maximum number of rows in a <literal>MERGE</literal> table + is (2<superscript>32</superscript>)<superscript>2</superscript> - (1.844E+19) rows. See <xref linkend="configure-options"/>. - Beginning with MySQL 5.0.4 all standard binaries are built - with this option. + (~1.844E+19; the same as for a <literal>MyISAM</literal> + table), provided that the server was build using the + <option>--with-big-tables</option> option. (All standard MySQL + ¤t-series; standard binaries are built with this option; + for more information, see + <xref linkend="configure-options"/>.) It is not possible to + merge multiple <literal>MyISAM</literal> tables into a single + <literal>MERGE</literal> table that would have more than this + number of rows. </para> </listitem> @@ -703,9 +705,11 @@ TABLES is in effect. The following does <emphasis>not</emphasis> work: -<programlisting>CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...; - LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE; - ALTER TABLE m1 ... UNION=(t1,t2) ...;</programlisting> +<programlisting> +CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...; +LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE; +ALTER TABLE m1 ... UNION=(t1,t2) ...; +</programlisting> However, you can do this with a temporary <literal>MERGE</literal> table.
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r10529 - in trunk: it/refman-5.1 pt/refman-5.1 refman-4.1 refman-5.0 refman-5.1 refman-6.0 | jon | 18 Apr |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.700 sec. using MySQL 5.1.14-beta-log