A Lightweight SQL Database for Cloud Infrastructure and Web Applications

innodb max index column length is 191, not 256 or 768 as one would "expect"

Reported by Stewart Smith on 2010-05-11
16
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
High
Stewart Smith
Dexter
High
Stewart Smith
HailDB
Undecided
Unassigned
MySQL Server
Unknown
Unknown

Bug Description

DICT_MAX_INDEX_COL_LEN

was 768

could be 1024

or could be 768*4.

<stewart> lbieber, 256*4 would be consistent with mysql in utf8 mode.
<stewart> lbieber, and 768*4(3072) would be consistent with mysql in latin1

Related branches

lp:~stewart/drizzle/bug578842-max-index-size
Jay Pipes (community): Approve on 2010-05-13
Brian Aker: Pending requested 2010-05-13
Stewart Smith (stewart) wrote :

investigating claims of " the disk format of some fixed-length columns would change" and if we at all care...

Changed in drizzle:
status: Confirmed → In Progress
Lee Bieber (kalebral) on 2010-05-11
Changed in drizzle:
importance: Undecided → High
Stewart Smith (stewart) wrote :

http://drupal.org/node/554820

drupal using the mysql utf8 3byte limit

Hi!

Changing the on disk format for us is really no big deal.

Cheers,
 -Brian

On May 11, 2010, at 8:21 AM, Lee Bieber wrote:

> ** Changed in: drizzle
> Importance: Undecided => High
>
> ** Also affects: drizzle/dexter
> Importance: High
> Assignee: Stewart Smith (stewart-flamingspork)
> Status: In Progress
>
> --
> innodb max index column length is 191, not 256 or 768 as one would "expect"
> https://bugs.launchpad.net/bugs/578842
> You received this bug notification because you are a member of Drizzle-
> developers, which is subscribed to Drizzle.
>
> Status in A Lightweight SQL Database for Cloud and Web: In Progress
> Status in Drizzle dexter series: In Progress
>
> Bug description:
> DICT_MAX_INDEX_COL_LEN
>
> was 768
>
> could be 1024
>
> or could be 768*4.
>
>
> <stewart> lbieber, 256*4 would be consistent with mysql in utf8 mode.
> <stewart> lbieber, and 768*4(3072) would be consistent with mysql in latin1
>
>

Stewart Smith (stewart) wrote :

more concerned with how this changes what goes on disk, not compatibility. Think I'm getting the picture now....

Stewart Smith (stewart) wrote :
Download full text (6.5 KiB)

row0sel.c:

in row_sel_sec_rec_is_for_blob():
/********************************************************************//**
Returns TRUE if the user-defined column in a secondary index record
is alphabetically the same as the corresponding BLOB column in the clustered
index record.
NOTE: the comparison is NOT done as a binary comparison, but character
fields are compared with collation!
@return TRUE if the columns are equal */

btr_copy_externally_stored_field_prefix() call using buffer thati s DICT_MAX_INDEX_COL_LEN (on stack)

in row0mysql.c:
in row_create_index_for_mysql()

prefix_len and actual length < DICT_MAX_INDEX_COL_LEN
(else, DB_TOO_BIG_RECORD)

in page0zip.c:
in page_zip_fields_encode():
   /* fixed-length non-nullable field */

   if (fixed_sum && UNIV_UNLIKELY
       (fixed_sum + field->fixed_len
        > DICT_MAX_INDEX_COL_LEN)) {
    /* Write out the length of the
    preceding non-nullable fields,
    to avoid exceeding the maximum
    length of a fixed-length column. */
    buf = page_zip_fixed_field_encode(
     buf, fixed_sum << 1 | 1);
    fixed_sum = 0;
    col++;
   }

in dict0mem.h:
** @brief DICT_MAX_INDEX_COL_LEN is measured in bytes and is the maximum
indexed column length (or indexed prefix length).

It is set to 3*256, so that one can create a column prefix index on
256 characters of a TEXT or VARCHAR column also in the UTF-8
charset. In that charset, a character may take at most 3 bytes. This
constant MUST NOT BE CHANGED, or the compatibility of InnoDB data
files would be at risk! */
#define DICT_MAX_INDEX_COL_LEN REC_MAX_INDEX_COL_LEN

** Data structure for a field in an index */
struct dict_field_struct{
 dict_col_t* col; /*!< pointer to the table column */
 const char* name; /*!< name of the column */
 unsigned prefix_len:10; /*!< 0 or the length of the column
     prefix in bytes in a MySQL index of
     type, e.g., INDEX (textcol(25));
     must be smaller than
     DICT_MAX_INDEX_COL_LEN; NOTE that
     in the UTF-8 charset, MySQL sets this
     to 3 * the prefix len in UTF-8 chars */
 unsigned fixed_len:10; /*!< 0 or the fixed length of the
     column if smaller than
     DICT_MAX_INDEX_COL_LEN */
};

Of course 2**10 = 1024. That seems to be the limit here. dict_field_t (typedef of this struct) is used relatively heavily around the place, so auditing its usage would be more work.

UNIV_INTERN
uint32_t
InnobaseEngine::max_supported_key_part_length() const
{
  return(DICT_MAX_INDEX_COL_LEN - 1);
}

dict0dict.c:
in dict_index_add_col():
 /* Long fixed-length fields that need external storage are treated as
 variable-length fields, so that the extern flag can be embedded in
 the length word. */
 if (field->fixed_len > DICT_MAX_INDEX_COL_LEN) {
  field->fixed_len = 0;
 }
#if DICT_MAX_INDEX_COL_LEN != 768*4
 /* The comparison limit above must be constant. If it were
 changed, the disk format of some fixed-length columns would
 change, which would be a disaster. */
# error "DICT_MAX_INDEX_COL_LEN != 1024"
#endif

data0data.c:
in dtuple_convert_big_rec():
**************************************************************//**
Moves parts of long fields in entry to the big record vector so that
the size of tu...

Read more...

Stewart Smith (stewart) wrote :

So at the end of all that, I'm comfortable to go to 1024. so we have the same limit as with 3byte utf8 in mysql. This should be enough for people. Going above this will require further testing and changes.

Brian Aker (brianaker) wrote :

Got a patch? :)

On May 12, 2010, at 7:24 AM, Stewart Smith wrote:

> So at the end of all that, I'm comfortable to go to 1024. so we have the
> same limit as with 3byte utf8 in mysql. This should be enough for
> people. Going above this will require further testing and changes.
>
> --
> innodb max index column length is 191, not 256 or 768 as one would "expect"
> https://bugs.launchpad.net/bugs/578842
> You received this bug notification because you are a member of Drizzle-
> developers, which is subscribed to Drizzle.
>
> Status in A Lightweight SQL Database for Cloud and Web: In Progress
> Status in Drizzle dexter series: In Progress
>
> Bug description:
> DICT_MAX_INDEX_COL_LEN
>
> was 768
>
> could be 1024
>
> or could be 768*4.
>
>
> <stewart> lbieber, 256*4 would be consistent with mysql in utf8 mode.
> <stewart> lbieber, and 768*4(3072) would be consistent with mysql in latin1
>
>

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.