New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cannot log in to the MySQL server #13543

Closed
nik6018 opened this Issue Jul 31, 2017 · 20 comments

Comments

Projects
None yet
5 participants
@nik6018

nik6018 commented Jul 31, 2017

Steps to reproduce

  1. Install MySQL 5.7

Expected behaviour

phpMyAdmin should have logged me in without any issues.

Actual behaviour

I get the following error while logging in
pma

However, the following PHP code which uses the mysqli_connect function is able to connect to the database without any error

$conn = new mysqli('localhost', 'root', $pass);

This is my config.inc.php

$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

Can anyone point me in the right direction regarding the issue as I have tried all the possible solution from Google with no success?

The Problem only occurs on Ubuntu servers and it's working fine on CentOS 6 & 7

Server configuration

Operating system:
Ubuntu 14.04

Web server:
Nginx 1.10

Database:
MySQL 5.7

PHP version:
PHP 5.6

phpMyAdmin version:
4.6.6

@nijel nijel self-assigned this Jul 31, 2017

@nijel nijel added the question label Jul 31, 2017

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Jul 31, 2017

Member

Do you get any other errors in bottom of the page? What might help is using $cfg['Servers'][$i]['connect_type'] = 'socket';

Member

nijel commented Jul 31, 2017

Do you get any other errors in bottom of the page? What might help is using $cfg['Servers'][$i]['connect_type'] = 'socket';

@nik6018

This comment has been minimized.

Show comment
Hide comment
@nik6018

nik6018 Jul 31, 2017

Tried, didn't help got the same error, even the WebServer logs were empty.
I even tried to set the$cfg['Servers'][$i]['auth_type'] to config that too resulted in the same error.
I am completely clueless about this.

I even hardcoded the root password in the connection function that too resulted in the same error.

nik6018 commented Jul 31, 2017

Tried, didn't help got the same error, even the WebServer logs were empty.
I even tried to set the$cfg['Servers'][$i]['auth_type'] to config that too resulted in the same error.
I am completely clueless about this.

I even hardcoded the root password in the connection function that too resulted in the same error.

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Jul 31, 2017

Member

Does it work if you remove config.inc.php altogether?

Member

nijel commented Jul 31, 2017

Does it work if you remove config.inc.php altogether?

@nik6018

This comment has been minimized.

Show comment
Hide comment
@nik6018

nik6018 Jul 31, 2017

Same error, nothing changed :(
Is there anything else I can do?

nik6018 commented Jul 31, 2017

Same error, nothing changed :(
Is there anything else I can do?

@Nilucshan

This comment has been minimized.

Show comment
Hide comment
@Nilucshan

Nilucshan Aug 1, 2017

Can you check your config.inc.php with the one in the following link. The discussion below might be useful.
#13472

Nilucshan commented Aug 1, 2017

Can you check your config.inc.php with the one in the following link. The discussion below might be useful.
#13472

@nik6018

This comment has been minimized.

Show comment
Hide comment
@nik6018

nik6018 Aug 1, 2017

@Nilucshan I have configured the config.inc.php correctly so no issues there.
Mainly my issue is that phpMyAdmin refuses to connect to the MySQL Server without any proper error message.

nik6018 commented Aug 1, 2017

@Nilucshan I have configured the config.inc.php correctly so no issues there.
Mainly my issue is that phpMyAdmin refuses to connect to the MySQL Server without any proper error message.

@nik6018

This comment has been minimized.

Show comment
Hide comment
@nik6018

nik6018 Aug 1, 2017

the error is originating from the phpmyadmin/libraries/plugins/AuthenticationPlugin.class.php in the 4.6.6 version where if it doesn't get an error no it just displays

"Cannot log in to the MySQL server"

@nijel Can you please guide where should I be looking next in the code.

nik6018 commented Aug 1, 2017

the error is originating from the phpmyadmin/libraries/plugins/AuthenticationPlugin.class.php in the 4.6.6 version where if it doesn't get an error no it just displays

"Cannot log in to the MySQL server"

@nijel Can you please guide where should I be looking next in the code.

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Aug 1, 2017

Member

You should also get errors from the database or other error specifying what went wrong. You can check auth log (usually /var/log/auth.log if you can see the failure detail there).

Member

nijel commented Aug 1, 2017

You should also get errors from the database or other error specifying what went wrong. You can check auth log (usually /var/log/auth.log if you can see the failure detail there).

@nik6018

This comment has been minimized.

Show comment
Hide comment
@nik6018

nik6018 Aug 1, 2017

@nijel Thank you for your response.

First I checked the /var/log/mysql/error.log and the following error is is thrown by MySQL:

Access denied for user 'root'@'localhost'

However the following command mysql -uroot -p with the ROOT password works fine

As mentioned by you I tailed the /var/log/auth.log file, which didn't give much information:

pam_unix(cron:session): session opened for user root by (uid=0)
pam_unix(cron:session): session closed for user root

nik6018 commented Aug 1, 2017

@nijel Thank you for your response.

First I checked the /var/log/mysql/error.log and the following error is is thrown by MySQL:

Access denied for user 'root'@'localhost'

However the following command mysql -uroot -p with the ROOT password works fine

As mentioned by you I tailed the /var/log/auth.log file, which didn't give much information:

pam_unix(cron:session): session opened for user root by (uid=0)
pam_unix(cron:session): session closed for user root

@OlafvdSpek

This comment has been minimized.

Show comment
Hide comment
@OlafvdSpek

OlafvdSpek Aug 2, 2017

@nijel Does MySQL log to /var/log/auth.log ?

OlafvdSpek commented Aug 2, 2017

@nijel Does MySQL log to /var/log/auth.log ?

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Aug 4, 2017

Member

No, but phpMyAdmin does (if php is built with syslog support), see https://docs.phpmyadmin.net/en/latest/config.html#cfg_AuthLog

Member

nijel commented Aug 4, 2017

No, but phpMyAdmin does (if php is built with syslog support), see https://docs.phpmyadmin.net/en/latest/config.html#cfg_AuthLog

@OlafvdSpek

This comment has been minimized.

Show comment
Hide comment
@OlafvdSpek

OlafvdSpek Aug 4, 2017

Isn't that new in 4.8?

OlafvdSpek commented Aug 4, 2017

Isn't that new in 4.8?

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Aug 6, 2017

Member

The configuration option is new, but it behaved like with "auto" before.

Member

nijel commented Aug 6, 2017

The configuration option is new, but it behaved like with "auto" before.

@OlafvdSpek

This comment has been minimized.

Show comment
Hide comment
@OlafvdSpek

OlafvdSpek Aug 6, 2017

Ah. So how does pMA choose between php and syslog?

OlafvdSpek commented Aug 6, 2017

Ah. So how does pMA choose between php and syslog?

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Aug 6, 2017

Member

If syslog is available it uses that. Actually older versions had only syslog and no php log support. It also uses apache_note, so that you can use variables in Apache logs if using Apache SAPI.

Member

nijel commented Aug 6, 2017

If syslog is available it uses that. Actually older versions had only syslog and no php log support. It also uses apache_note, so that you can use variables in Apache logs if using Apache SAPI.

@nijel

This comment has been minimized.

Show comment
Hide comment
@nijel

nijel Aug 29, 2017

Member

Anyway back to original question: Most likely you don't have enabled password authentication for root user in the MySQL, see for example https://stackoverflow.com/a/39973869/225718 for information how to configure it.

Member

nijel commented Aug 29, 2017

Anyway back to original question: Most likely you don't have enabled password authentication for root user in the MySQL, see for example https://stackoverflow.com/a/39973869/225718 for information how to configure it.

@nijel nijel closed this Oct 3, 2017

@nik6018

This comment has been minimized.

Show comment
Hide comment
@nik6018

nik6018 Oct 11, 2017

I have managed to solve this issue, so here is the fix.

On Ubuntu MySQL 5.7 you don't need to enter the ROOT password while logging in via Terminal as a ROOT user, as MySQL just check's the socket.

But as phpMyAdmin run's as non-root user you can't log in as ROOT.

So instead you can create a ROOT privilege user and change the connection type to TCP in PMA config file, after that you will be able to login as a ROOT user.

@nijel can you confirm if I am doing the right workaround to resolve this issue.

nik6018 commented Oct 11, 2017

I have managed to solve this issue, so here is the fix.

On Ubuntu MySQL 5.7 you don't need to enter the ROOT password while logging in via Terminal as a ROOT user, as MySQL just check's the socket.

But as phpMyAdmin run's as non-root user you can't log in as ROOT.

So instead you can create a ROOT privilege user and change the connection type to TCP in PMA config file, after that you will be able to login as a ROOT user.

@nijel can you confirm if I am doing the right workaround to resolve this issue.

@OlafvdSpek

This comment has been minimized.

Show comment
Hide comment
@OlafvdSpek

OlafvdSpek Oct 11, 2017

Changing the connection type shouldn't be necessary.

OlafvdSpek commented Oct 11, 2017

Changing the connection type shouldn't be necessary.

@nik6018

This comment has been minimized.

Show comment
Hide comment
@nik6018

nik6018 Oct 11, 2017

@OlafvdSpek didn't work, if i use localhost as the host it will try to connect via sockets and if i use 127.0.0.1 it will use TCP and connect properly.

nik6018 commented Oct 11, 2017

@OlafvdSpek didn't work, if i use localhost as the host it will try to connect via sockets and if i use 127.0.0.1 it will use TCP and connect properly.

@kstan79

This comment has been minimized.

Show comment
Hide comment
@kstan79

kstan79 Feb 6, 2018

pam_unix(cron:session): session opened for user root by (uid=0)
Seems this user is using external authentication backend? If you use external authentication then you may stuck cause I'm having same issue. You may try create another user via command line, grant the user full permission, then retry with new user in phpmyadmin.

kstan79 commented Feb 6, 2018

pam_unix(cron:session): session opened for user root by (uid=0)
Seems this user is using external authentication backend? If you use external authentication then you may stuck cause I'm having same issue. You may try create another user via command line, grant the user full permission, then retry with new user in phpmyadmin.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment