Jump to content

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

Recommended Posts

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

Link to comment
Share on other sites

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
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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 5 months later...


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.


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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...