Python utils.mysql_query函数代码示例

 def test_stop_replication(self):
   utils.debug("===========test_stop_replication=========")
   utils.run_vtctl('ChangeSlaveType test_nj-0000062345 replica')
   time.sleep(10)
   perform_insert(100)
   master_position = utils.mysql_query(62344, 'vt_test_keyspace', 'show master status')
   #The sleep is needed here, so the invalidator can catch up and the number can be tested.
   replica_tablet.mquery('vt_test_keyspace', "select MASTER_POS_WAIT('%s', %d)" % (master_position[0][0], master_position[0][1]), 5)
   time.sleep(5)
   inv_count1 = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/table_stats" % replica_host)))['Totals']['Invalidations']
   replica_tablet.mquery('vt_test_keyspace', "stop slave")
   perform_insert(100)
   # EOF is returned after 30s, sleeping a bit more to ensure we catch the EOF
   # and can test replication stop effectively.
   time.sleep(35)
   replica_tablet.mquery('vt_test_keyspace', "start slave")
   master_position = utils.mysql_query(62344, 'vt_test_keyspace', 'show master status')
   #The sleep is needed here, so the invalidator can catch up and the number can be tested.
   replica_tablet.mquery('vt_test_keyspace', "select MASTER_POS_WAIT('%s', %d)" % (master_position[0][0], master_position[0][1]), 5)
   time.sleep(10)
   invalidatorStats = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/vars" % replica_host)))['CacheInvalidationProcessor']
   utils.debug("invalidatorStats %s" % invalidatorStats)
   inv_count2 = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/table_stats" % replica_host)))['Totals']['Invalidations']
   utils.debug("invalidator count1 %d count2 %d" % (inv_count1, inv_count2))
   self.assertEqual(invalidatorStats["States"]["Current"], "Enabled", "Row-cache invalidator should be enabled")
   self.assertTrue(inv_count2 - inv_count1 > 0, "invalidator was able to restart after a small pause in replication")

  def test_stop_replication(self):
    # restart the replica tablet so the stats are reset
    replica_tablet.kill_vttablet()
    replica_tablet.start_vttablet(memcache=True)

    # insert 100 values, should cause 100 invalidations
    self.perform_insert(100)
    master_position = utils.mysql_query(master_tablet.tablet_uid,
                                        'vt_test_keyspace',
                                        'show master status')
    replica_tablet.mquery('vt_test_keyspace',
                          "select MASTER_POS_WAIT('%s', %d)" %
                          (master_position[0][0], master_position[0][1]), 5)

    # wait until the slave processed all data
    for timeout in xrange(300):
      time.sleep(0.1)
      inv_count1 = self.replica_stats()['Totals']['Invalidations']
      logging.debug("Got %u invalidations" % inv_count1)
      if inv_count1 == 100:
        break
    inv_count1 = self.replica_stats()['Totals']['Invalidations']
    self.assertEqual(inv_count1, 100,
                     "Unexpected number of invalidations: %u" % inv_count1)

    # stop replication insert more data, restart replication
    replica_tablet.mquery('vt_test_keyspace', "stop slave")
    self.perform_insert(100)
    time.sleep(2)
    replica_tablet.mquery('vt_test_keyspace', "start slave")
    master_position = utils.mysql_query(master_tablet.tablet_uid,
                                        'vt_test_keyspace',
                                        'show master status')
    replica_tablet.mquery('vt_test_keyspace',
                          "select MASTER_POS_WAIT('%s', %d)" %
                          (master_position[0][0], master_position[0][1]), 5)

    # wait until the slave processed all data
    for timeout in xrange(300):
      time.sleep(0.1)
      inv_count2 = self.replica_stats()['Totals']['Invalidations']
      logging.debug("Got %u invalidations" % inv_count2)
      if inv_count2 == 200:
        break
    inv_count2 = self.replica_stats()['Totals']['Invalidations']
    self.assertEqual(inv_count2, 200, "Unexpected number of invalidations: %u" %
                     inv_count2)

    # check and display some stats
    invalidatorStats = self.replica_vars()
    logging.debug("invalidatorStats %s" %
                  invalidatorStats['RowcacheInvalidationCheckPoint'])
    self.assertEqual(invalidatorStats["RowcacheInvalidationState"], "Enabled",
                     "Row-cache invalidator should be enabled")

  def test_outofband_statements(self):
    start = self.replica_vars()['InternalErrors'].get('Invalidation', 0)

    # Test update statement
    self._exec_vt_txn(
        "insert into vt_insert_test (id, msg) values (1000000, 'start')")
    self._wait_for_replica()
    self._wait_for_value([[1000000, 'start']])
    utils.mysql_write_query(
        master_tablet.tablet_uid,
        'vt_test_keyspace',
        "update vt_insert_test set msg = 'foo' where id = 1000000")
    self._wait_for_replica()
    self._wait_for_value([[1000000, 'foo']])
    end1 = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
    self.assertEqual(start, end1)

    # Test delete statement
    utils.mysql_write_query(master_tablet.tablet_uid,
                            'vt_test_keyspace',
                            'delete from vt_insert_test where id = 1000000')
    self._wait_for_replica()
    self._wait_for_value([])
    end2 = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
    self.assertEqual(end1, end2)

    # Test insert statement
    utils.mysql_write_query(
        master_tablet.tablet_uid,
        'vt_test_keyspace',
        "insert into vt_insert_test (id, msg) values(1000000, 'bar')")
    self._wait_for_replica()
    self._wait_for_value([[1000000, 'bar']])
    end3 = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
    self.assertEqual(end2, end3)

    # Test unrecognized statement
    utils.mysql_query(master_tablet.tablet_uid,
                      'vt_test_keyspace',
                      'truncate table vt_insert_test')
    self._wait_for_replica()
    timeout = 10
    while True:
      end4 = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
      if end4 == end3+1:
        break
      timeout = utils.wait_step('invalidation errors, got %d expecting %d' %
                                (end4, end3+1), timeout, sleep_time=0.1)
    self.assertEqual(end4, end3+1)

  def test_outofband_statements(self):
    start = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
    self._exec_vt_txn(["insert into vt_insert_test (id, msg) values (1000000, 'start')"])
    self._wait_for_replica()
    time.sleep(1.0)

    # Test update statement
    result = self._exec_replica_query('select * from vt_insert_test where id = 1000000')
    self.assertEqual(result, [(1000000, 'start')])
    utils.mysql_write_query(master_tablet.tablet_uid,
                            'vt_test_keyspace',
                            "update vt_insert_test set msg = 'foo' where id = 1000000")
    self._wait_for_replica()
    time.sleep(1.0)
    result = self._exec_replica_query('select * from vt_insert_test where id = 1000000')
    self.assertEqual(result, [(1000000, 'foo')])
    end1 = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
    self.assertEqual(start, end1)

    # Test delete statement
    utils.mysql_write_query(master_tablet.tablet_uid,
                            'vt_test_keyspace',
                            'delete from vt_insert_test where id = 1000000')
    self._wait_for_replica()
    time.sleep(1.0)
    result = self._exec_replica_query('select * from vt_insert_test where id = 1000000')
    self.assertEqual(result, [])
    end2 = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
    self.assertEqual(end1, end2)

    # Test insert statement
    utils.mysql_write_query(master_tablet.tablet_uid,
                            'vt_test_keyspace',
                            "insert into vt_insert_test (id, msg) values(1000000, 'bar')")
    self._wait_for_replica()
    time.sleep(1.0)
    result = self._exec_replica_query('select * from vt_insert_test where id = 1000000')
    self.assertEqual(result, [(1000000, 'bar')])
    end3 = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
    self.assertEqual(end2, end3)

    # Test unrecognized statement
    utils.mysql_query(master_tablet.tablet_uid,
                      'vt_test_keyspace',
                       'truncate table vt_insert_test')
    self._wait_for_replica()
    time.sleep(1.0)
    end4 = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
    self.assertEqual(end4, end3+1)

  def test_cache_invalidation(self):
    utils.debug("===========test_cache_invalidation=========")
    master_position = utils.mysql_query(62344, 'vt_test_keyspace', 'show master status')
    #The sleep is needed here, so the invalidator can catch up and the number can be tested.
    replica_tablet.mquery('vt_test_keyspace', "select MASTER_POS_WAIT('%s', %d)" % (master_position[0][0], master_position[0][1]), 5)
    time.sleep(5)
    invalidations = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/table_stats" % replica_host)))['Totals']['Invalidations']
    invalidatorStats = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/vars" % replica_host)))['CacheInvalidationProcessor']
    utils.debug("Invalidations %d InvalidatorStats %s" % (invalidations, invalidatorStats))
    self.assertTrue(invalidations > 0, "Invalidations are flowing through.")

    res = replica_tablet.mquery('vt_test_keyspace', "select min(id) from vt_insert_test")
    self.assertNotEqual(res[0][0], None, "Cannot proceed, no rows in vt_insert_test")
    id = int(res[0][0])
    stats_dict = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/table_stats" % replica_host)))['vt_insert_test']
    utils.debug("vt_insert_test stats %s" % stats_dict)
    misses = stats_dict['Misses']
    hits = stats_dict["Hits"]
    replica_tablet.vquery("select * from vt_insert_test where id=%d" % (id), path='test_keyspace/0')
    stats_dict = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/table_stats" % replica_host)))['vt_insert_test']
    self.assertEqual(stats_dict['Misses'] - misses, 1, "This shouldn't have hit the cache")

    replica_tablet.vquery("select * from vt_insert_test where id=%d" % (id), path='test_keyspace/0')
    stats_dict = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/table_stats" % replica_host)))['vt_insert_test']
    self.assertEqual(stats_dict['Hits'] - hits, 1, "This should have hit the cache")

 def _wait_for_replica(self):
   master_position = utils.mysql_query(master_tablet.tablet_uid,
                                       'vt_test_keyspace',
                                       'show master status')
   replica_tablet.mquery('vt_test_keyspace',
                         "select MASTER_POS_WAIT('%s', %d)" %
                         (master_position[0][0], master_position[0][1]), 5)

  def test_cache_invalidation(self):
    master_position = utils.mysql_query(master_tablet.tablet_uid,
                                        'vt_test_keyspace',
                                        'show master status')
    replica_tablet.mquery('vt_test_keyspace',
                          "select MASTER_POS_WAIT('%s', %d)" %
                          (master_position[0][0], master_position[0][1]), 5)
    invalidations = self.replica_stats()['Totals']['Invalidations']
    invalidatorStats = self.replica_vars()
    logging.debug("Invalidations %d InvalidatorStats %s" %
                  (invalidations,
                   invalidatorStats['RowcacheInvalidationCheckPoint']))
    self.assertTrue(invalidations > 0, "Invalidations are flowing through.")

    res = replica_tablet.mquery('vt_test_keyspace',
                                "select min(id) from vt_insert_test")
    self.assertNotEqual(res[0][0], None,
                        "Cannot proceed, no rows in vt_insert_test")
    id = int(res[0][0])
    stats_dict = self.replica_stats()['vt_insert_test']
    logging.debug("vt_insert_test stats %s" % stats_dict)
    misses = stats_dict['Misses']
    hits = stats_dict["Hits"]
    replica_tablet.vquery("select * from vt_insert_test where id=%d" % (id),
                          path='test_keyspace/0')
    stats_dict = self.replica_stats()['vt_insert_test']
    self.assertEqual(stats_dict['Misses'] - misses, 1,
                     "This shouldn't have hit the cache")

    replica_tablet.vquery("select * from vt_insert_test where id=%d" % (id),
                          path='test_keyspace/0')
    stats_dict = self.replica_stats()['vt_insert_test']
    self.assertEqual(stats_dict['Hits'] - hits, 1,
                     "This should have hit the cache")

    def test_cache_invalidation(self):
        logging.debug("===========test_cache_invalidation=========")
        master_position = utils.mysql_query(62344, "vt_test_keyspace", "show master status")
        replica_tablet.mquery(
            "vt_test_keyspace", "select MASTER_POS_WAIT('%s', %d)" % (master_position[0][0], master_position[0][1]), 5
        )
        invalidations = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/table_stats" % replica_host)))[
            "Totals"
        ]["Invalidations"]
        invalidatorStats = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/vars" % replica_host)))
        logging.debug(
            "Invalidations %d InvalidatorStats %s" % (invalidations, invalidatorStats["RowcacheInvalidationCheckPoint"])
        )
        self.assertTrue(invalidations > 0, "Invalidations are flowing through.")

        res = replica_tablet.mquery("vt_test_keyspace", "select min(id) from vt_insert_test")
        self.assertNotEqual(res[0][0], None, "Cannot proceed, no rows in vt_insert_test")
        id = int(res[0][0])
        stats_dict = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/table_stats" % replica_host)))[
            "vt_insert_test"
        ]
        logging.debug("vt_insert_test stats %s" % stats_dict)
        misses = stats_dict["Misses"]
        hits = stats_dict["Hits"]
        replica_tablet.vquery("select * from vt_insert_test where id=%d" % (id), path="test_keyspace/0")
        stats_dict = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/table_stats" % replica_host)))[
            "vt_insert_test"
        ]
        self.assertEqual(stats_dict["Misses"] - misses, 1, "This shouldn't have hit the cache")

        replica_tablet.vquery("select * from vt_insert_test where id=%d" % (id), path="test_keyspace/0")
        stats_dict = framework.MultiDict(json.load(urllib2.urlopen("http://%s/debug/table_stats" % replica_host)))[
            "vt_insert_test"
        ]
        self.assertEqual(stats_dict["Hits"] - hits, 1, "This should have hit the cache")

 def test_invalidation_failure(self):
   start = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
   self.perform_insert(10)
   utils.mysql_write_query(master_tablet.tablet_uid,
                           'vt_test_keyspace',
                           "update vt_insert_test set msg = 'foo' where id = 1")
   self._wait_for_replica()
   time.sleep(1.0)
   end1 = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
   self.assertEqual(start+1, end1)
   utils.mysql_query(master_tablet.tablet_uid,
                     'vt_test_keyspace',
                      "truncate table vt_insert_test")
   self._wait_for_replica()
   time.sleep(1.0)
   end2 = self.replica_vars()['InternalErrors'].get('Invalidation', 0)
   self.assertEqual(end1+1, end2)

def _get_master_current_position():
  res = utils.mysql_query(62344, 'vt_test_keyspace', 'show master status')
  start_position = update_stream_service.BinlogPosition(res[0][0], res[0][1])
  return start_position.__dict__

def _get_master_current_position():
  return _make_default_gtid(utils.mysql_query(master_tablet.tablet_uid,
                                           'vt_test_keyspace',
                                           'show master status')[0][4])

def _get_master_current_position():
  return str(utils.mysql_query(62344, 'vt_test_keyspace', 'show master status')[0][4])

def _get_master_current_position():
    res = utils.mysql_query(62344, "vt_test_keyspace", "show master status")
    start_position = update_stream_service.Coord(res[0][0], res[0][1])
    return start_position.__dict__