[Dovecot] expire: mysql table creation error

[John Fawcett]

Don't know if anyone found this already (couldn't see anything in the
archives about it) but maybe this is useful to someone else who finds
the same problem.

When following the instructions for setting up expire on v1.2+ for mysql
backend at http://wiki.dovecot.org/Plugins/Expire I got the following error:

mysql> CREATE TABLE expires (
    ->   username varchar(100) not null,
    ->   mailbox varchar(255) not null,
    ->   expire_stamp integer not null,
    ->   primary key (username, mailbox)
    -> );
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

that's because it's a utf8 database where every char is 3 bytes so a 355
char index is 1065 bytes.

it worked ok with the following modification:
mysql> CREATE TABLE expires (  
    ->   username varchar(100) not null,
    ->   mailbox varchar(255) not null,
    ->   expire_stamp integer not null,
    ->   primary key (username, mailbox(200))
    ->   );
Query OK, 0 rows affected (0.00 sec)

but maybe that's not such a good primary key,
probably better to do:

mysql> CREATE TABLE expires (  
    ->   id integer not null auto_increment,
    ->   username varchar(100) not null,  
    ->   mailbox varchar(255) not null,  
    ->   expire_stamp integer not null,  
    ->   primary key (id),
    ->   key (username, mailbox(200))
    ->   );
Query OK, 0 rows affected (0.00 sec)

John