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 security@mariadb.org