Re: [Maria-developers] 4f070b59005: 29 Jan, MDEV-371 Unique indexes for blobs
Hi, Sachin! That's generally pretty good. But I still don't understand what you store in frm and how you manipulate TABLE and TABLE_SHARE key lists. Could you please explain that? On Jan 29, Sachin Setiya wrote:
diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result new file mode 100644 --- /dev/null +++ b/mysql-test/main/long_unique.result @@ -0,0 +1,1392 @@ +#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); +#blob with primary key not allowed +create table t2(a blob,primary key(a(10000))); +ERROR 42000: Specified key was too long; max key length is 1000 bytes +create table t3(a varchar(10000) primary key); +ERROR 42000: Specified key was too long; max key length is 1000 bytes +insert into t1 values(2); +ERROR 23000: Duplicate entry '2' for key 'a' +#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
Good. But this also mean that HASH is a valid index type, and that one can write CREATE TABLE t1 (a blob, unique (a) USING HASH) generally, this should work. And one can write CREATE TABLE t1 (a blob, unique (a) USING BTREE) and this should fail, "key too long; max key length is 1000 bytes" Note, the syntax is already possible. You just need to handle these cases correctly in mysql_prepare_create_table(). And if neither HASH nor BTREE is specified, it's auto-detection, basically. For short keys it's BTREE, for long keys it's HASH. This is already implemented by this patch :)
+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 +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 +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 order by a; +a +NULL +NULL +1 +123456789034567890 +123456789034567891 +2 +3 +56 +maria +sachin +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' +select * from t1 order by a; +a +NULL +NULL +1 +123456789034567890 +123456789034567891 +2 +3 +56 +maria +sachin +insert into t1 values(11),(22),(33); +insert into t1 values(12),(22); +ERROR 23000: Duplicate entry '22' for key 'a' +select * from t1 order by a; +a +NULL +NULL +1 +11 +12 +123456789034567890 +123456789034567891 +2 +22 +3 +33 +56 +maria +sachin +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)) +) 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' +insert into t1 values(45,1,55),(45,1,55); +ERROR 23000: Duplicate entry '45' for key 'a' +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 name; +#this will change index to b tree; +alter table t1 change column a aa blob ;
eh, the comment is wrong. Your old code changed the column to int, that was indeed changing the index to a btree. But now you simply rename the blob. the index is not changed to btree anymore.
+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, + `aa` blob DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`) +) 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 aa A NULL NULL NULL YES HASH +# try to change the blob unique datatype; +#this will change index to b tree; +alter table t1 modify column aa 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, + `aa` int(11) DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`) +) 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 aa 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, + `aa` int(11) DEFAULT NULL, + `clm_changed` blob DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`), + 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 aa A NULL NULL NULL YES BTREE +t1 0 clm 1 clm_changed A NULL NULL NULL YES HASH +#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, + `aa` int(11) DEFAULT NULL, + `clm_changed` blob DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`) +) 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 aa 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 +insert into t1 values ('ae'); +insert into t1 values ('AE'); +ERROR 23000: Duplicate entry 'AE' for key 'a' +insert into t1 values ('Ä'); +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 +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 +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 e 1 e A NULL NULL NULL YES HASH + +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 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 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 +def test t1 0 test c 1 c A NULL NULL NULL YES HASH +def test t1 0 test d 1 d A NULL NULL NULL YES HASH +def test t1 0 test e 1 e A NULL NULL NULL YES HASH +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 order by a; +a b c d e +1 2 3 4 5 +123456789034567890 43545 657567567567 78967657567567 657567567567567676 +123456789034567891 353534 53453453453456 64565464564564 45435345345345 +2 11 22 33 44 +3111 222 333 444 555 +5611 2222 3333 4444 5555 +maria 345 frter dasd utyuty +sachin 341 fdf gfgfgfg hghgr +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 order by a; +a b c d e +1 2 3 4 5 +123456789034567890 43545 657567567567 78967657567567 657567567567567676 +123456789034567891 353534 53453453453456 64565464564564 45435345345345 +2 11 22 33 44 +3111 222 333 444 555 +5611 2222 3333 4444 5555 +maria 345 frter dasd utyuty +sachin 341 fdf gfgfgfg hghgr +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 should not break anything; +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 +t1 0 e 1 e A NULL NULL NULL YES HASH +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 +t1 0 e 1 e A NULL NULL NULL YES HASH +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 +#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 +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 +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 +t1 0 clm2 1 clm_changed2 A NULL NULL NULL YES HASH +#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 +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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 NULL NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 NULL NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 NULL NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 NULL NULL YES HASH + +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 NULL +2 5073 8 multip. ulonglong NULL +3 5065 8 multip. ulonglong 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 +def test t1 0 test a 2 b A NULL NULL NULL YES HASH +def test t1 0 test a 3 c A NULL NULL NULL YES HASH +def test t1 0 test c 1 c A NULL NULL NULL YES HASH +def test t1 0 test c 2 d A 0 NULL NULL YES HASH +def test t1 0 test c 3 e A 0 NULL NULL YES HASH +def test t1 0 test e 1 e A 0 NULL NULL YES HASH +def test t1 0 test e 2 f A 0 NULL NULL YES HASH +def test t1 0 test e 3 g A 0 NULL NULL YES HASH +def test t1 0 test e 4 h A 0 NULL NULL YES HASH +def test t1 0 test b 1 b A 0 NULL NULL YES HASH +def test t1 0 test b 2 d A 0 NULL NULL YES HASH +def test t1 0 test b 3 g A 0 NULL NULL YES HASH +def test t1 0 test b 4 h A 0 NULL NULL YES HASH +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 order by a; +a b c d e f g h +NULL NULL NULL NULL NULL NULL NULL NULL +NULL NULL NULL NULL NULL NULL NULL NULL +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 +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`(65535),`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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +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 `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#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 `a` (`aa`(65535),`bb`,`c`), + UNIQUE KEY `c` (`c`,`dd`(65535),`e`), + UNIQUE KEY `b` (`bb`,`dd`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 aa A NULL 65535 NULL YES HASH +t1 0 a 2 bb A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 dd A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 bb A 0 NULL NULL YES HASH +t1 0 b 2 dd A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +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 `a` (`a`(65535),`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#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 `a` (`a`,`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 NULL NULL YES BTREE +t1 0 a 2 b A NULL NULL NULL YES BTREE +t1 0 a 3 c A NULL NULL NULL YES BTREE +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A NULL 65535 NULL YES HASH +t1 0 c 3 e A NULL NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#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 `a` (`a`,`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 NULL NULL YES HASH +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#try to delete blob column in unique; +truncate table t1; +#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 +t1 0 a 2 b A NULL 65535 NULL YES HASH +t1 0 a 3 c A 0 65535 NULL YES HASH +t1 0 b 1 b A 0 65535 NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +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 order by a; +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 +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 order by a; +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 +t1 0 a 2 c A NULL 65535 NULL YES HASH +t1 0 b 1 b A NULL 65535 NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +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 order by a; +a b +1 19 +2 29 +3 39 +4 49 +select * from t2 order by DB_ROW_HASH_1; +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 +t1 0 b 1 b A NULL NULL NULL YES HASH +t1 0 b 2 c A 0 NULL NULL YES HASH +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 +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), unique(c(4500), d)); +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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 c 1 c A 0 4500 NULL YES HASH +t1 0 c 2 d A 0 NULL NULL YES HASH +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 UNI 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 UNI 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; +#partition +create table t1(a blob unique) partition by hash(a); +ERROR HY000: A BLOB field is not allowed in partition function +#key length > 2^15 -1 +create table t1(a blob, unique(a(100001))); +ERROR 42000: Specified key was too long; max key length is 1000 bytes +set @@GLOBAL.max_allowed_packet= @allowed_packet; diff --git a/mysql-test/main/long_unique_debug.result b/mysql-test/main/long_unique_debug.result new file mode 100644 --- /dev/null +++ b/mysql-test/main/long_unique_debug.result @@ -0,0 +1,32 @@ +#In this test case we will check what will happen in the case of hash collusion
collision, not collusion :)
+SET debug_dbug="d,same_long_unique_hash"; +create table t1(a blob unique); +insert into t1 values('xyz'); +insert into t1 values('abc'); +insert into t1 values('sachin'); +insert into t1 values('sachin'); +ERROR 23000: Duplicate entry 'sachin' for key 'a' +insert into t1 values('maria'); +insert into t1 values('maria'); +ERROR 23000: Duplicate entry 'maria' for key 'a' +drop table t1; +create table t1(a blob unique, b blob unique); +insert into t1 values('xyz', 11); +insert into t1 values('abc', 22); +insert into t1 values('sachin', 1); +insert into t1 values('sachin', 4); +ERROR 23000: Duplicate entry 'sachin' for key 'a' +insert into t1 values('maria', 2); +insert into t1 values('maria', 3); +ERROR 23000: Duplicate entry 'maria' for key 'a' +drop table t1; +create table t1(a blob , b blob , unique(a,b)); +insert into t1 values('xyz', 11); +insert into t1 values('abc', 22); +insert into t1 values('sachin', 1); +insert into t1 values('sachin', 1); +ERROR 23000: Duplicate entry 'sachin-1' for key 'a' +insert into t1 values('maria', 2); +insert into t1 values('maria', 2); +ERROR 23000: Duplicate entry 'maria-2' for key 'a' +drop table t1; diff --git a/mysql-test/main/mdev-504.test b/mysql-test/main/mdev-504.test --- a/mysql-test/main/mdev-504.test +++ b/mysql-test/main/mdev-504.test @@ -44,7 +43,7 @@ while ($trial) --connect (con2,localhost,root,,) --send CALL p_analyze()
- --let $run = 100 + --let $run = 1
Why?
while ($run) { diff --git a/mysql-test/main/type_blob.result b/mysql-test/main/type_blob.result --- a/mysql-test/main/type_blob.result +++ b/mysql-test/main/type_blob.result @@ -370,7 +370,7 @@ a 1 hello 1 drop table t1; create table t1 (a text, unique (a(2100))); -ERROR 42000: Specified key was too long; max key length is 1000 bytes +drop table t1;
or remove the create table instead, it's not a useful test anymore
create table t1 (a text, key (a(2100))); Warnings: Note 1071 Specified key was too long; max key length is 1000 bytes diff --git a/sql/field.h b/sql/field.h --- a/sql/field.h +++ b/sql/field.h @@ -687,7 +687,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};
unused
utype unireg_check; uint32 field_length; // Length of field diff --git a/sql/handler.cc b/sql/handler.cc --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3686,9 +3686,19 @@ void handler::print_error(int error, myf errflag) uint key_nr=get_dup_key(error); if ((int) key_nr >= 0 && key_nr < table->s->keys) { + KEY *long_key= NULL; + if (table->key_info[key_nr].algorithm + == HA_KEY_ALG_LONG_HASH) + { + long_key= table->key_info + key_nr; + setup_keyinfo_hash(long_key); + } print_keydup_error(table, &table->key_info[key_nr], errflag); + if (long_key) + re_setup_keyinfo_hash(long_key);
DBUG_VOID_RETURN; } + table->re_setup_table(); } textno=ER_DUP_KEY; break; @@ -6256,6 +6268,162 @@ 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; + KEY *key_info= table->key_info + key_no; + hash_field= key_info->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; +
remember, you have `key_info= table->key_info + key_no` above :) you don't need to write `table->key_info[key_no]` anymore here
+ key_copy(ptr, new_rec, &table->key_info[key_no], + table->key_info[key_no].key_length, false); + + if (!table->check_unique_buf) + table->check_unique_buf= (uchar *)alloc_root(&table->mem_root, + table->s->reclength*sizeof(uchar));
C99 standard says 6.5.3.4 The sizeof operator When applied to an operand that has type char, unsigned char, or signed char, (or a qualified version thereof) the result is 1. so don't bother with `*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; + Field * t_field; + Item_func_hash * temp= (Item_func_hash *)hash_field->vcol_info->expr; + Item ** arguments= temp->arguments(); + uint arg_count= temp->argument_count(); + do + { + long diff= table->check_unique_buf - new_rec; + is_same= true; + for (uint j=0; j < arg_count; j++)
you can add `is_same && ` here to the for() condition too.
+ { + 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())); + DBUG_ASSERT(fnc->arguments()[0]->type() == Item::FIELD_ITEM); + t_field= static_cast<Item_field *>(fnc->arguments()[0])->field; + uint length= fnc->arguments()[1]->val_int(); + if (t_field->cmp_max(t_field->ptr, t_field->ptr + diff, length)) + is_same= false; + } + } + } + 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 not to keep HA_ERR_LOCK_WAIT_TIMEOUT?
+ } + exit: + h->ha_index_end(); + return error; +}
empty line between functions, please
+/** @brief + check whether inserted records breaks the + unique constraint on long columns. + @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 (table->key_info[i].algorithm == HA_KEY_ALG_LONG_HASH && + (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. + 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_update(TABLE *table, handler *h, uchar *new_rec) +{ + Field *field; + uint key_parts; + int error= 0; + KEY *keyinfo; + KEY_PART_INFO *keypart; + /* + 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]; + if (!table->update_handler) + table->clone_handler_for_update(); + for (uint i= 0; i < table->s->keys; i++) + { + keyinfo= table->key_info + i; + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + { + key_parts= fields_in_hash_keyinfo(keyinfo); + keypart= keyinfo->key_part - key_parts; + for (uint j= 0; j < key_parts; j++, keypart++) + { + field= keypart->field; + /* 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
three typos in two lines :)
+ */ + break; + } + } + } + } + exit: + return error; +}
int handler::ha_write_row(uchar *buf) { diff --git a/sql/item.cc b/sql/item.cc --- a/sql/item.cc +++ b/sql/item.cc @@ -356,7 +356,7 @@ Item::Item(THD *thd): /* Initially this item is not attached to any JOIN_TAB. */ join_tab_idx= MAX_TABLES;
- /* Put item in free list so that we can free all items at end */ + /* Put item in free list so that we can free all items at end only if*/
Huh?
next= thd->free_list; thd->free_list= this; /* diff --git a/sql/item_func.cc b/sql/item_func.cc --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -1718,6 +1718,48 @@ bool Item_func_mod::fix_length_and_dec() DBUG_RETURN(FALSE); }
+inline void calc_hash_for_unique(ulong &nr1, ulong &nr2, String *str)
static inline (or simply static, a compiler will inline it anyway)
+{ + CHARSET_INFO *cs; + uchar l[4]; + int4store(l, str->length()); + cs= str->charset(); + 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); +} + +longlong Item_func_hash::val_int() +{ + DBUG_EXECUTE_IF("same_long_unique_hash", return 9;);
good that you've added tests for collisions!
+ unsigned_flag= true; + ulong nr1= 1,nr2= 4; + CHARSET_INFO *cs; + String * str; + for(uint i= 0;i<arg_count;i++) + { + str = args[i]->val_str(); + if(args[i]->null_value) + { + null_value= 1; + return 0; + } + calc_hash_for_unique(nr1, nr2, str); + } + null_value= 0; + return (longlong)nr1; +} + + +bool Item_func_hash::fix_length_and_dec() +{ + maybe_null= 1;
Why are you forcing maybe_null=1? Most functions are "normal" - they can return NULL if one of the arguments is NULL, otherwise they don't return NULL. And Item_func::fix_fields() sets maybe_null accordingly, it'll be 1 if any of the arguments has maybe_null==1, otherwise maybe_null will be 0. You only need to set maybe_null explicitly if your function is special, for ISNULL(x) # never returns NULL, and NULLIF(x,y) can return NULL when both arguments are not NULL. Looks like in your case the default maybe_null beahavior is correct, though.
+ decimals= 0; + max_length= 8; + return false; +} + +
double Item_func_neg::real_op() { diff --git a/sql/sql_base.cc b/sql/sql_base.cc --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -994,6 +994,8 @@ void close_thread_table(THD *thd, TABLE **table_ptr) table->file->update_global_table_stats(); table->file->update_global_index_stats(); } + if (table->update_handler) + table->delete_update_handler();
you're doing it in sql_update.cc, isn't it enough?
/* This look is needed to allow THD::notify_shared_lock() to diff --git a/sql/sql_show.cc b/sql/sql_show.cc --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2306,7 +2307,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->algorithm == HA_KEY_ALG_LONG_HASH)
I'd think that after setup_table_hash(), HA_NOSAME should be set and you wouldn't need extra checks.
packet->append(STRING_WITH_LEN("UNIQUE KEY ")); else if (key_info->flags & HA_FULLTEXT) packet->append(STRING_WITH_LEN("FULLTEXT KEY ")); @@ -6546,7 +6548,7 @@ static int get_schema_stat_record(THD *thd, TABLE_LIST *tables, else if (!tables->view) { TABLE *show_table= tables->table; - KEY *key_info=show_table->s->key_info; + KEY *key_info=show_table->key_info;
you mean, TABLE_SHARE::key_info shows your HASH definition? and but you change (with setup_table_hash) TABLE::key_info to store the original user's UNIQUE definition? Why is TABLE_SHARE::key_info changed?
if (show_table->file) { show_table->file->info(HA_STATUS_VARIABLE | diff --git a/sql/sql_table.cc b/sql/sql_table.cc --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4106,12 +4177,28 @@ 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_needed) { my_error(ER_TOO_LONG_KEY,MYF(0),max_key_length); DBUG_RETURN(TRUE); }
+ if (is_hash_field_needed) + { + Create_field *hash_fld= add_hash_field(thd, &alter_info->create_list, + create_info->default_table_charset, + key_info); + hash_fld->offset= record_offset; + record_offset+= hash_fld->pack_length; + if (key_info->flags & HA_NULL_PART_KEY) + null_fields++; + else + { + hash_fld->flags|= NOT_NULL_FLAG; + hash_fld->pack_flag&= ~FIELDFLAG_MAYBE_NULL; + } + }
Hmm, haven't we agreed *not* to write the virtual hash field into the frm, not show it to create/alter table and add it automatically when the table is opened?
if (validate_comment_length(thd, &key->key_create_info.comment, INDEX_COMMENT_MAXLEN, ER_TOO_LONG_INDEX_COMMENT, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -10820,6 +10820,12 @@ function_call_conflict: if (unlikely($$ == NULL)) MYSQL_YYABORT; } + | HASH_SYM '(' expr_list ')' + { + $$= new (thd->mem_root)Item_func_hash(thd,*$3); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + }
Haven't we agreed to make this HASH() function fully internal, not visible to the parser?
/* LAST_VALUE here conflicts with the definition for window functions. We have these 2 separate rules to remove the shift/reduce conflict. */ diff --git a/sql/table.cc b/sql/table.cc --- a/sql/table.cc +++ b/sql/table.cc @@ -747,7 +746,7 @@ 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); - n_length=keys * sizeof(KEY) + ext_key_parts * sizeof(KEY_PART_INFO); + n_length=keys * sizeof(KEY) + (ext_key_parts) * sizeof(KEY_PART_INFO);
Another left-over from an intermediate implementation? :)
if (!(keyinfo= (KEY*) alloc_root(&share->mem_root, n_length + len))) return 1; @@ -798,6 +797,12 @@ 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->key_length= HA_HASH_KEY_LENGTH_WITHOUT_NULL; + //Storing key hash + key_part++; + }
I don't understand what you do here
/* Add primary key to end of extended keys for non unique keys for @@ -831,7 +836,9 @@ static bool create_key_infos(const uchar *strpos, const uchar *frm_image_end, if (j == first_key_parts) keyinfo->ext_key_flags= keyinfo->flags | HA_EXT_NOSAME; } - share->ext_key_parts+= keyinfo->ext_key_parts; + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + share->ext_key_parts++; + share->ext_key_parts+= keyinfo->ext_key_parts;
I don't understand what you do here
} keynames=(char*) key_part; strpos+= strnmov(keynames, (char *) strpos, frm_image_end - strpos) - keynames; @@ -3322,6 +3421,36 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
key_part_end= key_part + (share->use_ext_keys ? key_info->ext_key_parts : key_info->user_defined_key_parts) ; + if (key_info->algorithm == HA_KEY_ALG_LONG_HASH) + { + /* + Either it can be first time opening the table share or it can be second time + of more. The difference is when it is first time key_part[0]->fieldnr points + to blob/long field, but when it is 2nd time there will bw always one key_part + and it will point to hash_field. + So in the case of 2nd time we will make key_info->key_part point to start of long + field.
you're overcomplicating here. if you would *always* create your HASH key_def in parse_vcol_defs(), you'll have TABLE_SHARE to be always "like the first time", you simply never modify TABLE_SHARE and always modify TABLEin parse_vcol_defs(). and you won't need setup_keyinfo_hash/setup_table_hash functions either.
+ For example we have unique(a,b,c) + In first share opening key_part will point to a field + but in parse_vcol_defs it will be changed to point to db_row_hash field + in Second or later opening key_part will be pointing to db_row_hash + We will chnage it back to point to field a, because in this way we can create + vcol_info for hash field in parse_vcol_defs. + */ + //Second or more time share opening + key_info->user_defined_key_parts= 0; + key_part_end= key_part; + while(!(share->field[key_part_end->fieldnr -1 ]->flags & LONG_UNIQUE_HASH_FIELD)) + { + key_part_end++; + key_info->user_defined_key_parts++; + } + key_info->usable_key_parts= key_info->ext_key_parts= key_info->user_defined_key_parts; + key_part_end++; + share_keyinfo= share->key_info + key_no; + if (share_keyinfo->key_part->field->flags & LONG_UNIQUE_HASH_FIELD) + share_keyinfo->key_part-= key_info->user_defined_key_parts; + } for ( ; key_part < key_part_end; key_part++) { Field *field= key_part->field= outparam->field[key_part->fieldnr - 1]; @@ -8498,6 +8630,120 @@ 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 expr +*/ +int fields_in_hash_keyinfo(KEY *keyinfo) +{ + Item_func_hash * temp= (Item_func_hash *) + keyinfo->key_part->field->vcol_info->expr; + return temp->argument_count(); +} + +void setup_keyinfo_hash(KEY *key_info) +{ + uint no_of_keyparts= fields_in_hash_keyinfo(key_info); + key_info->key_part-= no_of_keyparts; + key_info->user_defined_key_parts= key_info->usable_key_parts= + key_info->ext_key_parts= no_of_keyparts;
add DBUG_ASSERT to make sure it's your HA_KEY_ALG_LONG_HASH
+} + +void re_setup_keyinfo_hash(KEY *key_info) +{ + while(!(key_info->key_part->field->flags & LONG_UNIQUE_HASH_FIELD)) + key_info->key_part++; + key_info->user_defined_key_parts= key_info->usable_key_parts= + key_info->ext_key_parts= 1; +} +/** + @brief clone of current handler. + Creates a clone of handler used in update for + unique hash key. +*/ +void TABLE::clone_handler_for_update() +{ + handler *update_handler= NULL; + if (!s->long_unique_table) + return; + update_handler= file->clone(s->normalized_path.str, + in_use->mem_root); + update_handler->ha_external_lock(in_use, F_RDLCK); + this->update_handler= update_handler; + return; +} + +/** + @brief Deletes update handler object +*/ +void TABLE::delete_update_handler() +{ + update_handler->ha_external_lock(in_use, F_UNLCK); + update_handler->ha_close(); + delete update_handler; + this->update_handler= NULL; +} +/** + @brief This function makes table object with + long unique keys ready for optimizer and alter table
Looks like a bad function name and a bad explanation. I think - but not sure! - that it restores the original UNIQUE definition, with columns as specified by the user. Is that so?
+ @param table Table object + */ +void TABLE::setup_table_hash() +{ + + if (!this->s->long_unique_table) + return; + KEY *keyinfo= key_info; + for (uint i= 0; i < this->s->keys; i++, keyinfo++) + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + setup_keyinfo_hash(keyinfo); +} + +/** + @brief Revert the effect of setup_table_hash + @param table Table Object + */ +void TABLE::re_setup_table() +{ + if (!s->long_unique_table) + return; + KEY *keyinfo= key_info; + for (uint i= 0; i < s->keys; i++, keyinfo++) + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + re_setup_keyinfo_hash(keyinfo); +} + LEX_CSTRING *fk_option_name(enum_fk_option opt) { static LEX_CSTRING names[]= diff --git a/sql/table.h b/sql/table.h --- a/sql/table.h +++ b/sql/table.h @@ -1105,6 +1120,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 to find + which key is violated. Because we in case of duplicate hash we never reach + handler write_row function. So print_error will always print that + key 0 is violated. We store which key is violated in this variable + by default this should be initialized to -1 + */ + int dupp_hash_key;
couldn't you simply store it in table->file->errkey ?
uchar *write_row_record; /* Used as optimisation in THD::write_row */ uchar *insert_values; /* used by INSERT ... UPDATE */ diff --git a/sql/table_cache.cc b/sql/table_cache.cc --- a/sql/table_cache.cc +++ b/sql/table_cache.cc @@ -822,13 +822,10 @@ TABLE_SHARE *tdc_acquire_share(THD *thd, TABLE_LIST *tl, uint flags,
if (res == -1) DBUG_RETURN(0); - else if (res == 1) - continue;
Why?
element= (TDC_element*) lf_hash_search_using_hash_value(&tdc_hash, thd->tdc_hash_pins, hash_value, (uchar*) key, key_length); lf_hash_search_unpin(thd->tdc_hash_pins); - DBUG_ASSERT(element);
if (!(share= alloc_table_share(tl->db.str, tl->table_name.str, key, key_length))) { diff --git a/storage/myisam/myisamchk.c b/storage/myisam/myisamchk.c --- a/storage/myisam/myisamchk.c +++ b/storage/myisam/myisamchk.c @@ -1333,7 +1333,8 @@ static void descript(HA_CHECK *param, register MI_INFO *info, char * name) key++,keyinfo++) { keyseg=keyinfo->seg; - if (keyinfo->flag & HA_NOSAME) text="unique "; + if (keyinfo->flag & HA_NOSAME) + text="unique ";
I presume you can revert this change, looks like some left-over from when you tried to make myisamchk to recognize your uniques and then later reverted it.
else if (keyinfo->flag & HA_FULLTEXT) text="fulltext "; else text="multip.";
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
On 1/30/19 10:10 PM, Sergei Golubchik wrote: Hi Serg!
Hi, Sachin!
That's generally pretty good. But I still don't understand what you store in frm and how you manipulate TABLE and TABLE_SHARE key lists.
Could you please explain that?
Sure, In this new patch nothing is stored in frm. The only this is algorithm of long unique key will be HA_KEY_ALG_LONG_HASH. IN this new patch TABLE_SHARE->key_info will have key_part as defined by user. while TABLE->key_info will have have only one key_part which is hash_key_part. Lets take an example CREATE TABLE t1 ( a blob, b blob , c blob , UNIQUE(a, b, c)); Total no of key_parts for (in either case of TABLE_SHARE->key_info of TABLE->key_info) will be 4 (a, b, c, hash_key_part) however for TABLE_SHARE->key_info->user_defined_keyparts will be 3, So it can only access a, b, c key_parts. For TABLE ->key_info->user_defined_key_parts will be 1 and it will point to hash_key_part.
On Jan 29, Sachin Setiya wrote:
diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/lon g_unique.result new file mode 100644 --- /dev/null +++ b/mysql-test/main/long_unique.result @@ -0,0 +1,1392 @@ +#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); +#blob with primary key not allowed +create table t2(a blob,primary key(a(10000))); +ERROR 42000: Specified key was too long; max key length is 1000 bytes +create table t3(a varchar(10000) primary key); +ERROR 42000: Specified key was too long; max key length is 1000 bytes +insert into t1 values(2); +ERROR 23000: Duplicate entry '2' for key 'a' +#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 Good. But this also mean that HASH is a valid index type, and that one can write
CREATE TABLE t1 (a blob, unique (a) USING HASH)
generally, this should work. And one can write
CREATE TABLE t1 (a blob, unique (a) USING BTREE)
and this should fail, "key too long; max key length is 1000 bytes" Note, the syntax is already possible. You just need to handle these cases correctly in mysql_prepare_create_table().
+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 +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 +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 order by a; +a +NULL +NULL +1 +123456789034567890 +123456789034567891 +2 +3 +56 +maria +sachin +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' +select * from t1 order by a; +a +NULL +NULL +1 +123456789034567890 +123456789034567891 +2 +3 +56 +maria +sachin +insert into t1 values(11),(22),(33); +insert into t1 values(12),(22); +ERROR 23000: Duplicate entry '22' for key 'a' +select * from t1 order by a; +a +NULL +NULL +1 +11 +12 +123456789034567890 +123456789034567891 +2 +22 +3 +33 +56 +maria +sachin +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)) +) 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' +insert into t1 values(45,1,55),(45,1,55); +ERROR 23000: Duplicate entry '45' for key 'a' +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 name; +#this will change index to b tree; +alter table t1 change column a aa blob ; eh, the comment is wrong. Your old code changed the column to int, that was indeed changing the index to a btree. But now you simply rename the blob.
And if neither HASH nor BTREE is specified, it's auto-detection, basically. For short keys it's BTREE, for long keys it's HASH. This is already implemented by this patch :) Left to be done. the index is not changed to btree anymore. Sorry, Changed.
+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, + `aa` blob DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`) +) 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 aa A NULL NULL NULL YES HASH +# try to change the blob unique datatype; +#this will change index to b tree; +alter table t1 modify column aa 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, + `aa` int(11) DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`) +) 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 aa 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, + `aa` int(11) DEFAULT NULL, + `clm_changed` blob DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`), + 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 aa A NULL NULL NULL YES BTREE +t1 0 clm 1 clm_changed A NULL NULL NULL YES HASH +#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, + `aa` int(11) DEFAULT NULL, + `clm_changed` blob DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`) +) 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 aa 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 +insert into t1 values ('ae'); +insert into t1 values ('AE'); +ERROR 23000: Duplicate entry 'AE' for key 'a' +insert into t1 values ('Ä'); +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 +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 +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 e 1 e A NULL NULL NULL YES HASH + +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 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 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 +def test t1 0 test c 1 c A NULL NULL NULL YES HASH +def test t1 0 test d 1 d A NULL NULL NULL YES HASH +def test t1 0 test e 1 e A NULL NULL NULL YES HASH +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 order by a; +a b c d e +1 2 3 4 5 +123456789034567890 43545 657567567567 78967657567567 657567567567567676 +123456789034567891 353534 53453453453456 64565464564564 45435345345345 +2 11 22 33 44 +3111 222 333 444 555 +5611 2222 3333 4444 5555 +maria 345 frter dasd utyuty +sachin 341 fdf gfgfgfg hghgr +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 order by a; +a b c d e +1 2 3 4 5 +123456789034567890 43545 657567567567 78967657567567 657567567567567676 +123456789034567891 353534 53453453453456 64565464564564 45435345345345 +2 11 22 33 44 +3111 222 333 444 555 +5611 2222 3333 4444 5555 +maria 345 frter dasd utyuty +sachin 341 fdf gfgfgfg hghgr +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 should not break anything; +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 +t1 0 e 1 e A NULL NULL NULL YES HASH +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 +t1 0 e 1 e A NULL NULL NULL YES HASH +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 +#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 +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 +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 +t1 0 clm2 1 clm_changed2 A NULL NULL NULL YES HASH +#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 +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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 NULL NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 NULL NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 NULL NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 NULL NULL YES HASH + +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 NULL +2 5073 8 multip. ulonglong NULL +3 5065 8 multip. ulonglong 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 +def test t1 0 test a 2 b A NULL NULL NULL YES HASH +def test t1 0 test a 3 c A NULL NULL NULL YES HASH +def test t1 0 test c 1 c A NULL NULL NULL YES HASH +def test t1 0 test c 2 d A 0 NULL NULL YES HASH +def test t1 0 test c 3 e A 0 NULL NULL YES HASH +def test t1 0 test e 1 e A 0 NULL NULL YES HASH +def test t1 0 test e 2 f A 0 NULL NULL YES HASH +def test t1 0 test e 3 g A 0 NULL NULL YES HASH +def test t1 0 test e 4 h A 0 NULL NULL YES HASH +def test t1 0 test b 1 b A 0 NULL NULL YES HASH +def test t1 0 test b 2 d A 0 NULL NULL YES HASH +def test t1 0 test b 3 g A 0 NULL NULL YES HASH +def test t1 0 test b 4 h A 0 NULL NULL YES HASH +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 order by a; +a b c d e f g h +NULL NULL NULL NULL NULL NULL NULL NULL +NULL NULL NULL NULL NULL NULL NULL NULL +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 +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`(65535),`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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +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 `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#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 `a` (`aa`(65535),`bb`,`c`), + UNIQUE KEY `c` (`c`,`dd`(65535),`e`), + UNIQUE KEY `b` (`bb`,`dd`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 aa A NULL 65535 NULL YES HASH +t1 0 a 2 bb A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 dd A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 bb A 0 NULL NULL YES HASH +t1 0 b 2 dd A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +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 `a` (`a`(65535),`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#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 `a` (`a`,`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 NULL NULL YES BTREE +t1 0 a 2 b A NULL NULL NULL YES BTREE +t1 0 a 3 c A NULL NULL NULL YES BTREE +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A NULL 65535 NULL YES HASH +t1 0 c 3 e A NULL NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#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 `a` (`a`,`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 NULL NULL YES HASH +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#try to delete blob column in unique; +truncate table t1; +#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 +t1 0 a 2 b A NULL 65535 NULL YES HASH +t1 0 a 3 c A 0 65535 NULL YES HASH +t1 0 b 1 b A 0 65535 NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +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 order by a; +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 +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 order by a; +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 +t1 0 a 2 c A NULL 65535 NULL YES HASH +t1 0 b 1 b A NULL 65535 NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +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 order by a; +a b +1 19 +2 29 +3 39 +4 49 +select * from t2 order by DB_ROW_HASH_1; +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 +t1 0 b 1 b A NULL NULL NULL YES HASH +t1 0 b 2 c A 0 NULL NULL YES HASH +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 +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), unique(c(4500), d)); +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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 c 1 c A 0 4500 NULL YES HASH +t1 0 c 2 d A 0 NULL NULL YES HASH +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 UNI 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 UNI 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; +#partition +create table t1(a blob unique) partition by hash(a); +ERROR HY000: A BLOB field is not allowed in partition function +#key length > 2^15 -1 +create table t1(a blob, unique(a(100001))); +ERROR 42000: Specified key was too long; max key length is 1000 bytes +set @@GLOBAL.max_allowed_packet= @allowed_packet; diff --git a/mysql-test/main/long_unique_debug.result b/mysql-test/main/long_unique_debug.result new file mode 100644 --- /dev/null +++ b/mysql-test/main/long_unique_debug.result @@ -0,0 +1,32 @@ +#In this test case we will check what will happen in the case of hash collusion
collision, not collusion :) Sorry , Changed. > +SET debug_dbug="d,same_long_unique_hash"; > +create table t1(a blob unique); > +insert into t1 values('xyz'); > +insert into t1 values('abc'); > +insert into t1 values('sachin'); > +insert into t1 values('sachin'); > +ERROR 23000: Duplicate entry 'sachin' for key 'a' > +insert into t1 values('maria'); > +insert into t1 values('maria'); > +ERROR 23000: Duplicate entry 'maria' for key 'a' > +drop table t1; > +create table t1(a blob unique, b blob unique); > +insert into t1 values('xyz', 11); > +insert into t1 values('abc', 22); > +insert into t1 values('sachin', 1); > +insert into t1 values('sachin', 4); > +ERROR 23000: Duplicate entry 'sachin' for key 'a' > +insert into t1 values('maria', 2); > +insert into t1 values('maria', 3); > +ERROR 23000: Duplicate entry 'maria' for key 'a' > +drop table t1; > +create table t1(a blob , b blob , unique(a,b)); > +insert into t1 values('xyz', 11); > +insert into t1 values('abc', 22); > +insert into t1 values('sachin', 1); > +insert into t1 values('sachin', 1); > +ERROR 23000: Duplicate entry 'sachin-1' for key 'a' > +insert into t1 values('maria', 2); > +insert into t1 values('maria', 2); > +ERROR 23000: Duplicate entry 'maria-2' for key 'a' > +drop table t1; > diff --git a/mysql-test/main/mdev-504.test b/mysql-test/main/mdev-504.test > --- a/mysql-test/main/mdev-504.test > +++ b/mysql-test/main/mdev-504.test > @@ -44,7 +43,7 @@ while ($trial) > --connect (con2,localhost,root,,) > --send CALL p_analyze() > > - --let $run = 100 > + --let $run = 1 Why? This was a test failure , I forgot to change it. > > while ($run) > { > diff --git a/mysql-test/main/type_blob.result b/mysql-test/main/type_blob.result > --- a/mysql-test/main/type_blob.result > +++ b/mysql-test/main/type_blob.result > @@ -370,7 +370,7 @@ a 1 > hello 1 > drop table t1; > create table t1 (a text, unique (a(2100))); > -ERROR 42000: Specified key was too long; max key length is 1000 bytes > +drop table t1; or remove the create table instead, it's not a useful test anymore Removed. > create table t1 (a text, key (a(2100))); > Warnings: > Note 1071 Specified key was too long; max key length is 1000 bytes > diff --git a/sql/field.h b/sql/field.h > --- a/sql/field.h > +++ b/sql/field.h > @@ -687,7 +687,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}; unused Sorry, I have this optimizer patch , and sometime I mix up things. Removed. > > utype unireg_check; > uint32 field_length; // Length of field > diff --git a/sql/handler.cc b/sql/handler.cc > --- a/sql/handler.cc > +++ b/sql/handler.cc > @@ -3686,9 +3686,19 @@ void handler::print_error(int error, myf errflag) > uint key_nr=get_dup_key(error); > if ((int) key_nr >= 0 && key_nr < table->s->keys) > { > + KEY *long_key= NULL; > + if (table->key_info[key_nr].algorithm > + == HA_KEY_ALG_LONG_HASH) > + { > + long_key= table->key_info + key_nr; > + setup_keyinfo_hash(long_key); > + } > print_keydup_error(table, &table->key_info[key_nr], errflag); > + if (long_key) > + re_setup_keyinfo_hash(long_key); > DBUG_VOID_RETURN; > } > + table->re_setup_table(); > } > textno=ER_DUP_KEY; > break; > @@ -6256,6 +6268,162 @@ 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; > + KEY *key_info= table->key_info + key_no; > + hash_field= key_info->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; > + remember, you have `key_info= table->key_info + key_no` above :) you don't need to write `table->key_info[key_no]` anymore here Right , Thanks! > + key_copy(ptr, new_rec, &table->key_info[key_no], > + table->key_info[key_no].key_length, false); > + > + if (!table->check_unique_buf) > + table->check_unique_buf= (uchar *)alloc_root(&table->mem_root, > + table->s->reclength*sizeof(uchar)); C99 standard says
6.5.3.4 The sizeof operator
When applied to an operand that has type char, unsigned char, or signed char, (or a qualified version thereof) the result is 1.
+ + 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; + Field * t_field; + Item_func_hash * temp= (Item_func_hash *)hash_field->vcol_info->expr; + Item ** arguments= temp->arguments(); + uint arg_count= temp->argument_count(); + do + { + long diff= table->check_unique_buf - new_rec; + is_same= true; + for (uint j=0; j < arg_count; j++) you can add `is_same && ` here to the for() condition too. Done. + { + 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())); + DBUG_ASSERT(fnc->arguments()[0]->type() == Item::FIELD_ITEM); + t_field= static_cast<Item_field *>(fnc->arguments()[0])->field; + uint length= fnc->arguments()[1]->val_int(); + if (t_field->cmp_max(t_field->ptr, t_field->ptr + diff, length)) + is_same= false; + } + } + } + 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 not to keep HA_ERR_LOCK_WAIT_TIMEOUT? Because we get HA_ERR_LOCK_WAIT_TIMEOUT only in the case of HA_ERR_FOUND_DUPP_KEY + } + exit: + h->ha_index_end(); + return error; +} empty line between functions, please Done +/** @brief + check whether inserted records breaks the + unique constraint on long columns. + @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 (table->key_info[i].algorithm == HA_KEY_ALG_LONG_HASH && + (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. + 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_update(TABLE *table, handler *h, uchar *new_rec) +{ + Field *field; + uint key_parts; + int error= 0; + KEY *keyinfo; + KEY_PART_INFO *keypart; + /* + 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]; + if (!table->update_handler) + table->clone_handler_for_update(); + for (uint i= 0; i < table->s->keys; i++) + { + keyinfo= table->key_info + i; + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + { + key_parts= fields_in_hash_keyinfo(keyinfo); + keypart= keyinfo->key_part - key_parts; + for (uint j= 0; j < key_parts; j++, keypart++) + { + field= keypart->field; + /* 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
so don't bother with `*sizeof(uchar)` Changed. three typos in two lines :) Sorry
+ */ + break; + } + } + } + } + exit: + return error; +}
int handler::ha_write_row(uchar *buf) { diff --git a/sql/item.cc b/sql/item.cc --- a/sql/item.cc +++ b/sql/item.cc @@ -356,7 +356,7 @@ Item::Item(THD *thd): /* Initially this item is not attached to any JOIN_TAB. */ join_tab_idx= MAX_TABLES;
- /* Put item in free list so that we can free all items at end */ + /* Put item in free list so that we can free all items at end only if*/ Huh? Even I dont know , why I did this , reverted. next= thd->free_list; thd->free_list= this; /* diff --git a/sql/item_func.cc b/sql/item_func.cc --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -1718,6 +1718,48 @@ bool Item_func_mod::fix_length_and_dec() DBUG_RETURN(FALSE); }
+inline void calc_hash_for_unique(ulong &nr1, ulong &nr2, String *str) static inline (or simply static, a compiler will inline it anyway)
Done
+{ + CHARSET_INFO *cs; + uchar l[4]; + int4store(l, str->length()); + cs= str->charset(); + 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); +} + +longlong Item_func_hash::val_int() +{ + DBUG_EXECUTE_IF("same_long_unique_hash", return 9;); good that you've added tests for collisions!
+ unsigned_flag= true; + ulong nr1= 1,nr2= 4; + CHARSET_INFO *cs; + String * str; + for(uint i= 0;i<arg_count;i++) + { + str = args[i]->val_str(); + if(args[i]->null_value) + { + null_value= 1; + return 0; + } + calc_hash_for_unique(nr1, nr2, str); + } + null_value= 0; + return (longlong)nr1; +} + + +bool Item_func_hash::fix_length_and_dec() +{ + maybe_null= 1; Why are you forcing maybe_null=1?
Most functions are "normal" - they can return NULL if one of the arguments is NULL, otherwise they don't return NULL. And Item_func::fix_fields() sets maybe_null accordingly, it'll be 1 if any of the arguments has maybe_null==1, otherwise maybe_null will be 0.
You only need to set maybe_null explicitly if your function is special, for ISNULL(x) # never returns NULL, and NULLIF(x,y) can return NULL when both arguments are not NULL.
Looks like in your case the default maybe_null beahavior is correct, though. Actually I did not know this, So I removed this line
+ decimals= 0; + max_length= 8; + return false; +} + +
double Item_func_neg::real_op() { diff --git a/sql/sql_base.cc b/sql/sql_base.cc --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -994,6 +994,8 @@ void close_thread_table(THD *thd, TABLE **table_ptr) table->file->update_global_table_stats(); table->file->update_global_index_stats(); } + if (table->update_handler) + table->delete_update_handler(); you're doing it in sql_update.cc, isn't it enough?
Removed , I added it later because I was getting mem leaks but now no longer the case.
/* This look is needed to allow THD::notify_shared_lock() to diff --git a/sql/sql_show.cc b/sql/sql_show.cc --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2306,7 +2307,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->algorithm == HA_KEY_ALG_LONG_HASH)
I'd think that after setup_table_hash(), HA_NOSAME should be set and you wouldn't need extra checks. Actually I removed setup_table_hash and resetup_table because I dont need it any more , table ->s->key_info has HA_NOSAME flag
packet->append(STRING_WITH_LEN("UNIQUE KEY ")); else if (key_info->flags & HA_FULLTEXT) packet->append(STRING_WITH_LEN("FULLTEXT KEY ")); @@ -6546,7 +6548,7 @@ static int get_schema_stat_record(THD *thd, TABLE_LIST *tables, else if (!tables->view) { TABLE *show_table= tables->table; - KEY *key_info=show_table->s->key_info; + KEY *key_info=show_table->key_info;HA_ERR_FOUND_DUPP_KEY
you mean, TABLE_SHARE::key_info shows your HASH definition? and but you change (with setup_table_hash) TABLE::key_info to store the original user's UNIQUE definition?
Why is TABLE_SHARE::key_info changed? I guess this was remain of intermediate patch. This is no longer the case.
if (show_table->file) { show_table->file->info(HA_STATUS_VARIABLE | diff --git a/sql/sql_table.cc b/sql/sql_table.cc --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4106,12 +4177,28 @@ 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_needed) { my_error(ER_TOO_LONG_KEY,MYF(0),max_key_length); DBUG_RETURN(TRUE); }
+ if (is_hash_field_needed) + { + Create_field *hash_fld= add_hash_field(thd, &alter_info->create_list, + create_info->default_table_charset, + key_info); + hash_fld->offset= record_offset; + record_offset+= hash_fld->pack_length; + if (key_info->flags & HA_NULL_PART_KEY) + null_fields++; + else + { + hash_fld->flags|= NOT_NULL_FLAG; + hash_fld->pack_flag&= ~FIELDFLAG_MAYBE_NULL; + } + }
Hmm, haven't we agreed *not* to write the virtual hash field into the frm, not show it to create/alter table and add it automatically when the table is opened? Changed. Now new field is created in init_from_binary_frm_image
if (validate_comment_length(thd, &key->key_create_info.comment, INDEX_COMMENT_MAXLEN, ER_TOO_LONG_INDEX_COMMENT, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -10820,6 +10820,12 @@ function_call_conflict: if (unlikely($$ == NULL)) MYSQL_YYABORT; } + | HASH_SYM '(' expr_list ')' + { + $$= new (thd->mem_root)Item_func_hash(thd,*$3); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + }
Haven't we agreed to make this HASH() function fully internal, not visible to the parser? Sorry , I no longer use this logic in parse_vcol_defs , I forgot to remove it
/* LAST_VALUE here conflicts with the definition for window functions. We have these 2 separate rules to remove the shift/reduce conflict. */ diff --git a/sql/table.cc b/sql/table.cc --- a/sql/table.cc +++ b/sql/table.cc @@ -747,7 +746,7 @@ 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); - n_length=keys * sizeof(KEY) + ext_key_parts * sizeof(KEY_PART_INFO); + n_length=keys * sizeof(KEY) + (ext_key_parts) * sizeof(KEY_PART_INFO);
Another left-over from an intermediate implementation? :)
if (!(keyinfo= (KEY*) alloc_root(&share->mem_root, n_length + len))) return 1; @@ -798,6 +797,12 @@ 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->key_length= HA_HASH_KEY_LENGTH_WITHOUT_NULL; + //Storing key hash + key_part++; + }
I don't understand what you do here We need one extra key_part for long hash key info.
/* Add primary key to end of extended keys for non unique keys for @@ -831,7 +836,9 @@ static bool create_key_infos(const uchar *strpos, const uchar *frm_image_end, if (j == first_key_parts) keyinfo->ext_key_flags= keyinfo->flags | HA_EXT_NOSAME; } - share->ext_key_parts+= keyinfo->ext_key_parts; + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + share->ext_key_parts++; + share->ext_key_parts+= keyinfo->ext_key_parts;
I don't understand what you do here We need one extra key_part for long hash key info.
} keynames=(char*) key_part; strpos+= strnmov(keynames, (char *) strpos, frm_image_end - strpos) - keynames; @@ -3322,6 +3421,36 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
key_part_end= key_part + (share->use_ext_keys ? key_info->ext_key_parts : key_info->user_defined_key_parts) ; + if (key_info->algorithm == HA_KEY_ALG_LONG_HASH) + { + /* + Either it can be first time opening the table share or it can be second time + of more. The difference is when it is first time key_part[0]->fieldnr points + to blob/long field, but when it is 2nd time there will bw always one key_part + and it will point to hash_field. + So in the case of 2nd time we will make key_info->key_part point to start of long + field.
you're overcomplicating here.
if you would *always* create your HASH key_def in parse_vcol_defs(), you'll have TABLE_SHARE to be always "like the first time", you simply never modify TABLE_SHARE and always modify TABLEin parse_vcol_defs().
and you won't need setup_keyinfo_hash/setup_table_hash functions either.
Right. , Changed. I need to have setup_keyinfo_hash function , it is used in optimizer patch and it is also used in printing error.
+ For example we have unique(a,b,c) + In first share opening key_part will point to a field + but in parse_vcol_defs it will be changed to point to db_row_hash field + in Second or later opening key_part will be pointing to db_row_hash + We will chnage it back to point to field a, because in this way we can create + vcol_info for hash field in parse_vcol_defs. + */ + //Second or more time share opening + key_info->user_defined_key_parts= 0; + key_part_end= key_part; + while(!(share->field[key_part_end->fieldnr -1 ]->flags & LONG_UNIQUE_HASH_FIELD)) + { + key_part_end++; + key_info->user_defined_key_parts++; + } + key_info->usable_key_parts= key_info->ext_key_parts= key_info->user_defined_key_parts; + key_part_end++; + share_keyinfo= share->key_info + key_no; + if (share_keyinfo->key_part->field->flags & LONG_UNIQUE_HASH_FIELD) + share_keyinfo->key_part-= key_info->user_defined_key_parts; + } for ( ; key_part < key_part_end; key_part++) { Field *field= key_part->field= outparam->field[key_part->fieldnr - 1]; @@ -8498,6 +8630,120 @@ 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 expr +*/ +int fields_in_hash_keyinfo(KEY *keyinfo) +{ + Item_func_hash * temp= (Item_func_hash *) + keyinfo->key_part->field->vcol_info->expr; + return temp->argument_count(); +} + +void setup_keyinfo_hash(KEY *key_info) +{ + uint no_of_keyparts= fields_in_hash_keyinfo(key_info); + key_info->key_part-= no_of_keyparts; + key_info->user_defined_key_parts= key_info->usable_key_parts= + key_info->ext_key_parts= no_of_keyparts; add DBUG_ASSERT to make sure it's your HA_KEY_ALG_LONG_HASH Done. +} + +void re_setup_keyinfo_hash(KEY *key_info) +{ + while(!(key_info->key_part->field->flags & LONG_UNIQUE_HASH_FIELD)) + key_info->key_part++; + key_info->user_defined_key_parts= key_info->usable_key_parts= + key_info->ext_key_parts= 1; +} +/** + @brief clone of current handler. + Creates a clone of handler used in update for + unique hash key. +*/ +void TABLE::clone_handler_for_update() +{ + handler *update_handler= NULL; + if (!s->long_unique_table) + return; + update_handler= file->clone(s->normalized_path.str, + in_use->mem_root); + update_handler->ha_external_lock(in_use, F_RDLCK); + this->update_handler= update_handler; + return; +} + +/** + @brief Deletes update handler object +*/ +void TABLE::delete_update_handler() +{ + update_handler->ha_external_lock(in_use, F_UNLCK); + update_handler->ha_close(); + delete update_handler; + this->update_handler= NULL; +} +/** + @brief This function makes table object with + long unique keys ready for optimizer and alter table Looks like a bad function name and a bad explanation. I think - but not sure! - that it restores the original UNIQUE definition, with columns as specified by the user. Is that so?
+ @param table Table object + */ +void TABLE::setup_table_hash() +{ + + if (!this->s->long_unique_table) + return; + KEY *keyinfo= key_info; + for (uint i= 0; i < this->s->keys; i++, keyinfo++) + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + setup_keyinfo_hash(keyinfo); +} + +/** + @brief Revert the effect of setup_table_hash + @param table Table Object + */ +void TABLE::re_setup_table() +{ + if (!s->long_unique_table) + return; + KEY *keyinfo= key_info; + for (uint i= 0; i < s->keys; i++, keyinfo++) + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + re_setup_keyinfo_hash(keyinfo); +} + LEX_CSTRING *fk_option_name(enum_fk_option opt) { static LEX_CSTRING names[]= diff --git a/sql/table.h b/sql/table.h --- a/sql/table.h +++ b/sql/table.h @@ -1105,6 +1120,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 to find + which key is violated. Because we in case of duplicate hash we never reach + handler write_row function. So print_error will always print that + key 0 is violated. We store which key is violated in this variable + by default this should be initialized to -1 + */ + int dupp_hash_key; couldn't you simply store it in table->file->errkey ? Changed , Now I am using table->file->errkey uchar *write_row_record; /* Used as optimisation in THD::write_row */ uchar *insert_values; /* used by INSERT ... UPDATE */ diff --git a/sql/table_cache.cc b/sql/table_cache.cc --- a/sql/table_cache.cc +++ b/sql/table_cache.cc @@ -822,13 +822,10 @@ TABLE_SHARE *tdc_acquire_share(THD *thd, TABLE_LIST *tl, uint flags,
if (res == -1) DBUG_RETURN(0); - else if (res == 1) - continue; Why? Actually there was one test case failing , so I did this to simulate
Actually i was not able to come up with better name setup_keyinfo_hash changes the key_info to be same as defined by user re_setup_keyinfo_hash reverts setup_keyinfo_hash this. Reverted.
element= (TDC_element*) lf_hash_search_using_hash_value(&tdc_hash, thd->tdc_hash_pins, hash_value, (uchar*) key, key_length); lf_hash_search_unpin(thd->tdc_hash_pins); - DBUG_ASSERT(element);
if (!(share= alloc_table_share(tl->db.str, tl->table_name.str, key, key_length))) { diff --git a/storage/myisam/myisamchk.c b/storage/myisam/myisamchk.c --- a/storage/myisam/myisamchk.c +++ b/storage/myisam/myisamchk.c @@ -1333,7 +1333,8 @@ static void descript(HA_CHECK *param, register MI_INFO *info, char * name) key++,keyinfo++) { keyseg=keyinfo->seg; - if (keyinfo->flag & HA_NOSAME) text="unique "; + if (keyinfo->flag & HA_NOSAME) + text="unique ";
I presume you can revert this change, looks like some left-over from when you tried to make myisamchk to recognize your uniques and then later reverted it. Right , Changed.
else if (keyinfo->flag & HA_FULLTEXT) text="fulltext "; else text="multip.";
Regards, Sergei Chief Architect MariaDB andsecurity@mariadb.org
_______________________________________________ Mailing list:https://launchpad.net/~maria-developers Post to :maria-developers@lists.launchpad.net Unsubscribe :https://launchpad.net/~maria-developers More help :https://help.launchpad.net/ListHelp
Hi Serg! On 2/12/19 3:19 PM, sachin wrote:
On 1/30/19 10:10 PM, Sergei Golubchik wrote: Hi Serg!
Hi, Sachin!
That's generally pretty good. But I still don't understand what you store in frm and how you manipulate TABLE and TABLE_SHARE key lists.
Could you please explain that?
Sure, In this new patch nothing is stored in frm. The only this is algorithm of long unique key will be HA_KEY_ALG_LONG_HASH.
IN this new patch TABLE_SHARE->key_info will have key_part as defined by user. while TABLE->key_info will have have only one key_part which is hash_key_part.
Lets take an example
CREATE TABLE t1 ( a blob, b blob , c blob , UNIQUE(a, b, c));
Total no of key_parts for (in either case of TABLE_SHARE->key_info of TABLE->key_info) will be 4 (a, b, c, hash_key_part)
however for TABLE_SHARE->key_info->user_defined_keyparts will be 3, So it can only access a, b, c key_parts.
For TABLE ->key_info->user_defined_key_parts will be 1 and it will point to hash_key_part.
On Jan 29, Sachin Setiya wrote:
diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/lon g_unique.result new file mode 100644 --- /dev/null +++ b/mysql-test/main/long_unique.result @@ -0,0 +1,1392 @@ +#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); +#blob with primary key not allowed +create table t2(a blob,primary key(a(10000))); +ERROR 42000: Specified key was too long; max key length is 1000 bytes +create table t3(a varchar(10000) primary key); +ERROR 42000: Specified key was too long; max key length is 1000 bytes +insert into t1 values(2); +ERROR 23000: Duplicate entry '2' for key 'a' +#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 Good. But this also mean that HASH is a valid index type, and that one can write
CREATE TABLE t1 (a blob, unique (a) USING HASH)
generally, this should work. And one can write
CREATE TABLE t1 (a blob, unique (a) USING BTREE)
and this should fail, "key too long; max key length is 1000 bytes" Note, the syntax is already possible. You just need to handle these cases correctly in mysql_prepare_create_table().
And if neither HASH nor BTREE is specified, it's auto-detection, basically. For short keys it's BTREE, for long keys it's HASH. This is already implemented by this patch :) Left to be done.
Done in latest patch.
+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 +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 +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 order by a; +a +NULL +NULL +1 +123456789034567890 +123456789034567891 +2 +3 +56 +maria +sachin +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' +select * from t1 order by a; +a +NULL +NULL +1 +123456789034567890 +123456789034567891 +2 +3 +56 +maria +sachin +insert into t1 values(11),(22),(33); +insert into t1 values(12),(22); +ERROR 23000: Duplicate entry '22' for key 'a' +select * from t1 order by a; +a +NULL +NULL +1 +11 +12 +123456789034567890 +123456789034567891 +2 +22 +3 +33 +56 +maria +sachin +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)) +) 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' +insert into t1 values(45,1,55),(45,1,55); +ERROR 23000: Duplicate entry '45' for key 'a' +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 name; +#this will change index to b tree; +alter table t1 change column a aa blob ; eh, the comment is wrong. Your old code changed the column to int, that was indeed changing the index to a btree. But now you simply rename the blob. the index is not changed to btree anymore. Sorry, Changed. +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, + `aa` blob DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`) +) 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 aa A NULL NULL NULL YES HASH +# try to change the blob unique datatype; +#this will change index to b tree; +alter table t1 modify column aa 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, + `aa` int(11) DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`) +) 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 aa 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, + `aa` int(11) DEFAULT NULL, + `clm_changed` blob DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`), + 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 aa A NULL NULL NULL YES BTREE +t1 0 clm 1 clm_changed A NULL NULL NULL YES HASH +#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, + `aa` int(11) DEFAULT NULL, + `clm_changed` blob DEFAULT NULL, + UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), + UNIQUE KEY `a` (`aa`) +) 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 aa 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 +insert into t1 values ('ae'); +insert into t1 values ('AE'); +ERROR 23000: Duplicate entry 'AE' for key 'a' +insert into t1 values ('Ä'); +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 +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 +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 e 1 e A NULL NULL NULL YES HASH + +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 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 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 +def test t1 0 test c 1 c A NULL NULL NULL YES HASH +def test t1 0 test d 1 d A NULL NULL NULL YES HASH +def test t1 0 test e 1 e A NULL NULL NULL YES HASH +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 order by a; +a b c d e +1 2 3 4 5 +123456789034567890 43545 657567567567 78967657567567 657567567567567676 +123456789034567891 353534 53453453453456 64565464564564 45435345345345 +2 11 22 33 44 +3111 222 333 444 555 +5611 2222 3333 4444 5555 +maria 345 frter dasd utyuty +sachin 341 fdf gfgfgfg hghgr +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 order by a; +a b c d e +1 2 3 4 5 +123456789034567890 43545 657567567567 78967657567567 657567567567567676 +123456789034567891 353534 53453453453456 64565464564564 45435345345345 +2 11 22 33 44 +3111 222 333 444 555 +5611 2222 3333 4444 5555 +maria 345 frter dasd utyuty +sachin 341 fdf gfgfgfg hghgr +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 should not break anything; +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 +t1 0 e 1 e A NULL NULL NULL YES HASH +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 +t1 0 e 1 e A NULL NULL NULL YES HASH +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 +#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 +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 +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 +t1 0 clm2 1 clm_changed2 A NULL NULL NULL YES HASH +#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 +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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 NULL NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 NULL NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 NULL NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 NULL NULL YES HASH + +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 NULL +2 5073 8 multip. ulonglong NULL +3 5065 8 multip. ulonglong 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 +def test t1 0 test a 2 b A NULL NULL NULL YES HASH +def test t1 0 test a 3 c A NULL NULL NULL YES HASH +def test t1 0 test c 1 c A NULL NULL NULL YES HASH +def test t1 0 test c 2 d A 0 NULL NULL YES HASH +def test t1 0 test c 3 e A 0 NULL NULL YES HASH +def test t1 0 test e 1 e A 0 NULL NULL YES HASH +def test t1 0 test e 2 f A 0 NULL NULL YES HASH +def test t1 0 test e 3 g A 0 NULL NULL YES HASH +def test t1 0 test e 4 h A 0 NULL NULL YES HASH +def test t1 0 test b 1 b A 0 NULL NULL YES HASH +def test t1 0 test b 2 d A 0 NULL NULL YES HASH +def test t1 0 test b 3 g A 0 NULL NULL YES HASH +def test t1 0 test b 4 h A 0 NULL NULL YES HASH +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 order by a; +a b c d e f g h +NULL NULL NULL NULL NULL NULL NULL NULL +NULL NULL NULL NULL NULL NULL NULL NULL +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 +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`(65535),`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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +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 `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#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 `a` (`aa`(65535),`bb`,`c`), + UNIQUE KEY `c` (`c`,`dd`(65535),`e`), + UNIQUE KEY `b` (`bb`,`dd`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 aa A NULL 65535 NULL YES HASH +t1 0 a 2 bb A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 dd A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 bb A 0 NULL NULL YES HASH +t1 0 b 2 dd A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +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 `a` (`a`(65535),`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#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 `a` (`a`,`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 NULL NULL YES BTREE +t1 0 a 2 b A NULL NULL NULL YES BTREE +t1 0 a 3 c A NULL NULL NULL YES BTREE +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A NULL 65535 NULL YES HASH +t1 0 c 3 e A NULL NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#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 `a` (`a`,`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(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 NULL NULL YES HASH +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 a 3 c A NULL NULL NULL YES HASH +t1 0 c 1 c A NULL NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH +t1 0 c 3 e A 0 NULL NULL YES HASH +t1 0 b 1 b A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH +#try to delete blob column in unique; +truncate table t1; +#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 +t1 0 a 2 b A NULL 65535 NULL YES HASH +t1 0 a 3 c A 0 65535 NULL YES HASH +t1 0 b 1 b A 0 65535 NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +t1 0 b 3 g A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +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 order by a; +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 +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 order by a; +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 +t1 0 a 2 c A NULL 65535 NULL YES HASH +t1 0 b 1 b A NULL 65535 NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH +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 order by a; +a b +1 19 +2 29 +3 39 +4 49 +select * from t2 order by DB_ROW_HASH_1; +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 +t1 0 b 1 b A NULL NULL NULL YES HASH +t1 0 b 2 c A 0 NULL NULL YES HASH +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 +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), unique(c(4500), d)); +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 +t1 0 a 2 b A NULL NULL NULL YES HASH +t1 0 c 1 c A 0 4500 NULL YES HASH +t1 0 c 2 d A 0 NULL NULL YES HASH +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 UNI 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 UNI 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; +#partition +create table t1(a blob unique) partition by hash(a); +ERROR HY000: A BLOB field is not allowed in partition function +#key length > 2^15 -1 +create table t1(a blob, unique(a(100001))); +ERROR 42000: Specified key was too long; max key length is 1000 bytes +set @@GLOBAL.max_allowed_packet= @allowed_packet; diff --git a/mysql-test/main/long_unique_debug.result b/mysql-test/main/long_unique_debug.result new file mode 100644 --- /dev/null +++ b/mysql-test/main/long_unique_debug.result @@ -0,0 +1,32 @@ +#In this test case we will check what will happen in the case of hash collusion
collision, not collusion :) Sorry , Changed. > +SET debug_dbug="d,same_long_unique_hash"; > +create table t1(a blob unique); > +insert into t1 values('xyz'); > +insert into t1 values('abc'); > +insert into t1 values('sachin'); > +insert into t1 values('sachin'); > +ERROR 23000: Duplicate entry 'sachin' for key 'a' > +insert into t1 values('maria'); > +insert into t1 values('maria'); > +ERROR 23000: Duplicate entry 'maria' for key 'a' > +drop table t1; > +create table t1(a blob unique, b blob unique); > +insert into t1 values('xyz', 11); > +insert into t1 values('abc', 22); > +insert into t1 values('sachin', 1); > +insert into t1 values('sachin', 4); > +ERROR 23000: Duplicate entry 'sachin' for key 'a' > +insert into t1 values('maria', 2); > +insert into t1 values('maria', 3); > +ERROR 23000: Duplicate entry 'maria' for key 'a' > +drop table t1; > +create table t1(a blob , b blob , unique(a,b)); > +insert into t1 values('xyz', 11); > +insert into t1 values('abc', 22); > +insert into t1 values('sachin', 1); > +insert into t1 values('sachin', 1); > +ERROR 23000: Duplicate entry 'sachin-1' for key 'a' > +insert into t1 values('maria', 2); > +insert into t1 values('maria', 2); > +ERROR 23000: Duplicate entry 'maria-2' for key 'a' > +drop table t1; > diff --git a/mysql-test/main/mdev-504.test > b/mysql-test/main/mdev-504.test > --- a/mysql-test/main/mdev-504.test > +++ b/mysql-test/main/mdev-504.test > @@ -44,7 +43,7 @@ while ($trial) > --connect (con2,localhost,root,,) > --send CALL p_analyze() > - --let $run = 100 > + --let $run = 1 Why? This was a test failure , I forgot to change it. > while ($run) > { > diff --git a/mysql-test/main/type_blob.result > b/mysql-test/main/type_blob.result > --- a/mysql-test/main/type_blob.result > +++ b/mysql-test/main/type_blob.result > @@ -370,7 +370,7 @@ a 1 > hello 1 > drop table t1; > create table t1 (a text, unique (a(2100))); > -ERROR 42000: Specified key was too long; max key length is 1000 bytes > +drop table t1; or remove the create table instead, it's not a useful test anymore Removed. > create table t1 (a text, key (a(2100))); > Warnings: > Note 1071 Specified key was too long; max key length is 1000 > bytes > diff --git a/sql/field.h b/sql/field.h > --- a/sql/field.h > +++ b/sql/field.h > @@ -687,7 +687,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}; unused Sorry, I have this optimizer patch , and sometime I mix up things. Removed. > utype unireg_check; > uint32 field_length; // Length of field > diff --git a/sql/handler.cc b/sql/handler.cc > --- a/sql/handler.cc > +++ b/sql/handler.cc > @@ -3686,9 +3686,19 @@ void handler::print_error(int error, myf > errflag) > uint key_nr=get_dup_key(error); > if ((int) key_nr >= 0 && key_nr < table->s->keys) > { > + KEY *long_key= NULL; > + if (table->key_info[key_nr].algorithm > + == HA_KEY_ALG_LONG_HASH) > + { > + long_key= table->key_info + key_nr; > + setup_keyinfo_hash(long_key); > + } > print_keydup_error(table, &table->key_info[key_nr], errflag); > + if (long_key) > + re_setup_keyinfo_hash(long_key); > DBUG_VOID_RETURN; > } > + table->re_setup_table(); > } > textno=ER_DUP_KEY; > break; > @@ -6256,6 +6268,162 @@ 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; > + KEY *key_info= table->key_info + key_no; > + hash_field= key_info->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; > + remember, you have `key_info= table->key_info + key_no` above :) you don't need to write `table->key_info[key_no]` anymore here Right , Thanks! > + key_copy(ptr, new_rec, &table->key_info[key_no], > + table->key_info[key_no].key_length, false); > + > + if (!table->check_unique_buf) > + table->check_unique_buf= (uchar *)alloc_root(&table->mem_root, > + table->s->reclength*sizeof(uchar)); C99 standard says
6.5.3.4 The sizeof operator
When applied to an operand that has type char, unsigned char, or signed char, (or a qualified version thereof) the result is 1.
+ + 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; + Field * t_field; + Item_func_hash * temp= (Item_func_hash *)hash_field->vcol_info->expr; + Item ** arguments= temp->arguments(); + uint arg_count= temp->argument_count(); + do + { + long diff= table->check_unique_buf - new_rec; + is_same= true; + for (uint j=0; j < arg_count; j++) you can add `is_same && ` here to the for() condition too. Done. + { + 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())); + DBUG_ASSERT(fnc->arguments()[0]->type() == Item::FIELD_ITEM); + t_field= static_cast<Item_field *>(fnc->arguments()[0])->field; + uint length= fnc->arguments()[1]->val_int(); + if (t_field->cmp_max(t_field->ptr, t_field->ptr + diff, length)) + is_same= false; + } + } + } + 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 not to keep HA_ERR_LOCK_WAIT_TIMEOUT? Because we get HA_ERR_LOCK_WAIT_TIMEOUT only in the case of HA_ERR_FOUND_DUPP_KEY + } + exit: + h->ha_index_end(); + return error; +} empty line between functions, please Done +/** @brief + check whether inserted records breaks the + unique constraint on long columns. + @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 (table->key_info[i].algorithm == HA_KEY_ALG_LONG_HASH && + (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. + 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_update(TABLE *table, handler *h, uchar *new_rec) +{ + Field *field; + uint key_parts; + int error= 0; + KEY *keyinfo; + KEY_PART_INFO *keypart; + /* + 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]; + if (!table->update_handler) + table->clone_handler_for_update(); + for (uint i= 0; i < table->s->keys; i++) + { + keyinfo= table->key_info + i; + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + { + key_parts= fields_in_hash_keyinfo(keyinfo); + keypart= keyinfo->key_part - key_parts; + for (uint j= 0; j < key_parts; j++, keypart++) + { + field= keypart->field; + /* 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
so don't bother with `*sizeof(uchar)` Changed. three typos in two lines :) Sorry
+ */ + break; + } + } + } + } + exit: + return error; +} int handler::ha_write_row(uchar *buf) { diff --git a/sql/item.cc b/sql/item.cc --- a/sql/item.cc +++ b/sql/item.cc @@ -356,7 +356,7 @@ Item::Item(THD *thd): /* Initially this item is not attached to any JOIN_TAB. */ join_tab_idx= MAX_TABLES; - /* Put item in free list so that we can free all items at end */ + /* Put item in free list so that we can free all items at end only if*/ Huh? Even I dont know , why I did this , reverted. next= thd->free_list; thd->free_list= this; /* diff --git a/sql/item_func.cc b/sql/item_func.cc --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -1718,6 +1718,48 @@ bool Item_func_mod::fix_length_and_dec() DBUG_RETURN(FALSE); } +inline void calc_hash_for_unique(ulong &nr1, ulong &nr2, String *str) static inline (or simply static, a compiler will inline it anyway)
Done
+{ + CHARSET_INFO *cs; + uchar l[4]; + int4store(l, str->length()); + cs= str->charset(); + 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); +} + +longlong Item_func_hash::val_int() +{ + DBUG_EXECUTE_IF("same_long_unique_hash", return 9;); good that you've added tests for collisions!
+ unsigned_flag= true; + ulong nr1= 1,nr2= 4; + CHARSET_INFO *cs; + String * str; + for(uint i= 0;i<arg_count;i++) + { + str = args[i]->val_str(); + if(args[i]->null_value) + { + null_value= 1; + return 0; + } + calc_hash_for_unique(nr1, nr2, str); + } + null_value= 0; + return (longlong)nr1; +} + + +bool Item_func_hash::fix_length_and_dec() +{ + maybe_null= 1; Why are you forcing maybe_null=1?
Most functions are "normal" - they can return NULL if one of the arguments is NULL, otherwise they don't return NULL. And Item_func::fix_fields() sets maybe_null accordingly, it'll be 1 if any of the arguments has maybe_null==1, otherwise maybe_null will be 0.
You only need to set maybe_null explicitly if your function is special, for ISNULL(x) # never returns NULL, and NULLIF(x,y) can return NULL when both arguments are not NULL.
Looks like in your case the default maybe_null beahavior is correct, though. Actually I did not know this, So I removed this line
+ decimals= 0; + max_length= 8; + return false; +} + + double Item_func_neg::real_op() { diff --git a/sql/sql_base.cc b/sql/sql_base.cc --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -994,6 +994,8 @@ void close_thread_table(THD *thd, TABLE **table_ptr) table->file->update_global_table_stats(); table->file->update_global_index_stats(); } + if (table->update_handler) + table->delete_update_handler(); you're doing it in sql_update.cc, isn't it enough?
Removed , I added it later because I was getting mem leaks but now no longer the case.
/* This look is needed to allow THD::notify_shared_lock() to diff --git a/sql/sql_show.cc b/sql/sql_show.cc --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2306,7 +2307,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->algorithm == HA_KEY_ALG_LONG_HASH) I'd think that after setup_table_hash(), HA_NOSAME should be set and you wouldn't need extra checks. Actually I removed setup_table_hash and resetup_table because I dont need it any more , table ->s->key_info has HA_NOSAME flag packet->append(STRING_WITH_LEN("UNIQUE KEY ")); else if (key_info->flags & HA_FULLTEXT) packet->append(STRING_WITH_LEN("FULLTEXT KEY ")); @@ -6546,7 +6548,7 @@ static int get_schema_stat_record(THD *thd, TABLE_LIST *tables, else if (!tables->view) { TABLE *show_table= tables->table; - KEY *key_info=show_table->s->key_info; + KEY *key_info=show_table->key_info;HA_ERR_FOUND_DUPP_KEY you mean, TABLE_SHARE::key_info shows your HASH definition? and but you change (with setup_table_hash) TABLE::key_info to store the original user's UNIQUE definition?
Why is TABLE_SHARE::key_info changed? I guess this was remain of intermediate patch. This is no longer the case.
if (show_table->file) { show_table->file->info(HA_STATUS_VARIABLE | diff --git a/sql/sql_table.cc b/sql/sql_table.cc --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4106,12 +4177,28 @@ 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_needed) { my_error(ER_TOO_LONG_KEY,MYF(0),max_key_length); DBUG_RETURN(TRUE); } + if (is_hash_field_needed) + { + Create_field *hash_fld= add_hash_field(thd, &alter_info->create_list, + create_info->default_table_charset, + key_info); + hash_fld->offset= record_offset; + record_offset+= hash_fld->pack_length; + if (key_info->flags & HA_NULL_PART_KEY) + null_fields++; + else + { + hash_fld->flags|= NOT_NULL_FLAG; + hash_fld->pack_flag&= ~FIELDFLAG_MAYBE_NULL; + } + } Hmm, haven't we agreed *not* to write the virtual hash field into the frm, not show it to create/alter table and add it automatically when the table is opened? Changed. Now new field is created in init_from_binary_frm_image if (validate_comment_length(thd, &key->key_create_info.comment, INDEX_COMMENT_MAXLEN, ER_TOO_LONG_INDEX_COMMENT, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -10820,6 +10820,12 @@ function_call_conflict: if (unlikely($$ == NULL)) MYSQL_YYABORT; } + | HASH_SYM '(' expr_list ')' + { + $$= new (thd->mem_root)Item_func_hash(thd,*$3); + if (unlikely($$ == NULL)) + MYSQL_YYABORT; + } Haven't we agreed to make this HASH() function fully internal, not visible to the parser? Sorry , I no longer use this logic in parse_vcol_defs , I forgot to remove it /* LAST_VALUE here conflicts with the definition for window functions. We have these 2 separate rules to remove the shift/reduce conflict. */ diff --git a/sql/table.cc b/sql/table.cc --- a/sql/table.cc +++ b/sql/table.cc @@ -747,7 +746,7 @@ 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); - n_length=keys * sizeof(KEY) + ext_key_parts * sizeof(KEY_PART_INFO); + n_length=keys * sizeof(KEY) + (ext_key_parts) * sizeof(KEY_PART_INFO); Another left-over from an intermediate implementation? :)
if (!(keyinfo= (KEY*) alloc_root(&share->mem_root, n_length + len))) return 1; @@ -798,6 +797,12 @@ 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->key_length= HA_HASH_KEY_LENGTH_WITHOUT_NULL; + //Storing key hash + key_part++; + } I don't understand what you do here We need one extra key_part for long hash key info. /* Add primary key to end of extended keys for non unique keys for @@ -831,7 +836,9 @@ static bool create_key_infos(const uchar *strpos, const uchar *frm_image_end, if (j == first_key_parts) keyinfo->ext_key_flags= keyinfo->flags | HA_EXT_NOSAME; } - share->ext_key_parts+= keyinfo->ext_key_parts; + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + share->ext_key_parts++; + share->ext_key_parts+= keyinfo->ext_key_parts; I don't understand what you do here We need one extra key_part for long hash key info. } keynames=(char*) key_part; strpos+= strnmov(keynames, (char *) strpos, frm_image_end - strpos) - keynames; @@ -3322,6 +3421,36 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share, key_part_end= key_part + (share->use_ext_keys ? key_info->ext_key_parts : key_info->user_defined_key_parts) ; + if (key_info->algorithm == HA_KEY_ALG_LONG_HASH) + { + /* + Either it can be first time opening the table share or it can be second time + of more. The difference is when it is first time key_part[0]->fieldnr points + to blob/long field, but when it is 2nd time there will bw always one key_part + and it will point to hash_field. + So in the case of 2nd time we will make key_info->key_part point to start of long + field. you're overcomplicating here.
if you would *always* create your HASH key_def in parse_vcol_defs(), you'll have TABLE_SHARE to be always "like the first time", you simply never modify TABLE_SHARE and always modify TABLEin parse_vcol_defs().
and you won't need setup_keyinfo_hash/setup_table_hash functions either.
Right. , Changed.
I need to have setup_keyinfo_hash function , it is used in optimizer patch and it is also used in printing error.
+ For example we have unique(a,b,c) + In first share opening key_part will point to a field + but in parse_vcol_defs it will be changed to point to db_row_hash field + in Second or later opening key_part will be pointing to db_row_hash + We will chnage it back to point to field a, because in this way we can create + vcol_info for hash field in parse_vcol_defs. + */ + //Second or more time share opening + key_info->user_defined_key_parts= 0; + key_part_end= key_part; + while(!(share->field[key_part_end->fieldnr -1 ]->flags & LONG_UNIQUE_HASH_FIELD)) + { + key_part_end++; + key_info->user_defined_key_parts++; + } + key_info->usable_key_parts= key_info->ext_key_parts= key_info->user_defined_key_parts; + key_part_end++; + share_keyinfo= share->key_info + key_no; + if (share_keyinfo->key_part->field->flags & LONG_UNIQUE_HASH_FIELD) + share_keyinfo->key_part-= key_info->user_defined_key_parts; + } for ( ; key_part < key_part_end; key_part++) { Field *field= key_part->field= outparam->field[key_part->fieldnr - 1]; @@ -8498,6 +8630,120 @@ 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 expr +*/ +int fields_in_hash_keyinfo(KEY *keyinfo) +{ + Item_func_hash * temp= (Item_func_hash *) + keyinfo->key_part->field->vcol_info->expr; + return temp->argument_count(); +} + +void setup_keyinfo_hash(KEY *key_info) +{ + uint no_of_keyparts= fields_in_hash_keyinfo(key_info); + key_info->key_part-= no_of_keyparts; + key_info->user_defined_key_parts= key_info->usable_key_parts= + key_info->ext_key_parts= no_of_keyparts; add DBUG_ASSERT to make sure it's your HA_KEY_ALG_LONG_HASH Done. +} + +void re_setup_keyinfo_hash(KEY *key_info) +{ + while(!(key_info->key_part->field->flags & LONG_UNIQUE_HASH_FIELD)) + key_info->key_part++; + key_info->user_defined_key_parts= key_info->usable_key_parts= + key_info->ext_key_parts= 1; +} +/** + @brief clone of current handler. + Creates a clone of handler used in update for + unique hash key. +*/ +void TABLE::clone_handler_for_update() +{ + handler *update_handler= NULL; + if (!s->long_unique_table) + return; + update_handler= file->clone(s->normalized_path.str, + in_use->mem_root); + update_handler->ha_external_lock(in_use, F_RDLCK); + this->update_handler= update_handler; + return; +} + +/** + @brief Deletes update handler object +*/ +void TABLE::delete_update_handler() +{ + update_handler->ha_external_lock(in_use, F_UNLCK); + update_handler->ha_close(); + delete update_handler; + this->update_handler= NULL; +} +/** + @brief This function makes table object with + long unique keys ready for optimizer and alter table Looks like a bad function name and a bad explanation. I think - but not sure! - that it restores the original UNIQUE definition, with columns as specified by the user. Is that so?
Actually i was not able to come up with better name
setup_keyinfo_hash changes the key_info to be same as defined by user
re_setup_keyinfo_hash reverts setup_keyinfo_hash
+ @param table Table object + */ +void TABLE::setup_table_hash() +{ + + if (!this->s->long_unique_table) + return; + KEY *keyinfo= key_info; + for (uint i= 0; i < this->s->keys; i++, keyinfo++) + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + setup_keyinfo_hash(keyinfo); +} + +/** + @brief Revert the effect of setup_table_hash + @param table Table Object + */ +void TABLE::re_setup_table() +{ + if (!s->long_unique_table) + return; + KEY *keyinfo= key_info; + for (uint i= 0; i < s->keys; i++, keyinfo++) + if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH) + re_setup_keyinfo_hash(keyinfo); +} + LEX_CSTRING *fk_option_name(enum_fk_option opt) { static LEX_CSTRING names[]= diff --git a/sql/table.h b/sql/table.h --- a/sql/table.h +++ b/sql/table.h @@ -1105,6 +1120,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 to find + which key is violated. Because we in case of duplicate hash we never reach + handler write_row function. So print_error will always print that + key 0 is violated. We store which key is violated in this variable + by default this should be initialized to -1 + */ + int dupp_hash_key; couldn't you simply store it in table->file->errkey ? Changed , Now I am using table->file->errkey uchar *write_row_record; /* Used as optimisation in THD::write_row */ uchar *insert_values; /* used by INSERT ... UPDATE */ diff --git a/sql/table_cache.cc b/sql/table_cache.cc --- a/sql/table_cache.cc +++ b/sql/table_cache.cc @@ -822,13 +822,10 @@ TABLE_SHARE *tdc_acquire_share(THD *thd, TABLE_LIST *tl, uint flags, if (res == -1) DBUG_RETURN(0); - else if (res == 1) - continue; Why? Actually there was one test case failing , so I did this to simulate this. Reverted. element= (TDC_element*) lf_hash_search_using_hash_value(&tdc_hash, thd->tdc_hash_pins, hash_value, (uchar*) key, key_length); lf_hash_search_unpin(thd->tdc_hash_pins); - DBUG_ASSERT(element); if (!(share= alloc_table_share(tl->db.str, tl->table_name.str, key, key_length))) { diff --git a/storage/myisam/myisamchk.c b/storage/myisam/myisamchk.c --- a/storage/myisam/myisamchk.c +++ b/storage/myisam/myisamchk.c @@ -1333,7 +1333,8 @@ static void descript(HA_CHECK *param, register MI_INFO *info, char * name) key++,keyinfo++) { keyseg=keyinfo->seg; - if (keyinfo->flag & HA_NOSAME) text="unique "; + if (keyinfo->flag & HA_NOSAME) + text="unique "; I presume you can revert this change, looks like some left-over from when you tried to make myisamchk to recognize your uniques and then later reverted it. Right , Changed. else if (keyinfo->flag & HA_FULLTEXT) text="fulltext "; else text="multip.";
Regards, Sergei Chief Architect MariaDB andsecurity@mariadb.org
_______________________________________________ Mailing list:https://launchpad.net/~maria-developers Post to :maria-developers@lists.launchpad.net Unsubscribe :https://launchpad.net/~maria-developers More help :https://help.launchpad.net/ListHelp
participants (2)
-
sachin -
Sergei Golubchik