Re: Does --single-transaction apply to all db'es being dumped with mysqldump?
Posted by: Peter Brawley
Date: June 21, 2017 09:16AM

It's an interesting problem. A table may reference tables in other DBs, and of course MySQL will enforce those references. Does mysqldump --single-transaction respect them?

A simple test case ...

create schema a;
use a;
create table a.a( i int primary key );
insert into a.a values(1);

create schema b;
create table b.b ( j int primary key, i int, foreign key(i) references a.a(i) );
insert into b.b values(1,1);

create table b.c( k int primary key, j int, foreign key(j) references b(j) );
insert into b.c values(1,1);

Now run mysqldump --databases a b --single_transaction. The dump file has DDL and DML. Only the DML can be transactional. For each DB in the order listed on the cmd line, mysqldump uses the DB, does the DDL, issues a table write lock, does the inserts, unlocks. No cross-DB integrity. This is so even when DDL is skipped with --nocreate-db and --no-create-info. Same behaviour with --lock-all-tables.

Big problem if you have cross-database table relations.

Edited 1 time(s). Last edit at 06/21/2017 09:28AM by Peter Brawley.

Options: ReplyQuote

Written By
Re: Does --single-transaction apply to all db'es being dumped with mysqldump?
June 21, 2017 09:16AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.