error mismatch on maximum primary key length versus the actual

[Aarti Pai]

 ./test-run --suite=broken subselect_sj2.test

The error states that the maximum key length is 765 bytes. The primary key length in this query is 600 bytes which is below the maximum. The max length in the error message does not match the actual max length that Drizzle supports..
(Note, if the length of pk1, pk2, pk3 are changed to 100, the query runs.)

According to MySQL docs:
Size of primary key is 8 bytes if no primary key is provided and otherwise the sum of the max size of the attributes in the primary key.

subselect_sj2

broken.subselect_sj2 [ fail ]

drizzletest: At line 35: query 'create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200), pk3 char(200),
primary key(pk1, pk2, pk3)
) engine=innodb' failed: 1071: Specified key was too long; max key length is 767 bytes

The result from queries just before the failure was:
< snip >
7 4
8 4
9 5
explain select * from t2 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary
1 PRIMARY t2 ref b b 5 test.t1.a 1 End temporary
select * from t2 where b in (select a from t1);
a b
1 1
2 1
3 2
4 2
create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200), pk3 char(200),
primary key(pk1, pk2, pk3)
) engine=innodb;

More results from queries before failure can be found in /export/home/bzrwork/drizzle/tests/var/log/subselect_sj2.log

Aborting: broken.subselect_sj2 failed in default mode.
To continue, re-run with '--force'.
Stopping All Servers

[Brian Aker (brianaker)]

The limitation different I believe is due to UTF8 column

On Dec 21, 2008, at 3:20 PM, Aarti Pai wrote:

> Public bug reported:
>
> ./test-run --suite=broken subselect_sj2.test
>
> The error states that the maximum key length is 765 bytes. The
> primary key length in this query is 600 bytes which is below the
> maximum. The max length in the error message does not match the
> actual max length that Drizzle supports..
> (Note, if the length of pk1, pk2, pk3 are changed to 100, the query
> runs.)
>
> According to MySQL docs:
> Size of primary key is 8 bytes if no primary key is provided and
> otherwise the sum of the max size of the attributes in the primary
> key.
>
> subselect_sj2
>
> broken.subselect_sj2 [ fail ]
>
> drizzletest: At line 35: query 'create table t3 (
> a int,
> b int,
> key(b),
> pk1 char(200), pk2 char(200), pk3 char(200),
> primary key(pk1, pk2, pk3)
> ) engine=innodb' failed: 1071: Specified key was too long; max key
> length is 767 bytes
>
> The result from queries just before the failure was:
> < snip >
> 7 4
> 8 4
> 9 5
> explain select * from t2 where b in (select a from t1);
> id select_type table type possible_keys key
> key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL
> 3 Start temporary
> 1 PRIMARY t2 ref b b 5
> test.t1.a 1 End temporary
> select * from t2 where b in (select a from t1);
> a b
> 1 1
> 2 1
> 3 2
> 4 2
> create table t3 (
> a int,
> b int,
> key(b),
> pk1 char(200), pk2 char(200), pk3 char(200),
> primary key(pk1, pk2, pk3)
> ) engine=innodb;
>
> More results from queries before failure can be found in
> /export/home/bzrwork/drizzle/tests/var/log/subselect_sj2.log
>
> Aborting: broken.subselect_sj2 failed in default mode.
> To continue, re-run with '--force'.
> Stopping All Servers
>
> ** Affects: drizzle
> Importance: Undecided
> Status: New
>
> --
> error mismatch on maximum primary key length versus the actual
> https://bugs.launchpad.net/bugs/310344
> 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: New
>
> Bug description:
> ./test-run --suite=broken subselect_sj2.test
>
> The error states that the maximum key length is 765 bytes. The
> primary key length in this query is 600 bytes which is below the
> maximum. The max length in the error message does not match the
> actual max length that Drizzle supports..
> (Note, if the length of pk1, pk2, pk3 are changed to 100, the query
> runs.)
>
> According to MySQL docs:
> Size of primary key is 8 bytes if no primary key is provided and
> otherwise the sum of the max size of the attributes in the primary
> key.
>
> subselect_sj2
>
> broken.subselect_sj2 [ fail ]
>
> drizzletest: At line 35: query 'create table t3 (
> a int,
> b int,
> key(b),
> pk1 char(200), pk2 char(200), pk3 char(200),
> primary key(pk1, pk2, pk3)
> ) engine=innodb' failed: 1071: Specified key was too long; max key
> length ...

[Padraig O'Sullivan (posulliv)]

Confirmed on trunk:

$ cd tests
$ ./test-run subselect-sj2

main.subselect_sj2 [ fail ]

drizzletest: At line 36: query 'create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200), pk3 char(200),
primary key(pk1, pk2, pk3)
) engine=innodb' failed: 1071: Specified key was too long; max key length is 767 bytes

The result from queries just before the failure was:
< snip >
7 4
8 4
9 5
explain select * from t2 where b in (select a from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary
1 PRIMARY t2 ref b b 5 test.t1.a 1 End temporary
select * from t2 where b in (select a from t1);
a b
1 1
2 1
3 2
4 2
create table t3 (
a int,
b int,
key(b),
pk1 char(200), pk2 char(200), pk3 char(200),
primary key(pk1, pk2, pk3)
) engine=innodb;

-Padraig

[Andrew Ettinger (sillydeveloper)]

This actually happens on any char put in as pk.

[Andrew Ettinger (sillydeveloper)]

I amend my last comment: This is because mbmaxlen is 4 bytes instead of 3, not necessarily any char value.

So it checks for a 767 byte max primary key length, but since mbmaxlen is set to 4 bytes 4 x 200 = 800 bytes and it fails.

Any idea why mbmaxlen is at 4 bytes instead of 3?

[Andrew Ettinger (sillydeveloper)]

Ok, I see. I thought UTF8 can only be 3 bytes.

Solution is to update the check for 256 * 4 = 1024 max length.

[Lee Bieber (kalebral-deactivatedaccount)]

Andrew - any updates on this one?

[Andrew Ettinger (sillydeveloper)]

Still doesn't pass the innoDB tests, which are noted 'do not change' so I'm loathe to adjust them without some feedback.

Otherwise the attached branch seems to be ok.

[Lee Bieber (kalebral-deactivatedaccount)]

HI Andrew, would you clarify, do you have a branch that has a fix for this? I don't see any branch from you that has been proposed for merging.

[Andrew Ettinger (sillydeveloper)]

Let me re-merge and I'll propose it. Thanks Lee!

On Dec 4, 2009, at 9:01 AM, Lee Bieber wrote:

> HI Andrew, would you clarify, do you have a branch that has a fix for
> this? I don't see any branch from you that has been proposed for
> merging.
>
> --
> error mismatch on maximum primary key length versus the actual
> https://bugs.launchpad.net/bugs/310344
> You received this bug notification because you are a bug assignee.
>
> Status in A Lightweight SQL Database for Cloud and Web: In Progress
>
> Bug description:
> ./test-run --suite=broken subselect_sj2.test
>
> The error states that the maximum key length is 765 bytes. The
> primary key length in this query is 600 bytes which is below the
> maximum. The max length in the error message does not match the
> actual max length that Drizzle supports..
> (Note, if the length of pk1, pk2, pk3 are changed to 100, the query
> runs.)
>
> According to MySQL docs:
> Size of primary key is 8 bytes if no primary key is provided and
> otherwise the sum of the max size of the attributes in the primary
> key.
>
> subselect_sj2
>
> broken.subselect_sj2 [ fail ]
>
> drizzletest: At line 35: query 'create table t3 (
> a int,
> b int,
> key(b),
> pk1 char(200), pk2 char(200), pk3 char(200),
> primary key(pk1, pk2, pk3)
> ) engine=innodb' failed: 1071: Specified key was too long; max key
> length is 767 bytes
>
> The result from queries just before the failure was:
> < snip >
> 7 4
> 8 4
> 9 5
> explain select * from t2 where b in (select a from t1);
> id select_type table type possible_keys key
> key_len ref rows Extra
> 1 PRIMARY t1 ALL NULL NULL NULL NULL
> 3 Start temporary
> 1 PRIMARY t2 ref b b 5
> test.t1.a 1 End temporary
> select * from t2 where b in (select a from t1);
> a b
> 1 1
> 2 1
> 3 2
> 4 2
> create table t3 (
> a int,
> b int,
> key(b),
> pk1 char(200), pk2 char(200), pk3 char(200),
> primary key(pk1, pk2, pk3)
> ) engine=innodb;
>
> More results from queries before failure can be found in /export/
> home/bzrwork/drizzle/tests/var/log/subselect_sj2.log
>
> Aborting: broken.subselect_sj2 failed in default mode.
> To continue, re-run with '--force'.
> Stopping All Servers
>
>

[Lee Bieber (kalebral-deactivatedaccount)]

Andrew, any updates on this?

[Stewart Smith (stewart)]

Marking as invalid as we've now got the same character limits as 3byte utf8 limit in mysql.