MySQL Bugs: #37012: Online backup: Can't backup to a pipe.

Bug #37012	Online backup: Can't backup to a pipe.
Submitted:	27 May 2008 17:18	Modified:	13 Jan 2009 16:29
Reporter:	Priyesh Narayanan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.5 alpha, 6.0-backup BK	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am trying out MySQL online backup.
What I tried:
I wanted to backup to a named pipe so that my own custom program could read the backup data from that pipe and send it across the network, the benefit being not using/needing any local disk storage.

What I expected:
The backup succeeding - maybe not return until I read from the pipe - but at least not throwing any error.

What I get:
However the backup fails with the error 'ERROR 1630: Can't write to backup location ... (file already exists?)'.

I guess this means that online backup currently always tries to create a file and then writes to it (5.2 online backup was perhaps different on this aspect?) I wish to see an option that I can specify when I don't want mysql to create the file (and only write to it).

How to repeat:
A simple way to reproduce is shown below.
Tried on Linux
1. Create a pipe:
mkfifo /tmp/bak_pipe

2. Set appropriate write permissions to the pipe, perhaps something like
chmod 666 /tmp/bak_pipe

3. Fire a backup to the pipe.
mysql> backup database test to '/tmp/bak_pipe';

4. On another console, read from the pipe and write to a regular file.
cat /tmp/bak_pipe > /tmp/bak_file

Suggested fix:
Umm.. I guess there are two things required in general for backing up to a pipe to work:
1. Online backup takes an option that makes it not create a file but just write to it - this needs fixing.
2. Backup always writes sequentially (and restore reads sequentially) - but from what I understand, this is already the case today.

Thank you for the report.

Verified as described.

On Tue, May 27, 2008 at 12:02:29PM -0600, Peter Gulutzan wrote:
> On Tue, 2008-05-27 at 08:46 +0200, Lars Thalmann wrote:
> > Peter, what should the option be called?
> 
> As I understand the request:
> 
> The user wants to backup to a named pipe.
> The user does not want to restore from a named pipe.
> The user does not want LOCAL, i.e. backup on client machine.
> 
> One way is to allow writing even if something exists;
> BACKUP DATABASE d TO '' [ OVERWRITE | APPEND ];
> That would be convenient anyway, when re-using obsolete files.
> But I think it's insecure. Users with FILE and BACKUP
> privileges could overwrite interesting things.
> 
> So I think it's better to change nothing in the syntax,
> but just say "allow writing if 'file' is a named pipe".
> The most generic way to find out, I think, is to check
> if file size = 0. In that case, we don't have to call
> the option anything.
> 
> This probably won't work with "parallel" backup feature.
> 
> I do not know what should happen if the server starts with
> --chroot=path, -r path
> http://dev.mysql.com/doc/refman/5.0/en/server-options.html
> 
> FYI,
> Microsoft dropped named-pipes (Windows) support in 2005:
> "For security reasons, SQL Server does not support backup to named
> pipes."
> http://msdn.microsoft.com/en-us/library/ms144262.aspx
> But DB2 still allows it on Unix
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad...

Sounds ok.  

Don't change syntax, but do allow append without 
error to a zero-sized file.

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/48179

2638 Sergey Vojtovich	2008-06-19
      BUG#37012 - Online backup: Can't backup to a pipe.
      
      It was impossible to backup to a pipe. Backup code refuse
      to overwrite existing files no matter if they're regular files
      or named pipes.
      
      This fix allows to write backup image to existing named pipe.

Approved, given that the m_flags issue is resolved according to what Rafal and I agreed upon.

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/48806

2641 Sergey Vojtovich	2008-07-01
      BUG#37012 - Online backup: Can't backup to a pipe.
      
      It was impossible to backup to a pipe. Backup code refuse
      to overwrite existing files no matter if they're regular files
      or named pipes.
      
      This fix allows to write backup image to existing named pipe.
      
      No test case for this fix, as currently there is no good way
      in our test suite to determine if OS supports named pipes.

Was pushed to 6.0.6.

Noted in 6.0.6 changelog.

On Unix, it is now possible for the output file for BACKUP DATABASE
to be an existing FIFO.

Pushed into 6.0.6-alpha  (revid:svoj@mysql.com-20080701131203-hhtex74pkzs4qiio) (version source revid:hakan@mysql.com-20080716160135-l7310heud233kagu) (pib:3)

I have exactly the same symptom, but in my example; it was a windows system.
I run: Server version: 6.0.8-alpha-community MySQL Community Server (GPL)
I am not sure if this fix has been applied to this version as well...

Many thanks for your feedback. I tested with our actual bzr source release to make sure that we don't have a regression here.

All works fine and we could exclude a regression here at the moment.