Bug #69135 mysql.slave_master_info is not updated
Submitted: 3 May 2013 13:06 Modified: 24 Mar 2014 12:48
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.11 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Triage: Needs Triage: D1 (Critical)

[3 May 2013 13:06] Giuseppe Maxia
Description:
The table mysql.slave_master_info should get the same information of the file master.info.

Instead, binary log and position remains the same , For example:

QA.R4 tungsten@qa[rsandbox_5_6_11]$ ./s1 -e 'select * from mysql.slave_master_info\G'
*************************** 1. row ***************************
       Number_of_lines: 23
       Master_log_name: mysql-bin.000002
        Master_log_pos: 151       # <======================
                  Host: 127.0.0.1
             User_name: rsandbox
         User_password: rsandbox
                  Port: 18876
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca:
            Ssl_capath:
              Ssl_cert:
            Ssl_cipher:
               Ssl_key:
Ssl_verify_server_cert: 0
             Heartbeat: 1800
                  Bind:
    Ignored_server_ids: 0
                  Uuid: 5e30d648-b3f1-11e2-92e7-6c626da07446
           Retry_count: 86400
               Ssl_crl:
           Ssl_crlpath:
 Enabled_auto_position: 0
QA.R4 tungsten@qa[rsandbox_5_6_11]$ ./s1 -e 'show slave status\G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 18876
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 807           # <===================
               Relay_Log_File: mysql_sandbox18877-relay-bin.000005
                Relay_Log_Pos: 1017
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 807
              Relay_Log_Space: 1397
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 5e30d648-b3f1-11e2-92e7-6c626da07446
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 5e30d648-b3f1-11e2-92e7-6c626da07446:1-3
            Executed_Gtid_Set: 5e30d648-b3f1-11e2-92e7-6c626da07446:1-3
                Auto_Position: 0

How to repeat:
1) install MySQL Sandbox 3.0.35
2) run this script

export SANDBOX_HOME=$HOME/sandboxes
export MYSQL5_6_11=mysql-5.6.11-linux-glibc2.5-x86_64
export TARBALL=$HOME/downloads/$MYSQL5_6_11.tar.gz

if [ ! -f $TARBALL ]
then
    echo "$TARBALL not found"
    exit 1
fi

export SANDBOX_BINARY=$HOME/opt/mysql
if [ ! -d $SANDBOX_BINARY ]
then
    mkdir -p $SANDBOX_BINARY
fi
if [ ! -d $SANDBOX_BINARY/5.6.11 ]
then
    cd $SANDBOX_BINARY
    tar -xzf $TARBALL
    if [ ! -d $MYSQL5_6_11 ]
    then
        echo "error expanding $TARBALL"
        exit 1
    fi
    mv $MYSQL5_6_11 5.6.11
fi

make_replication_sandbox --how_many_slaves=1 5.6.11

cd $SANDBOX_HOME/rsandbox_5_6_11

echo "show that binlog positions are advancing in master.info"
echo "before updating"
cat node1/data/master.info
./s1 -e 'show slave status\G' | grep -i 'master\|running'
./m -e 'create table test.t1 (i int)'
./m -e 'set autocommit=0; begin; insert into test.t1 values (1); commit'

echo ""
echo "after updating"
cat node1/data/master.info
./s1 -e 'show slave status\G' | grep -i 'master\|running'

echo ""

echo "**** Now enabling gtid and crash safe tables"
./enable_gtid

echo "before updating"
./s1 -e 'select * from mysql.slave_master_info\G'
./s1 -e 'show slave status\G' | grep -i 'master\|running'
./m -e 'create table test.t2 (i int)'
./m -e 'set autocommit=0; begin; insert into test.t2 values (1); commit'

echo ""
echo "after updating"
./s1 -e 'select * from mysql.slave_master_info\G'
./s1 -e 'show slave status\G' | grep -i 'master\|running'
[6 May 2013 11:50] Umesh Shastry
Hello Giuseppe,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[6 May 2013 12:07] Giuseppe Maxia
Why is this bug classified as 'D2'? 

Crash safe tables are one of the major features of MySQL 5.6, and if the table is not updated it means that replication is running blind.
[30 May 2013 14:46] Matthew Lord
Isn't this expected (at least according to the documentation), if you don't set sync_master_info=1 ?

https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#sysvar_sync_master_...
[30 May 2013 15:36] Giuseppe Maxia
Matt,
It is definitely not expected.
I din't even suspect that synch_master_info existed.
What good is the safe-crash-slave table if by default updates its status every 10,000 events?
No. This is not expected.
[30 May 2013 15:48] Matthew Lord
Hi Giuseppe!

I noted "at least according to the documentation" because while this isn't technically a bug, but rather the documented and intentional behavior, I do agree that it's probably not expected from a logical user perspective. 

Perhaps the "fix" for this bug is simply to make sync_master_info=1 the default when master_info_repository = TABLE is set.

What do you think about this potential solution? We don't want to have that generally default to 1 though as it has a noticeable performance impact on the master.

Just FYI, this option has existed since 5.5. 
  https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#sysvar_sync_master_...

I'm not sure that we want to change the default value further. We already changed the default value from 0 to 10,000 in 5.6. But I'm interested to get your thoughts.

To be truly crash-safe, you should also enable CRC checks to ensure that the binary log isn't "sync'd" but corrupted and thus not worth a whole lot in practice:
  https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#option_mysqld_...
  https://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_slave...

Perhaps we could add a new option called --crash-safe-replication that sets up all of the related options (there are quite a few related ones, actually).

Best Regards,

Matt
[30 May 2013 16:17] Giuseppe Maxia
Matt,
Thanks for your suggestions.
I suspect that the reason synch_master_info=1 is not the default is because it may have performance impact.
As you know, I work for a company that has implemented this feature already many years ago, and the problem was solved by making the crash-safe tables enabled by default, coupled with block commit to sustain performance. 

With the current implementation, replication in MySQL 5.6 is not crash safe.  If I had my way, I'd enable GTID and crash-safe tables by default, so that all the integration bugs that are still lurking in the background would be found and eventually fixed.

If this bug can be fixed with a set of options, then the documentation should mention it explicitly in the user guide. Very few people go looking for every server variable in the manual, especially if they don't expect the new features  highlighted in the "what's new" section not to be enabled. What could make life easier is a chapter in the manual that mentions best practices for replication with crash-safe tables, possibly explaining what performance degradation to expect with various values of synch_master_info.
[31 May 2013 14:38] Luis Soares
Giuseppe, Matt, Allow me to add a comment.

For crash-safeness you should have:

  - slave_relay_log_info=TABLE
  - relay_log_recovery=ON

The first one ensures that positions are consistent with the
data. The second one, ensures that the relay log is discarded if
corrupted during the crash and that coordinates in master info
are recovered from the relay log info (thus lifting the
requirement to sync master info on every *event*, which would
likely impact performance a bit).

Also, let me point you to the documentation bug: BUG#67246 , 
in particular, Alfranio's comment:
[26 Oct 2012 21:18] Alfranio Tavares Correia Junior 

Perhaps we can make things even more clear in the manual...
[4 Nov 2013 7:34] Shane Bester
Setting verified as a docs bug.
[4 Nov 2013 15:06] Simon Mudd
Luis,

Why do you _need_ relay_log_recovery=ON, as at least in 5.6 by default you have binlog checksums so you can determine if the binlogs are broken or not.   There are other issues which I have raised a support ticket about which may mean that one really does not want to clean out relay logs on server startup as you may lose a lot of history which then has to be downloaded and used again. So off-topic for this bug but I'm not fullly convinced that relay_log_recovery=ON is such a good option as it currently works.
[4 Nov 2013 15:10] Simon Mudd
Correction from previous comment: you can determine if the relay logs are corrupt or not.
[24 Mar 2014 12:48] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.