"Can't find any matching row in the user table" after upgrading mysql from 5.6 to 5.7

Question

I'm getting this error:

Can't find any matching row in the user table

When trying to execute this line of my CI script:

GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'host' WITH GRANT OPTION;

I know that similar questions have been already asked:

Can't find any matching row in the user table

MySQL Error #1133 - Can't find any matching row in the user table

https://dba.stackexchange.com/questions/69921/error-code-1133-cant-find-any-matching-row-in-the-user-table

however, none of them solved my issue.

In particular, in my case, this issue started after I've upgraded my travis-ci from using ubuntu 14.04 to 16.04, which also changed the MySQL version from 5.6 to 5.7.

So I'm sure the same code works with MySQL 5.6 but not with MySQL 5.7.

What could be the issue?

Are you trying to create a new username? You should use `CREATE USER` first. — Barmar, Dec 21 '18 at 01:18
Actually, the change I was thinking of is in MySQL 8.0, not 5.7. They removed the ability to create users with `GRANT`. So I think this should work in 5.7. — Barmar, Dec 21 '18 at 11:50
@Barmar your solution solved the issue, would you like to post it as normal answer so I can accept it? — Francesco Borzi, Dec 21 '18 at 11:52

score 3 · Accepted Answer · answered Dec 21 '18 at 11:53

In MySQL 5.7 they changed the default SQL modes to include NO_AUTO_CREATE_USER. This prevents GRANT from creating users, so you have to use CREATE USER instead. You can also remove this setting from the SQL mode of your server.

This change was in preparation for 8.0, which removes the ability of GRANT to create users entirely.

"Can't find any matching row in the user table" after upgrading mysql from 5.6 to 5.7

1 Answers1