ダンプのデータとシーケンスの一貫性

DB

(8.0.26)

auto_incrementで採番した値をインサートしながらダンプ取得
このダンプをインポートしてauto_incrementとテーブルデータの整合性を確認する

drop table tab1;
create table tab1(col1 int  auto_increment primary key);


drop procedure proc1;

delimiter //
create procedure proc1()
begin
  while true do
    insert into tab1 values();
  end while;
end
//
delimiter ;

call proc1();


mysqldump -u root test -p --quick --source-data=2 --flush-logs  --single-transaction --triggers --routines --events > tab1.sql

drop database test;
create database test;

mysql -u root -p test < tab1.sql

 

select max(col1) from tab1;
+-----------+
| max(col1) |
+-----------+
|     16371 |
+-----------+
select auto_increment from information_schema.tables 
where table_schema = 'test'
and table_name = 'tab1';
+----------------+
| AUTO_INCREMENT |
+----------------+
|          16397 |
+----------------+


データ最大値 < シーケンス値
となるので、シーケンス巻き戻りは発生しないが、少しずれがある。

 

(19c)

シーケンスで採番した値をインサートしながらエクスポート
このダンプをインポートしてシーケンスとテーブルデータの整合性を確認する

drop table tab1 purge;
create table tab1(col1 int primary key);

drop sequence seq1;
create sequence seq1;

declare
begin
  while true loop
    insert into tab1 select seq1.nextval from dual;
    commit;
  end loop;
end;
/


expdp test/test@pdb1 directory=ORA_DIR dumpfile=test.dmp logfile=test.log schemas=test reuse_dumpfiles=yes

drop user test cascade;
create user test identified by test;
grant dba to test;

impdp test/test@pdb1 directory=ORA_DIR dumpfile=test.dmp logfile=test.log schemas=test


select max(col1) from tab1;

 MAX(COL1)
----------
   1238711
select LAST_NUMBER from user_sequences where sequence_name ='SEQ1';

LAST_NUMBER
-----------
     426381


シーケンス値取得後にデータエクスポートされるため
データ最大値 > シーケンス値
となり、シーケンスは巻き戻る★

pdb1(TEST)> insert into tab1 select seq1.nextval from dual;

insert into tab1 select seq1.nextval from dual
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(TEST.SYS_C006010)に反しています

-- エクスポート時にFLASHBACK_SCNパラメータを指定して一貫性のあるダンプを取得し、上記エラーが解消されるか確認


drop table tab1 purge;
create table tab1(col1 int primary key);

drop sequence seq1;
create sequence seq1;

declare
begin
  while true loop
    insert into tab1 select seq1.nextval from dual;
    commit;
  end loop;
end;
/

select current_scn from v$database;

expdp test/test@pdb1 directory=ORA_DIR dumpfile=test.dmp logfile=test.log schemas=test reuse_dumpfiles=yes FLASHBACK_SCN=6966405

drop user test cascade;
create user test identified by test;
grant dba to test;

impdp test/test@pdb1 directory=ORA_DIR dumpfile=test.dmp logfile=test.log schemas=test

select max(col1) from tab1;
 MAX(COL1)
----------
     74164
select LAST_NUMBER from user_sequences where sequence_name ='SEQ1';
LAST_NUMBER
-----------
     463681

データの取得時点がFLASHBACK_SCNで指定したexpdp開始前時点となり、
データ最大値 < シーケンス値
となるので、シーケンス巻き戻りは回避される。

 

(14)

シーケンスで採番した値をインサートしながらダンプ取得
このダンプをインポートしてシーケンスとテーブルデータの整合性を確認する

drop table tab1;
create table tab1(col1 int primary key);

drop sequence seq1;
create sequence seq1;


create or replace procedure proc1()
language plpgsql
as $$
begin
  while true loop
    insert into tab1 select nextval('seq1');
    commit;
  end loop;
end
$$;

call  proc1();

pg_dump -Fp  test > test.sql

drop database test;
create database test;

psql test < test.sql

select max(col1) from tab1;
  max
-------
 22813
select last_value from seq1;
 last_value
------------
      23177

データ最大値 < シーケンス値
となるので、シーケンス巻き戻りは発生しないが、
データ取得時点とシーケンス取得時点で少しずれがある。

 


(2019)

データとシーケンスを同時出力するダンプツールはない認識で検証不可