We recommend using PostgreSQL rather than modifying tables in MySQL if UTF-8 is needed.

Thanks,
Donna

I have the same problem, but the work around doesn't work. If I create the table before reaching the DB-connection step of the wizard, is says: "The database contains existing data." If I choose "Overwrite existing data" it deletes the table and is unable to create a new one. Is it now impossible to use utf8 databases?

Yes I also notice the problem of REMOTEROLEMEMBERS. The corrected SQL would be.

create table `REMOTEROLEMEMBERS` (`REMOTEROLEDIRECTORYID` bigint not null, `REMOTEROLENAME` varchar(160) not null, `REMOTEPRINCIPALDIRECTORYID` bigint not null, `REMOTEPRINCIPALNAME` varchar(160) not null, primary key (`REMOTEROLEDIRECTORYID`, `REMOTEROLENAME`, `REMOTEPRINCIPALDIRECTORYID`, `REMOTEPRINCIPALNAME`)) ;

Also I noticed two alters that failed as well when installing version 1.0.6, due to the fact that the create failed. I did not notice them when I installed 0.4.5, perhaps they are new for version 1. Please correct me if I am wrong.

alter table `REMOTEGROUPMEMBERS` add index FKF8D0F1005524905F (`REMOTEGROUPDIRECTORYID`, `REMOTEGROUPNAME`), add constraint FKF8D0F1005524905F foreign key (`REMOTEGROUPDIRECTORYID`, `REMOTEGROUPNAME`) references `REMOTEGROUP` (`ID`, `NAME`) ;

alter table `REMOTEGROUPMEMBERS` add index FKF8D0F100F19C38AE (`REMOTEPRINCIPALDIRECTORYID`, `REMOTEPRINCIPALNAME`), add constraint FKF8D0F100F19C38AE foreign key (`REMOTEPRINCIPALDIRECTORYID`, `REMOTEPRINCIPALNAME`) references `REMOTEPRINCIPAL` (`DIRECTORYID`, `NAME`) ;

alter table `REMOTEROLEMEMBERS` add index FK2E5157BD19C82CBC (`REMOTEROLEDIRECTORYID`, `REMOTEROLENAME`), add constraint FK2E5157BD19C82CBC foreign key (`REMOTEROLEDIRECTORYID`, `REMOTEROLENAME`) references `REMOTEROLE` (`ID`, `NAME`) ;

alter table `REMOTEROLEMEMBERS` add index FK2E5157BDF19C38AE (`REMOTEPRINCIPALDIRECTORYID`, `REMOTEPRINCIPALNAME`), add constraint FK2E5157BDF19C38AE foreign key (`REMOTEPRINCIPALDIRECTORYID`, `REMOTEPRINCIPALNAME`) references `REMOTEPRINCIPAL` (`DIRECTORYID`, `NAME`) ;

With version 1.0.2 also the creation of `REMOTEROLEMEMBERS` fails for the same reason. Changing the varchar sizes to 160 helps. No idea about the side effects, though.

Thanks a lot

I had a similar problem using crowd 0.4.5, SUSE SLES 9.3, tomcat 5.5 and MySQL 4.0.18.

REMOTEGROUPMEMBERS was not created. However, I got crowd setup using the above SQL before entering the license

This work around works:

1. Set up as usual into an empty database
2. Before entering the license key, execute the following into the database

   create table `REMOTEGROUPMEMBERS` (`REMOTEGROUPDIRECTORYID` bigint not null, `REMOTEGROUPNAME` varchar(160) not null, `REMOTEPRINCIPALDIRECTORYID` bigint not null, `REMOTEPRINCIPALNAME` varchar(160) not null, primary key (`REMOTEGROUPDIRECTORYID`, `REMOTEGROUPNAME`, `REMOTEPRINCIPALDIRECTORYID`, `REMOTEPRINCIPALNAME`))

This works for me on a unicode MySQL v4.1 database - if you aren't using UTF-8 (and the error says max key length is 255 instead of 1000) you will need to reduce the varchar(160) to varchar(115) instead. So far this hasn't affected my data - but I'm not sure if it will have adverse side effects.

The problem is that in UTF-8 on MySQL v4.1 (and v5.0) chars take up 3 bytes each. That would make each varchar be 765 bytes alone, if you add in the 8 byte bigint's thats a total keysize of 1546 bytes. On MySQL non-unicode databases traditionally have a 255 byte key length, which means that in those cases it would still come to 526.

Changing the varchars to 160 chars each converts to 976 bytes - however that still violates a non-unicode's 255 byte limit with 336 bytes in total.

Changing the varchars to 115 chars each converts to 246 bytes.