[Added web page to show replications status and dynamic 
Adrian Georgescu <ag@ag-projects.com>**20080205192440
     instructions about how to fix it
 
] conflictor [
hunk ./debian/changelog 1
+  * Fixed buildStatistics call in cron.d (env LANG=C)  -> 6.1.8 ?
+
]
:
hunk ./debian/changelog 1
+cdrtool (6.1.8) unstable; urgency=low
+
+  * Added web page to show replications status and dynamic 
+    instructions about how to fix it
+
+ -- Adrian Georgescu <ag@ag-projects.com>  Tue, 05 Feb 2008 20:23:55 +0100
+
addfile ./mysql_replication_lib.phtml
hunk ./mysql_replication_lib.phtml 1
+<?
+// This library can be used to display the status of mysql replication of
+// arbitrary number of mysql servers and instructions about how to fix
+// a broken replication process
+
+/*
+
+To use this library create a web page like this:
+
+$hostnames=array("db1"=>array('ip'       => '10.0.0.131',
+                              'slave_of' => 'db2',
+                              'user'     => 'root',
+                              'password' => 'pass'
+                                  ),
+                 "db2"=>array('ip'       => '10.0.0.132',
+                              'slave_of' => 'db1'
+                              'user'     => 'root',
+                              'password' => 'pass'
+                              )
+                 );
+
+
+require("mysql_replication_lib.phtml");
+
+$ReplicationOverview = new ReplicationOverview($hostnames);
+$ReplicationOverview->showOverview();
+
+*/
+
+
+class DBx extends DB_Sql {
+  function DBx($host='localhost', $user, $password) {
+      $this->Host = $host;
+      $this->User = $user;
+      $this->Password = $password;
+      $this->Database = 'mysql';
+      parent::DB_Sql();
+  }
+}
+
+class MySQLReplicationStatus {
+	var $slave_status_query="show slave status";
+    var $master_status_query="show master status";
+
+    function MySQLReplicationStatus($host,$hostnames) {
+
+        $db=new DBx ($host,$hostnames[$host]['user'],$hostnames[$host]['password']);
+
+        $db->query($this->slave_status_query);
+		$db->next_record();
+
+        $this->slave_master         = $db->f('Master_Host');
+        $this->slave_user           = $db->f('Master_User');
+        $this->slave_master_port    = $db->f('Master_Port');
+        $this->slave_log_file	    = $db->f('Master_Log_File');
+        $this->slave_position       = $db->f('Read_Master_Log_Pos');
+        $this->slave_sql_running	= $db->f('Slave_SQL_Running');
+        $this->slave_io_running	    = $db->f('Slave_IO_Running');
+        $this->slave_last_errno	    = $db->f('Last_Errno');
+        $this->slave_last_error 	= $db->f('Last_Error');
+        $this->slave_seconds_behind = $db->f('Seconds_Behind_Master');
+
+        $db->query($this->master_status_query);
+		$db->next_record();
+
+    	$this->master_position	    = $db->f('Position');
+    	$this->master_log_file	    = $db->f('File');
+
+    }
+}
+
+
+class ReplicationOverview {
+    var $status=array();
+
+    function ReplicationOverview($hostnames=array()) {
+    	$this->hostnames=$hostnames;
+        $this->host = $_REQUEST['repair'];
+
+        foreach (array_keys($this->hostnames) as $key) {
+            $this->status[$key]= new MySQLReplicationStatus($key,$this->hostnames);
+        }
+
+        foreach (array_keys($this->hostnames) as $key) {
+            if ($key==$this->host) {
+                $this->master_hostname=$this->hostnames[$key]['slave_of'];
+                $this->master_ip=$this->hostnames[$this->master_hostname]['ip'];
+                $this->master=$this->hostnames[$key]['slave_of'];
+                break;
+            }
+        }
+
+        foreach (array_keys($this->hostnames) as $key) {
+        	if ($this->hostnames[$key]['slave_of'] == $this->master && $key!=$this->host) {
+                $this->slave_ip=$this->hostnames[$key]['ip'];
+                $this->slave_hostname =$key;
+                break;
+            }
+        }
+    }
+
+    function showOverview() {
+
+        print "
+        <table border=0>
+        <tr>
+        <th></th>
+        ";
+        
+        foreach (array_keys($this->hostnames) as $key) {
+            printf ("<th>%s (%s)",$key,$this->hostnames[$key]['ip']);
+            if ($this->host != $key) {
+            	printf ("<br><a href=%s?repair=%s>Click to learn how to repair me</a></th>",$_SERVER['PHP_SELF'],$key);
+            }
+        }
+        
+        print "</tr>
+        ";
+
+        print "<tr>
+        <td></td>";
+        
+        foreach (array_keys($this->hostnames) as $key) {
+            print "<td class=border valign=top>";
+        
+            print "
+            <table>
+            <tr>
+            <td>Slave status</td><td>Master status</td></tr>
+            ";
+            print "<tr>";
+            print "
+            <td valign=top>
+            ";
+        
+            if ($this->status[$key]->slave_sql_running == 'No') {
+                $sql_color="red";
+            } else {
+                $sql_color="white";
+            }
+        
+            if ($this->status[$key]->slave_io_running == 'No') {
+                $io_color="red";
+            } else {
+                $io_color="white";
+            }
+        
+            print "<table border=1>";
+            printf ("<tr><td class=border>Master host</td><td>%s</td></tr>",$this->status[$key]->slave_master);
+            printf ("<tr><td class=border>Master port</td><td>%s</td></tr>",$this->status[$key]->slave_master_port);
+            printf ("<tr><td class=border>Log file</td><td>%s</td></tr>",$this->status[$key]->slave_log_file);
+            printf ("<tr><td class=border>Position</td><td>%s</td></tr>",$this->status[$key]->slave_position);
+            printf ("<tr><td class=border>SQL thread</td><td bgcolor=%s>%s</td></tr>",$sql_color,$this->status[$key]->slave_sql_running);
+            printf ("<tr><td class=border>IO thread</td><td bgcolor=%s>%s</td></tr>",$io_color,$this->status[$key]->slave_io_running);
+            printf ("<tr><td class=border>Delay</td><td>%s</td></tr>",$this->status[$key]->slave_seconds_behind);
+            printf ("<tr><td class=border>Last error</td><td>%s</td></tr>",$this->status[$key]->slave_last_error);
+            printf ("<tr><td class=border>Last errno</td><td>%s</td></tr>",$this->status[$key]->slave_last_errno);
+            print "</table>";
+            print "
+            </td>
+            ";
+        
+            print "
+            <td valign=top>
+            ";
+            print "<table border=1>";
+            printf ("<tr><td class=border>Log file</td><td>%s</td></tr>",$this->status[$key]->master_log_file);
+            printf ("<tr><td class=border>Position</td><td>%s</td></tr>",$this->status[$key]->master_position);
+            print "</table>";
+        
+            print "
+            </td>
+            </table>
+            ";
+        
+            print "</td>";
+        }
+        
+        print "</tr>";
+
+ 		$this->printInstructions();
+
+        print "
+        </table>
+        ";
+ }
+
+    function printStep ($hostname,$instructions='') {
+        $this->step++;
+        print "<tr>
+        <td>$this->step
+        </td>";
+
+        foreach (array_keys($this->hostnames) as $key) {
+            if ($key==$hostname) {
+                print "<td class=border><pre>$instructions</pre></td>";
+            } else {
+                print "<td class=border></td>";
+            }
+        }
+        print "</tr>";
+
+    }
+
+    function printInstructions() {
+
+    if (!$this->host) return;
+
+if ($this->slave_hostname) {
+
+$text=sprintf("
+mysql -u root -P
+
+flush tables with read lock;
+
+show master status;
+
+# write down the file and possition %s.file %s.pos
+# do not exit the mysql console
+
+",$this->master_hostname,$this->master_hostname);
+
+$this->printStep($this->master_hostname,$text);
+
+$text=sprintf("
+
+mysql -u root -P
+
+show slave status;
+# wait until same file/pos as %s is displayed)
+
+slave stop;
+",$this->master_hostname);
+
+$this->printStep($this->slave_hostname,$text);
+
+$text="unlock tables;";
+
+$this->printStep($this->master_hostname,$text);
+
+
+$text="
+
+/etc/init.d/monit stop
+
+/etc/init.d/mysql stop
+
+";
+
+$this->printStep($this->slave_hostname,$text);
+
+$text="
+
+/etc/init.d/monit stop
+
+/etc/init.d/mysql stop
+
+";
+
+$this->printStep($this->host,$text);
+
+$text=sprintf("
+rsync -avzP --delete /var/lib/mysql %s:/var/lib/
+
+/etc/init.d/mysql start
+
+/etc/init.d/monit start
+",$this->host);
+
+$this->printStep($this->slave_hostname,$text);
+
+
+$text=sprintf("
+(cd /var/lib/mysql/; rm *.info *relay-bin*)
+
+/etc/init.d/mysql start
+
+/etc/init.d/monit start
+
+mysql -u root -P
+
+show master status;
+# write down the file and possition %s.file %s.pos
+
+CHANGE MASTER TO MASTER_HOST='%s',
+MASTER_USER='%s',
+MASTER_PASSWORD='%s',
+MASTER_LOG_FILE='%s.file',
+MASTER_LOG_POS=%s.pos;
+
+slave start;
+
+",$this->host,
+$this->host,
+$this->master_ip,
+$this->hostnames[$this->master_hostname]['replication_user'],
+$this->hostnames[$this->master_hostname]['replication_password'],
+$this->master_hostname,
+$this->master_hostname
+);
+$this->printStep($this->host,$text);
+
+$text=sprintf("
+
+mysql -u root -P
+
+stop slave;
+
+CHANGE MASTER TO MASTER_HOST='%s',
+MASTER_USER='%s',
+MASTER_PASSWORD='%s',
+MASTER_LOG_FILE='%s.file',
+MASTER_LOG_POS=%s.pos;
+
+slave start;
+",
+$this->hostnames[$this->host]['ip'],
+$this->hostnames[$this->host]['replication_user'],
+$this->hostnames[$this->host]['replication_password'],
+$this->host,
+$this->host
+);
+
+$this->printStep($this->master_hostname,$text);
+
+    } else {
+
+
+$text="
+
+/etc/init.d/monit stop
+
+/etc/init.d/mysql stop
+
+";
+
+$this->printStep($this->host,$text);
+
+
+$text=sprintf("
+
+mysql -u root -P
+
+flush tables with read lock;
+
+show master status;
+# write down the file and possition %s.file %s.pos
+
+unlock tables;
+
+/etc/init.d/monit stop
+
+/etc/init.d/mysql stop
+
+rsync -avzP --delete /var/lib/mysql %s:/var/lib/
+
+/etc/init.d/mysql start
+
+/etc/init.d/monit start
+
+",
+$this->master_hostname,
+$this->master_hostname,
+$this->host
+);
+
+$this->printStep($this->master_hostname,$text);
+
+$text=sprintf("
+(cd /var/lib/mysql/; rm *.info *relay-bin*)
+
+/etc/init.d/mysql start
+
+/etc/init.d/monit start
+
+mysql -u root -P
+
+show master status;
+# write down the file and possition %s.file %s.pos
+
+CHANGE MASTER TO MASTER_HOST='%s',
+MASTER_USER='%s',
+MASTER_PASSWORD='%s',
+MASTER_LOG_FILE='%s.file',
+MASTER_LOG_POS=%s.pos;
+
+slave start;
+
+",$this->host,
+$this->host,
+$this->master_ip,
+$this->hostnames[$this->master_hostname]['replication_user'],
+$this->hostnames[$this->master_hostname]['replication_password'],
+$this->master_hostname,
+$this->master_hostname
+);
+$this->printStep($this->host,$text);
+
+$text=sprintf("
+
+mysql -u root -P
+
+stop slave;
+
+CHANGE MASTER TO MASTER_HOST='%s',
+MASTER_USER='%s',
+MASTER_PASSWORD='%s',
+MASTER_LOG_FILE='%s.file',
+MASTER_LOG_POS=%s.pos;
+
+slave start;
+",
+$this->hostnames[$this->host]['ip'],
+$this->hostnames[$this->host]['replication_user'],
+$this->hostnames[$this->host]['replication_password'],
+$this->host,
+$this->host
+);
+
+$this->printStep($this->master_hostname,$text);
+
+    }
+}
+}
+?>
addfile ./mysql_replication_status.phtml
hunk ./mysql_replication_status.phtml 1
-
+<?
+include("./global.inc");
+
+page_open(
+            array("sess" => "CDRTool_Session",
+                  "auth" => "CDRTool_Auth",
+                  "perm" => "CDRTool_Perm"));
+
+$perm->check("admin");
+
+$title="MySQL replication status";
+
+if (is_readable("local/header.phtml")) {
+    include("local/header.phtml");
+} else {
+    include("header.phtml");
+}
+
+$layout = new pageLayoutLocal();
+$layout->showTopMenu($title);
+
+require("mysql_replication_lib.phtml");
+
+$ReplicationOverview = new ReplicationOverview($replicated_databases);
+$ReplicationOverview->showOverview();
+    
+print "
+</body>
+</html>
+";
+
+page_close();
+?>
hunk ./phplib/local.inc 724
-            print " | <a href=rating_tables.phtml>Rating tables</a>";
+            print " | <a href=rating_tables.phtml>Rating</a>";
hunk ./phplib/local.inc 728
-            print " | <a href=provisioning.phtml>SIP accounts</a>";
+            print " | <a href=provisioning.phtml>SIP</a>";
hunk ./phplib/local.inc 733
-            print " | <a href=status/sip_online.phtml target=siponline>SIP registrar</a>";
-            print " | <a href=status/media_sessions.phtml target=mediasessions>RTP sessions</a>";
+            print " | <a href=status/sip_online.phtml target=siponline>Registrar</a>";
+            print " | <a href=status/media_sessions.phtml target=mediasessions>Sessions</a>";
hunk ./phplib/local.inc 737
+        
+        if ($perm->have_perm("admin")) {
+            print " | <a href=mysql_replication_status.phtml>Replication</a>";
+        }
hunk ./scripts/replicationStatus.php 1
-#!/usr/bin/php
-<?
-$path=dirname(realpath($_SERVER['PHP_SELF']));
-include($path."/../global.inc");
-
-system('clear');
-
-if ($CDRTool['replicated_databases']) {
-    print "\nPlease migrate global.inc setting \$CDRTool['replicated_databases'] to \$CDRTool['mysql_clusters']. See setup/global.inc.in for example\n\n";
-    exit;
-}
-
-$slaveStatusQuery  = "show slave status";
-$masterStatusQuery = "show master status";
-$replicationStatus = array();
-
-$clusters=array_keys($CDRTool['mysql_clusters']);
-
-foreach ($clusters as $_cluster) {
-    $replicationStatus=array();
-    
-    $title= "\nMySQL cluster: $_cluster\n\n";
-    print $title;
-
-    $databases=$CDRTool['mysql_clusters'][$_cluster];
-    
-    foreach ($databases as $_database) {
-        if (!class_exists($_database)) continue;
-        $_db = new $_database;
-    
-        if ($_db->query($slaveStatusQuery)) {
-            $_db->next_record();
-            $replicationStatus[$_db->Host]['slave_status']=array(
-                'Master_Host'	   => $_db->f('Master_Host'),
-                'Master_User'	   => $_db->f('Master_User'),
-                'Master_Port'	   => $_db->f('Master_Port'),
-                'Log_File'	   => $_db->f('Master_Log_File'),
-                'Position'    	   => $_db->f('Read_Master_Log_Pos'),
-                'Slave_SQL_Running'=> $_db->f('Slave_SQL_Running'),
-                'Slave_IO_Running' => $_db->f('Slave_IO_Running'),
-                'Last_errno'	   => $_db->f('Last_Errno'),
-                'Last_error'	   => $_db->f('Last_Error'),
-                'Skip_counter'	   => $_db->f('Skip_counter'),
-                'Connection'       => array('Host'     => $_db->Host,
-                                            'User'     => $_db->User,
-                                            'Password' => $_db->Password
-                                           )
-                );
-    
-        } else {
-            printf ("Error for query '%s' on server %s: %s(%s)\n",$slaveStatusQuery,$_database,$_db->Error,$_db->Errno);
-            $replicationStatus[$_db->Host]['slave_status']=array();
-            break;
-        }
-    
-        if ($_db->query($masterStatusQuery)) {
-            $_db->next_record();
-            $replicationStatus[$_db->Host]['master_status']=array(
-                'Position'	=> $_db->f('Position'),
-                'Log_File'	=> $_db->f('File')
-                );
-        } else {
-            printf ("Error for query '%s' on server %s: %s(%s)\n",$masterStatusQuery,$_database,$_db->Error,$_db->Errno);
-            $replicationStatus[$_db->Host]['master_status']=array();
-            break;
-        }
-    }
-    
-    //print_r($replicationStatus);
-    
-    $j=0;
-    foreach (array_keys($replicationStatus) as $_db) {
-    
-        if (count($replicationStatus[$_db]['slave_status'])) {
-            if ($j) print "\n";
-            $title="Replication status of slave server $_db\n";
-            print $title;
-            $t=1;
-            while ($t < strlen($title)) {
-                print "=";
-                $t++;
-            }
-            print "\n";
-            if ($replicationStatus[$_db]['slave_status']['Slave_SQL_Running'] == 'Yes' &&
-                $replicationStatus[$_db]['slave_status']['Slave_IO_Running'] == 'Yes' &&
-                $replicationStatus[$_db]['slave_status']['Log_File'] == $replicationStatus[$replicationStatus[$_db]['slave_status']['Master_Host']]['master_status']['Log_File']
-                ) {
-                printf ("Master %s ----> Slave %s, logfile %s:%s, replication OK\n",$replicationStatus[$_db]['slave_status']['Master_Host'],$_db,$replicationStatus[$_db]['slave_status']['Log_File'],$replicationStatus[$_db]['slave_status']['Position']);
-            } else {
-                printf ("Master %s ----> Slave %s, replication failed. \n\nThe replication parameters that failed are:\n\n",$replicationStatus[$_db]['slave_status']['Master_Host'],$_db);
-    
-                if (!$replicationStatus[$replicationStatus[$_db]['slave_status']['Master_Host']]['master_status']) { 
-                  printf ("Error: server %s is not defined. Please use the same IPs or hostnames used during replication setup.\n\n",$replicationStatus[$_db]['slave_status']['Master_Host']);
-                  continue;
-                }
-                
-                if ($replicationStatus[$_db]['slave_status']['Slave_SQL_Running'] != 'Yes') {
-                    printf ("%s = %s\n",'Slave_SQL_Running',$replicationStatus[$_db]['slave_status']['Slave_SQL_Running']);
-                }
-    
-                if ($replicationStatus[$_db]['slave_status']['Slave_IO_Running'] != 'Yes') {
-                    printf ("%s = %s\n",'Slave_IO_Running',$replicationStatus[$_db]['slave_status']['Slave_IO_Running']);
-                }
-    
-                if ($replicationStatus[$_db]['slave_status']['Log_File'] != $replicationStatus[$replicationStatus[$_db]['slave_status']['Master_Host']]['master_status']['Log_File']) {
-                    printf ("Master log file %s != slave log file %s\n",$replicationStatus[$replicationStatus[$_db]['slave_status']['Master_Host']]['master_status']['Log_File'],$replicationStatus[$_db]['slave_status']['Log_File']);
-                }
-    
-                if ($replicationStatus[$_db]['slave_status']['Position'] != $replicationStatus[$replicationStatus[$_db]['slave_status']['Master_Host']]['master_status']['Position']) {
-                    printf ("Master log position %s != slave log position %s\n",$replicationStatus[$replicationStatus[$_db]['slave_status']['Master_Host']]['master_status']['Position'],$replicationStatus[$_db]['slave_status']['Position']);
-                }
-    
-                printf ("\nTo restart the replication process run this command on %s: \n\n",$replicationStatus[$_db]['slave_status']['Connection']['Host']);
-                printf ("CHANGE MASTER to \nMASTER_HOST     = '%s', \nMASTER_USER     = '%s', \nMASTER_PASSWORD = '%s', \nMASTER_LOG_FILE = '%s', \nMASTER_LOG_POS  = %s;\n",
-                $replicationStatus[$_db]['slave_status']['Master_Host'],
-                $replicationStatus[$_db]['slave_status']['Connection']['User'],
-                $replicationStatus[$_db]['slave_status']['Connection']['Password'],
-                $replicationStatus[$replicationStatus[$_db]['slave_status']['Master_Host']]['master_status']['Log_File'],
-                $replicationStatus[$replicationStatus[$_db]['slave_status']['Master_Host']]['master_status']['Position']
-                );
-            }
-            $j++;
-        }
-    }
-}
-
-?>
rmfile ./scripts/replicationStatus.php
hunk ./setup/global.inc.in 174
-# To show mysql replication status using scripts/replicationStatus.php
-$CDRTool['mysql_clusters']=array('production'=>array('DB1','DB2'));
+$replicated_databases=array(
+                 "db1"=>array('ip'      =>'10.0.0.131',
+                                  'slave_of'=>'db-log2',
+			      	  'user'    =>'root',
+                                  'password'=>'password'
+                                  ),
+                 "db2"=>array('ip'      =>'10.0.0.132',
+                                  'slave_of'=>'db-log1',
+		      	          'user'    =>'root',
+                                  'password'=>'password'
+                                  )
+                 );
hunk ./setup/global.inc.new.installation 101
-// Used by /var/www/CDRTool/scripts/replicationStatus.php
-class DB1 extends DB_Sql {
-  var $Host     = "10.0.0.1";
-  var $Database = "radius";
-  var $User     = "root";
-  var $Password = "PASSWORD";
-  var $Halt_On_Error ="no";
-}
-
-class DB2 extends DB_Sql {
-  var $Host     = "10.0.0.2";
-  var $Database = "radius";
-  var $User     = "root";
-  var $Password = "PASSWORD";
-  var $Halt_On_Error ="no";
-}
-
-$CDRTool['mysql_clusters']=array('production'=>array('DB1','DB2'));
+$replicated_databases=array(
+                 "db1"=>array('ip'      =>'10.0.0.131',
+                                  'slave_of'=>'db-log2',
+			      	  'user'    =>'root',
+                                  'password'=>'password'
+                                  ),
+                 "db2"=>array('ip'      =>'10.0.0.132',
+                                  'slave_of'=>'db-log1',
+		      	          'user'    =>'root',
+                                  'password'=>'password'
+                                  )
+                 );
hunk ./version 1
-6.1.7
+6.1.8