Author: jstephens
Date: 2008-07-13 23:43:51 +0200 (Sun, 13 Jul 2008)
New Revision: 11234
Log:
Added a couple of examples of Things That Can Go Wrong if you change the
server SQL mode after creating a partitioned table
Fxies Docs Bug #37984
Modified:
trunk/refman-5.1/partitioning.xml
trunk/refman-6.0/partitioning.xml
Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml 2008-07-12 23:15:07 UTC (rev 11233)
+++ trunk/refman-5.1/partitioning.xml 2008-07-13 21:43:51 UTC (rev 11234)
Changed blocks: 1, Lines Added: 148, Lines Deleted: 0; 5714 bytes
@@ -4322,6 +4322,154 @@
</para>
</formalpara>
+
+ <formalpara>
+
+ <title>Examples</title>
+
+ <para>
+ The following examples illustrate some changes in behavior
+ of partitioned tables due to a change in the server SQL
+ mode:
+
+ <orderedlist>
+
+ <listitem>
+ <formalpara>
+
+ <title>Error handling</title>
+
+ <para>
+ Suppose you create a partitioned table whose
+ partitioning expression is one such as
+ <literal><replaceable>column</replaceable> DIV
+ 0</literal> or
+ <literal><replaceable>column</replaceable> MOD
+ 0</literal>, as shown here:
+
+<programlisting>
+mysql> <userinput>CREATE TABLE tn (c1 INT)</userinput>
+ -> <userinput>PARTITION BY LIST(1 DIV c1) (</userinput>
+ -> <userinput>PARTITION p0 VALUES IN (NULL),</userinput>
+ -> <userinput>PARTITION p1 VALUES IN (1)</userinput>
+ -> <userinput>);</userinput>
+Query OK, 0 rows affected (0.05 sec)
+</programlisting>
+
+ The default behavior for MySQL is to return
+ <literal>NULL</literal> for the result of a division
+ by zero, without producing any errors:
+
+<programlisting>
+mysql> <userinput>SELECT @@SQL_MODE;</userinput>
++------------+
+| @@SQL_MODE |
++------------+
+| |
++------------+
+1 row in set (0.00 sec)
+
+
+mysql> <userinput>INSERT INTO tn VALUES (NULL), (0), (1);</userinput>
+Query OK, 3 rows affected (0.00 sec)
+Records: 3 Duplicates: 0 Warnings: 0
+</programlisting>
+
+ However, changing the server SQL mode to treat
+ division by zero as an error and to enforce strict
+ error handling causes the same
+ <literal>INSERT</literal> statement to fail, as
+ shown here:
+
+<programlisting>
+mysql> <userinput>SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';</userinput>
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> <userinput>INSERT INTO tn VALUES (NULL), (0), (1);</userinput>
+<errortext>ERROR 1365 (22012): Division by 0</errortext>
+</programlisting>
+ </para>
+
+ </formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title>Table accessibility</title>
+
+ <para>
+ Sometimes a change in the server SQL mode can make
+ partitioned tables unusable. The following
+ <literal>CREATE TABLE</literal> statement can be
+ executed successfully only if the
+ <literal>NO_UNSIGNED_SUBTRACTION</literal> mode is
+ in effect:
+
+<programlisting>
+mysql> <userinput>SELECT @@SQL_MODE;</userinput>
++------------+
+| @@SQL_MODE |
++------------+
+| |
++------------+
+1 row in set (0.00 sec)
+
+mysql> <userinput>CREATE TABLE tu (c1 BIGINT UNSIGNED)</userinput>
+ -> <userinput>PARTITION BY RANGE(c1 - 10) (</userinput>
+ -> <userinput>PARTITION p0 VALUES LESS THAN (-5),</userinput>
+ -> <userinput>PARTITION p1 VALUES LESS THAN (0),</userinput>
+ -> <userinput>PARTITION p2 VALUES LESS THAN (5),</userinput>
+ -> <userinput>PARTITION p3 VALUES LESS THAN (10),</userinput>
+ -> <userinput>PARTITION p4 VALUES LESS THAN (MAXVALUE)</userinput>
+ -> <userinput>);</userinput>
+<errortext>ERROR 1563 (HY000): Partition constant is out of partition function domain</errortext>
+
+mysql> <userinput>SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';</userinput>
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> <userinput>SELECT @@SQL_MODE;</userinput>
++-------------------------+
+| @@SQL_MODE |
++-------------------------+
+| NO_UNSIGNED_SUBTRACTION |
++-------------------------+
+1 row in set (0.00 sec)
+
+mysql> <userinput>CREATE TABLE tu (c1 BIGINT UNSIGNED)</userinput>
+ -> <userinput>PARTITION BY RANGE(c1 - 10) (</userinput>
+ -> <userinput>PARTITION p0 VALUES LESS THAN (-5),</userinput>
+ -> <userinput>PARTITION p1 VALUES LESS THAN (0),</userinput>
+ -> <userinput>PARTITION p2 VALUES LESS THAN (5),</userinput>
+ -> <userinput>PARTITION p3 VALUES LESS THAN (10),</userinput>
+ -> <userinput>PARTITION p4 VALUES LESS THAN (MAXVALUE)</userinput>
+ -> <userinput>);</userinput>
+Query OK, 0 rows affected (0.05 sec)
+</programlisting>
+
+ If you remove the
+ <literal>NO_UNSIGNED_SUBTRACTION</literal> server
+ SQL mode after creating <literal>tu</literal>, you
+ may no longer be able to access this table:
+
+<programlisting>
+mysql> <userinput>SET SQL_MODE='';</userinput>
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> <userinput>SELECT * FROM tu;</userinput>
+<errortext>ERROR 1563 (HY000): Partition constant is out of partition function domain</errortext>
+mysql> <userinput>INSERT INTO tu VALUES (20);</userinput>
+<errortext>ERROR 1563 (HY000): Partition constant is out of partition function domain</errortext>
+</programlisting>
+ </para>
+
+ </formalpara>
+ </listitem>
+
+ </orderedlist>
+ </para>
+
+ </formalpara>
</listitem>
<listitem>
Modified: trunk/refman-6.0/partitioning.xml
===================================================================
--- trunk/refman-6.0/partitioning.xml 2008-07-12 23:15:07 UTC (rev 11233)
+++ trunk/refman-6.0/partitioning.xml 2008-07-13 21:43:51 UTC (rev 11234)
Changed blocks: 1, Lines Added: 148, Lines Deleted: 0; 5714 bytes
@@ -4231,6 +4231,154 @@
</para>
</formalpara>
+
+ <formalpara>
+
+ <title>Examples</title>
+
+ <para>
+ The following examples illustrate some changes in behavior
+ of partitioned tables due to a change in the server SQL
+ mode:
+
+ <orderedlist>
+
+ <listitem>
+ <formalpara>
+
+ <title>Error handling</title>
+
+ <para>
+ Suppose you create a partitioned table whose
+ partitioning expression is one such as
+ <literal><replaceable>column</replaceable> DIV
+ 0</literal> or
+ <literal><replaceable>column</replaceable> MOD
+ 0</literal>, as shown here:
+
+<programlisting>
+mysql> <userinput>CREATE TABLE tn (c1 INT)</userinput>
+ -> <userinput>PARTITION BY LIST(1 DIV c1) (</userinput>
+ -> <userinput>PARTITION p0 VALUES IN (NULL),</userinput>
+ -> <userinput>PARTITION p1 VALUES IN (1)</userinput>
+ -> <userinput>);</userinput>
+Query OK, 0 rows affected (0.05 sec)
+</programlisting>
+
+ The default behavior for MySQL is to return
+ <literal>NULL</literal> for the result of a division
+ by zero, without producing any errors:
+
+<programlisting>
+mysql> <userinput>SELECT @@SQL_MODE;</userinput>
++------------+
+| @@SQL_MODE |
++------------+
+| |
++------------+
+1 row in set (0.00 sec)
+
+
+mysql> <userinput>INSERT INTO tn VALUES (NULL), (0), (1);</userinput>
+Query OK, 3 rows affected (0.00 sec)
+Records: 3 Duplicates: 0 Warnings: 0
+</programlisting>
+
+ However, changing the server SQL mode to treat
+ division by zero as an error and to enforce strict
+ error handling causes the same
+ <literal>INSERT</literal> statement to fail, as
+ shown here:
+
+<programlisting>
+mysql> <userinput>SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';</userinput>
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> <userinput>INSERT INTO tn VALUES (NULL), (0), (1);</userinput>
+<errortext>ERROR 1365 (22012): Division by 0</errortext>
+</programlisting>
+ </para>
+
+ </formalpara>
+ </listitem>
+
+ <listitem>
+ <formalpara>
+
+ <title>Table accessibility</title>
+
+ <para>
+ Sometimes a change in the server SQL mode can make
+ partitioned tables unusable. The following
+ <literal>CREATE TABLE</literal> statement can be
+ executed successfully only if the
+ <literal>NO_UNSIGNED_SUBTRACTION</literal> mode is
+ in effect:
+
+<programlisting>
+mysql> <userinput>SELECT @@SQL_MODE;</userinput>
++------------+
+| @@SQL_MODE |
++------------+
+| |
++------------+
+1 row in set (0.00 sec)
+
+mysql> <userinput>CREATE TABLE tu (c1 BIGINT UNSIGNED)</userinput>
+ -> <userinput>PARTITION BY RANGE(c1 - 10) (</userinput>
+ -> <userinput>PARTITION p0 VALUES LESS THAN (-5),</userinput>
+ -> <userinput>PARTITION p1 VALUES LESS THAN (0),</userinput>
+ -> <userinput>PARTITION p2 VALUES LESS THAN (5),</userinput>
+ -> <userinput>PARTITION p3 VALUES LESS THAN (10),</userinput>
+ -> <userinput>PARTITION p4 VALUES LESS THAN (MAXVALUE)</userinput>
+ -> <userinput>);</userinput>
+<errortext>ERROR 1563 (HY000): Partition constant is out of partition function domain</errortext>
+
+mysql> <userinput>SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';</userinput>
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> <userinput>SELECT @@SQL_MODE;</userinput>
++-------------------------+
+| @@SQL_MODE |
++-------------------------+
+| NO_UNSIGNED_SUBTRACTION |
++-------------------------+
+1 row in set (0.00 sec)
+
+mysql> <userinput>CREATE TABLE tu (c1 BIGINT UNSIGNED)</userinput>
+ -> <userinput>PARTITION BY RANGE(c1 - 10) (</userinput>
+ -> <userinput>PARTITION p0 VALUES LESS THAN (-5),</userinput>
+ -> <userinput>PARTITION p1 VALUES LESS THAN (0),</userinput>
+ -> <userinput>PARTITION p2 VALUES LESS THAN (5),</userinput>
+ -> <userinput>PARTITION p3 VALUES LESS THAN (10),</userinput>
+ -> <userinput>PARTITION p4 VALUES LESS THAN (MAXVALUE)</userinput>
+ -> <userinput>);</userinput>
+Query OK, 0 rows affected (0.05 sec)
+</programlisting>
+
+ If you remove the
+ <literal>NO_UNSIGNED_SUBTRACTION</literal> server
+ SQL mode after creating <literal>tu</literal>, you
+ may no longer be able to access this table:
+
+<programlisting>
+mysql> <userinput>SET SQL_MODE='';</userinput>
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> <userinput>SELECT * FROM tu;</userinput>
+<errortext>ERROR 1563 (HY000): Partition constant is out of partition function domain</errortext>
+mysql> <userinput>INSERT INTO tu VALUES (20);</userinput>
+<errortext>ERROR 1563 (HY000): Partition constant is out of partition function domain</errortext>
+</programlisting>
+ </para>
+
+ </formalpara>
+ </listitem>
+
+ </orderedlist>
+ </para>
+
+ </formalpara>
</listitem>
<listitem>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r11234 - in trunk: refman-5.1 refman-6.0 | jon | 13 Jul |
