datalogInsert not creating web database users

Discussion in 'Installation/Configuration' started by ircf, Feb 6, 2014.

  1. ircf

    ircf New Member

    Hello,

    We are developping a PHP script to migrate from our current ISPC 3.0.3 architecture to our new ISPC 3.0.5.3 architecture (services like web domains are redistributed on different web servers).

    We managed to migrate DNS, web domains, ftp users, mail, web databases successfully, but we have a problem on migrating web_database_user : db users are created on dbispconfig master and slave databases, they appear on ISP Config web interface, but mysql users are NOT created on the slave server. Any idea to solve that pls ?

    We don't see any error on ISP config master/slave logs neither on mysql master/slave logs.

    Here is a sample of our script, which runs on the new master server :

    PHP:

    function confirm($text){
      
    ob_end_flush();
      echo 
    $text.' ? (y/n) ';
      do {
        
    $response strtolower(fgetc(STDIN));
      } while (
    $response!='y' && $response!='n');
      return 
    $response=='y';
    }

    echo 
    'Loading ISP Config libraries...';
    try{
      global 
    $app;
      require_once(
    '/usr/local/ispconfig/interface/lib/config.inc.php');
      require_once(
    '/usr/local/ispconfig/interface/lib/app.inc.php');
      echo 
    "OK\n";
    }catch (
    Exception $e){
      echo 
    "KO (".$e->getMessage().")\n";
      exit(
    0);
    }

    echo 
    'Initialize db connections...';
    try{
      
    $old_db = new PDO('mysql:host=old_ispconfig_master;dbname=dbispconfig''root''old_dbispconfig_password');
      
    $new_db $app->db;
      echo 
    "OK\n";
    }catch (
    Exception $e){
      echo 
    "KO (".$e->getMessage().")\n";
      exit(
    0);
    }

    echo 
    'Purge datalog...';
    $new_db->query('delete from sys_datalog');
    echo 
    "OK\n";

    // ... removed irrelevant code to migrate dns, web domains, ftp users, mail ...

    if (confirm('Migrate web_database_user')) {
      echo 
    'Migrate web_database_user...';
      
    ob_end_flush();
      try{
        
    $new_db->query("delete from web_database_user");
        
    $sth $old_db->query("select * from web_database");
        while(
    $r $sth->fetch(PDO::FETCH_ASSOC)) {
          
    $r['database_user_id'] = $r['database_id'];
          
    $r['server_id'] = 4// ID of our new slave server (which is different from the ID of the old one)
          
    $r['database_user_prefix'] = '';
          
    $data "(database_user_id, sys_userid, sys_groupid, sys_perm_user, sys_perm_group, sys_perm_other, server_id, database_user, database_user_prefix, database_password) values ('{$r['database_user_id']}', '{$r['sys_userid']}', '{$r['sys_groupid']}', '{$r['sys_perm_user']}', '{$r['sys_perm_group']}', '{$r['sys_perm_other']}', '{$r['server_id']}', '{$r['database_user']}', '{$r['database_user_prefix']}', '{$r['database_password']}')";
          
    $new_db->datalogInsert('web_database_user'$data'database_user_id');
        }
        echo 
    "OK\n";
      }catch(
    Exception $e){
        echo 
    "KO (".$e->getMessage().")\n";
        exit(
    0);
      }
    }

    We turned on mysql logs on the slave to see what's going on, but we don't have any "create user" query :

    Code:
    121 Query	REPLACE INTO web_database_user (`database_user_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`server_id`,`database_user`,`database_user_prefix`,`database_password`) VALUES ('99','1','2','riud','ru','','4','dbuser','','dbpass')
    121 Query	UPDATE server SET updated = 295074 WHERE server_id = 4
    
    Thank you for your help :)

    NB : This script is intended to run many times until the definitive migration (for testing purposes), that's why we delete records on the master before datalogInsert, I suspect part of the problem is that we don't datalogDelete. Is there any simple way to make a "differential" import (insert/update/delete) or do we have to check manually each row and use the appropriate datalog method ?

    EDIT : I tried to delete from web_database_user; on the slave database so that the script would insert new users on the slave instead of replace insert, but that didn't create mysql users...
     
    Last edited: Feb 6, 2014
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    The database users get added when the database is added. But why dont you use the remote api, which is made for that purpose?
     
  3. ircf

    ircf New Member

    Thank you Till,

    Indeed, I tried to remove db and users on both master/slave databases and re-run migration script on both to create db and users.

    Here is the mysql log on the slave :

    Code:
    228 Query	REPLACE INTO web_database_user (`database_user_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other
    `,`server_id`,`database_user`,`database_user_prefix`,`database_password`) VALUES ('97','1','2','riud','ru','','4','dbuser','','dbpass')
    		  228 Query	UPDATE server SET updated = 295270 WHERE server_id = 4
    ...
    228 Query	REPLACE INTO web_database (`database_id`,`sys_userid`,`sys_groupid`,`sys_perm_user`,`sys_perm_group`,`sys_perm_other`,`server_
    id`,`parent_domain_id`,`type`,`database_name`,`database_name_prefix`,`database_user_id`,`database_ro_user_id`,`database_charset`,`remote_access`,`remote_ips`,
    `backup_interval`,`backup_copies`,`active`) VALUES ('97','1','2','riud','ru','','4','5','mysql','dbname','','97','0','','n','','none','1','y')
    		  228 Query	UPDATE server SET updated = 295276 WHERE server_id = 4
    		  232 Connect	[email protected] on 
    		  232 Query	CREATE DATABASE dbname
    		  228 Query	SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '97'
    		  228 Query	SELECT `database_user`, `database_password` FROM `web_database_user` WHERE `database_user_id` = '0'
    		  232 Query	GRANT ALL ON dbname.* TO 'dbuser'@'localhost' IDENTIFIED BY PASSWORD 'dbpass'
    		  232 Query	FLUSH PRIVILEGES
    
    But still can't see the "CREATE USER" query... I'm gonna look more closely at the mysql plugin to see what I'm missing.

    I will also try to use the remote API to make a cleaner differential migration.

    Thank you again.
     
  4. ircf

    ircf New Member

    Fixed

    I finally created a web_database_user using ISP Config web interface and adapted my script so that the user works. Here it is if someone is interested (modified lines are commented as "FIXED") :

    Code:
    if (confirm('Migrate web_database_user')) {
      echo 'Migrate web_database_user...';
      ob_end_flush();
      try{
        $new_db->query("delete from web_database_user");
        $sth = $old_db->query("select * from web_database");
        while($r = $sth->fetch(PDO::FETCH_ASSOC)) {
          $r['sys_perm_group'] = 'riud'; // FIXED instead of ru
          $r['database_user_id'] = $r['database_id'];
          $r['server_id'] = 0; // FIXED 0 instead of server_id
          $r['database_user_prefix'] = '';
          $r['database_password'] = '*'.strtoupper(sha1(sha1($r['database_password'], true))); // FIXED password is now stored encrypted, this is the php equivalent for mysql password()
          $data = "(database_user_id, sys_userid, sys_groupid, sys_perm_user, sys_perm_group, sys_perm_other, server_id, database_user, database_user_prefix, database_password) values ('{$r['database_user_id']}', '{$r['sys_userid']}', '{$r['sys_groupid']}', '{$r['sys_perm_user']}', '{$r['sys_perm_group']}', '{$r['sys_perm_other']}', '{$r['server_id']}', '{$r['database_user']}', '{$r['database_user_prefix']}', '{$r['database_password']}')";
          $new_db->datalogInsert('web_database_user', $data, 'database_user_id');
        }
        echo "OK\n";
      }catch(Exception $e){
        echo "KO (".$e->getMessage().")\n";
        exit(0);
      }
    }
    
    Note : This is just a quick and dirty migration script from 3.0.3 to 3.0.5.3, it won't apply for any other version, like Till said it's better to use the remote API.
     

Share This Page