Thoughts on MySQL 5.6 new replication features

October 15, 2012

After playing a little bit with MySQL 5.6 (RC), and following closely on Giuseppe's MySQL 5.6 replication gotchas (and bugs), I was having some thoughts.

These are shared for a few reasons:

  • Maybe I didn't understand it well, and someone could correct me
  • Or I understood it well, and my input could be of service to the developers
  • Or it could be of service to the users

InnoDB tables in mysql schema

The introduction of InnoDB tables in mysql makes for crash-safe replication information: the exact replication position (master log file+pos, relay log file+pos etc.) is updated on InnoDB tables; with innodb_flush_logs_at_trx_commit=1 this means replication status is durable and consistent with server data. This is great news!

However, the introduction of InnoDB tables to the mysql schema also breaks some common usage on installation and setup of MySQL servers. You can't just drop your ib_data1 file upon dump+restore, since it also contains internal data. Giuseppe outlines the workaround for that.

I was thinking: would it be possible to have a completely different tablespace for MySQL's internal InnoDB tables? That could be a single tablespace file (who cares about file-per-table on a few internal tables). And I'm throwing an idea without being intimate with the internals: you know how it is possible to span the shared tablespace across multiple files, as in:


Would it be possible to, for example, force the first file in this setup to be the internal database? It would look like:


Only the user would not have to actually set this thing up: the internal tablespace would be there by default (and always first).

Then we would be able to drop our own table space as much as we would like to, but never touch the internal tablespace. It would always extend into our own ibdata1 file.

I'm wondering if I'm making sense at all and if this is possible.

GTID and settings

The fact that you have to specify both gtid_mode=ON as well as disable-gtid-unsafe-statements is a bit of a bummer. I wouldn't mind as much if error messages would be informative. But as it turned out, when I wanted to test GTID I did the following:

mysql> STOP SLAVE;
mysql> change master to MASTER_AUTO_POSITION=1;
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when GTID_MODE = ON.

-- OK, setting gtid_mode=ON in config file, restarting server.
-- Oooops, server won't restart!
-- Getting this error message in log: "--gtid-mode=UPGRADE_STEP_1 or --gtid-mode=UPGRADE_STEP_2 are not yet supported"
-- What?

Checking up on Giuseppe's post I realized I didn't set the disable-gtid-unsafe-statements param. But this was not mentioned on the above ERROR 1777, and the log error was quite cryptic.

TODO: just mention this other variable.

GTID, internal InnoDB tables & wreckage

OK, I managed to completely crash my replication setup. I setup GTID, and then:

set global master_info_repository:='table';
set global relay_log_info_repository='table';

Then shut down mysql; I wanted to see how reverting back to gtid_mode=OFF works. Oh, I didn't set the two params in the config file, so their effect was lost.

Starting mysql, I get:

ERROR 1794 (HY000) at line 1: Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

The error log says:

121015  9:38:58 [ERROR] Error creating master info: Multiple replication metadata repository instances found with data in them. Unable to decide which is the correct one to choose.
121015  9:38:58 [ERROR] Failed to create or recover replication info repository.
121015  9:38:58 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted.

What's interesting is that the data is still in the tables:

mysql> select * from mysql.slave_master_info\G
*************************** 1. row ***************************
       Number_of_lines: 23
       Master_log_name: mysql-bin.000003
        Master_log_pos: 2623
             User_name: rsandbox
         User_password: rsandbox
                  Port: 14701
         Connect_retry: 60
           Enabled_ssl: 0
Ssl_verify_server_cert: 0
             Heartbeat: 1800
    Ignored_server_ids: 0
                  Uuid: 10fa73da-13ac-11e2-bdcd-0024e8cd3122
           Retry_count: 86400
 Enabled_auto_position: 1

I've tried restarting, setting variables in the config file, changing them dynamically. To no avail.

No, I haven't filed a bug report yet.

These are still my first steps into 5.6 replication and my very first impressions.

  • I agree that the InnoDB tables (and/or .idb+.frm files for same) are not welcome in the `mysql` database. It may lead to inconsistences like this:

  • Ammon

    This was my experience trying out 5.6.6 as well. It seems too easy to paint yourself into horrible little corners where replication status is concerned. I do hope this is made more intuitive, but I imagine it will have to wait until 5.7 or whatever else is next.

  • XL

    Unfortunately there is no way to separate InnoDB "system" and "user" tables in a way that allows you to drop table spaces for user tables, but not for system tables. The reason is the InnoDB data dictionary and especially how InnoDB is panicking once it finds something that looks like data dictionary corruption.

    The data dictionary is located in the system table space (if there are multiple ibdata files, it might even be spread about those).

    Now if you remove an ibd file without touching the DD, then InnoDB will sometimes just refuse to do anything (including deleting the table from the DD) with "table does not exist" error. If you happen to delete the ibd file for a single partition of a partitioned table, then InnoDB might even crash.

    OTOH you could just delete the shared table space(s) (and thus also the DD) - then InnoDB will forget anything it ever knew about any InnoDB table. Any data in ibd files will be lost (not quite, it is possible to recover such data, but not automatically)

    OK. Nothing of this is new.

    Now what is bad with the new InnoDB system tables in 5.6 is that the server cannot recover from losing them. OTOH InnoDB can perfectly well recover from losing the DD - it will just create a new, empty one. And exactly the same it should do for the statistics and slave status tables. OK, this is a bit harder because those tables can use file_per_table. And they do have frm files. Maybe this was wrong in the first place. I don't see why those tables could not be anonymous system tables just as the DD.

  • @XL,

    Thank you for elaborating. 5.6 presents undo log on its own tablespace. Is this a development change that can allow multiple tablespaces, then?

  • Gerry

    Could your bug be related to the one I filed a few days ago? See

  • Pingback: Further experiments with MySQL 5.6.7-RC: submit your bugs |

  • Avi Vainshtein

    Sorry for posting here, but i didn't find a separate topic for issues about Delayed Replication in 5.6 .

    Scenario: Master-Slave replication in 5.6 with a Delay (let's say 2 hours). The Master fails and isn't available at all.
    The question is : what is the proper way to cause the Slave to execute all the not-yet-applied SQL ?(i.e. to cause SQL_Remaining_Delay to become 0 , without waiting the 2 hours ?)

  • @Avi,

    As per,
    and the slave will just execute whatever it has got in the relay logs.

  • Avi Vainshtein


    This isn't correct - as per my test.
    After the commands:
    stop slave;
    start slave;
    and while the Master is still not available - the IO slave is in "Connecting"State, all the existed before relay logs are deleted, and the SQL changes executed at the Master - were NOT applied at the Slave...

  • The existing relay logs are deleted? Funny/sad this should be the behavior; I haven't done so myself, so you're the expert on this.

    But do try following on the last paragraph in said documentation:

    " The file now contains the delay value, so the file format has changed. See Section, “Slave Status Logs”. In particular, the first line of the file now indicates how many lines are in the file. If you downgrade a slave server to a version older than MySQL 5.6, the older server will not read the file correctly. To address this, modify the file in a text editor to delete the initial line containing the number of lines. "

  • premature submit. Try to rewrite the number in said file (to 0?) while replication is STOPped. I'm curious to learn the answer.

    Also try asking on

  • Avi Vainshtein

    Thank you, Shlomi, for referring me to i've got an answer from there, which is :
    - stop slave;
    (where the RELAY_LOG_FILE and RELAY_LOG_POS values are taken from 'show slave status;' output)
    - start slave;

    I've tried that and it worked fine.

    As per your suggestion to try file: couldn't do that because of 'relay_log_info_repository=TABLE'.

Powered by Wordpress and MySQL. Theme by