MDL-58729 database: Improve performance of CLI tool.
[moodle.git] / admin / cli / mysql_collation.php
CommitLineData
fdd57d4f
PS
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
25define('CLI_SCRIPT', true);
26
1fcf0ca8 27require(__DIR__.'/../../config.php');
fdd57d4f
PS
28require_once($CFG->libdir.'/clilib.php'); // cli only functions
29
30if ($DB->get_dbfamily() !== 'mysql') {
31 cli_error('This function is designed for MySQL databases only!');
32}
33
34// now get cli options
35list($options, $unrecognized) = cli_get_params(array('help'=>false, 'list'=>false, 'collation'=>false, 'available'=>false),
36 array('h'=>'help', 'l'=>'list', 'a'=>'available'));
37
38if ($unrecognized) {
39 $unrecognized = implode("\n ", $unrecognized);
40 cli_error(get_string('cliunknowoption', 'admin', $unrecognized));
41}
42
43$help =
44 "MySQL collation conversions script.
45
46It is strongly recommended to stop the web server before the conversion.
47This script may be executed before the main upgrade - 1.9.x data for example.
48
49Options:
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
55Example:
c05dc071 56\$ sudo -u www-data /usr/bin/php admin/cli/mysql_collation.php --collation=utf8mb4_unicode_ci
fdd57d4f
PS
57";
58
59if (!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
0bbefd81
AG
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
fdd57d4f
PS
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 {
c05dc071
AG
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 }
fdd57d4f
PS
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
0bbefd81
AG
177 // Check for utf8mb4 collation.
178 $rowformat = $DB->get_row_format_sql($engine, $collation);
179
fdd57d4f
PS
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
0bbefd81
AG
184 $sql = "ALTER TABLE $table->name
185 MODIFY COLUMN $column->field $column->type
186 CHARACTER SET $charset
187 COLLATE $collation $notnull $default";
fdd57d4f
PS
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'" : '';
0bbefd81
AG
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";
fdd57d4f
PS
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
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
271function mysql_get_collations() {
272 global $DB;
273
274 $collations = array();
0bbefd81
AG
275 $sql = "SHOW COLLATION
276 WHERE Collation LIKE 'utf8\_%' AND Charset = 'utf8'
277 OR Collation LIKE 'utf8mb4\_%' AND Charset = 'utf8mb4'";
fdd57d4f
PS
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
292function 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}
c05dc071
AG
306
307function 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}