1

I install easyphp/devserver (v17.0) in a windows 10 x64 machine. Working with tables and testing to store remote data with simple php files (very new with DataBases). I'm trying to setup an access to my DB for my project (preferably using PDO).

Setup all database through mysql commands:

CREATE database arduDB;
CREATE USER 'Atmega'@'localhost';
GRANT USAGE on *.* to 'Atmega'@'localhost';
GRANT ALL PRIVILEGES ON arduDB.* TO 'Atmega'@'localhost';
CREATE USER 'Atmega'@'%';
GRANT USAGE on *.* to 'Atmega'@'%';
GRANT ALL PRIVILEGES ON arduDB.* TO 'Atmega'@'%';
FLUSH PRIVILEGES;

All fine up here.

But can't access to my DB (arduDB) using PDO, but can using MYSQLI without using DB name 'arduDB'.

I deleted '' (Any) users from phpmyadmin, but still can't access using PDO. Searched and reading all day about this issue, but can't find a reason why happen this. As well I create another user with a password, but can't access using PDO to DB neither.

It seems that phpmyadmin can't relate the permissions of the DBs,

edited This 'add.php', without using 'arduDB' name give access through MYSQLI:

<?php
    $servername = "localhost";
    $username = "Atmega";

    // Create connection
    $conn = new mysqli($servername, $username);

    // Check connection
    if ($conn->connect_error) {
        die("Failed: " . $conn->connect_error);
    }
    echo "Connected!!!";
?>

shows "Connected" in web browser. But same error if I try to use arduDB argument.

But when I use PDO, indicating arduDB database, I can't access to my DB.

<?php
    $servername = "localhost";
    $username = "Atmega";

try {
    $conn = new PDO("mysql:host=$servername;dbname=arduDB", $username);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected!!!";
    }
catch(PDOException $e)
    {
    echo "Failed: " . $e->getMessage();
    }
?>

SQLSTATE[HY000] [1044] Access denied for user ''@'localhost' to database 'ardudb' or SQLSTATE[HY000] [1044] Access denied for user 'Atmega'@'localhost' to database 'ardudb'

Please, what can I need to configure or use in my code to grant access to a specific DB using PDO in my project?

Lokotito
  • 27
  • 5
  • In mysqli you didn't select the DB. – Paul Spiegel Feb 15 '19 at 23:40
  • Using 'arduDB' in MYSQLI gives me the same error: Access denied for user 'Atmega'@'localhost' to database 'ardudb'. How can I change the permission for Atmega user ? I think already did it in mysql commands. – Lokotito Feb 16 '19 at 00:16
  • First of all always use a password, you will rid all kind of errors in the future and check user privileges if he is granted permissions for connecting to your database. https://serverfault.com/questions/2616/which-mysql-users-have-access-to-a-database – Vytenis Ščiukas Feb 16 '19 at 04:56
  • Thanks vytsci, I used a password also like I sayd, but try also without password to see what happens. Thanks also Paul Spiegel to make me notice of my error, but I found the issue, is related with phpmyadmin, this use "unix_socket" to store user passwords in 'mysql' db. – Lokotito Feb 16 '19 at 05:19

1 Answers1

0

Finlay I found the response. Like André Verwijs says in his post, phpmyadmins use "unix_socket" to store user passwords in 'mysql' db. This is the motive that even if create or change password for any user, in 'User accounts' section of phpmyadmin always appears "NO" in PASSWORD COLUMN. To solve this André Verwijs suggests do this in mysql console:

use mysql;

SELECT user, plugin FROM user;

UPDATE user SET plugin="mysql_native_password"; 

update user set authentication_string=password('USE-HERE-PASSWORD'), plugin='mysql_native_password' where user='Atmega';

FLUSH PRIVILEGES;

With this phpmyadmin will use plugin "mysql_native_password" for passwords in mysql DB. And now can access to my DB whit correct user and password (or without password).

Lokotito
  • 27
  • 5