Hi, Sachin! Could you please explain what you store in the frm file and how you modify keyinfo/keypart structures in memory?
Needs to be tested with partitioned tables. Particularly with MDEV-14005. Tests were great and thourough, good job. It'd be useful to have at least some tests for innodb too, though. See other comments below. On Oct 08, Sachin Setiya wrote: > diff --git a/mysql-test/main/long_unique.result > b/mysql-test/main/long_unique.result > new file mode 100644 > index 00000000000..1a06c230c72 > --- /dev/null > +++ b/mysql-test/main/long_unique.result > @@ -0,0 +1,1391 @@ > +#Structure of tests > +#First we will check all option for > +#table containing single unique column > +#table containing keys like unique(a,b,c,d) etc > +#then table containing 2 blob unique etc > +set @allowed_packet= @@max_allowed_packet; > +#table with single long blob column; > +create table t1(a blob unique); > +insert into t1 > values(1),(2),(3),(56),('sachin'),('maria'),(123456789034567891),(null),(null),(123456789034567890); > +#table structure; > +desc t1; > +Field Type Null Key Default Extra > +a blob YES UNI NULL > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + UNIQUE KEY `a` (`a`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table t1 > +Non_unique 0 > +Key_name a > +Seq_in_index 1 > +Column_name a > +Collation A > +Cardinality NULL > +Sub_part NULL > +Packed NULL > +Null YES > +Index_type HASH_INDEX I'm not sure about it. One cannot write UNIQUE (a) USING HASH_INDEX in CREATE TABLE, right? May be just report HASH here? (and fix the parser to behave accordingly) > +Comment > +Index_comment > + > +MyISAM file: DATADIR/test/t1 > +Record format: Packed > +Character set: latin1_swedish_ci (8) > +Data records: 10 Deleted blocks: 0 > +Recordlength: 20 > + > +table description: > +Key Start Len Index Type > +1 12 8 multip. ulonglong NULL good idea > +select * from information_schema.columns where table_schema = 'test' and > table_name = 't1'; > +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME > ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE > CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION > NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME > COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES > COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION > +def test t1 a 1 NULL YES blob 65535 65535 > NULL NULL NULL NULL NULL blob UNI > select,insert,update,references NEVER NULL > +select * from information_schema.statistics where table_schema = 'test' and > table_name = 't1'; > +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA > INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY > SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT > INDEX_COMMENT > +def test t1 0 test a 1 a A NULL > NULL NULL YES HASH_INDEX > +select * from information_schema.key_column_usage where table_schema= > 'test' and table_name= 't1'; > +CONSTRAINT_CATALOG def > +CONSTRAINT_SCHEMA test > +CONSTRAINT_NAME a > +TABLE_CATALOG def > +TABLE_SCHEMA test > +TABLE_NAME t1 > +COLUMN_NAME a > +ORDINAL_POSITION 1 > +POSITION_IN_UNIQUE_CONSTRAINT NULL > +REFERENCED_TABLE_SCHEMA NULL > +REFERENCED_TABLE_NAME NULL > +REFERENCED_COLUMN_NAME NULL > +# table select we should not be able to see db_row_hash_column; > +select * from t1; > +a > +1 > +2 > +3 > +56 > +sachin > +maria > +123456789034567891 > +NULL > +NULL > +123456789034567890 > +select db_row_hash_1 from t1; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list' > +#duplicate entry test; > +insert into t1 values(2); > +ERROR 23000: Duplicate entry '2' for key 'a' > +insert into t1 values('sachin'); > +ERROR 23000: Duplicate entry 'sachin' for key 'a' > +insert into t1 values(123456789034567891); > +ERROR 23000: Duplicate entry '123456789034567891' for key 'a' looks great :) > +select * from t1; > +a > +1 > +2 > +3 > +56 > +sachin > +maria > +123456789034567891 > +NULL > +NULL > +123456789034567890 > +insert into t1 values(11),(22),(33); > +insert into t1 values(12),(22); > +ERROR 23000: Duplicate entry '22' for key 'a' > +select * from t1; > +a > +1 > +2 > +3 > +56 > +sachin > +maria > +123456789034567891 > +NULL > +NULL > +123456789034567890 > +11 > +22 > +33 > +12 > +insert into t1 values(repeat('s',4000*10)),(repeat('s',4001*10)); > +insert into t1 values(repeat('m',4000*10)),(repeat('m',4000*10)); > +ERROR 23000: Duplicate entry > 'mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm' for key 'a' > +insert into t1 values(repeat('m',4001)),(repeat('m',4002)); > +truncate table t1; > +insert into t1 values(1),(2),(3),(4),(5),(8),(7); > + > +MyISAM file: DATADIR/test/t1 > +Record format: Packed > +Character set: latin1_swedish_ci (8) > +Data records: 7 Deleted blocks: 0 > +Recordlength: 20 > + > +table description: > +Key Start Len Index Type > +1 12 8 multip. ulonglong NULL > +#now some alter commands; > +alter table t1 add column b int; > +desc t1; > +Field Type Null Key Default Extra > +a blob YES UNI NULL > +b int(11) YES NULL > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` int(11) DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535)) I think there shouldn't be (65535) here > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +insert into t1 values(1,2); > +ERROR 23000: Duplicate entry '1' for key 'a' > +insert into t1 values(2,2); > +ERROR 23000: Duplicate entry '2' for key 'a' > +select db_row_hash_1 from t1; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list' > +#now try to change db_row_hash_1 column; > +alter table t1 drop column db_row_hash_1; > +ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists > +alter table t1 add column d int , add column e int , drop column > db_row_hash_1; > +ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists > +alter table t1 modify column db_row_hash_1 int ; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 add column a int , add column b int, modify column > db_row_hash_1 int ; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 change column db_row_hash_1 dsds int; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 add column asd int, change column db_row_hash_1 dsds int; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 drop column b , add column c int; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `c` int(11) DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +#now add some column with name db_row_hash; > +alter table t1 add column db_row_hash_1 int unique; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `c` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535)), > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +insert into t1 values(45,1,55),(46,1,55); > +ERROR 23000: Duplicate entry '55' for key 'db_row_hash_1' > +alter table t1 add column db_row_hash_2 int, add column db_row_hash_3 int; > +desc t1; > +Field Type Null Key Default Extra > +a blob YES UNI NULL > +c int(11) YES NULL > +db_row_hash_1 int(11) YES UNI NULL > +db_row_hash_2 int(11) YES NULL > +db_row_hash_3 int(11) YES NULL > +#this should also drop the unique index ; > +alter table t1 drop column a; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `c` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_3` int(11) DEFAULT NULL, > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL > NULL YES BTREE > +#add column with unique index on blob ; > +alter table t1 add column a blob unique; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `c` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_3` int(11) DEFAULT NULL, > + `a` blob DEFAULT NULL, > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), > + UNIQUE KEY `a` (`a`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +# try to change the blob unique column name; > +#this will change index to b tree; > +alter table t1 modify column a int ; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `c` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_3` int(11) DEFAULT NULL, > + `a` int(11) DEFAULT NULL, > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), > + UNIQUE KEY `a` (`a`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL > NULL YES BTREE > +t1 0 a 1 a A NULL NULL NULL YES > BTREE > +alter table t1 add column clm blob unique; > +#try changing the name ; > +alter table t1 change column clm clm_changed blob; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `c` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_3` int(11) DEFAULT NULL, > + `a` int(11) DEFAULT NULL, > + `clm_changed` blob DEFAULT NULL, > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), > + UNIQUE KEY `a` (`a`), > + UNIQUE KEY `clm` (`clm_changed`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL > NULL YES BTREE > +t1 0 a 1 a A NULL NULL NULL YES > BTREE > +t1 0 clm 1 clm_changed A NULL NULL NULL > YES HASH_INDEX > +#now drop the unique key; > +alter table t1 drop key clm; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `c` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_3` int(11) DEFAULT NULL, > + `a` int(11) DEFAULT NULL, > + `clm_changed` blob DEFAULT NULL, > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), > + UNIQUE KEY `a` (`a`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL > NULL YES BTREE > +t1 0 a 1 a A NULL NULL NULL YES > BTREE > +drop table t1; > +create table t1 (a TEXT CHARSET latin1 COLLATE latin1_german2_ci unique); > +desc t1; > +Field Type Null Key Default Extra > +a text YES UNI NULL > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL NULL NULL YES > HASH_INDEX > +insert into t1 values ('ae'); > +insert into t1 values ('AE'); > +ERROR 23000: Duplicate entry 'AE' for key 'a' > +insert into t1 values ('Ä'); this should've failed, shouldn't it? > +drop table t1; > +create table t1 (a int primary key, b blob unique); > +desc t1; > +Field Type Null Key Default Extra > +a int(11) NO PRI NULL > +b blob YES UNI NULL > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 PRIMARY 1 a A 0 NULL NULL > BTREE > +t1 0 b 1 b A NULL NULL NULL YES > HASH_INDEX > +insert into t1 values(1,1),(2,2),(3,3); > +insert into t1 values(1,1); > +ERROR 23000: Duplicate entry '1' for key 'b' > +insert into t1 values(7,1); > +ERROR 23000: Duplicate entry '1' for key 'b' > +drop table t1; > +#table with multiple long blob column and varchar text column ; > +create table t1(a blob unique, b int , c blob unique , d text unique , e > varchar(3000) unique); > +insert into t1 > values(1,2,3,4,5),(2,11,22,33,44),(3111,222,333,444,555),(5611,2222,3333,4444,5555), > +('sachin',341,'fdf','gfgfgfg','hghgr'),('maria',345,'frter','dasd','utyuty'), > +(123456789034567891,353534,53453453453456,64565464564564,45435345345345), > +(123456789034567890,43545,657567567567,78967657567567,657567567567567676); > +#table structure; > +desc t1; > +Field Type Null Key Default Extra > +a blob YES UNI NULL > +b int(11) YES NULL > +c blob YES UNI NULL > +d text YES UNI NULL > +e varchar(3000) YES UNI NULL > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` int(11) DEFAULT NULL, > + `c` blob DEFAULT NULL, > + `d` text DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + UNIQUE KEY `a` (`a`), > + UNIQUE KEY `c` (`c`), > + UNIQUE KEY `d` (`d`), > + UNIQUE KEY `e` (`e`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL NULL NULL YES > HASH_INDEX > +t1 0 c 1 c A NULL NULL NULL YES > HASH_INDEX > +t1 0 d 1 d A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 1 e A NULL NULL NULL YES > HASH_INDEX > + > +MyISAM file: DATADIR/test/t1 > +Record format: Packed > +Character set: latin1_swedish_ci (8) > +Data records: 8 Deleted blocks: 0 > +Recordlength: 3072 > + > +table description: > +Key Start Len Index Type > +1 3063 8 multip. ulonglong NULL > +2 3055 8 multip. ulonglong NULL > +3 3047 8 multip. ulonglong NULL > +4 3039 8 multip. ulonglong prefix NULL why "prefix" here? how is it different from others? > +select * from information_schema.columns where table_schema = 'test' and > table_name = 't1'; > +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME > ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE > CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION > NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME > COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES > COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION > +def test t1 a 1 NULL YES blob 65535 65535 > NULL NULL NULL NULL NULL blob UNI > select,insert,update,references NEVER NULL > +def test t1 b 2 NULL YES int NULL NULL > 10 0 NULL NULL NULL int(11) > select,insert,update,references NEVER NULL > +def test t1 c 3 NULL YES blob 65535 65535 > NULL NULL NULL NULL NULL blob UNI > select,insert,update,references NEVER NULL > +def test t1 d 4 NULL YES text 65535 65535 > NULL NULL NULL latin1 latin1_swedish_ci text UNI > select,insert,update,references NEVER NULL > +def test t1 e 5 NULL YES varchar 3000 3000 > NULL NULL NULL latin1 latin1_swedish_ci varchar(3000) UNI > select,insert,update,references NEVER NULL > +select * from information_schema.statistics where table_schema = 'test' and > table_name = 't1'; > +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA > INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY > SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT > INDEX_COMMENT > +def test t1 0 test a 1 a A NULL > NULL NULL YES HASH_INDEX > +def test t1 0 test c 1 c A NULL > NULL NULL YES HASH_INDEX > +def test t1 0 test d 1 d A NULL > NULL NULL YES HASH_INDEX > +def test t1 0 test e 1 e A NULL > NULL NULL YES HASH_INDEX > +select * from information_schema.key_column_usage where table_schema= 'test' > and table_name= 't1'; > +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG > TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION > POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME > REFERENCED_COLUMN_NAME > +def test a def test t1 a 1 NULL NULL > NULL NULL > +def test c def test t1 c 1 NULL NULL > NULL NULL > +def test d def test t1 d 1 NULL NULL > NULL NULL > +def test e def test t1 e 1 NULL NULL > NULL NULL > +#table select we should not be able to see db_row_hash_1 column; > +select * from t1; > +a b c d e > +1 2 3 4 5 > +2 11 22 33 44 > +3111 222 333 444 555 > +5611 2222 3333 4444 5555 > +sachin 341 fdf gfgfgfg hghgr > +maria 345 frter dasd utyuty > +123456789034567891 353534 53453453453456 64565464564564 45435345345345 > +123456789034567890 43545 657567567567 78967657567567 > 657567567567567676 > +select db_row_hash_1 from t1; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list' > +select db_row_hash_2 from t1; > +ERROR 42S22: Unknown column 'db_row_hash_2' in 'field list' > +select db_row_hash_3 from t1; > +ERROR 42S22: Unknown column 'db_row_hash_3' in 'field list' > +#duplicate entry test; > +insert into t1 values(21,2,3,42,51); > +ERROR 23000: Duplicate entry '3' for key 'c' > +insert into t1 values('sachin',null,null,null,null); > +ERROR 23000: Duplicate entry 'sachin' for key 'a' > +insert into t1 > values(1234567890345671890,4353451,6575675675617,789676575675617,657567567567567676); > +ERROR 23000: Duplicate entry '657567567567567676' for key 'e' > +select * from t1; > +a b c d e > +1 2 3 4 5 > +2 11 22 33 44 > +3111 222 333 444 555 > +5611 2222 3333 4444 5555 > +sachin 341 fdf gfgfgfg hghgr > +maria 345 frter dasd utyuty > +123456789034567891 353534 53453453453456 64565464564564 45435345345345 > +123456789034567890 43545 657567567567 78967657567567 > 657567567567567676 > +insert into t1 > values(repeat('s',4000*10),100,repeat('s',4000*10),repeat('s',4000*10), > +repeat('s',400)),(repeat('s',4001*10),1000,repeat('s',4001*10),repeat('s',4001*10), > +repeat('s',2995)); > +insert into t1 > values(repeat('m',4000*11),10,repeat('s',4000*11),repeat('s',4000*11),repeat('s',2995)); > +ERROR 23000: Duplicate entry > 'ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss' for key 'e' > +truncate table t1; > +insert into t1 > values(1,2,3,4,5),(2,11,22,33,44),(3111,222,333,444,555),(5611,2222,3333,4444,5555); > +#now some alter commands; > +alter table t1 add column f int; > +desc t1; > +Field Type Null Key Default Extra > +a blob YES UNI NULL > +b int(11) YES NULL > +c blob YES UNI NULL > +d text YES UNI NULL > +e varchar(3000) YES UNI NULL > +f int(11) YES NULL > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` int(11) DEFAULT NULL, > + `c` blob DEFAULT NULL, > + `d` text DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` int(11) DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535)), > + UNIQUE KEY `c` (`c`(65535)), > + UNIQUE KEY `d` (`d`(65535)), > + UNIQUE KEY `e` (`e`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +#unique key should not break; > +insert into t1 values(1,2,3,4,5,6); > +ERROR 23000: Duplicate entry '1' for key 'a' > +select db_row_hash_1 , db_row_hash_2, db_row_hash_3 from t1; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list' > +#now try to change db_row_hash_1 column; > +alter table t1 drop column db_row_hash_1, drop column db_row_hash_2, drop > column db_row_hash_3; > +ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists > +alter table t1 add column dg int , add column ef int , drop column > db_row_hash_1; > +ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists > +alter table t1 modify column db_row_hash_1 int , modify column db_row_hash_2 > int, modify column db_row_hash_3 int; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 add column ar int , add column rb int, modify column > db_row_hash_1 int , modify column db_row_hash_3 int; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 change column db_row_hash_1 dsds int , change column > db_row_hash_2 dfdf int , change column db_row_hash_3 gdfg int ; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 add column asd int, drop column a, change column > db_row_hash_1 dsds int, change db_row_hash_3 fdfdfd int; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 drop column b , add column g int; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `c` blob DEFAULT NULL, > + `d` text DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` int(11) DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535)), > + UNIQUE KEY `c` (`c`(65535)), > + UNIQUE KEY `d` (`d`(65535)), > + UNIQUE KEY `e` (`e`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +#now add some column with name db_row_hash; > +alter table t1 add column db_row_hash_1 int unique; > +alter table t1 add column db_row_hash_2 int unique; > +alter table t1 add column db_row_hash_3 int unique; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `c` blob DEFAULT NULL, > + `d` text DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` int(11) DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_3` int(11) DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535)), > + UNIQUE KEY `c` (`c`(65535)), > + UNIQUE KEY `d` (`d`(65535)), > + UNIQUE KEY `e` (`e`), > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), > + UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), > + UNIQUE KEY `db_row_hash_3` (`db_row_hash_3`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +alter table t1 add column db_row_hash_7 int, add column db_row_hash_5 int , > add column db_row_hash_4 int ; > +alter table t1 drop column db_row_hash_7,drop column db_row_hash_3, drop > column db_row_hash_4; > +desc t1; > +Field Type Null Key Default Extra > +a blob YES UNI NULL > +c blob YES UNI NULL > +d text YES UNI NULL > +e varchar(3000) YES UNI NULL > +f int(11) YES NULL > +g int(11) YES NULL > +db_row_hash_1 int(11) YES UNI NULL > +db_row_hash_2 int(11) YES UNI NULL > +db_row_hash_5 int(11) YES NULL > +#this show now break anything; "this should not" ? > +insert into t1 values(1,2,3,4,5,6,23,5,6); > +ERROR 23000: Duplicate entry '1' for key 'a' > +#this should also drop the unique index; > +alter table t1 drop column a, drop column c; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `d` text DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` int(11) DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_5` int(11) DEFAULT NULL, > + UNIQUE KEY `d` (`d`(65535)), > + UNIQUE KEY `e` (`e`), > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), > + UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 d 1 d A NULL 65535 NULL YES > HASH_INDEX > +t1 0 e 1 e A NULL NULL NULL YES > HASH_INDEX > +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL > NULL YES BTREE > +t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL > NULL YES BTREE > +#add column with unique index on blob; > +alter table t1 add column a blob unique; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `d` text DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` int(11) DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_5` int(11) DEFAULT NULL, > + `a` blob DEFAULT NULL, > + UNIQUE KEY `d` (`d`(65535)), > + UNIQUE KEY `e` (`e`), > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), > + UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), > + UNIQUE KEY `a` (`a`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 d 1 d A NULL 65535 NULL YES > HASH_INDEX > +t1 0 e 1 e A NULL NULL NULL YES > HASH_INDEX > +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL > NULL YES BTREE > +t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL > NULL YES BTREE > +t1 0 a 1 a A NULL NULL NULL YES > HASH_INDEX > +#try to change the blob unique column name; > +#this will change index to b tree; > +alter table t1 modify column a int , modify column e int; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `d` text DEFAULT NULL, > + `e` int(11) DEFAULT NULL, > + `f` int(11) DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_5` int(11) DEFAULT NULL, > + `a` int(11) DEFAULT NULL, > + UNIQUE KEY `d` (`d`(65535)), > + UNIQUE KEY `e` (`e`), > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), > + UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), > + UNIQUE KEY `a` (`a`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 d 1 d A NULL 65535 NULL YES > HASH_INDEX > +t1 0 e 1 e A NULL NULL NULL YES > BTREE > +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL > NULL YES BTREE > +t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL > NULL YES BTREE > +t1 0 a 1 a A NULL NULL NULL YES > BTREE > +alter table t1 add column clm1 blob unique,add column clm2 blob unique; > +#try changing the name; > +alter table t1 change column clm1 clm_changed1 blob, change column clm2 > clm_changed2 blob; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `d` text DEFAULT NULL, > + `e` int(11) DEFAULT NULL, > + `f` int(11) DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_5` int(11) DEFAULT NULL, > + `a` int(11) DEFAULT NULL, > + `clm_changed1` blob DEFAULT NULL, > + `clm_changed2` blob DEFAULT NULL, > + UNIQUE KEY `d` (`d`(65535)), > + UNIQUE KEY `e` (`e`), > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), > + UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), > + UNIQUE KEY `a` (`a`), > + UNIQUE KEY `clm1` (`clm_changed1`), > + UNIQUE KEY `clm2` (`clm_changed2`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 d 1 d A NULL 65535 NULL YES > HASH_INDEX > +t1 0 e 1 e A NULL NULL NULL YES > BTREE > +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL > NULL YES BTREE > +t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL > NULL YES BTREE > +t1 0 a 1 a A NULL NULL NULL YES > BTREE > +t1 0 clm1 1 clm_changed1 A NULL NULL NULL > YES HASH_INDEX > +t1 0 clm2 1 clm_changed2 A NULL NULL NULL > YES HASH_INDEX > +#now drop the unique key; > +alter table t1 drop key clm1, drop key clm2; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `d` text DEFAULT NULL, > + `e` int(11) DEFAULT NULL, > + `f` int(11) DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + `db_row_hash_5` int(11) DEFAULT NULL, > + `a` int(11) DEFAULT NULL, > + `clm_changed1` blob DEFAULT NULL, > + `clm_changed2` blob DEFAULT NULL, > + UNIQUE KEY `d` (`d`(65535)), > + UNIQUE KEY `e` (`e`), > + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), > + UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), > + UNIQUE KEY `a` (`a`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 d 1 d A NULL 65535 NULL YES > HASH_INDEX > +t1 0 e 1 e A NULL NULL NULL YES > BTREE > +t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL > NULL YES BTREE > +t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL > NULL YES BTREE > +t1 0 a 1 a A NULL NULL NULL YES > BTREE > +drop table t1; > +#now the table with key on multiple columns; the ultimate test; > +create table t1(a blob, b int , c varchar(2000) , d text , e varchar(3000) > , f longblob , g int , h text , > +unique(a,b,c), unique(c,d,e),unique(e,f,g,h), unique(b,d,g,h)); > +insert into t1 > values(1,1,1,1,1,1,1,1),(2,2,2,2,2,2,2,2),(3,3,3,3,3,3,3,3),(4,4,4,4,4,4,4,4),(5,5,5,5,5,5,5,5), > +('maria',6,'maria','maria','maria','maria',6,'maria'),('mariadb',7,'mariadb','mariadb','mariadb','mariadb',8,'mariadb') > +,(null,null,null,null,null,null,null,null),(null,null,null,null,null,null,null,null); > +#table structure; > +desc t1; > +Field Type Null Key Default Extra > +a blob YES MUL NULL > +b int(11) YES MUL NULL > +c varchar(2000) YES MUL NULL > +d text YES NULL > +e varchar(3000) YES MUL NULL > +f longblob YES NULL > +g int(11) YES NULL > +h text YES NULL > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` int(11) DEFAULT NULL, > + `c` varchar(2000) DEFAULT NULL, > + `d` text DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` longblob DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `h` text DEFAULT NULL, > + UNIQUE KEY `a` (`a`,`b`,`c`), > + UNIQUE KEY `c` (`c`,`d`,`e`), > + UNIQUE KEY `e` (`e`,`f`,`g`,`h`), > + UNIQUE KEY `b` (`b`,`d`,`g`,`h`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL NULL NULL YES > HASH_INDEX > +t1 0 a 2 b A NULL NULL NULL YES > HASH_INDEX > +t1 0 a 3 c A NULL NULL NULL YES > HASH_INDEX > +t1 0 c 1 c A NULL NULL NULL YES > HASH_INDEX > +t1 0 c 2 d A 0 NULL NULL YES > HASH_INDEX > +t1 0 c 3 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 1 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 2 f A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 4 h A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 1 b A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 2 d A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 4 h A 0 NULL NULL YES > HASH_INDEX > + > +MyISAM file: DATADIR/test/t1 > +Record format: Packed > +Character set: latin1_swedish_ci (8) > +Data records: 9 Deleted blocks: 0 > +Recordlength: 5092 > + > +table description: > +Key Start Len Index Type > +1 5081 8 multip. ulonglong prefix NULL > +2 5073 8 multip. ulonglong prefix NULL > +3 5065 8 multip. ulonglong prefix NULL > +4 5057 8 multip. ulonglong NULL > +select * from information_schema.columns where table_schema = 'test' and > table_name = 't1'; > +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME > ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE > CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION > NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME > COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES > COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION > +def test t1 a 1 NULL YES blob 65535 65535 > NULL NULL NULL NULL NULL blob MUL > select,insert,update,references NEVER NULL > +def test t1 b 2 NULL YES int NULL NULL > 10 0 NULL NULL NULL int(11) MUL > select,insert,update,references NEVER NULL > +def test t1 c 3 NULL YES varchar 2000 2000 > NULL NULL NULL latin1 latin1_swedish_ci varchar(2000) MUL > select,insert,update,references NEVER NULL > +def test t1 d 4 NULL YES text 65535 65535 > NULL NULL NULL latin1 latin1_swedish_ci text > select,insert,update,references NEVER NULL > +def test t1 e 5 NULL YES varchar 3000 3000 > NULL NULL NULL latin1 latin1_swedish_ci varchar(3000) MUL > select,insert,update,references NEVER NULL > +def test t1 f 6 NULL YES longblob > 4294967295 4294967295 NULL NULL NULL NULL NULL > longblob select,insert,update,references NEVER > NULL > +def test t1 g 7 NULL YES int NULL NULL > 10 0 NULL NULL NULL int(11) > select,insert,update,references NEVER NULL > +def test t1 h 8 NULL YES text 65535 65535 > NULL NULL NULL latin1 latin1_swedish_ci text > select,insert,update,references NEVER NULL > +select * from information_schema.statistics where table_schema = 'test' and > table_name = 't1'; > +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA > INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY > SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT > INDEX_COMMENT > +def test t1 0 test a 1 a A NULL > NULL NULL YES HASH_INDEX > +def test t1 0 test a 2 b A NULL > NULL NULL YES HASH_INDEX > +def test t1 0 test a 3 c A NULL > NULL NULL YES HASH_INDEX > +def test t1 0 test c 1 c A NULL > NULL NULL YES HASH_INDEX > +def test t1 0 test c 2 d A 0 > NULL NULL YES HASH_INDEX > +def test t1 0 test c 3 e A 0 > NULL NULL YES HASH_INDEX > +def test t1 0 test e 1 e A 0 > NULL NULL YES HASH_INDEX > +def test t1 0 test e 2 f A 0 > NULL NULL YES HASH_INDEX > +def test t1 0 test e 3 g A 0 > NULL NULL YES HASH_INDEX > +def test t1 0 test e 4 h A 0 > NULL NULL YES HASH_INDEX > +def test t1 0 test b 1 b A 0 > NULL NULL YES HASH_INDEX > +def test t1 0 test b 2 d A 0 > NULL NULL YES HASH_INDEX > +def test t1 0 test b 3 g A 0 > NULL NULL YES HASH_INDEX > +def test t1 0 test b 4 h A 0 > NULL NULL YES HASH_INDEX > +select * from information_schema.key_column_usage where table_schema= 'test' > and table_name= 't1'; > +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG > TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION > POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME > REFERENCED_COLUMN_NAME > +def test a def test t1 a 1 NULL NULL > NULL NULL > +def test a def test t1 b 2 NULL NULL > NULL NULL > +def test a def test t1 c 3 NULL NULL > NULL NULL > +def test c def test t1 c 1 NULL NULL > NULL NULL > +def test c def test t1 d 2 NULL NULL > NULL NULL > +def test c def test t1 e 3 NULL NULL > NULL NULL > +def test e def test t1 e 1 NULL NULL > NULL NULL > +def test e def test t1 f 2 NULL NULL > NULL NULL > +def test e def test t1 g 3 NULL NULL > NULL NULL > +def test e def test t1 h 4 NULL NULL > NULL NULL > +def test b def test t1 b 1 NULL NULL > NULL NULL > +def test b def test t1 d 2 NULL NULL > NULL NULL > +def test b def test t1 g 3 NULL NULL > NULL NULL > +def test b def test t1 h 4 NULL NULL > NULL NULL > +# table select we should not be able to see db_row_hash_1 column; > +select * from t1; > +a b c d e f g h > +1 1 1 1 1 1 1 1 > +2 2 2 2 2 2 2 2 > +3 3 3 3 3 3 3 3 > +4 4 4 4 4 4 4 4 > +5 5 5 5 5 5 5 5 > +maria 6 maria maria maria maria 6 maria > +mariadb 7 mariadb mariadb mariadb mariadb 8 mariadb > +NULL NULL NULL NULL NULL NULL NULL NULL > +NULL NULL NULL NULL NULL NULL NULL NULL > +select db_row_hash_1 from t1; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list' > +select db_row_hash_2 from t1; > +ERROR 42S22: Unknown column 'db_row_hash_2' in 'field list' > +select db_row_hash_3 from t1; > +ERROR 42S22: Unknown column 'db_row_hash_3' in 'field list' > +#duplicate entry test; > +#duplicate keys entry; > +insert into t1 values(1,1,1,0,0,0,0,0); > +ERROR 23000: Duplicate entry '1-1-1' for key 'a' > +insert into t1 values(0,0,1,1,1,0,0,0); > +ERROR 23000: Duplicate entry '1-1-1' for key 'c' > +insert into t1 values(0,0,0,0,1,1,1,1); > +ERROR 23000: Duplicate entry '1-1-1-1' for key 'e' > +insert into t1 values(1,1,1,1,1,0,0,0); > +ERROR 23000: Duplicate entry '1-1-1' for key 'a' > +insert into t1 values(0,0,0,0,1,1,1,1); > +ERROR 23000: Duplicate entry '1-1-1-1' for key 'e' > +insert into t1 values(1,1,1,1,1,1,1,1); > +ERROR 23000: Duplicate entry '1-1-1' for key 'a' > +select db_row_hash_1,db_row_hash_2,db_row_hash_3,db_row_hash_4,db_row_hash_5 > from t1; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list' > +alter table t1 drop column db_row_hash_1, drop column db_row_hash_2, drop > column db_row_hash_3; > +ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists > +alter table t1 add column dg int , add column ef int , drop column > db_row_hash_1; > +ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists > +alter table t1 modify column db_row_hash_1 int , modify column db_row_hash_2 > int, modify column db_row_hash_3 int; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 add column ar int , add column rb int, modify column > db_row_hash_1 int , modify column db_row_hash_3 int; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 change column db_row_hash_1 dsds int , change column > db_row_hash_2 dfdf int , change column db_row_hash_3 gdfg int ; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +alter table t1 add column asd int, drop column a, change column > db_row_hash_1 dsds int, change db_row_hash_3 fdfdfd int; > +ERROR 42S22: Unknown column 'db_row_hash_1' in 't1' > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` int(11) DEFAULT NULL, > + `c` varchar(2000) DEFAULT NULL, > + `d` text DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` longblob DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `h` text DEFAULT NULL, > + UNIQUE KEY `a` (`a`,`b`,`c`), > + UNIQUE KEY `c` (`c`,`d`,`e`), > + UNIQUE KEY `e` (`e`,`f`,`g`,`h`), > + UNIQUE KEY `b` (`b`,`d`,`g`,`h`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +# add column named db_row_hash_*; > +alter table t1 add column db_row_hash_7 int , add column db_row_hash_5 int, > +add column db_row_hash_1 int, add column db_row_hash_2 int; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` int(11) DEFAULT NULL, > + `c` varchar(2000) DEFAULT NULL, > + `d` text DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` longblob DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `h` text DEFAULT NULL, > + `db_row_hash_7` int(11) DEFAULT NULL, > + `db_row_hash_5` int(11) DEFAULT NULL, > + `db_row_hash_1` int(11) DEFAULT NULL, > + `db_row_hash_2` int(11) DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535),`b`,`c`), > + UNIQUE KEY `c` (`c`,`d`(65535),`e`), > + UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)), > + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL 65535 NULL YES > HASH_INDEX > +t1 0 a 2 b A NULL NULL NULL YES > HASH_INDEX > +t1 0 a 3 c A NULL NULL NULL YES > HASH_INDEX > +t1 0 c 1 c A NULL NULL NULL YES > HASH_INDEX > +t1 0 c 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 c 3 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 1 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 2 f A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 4 h A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 1 b A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 4 h A 0 65535 NULL YES > HASH_INDEX > +alter table t1 drop column db_row_hash_7 , drop column db_row_hash_5 , > +drop column db_row_hash_1, drop column db_row_hash_2 ; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` int(11) DEFAULT NULL, > + `c` varchar(2000) DEFAULT NULL, > + `d` text DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` longblob DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `h` text DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535),`b`,`c`), > + UNIQUE KEY `c` (`c`,`d`(65535),`e`), > + UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)), > + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL 65535 NULL YES > HASH_INDEX > +t1 0 a 2 b A NULL NULL NULL YES > HASH_INDEX > +t1 0 a 3 c A NULL NULL NULL YES > HASH_INDEX > +t1 0 c 1 c A NULL NULL NULL YES > HASH_INDEX > +t1 0 c 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 c 3 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 1 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 2 f A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 e 4 h A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 1 b A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 4 h A 0 65535 NULL YES > HASH_INDEX > +#try to change column names; > +alter table t1 change column a aa blob , change column b bb blob , change > column d dd blob; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `aa` blob DEFAULT NULL, > + `bb` blob DEFAULT NULL, > + `c` varchar(2000) DEFAULT NULL, > + `dd` blob DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` longblob DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `h` text DEFAULT NULL, > + UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)), > + UNIQUE KEY `a` (`aa`(65535),`bb`,`c`), > + UNIQUE KEY `c` (`c`,`dd`(65535),`e`), > + UNIQUE KEY `b` (`bb`,`dd`(65535),`g`,`h`(65535)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 e 1 e A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 2 f A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 3 g A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 4 h A NULL 65535 NULL YES > HASH_INDEX > +t1 0 a 1 aa A 0 65535 NULL YES > HASH_INDEX > +t1 0 a 2 bb A 0 NULL NULL YES > HASH_INDEX > +t1 0 a 3 c A 0 NULL NULL YES > HASH_INDEX > +t1 0 c 1 c A 0 NULL NULL YES > HASH_INDEX > +t1 0 c 2 dd A 0 65535 NULL YES > HASH_INDEX > +t1 0 c 3 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 1 bb A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 2 dd A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 4 h A 0 65535 NULL YES > HASH_INDEX > +alter table t1 change column aa a blob , change column bb b blob , change > column dd d blob; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` blob DEFAULT NULL, > + `c` varchar(2000) DEFAULT NULL, > + `d` blob DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` longblob DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `h` text DEFAULT NULL, > + UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)), > + UNIQUE KEY `a` (`a`(65535),`b`,`c`), > + UNIQUE KEY `c` (`c`,`d`(65535),`e`), > + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 e 1 e A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 2 f A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 3 g A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 4 h A NULL 65535 NULL YES > HASH_INDEX > +t1 0 a 1 a A 0 65535 NULL YES > HASH_INDEX > +t1 0 a 2 b A 0 NULL NULL YES > HASH_INDEX > +t1 0 a 3 c A 0 NULL NULL YES > HASH_INDEX > +t1 0 c 1 c A 0 NULL NULL YES > HASH_INDEX > +t1 0 c 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 c 3 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 1 b A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 4 h A 0 65535 NULL YES > HASH_INDEX > +#now we will change the data type to int and varchar limit so that we no > longer require hash_index; > +#on key a_b_c; > +alter table t1 modify column a varchar(20) , modify column b varchar(20) , > modify column c varchar(20); > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` varchar(20) DEFAULT NULL, > + `b` varchar(20) DEFAULT NULL, > + `c` varchar(20) DEFAULT NULL, > + `d` blob DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` longblob DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `h` text DEFAULT NULL, > + UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)), > + UNIQUE KEY `a` (`a`,`b`,`c`), > + UNIQUE KEY `c` (`c`,`d`(65535),`e`), > + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 e 1 e A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 2 f A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 3 g A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 4 h A NULL 65535 NULL YES > HASH_INDEX > +t1 0 a 1 a A NULL NULL NULL YES > BTREE > +t1 0 a 2 b A NULL NULL NULL YES > BTREE > +t1 0 a 3 c A 0 NULL NULL YES > BTREE > +t1 0 c 1 c A 0 NULL NULL YES > HASH_INDEX > +t1 0 c 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 c 3 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 1 b A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 4 h A 0 65535 NULL YES > HASH_INDEX > +#change it back; > +alter table t1 modify column a blob , modify column b blob , modify column c > blob; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` blob DEFAULT NULL, > + `c` blob DEFAULT NULL, > + `d` blob DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` longblob DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `h` text DEFAULT NULL, > + UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)), > + UNIQUE KEY `a` (`a`,`b`,`c`), > + UNIQUE KEY `c` (`c`,`d`(65535),`e`), > + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 e 1 e A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 2 f A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 3 g A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 4 h A NULL 65535 NULL YES > HASH_INDEX > +t1 0 a 1 a A 0 NULL NULL YES > HASH_INDEX > +t1 0 a 2 b A 0 NULL NULL YES > HASH_INDEX > +t1 0 a 3 c A 0 NULL NULL YES > HASH_INDEX > +t1 0 c 1 c A 0 NULL NULL YES > HASH_INDEX > +t1 0 c 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 c 3 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 1 b A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 4 h A 0 65535 NULL YES > HASH_INDEX > +#try to delete blob column in unique; > +truncate table t1; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` blob DEFAULT NULL, > + `c` blob DEFAULT NULL, > + `d` blob DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` longblob DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `h` text DEFAULT NULL, > + UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)), > + UNIQUE KEY `a` (`a`,`b`,`c`), > + UNIQUE KEY `c` (`c`,`d`(65535),`e`), > + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 e 1 e A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 2 f A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 3 g A NULL NULL NULL YES > HASH_INDEX > +t1 0 e 4 h A NULL 65535 NULL YES > HASH_INDEX > +t1 0 a 1 a A 0 NULL NULL YES > HASH_INDEX > +t1 0 a 2 b A 0 NULL NULL YES > HASH_INDEX > +t1 0 a 3 c A 0 NULL NULL YES > HASH_INDEX > +t1 0 c 1 c A 0 NULL NULL YES > HASH_INDEX > +t1 0 c 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 c 3 e A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 1 b A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 4 h A 0 65535 NULL YES > HASH_INDEX > +#now try to delete keys; > +alter table t1 drop key c, drop key e; > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` blob DEFAULT NULL, > + `c` blob DEFAULT NULL, > + `d` blob DEFAULT NULL, > + `e` varchar(3000) DEFAULT NULL, > + `f` longblob DEFAULT NULL, > + `g` int(11) DEFAULT NULL, > + `h` text DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535),`b`(65535),`c`(65535)), > + UNIQUE KEY `b` (`b`(65535),`d`(65535),`g`,`h`(65535)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL 65535 NULL YES > HASH_INDEX > +t1 0 a 2 b A NULL 65535 NULL YES > HASH_INDEX > +t1 0 a 3 c A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 1 b A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 b 3 g A 0 NULL NULL YES > HASH_INDEX > +t1 0 b 4 h A 0 65535 NULL YES > HASH_INDEX > +drop table t1; > +#now alter table containing some data basically some tests with ignore; > +create table t1 (a blob); > +insert into t1 values(1),(2),(3); > +#normal alter table; > +alter table t1 add unique key(a); > +alter table t1 drop key a; > +truncate table t1; > +insert into t1 values(1),(1),(2),(2),(3); > +alter table t1 add unique key(a); > +ERROR 23000: Duplicate entry '1' for key 'a' > +alter ignore table t1 add unique key(a); > +select * from t1; > +a > +1 > +2 > +3 > +insert into t1 values(1); > +ERROR 23000: Duplicate entry '1' for key 'a' > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL 65535 NULL YES > HASH_INDEX > +drop table t1; > +#Now with multiple keys; > +create table t1(a blob , b blob, c blob , d blob , e int); > +insert into t1 values (1,1,1,1,1); > +insert into t1 values (1,1,1,1,1); > +insert into t1 values (2,1,1,1,1); > +insert into t1 values (2,2,2,2,2); > +insert into t1 values (3,3,4,4,4); > +insert into t1 values (4,4,4,4,4); > +alter table t1 add unique key(a,c), add unique key(b,d), add unique key(e); > +ERROR 23000: Duplicate entry '1-1' for key 'a' > +alter ignore table t1 add unique key(a,c), add unique key(b,d), add unique > key(e); > +select * from t1; > +a b c d e > +1 1 1 1 1 > +2 2 2 2 2 > +3 3 4 4 4 > +insert into t1 values (1,12,1,13,14); > +ERROR 23000: Duplicate entry '1-1' for key 'a' > +insert into t1 values (12,1,14,1,14); > +ERROR 23000: Duplicate entry '1-1' for key 'b' > +insert into t1 values (13,12,13,14,4); > +ERROR 23000: Duplicate entry '4' for key 'e' > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + `b` blob DEFAULT NULL, > + `c` blob DEFAULT NULL, > + `d` blob DEFAULT NULL, > + `e` int(11) DEFAULT NULL, > + UNIQUE KEY `a` (`a`(65535),`c`(65535)), > + UNIQUE KEY `b` (`b`(65535),`d`(65535)), > + UNIQUE KEY `e` (`e`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL 65535 NULL YES > HASH_INDEX > +t1 0 a 2 c A NULL 65535 NULL YES > HASH_INDEX > +t1 0 b 1 b A NULL 65535 NULL YES > HASH_INDEX > +t1 0 b 2 d A 0 65535 NULL YES > HASH_INDEX > +t1 0 e 1 e A 0 NULL NULL YES > BTREE > +drop table t1; > +#visibility of db_row_hash > +create table t1 (a blob unique , b blob unique); > +desc t1; > +Field Type Null Key Default Extra > +a blob YES UNI NULL > +b blob YES UNI NULL > +insert into t1 values(1,19); > +insert into t1 values(2,29); > +insert into t1 values(3,39); > +insert into t1 values(4,49); > +create table t2 (DB_ROW_HASH_1 int, DB_ROW_HASH_2 int); > +insert into t2 values(11,1); > +insert into t2 values(22,2); > +insert into t2 values(33,3); > +insert into t2 values(44,4); > +select * from t1; > +a b > +1 19 > +2 29 > +3 39 > +4 49 > +select * from t2; > +DB_ROW_HASH_1 DB_ROW_HASH_2 > +11 1 > +22 2 > +33 3 > +44 4 > +select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1; > +ERROR 42S22: Unknown column 'DB_ROW_HASH_1' in 'field list' > +#bug > +select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1,t2; > +DB_ROW_HASH_1 DB_ROW_HASH_2 > +11 1 > +11 1 > +11 1 > +11 1 > +22 2 > +22 2 > +22 2 > +22 2 > +33 3 > +33 3 > +33 3 > +33 3 > +44 4 > +44 4 > +44 4 > +44 4 > +select * from t1 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t2); > +ERROR 42S22: Unknown column 'DB_ROW_HASH_1' in 'IN/ALL/ANY subquery' > +select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1,t2 where DB_ROW_HASH_1 in > (select DB_ROW_HASH_1 from t2); > +DB_ROW_HASH_1 DB_ROW_HASH_2 > +11 1 > +22 2 > +33 3 > +44 4 > +11 1 > +22 2 > +33 3 > +44 4 > +11 1 > +22 2 > +33 3 > +44 4 > +11 1 > +22 2 > +33 3 > +44 4 > +select * from t2 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t1); > +DB_ROW_HASH_1 DB_ROW_HASH_2 > +11 1 > +22 2 > +33 3 > +44 4 > +select DB_ROW_HASH_1 from t1,t2 where t1.DB_ROW_HASH_1 = t2.DB_ROW_HASH_2; > +ERROR 42S22: Unknown column 't1.DB_ROW_HASH_1' in 'where clause' > +select DB_ROW_HASH_1 from t1 inner join t2 on t1.a = t2.DB_ROW_HASH_2; > +DB_ROW_HASH_1 > +11 > +22 > +33 > +44 > +drop table t1,t2; > +#very long blob entry; > +SET @@GLOBAL.max_allowed_packet=67108864; > +connect 'newcon', localhost, root,,; > +connection newcon; > +show variables like 'max_allowed_packet'; > +Variable_name Value > +max_allowed_packet 67108864 > +create table t1(a longblob unique, b longblob , c longblob , unique(b,c)); > +desc t1; > +Field Type Null Key Default Extra > +a longblob YES UNI NULL > +b longblob YES MUL NULL > +c longblob YES NULL > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` longblob DEFAULT NULL, > + `b` longblob DEFAULT NULL, > + `c` longblob DEFAULT NULL, > + UNIQUE KEY `a` (`a`), > + UNIQUE KEY `b` (`b`,`c`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL NULL NULL YES > HASH_INDEX > +t1 0 b 1 b A NULL NULL NULL YES > HASH_INDEX > +t1 0 b 2 c A 0 NULL NULL YES > HASH_INDEX > +insert into t1 > values(concat(repeat('sachin',10000000),'1'),concat(repeat('sachin',10000000),'1'), > +concat(repeat('sachin',10000000),'1')); > +insert into t1 > values(concat(repeat('sachin',10000000),'2'),concat(repeat('sachin',10000000),'2'), > +concat(repeat('sachin',10000000),'1')); > +insert into t1 > values(concat(repeat('sachin',10000000),'2'),concat(repeat('sachin',10000000),'2'), > +concat(repeat('sachin',10000000),'4')); > +ERROR 23000: Duplicate entry > 'sachinsachinsachinsachinsachinsachinsachinsachinsachinsachinsach' for key 'a' > +insert into t1 > values(concat(repeat('sachin',10000000),'3'),concat(repeat('sachin',10000000),'1'), > +concat(repeat('sachin',10000000),'1')); > +ERROR 23000: Duplicate entry > 'sachinsachinsachinsachinsachinsachinsachinsachinsachinsachinsach' for key 'b' > +drop table t1; > +#long key unique with different key length > +create table t1(a blob, unique(a(3000))); > +desc t1; > +Field Type Null Key Default Extra > +a blob YES UNI NULL > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL 3000 NULL YES > HASH_INDEX > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob DEFAULT NULL, > + UNIQUE KEY `a` (`a`(3000)) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +insert into t1 value(concat(repeat('s',3000),'1')); > +insert into t1 value(concat(repeat('s',3000),'2')); > +ERROR 23000: Duplicate entry > 'ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss' for key 'a' > +insert into t1 value(concat(repeat('a',3000),'2')); > +drop table t1; > +create table t1(a varchar(4000), b longblob , c varchar(5000), d longblob, > +unique(a(3500), b(1000000)), unique(c(4500), d(10000000))); > +desc t1; > +Field Type Null Key Default Extra > +a varchar(4000) YES MUL NULL > +b longblob YES NULL > +c varchar(5000) YES MUL NULL > +d longblob YES NULL > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` varchar(4000) DEFAULT NULL, > + `b` longblob DEFAULT NULL, > + `c` varchar(5000) DEFAULT NULL, > + `d` longblob DEFAULT NULL, > + UNIQUE KEY `a` (`a`(3500),`b`), > + UNIQUE KEY `c` (`c`(4500),`d`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +show keys from t1; > +Table Non_unique Key_name Seq_in_index Column_name > Collation Cardinality Sub_part Packed Null Index_type > Comment Index_comment > +t1 0 a 1 a A NULL 3500 NULL YES > HASH_INDEX > +t1 0 a 2 b A NULL NULL NULL YES > HASH_INDEX > +t1 0 c 1 c A 0 4500 NULL YES > HASH_INDEX > +t1 0 c 2 d A 0 NULL NULL YES > HASH_INDEX > +drop table t1; > +disconnect newcon; > +connection default; > +SET @@GLOBAL.max_allowed_packet=4194304; > +#ext bug > +create table t1(a int primary key, b blob unique, c int, d blob , index(c)); > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` int(11) NOT NULL, > + `b` blob DEFAULT NULL, > + `c` int(11) DEFAULT NULL, > + `d` blob DEFAULT NULL, > + PRIMARY KEY (`a`), > + UNIQUE KEY `b` (`b`), > + KEY `c` (`c`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +insert into t1 values(1,23,1,33); > +insert into t1 values(2,23,1,33); > +ERROR 23000: Duplicate entry '23' for key 'b' > +drop table t1; > +create table t2 (a blob unique , c int , index(c)); > +show create table t2; > +Table Create Table > +t2 CREATE TABLE `t2` ( > + `a` blob DEFAULT NULL, > + `c` int(11) DEFAULT NULL, > + UNIQUE KEY `a` (`a`), > + KEY `c` (`c`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +insert into t2 values(1,1); > +insert into t2 values(2,1); > +drop table t2; > +#not null test //todo solve warnings > +create table t1(a blob unique not null); > +desc t1; > +Field Type Null Key Default Extra > +a blob NO PRI NULL > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob NOT NULL, > + UNIQUE KEY `a` (`a`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +insert into t1 values(1); > +insert into t1 values(3); > +insert into t1 values(1); > +ERROR 23000: Duplicate entry '1' for key 'a' > +drop table t1; > +create table t1(a int primary key, b blob unique , c blob unique not null); > +insert into t1 values(1,1,1); > +insert into t1 values(2,1,2); > +ERROR 23000: Duplicate entry '1' for key 'b' > +insert into t1 values(3,3,1); > +ERROR 23000: Duplicate entry '1' for key 'c' > +drop table t1; > +create table t1 (a blob unique not null, b blob not null, c blob not null, > unique(b,c)); > +desc t1; > +Field Type Null Key Default Extra > +a blob NO PRI NULL > +b blob NO MUL NULL > +c blob NO NULL > +show create table t1; > +Table Create Table > +t1 CREATE TABLE `t1` ( > + `a` blob NOT NULL, > + `b` blob NOT NULL, > + `c` blob NOT NULL, > + UNIQUE KEY `a` (`a`), > + UNIQUE KEY `b` (`b`,`c`) > +) ENGINE=MyISAM DEFAULT CHARSET=latin1 > +insert into t1 values (1, 2, 3); > +insert into t1 values (2, 1, 3); > +insert into t1 values (2, 1, 3); > +ERROR 23000: Duplicate entry '2' for key 'a' > +drop table t1; > +set @@GLOBAL.max_allowed_packet= @allowed_packet; > diff --git a/sql/field.h b/sql/field.h > index b6f28808e2e..13eea28fe8a 100644 > --- a/sql/field.h > +++ b/sql/field.h > @@ -539,6 +539,7 @@ class Virtual_column_info: public Sql_alloc > Item *expr; > LEX_CSTRING name; /* Name of constraint */ > uint flags; > + LEX_CSTRING hash_expr; It is not a good idea to put your hash-unique-constraint specific data into a common Virtual_column_info. Luckily, you don't need it. Just like "current_timestamp()" expression is generated on the fly as needed in parse_vcol_defs(), you can generate the hash expression there too, no need to do it in TABLE_SHARE::init_from_binary_frm_image(). So, just remove hash_expr. > > Virtual_column_info() > : vcol_type((enum_vcol_info_type)VCOL_TYPE_NONE), > @@ -681,7 +688,7 @@ class Field: public Value_source > GEOM_MULTIPOINT = 4, GEOM_MULTILINESTRING = 5, GEOM_MULTIPOLYGON = 6, > GEOM_GEOMETRYCOLLECTION = 7 > }; > - enum imagetype { itRAW, itMBR}; > + enum imagetype { itRAW, itMBR, itHASH}; itHASH isn't used anywhere in your patch :) > > utype unireg_check; > uint32 field_length; // Length of field > diff --git a/sql/field.cc b/sql/field.cc > index dc854826ed6..f75f52141e3 100644 > --- a/sql/field.cc > +++ b/sql/field.cc > @@ -9822,7 +9822,7 @@ int Field_bit::key_cmp(const uchar *str, uint length) > } > > > -int Field_bit::cmp_offset(uint row_offset) > +int Field_bit::cmp_offset(long row_offset) may be my_ptrdiff_t ? > { > if (bit_len) > { > diff --git a/sql/item_func.h b/sql/item_func.h > index 3b6cb4ceeac..0391de0c5ad 100644 > --- a/sql/item_func.h > +++ b/sql/item_func.h > @@ -803,6 +803,19 @@ class Item_long_func: public Item_int_func > }; > > > +class Item_func_hash: public Item_int_func > +{ > +public: > + Item_func_hash(THD *thd, List<Item> &item): Item_int_func(thd, item) > + {} > + longlong val_int(); > + void fix_length_and_dec(); > + const Type_handler *type_handler() const { return &type_handler_long; } > + Item *get_copy(THD *thd) > + { return get_item_copy<Item_func_hash>(thd, this); } > + const char *func_name() const { return "HASH"; } is it a user visible function? Can one write SELECT HASH(a) FROM t1 ? looks like yes, so 1. please add tests for it 2. we need to document what hash function, exactly, is used. 3. May be better to call it MARIADB_HASH? to highlight its internal-use nature and a home-baked hash function? And to reduce a chance for a collision with user defined functions. 4. An even better approach would be not to create any user visible function at all. Currently unpack_vcol_info_from_frm() calls the parser and then initializes vcol_info and calls fix_fields. If you split it in two you can do `new Item_func_hash` manually and skip the first part of unpack_vcol_info_from_frm(). And you won't need HASH to be user-visible. As a minor optimization, the same can be done for "current_timestamp()" > +}; > + > class Item_longlong_func: public Item_int_func > { > public: > diff --git a/sql/sql_show.cc b/sql/sql_show.cc > index 4923c628bf9..c620ea23165 100644 > --- a/sql/sql_show.cc > +++ b/sql/sql_show.cc > @@ -2300,7 +2300,7 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, > String *packet, > */ > packet->append(STRING_WITH_LEN("PRIMARY KEY")); > } > - else if (key_info->flags & HA_NOSAME) > + else if (key_info->flags & HA_NOSAME || key_info->flags & > HA_LONG_UNIQUE_HASH) Is it needed? As far as I can see, you always set HA_NOSAME whenever you set HA_LONG_UNIQUE_HASH. > packet->append(STRING_WITH_LEN("UNIQUE KEY ")); > else if (key_info->flags & HA_FULLTEXT) > packet->append(STRING_WITH_LEN("FULLTEXT KEY ")); > diff --git a/include/my_base.h b/include/my_base.h > index c36072c0bfa..ba17f8d80dc 100644 > --- a/include/my_base.h > +++ b/include/my_base.h > @@ -290,6 +291,11 @@ enum ha_base_keytype { > #define HA_KEY_HAS_PART_KEY_SEG 65536 > /* Internal Flag Can be calcaluted */ > #define HA_INVISIBLE_KEY 2<<18 > +/* > + Some more flags for keys these are not stored in > + frm it is calculated on the fly in init_from_binary_frm_image > +*/ 1. I don't think you need to make the same comment more verbose every time you repeat it :) Just say, like #define HA_INVISIBLE_KEY 2<<18 /* this is calculated too */ #define HA_LONG_UNIQUE_HASH 2<<19 /* this is calculated too */ 2. Do you need a separate flag for that, just HA_INVISIBLE_KEY isn't enough? > +#define HA_LONG_UNIQUE_HASH 2<<19 > /* Automatic bits in key-flag */ > > #define HA_SPACE_PACK_USED 4 /* Test for if SPACE_PACK used */ > @@ -317,6 +323,13 @@ enum ha_base_keytype { > #define HA_BIT_PART 1024 > #define HA_CAN_MEMCMP 2048 /* internal, never stored in frm */ > > +/* > + Used for key parts whole length is greater then > file->max_key_part_length > + Only used for HA_LONG_UNIQUE_HASH keys > +*/ //TODO a better name ?? > +#define HA_HASH_KEY_PART_FLAG 4096 > +/* Field need to be frees externally */ > +#define HA_FIELD_EX_FREED 8192 Okay, this is a weird name, agree. But it is not used anywere, so there's an easy solution :) > /* optionbits for database */ > #define HA_OPTION_PACK_RECORD 1U > #define HA_OPTION_PACK_KEYS 2U > diff --git a/sql/table.h b/sql/table.h > index 785fd9f3427..9ed7fcd7beb 100644 > --- a/sql/table.h > +++ b/sql/table.h > @@ -346,9 +346,39 @@ enum field_visibility_t { > INVISIBLE_FULL > }; > > -#define INVISIBLE_MAX_BITS 3 > +#define INVISIBLE_MAX_BITS 3 > +/* We will store the info into 3rd bit if field is hash field */ > +#define HASH_FIELD_MASK 15 > +#define HASH_FIELD_MASK_SHIFT 4 > +#define HA_HASH_FIELD_LENGTH 8 > +#define HA_HASH_KEY_LENGTH_WITHOUT_NULL 8 > +#define HA_HASH_KEY_LENGTH_WITH_NULL 9 > +//TODO is this correct ? how about size of char ptr on 32/16 bit machine? I don't understand that, sorry > +#define HA_HASH_KEY_PART_LENGTH 4 + 8 // 4 for length , 8 for > portable size of char ptr you almost always should use parentheses in macro expressions > > +const LEX_CSTRING ha_hash_str {STRING_WITH_LEN("HASH")}; ??? you create a copy of ha_hash_str symbol in every .o file that included table.h? > > + > +int find_field_pos_in_hash(Item *hash_item, const char * field_name); > + > +int fields_in_hash_str(Item *hash_item); > + > +Field * field_ptr_in_hash_str(Item *hash_item, int index); > + > +int get_key_part_length(KEY *keyinfo, int index); > + > +void calc_hash_for_unique(ulong &nr1, ulong &nr2, String *str); > + > +void create_update_handler(THD *thd, TABLE *table); > + > +void delete_update_handler(THD *thd, TABLE *table); > + > +void setup_table_hash(TABLE *table); > + > +void re_setup_table(TABLE *table); > + > +int get_hash_key(THD *thd, TABLE *table, handler *h, uint key_index, uchar > *rec_buf, > + uchar *key_buff); > /** > Category of table found in the table share. > */ > @@ -1094,6 +1124,17 @@ struct TABLE > THD *in_use; /* Which thread uses this */ > > uchar *record[3]; /* Pointer to records */ > + /* record buf to resolve hash collisions for long UNIQUE constraints */ > + uchar *check_unique_buf; > + handler *update_handler; /* Handler used in case of update */ > + /* > + In the case of write row for long unique we are unable of find > + Whick key is voilated. Because we in case of duplicate hash we never > reach > + handler write_row function. So print_error will always print that > + key 0 is voilated. We store which key is voilated in this variable > + by default this should be initialized to -1 typos :) "unable to find", "which", "violated" > + */ > + int dupp_hash_key; > uchar *write_row_record; /* Used as optimisation in > THD::write_row */ > uchar *insert_values; /* used by INSERT ... UPDATE */ > @@ -2898,6 +2939,7 @@ void append_unescaped(String *res, const char *pos, > size_t length); > void prepare_frm_header(THD *thd, uint reclength, uchar *fileinfo, > HA_CREATE_INFO *create_info, uint keys, KEY > *key_info); > const char *fn_frm_ext(const char *name); > +void calc_hash_for_unique(ulong &nr1, ulong &nr2, String *str); you have this prototype twice in table.h > > /* Check that the integer is in the internal */ > static inline int set_zone(int nr,int min_zone,int max_zone) > diff --git a/sql/table.cc b/sql/table.cc > index 73b1a6bd9b2..8cd4db2844d 100644 > --- a/sql/table.cc > +++ b/sql/table.cc > @@ -747,7 +748,13 @@ static bool create_key_infos(const uchar *strpos, const > uchar *frm_image_end, > if (i == 0) > { > ext_key_parts+= (share->use_ext_keys ? > first_keyinfo->user_defined_key_parts*(keys-1) : 0); > + /* > + Some keys can be HA_LONG_UNIQUE_HASH , but we do not know at this > point , > + how many ?, but will always be less than or equal to total num of > + keys. Each HA_LONG_UNIQUE_HASH key require one extra key_part in > which > + it stored hash. On safe side we will allocate memory for each key. > + */ > - n_length=keys * sizeof(KEY) + ext_key_parts * sizeof(KEY_PART_INFO); > + n_length=keys * sizeof(KEY) + (ext_key_parts +keys) * > sizeof(KEY_PART_INFO); Hmm, why wouldn't you store the number of HA_KEY_ALG_LONG_HASH keys in EXTRA2_LONG_UNIQUES ? Then you'll know it here. > if (!(keyinfo= (KEY*) alloc_root(&share->mem_root, > n_length + len))) > return 1; > @@ -798,6 +805,14 @@ static bool create_key_infos(const uchar *strpos, const > uchar *frm_image_end, > } > key_part->store_length=key_part->length; > } > + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) > + { > + keyinfo->flags|= HA_LONG_UNIQUE_HASH | HA_NOSAME; > + keyinfo->key_length= 0; > + share->ext_key_parts++; > + // This empty key_part for storing Hash > + key_part++; > + } so, you write keyinfo's for HA_LONG_UNIQUE_HASH keys into the frm? why don't you write keysegs for them? I mean, it is not very logical. I'd thought you won't write either keyinfos or keysegs. Or, okay, you could write both. But only keyinfos and no keysegs? That's strange > > /* > Add primary key to end of extended keys for non unique keys for > @@ -1143,13 +1159,21 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, > TABLE *table, > pos+= expr_length; > } > > - /* Now, initialize CURRENT_TIMESTAMP fields */ > + /* Now, initialize CURRENT_TIMESTAMP and UNIQUE_INDEX_HASH_FIELD fields */ > for (field_ptr= table->field; *field_ptr; field_ptr++) > { > Field *field= *field_ptr; > - if (field->has_default_now_unireg_check()) > + if (field->vcol_info && (length = field->vcol_info->hash_expr.length)) > { > expr_str.length(parse_vcol_keyword.length); > + expr_str.append((char*)field->vcol_info->hash_expr.str, length); > + vcol= unpack_vcol_info_from_frm(thd, mem_root, table, &expr_str, > + &(field->vcol_info), error_reported); see above about not going through the parser for hash keys. > + *(vfield_ptr++)= *field_ptr; > + > + } > + if (field->has_default_now_unireg_check()) > + { > expr_str.append(STRING_WITH_LEN("current_timestamp(")); > expr_str.append_ulonglong(field->decimals()); > expr_str.append(')'); > @@ -2106,7 +2132,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, > bool write, > uchar flags= *extra2_field_flags++; > if (flags & VERS_OPTIMIZED_UPDATE) > reg_field->flags|= VERS_UPDATE_UNVERSIONED_FLAG; > - > + if (flags & EXTRA2_LONG_UNIQUE_HASH_FIELD) > + reg_field->flags|= LONG_UNIQUE_HASH_FIELD; so, you write LONG_UNIQUE_HASH_FIELD fields to frm too. Why? > reg_field->invisible= f_visibility(flags); > } > if (reg_field->invisible == INVISIBLE_USER) > @@ -2350,6 +2438,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, > bool write, > key_part= keyinfo->key_part; > uint key_parts= share->use_ext_keys ? keyinfo->ext_key_parts : > keyinfo->user_defined_key_parts; > + if (keyinfo->flags & HA_LONG_UNIQUE_HASH) > + key_parts++; key_parts++ ? Doesn't your HA_LONG_UNIQUE_HASH key have only one part, always? > for (i=0; i < key_parts; key_part++, i++) > { > Field *field; > @@ -2363,7 +2453,16 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, > bool write, > > field= key_part->field= share->field[key_part->fieldnr-1]; > key_part->type= field->key_type(); > + if (keyinfo->flags & HA_LONG_UNIQUE_HASH > + &&(key_part->length > handler_file->max_key_part_length() > + || key_part->length == 0)) 1. fix the spacing and the indentation here, please 2. what should happen if HA_LONG_UNIQUE_HASH flag is set, but the key_part->length is small ? > + { > + key_part->key_part_flag= HA_HASH_KEY_PART_FLAG; > + key_part->store_length= HA_HASH_KEY_PART_LENGTH; > + } > + /* Invisible Full is currently used by long uniques */ > - if (field->invisible > INVISIBLE_USER && !field->vers_sys_field()) > + if ((field->invisible == INVISIBLE_USER || > + field->invisible == INVISIBLE_SYSTEM )&& > !field->vers_sys_field()) why is this change? > keyinfo->flags |= HA_INVISIBLE_KEY; > if (field->null_ptr) > { > @@ -2428,7 +2527,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, > bool write, > field->part_of_sortkey= share->keys_in_use; > } > } > - if (field->key_length() != key_part->length) > + if (field->key_length() != key_part->length && > + !(keyinfo->flags & HA_LONG_UNIQUE_HASH)) why is that? > { > #ifndef TO_BE_DELETED_ON_PRODUCTION > if (field->type() == MYSQL_TYPE_NEWDECIMAL) > @@ -2470,7 +2570,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, > bool write, > if (!(key_part->key_part_flag & (HA_BLOB_PART | HA_VAR_LENGTH_PART | > HA_BIT_PART)) && > key_part->type != HA_KEYTYPE_FLOAT && > - key_part->type == HA_KEYTYPE_DOUBLE) > + key_part->type == HA_KEYTYPE_DOUBLE && > + !(keyinfo->flags & HA_LONG_UNIQUE_HASH)) why is that? > key_part->key_part_flag|= HA_CAN_MEMCMP; > } > keyinfo->usable_key_parts= usable_parts; // Filesort > @@ -8346,6 +8454,377 @@ double KEY::actual_rec_per_key(uint i) > } > > > +/* > + find out the field positoin in hash_str() > + position starts from 0 > + else return -1; > +*/ > +int find_field_pos_in_hash(Item *hash_item, const char * field_name) > +{ > + Item_func_or_sum * temp= static_cast<Item_func_or_sum *>(hash_item); > + Item_args * t_item= static_cast<Item_args *>(temp); > + uint arg_count= t_item->argument_count(); > + Item ** arguments= t_item->arguments(); > + Field * t_field; > + > + for (uint j=0; j < arg_count; j++) > + { > + DBUG_ASSERT(arguments[j]->type() == Item::FIELD_ITEM || > + arguments[j]->type() == Item::FUNC_ITEM); > + if (arguments[j]->type() == Item::FIELD_ITEM) > + { > + t_field= static_cast<Item_field *>(arguments[j])->field; > + } > + else > + { > + Item_func_left *fnc= static_cast<Item_func_left *>(arguments[j]); > + t_field= static_cast<Item_field *>(fnc->arguments()[0])->field; > + } > + if (!my_strcasecmp(system_charset_info, t_field->field_name.str, > field_name)) > + return j; > + } > + return -1; > +} > + > +/* > + find total number of field in hash_str > +*/ > +int fields_in_hash_str(Item * hash_item) can be static > +{ > + Item_func_or_sum * temp= static_cast<Item_func_or_sum *>(hash_item); > + Item_args * t_item= static_cast<Item_args *>(temp); > + return t_item->argument_count(); > +} > + > +/* > + Returns fields ptr given by hash_str index > + Index starts from 0 > +*/ > +Field * field_ptr_in_hash_str(Item *hash_item, int index) > +{ > + Item_func_or_sum * temp= static_cast<Item_func_or_sum *>(hash_item); > + Item_args * t_item= static_cast<Item_args *>(temp); > + return static_cast<Item_field *>(t_item->arguments()[index])->field; > +} > + > +//NO longer Needed remove it, then :) > +int get_key_part_length(KEY *keyinfo, int index) > +{ > + DBUG_ASSERT(keyinfo->flags & HA_LONG_UNIQUE_HASH); > + TABLE *tbl= keyinfo->table; > + Item *h_item= keyinfo->key_part->field->vcol_info->expr; > + Field *fld= field_ptr_in_hash_str(h_item, index); > + if (!index) > + return fld->pack_length()+HA_HASH_KEY_LENGTH_WITH_NULL+1; > + return fld->pack_length()+1; > +} > +/** > + @brief clone of current handler. > + Creates a clone of handler used in update for > + unique hash key. > + @param thd Thread Object > + @param table Table Object > + @return handler object > +*/ > +void create_update_handler(THD *thd, TABLE *table) better, clone_handler_for_update > +{ > + handler *update_handler= NULL; > + for (uint i= 0; i < table->s->keys; i++) indentation > + { > + if (table->key_info[i].flags & HA_LONG_UNIQUE_HASH) > + { > + update_handler= table->file->clone(table->s->normalized_path.str, > + thd->mem_root); > + update_handler->ha_external_lock(thd, F_RDLCK); > + table->update_handler= update_handler; why do you store it in TABLE? you can just keep it in a local variable in mysql_update, you don't need it outside of mysql_update anyway. (and multi-update) also, you shouldn't need to scan all table keys here. use some flag or property in TABLE_SCHEMA to check quickly whether the table has these lock unique keys. > + return; > + } > + } > + return; > +} > + > +/** > + @brief Deletes update handler object > + @param thd Thread Object > + @param table Table Object > +*/ > +void delete_update_handler(THD *thd, TABLE *table) > +{ > + if (table->update_handler) > + { > + table->update_handler->ha_external_lock(thd, F_UNLCK); > + table->update_handler->ha_close(); > + delete table->update_handler; > + table->update_handler= NULL; > + } > +} > +/** > + @brief This function makes table object with > + long unique keys ready for storage engine. > + It makes key_part of HA_LONG_UNIQUE_HASH point to > + hash key_part. > + @param table Table object > + */ > +void setup_table_hash(TABLE *table) this and other functions could be methods in TABLE > +{ > + /* > + Extra parts of long unique key which are used only at server level > + for example in key unique(a, b, c) //a b c are blob > + extra_key_part_hash is 3 > + */ > + uint extra_key_part_hash= 0; > + uint hash_parts= 0; > + KEY *s_keyinfo= table->s->key_info; > + KEY *keyinfo= table->key_info; > + /* > + Sometime s_keyinfo or key_info can be null. So > + two different loop for keyinfo and s_keyinfo > + reference test case:- main.subselect_sj2 how they could be null here? > + */ > + > + if (keyinfo) > + { > + for (uint i= 0; i < table->s->keys; i++, keyinfo++) > + { > + if (keyinfo->flags & HA_LONG_UNIQUE_HASH) > + { > + DBUG_ASSERT(keyinfo->user_defined_key_parts == > + keyinfo->ext_key_parts); > + keyinfo->flags&= ~(HA_NOSAME | HA_LONG_UNIQUE_HASH); > + keyinfo->algorithm= HA_KEY_ALG_UNDEF; > + extra_key_part_hash+= keyinfo->ext_key_parts; > + hash_parts++; > + keyinfo->key_part= keyinfo->key_part+ keyinfo->ext_key_parts; > + keyinfo->user_defined_key_parts= keyinfo->usable_key_parts= > + keyinfo->ext_key_parts= 1; > + keyinfo->key_length= keyinfo->key_part->store_length; > + } > + } > + table->s->key_parts-= extra_key_part_hash; > + table->s->key_parts+= hash_parts; > + table->s->ext_key_parts-= extra_key_part_hash; I don't understand what you're doing here. Could you add a comment, explaning the resulting structure of keys and keysegs and what's where? > + } > + if (s_keyinfo) > + { > + for (uint i= 0; i < table->s->keys; i++, s_keyinfo++) > + { > + if (s_keyinfo->flags & HA_LONG_UNIQUE_HASH) > + { > + DBUG_ASSERT(s_keyinfo->user_defined_key_parts == > + s_keyinfo->ext_key_parts); > + s_keyinfo->flags&= ~(HA_NOSAME | HA_LONG_UNIQUE_HASH); > + s_keyinfo->algorithm= HA_KEY_ALG_BTREE; > + extra_key_part_hash+= s_keyinfo->ext_key_parts; > + s_keyinfo->key_part= s_keyinfo->key_part+ s_keyinfo->ext_key_parts; > + s_keyinfo->user_defined_key_parts= s_keyinfo->usable_key_parts= > + s_keyinfo->ext_key_parts= 1; > + s_keyinfo->key_length= s_keyinfo->key_part->store_length; > + } > + } > + if (!keyinfo) > + { > + table->s->key_parts-= extra_key_part_hash; > + table->s->key_parts+= hash_parts; > + table->s->ext_key_parts-= extra_key_part_hash; > + } > + } > +} > + > +/** > + @brief Revert the effect of setup_table_hash > + @param table Table Object > + */ > +void re_setup_table(TABLE *table) > +{ > + //extra key parts excluding hash , which needs to be added in keyparts > + uint extra_key_parts_ex_hash= 0; > + uint extra_hash_parts= 0; // this var for share->extra_hash_parts > + KEY *s_keyinfo= table->s->key_info; > + KEY *keyinfo= table->key_info; > + /* > + Sometime s_keyinfo can be null so > + two different loop for keyinfo and s_keyinfo > + ref test case:- main.subselect_sj2 > + */ > + if (keyinfo) > + { > + for (uint i= 0; i < table->s->keys; i++, keyinfo++) > + { > + if (keyinfo->user_defined_key_parts == 1 && > + keyinfo->key_part->field->flags & LONG_UNIQUE_HASH_FIELD) > + { > + keyinfo->flags|= (HA_NOSAME | HA_LONG_UNIQUE_HASH); > + keyinfo->algorithm= HA_KEY_ALG_LONG_HASH; > + /* Sometimes it can happen, that we does not parsed hash_str. > + Like when this function is called in ha_create. So we will > + Use field from table->field rather then share->field*/ > + Item *h_item= table->field[keyinfo->key_part->fieldnr - 1]-> > + vcol_info->expr; > + uint hash_parts= fields_in_hash_str(h_item); > + keyinfo->key_part= keyinfo->key_part- hash_parts; > + keyinfo->user_defined_key_parts= keyinfo->usable_key_parts= > + keyinfo->ext_key_parts= hash_parts; > + extra_key_parts_ex_hash+= hash_parts; > + extra_hash_parts++; > + keyinfo->key_length= -1; > + } > + } > + table->s->key_parts-= extra_hash_parts; > + table->s->key_parts+= extra_key_parts_ex_hash; > + table->s->ext_key_parts+= extra_key_parts_ex_hash + extra_hash_parts; > + } > + if (s_keyinfo) > + { > + for (uint i= 0; i < table->s->keys; i++, s_keyinfo++) > + { > + if (s_keyinfo->user_defined_key_parts == 1 && > + s_keyinfo->key_part->field->flags & LONG_UNIQUE_HASH_FIELD) > + { > + s_keyinfo->flags|= (HA_NOSAME | HA_LONG_UNIQUE_HASH); > + s_keyinfo->algorithm= HA_KEY_ALG_LONG_HASH; > + extra_hash_parts++; > + /* Sometimes it can happen, that we does not parsed hash_str. > + Like when this function is called in ha_create. So we will > + Use field from table->field rather then share->field*/ > + Item *h_item= table->field[s_keyinfo->key_part->fieldnr - 1]-> > + vcol_info->expr; > + uint hash_parts= fields_in_hash_str(h_item); > + s_keyinfo->key_part= s_keyinfo->key_part- hash_parts; > + s_keyinfo->user_defined_key_parts= s_keyinfo->usable_key_parts= > + s_keyinfo->ext_key_parts= hash_parts; > + extra_key_parts_ex_hash+= hash_parts; > + s_keyinfo->key_length= -1; > + } > + } > + if (!keyinfo) > + { > + table->s->key_parts-= extra_hash_parts; > + table->s->key_parts+= extra_key_parts_ex_hash; > + table->s->ext_key_parts+= extra_key_parts_ex_hash + extra_hash_parts; > + } > + } > +} > + > +/** > + @brief set_hash > + @param table > + @param key_index > + @param key_buff > + > +int get_hash_key(THD *thd,TABLE *table, handler *h, uint key_index, > + uchar * rec_buff, uchar *key_buff) unused? > +{ > + KEY *keyinfo= &table->key_info[key_index]; > + DBUG_ASSERT(keyinfo->flags & HA_LONG_UNIQUE_HASH); > + KEY_PART_INFO *temp_key_part= table->key_info[key_index].key_part; > + Field *fld[keyinfo->user_defined_key_parts]; > + Field *t_field; > + uchar hash_buff[9]; > + ulong nr1= 1, nr2= 4; > + String *str1, temp1; > + String *str2, temp2; > + bool is_null= false; > + bool is_same= true; > + bool is_index_inited= h->inited; > + /* difference between field->ptr and start of rec_buff * > + long diff = rec_buff- table->record[0]; > + int result= 0; > + for (uint i=0; i < keyinfo->user_defined_key_parts; i++, temp_key_part++) > + { > + uint maybe_null= MY_TEST(temp_key_part->null_bit); > + fld[i]= temp_key_part->field->new_key_field(thd->mem_root, table, > + key_buff + maybe_null, 12, > + maybe_null?key_buff:0, 1, > + temp_key_part->key_part_flag > + & HA_HASH_KEY_PART_FLAG); > + if (fld[i]->is_real_null()) > + { > + is_null= true; > + break; > + } > + str1= fld[i]->val_str(&temp1); > + calc_hash_for_unique(nr1, nr2, str1); > + key_buff+= temp_key_part->store_length; > + } > + if (is_null && !(keyinfo->flags & HA_NULL_PART_KEY)) > + return HA_ERR_KEY_NOT_FOUND; > + if (keyinfo->flags & HA_NULL_PART_KEY) > + { > + hash_buff[0]= is_null; > + int8store(hash_buff + 1, nr1); > + } > + else > + int8store(hash_buff, nr1); > + if (!is_index_inited) > + result= h->ha_index_init(key_index, 0); > + if (result) > + return result; > + > + setup_table_hash(table); > + result= h->ha_index_read_map(rec_buff, hash_buff, HA_WHOLE_KEY, > + HA_READ_KEY_EXACT); > + re_setup_table(table); > + if (!result) > + { > + for (uint i=0; i < keyinfo->user_defined_key_parts; i++) > + { > + t_field= keyinfo->key_part[i].field; > + t_field->move_field(t_field->ptr+diff, > + t_field->null_ptr+diff, t_field->null_bit); > + } > + do > + { > + re_setup_table(table); > + is_same= true; > + for (uint i=0; i < keyinfo->user_defined_key_parts; i++) > + { > + t_field= keyinfo->key_part[i].field; > + if (fld[i]->is_real_null() && t_field->is_real_null()) > + continue; > + if (!fld[i]->is_real_null() && !t_field->is_real_null()) > + { > + str1= t_field->val_str(&temp1); > + str2= fld[i]->val_str(&temp2); > + if (my_strcasecmp(str1->charset(), str1->c_ptr_safe(), > + str2->c_ptr_safe())) > + { > + is_same= false; > + break; > + } > + } > + else > + { > + is_same= false; > + break; > + } > + } > + setup_table_hash(table); > + } > + while (!is_same && !(result= h->ha_index_next_same(rec_buff, hash_buff, > + keyinfo->key_length))); > + for (uint i=0; i < keyinfo->user_defined_key_parts; i++) > + { > + t_field= keyinfo->key_part[i].field; > + t_field->move_field(t_field->ptr-diff, > + t_field->null_ptr-diff, t_field->null_bit); > + } > + } > + if (!is_index_inited) > + h->ha_index_end(); > + re_setup_table(table); > + for (uint i=0; i < keyinfo->user_defined_key_parts; i++) > + { > + if (keyinfo->key_part[i].key_part_flag & HA_FIELD_EX_FREED) > + { > + Field_blob *blb= static_cast<Field_blob *>(fld[i]); > + uchar * addr; > + blb->get_ptr(&addr); > + my_free(addr); > + } > + } > + return result; > +} > +*/ > LEX_CSTRING *fk_option_name(enum_fk_option opt) > { > static LEX_CSTRING names[]= > @@ -8814,3 +9293,15 @@ bool TABLE::export_structure(THD *thd, > Row_definition_list *defs) > } > return false; > } > + > +void calc_hash_for_unique(ulong &nr1, ulong &nr2, String *str) > +{ > + CHARSET_INFO *cs; > + uchar l[4]; > + int4store(l, str->length()); > + cs= &my_charset_bin; > + cs->coll->hash_sort(cs, l, sizeof(l), &nr1, &nr2); > + cs= str->charset(); > + cs->coll->hash_sort(cs, (uchar *)str->ptr(), str->length(), &nr1, &nr2); > + sql_print_information("setiya %lu, %s", nr1, str->ptr()); really? :) > +} > diff --git a/sql/sql_table.cc b/sql/sql_table.cc > index 4ac8b102d79..f42687c0377 100644 > --- a/sql/sql_table.cc > +++ b/sql/sql_table.cc > @@ -3343,6 +3343,87 @@ mysql_add_invisible_index(THD *thd, List<Key> > *key_list, > key_list->push_back(key, thd->mem_root); > return key; > } > +/** > + Add hidden level 3 hash field to table in case of long s/hidden level 3/fully invisible/ > + unique column > + @param thd Thread Context. > + @param create_list List of table fields. > + @param cs Field Charset > + @param key_info Whole Keys buffer > + @param key_index Index of current key 1. instead of key_info and key_index, you can just take a key_info of the current key here (that is key_info + key_index). 2. what is the "current key"? Is it the unique key over blobs that you create a hash field for? please clarify the comment. > +*/ > + > +static void add_hash_field(THD * thd, List<Create_field> *create_list, > + CHARSET_INFO *cs, KEY *key_info, int key_index) > +{ > + List_iterator<Create_field> it(*create_list); > + Create_field *dup_field, *cf= new (thd->mem_root) Create_field(); > + cf->flags|= UNSIGNED_FLAG | LONG_UNIQUE_HASH_FIELD; > + cf->charset= cs; > + cf->decimals= 0; > + cf->length= cf->char_length= cf->pack_length= HA_HASH_FIELD_LENGTH; > + cf->invisible= INVISIBLE_FULL; > + cf->pack_flag|= FIELDFLAG_MAYBE_NULL; > + uint num= 1; > + char *temp_name= (char *)thd->alloc(30); > + my_snprintf(temp_name, 30, "DB_ROW_HASH_%u", num); please, give names for 30 and for the whole my_snprintf - macros, functions, whatever. And better use a LEX_STRING for it, for example: #define LONG_HASH_FIELD_NAME_LENGTH 30 static inline make_long_hash_field_name(LEX_STRING buf, uint num) { buf->length= my_snprintf(buf->str, LONG_HASH_FIELD_NAME_LENGTH, "DB_ROW_HASH_%u", num); } > + /* > + Check for collusions collisions :) > + */ > + while ((dup_field= it++)) > + { > + if (!my_strcasecmp(system_charset_info, temp_name, > dup_field->field_name.str)) > + { > + num++; > + my_snprintf(temp_name, 30, "DB_ROW_HASH_%u", num); > + it.rewind(); > + } > + } > + it.rewind(); > + cf->field_name.str= temp_name; > + cf->field_name.length= strlen(temp_name); you won't need strlen here, if you use LEX_STRING above > + cf->set_handler(&type_handler_longlong); > + /* > + We have added db_row_hash field in starting of > + fields array , So we have to change key_part > + field index > + for (int i= 0; i <= key_index; i++, key_info++) > + { > + KEY_PART_INFO *info= key_info->key_part; > + for (uint j= 0; j < key_info->user_defined_key_parts; j++, info++) > + { > + info->fieldnr+= 1; > + info->offset+= HA_HASH_FIELD_LENGTH; > + } > + }*/ Forgot to remove it? > + key_info[key_index].flags|= HA_NOSAME; > + key_info[key_index].algorithm= HA_KEY_ALG_LONG_HASH; > + it.rewind(); > + uint record_offset= 0; > + while ((dup_field= it++)) > + { > + dup_field->offset= record_offset; > + if (dup_field->stored_in_db()) > + record_offset+= dup_field->pack_length; Why do you change all field offsets? You only need to put your field last, that's all. Like while ((dup_field= it++)) set_if_bigger(record_offset, dup_field->offset + dup_field->pack_length) > + } > + cf->offset= record_offset; > + /* > + it.rewind(); > + while ((sql_field= it++)) > + { > + if (!sql_field->stored_in_db()) > + { > + sql_field->offset= record_offset; > + record_offset+= sql_field->pack_length; > + } > + } why is that? > + */ > + /* hash column should be fully hidden */ > + //prepare_create_field(cf, NULL, 0); Forgot to remove it? > + create_list->push_back(cf,thd->mem_root); > +} > + > + > /* > Preparation for table creation > > @@ -3868,6 +3951,8 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO > *create_info, > } > > cols2.rewind(); > + key_part_info->fieldnr= field; > + key_part_info->offset= (uint16) sql_field->offset; why did you need to move that? > if (key->type == Key::FULLTEXT) > { > if ((sql_field->real_field_type() != MYSQL_TYPE_STRING && > @@ -3922,8 +4007,19 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO > *create_info, > column->length= MAX_LEN_GEOM_POINT_FIELD; > if (!column->length) > { > - my_error(ER_BLOB_KEY_WITHOUT_LENGTH, MYF(0), > column->field_name.str); > - DBUG_RETURN(TRUE); > + if (key->type == Key::PRIMARY) > + { > + my_error(ER_BLOB_KEY_WITHOUT_LENGTH, MYF(0), > column->field_name.str); > + DBUG_RETURN(TRUE); > + } > + else if (!is_hash_field_added) > + { > + add_hash_field(thd, &alter_info->create_list, > + create_info->default_table_charset, > + *key_info_buffer, key_number); > + column->length= 0; why column->length= 0 ? > + is_hash_field_added= true; > + } > } > } > #ifdef HAVE_SPATIAL > @@ -4062,11 +4159,29 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO > *create_info, > } > else > { > - my_error(ER_TOO_LONG_KEY, MYF(0), key_part_length); > - DBUG_RETURN(TRUE); > - } > + if(key->type == Key::UNIQUE) > + { > + if (!is_hash_field_added) > + { > + add_hash_field(thd, &alter_info->create_list, > + create_info->default_table_charset, > + *key_info_buffer, key_number); > + is_hash_field_added= true; instead of is_hash_field_added and many add_hash_field() here and there, I'd rather rename the variable to hash_field_needed. And only do hash_field_needed= true. And at the end if (hash_field_needed) add_hash_field(...) > + } > + } > + else > + { > + my_error(ER_TOO_LONG_KEY, MYF(0), key_part_length); any test cases for this error? > + DBUG_RETURN(TRUE); > + } > + } > } > - key_part_info->length= (uint16) key_part_length; > + /* We can not store key_part_length more then 2^16 - 1 in frm > + So we will simply make it zero */ Not really. If someone explicitly asks for a long prefix, it should be an error. Like CREATE TABLE (a blob, UNIQUE (a(65537)); -- this is an error > + if (is_hash_field_added && key_part_length > (2<<16) - 1) > + key_part_info->length= 0; > + else > + key_part_info->length= (uint16) key_part_length; > /* Use packed keys for long strings on the first column */ > if (!((*db_options) & HA_OPTION_NO_PACK_KEYS) && > !((create_info->table_options & HA_OPTION_NO_PACK_KEYS)) && > @@ -4122,12 +4237,37 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO > *create_info, > if (key->type == Key::UNIQUE && !(key_info->flags & HA_NULL_PART_KEY)) > unique_key=1; > key_info->key_length=(uint16) key_length; > - if (key_length > max_key_length && key->type != Key::FULLTEXT) > + if (key_length > max_key_length && key->type != Key::FULLTEXT && > + !is_hash_field_added) > { > my_error(ER_TOO_LONG_KEY,MYF(0),max_key_length); > DBUG_RETURN(TRUE); > } > > + if (is_hash_field_added) > + { > + if (key_info->flags & HA_NULL_PART_KEY) > + null_fields++; > + else > + { > + uint elements= alter_info->create_list.elements; > + Create_field *hash_fld= static_cast<Create_field *>(alter_info-> > + > create_list.elem(elements -1 )); > + hash_fld->flags|= NOT_NULL_FLAG; > + hash_fld->pack_flag&= ~FIELDFLAG_MAYBE_NULL; > + /* > + Althought we do not need default value anywhere in code , but if > we create > + table with non null long columns , then at the time of insert we > get warning. > + So default value is used so solve this warning. > + Virtual_column_info *default_value= new (thd->mem_root) > Virtual_column_info(); > + char * def_str= (char *)alloc_root(thd->mem_root, 2); > + strncpy(def_str, "0", 1); > + default_value->expr_str.str= def_str; > + default_value->expr_str.length= 1; > + default_value->expr_item= new (thd->mem_root) Item_int(thd,0); > + hash_fld->default_value= default_value; */ > + } Forgot to remove it? > + } > if (validate_comment_length(thd, &key->key_create_info.comment, > INDEX_COMMENT_MAXLEN, > ER_TOO_LONG_INDEX_COMMENT, > @@ -8328,6 +8468,11 @@ mysql_prepare_alter_table(THD *thd, TABLE *table, > enum Key::Keytype key_type; > LEX_CSTRING tmp_name; > bzero((char*) &key_create_info, sizeof(key_create_info)); > + if (key_info->flags & HA_LONG_UNIQUE_HASH) > + { > + key_info->flags&= ~(HA_LONG_UNIQUE_HASH); > + key_info->algorithm= HA_KEY_ALG_UNDEF; > + } Why? > key_create_info.algorithm= key_info->algorithm; > /* > We copy block size directly as some engines, like Area, sets this > diff --git a/sql/handler.cc b/sql/handler.cc > index b77b2a3fa2c..cda53dfdc87 100644 > --- a/sql/handler.cc > +++ b/sql/handler.cc > @@ -6179,6 +6185,185 @@ int handler::ha_reset() > DBUG_RETURN(reset()); > } > > +static int check_duplicate_long_entry_key(TABLE *table, handler *h, uchar > *new_rec, > + uint key_no) > +{ > + Field *hash_field; > + int result, error= 0; > + if (!(table->key_info[key_no].user_defined_key_parts == 1 > + && table->key_info[key_no].key_part->field->flags & > LONG_UNIQUE_HASH_FIELD )) > + return 0; What if LONG_UNIQUE_HASH_FIELD is set but user_defined_key_parts != 1? > + hash_field= table->key_info[key_no].key_part->field; > + DBUG_ASSERT((table->key_info[key_no].flags & HA_NULL_PART_KEY && > + table->key_info[key_no].key_length == HA_HASH_KEY_LENGTH_WITH_NULL) > + || table->key_info[key_no].key_length == > HA_HASH_KEY_LENGTH_WITHOUT_NULL); > + uchar ptr[HA_HASH_KEY_LENGTH_WITH_NULL]; > + > + if (hash_field->is_real_null()) > + return 0; > + > + key_copy(ptr, new_rec, &table->key_info[key_no], > + table->key_info[key_no].key_length, false); good, use existing function, no need to reinvent the wheel > + > + if (!table->check_unique_buf) > + table->check_unique_buf= (uchar *)alloc_root(&table->mem_root, > + table->s->reclength*sizeof(uchar)); > + > + result= h->ha_index_init(key_no, 0); > + if (result) > + return result; > + result= h->ha_index_read_map(table->check_unique_buf, > + ptr, HA_WHOLE_KEY, HA_READ_KEY_EXACT); > + if (!result) > + { > + bool is_same; > + do > + { > + Item_func_or_sum * temp= static_cast<Item_func_or_sum *>(hash_field-> > + > vcol_info->expr); > + Item_args * t_item= static_cast<Item_args *>(temp); > + uint arg_count= t_item->argument_count(); > + Item ** arguments= t_item->arguments(); > + long diff= table->check_unique_buf - new_rec; > + Field * t_field; > + is_same= true; > + for (uint j=0; j < arg_count; j++) > + { > + DBUG_ASSERT(arguments[j]->type() == Item::FIELD_ITEM || > + // this one for left(fld_name,length) > + arguments[j]->type() == Item::FUNC_ITEM); > + if (arguments[j]->type() == Item::FIELD_ITEM) > + { > + t_field= static_cast<Item_field *>(arguments[j])->field; > + if (t_field->cmp_offset(diff)) > + is_same= false; > + } > + else > + { > + Item_func_left *fnc= static_cast<Item_func_left *>(arguments[j]); > + DBUG_ASSERT(!my_strcasecmp(system_charset_info, "left", > fnc->func_name())); > + //item_data= fnc->val_str(&tmp1); > + DBUG_ASSERT(fnc->arguments()[0]->type() == Item::FIELD_ITEM); > + t_field= static_cast<Item_field *>(fnc->arguments()[0])->field; > + // field_data= t_field->val_str(&tmp2); > + // if (my_strnncoll(t_field->charset(),(const uchar > *)item_data->ptr(), > + // item_data->length(), > + // (const uchar *)field_data.ptr(), > + // item_data->length())) > + // return 0; > + uint length= fnc->arguments()[1]->val_int(); > + if (t_field->cmp_max(t_field->ptr, t_field->ptr + diff, length)) > + is_same= false; > + } > + } you don't need all that code, use key_cmp() or key_cmp_if_same() from key.cc > + } > + while (!is_same && !(result= > table->file->ha_index_next_same(table->check_unique_buf, > + ptr, table->key_info[key_no].key_length))); > + if (is_same) > + { > + table->dupp_hash_key= key_no; > + error= HA_ERR_FOUND_DUPP_KEY; > + goto exit; > + } > + else > + goto exit; > + } > + if (result == HA_ERR_LOCK_WAIT_TIMEOUT) > + { > + table->dupp_hash_key= key_no; > + //TODO check if this is the only case > + error= HA_ERR_FOUND_DUPP_KEY; Why? > + } > + exit: > + h->ha_index_end(); > + return error; > +} > +/** @brief > + check whether inserted/updated records breaks the > + unique constraint on long columns. > + In the case of update we just need to check the specic key > + reason for that is consider case > + create table t1(a blob , b blob , x blob , y blob ,unique(a,b) > + ,unique(x,y)) > + and update statement like this > + update t1 set a=23+a; in this case if we try to scan for > + whole keys in table then index scan on x_y will return 0 > + because data is same so in the case of update we take > + key as a parameter in normal insert key should be -1 > + @returns 0 if no duplicate else returns error > + */ > +static int check_duplicate_long_entries(TABLE *table, handler *h, uchar > *new_rec) > +{ > + table->dupp_hash_key= -1; > + int result; > + for (uint i= 0; i < table->s->keys; i++) > + { > + if ((result= check_duplicate_long_entry_key(table, h, new_rec, i))) > + return result; > + } > + return 0; > +} > + > +/** @brief > + check whether updated records breaks the > + unique constraint on long columns. > + @returns 0 if no duplicate else returns error > + */ > +static int check_duplicate_long_entries_update(TABLE *table, handler *h, > uchar *new_rec) > +{ > + Field **f, *field; > + Item *h_item; > + int error= 0; > + bool is_update_handler_null= false; > + /* > + Here we are comparing whether new record and old record are same > + with respect to fields in hash_str > + */ > + long reclength= table->record[1]-table->record[0]; > + for (uint i= 0; i < table->s->keys; i++) > + { > + if (table->key_info[i].user_defined_key_parts == 1 && > + table->key_info[i].key_part->field->flags & LONG_UNIQUE_HASH_FIELD) > + { > + /* > + Currently mysql_update is pacthed so that it will automatically set > the > + Update handler and then free it but ha_update_row is used in many > function ( > + like in case of reinsert) Instead of patching them all here we > check is > + update_handler is null then set it And then set it null again > + */ > + if (!table->update_handler) > + { > + create_update_handler(current_thd, table); > + is_update_handler_null= true; > + } > + h_item= table->key_info[i].key_part->field->vcol_info->expr; > + for (f= table->field; f && (field= *f); f++) > + { > + if ( find_field_pos_in_hash(h_item, field->field_name.str) != -1) > + { > + /* Compare fields if they are different then check for duplicates*/ > + if(field->cmp_binary_offset(reclength)) > + { > + if((error= check_duplicate_long_entry_key(table, > table->update_handler, > + new_rec, i))) > + goto exit; > + /* > + break beacuse check_duplicate_long_entrie_key will > + take care of remaning fields > + */ > + break; > + } > + } > + } > + } > + } > + exit: > + if (is_update_handler_null) > + { > + delete_update_handler(current_thd, table); > + } > + return error; > +} > > int handler::ha_write_row(uchar *buf) > { > @@ -6189,14 +6374,21 @@ int handler::ha_write_row(uchar *buf) > DBUG_ENTER("handler::ha_write_row"); > DEBUG_SYNC_C("ha_write_row_start"); > > + setup_table_hash(table); No-no. You cannot modify table structure back and forth for *every inserted row*. It's ok to do it once, when a table is opened. But not for every row, > MYSQL_INSERT_ROW_START(table_share->db.str, table_share->table_name.str); > mark_trx_read_write(); > increment_statistics(&SSV::ha_write_count); > > + if ((error= check_duplicate_long_entries(table, table->file, buf))) > + { > + re_setup_table(table); > + DBUG_RETURN(error); > + } > TABLE_IO_WAIT(tracker, m_psi, PSI_TABLE_WRITE_ROW, MAX_KEY, 0, > { error= write_row(buf); }) > > MYSQL_INSERT_ROW_DONE(error); > + re_setup_table(table); > if (likely(!error) && !row_already_logged) > { > rows_changed++; Regards, Sergei Chief Architect MariaDB and secur...@mariadb.org _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp