| 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: | |
| 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 |
[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)
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.