Fresh install of 3.4.5 gives a lot of "SQL error: Specified key was too long; max key length is ..." errors

[pixelpeter_merged]

Doing a fresh install on a Debian squeeze with MySQL 5.5.31-30.3 Percona Server.

Charset for database "test-ipb" is UTF-8.

Is doesn't change anything if I'm using MyISAM oder InnoDB tables;

I always get a "Specified key was too long;" error.

Changed some tables definition to this:

$TABLE[] = "CREATE TABLE backup_vars (
  backup_var_key    VARCHAR(255) NOT NULL DEFAULT '',
  backup_var_value  TEXT,
  PRIMARY KEY (backup_var_key(160))
);";

but this is very time-consuming and after 6 tables I stopped because it's tiresome and I don't know how many tables I have to modifiy :-(

I only found a old topic from 2009 regarding this issue.

How can I manage to get this cleanly iand flawlessly installed ?

Thanks for your help

[AndyF]

I *think* (assuming your own server) the problem might be key_buffer_size in mysql's config my.ini.

I'm aware of an older bug but iirc it was a mysql4 concern so would not really apply.

Here's what I have but its my localhost setup (my live host is shared so I don't have access) I'm not sure if its going to be any help but it works well for me with mysql5.5.24

port        = 3306
socket        = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 2M
table_open_cache = 256
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
thread_cache_size = 16
query_cache_size= 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 12

[pixelpeter_merged]

This box is optimzed for Magento so I have failry large values here:

key_buffer = 512M
max_allowed_packet = 64M
table_cache = 512
sort_buffer_size = 4m
read_buffer_size = 4m
read_rnd_buffer_size = 2m
myisam_sort_buffer_size = 64m
tmp_table_size = 128m
query_cache_size = 96m
query_cache_type = 1
thread_cache_size = 8
max_connections = 400
wait_timeout = 300

Anyhow thanks for your tip

[AndyF]

You're welcome.

I'm not sure what else to suggest as its more a server issue so not really my area. I'll move your topic to Server Management forum where "those in the know" are more likely to see it.

[pixelpeter_merged]

So I tried it on a different box with CentOS and MySQL 5.5.12.

On this box it worked without any problems.

Think I need to have a look at my Debian box again

[Cimme]

Hi,

I have the same Problem and the key-buffer-size is set to 1G and the Webhost uses PCI-SSDs for MySQL.

Are there any other suggestions?

Thanks a lot.

Marcus

[Grumpy]

Hi,

I have the same Problem and the key-buffer-size is set to 1G and the Webhost uses PCI-SSDs for MySQL.

Are there any other suggestions?

Thanks a lot.

Marcus

Perhaps you could add more details to your problem as the OP wasn't very detailed either. At the very least the full error generated.

[Cimme]

Hey Grumpy,

the solution was setting in phpmyadmin the collation to uft8_bin and make use of innodb (in my case)

The Error was pretty similar to the message showed above.

The error was caused by collation set so utf8mb4. the maximum size of 767 bits or 1000 bits is less then the possible of the utf8mb4 (1024 bit)