| Bug #17620 | Replicate (Row Based) Fails when Query Cache enabled on slave. | ||
|---|---|---|---|
| Submitted: | 21 Feb 2006 15:50 | Modified: | 25 Sep 2006 17:23 |
| Reporter: | Jeff C | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S2 (Serious) |
| Version: | 5.1.9/5.1BK | OS: | FreeBSD (Freebsd 6.0) |
| Assigned to: | Mats Kindahl | CPU Architecture: | Any |
[21 Feb 2006 15:50]
Jeff C
[4 Apr 2006 4:45]
James Day
Jeff, Did you do a select before the updates on the slave, then a select after the updates which didn't see the changed data until you cleared the query cache? If yes, that would suggest that the query cache isn't being reset properly by row-based replication.
[15 Apr 2006 7:21]
Jeff C
52 days in "analyzing"... perhaps they need to reassign bugs to people that are not overworked or something. I saw another bug go from analyzing to verified within 1 day. This is a S2 bug and it's at 52 days. At least say "We did not experience that" or "this bug is verified" so that it can get the ball rolling on being fixed. I spent a few hours figuring out why this was happening, and I've seen others with the comments in the Replication forums experiencing the samething. Thanks, Jeff
[15 Apr 2006 7:35]
James Day
Jeff, Did you do a select before the updates on the slave, then a select after the updates which didn't see the changed data until you cleared the query cache? You can safely assume that everyone working on bugs is heavily overworked. That means the greatest possible help from bug reporters will really speed things up. Bugs can go from reported to patch available in less than 24 hours sometimes. It depends on the details of the bug report and the severity of the problem. There's enough detail not specified in your report to slow things down - it's why I asked the narrowing down question I asked. At present this is assigned a priority which will result in it _never_ being looked at by any programmer. If you answer my question it would probably be either noted as fixed already or be fixed in the next release. I don't normally work on bugs - this needs to move so I'm asking you the question it'll take to get it moving.
[25 Apr 2006 10:56]
Jeff C
James,
I respect your comments.
Are you saying,
1) bug report too confusing
2) you tried it, did not get the same results
3) never tried it, trying to get further information to help the bug along.
If you're at #1, I'll find the time to further report it... lay it out step by step.. ( I thought I did).
If you're at 2, I'll lay it out step by step.
If you're at 3, well.. dunno :)
At this point, I will just continually keep checking new releases until the bug is fixed and then report back... I find it amusing to count the days in analyzing (btw, who reset it?)
[17 May 2006 17:33]
Jeff C
Ok, seeing how this bug is 5 months old... I'll see If I can't get it addressed.
Bug: once a query gets executed on the slave table, all updates coming in from master block.
1) Setup master/slave for replication.
Master:
[mysqld]
log-bin=new-bin
server-id = 1
binlog-format=row
Slave:
[mysqld]
server-id = 100
replicate-wild-do-table=test.%
2) Get slave talking to master;
change master to
master_host='123.123.123.123',
master_user='repl',
master_password='HopeThisGetsFixed';
start slave;
3) Disable query cache on slave;
*SLAVE*
set global query_cache_size=0;
4) Create new table, and insert sample data on MASTER
*MASTER*
use test;
create table testing (id int unsigned primary key);
insert into testing (id) values (1),(2),(3);
5) Toggle Query Caching on Slave
*SLAVE*
set global query_cache_size=16*1024*1024;
6) Update more data on master
*MASTER*
insert into testing (id) values (4),(5),(6);
7) Look at slave data
*SLAVE*
select * from testing;
8) Insert more data on master (HERE IS WHERE BUG IS)
*MASTER*
insert into testing (id) values (7),(8),(9);
9) BUG: check slave data
select * from testing;
10) Note: all updates will be blocked on slave until you run:
*SLAVE*
set global query_cache_size=0;
select * from testing;
* now you see the data *
use test;
select * from testing;
[17 May 2006 23:53]
MySQL Verification Team
Thank you for the bug report. Verified as described on Suse Linux 10:
miguel@hegel:~/dbs/5.1> bin/mysql --defaults-file=/home/miguel/dbs/5.1/my.cnf -uroot --prompt='master>'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.11-beta-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
master>grant all on *.* to 'slaveuser'@'localhost';
Query OK, 0 rows affected (0.09 sec)
master>show master status\G
*************************** 1. row ***************************
File: new-bin.000001
Position: 348
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.02 sec)
master>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.11-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='',
-> MASTER_LOG_FILE='new-bin.000001',
-> MASTER_LOG_POS=348;
Query OK, 0 rows affected (0.03 sec)
slave>START SLAVE;
Query OK, 0 rows affected (0.00 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: new-bin.000001
Read_Master_Log_Pos: 348
Relay_Log_File: hegel-relay-bin.000002
Relay_Log_Pos: 241
Relay_Master_Log_File: new-bin.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: test.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 348
Relay_Log_Space: 241
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.01 sec)
slave>set global query_cache_size=0;
Query OK, 0 rows affected (0.02 sec)
master>use test;
Database changed
master> create table testing (id int unsigned primary key);
Query OK, 0 rows affected (0.01 sec)
master> insert into testing (id) values (1),(2),(3);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
slave>set global query_cache_size=16*1024*1024;
Query OK, 0 rows affected (0.11 sec)
master>insert into testing (id) values (4),(5),(6);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
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 * from testing;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)
master>insert into testing (id) values (7),(8),(9);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
slave>select * from testing;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)
slave>set global query_cache_size=0;
Query OK, 0 rows affected (0.10 sec)
slave> select * from testing;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+----+
9 rows in set (0.00 sec)
[6 Sep 2006 15:45]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/11480 ChangeSet@1.2257, 2006-09-06 17:45:27+02:00, mats@romeo.(none) +3 -0 BUG#17620: Row-based replication fails when query cache enabled on slave Invalidating query cache when processing rows for a statement on the slave.
[6 Sep 2006 15:48]
Mats Kindahl
Patch approved by Lars. Setting status on his behalf.
[23 Sep 2006 7:32]
Lars Thalmann
Pushed into 5.1.12
[25 Sep 2006 17:23]
Paul DuBois
Noted in 5.1.12 changelog.
