MySQL Bugs: #21645: query cache NOT refreshing on slave after row-based replication updates

Bug #21645	query cache NOT refreshing on slave after row-based replication updates
Submitted:	15 Aug 2006 5:26	Modified:	27 Nov 2006 17:54
Reporter:	Lu Vo	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.11/5.1BK	OS:	Linux (centOS 4.3)
Assigned to:	Chuck Bell	CPU Architecture:	Any
Tags:	query cache, row-based replication

Description:
This is not the same as bug #17620.  replication is fine but cache is not refreshing.

Query cache is turned on for both master and slave.
When a value on master is updated, the row-based replication to slave is succesful.  However one would expect that the cache for that value should be cleared on both master and slave.  No problem on master.  On slave, if the same query (as before the update) is used, a stale record is returned.  In order to get the fresh data,  a slight mod to the query such as introducing a space between key words will work indicating that the cache is not cleared.

Other than that, row-based replication is working nicely.
Appreciate a quick fix on this.
Thanks. 

How to repeat:
ensure query caching on slave server is turned on.  We used 20M

1) slave
mysql> select password from users where id=21907;
+----------+
| password |
+----------+
| cccccc   | 
+----------+
1 row in set (0.00 sec)

2) master
mysql> select password from users where id=21907;
+----------+
| password |
+----------+
| cccccc   | 
+----------+
1 row in set (0.00 sec)

3) master
mysql> update users set password='aaaaaa' where id=21907;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select password from users where id=21907;
+----------+
| password |
+----------+
| aaaaaa   | 
+----------+
1 row in set (0.00 sec)

4) slave
mysql> select password from users where id=21907;
+----------+
| password |
+----------+
| cccccc   | 
+----------+
1 row in set (0.00 sec)

5) slave  ****change the query slightly so as not to hit cache***

mysql> select password from users where          id=21907;
+----------+
| password |
+----------+
| aaaaaa   | 
+----------+
1 row in set (0.00 sec)

6) slave  ****use the old query***
mysql> select password from users where id=21907;
+----------+
| password |
+----------+
| cccccc   | 
+----------+
1 row in set (0.00 sec)

Suggested fix:
Ensure cache is cleared for slave when values are updated - same as on master server.

Thank you for the bug report. Could you please provide the my.cnf for both
master/slave and the create table/insert data for the test you reported.
Thanks in advance.

master my.cnf

[mysqld]
server-id=3
log-output=FILE
log-bin=/var/log/mysql/binlog
binlog-format=row
max-binlog-size=100M
log-slow-queries=/var/log/mysql/slow-queries.log
skip-innodb
skip-bdb
safe-user-create
skip-name-resolve
skip-log-warnings
safe-show-database

set-variable = flush_time=86400
set-variable = max_connections=400
set-variable = max_connect_errors=100
set-variable = wait_timeout=60
set-variable = interactive_timeout=600
set-variable = myisam-recover=FORCE
set-variable = key_buffer_size=300M
set-variable = sort_buffer_size=500k
set-variable = read_buffer_size=10M
set-variable = table_cache=10M
set-variable = max_delayed_threads=0
set-variable = query_cache_size=500M
set-variable = thread_concurrency=4
set-variable = thread_cache_size=500
set-variable = long_query_time=120

[mysql.server]
user=mysql

[mysqld_safe]
log-error=/var/log/mysql/error.log

slave my.cf

[mysqld]
server-id=1
log-slow-queries=/var/log/mysql/slow-queries.log
skip-innodb
skip-bdb
safe-user-create
skip-name-resolve
skip-log-warnings
safe-show-database

set-variable = flush_time=86400
set-variable = max_connections=400
set-variable = max_connect_errors=100
set-variable = wait_timeout=60
set-variable = interactive_timeout=600
set-variable = myisam-recover=FORCE
set-variable = key_buffer_size=300M
set-variable = sort_buffer_size=5M
set-variable = read_buffer_size=10M
set-variable = table_cache=10M
set-variable = max_delayed_threads=0
set-variable = query_cache_size=300M
set-variable = thread_cache_size=500
set-variable = long_query_time=120

[mysql.server]
user=mysql

[mysqld_safe]
log-error=/var/log/mysql/error.log

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) unsigned NOT NULL DEFAULT '0',
  `status` char(1) NOT NULL DEFAULT 'Y',
  `disk_quota` int(7) NOT NULL DEFAULT '-1',
  `disk_usage` int(7) unsigned NOT NULL DEFAULT '0',
  `msg_usage` int(7) unsigned NOT NULL DEFAULT '0',
  `msg_quota` int(7) NOT NULL DEFAULT '-1',
  `lastrecalculate` int(11) unsigned NOT NULL DEFAULT '0',
  `user` varchar(128) NOT NULL DEFAULT '',
  `password` varchar(32) NOT NULL DEFAULT '',
  `mailpath` varchar(128) NOT NULL DEFAULT '',
  `warningemail` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx1` (`domain_id`,`user`(30))
) ENGINE=MyISAM AUTO_INCREMENT=51087 DEFAULT CHARSET=latin1;

INSERT INTO users SET domain_id='3', user='test', disk_quota='-1',
 password='aaaaaa', disk_usage='0', msg_usage='0', msg_quota='-1', mailpath='0/';

Any fixes for this yet ?
Thanks.

Thank you for the bug report. Verified as described:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

slave>CHANGE MASTER TO
    -> MASTER_HOST='localhost',
    -> MASTER_USER='slaveuser',
    -> MASTER_PASSWORD='slaveruser',
    -> MASTER_LOG_FILE='hegel.000001',
    -> MASTER_LOG_POS=381;
Query OK, 0 rows affected (0.04 sec)

slave>start slave;
Query OK, 0 rows affected (0.01 sec)

slave>show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: localhost
                Master_User: slaveuser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: hegel.000001
        Read_Master_Log_Pos: 668
             Relay_Log_File: hegel.000002
              Relay_Log_Pos: 526
      Relay_Master_Log_File: hegel.000001
           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: 668
            Relay_Log_Space: 526
            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)

slave>

master>use test;
Database changed
master>CREATE TABLE `users` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `domain_id` int(11) unsigned NOT NULL DEFAULT '0',
    ->   `status` char(1) NOT NULL DEFAULT 'Y',
    ->   `disk_quota` int(7) NOT NULL DEFAULT '-1',
    ->   `disk_usage` int(7) unsigned NOT NULL DEFAULT '0',
    ->   `msg_usage` int(7) unsigned NOT NULL DEFAULT '0',
    ->   `msg_quota` int(7) NOT NULL DEFAULT '-1',
    ->   `lastrecalculate` int(11) unsigned NOT NULL DEFAULT '0',
    ->   `user` varchar(128) NOT NULL DEFAULT '',
    ->   `password` varchar(32) NOT NULL DEFAULT '',
    ->   `mailpath` varchar(128) NOT NULL DEFAULT '',
    ->   `warningemail` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx1` (`domain_id`,`user`(30))
    -> ) ENGINE=MyISAM AUTO_INCREMENT=51087 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

master>INSERT INTO users SET domain_id='3', user='test', disk_quota='-1',
    ->  password='aaaaaa', disk_usage='0', msg_usage='0', msg_quota='-1',
    -> mailpath='0/';
Query OK, 1 row affected (0.03 sec)

master>select password from users where id=51087;
+----------+
| password |
+----------+
| aaaaaa   | 
+----------+
1 row in set (0.00 sec)

slave>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

slave>select password from users where id=51087;
+----------+
| password |
+----------+
| aaaaaa   | 
+----------+
1 row in set (0.00 sec)

master>update users set password='bbbbbb' where id=51087;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

master>select password from users where id=51087;
+----------+
| password |
+----------+
| bbbbbb   | 
+----------+
1 row in set (0.00 sec)

slave>select password from users where id=51087;
+----------+
| password |
+----------+
| aaaaaa   | 
+----------+
1 row in set (0.00 sec)

slave>select password from users where      id=51087;
+----------+
| password |
+----------+
| bbbbbb   | 
+----------+
1 row in set (0.00 sec)

slave>select password from users where id=51087;
+----------+
| password |
+----------+
| aaaaaa   | 
+----------+
1 row in set (0.00 sec)

Cannot verify. I have tested this on SUSE 10.0, Windows XP, and Win2003-64bit. The problem does not occur on these platforms. The problem may have been solved when BUG#18581 was fixed. If support cannot verify it, then they should close it as 'Can't repeat.'

Thank you for the bug report. Verifying with latest source server I was unable
to repeat.