Whisk & MySQL connection error

Hello, on my Mac, I installed MySQL but Whisk displays this message in the preview window: SQLSTATE [HY000] [2002] Operation not permitted
I have verified the correct username and password. Access is root. Maybe it's because of my version of MySQL (8.0.13) which is the latest version compatible with my OS High Sierra.
However, I have no problem surfing my pages with Chrome, Firefox or Safari.
What is my problem?
Bests, Marc.

I looked into this a little bit, and can basically reproduce connecting to a MAMP-based MySQL database with a line like this:

$conn = new mysqli("localhost", "username", "password", "db_name", "3306");

I think there are different ways to connect to MySQL (like a .sock file vs socket connection), and so it seems that the localhost way won't work with the command-line PHP tool that is used by Whisk. If you change from localhost to "127.0.0.1" it seems to allow using the other method which seems to work for me:

$conn = new mysqli("127.0.0.1", "username", "password", "db_name", "3306");

Does that help?

Does this help me? Not really, because all my SQL queries were in PDO, so I have to review everything in my site, because all my SQL queries crash PHP scripts. In addition, my database connection error handling no longer works. :frowning_face:
So, we will have to wait before seeing if it works or not, not to mention that I do not yet know how to switch from PDO to MYSQLI. :wink:

I'm guessing that the actual connection method may not matter whether it is PDO (unfortunately I'm not familiar with it) or mysqli, but probably choosing localhost vs 127.0.0.1 will still make a difference.

For my database code I use a config file that automatically determines if it is on my webhost's server or if it is local. It does it based on a path, but there's probably other ways. It looks like:

<?php

if(strpos(__FILE__, "[[some-path-on-server]]") == FALSE) {
	// local dev
	$licenses_db_servername = '127.0.0.1';
	$licenses_db_username = '[[local-username]]';
	$licenses_db_password = '[[local-pw]]';
	$licenses_db_dbname = '[[local-db]]';
	$licenses_db_port = 8889;
} else {
	// on webhost
	$licenses_db_servername = 'localhost';
	$licenses_db_username = '[[remote-username]]';
	$licenses_db_password = '[[remote-password]]';
	$licenses_db_dbname = '[[remote-db]]';
	$licenses_db_port = 3306;
}

?>

So maybe that's also a technique you can use if you aren't already employing it.

Hi Jonathan!
That's a bit what I do ... Finally everything is located in a PHP function without having to use a separate configuration file. Why ? Simply because I don't really know how to get there. But I keep learning to get there.
Well, I go back to the modification of my site and I restart the thread if ever Whisk does not succeed.

1 Like

Hi everyone!
I had a lot of work, but got back to my site. I also learned a bit about using mysqli, but I always come across the error "(2002) Operation not permitted" which does not appear with web browsers.
And when I use "127.0.0.1" instead of "localhost", PHP said this "(2002) No route to host".
Worst, I needed restart my computer, and PHPMyAdmin say this "#2006 - MySQL server has gone away" when I want see my datas.
What should I do to resolve this issue?

Apologies, I forgot to change the settings. Now that they have been entered and verified several times, my browser keeps telling me that the page is not working.

Are you using a vanilla install of MAMP, or are you running/installing MySQL another way?

I use a vanilla install de MySQL. I use the official install from MySQL website.

I just did an install, and did not have any problems using the sample code in this first example in Whisk to connect (changing to "127.0.0.1" along with relevant username/password/db).

I am using the system php (/usr/bin/php).

The only thing not vanilla was this screen during the mysql install:

I chose legacy password since it seemed like that would be most interoperable with an older PHP. I have no idea if this would change how you connect.

You might want to try a tool like TablePlus and see if you have any issues connecting through a different tool.

Hi Jonathan!
I used the strong encryption with MySQL. Now I use the legal one and it works perfectly ! Very big thanks ! I'll go again to writing my website… after I modify it to mysqli.
Bests, Marc.

1 Like

Oh, that's really interesting that ended up being the problem! I'm glad we were able to get to the bottom of it :smiley:.