Bulk add DNS Records

Discussion in 'Installation/Configuration' started by flevoict, Mar 25, 2010.

  1. flevoict

    flevoict New Member

    Dear all,

    I hope somebody can help.

    We have installed a third NS server in an other datacenter. Now I want to add an extra NS record to all my domains in ISP config (about 50 domains).

    Does somebody know how to do this in bulk, like add a record with mysql or something like that.

    I hope somebody can help me so I don't have to this manually. :mad:

    Cheers,

    Bas aka Flevoict
     
  2. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    You can do this e.g. with a script that uses the datalogInsert function from the ispconfig mysql library to insert the records in the database.
     
  3. flevoict

    flevoict New Member

    Thanks Till for your reply.

    Now I have the following issue, I'm not really a database/mysql programmer, more a network engineer who has to manage these things as well.

    Is there somebody who can point me in the right direction with the sql script.

    Thnx in advance.

    Bas
     
  4. SamTzu

    SamTzu Member HowtoForge Supporter

  5. BorderAmigos

    BorderAmigos New Member

    What and where is the "ispconfig mysql library"? I will write a script for it if I can find it. :)
     
  6. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    The ISPConfig mysql library is here:

    /usr/local/ispconfig/interface/lib/classes/db_mysql.inc.php

    Your php script should look like this (untested, partly pseudo code):

    Code:
    <?php
    
    include_once('/usr/local/ispconfig/interface/lib/config.inc.php');
    include_once('/usr/local/ispconfig/interface/lib/classes/db_mysql.inc.php');
    
    $db = new db;
    
    $tablename = "database_table_name";
    $insert_data = "(a,b,c) values ('a','b','c')";
    $index_field = "dbtable_index_field_name";
    
    $db->datalogInsert($tablename, $insert_data, $index_field);
    
    ?>
    The content of the variables has to be changed to match the data and table that you want to modify.
     
  7. BorderAmigos

    BorderAmigos New Member

    This is the script I came up with. There are no errors in any logs but it doesn't appear to work as the mail_content_filter table stays empty. Putting it here to see if someone can see what's not working...

    mail_content_filters.php
    Code:
    <?php
    // Note:  These includes and their ancestor directories needed 755 permissions 
    include('/usr/local/ispconfig/interface/lib/config.inc.php');
    include('/usr/local/ispconfig/interface/lib/classes/db_mysql.inc.php');
    
    $tablename = "mail_content_filter";  // I emptied this table first using phpMyAdmin
    $index_field = "content_filter_id";
    $db = new db;
    
    print "Running... ";
    $dataFile = fopen("./mail_content_filters.csv", "rb");
    while (!feof($dataFile) ) {
    	$mailFilters = fgets($dataFile);
    	$dVal = explode(',', $mailFilters);
    	$insert_data = "content_filter_id,sys_userid,sys_groupid,sys_perm_user,sys_perm_group,sys_perm_other,server_id,type,pattern,data,action,active";
    	$insert_data .= " values ";
    	$insert_data .= "$dVal[0],$dVal[1],$dVal[2],$dVal[3],$dVal[4],$dVal[5],$dVal[6],$dVal[7],$dVal[8],$dVal[9],$dVal[10],$dVal[11]";
    	$db->datalogInsert($tablename, $insert_data, $index_field);
    }
    fclose($dataFile);
    print " Finished!<br>";
    ?>
    
    mail_content_filters.csv (partial)
    Code:
    1,1,1,riud,riud,,1,body,"/^(Content-Type:.*|\s+)charset\s*=\s*""?(big5|euc-kr|gb2312|koi8|ks_c_5601-1987|Windows-1251)""?/",No foreign character sets please.,REJECT,y
    2,1,1,riud,riud,,1,body,"/(filename|name)="".*\..*""$/",No files of type $1 allowed here.,REJECT,y
    3,1,1,riud,riud,,1,body,"/(filename|name)=""(music*|dhcp*|Emanuel|kmbfejkm|Na kedWife|Seicho_no_ie|JAMGCJJA|Happy99|Navidad|pret typark|pretty|data|WTC|wtc|README)\.EXE""/",Known virus names,REJECT,y
    4,1,1,riud,riud,,1,body,"/(filename|name)=""(doc|body|file)\.scr""/",Known for MyDoom virus names 1,REJECT,y
    5,1,1,riud,riud,,1,body,"/(filename|name)=""(document|message|body|text|test) \.pif""/",Known for MyDoom virus names 2,REJECT,y
    6,1,1,riud,riud,,1,body,"/(filename|name)=""(document|message|body|text|test) \.zip""/",Known for MyDoom virus names 3,REJECT,y
    7,1,1,riud,riud,,1,body,/important information regarding your email address/,Possible W32_Mlmaill@MM,REJECT,y
    8,1,1,riud,riud,,1,body,/mailto:.*\@.*\?subject\=(3D)*(remove|removeme|plea sedelete|pleaseremove|deleteme)+/,Lets just say we are removing now.,REJECT,y
    9,1,1,riud,riud,,1,body,/.*\@.*\.ru/,No foreign character sets from ru please.,REJECT,y
    10,1,1,riud,riud,,1,body,/.*\@.*\.bg/,No foreign character sets from bg please.,REJECT,y
    11,1,1,riud,riud,,1,body,/.*\@.*\.it/,No foreign character sets from it please.,REJECT,y
    12,1,1,riud,riud,,1,body,/.*\@.*\.au/,No foreign character sets from au please.,REJECT,y
    
     
  8. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    No, do do this or you open up your system for hackers! Please restore the permissions on your system. The permissions must stay as they are. You have to execute your script as root or ispconfig to be able to include the files.

    Regarding your script, you missed a few () and also you missed to quote the content:

    Code:
    $insert_data = "(content_filter_id,sys_userid,sys_groupid,sys_perm_user,sys_perm_group,sys_perm_other,server_id,type,pattern,data,action,active)";
    	$insert_data .= " values ";
    	$insert_data .= "('$dVal[0]','$dVal[1]','$dVal[2]','$dVal[3]','$dVal[4]','$dVal[5]','$dVal[6]','$dVal[7]','$dVal[8]','$dVal[9]','$dVal[10]','$dVal[11]')";
     
  9. BorderAmigos

    BorderAmigos New Member

    The permissions were only temporary.

    I had tried both with and without the ().

    Now it gives:
    Fatal error: Call to a member function quote() on a non-object in /usr/local/ispconfig/interface/lib/classes/db_mysql.inc.php on line 280

    ...

    OK, got it working but had to modify the ISPConfig 3 code slightly. Not sure if the modification is a work around or a bug fix. Will leave that to others to decide.

    in /usr/local/ispconfig/interface/lib/classes/db_mysql.inc.php the $old_rec variable is empty and not being seen as an object. Added the line "$old_rec = $new_rec;" as shown below to define it and now my script works. Comments? Does this break something else?
    Code:
    	public function datalogInsert($tablename, $insert_data, $index_field) {
    		global $app;
    		
    		$old_rec = array();
    		$this->query("INSERT INTO $tablename $insert_data");
    		$index_value = $this->insertID();
    		$new_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'");
    		$old_rec = $new_rec;  // Added.
    		$this->datalogSave($tablename, 'INSERT', $index_field, $index_value, $old_rec, $new_rec);
    		
    		return $index_value;
    	}
    
    Also added a shell script so can be run as root instead of from the web server. When there is confirmation that this doesn't break something else I'll post the full scripts with the entire mail_content_filters.csv attached.
     
    Last edited: May 28, 2010
  10. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    Yes, the $app object from ispconfig is missing. Changing all accurrences in the diffrec function from:

    $app->db->quote(

    to:

    $this->quote(

    in the mysql class should fix this.
     
  11. BorderAmigos

    BorderAmigos New Member

    There are only 2 occurrences but in the datalogSave function, none in the diffrec function...

    $diffstr = $app->db->quote(serialize($diffrec_full));
    $username = $app->db->quote($_SESSION["s"]["user"]["username"]);

    changed to...

    $diffstr = $this->quote(serialize($diffrec_full));
    $username = $this->quote($_SESSION["s"]["user"]["username"]);

    (my previous hack removed)

    ... and does not work, error returns.
     
  12. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    Ant which error returns...? I can not guess them ;)
     
  13. BorderAmigos

    BorderAmigos New Member

    The same one I previously mentioned:

    Code:
    Fatal error: Call to a member function quote() on a non-object in /usr/local/ispconfig/interface/lib/classes/db_mysql.inc.php on line 280
    The $old_rec is being declared as array() and seems to not being passed as an object. If I just assign it to equal $new_rec this error goes away.
     
  14. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    Please post line 280.

    You misinderpreted the error message. It is not talking about passed variables. $old_rec must be an array.
     
  15. BorderAmigos

    BorderAmigos New Member

    Line 280 is
    Code:
    $diffstr = $app->db->quote(serialize($diffrec_full));
    
    Changing it to
    Code:
    $diffstr = $this>quote(serialize($diffrec_full));
    
    Does not fix it.

    $diffrec_full comes from
    Code:
    $tmp = $this->diffrec($record_old, $record_new);
    $diffrec_full = $tmp['diff_rec'];
    
    where $record_old and $record_new are passed from $old_rec and $new_rec respectively, one is an object, one is not.
    Code:
    $old_rec = array();
    $new_rec = $this->queryOneRecord("SELECT * FROM $tablename WHERE $index_field = '$index_value'");
    
    ------------

    Changing
    Code:
    $old_rec = array();
    to
    Code:
    $old_rec = (object) array();
    fixes the error. I don't think I am misinterpreting.
     
    Last edited: May 28, 2010
  16. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    You misinterpreted it. believe me, I have written this library. So undo the change with the object as this might surpress an error and cause the library to fail without error.

    The reason for the error message is a typo in line 280, you kissed a "-":

    wrong:

    $diffstr = $this>quote(serialize($diffrec_full));

    correct:

    $diffstr = $this->quote(serialize($diffrec_full));
     
  17. BorderAmigos

    BorderAmigos New Member

    You're right, I was looking at the wrong file. Local vs. on the server. And there is one additional change...
    Code:
    $app->db->query($sql);
    
    to
    Code:
    $this->query($sql);
    
    So that part of datalogSave() contains
    Code:
    		if($diff_num > 0) {
    			//print_r($diff_num);
    			//print_r($diffrec_full);
    			$diffstr = $this->quote(serialize($diffrec_full));
    			$username = $this->quote($_SESSION["s"]["user"]["username"]);
    			$dbidx = $primary_field.":".$primary_id;
    						
    			if($action == 'INSERT') $action = 'i';
    			if($action == 'UPDATE') $action = 'u';
    			if($action == 'DELETE') $action = 'd';
    			$sql = "INSERT INTO sys_datalog (dbtable,dbidx,server_id,action,tstamp,user,data) VALUES ('".$db_table."','$dbidx','$server_id','$action','".time()."','$username','$diffstr')";
    			$this->query($sql);
    		}
    
    With only the three
    $app->db->
    locations changed to
    $this->
    it appears to work now as far as putting the values in the database.

    The mail filters do not show as active in ISPConfig 3 interface though, the 'active' column in the table is all 'y'.
     
    Last edited: May 28, 2010
  18. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    Compare a activated and non activated record to see which field was set wrong as you inserted the records.
     
  19. BorderAmigos

    BorderAmigos New Member

    I did that and all the fields in that table look the same.

    Some issues still.

    Thanks.
     
  20. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    No other tables are involved, so there is a difference. Check the data that you insert again. E.g. it matters if you set the value of the active column in upper or lower case.
     

Share This Page