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.

Bug in transfer proccess - additional MySQL access hosts

Discussion in 'Database Discussions' started by Pixelation, Jul 15, 2010.

  1. Pixelation

    Pixelation Active Member

    Joined:
    Jun 3, 2004
    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    1
    I think I found a glitch in the transfer process from cPanel.

    I'm moving accounts to a new server. This new server doesn't run MySQL, but MySQL Proxy which 'redirects' MySQL connections to a different server. This means clients can still connect to "localhost" while in fact MySQL is running on a remote server. For this reason, I added the IP address of the webserver (running MySQL Proxy) to the "Additional MySQL Access Hosts" section in WHM. When a new MySQL account is created via cPanel, it will automatically grant access from my webserver IP. Which is nice!

    However, this isn't the case when accounts are transfered using the "Copy multiple accounts/packages from another server" feature. The MySQL users are restored, however they don't have access from the webserver, causing all PHP scripts to break after the transfer. Only the main MySQL user (ie: foobar) has the correct privileges, but any other MySQL user (ie: foobar_db1) hasn't.

    I tried the "mysqlupdateall" script (as suggested in the "Additional MySQL Access Hosts" section) but that doesn't resolve the issue.

    Any idea how I would resolve this problem as quick as possible?

    Thanks
     
    #1 Pixelation, Jul 15, 2010
    Last edited: Jul 15, 2010
  2. cPanelDon

    cPanelDon cPanel Quality Assurance Analyst
    Staff Member

    Joined:
    Nov 5, 2008
    Messages:
    2,557
    Likes Received:
    7
    Trophy Points:
    38
    Location:
    Houston, Texas, U.S.A.
    cPanel Access Level:
    DataCenter Provider
    Twitter:
    Given the reported details I believe the issue should be escalated via a support request; if a bug is suspected please note that the best avenue to report it is via our ticket system, such as by using the link in the top-right corner of the forums, labeled Bugs; using this method helps to ensure greater efficiency, accuracy in diagnosis, full and in-depth investigation leading to faster resolution. Thank you for your understanding.

    When available, please let me know the ticket ID number of your support request, (i.e., bug report), so that we may follow-up internally.
     
  3. Pixelation

    Pixelation Active Member

    Joined:
    Jun 3, 2004
    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    1
    Ticket ID: 770418

    Thanks
     
  4. Pixelation

    Pixelation Active Member

    Joined:
    Jun 3, 2004
    Messages:
    26
    Likes Received:
    0
    Trophy Points:
    1
    I managed to solve this issue with the following PHP script:

    PHP:
    <?php
    $config
    ['hostname'] = 'localhost';
    $config['username'] = 'root';
    $config['password'] = '*******************';
    $config['database'] = 'mysql';


    $db mysql_connect($config['hostname'], $config['username'], $config['password']) or die("Error: Unable to connect to database.\n");
    mysql_select_db($config['database'], $db) or die("Error: Unable to select database.");

    $query "SELECT DISTINCT * FROM db db1 WHERE db1.Host = '192.168.1.%' AND NOT EXISTS (SELECT * FROM db db2 WHERE db1.Db = db2.Db AND db1.User = db2.User AND db2.Host = 'XXX.XXX.XXX.XXX');";

    $results mysql_query($query$db);
    while(
    $row mysql_fetch_assoc($results)) {
        
    $query "UPDATE db SET Host = 'XXX.XXX.XXX.XXX' WHERE User = '".$row['User']."' AND Db = '".$row['Db']."' AND Host = '192.168.1.%';";
        echo 
    $query "\n";
        
    $results2 mysql_query($query$db);
        if (!
    $results2) {
            echo 
    'Invalid query: ' mysql_error() . "\n";
        }
    }

    /////////////////////////////////////////////////////////////////

    $query "SELECT DISTINCT * FROM user user1 WHERE user1.Host = '192.168.1.%' AND NOT EXISTS (SELECT * FROM user user2 WHERE user1.User = user2.User AND user2.Host = 'XXX.XXX.XXX.XXX');";

    $results mysql_query($query$db);
    while(
    $row mysql_fetch_assoc($results)) {
        
    $query "UPDATE user SET Host = 'XXX.XXX.XXX.XXX' WHERE User = '".$row['User']."' AND Host = '192.168.1.%';";
        echo 
    $query "\n";
        
    $results2 mysql_query($query$db);
        if (!
    $results2) {
            echo 
    'Invalid query: ' mysql_error() . "\n";
        }
    }
    ?>
    It's not the best solution, but it does the trick.

    EDIT: Note that for some odd reason, cPanel added privileges for 192.168.1.% instead of my webserver's IP address (XXX.XXX.XXX.XXX).
     
    #4 Pixelation, Jul 16, 2010
    Last edited: Jul 16, 2010
Loading...

Share This Page