#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
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   unique  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   unique  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 ;
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   unique  ulonglong NULL       
2   3055  8   unique  ulonglong NULL       
3   3047  8   unique  ulonglong NULL       
4   3039  8   unique  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   unique  ulonglong NULL       
2   5073  8   unique  ulonglong NULL       
3   5065  8   unique  ulonglong NULL       
4   5057  8   unique  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	PRI	NULL	
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` blob NOT NULL,
  UNIQUE KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values(1);
insert into t1 values(3);
insert into t1 values(1);
ERROR 23000: Duplicate entry '1' for key 'a'
drop table t1;
create table t1(a int primary key, b blob unique , c blob unique not null);
insert into t1 values(1,1,1);
insert into t1 values(2,1,2);
ERROR 23000: Duplicate entry '1' for key 'b'
insert into t1 values(3,3,1);
ERROR 23000: Duplicate entry '1' for key 'c'
drop table t1;
create table t1 (a blob unique not null, b blob not null, c blob not null, unique(b,c));
desc t1;
Field	Type	Null	Key	Default	Extra
a	blob	NO	PRI	NULL	
b	blob	NO	MUL	NULL	
c	blob	NO		NULL	
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` blob NOT NULL,
  `b` blob NOT NULL,
  `c` blob NOT NULL,
  UNIQUE KEY `a` (`a`),
  UNIQUE KEY `b` (`b`,`c`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values (1, 2, 3);
insert into t1 values (2, 1, 3);
insert into t1 values (2, 1, 3);
ERROR 23000: Duplicate entry '2' for key 'a'
drop table t1;
#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;