MySQL Bugs: #48124: Allow InnoDB to return the master coordinates when a transaction is started

Bug #48124	Allow InnoDB to return the master coordinates when a transaction is started
Submitted:	16 Oct 2009 21:04
Reporter:	Harrison Fisk	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S4 (Feature request)
Version:	5.0,5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Backup, Contribution, flush, hot, innodb, read lock

Description:
Currently mysqldump works by doing:

FLUSH TABLES WITH READ LOCK;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SHOW MASTER STATUS;
UNLOCK TABLES;
SELECT....;
SELECT....;

The reason the flush tables with read lock command is used is in order to get the coordinates synchronized properly with the start of the transaction.

Another solution would be to make the start of the transaction and the getting of the coordinates atomic. InnoDB already stores the master status inside of it, so it should be able to return that. This could either be done by extending the START TRANSACTION, or by adding something else that accesses the data (in a transaction consistent manner) such as a function or new statement.

This could then remove the need to use FLUSH TABLES WITH READ LOCK which would remove the locking and flushing requirement to make the InnoDB backup completely non-blocking.  In some cases, the FLUSH TABLES WITH READ LOCK will severely impact servers.

Example commands added could be:

START TRANSACTION WITH CONSISTENT SNAPSHOT RETURN INNODB MASTER STATUS;

START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT innodb_master_log(), innodb_master_pos();

How to repeat:
Take a backup and notice that FLUSH TABLES WITH READ LOCK is done.  
Wonder why there isn't a better solution.

Suggested fix:
Allow InnoDB to return the corresponding binary log coordinates for a transaction in a non-locking method.

First sign that it might work:
start transaction with consistent innodb snapshot;
File	Position
master-bin.000001	153779

previous patch file was corrupt

Attachment: 0001-Provide-fast-and-non-blocking-alternative-to-FLUSH-T.patch (application/octet-stream, text), 21.47 KiB.

The patch won't compile with embedded enabled.  show_master_offset is used in begin_trans, however if HAVE_REPLICATION isn't defined (such as for embedded), then show_master_offset isn't defined and hence causes an error in compiling.  

If you add an #ifdef HAVE_REPLICATION around the usage of show_master_offset, then it will compile fine.

updated patch

Attachment: ip (application/octet-stream, text), 20.68 KiB.

The main reason for using FLUSH TABLES WITH READ LOCK in innobackup is to stop DDL operations so that we can copy all the .frm files.

Hi Heikki!

The usage for this is for mysqldump primarily.  innodb hot backup can already get the binary logs from the crash recovery output, so it wouldn't get any advantage from this change.

mysqldump --master-data --single-transaction already has issues if there are DDL statements while it is running, so this wouldn't make it any worse.

I see, in this case the backup would only contain dumps of certain tables, as well as the master binlog position which corresponds to the consistent snapshot of the tables.

Maybe this implementation doesn't help InnoDB hot backup, but InnoDB hot backup has the same problem with FLUSH TABLES WITH READ LOCK. Nobody likes to run FTWRL on a busy master. So providing an alternative to FTWRL that doesn't have the potential to lock up a server might make ibbackup customers happy.

With the fix for Bug#989 DDL shouldn't cause any problems to a running transaction.

Patch for this is in http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/5.1/revision/3415