List:Commits« Previous MessageNext Message »
From:Ingo Struewing Date:December 27 2007 7:43pm
Subject:bk commit into 5.1 tree (istruewing:1.2650) BUG#4541
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 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:43:47+01:00, istruewing@stripped +10 -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 1335 UTF-8 characters. This is the maximum raise possible
  without changing other parameters. Patch for MySQL 5.1.
  
  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:43:46+01:00, istruewing@stripped +15 -2
    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_utf8.result@stripped, 2007-12-27 20:43:46+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:43:46+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:43:46+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:43:46+01:00, istruewing@stripped +218 -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:43:46+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_utf8.test@stripped, 2007-12-27 20:43:46+01:00, istruewing@stripped +4 -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:43:46+01:00, istruewing@stripped +80 -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:43:46+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.

  sql/unireg.h@stripped, 2007-12-27 20:43:46+01:00, istruewing@stripped +1 -1
    Bug#4541 - "Specified key was too long;
                max key length is 1000 bytes" with utf8
    Raised MAX_KEY_LENGTH to 4005.

diff -Nrup a/include/myisam.h b/include/myisam.h
--- a/include/myisam.h	2007-08-13 15:11:09 +02:00
+++ b/include/myisam.h	2007-12-27 20:43:46 +01:00
@@ -49,15 +49,28 @@ extern "C" {
 #define MI_MAX_KEY                  MAX_INDEXES         /* Max allowed keys */
 #endif
 
-#define MI_MAX_POSSIBLE_KEY_BUFF    (1024+6+6)      /* For myisam_chk */
 /*
   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           1000            /* Max length in bytes */
 #define MI_MAX_KEY_SEG              16              /* Max segments for key */
+  /*
+    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_utf8.result b/mysql-test/r/ctype_utf8.result
--- a/mysql-test/r/ctype_utf8.result	2007-11-06 13:03:53 +01:00
+++ b/mysql-test/r/ctype_utf8.result	2007-12-27 20:43:46 +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 1000 bytes
+create table t1 (a text character set utf8, primary key(a(1336)));
+ERROR 42000: Specified key was too long; max key length is 4005 bytes
+create table t1 (a text character set utf8, primary key(a(1335)));
+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 12:57:02 +01:00
+++ b/mysql-test/r/innodb.result	2007-12-27 20:43:46 +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-03-01 14:41:26 +01:00
+++ b/mysql-test/r/mix2_myisam.result	2007-12-27 20:43:46 +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 1000 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 12:57:02 +01:00
+++ b/mysql-test/r/myisam.result	2007-12-27 20:43:46 +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(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255), KEY t1 (a, b, c, d, e));
-ERROR 42000: Specified key was too long; max key length is 1000 bytes
-CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255));
-ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e);
-ERROR 42000: Specified key was too long; max key length is 1000 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);
@@ -1075,6 +1098,186 @@ length(c1)	c1
 2		A
 2	 B
 DROP TABLE t1;
+CREATE TABLE t1 (
+c1 VARCHAR(255) NOT NULL,
+c2 VARCHAR(255) NOT NULL,
+c3 VARCHAR(255) NOT NULL,
+c4 VARCHAR(255) NOT NULL,
+c5 VARCHAR(255) NOT NULL,
+c6 VARCHAR(61),
+KEY i1 (c1, c2, c3, c4, c5, c6)) DEFAULT CHARSET=UTF8;
+ERROR 42000: Specified key was too long; max key length is 4005 bytes
+CREATE TABLE t1 (
+c1 VARCHAR(255) NOT NULL,
+c2 VARCHAR(255) NOT NULL,
+c3 VARCHAR(255) NOT NULL,
+c4 VARCHAR(255) NOT NULL,
+c5 VARCHAR(255) NOT NULL,
+c6 VARCHAR(60),
+KEY i1 (c1, c2, c3, c4, c5, c6)) DEFAULT CHARSET=UTF8;
+INSERT INTO t1 VALUES
+(REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '1')),
+(REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '7')),
+(REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '5')),
+(REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '8')),
+(REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '6')),
+(REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '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(255) NOT NULL,
+  `c2` varchar(255) NOT NULL,
+  `c3` varchar(255) NOT NULL,
+  `c4` varchar(255) NOT NULL,
+  `c5` varchar(255) NOT NULL,
+  `c6` varchar(60) DEFAULT NULL,
+  KEY `i1` (`c1`,`c2`,`c3`,`c4`,`c5`,`c6`)
+) 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	
+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	
+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	
+Table	t1
+Non_unique	1
+Key_name	i1
+Seq_in_index	4
+Column_name	c4
+Collation	A
+Cardinality	1
+Sub_part	NULL
+Packed	NULL
+Null	
+Index_type	BTREE
+Comment	
+Table	t1
+Non_unique	1
+Key_name	i1
+Seq_in_index	5
+Column_name	c5
+Collation	A
+Cardinality	1
+Sub_part	NULL
+Packed	NULL
+Null	
+Index_type	BTREE
+Comment	
+Table	t1
+Non_unique	1
+Key_name	i1
+Seq_in_index	6
+Column_name	c6
+Collation	A
+Cardinality	6
+Sub_part	NULL
+Packed	NULL
+Null	YES
+Index_type	BTREE
+Comment	
+EXPLAIN SELECT SUBSTR(c6, 50) FROM t1
+ORDER BY c1, c2, c3, c4, c5, c6;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	i1
+key_len	4018
+ref	NULL
+rows	6
+Extra	Using index
+SELECT SUBSTR(c6, 50) FROM t1 ORDER BY c1, c2, c3, c4, c5, c6;
+SUBSTR(c6, 50)
+MMMMMMMMMM1
+MMMMMMMMMM5
+MMMMMMMMMM5
+MMMMMMMMMM6
+MMMMMMMMMM7
+MMMMMMMMMM8
+DELETE FROM t1 WHERE c6 LIKE '%5' LIMIT 1;
+UPDATE t1 SET c6 = CONCAT(REPEAT('M', 59), '9') WHERE c6 LIKE '%1';
+SELECT SUBSTR(c6, 50) FROM t1 ORDER BY c1, c2, c3, c4, c5, c6;
+SUBSTR(c6, 50)
+MMMMMMMMMM5
+MMMMMMMMMM6
+MMMMMMMMMM7
+MMMMMMMMMM8
+MMMMMMMMMM9
+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;
@@ -1650,7 +1853,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 1000 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:
@@ -1849,35 +2052,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 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` varchar(2048) DEFAULT NULL,
-  KEY `a` (`a`(1000))
+  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 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` varchar(2048) DEFAULT NULL,
-  KEY `a` (`a`(1000)) KEY_BLOCK_SIZE=4096
+  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 1000 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`(1000)) KEY_BLOCK_SIZE=4096
+  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;
@@ -1886,7 +2083,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`(1000)) KEY_BLOCK_SIZE=4096
+  KEY `b` (`b`) KEY_BLOCK_SIZE=9216
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=2048
 alter table t1 add c int, add key (c);
 show create table t1;
@@ -1896,7 +2093,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`(1000)) KEY_BLOCK_SIZE=4096,
+  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;
@@ -1909,33 +2106,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`(1000)) KEY_BLOCK_SIZE=4096,
+  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 1000 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`(1000))
+  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 1000 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`(1000))
+  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;
@@ -1957,13 +2150,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 1000 bytes
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` varchar(2048) DEFAULT NULL,
-  KEY `a` (`a`(1000)) KEY_BLOCK_SIZE=4096
+  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-11-23 10:53:27 +01:00
+++ b/mysql-test/r/type_blob.result	2007-12-27 20:43:46 +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 1000 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 1000 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`(1000))
+  KEY `a` (`a`(4005))
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 drop table t1;
 CREATE TABLE t1 (
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-06 13:03:54 +01:00
+++ b/mysql-test/t/ctype_utf8.test	2007-12-27 20:43:46 +01:00
@@ -162,9 +162,12 @@ drop table t1;
 #
 # Bug 2699
 # UTF8 breaks primary keys for cols > 333 characters
+# Limit raised to 1335 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(1336)));
+create table t1 (a text character set utf8, primary key(a(1335)));
+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 12:57:02 +01:00
+++ b/mysql-test/t/myisam.test	2007-12-27 20:43:46 +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(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255), KEY t1 (a, b, c, d, e));
-CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255));
+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;
 
 #
@@ -990,6 +1013,59 @@ 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)
+#
+--error 1071
+CREATE TABLE t1 (
+  c1 VARCHAR(255) NOT NULL,
+  c2 VARCHAR(255) NOT NULL,
+  c3 VARCHAR(255) NOT NULL,
+  c4 VARCHAR(255) NOT NULL,
+  c5 VARCHAR(255) NOT NULL,
+  c6 VARCHAR(61),
+  KEY i1 (c1, c2, c3, c4, c5, c6)) DEFAULT CHARSET=UTF8;
+CREATE TABLE t1 (
+  c1 VARCHAR(255) NOT NULL,
+  c2 VARCHAR(255) NOT NULL,
+  c3 VARCHAR(255) NOT NULL,
+  c4 VARCHAR(255) NOT NULL,
+  c5 VARCHAR(255) NOT NULL,
+  c6 VARCHAR(60),
+  KEY i1 (c1, c2, c3, c4, c5, c6)) DEFAULT CHARSET=UTF8;
+INSERT INTO t1 VALUES
+  (REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+   REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '1')),
+  (REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+   REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '7')),
+  (REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+   REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '5')),
+  (REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+   REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '8')),
+  (REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+   REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '6')),
+  (REPEAT('0', 255), REPEAT('0', 255), REPEAT('0', 255),
+   REPEAT('0', 255), REPEAT('0', 255), CONCAT(REPEAT('M', 59), '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(c6, 50) FROM t1
+                         ORDER BY c1, c2, c3, c4, c5, c6;
+SELECT SUBSTR(c6, 50) FROM t1 ORDER BY c1, c2, c3, c4, c5, c6;
+DELETE FROM t1 WHERE c6 LIKE '%5' LIMIT 1;
+UPDATE t1 SET c6 = CONCAT(REPEAT('M', 59), '9') WHERE c6 LIKE '%1';
+SELECT SUBSTR(c6, 50) FROM t1 ORDER BY c1, c2, c3, c4, c5, c6;
+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:43:46 +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;
 
diff -Nrup a/sql/unireg.h b/sql/unireg.h
--- a/sql/unireg.h	2007-09-10 13:16:01 +02:00
+++ b/sql/unireg.h	2007-12-27 20:43:46 +01:00
@@ -52,7 +52,7 @@
 #define MAX_SYS_VAR_LENGTH 32
 #define MAX_KEY MAX_INDEXES                     /* Max used keys */
 #define MAX_REF_PARTS 16			/* Max parts used as ref */
-#define MAX_KEY_LENGTH 3072			/* max possible key */
+#define MAX_KEY_LENGTH 4005 /* myisamdef.h */   /* max possible key */
 #if SIZEOF_OFF_T > 4
 #define MAX_REFLENGTH 8				/* Max length for record ref */
 #else
Thread
bk commit into 5.1 tree (istruewing:1.2650) BUG#4541Ingo Struewing27 Dec