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

[13 Mar 2005 1:08] Timothy Smith
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.
[6 Apr 2005 15:06] Heikki Tuuri
Peter,

can you look at this?

Regards,

Heikki
[7 Apr 2005 12:46] Heikki Tuuri
Guilhem,

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

Regards,

Heikki
[7 Apr 2005 12:56] Guilhem Bichot
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.
[11 Apr 2005 15:56] Guilhem Bichot
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
[20 Apr 2005 1:30] Paul Dubois
Noted in 4.1.11, 5.0.5 changelogs.