1# 2# WL#12731: Improve database synchronization during mysqld startup 3# 4# Setup - create few databases and tables so that the alter and drop statements can be tested. 5create database alter_db1 charset utf8mb4; 6create table alter_db1.t1 (a int); 7create database drop_db1; 8create database drop_db2; 9create table drop_db2.t1(a int) engine ndb; 10create database drop_db3; 11create table drop_db3.t1(a int); 12create database restore_db1; 13create table restore_db1.t1(a int) engine ndb; 14create database restore_db2 charset latin1; 15create table restore_db2.t1(a int) engine ndb; 16create database restore_db3 charset latin1; 17create table restore_db3.t1(a int) engine ndb; 18# Take a backup of the current setup 19# Drop restore_db1,restore_db2 to check how the server startup handles database restores 20drop database restore_db1; 21drop database restore_db2; 22# 23# Shutdown Server1 24# 25# 26# Run DDLs on Server2 when server1 is down 27# 28# Create a database 29create database create_db; 30# Add tables to it 31create table create_db.t1(a int) engine ndb; 32create table create_db.t2(a int); 33# Alter already existing databases 34alter database alter_db1 charset latin1; 35# Create and alter a database 36create database alter_db2 charset utf8mb4; 37alter database alter_db2 charset latin1; 38# Drop databases 39drop database drop_db1; 40drop database drop_db2; 41drop database drop_db3; 42# Drop database restore_db3 and then restore restore_db1, restore_db2, restore_db3 43drop database restore_db3; 44# 45# Startup server1 46# 47# 48# Now check that the databases are in expected states 49# 50# Verify that create_db has been discovered 51show create database create_db; 52Database Create Database 53create_db CREATE DATABASE `create_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ 54# Verify t1 has been created. t2 won't be as it is local to Server 2. 55show tables from create_db; 56Tables_in_create_db 57t1 58show create table create_db.t1; 59Table Create Table 60t1 CREATE TABLE `t1` ( 61 `a` int DEFAULT NULL 62) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 63show create table create_db.t2; 64ERROR 42S02: Table 'create_db.t2' doesn't exist 65# Verify that the database alter_db1 were properly altered 66show create database alter_db1; 67Database Create Database 68alter_db1 CREATE DATABASE `alter_db1` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!80016 DEFAULT ENCRYPTION='N' */ 69# Check that alter_db2 is created and altered 70show create database alter_db2; 71Database Create Database 72alter_db2 CREATE DATABASE `alter_db2` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!80016 DEFAULT ENCRYPTION='N' */ 73# Check the states of databases that were dropped 74# Databases drop_db1 and drop_db2 should be dropped. 75show create database drop_db1; 76ERROR 42000: Unknown database 'drop_db1' 77show create database drop_db2; 78ERROR 42000: Unknown database 'drop_db2' 79# Database drop_db3 should be preserved. 80show create database drop_db3; 81Database Create Database 82drop_db3 CREATE DATABASE `drop_db3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ 83# Check that the databases and tables that were 84# dropped and restored are now discovered 85show create database restore_db1; 86Database Create Database 87restore_db1 CREATE DATABASE `restore_db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ 88show create table restore_db1.t1; 89Table Create Table 90t1 CREATE TABLE `t1` ( 91 `a` int DEFAULT NULL 92) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 93# restore_db2 was dropped when both mysqlds were alive 94# so it would have been discovered with default charset 95show create database restore_db2; 96Database Create Database 97restore_db2 CREATE DATABASE `restore_db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ 98show create table restore_db2.t1; 99Table Create Table 100t1 CREATE TABLE `t1` ( 101 `a` int DEFAULT NULL 102) ENGINE=ndbcluster DEFAULT CHARSET=latin1 103# restore_db3 was dropped when server1 was down 104# when server1 came up, it should have been 105# preserved the database with previous setting 106show create database restore_db3; 107Database Create Database 108restore_db3 CREATE DATABASE `restore_db3` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!80016 DEFAULT ENCRYPTION='N' */ 109show create table restore_db3.t1; 110Table Create Table 111t1 CREATE TABLE `t1` ( 112 `a` int DEFAULT NULL 113) ENGINE=ndbcluster DEFAULT CHARSET=latin1 114# 115# Cleanup 116# 117drop table create_db.t2; 118drop table drop_db3.t1; 119drop database create_db; 120drop database alter_db1; 121drop database alter_db2; 122drop database drop_db3; 123drop database restore_db1; 124drop database restore_db2; 125drop database restore_db3; 126