MySQL Bugs: #41941: Failed OPTIMIZE TABLE is logged to binary log for MERGE engine

Bug #41941	Failed OPTIMIZE TABLE is logged to binary log for MERGE engine
Submitted:	8 Jan 2009 3:23	Modified:	28 Jul 2009 11:43
Reporter:	Chris Goffinet	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.30/5.0/6.0/4.1	OS:	Any
Assigned to:	Daogang Qu	CPU Architecture:	Any

Description:
Looks like this bug is back:

http://bugs.mysql.com/bug.php?id=5551

This occurs when you have a merge table of myisam. The optimize fails correctly, but it ends up getting logged to binary log even though it failed.

How to repeat:
create table t1(id int) engine=myisam;
create table t_merge (id int) union(t1) INSERT_METHOD=LAST engine=merge;
optimize table t_merge;

Thank you for the bug report. 5.0/6.0/4.1 are affected too:

#090108  9:30:31 server id 1  log_pos 308       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1231417831;
optimize table t_merge;

C:\temp\mysql-4.1.22-win32>

Rassigning to the replication team as discussed with Andrei.

Preliminary analyze looks like so:

MERGE engine cannot do OPTIMIZE. It returns HA_ADMIN_NOT_IMPLEMENTED.
mysql_admin_table() does not consider this error as fatal.
It returns OK and the operation is logged.

Somehow we must avoid logging in this case.

The replication in the slave works well when we create a merge table of myisam and optimize it. Is it worth to prevent the optimization from writing into binary log? 

The point is supported by the following test script and result:

Test script:
#
# Bug #41941
# This test verifies if the replication in the slave stops 
# when we create a merge table of myisam and optimize it.
#
-- source include/master-slave.inc

connection master;
--echo #on master
create table t1(id int) engine=myisam;
insert into t1 values (3);

create table t_merge (id int) union(t1) INSERT_METHOD=LAST engine=merge;
optimize table t_merge;

insert into t_merge values (12);
insert into t_merge values (11);
insert into t1 values (4);
select * from t_merge;

sync_slave_with_master;
connection slave;
--echo #on slave
select * from t_merge;

connection master;
--echo #on master
DROP TABLE t1;
DROP TABLE t_merge;
sync_slave_with_master;

Test result:
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
#on master
create table t1(id int) engine=myisam;
insert into t1 values (3);
create table t_merge (id int) union(t1) INSERT_METHOD=LAST engine=merge;
optimize table t_merge;
Table   Op      Msg_type        Msg_text
test.t_merge    optimize        note    The storage engine for the table doesn't support optimize
insert into t_merge values (12);
insert into t_merge values (11);
insert into t1 values (4);
select * from t_merge;
id
3
12
11
4
#on slave
select * from t_merge;
id
3
12
11
4
#on master
DROP TABLE t1;
DROP TABLE t_merge;

Hi Chris,
For Bug #41941, why you want to stop logging the optimization to binary log?
which solution do you like in the followings?
Solution 1:
Let things as they are, set its status to 'Not a Bug'.
Because the optimize won't fail, it just reports that it is not implemented.
The replication in the slave works well even if the optimization has been logged to binary log.  
The main argument in favor of keeping the current behavior is that it
would be possible to have different table definitions on the master 
and slaves and although the master has "merge" table the command 
would be logged to the binary log and executed on the slaves.
The "merge" table behaves as the "blackhole", "archive", "memory". So
changing the behavior for "merge" would oblige us to change the 
behavior for all the other engines that do not support the optimize 
command. Besides, if we don't want to log to the binary log we
could use the following command:

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] 

Based on these facts, it seems that BUG#41941 is not a bug. We need just to
update the documentation to make clear what is the behavior when the
engine does not support the optimize command.

Solution 2:
We don't log the optimization to binary log at all and an error will be
returned to indicate a failure.

The title of the bug is misleading as the command does not fail. Please
rewrite it. Correct me if I'm wrong. Thanks!

The replication in the slave works well and the optimization reports that it is not implemented when we create a table with csv or ndb engine and optimize it. It's the same behavior with the MERGE engine.
The same behavior happened when we create a table with example engine base on row-base or mixed binlog format and optimize it.

The replication in the slave works well, but the optimization reports the following error and note when we ceate a table with example engine base on statement binlog format and optimize it:

error: Binary logging not possible. Message: Statement-based format required for this statement, but not allowed by this combination of engines;

note: The storage engine for the table doesn't support optimize;

I think it should not report the error, because the optimization has been logged to binary log really. So I think I should report a new bug for it.

The above points are supported by the following appended tests:

1. NDB engine test:
#
# Bug #41941
# This test verifies if the replication in the slave works well
# and the optimization reports that it is not implemented 
# when we create a table with ndb engine and optimize it.
#
--source include/have_ndb.inc
--source include/master-slave.inc

connection master;
--echo #on master
create table t1(id int) engine=ndb;
insert into t1 values (3);

optimize table t1;

insert into t1 values (4);
select * from t1;

sync_slave_with_master;
connection slave;
--echo #on slave
select * from t1;

connection master;
--echo #on master
DROP TABLE t1;
sync_slave_with_master;

Test result:
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
#on master
create table t1(id int) engine=ndb;
insert into t1 values (3);
optimize table t1;
Table   Op      Msg_type        Msg_text
test.t1 optimize        note    The storage engine for the table doesn't support optimize
insert into t1 values (4);
select * from t1;
id
3
4
#on slave
select * from t1;
id
3
4
#on master
DROP TABLE t1;

2. CSV engine test:
# Bug #41941
# This test verifies if the replication in the slave works well
# and the optimization reports that it is not implemented 
# when we create a table with csv engine and optimize it.
#

--source include/have_csv.inc

--source include/master-slave.inc

connection master;
--echo #on master
create table t1(id int not null) engine=csv;
insert into t1 values (3);

optimize table t1;

insert into t1 values (4);
select * from t1;

sync_slave_with_master;
connection slave;
--echo #on slave
select * from t1;

connection master;
--echo #on master
DROP TABLE t1;
sync_slave_with_master;

Test result:

stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
#on master
create table t1(id int not null) engine=csv;
insert into t1 values (3);
optimize table t1;
Table   Op      Msg_type        Msg_text
test.t1 optimize        note    The storage engine for the table doesn't support optimize
insert into t1 values (4);
select * from t1;
id
3
4
#on slave
select * from t1;
id
3
4
#on master
DROP TABLE t1;

--source include/have_example_plugin.inc

# Prepare environment

SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='EXAMPLE';
INSTALL PLUGIN example SONAME 'ha_example.so';
SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='EXAMPLE';

# Verify command optimize

CREATE TABLE t1(id int) engine=example;
OPTIMIZE TABLE t1;

# Clean up

DROP TABLE t1;

SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='EXAMPLE';
UNINSTALL PLUGIN example;
SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='EXAMPLE';

SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='EXAMPLE';
ENGINE  SUPPORT COMMENT TRANSACTIONS    XA      SAVEPOINTS
INSTALL PLUGIN example SONAME 'ha_example.so';
SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='EXAMPLE';
ENGINE  SUPPORT COMMENT TRANSACTIONS    XA      SAVEPOINTS
EXAMPLE YES     Example storage engine  NO      NO      NO
CREATE TABLE t1(id int) engine=example;
OPTIMIZE TABLE t1;
Table   Op      Msg_type        Msg_text
test.t1 optimize        note    The storage engine for the table doesn't support optimize
DROP TABLE t1;
SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='EXAMPLE';
ENGINE  SUPPORT COMMENT TRANSACTIONS    XA      SAVEPOINTS
EXAMPLE YES     Example storage engine  NO      NO      NO
UNINSTALL PLUGIN example;
SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE='EXAMPLE';
ENGINE  SUPPORT COMMENT TRANSACTIONS    XA      SAVEPOINTS

Based on the analysis made by Daogang, this is not a bug.

All the engines that do not support the optimize command consistently
report a warning message and this behavior should be kept. See the
arguments that support this decision in "[27 Jul 5:38] Daogang Qu".

This isn't a server bug, and the behaviour is already well documented for OPTIMIZE TABLE - http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html -

As already noted, you can use the NO_WRITE_TO_BINLOG option.

Another alternative is to run the slave server with --skip-new or --safe-mode, which causes OPTIMIZE TABLE statements to be mapped to ALTER TABLE.