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
|
#
# Test that provides code coverage for the code that checks and handles
# entries in the cost constant tables that has invalid values.
# This will cause warnings to be written to the MySQL error log.
#
# The reason for having a separte test file for this is that we
# need to suppress warnings from the error log.
#
# Before starting the test check that the cost constant tables exists and
# have the expected content.
#
# Table: server_cost
#
# Mask out the content of the last_update column
--replace_column 3 #
SELECT * FROM mysql.server_cost;
#
# Table: engine_cost
#
# Mask out the content of the last_update column
--replace_column 5 #
SELECT * FROM mysql.engine_cost;
#
# TEST: correct warning when one of the cost constant tables are missing
#
# This will cause a warning in the error log that needs to be ignored
--disable_query_log
call mtr.add_suppression('Failed to open optimizer cost constant tables');
--enable_query_log
# Rename one of the tables
RENAME TABLE mysql.engine_cost TO mysql.engine_cost_renamed;
# Restart the server to check that it handles the missing cost constant
# table
--echo "Restarting MySQL server"
--source include/restart_mysqld.inc
--echo "MySQL restarted"
# Restore the engine_cost table:
RENAME TABLE mysql.engine_cost_renamed TO mysql.engine_cost;
# Verify the content of engine_cost:
#
# Mask out the content of the last_update column
--replace_column 5 #
SELECT * FROM mysql.engine_cost;
#
# TEST: handling of unrecognized or illegal entries in the server_cost table
#
# This will cause the following warnings in the error log that needs to
# be ignored
--disable_query_log
call mtr.add_suppression('Unknown cost constant "lunch_cost" in mysql.server_cost table');
call mtr.add_suppression('Invalid value for cost constant "row_evaluate_cost" in mysql.server_cost table: -1.0');
call mtr.add_suppression('Invalid value for cost constant "key_compare_cost" in mysql.server_cost table: 0.0');
--enable_query_log
# Add an unrecognized cost constant name
INSERT INTO mysql.server_cost
VALUES ("lunch_cost", 1.0, CURRENT_TIMESTAMP, "Lunch is important", DEFAULT);
# Change an existing cost constant to have a negative cost
UPDATE mysql.server_cost
SET cost_value=-1.0
WHERE cost_name="row_evaluate_cost";
# Change an existing cost constant to have zero cost
UPDATE mysql.server_cost
SET cost_value=0.0
WHERE cost_name="key_compare_cost";
# Restart the server to check that it handles the errors in the server_cost
# table
--echo "Restarting MySQL server"
--source include/restart_mysqld.inc
--echo "MySQL restarted"
# Clean-up:
DELETE FROM mysql.server_cost
WHERE cost_name LIKE "lunch_cost%";
UPDATE mysql.server_cost
SET cost_value=NULL
WHERE cost_name="row_evaluate_cost";
UPDATE mysql.server_cost
SET cost_value=NULL
WHERE cost_name="key_compare_cost";
#
# TEST: handling of unrecognized or illegal entries in the engine_cost table
#
# This will cause the following warnings in the error log that needs to
# be ignored
--disable_query_log
call mtr.add_suppression('Invalid value for cost constant "io_block_read_cost" for "default" storage engine and device type 0 in mysql.engine_cost table: 0.0');
call mtr.add_suppression('Unknown storage engine "Falcon" in mysql.engine_cost table');
call mtr.add_suppression('Invalid device type -1 for "InnoDB" storage engine for cost constant "io_block_read_cost" in mysql.engine_cost table');
call mtr.add_suppression('Unknown cost constant "lunch_cost" in mysql.engine_cost table');
--enable_query_log
# Add an unrecognized cost constant name
INSERT INTO mysql.engine_cost
VALUES ("InnoDB", 0, "lunch_cost", 1.0, CURRENT_TIMESTAMP, "Lunch is important", DEFAULT);
# Change an existing cost constant to have zero cost
UPDATE mysql.engine_cost
SET cost_value=0.0
WHERE cost_name="io_block_read_cost";
# Add a cost constant for an unknown storage engine
INSERT INTO mysql.engine_cost
VALUES ("Falcon", 0, "io_block_read_cost", 1.0, CURRENT_TIMESTAMP, "Unknown storage engine", DEFAULT);
# Add a cost constant where the device type is illegal
INSERT INTO mysql.engine_cost
VALUES ("InnoDB", -1, "io_block_read_cost", 1.0, CURRENT_TIMESTAMP, "1 is an illegal device type", DEFAULT);
# Restart the server to check that it handles the errors in the server_cost
# table
--echo "Restarting MySQL server"
--source include/restart_mysqld.inc
--echo "MySQL restarted"
# Clean-up:
DELETE FROM mysql.engine_cost
WHERE cost_name LIKE "lunch_cost%";
UPDATE mysql.engine_cost
SET cost_value=NULL;
DELETE FROM mysql.engine_cost
WHERE device_type = -1;
DELETE FROM mysql.engine_cost
WHERE engine_name LIKE "Falcon";
# Before ending the test check that the cost constant tables still exists and
# have the expected content.
#
# Table: server_cost
#
# Mask out the content of the last_update column
--replace_column 3 #
SELECT * FROM mysql.server_cost;
#
# Table: engine_cost
#
# Mask out the content of the last_update column
--replace_column 5 #
SELECT * FROM mysql.engine_cost;
|
