ansaurus

Question

Answer 1

+1 A:

Maybe you don't have remote connections set on the mySQL install. Your attempting to connect over a port, which is the same as a remote connection. Its been a while since I've done mySQL, but this is a dead giveaway:

Checked whether MySQL was running on the host by typing in Windows command prompt: "telnet 192.0.0.1 3306" and got the message "Could not open connection to the host, on port 3306: connection failed"

That its not going to work. Are you sure its configured for port 3306 and not another port? Double check that.

The code itself looks fine and is not the issue. The port is clearly the issue.

Mr-sk 2010-02-07 23:58:28

Thanks for your reply Mr-sk. How do I check whether MySQL is configured for port 3306 after it is installed? What commands do I use to get this info? I may have disabled it when I installed MySQL.

01010011 2010-02-08 00:03:10

What about using the IP 127.0.0.1?

Joel L 2010-02-08 00:05:29

Thanks for the reply Joel. What do you mean? How do I check the servername and username to make sure I put in the correct names? usernamemysql_connect(servername,username,password);

01010011 2010-02-08 00:27:07

@01010011: Under XAMPP, the MySQL configuration file is stored at "%xamppdir%\mysql\bin\my.ini" (note: the environment variable "%xamppdir%" won't be defined on your system; it's just there as a placeholder). Look for "port" and "socket" options in the [mysqld] section.

outis 2010-02-08 00:56:43

As for checking username and password, try connecting with a client. Xampp installs a command line client at "%xamppdir%\mysql\bin\mysql.exe", or you can try MySQL Workbench (http://www.mysql.com/products/workbench/) or one of the other suggestions at http://stackoverflow.com/questions/9185/what-is-the-best-mysql-client-application-for-windows.

outis 2010-02-08 00:57:04

Read through http://dev.mysql.com/doc/refman/5.1/en/user-account-management.html for information about handling user accounts.

outis 2010-02-08 01:38:00

@outis, I need that, thanks, cause I keep have a reoccurring problem of being lockout of MySQL which leaves me feeling powerless.

01010011 2010-02-08 02:41:42

Answer 2

+2 A:

You can check that the MySQL server is bound to port 3306 using tcpview. More simply, drop the port from the host specifier. The driver should then attempt to use a named pipe, rather than a TCP socket.

On an unrelated note, I strongly urge you to switch to the PDO MySQL driver. The one you're using is terribly out of date. One big advantage is PDO supports prepared statements, which offer security and efficiency benefits.

Edit:

This doesn't answer your main question, but posting this information in a comment would be a mess.

Rather than W3Schools, check out the resources suggested in:

outis 2010-02-07 23:58:50

Thanks for your reply and recommending tcpview (Netstat is on my list of things to learn). I ran it and did not see 3306 on the local address. Also thanks for recommending PDO - I'm new to this and was following w3schools example. I will do some research on it. Now how do I enable MySQL to network to the code via 3306??

01010011 2010-02-08 00:58:26

Definitely right about PDO; it amazes me how many people I see still getting started with the mysql_* commands instead. There are a lot of horrible, outdated tutorials out there! PDO is much better and not any more difficult to start with.

Alex JL 2010-02-08 01:36:07

@01010011: Don't bother having MySQL support TCP; it's less secure and shouldn't be done on a development server. Use 'localhost' as the host name and be done with it. As for where the settings are, see my 1st comment to Mr-sk's answer.

outis 2010-02-08 01:36:07

@Code Duck: W3Schools has much to answer for.

outis 2010-02-08 01:36:34

Here is the link to the w3schools tutorial I was following: http://www.w3schools.com/php/php_mysql_connect.asp

01010011 2010-02-08 02:10:24

@01010011: I've seen it. Don't bother with W3Schools.

outis 2010-02-08 02:16:25

@0101001 http://www.kitebird.com/articles/php-pdo.html is an excellent tutorial on PHP/PDO if you're interested.

Alex JL 2010-02-08 02:18:32

@Outis, wow thanks for the links, I browsed them, bookmarked them, and will return to them after reading the PDO tutorial/link. After reading it, I'll rewrite my code using it and post it here ASAP. Thanks again

01010011 2010-02-08 02:39:26

@Code Duck. Yes I am interested. I started reading the link Outis recommend but I need a backup just in case. Thanks.

01010011 2010-02-08 02:59:52

@Outis, the installation part (Windows) of the tutorial said to activate PDO by modifying the php.ini file with this line: "extension=php_pdo.dll" and below, enable the database-specific DLL with this line: extension=php_pdo_mysql.dll . Now, in XAMPP/php/php.ini I saw the first line, but I did not see the second,so I put it in and saved the file, right?

01010011 2010-02-08 03:29:57

@01010011: that's right. Make sure php_pdo_mysql.dll exists in "XAMPP\php\ext\"; if it doesn't, you might need to reinstall XAMPP. After that, restart Apache.

outis 2010-02-08 03:51:17

@Outis, I checked "XAMPP\php\ext\" and php_pdo_mysql.dll does exists. Great!

01010011 2010-02-08 18:08:12

@Outis, after stopping and restarting Apache and MySQL from XAMPP's control panel, I got two Warnings: "Module 'pdo_mysql' already loaded.

01010011 2010-02-08 18:45:14

@01010011: warnings you can often ignore. In this case, it means the extensions are already compiled into PHP. You can check this by commenting the lines to load the PDO and PDO MyQSL extensions, then creating a PHP page containing `<?php phpinfo(); ?>`. Finally, restart Apache and view the page in a browser. You should see some options beginning with '--with-pdo' in "Configure Command", and there should be a PDO section lower in the page.

outis 2010-02-08 20:46:40

@Outis, guess what! The w3schools' code above actually connected to the MySQL database! The problem was my firewall needed an exception to port 3306. I started the MySQL Server Instance Config Wizard to change my password yet again and while there I noticed that by default, the wizard provides networking on port 3306, but the firewall exceptions checkbox was not checked. So I checked it and ran the MySQL commands: show variables like 'port'; and show variables like 'skip_networking'; and this time the results were 3306 and OFF. And the code worked. To bad though, cause I am going to use PDO.

01010011 2010-02-09 16:13:37

Answer 3

+1 A:

Note: This is not the solution to the OP's problem, but I will keep my answer for historical reasons and also because it might be the issue for some other folks.

Are you on Windows Vista/7 and running PHP 5.3.1?

A recent change in the MySQL library in PHP right now (now using mysqlnd) is causing problems when connecting to localhost. A bug has been filled but until then, make sure that your hosts file contains an entry for localhost.

Your hosts file localhost entries must look like such:

127.0.0.1  localhost
#::1       localhost

As you can see, the IPv6 entry is commented out and the IPv4 entry isn't.

The hosts file is located at:

%WINDIR%\System32\drivers\etc\hosts

Source

Andrew Moore 2010-02-08 00:02:41

Thanks for your reply Andrew and thanks for that very useful source link. Im using XP and XAMPP. XAMPP's read me document stated among other things: "PHP 5.3.0", "Apache 2.2.12 (IPV6 enabled)"I checked the host file and "127.0.0.1 local host" was the only entry there. The link you provided stated that the line "#::1 local host" should be removed but I did not have that line. I also did not see anything concerning IPv4 or IPv6 in this file. So what do I do now?

01010011 2010-02-08 01:19:25

@01010011: In this case, this is not your issue. Check the answer above to make sure MySQL is listening on 3306.

Andrew Moore 2010-02-08 03:34:15

@Andrew, Marc B provided a nice way of finding this out. Can I change the port and skip_networking values from the MySQL Command Line?

01010011 2010-02-08 18:14:57

Answer 4

+1 A:

you cant recover the password but you can create a new one. turn off the mysql service and execute:

cd c:\mypathtomysql\bin
mysqladmin -u root password NEWPASSWORD

useless 2010-02-08 01:06:19

Yeah thanks for this. I still don't know why my password did not work for a day logging onto MySQL Command Line Client and that is very frustrating! Suddenly it started working the next day and this is the second time this has happened. So I changed the root hope this stops that problem

01010011 2010-02-08 01:30:23

My password stopped working again. However, I started the Server Instance Configuration Wizard and changed it and it worked, I got back in.

01010011 2010-02-09 15:47:45

Answer 5

+1 A:

If you can access the mysql shell, you can check the server's networking configuration like this:

mysql> show variable like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  | 
+---------------+-------+
mysql> show variables like 'skip_networking';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   | 
+-----------------+-------+
1 row in set (0.00 sec)

If 'skip_networking' is set to 'ON', then your mysql server has been configured to NOT allow access via the network and will function only via local sockets. Otherwise it'll be listening on the port specified in the 'port' configuration variable.

Marc B 2010-02-08 14:00:36

Nice one Marc and thanks for your reply! My port value was 0 and my skip_networking value was 'ON'. How do I change these values from the MySQL Command Line?

01010011 2010-02-08 17:26:26

I don't know if they can be changed from within mysql. My understanding is that if mysql starts up with skip_networking=on, it simply does not load the networking code and cannot be forced to load it afterwards.You'll most likely have to modify the server's my.ini (or my.cnf or whatever it's called on your particular install). skip-networking is set in the [mysqld] section, and port numbers in both the [client] and [mysqld] sections.

Marc B 2010-02-08 22:50:35

@Marc, I had a problem with my password for a third time and this time, I started the Server Instance Configuration Wizard and changed it and it worked. Anyway, while going through the configuration steps, I noticed that networking was on by default but the firewall exception for port 3306 was not checked. So I checked it and when the config was finished I tried the commands: show variables like 'port'; and show variables like 'skip_networking'; and this time the results were 3306 and OFF.

01010011 2010-02-09 15:52:04

Answer 6

+1 A:

Ok everybody, here is my very first attempts at connecting MySQL and PHP using PDO as suggested by Outis. It worked.

<?php 
$user = root;
$pass = password;
try
{
    $dbh = new PDO('mysql:host = localhost; dbname=databaseName', $user,$pass);
    if($dbh)
    {
        print "Connected successfully";
    }
}
catch (PDOException $e)
{
    print "Error: " . $e->getMessage(). "<br/>";
    die();
}
?>

Here is my second attempt - this time I am trying to do a query

<?php 
$user = root;
$pass = password;
try
{
    $dbh = new PDO('mysql:host = localhost; dbname=databaseName', $user,$pass);
    foreach($dbh->query('SELECT * FROM tableName') as $row)
{
    print_r($row);
}
$dbh = null; 
}
catch (PDOException $e)
{
    print "Error: " . $e->getMessage(). "<br/>";
    die();
}
?>

After running this code, I get the following results:

Array ( [exo_flowers_ID] => 1 [0] => 1 [name] => Dendroseris Neriifolia [1] => Dendroseris Neriifolia [country] => Chile [2] => Chile [env_workers_id] => 1 [3] => 1 ) Array ( [exo_flowers_ID] => 2 [0] => 2 [name] => Snowdonia Hawkweed [1] => Snowdonia Hawkweed [country] => North Wales [2] => North Wales [env_workers_id] => 1 [3] => 1 )

All I wanted was to display the contents of a row or column , but instead I got all of the contents plus all these brackets. How do I display only the contents of a table's row or column?

And finally, how do I so a query like: SELECT * FROM tableName WHERE 'columnName1' = 'somename' AND 'columnName2' = 'someothername'; ?

01010011 2010-02-09 23:34:19

I solved the problem above. Thanks for all the help guys

01010011 2010-02-13 11:46:01

ansaurus

tags:

views:

answers:

PHP MySQL Connection Failed

related questions