AMBROSIA60.dnsalias.net Portal

Activate SSL for this page to secure your communication

SSL Powered

Welcome to the Fido-History-Project 3rd Generation !

The FidoBase Project ...

[30.09.2009] Can be the FidoBase Project be part of the Fido-History-Project ?
Well, because the FidoBase contains messages from at least 1996 its also be a part of the Fido-History-Project.
The only problem is, its not yet well documented.
Starting today, i'll try some documentation on this.

Intro

Project Team Members:

Dirk Astrath
Kees van Eeten
Uli Schroeter

Project Development starts:
... some years ago (2004?, 2005?, 2006?) around Pfingsten

Data Source:
based on main rescans from Olav, Knut and Dirk (and some others), all *.pkt

Program Souce:
Actual Source is a bundle of PERL scripts.
Port to PHP is probably possible

FidoBase actual State (1.10.2009):

Actual state FidoBase

The actual state is:

only Cluster Node A imports mails from the ftn network into the MySQL db and then pushes by sql mails to the other cluster nodes
There is no import or export activity on clusternodes B and C as of missing scripts
Each of the cluster nodes systems has several downlinks thru ftn connections

Example: if downlink #2 at ftn node B writes a mail, the mail flows thru ftn node B to ftn node A, pushed with a perl script into the MySQL database of cluster node A and then will be forwared to cluster node B with sql inserts (also to cluster node C).

The actual system uses the Dual Write method.

Pro's: its easy to implement
Con's: if one node hangs, the replications hangs

Better:
Using an async multiple nodes replication mode

Database Structure

37 tables named echo_#, where # = 0..9, a..z and temp
Each table contains mails from all the echos starting the named first letter
Each record of each mail contains the area name in field folder
Structure of each table is identical
echo_temp includes the actual mails as duplicate upto 10 days (not yet working)

fieldname	data type	Flags	default value
id	INTEGER	No Unsigned, No ZeroFill	NULL
ftscdate	VARCHAR(20)	No Binary
datetime	TIMESTAMP		CURRENT_TIME
folder	VARCHAR(72)	No Binary
fromnode	VARCHAR(72)	No Binary
tonode	varchar(72)	No Binary
fromname	varchar(36)	No Binary
toname	varchar(36)	No Binary
subject	varchar(72)	No Binary
attrib	smallint(5)	Unsigned, No ZeroFill	0
msgid	varchar(72)	Not Binary
replyid	varchar(72)	Not Binary
origin	varchar(72)	Not Binary
path	varchar(255)	Not Binary
local	char(1)	Not Binary, Not ASCII, Not UNIC	Y
rcvd	char(1)	Not Binary, Not ASCII, Not UNIC	N
sent	char(1)	Not Binary, Not ASCII, Not UNIC	N
kludges	mediumblob		NULL
body	mediumblob		NULL
seenby	mediumblob		NULL
datewritten	datetime		NULL
Uplink	int(10)	Unsigned, Not ZeroFill	0

CREATE TABLE `echo_0` (
  `id` int(11) NOT NULL auto_increment,
  `ftscdate` varchar(20) NOT NULL default '',
  `datetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `folder` varchar(72) NOT NULL default '',
  `fromnode` varchar(72) NOT NULL default '',
  `tonode` varchar(72) NOT NULL default '',
  `fromname` varchar(36) NOT NULL default '',
  `toname` varchar(36) NOT NULL default '',
  `subject` varchar(72) NOT NULL default '',
  `attrib` smallint(5) unsigned NOT NULL default '0',
  `msgid` varchar(72) NOT NULL default '',
  `replyid` varchar(72) NOT NULL default '',
  `origin` varchar(72) NOT NULL default '',
  `path` varchar(255) NOT NULL default '',
  `local` char(1) NOT NULL default 'Y',
  `rcvd` char(1) NOT NULL default 'N',
  `sent` char(1) NOT NULL default 'N',
  `kludges` mediumblob,
  `body` mediumblob,
  `seenby` mediumblob,
  `datewritten` datetime default NULL,
  `Uplink` int(10) unsigned default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `dupecheck` (`folder`,`ftscdate`,`msgid`,`subject`),
  KEY `Export` (`datetime`,`folder`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ToDo / Wishlist

PHP Import / Export Scripts
Connect downlinks to all nodes (same sessionpwd database)
Sync to all nodes not only triggered by one node
Authoritive recovery on disaster recovery

Each downlink can connect to all cluster nodes. The connection will be a ftn mailer session. The session password is read from the online database that resides on each nodes cluster site. Mailbundles will be streamed online, on the fly to the connected downlink from the nodes cluster database (echo_temp).
If the downlink connects a 2nd time onto anothers cluster node, the tables needs updated just in time, so the downlink doesn't receive the mailbundle again.
So the actual system needs two more tables:

Password Table
Actual Downlinks state

Table: DLSTAT
	Downlink 1	Downlink 2	Downlink 3	.	.	.	Downlink n
Echo 1	HWM.1.1	-.-	-.-				HWM.n.1
Echo 2	HWM.1.2	HWM.2.2	HWM.3.2				-.-
Echo 3	-.-	-.-	HWM.3.3				HWM.n.3
.
.
.
Echo n	-.-	HWM.2.n	-.-				-.-

Normalized

Table: DLSTAT
	Link	Link Stat
Echo 1	LinkID 1	HWM.1.1
Echo 2	LinkID 1	HWM.1.2
Echo 2	LinkID 2	HWM.2.2
Echo 2	LinkID 3	HWM.3.2
Echo 3	LinkID 3	HWM.3.3
Echo 3	LinkID n	HWM.n.3
Echo n	LinkID 2	HWM.2.n

Table: SESSPWD
Link ID	(Default Link)	AKA	Pwd
LinkID 1	Link 1	Aka 1	Pwd 1
LinkID 2	Link 2	Aka 2	Pwd 2
LinkID 3	Link 3	Aka 3	Pwd 3
LinkID n	Link n	Aka n	Pwd n

Database Sync Methods

Dual Writes x¹)

Coordinated Commits

Others ?

Database Sync Methods - Dual Writes

x¹) Dual Writes

This method is actual used by the FidoBase project (see above).

Database Sync Methods - Coordinated Commits

Discussion

Using Queues ?

Based on the Dual Writes method, each Commit is queued for all connected nodes.
If a node is offline, the queue increases until the node is back online.
Each node has to use a detect and resolve conflicts resolution algorhytm (dupe detection).
If a dupe is identified, the related records needs to be bounced.
Each node has two inputs: the ftn mailer input and the syncing queue.
Both queues needs to be merged at each nodes cluster end.

Queued Writes

Queue-IN: needs information of source (FTN Mailer or Cluster Node Update).

Processing needs information about source of update, so updates from FTN mailers needs to be written to database and Queue-OUT.
Flow: a1 -> IN -> a2 -> Replication Agent -> pushes a3 to Database and pushes a3 to Queue-OUT
Udpates from Cluster nodes needs to be written only to database.
Flow: b1 -> IN -> b2 -> Replication Agent -> pushes b3 to Database only

Push or Pull Updates or Both ?

Push Update

On Push updates the push fails if a cluster node is offline
Leave update if cluster node is offline
The Push update can be done if the offline cluster is online next time

Pull Update

On Pull updates the initiating cluster node doesn't need check each time a cluster node goes offline
Updates are as long in the queue as long as a cluster node is offline

Push and Pull Update

Possible conflicting Push and Pull update if an offline cluster node comes online the next time
Updates are as long in the queue as long as a cluster node is offline
Alternate: Push update on initiating first write. If this fails, still leave the record in the queue and wait for a Pull update x²)

Push and/or Pull or Both updates needs info about available cluster nodes.
... so the list of cluster nodes can increase / decrease and is flexible.
If a cluster node goes offline forever, all related records in the Queue-OUT can be deleted.
Problem solved.
Records needs to be created in the Queue-OUT for all cluster nodes except the own cluster-node.

List of Processes

Process 1 - FTN mailer connection

This process writes updates to the Queue-IN with the information of source (FTN mailer or Cluster node update).
Triggers the Update Process (2)

Process 2 - Update Process

The Update Process is the main sync process on each cluster node.
This process has to ...
- checks for updates in the Queue-OUT of all other cluster nodes x²) (Pull replication) (transfer from Queue-OUT of other cluster nodes to the own Queue-IN)
- check for updates in the Queue-IN
- check for dupes (conflict detection and resolution)
- pushes writes to the database
- pushes writes to the Queue-OUT (Push replication)
- pushes initial writes (one time only) to the Queue-IN of all other cluster nodes (Push replication)
This process needs to be triggered by the FTN mailer process and on recuring schedules.

Process 3 - Maintenance Process
If a cluster node goes offline forever, the queues still needs cleaned from records for this offline cluster node.

Full and/or partial Rescans

(between cluster nodes)
Full or partial rescans are no problem with this design. Trigger a full rescan exports all records from the database to the Queue-OUT for a specific cluster node.
Partial rescans can be handled by count or days (export the last 5000 records, export the last 3 months). As of the dupe detection on the other side, dupe records are prevented.

Downlinks Handling ?

This procedure describes only the sync of cluster nodes with updates from FTN mailers. But it doesn't includes the update to the downlinks.

How get the downlinks their bundles?

i. Handle downlinks as they were cluster nodes too ?
ii. Handle downlinks with tables of highwatermarks for each connected area ?

i. has the problem, that each cluster node has to queue all the mails for all downlinks, so the mails in the Queue-OUT needs to be duplicated to all real cluster nodes, because each downlink can connect to one of the other real cluster nodes.
If the downlink connects to one real cluster node, the pulled mails from Queue-OUT also needs deleted from the Queue-OUT of the other real cluster nodes too.

ii. the order of mails in each cluster nodes database can vary as the input may be unordered. This complicates the use of highwater marks. The highwater mark in database of cluster node A isn't the same highwater mark in the 2nd database of cluster node B and vice versa. If a downlink connects to a cluster node, the cluster node has to identify the point of which mails the downlink has received the last mail in one area (this is the same problem for all connected areas).

Rcvd Mails DL1 to Node A, Rcvd Mails DL2 to Node B

Rcvd Mails DL1 to Node A, Rcvd Mails DL2 to Node B

Downlink 1 connects to Cluster Node A, transfers some mails
Downlink 2 connects to Cluster Node B, transfers some mails

Replication Node A to B

Mails from Downlink 1 still gets replicated from Cluster Node A to Cluster Node B

Replication Node B to A

and Mails from Downlink 2 still gets replicated from Cluster Node B to Cluster Node A

All tables are synchronized

What's next time Downlink 2 connects to Cluster Node A and tries to get his 'actual' mails ?
Remember: Downlink 2 sends mails Echo 1: #6, Echo 2: #4, #5, #6.
The last sent mails for Echo #1 is #3, and for Echo 2 is #2.
So sending all mails after Echo 1 #3 and Echo 2 #2 doesn't fit.
Downlink 2 only needs the blue ones.

Using addtl. Temp table?

Filling Temp Downlinks table

The next time, Downlink 2 connects to Cluster Node A, Cluster Node A sends mail beginning after row E2:2 =>
E1:4, E1:5, E2:3, skipping: E1:6, E2:4, E2:5, E2:6
and sets Highwate mark to row E2:6

Temp table updated on Cluster Node A for Downlink 2

Temp table updated at Cluster Node A for Downlink 2

Replicate information to Cluster Node B:
DL2: E1:4+, E1:5+, E2:3+

Temp table on Cluster Node B

now receiving update information from Cluster Node A

Temp table updated on Cluster Node B

New state Temp table on Cluster Node B

Flat table structure for Temp Downlinks table

[-----------Mails-----------]  [DL1,DL2,...,DLn]
Table (?), Folder (?), MailID, DownlinkID, State

Replication Agent Procedure:

for i = 1 to n-Updates
   for j = 1 to n-Downlinks
      Dest = Downlinks[j]
      if Dest != MyNodeID
        case Action==AddMail
          Queue-OUT(From:MyNodeID,To:Dest,Action:AddMail,Content:add(id,ftscdate,datetime,folder,...))        
        case Action==UpdateTemp
          Queue-OUT(From:MyNodeID,To:Dest,Action:UpdateTemp,Content:update(MailID,DL#,state))
      endif
   next
next

Requirements

Mails need unique IDs (not index autoincrement!) so entries can be identified accross all cluster node tables (requirement results from temp downlinks table, see above i.e. E2:3)
Replication needs some informations:
- Replication source (FTN mailer, Cluster node)
- Replication target (Cluster node A, B, ...)
- Action requiered: add new mail, update temp downlinks table
- Action on Table Name (?): TableName
- Replication records uses different record structures (echoes tables, temp downlinks table)
  {Echoes Table: id, ftscdate, datetime, folder, fromnode, ...}
  {Temp Table: MailID, DL#, state (+|r|?)}

Further Actions

Add, Remove Cluster Nodes
To add and remove Cluster Nodes, a table that holds the Cluster Nodes info needs to exist.
Update Record thru Replication process.
Proposed table structure Cluster Nodes:

ClusterNodesID, AKA, Name

Add, Remove Downlinks
To add and remove Downlinks, a table for Downlinks (with Session Password) needs to exist.
Update record thru Replication process.
Proposed table structure Cluster Nodes:

DownlinkID, AKA, Name, SessionPwd

Update Table Structures ?
To add and remove Table Columns (Structureupdate) a procedure needs to exist
Update table structures thru Replication process ?

Updated Replication Agent Procedure:

for i = 1 to n-Updates
   for j = 1 to n-ClusterNodes
      Dest = ClusterNodes[j]
      if Dest != MyNodeID
        case Action==AddMail
          Queue-OUT(From:MyNodeID,To:Dest,Action:AddMail,Content:add(id,ftscdate,datetime,folder,...))        
        case Action==UpdateTemp
          Queue-OUT(From:MyNodeID,To:Dest,Action:UpdateTemp,Content:update(MailID,DL#,state))
        case Action==AddClusterNode
          Queue-OUT(From:MyNodeID,To:Dest,Action:AddClusterNode,Content:add(ClusterNodeID,AKA,Name))
        case Action==AddDownlink
          Queue-OUT(From:MyNodeID,To:Dest,Action:AddDownlink,Content:add(DownlinkID,AKA,Name,SessionPwd))
        case Action==DelClusterNode
          Queue-OUT(From:MyNodeID,To:Dest,Action:DelClusterNode,Content:del(ClusterNodeID))
        case Action==DelDownlink
          Queue-OUT(From:MyNodeID,To:Dest,Action:DelDownlink,Content:del(DownlinkID))
      endif
   next
next

Exports, External Links and Dupe Ring detections

Case:
Two links connected to one of the cluster nodes (aka BBR0)
One link sends new mail (FTN mailer goes to Queue-IN)
MsgID#1 created at cluster node A
Another link sends the same mail to another cluster node (FTN mailer)
MsgID#2 created on cluster node B
Now dupe collision detection needs to compare MsgID #1 and #2
Which MsgID will win ?
Using the cluster node table ascending order of hirarchy can be used to for select the higher priority for one of these mails
If one cluster node goes offline, the next one is the virtual master for the moment of dupe collision
If the failed cluster node comes back online, the authoritive restore overwrites dupe collisions upto the moment the cluster node is in sync again.

Next problem:
One link rcvd mail from downlink 1, downlink sends also mail thru FTN channel to downlink 2 (but needs some time). Cluster node forwards and exports mail to downlink 2. Now dupe detection is at FTN channel side, unreachable for the cluster node dupe detection process.
Prevent sending mails to downlinks with known addtl. FTN links ?
Implement a path analyse ? (aka CPD circular path detection)

Dupe Detection with external links

The collision detection process:
1. rcvd dupe msg
2. is one of the msgs from (last) dupe-master?
3. if yes, replace Id with Id from dupe-master
4. if no, is (last) master online?
5. send msgs request to master
6. dupe-master sends replace Id with dupe-master-Id for msg#
7. if dupe-master isn't online, the next one in the cluster nodes table becomes dupe-master

The problem:
FTN mailer downlinks doesn't have their own intelligence but needs to be included into the sync schema. One possible solution can be to build up the replication schema for FTN mailer downlinks within the database virtualy. This needs inclusion for all connected and sub-connected FTN mailer systems that are connected to any of the echo areas. If mails are exported to FTN mailer downlink #1 who has 2 FTN mailer downlinks the export has to be recorded into the system too.

Cluster node A exports to FTN mailer downlink #1.
Tables needs updated for downlink #1.
Downlink #1 has also 2 more downlinks, downlink #2 and #3.
Tables for downlinks #2 and #3 has to be added into the system and needs to be updated in the same way as downlink #1 table is updated.

By default actions, larger dupe ring detection is impossible as the database actions are as fast as possible. Mail flow thru the FTN mailers are someway delayed.
Proposed large dupe ring detection:
Sending Test pings, that aren't included into the database ? and delayed upto the receiving from another FTN mailer ? (delayed database transfer actions on mails on continous schedules?)
If sometimes the cluster node insert into database process of a single mail will be delayed, the mailflow thru the FTN mailer ring starts and ends up on the same cluster node or another cluster node, the information of the dupe ring can be detected.
Using this mechanism, is an option in detecting dupe rings and further prevention of dupes by setting the related FTN mailer downlink to import or export state only.
But the problem on this idea is, the mails that are used as test ping needs to be production mails to be tossed into the mail databases of all FTN downlinks to get them passed thru. ;& so that sending test ping mails have to use production mails ... i.e. monthly rules posting ?!?
Sending test pings in the cluster node synchronisation is no problem, but sending test pings thru FTN downlinks tossers, that aren't aware of special 'test ping' mails is a problem.
Solution search:
Adding a X-TEST kludge to such a mail? (added X-TEST: 002002440112000000091009113800 to one mail)

If a rules posting is received at one cluster node, the rules posting will be added the delayed action - meaning, waiting 24 hours to send out to the next downlink, awaiting next receive, send to the next downlink, wait again 24 hours and so on upto the last downlink (adding different X-TEST kludges to different downlinks? to identify late distributions thru other channels?!?).
If once a mail with an X-TEST is received by a cluster node, this X-TEST kludge can be identified to a special test mail sent to downlink 1, received from downlink 2, so between downlink 1 and downlink 2 exists a dupe ring and the path can be read from the mail received from downlink 2 ....
Area info will be great, added to the X-TEST kludge ... so it has to read (field-length):
X-TEST: [area(1-n)]-[zone(3)][net(5)][node(5)][point(5)][year(2)][month(2)][day(2)][hour(2)][minute(2)][seconds(2)][[optional random number]]
sample:
X-TEST: fidocon.bleichenbach.1996-002002440112000000091009113800

to be continued ...

Path Analyse

sample: FIDOCON.BLEICHENBACH.1996

PATHS: Maintain and report PATHS a message takes within an echo.
       Copyright (C) 1991-1992, Graham J Stair. All rights reserved.
       Release 2a for DOS (10th January 1993, 21:21) {-? for help}
       
Checked on          :  Wed Oct 07 18:04:20 2009

Number of nodes     :  23
Number of messages  :  344xx
Earliest message    :  Nov 27 2008
Latest message      :  Feb 05 2009 22:28:16 2106

2:244/1120  (6 of -31120)
  +->2:244/1120.2  (784 of 784)
  |                                          
  +->2:240/4030  (4983 of 4983)              
  |  
  +->2:244/1120.6  (2066 of 2066)
  |  
  +->2:244/1117  (1371 of 1371)
  |                                   
  +->2:240/2188.262  (9401 of 9401)   
  |  
  +->2:2443/1313  (0 of 5490)                -------+
  |    +->2:280/5003  (19 of 2988)                  |
  |    |    +->2:280/5003.4  (2967 of 2967)         |
  |    |    +->2:280/5555  (2 of 2)                 |
  |    |    |                                       |
  |    |    +->2:280/5004 (0)
  |    |                                            |
  |    +->2432/200  (0 of 168)               -----+ |
  |    |    +->2:2448/44  (0 of 167)              | |
  |    |    |    +->2:2448/44.23  (167 of 167)    | |
  |    |    |                                     V V
  |    |    +->2:2432/390  (1 of 1)               ? ?                            
  |    |    |                                     (+->2:240/2188  (0 of 9401) ???)
  |    |   ???
  |    |    |                                -------> 2:240/5832 ?
  |    |    |                                -------> 2:2437/33  ?
  |    |    SeenBy
  |    |    +->2:24/905
  |    |    +->2:240/2188
  |    |    +->2:240/5778
  |    |    +->2:244/1200
  |    |    +->2:249/3110
  |    |    +->2:313/41
  |    |    +->2:423/81
  |    |    +->2:2411/413
  |    |    +->2:2432/0
  |    |    +->2:2432/201
  |    |    +->2:2432/215
  |    |    +->2:2432/300
  |    |    +->2:2433/401
  |    |    +->2:2437/40
  |    |    +->2:2443/1311
  |    |    +->2:2452/250    
  |    |
  |    |
  |    +->2:2443/1313.13  (1139 of 1139)  
  |    |  
  |    +->2:2443/1313.666  (6 of 6)
  |    |  
  |    +->2:2443/1313.1168  (29 of 29)
  |    |  
  |    +->1/2443  (14 of 14)
  |    |  
  |    +->2:2443/1313.80  (4 of 4)
  |    |  
  |    +->2:2443/1313.87  (3 of 3)
  |    |
  |   ???
  |  
  +->2:244/1120.21  (9868 of 9868)
  |  
  +->2:244/1120.23  (1586 of 1586)

+---------------------------------------------------------------------+
 Average msg hops: 1.9        Maximum msg hops: 4
+---------------------------------------------------------------------+

The DOS timepacking problem

As in the history plain old DOS software is used and timestamps are packed with the DOS timepack algorhytm, timestamps with the 2 second difference could be a problem in syncing two databases. So the proposal is, to convert all timestamps to timestamps with even seconds. Every timestamp conversion then needs to be filtered thru the 2 seconds correction routine.

Timestamp A: 2009-10-03 10:28:02 -> OK.
Timestamp B: 2009-10-03 10:28:01 -> needs conversion to 2009-10-03 10:28:02

Note from KvE:

During my exercises last year, while merging and cleaning the databases from Dirk and Knut, i found that for the old dataset, the inclusion of ftscdate in the dupcheck key resulted in more dupes in the database. If I recall correctly, the differences were larger as the modulo 2 seconds. For the size of the merge, using only folder, msgid and subject pooved to be sufficient.

Athough it will probably not happen anymore, I found that the msgid of messages where this attribute is missing, can reliably be generated before inclusion into the DB. If for purist reasons this atrribute should not be added to the original message, the special attribute will do.

Alternates?

Software

An alternate can be the PhFiTo project at SourceForge.net. A PHP tosser project with multiple ftn api's

    This is a part of PhFiTo (aka PHP Fido Tosser)
    Copyright (c) Alex Kocharin, 2:50/13

    This program is distributed under GNU GPL v2
    See docs/license for details

    $Id: phfito.php,v 1.13 2008/03/18 22:31:28 kocharin Exp $

API's included in this package:

FIPS
JAM
MsgBase
MySQL
OPUS
phBB2
phBB3
Squish
XMLbase
PKT

Database Replycation, Synchronisation

Wiki SyncML
SyncML Schema
SyncML PHP project
SyncML Toolkits

Synchronize MySQL Databases with PHP

Synchronize multiple MySQL Databases with PHP

**FidoBase-Project History Log**
Date	Activities
30.09.2009	Starting Documentation As of a long time development w/o any documentation, i'll give this a try. If there is new infos from the past, they will be added.

Future4Fido
[ Join Now \| Ring Hub \| Random \| << Prev \| Next >> ]

©WebRing Inc.

FidoNet World Wide WebRing
<< Prev \| Ring Hub \| Join \| Rate\| Next >>