On 14.04.17 17:15, Juha Heinanen wrote:

Juha Heinanen writes:

...

domain-create.sql contains this:

CREATE TABLE `domain_attrs` ( `id` INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, `did` VARCHAR(64) NOT NULL, `name` VARCHAR(32) NOT NULL, `type` INT UNSIGNED NOT NULL, `value` VARCHAR(255) NOT NULL, `last_modified` DATETIME DEFAULT '2000-01-01 00:00:01' NOT NULL, CONSTRAINT domain_attrs_idx UNIQUE (`did`, `name`, `value`) );

Why value needs to be part of the index?

I haven't used this table myself, it's coming from SER branch, but I guess the unique constraint is for being sure there are no duplicated values for the same did/name tuple, but more records with same did and name.

If it would be just for fast searching, then a normal index should be used.

Cheers, Daniel

On 15.04.17 08:30, Juha Heinanen wrote:

Daniel-Constantin Mierla writes:

...

I haven't used this table myself, it's coming from SER branch, but I guess the unique constraint is for being sure there are no duplicated values for the same did/name tuple, but more records with same did and name.

lookup_domain() adds attributes associated with did to AVPs. Sure there can be more than one AVP with the same name. Therefore I suggest removing name and value from the index.

I am fine to drop this unique constraint if nobody else has a reason to keep it and eventually add some normal indexes that could help when loading/updating records in database.

Cheers, Daniel

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.

-- Juha

Daniel-Constantin Mierla writes:

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.

I have used domain_attrs to store properties that are common to all users of the domain, e.g., which lcr index (= set of pstn gws) they use, but I could imagine also something with multiple values, such as which geoip countries users of the domain can place calls.

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.

reload_tables() loads all records in domain_attrs table to memory and then adds them to hash table. There is no option for db_only operation. So I don't see a point for such a unique index and suggest that it is changed to non-unique index on did and name only.

-- Juha