I have a replication slave configured with read-only on server start up, and a table that needs writing on this slave only. To that end, I’ve created a user with the following privileges:
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON slave_only_wr.* TO ‘slave_only_wr’@’%’;
GRANT SUPER on . TO ‘slave_only_wr’@’%’;
The ‘slave_only_wr’ database and user are created on the replication master, as is where the privileges were given, and flushed. And the tables in this DB exist on the slave only - which is where they should be written to. The user created specifically for this purpose, ‘slave_only_wr’ can write slave_only_wr.*, no problem, but, if I grant the above privileges to a pre-existing user (e.g. ‘app_user’@’%’ who already has ~50 other privileges, not including SUPER until now), and try writing the database or its tables, I keep getting:
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
I’m running out of ideas what may be causing this. Could this happen if the pre-existing user was created just by issuing GRANT … ON … TO…;, instead of running CREATE USER …? Granting the above privileges to ‘app_user’@‘hostame’ gives the desired effect.
Any ideas would be very appreciated.