def test_open_connections_with_default_session_sqls(self): """Default session parameters should be applied if no custom session SQLs""" with patch('tap_mysql.connection.MySQLConnection.connect'): with patch('tap_mysql.connection.run_sql') as run_sql_mock: run_sql_mock.side_effect = self.run_sql_mock conn = MySQLConnectionMock(config=test_utils.get_db_config()) connect_with_backoff(conn) # Test if session variables applied on connection self.assertEqual(self.executed_queries, tap_mysql.connection.DEFAULT_SESSION_SQLS)
def main_impl(): args = utils.parse_args(REQUIRED_CONFIG_KEYS) connection = MySQLConnection(args.config) connect_with_backoff(connection) warnings = [] with connection.cursor() as cur: try: cur.execute('SET @@session.time_zone="+0:00"') except pymysql.err.InternalError as e: warnings.append( 'Could not set session.time_zone. Error: ({}) {}'.format( *e.args)) try: cur.execute('SET @@session.wait_timeout=2700') except pymysql.err.InternalError as e: warnings.append( 'Could not set session.wait_timeout. Error: ({}) {}'.format( *e.args)) try: cur.execute('SET @@session.innodb_lock_wait_timeout=2700') except pymysql.err.InternalError as e: warnings.append( 'Could not set session.innodb_lock_wait_timeout. Error: ({}) {}' .format(*e.args)) if warnings: LOGGER.info(( "Encountered non-fatal errors when configuring MySQL session that could " "impact performance:")) for w in warnings: LOGGER.warning(w) log_server_params(connection) if args.discover: do_discover(connection) elif args.catalog: state = args.state or {} do_sync(connection, args.config, args.catalog, state) elif args.properties: catalog = Catalog.from_dict(args.properties) state = args.state or {} do_sync(connection, args.config, catalog, state) else: LOGGER.info("No properties were selected")
def setUp(self): self.conn = test_utils.get_test_connection() with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute("CREATE TABLE full_table (val int)") cursor.execute("INSERT INTO full_table (val) VALUES (1)") self.catalog = test_utils.discover_catalog(self.conn, {}) for stream in self.catalog.streams: stream.key_properties = [] stream.metadata = [ { "breadcrumb": (), "metadata": { "selected": True, "database-name": "tap_mysql_test" }, }, { "breadcrumb": ("properties", "val"), "metadata": { "selected": True } }, ] stream.stream = stream.table test_utils.set_replication_method_and_key(stream, "FULL_TABLE", None)
def pks_are_integer_or_varchar(mysql_conn, config, catalog_entry): database_name = common.get_database_name(catalog_entry) key_properties = common.get_key_properties(catalog_entry) if config.get('allow_non_auto_increment_pks') == 'true' and key_properties: valid_column_types = set([ 'tinyint', 'smallint' 'mediumint', 'int', 'bigint', 'varchar', 'char' ]) sql = """SELECT data_type FROM information_schema.columns WHERE table_schema = '{}' AND table_name = '{}' AND column_name = '{}' """ with connect_with_backoff(mysql_conn) as open_conn: with open_conn.cursor() as cur: for pk in key_properties: cur.execute( sql.format(database_name, catalog_entry.table, pk)) result = cur.fetchone() if not result: raise Exception( "Primary key column {} does not exist.".format(pk)) if result[0] not in valid_column_types: return False return True return False
def log_server_params(mysql_conn): with connect_with_backoff(mysql_conn) as open_conn: try: with open_conn.cursor() as cur: cur.execute(''' SELECT VERSION() as version, @@session.wait_timeout as wait_timeout, @@session.innodb_lock_wait_timeout as innodb_lock_wait_timeout, @@session.max_allowed_packet as max_allowed_packet, @@session.interactive_timeout as interactive_timeout''') row = cur.fetchone() LOGGER.info('Server Parameters: ' + 'version: %s, ' + 'wait_timeout: %s, ' + 'innodb_lock_wait_timeout: %s, ' + 'max_allowed_packet: %s, ' + 'interactive_timeout: %s', *row) with open_conn.cursor() as cur: cur.execute(''' show session status where Variable_name IN ('Ssl_version', 'Ssl_cipher')''') rows = cur.fetchall() mapped_row = {r[0]: r[1] for r in rows} LOGGER.info( 'Server SSL Parameters(blank means SSL is not active): [ssl_version: %s], [ssl_cipher: %s]', mapped_row['Ssl_version'], mapped_row['Ssl_cipher']) except pymysql.err.InternalError as exc: LOGGER.warning("Encountered error checking server params. Error: (%s) %s", *exc.args)
def pks_are_auto_incrementing(mysql_conn, catalog_entry): database_name = common.get_database_name(catalog_entry) key_properties = common.get_key_properties(catalog_entry) if not key_properties: return False sql = """SELECT 1 FROM information_schema.columns WHERE table_schema = '{}' AND table_name = '{}' AND column_name = '{}' AND extra LIKE '%auto_increment%' """ with connect_with_backoff(mysql_conn) as open_conn: with open_conn.cursor() as cur: for pk in key_properties: cur.execute(sql.format(database_name, catalog_entry.table, pk)) result = cur.fetchone() if not result: return False return True
def log_engine(mysql_conn, catalog_entry): is_view = common.get_is_view(catalog_entry) database_name = common.get_database_name(catalog_entry) if is_view: LOGGER.info("Beginning sync for view %s.%s", database_name, catalog_entry.table) else: with connect_with_backoff(mysql_conn) as open_conn: with open_conn.cursor() as cur: cur.execute( """ SELECT engine FROM information_schema.tables WHERE table_schema = %s AND table_name = %s """, (database_name, catalog_entry.table), ) row = cur.fetchone() if row: LOGGER.info( "Beginning sync for %s table %s.%s", row[0], database_name, catalog_entry.table, )
def verify_binlog_config(mysql_conn): with connect_with_backoff(mysql_conn) as open_conn: with open_conn.cursor() as cur: cur.execute("SELECT @@binlog_format") binlog_format = cur.fetchone()[0] if binlog_format != 'ROW': raise Exception( "Unable to replicate binlog stream because binlog_format is not set to 'ROW': {}." .format(binlog_format)) try: cur.execute("SELECT @@binlog_row_image") binlog_row_image = cur.fetchone()[0] except pymysql.err.InternalError as ex: if ex.args[0] == 1193: raise Exception( "Unable to replicate binlog stream because binlog_row_image system variable does not exist. MySQL version must be at least 5.6.2 to use binlog replication." ) raise ex if binlog_row_image != 'FULL': raise Exception( "Unable to replicate binlog stream because binlog_row_image is not set to 'FULL': {}." .format(binlog_row_image))
def setUp(self): self.conn = test_utils.get_test_connection() with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute("CREATE TABLE a (`b c` int)") cursor.execute("INSERT INTO a (`b c`) VALUES (1)") self.catalog = test_utils.discover_catalog(self.conn, {}) self.catalog.streams[0].stream = "some_stream_name" self.catalog.streams[0].metadata = [ { "breadcrumb": (), "metadata": { "selected": True, "table-key-properties": [], "database-name": "tap_mysql_test", }, }, { "breadcrumb": ("properties", "b c"), "metadata": { "selected": True } }, ] test_utils.set_replication_method_and_key(self.catalog.streams[0], "FULL_TABLE", None)
def setUp(self): self.conn = test_utils.get_test_connection() with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute('CREATE TABLE full_table (val int)') cursor.execute('INSERT INTO full_table (val) VALUES (1)') self.catalog = test_utils.discover_catalog(self.conn, {}) for stream in self.catalog.streams: stream.key_properties = [] stream.metadata = [{ 'breadcrumb': (), 'metadata': { 'selected': True, 'database-name': 'tap_mysql_test' } }, { 'breadcrumb': ('properties', 'val'), 'metadata': { 'selected': True } }] stream.stream = stream.table test_utils.set_replication_method_and_key(stream, 'FULL_TABLE', None)
def setUpClass(cls): conn = test_utils.get_test_connection() with connect_with_backoff(conn) as open_conn: with open_conn.cursor() as cur: cur.execute(''' CREATE TABLE test_type_mapping ( c_pk INTEGER PRIMARY KEY, c_decimal DECIMAL, c_decimal_2_unsigned DECIMAL(5, 2) UNSIGNED, c_decimal_2 DECIMAL(11, 2), c_tinyint TINYINT, c_tinyint_1 TINYINT(1), c_tinyint_1_unsigned TINYINT(1) UNSIGNED, c_smallint SMALLINT, c_mediumint MEDIUMINT, c_int INT, c_bigint BIGINT, c_bigint_unsigned BIGINT(20) UNSIGNED, c_float FLOAT, c_double DOUBLE, c_bit BIT(4), c_date DATE, c_time TIME, c_year YEAR )''') catalog = test_utils.discover_catalog(conn, {}) cls.schema = catalog.streams[0].schema cls.metadata = catalog.streams[0].metadata
def runTest(self): conn = test_utils.get_test_connection() with connect_with_backoff(conn) as open_conn: with open_conn.cursor() as cur: cur.execute(''' CREATE TABLE tab ( id INTEGER PRIMARY KEY, a INTEGER, b INTEGER) ''') catalog = test_utils.discover_catalog(conn, {}) catalog.streams[0].stream = 'tab' catalog.streams[0].metadata = [ {'breadcrumb': (), 'metadata': {'selected': True, 'database-name': 'tap_mysql_test'}}, {'breadcrumb': ('properties', 'a'), 'metadata': {'selected': True}} ] test_utils.set_replication_method_and_key(catalog.streams[0], 'FULL_TABLE', None) global SINGER_MESSAGES SINGER_MESSAGES.clear() tap_mysql.do_sync(conn, {}, catalog, {}) schema_message = list(filter(lambda m: isinstance(m, singer.SchemaMessage), SINGER_MESSAGES))[0] self.assertTrue(isinstance(schema_message, singer.SchemaMessage)) # tap-mysql selects new fields by default. If a field doesn't appear in the schema, then it should be # selected expectedKeys = ['id', 'a', 'b'] self.assertEqual(schema_message.schema['properties'].keys(), set(expectedKeys))
def setUp(self): self.conn = test_utils.get_test_connection() with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute('CREATE TABLE incremental (val int, updated datetime)') cursor.execute('INSERT INTO incremental (val, updated) VALUES (1, \'2017-06-01\')') cursor.execute('INSERT INTO incremental (val, updated) VALUES (2, \'2017-06-20\')') cursor.execute('INSERT INTO incremental (val, updated) VALUES (3, \'2017-09-22\')') cursor.execute('CREATE TABLE integer_incremental (val int, updated int)') cursor.execute('INSERT INTO integer_incremental (val, updated) VALUES (1, 1)') cursor.execute('INSERT INTO integer_incremental (val, updated) VALUES (2, 2)') cursor.execute('INSERT INTO integer_incremental (val, updated) VALUES (3, 3)') self.catalog = test_utils.discover_catalog(self.conn, {}) for stream in self.catalog.streams: stream.metadata = [ {'breadcrumb': (), 'metadata': { 'selected': True, 'table-key-properties': [], 'database-name': 'tap_mysql_test' }}, {'breadcrumb': ('properties', 'val'), 'metadata': {'selected': True}} ] stream.stream = stream.table test_utils.set_replication_method_and_key(stream, 'INCREMENTAL', 'updated')
def fetch_server_id(mysql_conn): with connect_with_backoff(mysql_conn) as open_conn: with open_conn.cursor() as cur: cur.execute("SELECT @@server_id") server_id = cur.fetchone()[0] return server_id
def test_open_connections_with_session_sqls(self): """Custom session parameters should be applied if defined""" session_sqls = [ 'SET SESSION max_statement_time=0', 'SET SESSION wait_timeout=28800' ] with patch('tap_mysql.connection.MySQLConnection.connect'): with patch('tap_mysql.connection.run_sql') as run_sql_mock: run_sql_mock.side_effect = self.run_sql_mock conn = MySQLConnectionMock(config={**test_utils.get_db_config(), **{'session_sqls': session_sqls}}) connect_with_backoff(conn) # Test if session variables applied on connection self.assertEqual(self.executed_queries, session_sqls)
def setUp(self): self.conn = test_utils.get_test_connection() with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute('CREATE TABLE a (`b c` int)') cursor.execute('INSERT INTO a (`b c`) VALUES (1)') self.catalog = test_utils.discover_catalog(self.conn, {}) self.catalog.streams[0].stream = 'some_stream_name' self.catalog.streams[0].metadata = [{ 'breadcrumb': (), 'metadata': { 'selected': True, 'table-key-properties': [], 'database-name': 'tap_mysql_test' } }, { 'breadcrumb': ('properties', 'b c'), 'metadata': { 'selected': True } }] test_utils.set_replication_method_and_key(self.catalog.streams[0], 'FULL_TABLE', None)
def init_tables(conn): with connect_with_backoff(conn) as open_conn: with open_conn.cursor() as cur: cur.execute(""" CREATE TABLE table_1 ( id BIGINT AUTO_INCREMENT PRIMARY KEY, foo BIGINT, bar VARCHAR(10) )""") cur.execute(""" CREATE TABLE table_2 ( id BIGINT AUTO_INCREMENT PRIMARY KEY, foo BIGINT, bar VARCHAR(10) )""") for record in TABLE_1_DATA: insert_record(conn, "table_1", record) for record in TABLE_2_DATA: insert_record(conn, "table_2", record) catalog = test_utils.discover_catalog(conn, {}) return catalog
def calculate_bookmark(mysql_conn, binlog_streams_map, state): min_log_pos_per_file = get_min_log_pos_per_log_file( binlog_streams_map, state) with connect_with_backoff(mysql_conn) as open_conn: with open_conn.cursor() as cur: cur.execute("SHOW BINARY LOGS") binary_logs = cur.fetchall() if binary_logs: server_logs_set = {log[0] for log in binary_logs} state_logs_set = set(min_log_pos_per_file.keys()) expired_logs = state_logs_set.difference(server_logs_set) if expired_logs: raise Exception( "Unable to replicate binlog stream because the following binary log(s) no longer exist: {}" .format(", ".join(expired_logs))) for log_file in sorted(server_logs_set): if min_log_pos_per_file.get(log_file): return log_file, min_log_pos_per_file[log_file][ 'log_pos'] raise Exception( "Unable to replicate binlog stream because no binary logs exist on the server." )
def sync_table(mysql_conn, catalog_entry, state, columns, stream_version): common.whitelist_bookmark_keys(generate_bookmark_keys(catalog_entry), catalog_entry.tap_stream_id, state) bookmark = state.get('bookmarks', {}).get(catalog_entry.tap_stream_id, {}) version_exists = True if 'version' in bookmark else False initial_full_table_complete = singer.get_bookmark( state, catalog_entry.tap_stream_id, 'initial_full_table_complete') state_version = singer.get_bookmark(state, catalog_entry.tap_stream_id, 'version') activate_version_message = singer.ActivateVersionMessage( stream=catalog_entry.stream, version=stream_version) # For the initial replication, emit an ACTIVATE_VERSION message # at the beginning so the records show up right away. if not initial_full_table_complete and not (version_exists and state_version is None): singer.write_message(activate_version_message) perform_resumable_sync = sync_is_resumable(mysql_conn, catalog_entry) pk_clause = "" with connect_with_backoff(mysql_conn) as open_conn: with open_conn.cursor() as cur: select_sql = common.generate_select_sql(catalog_entry, columns) if perform_resumable_sync: LOGGER.info( "Full table sync is resumable based on primary key definition, will replicate incrementally" ) state = update_incremental_full_table_state( catalog_entry, state, cur) pk_clause = generate_pk_clause(catalog_entry, state) select_sql += pk_clause try: select_sql = _create_temp_table(mysql_conn, catalog_entry, columns, pk_clause) except Exception as ex: logging.warning("creating temp table failed: {}".format( str(ex))) params = {} common.sync_query(cur, catalog_entry, state, select_sql, columns, stream_version, params) # clear max pk value and last pk fetched upon successful sync singer.clear_bookmark(state, catalog_entry.tap_stream_id, 'max_pk_values') singer.clear_bookmark(state, catalog_entry.tap_stream_id, 'last_pk_fetched') singer.write_message(activate_version_message)
def sync_table(mysql_conn, catalog_entry, state, columns, original_state_file=''): common.whitelist_bookmark_keys(BOOKMARK_KEYS, catalog_entry.tap_stream_id, state) catalog_metadata = metadata.to_map(catalog_entry.metadata) stream_metadata = catalog_metadata.get((), {}) replication_key_metadata = stream_metadata.get('replication-key') replication_key_state = singer.get_bookmark(state, catalog_entry.tap_stream_id, 'replication_key') replication_key_value = None if replication_key_metadata == replication_key_state: replication_key_value = singer.get_bookmark( state, catalog_entry.tap_stream_id, 'replication_key_value') else: state = singer.write_bookmark(state, catalog_entry.tap_stream_id, 'replication_key', replication_key_metadata) state = singer.clear_bookmark(state, catalog_entry.tap_stream_id, 'replication_key_value') stream_version = common.get_stream_version(catalog_entry.tap_stream_id, state) state = singer.write_bookmark(state, catalog_entry.tap_stream_id, 'version', stream_version) activate_version_message = singer.ActivateVersionMessage( stream='%s_%s' % (common.get_database_name(catalog_entry), catalog_entry.stream), version=stream_version) singer.write_message(activate_version_message) with connect_with_backoff(mysql_conn) as open_conn: with open_conn.cursor() as cur: select_sql = common.generate_select_sql(catalog_entry, columns) params = {} if replication_key_value is not None: if catalog_entry.schema.properties[ replication_key_metadata].format == 'date-time': replication_key_value = pendulum.parse( replication_key_value) select_sql += ' WHERE `{}` >= %(replication_key_value)s ORDER BY `{}` ASC'.format( replication_key_metadata, replication_key_metadata) params['replication_key_value'] = replication_key_value common.sync_query(cur, catalog_entry, state, select_sql, columns, stream_version, params, original_state_file)
def test_open_connections_with_invalid_session_sqls(self): """Invalid SQLs in session_sqls should be ignored""" session_sqls = [ 'SET SESSION max_statement_time=0', 'INVALID-SQL-SHOULD-BE-SILENTLY-IGNORED', 'SET SESSION wait_timeout=28800' ] with patch('tap_mysql.connection.MySQLConnection.connect'): with patch('tap_mysql.connection.run_sql') as run_sql_mock: run_sql_mock.side_effect = self.run_sql_mock conn = MySQLConnectionMock(config={**test_utils.get_db_config(), **{'session_sqls': session_sqls}}) connect_with_backoff(conn) # Test if session variables applied on connection self.assertEqual(self.executed_queries, ['SET SESSION max_statement_time=0', 'SET SESSION wait_timeout=28800'])
def setUp(self): self.maxDiff = None self.state = {} self.conn = test_utils.get_test_connection() log_file, log_pos = binlog.fetch_current_log_file_and_pos(self.conn) with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute('CREATE TABLE binlog_1 (id int, updated datetime)') cursor.execute('CREATE TABLE binlog_2 (id int, updated datetime)') cursor.execute('INSERT INTO binlog_1 (id, updated) VALUES (1, \'2017-06-01\')') cursor.execute('INSERT INTO binlog_1 (id, updated) VALUES (2, \'2017-06-20\')') cursor.execute('INSERT INTO binlog_1 (id, updated) VALUES (3, \'2017-09-22\')') cursor.execute('INSERT INTO binlog_2 (id, updated) VALUES (1, \'2017-10-22\')') cursor.execute('INSERT INTO binlog_2 (id, updated) VALUES (2, \'2017-11-10\')') cursor.execute('INSERT INTO binlog_2 (id, updated) VALUES (3, \'2017-12-10\')') cursor.execute('UPDATE binlog_1 set updated=\'2018-06-18\' WHERE id = 3') cursor.execute('UPDATE binlog_2 set updated=\'2018-06-18\' WHERE id = 2') cursor.execute('DELETE FROM binlog_1 WHERE id = 2') cursor.execute('DELETE FROM binlog_2 WHERE id = 1') open_conn.commit() self.catalog = test_utils.discover_catalog(self.conn, {}) for stream in self.catalog.streams: stream.stream = stream.table stream.metadata = [ {'breadcrumb': (), 'metadata': { 'selected': True, 'database-name': 'tap_mysql_test', 'table-key-propertes': ['id'] }}, {'breadcrumb': ('properties', 'id'), 'metadata': {'selected': True}}, {'breadcrumb': ('properties', 'updated'), 'metadata': {'selected': True}} ] test_utils.set_replication_method_and_key(stream, 'LOG_BASED', None) self.state = singer.write_bookmark(self.state, stream.tap_stream_id, 'log_file', log_file) self.state = singer.write_bookmark(self.state, stream.tap_stream_id, 'log_pos', log_pos) self.state = singer.write_bookmark(self.state, stream.tap_stream_id, 'version', singer.utils.now())
def test_sync_messages_are_correct(self): self.catalog.streams[0] = test_utils.set_replication_method_and_key(self.catalog.streams[0], 'LOG_BASED', None) self.catalog.streams[0] = test_utils.set_selected(self.catalog.streams[0], True) global SINGER_MESSAGES SINGER_MESSAGES.clear() #inital sync tap_mysql.do_sync(self.conn, {}, self.catalog, {}) # get schema message to test that it has all the table's columns schema_message = next(filter(lambda m: isinstance(m, singer.SchemaMessage), SINGER_MESSAGES)) expectedKeys = ['good_pk', 'age'] self.assertEqual(schema_message.schema['properties'].keys(), set(expectedKeys)) # get the records, these are generated by Full table replication record_messages = list(filter(lambda m: isinstance(m, singer.RecordMessage), SINGER_MESSAGES)) self.assertEqual(len(record_messages), 4) self.assertListEqual([ {'age': 20, 'good_pk': '61'}, {'age': 30, 'good_pk': '62'}, {'age': 30, 'good_pk': '63'}, {'age': 40, 'good_pk': '64'}, ], [rec.record for rec in record_messages]) # get the last state message to be fed to the next sync state_message = list(filter(lambda m: isinstance(m, singer.StateMessage), SINGER_MESSAGES))[-1] SINGER_MESSAGES.clear() # run some queries with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute("UPDATE good_pk_tab set age=age+5") cursor.execute("INSERT INTO good_pk_tab (good_pk, age) VALUES " "(BINARY('e'), 16), " "(BINARY('f'), 5)") # do a sync and give the state so that binlog replication start from the last synced position tap_mysql.do_sync(self.conn, test_utils.get_db_config(), self.catalog, state_message.value) # get the changed/new records record_messages = list(filter(lambda m: isinstance(m, singer.RecordMessage), SINGER_MESSAGES)) self.assertEqual(len(record_messages), 6) self.assertListEqual([ {'age': 25, 'good_pk': '61'}, {'age': 35, 'good_pk': '62'}, {'age': 35, 'good_pk': '63'}, {'age': 45, 'good_pk': '64'}, {'age': 16, 'good_pk': '65'}, {'age': 5, 'good_pk': '66'}, ], [rec.record for rec in record_messages])
def insert_record(conn, table_name, record): value_sql = ",".join(["%s" for i in range(len(record))]) insert_sql = """ INSERT INTO {}.{} ( `foo`, `bar` ) VALUES ( {} )""".format(test_utils.DB_NAME, table_name, value_sql) with connect_with_backoff(conn) as open_conn: with open_conn.cursor() as cur: cur.execute(insert_sql, record)
def sync_table(mysql_conn, catalog_entry, state, columns): common.whitelist_bookmark_keys(BOOKMARK_KEYS, catalog_entry.tap_stream_id, state) catalog_metadata = metadata.to_map(catalog_entry.metadata) stream_metadata = catalog_metadata.get((), {}) replication_key_metadata = stream_metadata.get("replication-key") replication_key_state = singer.get_bookmark( state, catalog_entry.tap_stream_id, "replication_key" ) replication_key_value = None if replication_key_metadata == replication_key_state: replication_key_value = singer.get_bookmark( state, catalog_entry.tap_stream_id, "replication_key_value" ) else: state = singer.write_bookmark( state, catalog_entry.tap_stream_id, "replication_key", replication_key_metadata ) state = singer.clear_bookmark(state, catalog_entry.tap_stream_id, "replication_key_value") stream_version = common.get_stream_version(catalog_entry.tap_stream_id, state) state = singer.write_bookmark(state, catalog_entry.tap_stream_id, "version", stream_version) activate_version_message = singer.ActivateVersionMessage( stream=catalog_entry.stream, version=stream_version ) singer.write_message(activate_version_message) with connect_with_backoff(mysql_conn) as open_conn: with open_conn.cursor() as cur: select_sql = common.generate_select_sql(catalog_entry, columns) params = {} if replication_key_value is not None: if catalog_entry.schema.properties[replication_key_metadata].format == "date-time": replication_key_value = pendulum.parse(replication_key_value) select_sql += " WHERE `{}` >= %(replication_key_value)s ORDER BY `{}` ASC".format( replication_key_metadata, replication_key_metadata ) params["replication_key_value"] = replication_key_value elif replication_key_metadata is not None: select_sql += " ORDER BY `{}` ASC".format(replication_key_metadata) common.sync_query( cur, catalog_entry, state, select_sql, columns, stream_version, params )
def fetch_current_log_file_and_pos(mysql_conn): with connect_with_backoff(mysql_conn) as open_conn: with open_conn.cursor() as cur: cur.execute("SHOW MASTER STATUS") result = cur.fetchone() if result is None: raise Exception("MySQL binary logging is not enabled.") current_log_file, current_log_pos = result[0:2] return current_log_file, current_log_pos
def setUp(self): self.conn = test_utils.get_test_connection() with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute( "CREATE TABLE incremental (val int, updated datetime)") cursor.execute( "INSERT INTO incremental (val, updated) VALUES (1, '2017-06-01')" ) cursor.execute( "INSERT INTO incremental (val, updated) VALUES (2, '2017-06-20')" ) cursor.execute( "INSERT INTO incremental (val, updated) VALUES (3, '2017-09-22')" ) cursor.execute( "CREATE TABLE integer_incremental (val int, updated int)") cursor.execute( "INSERT INTO integer_incremental (val, updated) VALUES (1, 1)" ) cursor.execute( "INSERT INTO integer_incremental (val, updated) VALUES (2, 2)" ) cursor.execute( "INSERT INTO integer_incremental (val, updated) VALUES (3, 3)" ) self.catalog = test_utils.discover_catalog(self.conn, {}) for stream in self.catalog.streams: stream.metadata = [ { "breadcrumb": (), "metadata": { "selected": True, "table-key-properties": [], "database-name": "tap_mysql_test", }, }, { "breadcrumb": ("properties", "val"), "metadata": { "selected": True } }, ] stream.stream = stream.table test_utils.set_replication_method_and_key(stream, "INCREMENTAL", "updated")
def setUp(self): self.conn = test_utils.get_test_connection() with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute( 'CREATE TABLE bad_pk_tab (bad_pk BINARY, age INT, PRIMARY KEY (bad_pk))' ) # BINARY not presently supported cursor.execute( 'CREATE TABLE good_pk_tab (good_pk INT, age INT, PRIMARY KEY (good_pk))' ) cursor.execute( "INSERT INTO bad_pk_tab (bad_pk, age) VALUES ('a', 100)") cursor.execute( "INSERT INTO good_pk_tab (good_pk, age) VALUES (1, 100)")
def setUp(self): self.conn = test_utils.get_test_connection() with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute(''' CREATE TABLE a_table ( id int primary key, a int, b int) ''') cursor.execute(''' CREATE VIEW a_view AS SELECT id, a FROM a_table ''')
def setUp(self): self.conn = test_utils.get_test_connection() with connect_with_backoff(self.conn) as open_conn: with open_conn.cursor() as cursor: cursor.execute( # BINARY is presently supported 'CREATE TABLE good_pk_tab (good_pk BINARY(10), age INT, PRIMARY KEY (good_pk))') cursor.execute("INSERT INTO good_pk_tab (good_pk, age) VALUES " "(BINARY('a'), 20), " "(BINARY('b'), 30), " "(BINARY('c'), 30), " "(BINARY('d'), 40)") self.catalog = test_utils.discover_catalog(self.conn, {})