[Summary view]

Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

   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   * DML read/read-write database handle use tests
  19   *
  20   * @package    core
  21   * @category   dml
  22   * @copyright  2018 Srdjan Janković, Catalyst IT
  23   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24   */
  25  
  26  defined('MOODLE_INTERNAL') || die();
  27  
  28  require_once (__DIR__.'/fixtures/read_slave_moodle_database_table_names.php');
  29  require_once (__DIR__.'/fixtures/read_slave_moodle_database_special.php');
  30  
  31  /**
  32   * DML read/read-write database handle use tests
  33   *
  34   * @package    core
  35   * @category   dml
  36   * @copyright  2018 Catalyst IT
  37   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  38   * @covers     \moodle_read_slave_trait
  39   */
  40  class dml_read_slave_test extends base_testcase {
  41  
  42      /** @var float */
  43      static private $dbreadonlylatency = 0.8;
  44  
  45      /**
  46       * Instantiates a test database interface object.
  47       *
  48       * @param bool $wantlatency
  49       * @param mixed $readonly
  50       * @param mixed $dbclass
  51       * @return read_slave_moodle_database $db
  52       */
  53      public function new_db(
  54          $wantlatency = false,
  55          $readonly = [
  56              ['dbhost' => 'test_ro1', 'dbport' => 1, 'dbuser' => 'test1', 'dbpass' => 'test1'],
  57              ['dbhost' => 'test_ro2', 'dbport' => 2, 'dbuser' => 'test2', 'dbpass' => 'test2'],
  58              ['dbhost' => 'test_ro3', 'dbport' => 3, 'dbuser' => 'test3', 'dbpass' => 'test3'],
  59          ],
  60          $dbclass = read_slave_moodle_database::class
  61      ) : read_slave_moodle_database {
  62          $dbhost = 'test_rw';
  63          $dbname = 'test';
  64          $dbuser = 'test';
  65          $dbpass = 'test';
  66          $prefix = 'test_';
  67          $dboptions = ['readonly' => ['instance' => $readonly, 'exclude_tables' => ['exclude']]];
  68          if ($wantlatency) {
  69              $dboptions['readonly']['latency'] = self::$dbreadonlylatency;
  70          }
  71  
  72          $db = new $dbclass();
  73          $db->connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
  74          return $db;
  75      }
  76  
  77      /**
  78       * Asert that the mock handle returned from read_slave_moodle_database methods
  79       * is a readonly slave handle.
  80       *
  81       * @param string $handle
  82       * @return void
  83       */
  84      private function assert_readonly_handle($handle) : void {
  85          $this->assertMatchesRegularExpression('/^test_ro\d:\d:test\d:test\d$/', $handle);
  86      }
  87  
  88      /**
  89       * moodle_read_slave_trait::table_names() test data provider
  90       *
  91       * @return array
  92       * @dataProvider table_names_provider
  93       */
  94      public function table_names_provider() : array {
  95          return [
  96              [
  97                  "SELECT *
  98                   FROM {user} u
  99                   JOIN (
 100                       SELECT DISTINCT u.id FROM {user} u
 101                       JOIN {user_enrolments} ue1 ON ue1.userid = u.id
 102                       JOIN {enrol} e ON e.id = ue1.enrolid
 103                       WHERE u.id NOT IN (
 104                           SELECT DISTINCT ue.userid FROM {user_enrolments} ue
 105                           JOIN {enrol} e ON (e.id = ue.enrolid AND e.courseid = 1)
 106                           WHERE ue.status = 'active'
 107                             AND e.status = 'enabled'
 108                             AND ue.timestart < now()
 109                             AND (ue.timeend = 0 OR ue.timeend > now())
 110                       )
 111                   ) je ON je.id = u.id
 112                   JOIN (
 113                       SELECT DISTINCT ra.userid
 114                         FROM {role_assignments} ra
 115                        WHERE ra.roleid IN (1, 2, 3)
 116                          AND ra.contextid = 'ctx'
 117                    ) rainner ON rainner.userid = u.id
 118                    WHERE u.deleted = 0",
 119                  [
 120                      'user',
 121                      'user',
 122                      'user_enrolments',
 123                      'enrol',
 124                      'user_enrolments',
 125                      'enrol',
 126                      'role_assignments',
 127                  ]
 128              ],
 129          ];
 130      }
 131  
 132      /**
 133       * Test moodle_read_slave_trait::table_names() query parser.
 134       *
 135       * @param string $sql
 136       * @param array $tables
 137       * @return void
 138       * @dataProvider table_names_provider
 139       */
 140      public function test_table_names($sql, $tables) : void {
 141          $db = new read_slave_moodle_database_table_names();
 142  
 143          $this->assertEquals($tables, $db->table_names($db->fix_sql_params($sql)[0]));
 144      }
 145  
 146      /**
 147       * Test correct database handles are used in a read-read-write-read scenario.
 148       * Test lazy creation of the write handle.
 149       *
 150       * @return void
 151       */
 152      public function test_read_read_write_read() : void {
 153          $DB = $this->new_db(true);
 154  
 155          $this->assertEquals(0, $DB->perf_get_reads_slave());
 156          $this->assertNull($DB->get_dbhwrite());
 157  
 158          $handle = $DB->get_records('table');
 159          $this->assert_readonly_handle($handle);
 160          $readsslave = $DB->perf_get_reads_slave();
 161          $this->assertGreaterThan(0, $readsslave);
 162          $this->assertNull($DB->get_dbhwrite());
 163  
 164          $handle = $DB->get_records('table2');
 165          $this->assert_readonly_handle($handle);
 166          $readsslave = $DB->perf_get_reads_slave();
 167          $this->assertGreaterThan(1, $readsslave);
 168          $this->assertNull($DB->get_dbhwrite());
 169  
 170          $now = microtime(true);
 171          $handle = $DB->insert_record_raw('table', array('name' => 'blah'));
 172          $this->assertEquals('test_rw::test:test', $handle);
 173  
 174          if (microtime(true) - $now < self::$dbreadonlylatency) {
 175              $handle = $DB->get_records('table');
 176              $this->assertEquals('test_rw::test:test', $handle);
 177              $this->assertEquals($readsslave, $DB->perf_get_reads_slave());
 178  
 179              sleep(1);
 180          }
 181  
 182          $handle = $DB->get_records('table');
 183          $this->assert_readonly_handle($handle);
 184          $this->assertEquals($readsslave + 1, $DB->perf_get_reads_slave());
 185      }
 186  
 187      /**
 188       * Test correct database handles are used in a read-write-write scenario.
 189       *
 190       * @return void
 191       */
 192      public function test_read_write_write() : void {
 193          $DB = $this->new_db();
 194  
 195          $this->assertEquals(0, $DB->perf_get_reads_slave());
 196          $this->assertNull($DB->get_dbhwrite());
 197  
 198          $handle = $DB->get_records('table');
 199          $this->assert_readonly_handle($handle);
 200          $readsslave = $DB->perf_get_reads_slave();
 201          $this->assertGreaterThan(0, $readsslave);
 202          $this->assertNull($DB->get_dbhwrite());
 203  
 204          $handle = $DB->insert_record_raw('table', array('name' => 'blah'));
 205          $this->assertEquals('test_rw::test:test', $handle);
 206  
 207          $handle = $DB->update_record_raw('table', array('id' => 1, 'name' => 'blah2'));
 208          $this->assertEquals('test_rw::test:test', $handle);
 209          $this->assertEquals($readsslave, $DB->perf_get_reads_slave());
 210      }
 211  
 212      /**
 213       * Test correct database handles are used in a write-read-read scenario.
 214       *
 215       * @return void
 216       */
 217      public function test_write_read_read() : void {
 218          $DB = $this->new_db();
 219  
 220          $this->assertEquals(0, $DB->perf_get_reads_slave());
 221          $this->assertNull($DB->get_dbhwrite());
 222  
 223          $handle = $DB->insert_record_raw('table', array('name' => 'blah'));
 224          $this->assertEquals('test_rw::test:test', $handle);
 225          $this->assertEquals(0, $DB->perf_get_reads_slave());
 226  
 227          sleep(1);
 228          $handle = $DB->get_records('table');
 229          $this->assertEquals('test_rw::test:test', $handle);
 230          $this->assertEquals(0, $DB->perf_get_reads_slave());
 231  
 232          $handle = $DB->get_records('table2');
 233          $this->assert_readonly_handle($handle);
 234          $this->assertEquals(1, $DB->perf_get_reads_slave());
 235  
 236          $handle = $DB->get_records_sql("SELECT * FROM {table2} JOIN {table}");
 237          $this->assertEquals('test_rw::test:test', $handle);
 238          $this->assertEquals(1, $DB->perf_get_reads_slave());
 239      }
 240  
 241      /**
 242       * Test readonly handle is not used for reading from temptables.
 243       *
 244       * @return void
 245       */
 246      public function test_read_temptable() : void {
 247          $DB = $this->new_db();
 248          $DB->add_temptable('temptable1');
 249  
 250          $this->assertEquals(0, $DB->perf_get_reads_slave());
 251          $this->assertNull($DB->get_dbhwrite());
 252  
 253          $handle = $DB->get_records('temptable1');
 254          $this->assertEquals('test_rw::test:test', $handle);
 255          $this->assertEquals(0, $DB->perf_get_reads_slave());
 256  
 257          $DB->delete_temptable('temptable1');
 258      }
 259  
 260      /**
 261       * Test readonly handle is not used for reading from excluded tables.
 262       *
 263       * @return void
 264       */
 265      public function test_read_excluded_tables() : void {
 266          $DB = $this->new_db();
 267  
 268          $this->assertEquals(0, $DB->perf_get_reads_slave());
 269          $this->assertNull($DB->get_dbhwrite());
 270  
 271          $handle = $DB->get_records('exclude');
 272          $this->assertEquals('test_rw::test:test', $handle);
 273          $this->assertEquals(0, $DB->perf_get_reads_slave());
 274      }
 275  
 276      /**
 277       * Test readonly handle is not used during transactions.
 278       * Test last written time is adjusted post-transaction,
 279       * so the latency parameter is applied properly.
 280       *
 281       * @return void
 282       */
 283      public function test_transaction() : void {
 284          $DB = $this->new_db(true);
 285  
 286          $this->assertNull($DB->get_dbhwrite());
 287  
 288          $transaction = $DB->start_delegated_transaction();
 289          $now = microtime(true);
 290          $handle = $DB->get_records_sql("SELECT * FROM {table}");
 291          // Use rw handle during transaction.
 292          $this->assertEquals('test_rw::test:test', $handle);
 293  
 294          $handle = $DB->insert_record_raw('table', array('name' => 'blah'));
 295          // Introduce delay so we can check that table write timestamps
 296          // are adjusted properly.
 297          sleep(1);
 298          $transaction->allow_commit();
 299          // This condition should always evaluate true, however we need to
 300          // safeguard from an unaccounted delay that can break this test.
 301          if (microtime(true) - $now < 1 + self::$dbreadonlylatency) {
 302              // Not enough time passed, use rw handle.
 303              $handle = $DB->get_records_sql("SELECT * FROM {table}");
 304              $this->assertEquals('test_rw::test:test', $handle);
 305  
 306              // Make sure enough time passes.
 307              sleep(1);
 308          }
 309  
 310          // Exceeded latency time, use ro handle.
 311          $handle = $DB->get_records_sql("SELECT * FROM {table}");
 312          $this->assert_readonly_handle($handle);
 313      }
 314  
 315      /**
 316       * Test readonly handle is not used immediately after update
 317       * Test last written time is adjusted post-write,
 318       * so the latency parameter is applied properly.
 319       *
 320       * @return void
 321       * @covers ::can_use_readonly
 322       * @covers ::query_end
 323       */
 324      public function test_long_update(): void {
 325          $DB = $this->new_db(true);
 326  
 327          $this->assertNull($DB->get_dbhwrite());
 328  
 329          $skip = false;
 330  
 331          list($sql, $params, $ptype) = $DB->fix_sql_params("UPDATE {table} SET a = 1 WHERE id = 1");
 332          $DB->with_query_start_end($sql, $params, SQL_QUERY_UPDATE, function ($dbh) use (&$now) {
 333              sleep(1);
 334              $now = microtime(true);
 335          });
 336  
 337          // This condition should always evaluate true, however we need to
 338          // safeguard from an unaccounted delay that can break this test.
 339          if (microtime(true) - $now < self::$dbreadonlylatency) {
 340              // Not enough time passed, use rw handle.
 341              $handle = $DB->get_records_sql("SELECT * FROM {table}");
 342              $this->assertEquals('test_rw::test:test', $handle);
 343  
 344              // Make sure enough time passes.
 345              sleep(1);
 346          } else {
 347              $skip = true;
 348          }
 349  
 350          // Exceeded latency time, use ro handle.
 351          $handle = $DB->get_records_sql("SELECT * FROM {table}");
 352          $this->assert_readonly_handle($handle);
 353  
 354          if ($skip) {
 355              $this->markTestSkipped("Delay too long to test write handle immediately after transaction");
 356          }
 357      }
 358  
 359      /**
 360       * Test failed readonly connection falls back to write connection.
 361       *
 362       * @return void
 363       */
 364      public function test_read_only_conn_fail() : void {
 365          $DB = $this->new_db(false, 'test_ro_fail');
 366  
 367          $this->assertEquals(0, $DB->perf_get_reads_slave());
 368          $this->assertNotNull($DB->get_dbhwrite());
 369  
 370          $handle = $DB->get_records('table');
 371          $this->assertEquals('test_rw::test:test', $handle);
 372          $readsslave = $DB->perf_get_reads_slave();
 373          $this->assertEquals(0, $readsslave);
 374      }
 375  
 376      /**
 377       * In multiple slaves scenario, test failed readonly connection falls back to
 378       * another readonly connection.
 379       *
 380       * @return void
 381       */
 382      public function test_read_only_conn_first_fail() : void {
 383          $DB = $this->new_db(false, ['test_ro_fail', 'test_ro_ok']);
 384  
 385          $this->assertEquals(0, $DB->perf_get_reads_slave());
 386          $this->assertNull($DB->get_dbhwrite());
 387  
 388          $handle = $DB->get_records('table');
 389          $this->assertEquals('test_ro_ok::test:test', $handle);
 390          $readsslave = $DB->perf_get_reads_slave();
 391          $this->assertEquals(1, $readsslave);
 392      }
 393  
 394      /**
 395       * Helper to restore global $DB
 396       *
 397       * @param callable $test
 398       * @return void
 399       */
 400      private function with_global_db($test) {
 401          global $DB;
 402  
 403          $dbsave = $DB;
 404          try {
 405              $test();
 406          }
 407          finally {
 408              $DB = $dbsave;
 409          }
 410      }
 411  
 412      /**
 413       * Test lock_db table exclusion
 414       *
 415       * @return void
 416       */
 417      public function test_lock_db() : void {
 418          $this->with_global_db(function () {
 419              global $DB;
 420  
 421              $DB = $this->new_db(true, ['test_ro'], read_slave_moodle_database_special::class);
 422              $DB->set_tables([
 423                  'lock_db' => [
 424                      'columns' => [
 425                          'resourcekey' => (object)['meta_type' => ''],
 426                          'owner' => (object)['meta_type' => ''],
 427                      ]
 428                  ]
 429              ]);
 430  
 431              $this->assertEquals(0, $DB->perf_get_reads_slave());
 432              $this->assertNull($DB->get_dbhwrite());
 433  
 434              $lockfactory = new \core\lock\db_record_lock_factory('default');
 435              if (!$lockfactory->is_available()) {
 436                  $this->markTestSkipped("db_record_lock_factory not available");
 437              }
 438  
 439              $lock = $lockfactory->get_lock('abc', 2);
 440              $lock->release();
 441              $this->assertEquals(0, $DB->perf_get_reads_slave());
 442              $this->assertTrue($DB->perf_get_reads() > 0);
 443          });
 444      }
 445  
 446      /**
 447       * Test sessions table exclusion
 448       *
 449       * @return void
 450       */
 451      public function test_sessions() : void {
 452          $this->with_global_db(function () {
 453              global $DB, $CFG;
 454  
 455              $CFG->dbsessions = true;
 456              $DB = $this->new_db(true, ['test_ro'], read_slave_moodle_database_special::class);
 457              $DB->set_tables([
 458                  'sessions' => [
 459                      'columns' => [
 460                          'sid' => (object)['meta_type' => ''],
 461                      ]
 462                  ]
 463              ]);
 464  
 465              $this->assertEquals(0, $DB->perf_get_reads_slave());
 466              $this->assertNull($DB->get_dbhwrite());
 467  
 468              $session = new \core\session\database();
 469              $session->handler_read('dummy');
 470  
 471              $this->assertEquals(0, $DB->perf_get_reads_slave());
 472              $this->assertTrue($DB->perf_get_reads() > 0);
 473          });
 474  
 475          \core\session\manager::restart_with_write_lock(false);
 476      }
 477  }