Bug #578842 “innodb max index column length is 191, not 256 or 7...” : Bugs : HailDB

Revision history for this message

Stewart Smith (stewart) wrote on 2010-05-11:

#1

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-deactivatedaccount) on 2010-05-11

Changed in drizzle:
importance:	Undecided → High

Revision history for this message

Stewart Smith (stewart) wrote on 2010-05-11:

#2

http://drupal.org/node/554820

drupal using the mysql utf8 3byte limit

Revision history for this message

Brian Aker (brianaker) wrote on 2010-05-11: Re: [Bug 578842] Re: innodb max index column length is 191, not 256 or 768 as one would "expect"

#3

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

Revision history for this message

Stewart Smith (stewart) wrote on 2010-05-12:

#4

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

Revision history for this message

Stewart Smith (stewart) wrote on 2010-05-12:

#5

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...

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 tuple drops below the maximum record size allowed in the
database. Moves data only from those fields which are not necessary
to determine uniquely the insertion place of the tuple in the index.
@return own: created big record vector, NULL if we are not able to
shorten the entry enough, i.e., if there are too many fixed-length or
short fields in entry or the index is clustered */

if (dict_table_get_format(index->table) < DICT_TF_FORMAT_ZIP) {
		/* up to MySQL 5.1: store a 768-byte prefix locally */
		local_len = BTR_EXTERN_FIELD_REF_SIZE + DICT_MAX_INDEX_COL_LEN;
	} else {
		/* new-format table: do not store any BLOB prefix locally */
		local_len = BTR_EXTERN_FIELD_REF_SIZE;
	}

/**************************************************************//**
Puts back to entry the data stored in vector. Note that to ensure the
fields in entry can accommodate the data, vector must have been created
from entry with dtuple_convert_big_rec. */
UNIV_INTERN
void
dtuple_convert_back_big_rec()
	for (; b < end; b++) {
		dfield_t*	dfield;
		ulint		local_len;

dfield = dtuple_get_nth_field(entry, b->field_no);
		local_len = dfield_get_len(dfield);

ut_ad(dfield_is_ext(dfield));
		ut_ad(local_len >= BTR_EXTERN_FIELD_REF_SIZE);

local_len -= BTR_EXTERN_FIELD_REF_SIZE;

ut_ad(local_len <= DICT_MAX_INDEX_COL_LEN);

dfield_set_data(dfield,
				(char*) b->data - local_len,
				b->len + local_len);
	}

in trx0rec.c:
/*Reads from an undo log record a stored column value.
@return	remaining part of undo log record after reading these values */
static byte* trx_undo_rec_get_col_val()

case UNIV_EXTERN_STORAGE_FIELD:

ut_ad(*orig_len >= BTR_EXTERN_FIELD_REF_SIZE);
		ut_ad(*len > *orig_len);
		ut_ad(*len >= REC_MAX_INDEX_COL_LEN
		      + BTR_EXTERN_FIELD_REF_SIZE);

Fetch a prefix of an externally stored column, for writing to the undo log
of an update or delete marking of a clustered index record.
@return	ext_buf */
static
byte*
trx_undo_page_fetch_ext(
/*====================*/
	byte*		ext_buf,	/*!< in: a buffer of
					REC_MAX_INDEX_COL_LEN
					+ BTR_EXTERN_FIELD_REF_SIZE */
	ulint		zip_size,	/*!< compressed page size in bytes,
					or 0 for uncompressed BLOB  */
	const byte*	field,		/*!< in: an externally stored column */
	ulint*		len)		/*!< in: length of field;
					out: used length of ext_buf */
{
	/* Fetch the BLOB. */
	ulint	ext_len = btr_copy_externally_stored_field_prefix(
		ext_buf, REC_MAX_INDEX_COL_LEN, zip_size, field, *len);
	/* BLOBs should always be nonempty. */
	ut_a(ext_len);

(and a bunch more stuff to read/write undo log)

/*----------------------------------------*/
	/* In the case of a delete marking, and also in the case of an update
	where any ordering field of any index changes, store the values of all
	columns which occur as ordering fields in any index. This info is used
	in the purge of old versions where we use it to build and search the
	delete marked index records, to look if we can remove them from the
	index tree. Note that starting from 4.0.14 also externally stored
	fields can be ordering in some index. Starting from 5.2, we no longer
	store REC_MAX_INDEX_COL_LEN first bytes to the undo log record,
	but we can construct the column prefix fields in the index by
	fetching the first page of the BLOB that is pointed to by the
	clustered index. This works also in crash recovery, because all pages
	(including BLOBs) are recovered before anything is rolled back. */

Revision history for this message

Stewart Smith (stewart) wrote on 2010-05-12:

#6

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.

Revision history for this message

Brian Aker (brianaker) wrote on 2010-05-12:

#7

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

	Status	Importance	Assigned to	Milestone
Drizzle	Fix Released	High	Stewart Smith	Drizzle 2010-05-24
Dexter	Fix Released	High	Stewart Smith	Drizzle 2010-05-24
HailDB	New	Undecided	Unassigned
MySQL Server	Unknown	Unknown	mysql-bugs #32915

HailDB

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

Bug Description

Related branches

Duplicates of this bug

Other bug subscribers

Remote bug watches