MySQL Bugs: #19660: mysqldump --single-transaction should be with CONSISTENT SNAPSHOT

Bug #19660	mysqldump --single-transaction should be with CONSISTENT SNAPSHOT
Submitted:	10 May 2006 3:52	Modified:	13 Nov 2006 5:45
Reporter:	Morgan Tocker
Status:	Closed
Category:	Server: Backup	Severity:	S3 (Non-critical)
Version:	4.1,5.0,5.1	OS:
Assigned to:	Iggy Galarza	Target Version:

Description:
mysqldump --single-transaction should immediatly set a consistent snapshot.

i.e. START TRANSACTION WITH CONSISTENT SNAPSHOT;
- not -
BEGIN;

How to repeat:
mysqldump --single-transaction

We can't always use CONSISTENT SNAPSHOT, because it would break backwards compatibility:
this syntax was introduced in release 4.1.  mysqldump solves this problem by outputting 
CONSISTENT SNAPSHOT only in conjunction with the --master-data option.

It's (barely) possible that this might not work for the submitter, so we're currently
waiting for confirmation.

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

ChangeSet@1.2542, 2006-10-03 11:26:50-04:00, iggy@rolltop.ignatz42.dyndns.org +1 -0
  Bug#19660 mysqldump --single-transaction should be with CONSISTENT SNAPSHOT

OK to push, Iggy.  Thanks!

This will be a bonus for people who want more flexibility in integrating mysqldump into
their own backup scripts.

Regards,

Timothy

Noted in 4.1.23, 5.0.30 (not 5.0.29), 5.1.13 changelogs.

mysqldump --single-transaction now uses START TRANSACTION /*!40100
WITH CONSISTENT SNAPSHOT */ rather than BEGIN to start a transaction,
so that a consistent snapshot will be used on those servers that support it.

Hey,

Was pointed here after tracking something obscure with mysqldump.

It appears that this fix will cause silent corruption against a 4.0 database if used with
the '--consistent-snapshot' and '--master-data' options.

Please note the comment above where the fix was applied in mysqldump.c:

    We use BEGIN for old servers. --single-transaction --master-data will fail
    on old servers, but that's ok as it was already silently broken (it didn't
    do a consistent read, so better tell people frankly, with the error).

    We want the first consistent read to be used for all tables to dump so we
    need the REPEATABLE READ level (not anything lower, for example READ
    COMMITTED would give one new consistent read per dumped table).

---

The comment is still the same, but doesn't reflect the code anymore. I'm told that you
_cannot_ achieve an accurate binlog position with these two options against mysql 4.0, and
it appears to be documented as so within mysqldump.c.

However, I'll re-open this bug as the mysql manual does not reflect this, and mysqldump
really ought to warn or exit if you attempt this combo against a 4.0 database.

See Bug #30444.