Re: cannot connect from mysql client nor PHP, only sudo
Date: Sat, 11 Feb 2017 17:01:23 +0100
Message-ID: <3144370.R56niFO833_at_PointedEars.de>
Axel Schwenke wrote:
> On 10.02.2017 11:04, Jivanmukta wrote:
>> I programmed my first application in PHP+MySQL. It was working fine. >> Since a few days I have error connecting to database
>
> If it worked until a few days ago: what has changed?
Probably the MySQL version. One of
ls -t /var/log/aptitude.log* | xargs -r zless
ls -t /var/log/apt/history.log* | xargs -r zless
ls -t /var/log/dpkg.log* | xargs -r zless
can tell (provided the “findutils” [for xargs(1)] and the “gzip” package [for zless(1)] are installed, which they should be as they are “essential” ones). This would list the log files (even the compressed ones) in reverse order of last modification using less(1); for help in less(1), type “h”.
>> (HY000/1698): Access denied for user 'root'_at_'localhost'. >> I tried: "mysql -u root -p -h localhost" and I have "Access denied for >> user root_at_localhost'
>
> Did you ever set a password for root_at_localhost? MySQL ships by default
> with no password for that user, but some installation routines may ask the
> user to set a password.
Also, the cryptographic hash algorithm used to generate and check passwords has changed in MySQL 4.1 for security reasons. This was tolerated by default until before MySQL 5.6, from which the “old-passwords” server option is required to support the old format:
<https://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html>
(Therefore, recreating the “root” MySQL user as described on Stack Overflow would solve the problem, too, and a package configuration script’s dialog would suggest that to the system administrator upon reinstall. However,
>> Command "sudo mysql -u root -p -h localhost" works fine.
indicates that this is not the cause of the problem here.)
> Now this is funny. Are you sure you are running MySQL and not MariaDB?
> Because recent versions of MariaDB use the "unix_socket" authentication
> method to secure the root_at_localhost account. MySQL has a similar
> authentication plugin but AFAIK they don't enable it by default.
They do now. [I have never found it necessary to specify “-h localhost” for this.]
>> In mysql log I have only access denied. >> Reinstalling mysql doesn't help. mysql_upgrade doesn't help, either.
>
> Naturally. Account information is stored in the system database. And this
> is not touched by reinstallion.
*Re*installation can trigger the package reconfiguration script which would provide the option to upgrade a "system database"¹ that is using old formats.
¹ There are actually *several* MySQL system databases: at least
`information_schema`, `mysql`, and `performance_schema`, whereas the tables `information_schema`.`user_privileges` (specific grants) and `mysql`.`user` (usernames, passwords and global privileges) stores the "account information" that is relevant here.
>> Here you have my privileges: >> mysql> show grants for 'root'_at_'localhost';
>
> Better use: SHOW CREATE USER 'root'_at_'localhost';
Not supported before MySQL 5.7 (so it should work with the OP’s version).
<https://dev.mysql.com/doc/refman/5.6/en/show.html>
vs.
<https://dev.mysql.com/doc/refman/5.7/en/show.html>
The command used by the OP is the backwards-compatible one, though its output might not be as verbose. [I find it important to always give at least a short rationale for my recommendations. Recommendations should never be accepted based on authority only.]
>> Please help. I would like to avoid reinstalling Ubuntu.
>
> Are you from the Windows world? Reinstalling never solves this kind of
> problem.
It very well could because on Debian-based Linux distributions it triggers the package configuration script again which allows the system administrator to modify the MySQL server configuration interactively.
-- PointedEars Twitter: _at_PointedEars2 Please do not cc me. / Bitte keine Kopien per E-Mail.Received on Sat Feb 11 2017 - 17:01:23 CET