Can't access database through phpMyAdmin

Question

Basically, I have a problem with my config.inc.php file. But I'll describe the sequence of events in case I've mangled something else that I'm not aware of.

I've built a database in phpMyAdmin which I've been using for a few months on localhost. It was installed with XAMPP. It's phpMyAdmin Version 3.5.7, according to the XAMPP/xamppfiles/phpMyAdmin/README file.

For awhile, I had left ['controluser'] = 'pma' and ['controluserpass'] = 'pmapass'. A few weeks ago, I changed those values, but that apparently created a problem, since phpMyAdmin started showing me the

"Connection for controluser as defined in your configuration failed"

warnings all the time. Nevertheless, I was still able to do everything I needed to do; so I kept using my database.

Yesterday I had to kill a query that had gone on for over 8 hours. After that, phpMyAdmin seemed to be loading other databases and tables a bit slow. So I opened XAMPP and pressed the stop button for MySQL. Evidently, this button no longer stops MySQL for me. The icon simply spins and spins as if thinking. So, as of today, I've been stopping and starting MySQL directly from the terminal rather than through the XAMPP Control interface.

Since I was seeing warnings for the controluser, I decided to rewrite the config.inc.php file to cure whatever problem was there. Unfortunately, I've only made things worse. Now I can't access my database at all.

I've spent 5 hours today researching how to set up my config.inc.php file, but I'm still unable to access my database. In the past, I had it set up with the 'config' option and never had to enter a password. Ideally I'd prefer to have password security. But at this stage, I'd be happy just to regain access to my database.

Right now, localhost/phpmyadmin goes to the login page, where it defaults to having "root" entered for "username". But I can't seem to log in. Depending on the adjustments I've made to config.inc.php, sometimes the "username" defaults to gibberish; and other times an error message appears instead of the login. No matter what I enter for the password, phpMyAdmin won't accept it:

2002 Cannot log in to the MySQL server

Other times, I get this error:

2002 - Can't connect to local MySQL server through socket '/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock' (2) The server is not responding (or the local server's socket is not correctly configured).

I've tried switching back and forth between

$cfg['Servers'][$i]['auth_type'] = 'cookie'

and

$cfg['Servers'][$i]['auth_type'] = 'config'

and between

$cfg['Servers'][$i]['connect_type'] = 'tcp'

and

$cfg['Servers'][$i]['connect_type'] = 'socket'

and between

$cfg['Servers'][$i]['AllowNoPassword'] = false

and

$cfg['Servers'][$i]['AllowNoPassword'] = true

and between

$cfg['Servers'][$i]['host'] = 'localhost'

and

$cfg['Servers'][$i]['host'] = '127.0.0.1'

Nothing seems to allow to access my database.

At the moment, I'm getting the login screen and this error:

2002 Cannot log in to the MySQL server

What should I change? Below is my config.inc.php file:

<?php

$cfg['blowfish_secret'] = '89x7a3f';

$i = 0;

$i++;

$cfg['Servers'][$i]['auth_type'] = 'cookie';

$cfg['Servers'][$i]['host'] = 'localhost';

$cfg['Servers'][$i]['AllowRoot'] = true;

$cfg['Servers'][$i]['connect_type'] = 'tcp'; 

$cfg['Servers'][$i]['compress'] = true;

$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'pass';

// $cfg['Servers'][$i]['controlhost'] = '';

$cfg['Servers'][$i]['controluser']   = 'pma';
$cfg['Servers'][$i]['controlpass']   = 'pmapass';

$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma_bookmark';
$cfg['Servers'][$i]['relation'] = 'pma_relation';
$cfg['Servers'][$i]['table_info'] = 'pma_table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma_table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma_pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma_column_info';
$cfg['Servers'][$i]['history'] = 'pma_history';

//$cfg['DefaultConnectionCollation'] = 'utf8_general_ci';

$cfg['Servers'][$i]['table_uiprefs'] = 'pma_table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma_tracking';
$cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
$cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';
$cfg['Servers'][$i]['recent'] = 'pma_recent';

// $cfg['Servers'][$i]['auth_swekey_config'] = '/etc/swekey-pma.conf';

$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';

$cfg['DefaultLang'] = 'en';


?>

I've continued changing various settings in config.inc.php with no luck. But I've noticed that there is no mysql.sock file in the directory indicated by this error message: "#2002 - Can't connect to local MySQL server through socket '/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock' (2) The server is not responding (or the local server's socket is not correctly configured)." Somebody else on StackOverflow.com reported a similar problem but nobody ever came up with an answer: http://stackoverflow.com/questions/5530215/mysql-problem-no-mysql-sock — user2647547, Aug 03 '13 at 03:40
If I try to shut down MySQL through XAMPP Control and wait long enough, I receive this error message: "Shutting down MySQL.............................................. ERROR! " — user2647547, Aug 03 '13 at 04:39
In terminal /applications/xampp/xamppfiles/bin/mysqladmin version I get error: 'Can't connect to local MySQL server through socket '/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock' exists! Meanwhile, localhost/phpmyadmin takes me to the screen with all my databases and tables. Prior to restarting my computer, it went to a login screen. Now it skips that even though I changed nothing. But phpMyAdmin doesn't connect to the databases or tables themselves. — user2647547, Aug 03 '13 at 22:07

score 4 · Answer 1 · answered Aug 02 '13 at 23:37

4

If you just need access to your database and you know your login credentials, try using adminer:

http://www.adminer.org/

It's just a single PHP file that you drop in to your web server, and doesn't have the history of security holes that phpMyAdmin does. We've replaced all our phpMyAdmin installs with adminer on both MySQL and Postgres and it works great.

answered Aug 02 '13 at 23:37

ChunkyBaconPlz

580
2
10

Thanks, but I like phpMyAdmin. I just need to restore access. – user2647547 Aug 03 '13 at 00:34
If phpMyAdmin is the only thing that's broken then just reinstall it. – ChunkyBaconPlz Aug 03 '13 at 01:01
I'm not sure that phpMyAdmin as a whole is broken. It's probably just an issue with the config.inc.php file. Everything was working fine yesterday and had been for several months. I'd rather not have to reinstall the software repeatedly, since that's like using a sledge hammer to push in a thumb tack. Plus, it might cause unintended data loss. – user2647547 Aug 03 '13 at 01:06
phpMyAdmin is just a management suite. Can you access your MySQL server from the command line? Is your MySQL server listening on a socket, or an IP? `mysql -u root -p'rootpass'` If you can just find the default config.inc.php file you can just use that. – ChunkyBaconPlz Aug 03 '13 at 01:08
1

I receive this error in the terminal: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) – user2647547 Aug 03 '13 at 01:49
Make sure you replace "rootpass" in my example with your actual root password, MySQL is telling you it's wrong. At least now we know your MySQL server is up, running, and listening on 127.0.0.1, however. – ChunkyBaconPlz Aug 03 '13 at 01:54
Yes, I used the password that I set -- at least, believe I set. At the moment, however, I can't even connect to localhost. More troubleshooting. That localhost issue was easy to fix, though. I just had forgotten to restart the other bits. – user2647547 Aug 03 '13 at 01:56
The other possibility is that root isn't allowed to log in from 127.0.0.1. Do you have any other passwords? Is your website logging into this MySQL database? Can you try its username/password combos? Also, this is Windows, right? – ChunkyBaconPlz Aug 03 '13 at 02:00
When I revert to the default settings in config.sample.inc.php, the only difference is that the login screen displays gibberish in the username field instead of "root": 2@��8SD – user2647547 Aug 03 '13 at 02:01
No, it's not Windows. I should have mentioned that. It's Mac OS. There are no websites involved -- strictly local. I've tried various password combinations. Search results indicated that I might try "root", "admin", "mysql", etc. in addition to the password I set for XAMPP or the passwords I've tried using within the config.inc.php file. Thanks for your help, by the way. – user2647547 Aug 03 '13 at 02:05
Reset your root password and use adminer to log in afterwards. If that doesn't work then something is seriously wrong: http://www.coolestguyplanettech.com/how-to-change-the-mysql-root-password/ – ChunkyBaconPlz Aug 03 '13 at 02:08
I may give it a day to see if I can look at it with fresh eyes tomorrow. I'd much rather be writing SQL code than beating my head against the front door of the phpMyAdmin house like this! Thanks for your advice. – user2647547 Aug 03 '13 at 02:17
I just noticed that at http://localhost/xampp/ it says "MySQL Database Deactivated". That's after entering in the terminal: sudo /usr/local/mysql/support-files/mysql.server start and seeing "Success!" – user2647547 Aug 03 '13 at 02:19
I dunno, either xampp itself "deactivated" mysql somehow (disallowing all access?), or it's wrong because mysql is definitely running and listening. Check the mysql log files. My advice remains the same in regards to the password reset and adminer. – ChunkyBaconPlz Aug 03 '13 at 02:25

score 0 · Answer 2 · answered Aug 04 '13 at 00:10

Well after 2 days spent trying everything and anything anybody had ever posted online, I solved the problem. That doesn't mean I know how the problem happened, or how to prevent it from happening again, or why the solution worked, or what any of it means. But here's what I discovered, and here's how I fixed the issue.

I noticed that when I entered the following into the terminal

locate mysql.sock

I got a surprising result:

/private/var/mysql/mysql.sock

I was expecting a different file path for mysql.sock. Someone else online had solved a similar problem by using a symbolic link between the 2 file paths. In my case, the whole issue was resolved with one command in the terminal:

ln -s /private/var/mysql/mysql.sock /Applications/xampp/xamppfiles/var/mysql/mysql.sock

Now I'm able to edit my config.inc.php file without problems. Also, I'm now able to stop MySQL through XAMPP Control. Most importantly, I can access my database again through phpMyAdmin.

So my problem is 99% solved. One thing that puzzles me a bit is this, though: In "relation view", I used to see 2 columns -- one for foreign keys and another for keys internal to the table. Now I just see foreign keys. So I'm wondering what needs to be changed in config.inc.php to regain the other option, which I believe I had used in at least one table.

Can't access database through phpMyAdmin

2 Answers2