Re: Importing a database dump created with --single-transaction does not result in single-transaction import of the file

[Peter Brawley]

1. The mysqldump --single-transaction option refers to script generation, not script execution later when it is being read.

2. InnoDB transactions commit DDL. This wee script illustrates the problem:

drop table if exists t;
create table t(i int primary key auto_increment, j int);
insert into t (j) values(0);
select * from t;
+---+------+
| i | j    |
+---+------+
| 1 |    0 |
+---+------+

-- client 1 drops, recreates & inserts, but does not commit
begin work;
drop table if exists t;
create table t(i int primary key auto_increment, j int);
insert into t (j) values(1);
select * from t;
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
+---+------+

-- client 2 can see result of client 1's changes before client 1 issues a commit: 
select * from t;
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
+---+------+

So, can your script be reformulated to avoid DDL?



Edited 2 time(s). Last edit at 02/11/2013 04:29PM by Peter Brawley.