@recaptime-dev's working patches + fork for Phorge, a community fork of Phabricator. (Upstream dev and stable branches are at upstream/main and upstream/stable respectively.)
hq.recaptime.dev/wiki/Phorge
phorge
phabricator
1<?php
2
3final class PhabricatorMySQLSetupCheck extends PhabricatorSetupCheck {
4
5 public function getDefaultGroup() {
6 return self::GROUP_MYSQL;
7 }
8
9 protected function executeChecks() {
10 $refs = PhabricatorDatabaseRef::getActiveDatabaseRefs();
11 foreach ($refs as $ref) {
12 try {
13 $this->executeRefChecks($ref);
14 } catch (AphrontConnectionQueryException $ex) {
15 // If we're unable to connect to a host, just skip the checks for it.
16 // This can happen if we're restarting during a cluster incident. See
17 // T12966 for discussion.
18 }
19 }
20 }
21
22 private function executeRefChecks(PhabricatorDatabaseRef $ref) {
23 $max_allowed_packet = $ref->loadRawMySQLConfigValue('max_allowed_packet');
24
25 $host_name = $ref->getRefKey();
26
27 // This primarily supports setting the filesize limit for MySQL to 8MB,
28 // which may produce a >16MB packet after escaping.
29 $recommended_minimum = (32 * 1024 * 1024);
30 if ($max_allowed_packet < $recommended_minimum) {
31 $message = pht(
32 'On host "%s", MySQL is configured with a small "%s" (%d), which '.
33 'may cause some large writes to fail. The recommended minimum value '.
34 'for this setting is "%d".',
35 $host_name,
36 'max_allowed_packet',
37 $max_allowed_packet,
38 $recommended_minimum);
39
40 $this->newIssue('mysql.max_allowed_packet')
41 ->setName(pht('Small MySQL "%s"', 'max_allowed_packet'))
42 ->setMessage($message)
43 ->setDatabaseRef($ref)
44 ->addMySQLConfig('max_allowed_packet');
45 }
46
47 $modes = $ref->loadRawMySQLConfigValue('sql_mode');
48 $modes = explode(',', $modes);
49
50 if (!in_array('STRICT_ALL_TABLES', $modes)) {
51 $summary = pht(
52 'MySQL is not in strict mode (on host "%s"), but using strict mode '.
53 'is recommended.',
54 $host_name);
55
56 $message = pht(
57 'On database host "%s", the global "sql_mode" setting does not '.
58 'include the "STRICT_ALL_TABLES" mode. Enabling this mode is '.
59 'recommended to generally improve how MySQL handles certain errors.'.
60 "\n\n".
61 'Without this mode enabled, MySQL will silently ignore some error '.
62 'conditions, including inserts which attempt to store more data in '.
63 'a column than actually fits. This behavior is usually undesirable '.
64 'and can lead to data corruption (by truncating multibyte characters '.
65 'in the middle), data loss (by discarding the data which does not '.
66 'fit into the column), or security concerns (for example, by '.
67 'truncating keys or credentials).'.
68 "\n\n".
69 'This software is developed and tested in "STRICT_ALL_TABLES" mode so '.
70 'you should normally never encounter these situations, but may run '.
71 'into them if you interact with the database directly, run '.
72 'third-party code, develop extensions, or just encounter a bug in '.
73 'the software.'.
74 "\n\n".
75 'Enabling "STRICT_ALL_TABLES" makes MySQL raise an explicit error '.
76 'if one of these unusual situations does occur. This is a safer '.
77 'behavior and prevents these situations from causing secret, subtle, '.
78 'and potentially serious issues later on.'.
79 "\n\n".
80 'You can find more information about this mode (and how to configure '.
81 'it) in the MySQL manual. Usually, it is sufficient to add this to '.
82 'your "my.cnf" file (in the "[mysqld]" section) and then '.
83 'restart "mysqld":'.
84 "\n\n".
85 '%s'.
86 "\n".
87 'Note that if you run other applications against the same database, '.
88 'they may not work in strict mode.'.
89 "\n\n".
90 'If you can not or do not want to enable "STRICT_ALL_TABLES", you '.
91 'can safely ignore this warning. This software will work correctly '.
92 'with this mode enabled or disabled.',
93 $host_name,
94 phutil_tag('pre', array(), 'sql_mode=STRICT_ALL_TABLES'));
95
96 $this->newIssue('sql_mode.strict')
97 ->setName(pht('MySQL %s Mode Not Set', 'STRICT_ALL_TABLES'))
98 ->setSummary($summary)
99 ->setMessage($message)
100 ->setDatabaseRef($ref)
101 ->addMySQLConfig('sql_mode');
102 }
103
104 $is_innodb_fulltext = false;
105 $is_myisam_fulltext = false;
106 if ($this->shouldUseMySQLSearchEngine()) {
107 if (PhabricatorSearchDocument::isInnoDBFulltextEngineAvailable()) {
108 $is_innodb_fulltext = true;
109 } else {
110 $is_myisam_fulltext = true;
111 }
112 }
113
114 if ($is_myisam_fulltext) {
115 $stopword_file = $ref->loadRawMySQLConfigValue('ft_stopword_file');
116 if ($stopword_file === null) {
117 $summary = pht(
118 'Your version of MySQL (on database host "%s") does not support '.
119 'configuration of a stopword file. You will not be able to find '.
120 'search results for common words.',
121 $host_name);
122
123 $message = pht(
124 "Database host \"%s\" does not support the %s option. You will not ".
125 "be able to find search results for common words. You can gain ".
126 "access to this option by upgrading MySQL to a more recent ".
127 "version.\n\n".
128 "You can ignore this warning if you plan to configure Elasticsearch ".
129 "later, or aren't concerned about searching for common words.",
130 $host_name,
131 phutil_tag('tt', array(), 'ft_stopword_file'));
132
133 $this->newIssue('mysql.ft_stopword_file')
134 ->setName(pht('MySQL %s Not Supported', 'ft_stopword_file'))
135 ->setSummary($summary)
136 ->setMessage($message)
137 ->setDatabaseRef($ref)
138 ->addMySQLConfig('ft_stopword_file');
139
140 } else if ($stopword_file == '(built-in)') {
141 $root = dirname(phutil_get_library_root('phabricator'));
142 $stopword_path = $root.'/resources/sql/stopwords.txt';
143 $stopword_path = Filesystem::resolvePath($stopword_path);
144
145 $namespace = PhabricatorEnv::getEnvConfig('storage.default-namespace');
146
147 $summary = pht(
148 'MySQL (on host "%s") is using a default stopword file, which '.
149 'will prevent searching for many common words.',
150 $host_name);
151
152 $message = pht(
153 "Database host \"%s\" is using the builtin stopword file for ".
154 "building search indexes. This can make the search ".
155 "feature less useful.\n\n".
156 "Stopwords are common words which are not indexed and thus can not ".
157 "be searched for. The default stopword file has about 500 words, ".
158 "including various words which you are likely to wish to search ".
159 "for, such as 'various', 'likely', 'wish', and 'zero'.\n\n".
160 "To make search more useful, you can use an alternate stopword ".
161 "file with fewer words. Alternatively, if you aren't concerned ".
162 "about searching for common words, you can ignore this warning. ".
163 "If you later plan to configure Elasticsearch, you can also ignore ".
164 "this warning: this stopword file only affects MySQL fulltext ".
165 "indexes.\n\n".
166 "To choose a different stopword file, add this to your %s file ".
167 "(in the %s section) and then restart %s:\n\n".
168 "%s\n".
169 "(You can also use a different file if you prefer. The file ".
170 "suggested above has about 50 of the most common English words.)",
171 $host_name,
172 phutil_tag('tt', array(), 'my.cnf'),
173 phutil_tag('tt', array(), '[mysqld]'),
174 phutil_tag('tt', array(), 'mysqld'),
175 phutil_tag('pre', array(), 'ft_stopword_file='.$stopword_path));
176
177 $this->newIssue('mysql.ft_stopword_file')
178 ->setName(pht('MySQL is Using Default Stopword File'))
179 ->setSummary($summary)
180 ->setMessage($message)
181 ->setDatabaseRef($ref)
182 ->addMySQLConfig('ft_stopword_file');
183 }
184 }
185
186 if ($is_myisam_fulltext) {
187 $min_len = $ref->loadRawMySQLConfigValue('ft_min_word_len');
188 if ($min_len >= 4) {
189 $namespace = PhabricatorEnv::getEnvConfig('storage.default-namespace');
190
191 $summary = pht(
192 'MySQL is configured (on host "%s") to only index words with at '.
193 'least %d characters.',
194 $host_name,
195 $min_len);
196
197 $message = pht(
198 "Database host \"%s\" is configured to use the default minimum word ".
199 "length when building search indexes, which is 4. This means words ".
200 "which are only 3 characters long will not be indexed and can not ".
201 "be searched for.\n\n".
202 "For example, you will not be able to find search results for words ".
203 "like 'SMS', 'web', or 'DOS'.\n\n".
204 "You can change this setting to 3 to allow these words to be ".
205 "indexed. Alternatively, you can ignore this warning if you are ".
206 "not concerned about searching for 3-letter words. If you later ".
207 "plan to configure Elasticsearch, you can also ignore this warning: ".
208 "only MySQL fulltext search is affected.\n\n".
209 "To reduce the minimum word length to 3, add this to your %s file ".
210 "(in the %s section) and then restart %s:\n\n".
211 "%s\n",
212 $host_name,
213 phutil_tag('tt', array(), 'my.cnf'),
214 phutil_tag('tt', array(), '[mysqld]'),
215 phutil_tag('tt', array(), 'mysqld'),
216 phutil_tag('pre', array(), 'ft_min_word_len=3'));
217
218 $this->newIssue('mysql.ft_min_word_len')
219 ->setName(pht('MySQL is Using Default Minimum Word Length'))
220 ->setSummary($summary)
221 ->setMessage($message)
222 ->setDatabaseRef($ref)
223 ->addMySQLConfig('ft_min_word_len');
224 }
225 }
226
227 // NOTE: The default value of "innodb_ft_min_token_size" is 3, which is
228 // a reasonable value, so we do not warn about it: if it is set to
229 // something else, the user adjusted it on their own.
230
231 // NOTE: We populate a stopwords table at "phabricator_search.stopwords",
232 // but the default InnoDB stopword list is pretty reasonable (36 words,
233 // versus 500+ in MyISAM). Just use the builtin list until we run into
234 // concrete issues with it. Users can switch to our stopword table with:
235 //
236 // [mysqld]
237 // innodb_ft_server_stopword_table = phabricator_search/stopwords
238
239 $innodb_pool = $ref->loadRawMySQLConfigValue('innodb_buffer_pool_size');
240 $innodb_bytes = phutil_parse_bytes($innodb_pool);
241 $innodb_readable = phutil_format_bytes($innodb_bytes);
242
243 // This is arbitrary and just trying to detect values that the user
244 // probably didn't set themselves. The Mac OS X default is 128MB and
245 // 40% of an AWS EC2 Micro instance is 245MB, so keeping it somewhere
246 // between those two values seems like a reasonable approximation.
247 $minimum_readable = '225MB';
248
249 $minimum_bytes = phutil_parse_bytes($minimum_readable);
250 if ($innodb_bytes < $minimum_bytes) {
251 $summary = pht(
252 'MySQL (on host "%s") is configured with a very small '.
253 'innodb_buffer_pool_size, which may impact performance.',
254 $host_name);
255
256 $message = pht(
257 "Database host \"%s\" is configured with a very small %s (%s). ".
258 "This may cause poor database performance and lock exhaustion.\n\n".
259 "There are no hard-and-fast rules to setting an appropriate value, ".
260 "but a reasonable starting point for a standard install is something ".
261 "like 40%% of the total memory on the machine. For example, if you ".
262 "have 4GB of RAM on the machine you have installed this software on, ".
263 "you might set this value to %s.\n\n".
264 "You can read more about this option in the MySQL documentation to ".
265 "help you make a decision about how to configure it for your use ".
266 "case. There are no concerns specific to this software which make it ".
267 "different from normal workloads with respect to this setting.\n\n".
268 "To adjust the setting, add something like this to your %s file (in ".
269 "the %s section), replacing %s with an appropriate value for your ".
270 "host and use case. Then restart %s:\n\n".
271 "%s\n".
272 "If you're satisfied with the current setting, you can safely ".
273 "ignore this setup warning.",
274 $host_name,
275 phutil_tag('tt', array(), 'innodb_buffer_pool_size'),
276 phutil_tag('tt', array(), $innodb_readable),
277 phutil_tag('tt', array(), '1600M'),
278 phutil_tag('tt', array(), 'my.cnf'),
279 phutil_tag('tt', array(), '[mysqld]'),
280 phutil_tag('tt', array(), '1600M'),
281 phutil_tag('tt', array(), 'mysqld'),
282 phutil_tag('pre', array(), 'innodb_buffer_pool_size=1600M'));
283
284 $this->newIssue('mysql.innodb_buffer_pool_size')
285 ->setName(pht('MySQL May Run Slowly'))
286 ->setSummary($summary)
287 ->setMessage($message)
288 ->setDatabaseRef($ref)
289 ->addMySQLConfig('innodb_buffer_pool_size');
290 }
291
292 $conn = $ref->newManagementConnection();
293
294 $ok = PhabricatorStorageManagementAPI::isCharacterSetAvailableOnConnection(
295 'utf8mb4',
296 $conn);
297 if (!$ok) {
298 $summary = pht(
299 'You are using an old version of MySQL (on host "%s"), and should '.
300 'upgrade.',
301 $host_name);
302
303 $message = pht(
304 'You are using an old version of MySQL (on host "%s") which has poor '.
305 'unicode support (it does not support the "utf8mb4" collation set). '.
306 'You will encounter limitations when working with some unicode data.'.
307 "\n\n".
308 'We strongly recommend you upgrade to MySQL 5.5 or newer.',
309 $host_name);
310
311 $this->newIssue('mysql.utf8mb4')
312 ->setName(pht('Old MySQL Version'))
313 ->setSummary($summary)
314 ->setDatabaseRef($ref)
315 ->setMessage($message);
316 }
317
318 $info = queryfx_one(
319 $conn,
320 'SELECT UNIX_TIMESTAMP() epoch');
321
322 $epoch = (int)$info['epoch'];
323 $local = PhabricatorTime::getNow();
324 $delta = (int)abs($local - $epoch);
325 if ($delta > 60) {
326 $this->newIssue('mysql.clock')
327 ->setName(pht('Major Web/Database Clock Skew'))
328 ->setSummary(
329 pht(
330 'This web host ("%s") is set to a very different time than a '.
331 'database host "%s".',
332 php_uname('n'),
333 $host_name))
334 ->setMessage(
335 pht(
336 'A database host ("%s") and this web host ("%s") disagree on the '.
337 'current time by more than 60 seconds (absolute skew is %s '.
338 'seconds). Check that the current time is set correctly '.
339 'everywhere.',
340 $host_name,
341 php_uname('n'),
342 new PhutilNumber($delta)));
343 }
344
345 $local_infile = $ref->loadRawMySQLConfigValue('local_infile');
346 if ($local_infile) {
347 $summary = pht(
348 'The MySQL "local_infile" option is enabled. This option is '.
349 'unsafe.');
350
351 $message = pht(
352 'Your MySQL server is configured with the "local_infile" option '.
353 'enabled. This option allows an attacker who finds an SQL injection '.
354 'hole to escalate their attack by copying files from the webserver '.
355 'into the database with "LOAD DATA LOCAL INFILE" queries, then '.
356 'reading the file content with "SELECT" queries.'.
357 "\n\n".
358 'You should disable this option in your %s file, in the %s section:'.
359 "\n\n".
360 '%s',
361 phutil_tag('tt', array(), 'my.cnf'),
362 phutil_tag('tt', array(), '[mysqld]'),
363 phutil_tag('pre', array(), 'local_infile=0'));
364
365 $this->newIssue('mysql.local_infile')
366 ->setName(pht('Unsafe MySQL "local_infile" Setting Enabled'))
367 ->setSummary($summary)
368 ->setMessage($message)
369 ->setDatabaseRef($ref)
370 ->addMySQLConfig('local_infile');
371 }
372
373 }
374
375 protected function shouldUseMySQLSearchEngine() {
376 $services = PhabricatorSearchService::getAllServices();
377 foreach ($services as $service) {
378 if ($service->getEngine() instanceof
379 PhabricatorFerretFulltextStorageEngine) {
380 return true;
381 }
382 }
383 return false;
384 }
385
386}