Giving permissions to user doesn’t work in MySQL

Question

I executed this in phpMyAdmin.

I executed these lines:

CREATE USER root IDENTIFIED BY PASSWORD '';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

I get this when trying to connect:

Connect failed: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO)

$dsn = 'mysql:dbname=one';
$user = 'root';
$password = '';
$pdo = "Not set";
try{
    $pdo=new PDO($dsn, $user, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
}
catch(Exception $ex){
    echo "Connect failed: " . $ex->getMessage();
}

Note that the connection above used to work..now it doesn’t (since I switched to XAMPP).

I tested it again and i got this:host=127.0.0.1 . it works with number but doesnt work with localhost. However, when i get to the localhost i get to it like this: localhost:82 . could that be the reason that it didnt work..cause i didnt include the port number..and when i approach it with an ip it works? — BlackFire27, Dec 12 '12 at 14:40
Why are you creating the user `root`? It should always exist anyway, so there's no need to create it. — Berry Langerak, Dec 12 '12 at 14:40

score 1 · Accepted Answer · edited Nov 09 '19 at 18:52

1

Are you sure that the root user doesn't have a password? Do you have PhpMyAdmin installed to check this?

If the problem isn't the privileges, then it's probably the port. The default port of MySQL is 3306, so if your port for MySQL is now 82 that you have to add the port in the connection.

edited Nov 09 '19 at 18:52

Peter Mortensen

30,738
21
105
131

answered Dec 12 '12 at 14:35

Perry

11,172
2
27
37

score 0 · Answer 2 · answered Dec 12 '12 at 14:37

0

Please, check create user specification, seems you should use another syntax for account name

Syntax for account names is 'user_name'@'host_name'.

So, something like CREATE USER 'root'@'localhost' IDENTIFIED BY PASSWORD '';. In your case CREATE USER 'root'@'%'; but I really did not recommend use '%' for root user.

answered Dec 12 '12 at 14:37

Vadim

622
3
5

I think it is something to do do with the port ..i think I should connect with localhost:82, instead of localhost – BlackFire27 Dec 12 '12 at 14:41
localhost and 127.0.0.1 are similar, so, if your connection work with 127.0.0.1 it's enough. – Vadim Dec 12 '12 at 15:53
also, if it not help, please, check http://stackoverflow.com/questions/1435445/error-on-creating-connection-to-pdo-in-php approved answer. Those answer has useful information of different DSN which used while we try to connect to DB via PDO – Vadim Dec 12 '12 at 15:54

score 0 · Answer 3 · answered Dec 12 '12 at 14:39

0

Are you flushing privileges after altering permissions?

You should execute FLUSH PRIVILEGES; to tell the server to reload the grant tables.

answered Dec 12 '12 at 14:39

philwilks

669
5
16

score 0 · Answer 4 · edited Nov 09 '19 at 18:50

0

The host % does not apply to localhost. You need to grand permissions for localhost separately.

edited Nov 09 '19 at 18:50

Peter Mortensen

30,738
21
105
131

answered Dec 12 '12 at 14:50

Maxim Krizhanovsky

26,265
5
59
89

Giving permissions to user doesn’t work in MySQL

4 Answers4

Linked