MySQL Bugs: #10465: DECIMAL, crash on DELETE

Bug #10465	DECIMAL, crash on DELETE
Submitted:	9 May 2005 9:43	Modified:	24 May 2005 19:41
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:
Assigned to:	Jani Tolonen	CPU Architecture:	Any

Description:
SET SESSION STORAGE_ENGINE=INNODB;
CREATE TABLE t1 (F1 DECIMAL(4) NOT NULL, UNIQUE (F1));
CREATE TABLE t2 (F1 DECIMAL(4), FOREIGN KEY (F1) REFERENCES t1(F1));

# Here comes the server crash
DELETE FROM t2 WHERE F1 = 5;

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1934.2.2, 2005-05-07

Some observations:
- I am nearly sure that this bug was introduced by a push between
  the 29'th of April and the 7'th of May. 
- If I use the data type BIGINT instead of DECIMAL, the crash disappears.
- The master.err file shows that it is most probably an InnoDB problem.  

How to repeat:
Please use my attached test case 
nist_test.test , copy it to mysql-test/t

  touch r/nist_test.result     # Produce a dummy file with expected
                                      # results.
  ./mysql-test-run nist_test

test case

Attachment: nist_test.test (application/test, text), 3.14 KiB.

master.err

Attachment: master.err (application/octet-stream, text), 4.72 KiB.

Addditional information:

050509 11:56:53InnoDB: Assertion failure in thread 147466 in file row0sel.c line 2082
InnoDB: Failing assertion: dict_col_get_type(field->col)->mtype == dfield_get_type(dfield)->mtype
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 147466 (LWP 15439)]
0x0830ab2a in row_sel_convert_mysql_key_to_innobase (tuple=0x40ad8468,
    buf=0x8c7741a '¥' <repeats 62 times>, "h4z\025é\a", buf_len=57,
    index=0x40ae4468, key_ptr=0x65d0143b "¥p'a\bž Å\b\n", key_len=4,
    trx=0x40ad3468) at row0sel.c:2082
2082                         == dfield_get_type(dfield)->mtype);
Current language:  auto; currently c
(gdb) p dict_col_get_type(field->col)
$1 = (dtype_t *) 0x40ae30b8
(gdb) p *dict_col_get_type(field->col)
$2 = {mtype = 8, prtype = 256, len = 6, prec = 0, mbminlen = 0, mbmaxlen = 0}
(gdb) p *dfield_get_type(dfield)
$3 = {mtype = 0, prtype = 0, len = 0, prec = 0, mbminlen = 0, mbmaxlen = 0}
(gdb) where
#0  0x0830ab2a in row_sel_convert_mysql_key_to_innobase (tuple=0x40ad8468,
    buf=0x8c7741a '¥' <repeats 62 times>, "h4z\025é\a", buf_len=57,
    index=0x40ae4468, key_ptr=0x65d0143b "¥p'a\bž Å\b\n", key_len=4,
    trx=0x40ad3468) at row0sel.c:2082
#1  0x0826da23 in ha_innobase::records_in_range(unsigned, st_key_range*, st_key_range*) (this=0x8c76f70, keynr=0, min_key=0x65d00fec, max_key=0x65d00fdc)
    at ha_innodb.cc:5015
#2  0x0824b286 in check_quick_keys (param=0x65d011fc, idx=0,
    key_tree=0x8c775e0, min_key=0x65d01438 "", min_key_flag=0,
    max_key=0x65d01b36 "", max_key_flag=0) at opt_range.cc:5171
#3  0x0824abee in check_quick_select (param=0x65d011fc, idx=0, tree=0x8c775e0)
    at opt_range.cc:4987
#4  0x08246329 in get_key_scans_params (param=0x65d011fc, tree=0x8c774a8,
    index_read_must_be_used=false, read_time=3.2999999999999998)
    at opt_range.cc:3140
#5  0x082434c5 in SQL_SELECT::test_quick_select(THD*, Bitmap<64>, unsigned long long, unsigned long long, bool) (this=0x8c78cb0, thd=0x8c520b8, keys_to_use=
      {map = 1}, prev_tables=0, limit=18446744073709551615,
    force_quick_range=false) at opt_range.cc:1786
#6  0x08253946 in SQL_SELECT::check_quick(THD*, bool, unsigned long long) (
    this=0x8c78cb0, thd=0x8c520b8, force_quick_range=false,
    limit=18446744073709551615) at opt_range.h:694
#7  0x08213881 in mysql_delete(THD*, st_table_list*, Item*, st_sql_list*, unsig---Type <return> to continue, or q <return> to quit---
ned long long, unsigned long) (thd=0x8c520b8, table_list=0x8c789a0,
    conds=0x8c78bc8, order=0x8c523b8, limit=18446744073709551615, options=0)
    at sql_delete.cc:102
#8  0x081b38df in mysql_execute_command(THD*) (thd=0x8c520b8)
    at sql_parse.cc:3225
#9  0x081b8d02 in mysql_parse(THD*, char*, unsigned) (thd=0x8c520b8,
    inBuf=0x8c78930 "DELETE FROM t2 WHERE F1 = 5", length=27)
    at sql_parse.cc:5243
#10 0x081afbfc in dispatch_command(enum_server_command, THD*, char*, unsigned)
    (command=COM_QUERY, thd=0x8c520b8,
    packet=0x8c708d1 "DELETE FROM t2 WHERE F1 = 5", packet_length=28)
    at sql_parse.cc:1651
#11 0x081af51a in do_command(THD*) (thd=0x8c520b8) at sql_parse.cc:1457
#12 0x081ae781 in handle_one_connection (arg=0x8c520b8) at sql_parse.cc:1114
#13 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#14 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#15 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb)

Based on below gdb output, why key_len = 4 when field length is 2 ? Because of this
row_sel_convert_mysql_key_to_innobase tryes to convert more than one key
to InnoDB.

Breakpoint 1, row_sel_convert_mysql_key_to_innobase (tuple=0x40ad9468,
    buf=0x8c49d80 '¥' <repeats 64 times>, "h4z\025\221", buf_len=57,
    index=0x40ad8468, key_ptr=0x637a7438 "", key_len=4, trx=0x40acbc68)
    at row0sel.c:2039
2039            byte*           original_buf    = buf;
Current language:  auto; currently c
(gdb) next
2040            byte*           original_key_ptr = key_ptr;
(gdb)
2048            ulint           n_fields = 0;
(gdb)
2054            key_end = key_ptr + key_len;
(gdb)
2058            dtuple_set_n_fields(tuple, ULINT_MAX);
(gdb)
2060            dfield = dtuple_get_nth_field(tuple, 0);
(gdb)
2061            field = dict_index_get_nth_field(index, 0);
(gdb)
2063            if (dfield_get_type(dfield)->mtype == DATA_SYS) {
(gdb) p *dfield
$1 = {data = 0x0, len = 0, type = {mtype = 4, prtype = 4130038, len = 2,
    prec = 0, mbminlen = 1, mbmaxlen = 1}}
(gdb) p *field
$2 = {col = 0x40ad7870, name = 0x40ad79a0 "F1", order = 0, prefix_len = 0,
  fixed_len = 0, fixed_offs = 0}
(gdb) p *(field->col)
$3 = {hash = 0x0, ind = 0, clust_pos = 3, ord_part = 1,
  name = 0x40ad79a0 "F1", type = {mtype = 4, prtype = 4130038, len = 2,
    prec = 0, mbminlen = 1, mbmaxlen = 1}, table = 0x40ad6e68, aux = 0}
(gdb) next
2079            while (key_ptr < key_end) {
(gdb) p key_len
$4 = 4
(gdb) p key_ptr
$5 = (unsigned char *) 0x637a7438 ""
(gdb) p key_end
$6 = (unsigned char *) 0x637a743c ""
(gdb) next
2082                         == dfield_get_type(dfield)->mtype);
(gdb)
2084                    data_offset = 0;
(gdb)
2085                    is_null = FALSE;
(gdb)
2087                    if (!(dfield_get_type(dfield)->prtype & DATA_NOT_NULL)) {
(gdb) p dfield_get_type(dfield)->prtype
$7 = 4130038
(gdb) next
2091                            data_offset = 1;
(gdb)
2093                            if (*key_ptr != 0) {
(gdb) p *key_ptr
$8 = 0 '\0'
(gdb) next
2100                    type = dfield_get_type(dfield)->mtype;
(gdb)
2104                    if (type == DATA_BLOB) {
(gdb) p *type
Cannot access memory at address 0x4
(gdb) next
2128                    } else if (field->prefix_len > 0) {
(gdb) p *dfield
$9 = {data = 0x0, len = 0, type = {mtype = 4, prtype = 4130038, len = 2,
    prec = 0, mbminlen = 1, mbmaxlen = 1}}
(gdb) next
2143                            data_len = dfield_get_type(dfield)->len;
(gdb)
2144                            data_field_len = data_offset + data_len;
(gdb) p data_len
$10 = 2
(gdb) next
2147                    if (dtype_get_mysql_type(dfield_get_type(dfield))
(gdb) p data_field_len
$11 = 3
(gdb) next
2165                    if (!is_null) {
(gdb)
2166                            row_mysql_store_col_in_innobase_format(
(gdb)
2173                            buf += data_len;
(gdb)
2176                    key_ptr += data_field_len;
(gdb)
2178                    if (key_ptr > key_end) {
(gdb)
2208                    n_fields++;
(gdb)
2209                    field++;
(gdb)
2210                    dfield++;
(gdb)
2079            while (key_ptr < key_end) {
(gdb) p key_ptr
$12 = (unsigned char *) 0x637a743b "¥"
(gdb) p key_end
$13 = (unsigned char *) 0x637a743c ""
(gdb) next
2082                         == dfield_get_type(dfield)->mtype);
(gdb) list 2081
2076                    return;
2077            }
2078
2079            while (key_ptr < key_end) {
2080
2081                    ut_a(dict_col_get_type(field->col)->mtype
2082                         == dfield_get_type(dfield)->mtype);
2083
2084                    data_offset = 0;
2085                    is_null = FALSE;
(gdb) p *field->col
$14 = {hash = 0x0, ind = 1, clust_pos = 0, ord_part = 2,
  name = 0x40ad79a8 "DB_ROW_ID", type = {mtype = 8, prtype = 256, len = 6,
    prec = 0, mbminlen = 0, mbmaxlen = 0}, table = 0x40ad6e68,
  aux = 4294967295}
(gdb) p dfield
$15 = (dfield_t *) 0x40ad94a4
(gdb) p *dfield
$16 = {data = 0x0, len = 0, type = {mtype = 0, prtype = 0, len = 0, prec = 0,
    mbminlen = 0, mbmaxlen = 0}}

Changing category to MySQL Server because this error is most probable in MYSQL code. DECIMAL(4) can be store to 2 bytes. But ha_innobase::records_in_range() gets as a parameter min_key and max_key length 4:

#1  0x0826da23 in ha_innobase::records_in_range(unsigned, st_key_range*, st_key_range*) (this=0x8c50080, keynr=0, min_key=0x637a6fec, max_key=0x637a6fdc)
    at ha_innodb.cc:5015
5015            row_sel_convert_mysql_key_to_innobase(
Current language:  auto; currently c++
(gdb) p *min_key
$1 = {key = 0x637a7438 "", length = 4, flag = HA_READ_KEY_EXACT}
(gdb) p *max_key
$2 = {key = 0x637a7b36 "", length = 4, flag = HA_READ_AFTER_KEY}
(gdb) p/x *max_key.key@4
$5 = {0x0, 0x80, 0x5, 0xa5}
(gdb) p/x *min_key.key@4
$6 = {0x0, 0x80, 0x5, 0xa5}

Regards,
    JanL

Changed the synopsis, since the FOREIGN KEY had nothing to do with this.
--Heikki

I guess I found an easier test case to replay the problem:
SET SESSION STORAGE_ENGINE=INNODB;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL,
PRIMARY KEY (GRADE));
INSERT INTO t1 (GRADE) VALUES (151);
SELECT GRADE  FROM t1;
GRADE
151
SELECT GRADE  FROM t1 WHERE GRADE= 151;
GRADE
         <---- Where is the record
The combination PRIMARY KEY/InnoDB/DECIMAL is important.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Jani,

if I understand correctly this is an incompatible change, and must be documented prominently.

Regards,

Heikki

Noted in 5.0 upgrading instructions and
5.0.6 changelog.