When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';
, mysqld wants to insert a row into the grant table mysql.tables_priv
. Here is mysql.tables_priv:
mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)
mysql>
Since you want to insert a row into mysql.table_priv
where user='wordpress' and host='%', there has to exist a row in mysql.user
where user='wordpress' and host='%'.
You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'
. That would usually have all rights and a password.
If you want to just allow anonymous SELECT against that table, first run this:
GRANT USAGE ON *.* TO 'wordpress'@'%';
This will place wordpress@'%'
into mysql.user
. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%'
should run just fine.
You will have to see what other wordpress entries are in mysql.user
. This should show what SQL GRANT commands you need:
SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
SELECT CONCAT('''',user,'''@''',host,'''') userhost
FROM mysql.user WHERE user='wordpress'
) A;
Best Answer
Do you have access to the root account? Are you pointing to the right host? Sometimes it is useful to identify the host with the -h option to avoid DNS issues
If you still can't get into MySQL, you can reset the root password by starting MySQL with --skip-grant-tables option
Stop MySQL
In Linux, as root user or equivalent
Start MySQL with --skip-grant-tables option, you can find the mysqld_safe binary file with the which command
Login to MySQL without password
Login to MySQL database
Update the root user password
This will reset the password for any account called root, regardless of the host.
Flush privliges
Stop and Start MySQL
If you are using Windows, you can simply update the my.cnf file with this option under the [mysqld] section
If you are not sure where the my.cnf file is, you can find the path by going to Administrative Tools available in Control Panel
In this case, you would most likely find the my.cnf file in a subdirectory of C:\Program Files\MySQL\MySQL Server 5.5\
And then restart MySQL using the Windows Services control panel in Administrative Tools.
From there you would be in safe mode and the commands would be the same.
update user set password=PASSWORD("mynewpassword") where User='root'; flush privileges; quit
remove the skip-grant-tables from the my.cnf file and then restart MySQL
Conclusion
Now that you have root access again to MySQL, you can setup new users again