Not the answer you need?
Register and ask your own question!

SUPER allows writing read-only slave for some users, but not the others

milosbmilosb ContributorCurrent User Role Contributor
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.

Comments

  • milosbmilosb Contributor Current User Role Contributor
    Update: Because I was connecting to the replication slave locally (i.e. -h localhost implies), and there already was a 'app_user'@'localhost' defined, to whom the SUPER privilege was never granted, the user/privilege earlier assigned was never evaluated because @'localhost' is a closer match.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Thanks for that update, it could be very useful to another reader. Are you all good now?
  • milosbmilosb Contributor Current User Role Contributor
    Yes, thanks. This issue is resolved.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.