Solved

Cannot connect to mysql

Posted on 2013-12-11
31
490 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
  • 6
  • +1
31 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39712696
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
ID: 39712707
Thanks,

The error is
Could not connect: Permission denied
0
 

Author Comment

by:SiemensSEN
ID: 39712738
I notice that I can connect if I run the program from the local shell. However, I get permission denied from the browser..
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)

 
LVL 19

Expert Comment

by:xterm
ID: 39712784
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
ID: 39712797
I am using linux but this command does not exist
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39712798
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 83

Expert Comment

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

Author Comment

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

Expert Comment

by:xterm
ID: 39712804
What do you see if you type the following?

telnet localhost 3306
0
 

Author Comment

by:SiemensSEN
ID: 39712805
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
ID: 39712809
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
ID: 39712812
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
ID: 39712816
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
ID: 39712817
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
ID: 39712822
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
 

Author Comment

by:SiemensSEN
ID: 39712847
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
ID: 39712875
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
ID: 39713054
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
ID: 39713060
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
ID: 39713063
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
ID: 39713072
Please show the following SQL commands in the CLI in DB mysql:

SELECT * FROM db;
SELECT * FROM user;
0
 

Author Comment

by:SiemensSEN
ID: 39713133
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
ID: 39713211
I can connect from the linux shell. However, i get access denied when I access it from http
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39713277
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 83

Expert Comment

by:Dave Baldwin
ID: 39713419
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
ID: 39713799
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 83

Expert Comment

by:Dave Baldwin
ID: 39714676
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
ID: 39714686
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
ID: 39714722
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
ID: 39714907
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
ID: 39721840
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do I add date to text file name 15 28
Php variable to be sent back 3 35
Convert complicated date to yyyy-mm-dd format 22 53
Undefined variable with $_POST in PHP 5 39
This article discusses four methods for overlaying images in a container on a web page
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

739 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