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
On Thu, 2009-05-28 at 08:04 +0200, John Fawcett wrote:> 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)Nothing uses the id, so I don't really see a point in having primary key at all then. You could instead create a unique index on (username, mailbox), unless MySQL disallows that too. -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 197 bytes Desc: This is a digitally signed message part URL: <http://dovecot.org/pipermail/dovecot/attachments/20090528/de8a3e5e/attachment-0002.bin>
Timo Sirainen wrote:> On Thu, 2009-05-28 at 08:04 +0200, John Fawcett wrote: > >> 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) >> > > Nothing uses the id, so I don't really see a point in having primary key > at all then. You could instead create a unique index on (username, > mailbox), unless MySQL disallows that too. > >yes it works without the primary key. It is best to not have a unique index otherwise there will be insert failures for (the unlikely event) of mailboxes for the same user with mailbox names longer than 200 chars and the first 200 chars identical. A non unique index is fine. CREATE TABLE expires_test ( username varchar(100) not null, mailbox varchar(255) not null, expire_stamp integer not null, key (username, mailbox(200)) );