Why does my connection to MySQL always fail (10061)? [PHP5]

[Shaitan00]

I am encountering some issues accessing my local MySQL Server from PHP and I was hoping for some help/guidance...

In my PHP.INI file I uncommented out:
extension=php_mysql.dll
extension=php_mysqli.dll
I didn't set any of the other things (mysql.default_port, mysql.default_socket, mysql.default_host, etc...) as I assumed this could be taken care of with my PHP application.

I am using Windows Vista 64-bit, I installed MySQL (5.1.32 32bit) and am running the console application just fine (created database, tables, etc...)
- The mysql.exe *32 process is running

But everytime I try to connect I get the following error:
Error: Can't connect to MySQL server on 'localhost' (10061)
(among a bunch of others that follow after that... but I assume this is the root of the problem)

I've done some reading and tested using mysqladmin and it gave the following:
mysqladmin.exe ping
mysqladmin.exe: connect to server at 'localhost' failed
error: 'Access denied for user 'ODBC'@'localhost' (using password: NO)'

This is the code I am using to connect, I know there is a Database called "db" as I created it and can use it when I launch the MySQL console application, at the same time it prompts me for my password so I know that is valid too (because it works).

    function Database()
    {
        $this->server   = "localhost";
    	$this->user     = "";
    	$this->password = "pass";
    	$this->database = "db";
    }

    private function Connect()
    {
        $this->connection_id = @mysql_connect($this->server, $this->user, $this->pass);
    }

But even with all this, I still get the same error everytime ...

Do I need to configure MySQL for Localhost somehow?
Is there maybe something with my Firewall I need to do (for LocalHost)?
Can anyone think of any tests I could run to attempt to pinpoint the issue?

Any help would be immensely appreciated, this is blocking me from debugging my project...
Thanks,

[abduraooft]

Check the mysql_error() message, like

PHP Code:

$this->connection_id = mysql_connect($this->server, $this->user, $this->pass) or die(mysql_error()); 


You may get a more specific error message.

[achcore]

What is the user for connecting your database? You have entered a blank user in the function named Database().

[Shaitan00]

achcore: Seriously - I would like to know, when I installed MySQL it never asked me to provide a USER, I tried blank and "root" but neither work - is there a way for me to find out and confirm?

Could this have something to do with the fact that I am using IIS and not Apache?

[abduraooft]

hen I installed MySQL it never asked me to provide a USER, I tried blank and "root" but neither work - is there a way for me to find out and confirm?

Try

PHP Code:

$this->user     = "root";
$this->password = ""; 


[Shaitan00]

I tried "root"/"pass", ""/"pass", "root"/"", etc... and nothing works...
Also added mysql_error() to get more information and it returns:
Can't connect to MySQL server on 'localhost' (10061)

Any clues?

[bdl]

MySQL doesn't have anything to do with IIS, Apache or PHP. It's perfectly happy running by itself with no other services.

This is the code I am using to connect, I know there is a Database called "db" as I created it and can use it when I launch the MySQL console application, at the same time it prompts me for my password so I know that is valid too (because it works).

So, you can connect using the mysql command line client but you don't know the username?

Questions:

• How did you install MySQL? With one of the executables that provides some assistance in setting up the Windows service, username, password, etc? Or did you simply unzip the ZIP distribution and hope for the best?
• Is it running? Hint: net start MySQL
• Have you read through the MySQL manual sections on installing on the Windows platform and post installation setup and testing?
• Have you read through the section on errors, error codes and common problems?

[Shaitan00]

bdl: good to know MySQL is independant, people keep telling me to install Apache instead of IIS and I found it hard to beleive.

Yes - MySQL runs fine from the command line.
And the User is "root" (from the user table, and from my recollection).
Password is "pass" (I set it on install).
Install was done with the file downloaded from MySQL site (windows exe).

Service is running (started it manually - and console application works)
mysqladmin.exe -u root -p ping
Enter password: **
mysqld is alive

From the links you provided I assume PHP should be using "root" and not "odbc" account to try to connect (makes sense), how do I change this for PHP? I pass in $this->user which is 'root' ... so why is it using ODBC?

[bdl]

Ok very good. Now, login to the 'mysql' command line client (as 'root' of course).

Issue this statement:
SELECT User, Host, Password FROM mysql.user;

This will return the users, the host they are allowed to connect from, and the respective hashed password values. I'm curious to see whether or not the 'root' user's Host is defined as 'localhost' or your PC's hostname / IP address. If there are multiple 'root' users, I'd suggest deleting any other entry than the 'localhost' entry. You should also issue a command like
CREATE USER 'shaitan'@'localhost' IDENTIFIED BY 'a_password_here';
Then you can grant access to the `db` database, e.g.
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON db.* TO 'shaitan'@'localhost';

Two good practices - never allow the 'root' user to connect from any other host than 'localhost', and always have a more limited user to access the database via PHP (or just for general testing - you don't always need admin credentials to work with the data).

[Shaitan00]

bdl: I checked the DB and got only one user:

mysql> SELECT User, Host, Password FROM mysql.user;
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *lots of stuff |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

Also, I tried the following:
C:\>telnet localhost 3306
Connecting To localhost...Could not open connection to the host, on port 3306: Connect failed

Could this be a Firewall issue of somekind?
I am running Vista 64-bit, I added BIN\MySQLd.exe to the firewall options @ opened port 3306 on the Firewall - didn't touch my router (using localhost - I imagine I don't need to go that far).

I created the SHAITAN account with the grant permissions as you mentioned - but what do I do with this? Seeing as I could connect using root/pass (console) did I really need another account?

[bdl]

Ah, ok. Well, you shouldn't be able to telnet to the MySQL server, but it's interesting that you mentioned port 3306. I wonder if your config is set to use pipes and not networking. Check your my.ini config file for "skip-networking" and "bind-address" settings.

Since this is localhost, you shouldn't have to do anything with the firewall settings - you're not connecting from an external host.

[bdl]

Originally Posted by Shaitan00

I created the SHAITAN account with the grant permissions as you mentioned - but what do I do with this? Seeing as I could connect using root/pass (console) did I really need another account?

You don't "need" another account, if all you're doing is running the server on your own PC, and don't expect to accept connections from anywhere else. Even on a LAN would be fine. But it's good practice to have a non-admin user connect from PHP, and it's just a Good Idea to have separate users for different tasks. For example, I only login as 'root' if I need to perform some server management task. I have my own user with certain privileges that lies somewhere between admin and basic user that I use on a regular basis for data and structure maintenance, backups, importing data, testing statements, etc. Then I have another user with limited privileges that I use for PHP, Perl, Java, Ruby, etc. On one specific server I have yet another user that I give access from external hosts (i.e. the wildcard '%' - any host, or from specific host / IP subnet on the LAN).

[Shaitan00]

From my [my.ini]:
skip-networking
enable-named-pipe

There is nothing under "bind-address" ...

Is this good?
What should I change if it isnt?

The reaons I tried to connect to 3306 is that I am doing a lot of searching to try and fix this - most people say 3306 is the default MySQL should be running on ...

Could it be something in my PHP.INI?

Never imagined it would be so hard to access a DB

[bdl]

Well, the "skip-networking" option eliminates TCP/IP and does not run MySQL on port 3306, it uses a 'named pipe' instead. So PHP has to be able to connect via TCP/IP, this is likely your problem. Comment out the lines that says "skip-networking" and "enable-named-pipe", and stop / start the MySQL server. For good measure you might restart the IIS service as well.

[Shaitan00]

Ok - I commented out both lines and restart MySQL and IIS ...
Now the MySQL process no longer runs (MySQL) and if I try to run mysqladmin:
mysqladmin.exe -u root -p ping
Enter password: **
mysqladmin.exe: connect to server at 'localhost' failed
error: 'Can't open named pipe to host: . pipe: mysql (2)'

Also, if I run the console application it prompts me for password then just closes automatically (I used to be able to do all my DB work, create tables, query, etc... - now it seems to "Crash" or something).

Oddly TelNet acts differently now ...
telnet localhost 3306
5.1.32-community♦EQHUPqj☻516Y=eeM0FRH
Connection to host lost.

ANy clues?