MySQL Bugs: #52: 4.0 If only transactions are run on the master then the slave shows wrong info

Bug #52	4.0 If only transactions are run on the master then the slave shows wrong info
Submitted:	31 Jan 2003 22:53	Modified:	6 Mar 2003 8:49
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	4.0, 4.1	OS:	Any (all)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
If only transactions are run on the master then the slave shows wrong Exec_master_log_pos in SHOW SLAVE STATUS, which can be confusing for debugging (like if the slave was late while it's not).

How to repeat:
Run many transactions on the master (only transactions, and no DDL). Once they are finished and replicated, do SHOW SLAVE STATUS.

Suggested fix:
I'm working on it.

As the slave has wrong info, MASTER_POS_WAIT could wait forever, SHOW SLAVE STATUS shows stupid things etc.
This is a master's bug. I've just fixed it, will send a patch after further testing and after debugging other things (will make a patch pack... a "service pack" ;) ).

More precise "How to repeat" :

SLAVE> start slave;
Query OK, 0 rows affected (0.00 sec)

SLAVE> show slave status;
+-------------+-------------+-------------+---------------+-----------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+
| Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | 
Relay_Log_File        | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | 
Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | 
Relay_log_space |
+-------------+-------------+-------------+---------------+-----------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+
| localhost   | root        | 3306        | 60            | gbichot-bin.001 | 79                  | 
gbichot-relay-bin.002 | 121           | gbichot-bin.001       | Yes              | Yes               |       
          |                     | 0          |            | 0            | 79                  | 125         
    |
+-------------+-------------+-------------+---------------+-----------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+
1 row in set (0.00 sec)

(note exec_master_log_pos)

MASTER> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_do_db | Binlog_ignore_db |
+-----------------+----------+--------------+------------------+
| gbichot-bin.001 | 79       |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MASTER> create table test.c(a int) type=innodb;
Query OK, 0 rows affected (0.05 sec)

MASTER> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_do_db | Binlog_ignore_db |
+-----------------+----------+--------------+------------------+
| gbichot-bin.001 | 148      |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MASTER> begin;
Query OK, 0 rows affected (0.00 sec)

MASTER> insert into test.c values(1);
Query OK, 1 row affected (0.01 sec)

MASTER> commit;
Query OK, 0 rows affected (0.00 sec)

MASTER> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_do_db | Binlog_ignore_db |
+-----------------+----------+--------------+------------------+
| gbichot-bin.001 | 280      |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

SLAVE> select * from test.c;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

SLAVE> show slave status;
+-------------+-------------+-------------+---------------+-----------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+
| Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | 
Relay_Log_File        | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | 
Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | 
Relay_log_space |
+-------------+-------------+-------------+---------------+-----------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+
| localhost   | root        | 3306        | 60            | gbichot-bin.001 | 280                 | 
gbichot-relay-bin.002 | 322           | gbichot-bin.001       | Yes              | Yes               |       
          |                     | 0          |            | 0            | 148                 | 326         
    |
+-------------+-------------+-------------+---------------+-----------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+------------+------------+--------------+---------------------+-----------------+
1 row in set (0.00 sec)

see how exec_master_log_pos is the position of "CREATE TABLE",
whereas "INSERT" has been processed too (as c contains 1).
exec_master_log_pos should be 280.

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html