The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Auto-Creation Of MySQL DB with postwwwacct

Discussion in 'cPanel Developers' started by Tyler.S, Sep 26, 2010.

  1. Tyler.S

    Tyler.S Member

    Joined:
    Sep 25, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ontario Canada
    I am trying to create a script using your example code for auto-creation of a mysql db and user with all the privs. Found HERE

    I have completed modifying the script and I have substituted

    PHP:
    //create our xmlapi object and set it's params
    $xmlapi = new cpScriptsXmlApi($argv,'127.0.0.1');
    //your root auth hash
    $hash file_get_contents('/root/.accesshash');
    $xmlapi->set_user('root');
    $xmlapi->set_hash($hash);
     
    $xmlapi->set_port('2087');
    For:

    PHP:
    //create our xmlapi object and set it's params
    $xmlapi = new cpScriptsXmlApi($argv,'**.**.**.***');
    $xmlapi->set_port('2087');
    $xmlapi->set_password("root""**********************");
    Information "*'ed" out for security purposes of course. Now upon account creation it does say it has successfully run the postwwwacct script and no errors are returned but when I check there has been no DB or users created.

    I have attached the full script to this post. I will also post the full code below.

    Any help would be greatly appreciated,

    Thank You


    Full Script:

    PHP:
    #!/usr/bin/php -q

    <?php

    //set error handling so any warnings are logged
    ini_set('error_log','/usr/local/cpanel/logs/error_log');
    ini_set('display_errors',0);

    //include basic xmlapi client class
    include('/home/cpanelscripthelpers/xmlapi.php');

    /**
    * extend the basic xmlapi class
    *  add the method for getting args
    */
    Class cpScriptsXmlApi extends XMLAPI
    {

        public 
    $cliargs = array();
        
        
    /**
         * constructor
         *
         *@param array $scriptargs cli $argv that will be parsed
         *@param string $host 
         *@param string $user
         *@param string $password
         *@return cpScriptsXmlApi
        */
        
    public function __construct($scriptargs = array(), $host null$user null$password null)
        {
                
    parent::__construct($host,$user,$password);
                
    $this->cliargs $this->argv2array($scriptargs);
                return 
    $this;
        }
        
        
        
    /**
         * Simple method to store args into an array
         *
         *@params array $argv shell array to be parsed
         *@return array
        */
        
    public function argv2array ($argv)
        {
                
    $opts = array();
                
    $argv0 array_shift($argv);
        
               while(
    count($argv)) {
                        
    $key array_shift($argv);
                        
    $value array_shift($argv);
                        
    $opts[$key] = $value;
              }
              return 
    $opts;
        }

        
    /**
         * Create a database
         *
         *@param string $user cpanel user to create db as
         *@param string $dbname name for database
        */
        
    public function createUserDb($user,$dbname)
        {
              
    $args = array($dbname); 
              return 
    $this->api1_query($user,'Mysql','adddb',$args);
        }
          
        
    /**
         * Create a db virtuser
         *
         *@param string $user cpanel user to create virtuser as
         *@param string $virtusername name for db virtuser
         *@param string $password password for new db virtuser
        */
        
    public function createDbVirtuser($user,$virtusername,$password){
            
    $args = array($virtusername,$password);
                return 
    $this->api1_query($user,'Mysql','adduser',$args);
        }
          
        
    /**
         * Assign user privs 
         *
         *@param string $user cpanel user to work on behalf of
         *@param string $dbname name of database
         *@param string $virtusername receiver of privs
         *@param array  $privs array of privileges to assign.
        */
        
    public function assignUserPrivs($user,$dbname,$virtusername,$privs = array())
        {
            
    $privs =  (empty($privs))? array('all'): $privs//not the best, you can change the default if you wish
            
    $priv_str implode(',',$privs);
            
    $args = array($dbname$virtusername$priv_str);
            return 
    $this->api1_query($user,'Mysql','adduserdb',$args);
         }
    }


    //create our xmlapi object and set it's params
    $xmlapi = new cpScriptsXmlApi($argv,'**.**.**.***');
    $xmlapi->set_port('2087');
    $xmlapi->set_password("root""**********************");


    //generic vars for automation//
    // db resource names should be small, no more than 7 chars for sure
    $dbname 'Hmail'// "a database"
    $virtusername 'Hadmin'// "a user"
    $privs = array('all'); //you probably what to look into what you need; 'all' is very liberal

    //determine if db mapping is on
    $prefixing 1//default, may not be explicitly defined in config
    $config file('/var/cpanel/cpanel.config');

    foreach(
    $config as $key=>$value){
        if(
    stripos($value,'database_prefix=') === 0){
            
    $prefixing substr(trim($value),-1);  // bool/int
        
    }
     }


    // make a prefix that allows max length of primary database username
    //  not perfect; possible collision, but that would happen even in non-automated 
    //   db resource creation if usernames are long (cpanel softlimits to 8 char, so the point should be moot)
    //   NOTE: db users have a MySQL hardlimit of 16 char, our generic user name are 4 char 16 - 4 - 1 for userscore = 11 usable
    $dbprefix = (strlen($xmlapi->cliargs['user']) > 11)? substr($xmlapi->cliargs['user'], 0,10) : $xmlapi->cliargs['user'];

    if( (int)
    $prefixing === ){
        
    // prefixing is off
        // double check the primary username. any prefix should be based on that name
        
    if($xmlapi->cliargs['user'] != $xmlapi->cliargs['dbuser']){
            
    $dbprefix = (strlen($xmlapi->cliargs['dbuser']) > 11)? substr($xmlapi->cliargs['dbuser'], 0,10) : $xmlapi->cliargs['dbuser'];        
        }

        
    //since prefixing is off, we should use literal names in both creation and assignment
        
    $xmlapi->createUserDb($xmlapi->cliargs['user'], $dbprefix.'_'.$dbname);
        
    $xmlapi->createDbVirtuser($xmlapi->cliargs['user'],$dbprefix.'_'.$virtusername$xmlapi->cliargs['pass']); //setting passwd same is not wise, but is done per commission request

        
    $xmlapi->assignUserPrivs($xmlapi->cliargs['user'], $dbprefix.'_'.$dbname$dbprefix.'_'.$virtusername$privs);
        
        
    $xmlapi->assignUserPrivs($xmlapi->cliargs['user'], $dbprefix.'_'.$dbname$xmlapi->cliargs['dbuser'], $privs);
        echo 
    "Auto-generated database '".$dbprefix.'_'.$dbname."' for database user '".$dbprefix.'_'.$virtusername."'.\n";
     
        
    }else{
        
    // prefixing is ON, default on all cpanel systems 
        // prefix only on assignment
        
    $xmlapi->createUserDb($xmlapi->cliargs['user'], $dbname);
        
    $xmlapi->createDbVirtuser($xmlapi->cliargs['user'],$virtusername$xmlapi->cliargs['pass']); //setting passwd same is not wise, but is done per commission request

        
    $xmlapi->assignUserPrivs($xmlapi->cliargs['user'], $dbprefix.'_'.$dbname$dbprefix.'_'.$virtusername$privs);
        echo 
    "Auto-generated database '".$dbprefix.'_'.$dbname."' for database user '".$dbprefix.'_'.$virtusername."'.\n";
    }
     

    Attached Files:

  2. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Hi Tyler,

    I'm not able to test your code at this exact moment but I should be able to run it tomorrow, once I'm back in the office. In the meantime, can you check your error_log for any messages (generated by the postwwwacct script)?

    I might be easiest to just start tailing the log ('tail -f /usr/local/cpanel/logs/error_log') and then use WHM or /scripts/wwwacct to create a new account. You may get some general warnings during the account creation, but any warning or error messages coming from the postwwwacct script (or the API calls it makes) would certainly lead to the resolution of this mysterious failure.

    Another thing that will be important to know is what version of WHM/cPanel you're using.

    There's also two other debugging tools at your ready. One is the API Tracer. It's a CustomEventHandler that will log API input/output into the cPanel error_log.

    Second is MySQL general logging. This forum post will tell you how to enable it, depending on which version of MySQL you're using, if you're not already familiar with MySQL loggin.

    NOTE: you should NOT perform either of these logging mechanisms on a production server, as they record raw queries which may have sensitive information.

    At a cursory glance, I don't see anything that immediately jumps out as incorrect; like I said, I test it and get back with you. Check out your error_log and post back any relevant info.

    Regards,
    -DavidN
     
  3. Tyler.S

    Tyler.S Member

    Joined:
    Sep 25, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ontario Canada
    (Updated)

    Hello David,

    Thanks for the reply. Here is the requested information;

    WHM 11.26.8
    CENTOS 5.5 i686 virtuozzo

    I am currently running it in a production environment but I will see what I can do.

    I inserted the API Tracer as you suggested and created a new account in WHM and it spit out a bunch of lines. I have attached [UPDATED, removed for security] it as it is too much to paste on here. I have also deleted logs from before tonights date as they are irrelevant.

    When I create an account it verifies that it has run the postwwwacct and comes back with a green checkmark and no errors.

    There was also nothing in the mysql error log. Very confusing...I wonder if it might just be a connection issue. I will try different connection variations and will report back.
     
    #3 Tyler.S, Sep 26, 2010
    Last edited: Sep 26, 2010
  4. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Thanks for the info. I didn't see anything in your error_log related to MySQL resource creation. So, I think that the script hook isn't actually being fired. The green check mark, unfortunately, has little bearing on the actual run status of the postwwwacct script. It's more related to that fact that it exists.

    FYI: Since, you error_log was generated from a production server, I've updated your post and removed the error_log attachment to ensure that any sensitive information wasn't accidentally posted (though all I saw was API calls from the cPanel UI rendering; all benign).

    I will be setting up my test environment and testing your script now and will post back with my results.

    Regards,
    -DavidN
     
  5. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Okay, so we have a few things going on:

    1) double check the permissions on your script; it should have the execution bit set. I'm almost certain that this is why it wasn't showing any relevant data in the error_log. It simply wasn't an executable shell script.

    2) Where you've changed the authentication type, you're using 'set_password()'. 'set_password()' only takes a password value. If you want to set the full credentials in one method call, use 'password_auth($user,$pass)' instead. Otherwise you need to call 'set_user()' to establish the authenticating user (ie, 'root').

    3) The last item has to do with ensuring the right defaults are passed/assumed in the script.

    Add this code at line 120, or anywhere after you've instantiated the cpScriptsXmlApi object and before the "if( (int)$prefixing === 0 ){" logic block. This will ensure that if the account creation function doesn't pass a 'dbuser' variable that the proper default is set for our script. (this was simpler than rewriting various other logic in the script).

    Code:
    if(!array_key_exists('dbuser', $xmlapi->cliargs)){ 
        //the following is safe because a system that doesn't pass this variable
        // assumes that the parameters are synonymous when performing
        // Mysql actions in the backend
     
        $xmlapi->cliargs['dbuser'] = $xmlapi->cliargs['user'];
    }   
    
    Once you've made those changes, you code should work for you cPanel version.

    Best Regards,
    -DavidN


    FYI: If anyone is attempting to use this script on a NIGHTLY build, you may experience unexpected behavior (often the case with anything in NIGHTLY ;) ). PM if you are one of those folks and need assistance.
     
  6. Tyler.S

    Tyler.S Member

    Joined:
    Sep 25, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ontario Canada
    Great thanks. It finally works. I was still having issues until I found out why.

    1. was using old <?php opening tag and no closing tag

    2. file was stored on server as .php instead of no extension

    3. the file was not CHMOD to 755

    Thank you for your help. Now, any Ideas on how to get it to auto populate some tables into the DB? Also I was wondering if you might be able to point me into the correct direction on how to start with an auto-chmod for files copied with the skel directory?
     
  7. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Glad it's working.

    In response to your last questions: You can do both of those things using Bash or Perl. Those are typical shell script things. You can do them in PHP too; It's just I typically prefer a more mature/robust scripting language when I start doing file manipulation.

    I suggest opening a new thread for those topics; maybe someone already has a solution prepared that they're willing to share!.

    Best Regards,
    -DavidN
     
  8. Tyler.S

    Tyler.S Member

    Joined:
    Sep 25, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ontario Canada
    Ok, well would it be possible to just add at the end of the script a mysql connect and then a create tables code, or would I have to create a whole new script and include it?

    I hope I can just use it in the same script because it would be easier to use the same variables then having to pull them all on a different script.

    UPDATE:

    Also in the postwwwacct that we setup, how do I pull the password that was created for the db user. This is what I have so far for the mysql connect:

    Would I just use the : $password variable?

    PHP:
    $con mysql_connect("localhost",$dbprefix.'_'.$virtusername,$password);
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }
      
      
    mysql_select_db($dbprefix.'_'.$dbname$con);
      
      
    mysql_query($sql,$con);
    mysql_close($con);
     
    #8 Tyler.S, Sep 27, 2010
    Last edited: Sep 27, 2010
  9. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Both are correct:

    1) You can include whatever you need in your postwwwacct script, including direct mysql_connect() methods for populating databases and/or the chmod stuff.

    2) the sample script that I provided in that blog post does not have any sophisticated password generation; that is left up to the end developer. The current, sample script just uses the same password as the cPanel password. S, yes, you should be able to something like what you have in you sample code.

    Side Note1: When an account is created in cPanel, that account also receives a MySQL user with the same name and password. So, technically, you could use that cPanel credentials when populating any databases owned by that cPanel account.

    Side Note2: I wanted to make clear that the use of mysql_connect (and friends) should only be used for database population. Even though your postwwwacct script has root authentication access to MySQL, it's important that developers refrain from creating databases and database users directly. cPanel has added the API abstraction layer for an important reason. The cPanel software manages the ownership of databases in the shared hosting environment. If created manually, there will be a lack of 'bookkeeping' and the end result will likely be more support issues for hosting companies and their end users.

    Regards,
    -DavidN
     
  10. Tyler.S

    Tyler.S Member

    Joined:
    Sep 25, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ontario Canada
    The way I am creating the script would be to use the newly created db user to connect to the db and create the tables and populate them. I wouldn't be using the root credentials for that.
     
  11. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Tyler,

    That's good to hear. I presumed that what your intent was. I only stated the root/MySQL thing for clarity to anyone who is reading this thread, it was not directed to you specifically.

    Unfortunately, I've seen people say "well, if the script is running as root, then why don't I connect to MySQL and just make the end user databases myself; not use the API at all". And while that seem logical, it's a narrow and innocent point of view that can lead to serious complications for the hoster/sysadmin and end user. This is especially true if you're running cPanel 11.25.1/11.27 or 11.28 and higher.


    Regards,
    -DavidN
     
  12. Tyler.S

    Tyler.S Member

    Joined:
    Sep 25, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ontario Canada
    Ok David,

    This is what I have been able to come up with. I have posted both the code and the error_log. It is creating the database, the db user, but it's not creating the tables or inputting the information into it.

    Any ideas?
     

    Attached Files:

  13. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    From error_log:
    The problem is that you're not properly interpolating the $user variable when you try to load you db_mysql.php

    Code:
    // WRONG: the $user variable is taken literally because
    //   the string has single quotes. This is how you have it on line 123
    require_once('/home/$user/public_html/mail/includes/db_mysql.php');   
    
    // RIGHT: use double quotes
    require_once("/home/$user/public_html/mail/includes/db_mysql.php");   
    
    // RIGHT: or you can concatenate
    require_once('/home/' . $user . '/public_html/mail/includes/db_mysql.php');   
    
    Regards,
    -DavidN
     
  14. Tyler.S

    Tyler.S Member

    Joined:
    Sep 25, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ontario Canada
    I did what you suggested but it is still not creating the tables or insterting any information to the tables that were to be created. I am still receiving this error:

    Code:
    [29-Sep-2010 18:14:08] PHP Warning:  require_once(/home//public_html/mail/includes/db_mysql.php) [<a href='function.require-once'>function.require-once</a>]: failed to open stream: No such file or directory in /scripts/postwwwacct on line 123
    [29-Sep-2010 18:14:08] PHP Fatal error:  require_once() [<a href='function.require'>function.require</a>]: Failed opening required '/home//public_html/mail/includes/db_mysql.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /scripts/postwwwacct on line 123
    
    Here is line 123:

    PHP:
    require_once('/home/'.$user.'/public_html/mail/includes/db_mysql.php');
    Any thoughts?
     
  15. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Well, the error clear states that $user is blank, ie "". You can see that the file that the PHP engine is trying to "require_once" is "/home//public_html/.....". For whatever reason, at that point in your code the $user value is not defined.


    I'd suggest reviewing you code to verify that
    A) $user is the proper variable in that context
    B) $user is proper set and has not possibly be over written when you do the various includes and other PHP code (that you've added)

    -DavidN
     
  16. Tyler.S

    Tyler.S Member

    Joined:
    Sep 25, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ontario Canada
    What would I use as a variable to pull the cPanel account username that is being created?
    PHP:
    '$xmlapi->cliargs['user']'
    ?
     
  17. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Yes, that should be an acceptable variable, if I understand your intentions properly. However, I have a question: Do all your user's have that directory path and file upon creation (ie, in a skeleton directory)? Otherwise, you'll need to change that path.

    -DavidN
     
  18. Tyler.S

    Tyler.S Member

    Joined:
    Sep 25, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ontario Canada
    Yes they will all have the same structure. I am just trying to automate the installation of our script for our hosted solutions so that it doesn't take up either our client's time or our admin's. Once this is working it will cut install times from 30-45mins to 30 secs! lol

    Here is what I got so far:

    PHP:
    require_once("/home/$xmlapi->cliargs['user']/public_html/mail/includes/db_mysql.php");
    Thanks again for the help David, I really do appreciate it!
     
  19. Tyler.S

    Tyler.S Member

    Joined:
    Sep 25, 2010
    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Ontario Canada
    I am still receiving the same error, the only thing that changes is the variable that I enter. Here is a snippet from the error_log:

    Code:
    
    [30-Sep-2010 06:10:34] PHP Fatal error:  require_once() [<a href='function.require'>function.require</a>]: Failed opening required '/home/Array['user']/public_html/mail/includes/db_mysql.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /scripts/postwwwacct on line 123
    
    I'm at a loss. :mad:
     
  20. cPanelDavidN

    cPanelDavidN Integration Developer
    Staff Member

    Joined:
    Dec 17, 2009
    Messages:
    571
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Again, it's about proper interpolation.

    I hardly ever interpolate my variables in strings; always concatenate with '.' so that this stuff is avoided. It may not be as pretty, but it always works and the intent is clear when reading the code. Other coders may disagree with me on that last point, but as far as your code is concerned, you won't be able to source an object's member's contents. Apparently, the reason has something to do with how PHP internally references variable stores.

    simply try:
    Code:
    $path = "/home/" 
            . $xmlapi->cliargs['user']
            . "/public_html/mail/includes/db_mysql.php"
    ;
    
    require_once($path);
    
    
    -DavidN
     
Loading...

Share This Page