Bug #52 4.0 If only transactions are run on the master then the slave shows wrong info
Submitted: 31 Jan 2003 23:53 Modified: 6 Mar 2003 9:49
Reporter: Guilhem Bichot
Status: Closed
Category:Server: Replication Severity:S3 (Non-critical)
Version:4.0, 4.1 OS:Any (all)
Assigned to: Guilhem Bichot Target Version:

[31 Jan 2003 23: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.
[1 Feb 2003 0: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 7: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 9: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