MDL-58729 database: Improve performance of CLI tool.
[moodle.git] / 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/>.
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  */
25 define('CLI_SCRIPT', true);
27 require(__DIR__.'/../../config.php');
28 require_once($CFG->libdir.'/clilib.php');      // cli only functions
30 if ($DB->get_dbfamily() !== 'mysql') {
31     cli_error('This function is designed for MySQL databases only!');
32 }
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'));
38 if ($unrecognized) {
39     $unrecognized = implode("\n  ", $unrecognized);
40     cli_error(get_string('cliunknowoption', 'admin', $unrecognized));
41 }
43 $help =
44     "MySQL collation conversions script.
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.
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
55 Example:
56 \$ sudo -u www-data /usr/bin/php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
57 ";
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     }
67     $collationinfo = explode('_', $collation);
68     $charset = reset($collationinfo);
70     $engine = strtolower($DB->get_dbengine());
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     }
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     }
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). " - ";
143         if ($table->collation === $collation) {
144             echo "NO CHANGE\n";
145             $skipped++;
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         }
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             }
177             // Check for utf8mb4 collation.
178             $rowformat = $DB->get_row_format_sql($engine, $collation);
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);
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'" : '';
194                 if ($rowformat != '') {
195                     $sql = "ALTER TABLE $table->name $rowformat";
196                     $DB->change_database_structure($sql);
197                 }
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                 $error++;
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
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();
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
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;
262 } else {
263     echo $help;
264     die;
269 // ========== Some functions ==============
271 function mysql_get_collations() {
272     global $DB;
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();
284     $collation = $DB->get_dbcollation();
285     if (isset($collations[$collation])) {
286         $collations[$collation] .= ' (default)';
287     }
289     return $collations;
292 function mysql_get_column_collations($tablename) {
293     global $DB;
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;
307 function mysql_set_row_format($tablename, $charset, $collation, $engine) {
308     global $DB;
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;