#At file:///export/home/x/mysql-trunk-bug12585718/ based on revid:tor.didriksen@stripped
3142 Jon Olav Hauglid 2011-06-01
Bug#12585718 REPLICATION BREAKS WITH LARGE INDEX PREFIX ON UNIQUE INDEX
Tentative patch for discussion purposes.
Before this patch, creating an index with a prefix longer than the
maximum supported length only gave a warning for non-unique indexes
but an error for unique indexes.
This patch changes the behavior for non-unique indexes so
an ER_TOO_LONG_KEY error is reported in this case as well.
Test case added to create.test.
modified:
mysql-test/include/ctype_utf8mb4.inc
mysql-test/include/mix1.inc
mysql-test/r/create.result
mysql-test/r/ctype_utf8mb4.result
mysql-test/r/ctype_utf8mb4_innodb.result
mysql-test/r/ctype_utf8mb4_myisam.result
mysql-test/r/type_blob.result
mysql-test/suite/innodb/r/innodb.result
mysql-test/suite/innodb/r/innodb_index_large_prefix.result
mysql-test/suite/innodb/r/innodb_mysql.result
mysql-test/suite/innodb/r/innodb_prefix_index_liftedlimit.result
mysql-test/suite/innodb/t/innodb.test
mysql-test/suite/innodb/t/innodb_index_large_prefix.test
mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test
mysql-test/t/create.test
mysql-test/t/ctype_utf8mb4.test
mysql-test/t/type_blob.test
sql/sql_table.cc
=== modified file 'mysql-test/include/ctype_utf8mb4.inc'
--- a/mysql-test/include/ctype_utf8mb4.inc 2010-03-05 08:17:19 +0000
+++ b/mysql-test/include/ctype_utf8mb4.inc 2011-06-01 14:57:29 +0000
@@ -1636,7 +1636,7 @@ eval CREATE TABLE t1 (
clipid INT NOT NULL,
Tape TINYTEXT,
PRIMARY KEY (clipid),
- KEY tape(Tape(255))
+ KEY tape(Tape(191))
) CHARACTER SET=utf8mb4 ENGINE $engine;
ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid;
SHOW CREATE TABLE t1;
=== modified file 'mysql-test/include/mix1.inc'
--- a/mysql-test/include/mix1.inc 2011-03-30 13:33:29 +0000
+++ b/mysql-test/include/mix1.inc 2011-06-01 14:57:29 +0000
@@ -941,6 +941,7 @@ DROP TABLE t1;
#
create table t1(a text) engine=innodb default charset=utf8;
insert into t1 values('aaa');
+--error ER_TOO_LONG_KEY
alter table t1 add index(a(1024));
show create table t1;
drop table t1;
=== modified file 'mysql-test/r/create.result'
--- a/mysql-test/r/create.result 2011-02-21 02:57:30 +0000
+++ b/mysql-test/r/create.result 2011-06-01 14:57:29 +0000
@@ -2451,3 +2451,17 @@ create temporary table t1 select f();
ERROR HY000: Can't update table 't2' while 't1' is being created.
drop view t2;
drop function f;
+#
+# BUG#12585718 REPLICATION BREAKS WITH LARGE INDEX PREFIX ON UNIQUE INDEX
+#
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1(col1 TEXT, UNIQUE INDEX(col1(1001)));
+ERROR 42000: Specified key was too long; max key length is 1000 bytes
+CREATE TABLE t1(col1 VARCHAR(1001), UNIQUE INDEX(col1));
+ERROR 42000: Specified key was too long; max key length is 1000 bytes
+CREATE TABLE t1(col1 TEXT, INDEX(col1(1001)));
+ERROR 42000: Specified key was too long; max key length is 1000 bytes
+CREATE TABLE t1(col1 VARCHAR(1001), INDEX(col1));
+Warnings:
+Warning 1071 Specified key was too long; max key length is 1000 bytes
+DROP TABLE t1;
=== modified file 'mysql-test/r/ctype_utf8mb4.result'
--- a/mysql-test/r/ctype_utf8mb4.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/ctype_utf8mb4.result 2011-06-01 14:57:29 +0000
@@ -2187,10 +2187,8 @@ CREATE TABLE t1 (
clipid INT NOT NULL,
Tape TINYTEXT,
PRIMARY KEY (clipid),
-KEY tape(Tape(255))
+KEY tape(Tape(250))
) CHARACTER SET=utf8mb4;
-Warnings:
-Warning 1071 Specified key was too long; max key length is 1000 bytes
ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid;
SHOW CREATE TABLE t1;
Table Create Table
=== modified file 'mysql-test/r/ctype_utf8mb4_innodb.result'
--- a/mysql-test/r/ctype_utf8mb4_innodb.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/ctype_utf8mb4_innodb.result 2011-06-01 14:57:29 +0000
@@ -2155,10 +2155,8 @@ CREATE TABLE t1 (
clipid INT NOT NULL,
Tape TINYTEXT,
PRIMARY KEY (clipid),
-KEY tape(Tape(255))
+KEY tape(Tape(191))
) CHARACTER SET=utf8mb4 ENGINE InnoDB;
-Warnings:
-Warning 1071 Specified key was too long; max key length is 767 bytes
ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid;
SHOW CREATE TABLE t1;
Table Create Table
=== modified file 'mysql-test/r/ctype_utf8mb4_myisam.result'
--- a/mysql-test/r/ctype_utf8mb4_myisam.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/ctype_utf8mb4_myisam.result 2011-06-01 14:57:29 +0000
@@ -2155,10 +2155,8 @@ CREATE TABLE t1 (
clipid INT NOT NULL,
Tape TINYTEXT,
PRIMARY KEY (clipid),
-KEY tape(Tape(255))
+KEY tape(Tape(191))
) CHARACTER SET=utf8mb4 ENGINE MyISAM;
-Warnings:
-Warning 1071 Specified key was too long; max key length is 1000 bytes
ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid;
SHOW CREATE TABLE t1;
Table Create Table
@@ -2167,7 +2165,7 @@ t1 CREATE TABLE `t1` (
`mos` tinyint(4) DEFAULT '0',
`Tape` tinytext,
PRIMARY KEY (`clipid`),
- KEY `tape` (`Tape`(250))
+ KEY `tape` (`Tape`(191))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
DROP TABLE t1;
#
=== modified file 'mysql-test/r/type_blob.result'
--- a/mysql-test/r/type_blob.result 2010-11-01 09:52:05 +0000
+++ b/mysql-test/r/type_blob.result 2011-06-01 14:57:29 +0000
@@ -359,15 +359,7 @@ 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)));
-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` text,
- KEY `a` (`a`(1000))
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-drop table t1;
+ERROR 42000: Specified key was too long; max key length is 1000 bytes
CREATE TABLE t1 (
t1_id bigint(21) NOT NULL auto_increment,
_field_72 varchar(128) DEFAULT '' NOT NULL,
=== modified file 'mysql-test/suite/innodb/r/innodb.result'
--- a/mysql-test/suite/innodb/r/innodb.result 2011-05-19 12:43:26 +0000
+++ b/mysql-test/suite/innodb/r/innodb.result 2011-06-01 14:57:29 +0000
@@ -2488,21 +2488,13 @@ create table t2 (col1 varbinary(768), in
character set = latin1 engine = innodb;
Warnings:
Warning 1071 Specified key was too long; max key length is 767 bytes
-create table t3 (col1 text, index(col1(768)))
-character set = latin1 engine = innodb;
-Warnings:
-Warning 1071 Specified key was too long; max key length is 767 bytes
-create table t4 (col1 blob, index(col1(768)))
-character set = latin1 engine = innodb;
-Warnings:
-Warning 1071 Specified key was too long; max key length is 767 bytes
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`col1` varchar(768) DEFAULT NULL,
KEY `col1` (`col1`(767))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
-drop table t1, t2, t3, t4;
+drop table t1, t2;
create table t1 (col1 varchar(768) primary key)
character set = latin1 engine = innodb;
ERROR 42000: Specified key was too long; max key length is 767 bytes
@@ -2515,6 +2507,12 @@ ERROR 42000: Specified key was too long;
create table t4 (col1 blob, primary key(col1(768)))
character set = latin1 engine = innodb;
ERROR 42000: Specified key was too long; max key length is 767 bytes
+create table t3 (col1 text, index(col1(768)))
+character set = latin1 engine = innodb;
+ERROR 42000: Specified key was too long; max key length is 767 bytes
+create table t4 (col1 blob, index(col1(768)))
+character set = latin1 engine = innodb;
+ERROR 42000: Specified key was too long; max key length is 767 bytes
CREATE TABLE t1
(
id INT PRIMARY KEY
=== modified file 'mysql-test/suite/innodb/r/innodb_index_large_prefix.result'
--- a/mysql-test/suite/innodb/r/innodb_index_large_prefix.result 2011-05-31 09:30:59 +0000
+++ b/mysql-test/suite/innodb/r/innodb_index_large_prefix.result 2011-06-01 14:57:29 +0000
@@ -162,15 +162,12 @@ rollback;
drop table worklog5743;
create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb;
create index idx on worklog5743(a(3073));
-Warnings:
-Warning 1071 Specified key was too long; max key length is 3072 bytes
-Warning 1071 Specified key was too long; max key length is 3072 bytes
+ERROR 42000: Specified key was too long; max key length is 3072 bytes
create index idx2 on worklog5743(a(3072));
show create table worklog5743;
Table Create Table
worklog5743 CREATE TABLE `worklog5743` (
`a` text NOT NULL,
- KEY `idx` (`a`(3072)),
KEY `idx2` (`a`(3072))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
drop table worklog5743;
=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result 2011-05-26 16:23:52 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2011-06-01 14:57:29 +0000
@@ -1128,14 +1128,11 @@ DROP TABLE t1;
create table t1(a text) engine=innodb default charset=utf8;
insert into t1 values('aaa');
alter table t1 add index(a(1024));
-Warnings:
-Warning 1071 Specified key was too long; max key length is 767 bytes
-Warning 1071 Specified key was too long; max key length is 767 bytes
+ERROR 42000: Specified key was too long; max key length is 767 bytes
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `a` text,
- KEY `a` (`a`(255))
+ `a` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8
drop table t1;
CREATE TABLE t1 (
=== modified file 'mysql-test/suite/innodb/r/innodb_prefix_index_liftedlimit.result'
--- a/mysql-test/suite/innodb/r/innodb_prefix_index_liftedlimit.result 2011-06-01 09:55:08 +0000
+++ b/mysql-test/suite/innodb/r/innodb_prefix_index_liftedlimit.result 2011-06-01 14:57:29 +0000
@@ -1313,9 +1313,7 @@ WHERE col_1_text = REPEAT("c", 4000) AND
col_1_text = REPEAT("c", 4000)
DROP INDEX prefix_idx ON worklog5743;
CREATE INDEX prefix_idx ON worklog5743(col_1_text (4000));
-Warnings:
-Warning 1071 Specified key was too long; max key length is 3072 bytes
-Warning 1071 Specified key was too long; max key length is 3072 bytes
+ERROR 42000: Specified key was too long; max key length is 3072 bytes
INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743;
col_1_text = REPEAT("a", 4000)
=== modified file 'mysql-test/suite/innodb/t/innodb.test'
--- a/mysql-test/suite/innodb/t/innodb.test 2011-05-19 12:43:26 +0000
+++ b/mysql-test/suite/innodb/t/innodb.test 2011-06-01 14:57:29 +0000
@@ -1544,28 +1544,30 @@ create table t1 (col1 varchar(768), inde
character set = latin1 engine = innodb;
create table t2 (col1 varbinary(768), index(col1))
character set = latin1 engine = innodb;
-create table t3 (col1 text, index(col1(768)))
- character set = latin1 engine = innodb;
-create table t4 (col1 blob, index(col1(768)))
- character set = latin1 engine = innodb;
show create table t1;
-drop table t1, t2, t3, t4;
+drop table t1, t2;
# these should be refused
---error 1071
+--error ER_TOO_LONG_KEY
create table t1 (col1 varchar(768) primary key)
character set = latin1 engine = innodb;
---error 1071
+--error ER_TOO_LONG_KEY
create table t2 (col1 varbinary(768) primary key)
character set = latin1 engine = innodb;
---error 1071
+--error ER_TOO_LONG_KEY
create table t3 (col1 text, primary key(col1(768)))
character set = latin1 engine = innodb;
---error 1071
+--error ER_TOO_LONG_KEY
create table t4 (col1 blob, primary key(col1(768)))
character set = latin1 engine = innodb;
+--error ER_TOO_LONG_KEY
+create table t3 (col1 text, index(col1(768)))
+ character set = latin1 engine = innodb;
+--error ER_TOO_LONG_KEY
+create table t4 (col1 blob, index(col1(768)))
+ character set = latin1 engine = innodb;
#
# Test improved foreign key error messages (bug #3443)
=== modified file 'mysql-test/suite/innodb/t/innodb_index_large_prefix.test'
--- a/mysql-test/suite/innodb/t/innodb_index_large_prefix.test 2011-05-31 09:12:32 +0000
+++ b/mysql-test/suite/innodb/t/innodb_index_large_prefix.test 2011-06-01 14:57:29 +0000
@@ -246,7 +246,8 @@ drop table worklog5743;
# We have a limit of 3072 bytes for Barracuda table
create table worklog5743(a TEXT not null) ROW_FORMAT=DYNAMIC, engine = innodb;
-# Length exceeds maximum supported key length, will auto-truncated to 3072
+# Length exceeds maximum supported key length, error reported
+--error ER_TOO_LONG_KEY
create index idx on worklog5743(a(3073));
create index idx2 on worklog5743(a(3072));
=== modified file 'mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test'
--- a/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test 2011-06-01 09:55:08 +0000
+++ b/mysql-test/suite/innodb/t/innodb_prefix_index_liftedlimit.test 2011-06-01 14:57:29 +0000
@@ -1277,6 +1277,7 @@ DROP INDEX prefix_idx ON worklog5743;
# If "innodb_large_prefix" is turned on, than the index prefix larger than 3072
# will be truncated to 3072. If the table is REDUNDANT and COMPACT, which does
# not support prefix > 767, the create index will be rejected.
+--error ER_TOO_LONG_KEY
CREATE INDEX prefix_idx ON worklog5743(col_1_text (4000));
INSERT INTO worklog5743 VALUES(REPEAT("a", 4000),REPEAT("o", 4000));
SELECT col_1_text = REPEAT("a", 4000) FROM worklog5743;
=== modified file 'mysql-test/t/create.test'
--- a/mysql-test/t/create.test 2011-02-21 02:57:30 +0000
+++ b/mysql-test/t/create.test 2011-06-01 14:57:29 +0000
@@ -2083,3 +2083,26 @@ create temporary table t1 select f();
drop view t2;
drop function f;
+
+
+--echo #
+--echo # BUG#12585718 REPLICATION BREAKS WITH LARGE INDEX PREFIX ON UNIQUE INDEX
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+# These used to give an error
+--error ER_TOO_LONG_KEY
+CREATE TABLE t1(col1 TEXT, UNIQUE INDEX(col1(1001)));
+--error ER_TOO_LONG_KEY
+CREATE TABLE t1(col1 VARCHAR(1001), UNIQUE INDEX(col1));
+
+# This used to just give a warning, but should now give error
+--error ER_TOO_LONG_KEY
+CREATE TABLE t1(col1 TEXT, INDEX(col1(1001)));
+
+# This should still just give a warning
+CREATE TABLE t1(col1 VARCHAR(1001), INDEX(col1));
+DROP TABLE t1;
=== modified file 'mysql-test/t/ctype_utf8mb4.test'
--- a/mysql-test/t/ctype_utf8mb4.test 2010-09-28 15:29:26 +0000
+++ b/mysql-test/t/ctype_utf8mb4.test 2011-06-01 14:57:29 +0000
@@ -1544,7 +1544,7 @@ CREATE TABLE t1 (
clipid INT NOT NULL,
Tape TINYTEXT,
PRIMARY KEY (clipid),
- KEY tape(Tape(255))
+ KEY tape(Tape(250))
) CHARACTER SET=utf8mb4;
ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid;
SHOW CREATE TABLE t1;
=== modified file 'mysql-test/t/type_blob.test'
--- a/mysql-test/t/type_blob.test 2010-11-01 09:52:05 +0000
+++ b/mysql-test/t/type_blob.test 2011-06-01 14:57:29 +0000
@@ -131,11 +131,10 @@ select c,count(*) from t1 group by c;
select d,count(*) from t1 group by d;
drop table t1;
--- error 1071
+--error ER_TOO_LONG_KEY
create table t1 (a text, unique (a(2100))); # should give an error
-create table t1 (a text, key (a(2100))); # key is auto-truncated
-show create table t1;
-drop table t1;
+--error ER_TOO_LONG_KEY
+create table t1 (a text, key (a(2100))); # also gives error
#
# Test of join with blobs and min
=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc 2011-06-01 09:11:28 +0000
+++ b/sql/sql_table.cc 2011-06-01 14:57:29 +0000
@@ -3672,22 +3672,8 @@ mysql_prepare_create_table(THD *thd, HA_
length > file->max_key_part_length())
{
length=min(max_key_length, file->max_key_part_length());
- if (key->type == Key::MULTIPLE)
- {
- /* not a critical problem */
- char warn_buff[MYSQL_ERRMSG_SIZE];
- my_snprintf(warn_buff, sizeof(warn_buff), ER(ER_TOO_LONG_KEY),
- length);
- push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
- ER_TOO_LONG_KEY, warn_buff);
- /* Align key length to multibyte char boundary */
- length-= length % sql_field->charset->mbmaxlen;
- }
- else
- {
- my_error(ER_TOO_LONG_KEY,MYF(0),length);
- DBUG_RETURN(TRUE);
- }
+ my_error(ER_TOO_LONG_KEY,MYF(0),length);
+ DBUG_RETURN(TRUE);
}
}
// Catch invalid use of partial keys
Attachment: [text/bzr-bundle] bzr/jon.hauglid@oracle.com-20110601145729-29ceihxpo2lim3j0.bundle
