0

I am using MySQL 8.0 and php 7.3.0

I have granted full permissions for 'user_insert_'@'127.0.0.1', and am able to run the following SELECT statement fine from pymysql and MySQL Workbench, but not through PDO in php:

"SELECT * FROM worker_status"

The error I receive is:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'user_insert_'@'localhost' for table 'worker_status' in C:\xampp\htdocs\hud\test_api_2\objects\activity.php:33

I expected to receive a single row response.

I have already modified the MySql user to use native passwords using the following line:

ALTER USER user_insert_ IDENTIFIED WITH mysql_native_password BY 'password123';

This modification is a solution referenced here: phpMyAdmin on MySQL 8.0 (Also the solution from which this thread is marked as a duplicate)

Here's the relevant code :

class Database{

private $host = "127.0.0.1";
private $db_name = "hud";
private $username = "user_insert_";
private $password = "password123";
private $charset = 'utf8mb4';
private $dsn = "";
public $conn;

public function getConnection(){
  $this->conn = null;

    $this->dsn = "mysql:host=$this->host;dbname=$this->db_name;charset=$this->charset";
    print($this->dsn);
    $this->options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    try {
     $this->conn = new PDO($this->dsn, $this->username, $this->password, $this->options);
    } catch (\PDOException $e) {
         throw new \PDOException($e->getMessage(), (int)$e->getCode());
    }


    return $this->conn;
}


    public function __construct($db){
    $this->conn = $db;
}

function read(){

    $query = "SELECT * FROM worker_status";
    $stmt = $this->conn->prepare($query);
    $stmt->execute();
    $result = $stmt->fetch();

    return $stmt;
}

I've checked:

  • show grants for 'user_insert_'@'127.0.0.1'; -- Which shows that all permissions are granted for that user.
  • There aren't any exceptions generated when the database connection starts.
  • The correct database and table is being connected to.
  • That my PDO syntax matches the documentation.
  • The user is already connecting using mysql_native_password ( Reference: phpMyAdmin on MySQL 8.0)

This seemed to me like a pretty vanilla case. Why am I getting a 1142 SELECT permission error exclusively with php PDO?

Daniel Scott
  • 979
  • 7
  • 16
  • You error message shows `'user_insert_'@'localhost'` while your question mentions `'user_insert_'@'127.0.0.1'` ; try changing your connection string to host `127.0.0.1` – GMB Jan 04 '19 at 23:06
  • @GMB I had used `private $host = "127.0.0.1";` as the variable in my connection string (not localhost), but just tried overwriting the variable in the connection string like this: `$this->dsn = "mysql:host=127.0.0.1;dbname=$this->db_name;charset=$this->charset";` Either version gives me the original error. – Daniel Scott Jan 04 '19 at 23:20
  • Change the host part of the user from 127.0.0.1 to localhost. – Shadow Jan 04 '19 at 23:21
  • @Shadow after modifying`private $host = "localhost";` I get the error: `Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client in C:\xampp\htdocs\hud\test_api_2\config\database.php:26` – Daniel Scott Jan 04 '19 at 23:23
  • If you get an error message, pls do a search on it because it is extremely likely that it already has an answer. See https://stackoverflow.com/questions/49948350/phpmyadmin-on-mysql-8-0 – Shadow Jan 04 '19 at 23:35
  • @Shadow You make a good point, and I had anticipated the nudge towards this solution. I have already implemented the solution found there, and other similar threads, which I tried to highlight with my last bullet point. I will try to do a better job making that clear for future questions. – Daniel Scott Jan 04 '19 at 23:39

0 Answers0