Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
  • Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.
   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  }