From: Ingo Struewing
Date: December 27 2007 7:44pm
Subject: bk commit into 6.0 tree (istruewing:1.2777) BUG#4541
List-Archive: http://lists.mysql.com/commits/40435
X-Bug: 4541
Message-Id: <E1J7yew-0000aN-NN@stella>

Below is the list of changes that have just been committed into a local
6.0 repository of istruewing.  When istruewing does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-12-27 20:44:46+01:00, istruewing@stripped +13 -0
  Bug#4541 - "Specified key was too long;
              max key length is 1000 bytes" with utf8
  
  Not to be pushed. This patch is meant as a courtesy to the
  community. It raises the maximum key length to 4005 bytes
  or 1001 UTF-8 characters. This is the maximum raise possible
  without changing other parameters. Patch for MySQL 6.0.
  
  Note that since 6.0 UTF-8 characters can take up to 4 bytes.
  
  This patch will not go into the source repositories because
  it results in bigger buffers and as such increases memory
  footprint and decreases performance. I do not know if the
  performance hit is measurable, but it exists in theory. This
  is enough to keep it out of the standard source.
  
  The planned solution for the problem is to add hash indexes
  to MyISAM. This would allow for arbitrary long keys. For a
  couple of reasons, I cannot expand on at the moment, I guess
  that this will never be implemented for MyISAM. This is my
  personal estimate. Do not take it as official statement.
  
  Anyway, this patch does now allow projects or users, who build
  from source, to work around the problem reported in bug #4541.

  include/myisam.h@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +15 -3
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Raised MI_MAX_KEY_LENGTH to 4005.
    Adjusted MI_MAX_POSSIBLE_KEY_BUFF accordingly.

  mysql-test/r/ctype_utf16.result@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +4 -4
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test result for longer keys.

  mysql-test/r/ctype_utf32.result@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +4 -4
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test result for longer keys.

  mysql-test/r/ctype_utf8.result@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +4 -2
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test result for longer keys.

  mysql-test/r/innodb.result@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +1 -1
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test result for longer keys.

  mysql-test/r/mix2_myisam.result@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +1 -1
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test result for longer keys.

  mysql-test/r/myisam.result@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +192 -27
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test result for longer keys.
    Added test result.

  mysql-test/r/type_blob.result@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +13 -5
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test result for longer keys.

  mysql-test/t/ctype_utf16.test@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +4 -3
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test for longer keys.

  mysql-test/t/ctype_utf32.test@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +4 -3
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test for longer keys.

  mysql-test/t/ctype_utf8.test@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +6 -1
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test for longer keys.

  mysql-test/t/myisam.test@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +78 -4
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test for longer keys.
    Added test.

  mysql-test/t/type_blob.test@stripped, 2007-12-27 20:44:44+01:00, istruewing@stripped +8 -2
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Fixed test for longer keys.

diff -Nrup a/include/myisam.h b/include/myisam.h
--- a/include/myisam.h	2007-10-22 13:43:27 +02:00
+++ b/include/myisam.h	2007-12-27 20:44:44 +01:00
@@ -53,12 +53,24 @@ extern "C" {
   The following defines can be increased if necessary.
   But beware the dependency of MI_MAX_POSSIBLE_KEY_BUFF and MI_MAX_KEY_LENGTH.
 */
-#define MI_MAX_KEY_LENGTH           1332            /* Max length in bytes */
 #define MI_MAX_KEY_SEG              16              /* Max segments for key */
-
-#define MI_MAX_POSSIBLE_KEY_BUFF (MI_MAX_KEY_LENGTH + 6 + 6) /* For mi_check */
+  /*
+    Max key length computes from max key block. At least 4 keys should
+    go into a key block. Each key has a length (1 or 3 bytes), a data
+    file pointer (max 8 bytes), optionally a key file pointer (max 8
+    bytes), optional length per key segment (max 3 bytes), and an
+    optional null byte per key segment. The key_length computation in
+    mi_create() adds another data pointer per key (max 8 bytes). A block
+    has a 2 byte length and optionally a key file pointer. See
+    myisamdef.h:MI_BLOCK_SIZE() and mi_create(). With
+    MI_MAX_KEY_BLOCK_LENGTH = 16384 and MI_MAX_KEY_SEG = 16 we have
+    (16384-2-8)/4-8-8-(8+(3+1)*16) = 4005 bytes.
+  */
+#define MI_MAX_KEY_LENGTH 4005          /* Max key length in bytes */
 
 #define MI_MAX_KEY_BUFF  (MI_MAX_KEY_LENGTH+MI_MAX_KEY_SEG*6+8+8)
+#define MI_MAX_POSSIBLE_KEY_BUFF (MI_MAX_KEY_LENGTH+24+6+6) /* For myisam_chk */
+#define MI_MAX_POSSIBLE_KEY 64                              /* For myisam_chk */
 #define MI_MAX_MSG_BUF      1024 /* used in CHECK TABLE, REPAIR TABLE */
 #define MI_NAME_IEXT	".MYI"
 #define MI_NAME_DEXT	".MYD"
diff -Nrup a/mysql-test/r/ctype_utf16.result b/mysql-test/r/ctype_utf16.result
--- a/mysql-test/r/ctype_utf16.result	2007-10-30 14:44:45 +01:00
+++ b/mysql-test/r/ctype_utf16.result	2007-12-27 20:44:44 +01:00
@@ -1016,16 +1016,16 @@ t1	CREATE TABLE `t1` (
   `a` mediumtext CHARACTER SET utf16
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
-create table t1 (a varchar(250) character set utf16 primary key);
+create table t1 (a varchar(1001) character set utf16 primary key);
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` varchar(250) CHARACTER SET utf16 NOT NULL,
+  `a` varchar(1001) CHARACTER SET utf16 NOT NULL,
   PRIMARY KEY (`a`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
-create table t1 (a varchar(334) character set utf16 primary key);
-ERROR 42000: Specified key was too long; max key length is 1332 bytes
+create table t1 (a varchar(1002) character set utf16 primary key);
+ERROR 42000: Specified key was too long; max key length is 4005 bytes
 create table t1 (a char(1) character set utf16);
 insert into t1 values (0xD800DC00),(0xD800DCFF),(0xDB7FDC00),(0xDB7FDCFF);
 insert into t1 values (0x00C0), (0x00FF),(0xE000), (0xFFFF);
diff -Nrup a/mysql-test/r/ctype_utf32.result b/mysql-test/r/ctype_utf32.result
--- a/mysql-test/r/ctype_utf32.result	2007-10-30 14:44:45 +01:00
+++ b/mysql-test/r/ctype_utf32.result	2007-12-27 20:44:44 +01:00
@@ -987,16 +987,16 @@ t1	CREATE TABLE `t1` (
   `a` mediumtext CHARACTER SET utf32
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
-create table t1 (a varchar(250) character set utf32 primary key);
+create table t1 (a varchar(1001) character set utf32 primary key);
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` varchar(250) CHARACTER SET utf32 NOT NULL,
+  `a` varchar(1001) CHARACTER SET utf32 NOT NULL,
   PRIMARY KEY (`a`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
-create table t1 (a varchar(334) character set utf32 primary key);
-ERROR 42000: Specified key was too long; max key length is 1332 bytes
+create table t1 (a varchar(1002) character set utf32 primary key);
+ERROR 42000: Specified key was too long; max key length is 4005 bytes
 create table t1 (a varchar(333) character set utf32, key(a));
 insert into t1 values (repeat('a',333)), (repeat('b',333));
 flush tables;
diff -Nrup a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
--- a/mysql-test/r/ctype_utf8.result	2007-11-15 14:41:32 +01:00
+++ b/mysql-test/r/ctype_utf8.result	2007-12-27 20:44:44 +01:00
@@ -240,8 +240,10 @@ select hex(s1) from t1;
 hex(s1)
 41
 drop table t1;
-create table t1 (a text character set utf8, primary key(a(360)));
-ERROR 42000: Specified key was too long; max key length is 1332 bytes
+create table t1 (a text character set utf8, primary key(a(1002)));
+ERROR 42000: Specified key was too long; max key length is 4005 bytes
+create table t1 (a text character set utf8, primary key(a(1001)));
+drop table t1;
 CREATE TABLE t1 ( a varchar(10) ) CHARACTER SET utf8;
 INSERT INTO t1 VALUES ( 'test' );
 SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a;
diff -Nrup a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
--- a/mysql-test/r/innodb.result	2007-12-13 13:56:17 +01:00
+++ b/mysql-test/r/innodb.result	2007-12-27 20:44:44 +01:00
@@ -2695,7 +2695,7 @@ c varchar(255) character set utf8,
 d varchar(255) character set utf8,
 e varchar(255) character set utf8,
 key (a,b,c,d,e)) engine=innodb;
-ERROR 42000: Specified key was too long; max key length is 3072 bytes
+ERROR 42000: Specified key was too long; max key length is 3500 bytes
 create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb;
 create table t2 (s1 binary(2),primary key (s1)) engine=innodb;
 create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb;
diff -Nrup a/mysql-test/r/mix2_myisam.result b/mysql-test/r/mix2_myisam.result
--- a/mysql-test/r/mix2_myisam.result	2007-12-13 13:47:12 +01:00
+++ b/mysql-test/r/mix2_myisam.result	2007-12-27 20:44:44 +01:00
@@ -1979,7 +1979,7 @@ a	b
 drop table t1;
 create table t1 (v varchar(65530), key(v));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1332 bytes
+Warning	1071	Specified key was too long; max key length is 4005 bytes
 drop table t1;
 create table t1 (v varchar(65536));
 Warnings:
diff -Nrup a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
--- a/mysql-test/r/myisam.result	2007-12-13 13:56:17 +01:00
+++ b/mysql-test/r/myisam.result	2007-12-27 20:44:44 +01:00
@@ -322,11 +322,34 @@ CHECK TABLE t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
 drop table t1;
-CREATE TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300), KEY t1 (a, b, c, d, e));
-ERROR 42000: Specified key was too long; max key length is 1332 bytes
-CREATE TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300));
-ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e);
-ERROR 42000: Specified key was too long; max key length is 1332 bytes
+CREATE TABLE t1 (
+a varchar(255), b varchar(255), c varchar(255), d varchar(255),
+e varchar(255), f varchar(255), g varchar(255), h varchar(255),
+i varchar(255), j varchar(255), k varchar(255), l varchar(255),
+m varchar(255), n varchar(255), o varchar(255), p varchar(181),
+KEY t1 (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p));
+ERROR 42000: Specified key was too long; max key length is 4005 bytes
+CREATE TABLE t1 (
+a varchar(255), b varchar(255), c varchar(255), d varchar(255),
+e varchar(255), f varchar(255), g varchar(255), h varchar(255),
+i varchar(255), j varchar(255), k varchar(255), l varchar(255),
+m varchar(255), n varchar(255), o varchar(255), p varchar(181));
+ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p);
+ERROR 42000: Specified key was too long; max key length is 4005 bytes
+DROP TABLE t1;
+CREATE TABLE t1 (
+a varchar(255), b varchar(255), c varchar(255), d varchar(255),
+e varchar(255), f varchar(255), g varchar(255), h varchar(255),
+i varchar(255), j varchar(255), k varchar(255), l varchar(255),
+m varchar(255), n varchar(255), o varchar(255), p varchar(180),
+KEY t1 (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p));
+DROP TABLE t1;
+CREATE TABLE t1 (
+a varchar(255), b varchar(255), c varchar(255), d varchar(255),
+e varchar(255), f varchar(255), g varchar(255), h varchar(255),
+i varchar(255), j varchar(255), k varchar(255), l varchar(255),
+m varchar(255), n varchar(255), o varchar(255), p varchar(180));
+ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p);
 DROP TABLE t1;
 CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a));
 INSERT into t1 values (0, null, 0), (0, null, 1), (0, null, 2), (0, null,3), (1,1,4);
@@ -1077,6 +1100,160 @@ length(c1)	c1
 2		A
 2	 B
 DROP TABLE t1;
+CREATE TABLE t1 (
+c1 VARCHAR(250) NOT NULL,
+c2 VARCHAR(250) NOT NULL,
+c3 VARCHAR(250) NOT NULL,
+c4 VARCHAR(252) NOT NULL,
+KEY i1 (c1, c2, c3, c4)) DEFAULT CHARSET=UTF8;
+ERROR 42000: Specified key was too long; max key length is 4005 bytes
+CREATE TABLE t1 (
+c1 VARCHAR(250) NOT NULL,
+c2 VARCHAR(250) NOT NULL,
+c3 VARCHAR(250) NOT NULL,
+c4 VARCHAR(251) NOT NULL,
+KEY i1 (c1, c2, c3, c4)) DEFAULT CHARSET=UTF8;
+INSERT INTO t1 VALUES
+(REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+CONCAT(REPEAT('M', 250), '1')),
+(REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+CONCAT(REPEAT('M', 250), '7')),
+(REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+CONCAT(REPEAT('M', 250), '5')),
+(REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+CONCAT(REPEAT('M', 250), '8')),
+(REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+CONCAT(REPEAT('M', 250), '6')),
+(REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+CONCAT(REPEAT('M', 250), '5'));
+CHECK TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+OPTIMIZE TABLE t1;
+Table	test.t1
+Op	optimize
+Msg_type	status
+Msg_text	OK
+SHOW CREATE TABLE t1;
+Table	t1
+Create Table	CREATE TABLE `t1` (
+  `c1` varchar(250) NOT NULL,
+  `c2` varchar(250) NOT NULL,
+  `c3` varchar(250) NOT NULL,
+  `c4` varchar(251) NOT NULL,
+  KEY `i1` (`c1`,`c2`,`c3`,`c4`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8
+SHOW TABLE STATUS LIKE 't1';
+Name	t1
+Engine	MyISAM
+Version	10
+Row_format	Dynamic
+Rows	6
+Avg_row_length	#
+Data_length	#
+Max_data_length	#
+Index_length	#
+Data_free	0
+Auto_increment	NULL
+Create_time	#
+Update_time	#
+Check_time	#
+Collation	utf8_general_ci
+Checksum	NULL
+Create_options	
+Comment	
+SHOW INDEX FROM t1;
+Table	t1
+Non_unique	1
+Key_name	i1
+Seq_in_index	1
+Column_name	c1
+Collation	A
+Cardinality	1
+Sub_part	NULL
+Packed	NULL
+Null	
+Index_type	BTREE
+Comment	
+Index_Comment	
+Table	t1
+Non_unique	1
+Key_name	i1
+Seq_in_index	2
+Column_name	c2
+Collation	A
+Cardinality	1
+Sub_part	NULL
+Packed	NULL
+Null	
+Index_type	BTREE
+Comment	
+Index_Comment	
+Table	t1
+Non_unique	1
+Key_name	i1
+Seq_in_index	3
+Column_name	c3
+Collation	A
+Cardinality	1
+Sub_part	NULL
+Packed	NULL
+Null	
+Index_type	BTREE
+Comment	
+Index_Comment	
+Table	t1
+Non_unique	1
+Key_name	i1
+Seq_in_index	4
+Column_name	c4
+Collation	A
+Cardinality	6
+Sub_part	NULL
+Packed	NULL
+Null	
+Index_type	BTREE
+Comment	
+Index_Comment	
+EXPLAIN SELECT SUBSTR(c4, 240) FROM t1
+ORDER BY c1, c2, c3, c4;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	i1
+key_len	4012
+ref	NULL
+rows	6
+Extra	Using index
+SELECT SUBSTR(c4, 240) FROM t1 ORDER BY c1, c2, c3, c4;
+SUBSTR(c4, 240)
+MMMMMMMMMMM1
+MMMMMMMMMMM5
+MMMMMMMMMMM5
+MMMMMMMMMMM6
+MMMMMMMMMMM7
+MMMMMMMMMMM8
+DELETE FROM t1 WHERE c4 LIKE '%5' LIMIT 1;
+UPDATE t1 SET c4 = CONCAT(REPEAT('M', 250), '9') WHERE c4 LIKE '%1';
+SELECT SUBSTR(c4, 240) FROM t1 ORDER BY c1, c2, c3, c4;
+SUBSTR(c4, 240)
+MMMMMMMMMMM5
+MMMMMMMMMMM6
+MMMMMMMMMMM7
+MMMMMMMMMMM8
+MMMMMMMMMMM9
+CHECK TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+REPAIR TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	repair	status	OK
+CHECK TABLE t1 EXTENDED;
+Table	Op	Msg_type	Msg_text
+test.t1	check	status	OK
+DROP TABLE t1;
 End of 4.1 tests
 set storage_engine=MyISAM;
 drop table if exists t1,t2,t3;
@@ -1652,7 +1829,7 @@ a	b
 drop table t1;
 create table t1 (v varchar(65530), key(v));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1332 bytes
+Warning	1071	Specified key was too long; max key length is 4005 bytes
 drop table if exists t1;
 create table t1 (v varchar(65536));
 Warnings:
@@ -1851,35 +2028,29 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 (a varchar(2048), key `a` (a));
-Warnings:
-Warning	1071	Specified key was too long; max key length is 1332 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` varchar(2048) DEFAULT NULL,
-  KEY `a` (`a`(1332))
+  KEY `a` (`a`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 (a varchar(2048), key `a` (a) key_block_size=1024);
-Warnings:
-Warning	1071	Specified key was too long; max key length is 1332 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` varchar(2048) DEFAULT NULL,
-  KEY `a` (`a`(1332)) KEY_BLOCK_SIZE=6144
+  KEY `a` (`a`) KEY_BLOCK_SIZE=9216
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=1024;
-Warnings:
-Warning	1071	Specified key was too long; max key length is 1332 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL,
   `b` varchar(2048) DEFAULT NULL,
   KEY `a` (`a`),
-  KEY `b` (`b`(1332)) KEY_BLOCK_SIZE=6144
+  KEY `b` (`b`) KEY_BLOCK_SIZE=9216
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=1024
 alter table t1 key_block_size=2048;
 show create table t1;
@@ -1888,7 +2059,7 @@ t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL,
   `b` varchar(2048) DEFAULT NULL,
   KEY `a` (`a`) KEY_BLOCK_SIZE=1024,
-  KEY `b` (`b`(1332)) KEY_BLOCK_SIZE=8192
+  KEY `b` (`b`) KEY_BLOCK_SIZE=16384
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2048
 alter table t1 add c int, add key (c);
 show create table t1;
@@ -1898,7 +2069,7 @@ t1	CREATE TABLE `t1` (
   `b` varchar(2048) DEFAULT NULL,
   `c` int(11) DEFAULT NULL,
   KEY `a` (`a`) KEY_BLOCK_SIZE=1024,
-  KEY `b` (`b`(1332)) KEY_BLOCK_SIZE=8192,
+  KEY `b` (`b`) KEY_BLOCK_SIZE=16384,
   KEY `c` (`c`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2048
 alter table t1 key_block_size=0;
@@ -1911,33 +2082,29 @@ t1	CREATE TABLE `t1` (
   `c` int(11) DEFAULT NULL,
   `d` int(11) DEFAULT NULL,
   KEY `a` (`a`) KEY_BLOCK_SIZE=1024,
-  KEY `b` (`b`(1332)) KEY_BLOCK_SIZE=8192,
+  KEY `b` (`b`) KEY_BLOCK_SIZE=16384,
   KEY `c` (`c`) KEY_BLOCK_SIZE=2048,
   KEY `d` (`d`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 (a int not null, b varchar(2048), key (a), key(b)) key_block_size=8192;
-Warnings:
-Warning	1071	Specified key was too long; max key length is 1332 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL,
   `b` varchar(2048) DEFAULT NULL,
   KEY `a` (`a`),
-  KEY `b` (`b`(1332))
+  KEY `b` (`b`) KEY_BLOCK_SIZE=16384
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8192
 drop table t1;
 create table t1 (a int not null, b varchar(2048), key (a) key_block_size=1024, key(b)) key_block_size=8192;
-Warnings:
-Warning	1071	Specified key was too long; max key length is 1332 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL,
   `b` varchar(2048) DEFAULT NULL,
   KEY `a` (`a`) KEY_BLOCK_SIZE=1024,
-  KEY `b` (`b`(1332))
+  KEY `b` (`b`) KEY_BLOCK_SIZE=16384
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8192
 drop table t1;
 create table t1 (a int not null, b int, key (a) key_block_size=1024, key(b) key_block_size=8192) key_block_size=16384;
@@ -1959,13 +2126,11 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000);
-Warnings:
-Warning	1071	Specified key was too long; max key length is 1332 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` varchar(2048) DEFAULT NULL,
-  KEY `a` (`a`(1332)) KEY_BLOCK_SIZE=6144
+  KEY `a` (`a`) KEY_BLOCK_SIZE=9216
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 create table t1 (a int not null, key `a` (a) key_block_size=1025);
diff -Nrup a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
--- a/mysql-test/r/type_blob.result	2007-12-13 13:47:12 +01:00
+++ b/mysql-test/r/type_blob.result	2007-12-27 20:44:44 +01:00
@@ -356,16 +356,24 @@ HELLO MY	1
 a	1
 hello	1
 drop table t1;
-create table t1 (a text, unique (a(2100)));
-ERROR 42000: Specified key was too long; max key length is 1332 bytes
-create table t1 (a text, key (a(2100)));
+create table t1 (a text, unique (a(4006)));
+ERROR 42000: Specified key was too long; max key length is 4005 bytes
+create table t1 (a text, unique (a(4005)));
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` text,
+  UNIQUE KEY `a` (`a`(4005))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+create table t1 (a text, key (a(4006)));
 Warnings:
-Warning	1071	Specified key was too long; max key length is 1332 bytes
+Warning	1071	Specified key was too long; max key length is 4005 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` text,
-  KEY `a` (`a`(1332))
+  KEY `a` (`a`(4005))
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 CREATE TABLE t1 (
diff -Nrup a/mysql-test/t/ctype_utf16.test b/mysql-test/t/ctype_utf16.test
--- a/mysql-test/t/ctype_utf16.test	2007-10-30 14:44:45 +01:00
+++ b/mysql-test/t/ctype_utf16.test	2007-12-27 20:44:44 +01:00
@@ -660,13 +660,14 @@ show create table t1;
 drop table t1;
 
 #
-# Testing that maximim possible key length is 1000 bytes
+# Testing that maximum possible key length is 4005 bytes
+# Limit raised to 1001 UTF-16 chars by fix for Bug#4541. See myisam.test.
 #
-create table t1 (a varchar(250) character set utf16 primary key);
+create table t1 (a varchar(1001) character set utf16 primary key);
 show create table t1;
 drop table t1;
 --error ER_TOO_LONG_KEY
-create table t1 (a varchar(334) character set utf16 primary key);
+create table t1 (a varchar(1002) character set utf16 primary key);
 
 #
 # Conversion to utf8
diff -Nrup a/mysql-test/t/ctype_utf32.test b/mysql-test/t/ctype_utf32.test
--- a/mysql-test/t/ctype_utf32.test	2007-10-30 14:44:45 +01:00
+++ b/mysql-test/t/ctype_utf32.test	2007-12-27 20:44:44 +01:00
@@ -693,13 +693,14 @@ show create table t1;
 drop table t1;
 
 #
-# Testing that maximim possible key length is 1332 bytes
+# Testing that maximum possible key length is 4005 bytes
+# Limit raised to 1001 UTF-32 chars by fix for Bug#4541. See myisam.test.
 #
-create table t1 (a varchar(250) character set utf32 primary key);
+create table t1 (a varchar(1001) character set utf32 primary key);
 show create table t1;
 drop table t1;
 --error ER_TOO_LONG_KEY
-create table t1 (a varchar(334) character set utf32 primary key);
+create table t1 (a varchar(1002) character set utf32 primary key);
 
 #
 # Testing mi_check with long key values
diff -Nrup a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test
--- a/mysql-test/t/ctype_utf8.test	2007-11-15 14:41:39 +01:00
+++ b/mysql-test/t/ctype_utf8.test	2007-12-27 20:44:44 +01:00
@@ -162,9 +162,14 @@ drop table t1;
 #
 # Bug 2699
 # UTF8 breaks primary keys for cols > 333 characters
+# UTF-8 characters may use up to 4 bytes since MySQL 6.0.
+# Thus the limit became 250.
+# Limit raised to 1001 UTF-8 chars by fix for Bug#4541. See myisam.test.
 #
 --error 1071
-create table t1 (a text character set utf8, primary key(a(360)));
+create table t1 (a text character set utf8, primary key(a(1002)));
+create table t1 (a text character set utf8, primary key(a(1001)));
+drop table t1;
 
 
 #
diff -Nrup a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test
--- a/mysql-test/t/myisam.test	2007-12-13 13:56:18 +01:00
+++ b/mysql-test/t/myisam.test	2007-12-27 20:44:44 +01:00
@@ -334,13 +334,36 @@ drop table t1;
 
 #
 # Test of creating table with too long key
+# Limit raised to 4005 bytes by fix for Bug#4541. See myisam.test.
 #
-
 --error 1071
-CREATE TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300), KEY t1 (a, b, c, d, e));
-CREATE TABLE t1 (a varchar(300), b varchar(300), c varchar(300), d varchar(300), e varchar(300));
+CREATE TABLE t1 (
+  a varchar(255), b varchar(255), c varchar(255), d varchar(255),
+  e varchar(255), f varchar(255), g varchar(255), h varchar(255),
+  i varchar(255), j varchar(255), k varchar(255), l varchar(255),
+  m varchar(255), n varchar(255), o varchar(255), p varchar(181),
+  KEY t1 (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p));
+CREATE TABLE t1 (
+  a varchar(255), b varchar(255), c varchar(255), d varchar(255),
+  e varchar(255), f varchar(255), g varchar(255), h varchar(255),
+  i varchar(255), j varchar(255), k varchar(255), l varchar(255),
+  m varchar(255), n varchar(255), o varchar(255), p varchar(181));
 --error 1071
-ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e);
+ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p);
+DROP TABLE t1;
+CREATE TABLE t1 (
+  a varchar(255), b varchar(255), c varchar(255), d varchar(255),
+  e varchar(255), f varchar(255), g varchar(255), h varchar(255),
+  i varchar(255), j varchar(255), k varchar(255), l varchar(255),
+  m varchar(255), n varchar(255), o varchar(255), p varchar(180),
+  KEY t1 (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p));
+DROP TABLE t1;
+CREATE TABLE t1 (
+  a varchar(255), b varchar(255), c varchar(255), d varchar(255),
+  e varchar(255), f varchar(255), g varchar(255), h varchar(255),
+  i varchar(255), j varchar(255), k varchar(255), l varchar(255),
+  m varchar(255), n varchar(255), o varchar(255), p varchar(180));
+ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p);
 DROP TABLE t1;
 
 #
@@ -992,6 +1015,57 @@ SELECT DISTINCT length(c1), c1 FROM t1 W
 SELECT DISTINCT COUNT(*) FROM t1 IGNORE INDEX (c1) WHERE c1 = '';
 SELECT DISTINCT length(c1), c1 FROM t1 IGNORE INDEX (c1) WHERE c1 = '';
 SELECT DISTINCT length(c1), c1 FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+#
+# Bug#4541 - "Specified key was too long; max key length is 1000 bytes"
+#            with utf8
+# Raised limit for max key length from 1000 to 4005 bytes.
+# This is maximum without changing other limits (e.g. MI_MAX_KEY_BLOCK_LENGTH)
+# Since MySQL 60. UTF-8 characters can take up to 4 bytes.
+# The limit is thus 1001 UTF-8 characters.
+#
+--error 1071
+CREATE TABLE t1 (
+  c1 VARCHAR(250) NOT NULL,
+  c2 VARCHAR(250) NOT NULL,
+  c3 VARCHAR(250) NOT NULL,
+  c4 VARCHAR(252) NOT NULL,
+  KEY i1 (c1, c2, c3, c4)) DEFAULT CHARSET=UTF8;
+CREATE TABLE t1 (
+  c1 VARCHAR(250) NOT NULL,
+  c2 VARCHAR(250) NOT NULL,
+  c3 VARCHAR(250) NOT NULL,
+  c4 VARCHAR(251) NOT NULL,
+  KEY i1 (c1, c2, c3, c4)) DEFAULT CHARSET=UTF8;
+INSERT INTO t1 VALUES
+  (REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+   CONCAT(REPEAT('M', 250), '1')),
+  (REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+   CONCAT(REPEAT('M', 250), '7')),
+  (REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+   CONCAT(REPEAT('M', 250), '5')),
+  (REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+   CONCAT(REPEAT('M', 250), '8')),
+  (REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+   CONCAT(REPEAT('M', 250), '6')),
+  (REPEAT('0', 250), REPEAT('0', 250), REPEAT('0', 250),
+   CONCAT(REPEAT('M', 250), '5'));
+CHECK TABLE t1 EXTENDED;
+query_vertical OPTIMIZE TABLE t1;
+query_vertical SHOW CREATE TABLE t1;
+--replace_column 6 # 7 # 8 # 9 # 12 # 13 # 14 #
+query_vertical SHOW TABLE STATUS LIKE 't1';
+query_vertical SHOW INDEX FROM t1;
+query_vertical EXPLAIN SELECT SUBSTR(c4, 240) FROM t1
+                         ORDER BY c1, c2, c3, c4;
+SELECT SUBSTR(c4, 240) FROM t1 ORDER BY c1, c2, c3, c4;
+DELETE FROM t1 WHERE c4 LIKE '%5' LIMIT 1;
+UPDATE t1 SET c4 = CONCAT(REPEAT('M', 250), '9') WHERE c4 LIKE '%1';
+SELECT SUBSTR(c4, 240) FROM t1 ORDER BY c1, c2, c3, c4;
+CHECK TABLE t1 EXTENDED;
+REPAIR TABLE t1 EXTENDED;
+CHECK TABLE t1 EXTENDED;
 DROP TABLE t1;
 
 --echo End of 4.1 tests
diff -Nrup a/mysql-test/t/type_blob.test b/mysql-test/t/type_blob.test
--- a/mysql-test/t/type_blob.test	2007-11-23 10:53:27 +01:00
+++ b/mysql-test/t/type_blob.test	2007-12-27 20:44:44 +01:00
@@ -131,9 +131,15 @@ select c,count(*) from t1 group by c;
 select d,count(*) from t1 group by d;
 drop table t1;
 
+#
+# Limit raised to 4005 bytes by fix for Bug#4541. See myisam.test.
+#
 -- error 1071
-create table t1 (a text, unique (a(2100))); # should give an error
-create table t1 (a text, key (a(2100)));    # key is auto-truncated
+create table t1 (a text, unique (a(4006))); # should give an error
+create table t1 (a text, unique (a(4005))); # should give an error
+show create table t1;
+drop table t1;
+create table t1 (a text, key (a(4006)));    # key is auto-truncated
 show create table t1;
 drop table t1;