MySQL Bugs: #9149: OPTIMIZE TABLE statement on InnoDB table is logged twice in the binary log

Bug #9149	OPTIMIZE TABLE statement on InnoDB table is logged twice in the binary log
Submitted:	13 Mar 2005 1:08	Modified:	20 Apr 2005 1:30
Reporter:	Timothy Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.1.10	OS:	Any (any)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
An OPTIMIZE TABLE command against an InnoDB table is written twice to the binary log.

See:

# at 2501275
#050313 13:49:00 server id 33410  log_pos 2501275       Query   thread_id=7     exec_time=32    error_code=0
SET TIMESTAMP=1110746940;
optimize table innoa;
# at 2501329
#050313 13:49:00 server id 33410  log_pos 2501329       Query   thread_id=7     exec_time=32    error_code=0
SET TIMESTAMP=1110746940;
optimize table innoa;

How to repeat:

Using the latest BK code as of early 2005-03-13 (customer was using 4.1.10 release), on FreeBSD (customer using Linux):

create table foo (x int) engine=innodb;
insert into foo values (1);
optimize table foo;

Then, tail the output of 'mysqlbinlog' for the latest binary log file.

It does not happen with MyISAM tables, only InnoDB.

Suggested fix:
None at this time.

Peter,

can you look at this?

Regards,

Heikki

Guilhem,

can you please look at this? I recall Peter Z implemented this 2 or 3 years ago.

Regards,

Heikki

Hi Heikki,
no, can't work on it soon. The reason could be that OPTIMIZE TABLE is mapped to ALTER TABLE (done by me last year), and so I guess there is one binlogging done by ALTER and the other done by the general OPTIMIZE framework.

Docs team, could you please mention in changelog that this is now fixed in 4.1.11 and 5.0.5 (I'm not saying 5.0.4, this is not a critical bugfix, I didn't make it into 5.0.4).
ChangeSet@1.2165.1.9, 2005-04-11 16:46:03+02:00, gbichot@quadita2.mysql.com

Noted in 4.1.11, 5.0.5 changelogs.