1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
|
#
# Test for the optimizer cost model
#
# Validate that the optimizer cost configuration tables exists and
# has the expected content.
#
# Table: server_cost
#
# Mask out the content of the last_update column
--replace_column 3 #
SELECT * FROM mysql.server_cost;
# Check that it has the expected index defined
# The index should be:
#
# PRIMARY (cost_name)
#
# We do not care about the cardinality of this index since it can vary
--replace_column 7 #
SHOW INDEX FROM mysql.server_cost;
#
# Table: engine_cost
#
# Mask out the content of the last_update column
--replace_column 5 #
SELECT * FROM mysql.engine_cost;
# Check that it has the expected index defined
# The index should be a unique index on:
#
# PRIMARY (cost_name, engine_name, device_type)
#
# We do not care about the cardinality of this index since it can vary
--replace_column 7 #
SHOW INDEX FROM mysql.engine_cost;
#
# Test of updating cost constants in server_cost
#
# 1. Updating an existing cost constant
#
# Change the value
UPDATE mysql.server_cost
SET cost_value=0.1
WHERE cost_name="row_evaluate_cost";
--replace_column 3 #
SELECT *
FROM mysql.server_cost
WHERE cost_name="row_evaluate_cost";
# Reset it to its default value
UPDATE mysql.server_cost
SET cost_value=DEFAULT
WHERE cost_name="row_evaluate_cost";
--replace_column 3 #
SELECT *
FROM mysql.server_cost
WHERE cost_name="row_evaluate_cost";
#
# 2. Insert a new cost constant
#
INSERT INTO mysql.server_cost
VALUES ("lunch_cost", DEFAULT, CURRENT_TIMESTAMP, "Lunch is important", DEFAULT);
--replace_column 3 #
SELECT * FROM mysql.server_cost;
DELETE FROM mysql.server_cost
WHERE cost_name="lunch_cost";
#
# 3. Try to insert an already existing cost constant.
#
--error ER_DUP_ENTRY
INSERT INTO mysql.server_cost
VALUES ("row_evaluate_cost", DEFAULT, CURRENT_TIMESTAMP, "Faster CPU", DEFAULT);
#
# 4. Insert an entry with the same name as an existing cost constant
# but in upper case.
#
--error ER_DUP_ENTRY
INSERT INTO mysql.server_cost
VALUES ("ROW_EVALUATE_COST", DEFAULT, CURRENT_TIMESTAMP, "Faster CPU", DEFAULT);
--replace_column 3 #
SELECT * FROM mysql.server_cost;
#
# Test of updating cost constants in engine_cost
#
# 1. Updating an existing cost constant
#
# Change the value
UPDATE mysql.engine_cost
SET cost_value=0.1
WHERE cost_name="io_block_read_cost";
--replace_column 5 #
SELECT *
FROM mysql.engine_cost
WHERE cost_name="io_block_read_cost";
# Reset it to its default value
UPDATE mysql.engine_cost
SET cost_value=DEFAULT
WHERE cost_name="io_block_read_cost";
--replace_column 5 #
SELECT *
FROM mysql.engine_cost
WHERE cost_name="io_block_read_cost";
#
# 2. Insert some new cost constant
#
INSERT INTO mysql.engine_cost
VALUES ("InnoDB", 2, "lunch_cost1", DEFAULT, CURRENT_TIMESTAMP, "Lunch 1", DEFAULT),
("InnoDB", 2, "lunch_cost2", DEFAULT, CURRENT_TIMESTAMP, "Lunch 2", DEFAULT);
--replace_column 5 #
SELECT * FROM mysql.engine_cost;
DELETE FROM mysql.engine_cost
WHERE cost_name LIKE "lunch_cost%";
#
# 3. Try to insert an already existing cost constant
# (this should fail due to the primary key)
#
INSERT INTO mysql.engine_cost
VALUES ("default", 0, "lunch_cost", DEFAULT, CURRENT_TIMESTAMP, "Lunch", DEFAULT);
--error ER_DUP_ENTRY
INSERT INTO mysql.engine_cost
VALUES ("default", 0, "lunch_cost", DEFAULT, CURRENT_TIMESTAMP, "Lunch", DEFAULT);
--replace_column 5 #
SELECT * FROM mysql.engine_cost;
DELETE FROM mysql.engine_cost
WHERE cost_name="lunch_cost";
#
# 5. Insert an entry with the same name as an existing cost constant
# but in upper case.
# (the collation on the table should prevent this from being accepted)
#
--error ER_DUP_ENTRY
INSERT INTO mysql.engine_cost
VALUES ("default", 0, "IO_BLOCK_READ_COST", DEFAULT, CURRENT_TIMESTAMP,
"Lunch", DEFAULT);
--replace_column 5 #
SELECT * FROM mysql.engine_cost;
#
# Test that the last_update column in the two tables are automatically
# updated with a new time stamp value when updating the content of
# a row
#
CREATE TABLE server_cost_tmp (
cost_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP
);
CREATE TABLE engine_cost_tmp (
cost_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP
);
# Copy the last_update time stamp values form the cost tables
INSERT INTO server_cost_tmp
SELECT cost_name, last_update FROM mysql.server_cost;
INSERT INTO engine_cost_tmp
SELECT cost_name, last_update FROM mysql.engine_cost;
# Need to do a sleep to ensure that we get a new distinct time stamp
--sleep 1
#
# 1. Test server_cost table
#
# Update on entry in the server_cost table
UPDATE mysql.server_cost
SET cost_value=0.1
WHERE cost_name="row_evaluate_cost";
# Validate that this has gotten a new time stamp and all others have the same
# This query should return "row_evaluate_cost"
SELECT mysql.server_cost.cost_name
FROM mysql.server_cost JOIN server_cost_tmp
ON mysql.server_cost.cost_name = server_cost_tmp.cost_name
WHERE mysql.server_cost.last_update > server_cost_tmp.last_update;
# Reset the cost value
UPDATE mysql.server_cost
SET cost_value=DEFAULT
WHERE cost_name="row_evaluate_cost";
#
# 2. Test engine_cost table
#
# Update on entry in the engine_cost table
UPDATE mysql.engine_cost
SET cost_value=2.0
WHERE cost_name="io_block_read_cost";
# Validate that this has gotten a new time stamp and all others have the same
# This query should return "io_block_read_cost"
SELECT mysql.engine_cost.cost_name
FROM mysql.engine_cost JOIN engine_cost_tmp
ON mysql.engine_cost.cost_name = engine_cost_tmp.cost_name
WHERE mysql.engine_cost.last_update > engine_cost_tmp.last_update;
# Reset the cost value
UPDATE mysql.engine_cost
SET cost_value=DEFAULT
WHERE cost_name="io_block_read_cost";
DROP TABLE server_cost_tmp, engine_cost_tmp;
--echo
--echo WL#10128: Add defaults column to optimizer cost tables
--echo
--echo Verify that default values are defined for all cost constants
SELECT COUNT(*) FROM mysql.server_cost WHERE default_value IS NULL;
SELECT COUNT(*) FROM mysql.engine_cost WHERE default_value IS NULL;
--echo Verify that default_value columns can not be updated
--error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
UPDATE mysql.server_cost SET default_value = 1.0;
--error ER_NON_DEFAULT_VALUE_FOR_GENERATED_COLUMN
UPDATE mysql.engine_cost SET default_value = 1.0;
|
