Different settings in my.cnf are causing "max key length is 1000 bytes" error

Question

Edit: This may be a 5.0-specific bug. (I'm on 5.0.83). Removing the innodb_log_file_size setting gets rid of the problem. Which makes complete sense. Not.

Googling about finds a handful of similar, but not identical problems in 5.0 that were patched later.

With the "wrong" settings in my.cnf, this create statement on MySQL 5.0 will give me the "Specified key was too long; max key length is 1000 bytes".

CREATE TABLE ReproduceTheProblem (
COLUMN_ONE varchar(200) character set utf8 default NULL,
COLUMN_TWO varchar(200) character set utf8 default NULL,  
KEY ThisKeyBreaks(COLUMN_ONE, COLUMN_TWO)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

In trying to improve performance, I arrived at my.cnf settings that perform well but get the "key was too long" error. I do have my.cnf settings that can create this key, but that perform terribly on importing data into a different, very large table.

The settings that work but perform badly (and which have some whacky values from my attempting to dial different settings in):

[client]
port     = 3306
socket   = /tmp/mysql.sock

[mysqld]
port     = 3306
socket   = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1024M 
table_cache = 256
max_sp_recursion_depth=255
sort_buffer_size = 2M 
read_buffer_size = 2M 
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 30G
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
max_heap_table_size = 9900000
skip-federated
log-bin=mysql-bin
server-id   = 1

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

The settings that perform well, but give me the key length error:

[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
max_allowed_packet = 200M
table_cache = 256
query_cache_type = 1
query_cache_limit = 20M
query_cache_size = 500M
long_query_time = 2
thread_cache_size = 1000
thread_concurrency = 4
innodb_thread_concurrency = 4 
old_passwords = 1
max_connections = 1000
max_sp_recursion_depth = 255
server-id       = 0
innodb_buffer_pool_size = 800M
innodb_additional_mem_pool_size = 50M
innodb_log_file_size=50M
innodb_log_buffer_size = 200M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout =50

[mysqldump]
quick
max_allowed_packet = 200M

[mysql]
no-auto-rehash

Answer 1

I don't think your my.cnf has much to do with this, there must be some other variable.

From the Restrictions on InnoDB Tables:

The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.)

I also note bug 4541 indicates a limit of 1000 bytes, and that there's no plans to simply increase this.

On the performance front, big indexes (and tables) like this will be really really slow. I'd suggest indexing only the prefix of the varchars rather than the whole string.