Bug #38083 Error-causing row inserted into partitioned table despite error
Submitted: 13 Jul 2008 12:47 Modified: 17 Oct 2008 17:51
Reporter: Jon Stephens Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.28-bzr, 6.0.6-bzr OS:Linux (64-bit SuSE 10.2)
Assigned to: Alexey Botchkov CPU Architecture:Any
Triage: Triaged: D1 (Critical) / R3 (Medium) / E3 (Medium)

[13 Jul 2008 12:47] Jon Stephens
Description:
An attempt is made to insert one or more rows into a partitioned table, and (one of) the column value(s), when evaluated for the table's partitioning expression, produces an error. The error is reported correctly; however, the offending row is still inserted. This occurs for both single-row and multiple-row inserts.

How to repeat:
jon@tonfisk:~/bin/mysql-5.1/bin> ./mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.28 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@SQL_MODE;
+----------------------------------------------+
| @@SQL_MODE                                   |
+----------------------------------------------+
| STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tn (c1 INT)
    ->     PARTITION BY LIST(1 DIV c1) (
    ->       PARTITION p0 VALUES IN (NULL),
    ->       PARTITION p1 VALUES IN (1)
    ->     );
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO tn VALUES (0);
ERROR 1365 (22012): Division by 0
mysql> SELECT * FROM tn;
+------+
| c1   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> TRUNCATE tn;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tn;
Empty set (0.00 sec)

mysql> INSERT INTO tn VALUES (NULL), (0), (1), (2);
ERROR 1365 (22012): Division by 0
mysql> SELECT * FROM tn;
+------+
| c1   |
+------+
| NULL |
|    0 |
+------+
2 rows in set (0.00 sec)

# When the same expression is used as part of an insert
# into a non-partitioned table, the row is not inserted:

mysql> SELECT @@SQL_MODE;
+----------------------------------------------+
| @@SQL_MODE                                   |
+----------------------------------------------+
| STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tn2 (c1 INT);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tn2 VALUES (1/0);
ERROR 1365 (22012): Division by 0
mysql> SELECT * FROM tn2;
Empty set (0.00 sec)

Suggested fix:
Check for errors generated when evaluating the partitioning expression *before* inserting the row.
[13 Jul 2008 14:57] Jon Stephens
The same thing happens with MOD in the partitioning expression:
mysql> SELECT @@SQL_MODE;
+----------------------------------------------+
| @@SQL_MODE                                   |
+----------------------------------------------+
| STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tm (c1 INT)     
    ->     PARTITION BY LIST(1 MOD c1) (
    ->       PARTITION p0 VALUES IN (NULL),
    ->       PARTITION p1 VALUES IN (1)
    ->     );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO tm VALUES (0), (NULL);
ERROR 1365 (22012): Division by 0
mysql> SELECT * FROM tm;
+------+
| c1   |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

Again, the row is inserted in spite of the error raised.
[13 Jul 2008 14:58] Sveta Smirnova
Thank you for the report.

With release build verified as described.

Debug build crashes with:

Version: '6.0.6-alpha-debug-log'  socket: '/users/ssmirnova/src/mysql-6.0/mysql-test/var/tmp/master.sock'  port: 9306  Source distribution
mysqld: sql_class.cc:411: void Diagnostics_area::set_ok_status(THD*, ha_rows, ulonglong, const char*): Assertion `! is_set()' failed.
080713 17:54:41 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=1048576
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 60038 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x9bc9520
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xad2db448 thread_stack 0x30000
/users/ssmirnova/src/mysql-6.0/sql/mysqld(my_print_stacktrace+0x26)[0x88f8f51]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(handle_segfault+0x2de)[0x82e93f6]
[0x2ce420]
/lib/libc.so.6(abort+0xf8)[0x316678]
/lib/libc.so.6(__assert_fail+0xfd)[0x30e269]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_ZN16Diagnostics_area13set_ok_statusEP3THDyyPKc+0x74)[0x82d05e4]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_Z5my_okP3THDyyPKc+0x59)[0x821732d]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_Z12mysql_insertP3THDP10TABLE_LISTR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0x1226)[0x838e7e6]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_Z21mysql_execute_commandP3THD+0x2e43)[0x8301557]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x22b)[0x8308217]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x8dc)[0x8308c7e]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(_Z10do_commandP3THD+0x23a)[0x8309f90]
/users/ssmirnova/src/mysql-6.0/sql/mysqld(handle_one_connection+0x11d)[0x82f673d]
/lib/libpthread.so.0[0x45fbd4]
/lib/libc.so.6(__clone+0x5e)[0x3b74fe]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x9c24f58 = INSERT INTO tn VALUES (0)
thd->thread_id=1
thd->killed=KILL_BAD_DATA
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
Writing a core file
[5 Aug 2008 9:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/50910

2683 Alexey Botchkov	2008-08-05
      Bug#38083 Error-causing row inserted into partitioned table despite error
      
      problems are located in the sql_partition.cc where functions calculation partition_id
      don't expect error returned from item->val_int().
      Fixed by adding checks to these functions.
      Note  - it tries to fix more problems than just the reported bug
      
      per-file comments:
        mysql-test/r/partition_error.result
          Bug#38083 Error-causing row inserted into partitioned table despite error
          test result
      
        mysql-test/t/partition_error.test
          Bug#38083 Error-causing row inserted into partitioned table despite error
          test case
      
        sql/sql_partition.cc
          Bug#38083 Error-causing row inserted into partitioned table despite error
          
          various functions calculationg partition_id didn't expect an error returned
          from item->val_int().
          Error checks added.
[18 Sep 2008 14:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54308

2743 Alexey Botchkov	2008-09-18
      Bug#38083 Error-causing row inserted into partitioned table despite error
            
        problems are located in the sql_partition.cc where functions calculation partition_id
        don't expect error returned from item->val_int().
        Fixed by adding checks to these functions.
        Note  - it tries to fix more problems than just the reported bug
            
        per-file comments:
          mysql-test/r/partition_error.result
            Bug#38083 Error-causing row inserted into partitioned table despite error
            test result
            
          mysql-test/t/partition_error.test
            Bug#38083 Error-causing row inserted into partitioned table despite error
            test case
            
          sql/sql_partition.cc
            Bug#38083 Error-causing row inserted into partitioned table despite error
                
            various functions calculationg partition_id and subpart_id didn't expect
            an error returned from item->val_int().
            Error checks added.
[19 Sep 2008 14:51] Mattias Jonsson
After fixing the comments (see the review mail), OK to push for me.
[22 Sep 2008 9:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54447

2743 Alexey Botchkov	2008-09-22
      Bug#38083 Error-causing row inserted into partitioned table despite error
      
              problems are located in the sql_partition.cc where functions calculation
              partition_id
              don't expect error returned from item->val_int().
              Fixed by adding checks to these functions.
              Note  - it tries to fix more problems than just the reported bug.
      
              get_part_id_* functions return HA_ERR_NO_PARTITION_FOUND in the
              *part_id out parameter in the case of the error.
      
      per-file comments:
        mysql-test/r/partition.result
              Bug#38083 Error-causing row inserted into partitioned table despite error
              test result
        mysql-test/t/partition.test
              Bug#38083 Error-causing row inserted into partitioned table despite error
              test case
        sql/ha_ndbcluster.cc
              Bug#38083 Error-causing row inserted into partitioned table despite error
              get_part_id() call fixed
        sql/ha_partition.cc
              Bug#38083 Error-causing row inserted into partitioned table despite error
              get_part_id() call fixed
        sql/opt_range.cc
              Bug#38083 Error-causing row inserted into partitioned table despite error
              get_part_id() call fixed
        sql/partition_info.h
              Bug#38083 Error-causing row inserted into partitioned table despite error
              get_subpart_id_func interface changed. 
        sql/sql_partition.cc
              Bug#38083 Error-causing row inserted into partitioned table despite error
              various functions calculationg partition_id and subpart_id didn't expect
                  an error returned from item->val_int().
                  Error checks added.
[6 Oct 2008 7:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55396

2763 Alexey Botchkov	2008-10-06
      Bug#38083 Error-causing row inserted into partitioned table despite error
            
          problems are located in the sql_partition.cc where functions calculation
          partition_id don't expect error returned from item->val_int().
          Fixed by adding checks to these functions.
          Note  - it tries to fix more problems than just the reported bug.
            
      per-file comments:
      modified:
        mysql-test/r/partition.result
          Bug#38083 Error-causing row inserted into partitioned table despite error
              test result
        mysql-test/t/partition.test
          Bug#38083 Error-causing row inserted into partitioned table despite error
              test case
        sql/opt_range.cc
          Bug#38083 Error-causing row inserted into partitioned table despite error
              get_part_id() call fixed
        sql/partition_info.h
          Bug#38083 Error-causing row inserted into partitioned table despite error
              get_subpart_id_func interface changed. 
        sql/sql_partition.cc
          Bug#38083 Error-causing row inserted into partitioned table despite error
              various functions calculationg partition_id and subpart_id didn't expect
                  an error returned from item->val_int().  Error checks added.
[6 Oct 2008 13:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55450

2689 Alexey Botchkov	2008-10-06
      Bug#38083 Error-causing row inserted into partitioned table despite error
[7 Oct 2008 19:59] Paul Dubois
Noted in 5.1.29 changelog.

If an error occurred when evaluating a column of a partitioned table
for the partitioning function, the row could be inserted anyway.

Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.
[9 Oct 2008 17:37] Bugs System
Pushed into 5.1.30  (revid:holyfoot@mysql.com-20081006060534-9i1x30ckvt22xj71) (version source revid:mats@sun.com-20081008113713-2vxny72m5w1tywoi) (pib:4)
[9 Oct 2008 18:44] Paul Dubois
Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:46] Bugs System
Pushed into 6.0.8-alpha  (revid:holyfoot@mysql.com-20081006122238-ttlpizkjo5hy6j8l) (version source revid:kgeorge@mysql.com-20081007153644-uypi14yjgque9obc) (pib:5)
[17 Oct 2008 17:51] Paul Dubois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:06] Bugs System
Pushed into 5.1.29-ndb-6.2.17  (revid:holyfoot@mysql.com-20081006060534-9i1x30ckvt22xj71) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:24] Bugs System
Pushed into 5.1.29-ndb-6.3.19  (revid:holyfoot@mysql.com-20081006060534-9i1x30ckvt22xj71) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:50] Bugs System
Pushed into 5.1.29-ndb-6.4.0  (revid:holyfoot@mysql.com-20081006060534-9i1x30ckvt22xj71) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)