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))');