-- source include/have_ndb.inc -- source include/not_embedded.inc --disable_warnings drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; --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; --remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm select * from t1; show status like 'handler_discover%'; # # Test discover + INSERT # flush tables; --remove_file $MYSQLTEST_VARDIR/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; --remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm select * from t1 order by id; show status like 'handler_discover%'; # # Test discover + UPDATE # flush tables; --remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm update t1 set name="Autodiscover" where id = 2; show status like 'handler_discover%'; select * from t1 order by id; show status like 'handler_discover%'; # # Test discover + DELETE # flush tables; --remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm delete from t1 where id = 3; select * from t1 order by id; 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" >> $MYSQLTEST_VARDIR/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 --remove_file $MYSQLTEST_VARDIR/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%'; # 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 in NDB # is discovered when SHOW TABLES # is used # flush status; create table t7( id int not null primary key, name char(255) ) engine=ndb; create table t6( id int not null primary key, name char(255) ) engine=MyISAM; insert into t7 values (1, "Explorer"); insert into t6 values (2, "MyISAM table"); select * from t7; show status like 'handler_discover%'; # Remove the frm file from disk flush tables; --remove_file $MYSQLTEST_VARDIR/master-data/test/t7.frm show tables from test; show status like 'handler_discover%'; # Remove the frm file from disk again flush tables; --remove_file $MYSQLTEST_VARDIR/master-data/test/t7.frm --replace_column 6 # 7 # 8 # 9 # 12 # 13 # 15 # 18 # show table status; show status like 'handler_discover%'; drop table t6, t7; ####################################################### # Test that a table that has been dropped from NDB # but still exists on disk, get a consistent error message # saying "No such table existed" # 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 exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t4 >> $NDB_TOOLS_OUTPUT ; # # Test that correct error is returned --error 1146 select * from t4; --error 1146 select * from t4; show status like 'handler_discover%'; --error 1051 drop table t4; 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 exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t4 >> $NDB_TOOLS_OUTPUT ; --error 1146 select * from t4; drop table if exists t4; # Test that dropping a table that does not exists # on disk or in NDB gives same result as above --error 1051 drop table t5; drop table if exists t5; ####################################################### # Test that a table that has been dropped from NDB # but still exists on disk is deleted from disk # when SHOW TABLES is called # flush status; create table t4( id int not null primary key, id2 int, name char(27) ) engine=ndb; insert into t4 values (1, 76, "Automatic2"); select * from t4; flush tables; # Remove the table from NDB system exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t4 >> $NDB_TOOLS_OUTPUT ; SHOW TABLES; --error 1146 select * from t4; ####################################################### # Test SHOW TABLES ability to detect new and delete old # tables. Test all at once using many tables # flush status; # Create tables create table t1(id int) engine=ndbcluster; create table t2(id int, b char(255)) engine=myisam; create table t3(id int, c char(255)) engine=ndbcluster; create table t4(id int) engine=myisam; create table t5(id int, d char(56)) engine=ndbcluster; create table t6(id int) engine=ndbcluster; create table t7(id int) engine=ndbcluster; create table t8(id int, e char(34)) engine=myisam; create table t9(id int) engine=myisam; # Populate tables insert into t2 values (2, "myisam table 2"); insert into t3 values (3, "ndb table 3"); insert into t5 values (5, "ndb table 5"); insert into t6 values (6); insert into t8 values (8, "myisam table 8"); insert into t9 values (9); # Remove t3, t5 from NDB system exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t3 >> $NDB_TOOLS_OUTPUT ; system exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t5 >> $NDB_TOOLS_OUTPUT ; # Remove t6, t7 from disk --remove_file $MYSQLTEST_VARDIR/master-data/test/t6.frm --remove_file $MYSQLTEST_VARDIR/master-data/test/t7.frm SHOW TABLES; select * from t6; select * from t7; show status like 'handler_discover%'; drop table t1, t2, t4, t6, t7, t8, t9; ####################################################### # Test SHOW TABLES LIKE ability to detect new and delete old # tables. Test all at once using many tables. # flush status; # Create tables create table t1(id int) engine=ndbcluster; create table t2(id int, b char(255)) engine=myisam; create table t3(id int, c char(255)) engine=ndbcluster; create table t4(id int) engine=myisam; create table t5(id int, d char(56)) engine=ndbcluster; create table t6(id int) engine=ndbcluster; create table t7(id int) engine=ndbcluster; create table t8(id int, e char(34)) engine=myisam; create table t9(id int) engine=myisam; # Populate tables insert into t2 values (2, "myisam table 2"); insert into t3 values (3, "ndb table 3"); insert into t5 values (5, "ndb table 5"); insert into t6 values (6); insert into t8 values (8, "myisam table 8"); insert into t9 values (9); # Remove t3, t5 from NDB system exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t3 > /dev/null ; system exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test t5 > /dev/null ; # Remove t6, t7 from disk --remove_file $MYSQLTEST_VARDIR/master-data/test/t6.frm --remove_file $MYSQLTEST_VARDIR/master-data/test/t7.frm SHOW TABLES LIKE 't6'; show status like 'handler_discover%'; # Check that t3 or t5 can't be created # frm files for these tables is stilll on disk --error 1050 create table t3(a int); --error 1050 create table t5(a int); SHOW TABLES LIKE 't%'; show status like 'handler_discover%'; drop table t1, t2, t4, t6, t7, t8, t9; ###################################################### # Test that several tables can be discovered when # one statement access several table at once. # flush status; # Create tables create table t1(id int) engine=ndbcluster; create table t2(id int, b char(255)) engine=ndbcluster; create table t3(id int, c char(255)) engine=ndbcluster; create table t4(id int) engine=myisam; # Populate tables insert into t1 values (1); insert into t2 values (2, "table 2"); insert into t3 values (3, "ndb table 3"); insert into t4 values (4); # Remove t1, t2, t3 from disk --remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm --remove_file $MYSQLTEST_VARDIR/master-data/test/t2.frm --remove_file $MYSQLTEST_VARDIR/master-data/test/t3.frm flush tables; # Select from the table which only exists in NDB. select * from t1, t2, t3, t4; # 3 table should have been discovered show status like 'handler_discover%'; drop table t1, t2, t3, 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; ALTER TABLE t5 ADD COLUMN adress char(255) FIRST; 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; ALTER TABLE t6 ADD COLUMN adress char(255) FIRST; 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; ##################################################### # Test that only tables in the current database shows # up in SHOW TABLES # show tables; create table t1 (a int,b longblob) engine=ndb; show tables; create database test2; use test2; show tables; --error 1146 select * from t1; create table t2 (b int,c longblob) engine=ndb; use test; select * from t1; show tables; drop table t1; use test2; drop table t2; drop database test2; use test; ######################################################### # Bug#8035 # mysqld would segfault on second select * before bug was fixed # --disable_warnings drop database if exists test_only_ndb_tables; --enable_warnings create database test_only_ndb_tables; use test_only_ndb_tables; create table t1 (a int primary key) engine=ndb; select * from t1; --exec $NDB_MGM --no-defaults -e "all restart -n" > /dev/null --exec $NDB_TOOLS_DIR/ndb_waiter --no-defaults --not-started > /dev/null --error 1015 select * from t1; --exec $NDB_MGM --no-defaults -e "all start" > /dev/null --exec $NDB_TOOLS_DIR/ndb_waiter --no-defaults > /dev/null use test; drop database test_only_ndb_tables; ##################################################### # Test that it's not possible to create tables # with same name as NDB internal tables # This will also test that it's not possible to create # a table with tha same name as a table that can't be # discovered( for example a table created via NDBAPI) # Test disabled since it doesn't work on case insensitive systems #--error 1050 #CREATE TABLE sys.SYSTAB_0 (a int); #--error 1105 #select * from sys.SYSTAB_0; #CREATE TABLE IF NOT EXISTS sys.SYSTAB_0 (a int); #show warnings; #--error 1105 #select * from sys.SYSTAB_0; #--error 1051 #drop table sys.SYSTAB_0; #drop table IF EXISTS sys.SYSTAB_0; ###################################################### # 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 --remove_file $MYSQLTEST_VARDIR/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. create table t10 ( a int not null primary key, b blob ) engine=ndb; insert into t10 values (1, 'kalle'); --exec $NDB_TOOLS_DIR/ndb_drop_table --no-defaults -d test `$NDB_TOOLS_DIR/ndb_show_tables --no-defaults | grep BLOB | while read a b c d e f g; do echo $g; done` >> $NDB_TOOLS_OUTPUT 2>&1 || true # End of 4.1 tests