[Summary view]

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

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