Perl How to change account host using DBI module?

Question

My company has a Perl application in server with IP : 10.10.3.39

And because of the new rule implemented, I must migrate the database to MySQL DB in server with IP : 10.10.1.18

My company DB administrator has create an account and grant the access for the apps with username : 'user'@'10.10.3.39'. So the account just can be used from server with IP 10.10.3.39

I tried the connection in the server using command mysql -h 10.10.1.18 -u user -p

[hanief@dev39 project]$ mysql -h 10.10.1.18 -u user -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19484169
Server version: 10.0.15-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

I have a database connection script test_db.pm in Perl like this:

use DBI;

$user = "user";
$pw = 'password';
$dbh = DBI->connect("DBI:mysql:database=mysql;host=10.10.1.18;mysql_enable_utf8=1",$user, $pw, %attr) or die "Cannot connect to MySQL server\n";

And then, I tried to run it using command perl test_db.pm, I got this error

[hanief@dev39 project]$ perl test_db.pm
DBI connect('database=mysql;host=10.10.1.18;port=3306;mysql_enable_utf8=1','user',...)
failed: Access denied for user 'user'@'%' to database 'mysql' at test_db.pm line 6.
Cannot connect to MySQL server

I don't know why the account name suddenly has @'%' behind it

I have tried to change the variable $user to :

• $user = "user\@10.10.3.39";

failed: Access denied for user '[email protected]'@'10.10.3.39' (using password: YES) at test_db.pm line 6.

• $user = "user\@'10.10.3.39'";

failed: Access denied for user 'user@'10.10.3.39''@'10.10.3.39' (using password: YES) at test_db.pm line 6.

• $user = "'user'\@'10.10.3.39'";

failed: Access denied for user 'user'@'10.10.3.39'@'10.10.3.39' (using password: YES) at test_db.pm line 6.

But still, didn't work. The server still can't connected to DB server.

I Don't know why there's an extra @'10.10.3.39' behind the user account. And don't know why % before, suddenly changed to 10.10.3.39

Any solution for my case?

Enamor

Have you tried setting the debug level up so you can see some of what DBI and DBD::MySQL (I'm not sure of the capitalization for MySQL in the module name) indicate more of what they are doing? You might be able to see where the changes (which are mysterious to me) are coming from. Are you using the same version of Perl as before? The same version of DBI? Of DBD? Can you still connect to the old server? (I guess it probably isn't running any more, but it would be better if they'd left it around until the new system is stabilized.) – Eustasius

Setting the DBI_TRACE environment variable to a non-zero value may help. Using DBI_TRACE=9 gives approximately the most information available. – Eustasius

@JonathanLeffler Yeah, I still using same version of perl, DBI and DBD. Of course I still can connect to the old mysql server, which is on the same server with the apps. But because of the new rule I must migrate it ASAP. For now it's still works (because we still using old DB) – Enamor

Will try it the your suggestion about DBI_TRACE. – Enamor

If you can still access the old server, then run the connection to that with DBI_TRACE set and capture the output (it is written to standard error, so you might need perl yourscript.pl >output.1 2>&1 (assuming a Bourne/POSIX style shell). Then run the connection to the new server with DBI_TRACE set and capture that output. Now compare the two sets of outputs to see what is different. There should be something that's different. But I know not what. (Disclaimer: I'm not a MySQL user — but I did write most of the DBD::Informix driver, and I still maintain it, albeit not very actively). – Eustasius

Answer 1

I'm not entirely sure what's going on here, but I think I can shed a little bit of light.

MySQL (and, hence, MariaDB) has a login system that isn't just dependent on usernames and passwords. It also takes into account the host that the connection is coming from. If you look at the mysql.user table, you'll see that the first three columns are Host, User and Password.

The Host column is interesting. It can either contain an IP address or a hostname, but it can also contain the symbol '%', which means "any host".

So when the DBA told you they had:

create an account and grant the access for the apps with username : 'user'@'10.10.3.39'

I'm betting they mean they've inserted the values user and 10.10.3.39 in the mysql.user table.

That means your $user variable needs to be set to user, not [email protected] - as MySQL will work out the IP address from your incoming connection.

So when you see errors like this:

failed: Access denied for user '[email protected]'@'10.10.3.39'

the bit inside the first pair of quotes ("[email protected]") is the username you're trying to use and the bit inside the second pair of quotes ("10.10.3.39") is the IP address that MySQL thinks you're connecting from. Obviously, that's not going to work because that username doesn't exist in the mysql.users table.

So, we're left looking at your first example:

$dbh = DBI->connect("DBI:mysql:database=mysql;host=10.10.1.18;mysql_enable_utf8=1",$user, $pw, %attr)
  or die "Cannot connect to MySQL server\n";

Which gives this error:

failed: Access denied for user 'user'@'%'

Now, the fact that this error uses "%" as your hostname, seems to imply that the MariaDB server didn't recognise the IP address that you're coming from as one of the specific IP addresses listed as allowed for your user.

So, two things I would double-check in this instance:

• Are you definitely trying to connect from 10.10.3.39? This includes checking if there are any proxies between you and the DB server that might change your IP address.
• Is the IP address in the mysql.user table really 10.10.3.39? Or does the value, perhaps, contain a typo?

I'm aware that this isn't a "here's the solution to your problem" post but, hopefully, it explains a bit more about what is going on and gives you a couple of avenues to investigate.

Judsonjudus

thanks @Dave Cross, I will try to investigate the issue based on your directive first. And will communicate with the DBA to check what he really set. – Enamor