[mod_mariadb] Missing primary indexes on tables created by sofia

[bodji]

Hi,

When using the new module mod_mariadb with a galera cluster, we have some errors :

2019-09-17 15:52:09.462379 [ERR] switch_core_sqldb.c:702 [db="Server=mysql-cluster-0.mysql-cluster;Database=freeswitch;Uid=freeswitch;Pwd=freeswitch;",type="database_interface"] mariadb SQL ERR [The table does not comply with the requirements by an external plugin.]delete from channels where hostname=''
2019-09-17 15:52:09.462400 [ERR] switch_core_sqldb.c:3760 Database Error [The table does not comply with the requirements by an external plugin.]

When digging a little it seems it is caused by tables without primary keys,

and this is the case for tables created by sofia stack :

https://freeswitch.org/stash/projects/FS/repos/freeswitch/browse/src/mod/endpoints/mod_sofia/sofia_glue.c#2375

We see directives like this :

char pres_sql[] =
        "CREATE TABLE sip_presence (\n"
        "   sip_user        VARCHAR(255),\n"
        "   sip_host        VARCHAR(255),\n"
        "   status          VARCHAR(255),\n"
        "   rpid            VARCHAR(255),\n"
        "   expires         BIGINT,\n"
        "   user_agent      VARCHAR(255),\n"
        "   profile_name    VARCHAR(255),\n"
        "   hostname        VARCHAR(255),\n"
        "   network_ip      VARCHAR(255),\n"
        "   network_port    VARCHAR(6),\n"
        "   open_closed     VARCHAR(255)\n"
        ");\n";

Can you add an "id" column or something acting as PRIMARY KEY for those tables ? Thank you very much !

Kind regards, Mathieu Bodjikian

[andywolk]

If you add an id column yourself, does it error somewhere else? @bodji

[bodji]

I try, and I come back to you asap.

[bodji]

Not much better.

There a plenty of hard coded INSERT with no column listing, and when we have a primary key (even if auto_increment is enabled) :

[CRIT] mod_mariadb.c:632 Failed to send query (insert into complete values (0,'nat_map', 'reinit', '', '', '', '', '', '', '', '', 'freeswitch-zhst8')) to database: Column count doesn't match value count at row 1

Mathieu

[doublexy]

Hi, has the problem been solved? I meet this problem too. When i use MySQL(8+) on cloud，I try to create table with the id primary, but it doesn't work bacause of the hard code like INSERT.

[ghzserg]

Not much better.

There a plenty of hard coded INSERT with no column listing, and when we have a primary key (even if auto_increment is enabled) :

[CRIT] mod_mariadb.c:632 Failed to send query (insert into complete values (0,'nat_map', 'reinit', '', '', '', '', '', '', '', '', 'freeswitch-zhst8')) to database: Column count doesn't match value count at row 1

Mathieu

This error fixed in https://github.com/signalwire/freeswitch/pull/2100