Error: 1071 Specified key was too long

[Abigaill]

I have a table whose fields are essentially VARCHAR (255). When I try to create a unique index composed of several fields, I get the error: 1071 Specified key was too long, max key length is 1000 bytes. I read somewhere that this is due to the charset utf-8 that occupies more space than Latin. What is the difference between the 2 charset latin and utf-8? Which should I use?

[Dwarner]

Begin by asking yourself if you really need all 255 characters in these columns! You can see the table structure? However, you can even try out by counting the characters only, and then you can eventually discard all the bytes that appear after 1000. If that doesn't solves the problem then simply add a runtime or compiler option which allows you to adjust the expected characters/bytes factor for UTF8 multi-byte.

[Abigaill]

It's just hard to know if I need 255 characters per record because I do not control the content. And to be sure that all data will pass, I'm 255. In fact I am developing an application that dynamically creates tables that should contain data that can be IP addresses, dates, lengthy text, etc. But as the tables are created dynamically, it is impossible to know that this field will contain such data type. So I took while VARCHAR (255). After, I need to make a line already registered, its not registered again. That's why I want to make a unique index of all fields. The structure of one of the tables:

CREATE TABLE IF NOT EXISTS `log_w3svc1` (
`Nr` int(11) NOT NULL AUTO_INCREMENT,
`date` varchar(255) DEFAULT NULL,
`time` varchar(255) DEFAULT NULL,
`s_sitename` varchar(255) DEFAULT NULL,
`s_ip` varchar(255) DEFAULT NULL,
`cs_method` varchar(255) DEFAULT NULL,
`cs_uri_stem` varchar(255) DEFAULT NULL,
`cs_uri_query` varchar(255) DEFAULT NULL,
`s_port` varchar(255) DEFAULT NULL,
`cs_usrname` varchar(255) DEFAULT NULL,
`c_ip` varchar(255) DEFAULT NULL,
`cs(usr_agent)` varchar(255) DEFAULT NULL,
`sc_status` varchar(255) DEFAULT NULL,
`sc_substatus` varchar(255) DEFAULT NULL,
`sc_win32_status` varchar(255) DEFAULT NULL,
`Application` varchar(160) NOT NULL DEFAULT 'GestLog',
PRIMARY KEY (`Nr`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2490 ;

[Dwarner]

The columns date and time should not be called like that because they are words of SQL. There are is some format these are mentioned, DATE 'yyyy-mm-dd' TIME 'hh:mm:ss" and DATETIME ' yyyy-mm-dd hh:mm:ss"

s_ip: Unless you have the IPV6, the format will always be up to '255.255.255.255' and so you do not need 255 characters!

s_port: if it is a computer port, I doubt that there has to be 255 characters!

c_ip: similar to s_ip

And for others, a username to 255 characters, it is potentially possible but it's still a lot!

In fact I am developing an application that dynamically creates tables

It is not good at all! It defines the data structure and it is left over in case of significant change in the needs of the user application or more generally of the information system. In short, large errors in design from the start cause the problem you are facing and for which you have started this discussion!

[Abigaill]

In short, large errors in design from the start cause the problem you are facing and for which you have started this discussion!

No, there is no 'big design mistakes'. This application MUST dynamically create the tables. I know that IP, date, or a port are not more than 20 characters. But that's because the table is already created that we know that these are dates, or IP. Prior to the establishment, we do not know what it is. I think quotes around the PRIMARY KEY are needed if we mention a specific number of characters.

[Shreevats]

This problem is related to MySQL storage allocation. If the database assigns the storage space depending on the character encoding setting you might encounter this problem. Ideally, UTF-8 uses 1-4 bytes per character which is not supported by MySQL and thus the problem occurs. So to remedy this problem, there are two ways. First is change the character encoding and the second method includes modifying the DDL. According to me the DDL modification is the simpler solution to this problem. In this method, you change the total key column length and set it to stay under 1000 bytes. Since this solution is specific to the data, the solution does not generates any further problem.