There are many features that are similar in MySQL and Drizzle. There are also many that are not.
I’ve previously discussed topics like Datatypes and tables , SQL_MODE and SHOW .
A key difference in Drizzle is the definition of utf8 as 4 bytes, not 3 bytes as in MySQL. This combined with no other character sets leads to an impact on the length in keys supported in Innodb.
During a recent test with a client, I was unable to successfully migrated the schema and provide the same schema due to unique indexes defined for utf8 VARHAR(255) fields.
Here is the problem.
mysql> create table t1(c1 int unsigned not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset latin1; Query OK, 0 rows affected (0.05 sec) mysql> create table t2(c1 int unsigned not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset utf8; Query OK, 0 rows affected (0.03 sec) mysql> create table t3(c1 int unsigned not null auto_increment primary key, c2 varchar(256) not null, unique key (c2)) engine=innodb default charset utf8; ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset latin1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'charset latin1' at line 1 drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb; ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
Only a maximum of 191 is now possible.
drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(191) not null, unique key (c2)) engine=innodb; Query OK, 0 rows affected (0.03 sec) drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(192) not null, unique key (c2)) engine=innodb; ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
