Locked out of phpMyAdmin

[CluelessExceptForSQL]

Hello,

Problem might be any of these things:
(1) Wrong settings in config.inc.php or other files
(2) Background process still running that I can't locate or kill
(3) Permissions issue

At this point, there may be more than one problem because I seem to have made things worse by trying to fix the original problem. Everything here is for localhost only. The database has only ever been used with localhost.

Several months ago, I installed XAMPP and built an extensive MySQL database through phpMyAdmin, which I have been using daily for months. Everything was working fine until 2 days ago. One query for a table of 2.5 million rows had taken over 6 hours. So I killed the query through phpMyAdmin in another window. After that, things seemed to be running a bit slow. And since I had been seeing "Connection for controluser as defined in your configuration failed" warnings for a few weeks anyway, I decided that I'd alter the config.inc.php file and then restart everything.

Now I can't access my database at all, and MySQL appears not to be running correctly. I've been working on regaining access for a day and a half already with no luck. The symptoms have varied because I've tried multiple fixes during that time. Here's some of what I've seen:

(1) Can't shut down MySQL through XAMPP Control. The green light remains on, regardless of whether I have started or stopped MySQL through the terminal using commands like this one:

sudo /usr/local/mysql/support-files/mysql.server stop

(2) This is the typical 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)."

(3) phpMyAdmin used to be set up with the 'config' option rather than the 'cookie' option in the config.inc.php file. But now, no matter how I change that config.inc.php file, I see a login screen at localhost/phpmyadmin. And no password seems to work.

(4) Within the config.inc.php file, ['user'] = 'root'. And 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.

(5) After this problem began yesterday, I was still able to stop and start MySQL using commands like

sudo /usr/local/mysql/support-files/mysql.server stop

At least, the terminal said "SUCCESS!" after each MySQL start. But apparently I've created a new problem. Some advice online indicated that I should alter the php.ini or my.cnf file. I did so, found it didn't work, and reverted to the original. However, now MySQL can't be started through the terminal at all. The command times out after 60 seconds and gives this error:

"ERROR! The server quit without updating PID file"

(6) To test whether MySQL is running at all, I've entered this into the terminal:

/applications/xampp/xamppfiles/bin/mysqladmin version

And I see this error message:

"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!"


Anyway, I am completely stumped. The database was running smoothly until yesterday morning. I'm the first to admit that I don't understand the technical issues involved here. So I really have no clue what is going on or where to go from here. Any help would be MUCH appreciated!


P.S. I think the problem goes beyond just the config.inc.php file. I've changed the settings in that file back and forth frequently for troubleshooting. But here's what it looked like not too long ago:

<?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';


?>

[CluelessExceptForSQL]

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.