0

I have a MySQL 5.6.21 slave and a MySQL 5.1.73 slave that are both replicating from the same MySQL 5.1.73 master.

I noticed that for several of the tables ( I said several because when I try to rerun the replication test, I noticed that different tables could get affected, so this is not exactly deterministic ) in the 5.6.21 slave's database has AUTO_INCREMENT values that are slightly off from that of the 5.1.73 slave. However, both tables have the same exact number of rows and there are no row differences ( verified using mysqldbcompare ).

For example, when I do SHOW TABLE STATUS for the same table on both the 5.1 slave vs 5.6 slave, I would see this on 5.1:

Engine: InnoDB
    Version: 10
     Row_format: Compact
       Rows: 861
 Avg_row_length: 95
    Data_length: 81920
Max_data_length: 0
   Index_length: 98304
      Data_free: 0
 Auto_increment: 7290
    Create_time: 2014-10-29 18:45:44
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
    Comment: 
1 row in set (0.00 sec)

and then, 5.6

     Engine: InnoDB
    Version: 10
     Row_format: Compact
       Rows: 981
 Avg_row_length: 83
    Data_length: 81920
Max_data_length: 0
   Index_length: 98304
      Data_free: 0
 Auto_increment: 7289
    Create_time: 2014-10-29 18:45:51
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
    Comment: 

Notice that 5.1 has an AUTO_INCREMENT value of 7290 while 5.6 has a value of 7289

Is there something different in how 5.6 replicates ( in comparison to 5.1 ) that causes the AUTO_INCREMENT values to be different.

Thanks

  • Curious... what is the master's binlog_format set to? In the table above, what's the largest id actually present, and are the tables InnoDB? – Michael - sqlbot Nov 4 '14 at 1:17
  • Hi Michael, yes, every table in the DB is InnoDB, and the binlog_format on all masters and slaves are always STATEMENT. And the largest id for the table above is actually 7288 for both tables. And there are no row differences for both tables – user1805458 Nov 4 '14 at 16:16
  • SELECT @@auto_increment_increment, @@auto_increment_offset; on all servers, and compare? – Michael - sqlbot Nov 4 '14 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.