#35 MySQL error "Specified key was too long" upon installation

open
nobody
None
5
2007-03-06
2007-03-06
Anonymous
No

Windows XP SP2
MySQL 5.0.20a-nt
PHP 5.1.2
phpMyLibrary 2.2.1-3

Upon installation step 2, MySQL reported an error:
Specified key was too long; max key length is 1000 bytes
when creating the pml_patron table.

I did a little bit for web search, and it turned out that the problem is that my MySQL's default encoding is set to utf8. MySQL somehow hardcoded the vchar-to-byte conversion ratio to 3 for utf8. This essentially "blows" most of the keys on pml_* tables to a very big size.

Since our library contains books that are not in western languages, we do need the utf8 support. Is there any way to work around this problem?

Discussion

  • If you can open the SQL file for the sample data (installation/sql/phpmylibrary.sql), you can update all instances of "TYPE=MyISAM" to the following, and installation will go through will little/no troubles. I had the same error, updated those lines and it installed fine for me. Depending on your MySQL version and setup, you may still have other problems though. Good luck!

    Change
    TYPE=MyISAM
    to
    TYPE=MyISAM, DEFAULT CHARACTER SET latin1;