########################################
# Author: JBM
# Date: 2006-01-24
# Purpose: Test CDD backup and restore
########################################

-- source include/have_multi_ndb.inc
-- source suite/ndb/include/backup_restore_setup.inc

--disable_query_log
set new=on;
--enable_query_log

############ Test 1 Simple DD backup and restore #############
-- echo **** Test 1 Simple DD backup and restore ****

CREATE LOGFILE GROUP log_group1
ADD UNDOFILE './log_group1/undofile.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE = 1M
ENGINE=NDB;

CREATE TABLESPACE table_space1
ADD DATAFILE './table_space1/datafile.dat'
USE LOGFILE GROUP log_group1
INITIAL_SIZE 24M
ENGINE NDB;


CREATE TABLE test.t1
(pk1 MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 CHAR(50) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL) TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;

let $j= 500;
--disable_query_log
while ($j)
{
  eval INSERT INTO test.t1 VALUES (NULL, "Sweden", $j, b'1');
  dec $j;
}
--enable_query_log
SELECT COUNT(*) FROM test.t1;
SELECT pk1, c2, c3,  hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;

-- source include/ndb_backup.inc

DROP TABLE test.t1;

ALTER TABLESPACE table_space1
DROP DATAFILE './table_space1/datafile.dat';

DROP TABLESPACE table_space1;

DROP LOGFILE GROUP log_group1
ENGINE NDB;

# Store initial counts of both detected and synchronized objects
--let $initial_detected_count_server1 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_detected_count', Value, 1)
--let $initial_synced_count_server1 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_synced_count', Value, 1)
--connection server2
--let $initial_detected_count_server2 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_detected_count', Value, 1)
--let $initial_synced_count_server2 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_synced_count', Value, 1)
--connection server1
# Set low interval so changes are detected quickly
SET @old_ndb_metadata_check_interval = @@global.ndb_metadata_check_interval;
SET GLOBAL ndb_metadata_check_interval = 0;

-- source include/ndb_restore_master.inc

# Wait until the following 3 object changes are detected:
# Logfile group 'log_group1'
# Tablespace 'table_space1'
# Table 'test.t1'
--let $initial_detected_count = $initial_detected_count_server1
--let $expected_changes = 3
--let $max_wait = 30
--source wait_metadata_changes_detected.inc

# Changes have been detected, reset value
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;

# Wait until the changes detected have been synced
--let $initial_synced_count = $initial_synced_count_server1
--let $max_wait = 30
--source wait_metadata_synced.inc

--connection server2
# Sync on server 2 as well
# Set low interval so changes are detected quickly
SET @old_ndb_metadata_check_interval = @@global.ndb_metadata_check_interval;
SET GLOBAL ndb_metadata_check_interval = 0;

--let $initial_detected_count = $initial_detected_count_server2
--let $expected_changes = 3
--let $max_wait = 30
--source wait_metadata_changes_detected.inc

# Changes have been detected, reset value
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;

# Wait until the changes detected have been synced
--let $initial_synced_count = $initial_synced_count_server2
--let $max_wait = 30
--source wait_metadata_synced.inc

--connection server1

SELECT COUNT(*) FROM test.t1; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5;

################# Mixed Cluster Test ############################
-- echo **** Test 2 Mixed Cluster Test backup and restore ****

CREATE TABLE test.t2
(pk1 MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(200) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL)ENGINE=NDB;

let $j= 500;
--disable_query_log
while ($j)
{
  eval INSERT INTO test.t2 VALUES (NULL, "Sweden, Texas", $j, b'0');
  dec $j;
}
--enable_query_log

CREATE TABLE test.t3 (c1 int not null auto_increment, data LONGBLOB, PRIMARY KEY(c1))TABLESPACE table_space1 STORAGE DISK ENGINE=NDB;

CREATE TABLE test.t4 (c1 int not null auto_increment, data LONGBLOB, PRIMARY KEY(c1))ENGINE=NDB;

let $j= 50;
--disable_query_log
while ($j)
{
  INSERT INTO test.t3 VALUES (NULL, repeat('a',1*1024));
  INSERT INTO test.t3 VALUES (NULL, repeat('b',16*1024));
  INSERT INTO test.t4 VALUES (NULL, repeat('a',1*1024));
  INSERT INTO test.t4 VALUES (NULL, repeat('b',16*1024));
  dec $j;
}
--enable_query_log

SELECT COUNT(*) FROM test.t1;

SELECT pk1, c2, c3,  hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5; 

SELECT COUNT(*) FROM test.t2; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t2 ORDER BY pk1 LIMIT 5; 

SELECT COUNT(*) FROM test.t3; 

SELECT LENGTH(data) FROM test.t3 WHERE c1 = 1; 

SELECT LENGTH(data) FROM test.t3 WHERE c1 = 2; 

SELECT COUNT(*) FROM test.t4; 

SELECT LENGTH(data) FROM test.t4 WHERE c1 = 1; 

SELECT LENGTH(data) FROM test.t4 WHERE c1 = 2;

-- source include/ndb_backup.inc

DROP TABLE test.t1;
DROP TABLE test.t2;
DROP TABLE test.t3;
DROP TABLE test.t4;

ALTER TABLESPACE table_space1
DROP DATAFILE './table_space1/datafile.dat';

DROP TABLESPACE table_space1;

DROP LOGFILE GROUP log_group1
ENGINE NDB;

# Store initial counts of both detected and synchronized objects
--let $initial_detected_count_server1 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_detected_count', Value, 1)
--let $initial_synced_count_server1 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_synced_count', Value, 1)
--connection server2
--let $initial_detected_count_server2 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_detected_count', Value, 1)
--let $initial_synced_count_server2 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_synced_count', Value, 1)
--connection server1
# Set low interval so changes are detected quickly
SET GLOBAL ndb_metadata_check_interval = 0;

-- source include/ndb_restore_master.inc

# Wait until the following 6 object changes are detected:
# Logfile group 'log_group1'
# Tablespace 'table_space1'
# Table 'test.t1'
# Table 'test.t2'
# Table 'test.t3'
# Table 'test.t4'
--let $initial_detected_count = $initial_detected_count_server1
--let $expected_changes = 6
--let $max_wait = 30
--source wait_metadata_changes_detected.inc

# Changes have been detected, reset value
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;

# Wait until the changes detected have been synced
--let $initial_synced_count = $initial_synced_count_server1
--let $max_wait = 30
--source wait_metadata_synced.inc

--connection server2
# Sync on server 2 as well
# Set low interval so changes are detected quickly
SET GLOBAL ndb_metadata_check_interval = 0;

--let $initial_detected_count = $initial_detected_count_server2
--let $expected_changes = 6
--let $max_wait = 30
--source wait_metadata_changes_detected.inc

# Changes have been detected, reset value
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;

# Wait until the changes detected have been synced
--let $initial_synced_count = $initial_synced_count_server2
--let $max_wait = 30
--source wait_metadata_synced.inc

--connection server1

SELECT COUNT(*) FROM test.t1;

SELECT pk1, c2, c3,  hex(c4) FROM test.t1 ORDER BY pk1 LIMIT 5; 

SELECT COUNT(*) FROM test.t2; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t2 ORDER BY pk1 LIMIT 5; 

SELECT COUNT(*) FROM test.t3; 

SELECT LENGTH(data) FROM test.t3 WHERE c1 = 1; 

SELECT LENGTH(data) FROM test.t3 WHERE c1 = 2; 

SELECT COUNT(*) FROM test.t4; 

SELECT LENGTH(data) FROM test.t4 WHERE c1 = 1; 

SELECT LENGTH(data) FROM test.t4 WHERE c1 = 2;

DROP TABLE test.t1;
DROP TABLE test.t2;
DROP TABLE test.t3;
DROP TABLE test.t4;
###################### Adding partition #################################
-- echo **** Test 3 Adding  partition Test backup and restore ****

CREATE TABLESPACE table_space2
ADD DATAFILE './table_space2/datafile.dat'
USE LOGFILE GROUP log_group1
INITIAL_SIZE 12M
ENGINE NDB;

CREATE TABLE test.t1 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 VARCHAR(150) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))TABLESPACE table_space1 STORAGE DISK ENGINE=NDB PARTITION BY HASH(c3) PARTITIONS 4;

CREATE TABLE test.t4 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 VARCHAR(180) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))ENGINE=NDB PARTITION BY HASH(c3) PARTITIONS 2;

CREATE TABLE test.t2 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 TEXT NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))TABLESPACE table_space2 STORAGE DISK ENGINE=NDB PARTITION BY KEY(c3) (PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);

CREATE TABLE test.t5 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 TEXT NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))ENGINE=NDB PARTITION BY KEY(pk1) (PARTITION p0 ENGINE = NDB, PARTITION p1 ENGINE = NDB);

CREATE TABLE test.t3 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 VARCHAR(202) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))TABLESPACE table_space2 STORAGE DISK ENGINE=NDB PARTITION BY RANGE (c3) PARTITIONS 3 (PARTITION x1 VALUES LESS THAN (105), PARTITION x2 VALUES LESS THAN (333), PARTITION x3 VALUES LESS THAN (720));

CREATE TABLE test.t6 (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT, c2 VARCHAR(220) NOT NULL, c3 INT NOT NULL, c4 BIT NOT NULL, PRIMARY KEY(pk1,c3))ENGINE=NDB PARTITION BY RANGE (pk1) PARTITIONS 2 (PARTITION x1 VALUES LESS THAN (333), PARTITION x2 VALUES LESS THAN (720));

SHOW CREATE TABLE test.t1;

SHOW CREATE TABLE test.t2;

SHOW CREATE TABLE test.t3;

SHOW CREATE TABLE test.t4;

SHOW CREATE TABLE test.t5;

SHOW CREATE TABLE test.t6;

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't1'; 

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't2'; 

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't3'; 

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't4'; 

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't5'; 

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't6';


let $j= 500;
--disable_query_log
while ($j)
{
  eval INSERT INTO test.t1 VALUES (NULL, "Sweden, Texas", $j, b'0');
  eval INSERT INTO test.t4 VALUES (NULL, "Sweden, Texas", $j, b'0');
  dec $j;
  eval INSERT INTO test.t2 VALUES (NULL, "Sweden, Texas, ITALY, Kyle, JO, JBM,TU", $j, b'1');
  eval INSERT INTO test.t5 VALUES (NULL, "Sweden, Texas, ITALY, Kyle, JO, JBM,TU", $j, b'1');
  dec $j;
  eval INSERT INTO test.t3 VALUES (NULL, "TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU", $j, b'1');
  eval INSERT INTO test.t6 VALUES (NULL, "TEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXASTEXAS, ITALY, Kyle, JO, JBM,TU", $j, b'1'); } --enable_query_log

SELECT COUNT(*) FROM test.t1;

SELECT pk1, c2, c3,  hex(c4) FROM test.t1 ORDER BY c3 LIMIT 5; 

SELECT COUNT(*) FROM test.t2; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t2 ORDER BY c3 LIMIT 5; 

SELECT COUNT(*) FROM test.t3; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t3 ORDER BY c3 LIMIT 5; 

SELECT COUNT(*) FROM test.t4; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t4 ORDER BY c3 LIMIT 5; 

SELECT COUNT(*) FROM test.t5; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t5 ORDER BY c3 LIMIT 5; 

SELECT COUNT(*) FROM test.t6; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t6 ORDER BY c3 LIMIT 5;

-- source include/ndb_backup.inc

DROP TABLE test.t1;
DROP TABLE test.t2;
DROP TABLE test.t3;
DROP TABLE test.t4;
DROP TABLE test.t5;
DROP TABLE test.t6;

ALTER TABLESPACE table_space1
DROP DATAFILE './table_space1/datafile.dat';

ALTER TABLESPACE table_space2
DROP DATAFILE './table_space2/datafile.dat';

DROP TABLESPACE table_space1;

DROP TABLESPACE table_space2;

DROP LOGFILE GROUP log_group1
ENGINE NDB;

# Store initial counts of both detected and synchronized objects
--let $initial_detected_count_server1 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_detected_count', Value, 1)
--let $initial_synced_count_server1 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_synced_count', Value, 1)
--connection server2
--let $initial_detected_count_server2 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_detected_count', Value, 1)
--let $initial_synced_count_server2 = query_get_value(SHOW STATUS LIKE 'Ndb_metadata_synced_count', Value, 1)
--connection server1
# Set low interval so changes are detected quickly
SET GLOBAL ndb_metadata_check_interval = 0;

-- source include/ndb_restore_master.inc

# Wait until the following 9 object changes are detected:
# Logfile group 'log_group1'
# Tablespace 'table_space1'
# Tablespace 'table_space2'
# Table 'test.t1'
# Table 'test.t2'
# Table 'test.t3'
# Table 'test.t4'
# Table 'test.t5'
# Table 'test.t6'
--let $initial_detected_count = $initial_detected_count_server1
--let $expected_changes = 9
--let $max_wait = 45
--source wait_metadata_changes_detected.inc

# Changes have been detected, reset value
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;

# Wait until the changes detected have been synced
--let $initial_synced_count = $initial_synced_count_server1
--let $max_wait = 45
--source wait_metadata_synced.inc

--connection server2
# Sync on server 2 as well
# Set low interval so changes are detected quickly
SET GLOBAL ndb_metadata_check_interval = 0;

--let $initial_detected_count = $initial_detected_count_server2
--let $expected_changes = 9
--let $max_wait = 45
--source wait_metadata_changes_detected.inc

# Changes have been detected, reset value
SET GLOBAL ndb_metadata_check_interval = @old_ndb_metadata_check_interval;

# Wait until the changes detected have been synced
--let $initial_synced_count = $initial_synced_count_server2
--let $max_wait = 45
--source wait_metadata_synced.inc

--connection server1

SHOW CREATE TABLE test.t1;

SHOW CREATE TABLE test.t2;

SHOW CREATE TABLE test.t3;

SHOW CREATE TABLE test.t4;

SHOW CREATE TABLE test.t5;

SHOW CREATE TABLE test.t6;

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't1';

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't2';

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't3';

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't4';

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression FROM information_schema.partitions WHERE table_name= 't5';

SELECT table_name, partition_name, partition_ordinal_position, partition_method, partition_expression, partition_description FROM information_schema.partitions WHERE table_name= 't6';

SELECT COUNT(*) FROM test.t1; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t1 ORDER BY c3 LIMIT 5; 

SELECT COUNT(*) FROM test.t2; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t2 ORDER BY c3 LIMIT 5; 

SELECT COUNT(*) FROM test.t3; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t3 ORDER BY c3 LIMIT 5; 

SELECT COUNT(*) FROM test.t4; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t4 ORDER BY c3 LIMIT 5; 

SELECT COUNT(*) FROM test.t5; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t5 ORDER BY c3 LIMIT 5; 

SELECT COUNT(*) FROM test.t6; 

SELECT pk1, c2, c3,  hex(c4) FROM test.t6 ORDER BY c3 LIMIT 5;

# Cleanup

DROP TABLE test.t1;
DROP TABLE test.t2;
DROP TABLE test.t3;
DROP TABLE test.t4;
DROP TABLE test.t5;
DROP TABLE test.t6;

ALTER TABLESPACE table_space1 DROP DATAFILE './table_space1/datafile.dat';

ALTER TABLESPACE table_space2 DROP DATAFILE './table_space2/datafile.dat';

DROP TABLESPACE table_space1;

DROP TABLESPACE table_space2;

DROP LOGFILE GROUP log_group1 ENGINE = NDB;
--source suite/ndb/include/backup_restore_cleanup.inc

#End 5.1 test case


--remove_file $NDB_TOOLS_OUTPUT