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
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?
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.