MySQL Bugs: #31074: keys on innodb spatial columns cause prefix warnings in logs

Bug #31074	keys on innodb spatial columns cause prefix warnings in logs
Submitted:	18 Sep 2007 5:04	Modified:	25 Nov 2007 14:41
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.50, 5.1.23BK	OS:	Any
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
Version: '5.0.48-enterprise-gpl'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Enterprise Server (GPL)
070918  6:42:41  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `col001` of table `test/t1`. Last data field length 24 bytes,
InnoDB: key ptr now exceeds key end by 2 bytes.
InnoDB: Key value in the MySQL format:
 len 22; hex 01000000000000000000000000000000000000000000; asc                       ;
070918  6:42:41  InnoDB: Warning: using a partial-field key prefix in search.
InnoDB: index `col001` of table `test/t1`. Last data field length 24 bytes,
InnoDB: key ptr now exceeds key end by 2 bytes.
InnoDB: Key value in the MySQL format:
 len 22; hex 01000000000000000000000000000000000000000000; asc           

How to repeat:
drop table if exists t1;
create table `t1` (`col001` point ,key(`col001` ))engine=innodb;
insert into t1 values (),();
select * from t1 where col001 is not null;

Suggested fix:
Is this error expected or is there something to fix here?

Shane,

field.h in 5.0:

#ifdef HAVE_SPATIAL
class Field_geom :public Field_blob {
public:
  enum geometry_type geom_type;

  Field_geom(char *ptr_arg, uchar *null_ptr_arg, uint null_bit_arg,
             enum utype unireg_check_arg, const char *field_name_arg,
             struct st_table *table_arg,uint blob_pack_length,
             enum geometry_type geom_type_arg)
     :Field_blob(ptr_arg, null_ptr_arg, null_bit_arg, unireg_check_arg,
                 field_name_arg, table_arg, blob_pack_length,&my_charset_bin)
  { geom_type= geom_type_arg; }
  Field_geom(uint32 len_arg,bool maybe_null_arg, const char *field_name_arg,
             struct st_table *table_arg, enum geometry_type geom_type_arg)
     :Field_blob(len_arg, maybe_null_arg, field_name_arg,
                 table_arg, &my_charset_bin)
  { geom_type= geom_type_arg; }
  enum ha_base_keytype key_type() const { return HA_KEYTYPE_VARBINARY2; }
  enum_field_types type() const { return FIELD_TYPE_GEOMETRY; }
  void sql_type(String &str) const;
  int  store(const char *to, uint length, CHARSET_INFO *charset);
  int  store(double nr);
  int  store(longlong nr, bool unsigned_val);
  int  store_decimal(const my_decimal *);
  uint get_key_image(char *buff,uint length,imagetype type);
  uint size_of() const { return sizeof(*this); }
  int  reset(void) { return !maybe_null() || Field_blob::reset(); }
};
#endif /*HAVE_SPATIAL*/

A geometry type is a BLOB in MySQL.

http://dev.mysql.com/doc/refman/5.0/en/gis-class-point.html

A point apparently contains two doubles. It should be 16 bytes.

I wonder where the data length 24 and the key length 22 come from.

Can you assign this to Holyfoot or some other spatial expert?

Regards,

Heikki

> A point apparently contains two doubles. It should be 16 bytes.
> I wonder where the data length 24 and the key length 22 come from.

Point data contains also geometry type and coordinate system description,
so is 25 by now.
The warning was indeed an error, but most probably was fixed meanwhile.