MySQL Lists are EOL. Please join:

List:MySQL and Perl« Previous MessageNext Message »
From:Dave Howorth Date:June 23 2005 11:16am
Subject:Re: DBD::mysql anonymous user problem
View as plain text  
Michael Stassen wrote:
> Dave Howorth wrote:
>  > I'm trying to upgrade DBD::mysql and having trouble with the anonymous
>  > user for the test database. When I run the install the tests fail with
>  > messages like:
>  >
>  >   DBI connect('test','',...) failed: Access denied for user:
>  >   'root@localhost' (Using password: NO) at t/50chopblanks.t line 57
>  >   Cannot connect: Access denied for user: 'root@localhost' (Using
>  >   password: NO)
> Look at the error message.  The tests are attempting to log into mysql 
> as user root@localhost with no password.

Hi Michael,

OK, I'm with you so far ...

> <snip>
>  > According to the mysql manual:
>  >
>  > "The DBD::mysql installation runs a number of tests. These tests require
>  > being able to connect to the local MySQL server as the anonymous user
>  > with no password. If you have removed anonymous accounts or assigned
>  > them passwords, the tests fail."
> This is from <>.
> The manual is wrong.

Hmm. This is a pretty bold statement and I have no way to judge whether 
you're right or not. Can anybody else confirm this?

In the meantime, let's assume you're right and carry on ...

>  By default, the tests do not specify the user or 
> password.  With neither specified, mysql will use defaults.  If the 
> current unix user (when using CPAN, this is usually root, as in your 
> case) exists as user@localhost in mysql, that is the default.  If 
> user@localhost does not exist, then the anonymous user, ''@localhost, is 
> the next option, if it exists.

This sounds like it could be handled better, then?

You say 'mysql will use defaults'. Do you mean mysql or the Perl 
interface? Because your description of the mechanism doesn't seem to 
match that given in 5.6.5. Access Control, Stage 1: Connection 
Verification at

But again, assuming your description is right ...

As I understand it, mysql creates its 'root' user when it's installed 
and as you say the DBD::mysql install will almost certainly be run as 
root. So trying to connect as root@localhost will be the normal use case 
for the tests? And 'good doobies' will have secured that account as the 
mysql manual suggests. Particularly if this is an upgrade of DBD::mysql 
rather than an initial install. Why isn't it set up to use the anonymous 
user for the tests, which would match mysql's default configuration?

I find the anonymous user baffling, because I haven't been able to find 
any documentation apart from the mysql manual, which doesn't describe 
the Perl syntax. There's no mention in DBI (but then again it's 
mysql-specific, yes?). There's also no mention in DBD::mysql, which 
surprises me. There's no index entry in the couple of books I have. I 
didn't find anything useful with Google. Is there a good description 
anywhere? What is the syntax to establish a connection as an anonymous user?

>  > I haven't changed the anonymous user accounts AFAIK. I did add some
>  > extra accounts. My mysql.user table  looks like this:
>  >
>  > mysql> select host,user,password,select_priv from user;
>  > +--------------------+----------------+------------------+-------------+
>  > | host               | user           | password         | select_priv |
>  > +--------------------+----------------+------------------+-------------+
>  > | localhost          | root           | 5b3e0551192fc0ec | Y           |
>  > | suse3.lmb.internal | root           | 5b3e0551192fc0ec | Y           |
>  > | localhost          |                |                  | N           |
>  > | suse3.lmb.internal |                |                  | N           |
>  > | %                  | test           | 378b243e220ca493 | N           |
>  > | %                  | test@localhost | 378b243e220ca493 | N           |
>  > | localhost          | test           | 378b243e220ca493 | N           |
>  > +--------------------+----------------+------------------+-------------+
>  > 7 rows in set (0.00 sec)
> The next to last row, user = test@localhost, host = %, makes no sense 
> and should probably be dropped.  I'd also recommend reconsidering 
> whether you really need test@%, from a security standpoint.  If you do 
> need an external test user, you may still be able to restrict the hosts 
> from which test can connect to something less than every machine in the 
> world.  Also, as you've gone to the trouble of creating a test user, you 
> should consider whether you really need the anonymous user anymore.  I 
> think most of us delete the anonymous user accounts at the same time as 
> we give root a password, again for security reasons.  Passwordless 
> access to mysql just doesn't seem safe.

Thanks for this advice. The test@localhost line is an error; I created 
it by accident but wanted to show the complete environment that was in 
place at the time of the problem I was reporting. I also agree about 
deleting test@% - I was just setting up all possible combinations to get 
the @*%$ thing to run :)

The only reason I created the test user was because the tests didn't 
seem to run with the anonymous user as the docs say they should. 
Personally, I think it would all be much clearer if mysql automatically 
set up a 'test' database with a 'test' user with a 'test' password and 
if DBD::mysql (and every other Perl module!) used that by default.

But I just want to end up with a standard installation where whoever 
maintains this system after I move on will be able to leverage their 
knowledge and not have to deal with special cases (or bother with 
reading my documentation, if I'm an extra good doobie :)

>  > There's also a user comment on the mysql manual:
>  >
>  > "Note that if you've been a good doobie and already changed the root
>  > password then the DBD::mysql tests will fail."
> This is accurate, but fixable.  You run CPAN as root (needed for the 
> install part), so the tests default to connecting as root@localhost.  
> You need to override that default to make the tests work.

I'd prefer not to do that for two reasons:
(1) It makes it non-standard and I don't like that as above. The tests 
ought to work out of the box, IMHO.
(2) There doesn't seem to be any way to reconfigure the tests without 
breaking the File Hierarchy Standard on a SuSE system anyway. Template 
Toolkit, for example stops and asks me what user to use and writes its 
own private test configuration file. That's OK.

>  > This fits my circumstances; I have indeed given root a password and a
>  > .my.cnf file. I don't want to take it away again - it seems pretty
>  > tedious if I have to change root's password every time I want to upgrade
>  > modules that use mysql.
>  >
>  > Is the user comment correct and if so, is there anything I can do to
>  > make test installation work as normal? I don't understand why changing
>  > root's password affects the anonymous user.
> It doesn't, but you aren't using the anonymous user.
> <snip>
> Dave Howorth also wrote:
>> Jochen Wiedmann wrote:
>>> On 6/21/05, Dave Howorth <dhoworth@stripped> wrote:
>>>> No. I should have mentioned that I'm using CPANPLUS for the install so
>>>> there's no opportunity to interact. If I run the tests manually, I can
>>>> edit mysql.mtest to the user 'test' that I added, but I don't 
>>>> understand
>>>> why the default CPAN installation isn't working. I thought it was all
>>>> supposed to default to the anonymous user?
>>> There is a possibility to interact, which was designed spefically with
>>> CPAN installations in mind: Write your own "mysqlconfig". Should be
>>> documented in the INSTALL section.
>> Well after having cpan run and then going back and investigating what 
>> went wrong, you can change something. But mysql_config is already 
>> installed in /usr/bin by SuSE so that's not really something that 
>> should be edited by a user. There should be something elsewhere in a 
>> user-alterable part of the file system, which is why I bodged it by 
>> editing mysql.mtest.
> I agree.  Editing mysql_config to fix this problem isn't a very good 
> idea. It's overkill, and it's a maintenance and security headache.

! :)

>> But my main issue is that none of this should be necessary. It should 
>> just install automatically using the default mysql anonymous accounts. 
>> Why is that not working?
> DBD::mysql has always been a pain to install via CPAN.  If you install 
> it manually, you are prompted for a valid mysql user and password to use 
> to access the test db, but CPAN doesn't prompt you, leaving those 
> options set to their defaults (undef).  Fortunately, the defaults can be 
> altered via command line switches to `perl Makefile.PL`, and there is a 
> way to set those in CPAN.  First, you need to enter
>   cpan> o conf makepl_arg "--testuser=test --testpass=passwd"
> (substituting the real password for test@localhost in place of 
> "paswd").  Then enter
>   cpan> install DBD::mysql
> Now the test scripts should connect as test@localhost using the correct 
> password, so you should get valid test results.

OK, I guess this is as good a workaround as I've got. But it's magic. 
Whoever does the install/upgrade has to know in advance about the 
problem and remember the incantation (or look it up in the book of 
shadows). At least the way TT does it there's a prompt to alert you that 
there might be a problem.

> I haven't used CPANPLUS much yet, so I'm not sure how to accomplish this 
> there.

I try to use it every now and again, because it's the future and because 
it automatically sends install reports which make me feel like I'm 
helping. But every time I try it, it eventually bites me and I go back 
to cpan. It does seem to be getting better slowly. Purely my personal 
top of the head opinion, YMMV.

Cheers, Dave

DBD::mysql anonymous user problemDave Howorth21 Jun
  • Re: DBD::mysql anonymous user problemRudy Lippan21 Jun
    • Re: DBD::mysql anonymous user problemDave Howorth21 Jun
Re: DBD::mysql anonymous user problemDave Howorth21 Jun
  • Re: DBD::mysql anonymous user problemMichael Stassen21 Jun
    • Re: DBD::mysql anonymous user problemDave Howorth23 Jun