Ronan <[EMAIL PROTECTED]> wrote on 16/02/2006 11:56:18:
> Im trying to set up a primary key of server(text), date (date), hour
> (small int) but when i try to include the server field in the key it
> replies with
>
> ALTER TABLE `exim` DROP PRIMARY KEY ,
> ADD PRIMARY KEY ( `date` , `hour` , `server` )
>
> #1170 - BLOB/TEXT column 'server' used in key specification without a
> key length
>
> i have googled, but not much is relevant to my example i dont think..
>
>
> CREATE TABLE `exim` (
> `date` date NOT NULL default '0000-00-00',
> `server` longtext NOT NULL,
> `hour` tinyint(4) NOT NULL default '0',
> `count` smallint(6) NOT NULL default '0',
> PRIMARY KEY (`date`,`hour`),
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='exim realtime stats';
A LONGTEXT field may be up to 4 Gigabytes long. MySQL cannot (and, I would
think, should not) include thenwhole 4Gb in the index. It therefore needs
you to indicate how many characters of the server field it should actually
use in the index. To get it to use only the first 64 characters in the
key, you should put in server(64) (the single quotes you are using are
necesary only if you wish to give a colum the same name as a reserved
wioord - a vary bad practice).
However, if you are using it as a PRIMARY KEY or UNIQUE KEY, *you* must
guarantee that those firat 64 characters are unique. within any given date
and hour i.e., I would guess, that your servers are unique within the
first 64 (or however many you choose) characters.
May I suggest that a more conventional way to do what I think you are
doing woiuld be to have two tables. Allocate each server a number, and put
the number in the exim table. Then have another table to convert the
server name to a number. It is then trivially easy to use that table to
convert from server number to name or vice versa. And the server name no
l;onger has to be unique in the first N characters: as long as the names
differ, the table will work.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
