1

I am setting up a small development local server (is a VM running CentOS 7) and because of that I didn't add any password to MySQL after run the command: mysql_secure_installation. Now I need to connect from the host through MySQL Administrator but I can't because the user root isn't allowed to connect from the host IP address so I am gonna to change that by giving root all privileges and the host.

This is what I have done so far:

$ mysql -uroot -p
Enter password:  [here is just ENTER because root hasn't a password]
Server version: 10.1.18-MariaDB MariaDB Server

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.3.%' WITH GRANT OPTION;
ERROR 1133 (28000): Can't find any matching row in the user table

I have found that error so I do some research on Google and found a bunch of links so I pick some of them like this one and tried:

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.3.%' WITH GRANT OPTION;
ERROR 1133 (28000): Can't find any matching row in the user table

But still not working, is a restriction and I am not aware? is a configuration on the my.cnf file? is a problem in the SQL query? Any help?

1
  • 1
    either first set your set sql_mode =''; or first issue create user command and then issue the grant statement.. Hope it helps. Oct 10 '16 at 9:42
2

"If the NO_AUTO_CREATE_USER SQL_MODE is set, users can only be created with a CREATE USER statement. In this case GRANT will produce an error when the specified user does not exist." -- https://mariadb.com/kb/en/mariadb/grant/

If that is the case, then the solution is probably:

CREATE USER 'root'@'192.168.3.%';
GRANT ... (as you tried)

"Can't find any matching row..." means that there was no row with both user='root' and host='192.168.3.%'. You were coming in with user='root' and host='localhost' (or something like that).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.