[FIXED] Why can't access to especific DB using PDO, but can using mysqli without DB name? (usign devserver/phpmyadmin)?

Issue

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?

Solution

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).

Answered By - Lokotito

Answer Checked By - Robin (PHPFixing Admin)