Bug #19660 mysqldump --single-transaction should be with CONSISTENT SNAPSHOT
Submitted: 10 May 2006 1:52 Modified: 13 Nov 2006 4:45
Reporter: Morgan Tocker Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:4.1,5.0,5.1 OS:
Assigned to: Iggy Galarza CPU Architecture:Any

[10 May 2006 1:52] Morgan Tocker
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
[29 May 2006 7:03] Greg Lehey
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.
[3 Oct 2006 15:25] Bugs System
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
[4 Oct 2006 16:58] Timothy Smith
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
[13 Nov 2006 4:45] Paul Dubois
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.
[14 Aug 2007 18:19] Alan Kasindorf
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.
[22 Aug 2007 23:10] Timothy Smith
See Bug #30444.