Rails & MySQL login problem (#42000Access denied for user)

[Stephan]

I'm running Ruby on Rails and MySQL on a Fedora Core 4 server. When I
was working on my application suddenly an error occured on all pages
(including pages that worked fine before). I get the following error:

#42000Access denied for user 'members'@'localhost' to database 'dev_members'

I checked MySQL, but I can login with the user 'members' from localhost
(with the same password as in the database.yml). I can also access the
dev_members database and execute selects on the tables.

I also tried restarting mysql and the webserver (lighttpd), which did
not help.

I think the problem is in rails, but I have know idea on how to fix
this? Any help is appreciated...


This the trace give by rails:
==================================================================================
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/vendor/mysql.rb:510:in
`read'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/vendor/mysql.rb:152:in
`real_connect'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/connection_adapters/mysql_adapter.rb:45:in
`mysql_connection'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/connection_adapters/abstract/connection_specification.rb:145:in
`connection_without_query_cache='
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/query_cache.rb:54:in
`connection='
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/connection_adapters/abstract/connection_specification.rb:106:in
`retrieve_connection'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/connection_adapters/abstract/connection_specification.rb:20:in
`connection'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/base.rb:931:in
`add_limit!'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/base.rb:924:in
`construct_finder_sql'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/base.rb:395:in
`find'
/usr/lib/ruby/gems/1.8/gems/activerecord-1.13.1/lib/active_record/deprecated_finders.rb:37:in
`find_all'
#{RAILS_ROOT}/app/controllers/team_controller.rb:5:in `list'
==================================================================================

 

[Lou Vanek]

It looks like you or your host have upgraded to the latest Rails gems.
There is a lot of discussion about the AR problems on the Rails list.
You are probably working on an old MySQL database (I'm guessing 4.0),
and using old-style passwords.
It looks like ActiveRecord 1.13.1 for some reason is now trying to
log on using the new authentication method that MySQL 4.1+ uses.
A bug has been filed for this.
In the mean time you may want to either revert your rails gems,
update the database, or perform some fu on mysql.rb 'real_connect'.

 

[Jeremy Kemper]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It looks like ActiveRecord 1.13.1 for some reason is now trying to
log on using the new authentication method that MySQL 4.1+ uses.
A bug has been filed for this.

Because AR bundles the latest version of the pure-Ruby mysql driver
(0.2.6) along with a patch to support 4.1/5.0 servers. The patch has
a bug which breaks compatibility with 3.23/4.0.

In the mean time you may want to either revert your rails gems,
update the database, or perform some fu on mysql.rb 'real_connect'.

Or install the mysql-ruby C bindings, which have always worked:
gem install mysql

jeremy
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (Darwin)

iD8DBQFDmgnMAQHALep9HFYRAo+uAJwP4MObhdmSPrjAozx3LQIDRVd0zQCeN/+t
5KlqkTyPd44okQLsRG8RPXI=
=QkrT
-----END PGP SIGNATURE-----

 

[Yan-Fa Li]

Because AR bundles the latest version of the pure-Ruby mysql driver
(0.2.6) along with a patch to support 4.1/5.0 servers. The patch has
a bug which breaks compatibility with 3.23/4.0.




Or install the mysql-ruby C bindings, which have always worked:
gem install mysql

jeremy

On FC4 you may need to type:

sudo gem install mysql -- --with-mysql-lib=/location/of/your/mysqllibs

e.g. sudo gem install mysql -- --with-mysql-lib=/usr/lib64/mysql

on an AMD64 system


Yan

 

[Stephan]

On FC4 you may need to type:

sudo gem install mysql -- --with-mysql-lib=/location/of/your/mysqllibs

e.g. sudo gem install mysql -- --with-mysql-lib=/usr/lib64/mysql

on an AMD64 system


Yan

Thanks for your help, but I still didn't get it working.

I have MySQL version 4.1.15 and I have set old_passwords=0 in the MySQL
config file. So the MySQL version should not be a problem here, right?

I installed the gem with the command given above, with the following result:
gem install mysql -- --with-mysql-lib=/usr/lib/mysql
<...cut...>
Successfully installed mysql-2.7

After this the problem remains. There is a slight difference in the
error message, it is now: "Access denied for user 'members'@'localhost'
to database 'dev_members'", so the error number is missing now.

Any more ideas?


Stephan

 

[Lou Vanek]

When you look at field mysql.`user`.password do you see strings that are
about 16 characters long? If so, then for some reason old passwords are
being used (they would be much longer, 41 bytes, if they are new passwords).

If you are currently trying to log on with a user that has new passwords,
then I'd try updating the password to the old, 16-character hash format.
On the other hand, if you trying to log on with a user that has an old
password, then you might try updating the password to the new, 41-character
hash format.

To change to an old-style password:
mysql> SET PASSWORD FOR 'members'@'localhost' = OLD_PASSWORD('the_password');

To change to a new-style password:
mysql> SET PASSWORD FOR 'members'@'localhost' = PASSWORD('the_password');

Then either restart the mysql server or run,
mysqladmin reload

You may also want to try changing 'host' from 'localhost' to the actual IP
address, '127.0.0.1' in the yml file.

If you cannot get any of this to work I suggest rolling back the
gems until the problem is fixed.

 

[Yan-Fa Li]

Thanks for your help, but I still didn't get it working.

I have MySQL version 4.1.15 and I have set old_passwords=0 in the MySQL
config file. So the MySQL version should not be a problem here, right?

I installed the gem with the command given above, with the following
result:
gem install mysql -- --with-mysql-lib=/usr/lib/mysql
<...cut...>
Successfully installed mysql-2.7

After this the problem remains. There is a slight difference in the
error message, it is now: "Access denied for user 'members'@'localhost'
to database 'dev_members'", so the error number is missing now.

Any more ideas?

Have you tried running mysql client from the command line with the same
login params to see if it gives you the same errors ?

mysql -umembers -p dev_members

If it lets you login in then the database is set up correctly. If it
doesn't then your database permissions may need attention. You also
might want to try setting the Host column in the members table to '%'
just to test. I often set Host to '%' for testing inside of a private
network, as it's fairly benign from a security perspective; don't do
that on a production network though. It might need a fully qualified
hostname, you could try 'localhost.localdomain' which is the default for
a FC4 host without a proper DNS server.

Yan

 

[Stephan]

When you look at field mysql.`user`.password do you see strings that are
about 16 characters long? If so, then for some reason old passwords are
being used (they would be much longer, 41 bytes, if they are new
passwords).

If you are currently trying to log on with a user that has new passwords,
then I'd try updating the password to the old, 16-character hash format.
On the other hand, if you trying to log on with a user that has an old
password, then you might try updating the password to the new, 41-character
hash format.

To change to an old-style password:
mysql> SET PASSWORD FOR 'members'@'localhost' =
OLD_PASSWORD('the_password');

To change to a new-style password:
mysql> SET PASSWORD FOR 'members'@'localhost' =
PASSWORD('the_password');

Then either restart the mysql server or run,
mysqladmin reload

You may also want to try changing 'host' from 'localhost' to the actual IP
address, '127.0.0.1' in the yml file.

If you cannot get any of this to work I suggest rolling back the
gems until the problem is fixed.

Ok thanks to all who answered, this OLD_PASSWORD thing seems to have
fixed the problem.

Stephan