MySQL Bugs: #15860: SPATIAL KEY InnoDB crash: MySQL gives key len too small?

Bug #15860	SPATIAL KEY InnoDB crash: MySQL gives key len too small?
Submitted:	19 Dec 2005 13:25	Modified:	13 Jun 2006 11:49
Reporter:	Ask Hansen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.0.16/5.0.18 BK	OS:	darwin / OS X 10.4.3/Linux
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
A simple select with a spatial index on an InnoDB table crashes MySQL 5.0.16.

051219  5:23:40  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `location_idx` of table `geourl/cities`. Last data field length 34 bytes,
InnoDB: key ptr now exceeds key end by 2 bytes.
InnoDB: Key value in the MySQL format:
 len 32; hex 73dbd9c4bf8b42405ad40c3c01b943401708182a3c6d55c0828e0623b96155c0; asc s     B@Z  <  C@   *<mU    # aU ;
051219  5:23:40InnoDB: Assertion failure in thread 25482752 in file row0sel.c line 2212
InnoDB: Failing assertion: buf <= original_buf + buf_len
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.
mysqld got signal 10;
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=67108864
read_buffer_size=1044480
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 269935 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

051219 05:23:40  mysqld restarted
051219  5:23:40 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
051219  5:23:40  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
051219  5:23:40  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1583713252.
InnoDB: Doing recovery: scanned up to log sequence number 0 1583713252
InnoDB: Last MySQL binlog file position 0 0, file name 
051219  5:23:40  InnoDB: Started; log sequence number 0 1583713252
051219  5:23:40 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.16-max'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Experimental (GPL)

How to repeat:

CREATE TABLE `cities` (
  `id` int(11) NOT NULL auto_increment,
  `country` varchar(2) NOT NULL default '',
  `city_normalized` varchar(255) NOT NULL default '',
  `city` varchar(255) NOT NULL default '',
  `region_code` varchar(2) NOT NULL default '',
  `population`  integer,
  `dlatitude` decimal(10,7) NOT NULL default '0.0000000',
  `dlongitude` decimal(10,7) NOT NULL default '0.0000000',
  `location` point NOT NULL default '',
  `latitude` double default NULL,
  `longitude` double default NULL,
  PRIMARY KEY  (`id`),
  key (population),
  SPATIAL KEY `location_idx` (`location`(32)),
  KEY `country` (`country`,`region_code`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- Load data from http://tmp.askask.com/2005/12/cities.db

select id,  astext(location) from cities where   MBRContains(GeomFromText("Polygon((37.0917898238903 -85.5269248546629, 39.4453501761097 -85.5269248546629, 39.4453501761097 -85.7067971453365, 37.0917898238903 -85.7067971453365, 37.0917898238903 -85.5269248546629))"),location)   limit 3;

Suggested fix:
Make it not crash?  :-)

Workaround is to alter table cities ENGINE=MYISAM; but that's not really much fun...

Thank you for the bug report.

051219 11:40:38 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.18-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1131862960 (LWP 15717)]
[Thread 1131862960 (zombie) exited]
[New Thread 1131862960 (LWP 15719)]
051219 11:48:59  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `location_idx` of table `db4/cities`. Last data field length 34 bytes,
InnoDB: key ptr now exceeds key end by 2 bytes.
InnoDB: Key value in the MySQL format:
 len 32; hex 73dbd9c4bf8b42405ad40c3c01b943401708182a3c6d55c0828e0623b96155c0; asc s     B@Z  <  C@   *<mU    # aU ;
051219 11:48:59InnoDB: Assertion failure in thread 1131862960 in file row0sel.c line 2212
InnoDB: Failing assertion: buf <= original_buf + buf_len
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 1131862960 (LWP 15719)]
0x0843dd26 in row_sel_convert_mysql_key_to_innobase (tuple=0x4039ac68, buf=0x8e7f5ab "_zone.MYI", buf_len=1678, index=0x4039c968, 
    key_ptr=0x4376a37e "9@<", key_len=32, trx=0x4038a868) at row0sel.c:2212
2212            ut_a(buf <= original_buf + buf_len);
Current language:  auto; currently c
(gdb)

Hi!

This warning:

051219 11:48:59  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `location_idx` of table `db4/cities`. Last data field length 34
bytes,
InnoDB: key ptr now exceeds key end by 2 bytes.

is usually caused by MySQL passing a wrong key value length in a query.

Regards,
Heikki

Jan,

please look at this.

Heikki

HA_READ_PREFIX_LAST does not currently work in InnoDB with partial-field key value prefixes:

Breakpoint 1, row_sel_convert_mysql_key_to_innobase (tuple=0xb3c78068,
    buf=0x8cac830 '¥' <repeats 200 times>..., buf_len=1678, index=0xb3c5e968,
    key_ptr=0x8c7fb22c "sÛÙÄ¿\213B@ZÔ\f<\001¹C@\027\b\030*<mUÀ\202\216\006#¹aUÀ³D:\b\b³\177\214",
    key_len=32, trx=0xb3af2c68) at row0sel.c:2038
2038            byte*           original_buf    = buf;
(gdb) bt
#0  row_sel_convert_mysql_key_to_innobase (tuple=0xb3c78068, buf=0x8cac830 '¥' <repeats 200 times>...,
    buf_len=1678, index=0xb3c5e968,
    key_ptr=0x8c7fb22c "sÛÙÄ¿\213B@ZÔ\f<\001¹C@\027\b\030*<mUÀ\202\216\006#¹aUÀ³D:\b\b³\177\214",
    key_len=32, trx=0xb3af2c68) at row0sel.c:2038
#1  0x0829715a in ha_innobase::records_in_range (this=0x8caa298, keynr=2, min_key=0x8c7fadbc,
    max_key=0x0) at ha_innodb.cc:5329
#2  0x08270179 in check_quick_keys (param=0x8c7fafec, idx=0, key_tree=0x8c964b8,
    min_key=0x8c7fb22c "sÛÙÄ¿\213B@ZÔ\f<\001¹C@\027\b\030*<mUÀ\202\216\006#¹aUÀ³D:\b\b³\177\214",
    min_key_flag=0, max_key=0x8c7fc12a "\177\214V;\bÌ´p\222¨\016Q\b+", max_key_flag=0)
    at opt_range.cc:5380
#3  0x0827056c in check_quick_select (param=0x8c7fafec, idx=0, tree=0x8c964b8) at opt_range.cc:5208
#4  0x08270839 in get_key_scans_params (param=0x8c7fafec, tree=0x8c96360, index_read_must_be_used=false,
    read_time=507.19999999999999) at opt_range.cc:3235
#5  0x0827a583 in SQL_SELECT::test_quick_select (this=0x8c9bb00, thd=0x8c7a798, keys_to_use={map = 4},
    prev_tables=0, limit=3, force_quick_range=false) at opt_range.cc:1877
#6  0x081f939f in get_quick_record_count (thd=0x8c7a798, select=0x8c9bb00, table=0x8ca9a70,
    keys=0x8c9b904, limit=3) at sql_select.cc:1928
#7  0x0820b5f6 in make_join_statistics (join=0x8c9aa08, tables=0x0, conds=0x8c9a8b0,
    keyuse_array=0x8c9b728) at sql_select.cc:2246
#8  0x08216a0c in JOIN::optimize (this=0x8c9aa08) at sql_select.cc:674
#9  0x0821c740 in mysql_select (thd=0x8c7a798, rref_pointer_array=0x8c7ab30, tables=0x8c9a378,
    wild_num=0, fields=@0x8c7aa94, conds=0x8c9a8b0, og_num=0, order=0x0, group=0x0, having=0x0,
    proc_param=0x0, select_options=2156153344, result=0x8c9a9f8, unit=0x8c7a7e8, select_lex=0x8c7aa10)
    at sql_select.cc:1871
#10 0x0821caae in handle_select (thd=0x8c7a798, lex=0x8c7a7d8, result=0x8c9a9f8,
    setup_tables_done_option=0) at sql_select.cc:250
#11 0x081bba4a in mysql_execute_command (thd=0x8c7a798) at sql_parse.cc:2494
#12 0x081c4175 in mysql_parse (thd=0x8c7a798,
    inBuf=0x8c99f88 "select id,  astext(location) from cities where   MBRContains(GeomFromText(\"Polygon((37.0917898238903 -85.5269248546629,\n39.4453501761097 -85.5269248546629, 39.4453501761097 -85.7067971453365,\n37.09178"..., length=286) at sql_parse.cc:5628
---Type <return> to continue, or q <return> to quit---
#13 0x081c4bbf in dispatch_command (command=COM_QUERY, thd=0x8c7a798,
    packet=0x8c91f29 "select id,  astext(location) from cities where   MBRContains(GeomFromText(\"Polygon((37.0917898238903 -85.5269248546629,\n39.4453501761097 -85.5269248546629, 39.4453501761097 -85.7067971453365,\n37.09178"..., packet_length=287) at sql_parse.cc:1713
#14 0x081c6173 in do_command (thd=0x8c7a798) at sql_parse.cc:1514
#15 0x081c657c in handle_one_connection (arg=0x8c7a798) at sql_parse.cc:1158
#16 0xb7edf361 in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
#17 0xb7e10bde in clone () from /lib/tls/i686/cmov/libc.so.6
(gdb) next
2039            byte*           original_key_ptr = key_ptr;
(gdb)
2047            ulint           n_fields = 0;
(gdb)
2053            key_end = key_ptr + key_len;
(gdb) p key_len
$4 = 32
(gdb) next
2057            dtuple_set_n_fields(tuple, ULINT_MAX);
(gdb)
2059            dfield = dtuple_get_nth_field(tuple, 0);
(gdb)
2060            field = dict_index_get_nth_field(index, 0);
(gdb)
2062            if (dfield_get_type(dfield)->mtype == DATA_SYS) {
(gdb)
2078            while (key_ptr < key_end) {
(gdb)
2080                    ut_a(dict_col_get_type(field->col)->mtype
(gdb)
2083                    data_offset = 0;
(gdb)
2084                    is_null = FALSE;
(gdb)
2086                    if (!(dfield_get_type(dfield)->prtype & DATA_NOT_NULL)) {
(gdb)
2099                    type = dfield_get_type(dfield)->mtype;
(gdb)
2103                    if (type == DATA_BLOB) {
(gdb)
2107                            ut_a(field->prefix_len > 0);
(gdb)
2118                            data_len = key_ptr[data_offset]
(gdb)
2120                            data_field_len = data_offset + 2 + field->prefix_len;
(gdb) p data_len
$5 = 56179
(gdb) p field->prefix_len
$6 = 32
(gdb) next
2122                            data_offset += 2;
(gdb) p data_field_len
$7 = 34
(gdb) next
2146                    if (dtype_get_mysql_type(dfield_get_type(dfield))
(gdb)
2164                    if (!is_null) {
(gdb)
2165                            row_mysql_store_col_in_innobase_format(
(gdb)
2172                            buf += data_len;
(gdb) p data_len
$8 = 56179
(gdb) next
2175                    key_ptr += data_field_len;
(gdb)
2177                    if (key_ptr > key_end) {
(gdb)
2187                            ut_print_timestamp(stderr);
(gdb)
051220 14:49:392189                             fputs(
(gdb)
  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: 2192                            dict_index_name_print(stderr, trx, index);
(gdb) list 2165
2160                    }
2161
2162                    /* Storing may use at most data_len bytes of buf */
2163
2164                    if (!is_null) {
2165                            row_mysql_store_col_in_innobase_format(
2166                                            dfield,
2167                                            buf,
2168                                            FALSE, /* MySQL key value format col */
2169                                            key_ptr + data_offset,
(gdb)
2170                                            data_len,
2171                                            index->table->comp);
2172                            buf += data_len;
2173                    }
2174
2175                    key_ptr += data_field_len;
2176
2177                    if (key_ptr > key_end) {
2178                            /* The last field in key was not a complete key field
2179                            but a prefix of it.
(gdb)
2180
2181                            Print a warning about this! HA_READ_PREFIX_LAST does
2182                            not currently work in InnoDB with partial-field key
2183                            value prefixes. Since MySQL currently uses a padding
2184                            trick to calculate LIKE 'abc%' type queries there
2185                            should never be partial-field prefixes in searches. */
2186
2187                            ut_print_timestamp(stderr);
2188
2189                            fputs(
(gdb)
2190      "  InnoDB: Warning: using a partial-field key prefix in search.\n"
2191      "InnoDB: ", stderr);
2192                            dict_index_name_print(stderr, trx, index);
2193                            fprintf(stderr, ". Last data field length %lu bytes,\n"
2194      "InnoDB: key ptr now exceeds key end by %lu bytes.\n"
2195      "InnoDB: Key value in the MySQL format:\n",
2196                                              (ulong) data_field_len,
2197                                              (ulong) (key_ptr - key_end));
2198                            fflush(stderr);
2199                            ut_print_buf(stderr, original_key_ptr, key_len);
(gdb)

Jan,

what could we do to make it work?

Regards,

Heikki

Heikki,

Could we use the same padding trick similarly as MySQL does in LIKE 'abc%'  type queries ?

Regards,
   Jan

Jan,

maybe :).

Heikki

I propose that this bug is fixed on MySQL server. MySQL should provide a complete key field or create a complete key field using padding (similarly as in LIKE if possbile) for InnoDB and not use prefix of a last field in a key.  Currently InnoDB does not support HA_READ_PREFIX_LAST with partial-field keys. I found only this information from the source (ha_innodb.cc):

"In MySQL-4.0 HA_READ_PREFIX and HA_READ_PREFIX_LAST always pass a complete-field prefix of a key value as the search tuple. I.e., it is not allowed that the last field would just contain n first bytes of the full field value. MySQL uses a 'padding' trick to convert LIKE 'abc%' type queries so that it can use as a search tuple a complete-field-prefix of a key value. Thus, the InnoDB search mode PAGE_CUR_LE_OR_EXTENDS is never used. TODO: when/if MySQL starts to use also partial-field prefixes, we have to deal with stripping of spaces and comparison of non-latin1 char type fields in innobase_mysql_cmp() to get PAGE_CUR_LE_OR_EXTENDS to work correctly."

Regards,
    Jan

Jan,

but is MySQL really intending to pass a partial key prefix?

In the stack trace, key_len is 32. As if it would intend to pass the whole key?

    key_len=32, trx=0xb3af2c68) at row0sel.c:2038

Maybe MySQL has simply calculated the length wrong?

Regards,

Heikki

Not sure if MySQL has calculated key length incorrectly, location attribute is point type and spatial index for this attribute is defined as SPATIAL KEY `location_idx` (`location`(32)). Note that default charset for the table is utf8. Thus we should have a prefix index for the attribute location and this is true

(gdb) p *index
$1 = {id = {high = 0, low = 25}, heap = 0xb3b1cf28, type = 0, name = 0xb3b1d468 "location_idx",
  table_name = 0xb3b1b868 "test/cities", table = 0xb3b1ac68, space = 3, trx_id_offset = 0,
  n_user_defined_cols = 1, n_uniq = 2, n_def = 2, n_fields = 2, fields = 0xb3b1d478, n_nullable = 0,
  indexes = {prev = 0xb3b1ce68, next = 0xb3b1d868}, tree = 0xb3b1d668, tree_indexes = {prev = 0x0,
    next = 0x0}, cached = 1, search_info = 0xb3b1d4c8, stat_n_diff_key_vals = 0xb3b1d510,
  stat_index_size = 3, stat_n_leaf_pages = 2, magic_n = 76789786}
(gdb) p *index->fields
$2 = {col = 0xb3b1ba18, name = 0xb3b1c0c8 "location", order = 0, prefix_len = 32, fixed_len = 0,
  fixed_offs = 0}

and, 

(gdb) p dtype_get_mysql_type(dfield_get_type(dfield))
$13 = 255
(gdb) p data_field_len
$14 = 34
(gdb) p data_len
$15 = 56179

This field is BLOB inside InnoDB, thus we use 2 bytes for the length and then store the prefix i.e. 32 bytes. However, MySQL for some reason would like only 32 bytes for total length. Thus, is this a bug in MySQL?

Regards,
    Jan

Alex,

you put the GIS data type to InnoDB in November:

http://lists.mysql.com/internals/32681

Can you find out if MySQL is giving the key len right in this bug. My guess is that MySQL should really give 34 as the key len, not 32.

Regards,

Heikki

Alex,

any progress on this?

Regards,

Heikki

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/4874

Ok to push

Any estimate as to which release will have the fix?

Noted in 5.0.22 changelog.

<literal>InnoDB</literal> does not support
<literal>SPATIAL</literal> indexes, but did not prevent
creation of such an index. (Bug #15860)

Is there another ticket tracking fixing support for spatial indexes?  (the fix listed in this ticket just disables it).

#18929 was created and closed for the documentation fix.  This bug shouldn't be closed as it was tracking the actual bug (rather than the docs update).  

(Unless I am missing something).

This bug was actually fixed as you can't create spatial index in InnoDB table anymore, so the server doesn't crash in the manner mentioned above.

If you're taliking about working spatial key in InnoDB - i'm not sure it ever happen.
Anyway it's not a bug, rather feature request.

Hmn, that's a bit sad.    Was there a discussion of the issue elsewhere?  Reading the comments here in the ticket it seemed like it was a relatively simple "off by two" problem.