Bug in transfer proccess - additional MySQL access hosts

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

 

Ticket ID: 770418

Thanks

 

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).