DBA v7

Master MySQL Astrad DBA V007

André Guimarães, 2012-03-08

Switzernet

1. New functionalities and corrections. 1

2. Known problems. 7

3. Wish list for next versions. 7

4. Resources. 7

This version is compatible at least with Astrad versions 8, 9, 10, 11 and 12.

New functionalities and corrections

MySQL version was upgraded to 5.1. This allows the use of Events to automate procedures.

This version can be now slave of a PBS or Master. The PBS will replicate from the Master and have these two additional tables: astrad_confs and customer_confs. In astrad_confs it is possible to define in which port a node is running (when different from 5060) and in customer_confs it will allow customization of each account parameters (not in use yet).

Modified setup-mysql-replication.pl and exec-registration.pl for this feature. Each binary used by the script perl has now full path, as the PATH is not defined when run from CRON.

Defaults were added to the field Useragent. When this field was NULL there were replication problems. After each insertion the perl script would insert the call again and again creating a cycle that would increase the CPU charge and traffic in all DBAs.

Structure of the two new tables added to Porta-Billing database:

-- Table structure for table `customer_confs`

DROP TABLE IF EXISTS `customer_confs`;

CREATE TABLE IF NOT EXISTS `customer_confs` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(80) NOT NULL,

`nat` enum('yes','no','force_rport','comedia') NOT NULL DEFAULT 'yes',

`sipalg` int(11) DEFAULT 0,

`video` bool DEFAULT 0,

`codecorder` varchar(100) DEFAULT NULL,

`codecusefirstonly` bool DEFAULT 0,

`TTL` int(11) DEFAULT 3600,

PRIMARY KEY (`id`),

UNIQUE KEY `name` (`name`),

KEY `name_2` (`name`)

);

DROP TABLE IF EXISTS `astrad_confs`;

CREATE TABLE IF NOT EXISTS `astrad_confs` (

`id` int(11) NOT NULL auto_increment,

`host` varchar(15) NOT NULL,

`port` int(11) DEFAULT 5060,

PRIMARY KEY (`id`),

UNIQUE KEY `name` (`host`),

KEY `name_2` (`host`)

);

And trigger replacements to propagate the data in these new tables to the list of nodes in database Asterisk:

DELIMITER |

CREATE TRIGGER `Trg_Insert_astrad_confs` AFTER INSERT ON `astrad_confs` FOR EACH ROW BEGIN

DECLARE exist INT default 0;

SET exist=IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip=NEW.host),0);

IF exist = 1 THEN

UPDATE `astrad`.ip_customers SET port=NEW.port WHERE remote_ip=NEW.host;

END IF;

SET exist=IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip=NEW.host),0);

IF exist = 1 THEN

UPDATE `astrad`.vendors SET port=NEW.port WHERE remote_ip=NEW.host;

END IF;

END |

CREATE TRIGGER `Trg_Update_astrad_confs` AFTER UPDATE ON `astrad_confs` FOR EACH ROW BEGIN

DECLARE exist INT default 0;

IF OLD.host <> NEW.host THEN

SET exist=IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip=OLD.host),0);

IF exist = 1 THEN

UPDATE `astrad`.ip_customers SET port=5060 WHERE remote_ip=OLD.host;

END IF;

SET exist=IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip=OLD.host),0);

IF exist = 1 THEN

UPDATE `astrad`.vendors SET port=5060 WHERE remote_ip=OLD.host;

END IF;

SET exist=IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip=NEW.host),0);

IF exist = 1 THEN

UPDATE `astrad`.ip_customers SET port=NEW.port WHERE remote_ip=NEW.host;

END IF;

SET exist=IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip=NEW.host),0);

IF exist = 1 THEN

UPDATE `astrad`.vendors SET port=NEW.port WHERE remote_ip=NEW.host;

END IF;

END |

CREATE TRIGGER `Trg_Delete_astrad_confs` AFTER DELETE ON `astrad_confs` FOR EACH ROW BEGIN

DECLARE exist INT default 0;

SET exist=IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip=OLD.host),0);

IF exist = 1 THEN

UPDATE `astrad`.ip_customers SET port=5060 WHERE remote_ip=OLD.host;

END IF;

SET exist=IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip=OLD.host),0);

IF exist = 1 THEN

UPDATE `astrad`.vendors SET port=5060 WHERE remote_ip=OLD.host;

END IF;

END |

DELIMITER ;

USE `astrad`;

ALTER TABLE ip_customers ADD COLUMN port INT DEFAULT 5060;

ALTER TABLE vendors ADD COLUMN port INT DEFAULT 5060;

-- Triggers for table `ip_customers`

DELIMITER |

DROP TRIGGER IF EXISTS `Trg_Insert_ip_customers`|

CREATE TRIGGER `Trg_Insert_ip_customers` AFTER INSERT ON `ip_customers` FOR EACH ROW BEGIN

DECLARE DN text DEFAULT '';

DECLARE REQ text DEFAULT '';

DECLARE REQ2 text DEFAULT '';

DECLARE cont VARCHAR(10) DEFAULT '';

IF NEW.is_node='Y' THEN

SET cont='fromnode';

ELSEIF NEW.is_node='N' THEN

SET cont='fromhost';

END IF;

IF (SELECT 1 FROM vendors WHERE remote_ip=NEW.remote_ip) IS NULL THEN

SET REQ=CONCAT("INSERT IGNORE INTO sippeers (name,host,port,context,insecure) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"','",NEW.port,"','",cont,"','port');");

SET REQ2=CONCAT("INSERT IGNORE INTO sipdevices(name,host,port,context,insecure) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"','",NEW.port,"','",cont,"','port');");

INSERT INTO request (val) VALUES (REQ);

INSERT INTO request (val) VALUES (REQ2);

INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Add to sippeers and sipdevices ',NEW.remote_ip,' context=',cont));

ELSE

SET REQ=CONCAT("UPDATE sippeers SET context='",cont,"', insecure='port' WHERE name='",NEW.remote_ip,"' AND context='forbidden';");

SET REQ2=CONCAT("UPDATE sipdevices SET context='",cont,"', insecure='port' WHERE name='",NEW.remote_ip,"' AND context='forbidden';");

INSERT INTO request (val) VALUES (REQ);

INSERT INTO request (val) VALUES (REQ2);

INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Set context (',cont,') to ip_customer in sippeers and sipdevices ',NEW.remote_ip));

END IF;

SET DN=(SELECT get_deny());

SET REQ=CONCAT("UPDATE sipusers SET deny='",DN,"';");

SET REQ2=CONCAT("UPDATE config SET value='",DN,"' WHERE id = 1;");

INSERT INTO request (val) VALUES (REQ);

INSERT INTO request (val) VALUES (REQ2);

INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Update deny in sipusers and config ',NEW.remote_ip));

END |

DROP TRIGGER IF EXISTS `Trg_Update_ip_customers`|

CREATE TRIGGER `Trg_Update_ip_customers` AFTER UPDATE ON `ip_customers` FOR EACH ROW BEGIN

DECLARE DN text DEFAULT '';

DECLARE REQ text DEFAULT '';

DECLARE REQ2 text DEFAULT '';

DECLARE cont VARCHAR(10) DEFAULT '';

IF NEW.is_node='Y' THEN

SET cont='fromnode';

ELSEIF NEW.is_node='N' THEN

SET cont='fromhost';

END IF;

SET REQ=CONCAT("UPDATE sippeers SET name='",NEW.remote_ip,"', host='",NEW.remote_ip,"', context='",cont,"', port='",NEW.port,"' WHERE name='",OLD.remote_ip,"';");

SET REQ2=CONCAT("UPDATE sipdevices SET name='",NEW.remote_ip,"', host='",NEW.remote_ip,"', context='",cont,"', port='",NEW.port,"' WHERE name='",OLD.remote_ip,"';");

INSERT INTO request (val) VALUES (REQ);

INSERT INTO request (val) VALUES (REQ2);

INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','UPDATE',NOW(),CONCAT('update ip customer in sippeers and sipdevices remote_ip=',NEW.remote_ip,' old remote_ip=',OLD.remote_ip));

IF OLD.remote_ip <> NEW.remote_ip THEN

SET DN=(SELECT get_deny());

SET REQ=CONCAT("UPDATE sipusers SET deny='",DN,"';");

SET REQ2=CONCAT("UPDATE config SET value='",DN,"' WHERE id = 1;");

INSERT INTO request (val) VALUES (REQ);

INSERT INTO request (val) VALUES (REQ2);

INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Update deny in sipusers and sipdevices from ',OLD.remote_ip,' to ',NEW.remote_ip));

END IF;

END |

DELIMITER ;

-- Triggers for table `vendors`

DELIMITER |

DROP TRIGGER IF EXISTS `Trg_Insert_vendors`|

CREATE TRIGGER `Trg_Insert_vendors` AFTER INSERT ON `vendors` FOR EACH ROW BEGIN

DECLARE REQ text DEFAULT '';

IF (SELECT 1 FROM ip_customers WHERE remote_ip=NEW.remote_ip ) IS NULL THEN

SET REQ=CONCAT("INSERT IGNORE INTO sippeers (name,host,port) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"','",NEW.port,"');");

INSERT INTO request (val) VALUES (REQ);

SET REQ=CONCAT("INSERT IGNORE INTO sipdevices (name,host,port) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"','",NEW.port,"');");

INSERT INTO request (val) VALUES (REQ);

INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','INSERT',NOW(),CONCAT('Add vendor to sippeers and sipdevices ',NEW.remote_ip));

ELSE

INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','INSERT',NOW(),CONCAT('Vendor already exist on sippeers/sipdevices (ip existe on ip_customers)',NEW.remote_ip));

END IF;

END |

DROP TRIGGER IF EXISTS `Trg_Update_vendors`|

CREATE TRIGGER `Trg_Update_vendors` AFTER UPDATE ON `vendors` FOR EACH ROW BEGIN

DECLARE REQ text DEFAULT '';

SET REQ=CONCAT("UPDATE sippeers SET name='",NEW.remote_ip,"', host='",NEW.remote_ip,"', port='",NEW.port,"' WHERE name='",OLD.remote_ip,"' AND context='forbidden';");

INSERT INTO request (val) VALUES (REQ);

SET REQ=CONCAT("UPDATE sipdevices SET name='",NEW.remote_ip,"', host='",NEW.remote_ip,"', port='",NEW.port,"' WHERE name='",OLD.remote_ip,"' AND context='forbidden';");

INSERT INTO request (val) VALUES (REQ);

INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','UPDATE',NOW(),CONCAT('Update sippeers and sipdevices remote_ip=',NEW.remote_ip,' old remote_ip=',OLD.remote_ip));

END |

DELIMITER ;

The following trigger corrects a problem when the i_rep was not defined in a customer.

DELIMITER |

DROP TRIGGER IF EXISTS `Trg_Update_Customers`|

CREATE TRIGGER `Trg_Update_Customers` AFTER UPDATE ON `Customers` FOR EACH ROW BEGIN

DECLARE n_i_rep int(10) unsigned;

DECLARE o_i_rep int(10) unsigned;

SET n_i_rep=IFNULL(NEW.i_rep,0);

SET o_i_rep=IFNULL(OLD.i_rep,0);

IF o_i_rep IN (3,17,5,9,12) AND n_i_rep NOT IN (3,17,5,9,12) THEN

CALL UPDATE_CUSTOMER(n_i_rep,NEW.i_customer);

ELSEIF o_i_rep NOT IN (3,17,5,9,12) AND n_i_rep IN (3,17,5,9,12) THEN

CALL UPDATE_CUSTOMER(n_i_rep,NEW.i_customer);

END IF;

END |

DELIMITER ;

After each DBA installation, table location2 remains with old registrations that should be deleted. A new event was created to delete every registration that as expired one hour ago. The vent is run every hour.

DELIMITER |

DROP EVENT IF EXISTS `Evt_location2_Delete`|

CREATE EVENT `Evt_location2_Delete` ON SCHEDULE EVERY 1 HOUR

BEGIN

DELETE FROM location2 WHERE expires < DATE_SUB(NOW(), INTERVAL 1 HOUR);

END |

DELIMITER ;

The SQLs for the changes above can be seen here:

Download 120103-update.sql

Download 120208-update.sql

Download 120223-update.sql

Known problems

Users registered with the same login in the same or different servers are not always added to table multiple_ua. A count in location2 for all users registered with the same account is different from the number of entries in multiple_ua for that same login. It affects around 20% of accounts.

Replication is not fully working for table Service_Attribute_Values when replicating from PBS. This table is not used at the moment. For some reason not yet known PBS doesn’t propagate all changes made to this table.

Wish list for next versions

Each DBA should allow replication from other DBAs

The configuration file in puppet should remove all allowed users and add again all configured DBAs and Astrads when the configuration file of DBAs and Astrads change.

multiple_ua should be cleaned when there is only one telephone registered with the same account.

Resources

DBA versions (Astrad Master/Billing Slave server)

http://switzernet.com/3/public/110317-db3-versions/

Astrad/DBA schema

http://switzernet.com/3/public/111019-astrad-dba-schema/

Astrad versions

http://switzernet.com/3/public/110126-astrad-versions/