Solved

Cannot connect to mysql

Posted on 2013-12-11
31
479 Views
Last Modified: 2014-01-15
Hello .
  I cannot connect to MYSQL from PHP.

The ID/pass is correct since I can connect from the shell.

GRANT OUTPUT
+----------------------------------------------+
| Grants for wikiadm@%                         |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wikiadm'@'%' |
+----------------------------------------------+

Open in new window

MYSQL USER TABLE
user    | host      |
+---------+-----------+
| wikiadm | %         |
|| root    | localhost |
| wikiadm | localhost |
+---------+-----------+


PHP CODE
<?php

// Show all information, defaults to INFO_ALL
$username = "wikiadm";
$password = "password";
$hostname = "localhost";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

Open in new window

0
Comment
Question by:SiemensSEN
  • 14
  • 10
  • 6
  • +1
31 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
That code has always worked for me.  http://www.php.net/manual/en/function.mysql-connect.php  Here's the code from that page.  Try it with your user info and let us know what the error message is.
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

Open in new window

0
 

Author Comment

by:SiemensSEN
Comment Utility
Thanks,

The error is
Could not connect: Permission denied
0
 

Author Comment

by:SiemensSEN
Comment Utility
I notice that I can connect if I run the program from the local shell. However, I get permission denied from the browser..
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
What operating system are you running, and if Linux, what is the output of the following command?

/usr/sbin/getenforce
0
 

Author Comment

by:SiemensSEN
Comment Utility
I am using linux but this command does not exist
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
If you're on Windows 7 or above, try using '127.0.0.1' as the server name.  Some versions of MySQL don't work with IPV6 which is the default 'localhost' in Windows 7 and above.  Alternately use the IP address of the machine itself.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Is the web server on the same machine as the MySQL server?  It has to be to use 'localhost'.
0
 

Author Comment

by:SiemensSEN
Comment Utility
Yes, Both the webserver (apache) and the mysql is on the same server
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
What do you see if you type the following?

telnet localhost 3306
0
 

Author Comment

by:SiemensSEN
Comment Utility
I  run  the program from the Linux shell and it connect to the DB. However, it does not work if I run it from the browser via webserver
0
 

Author Comment

by:SiemensSEN
Comment Utility
telnet localhost 3306
Trying ::1...
telnet: connect to address ::1: Connection refused
Trying 127.0.0.1...
telnet: connect to address 127.0.0.1: Connection refused

-----
I will look at the port rules again..
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
I  run  the program from the Linux shell and it connect to the DB. However, it does not work if I run it from the browser via webserver

Yes, I'm trying to determine if your MySQL server is listening on port 3306 on localhost.  When you use the mysql cli, it usually uses the socket file (normally /tmp/mysql.sock) but Apache/PHP connects via tcp, port 3306.

So I would still like to see the output of each of these two commands:

telnet localhost 3306
netstat -na | grep :3306
0
 

Author Comment

by:SiemensSEN
Comment Utility
telnet localhost 3306
Trying ::1...
telnet: connect to address ::1: Connection refused
Trying 127.0.0.1...
telnet: connect to address 127.0.0.1: Connection refused
-----------------------------------------------------------------

 netstat -na | grep :3306
tcp        0      0 172.20.32.86:3306       0.0.0.0:*               LISTEN
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
You need to set bind-address to 0.0.0.0 in /etc/my.cnf and restart MySQL and then it will work.

Right now it's only listening on your external IP address.
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
The output of netstat should look something like this after you change it:

[xterm@foo ~]$ netstat -na | grep :3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:SiemensSEN
Comment Utility
I understand .. but
Here my issue.

I have  a WIKi that connect to mySQL on server A that connect to DB and I can access it via a URL.

here is the output for SERVER A
 netstat -na | grep :3306
tcp        0      0 172.25.0.14:3306        0.0.0.0:*               LISTEN
------------------------------
telnet localhost 3306
Trying 127.0.0.1...
telnet: connect to address 127.0.0.1: Connection refused
Trying ::1...

Open in new window


On SERVER B it does not work. It have same wiki and mysql

telnet localhost 3306
Trying ::1...
telnet: connect to address ::1: Connection refused
Trying 127.0.0.1...
telnet: connect to address 127.0.0.1: Connection refused
usbrws02:(root) [/export/apps] $ netstat -na | grep :3306
tcp        0      0 172.20.32.86:3306       0.0.0.0:*               LISTEN

Why would SERVER A works and not SERVER B
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
Probably on server A you configured it to connect to 172.25.0.14 instead of localhost, because your code from server B won't work there either.

You can change $hostname on server B to be 172.20.32.86 instead of localhost, and it will probably work, but then you will need to put that IP in the mysql user table and do a reload/flush privileges.
0
 

Author Comment

by:SiemensSEN
Comment Utility
change it to bind to 0.0.0.0 but it did not work

 netstat -na | grep :3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

the error is..

Can't contact the database server: Permission denied (localhost))
0
 

Author Comment

by:SiemensSEN
Comment Utility
This is grant command for the id wikiadm

 Grants for wikiadm@localhost                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wikiadm'@'localhost' IDENTIFIED BY PASSWORD '*1B36526200EB58A9AF17101DFBAEBD2DAE877C1E' |
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
Have you run "flush privileges" since you did the GRANT command?

BTW, now the problem of not being able to connect to localhost is fixed, now you have a permissions issue.  Are you sure you're able to connect with the CLI?
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
Please show the following SQL commands in the CLI in DB mysql:

SELECT * FROM db;
SELECT * FROM user;
0
 

Author Comment

by:SiemensSEN
Comment Utility
Yes, I issue the flush command

mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

GRANT output

mysql> show grants for wikiadm@localhost
    -> ;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for wikiadm@localhost                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wikiadm'@'localhost' IDENTIFIED BY PASSWORD '*1B36526200EB58A9AF17101DFBAEBD2DAE877C1E' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

OUTPUT from DB (the wikiadm does not exist..)

mysql> SELECT host, db, user  FROM db;
+-----------+-------+-------+
| host      | db    | user  |
+-----------+-------+-------+
| localhost | oemdb | oemdb |
+-----------+-------+-------+
1 row in set (0.00 sec)

Open in new window

-----------------------

mysql> select host,user from user;
+-----------+---------+
| host      | user    |
+-----------+---------+
| 127.0.0.1 | root    |
| ::1       | root    |
| localhost | oemdb   |
| localhost | root    |
| localhost | wikiadm |
+-----------+---------+
5 rows in set (0.01 sec)

Open in new window

0
 

Author Comment

by:SiemensSEN
Comment Utility
I can connect from the linux shell. However, i get access denied when I access it from http
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
This really isn't this hard.  If you are connecting to the MySQL server from the machine that it is on, you can use 'localhost'.  If you are connecting from another machine, you have to use the IP address of the machine that MySQL is running on.  A remote connection can not be made with 'localhost' because it means exactly what it sounds like.  On the "local host" and no where else.  Same with IPV6 ::1 and IPV4 127.0.0.1, those are strictly 'localhost', not remote host.

http://dev.mysql.com/doc/refman/5.5/en/connecting.html
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
I was just working on my Ubuntu Linux box and I couldn't connect to it remotely.  So I brought up the settings in WebMin and it was set to only respond to '127.0.0.1'.  I changed it to 'anyhost' and the problem was solved.  Now I have to do it with my other machine.  I have my MS SQL servers set up to accept remote connections also.  Makes it easier to test code that way.
0
 

Author Comment

by:SiemensSEN
Comment Utility
Maybe I an not clear..

<?php

// Show all information, defaults to INFO_ALL
$username = "wikiadm";
$password = "password";
$hostname = "localhost";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>"; 

Open in new window


The code above works when I run it from the linux shell [[php connect.php]]

However, if i open a browser from a remote machine and type [[http://<hostIP>/connect.php then I get the access error
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Is the web server where PHP is running on the same machine where you are connecting from the shell?  Then it should work.  Or is the web server where PHP is being run on a different machine?  It that is the case, then 'localhost' shouldn't work.
0
 
LVL 19

Expert Comment

by:xterm
Comment Utility
What happens when you type from the linux shell?:

mysql -u wikiadm -p password oemdb

(where "password" is whatever you set the password to for the wikiadm user)
0
 

Author Comment

by:SiemensSEN
Comment Utility
The code works from the Linux shell


If  I change the ip from localhost to the server ip (172.20.nn.nn) then the code works from the shell and browser

So, localhost or 127.0.0.1 does not work when I try to access the script via http . However it works from the shell
0
 

Expert Comment

by:selhamwy
Comment Utility
try changing the $hostname
technicallly should be service in the format

hostname:port/database/

default port is 3306 for localhost,
yours may be different

localhost:3306/oemdb/

see if that fixes it
that was the  problem with me at first
0
 
LVL 19

Accepted Solution

by:
xterm earned 300 total points
Comment Utility
If the CLI works, but the web server doesn't, it sure sounds like selinux is denying it - what distribution of Linux are you running, and do you have the folder on your system /var/log/audit?
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now