MySQL specified key was too long problem

There is a continuing issue with the msyql database that causes a conflict with dcm4chee.   Mysql indexing does not support a total index character length greater than 1000 bytes and the UTF-8 character set needs 3 bytes per character.  The dcm4chee database has several tables where the total bytes of the indexed fields is greater than 1000.  This causes the sqlscripts to throw a "Specified key was too long; max key length is 1000 bytes" error.  This can be fixed in two ways.

 First (Recommended):

 Change the default character set for the dcm4chee database within mysql to latin_1.  The following command will create a database called pacsdb with a latin_1 character set

mysql -u root -p
CREATE  DATABASE  `pacsdb`  DEFAULT  CHARACTER  SET latin1 COLLATE latin1_bin;
GRANT ALL on pacsdb.* to 'pacs'@'localhost' identified by 'pacs';
quit
mysql -u root -p pacsdb < /DCM4CHEE_ROOT/sql/create.mysql

 The first command enters a mysql console (you'll need to enter the root password).  The second creates the database.  The third gives a user called pacs connecting from localhost all rights to the pacsdb database when using the password 'pacs', and the forth leaves the console.  The fith line runs the sql contained in create.mysql on thepacsdb database.

Second Option (Not Recommended):

You should not have to use these options to fix the problem.  Change the length of the varchars to 64 in the create.mysql file.  We've already done this and attached a new create.mysql file to this page (see attachments). Using this method has obvious drawbacks.  A third option, which I'll include here, is to remove the index marker for some of the fields in each table so that the total length of the indexed fields and the primary key are less than 1000 bytes.  You'll need to remember to mulitply each varchar length by 3 before adding the length to the total.  Though this will not have any effect on data it will have an detramental effect on the performance of dcm4chee.  Hopefully our friends working on the MySQL project will extend the maximum length of the indexed fields in future versions.