MySQL Bugs: #39780: Allow restore to skip gap event during restore on master

Bug #39780	Allow restore to skip gap event during restore on master
Submitted:	1 Oct 2008 15:43	Modified:	15 Apr 2009 1:22
Reporter:	Chuck Bell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S4 (Feature request)
Version:	6.0.8	OS:	Any
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
Note: This feature request is pending the completion of WL#4209 and WL#4280.

When a restre is executed on a master, a gap event is written to the binary log to signal all slaves to stop replication. This is a protective measure to ensure that whatever changes the restore does on the master (restore is currently destructive) does not break replication. 

The normal process is for the user to assess the effect of the restore and if appropriate apply the restore on the slave prior to restarting replication.

However, it is possible that the slave isn't replicating the database(s) in the restore and therefore the restore would have no affect on the slave. Under these conditions, the slave should not be stopped and the restore is safe to execute without disrupting replication.

What is needed is a way to tell the restore operation to skip writing the gap event. 

How to repeat:
NA

Suggested fix:
There are two proposals to accomplish this:

1) Create a global dynamic variable like 'ignore_gap_event_on_restore' that users can turn on (TRUE) prior to the restore and turn off (FALSE - the default) after the restore.

2) Create an extension to the RESTORE command to allow the restore to skip the gap event such as RESTORE ... SKIP GAP EVENT.

Note: Research into which method is best should be conducted as part of the solution for this feature request.

Analysis of the two suggested implementations
---------------------------------------------

1) Using a variable.

A variable would be very easy to implement. If it were local only then there would be less opportunity for leaving the setting turned on when not intended.

However, a variable would require users to remember yet another tidbit of 'how to do it'.

2) Using an override on the command.

An override option would allow users to ignore the gap event for that restore only. It has no possibility of mistaken settings like the variable (other than the obvious user error).

However, an override would require changing the parser to recognize yet another exception to the normal flow. It may also complicate later efforts to streamline the RESTORE command code to use key/value pairs (not currently in progress but discussed as a possibility).

Conclusion
----------
The override option would be easier to implement and easier for the user to use.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62560

2746 Chuck Bell	2009-01-06
      BUG#39780 Allow restore to skip gap event during restore on master 
      
      This patch adds a new option to the restore command: skip_gap_event.
      It is used to skip writing the incident event when a restore is
      run on a master in an active replication topology (gap event).

Back to "In progress" while waiting for a reply to my review.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65674

2764 Chuck Bell	2009-02-09
      BUG#39780 Allow restore to skip gap event during restore on master 
      
      This patch adds a new option to the restore command: skip_gap_event.
      It is used to skip writing the incident event when a restore is
      run on a master in an active replication topology (gap event).

Good to push.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65929

2768 Chuck Bell	2009-02-11
      BUG#39780 Allow restore to skip gap event during restore on master 
      
      This patch adds a new option to the restore command: skip_gap_event.
      It is used to skip writing the incident event when a restore is
      run on a master in an active replication topology (gap event).

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090326121822-pt84kzxxayzho4mn) (version source revid:rafal.somla@sun.com-20090302164601-znhm4tadplfi2iqu) (merge vers: 6.0.11-alpha) (pib:6)

"This patch adds a new option to the restore command: skip_gap_event."

Please specify what the syntax of RESTORE is after this addition.

The new syntax is:

RESTORE FROM 'image_file_name' [OVERWRITE] [SKIP_GAP_EVENT]

:)

Noted in 6.0.11 changelog.

The RESTORE statement now has a SKIP_GAP_EVENT option that causes the
restore operation not to write the gap event to the binary log that
causes any replication slaves to stop replication. This is useful 
when RESTORE is run on a master server and the backup image does not
contain databases that are replicated to the slaves.