admin/cli/mysql_collation.php

   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 }