Re: Does --single-transaction apply to all db'es being dumped with mysqldump?

[Peter Brawley]

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.