[Summary view]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * MySQL collation conversion tool.
  19   *
  20   * @package    core
  21   * @copyright  2012 Petr Skoda (http://skodak.org)
  22   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23   */
  24  
  25  define('CLI_SCRIPT', true);
  26  
  27  require(__DIR__.'/../../config.php');
  28  require_once($CFG->libdir.'/clilib.php');      // cli only functions
  29  
  30  if ($DB->get_dbfamily() !== 'mysql') {
  31      cli_error('This function is designed for MySQL databases only!');
  32  }
  33  
  34  // now get cli options
  35  list($options, $unrecognized) = cli_get_params(array('help'=>false, 'list'=>false, 'collation'=>false, 'available'=>false),
  36      array('h'=>'help', 'l'=>'list', 'a'=>'available'));
  37  
  38  if ($unrecognized) {
  39      $unrecognized = implode("\n  ", $unrecognized);
  40      cli_error(get_string('cliunknowoption', 'admin', $unrecognized));
  41  }
  42  
  43  $help =
  44      "MySQL collation conversions script.
  45  
  46  It is strongly recommended to stop the web server before the conversion.
  47  This script may be executed before the main upgrade - 1.9.x data for example.
  48  
  49  Options:
  50  --collation=COLLATION Convert MySQL tables to different collation
  51  -l, --list            Show table and column information
  52  -a, --available       Show list of available collations
  53  -h, --help            Print out this help
  54  
  55  Example:
  56  \$ sudo -u www-data /usr/bin/php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
  57  ";
  58  
  59  if (!empty($options['collation'])) {
  60      $collations = mysql_get_collations();
  61      $collation = clean_param($options['collation'], PARAM_ALPHANUMEXT);
  62      $collation = strtolower($collation);
  63      if (!isset($collations[$collation])) {
  64          cli_error("Error: collation '$collation' is not available on this server!");
  65      }
  66  
  67      $collationinfo = explode('_', $collation);
  68      $charset = reset($collationinfo);
  69  
  70      $engine = strtolower($DB->get_dbengine());
  71  
  72      // Do checks for utf8mb4.
  73      if (strpos($collation, 'utf8mb4') === 0) {
  74          // Do we have the right engine?
  75          if ($engine !== 'innodb' && $engine !== 'xtradb') {
  76              cli_error("Error: '$collation' requires InnoDB or XtraDB set as the engine.");
  77          }
  78          // Are we using Barracuda?
  79          if ($DB->get_row_format() != 'Barracuda') {
  80              // Try setting it here.
  81              try {
  82                  $DB->execute("SET GLOBAL innodb_file_format=Barracuda");
  83              } catch (dml_exception $e) {
  84                  cli_error("Error: '$collation' requires the file format to be set to Barracuda.
  85                          An attempt was made to change the format, but it failed. Please try doing this manually.");
  86              }
  87              echo "GLOBAL SETTING: innodb_file_format changed to Barracuda\n";
  88          }
  89          // Is one file per table being used?
  90          if (!$DB->is_file_per_table_enabled()) {
  91              try {
  92                  $DB->execute("SET GLOBAL innodb_file_per_table=1");
  93              } catch (dml_exception $e) {
  94                  cli_error("Error: '$collation' requires the setting 'innodb_file_per_table' be set to 'ON'.
  95                          An attempt was made to change the format, but it failed. Please try doing this manually.");
  96              }
  97              echo "GLOBAL SETTING: innodb_file_per_table changed to 1\n";
  98          }
  99          // Is large prefix set?
 100          if (!$DB->is_large_prefix_enabled()) {
 101              try {
 102                  $DB->execute("SET GLOBAL innodb_large_prefix=1");
 103              } catch (dml_exception $e) {
 104                  cli_error("Error: '$collation' requires the setting 'innodb_large_prefix' be set to 'ON'.
 105                          An attempt was made to change the format, but it failed. Please try doing this manually.");
 106              }
 107              echo "GLOBAL SETTING: innodb_large_prefix changed to 1\n";
 108          }
 109      }
 110  
 111      $sql = "SHOW VARIABLES LIKE 'collation_database'";
 112      if (!$dbcollation = $DB->get_record_sql($sql)) {
 113          cli_error("Error: Could not access collation information on the database.");
 114      }
 115      $sql = "SHOW VARIABLES LIKE 'character_set_database'";
 116      if (!$dbcharset = $DB->get_record_sql($sql)) {
 117          cli_error("Error: Could not access character set information on the database.");
 118      }
 119      if ($dbcollation->value !== $collation || $dbcharset->value !== $charset) {
 120          // Try to convert the DB.
 121          echo "Converting database to '$collation' for $CFG->wwwroot:\n";
 122          $sql = "ALTER DATABASE `$CFG->dbname` DEFAULT CHARACTER SET $charset DEFAULT COLLATE = $collation";
 123          try {
 124              $DB->change_database_structure($sql);
 125          } catch (exception $e) {
 126              cli_error("Error: Tried to alter the database with no success. Please try manually changing the database
 127                      to the new collation and character set and then run this script again.");
 128          }
 129          echo "DATABASE CONVERTED\n";
 130      }
 131  
 132      echo "Converting tables and columns to '$collation' for $CFG->wwwroot:\n";
 133      $prefix = $DB->get_prefix();
 134      $prefix = str_replace('_', '\\_', $prefix);
 135      $sql = "SHOW TABLE STATUS WHERE Name LIKE BINARY '$prefix%'";
 136      $rs = $DB->get_recordset_sql($sql);
 137      $converted = 0;
 138      $skipped   = 0;
 139      $errors    = 0;
 140      foreach ($rs as $table) {
 141          echo str_pad($table->name, 40). " - ";
 142  
 143          if ($table->collation === $collation) {
 144              echo "NO CHANGE\n";
 145              $skipped++;
 146  
 147          } else {
 148              try {
 149                  $DB->change_database_structure("ALTER TABLE `$table->name` CONVERT TO CHARACTER SET $charset COLLATE $collation");
 150                  echo "CONVERTED\n";
 151                  $converted++;
 152              } catch (ddl_exception $e) {
 153                  $result = mysql_set_row_format($table->name, $charset, $collation, $engine);
 154                  if ($result) {
 155                      echo "CONVERTED\n";
 156                      $converted++;
 157                  } else {
 158                      // We don't know what the problem is. Stop the conversion.
 159                      cli_error("Error: Tried to convert $table->name, but there was a problem. Please check the details of this
 160                              table and try again.");
 161                      die();
 162                  }
 163              }
 164          }
 165  
 166          $sql = "SHOW FULL COLUMNS FROM `$table->name` WHERE collation IS NOT NULL";
 167          $rs2 = $DB->get_recordset_sql($sql);
 168          foreach ($rs2 as $column) {
 169              $column = (object)array_change_key_case((array)$column, CASE_LOWER);
 170              echo '    '.str_pad($column->field, 36). " - ";
 171              if ($column->collation === $collation) {
 172                  echo "NO CHANGE\n";
 173                  $skipped++;
 174                  continue;
 175              }
 176  
 177              // Check for utf8mb4 collation.
 178              $rowformat = $DB->get_row_format_sql($engine, $collation);
 179  
 180              if ($column->type === 'tinytext' or $column->type === 'mediumtext' or $column->type === 'text' or $column->type === 'longtext') {
 181                  $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
 182                  $default = (!is_null($column->default) and $column->default !== '') ? "DEFAULT '$column->default'" : '';
 183                  // primary, unique and inc are not supported for texts
 184                  $sql = "ALTER TABLE `$table->name`
 185                          MODIFY COLUMN $column->field $column->type
 186                          CHARACTER SET $charset
 187                          COLLATE $collation $notnull $default";
 188                  $DB->change_database_structure($sql);
 189  
 190              } else if (strpos($column->type, 'varchar') === 0) {
 191                  $notnull = ($column->null === 'NO') ? 'NOT NULL' : 'NULL';
 192                  $default = !is_null($column->default) ? "DEFAULT '$column->default'" : '';
 193  
 194                  if ($rowformat != '') {
 195                      $sql = "ALTER TABLE `$table->name` $rowformat";
 196                      $DB->change_database_structure($sql);
 197                  }
 198  
 199                  $sql = "ALTER TABLE `$table->name`
 200                          MODIFY COLUMN $column->field $column->type
 201                          CHARACTER SET $charset
 202                          COLLATE $collation $notnull $default";
 203                  $DB->change_database_structure($sql);
 204              } else {
 205                  echo "ERROR (unknown column type: $column->type)\n";
 206                  $errors++;
 207                  continue;
 208              }
 209              echo "CONVERTED\n";
 210              $converted++;
 211          }
 212          $rs2->close();
 213      }
 214      $rs->close();
 215      echo "Converted: $converted, skipped: $skipped, errors: $errors\n";
 216      exit(0); // success
 217  
 218  } else if (!empty($options['list'])) {
 219      echo "List of tables for $CFG->wwwroot:\n";
 220      $prefix = $DB->get_prefix();
 221      $prefix = str_replace('_', '\\_', $prefix);
 222      $sql = "SHOW TABLE STATUS WHERE Name LIKE BINARY '$prefix%'";
 223      $rs = $DB->get_recordset_sql($sql);
 224      $counts = array();
 225      foreach ($rs as $table) {
 226          if (isset($counts[$table->collation])) {
 227              $counts[$table->collation]++;
 228          } else {
 229              $counts[$table->collation] = 1;
 230          }
 231          echo str_pad($table->name, 40);
 232          echo $table->collation.  "\n";
 233          $collations = mysql_get_column_collations($table->name);
 234          foreach ($collations as $columname=>$collation) {
 235              if (isset($counts[$collation])) {
 236                  $counts[$collation]++;
 237              } else {
 238                  $counts[$collation] = 1;
 239              }
 240              echo '    ';
 241              echo str_pad($columname, 36);
 242              echo $collation.  "\n";
 243          }
 244      }
 245      $rs->close();
 246  
 247      echo "\n";
 248      echo "Table collations summary for $CFG->wwwroot:\n";
 249      foreach ($counts as $collation => $count) {
 250          echo "$collation: $count\n";
 251      }
 252      exit(0); // success
 253  
 254  } else if (!empty($options['available'])) {
 255      echo "List of available MySQL collations for $CFG->wwwroot:\n";
 256      $collations = mysql_get_collations();
 257      foreach ($collations as $collation) {
 258          echo " $collation\n";
 259      }
 260      die;
 261  
 262  } else {
 263      echo $help;
 264      die;
 265  }
 266  
 267  
 268  
 269  // ========== Some functions ==============
 270  
 271  function mysql_get_collations() {
 272      global $DB;
 273  
 274      $collations = array();
 275      $sql = "SHOW COLLATION
 276              WHERE Collation LIKE 'utf8\_%' AND Charset = 'utf8'
 277                 OR Collation LIKE 'utf8mb4\_%' AND Charset = 'utf8mb4'";
 278      $rs = $DB->get_recordset_sql($sql);
 279      foreach ($rs as $collation) {
 280          $collations[$collation->collation] = $collation->collation;
 281      }
 282      $rs->close();
 283  
 284      $collation = $DB->get_dbcollation();
 285      if (isset($collations[$collation])) {
 286          $collations[$collation] .= ' (default)';
 287      }
 288  
 289      return $collations;
 290  }
 291  
 292  function mysql_get_column_collations($tablename) {
 293      global $DB;
 294  
 295      $collations = array();
 296      $sql = "SELECT column_name, collation_name
 297                FROM INFORMATION_SCHEMA.COLUMNS
 298               WHERE table_schema = DATABASE() AND table_name = ? AND collation_name IS NOT NULL";
 299      $rs = $DB->get_recordset_sql($sql, array($tablename));
 300      foreach($rs as $record) {
 301          $collations[$record->column_name] = $record->collation_name;
 302      }
 303      $rs->close();
 304      return $collations;
 305  }
 306  
 307  function mysql_set_row_format($tablename, $charset, $collation, $engine) {
 308      global $DB;
 309  
 310      $sql = "SELECT row_format
 311                FROM INFORMATION_SCHEMA.TABLES
 312               WHERE table_schema = DATABASE() AND table_name = ?";
 313      $rs = $DB->get_record_sql($sql, array($tablename));
 314      if ($rs) {
 315          if ($rs->row_format == 'Compact' || $rs->row_format == 'Redundant') {
 316              $rowformat = $DB->get_row_format_sql($engine, $collation);
 317              // Try to convert to compressed format and then try updating the collation again.
 318              $DB->change_database_structure("ALTER TABLE `$tablename` $rowformat");
 319              $DB->change_database_structure("ALTER TABLE `$tablename` CONVERT TO CHARACTER SET $charset COLLATE $collation");
 320          } else {
 321              // Row format may not be the problem. Can not diagnose problem. Send fail reply.
 322              return false;
 323          }
 324      } else {
 325          return false;
 326      }
 327      return true;
 328  }