Fusionpbx HA setup NATIVE SQL ERR [no such table: sip_registrations]

[Aqs Younas]

Greeting list,

I have followed a nice article by DigitalDaz and everything went smoothly without any apparent error. I created an extension on the first server(Debian 8.10 with posgreSQL 9.4.7) and it was replicated on the other server. But when I tried to connect to freeswitch on the servers, I got some member table missing error on both servers.

After that, I deleted all the databases from location /var/lib/freeswitch/db/ by assuming that freeswitch will auto create them.

But databases were not created there and I now get below errors.

2018-04-19 03:22:35.762752 [ERR] switch_core_sqldb.c:587 NATIVE SQL ERR [no such table: sip_registrations]
delete from sip_registrations where sub_host is null and hostname='debian' and network_ip like '%' and network_port like '%' and sip_username like '%' and mwi_user like '%' and mwi_host like '%' and orig_server_host like '%' and orig_hostname like '%'
2018-04-19 03:22:35.762777 [ERR] switch_core_sqldb.c:587 NATIVE SQL ERR [no such table: sip_registrations]
select ping_count from sip_registrations
2018-04-19 03:22:35.762792 [ERR] switch_core_sqldb.c:587 NATIVE SQL ERR [no such table: sip_registrations]
select ping_status from sip_registrations
2018-04-19 03:22:35.762805 [ERR] switch_core_sqldb.c:587 NATIVE SQL ERR [no such table: sip_registrations]
select ping_expires from sip_registrations
2018-04-19 03:22:35.762818 [ERR] switch_core_sqldb.c:587 NATIVE SQL ERR [no such table: sip_registrations]
select ping_time from sip_registrations
2018-04-19 03:22:35.762830 [ERR] switch_core_sqldb.c:587 NATIVE SQL ERR [no such table: sip_registrations]
select force_ping from sip_registrations
2018-04-19 03:22:35.762872 [ERR] switch_core_sqldb.c:587 NATIVE SQL ERR [no such table: sip_registrations]
delete from sip_registrations where sub_host is null and hostname='debian' and network_ip like '%' and network_port like '%' and sip_username like '%' and mwi_user like '%' and mwi_host like '%' and orig_server_host like '%' and orig_hostname like '%';delete from sip_registrations where sub_host is null and hostname='debian' and network_ip like '%' and network_port like '%' and sip_username like '%' and mwi_user like '%' and mwi_host like '%' and orig_server_host like '%' and orig_hostname like '%'
2018-04-19 03:22:35.762883 [CRIT] sofia_glue.c:2297 GREAT SCOTT!!! Cannot execute batched statements! [no such table: sip_registrations]


I have tried to copy databases from other installed fusionpbx server but the error did not go away.

I hope someone might help me out.

Best Regards,

Aqs Younas

 

[DigitalDaz]

I would need to check but if I remember rightly, I thought I had chosen not to even bother replicating the Freeswitch database.

 

[Aqs Younas]

Sorry, I did not follow you.
Do, I need to create the database by myself and replicate them between two servers?

I have checked switch.conf.xml and i see this line.
<param name="core-db-dsn" value="$${dsn}" />

But when I checked vars.xml just to understand the value behind of $${dsn} it was empty.

Any suggestion would be much appreciated.

Best Regards,

Aqs Younas

 

[DigitalDaz]

Ys you would have to create the dsn if you are replicationg the freeswitch db but there is also more to it than that, you then have to change the profiles and a bunch of other things too in order for them to use the freeswitch replicated db.

 

[smn]

You just need to create the database and add the connection info.

So create the DB like this.

sudo -u postgres psql -c "CREATE DATABASE freeswitch;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE freeswitch to fusionpbx;"

Then add the connection info in /etc/freeswitch/autoload_configs/switch.conf.xml

<param name="core-db-dsn" value="pgsql://hostaddr=127.0.0.1 dbname=freeswitch user=fusionpbx password='somepassword'" />

Then restart freeswitch and you should see all the tables created in the freeswitch database.

 

[Aqs Younas]

Thank you guys, especially DigitalDiz for creating the tutorial which nearly makes things effortless.

Somehow vars.xml was not created I copied it from source code and put the required ODBC details and things got working for me.
I have other question when I make a call on the first server it does not show up in UI of other the server though I see the call on the freeswitch of the second server.

Br, Aqs.