14 Apr
2017
14 Apr
'17
5:04 p.m.
I tried to install kamailio mysql db on Debian Stretch. Create of
domain_attrs table failed with this kind of error message:
ERROR 1071 (42000) at line 26: Specified key was too long; max key length is 767 bytes
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`)
);
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?
-- Juha
14 Apr
14 Apr
5:15 p.m.
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?
-- Juha
15 Apr
15 Apr
8:19 a.m.
On 14.04.17 17:15, Juha Heinanen wrote:
Juha Heinanen 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.
If it would be just for fast searching, then a normal index should be used.
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
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?
8:30 a.m.
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.
-- Juha
8:38 a.m.
On 15.04.17 08:30, Juha Heinanen wrote:
Daniel-Constantin Mierla writes:
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
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
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.
8:16 a.m.
Hello,
On 14.04.17 17:04, Juha Heinanen wrote:
I tried to install kamailio mysql db on Debian
Stretch. Create of
domain_attrs table failed with this kind of error message:
ERROR 1071 (42000) at line 26: Specified key was too long; max key length is 767 bytes
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`)
);
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).
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
8:27 a.m.
Daniel-Constantin Mierla writes:
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
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).
8:37 a.m.
On 15.04.17 08:27, Juha Heinanen wrote:
Daniel-Constantin Mierla writes:
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
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).
8:55 a.m.
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
8:59 a.m.
On 15.04.17 08:55, Juha Heinanen wrote:
Daniel-Constantin Mierla writes:
OK, feel free to update now -- if
anyone else complains later, it will
be discussed and decided again.
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
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.
2592
days inactive
2593
days old
9 comments
2 participants
participants (2)
-
Daniel-Constantin Mierla -
Juha Heinanen