This question was marked earlier as a duplicate even though I had already implemented the most-searched solutions. I modified the MySql user to use native passwords using the following line (which did not fix my connection issue):
ALTER USER user_insert_ IDENTIFIED WITH mysql_native_password BY 'password123';
This code was a solution referenced here: phpMyAdmin on MySQL 8.0 (Also the solution from which this thread is marked as a duplicate)
1142 SELECT command denied to user
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.
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;
}
Also, when trying to access phpmyadmin at "", I get a red error message referencing that caching_sha2_password is not enabled for broswer access.
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?
