[OpenSIPS-Users] [OpenSIPS-Devel] rls_presentity table can not be created in MySQL 4.1.22

[Anca Vamanu]

Hi Andrew,

It seems that MySQL 1.4.22 does not accept a key component with length 
greater than 255 unless it is clearly specified in the index definition (

CONSTRAINT rls_presentity_idx UNIQUE (rlsubs_did(512), resource_uri)).

While looking at this I saw that the current length for the rlsubs_did ( 
512) is in fact too big. It is constructed out of 3 strings that have a 
maximum length of 64 separated by one character delimiter. Therefore, I 
will change the length of the filed to 255 and so fix the problem with 
MySQL 1.4.22.

Thanks and regards,
Anca Vamanu

Andrew Pogrebennyk wrote:
> Hi,
> The following SQL statement (from rls-create.sql) fails in MySQL 4.1.22:
>
> CREATE TABLE rls_presentity (
>      id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
>      rlsubs_did VARCHAR(512) NOT NULL,
>      resource_uri VARCHAR(128) NOT NULL,
>      content_type VARCHAR(64) NOT NULL,
>      presence_state BLOB NOT NULL,
>      expires INT(11) NOT NULL,
>      updated INT(11) NOT NULL,
>      auth_state INT(11) NOT NULL,
>      reason VARCHAR(64) NOT NULL,
>      CONSTRAINT rls_presentity_idx UNIQUE (rlsubs_did, resource_uri)
> ) ENGINE=MyISAM;
>
> ERROR 1170 (42000): BLOB/TEXT column 'rlsubs_did' used in key 
> specification without a key length
>
> Hence presence table can not be created. I have seen on the internet a 
> comment from the MySQL support saying that for BLOB/TEXT column you can 
> create index only on the prefix of column. Apparently, there's similar 
> restriction for VARCHAR columns and constraints. How do I modify the SQL 
> query so that it can be executed on 4.1.22? I think the fix should go to 
> the repository as well. Or did I miss something and 4.1.22 is not 
> officially supported since 1.4.0?
>
>