[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  final class PhabricatorSetupCheckMySQL extends PhabricatorSetupCheck {
   4  
   5    public static function loadRawConfigValue($key) {
   6      $conn_raw = id(new PhabricatorUser())->establishConnection('w');
   7  
   8      try {
   9        $value = queryfx_one($conn_raw, 'SELECT @@%Q', $key);
  10        $value = $value['@@'.$key];
  11      } catch (AphrontQueryException $ex) {
  12        $value = null;
  13      }
  14  
  15      return $value;
  16    }
  17  
  18    protected function executeChecks() {
  19      $max_allowed_packet = self::loadRawConfigValue('max_allowed_packet');
  20      $recommended_minimum = 1024 * 1024;
  21      if ($max_allowed_packet < $recommended_minimum) {
  22        $message = pht(
  23          "MySQL is configured with a very small 'max_allowed_packet' (%d), ".
  24          "which may cause some large writes to fail. Strongly consider raising ".
  25          "this to at least %d in your MySQL configuration.",
  26          $max_allowed_packet,
  27          $recommended_minimum);
  28  
  29        $this->newIssue('mysql.max_allowed_packet')
  30          ->setName(pht('Small MySQL "max_allowed_packet"'))
  31          ->setMessage($message)
  32          ->addMySQLConfig('max_allowed_packet');
  33      }
  34  
  35      $modes = self::loadRawConfigValue('sql_mode');
  36      $modes = explode(',', $modes);
  37  
  38      if (!in_array('STRICT_ALL_TABLES', $modes)) {
  39        $summary = pht(
  40          'MySQL is not in strict mode, but using strict mode is strongly '.
  41          'encouraged.');
  42  
  43        $message = pht(
  44          "On your MySQL instance, the global %s is not set to %s. ".
  45          "It is strongly encouraged that you enable this mode when running ".
  46          "Phabricator.\n\n".
  47          "By default MySQL will silently ignore some types of errors, which ".
  48          "can cause data loss and raise security concerns. Enabling strict ".
  49          "mode makes MySQL raise an explicit error instead, and prevents this ".
  50          "entire class of problems from doing any damage.\n\n".
  51          "You can find more information about this mode (and how to configure ".
  52          "it) in the MySQL manual. Usually, it is sufficient to add this to ".
  53          "your %s file (in the %s section) and then restart %s:\n\n".
  54          "%s\n".
  55          "(Note that if you run other applications against the same database, ".
  56          "they may not work in strict mode. Be careful about enabling it in ".
  57          "these cases.)",
  58          phutil_tag('tt', array(), 'sql_mode'),
  59          phutil_tag('tt', array(), 'STRICT_ALL_TABLES'),
  60          phutil_tag('tt', array(), 'my.cnf'),
  61          phutil_tag('tt', array(), '[mysqld]'),
  62          phutil_tag('tt', array(), 'mysqld'),
  63          phutil_tag('pre', array(), 'sql_mode=STRICT_ALL_TABLES'));
  64  
  65        $this->newIssue('mysql.mode')
  66          ->setName(pht('MySQL STRICT_ALL_TABLES Mode Not Set'))
  67          ->setSummary($summary)
  68          ->setMessage($message)
  69          ->addMySQLConfig('sql_mode');
  70      }
  71      if (in_array('ONLY_FULL_GROUP_BY', $modes)) {
  72        $summary = pht(
  73          'MySQL is in ONLY_FULL_GROUP_BY mode, but using this mode is strongly '.
  74          'discouraged.');
  75  
  76        $message = pht(
  77          "On your MySQL instance, the global %s is set to %s. ".
  78          "It is strongly encouraged that you disable this mode when running ".
  79          "Phabricator.\n\n".
  80          "With %s enabled, MySQL rejects queries for which the select list ".
  81          "or (as of MySQL 5.0.23) %s list refer to nonaggregated columns ".
  82          "that are not named in the %s clause. More importantly, Phabricator ".
  83          "does not work properly with this mode enabled.\n\n".
  84          "You can find more information about this mode (and how to configure ".
  85          "it) in the MySQL manual. Usually, it is sufficient to change the %s ".
  86          "in your %s file (in the %s section) and then restart %s:\n\n".
  87          "%s\n".
  88          "(Note that if you run other applications against the same database, ".
  89          "they may not work with %s. Be careful about enabling ".
  90          "it in these cases and consider migrating Phabricator to a different ".
  91          "database.)",
  92          phutil_tag('tt', array(), 'sql_mode'),
  93          phutil_tag('tt', array(), 'ONLY_FULL_GROUP_BY'),
  94          phutil_tag('tt', array(), 'ONLY_FULL_GROUP_BY'),
  95          phutil_tag('tt', array(), 'HAVING'),
  96          phutil_tag('tt', array(), 'GROUP BY'),
  97          phutil_tag('tt', array(), 'sql_mode'),
  98          phutil_tag('tt', array(), 'my.cnf'),
  99          phutil_tag('tt', array(), '[mysqld]'),
 100          phutil_tag('tt', array(), 'mysqld'),
 101          phutil_tag('pre', array(), 'sql_mode=STRICT_ALL_TABLES'),
 102          phutil_tag('tt', array(), 'ONLY_FULL_GROUP_BY'));
 103  
 104        $this->newIssue('mysql.mode')
 105          ->setName(pht('MySQL ONLY_FULL_GROUP_BY Mode Set'))
 106          ->setSummary($summary)
 107          ->setMessage($message)
 108          ->addMySQLConfig('sql_mode');
 109      }
 110  
 111      $stopword_file = self::loadRawConfigValue('ft_stopword_file');
 112      if (!PhabricatorDefaultSearchEngineSelector::shouldUseElasticSearch()) {
 113        if ($stopword_file === null) {
 114          $summary = pht(
 115            'Your version of MySQL does not support configuration of a '.
 116            'stopword file. You will not be able to find search results for '.
 117            'common words.');
 118  
 119          $message = pht(
 120            "Your MySQL instance does not support the %s option. You will not ".
 121            "be able to find search results for common words. You can gain ".
 122            "access to this option by upgrading MySQL to a more recent ".
 123            "version.\n\n".
 124            "You can ignore this warning if you plan to configure ElasticSearch ".
 125            "later, or aren't concerned about searching for common words.",
 126            phutil_tag('tt', array(), 'ft_stopword_file'));
 127  
 128          $this->newIssue('mysql.ft_stopword_file')
 129            ->setName(pht('MySQL ft_stopword_file Not Supported'))
 130            ->setSummary($summary)
 131            ->setMessage($message)
 132            ->addMySQLConfig('ft_stopword_file');
 133  
 134        } else if ($stopword_file == '(built-in)') {
 135          $root = dirname(phutil_get_library_root('phabricator'));
 136          $stopword_path = $root.'/resources/sql/stopwords.txt';
 137          $stopword_path = Filesystem::resolvePath($stopword_path);
 138  
 139          $namespace = PhabricatorEnv::getEnvConfig('storage.default-namespace');
 140  
 141          $summary = pht(
 142            'MySQL is using a default stopword file, which will prevent '.
 143            'searching for many common words.');
 144  
 145          $message = pht(
 146            "Your MySQL instance is using the builtin stopword file for ".
 147            "building search indexes. This can make Phabricator's search ".
 148            "feature less useful.\n\n".
 149            "Stopwords are common words which are not indexed and thus can not ".
 150            "be searched for. The default stopword file has about 500 words, ".
 151            "including various words which you are likely to wish to search ".
 152            "for, such as 'various', 'likely', 'wish', and 'zero'.\n\n".
 153            "To make search more useful, you can use an alternate stopword ".
 154            "file with fewer words. Alternatively, if you aren't concerned ".
 155            "about searching for common words, you can ignore this warning. ".
 156            "If you later plan to configure ElasticSearch, you can also ignore ".
 157            "this warning: this stopword file only affects MySQL fulltext ".
 158            "indexes.\n\n".
 159            "To choose a different stopword file, add this to your %s file ".
 160            "(in the %s section) and then restart %s:\n\n".
 161            "%s\n".
 162            "(You can also use a different file if you prefer. The file ".
 163            "suggested above has about 50 of the most common English words.)\n\n".
 164            "Finally, run this command to rebuild indexes using the new ".
 165            "rules:\n\n".
 166            "%s",
 167            phutil_tag('tt', array(), 'my.cnf'),
 168            phutil_tag('tt', array(), '[mysqld]'),
 169            phutil_tag('tt', array(), 'mysqld'),
 170            phutil_tag('pre', array(), 'ft_stopword_file='.$stopword_path),
 171            phutil_tag(
 172              'pre',
 173              array(),
 174              "mysql> REPAIR TABLE {$namespace}_search.search_documentfield;"));
 175  
 176          $this->newIssue('mysql.ft_stopword_file')
 177            ->setName(pht('MySQL is Using Default Stopword File'))
 178            ->setSummary($summary)
 179            ->setMessage($message)
 180            ->addMySQLConfig('ft_stopword_file');
 181        }
 182      }
 183  
 184      $min_len = self::loadRawConfigValue('ft_min_word_len');
 185      if ($min_len >= 4) {
 186        if (!PhabricatorDefaultSearchEngineSelector::shouldUseElasticSearch()) {
 187          $namespace = PhabricatorEnv::getEnvConfig('storage.default-namespace');
 188  
 189          $summary = pht(
 190            'MySQL is configured to only index words with at least %d '.
 191            'characters.',
 192            $min_len);
 193  
 194          $message = pht(
 195            "Your MySQL instance is configured to use the default minimum word ".
 196            "length when building search indexes, which is 4. This means words ".
 197            "which are only 3 characters long will not be indexed and can not ".
 198            "be searched for.\n\n".
 199            "For example, you will not be able to find search results for words ".
 200            "like 'SMS', 'web', or 'DOS'.\n\n".
 201            "You can change this setting to 3 to allow these words to be ".
 202            "indexed. Alternatively, you can ignore this warning if you are ".
 203            "not concerned about searching for 3-letter words. If you later ".
 204            "plan to configure ElasticSearch, you can also ignore this warning: ".
 205            "only MySQL fulltext search is affected.\n\n".
 206            "To reduce the minimum word length to 3, add this to your %s file ".
 207            "(in the %s section) and then restart %s:\n\n".
 208            "%s\n".
 209            "Finally, run this command to rebuild indexes using the new ".
 210            "rules:\n\n".
 211            "%s",
 212            phutil_tag('tt', array(), 'my.cnf'),
 213            phutil_tag('tt', array(), '[mysqld]'),
 214            phutil_tag('tt', array(), 'mysqld'),
 215            phutil_tag('pre', array(), 'ft_min_word_len=3'),
 216            phutil_tag(
 217              'pre',
 218              array(),
 219              "mysql> REPAIR TABLE {$namespace}_search.search_documentfield;"));
 220  
 221          $this->newIssue('mysql.ft_min_word_len')
 222            ->setName(pht('MySQL is Using Default Minimum Word Length'))
 223            ->setSummary($summary)
 224            ->setMessage($message)
 225            ->addMySQLConfig('ft_min_word_len');
 226        }
 227      }
 228  
 229      $innodb_pool = self::loadRawConfigValue('innodb_buffer_pool_size');
 230      $innodb_bytes = phutil_parse_bytes($innodb_pool);
 231      $innodb_readable = phutil_format_bytes($innodb_bytes);
 232  
 233      // This is arbitrary and just trying to detect values that the user
 234      // probably didn't set themselves. The Mac OS X default is 128MB and
 235      // 40% of an AWS EC2 Micro instance is 245MB, so keeping it somewhere
 236      // between those two values seems like a reasonable approximation.
 237      $minimum_readable = '225MB';
 238  
 239      $minimum_bytes = phutil_parse_bytes($minimum_readable);
 240      if ($innodb_bytes < $minimum_bytes) {
 241        $summary = pht(
 242          'MySQL is configured with a very small innodb_buffer_pool_size, '.
 243          'which may impact performance.');
 244  
 245        $message = pht(
 246          "Your MySQL instance is configured with a very small %s (%s). ".
 247          "This may cause poor database performance and lock exhaustion.\n\n".
 248          "There are no hard-and-fast rules to setting an appropriate value, ".
 249          "but a reasonable starting point for a standard install is something ".
 250          "like 40%% of the total memory on the machine. For example, if you ".
 251          "have 4GB of RAM on the machine you have installed Phabricator on, ".
 252          "you might set this value to %s.\n\n".
 253          "You can read more about this option in the MySQL documentation to ".
 254          "help you make a decision about how to configure it for your use ".
 255          "case. There are no concerns specific to Phabricator which make it ".
 256          "different from normal workloads with respect to this setting.\n\n".
 257          "To adjust the setting, add something like this to your %s file (in ".
 258          "the %s section), replacing %s with an appropriate value for your ".
 259          "host and use case. Then restart %s:\n\n".
 260          "%s\n".
 261          "If you're satisfied with the current setting, you can safely ".
 262          "ignore this setup warning.",
 263          phutil_tag('tt', array(), 'innodb_buffer_pool_size'),
 264          phutil_tag('tt', array(), $innodb_readable),
 265          phutil_tag('tt', array(), '1600M'),
 266          phutil_tag('tt', array(), 'my.cnf'),
 267          phutil_tag('tt', array(), '[mysqld]'),
 268          phutil_tag('tt', array(), '1600M'),
 269          phutil_tag('tt', array(), 'mysqld'),
 270          phutil_tag('pre', array(), 'innodb_buffer_pool_size=1600M'));
 271  
 272        $this->newIssue('mysql.innodb_buffer_pool_size')
 273          ->setName(pht('MySQL May Run Slowly'))
 274          ->setSummary($summary)
 275          ->setMessage($message)
 276          ->addMySQLConfig('innodb_buffer_pool_size');
 277      }
 278  
 279    }
 280  
 281  }