Added test(s) for auto discover of frm file

5 files changed, 538 insertions, 0 deletions

diff --git a/mysql-test/r/ndb_autodiscover.result b/mysql-test/r/ndb_autodiscover.result
new file mode 100644
index 00000000000..29fa93cacb0
--- /dev/null
+++ b/mysql-test/r/ndb_autodiscover.result
@@ -0,0 +1,211 @@
+drop table if exists t1,t2,t3,t4,t5,t6,t9;
+flush status;
+create table t1(
+id int not null primary key,
+name char(20)
+) engine=ndb;
+insert into t1 values(1, "Autodiscover");
+flush tables;
+select * from t1;
+id	name
+1	Autodiscover
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	1
+flush tables;
+insert into t1 values (2, "Auto 2");
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	2
+insert into t1 values (3, "Discover 3");
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	2
+flush tables;
+select * from t1;
+id	name
+2	Auto 2
+3	Discover 3
+1	Autodiscover
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	3
+flush tables;
+update t1 set name="Autodiscover" where id = 2;
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	4
+select * from t1 order by name;
+id	name
+2	Autodiscover
+1	Autodiscover
+3	Discover 3
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	4
+flush tables;
+delete from  t1 where id = 3;
+select * from t1;
+id	name
+2	Autodiscover
+1	Autodiscover
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	5
+drop table t1;
+flush status;
+create table t2(
+id int not null primary key,
+name char(22)
+) engine=ndb;
+insert into t2 values (1, "Discoverer");
+select * from t2;
+id	name
+1	Discoverer
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	0
+flush tables;
+select * from t2;
+id	name
+1	Discoverer
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	1
+drop table t2;
+flush status;
+create table t3(
+id int not null primary key,
+name char(255)
+) engine=ndb;
+insert into t3 values (1, "Explorer");
+select * from t3;
+id	name
+1	Explorer
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	0
+flush tables;
+create table t3(
+id int not null primary key,
+name char(20), a int, b float, c char(24)
+) engine=ndb;
+ERROR 42S01: Table 't3' already exists
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	1
+SHOW TABLES FROM test;
+Tables_in_test
+create table IF NOT EXISTS t3(
+id int not null primary key,
+id2 int not null,
+name char(20)
+) engine=ndb;
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	2
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
+  `id` int(11) NOT NULL default '0',
+  `name` char(255) default NULL,
+  PRIMARY KEY  (`id`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+select * from t3;
+id	name
+1	Explorer
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	2
+drop table t3;
+flush status;
+create table t4(
+id int not null primary key,
+name char(27)
+) engine=ndb;
+insert into t4 values (1, "Automatic");
+select * from t4;
+id	name
+1	Automatic
+select * from t4;
+ERROR HY000: Got error 284 'Table not defined in transaction coordinator' from NDBCLUSTER
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	0
+drop table t4;
+flush tables;
+show tables;
+Tables_in_test
+select * from t4;
+ERROR 42S02: Table 'test.t4' doesn't exist
+flush status;
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	0
+create table t5(
+id int not null primary key,
+name char(200)
+) engine=ndb;
+insert into t5 values (1, "Magnus");
+select * from t5;
+id	name
+1	Magnus
+ALTER TABLE t5 ADD COLUMN adress char(255) FIRST;
+select * from t5;
+adress	id	name
+NULL	1	Magnus
+flush table t5;
+select * from t5;
+adress	id	name
+NULL	1	Magnus
+insert into t5 values 
+("Adress for record 2", 2, "Carl-Gustav"), 
+("Adress for record 3", 3, "Karl-Emil");
+update t5 set name="Bertil" where id = 2;
+select * from t5 order by id;
+adress	id	name
+NULL	1	Magnus
+Adress for record 2	2	Bertil
+Adress for record 3	3	Karl-Emil
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	0
+drop table t5;
+flush status;
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	0
+create table t6(
+id int not null primary key,
+name char(20)
+) engine=ndb;
+insert into t6 values (1, "Magnus");
+select * from t6;
+id	name
+1	Magnus
+ALTER TABLE t6 ADD COLUMN adress char(255) FIRST;
+select * from t6;
+adress	id	name
+NULL	1	Magnus
+flush table t6;
+select * from t6;
+adress	id	name
+NULL	1	Magnus
+insert into t6 values 
+("Adress for record 2", 2, "Carl-Gustav"), 
+("Adress for record 3", 3, "Karl-Emil");
+update t6 set name="Bertil" where id = 2;
+select * from t6 order by id;
+adress	id	name
+NULL	1	Magnus
+Adress for record 2	2	Bertil
+Adress for record 3	3	Karl-Emil
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	0
+drop table t6;
+CREATE TABLE t9 (
+a int NOT NULL PRIMARY KEY,
+b int
+) engine=ndb;
+insert t9 values(1, 2), (2,3), (3, 4), (4, 5);
diff --git a/mysql-test/r/ndb_autodiscover2.result b/mysql-test/r/ndb_autodiscover2.result
new file mode 100644
index 00000000000..cafaf4dce6f
--- /dev/null
+++ b/mysql-test/r/ndb_autodiscover2.result
@@ -0,0 +1,10 @@
+select * from t9;
+a	b
+2	3
+4	5
+3	4
+1	2
+show status like 'handler_discover%';
+Variable_name	Value
+Handler_discover	1
+drop table t9;
diff --git a/mysql-test/t/ndb_autodiscover.test b/mysql-test/t/ndb_autodiscover.test
new file mode 100644
index 00000000000..ddd20632a09
--- /dev/null
+++ b/mysql-test/t/ndb_autodiscover.test
@@ -0,0 +1,303 @@
+-- source include/have_ndb.inc
+
+--disable_warnings
+drop table if exists t1,t2,t3,t4,t5,t6,t9;
+--enable_warnings
+
+################################################
+# Test that a table that does not exist as a 
+# frm file on disk can be "discovered" from a 
+# connected NDB Cluster
+#
+
+flush status;
+
+#
+# Test discover + SELECT
+#
+
+create table t1(
+  id int not null primary key,
+  name char(20)
+) engine=ndb;
+
+insert into t1 values(1, "Autodiscover");
+flush tables;
+system rm var/master-data/test/t1.frm ;
+select * from t1;
+show status like 'handler_discover%';
+
+#
+# Test discover + INSERT
+#
+
+flush tables;
+system rm var/master-data/test/t1.frm ;
+insert into t1 values (2, "Auto 2");
+show status like 'handler_discover%';
+insert into t1 values (3, "Discover 3");
+show status like 'handler_discover%';
+flush tables;
+system rm var/master-data/test/t1.frm ;
+select * from t1;
+show status like 'handler_discover%';
+
+#
+# Test discover + UPDATE
+#
+
+flush tables;
+system rm var/master-data/test/t1.frm ;
+update t1 set name="Autodiscover" where id = 2;
+show status like 'handler_discover%';
+select * from t1 order by name;
+show status like 'handler_discover%';
+
+#
+# Test discover + DELETE
+#
+
+flush tables;
+system rm var/master-data/test/t1.frm ;
+delete from  t1 where id = 3;
+select * from t1;
+show status like 'handler_discover%';
+
+drop table t1;
+
+
+
+######################################################
+# Test that a table that is outdated on disk
+# can be "discovered" from a connected NDB Cluster
+#
+
+flush status;
+
+create table t2(
+  id int not null primary key,
+  name char(22)
+) engine=ndb;
+insert into t2 values (1, "Discoverer");
+select * from t2;
+show status like 'handler_discover%';
+flush tables;
+
+# Modify the frm file on disk
+system echo "blaj" >> var/master-data/test/t2.frm ;
+select * from t2;
+
+show status like 'handler_discover%';
+
+drop table t2;
+
+
+##################################################
+# Test that a table that already exists in NDB 
+# is only discovered if CREATE TABLE IF NOT EXISTS 
+# is used
+#
+
+flush status;
+
+create table t3(
+  id int not null primary key,
+  name char(255)
+) engine=ndb;
+insert into t3 values (1, "Explorer");
+select * from t3;
+show status like 'handler_discover%';
+flush tables;
+
+# Remove the frm file from disk
+system rm var/master-data/test/t3.frm ;
+
+--error 1050
+create table t3(
+  id int not null primary key,
+  name char(20), a int, b float, c char(24)
+) engine=ndb;
+
+# The table shall not have been discovered since
+# IF NOT EXISTS wasn't specified
+
+show status like 'handler_discover%';
+SHOW TABLES FROM test;
+
+# now it should be discovered
+create table IF NOT EXISTS t3(
+  id int not null primary key,
+  id2 int not null,
+  name char(20)
+) engine=ndb;
+
+# NOTE! the table called t3 have now been updated to 
+# use the same frm as in NDB, thus it's not certain that 
+# the table schema is the same as was stated in the 
+# CREATE TABLE statement above
+
+show status like 'handler_discover%';
+
+SHOW CREATE TABLE t3;
+
+select * from t3;
+show status like 'handler_discover%';
+
+drop table t3;
+
+#######################################################
+# Test that a table that already exists as frm file
+# but not in NDB can be deleted from disk.
+#
+
+flush status;
+
+create table t4(
+  id int not null primary key,
+  name char(27)
+) engine=ndb;
+insert into t4 values (1, "Automatic");
+select * from t4;
+
+# Remove the table from NDB
+#system drop_tab -c "$NDB_CONNECTSTRING2" -d test t4 > /dev/null ;
+system drop_tab -c "host=localhost:2200;nodeid=5" -d test t4 > /dev/null ;
+
+--error 1296
+select * from t4;
+
+show status like 'handler_discover%';
+drop table t4;
+flush tables;
+show tables;
+--error 1146
+select * from t4;
+
+
+#########################################################
+# Test that a table that has been changed in NDB 
+# since it's been opened will be refreshed and discovered
+# again
+#
+
+flush status;
+
+show status like 'handler_discover%';
+
+create table t5(
+  id int not null primary key,
+  name char(200)
+) engine=ndb;
+insert into t5 values (1, "Magnus");
+select * from t5;
+
+# Ugly trick to change version of the table in NDB
+# Requires nodeid=5 to be defined and not used
+# Until ALTER TABLE works
+#system copy_tab -c "$NDB_CONNECTSTRING2" -d test t1 t1_copy > /dev/null ;
+#system drop_tab -c "$NDB_CONNECTSTRING2" -d test t1 > /dev/null ;
+#system copy_tab -c "$NDB_CONNECTSTRING2" -d test t1_copy t1 > /dev/null ;
+#system drop_tab -c "$NDB_CONNECTSTRING2" -d test t1_copy > /dev/null ;
+
+ALTER TABLE t5 ADD COLUMN adress char(255) FIRST;
+
+# The follwing select will exit with 
+# 1030 Got error 241 from storage engine
+# This means it has detected that the schema version of the meta data
+# cached locally in NdbApi is not the same as in the Dictionary of NDB.
+# The user has to resolve this problem by performing a FLUSH TABLE tabname
+#MASV--error 1030
+select * from t5;
+
+# The application/user is required to call FLUSH TABLE when error 241 is 
+# returned. This is a workaround and will in the future be done 
+# automatically by the server
+flush table t5;
+
+select * from t5;
+insert into t5 values 
+ ("Adress for record 2", 2, "Carl-Gustav"), 
+ ("Adress for record 3", 3, "Karl-Emil");
+update t5 set name="Bertil" where id = 2;
+select * from t5 order by id;
+
+show status like 'handler_discover%';
+
+drop table t5;
+
+
+################################################################
+# Test that a table that has been changed with ALTER TABLE
+# can be used from the same thread
+#
+
+flush status;
+
+show status like 'handler_discover%';
+
+create table t6(
+  id int not null primary key,
+  name char(20)
+) engine=ndb;
+insert into t6 values (1, "Magnus");
+select * from t6;
+
+# Ugly trick to change version of the table in NDB
+# Requires nodeid=5 to be defined and not used
+# Until ALTER TABLE works
+#system copy_tab -c "$NDB_CONNECTSTRING2" -d test t6 t6_copy > /dev/null ;
+#system drop_tab -c "$NDB_CONNECTSTRING2" -d test t6 > /dev/null ;
+#system copy_tab -c "$NDB_CONNECTSTRING2" -d test t6_copy t6 > /dev/null ;
+#system drop_tab -c "$NDB_CONNECTSTRING2" -d test t6_copy > /dev/null ;
+
+ALTER TABLE t6 ADD COLUMN adress char(255) FIRST;
+
+# The follwing select will exit with 
+# 1030 Got error 241 from storage engine
+# This means it has detected that the schema version of the meta data
+# cached locally in NdbApi is not the same as in the Dictionary of NDB.
+# The user has to resolve this problem by performing a FLUSH TABLE tabname
+#MASV--error 1030
+select * from t6;
+
+# The application/user is required to call FLUSH TABLE when error 241 is 
+# returned. This is a workaround and will in the future be done 
+# automatically by the server
+flush table t6;
+
+select * from t6;
+insert into t6 values 
+ ("Adress for record 2", 2, "Carl-Gustav"), 
+ ("Adress for record 3", 3, "Karl-Emil");
+update t6 set name="Bertil" where id = 2;
+select * from t6 order by id;
+
+show status like 'handler_discover%';
+
+drop table t6;
+
+######################################################
+# Simple test to show use of discover on startup
+# Note! This should always be the last step in this 
+# file, the table t9 will be used and dropped 
+# by ndb_autodiscover2
+#
+
+CREATE TABLE t9 (
+  a int NOT NULL PRIMARY KEY,
+  b int
+) engine=ndb;
+
+insert t9 values(1, 2), (2,3), (3, 4), (4, 5);
+
+#Don't drop the table, instead remove the frm file
+system rm var/master-data/test/t9.frm ;
+
+# Now leave test case, when ndb_autodiscover2 will  run, this 
+# MySQL Server will have been restarted because it has a 
+# ndb_autodiscover2-master.opt file. And thus the table should 
+# have been discovered by the "discover on startup" function.
+
+#TODO
+#SLECT * FROM t1, t2, t4;
+#handler discover 3;
diff --git a/mysql-test/t/ndb_autodiscover2-master.opt b/mysql-test/t/ndb_autodiscover2-master.opt
new file mode 100644
index 00000000000..e0d075c3fbd
--- /dev/null
+++ b/mysql-test/t/ndb_autodiscover2-master.opt
@@ -0,0 +1 @@
+--skip-external-locking
diff --git a/mysql-test/t/ndb_autodiscover2.test b/mysql-test/t/ndb_autodiscover2.test
new file mode 100644
index 00000000000..2aae2508a2b
--- /dev/null
+++ b/mysql-test/t/ndb_autodiscover2.test
@@ -0,0 +1,13 @@
+-- source include/have_ndb.inc
+
+#
+# Simple test to show use of discover on startup
+# The previous step has simply removed the frm file
+# from disk, but left the table in NDB
+#
+select * from t9;
+
+# handler_discover should be zero
+show status like 'handler_discover%';
+
+drop table t9;