Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a primary write server (server1) which replications to two servers (server2 and server3) which are query servers. I am replicating all databases to these servers including the MySQL database.

When i execute a GRANT as follows replication works perfectly..

GRANT execute,select ON database1.* TO `user1`@`host` IDENTIFIED BY 'password';

However if i did the same GRANT to alter permissions on an existing user without IDENTIFIED clause replication breaks..

Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'GRANT execute,select ON database1.* TO `user`@`host`'

If I try and run the query manually i get the same error..

Server 1:

mysql> SHOW VARIABLES LIKE "%version%";                                                                           
+-------------------------+------------------------------------------------------------+
| Variable_name           | Value                                                      |
+-------------------------+------------------------------------------------------------+
| protocol_version        | 10                                                         | 
| version                 | 5.0.77-log                                                 | 

**my.cnf**
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    old_passwords=1
    symbolic-links=0
    max_allowed_packet = 100M

    log-bin = /var/lib/mysql/logs/borg-binlog.log
    max_binlog_size=50M
    expire_logs_days=7

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

Server 2:

mysql>  SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------------------------------------+
| Variable_name           | Value                                                      |
+-------------------------+------------------------------------------------------------+
| protocol_version        | 10                                                         | 
| version                 | 5.0.77-log                                                 | 

my.cnf

server-id=12
master-host=x
master-user=x
master-password=x
master-connect-retry=60
relay-log=/var/lib/mysql/borg-relay.log
relay-log-index=/var/lib/mysql/borg-relay-log.index

Thanks for taking a look


Edit: Currently its running fine, until you do the grant which breaks it...

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.128.0.5
                Master_User: repli-ragnarok
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: borg-binlog.002730
        Read_Master_Log_Pos: 4375760
             Relay_Log_File: borg-relay.005489
              Relay_Log_Pos: 4375899
      Relay_Master_Log_File: borg-binlog.002730
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 4375760
            Relay_Log_Space: 4375899
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Edit: Broken show slave status from history

+----------------------------------+-------------+----------------+-------------+---------------+--------------------+---------------------+-------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host | Master_User    | Master_Port | Connect_Retry | Master_Log_File    | Read_Master_Log_Pos | Relay_Log_File    | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error                                                                                                                                                                                                     | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+-------------+----------------+-------------+---------------+--------------------+---------------------+-------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 10.128.0.5  | repli-valhalla |        3306 |            60 | borg-binlog.002729 |            40429793 | borg-relay.005486 |      40311514 | borg-binlog.002729    | Yes              | No                |                 |                     |                    |                        |                         |                             |       1133 | Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'GRANT execute,select ON auth_tracker.* TO `mail-sin1`@`%.sin1.netline.net.uk` IDENTIFIED BY 'mail-sin1666'' |            0 |            40311375 |        40429932 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL |
+----------------------------------+-------------+----------------+-------------+---------------+--------------------+---------------------+-------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.06 sec)
share|improve this question
    
What does "show slave status\G" say on your slave servers? –  Cakemox Mar 2 '11 at 11:47
    
@Cakemox - I have added the current show slave status and one from my xterm history showing where it had broken. Thanks –  Lee Mar 2 '11 at 12:45
    
Have you verified that users are being created on the slaves? Honestly it sounds like the user isn't being created on the slaves via the initial grant command. Can you run a grant with identified password command, and then show the output on the two slaves to make sure they are being created, and we can go from there. Also, is there anything in the logs to indicate something is happening? –  rfelsburg May 7 '11 at 4:09

2 Answers 2

A few things I would check:

  • Flush Privileges on all three servers (probably will do nothing but can't hurt).
  • Make sure the mysql.user table on the master is identical to that on the two slaves.
  • There appears to be a very similar MySQL Bug Report that may be relevant to your issue.
  • Check the server mode to see if indeed the issue is related to the NO_AUTO_CREATE_USER mode.
  • Try running the GRANT or manual queries directly on the slaves and see if you get the same or different results. Be aware of how this will replication and backup before playing with anything.
share|improve this answer
    
Hi there, running the query causes the same error however the user does seem to exist within the MySQL user table itself. NO_AUTO_CREATE_USER mode cant be set as an initial GRANT creates the user with an issue, it only happens when modifying a users permissions after it has been created.. Its very weird. –  Lee Mar 2 '11 at 15:13

I just had the same problem. In my case, when I set the SLAVE server, I just imported my application database dump, thus users created at default MySQL database are not on my SLAVE db server, then, the GRANT statement fails.

The way I see it, now either I create the users by hand, or start from scratch importing also the default database to my SLAVE server.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.