[SR-Users] too long mysql table key

[Daniel-Constantin Mierla]

On 15.04.17 08:27, Juha Heinanen wrote:
> Daniel-Constantin Mierla writes:
>
>>> Looks like default charset is something (e.g. utf8), where one char takes
>>> more than 1 byte and 767 limit is exceeded.
>>>
>>> Any suggestions on how to solve this?
>>>
>> I haven't trying for UNIQUE, but it may be the same as for usual INDEX
>> where one can provide the length for column (prefix size to be taken in
>> consideration).
> Weird, but when I dropped kamailio db and created it again, the error
> disappeared.  I then checked and charset is latin1, not utf8:
>
> CREATE TABLE `domain_attrs` (
>   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
>   `did` varchar(64) NOT NULL,
>   `name` varchar(32) NOT NULL,
>   `type` int(10) unsigned NOT NULL,
>   `value` varchar(255) NOT NULL,
>   `last_modified` datetime NOT NULL DEFAULT '2000-01-01 00:00:01',
>   PRIMARY KEY (`id`),
>   UNIQUE KEY `domain_attrs_idx` (`did`,`name`,`value`)
> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
>
> I still don't understand why value (and name) need to be part of the
> index, since lookup_domain() loads attributes based on did only.
>
As I said, I haven't added this table, nor use it, my guess was that
this constraint is to avoid having same (did,name,value) more than once.

It doesn't seem to be an index for the purpose of searching. If it loads
by did, then an index on did would be recommended, however, if it loads
all records at once and just groups by did in kamailio memory, then such
index will be useful. Eventually an index on did+name would be useful
when updating the records in database.

Cheers,
Daniel


-- 
Daniel-Constantin Mierla
www.twitter.com/miconda -- www.linkedin.com/in/miconda
Kamailio Advanced Training - May 22-24 (USA) - www.asipto.com
Kamailio World Conference - May 8-10, 2017 - www.kamailioworld.com