| 1 |
call mtr.add_suppression("Can't create database 'performance_schema'; database exists"); |
| 2 |
# restart:--upgrade=FORCE --log-error=MYSQLD_LOG |
| 3 |
call mtr.add_suppression("Can't create database 'performance_schema'; database exists"); |
| 4 |
# restart:--upgrade=FORCE --log-error=MYSQLD_LOG |
| 5 |
First initialize the binlog |
| 6 |
reset master; |
| 7 |
DROP TABLE IF EXISTS t1; |
| 8 |
Test that binlog purging doesn't use table lock on ndb_binlog_index |
| 9 |
Put some real stuff in the Binlog. |
| 10 |
create table test.t1 ( |
| 11 |
pk integer primary key auto_increment, |
| 12 |
a varchar(1000) |
| 13 |
) engine=ndb; |
| 14 |
insert into test.t1 values (NULL, repeat('BJC', 300)); |
| 15 |
insert into test.t1 select NULL, a from test.t1; |
| 16 |
insert into test.t1 select NULL, a from test.t1; |
| 17 |
insert into test.t1 select NULL, a from test.t1; |
| 18 |
insert into test.t1 select NULL, a from test.t1; |
| 19 |
insert into test.t1 select NULL, a from test.t1; |
| 20 |
insert into test.t1 select NULL, a from test.t1; |
| 21 |
insert into test.t1 select NULL, a from test.t1; |
| 22 |
insert into test.t1 select NULL, a from test.t1; |
| 23 |
insert into test.t1 select NULL, a from test.t1; |
| 24 |
insert into test.t1 select NULL, a from test.t1; |
| 25 |
insert into test.t1 select NULL, a from test.t1; |
| 26 |
select trim(trailing "binlog.000001" from File) |
| 27 |
from mysql.ndb_binlog_index |
| 28 |
where epoch in (select max(epoch) from mysql.ndb_binlog_index) |
| 29 |
into @file_prefix; |
| 30 |
select concat(@file_prefix, "binlog.000001") into @file1; |
| 31 |
Have at least 100 epoch rows |
| 32 |
select count(*)/100 > 0 from mysql.ndb_binlog_index where File=@file1; |
| 33 |
count(*)/100 > 0 |
| 34 |
1 |
| 35 |
Flush log to get onto the next file... |
| 36 |
FLUSH LOGS; |
| 37 |
select concat(@file_prefix, "binlog.000002") into @file2; |
| 38 |
create table test.files(file1 varchar(255), file2 varchar(255)); |
| 39 |
insert into test.files values(@file1, @file2); |
| 40 |
Pick one row from ndb_binlog_index |
| 41 |
CREATE TEMPORARY TABLE my_ndb_binlog_index ( |
| 42 |
epoch BIGINT UNSIGNED NOT NULL, |
| 43 |
orig_server_id INT UNSIGNED NOT NULL, |
| 44 |
orig_epoch BIGINT UNSIGNED NOT NULL |
| 45 |
); |
| 46 |
insert into my_ndb_binlog_index |
| 47 |
select epoch, orig_server_id, orig_epoch |
| 48 |
from mysql.ndb_binlog_index |
| 49 |
where File=@file1 limit 1; |
| 50 |
select epoch from my_ndb_binlog_index into @epoch; |
| 51 |
select orig_server_id from my_ndb_binlog_index into @orig_server_id; |
| 52 |
select orig_epoch from my_ndb_binlog_index into @orig_epoch; |
| 53 |
Lock one row to block purge |
| 54 |
start transaction; |
| 55 |
select count(1) from mysql.ndb_binlog_index |
| 56 |
where epoch = @epoch and |
| 57 |
orig_server_id = @orig_server_id and |
| 58 |
orig_epoch = @orig_epoch and |
| 59 |
File=@file1 for update; |
| 60 |
count(1) |
| 61 |
1 |
| 62 |
Check that there are no rows in the next file |
| 63 |
select count(*)=0 from mysql.ndb_binlog_index where File=@file2; |
| 64 |
count(*)=0 |
| 65 |
1 |
| 66 |
Start the purge that will get stuck on one row |
| 67 |
PURGE BINARY LOGS TO 'binlog.000002'; |
| 68 |
Wait 10 sec for purge to run into the row lock |
| 69 |
Now we should still have some rows in the first file (since the purge is stuck) |
| 70 |
select count(*)>0 from mysql.ndb_binlog_index where File=@file1; |
| 71 |
count(*)>0 |
| 72 |
1 |
| 73 |
Insert some more rows |
| 74 |
insert into test.t1 select NULL, a from test.t1; |
| 75 |
Wait for rows to be committed to binlog |
| 76 |
Now we see rows in the next file (which means there is no table lock) |
| 77 |
select count(*)>0 from mysql.ndb_binlog_index where File=@file2; |
| 78 |
count(*)>0 |
| 79 |
1 |
| 80 |
Release the purge |
| 81 |
commit; |
| 82 |
Purge done |
| 83 |
Now we should have no rows in the first file |
| 84 |
select count(*)=0 from mysql.ndb_binlog_index where File=@file1 into @result; |
| 85 |
select @result; |
| 86 |
@result |
| 87 |
1 |
| 88 |
Now we still should see rows in the next file |
| 89 |
select count(*)>0 from mysql.ndb_binlog_index where File=@file2; |
| 90 |
count(*)>0 |
| 91 |
1 |
| 92 |
cleanup |
| 93 |
drop table test.t1, test.files; |
| 94 |
Done |