Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
-
None
-
10.0.22, 10.0.24
Description
If ALTER TABLE is interrupted, it can happen that it performs the operation, e.g. adds a column, but is not written to the binary log, which of course causes further discrepancy.
The MTR test below is for 10.1 only, because it uses max_statement_time, but the problem is not specific to it, same happens with KILL QUERY (see the RQG test which can be used for 10.0 as well).
The MTR test highly depends on the machine timing, so it might need some tuning, e.g. more or less data in the table, longer or shorter max_statement_time, etc.
|
MTR test case |
--source include/have_innodb.inc
|
--source include/have_binlog_format_statement.inc
|
|
|
--disable_query_log
|
|
|
set sql_log_bin = 0; |
|
|
create table t1 (pk int auto_increment primary key, i int, c varchar(8)) engine=InnoDB; |
insert into t1 (i,c) values (1,'foo'),(2,'bar'),(3,'qux'),(4,'foobar'); |
insert into t1 select null, i, c from t1; |
insert into t1 select null, i, c from t1; |
insert into t1 select null, i, c from t1; |
insert into t1 select null, i, c from t1; |
insert into t1 select null, i, c from t1; |
insert into t1 select null, i, c from t1; |
insert into t1 select null, i, c from t1; |
insert into t1 select null, i, c from t1; |
insert into t1 select null, i, c from t1; |
insert into t1 select null, i, c from t1; |
|
|
set sql_log_bin = 1; |
|
|
let $col_count = 3;
|
let $master_pos = query_get_value(show master status, Position, 1);
|
|
|
let $timeout = 3;
|
|
|
while ($timeout)
|
{
|
eval set max_statement_time = $timeout; |
let $run = 10;
|
while ($run)
|
{
|
let $colname = c$timeout-$run;
|
echo # Adding $colname;
|
--error 0,ER_STATEMENT_TIMEOUT,ER_QUERY_INTERRUPTED |
eval alter table t1 add `$colname` int; |
let $new_col_count = `select count(*) from information_schema.columns where table_name = 't1'`; |
echo # Error code $mysql_errno, new column count $new_col_count; |
if ($new_col_count > $col_count) { |
let $new_master_pos = query_get_value(show master status, Position, 1);
|
if ($new_master_pos == $master_pos) |
{
|
show binlog events;
|
show create table t1; |
echo "Column $colname was added, but ALTER statement was not written to the binlog"; |
die "Binlog problem"; |
}
|
let $master_pos = $new_master_pos;
|
}
|
let $col_count = $new_col_count;
|
dec $run; |
}
|
dec $timeout; |
}
|
|
|
drop table t1; |
|
Example of the failure |
...
|
# Adding c2-9
|
# Error code 0, new column count 15 |
# Adding c2-8
|
# Error code 1968, new column count 16 |
Log_name Pos Event_type Server_id End_log_pos Info
|
master-bin.000001 4 Format_desc 1 248 Server ver: 10.1.5-MariaDB-wsrep-debug-log, Binlog ver: 4
|
...
|
master-bin.000001 1745 Gtid 1 1783 GTID 0-1-12
|
master-bin.000001 1783 Query 1 1875 use `test`; alter table t1 add `c2-9` int |
Table Create Table |
t1 CREATE TABLE `t1` ( |
`pk` int(11) NOT NULL AUTO_INCREMENT, |
`i` int(11) DEFAULT NULL, |
`c` varchar(8) DEFAULT NULL, |
`c3-10` int(11) DEFAULT NULL, |
`c3-9` int(11) DEFAULT NULL, |
`c3-8` int(11) DEFAULT NULL, |
`c3-7` int(11) DEFAULT NULL, |
`c3-6` int(11) DEFAULT NULL, |
`c3-5` int(11) DEFAULT NULL, |
`c3-4` int(11) DEFAULT NULL, |
`c3-3` int(11) DEFAULT NULL, |
`c3-2` int(11) DEFAULT NULL, |
`c3-1` int(11) DEFAULT NULL, |
`c2-10` int(11) DEFAULT NULL, |
`c2-9` int(11) DEFAULT NULL, |
`c2-8` int(11) DEFAULT NULL, |
PRIMARY KEY (`pk`) |
) ENGINE=InnoDB AUTO_INCREMENT=8179 DEFAULT CHARSET=latin1 |
"Column c2-8 was added, but ALTER statement was not written to the binlog"
|
For running the RQG test, clone lp:~elenst/randgen/mariadb-patches (the main randgen tree might miss some changes or fixes in the required components).
|
RQG grammar alter.yy |
query:
|
ALTER TABLE _table ADD _letter[invariant] INT ; ALTER TABLE _table DROP _letter[invariant] ;
|
|
RQG command line |
perl ./runall-new.pl --threads=1 --duration=200 --queries=100M --reporters=QueryTimeout,BinlogConsistency --grammar=alter.yy --engine=InnoDB --mysqld=--log-bin --rows=100,100,100,100,100,100,100,100,1000 --querytimeout=1 --basedir=<basedir> --vardir=<vardir>
|
The failure can come in two flavors: either it complains about binlog replaying error due to an unknown column, or reports multiple differences like
-INSERT INTO `BB` VALUES (100,6,NULL,'2002-04-16','2002-04-16','00:43:36','00:43:36','2002-05-14 10:52:52','2002-05-14 10:52:52',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
|
+INSERT INTO `BB` VALUES (100,6,NULL,'2002-04-16','2002-04-16','00:43:36','00:43:36','2002-05-14 10:52:52','2002-05-14 10:52:52',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
|
where the only difference is the number of columns in the table.
If neither happens, try to increase the number of threads on the command line.