^^&&^^

Edit File: ndb_restore_schema_tolerance.test

######################################################################
# Test restoring backups into similar schemas
######################################################################

-- source include/have_ndb.inc
-- source include/ndb_default_cluster.inc

--echo Test ndb_restore ability to ignore some schema differences

use test;
create table t1 (
  a int,
  b int,
  c int,
  d int,
  e varchar(200),
  f int,
  g char(20),
  h text,
  i int,
  primary key(a,b)) TABLESPACE ts1 engine = ndb;

insert into t1 values
        (1, 1, 1, 1, '1', 1, 'Rankin', 'Rebus', 1),
        (2, 2, 2, 2, '2', 2, 'Doyle', 'Holmes', 2),
        (3, 3, 3, 3, '3', 3, 'Burns', 'Mouse', 3),
        (4, 4, 4, 4, '4', 4, 'Gibbon', 'Chris', 4),
        (5, 5, 5, 5, '5', 5, 'Gray', 'Lanark', 5);

select * from t1 order by a;

--echo Backing up data
--source include/ndb_backup.inc

drop table t1;

--echo Normal restore
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r -m  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 2 -r     $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

show create table t1;

select * from t1 order by a;

truncate t1;

--echo Column name change, should fail without --exclude-missing-columns

alter table t1 change c cc int;

--error 1
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

--echo Retry with --exclude-missing-columns

--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r --exclude-missing-columns $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 2 -r --exclude-missing-columns $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

select * from t1 order by a;
truncate t1;

--echo Column type change, should fail

alter table t1 change cc c bigint;
show create table t1; #REMOVE!
--error 1
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

--echo Retry with --promote-attribute

--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r --promote-attribute $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 2 -r --promote-attribute $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

select * from t1 order by a;
truncate t1;

--echo Column nullability change, should fail

alter table t1 change c c int not null;

--error 1
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

alter table t1 change c c int;

# Skip Precision and scale differences - should fail

--echo Column length change, should fail

alter table t1 change g g char(22);

--error 1
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

--echo Character set difference, should fail

alter table t1 change g g char(20) character set binary;

--error 1
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

alter table t1 change g g char(20);

--echo AutoIncrement difference, should fail

alter table t1 change b b int auto_increment;

--error 1
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

--echo Default difference, should pass 
alter table t1 change b b int default 22;

--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 2 -r  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

select * from t1 order by a;
truncate t1;

alter table t1 change b b int;

--echo ArrayType difference, should fail
alter table t1 change e e varchar(300);

--error 1
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

alter table t1 change e e varchar(200);

--echo StorageType difference, should pass
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE = 1M
ENGINE=NDB;

CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 12M
ENGINE NDB;

alter table t1 change i i int storage disk;

select * from t1 order by a;

alter table t1 change i i int storage memory;
truncate t1;

# Skip BlobType difference (should fail)

--echo Dynamic property difference, should pass

alter table t1 change c c int column_format dynamic;

select * from t1 order by a;

drop table t1;
alter tablespace ts1 drop datafile 'datafile.dat' engine=ndb;
drop tablespace ts1 engine=ndb;
drop logfile group lg1 engine=ndb;

--echo Different PK columns, should fail
create table t1 (
  a int,
  b int,
  c int,
  d int,
  e varchar(200),
  f int,
  g char(20),
  h text,
  i int,
  primary key (a)) TABLESPACE ts1 engine = ndb;

--error 1
--exec $NDB_RESTORE --no-defaults --core-file=false -b $the_backup_id -n 1 -r  $NDB_BACKUP_DIR/BACKUP/BACKUP-$the_backup_id >> $NDB_TOOLS_OUTPUT

drop table t1;

--echo Different distribution keys, should pass

create table t1 (
  a int,
  b int,
  c int,
  d int,
  e varchar(200),
  f int,
  g char(20),
  h text,
  i int,
  primary key(a,b)) TABLESPACE ts1 engine = ndb partition by key(a);

select * from t1 order by a;

drop table t1;