MySQL Bugs: #4994: Optimize Table with no_write_to

Bug #4994	Optimize Table with no_write_to_binlog keyword
Submitted:	11 Aug 2004 16:27	Modified:	11 Aug 2004 22:01
Reporter:	Felipe Riccetto	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.1.3b-beta-log	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When I execute "OPTIMIZE NO_WRITE_TO_BINLOG TABLES <all_tables>" on master, this statement is also writen to binlog and replicated to slave.

How to repeat:
Execute "OPTIMIZE NO_WRITE_TO_BINLOG TABLES <all_tables>" on master and see the binlog file and relay-log on the slave.

Can't repeat with 4.1.3 on Windows XP and latest 4.1 source tree on Linux:
MASTER> reset master; show binlog events; optimize no_write_to_binlog table uuu,v; show binlog events;
Query OK, 0 rows affected (0.01 sec)

+---------------------+-----+------------+-----------+--------------+--------------------------------------------------------------+
| Log_name            | Pos | Event_type | Server_id | Orig_log_pos | Info                                                         |
+---------------------+-----+------------+-----------+--------------+--------------------------------------------------------------+
| gbichot2-bin.000001 |   4 | Start      |         1 |            4 | Server ver: 4.1.4-beta-valgrind-max-debug-log, Binlog ver: 3 |
+---------------------+-----+------------+-----------+--------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

+----------+----------+----------+-----------------------------+
| Table    | Op       | Msg_type | Msg_text                    |
+----------+----------+----------+-----------------------------+
| test.uuu | optimize | status   | Table is already up to date |
| test.v   | optimize | status   | Table is already up to date |
+----------+----------+----------+-----------------------------+
2 rows in set (0.00 sec)

+---------------------+-----+------------+-----------+--------------+--------------------------------------------------------------+
| Log_name            | Pos | Event_type | Server_id | Orig_log_pos | Info                                                         |
+---------------------+-----+------------+-----------+--------------+--------------------------------------------------------------+
| gbichot2-bin.000001 |   4 | Start      |         1 |            4 | Server ver: 4.1.4-beta-valgrind-max-debug-log, Binlog ver: 3 |
+---------------------+-----+------------+-----------+--------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

The OPTIMIZE TABLE did not get into the binary log.
Could you please provide a repeatable test case?
You wrote "<all_tables>": could you give an example of "<all_tables>" ?

Thanks.

I executed this test case on Windows XP Pro, Windows 98 SE and Windows 2000 Server:

create database test;
use test;

CREATE TABLE transacao (
  tra_ID int(11) NOT NULL,
  tra_NSU int(11) NOT NULL,
  tra_DataHoraCad datetime NOT NULL,
  tra_NomeUsuCad varchar(10) DEFAULT NULL,
  tra_DataHoraAlt datetime DEFAULT NULL,
  tra_NomeUsuAlt varchar(10) DEFAULT NULL,
  PRIMARY KEY (tra_ID),
  UNIQUE KEY tra_idx_NSU (tra_NSU)
) TYPE=InnoDB;

CREATE TABLE `sequencia` (
  `seq_ID` varchar(7) NOT NULL default '',
  `seq_Contador` int(11) default NULL,
  PRIMARY KEY  (`seq_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `sequencia` VALUES ('CONFNOT',129090),('DESC',77),('ENDEREC',2),('NOTA',201734),('NSU',6),('PREFERE',105),('TELEFON',4),('TRA_ID',6);

optimize no_write_to_binlog tables sequencia,transacao;

show binlog events;

------- and I execute: mysqlbinlog server-bin.000001 ---------------------

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#040811 21:20:16 server id 1  log_pos 4 	Start: binlog v 3, server v 4.1.3b-beta-nt-max-log created 040811 21:20:16 at startup
# at 79
#040811 21:20:37 server id 1  log_pos 79 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1092270037;
create database test;
# at 130
#040811 21:20:37 server id 1  log_pos 130 	Query	thread_id=2	exec_time=0	error_code=0
use test;
SET TIMESTAMP=1092270037;
CREATE TABLE transacao (
  tra_ID int(11) NOT NULL,
  tra_NSU int(11) NOT NULL,
  tra_DataHoraCad datetime NOT NULL,
  tra_NomeUsuCad varchar(10) DEFAULT NULL,
  tra_DataHoraAlt datetime DEFAULT NULL,
  tra_NomeUsuAlt varchar(10) DEFAULT NULL,
  PRIMARY KEY (tra_ID),
  UNIQUE KEY tra_idx_NSU (tra_NSU)
) TYPE=InnoDB;
# at 481
#040811 21:20:38 server id 1  log_pos 481 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1092270038;
CREATE TABLE `sequencia` (
  `seq_ID` varchar(7) NOT NULL default '',
  `seq_Contador` int(11) default NULL,
  PRIMARY KEY  (`seq_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# at 689
#040811 21:20:38 server id 1  log_pos 689 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1092270038;
INSERT INTO `sequencia` VALUES ('CONFNOT',129090),('DESC',77),('ENDEREC',2),('NOTA',201734),('NSU',6),('PREFERE',105),('TELEFON',4),('TRA_ID',6);
# at 868
#040811 21:20:38 server id 1  log_pos 868 	Query	thread_id=2	exec_time=0	error_code=0
SET TIMESTAMP=1092270038;
optimize no_write_to_binlog tables sequencia,transacao;
# at 957
#040811 21:20:38 server id 1  log_pos 957 	Query	thread_id=2	exec_time=1	error_code=0
SET TIMESTAMP=1092270038;
optimize no_write_to_binlog tables sequencia,transacao;

----------------------------------------------------------------
The "optimize no_write_to_binlog" appears twice on the binlog.