I can remotely connect to mySQL database in command line, but not with php script

Question

The Error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[28000] [1045] Access denied for user 'tech'@'localhost' (using password: YES)' in .......

I'm able to remotely connect in the command line, but not using php, any ideas where to look?

Here are the details:

Webserver A: 1.1.1.1 holds the php script:

<?php
    error_reporting(E_ALL);
    ini_set('display_errors', '1');

    $REGIS_DB_SERVER = "2.2.2.2"; //I've tried the IP as well as "pretendhostname.com"
    $REGIS_DB_NAME = "db_name";
    $REGIS_DB_USER = "tech"; // or any other user name
    $REGIS_DB_PASSWORD = "password";

    //connect to db
    $db = new PDO("mysql:host = $REGIS_DB_SERVER; dbname=$REGIS_DB_NAME; charset=utf8", $REGIS_DB_USER, $REGIS_DB_PASSWORD);
    print_r($db -> errorInfo());
    echo "Connected<br>";

    $sql = "CREATE TABLE Test(PersonID int,LastName varchar(255),FirstName varchar(255),Address varcha(255),City varchar(255))";
    $result = $db->query($sql);

    echo "$result <be>";

?>

Webserver B: 2.2.2.2 holds the mySQL database: Running Ubuntu 11.04

in /etc/mysql/my.cnf

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#Commented out to allow remote connections to this database
#bind-address           = 216.70.81.240

I run mysql> select Host,User from user; and get:

+-----------------------+------------------+
| Host                  | User             |
+-----------------------+------------------+
| %                     | root             |
| 127.0.0.1             | root             |
| 1.1.1.1               | tech             |
| localhost             | debian-sys-maint |
| localhost             | root             |
| localhost             | tech             |
+-----------------------+------------------+

Open Port 3306 in ubuntu 12.04 to allow connections to mysql from any ip implies it's not an iptable problem and to test out the connection:

I ssh-ed into Webserver A 1.1.1.1 and ran:

mysql -u tech -p -h 2.2.2.2
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 200672
Server version: 5.1.63-0ubuntu0.11.04.1 (Ubuntu)

this looks like database permissions/privileges issue. You might want to double check your database permissions for this user from this host. — Maximus2012
– Maximus2012, Commented Sep 25, 2013 at 18:38
Notice that the error message says tech@localhost, not [email protected]. It looks it's ignoring host = $REGIS_DB_SERVER. — Barmar
– Barmar, Commented Sep 25, 2013 at 18:40
I noticed this and it could be the reason why. I can't figure out why it's ignoring mysql:host = $REGIS_DB_SERVER; am I missing a setting on Webserver A 1.1.1.1? — Alf
– Alf, Commented Sep 25, 2013 at 19:40
According to MYSQL documentation: dev.mysql.com/doc/refman/5.5/en/access-denied.html It says: If you have access problems with a Perl, PHP, Python, or ODBC program, try to connect to the server with mysql -u user_name db_name or mysql -u user_name -pyour_pass db_name. If you are able to connect using the mysql client, the problem lies with your program, not with the access privileges. So I guess my PHP is wrong? The php script works for accessing local mySQL db though. It just seems to ignore the host= for remote databases — Alf
– Alf, Commented Sep 25, 2013 at 20:51

Suraj Rao · Accepted Answer · 2022-06-30 11:45:02Z

4

you need disabled the SELinux, for run the below CMD

setsebool httpd_can_network_connect=1

edited Jun 30, 2022 at 11:45

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Apr 19, 2018 at 11:21

Ganesan Murugesan

1552 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Indra Kumar S Over a year ago

+1.Thanks for your answer. It helped me i just upgraded my server from centos 6 to centos 8 and was struggling bcz of SELINUX

Indra Kumar S Over a year ago

And add -P for persistence even after reboot. sudo setsebool -P httpd_can_network_connect=1

Community · Accepted Answer · 2017-05-23 12:13:04Z

1

After finding: PDO cannot connect remote mysql server I realized that if the host is not "understood", the PDO connection will default to host=localhost. Looking more closely, I realized there were 2 spaces between the assignment. I did not realize spaces mattered in the syntax.

So in the end, it was a syntax error:

The working solution (by removing spaces):

$db = new PDO("mysql:host=$REGIS_DB_SERVER; dbname=$REGIS_DB_NAME; charset=utf8", $REGIS_DB_USER, $REGIS_DB_PASSWORD);

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Sep 26, 2013 at 22:32

Alf

8621 gold badge8 silver badges18 bronze badges

Comments

aaron · Accepted Answer · 2013-09-25 18:51:14Z

0

This is definitely a database permissions/privileges issue. Ask your dba/sysad to GRANT privileges for your local server to access the database

answered Sep 25, 2013 at 18:51

aaron

6874 silver badges11 bronze badges

3 Comments

Alf Over a year ago

GRANT ALL ON db.* TO tech@'localhost' IDENTIFIED BY 'password'; I've tried the above replacing db with the database name and password with pw

aaron Over a year ago

Webserver A: 1.1.1.1 holds your php script. Webserver B: 2.2.2.2 holds the mySQL database. So, on Webserver B, you should run the command as GRANT ALL ON db.* TO tech@'1.1.1.1' IDENTIFIED BY 'password'. This is because, you are doing an ssh into 1.1.1.1 and then connecting to 2.2.2.2. so the webserver B has to grant privileges on the server from where you are runnning the php, in this case 1.1.1.1

Alf Over a year ago

On webserver B, I opened mysql> and ran the suggested command. It returns Query OK, 0 rows affected (0.00 sec) Thanks for the explanation. Unfortunately, it doesn't work. I checked the mysql database on phpmyadmin, and in the user table, I see the 1.1.1.1 and tech host+user combo. All the priveleges seem to still be N. But I continue to be able to access remotely from command line, but not from the php script :( EDIT- I can connect to a db in command line and run SQL queries. I think my best lead for the PHP is still the host ignoring part

Collectives™ on Stack Overflow

I can remotely connect to mySQL database in command line, but not with php script

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related