Asked  7 Months ago    Answers:  5   Viewed   71 times

I am trying to do some testing, i am using MAMP and trying to connect to my SQL database on my server. I haven't used PDO before and am having a hard time tracking down what error codes mean what. I am getting this:

SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'hostname' (60)

My connection function:

  function getConnection() {
    $dbhost="hostname";
    $dbuser="user";
    $dbpass="password";
    $dbname="somedb";
    $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $dbh;
  }

where i am trying to perform the query:

  function getTypes() {
    $sql = "SELECT id, name FROM type";
    try {
      $db = getConnection();
      $stmt = $db->query($sql);
      $types = $stmt->fetchAll(PDO::FETCH_OBJ);
      $db = null;
      echo '{"wine": ' . json_encode($types) . '}';
    } catch(PDOException $e) {
      echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
    echo 'getTypes';
  }

Edit: I can connect using the same credentials with mysqli.

 Answers

53

It's a security problem. To avoid your database beeing flooded or filled with garbage a configuration setting says "only local ip can access to the database". All shared hosting do that and it is a good practice to do it if you have a dedicated server.

in /var/mysql/my.conf on linux you have got something like that :

skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
Saturday, May 29, 2021
 
jcubic
answered 7 Months ago
79

The issue was with our SQL server configuration, and not the Unix SQLSRV PDO @ https://github.com/Microsoft/msphpsql.

On our SQL server on Azure, static connections to dynamic ports without the UDP communication occurring on port 1434 were failing.

Assumptions are as a DOS mitigation with SQL Browser, without a solicitated communication (UDP) to SQL, to utilize dynamic TCP ports, those ports will refuse via an ICMP unreachable message.

Once TCP Dynamic Ports were cleared from the daughter NICs and parent IP ALL configurations, and a TCP Port declared on the IP ALL and active NIC, connections formed.

SQL Server TCP Config Example for MSPHPSQL PDO

Wednesday, March 31, 2021
 
Elxx
answered 9 Months ago
63

fetchAll returns an array containing all of the result set rows. So you can access to password with $data_array[0]['password'] if you used it. You may want use fetch instead.

$data_array = $stmt->fetch(PDO::FETCH_ASSOC);
Saturday, May 29, 2021
 
JakeGR
answered 7 Months ago
31

All of the mysqli functions/methods can fail in which case they will return false. I.e. if prepare() fails $stmt isn't an object you can call a method on but a bool(false). You have to check the return values and add some error handling, e.g.

$stmt = $mysqli->prepare('SELECT name FROM `rooms` WHERE r_id=?');
if ( !$stmt ) {
    printf('errno: %d, error: %s', $mysqli->errno, $mysqli->error);
    die;
}

$b = $stmt->bind_param('i', $roomID);
if ( !$b ) {
    printf('errno: %d, error: %s', $stmt->errno, $stmt->error);
}

$b = $stmt->execute();
if ( !$b ) {
  and so on and on

see http://docs.php.net/mysqli-stmt.errno et al


in this case you probably bumped into the problem that you can't create an other statement while there are still results/result sets pending for the previous statement.
see http://docs.php.net/mysqli-stmt.close:

Closes a prepared statement. mysqli_stmt_close() also deallocates the statement handle. If the current statement has pending or unread results, this function cancels them so that the next query can be executed.
Wednesday, September 1, 2021
 
Ryan Stewart
answered 3 Months ago
55

After a long thread with Google Support, we found the reason to be: simply we should enable public access to Cloud SQL without any firewall rule. It is undocumented and can drive you crazy, but the silver bullet for the support team is to say: it is in beta!

Tuesday, November 23, 2021
 
okapi
answered 2 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share