Re: [SR-Users] too long mysql table key
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 ___ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
Re: [SR-Users] too long mysql table key
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 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 ___ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
Re: [SR-Users] too long mysql table key
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 ___ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
Re: [SR-Users] too long mysql table key
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 ___ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
Re: [SR-Users] too long mysql table key
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 ___ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
Re: [SR-Users] too long mysql table key
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 -- 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 ___ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
Re: [SR-Users] too long mysql table key
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 ___ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
[SR-Users] too long mysql table key
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 ___ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
[SR-Users] too long mysql table key
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 ___ Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
