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

[31 Jan 2003 22:53] Guilhem Bichot
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.
[31 Jan 2003 23:50] MySQL Developer
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" ;) ).
[21 Feb 2003 6:18] Guilhem Bichot
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.
[6 Mar 2003 8:49] Guilhem Bichot
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