admin/cli/mysql_engine.php

   1 <?php
   2
   3 // This file is part of Moodle - http://moodle.org/
   4 //
   5 // Moodle is free software: you can redistribute it and/or modify
   6 // it under the terms of the GNU General Public License as published by
   7 // the Free Software Foundation, either version 3 of the License, or
   8 // (at your option) any later version.
   9 //
  10 // Moodle is distributed in the hope that it will be useful,
  11 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  12 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13 // GNU General Public License for more details.
  14 //
  15 // You should have received a copy of the GNU General Public License
  16 // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  17
  18 /**
  19  * MySQL engine conversion tool.
  20  *
  21  * @package    core
  22  * @subpackage cli
  23  * @copyright  2009 Petr Skoda (http://skodak.org)
  24  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  25  */
  26
  27 define('CLI_SCRIPT', true);
  28
  29 require(__DIR__.'/../../config.php');
  30 require_once($CFG->libdir.'/clilib.php');      // cli only functions
  31
  32 if ($DB->get_dbfamily() !== 'mysql') {
  33     cli_error('This function is designed for MySQL databases only!');
  34 }
  35
  36 // now get cli options
  37 list($options, $unrecognized) = cli_get_params(array('help'=>false, 'list'=>false, 'engine'=>false, 'available'=>false),
  38                                                array('h'=>'help', 'l'=>'list', 'a'=>'available'));
  39
  40 if ($unrecognized) {
  41     $unrecognized = implode("\n  ", $unrecognized);
  42     cli_error(get_string('cliunknowoption', 'admin', $unrecognized));
  43 }
  44
  45 $help =
  46 "MySQL engine conversions script.
  47
  48 It is recommended to stop the web server before the conversion.
  49 Do not use MyISAM if possible, because it is not ACID compliant
  50 and does not support transactions.
  51
  52 Options:
  53 --engine=ENGINE       Convert MySQL tables to different engine
  54 -l, --list            Show table information
  55 -a, --available       Show list of available engines
  56 -h, --help            Print out this help
  57
  58 Example:
  59 \$sudo -u www-data /usr/bin/php admin/cli/mysql_engine.php --engine=InnoDB
  60 ";
  61
  62 if (!empty($options['engine'])) {
  63     $engines = mysql_get_engines();
  64     $engine = clean_param($options['engine'], PARAM_ALPHA);
  65     if (!isset($engines[strtoupper($engine)])) {
  66         cli_error("Error: engine '$engine' is not available on this server!");
  67     }
  68
  69     echo "Converting tables to '$engine' for $CFG->wwwroot:\n";
  70     $prefix = $DB->get_prefix();
  71     $prefix = str_replace('_', '\\_', $prefix);
  72     $sql = "SHOW TABLE STATUS WHERE Name LIKE BINARY '$prefix%'";
  73     $rs = $DB->get_recordset_sql($sql);
  74     $converted = 0;
  75     $skipped   = 0;
  76     $errors    = 0;
  77     foreach ($rs as $table) {
  78         if (strtoupper($table->engine) === strtoupper($engine)) {
  79             $newengine = mysql_get_table_engine($table->name);
  80             echo str_pad($table->name, 40). " - NO CONVERSION NEEDED ($newengine)\n";
  81             $skipped++;
  82             continue;
  83         }
  84         echo str_pad($table->name, 40). " - ";
  85
  86         try {
  87             $DB->change_database_structure("ALTER TABLE {$table->name} ENGINE = $engine");
  88             $newengine = mysql_get_table_engine($table->name);
  89             if (strtoupper($newengine) !== strtoupper($engine)) {
  90                 echo "ERROR ($newengine)\n";
  91                 $errors++;
  92                 continue;
  93             }
  94             echo "DONE ($newengine)\n";
  95             $converted++;
  96         } catch (moodle_exception $e) {
  97             echo $e->getMessage()."\n";
  98             $errors++;
  99             continue;
 100         }
 101     }
 102     $rs->close();
 103     echo "Converted: $converted, skipped: $skipped, errors: $errors\n";
 104     exit(0); // success
 105
 106 } else if (!empty($options['list'])) {
 107     echo "List of tables for $CFG->wwwroot:\n";
 108     $prefix = $DB->get_prefix();
 109     $prefix = str_replace('_', '\\_', $prefix);
 110     $sql = "SHOW TABLE STATUS WHERE Name LIKE BINARY '$prefix%'";
 111     $rs = $DB->get_recordset_sql($sql);
 112     $counts = array();
 113     foreach ($rs as $table) {
 114         if (isset($counts[$table->engine])) {
 115             $counts[$table->engine]++;
 116         } else {
 117             $counts[$table->engine] = 1;
 118         }
 119         echo str_pad($table->engine, 10);
 120         echo $table->name .  "\n";
 121     }
 122     $rs->close();
 123
 124     echo "\n";
 125     echo "Table engines summary for $CFG->wwwroot:\n";
 126     foreach ($counts as $engine => $count) {
 127         echo "$engine: $count\n";
 128     }
 129     exit(0); // success
 130
 131 } else if (!empty($options['available'])) {
 132     echo "List of available MySQL engines for $CFG->wwwroot:\n";
 133     $engines = mysql_get_engines();
 134     foreach ($engines as $engine) {
 135         echo " $engine\n";
 136     }
 137     die;
 138
 139 } else {
 140     echo $help;
 141     die;
 142 }
 143
 144
 145
 146 // ========== Some functions ==============
 147
 148 function mysql_get_engines() {
 149     global $DB;
 150
 151     $sql = "SHOW Engines";
 152     $rs = $DB->get_recordset_sql($sql);
 153     $engines = array();
 154     foreach ($rs as $engine) {
 155         if (strtoupper($engine->support) !== 'YES' and strtoupper($engine->support) !== 'DEFAULT') {
 156             continue;
 157         }
 158         $engines[strtoupper($engine->engine)] = $engine->engine;
 159         if (strtoupper($engine->support) === 'DEFAULT') {
 160             $engines[strtoupper($engine->engine)] .= ' (default)';
 161         }
 162     }
 163     $rs->close();
 164
 165     return $engines;
 166 }
 167
 168 function mysql_get_table_engine($tablename) {
 169     global $DB;
 170
 171     $engine = null;
 172     $sql = "SHOW TABLE STATUS WHERE Name = '$tablename'"; // no special chars expected here
 173     $rs = $DB->get_recordset_sql($sql);
 174     if ($rs->valid()) {
 175         $record = $rs->current();
 176         $engine = $record->engine;
 177     }
 178     $rs->close();
 179     return $engine;
 180 }