Debian Codegraph @

mysql-test/suite/ndb/t/ndb_restore_conv_null.test

raw
1 --source include/have_ndb.inc
2 --source include/have_ndb_debug.inc
3 -- source suite/ndb/include/backup_restore_setup.inc
4
5 --echo Test nullable <-> not-null conversions in data restore in ndb_restore
6
7 create table nullable_no_nulls (
8 id int primary key,
9 col1 int unsigned,
10 col2 varchar(20),
11 col3 text,
12 col4 date) engine=ndb;
13
14 create table nullable_with_nulls (
15 id int primary key,
16 col1 int unsigned,
17 col2 varchar(20),
18 col3 text,
19 col4 date) engine=ndb;
20
21 create table not_nullable_no_nulls (
22 id int primary key,
23 col1 int unsigned not null,
24 col2 varchar(20) not null,
25 col3 text not null,
26 col4 date not null)engine=ndb;
27
28 --disable_query_log
29 let $i=100;
30 while ($i)
31 {
32 eval insert into nullable_no_nulls values ($i, $i, REPEAT("abcde", 4), REPEAT("XYZ", 2000), "2021-04-01");
33 eval insert into nullable_with_nulls values ($i, NULL, NULL, NULL, NULL);
34 eval insert into not_nullable_no_nulls values ($i, $i, REPEAT("abcde", 4), REPEAT("XYZ", 2000), "2021-04-01");
35 dec $i;
36 }
37 --enable_query_log
38
39 --echo # run backup
40 --source include/ndb_backup.inc
41
42 drop table nullable_no_nulls;
43 drop table nullable_with_nulls;
44 drop table not_nullable_no_nulls;
45
46 --echo # conversion from nullable -> not null with no NULLs in table
47 create table nullable_no_nulls (
48 id int primary key,
49 col1 int unsigned not null,
50 col2 varchar(20) not null,
51 col3 text not null,
52 col4 date not null)engine=ndb;
53
54 --echo # conversion from nullable -> not null with NULLs in table
55 create table nullable_with_nulls (
56 id int primary key not null,
57 col1 int unsigned not null,
58 col2 varchar(20) not null,
59 col3 text not null,
60 col4 date not null) engine=ndb;
61
62 --echo # conversion from not-null -> nullable
63 create table not_nullable_no_nulls (
64 id int primary key,
65 col1 int unsigned,
66 col2 varchar(20),
67 col3 text,
68 col4 date)engine=ndb;
69
70 --echo # cannot restore nullable->non-nullable without --lossy-conversions
71 --error 1
72 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --include-tables=test.nullable_no_nulls $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
73
74 --echo # can restore nullable->non-nullable if non-nullable col has no NULL values
75 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --include-tables=test.nullable_no_nulls --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
76 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --include-tables=test.nullable_no_nulls --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
77
78 --echo # cannot restore nullable->non-nullable if non-nullable col has NULL values
79 --error 1
80 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --include-tables=test.nullable_with_nulls --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
81
82 --echo # cannot restore not-null->non-nullable without --promote-attributes
83 --error 1
84 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --include-tables=test.not_nullable_no_nulls $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
85
86 --echo # can restore not-null->nullable
87 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --include-tables=test.not_nullable_no_nulls --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
88 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --include-tables=test.not_nullable_no_nulls --promote-attributes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
89
90 select count(*) from nullable_no_nulls where col2=REPEAT("abcde", 4) and col3=REPEAT("XYZ", 2000) and col4="2021-04-01";
91 select count(*) from nullable_with_nulls;
92 select count(*) from not_nullable_no_nulls where col2=REPEAT("abcde", 4) and col3=REPEAT("XYZ", 2000) and col4="2021-04-01";
93
94 drop table nullable_no_nulls;
95 drop table nullable_with_nulls;
96 drop table not_nullable_no_nulls;
97
98 --echo Test nullable <-> not-null conversions in log replay in ndb_restore
99
100 create table nullable (
101 id int primary key,
102 col1 int unsigned,
103 col2 varchar(20),
104 col3 text,
105 col4 date) engine=ndb;
106
107 --disable_query_log
108 let $i=100;
109 while ($i)
110 {
111 eval insert into nullable values ($i, $i, REPEAT("abcde", 4), REPEAT("XYZ", 2000), "2021-04-01");
112 dec $i;
113 }
114 --enable_query_log
115
116 --echo Cause backup stall
117 --exec $NDB_MGM -e "all error 10039"
118
119 --source suite/ndb/t/ndb_backup_nowait_start.inc
120
121 update nullable set col2=NULL;
122
123 --exec $NDB_MGM -e "all error 0"
124 --source suite/ndb/t/ndb_backup_nowait_wait.inc
125
126 drop table nullable;
127
128 --echo # conversion from nullable -> not null
129 create table nullable (
130 id int primary key,
131 col1 int unsigned not null,
132 col2 varchar(20) not null,
133 col3 text not null,
134 col4 date not null)engine=ndb;
135
136 --echo # cannot restore nullable->non-nullable if backup log sets NULL values
137 --error 1
138 --exec $NDB_RESTORE -b $the_backup_id -n 1 -r --include-tables=test.nullable --lossy-conversions $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT 2>&1
139
140 select * from nullable;
141
142 drop table nullable;
143 --source suite/ndb/include/backup_restore_cleanup.inc
144 --remove_file $NDB_TOOLS_OUTPUT