A Smart Telephony Platform

Page tree
Skip to end of metadata
Go to start of metadata

Problem

After installation calls are failing with "table 'astpp.packages_view' doesn't exist" error. 

FreeSwitch Console Log
2019-09-17 14:32:07.192375 [DEBUG] switch_cpp.cpp:1365 [ASTPP] [GET_PACKAGE_INFO] Query :SELECT *,P.id as package_id,P.product_id as product_id FROM packages_view as P inner join package_patterns as PKGPTR on P.product_id = PKGPTR.product_id WHERE (patterns = '^15655756756.*' OR patterns = '^1565575675.*' OR patterns = '^156557567.*' OR patterns = '^15655756.*' OR patterns = '^1565575.*' OR patterns = '^156557.*' OR patterns = '^15655.*' OR patterns = '^1565.*' OR patterns = '^156.*' OR patterns = '^15.*' OR patterns = '^1.*' OR patterns ='--') AND accountid = 2 ORDER BY LENGTH(PKGPTR.patterns) DESC
2019-09-17 14:32:07.231930 [ERR] switch_core_sqldb.c:1183 ERR: [SELECT *,P.id as package_id,P.product_id as product_id FROM packages_view as P inner join package_patterns as PKGPTR on P.product_id = PKGPTR.product_id WHERE (patterns = '^15655756756.*' OR patterns = '^1565575675.*' OR patterns = '^156557567.*' OR patterns = '^15655756.*' OR patterns = '^1565575.*' OR patterns = '^156557.*' OR patterns = '^15655.*' OR patterns = '^1565.*' OR patterns = '^156.*' OR patterns = '^15.*' OR patterns = '^1.*' OR patterns ='--') AND accountid = 2 ORDER BY LENGTH(PKGPTR.patterns) DESC]
[STATE: 42S02 CODE 1146 ERROR: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.17]Table 'astpp.packages_view' doesn't exist
]
2019-09-17 14:32:07.231930 [ERR] freeswitch_lua.cpp:460 DBH NOT Connected.
2019-09-17 14:32:07.231930 [ERR] mod_lua.cpp:203 /usr/share/freeswitch/scripts/astpp/lib/astpp.functions.lua:849: assertion failed!
stack traceback:
        [C]: in function 'assert'
        /usr/share/freeswitch/scripts/astpp/lib/astpp.functions.lua:849: in function 'package_calculation'
        /usr/share/freeswitch/scripts/astpp/scripts/astpp.dialplan.lua:225: in main chunk
        [C]: in function 'dofile'
        /usr/share/freeswitch/scripts/astpp/astpp.lua:85: in main chunk
2019-09-17 14:32:07.231930 [ERR] mod_lua.cpp:271 LUA script parse/execute error!
2019-09-17 14:32:07.231930 [DEBUG] freeswitch_lua.cpp:382 DBH handle 0x7f4d0800e9c0 released.
2019-09-17 14:32:07.231930 [WARNING] mod_dialplan_xml.c:667 Context default not found
2019-09-17 14:32:07.231930 [INFO] switch_core_state_machine.c:311 No Route, Aborting
2019-09-17 14:32:07.231930 [NOTICE] switch_core_state_machine.c:312 Hangup sofia/default/4727985745@192.168.1.202 [CS_ROUTING] [NO_ROUTE_DESTINATION]

Solution

Create missing packages_view in database. 

Steps
1. Connect to MySQL using root password 
mysql -u root -p astpp 
ENTER PASSWORD: 

2. Run the following query

CREATE OR REPLACE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `packages_view` AS ( select `O`.`order_id` AS `id`, `P`.`id` AS `product_id`, `P`.`name` AS `package_name`,`O`.`free_minutes` AS `free_minutes`,`P`.`applicable_for` AS `applicable_for`,`O`.`accountid` AS `accountid` from (`products` `P` join `order_items` `O`) where ((`P`.`id` = `O`.`product_id`) and (`P`.`product_category` = 1) and (`P`.`status` = 0) and ((`O`.`termination_date` >= utc_timestamp()) or (`O`.`termination_date` = '0000-00-00 00:00:00'))));

3. exit from MySQL 

Now test the calls.