Jeremy Orem had given me some new ideas on how to implement the algorithm. The problem with the previous one was with the counting update. UPDATE queries were used to increment the count on the database.

The algorithm Jeremy mentioned uses random with weight. The first step was to calculate the sum of weight.

Or rather, first step was to restore the database to a clean state, followed by changing the schema for this solution.

Dropped all the tables, followed by importing the dump file i had created earlier. Then added a weight column to releases_geo.

alter table releases_geo add COLUMN weight TINYINT UNSIGNED NOT NULL DEFAULT 0;

drop view auto_geo_CN;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW `auto_geo_CN` AS select releases_geo.name AS name,releases_geo.ttl AS ttl,releases_geo.rdtype AS rdtype,releases_geo.rdata AS rdata,releases_geo.CC AS CC,releases_geo.enabled AS enabled,releases_geo.description AS description,releases_geo.region AS region,releases_geo.world AS world,releases_geo.id AS id,releases_geo.weight AS weight from releases_geo where (((releases_geo.CC = ‘CN’) or (releases_geo.rdtype in (‘SOA’,'NS’))) and (releases_geo.enabled = 1));

drop view auto_geo_GLOBAL;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW `auto_geo_GLOBAL` AS select releases_geo.name AS name,releases_geo.ttl AS ttl,releases_geo.rdtype AS rdtype,releases_geo.rdata AS rdata,releases_geo.CC AS CC,releases_geo.enabled AS enabled,releases_geo.description AS description,releases_geo.region AS region,releases_geo.world AS world,releases_geo.id AS id,releases_geo.weight AS weight from releases_geo where (((releases_geo.world = 1) or (releases_geo.rdtype in (‘SOA’,'NS’))) and (releases_geo.enabled = 1)) ;

Then i created a stored procedure to perform the summation of weight.

create procedure weightSum(IN tableName varchar(255), IN lookupName varchar(255))
begin
    set @sumQuery = concat(‘SELECT SUM(weight) FROM ‘,tableName,’ WHERE Upper(rdtype)=\’A\’ and Upper(name)=\”,lookupName,’\”);
    PREPARE stmt FROM @sumQuery;
    EXECUTE stmt;
end

that’s all for now, still trying to implement to loop.

for the system to work properly, the two tables (releases_geo and preference) need to be more or else consistent. This means that when a new record is added in releases_geo, it should appear in preference. When a record is removed from release_geo, it should be removed from preference,

Therefore, i added 2 more triggers to the database in attempt to keep the table in order.

CREATE TRIGGER preferenceDelete AFTER DELETE ON releases_geo
FOR EACH ROW BEGIN
delete from preference where preference.id=old.id;
END;

CREATE TRIGGER preferenceINSERT AFTER INSERT ON releases_geo
FOR EACH ROW BEGIN
insert into preference(id) values(new.id);
END;

Made some changes to the SQL statements that were posted eariler.

delimiter |

CREATE TRIGGER preferenceUpdate AFTER UPDATE ON releases_geo
FOR EACH ROW BEGIN
DECLARE totalWeight tinyint(3) unsigned;
DECLARE totalUse int(10) unsigned;
select sum(usecount), sum(weight) into totalUse, totalWeight from preference where preference.id in ( select id from releases_geo where upper(releases_geo.name) = upper(‘releases.geo.mozilla.com’) and upper(releases_geo.rdtype)=’A’ and enabled );
update preference set usecount=usecount+1 where preference.id=new.id;
update preference set preference.preference=((preference.weight/totalWeight*100)-(preference.usecount/totalUse*100)) where preference.id in ( select id from releases_geo where upper(releases_geo.name) = upper(‘releases.geo.mozilla.com’) and upper(releases_geo.rdtype)=’A’ and enabled );
END; |

delimiter ;

drop view auto_geo_CN;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW `auto_geo_CN` AS select releases_geo.name AS name,releases_geo.ttl AS ttl,releases_geo.rdtype AS rdtype,releases_geo.rdata AS rdata,releases_geo.CC AS CC,releases_geo.enabled AS enabled,releases_geo.description AS description,releases_geo.region AS region,releases_geo.world AS world,releases_geo.id AS id,releases_geo.usecount AS usecount from releases_geo where (((releases_geo.CC = ‘CN’) or (releases_geo.rdtype in (‘SOA’,'NS’))) and (releases_geo.enabled = 1));

drop view auto_geo_GLOBAL;

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW `auto_geo_GLOBAL` AS select releases_geo.name AS name,releases_geo.ttl AS ttl,releases_geo.rdtype AS rdtype,releases_geo.rdata AS rdata,releases_geo.CC AS CC,releases_geo.enabled AS enabled,releases_geo.description AS description,releases_geo.region AS region,releases_geo.world AS world,releases_geo.id AS id,releases_geo.usecount AS usecount from releases_geo where (((releases_geo.world = 1) or (releases_geo.rdtype in (‘SOA’,'NS’))) and (releases_geo.enabled = 1)) ;

did some modification to the database. The preference field should not be in the releases_geo table because of the trigger used.

the current flow of event is as follows:

client query DNS –> DNS Server selects resource with highest preference –> DNS Server increase use count by 1 –> return resource data.

when the use count field (usecount) in the releases_geo table is updated the trigger preferenceUpdate is triggered. The trigger will increases the use count in the preference table and recalculate the preference field for all rows with the same name.

Removing previously added field:

alter table releases_geo drop column weight;
alter table releases_geo drop column preference;

Creating the preference table:

CREATE TABLE preference(id int(11) null,name varchar(255) null, weight TINYINT UNSIGNED NOT NULL DEFAULT 0, usecount INT UNSIGNED NOT NULL DEFAULT 0, preference DECIMAL(7,4) NOT NULL DEFAULT 0);

Populating the preference table with resources that already exists in the releases_geo table

insert into preference(id,name) select id,name from releases_geo;

Creating the preferenceUpdate trigger

CREATE TRIGGER preferenceUpdate AFTER UPDATE ON releases_geo
FOR EACH ROW BEGIN
    DECLARE totalWeight tinyint(3) unsigned;
    DECLARE totalUse  int(10) unsigned;
    select sum(usecount), sum(weight) into totalUse,totalWeight from preference where preference.name = new.name;
    update preference set usecount=usecount+1 where preference.id=new.id;
    update preference set preference.preference=((preference.weight/totalWeight*100)-(preference.usecount/totalUse*100))
    where preference.name=new.name;
END;

recreating the views:

drop view auto_geo_CN;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `auto_geo_CN` AS select `releases_geo`.`name` AS `name`,`releases_geo`.`ttl` AS `ttl`,`releases_geo`.`rdtype` AS `rdtype`,`releases_geo`.`rdata` AS `rdata`,`releases_geo`.`CC` AS `CC`,`releases_geo`.`enabled` AS `enabled`,`releases_geo`.`description` AS `description`,`releases_geo`.`region` AS `region`,`releases_geo`.`world` AS `world`,`releases_geo`.`id` AS `id`,`releases_geo`.`usecount` AS `usecount` from `releases_geo` where (((`releases_geo`.`CC` = _latin1′CN’) or (`releases_geo`.`rdtype` in (_latin1′SOA’,_latin1′NS’))) and (`releases_geo`.`enabled` = 1));
drop view auto_geo_GLOBAL;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `auto_geo_GLOBAL` AS select `releases_geo`.`name` AS `name`,`releases_geo`.`ttl` AS `ttl`,`releases_geo`.`rdtype` AS `rdtype`,`releases_geo`.`rdata` AS `rdata`,`releases_geo`.`CC` AS `CC`,`releases_geo`.`enabled` AS `enabled`,`releases_geo`.`description` AS `description`,`releases_geo`.`region` AS `region`,`releases_geo`.`world` AS `world`,`releases_geo`.`id` AS `id`,`releases_geo`.`usecount` AS `usecount` from `releases_geo` where (((`releases_geo`.`world` = 1) or (`releases_geo`.`rdtype` in (_latin1′SOA’,_latin1′NS’))) and (`releases_geo`.`enabled` = 1)) ;

Hadn’t cleaned up my codes in mysqldb.c yet. But in summary this is what is happening.

select the resource with highest preference:

snprintf(str, sizeof(str),"select resources.id, preference.preference from %s resources, preference where UPPER(resources.name)=UPPER(‘%s’) and UPPER(resources.rdtype)=’A’ and resources.enabled and resources.id = preference.id order by preference.preference desc limit 1", dbi->table,  name);

incrementing use counter:

snprintf(str, sizeof(str),"UPDATE %s set usecount=usecount+1 where id=%d", dbi->table, rid);

get the answer for the DNS query:

snprintf(str, sizeof(str),"(SELECT ttl, rdtype, rdata FROM %s WHERE UPPER(name) = UPPER(‘%s’) and UPPER(rdtype)!=’A’ and enabled)UNION(SELECT ttl, rdtype, rdata FROM %s WHERE id=%d)", dbi->table, name, dbi->table, rid);

After querying the for the same resource (“releases.geo.mozilla.com”) for several times, the resources selected seems to be distributed among those available. But further testing will be required.

finished writing some initial code that should make the system work.

but i can’t compile the codes. stuck with the following error.

bind-9.4.2/lib/dns/acl.c:227: undefined reference to `GeoIP_country_code_by_addr’
bind-9.4.2/lib/dns/acl.c:222: undefined reference to `GeoIP_new’

this was what i did:

./configure –prefix=/usr/local –sysconfdir=/etc –localstatedir=/var  –disable-openssl-version-check
make clean
make

everything seems correct, but make just keep producing this error. Not sure why, still trying to solve the problem.

alter table releases_geo add COLUMN weight TINYINT UNSIGNED NOT NULL DEFAULT 0;
alter table releases_geo add COLUMN usecount INT UNSIGNED NOT NULL DEFAULT 0;
alter table releases_geo add COLUMN preference DECIMAL(7,4) NOT NULL DEFAULT 0;
desc releases_geo;

drop view auto_geo_CN;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `auto_geo_CN` AS select `releases_geo`.`name` AS `name`,`releases_geo`.`ttl` AS `ttl`,`releases_geo`.`rdtype` AS `rdtype`,`releases_geo`.`rdata` AS `rdata`,`releases_geo`.`CC` AS `CC`,`releases_geo`.`enabled` AS `enabled`,`releases_geo`.`description` AS `description`,`releases_geo`.`region` AS `region`,`releases_geo`.`world` AS `world`,`releases_geo`.`id` AS `id`,`releases_geo`.`usecount` AS `usecount`,`releases_geo`.`preference` AS `preference` from `releases_geo` where (((`releases_geo`.`CC` = _latin1′CN’) or (`releases_geo`.`rdtype` in (_latin1′SOA’,_latin1′NS’))) and (`releases_geo`.`enabled` = 1));