| 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. |
| 5 |
create database alter_db1 charset utf8mb4; |
| 6 |
create table alter_db1.t1 (a int); |
| 7 |
create database drop_db1; |
| 8 |
create database drop_db2; |
| 9 |
create table drop_db2.t1(a int) engine ndb; |
| 10 |
create database drop_db3; |
| 11 |
create table drop_db3.t1(a int); |
| 12 |
create database restore_db1; |
| 13 |
create table restore_db1.t1(a int) engine ndb; |
| 14 |
create database restore_db2 charset latin1; |
| 15 |
create table restore_db2.t1(a int) engine ndb; |
| 16 |
create database restore_db3 charset latin1; |
| 17 |
create 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 |
| 20 |
drop database restore_db1; |
| 21 |
drop database restore_db2; |
| 22 |
# |
| 23 |
# Shutdown Server1 |
| 24 |
# |
| 25 |
# |
| 26 |
# Run DDLs on Server2 when server1 is down |
| 27 |
# |
| 28 |
# Create a database |
| 29 |
create database create_db; |
| 30 |
# Add tables to it |
| 31 |
create table create_db.t1(a int) engine ndb; |
| 32 |
create table create_db.t2(a int); |
| 33 |
# Alter already existing databases |
| 34 |
alter database alter_db1 charset latin1; |
| 35 |
# Create and alter a database |
| 36 |
create database alter_db2 charset utf8mb4; |
| 37 |
alter database alter_db2 charset latin1; |
| 38 |
# Drop databases |
| 39 |
drop database drop_db1; |
| 40 |
drop database drop_db2; |
| 41 |
drop database drop_db3; |
| 42 |
# Drop database restore_db3 and then restore restore_db1, restore_db2, restore_db3 |
| 43 |
drop 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 |
| 51 |
show create database create_db; |
| 52 |
Database Create Database |
| 53 |
create_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. |
| 55 |
show tables from create_db; |
| 56 |
Tables_in_create_db |
| 57 |
t1 |
| 58 |
show create table create_db.t1; |
| 59 |
Table Create Table |
| 60 |
t1 CREATE TABLE `t1` ( |
| 61 |
`a` int DEFAULT NULL |
| 62 |
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 63 |
show create table create_db.t2; |
| 64 |
ERROR 42S02: Table 'create_db.t2' doesn't exist |
| 65 |
# Verify that the database alter_db1 were properly altered |
| 66 |
show create database alter_db1; |
| 67 |
Database Create Database |
| 68 |
alter_db1 CREATE DATABASE `alter_db1` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
| 69 |
# Check that alter_db2 is created and altered |
| 70 |
show create database alter_db2; |
| 71 |
Database Create Database |
| 72 |
alter_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. |
| 75 |
show create database drop_db1; |
| 76 |
ERROR 42000: Unknown database 'drop_db1' |
| 77 |
show create database drop_db2; |
| 78 |
ERROR 42000: Unknown database 'drop_db2' |
| 79 |
# Database drop_db3 should be preserved. |
| 80 |
show create database drop_db3; |
| 81 |
Database Create Database |
| 82 |
drop_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 |
| 85 |
show create database restore_db1; |
| 86 |
Database Create Database |
| 87 |
restore_db1 CREATE DATABASE `restore_db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
| 88 |
show create table restore_db1.t1; |
| 89 |
Table Create Table |
| 90 |
t1 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 |
| 95 |
show create database restore_db2; |
| 96 |
Database Create Database |
| 97 |
restore_db2 CREATE DATABASE `restore_db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
| 98 |
show create table restore_db2.t1; |
| 99 |
Table Create Table |
| 100 |
t1 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 |
| 106 |
show create database restore_db3; |
| 107 |
Database Create Database |
| 108 |
restore_db3 CREATE DATABASE `restore_db3` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
| 109 |
show create table restore_db3.t1; |
| 110 |
Table Create Table |
| 111 |
t1 CREATE TABLE `t1` ( |
| 112 |
`a` int DEFAULT NULL |
| 113 |
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
| 114 |
# |
| 115 |
# Cleanup |
| 116 |
# |
| 117 |
drop table create_db.t2; |
| 118 |
drop table drop_db3.t1; |
| 119 |
drop database create_db; |
| 120 |
drop database alter_db1; |
| 121 |
drop database alter_db2; |
| 122 |
drop database drop_db3; |
| 123 |
drop database restore_db1; |
| 124 |
drop database restore_db2; |
| 125 |
drop database restore_db3; |