Author: jstephens
Date: 2007-10-08 21:20:29 +0200 (Mon, 08 Oct 2007)
New Revision: 8029
Log:
INSERT IGNORE with partitioned tables (fixes Doc Bug #30191)
Modified:
trunk/refman-5.1/partitioning.xml
trunk/refman-5.1/sql-syntax.xml
trunk/refman-5.2/partitioning.xml
trunk/refman-5.2/sql-syntax.xml
Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml 2007-10-08 18:20:58 UTC (rev 8028)
+++ trunk/refman-5.1/partitioning.xml 2007-10-08 19:20:29 UTC (rev 8029)
Changed blocks: 3, Lines Added: 87, Lines Deleted: 2; 4185 bytes
@@ -152,8 +152,8 @@
building MySQL, see <xref linkend="installing-source"/>. If you have
problems compiling a partitioning-enabled MySQL ¤t-series;
build, check the <ulink url="&base-url-forum-list;?106">MySQL
- Partitioning Forum</ulink> and ask for assistance there if you don't
- find a solution to your problem already posted.
+ Partitioning Forum</ulink> and ask for assistance there if you do
+ not find a solution to your problem already posted.
</para>
<section id="partitioning-overview">
@@ -856,6 +856,17 @@
);
</programlisting>
+ <note>
+ <para>
+ Another way to avoid an error when no matching value is found
+ is to use the <literal>IGNORE</literal> keyword as part of the
+ <literal>INSERT</literal> statement. For an example, see
+ <xref linkend="partitioning-list"/>. Also see
+ <xref linkend="insert"/>, for general information about
+ <literal>IGNORE</literal>.
+ </para>
+ </note>
+
<para>
<literal>MAXVALUE</literal> represents the greatest possible
integer value. Now, any rows whose <literal>store_id</literal>
@@ -1151,6 +1162,80 @@
<xref linkend="partitioning-subpartitions"/>.
</para>
+ <para>
+ Unlike the case with <literal>RANGE</literal> partitioning,
+ there is no <quote>catch-all</quote> such as
+ <literal>MAXVALUE</literal>; all expected values for the
+ partitioning expression should be covered in <literal>PARTITION
+ ... VALUES IN (...)</literal> clauses. An
+ <literal>INSERT</literal> statement containing an unmatched
+ partitioning column value fails with an error, as shown in this
+ example:
+
+<programlisting>
+mysql> <userinput>CREATE TABLE h2 (</userinput>
+ -> <userinput>c1 INT,</userinput>
+ -> <userinput>c2 INT</userinput>
+ -> <userinput>)</userinput>
+ -> <userinput>PARTITION BY LIST(c1) (</userinput>
+ -> <userinput>PARTITION p0 VALUES IN (1, 4, 7),</userinput>
+ -> <userinput>PARTITION p1 VALUES IN (2, 5, 8)</userinput>
+ -> <userinput>);</userinput>
+Query OK, 0 rows affected (0.11 sec)
+
+mysql> <userinput>INSERT INTO h2 VALUES (3, 5);</userinput>
+<errortext>ERROR 1525 (HY000): Table has no partition for value 3</errortext>
+</programlisting>
+
+ When inserting multiple rows using a single
+ <literal>INSERT</literal> statement, any rows coming before the
+ row containing the unmatched value are inserted, but any coming
+ after it are not:
+
+<programlisting>
+mysql> <userinput>SELECT * FROM h2;</userinput>
+Empty set (0.00 sec)
+
+mysql> <userinput>INSERT INTO h2 VALUES (4, 7), (3, 5), (6, 0);</userinput>
+<errortext>ERROR 1525 (HY000): Table has no partition for value 3</errortext>
+mysql> <userinput>SELECT * FROM h2;</userinput>
++------+------+
+| c1 | c2 |
++------+------+
+| 4 | 7 |
++------+------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ You can cause this type of error to be ignored by using the
+ <literal>IGNORE</literal> key word. If you do so, rows
+ containing unmatched partitioning column values are not
+ inserted, but any rows with matching values
+ <emphasis>are</emphasis> inserted, and no errors are reported:
+
+<programlisting>
+mysql> <userinput>TRUNCATE h2;</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql> <userinput>SELECT * FROM h2;</userinput>
+Empty set (0.00 sec)
+
+mysql> <userinput>INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);</userinput>
+Query OK, 3 rows affected (0.00 sec)
+Records: 5 Duplicates: 2 Warnings: 0
+
+mysql> <userinput>SELECT * FROM h2;</userinput>
++------+------+
+| c1 | c2 |
++------+------+
+| 7 | 5 |
+| 1 | 9 |
+| 2 | 5 |
++------+------+
+3 rows in set (0.00 sec)
+</programlisting>
+ </para>
+
</section>
<section id="partitioning-hash">
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2007-10-08 18:20:58 UTC (rev 8028)
+++ trunk/refman-5.1/sql-syntax.xml 2007-10-08 19:20:29 UTC (rev 8029)
Changed blocks: 3, Lines Added: 35, Lines Deleted: 15; 3582 bytes
@@ -6903,15 +6903,35 @@
KEY</literal> value in the table causes a duplicate-key
error and the statement is aborted. With
<literal>IGNORE</literal>, the row still is not inserted,
- but no error is issued. Data conversions that would trigger
- errors abort the statement if <literal>IGNORE</literal> is
- not specified. With <literal>IGNORE</literal>, invalid
- values are adjusted to the closest values and inserted;
- warnings are produced but the statement does not abort. You
- can determine with the <literal>mysql_info()</literal> C API
- function how many rows were actually inserted into the
- table.
+ but no error is issued.
</para>
+
+ <para>
+ <indexterm>
+ <primary>IGNORE</primary>
+ <secondary>with partitioned tables</secondary>
+ </indexterm>
+
+ <literal>IGNORE</literal> has a similar effect on inserts
+ into partitioned tables where no partition matching a given
+ value is found. Without <literal>IGNORE</literal>, such
+ <literal>INSERT</literal> statements are aborted with an
+ error; however, when <literal>INSERT IGNORE</literal> is
+ used, the insert operation fails silently for the row
+ containing the unmatched value, but any rows that are
+ matched are inserted. For an example, see
+ <xref linkend="partitioning-list"/>.
+ </para>
+
+ <para>
+ Data conversions that would trigger errors abort the
+ statement if <literal>IGNORE</literal> is not specified.
+ With <literal>IGNORE</literal>, invalid values are adjusted
+ to the closest values and inserted; warnings are produced
+ but the statement does not abort. You can determine with the
+ <literal>mysql_info()</literal> C API function how many rows
+ were actually inserted into the table.
+ </para>
</listitem>
<listitem>
@@ -13184,11 +13204,11 @@
<para>
<replaceable>col_name</replaceable> can be a column name, or a
- string containing the SQL <quote><literal>%</literal></quote> and
- <quote><literal>_</literal></quote> wildcard characters to obtain
- output only for the columns with names matching the string.
- There is no need to enclose the string within quotes unless it
- contains spaces or other special characters.
+ string containing the SQL <quote><literal>%</literal></quote>
+ and <quote><literal>_</literal></quote> wildcard characters to
+ obtain output only for the columns with names matching the
+ string. There is no need to enclose the string within quotes
+ unless it contains spaces or other special characters.
</para>
<programlisting>
@@ -21296,8 +21316,8 @@
<para>
How the column is sorted in the index. In MySQL, this can
- have values <quote><literal>A</literal></quote> (Ascending)
- or <literal>NULL</literal> (Not sorted).
+ have values <quote><literal>A</literal></quote>
+ (Ascending) or <literal>NULL</literal> (Not sorted).
</para>
</listitem>
Modified: trunk/refman-5.2/partitioning.xml
===================================================================
--- trunk/refman-5.2/partitioning.xml 2007-10-08 18:20:58 UTC (rev 8028)
+++ trunk/refman-5.2/partitioning.xml 2007-10-08 19:20:29 UTC (rev 8029)
Changed blocks: 3, Lines Added: 90, Lines Deleted: 12; 4977 bytes
@@ -123,26 +123,19 @@
</formalpara>
- <remark role="NOTE">
- [js] Following para commented out until 5.2 binaries are actually
- made available.
- </remark>
-
-<!--
<para>
MySQL ¤t-series; binaries are now available from
<ulink url="http://dev.mysql.com/downloads/mysql/¤t-series;.html"/>.
However, for the latest partitioning bugfixes and feature additions,
you can obtain the source from our BitKeeper repository. To enable
partitioning, you need to compile the server using the
- <option>&ddash;with-partition</option> option. For more information about
+ <option>--with-partition</option> option. For more information about
building MySQL, see <xref linkend="installing-source"/>. If you have
- problems compiling a partitioning-enabled MySQL ¤t-series; build, check the
- <ulink url="&base-url-forum-list;?106">MySQL Partitioning
- Forum</ulink> and ask for assistance there if you don't find a
- solution to your problem already posted.
+ problems compiling a partitioning-enabled MySQL ¤t-series;
+ build, check the <ulink url="&base-url-forum-list;?106">MySQL
+ Partitioning Forum</ulink> and ask for assistance there if you do
+ not find a solution to your problem already posted.
</para>
--->
<section id="partitioning-overview">
@@ -824,6 +817,17 @@
);
</programlisting>
+ <note>
+ <para>
+ Another way to avoid an error when no matching value is found
+ is to use the <literal>IGNORE</literal> keyword as part of the
+ <literal>INSERT</literal> statement. For an example, see
+ <xref linkend="partitioning-list"/>. Also see
+ <xref linkend="insert"/>, for general information about
+ <literal>IGNORE</literal>.
+ </para>
+ </note>
+
<para>
<literal>MAXVALUE</literal> represents the greatest possible
integer value. Now, any rows whose <literal>store_id</literal>
@@ -1119,6 +1123,80 @@
<xref linkend="partitioning-subpartitions"/>.
</para>
+ <para>
+ Unlike the case with <literal>RANGE</literal> partitioning,
+ there is no <quote>catch-all</quote> such as
+ <literal>MAXVALUE</literal>; all expected values for the
+ partitioning expression should be covered in <literal>PARTITION
+ ... VALUES IN (...)</literal> clauses. An
+ <literal>INSERT</literal> statement containing an unmatched
+ partitioning column value fails with an error, as shown in this
+ example:
+
+<programlisting>
+mysql> <userinput>CREATE TABLE h2 (</userinput>
+ -> <userinput>c1 INT,</userinput>
+ -> <userinput>c2 INT</userinput>
+ -> <userinput>)</userinput>
+ -> <userinput>PARTITION BY LIST(c1) (</userinput>
+ -> <userinput>PARTITION p0 VALUES IN (1, 4, 7),</userinput>
+ -> <userinput>PARTITION p1 VALUES IN (2, 5, 8)</userinput>
+ -> <userinput>);</userinput>
+Query OK, 0 rows affected (0.11 sec)
+
+mysql> <userinput>INSERT INTO h2 VALUES (3, 5);</userinput>
+<errortext>ERROR 1525 (HY000): Table has no partition for value 3</errortext>
+</programlisting>
+
+ When inserting multiple rows using a single
+ <literal>INSERT</literal> statement, any rows coming before the
+ row containing the unmatched value are inserted, but any coming
+ after it are not:
+
+<programlisting>
+mysql> <userinput>SELECT * FROM h2;</userinput>
+Empty set (0.00 sec)
+
+mysql> <userinput>INSERT INTO h2 VALUES (4, 7), (3, 5), (6, 0);</userinput>
+<errortext>ERROR 1525 (HY000): Table has no partition for value 3</errortext>
+mysql> <userinput>SELECT * FROM h2;</userinput>
++------+------+
+| c1 | c2 |
++------+------+
+| 4 | 7 |
++------+------+
+1 row in set (0.00 sec)
+</programlisting>
+
+ You can cause this type of error to be ignored by using the
+ <literal>IGNORE</literal> key word. If you do so, rows
+ containing unmatched partitioning column values are not
+ inserted, but any rows with matching values
+ <emphasis>are</emphasis> inserted, and no errors are reported:
+
+<programlisting>
+mysql> <userinput>TRUNCATE h2;</userinput>
+Query OK, 1 row affected (0.00 sec)
+
+mysql> <userinput>SELECT * FROM h2;</userinput>
+Empty set (0.00 sec)
+
+mysql> <userinput>INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);</userinput>
+Query OK, 3 rows affected (0.00 sec)
+Records: 5 Duplicates: 2 Warnings: 0
+
+mysql> <userinput>SELECT * FROM h2;</userinput>
++------+------+
+| c1 | c2 |
++------+------+
+| 7 | 5 |
+| 1 | 9 |
+| 2 | 5 |
++------+------+
+3 rows in set (0.00 sec)
+</programlisting>
+ </para>
+
</section>
<section id="partitioning-hash">
Modified: trunk/refman-5.2/sql-syntax.xml
===================================================================
--- trunk/refman-5.2/sql-syntax.xml 2007-10-08 18:20:58 UTC (rev 8028)
+++ trunk/refman-5.2/sql-syntax.xml 2007-10-08 19:20:29 UTC (rev 8029)
Changed blocks: 3, Lines Added: 35, Lines Deleted: 15; 3582 bytes
@@ -6883,15 +6883,35 @@
KEY</literal> value in the table causes a duplicate-key
error and the statement is aborted. With
<literal>IGNORE</literal>, the row still is not inserted,
- but no error is issued. Data conversions that would trigger
- errors abort the statement if <literal>IGNORE</literal> is
- not specified. With <literal>IGNORE</literal>, invalid
- values are adjusted to the closest values and inserted;
- warnings are produced but the statement does not abort. You
- can determine with the <literal>mysql_info()</literal> C API
- function how many rows were actually inserted into the
- table.
+ but no error is issued.
</para>
+
+ <para>
+ <indexterm>
+ <primary>IGNORE</primary>
+ <secondary>with partitioned tables</secondary>
+ </indexterm>
+
+ <literal>IGNORE</literal> has a similar effect on inserts
+ into partitioned tables where no partition matching a given
+ value is found. Without <literal>IGNORE</literal>, such
+ <literal>INSERT</literal> statements are aborted with an
+ error; however, when <literal>INSERT IGNORE</literal> is
+ used, the insert operation fails silently for the row
+ containing the unmatched value, but any rows that are
+ matched are inserted. For an example, see
+ <xref linkend="partitioning-list"/>.
+ </para>
+
+ <para>
+ Data conversions that would trigger errors abort the
+ statement if <literal>IGNORE</literal> is not specified.
+ With <literal>IGNORE</literal>, invalid values are adjusted
+ to the closest values and inserted; warnings are produced
+ but the statement does not abort. You can determine with the
+ <literal>mysql_info()</literal> C API function how many rows
+ were actually inserted into the table.
+ </para>
</listitem>
<listitem>
@@ -13682,11 +13702,11 @@
<para>
<replaceable>col_name</replaceable> can be a column name, or a
- string containing the SQL <quote><literal>%</literal></quote> and
- <quote><literal>_</literal></quote> wildcard characters to obtain
- output only for the columns with names matching the string.
- There is no need to enclose the string within quotes unless it
- contains spaces or other special characters.
+ string containing the SQL <quote><literal>%</literal></quote>
+ and <quote><literal>_</literal></quote> wildcard characters to
+ obtain output only for the columns with names matching the
+ string. There is no need to enclose the string within quotes
+ unless it contains spaces or other special characters.
</para>
<programlisting>
@@ -21694,8 +21714,8 @@
<para>
How the column is sorted in the index. In MySQL, this can
- have values <quote><literal>A</literal></quote> (Ascending)
- or <literal>NULL</literal> (Not sorted).
+ have values <quote><literal>A</literal></quote>
+ (Ascending) or <literal>NULL</literal> (Not sorted).
</para>
</listitem>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r8029 - in trunk: refman-5.1 refman-5.2 | jon | 8 Oct |
