class TestCTLConnectionSettings(base.PyMySQLReplicationTestCase):
def setUp(self):
super(TestCTLConnectionSettings, self).setUp()
self.stream.close()
ctl_db = copy.copy(self.database)
ctl_db["db"] = None
ctl_db["port"] = 3307
self.ctl_conn_control = pymysql.connect(**ctl_db)
self.ctl_conn_control.cursor().execute(
"DROP DATABASE IF EXISTS pymysqlreplication_test")
self.ctl_conn_control.cursor().execute(
"CREATE DATABASE pymysqlreplication_test")
self.ctl_conn_control.close()
ctl_db["db"] = "pymysqlreplication_test"
self.ctl_conn_control = pymysql.connect(**ctl_db)
self.stream = BinLogStreamReader(
self.database,
ctl_connection_settings=ctl_db,
server_id=1024,
only_events=(WriteRowsEvent, ),
fail_on_table_metadata_unavailable=True)
def tearDown(self):
super(TestCTLConnectionSettings, self).tearDown()
self.ctl_conn_control.close()
def test_separate_ctl_settings_table_metadata_unavailable(self):
self.execute("CREATE TABLE test (id INTEGER(11))")
self.execute("INSERT INTO test VALUES (1)")
self.execute("COMMIT")
had_error = False
try:
event = self.stream.fetchone()
except TableMetadataUnavailableError as e:
had_error = True
assert "test" in e.args[0]
finally:
self.resetBinLog()
assert had_error
def test_separate_ctl_settings_no_error(self):
self.execute("CREATE TABLE test (id INTEGER(11))")
self.execute("INSERT INTO test VALUES (1)")
self.execute("DROP TABLE test")
self.execute("COMMIT")
self.ctl_conn_control.cursor().execute(
"CREATE TABLE test (id INTEGER(11))")
self.ctl_conn_control.cursor().execute("INSERT INTO test VALUES (1)")
self.ctl_conn_control.cursor().execute("COMMIT")
try:
self.stream.fetchone()
except Exception as e:
self.fail(
"raised unexpected exception: {exception}".format(exception=e))
finally:
self.resetBinLog()
class TestCTLConnectionSettings(base.PyMySQLReplicationTestCase):
def setUp(self):
super(TestCTLConnectionSettings, self).setUp()
self.stream.close()
ctl_db = copy.copy(self.database)
ctl_db["db"] = None
ctl_db["port"] = 3307
self.ctl_conn_control = pymysql.connect(**ctl_db)
self.ctl_conn_control.cursor().execute("DROP DATABASE IF EXISTS pymysqlreplication_test")
self.ctl_conn_control.cursor().execute("CREATE DATABASE pymysqlreplication_test")
self.ctl_conn_control.close()
ctl_db["db"] = "pymysqlreplication_test"
self.ctl_conn_control = pymysql.connect(**ctl_db)
self.stream = BinLogStreamReader(
self.database,
ctl_connection_settings=ctl_db,
server_id=1024,
only_events=(WriteRowsEvent,),
fail_on_table_metadata_unavailable=True
)
def tearDown(self):
super(TestCTLConnectionSettings, self).tearDown()
self.ctl_conn_control.close()
def test_seperate_ctl_settings_table_metadata_unavailable(self):
self.execute("CREATE TABLE test (id INTEGER(11))")
self.execute("INSERT INTO test VALUES (1)")
self.execute("COMMIT")
had_error = False
try:
event = self.stream.fetchone()
except TableMetadataUnavailableError as e:
had_error = True
assert "test" in e.args[0]
finally:
self.resetBinLog()
assert had_error
def test_seperate_ctl_settings_no_error(self):
self.execute("CREATE TABLE test (id INTEGER(11))")
self.execute("INSERT INTO test VALUES (1)")
self.execute("DROP TABLE test")
self.execute("COMMIT")
self.ctl_conn_control.cursor().execute("CREATE TABLE test (id INTEGER(11))")
self.ctl_conn_control.cursor().execute("INSERT INTO test VALUES (1)")
self.ctl_conn_control.cursor().execute("COMMIT")
try:
self.stream.fetchone()
except Exception as e:
self.fail("raised unexpected exception: {exception}".format(exception=e))
finally:
self.resetBinLog()
class TestGtidBinLogStreamReader(base.PyMySQLReplicationTestCase):
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "SELECT @@global.gtid_executed;"
gtid = self.execute(query).fetchone()[0]
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
blocking=True,
auto_position=gtid)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# Insert first event
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(1, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
firstevent = self.stream.fetchone()
self.assertIsInstance(firstevent, GtidEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
# Insert second event
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(2, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
secondevent = self.stream.fetchone()
self.assertIsInstance(secondevent, GtidEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
self.assertEqual(secondevent.gno, firstevent.gno + 1)
def test_position_gtid(self):
query = "CREATE TABLE test (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(1, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
query = "CREATE TABLE test2 (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "SELECT @@global.gtid_executed;"
gtid = self.execute(query).fetchone()[0]
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
blocking=True,
auto_position=gtid)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertIsInstance(self.stream.fetchone(), GtidEvent)
event = self.stream.fetchone()
self.assertEqual(
event.query,
'CREATE TABLE test2 (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))'
)
class TestBasicBinLogStreamReader(base.PyMySQLReplicationTestCase):
def ignoredEvents(self):
return [GtidEvent]
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog, "mysql-bin.000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_read_query_event_with_unicode(self):
query = u"CREATE TABLE `testÈ` (id INT NOT NULL AUTO_INCREMENT, dataÈ VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog, "mysql-bin.000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_reading_rotate_event(self):
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.stream.close()
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
# Rotate event
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
def test_connection_stream_lost_event(self):
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
blocking=True,
ignored_events=self.ignoredEvents())
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query2 = "INSERT INTO test (data) VALUES('a')"
for i in range(0, 10000):
self.execute(query2)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
self.conn_control.kill(self.stream._stream_connection.thread_id())
for i in range(0, 1000):
event = self.stream.fetchone()
self.assertIsNotNone(event)
def test_filtering_events(self):
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
only_events=[QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the Create Table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, 'data')
def test_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
def test_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
def test_log_pos(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
for i in range(6):
self.stream.fetchone()
# record position after insert
log_file, log_pos = self.stream.log_file, self.stream.log_pos
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
# resume stream from previous position
if self.stream is not None:
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
resume_stream=True,
log_file=log_file,
log_pos=log_pos,
ignored_events=self.ignoredEvents())
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), UpdateRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
def test_log_pos_handles_disconnects(self):
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
resume_stream=False,
only_events=[
FormatDescriptionEvent,
QueryEvent, TableMapEvent,
WriteRowsEvent, XidEvent
])
query = "CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR (50) NOT NULL)"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertGreater(self.stream.log_pos, 0)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
self.assertGreater(self.stream.log_pos, 0)
def handle_binlog_stream(config):
cache = Cache(config.SLAVE_UUID)
# 该操作可以关闭旧有binlog连接
stream_binlog = BinLogStreamReader(
connection_settings=config.BINLOG_CONNECTION,
server_id=config.SERVER_ID,
blocking=False,
resume_stream=True,
slave_uuid=config.SLAVE_UUID
)
stream_binlog.fetchone()
only_schemas = set()
only_tables = set()
event2jobs = defaultdict(list)
for task in config.TASKS:
only_schemas.add(task["stream"]["database"])
only_tables.add(task["stream"]["table"])
for job in task["jobs"]:
for action in job["actions"]:
event = "{host}_{schema}_{table}_{action}".format(host=config.BINLOG_CONNECTION["host"],
schema=task["stream"]["database"], table=task["stream"]["table"], action=action)
event2jobs[event].append(job)
stream_binlog = BinLogStreamReader(
connection_settings=config.BINLOG_CONNECTION,
server_id=config.SERVER_ID,
blocking=True,
only_events=[WriteRowsEvent, UpdateRowsEvent, DeleteRowsEvent, RotateEvent], only_schemas=only_schemas,
only_tables=only_tables,
freeze_schema=True,
log_file=cache.get_log_file(),
log_pos=cache.get_log_pos(),
resume_stream=True,
slave_uuid=config.SLAVE_UUID
)
for binlogevent in stream_binlog:
if isinstance(binlogevent, RotateEvent):
cache.set_log_file(binlogevent.next_binlog)
cache.set_log_pos(binlogevent.position)
else:
print binlogevent.packet.log_pos
for row in binlogevent.rows:
event = {"host": binlogevent._ctl_connection.host, "schema": binlogevent.schema,
"table": binlogevent.table,
"timestamp": datetime.datetime.fromtimestamp(binlogevent.timestamp).strftime('%Y-%m-%d %H:%M:%S')}
# 组装event
if isinstance(binlogevent, DeleteRowsEvent):
event["action"] = "delete"
event["values"] = dict(row["values"].items())
elif isinstance(binlogevent, UpdateRowsEvent):
event["action"] = "update"
event["before_values"] = dict(row["before_values"].items())
event["values"] = dict(row["after_values"].items())
elif isinstance(binlogevent, WriteRowsEvent):
event["action"] = "insert"
event["values"] = dict(row["values"].items())
event_type = "{host}_{schema}_{table}_{action}".format(host=event["host"], schema=event["schema"],
table=event["table"], action=event["action"])
jobs = event2jobs[event_type]
for job in jobs:
if event["action"] in job["actions"]:
pipeline = job["pipeline"]
rows = do_pipeline(pipeline, event["values"])
dest = job["dest"]
if isinstance(dest, list):
for d in dest:
to_dest(d, rows)
else:
to_dest(dest, rows)
cache.set_log_pos(binlogevent.packet.log_pos)
logging.info(json.dumps(event, cls=DateEncoder))
class TestMultipleRowBinLogStreamReader(base.PyMySQLReplicationTestCase):
def ignoredEvents(self):
return [GtidEvent]
def test_insert_multiple_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.resetBinLog()
query = "INSERT INTO test (data) VALUES('Hello'),('World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(len(event.rows), 2)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello")
self.assertEqual(event.rows[1]["values"]["id"], 2)
self.assertEqual(event.rows[1]["values"]["data"], "World")
def test_update_multiple_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
query = "INSERT INTO test (data) VALUES('World')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'Toto'"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(len(event.rows), 2)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "Toto")
self.assertEqual(event.rows[1]["before_values"]["id"], 2)
self.assertEqual(event.rows[1]["before_values"]["data"], "World")
self.assertEqual(event.rows[1]["after_values"]["id"], 2)
self.assertEqual(event.rows[1]["after_values"]["data"], "Toto")
def test_delete_multiple_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
query = "INSERT INTO test (data) VALUES('World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(len(event.rows), 2)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello")
self.assertEqual(event.rows[1]["values"]["id"], 2)
self.assertEqual(event.rows[1]["values"]["data"], "World")
def test_drop_table(self):
self.execute("CREATE TABLE test (id INTEGER(11))")
self.execute("INSERT INTO test VALUES (1)")
self.execute("DROP TABLE test")
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the Create Table
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
#XidEvent
self.stream.fetchone()
#QueryEvent for the DROP
self.stream.fetchone()
# Verify no more events - means dropped table's event are skipped
event = self.stream.fetchone()
self.assertEqual(None, event)
def test_drop_table_tablemetadata_unavailable(self):
self.stream.close()
self.execute("CREATE TABLE test (id INTEGER(11))")
self.execute("INSERT INTO test VALUES (1)")
self.execute("DROP TABLE test")
self.execute("COMMIT")
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
only_events=(WriteRowsEvent, ),
fail_on_table_metadata_unavailable=True)
had_error = False
try:
event = self.stream.fetchone()
except TableMetadataUnavailableError as e:
had_error = True
assert "test" in e.args[0]
finally:
self.resetBinLog()
assert had_error
def test_drop_column(self):
self.stream.close()
self.execute(
"CREATE TABLE test_drop_column (id INTEGER(11), data VARCHAR(50))")
self.execute("INSERT INTO test_drop_column VALUES (1, 'A value')")
self.execute("COMMIT")
self.execute("ALTER TABLE test_drop_column DROP COLUMN data")
self.execute("INSERT INTO test_drop_column VALUES (2)")
self.execute("COMMIT")
self.stream = BinLogStreamReader(self.database,
server_id=1024,
only_events=(WriteRowsEvent, ))
try:
self.stream.fetchone() # insert with two values
self.stream.fetchone() # insert with one value
except Exception as e:
self.fail(
"raised unexpected exception: {exception}".format(exception=e))
finally:
self.resetBinLog()
@unittest.expectedFailure
def test_alter_column(self):
self.stream.close()
self.execute(
"CREATE TABLE test_alter_column (id INTEGER(11), data VARCHAR(50))"
)
self.execute("INSERT INTO test_alter_column VALUES (1, 'A value')")
self.execute("COMMIT")
# this is a problem only when column is added in position other than at the end
self.execute(
"ALTER TABLE test_alter_column ADD COLUMN another_data VARCHAR(50) AFTER id"
)
self.execute(
"INSERT INTO test_alter_column VALUES (2, 'Another value', 'A value')"
)
self.execute("COMMIT")
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
only_events=(WriteRowsEvent, ),
)
event = self.stream.fetchone() # insert with two values
# both of these asserts fail because of issue underlying proble described in issue #118
# because it got table schema info after the alter table, it wrongly assumes the second
# column of the first insert is 'another_data'
# ER: {'id': 1, 'data': 'A value'}
# AR: {'id': 1, 'another_data': 'A value'}
self.assertIn("data", event.rows[0]["values"])
self.assertNot("another_data", event.rows[0]["values"])
self.assertEqual(event.rows[0]["values"]["data"], 'A value')
self.stream.fetchone() # insert with three values
class TestBasicBinLogStreamReader(base.PyMySQLReplicationTestCase):
def ignoredEvents(self):
return [GtidEvent]
def test_allowed_event_list(self):
self.assertEqual(
len(self.stream._allowed_event_list(None, None, False)), 14)
self.assertEqual(
len(self.stream._allowed_event_list(None, None, True)), 13)
self.assertEqual(
len(self.stream._allowed_event_list(None, [RotateEvent], False)),
13)
self.assertEqual(
len(self.stream._allowed_event_list([RotateEvent], None, False)),
1)
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog,
self.bin_log_basename() + ".000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_read_query_event_with_unicode(self):
query = u"CREATE TABLE `testÈ` (id INT NOT NULL AUTO_INCREMENT, dataÈ VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog,
self.bin_log_basename() + ".000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_reading_rotate_event(self):
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.stream.close()
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
# Rotate event
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
""" `test_load_query_event` needs statement-based binlog
def test_load_query_event(self):
# prepare csv
with open("/tmp/test_load_query.csv", "w") as fp:
fp.write("1,aaa\n2,bbb\n3,ccc\n4,ddd\n")
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "LOAD DATA INFILE '/tmp/test_load_query.csv' INTO TABLE test \
FIELDS TERMINATED BY ',' \
ENCLOSED BY '\"' \
LINES TERMINATED BY '\r\n'"
self.execute(query)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# create table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
# begin
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), BeginLoadQueryEvent)
self.assertIsInstance(self.stream.fetchone(), ExecuteLoadQueryEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
"""
def test_connection_stream_lost_event(self):
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
blocking=True,
ignored_events=self.ignoredEvents())
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query2 = "INSERT INTO test (data) VALUES('a')"
for i in range(0, 10000):
self.execute(query2)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
self.conn_control.kill(self.stream._stream_connection.thread_id())
for i in range(0, 10000):
event = self.stream.fetchone()
self.assertIsNotNone(event)
def test_filtering_only_events(self):
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
only_events=[QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_filtering_ignore_events(self):
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
ignored_events=[QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, RotateEvent)
def test_filtering_table_event_with_only_tables(self):
self.stream.close()
self.assertEqual(self.bin_log_format(), "ROW")
self.stream = BinLogStreamReader(self.database,
server_id=1024,
only_events=[WriteRowsEvent],
only_tables=["test_2"])
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.execute("INSERT INTO test_2 (data) VALUES ('alpha')")
self.execute("INSERT INTO test_3 (data) VALUES ('alpha')")
self.execute("INSERT INTO test_2 (data) VALUES ('beta')")
self.execute("COMMIT")
event = self.stream.fetchone()
self.assertEqual(event.table, "test_2")
event = self.stream.fetchone()
self.assertEqual(event.table, "test_2")
def test_filtering_table_event_with_ignored_tables(self):
self.stream.close()
self.assertEqual(self.bin_log_format(), "ROW")
self.stream = BinLogStreamReader(self.database,
server_id=1024,
only_events=[WriteRowsEvent],
ignored_tables=["test_2"])
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.execute("INSERT INTO test_2 (data) VALUES ('alpha')")
self.execute("INSERT INTO test_3 (data) VALUES ('alpha')")
self.execute("INSERT INTO test_2 (data) VALUES ('beta')")
self.execute("COMMIT")
event = self.stream.fetchone()
self.assertEqual(event.table, "test_3")
def test_filtering_table_event_with_only_tables_and_ignored_tables(self):
self.stream.close()
self.assertEqual(self.bin_log_format(), "ROW")
self.stream = BinLogStreamReader(self.database,
server_id=1024,
only_events=[WriteRowsEvent],
only_tables=["test_2"],
ignored_tables=["test_3"])
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.execute("INSERT INTO test_2 (data) VALUES ('alpha')")
self.execute("INSERT INTO test_3 (data) VALUES ('alpha')")
self.execute("INSERT INTO test_2 (data) VALUES ('beta')")
self.execute("COMMIT")
event = self.stream.fetchone()
self.assertEqual(event.table, "test_2")
def test_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the Create Table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, 'data')
def test_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
def test_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
def test_minimal_image_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "SET SESSION binlog_row_image = 'minimal'"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the Create Table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, 'data')
def test_minimal_image_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
query = "SET SESSION binlog_row_image = 'minimal'"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], None)
def test_minimal_image_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
query = "SET SESSION binlog_row_image = 'minimal'"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], None)
self.assertEqual(event.rows[0]["after_values"]["id"], None)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
def test_log_pos(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
for i in range(6):
self.stream.fetchone()
# record position after insert
log_file, log_pos = self.stream.log_file, self.stream.log_pos
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
# resume stream from previous position
if self.stream is not None:
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
resume_stream=True,
log_file=log_file,
log_pos=log_pos,
ignored_events=self.ignoredEvents())
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), UpdateRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
def test_log_pos_handles_disconnects(self):
self.stream.close()
self.stream = BinLogStreamReader(self.database,
server_id=1024,
resume_stream=False,
only_events=[
FormatDescriptionEvent,
QueryEvent, TableMapEvent,
WriteRowsEvent, XidEvent
])
query = "CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR (50) NOT NULL)"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertGreater(self.stream.log_pos, 0)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
self.assertGreater(self.stream.log_pos, 0)
def test_skip_to_timestamp(self):
self.stream.close()
query = "CREATE TABLE test_1 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
time.sleep(1)
query = "SELECT UNIX_TIMESTAMP();"
timestamp = self.execute(query).fetchone()[0]
query2 = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query2)
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
skip_to_timestamp=timestamp,
ignored_events=self.ignoredEvents(),
)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query2)
class TestGtidBinLogStreamReader(base.PyMySQLReplicationTestCase):
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "SELECT @@global.gtid_executed;"
gtid = self.execute(query).fetchone()[0]
self.stream.close()
self.stream = BinLogStreamReader(self.database, server_id=1024, blocking=True, auto_position=gtid)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# Insert first event
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(1, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
firstevent = self.stream.fetchone()
self.assertIsInstance(firstevent, GtidEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
# Insert second event
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(2, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
secondevent = self.stream.fetchone()
self.assertIsInstance(secondevent, GtidEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
self.assertEqual(secondevent.gno, firstevent.gno + 1)
def test_position_gtid(self):
query = "CREATE TABLE test (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(1, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
query = "CREATE TABLE test2 (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "SELECT @@global.gtid_executed;"
gtid = self.execute(query).fetchone()[0]
self.stream.close()
self.stream = BinLogStreamReader(self.database, server_id=1024, blocking=True, auto_position=gtid)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertIsInstance(self.stream.fetchone(), GtidEvent)
event = self.stream.fetchone()
self.assertEqual(
event.query, "CREATE TABLE test2 (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
)
class TestBasicBinLogStreamReader(base.PyMySQLReplicationTestCase):
def ignoredEvents(self):
return [GtidEvent]
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog, "mysql-bin.000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_read_query_event_with_unicode(self):
query = u"CREATE TABLE `testÈ` (id INT NOT NULL AUTO_INCREMENT, dataÈ VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog, "mysql-bin.000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_reading_rotate_event(self):
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.stream.close()
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
# Rotate event
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
def test_connection_stream_lost_event(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database, server_id=1024, blocking=True, ignored_events=self.ignoredEvents()
)
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query2 = "INSERT INTO test (data) VALUES('a')"
for i in range(0, 10000):
self.execute(query2)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
self.conn_control.kill(self.stream._stream_connection.thread_id())
for i in range(0, 1000):
event = self.stream.fetchone()
self.assertIsNotNone(event)
def test_filtering_events(self):
self.stream.close()
self.stream = BinLogStreamReader(self.database, server_id=1024, only_events=[QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# QueryEvent for the Create Table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, "data")
def test_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
def test_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
def test_log_pos(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
for i in range(6):
self.stream.fetchone()
# record position after insert
log_file, log_pos = self.stream.log_file, self.stream.log_pos
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
# resume stream from previous position
if self.stream is not None:
self.stream.close()
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
resume_stream=True,
log_file=log_file,
log_pos=log_pos,
ignored_events=self.ignoredEvents(),
)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), UpdateRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
def test_log_pos_handles_disconnects(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
resume_stream=False,
only_events=[FormatDescriptionEvent, QueryEvent, TableMapEvent, WriteRowsEvent, XidEvent],
)
query = "CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR (50) NOT NULL)"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertGreater(self.stream.log_pos, 0)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
self.assertGreater(self.stream.log_pos, 0)
class TestBasicBinLogStreamReader(base.PyMySQLReplicationTestCase):
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_connection_lost_event(self):
self.stream.close()
self.stream = BinLogStreamReader(connection_settings=self.database,
blocking=True)
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query2 = "INSERT INTO test (data) VALUES('a')"
for i in range(0, 10000):
self.execute(query2)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
self.conn_control.kill(self.stream._stream_connection.thread_id())
for i in range(0, 1000):
event = self.stream.fetchone()
self.assertIsNotNone(event)
def test_filtering_events(self):
self.stream.close()
self.stream = BinLogStreamReader(connection_settings=self.database,
only_events=[QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the Create Table
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
self.assertEqual(event.event_type, WRITE_ROWS_EVENT)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, 'data')
def test_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
self.assertEqual(event.event_type, DELETE_ROWS_EVENT)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
def test_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
class TestMultipleRowBinLogStreamReader(base.PyMySQLReplicationTestCase):
def ignoredEvents(self):
return [GtidEvent]
def test_insert_multiple_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.resetBinLog()
query = "INSERT INTO test (data) VALUES('Hello'),('World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(len(event.rows), 2)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello")
self.assertEqual(event.rows[1]["values"]["id"], 2)
self.assertEqual(event.rows[1]["values"]["data"], "World")
def test_update_multiple_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
query = "INSERT INTO test (data) VALUES('World')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'Toto'"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(len(event.rows), 2)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "Toto")
self.assertEqual(event.rows[1]["before_values"]["id"], 2)
self.assertEqual(event.rows[1]["before_values"]["data"], "World")
self.assertEqual(event.rows[1]["after_values"]["id"], 2)
self.assertEqual(event.rows[1]["after_values"]["data"], "Toto")
def test_delete_multiple_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
query = "INSERT INTO test (data) VALUES('World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(len(event.rows), 2)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello")
self.assertEqual(event.rows[1]["values"]["id"], 2)
self.assertEqual(event.rows[1]["values"]["data"], "World")
def test_drop_table(self):
self.execute("CREATE TABLE test (id INTEGER(11))")
self.execute("INSERT INTO test VALUES (1)")
self.execute("DROP TABLE test")
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the Create Table
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual([], event.rows)
def test_drop_column(self):
self.stream.close()
self.execute("CREATE TABLE test_drop_column (id INTEGER(11), data VARCHAR(50))")
self.execute("INSERT INTO test_drop_column VALUES (1, 'A value')")
self.execute("COMMIT")
self.execute("ALTER TABLE test_drop_column DROP COLUMN data")
self.execute("INSERT INTO test_drop_column VALUES (2)")
self.execute("COMMIT")
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
only_events=(WriteRowsEvent,),
)
try:
self.stream.fetchone() # insert with two values
self.stream.fetchone() # insert with one value
except Exception as e:
self.fail("raised unexpected exception: {exception}".format(exception=e))
finally:
self.resetBinLog()
@unittest.expectedFailure
def test_alter_column(self):
self.stream.close()
self.execute("CREATE TABLE test_alter_column (id INTEGER(11), data VARCHAR(50))")
self.execute("INSERT INTO test_alter_column VALUES (1, 'A value')")
self.execute("COMMIT")
# this is a problem only when column is added in position other than at the end
self.execute("ALTER TABLE test_alter_column ADD COLUMN another_data VARCHAR(50) AFTER id")
self.execute("INSERT INTO test_alter_column VALUES (2, 'Another value', 'A value')")
self.execute("COMMIT")
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
only_events=(WriteRowsEvent,),
)
event = self.stream.fetchone() # insert with two values
# both of these asserts fail because of issue underlying proble described in issue #118
# because it got table schema info after the alter table, it wrongly assumes the second
# column of the first insert is 'another_data'
# ER: {'id': 1, 'data': 'A value'}
# AR: {'id': 1, 'another_data': 'A value'}
self.assertIn("data", event.rows[0]["values"])
self.assertNot("another_data", event.rows[0]["values"])
self.assertEqual(event.rows[0]["values"]["data"], 'A value')
self.stream.fetchone() # insert with three values
class TestBasicBinLogStreamReader(base.PyMySQLReplicationTestCase):
def ignoredEvents(self):
return [GtidEvent]
def test_allowed_event_list(self):
self.assertEqual(len(self.stream._allowed_event_list(None, None, False)), 13)
self.assertEqual(len(self.stream._allowed_event_list(None, None, True)), 12)
self.assertEqual(len(self.stream._allowed_event_list(None, [RotateEvent], False)), 12)
self.assertEqual(len(self.stream._allowed_event_list([RotateEvent], None, False)), 1)
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog, "mysql-bin.000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_read_query_event_with_unicode(self):
query = u"CREATE TABLE `testÈ` (id INT NOT NULL AUTO_INCREMENT, dataÈ VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertEqual(event.position, 4)
self.assertEqual(event.next_binlog, "mysql-bin.000001")
self.assertIsInstance(event, RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_reading_rotate_event(self):
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.stream.close()
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
# Rotate event
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
""" `test_load_query_event` needs statement-based binlog
def test_load_query_event(self):
# prepare csv
with open("/tmp/test_load_query.csv", "w") as fp:
fp.write("1,aaa\n2,bbb\n3,ccc\n4,ddd\n")
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "LOAD DATA INFILE '/tmp/test_load_query.csv' INTO TABLE test \
FIELDS TERMINATED BY ',' \
ENCLOSED BY '\"' \
LINES TERMINATED BY '\r\n'"
self.execute(query)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# create table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
# begin
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), BeginLoadQueryEvent)
self.assertIsInstance(self.stream.fetchone(), ExecuteLoadQueryEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
"""
def test_connection_stream_lost_event(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database, server_id=1024, blocking=True,
ignored_events=self.ignoredEvents())
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query2 = "INSERT INTO test (data) VALUES('a')"
for i in range(0, 10000):
self.execute(query2)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
self.conn_control.kill(self.stream._stream_connection.thread_id())
for i in range(0, 1000):
event = self.stream.fetchone()
self.assertIsNotNone(event)
def test_filtering_only_events(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database, server_id=1024, only_events=[QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_filtering_ignore_events(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database, server_id=1024, ignored_events=[QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, RotateEvent)
def test_filtering_table_event(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
only_events=[WriteRowsEvent],
only_tables = ["test_2"])
query = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "CREATE TABLE test_3 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.execute("INSERT INTO test_2 (data) VALUES ('alpha')")
self.execute("INSERT INTO test_3 (data) VALUES ('alpha')")
self.execute("INSERT INTO test_2 (data) VALUES ('beta')")
self.execute("COMMIT")
event = self.stream.fetchone()
self.assertEqual(event.table, "test_2")
event = self.stream.fetchone()
self.assertEqual(event.table, "test_2")
def test_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the Create Table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, 'data')
def test_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
def test_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
def test_minimal_image_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "SET SESSION binlog_row_image = 'minimal'"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the Create Table
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, WRITE_ROWS_EVENT_V1)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, 'data')
def test_minimal_image_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
query = "SET SESSION binlog_row_image = 'minimal'"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, DELETE_ROWS_EVENT_V1)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], None)
def test_minimal_image_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
query = "SET SESSION binlog_row_image = 'minimal'"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
#QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
event = self.stream.fetchone()
if self.isMySQL56AndMore():
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V2)
else:
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT_V1)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], None)
self.assertEqual(event.rows[0]["after_values"]["id"], None)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
def test_log_pos(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
for i in range(6):
self.stream.fetchone()
# record position after insert
log_file, log_pos = self.stream.log_file, self.stream.log_pos
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
# resume stream from previous position
if self.stream is not None:
self.stream.close()
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
resume_stream=True,
log_file=log_file,
log_pos=log_pos,
ignored_events=self.ignoredEvents()
)
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
# QueryEvent for the BEGIN
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), UpdateRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
def test_log_pos_handles_disconnects(self):
self.stream.close()
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
resume_stream=False,
only_events = [FormatDescriptionEvent, QueryEvent, TableMapEvent, WriteRowsEvent, XidEvent]
)
query = "CREATE TABLE test (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR (50) NOT NULL)"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.execute("COMMIT")
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertGreater(self.stream.log_pos, 0)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
self.assertGreater(self.stream.log_pos, 0)
def test_skip_to_timestamp(self):
self.stream.close()
query = "CREATE TABLE test_1 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
time.sleep(1)
query = "SELECT UNIX_TIMESTAMP();"
timestamp = self.execute(query).fetchone()[0]
query2 = "CREATE TABLE test_2 (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query2)
self.stream = BinLogStreamReader(
self.database,
server_id=1024,
skip_to_timestamp=timestamp,
ignored_events=self.ignoredEvents(),
)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query2)
class TestBasicBinLogStreamReader(base.PyMySQLReplicationTestCase):
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_connection_lost_event(self):
self.stream.close()
self.stream = BinLogStreamReader(connection_settings = self.database, blocking = True)
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query2 = "INSERT INTO test (data) VALUES('a')";
for i in range(0, 10000):
self.execute(query2)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
self.conn_control.kill(self.stream._stream_connection.thread_id())
for i in range(0, 1000):
event = self.stream.fetchone()
self.assertIsNotNone(event)
def test_filtering_events(self):
self.stream.close()
self.stream = BinLogStreamReader(connection_settings = self.database, only_events = [QueryEvent])
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
event = self.stream.fetchone()
self.assertIsInstance(event, QueryEvent)
self.assertEqual(event.query, query)
def test_write_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the Create Table
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
self.assertEqual(event.event_type, WRITE_ROWS_EVENT)
self.assertIsInstance(event, WriteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
self.assertEqual(event.schema, "pymysqlreplication_test")
self.assertEqual(event.table, "test")
self.assertEqual(event.columns[1].name, 'data')
def test_delete_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello World')"
self.execute(query)
self.resetBinLog()
query = "DELETE FROM test WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
self.assertEqual(event.event_type, DELETE_ROWS_EVENT)
self.assertIsInstance(event, DeleteRowsEvent)
self.assertEqual(event.rows[0]["values"]["id"], 1)
self.assertEqual(event.rows[0]["values"]["data"], "Hello World")
def test_update_row_event(self):
query = "CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "INSERT INTO test (data) VALUES('Hello')"
self.execute(query)
self.resetBinLog()
query = "UPDATE test SET data = 'World' WHERE id = 1"
self.execute(query)
self.execute("COMMIT")
#RotateEvent
self.stream.fetchone()
#FormatDescription
self.stream.fetchone()
#QueryEvent for the BEGIN
self.stream.fetchone()
event = self.stream.fetchone()
self.assertIsInstance(event, TableMapEvent)
event = self.stream.fetchone()
self.assertEqual(event.event_type, UPDATE_ROWS_EVENT)
self.assertIsInstance(event, UpdateRowsEvent)
self.assertEqual(event.rows[0]["before_values"]["id"], 1)
self.assertEqual(event.rows[0]["before_values"]["data"], "Hello")
self.assertEqual(event.rows[0]["after_values"]["id"], 1)
self.assertEqual(event.rows[0]["after_values"]["data"], "World")
class BinLogStreamReaderWrapper(object):
''' Wrapper class for the python-mysql-replication library '''
def __init__(self,
mysql_settings,
server_id=1,
blocking=False,
resume_stream=True,
log_file=None,
log_pos=None,
slave_heartbeat=None):
self.__stream = BinLogStreamReader(
connection_settings=mysql_settings,
server_id=server_id,
blocking=blocking,
resume_stream=resume_stream,
only_events=[
DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent,
TableMapEvent, BeginLoadQueryEvent, ExecuteLoadQueryEvent,
QueryEvent
], # RotateEvent, QueryEvent, HeartbeatLogEvent
log_file=log_file,
log_pos=log_pos,
slave_heartbeat=slave_heartbeat)
def close(self):
self.__stream.close()
def fetch_event(self):
return self.__parse_event(self.__stream.fetchone())
def __iter__(self):
return iter(self.fetch_event, None)
def __parse_event(self, binlogevent):
event = {
'event_type': self.__get_event_type(binlogevent),
'pymysqlreplication_event_type': type(binlogevent).__name__,
'timestamp': binlogevent.timestamp,
'log_pos': binlogevent.packet.log_pos,
'log_file': self.__stream.log_file
}
if self.__is_query_event(binlogevent):
event['log_pos'] = binlogevent.packet.log_pos
event['log_file'] = self.__stream.log_file
elif self.__is_rotate_event(binlogevent):
event['log_pos'] = binlogevent.position
event['log_file'] = binlogevent.next_binlog
elif self.__is_row_event(binlogevent) or self.__is_table_event(
binlogevent):
if binlogevent.schema != 'auth': # For security
event['schema'] = binlogevent.schema
event['table'] = binlogevent.table
if self.__is_row_event(binlogevent):
for row in binlogevent.rows:
event['primary_key'] = binlogevent.primary_key
event['after_values'] = self.__get_before_values(
binlogevent, row)
event['before_values'] = self.__get_after_values(
binlogevent, row)
elif self.__is_heartbeat_event(binlogevent):
event['log_file'] = binlogevent.ident
return event
def __get_event_type(self, binlogevent):
event_type = None
if self.__is_heartbeat_event(binlogevent) or self.__is_rotate_event(
binlogevent) or self.__is_heartbeat_event(binlogevent):
event_type = EventType.LOG_STATE
elif self.__is_delete_event(binlogevent):
event_type = EventType.DELETE
elif self.__is_update_event(binlogevent):
event_type = EventType.UPDATE
elif self.__is_insert_event(binlogevent):
event_type = EventType.INSERT
elif self.__is_table_event(binlogevent):
event_type = EventType.TABLE
else:
event_type = EventType.OTHER
return event_type
def __get_before_values(self, binlogevent, row):
before_values = None
if isinstance(binlogevent, UpdateRowsEvent):
before_values = row['before_values']
elif isinstance(binlogevent, DeleteRowsEvent):
before_values = row['values']
return before_values
def __get_after_values(self, binlogevent, row):
after_values = None
if isinstance(binlogevent, WriteRowsEvent):
after_values = row['values']
elif isinstance(binlogevent, UpdateRowsEvent):
after_values = row['after_values']
return after_values
def __is_row_event(self, binlogevent):
return self.__is_insert_event(binlogevent) or self.__is_update_event(
binlogevent) or self.__is_delete_event(binlogevent)
def __is_delete_event(self, binlogevent):
return isinstance(binlogevent, DeleteRowsEvent)
def __is_update_event(self, binlogevent):
return isinstance(binlogevent, UpdateRowsEvent)
def __is_insert_event(self, binlogevent):
return isinstance(binlogevent, WriteRowsEvent)
def __is_table_event(self, binlogevent):
return isinstance(binlogevent, (TableMapEvent))
def __is_query_event(self, binlogevent):
return isinstance(binlogevent, (QueryEvent))
def __is_begin_query_event(self, binlogevent):
return isinstance(binlogevent, (BeginLoadQueryEvent))
def __is_load_query_event(self, binlogevent):
return isinstance(binlogevent, (ExecuteLoadQueryEvent))
def __is_rotate_event(self, binlogevent):
return isinstance(binlogevent, (RotateEvent))
def __is_heartbeat_event(self, binlogevent):
return isinstance(binlogevent, (HeartbeatLogEvent))
class TestGtidBinLogStreamReader(base.PyMySQLReplicationTestCase):
def setUp(self):
super(TestGtidBinLogStreamReader, self).setUp()
if not self.supportsGTID:
raise unittest.SkipTest("database does not support GTID, skipping GTID tests")
def test_read_query_event(self):
query = "CREATE TABLE test (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "SELECT @@global.gtid_executed;"
gtid = self.execute(query).fetchone()[0]
self.stream.close()
self.stream = BinLogStreamReader(
self.database, server_id=1024, blocking=True, auto_position=gtid,
ignored_events=[HeartbeatLogEvent])
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
# Insert first event
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(1, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
firstevent = self.stream.fetchone()
self.assertIsInstance(firstevent, GtidEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
# Insert second event
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(2, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
secondevent = self.stream.fetchone()
self.assertIsInstance(secondevent, GtidEvent)
self.assertIsInstance(self.stream.fetchone(), QueryEvent)
self.assertIsInstance(self.stream.fetchone(), TableMapEvent)
self.assertIsInstance(self.stream.fetchone(), WriteRowsEvent)
self.assertIsInstance(self.stream.fetchone(), XidEvent)
self.assertEqual(secondevent.gno, firstevent.gno + 1)
def test_position_gtid(self):
query = "CREATE TABLE test (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
query = "BEGIN;"
self.execute(query)
query = "INSERT INTO test (id, data) VALUES(1, 'Hello');"
self.execute(query)
query = "COMMIT;"
self.execute(query)
query = "SELECT @@global.gtid_executed;"
gtid = self.execute(query).fetchone()[0]
query = "CREATE TABLE test2 (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))"
self.execute(query)
self.stream.close()
self.stream = BinLogStreamReader(
self.database, server_id=1024, blocking=True, auto_position=gtid,
ignored_events=[HeartbeatLogEvent])
self.assertIsInstance(self.stream.fetchone(), RotateEvent)
self.assertIsInstance(self.stream.fetchone(), FormatDescriptionEvent)
self.assertIsInstance(self.stream.fetchone(), GtidEvent)
event = self.stream.fetchone()
self.assertEqual(event.query, 'CREATE TABLE test2 (id INT NOT NULL, data VARCHAR (50) NOT NULL, PRIMARY KEY (id))');
