Thoughts on MySQL 5.6 new replication features

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:

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

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

[mysqld]
innodb_data_file_path=ibdata_internal_do_not_touch:2M;ibdata1_this_one_is_yours:50M:autoextend

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
                  Host: 127.0.0.1
             User_name: rsandbox
         User_password: rsandbox
                  Port: 14701
         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: 10fa73da-13ac-11e2-bdcd-0024e8cd3122
           Retry_count: 86400
               Ssl_crl: 
           Ssl_crlpath: 
 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.