def _run(self):
start = datetime.now()
self.log('Pipe worker starting')
self.chunk.status = 'migrating'
self.chunk.num_records_exported = 0
self.chunk.num_records_imported = 0
self.chunk.update()
with db.shard_connection(self.c.source_shard, read=True) as source_conn:
with db.shard_connection(self.c.destination_shard, read=False) as dest_conn:
update_status = self.migrate(source_conn, dest_conn)
if update_status:
self.chunk.import_elapsed_ms = int((datetime.now() - start).total_seconds() * 1000)
self.chunk.status = 'imported'
self.chunk.update()
(migration_status, _, _, _, _) = status.get_migration_status(migration_id=self.c.migration_id)
migration = orm.Migration.get(self.redis_conn, migration_id=self.c.migration_id)
migration.status = migration_status
migration.update()
if config.ENABLE_VERIFIER:
verifier.queue_verification(self.c)
self.log('Pipe worker finished elapsed=%s', datetime.now() - start)
def test_run(self):
self.mox.StubOutWithMock(exporter_mysql, 'time')
exporter_mysql.time.sleep(mox.IgnoreArg())
exporter_mysql.time.time().AndReturn(42)
self.chunk.update()
self.mox.StubOutWithMock(db, 'shard_connection')
conn = self.mox.CreateMockAnything()
db.shard_connection('src', read=True).AndReturn(self.context(conn))
self.mox.StubOutWithMock(subprocess, 'Popen')
proc = self.mox.CreateMockAnything()
subprocess.Popen(mox.IgnoreArg(), shell=True, stdin=subprocess.PIPE).AndReturn(proc)
proc.stdin = self.mox.CreateMockAnything()
proc.stdin.close()
proc.wait()
proc.returncode = 0
self.mox.StubOutWithMock(db, 'cursor')
cur = self.mox.CreateMockAnything()
db.cursor(conn).AndReturn(self.context(cur))
cur.execute(mox.IgnoreArg(), mox.IgnoreArg())
cur.rowcount = 1
self.mox.StubOutWithMock(exporter_mysql.streamer, 'queue_stream_chunk')
exporter_mysql.streamer.queue_stream_chunk(self.config)
self.chunk.update()
self.chunk.update_status('exported', where_status='exporting')
proc = self.mox.CreateMockAnything()
subprocess.Popen(mox.IgnoreArg(), shell=True, stdin=subprocess.PIPE).AndReturn(proc)
proc.stdin = self.mox.CreateMockAnything()
proc.stdin.close()
proc.wait()
self.mox.ReplayAll()
self.worker._run()
def expected_configs(self, configs):
new_conn = True
for chunk_config in configs:
if new_conn:
redis_conn = self.mox.CreateMockAnything()
db.redis_conn().AndReturn(redis_conn)
redis_conn.__enter__().AndReturn(redis_conn)
orm.Migration.get_latest(
redis_conn,
source_shard=chunk_config.source_shard, destination_shard=chunk_config.destination_shard,
partition_val=chunk_config.partition_val
).AndReturn(None)
migration = self.mox.CreateMockAnything()
migration._redis = redis_conn
orm.Migration(redis_conn).AndReturn(migration)
conn = self.mox.CreateMockAnything()
db.shard_connection(chunk_config.source_shard, read=True).AndReturn(context(conn))
conn.get_current_timestamp().AndReturn(123)
migration.insert()
else:
redis_conn.__exit__(mox.IgnoreArg(), mox.IgnoreArg(), mox.IgnoreArg())
new_conn = not new_conn
tt = self.mox.CreateMockAnything()
orm.Table(redis_conn).AndReturn(tt)
tt.insert()
queuer.queue_migrate_table(chunk_config)
def main():
with db.shard_connection('crate', read=True) as conn:
print('Creating shinkansen.host')
with db.cursor(conn) as cur:
cur.execute('DROP TABLE IF EXISTS shinkansen.host')
with db.cursor(conn) as cur:
cur.execute('''
CREATE TABLE shinkansen.host (
id INT PRIMARY KEY,
name String,
created TIMESTAMP
)''')
print('Creating shinkansen.host_checkin')
with db.cursor(conn) as cur:
cur.execute('DROP TABLE IF EXISTS shinkansen.host_checkin')
with db.cursor(conn) as cur:
cur.execute('''CREATE TABLE shinkansen.host_checkin (
host_id INT,
time TIMESTAMP,
status String
)''')
print('Creating shinkansen.host_metric')
with db.cursor(conn) as cur:
cur.execute('DROP TABLE IF EXISTS shinkansen.host_metric')
with db.cursor(conn) as cur:
cur.execute('''CREATE TABLE shinkansen.host_metric (
host_id INT,
name String,
time TIMESTAMP,
value String
)''')
def main():
print('Generating data')
with db.shard_connection('mysql', read=False) as conn:
for host_id in xrange(1, 11):
print('.')
name = str(uuid.uuid4())
time = datetime.now() - timedelta(days=365)
with db.cursor(conn) as cur:
cur.execute(
'INSERT INTO shinkansen.host (id, name, created) VALUES (%s, %s, %s)',
(host_id, name, time)
)
while time < datetime.now():
status = 'up' if random.random() < 0.9 else 'down'
with db.cursor(conn) as cur:
cur.execute(
'INSERT INTO shinkansen.host_checkin (host_id, time, status) VALUES (%s, %s, %s)',
(host_id, time, status)
)
if status == 'up':
for metric in ['cpu', 'mem', 'disk']:
with db.cursor(conn) as cur:
cur.execute(
'INSERT INTO shinkansen.host_metric (host_id, name, time, value) '
'VALUES (%s, %s, %s, %s)',
(host_id, metric, time, random.random())
)
time += timedelta(days=1)
conn.commit()
print('done')
def _import_to_crate(self):
with db.shard_connection(self.c.destination_shard, read=True) as conn:
self.log('Starting import')
with db.cursor(conn) as cur:
sql = "COPY %s.%s FROM '%s'" % (
self.c.destination_schema, self.c.table_config.table_name, self.c.import_filename)
cur.execute(sql)
self.c.num_records_imported = cur.rowcount
conn.commit()
def test_run_proc_fails(self):
self.mox.StubOutWithMock(exporter_mysql, 'time')
exporter_mysql.time.sleep(mox.IgnoreArg())
exporter_mysql.time.time().AndReturn(42)
self.chunk.update()
self.mox.StubOutWithMock(db, 'shard_connection')
conn = self.mox.CreateMockAnything()
db.shard_connection('src', read=True).AndReturn(self.context(conn))
self.mox.StubOutWithMock(subprocess, 'Popen')
proc = self.mox.CreateMockAnything()
subprocess.Popen(mox.IgnoreArg(), shell=True, stdin=subprocess.PIPE).AndReturn(proc)
proc.stdin = self.mox.CreateMockAnything()
proc.stdin.close()
proc.wait()
proc.returncode = 1
self.mox.ReplayAll()
with self.assertRaises(exporter_mysql.CommandException):
self.worker._run()
def get_column_metadata(self, conn):
columns = []
with db.cursor(conn, dictionary=True) as cur:
cur.execute(
'SELECT * FROM information_schema.columns WHERE schema_name = %(?)s AND table_name = %(?)s ' % {
'?': conn.PARAMETER_PLACEHOLDER
},
(self.c.source_schema.lower(), self.c.table_config.table_name.lower())
)
column_recs = cur.fetchall()
pk_cols = set()
with db.cursor(conn) as cur:
cur.execute(
'SELECT constraint_name FROM information_schema.table_constraints '
'WHERE constraint_type = %(?)s AND schema_name = %(?)s AND table_name = %(?)s' % {
'?': conn.PARAMETER_PLACEHOLDER
},
('PRIMARY_KEY', self.c.source_schema.lower(), self.c.table_config.table_name.lower())
)
for (constraint_name,) in cur.fetchall():
# constraint_name is a list of the columns in the key
for column in constraint_name:
pk_cols.add(column.lower())
for column in column_recs:
col = db.Column(
column['column_name'],
self.TYPE_MAP[column['data_type']],
column['column_name'].lower() in pk_cols,
ignore=(column['column_name'].lower() in self.c.table_config.ignore_columns),
)
columns.append(col)
self.column_map[col.lname] = col
if (
self.c.migration_type == orm.MigrationType.DELTA
or self.c.chunk_migration_type == orm.ChunkMigrationType.DIRECT
):
# Check the destination for the primary key columns as well since the schemas may be different
with db.shard_connection(self.c.destination_shard, read=True) as conn:
primary_key = conn.get_table_primary_key_columns(
self.c.destination_schema,
self.c.table_config.table_name
)
for col_name in primary_key:
if col_name.lower() not in self.column_map:
raise UnrecoverableError(
'Primary key column in destination does not exist in source '
'table=%s column=%s source=%s destination=%s' % (
self.c.table_config.table_name, col_name, self.c.source_shard, self.c.destination_shard))
self.column_map[col_name.lower()].is_primary_key = True
self.c.columns = columns
def main():
args = docopt.docopt(__doc__)
for shard_name, shard_config in config.CRATE_SHARDS.items():
with db.shard_connection(shard_name, read=False) as conn:
trim_shard(conn, shard_name, shard_config, args['--delete'])
for partition_val, partition_trimming_threshold in config.PARTITION_SPECIFIC_TRIMMING_THRESHOLDS.items():
for shard_name, shard_config in config.CRATE_SHARDS.items():
if isinstance(partition_trimming_threshold, datetime.timedelta):
shard_partition_trimming_threshold = partition_trimming_threshold
else:
shard_partition_trimming_config = partition_trimming_threshold.get(shard_config['shard_type'])
if shard_partition_trimming_config is None:
continue
if (
'shard_names' in shard_partition_trimming_config
and shard_name not in shard_partition_trimming_config['shard_names']
):
continue
shard_partition_trimming_threshold = shard_partition_trimming_config['threshold']
with db.shard_connection(shard_name, read=False) as conn:
trim_shard(
conn, shard_name, shard_config, args['--delete'], partition_val, shard_partition_trimming_threshold)
def get_table_metadata(self):
with db.shard_connection(self.c.source_shard, read=True) as conn:
self.table.source_start_time = conn.get_current_timestamp()
self.table.start_time = int(time.time() * 1000)
self.table.status = 'in_progress'
self.table.update()
self.get_column_metadata(conn)
self.c.where_clauses, self.c.where_values = worker.generate_where(conn, self.c, self.c.table_config)
if (
self.c.migration_type == orm.MigrationType.DELTA
and self.c.table_config.join
):
# TODO: come up with a different way to do deltas for crate with a join clause. We don't need the chunking if we do it via json export
if self.c.source_type == 'crate':
self.log_error(
'The %s table specifies a join clause but joins are not supported for crate due to lack of '
'aggregation support for JOIN queries. This table will not have any delta migrations '
'performed.',
self.c.table_config.table_name
)
return
join = self.c.table_config.join % {'schema': self.c.source_schema}
else:
join = ''
sql = (
'SELECT COUNT(*), MIN(%(chunk_col)s), MAX(%(chunk_col)s) '
'FROM %(schema)s.%(table)s %(table_alias)s %(join)s %(where)s'
) % {
'chunk_col': self.c.table_config.chunk_col,
'schema': self.c.source_schema,
'table': self.c.table_config.table_name,
'table_alias': self.c.table_config.table_alias,
# We only need the join clause for delta and direct currently
'join': join,
'where': (' WHERE ' + (' AND '.join(self.c.where_clauses))) if self.c.where_clauses else ''
}
with db.cursor(conn) as cur:
cur.execute(sql % {'?': conn.PARAMETER_PLACEHOLDER}, self.c.where_values)
(self.num_rows, self.min_id, self.max_id) = cur.fetchone()
self.log('num_rows=%r min_id=%r max_id=%r', self.num_rows, self.min_id, self.max_id)
def check_shard(self, shard):
start_time = time.time()
try:
with db.shard_connection(shard, read=False) as conn:
conn.check_health()
with self.result_lock:
if shard not in self.result['databases']:
self.result['databases'][shard] = {
'ms': int((time.time() - start_time) * 1000),
'status': 'ok',
}
except Exception, exc:
with self.result_lock:
if shard not in self.result['databases']:
self.result['databases'][shard] = {
'ms': int((time.time() - start_time) * 1000),
'status': 'error',
'error': 'Exception checking shard %r' % (exc,)
}
def _import_to_mysql(self):
with db.shard_connection(self.c.destination_shard, read=False) as conn:
self.log('Starting import')
with db.cursor(conn) as cur:
sql = (
(
"LOAD DATA INFILE '%(infile)s' "
# TODO(jpatrin): If the mysql source had timestamp fields with the special value
# '0000-00-00 00:00:00' then the imports will fail if the mysql server is set to be strict about
# timestamp types. In this case adding IGNORE below will fix the issue, but may mask other
# issues. If this issue recurs we should probably add special support to the migrator to handle
# the special '0000-00-00 00:00:00' timestamp value, similar to what is done for NULL.
# "IGNORE "
"INTO TABLE %(schema)s.%(table)s CHARACTER SET utf8 "
# We use | as an escape character as MySQL's default of \ is problematic and at some times is
# not compatible with the csv module's parser.
"""FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '|' """
"LINES TERMINATED BY '\\n' "
'(%(columns)s) SET %(sets)s'
) % {
'infile': self.c.import_filename,
'schema': self.c.destination_schema,
'table': self.c.table_config.table_name,
'columns': ', '.join(
('' if col.ignore else '@') + col.name
for col in self.c.export_columns
),
'sets': ', '.join(
"%s = IF(%s = '%s', NULL, %s)" % (
col.name,
'@' + col.name,
NULL_SENTINEL,
conn.column_insert_sql(col) % {'?': '@' + col.name}
)
for col in self.c.export_columns
if not col.ignore
),
}
)
cur.execute(sql)
self.c.num_records_imported = cur.rowcount
conn.commit()
def get_column_metadata(self, conn):
columns = []
with db.cursor(conn, dictionary=True) as cur:
cur.execute('DESCRIBE %s.%s' % (self.c.source_schema, self.c.table_config.table_name))
for column in cur.fetchall():
alias = '%s.' % (self.c.table_config.table_alias,) if self.c.table_config.table_alias else ''
base_type = column['Type'].lower().split('(')[0] # Ignore anything after an opening parenthesis
col = db.Column(
column['Field'],
self.TYPE_MAP.get(base_type, db.ColumnType.STRING),
column['Key'] == 'PRI',
ignore=(column['Field'].lower() in self.c.table_config.ignore_columns),
source_alias=alias
)
columns.append(col)
self.column_map[col.lname] = col
if (
self.c.migration_type == orm.MigrationType.DELTA
or self.c.chunk_migration_type == orm.ChunkMigrationType.DIRECT
):
# Check the destination for the primary key columns as well since the schemas may be different
with db.shard_connection(self.c.destination_shard, read=True) as conn:
primary_key = conn.get_table_primary_key_columns(
self.c.destination_schema,
self.c.table_config.table_name
)
for col_name in primary_key:
if col_name.lower() not in self.column_map:
raise UnrecoverableError(
'Primary key column in destination does not exist in source '
'table=%s column=%s source=%s destination=%s' % (
self.c.table_config.table_name, col_name, self.c.source_shard, self.c.destination_shard))
self.column_map[col_name.lower()].is_primary_key = True
self.c.columns = columns
def calculate_verification_status(self, migration, table_verification_statuses):
with db.shard_connection(self.c.destination_shard, read=False) as conn:
for table_config in config.SOURCES[self.c.source_shard]['config']['tables']:
table_data = self.check_table(table_config, conn)
if table_data is None:
continue
table_verification_statuses.setdefault(table_data.verification_status, []).append(table_data)
if table_data.status not in ('chunks_queued', 'empty'):
raise Error('Something is amiss, migration status is %r while table %r has status %r' % (
migration.status, table_config.table_name, table_data.status))
if len(table_verification_statuses) == 1:
return table_verification_statuses.keys()[0]
# If some tables are unknown but all others are something else, use the else
elif (
len(table_verification_statuses) == 2
and 'unknown' in table_verification_statuses
):
return [
s for s in table_verification_statuses.keys()
if s != 'unknown'
][0]
else:
return 'failed'
def _run(self):
time.sleep(random.randint(0, 10))
start = datetime.now()
if self.c.source_type != 'crate':
raise UnrecoverableError('This exporter only supports crate sources, passed in source_type was %r' % (
self.c.source_type,))
self.chunk.status = 'exporting'
self.chunk.start_time = int(time.time() * 1000)
self.chunk.update()
with db.shard_connection(self.c.source_shard, read=True) as crate_conn:
self.log('Starting export')
self.c.export_dir = os.path.join(worker.SOURCE_DIR, ('%s_%s.%s_%s_%s' % (
self.c.migration_id, self.c.source_schema, self.c.table_config.table_name, self.c.partition_val,
self.c.chunk_num)))
def make_source_dir(node):
cmd = (
'sudo mkdir -p %(export_dir)s && sudo chmod 777 %(export_dir)s'
) % {
'export_dir': self.c.export_dir,
}
try:
ssh.SSHHost(
node['host'], node['ssh_port'], config.SSH_USER,
identity=config.SSH_PRIVKEY
).run(cmd)
except ssh.SSHException, e:
raise CommandException('Checking for and removing export file failed %r' % (e,))
crate_cluster = config.DESTINATIONS[self.c.source_shard]
data_nodes = crate_cluster['config']['data_nodes']
threads = []
for node in data_nodes:
thread = threading.Thread(target=make_source_dir, args=(node,))
thread.start()
threads.append(thread)
for thread in threads:
thread.join()
# Inline trimming may be in self.c.where_clauses
wheres = copy.deepcopy(self.c.where_clauses)
where_values = copy.deepcopy(self.c.where_values)
wheres.extend([
'%s >= %%(?)s' % (self.c.table_config.chunk_col,),
'%s < %%(?)s' % (self.c.table_config.chunk_col,),
])
where_values.extend([
self.c.start_id,
self.c.start_id + self.c.chunk_size,
])
if self.c.table_config.join:
# TODO: This is a HORRIBLE HACK that removes the hard-coded table alias from the partition and chunk
# columns.
# It is likely to cause problems. A refactoring of the join code is needed.
column_alias = '%s.' % (self.c.table_config.table_alias,)
fixed_wheres = []
for where in wheres:
if where.startswith(column_alias):
where = where[len(column_alias):]
fixed_wheres.append(where)
wheres = fixed_wheres
# We want to kick off the streamer here to enable streaming while the export file is still being written
# but if we do and the export below fails the streamer will be stuck in an infinite loop and the next time
# the export task gets retried we'll kick off yet another streamer task, potentially corrupting data.
# TODO(jpatrin): A fix for this would be to use a random token so the streamer knows it belongs to the
# running exporter. Before kicking off the streamer, write a file with a random UUID next to where the
# exported file will be. Put the token in self.c so it gets passed to the streamer. When the streamer
# starts up, read the token file and check it vs. the token in self.c. If it's different, mark the chunk
# as failed, end the streamer without doing anything, and don't retry the task.
# NOTE: This would only work with crate if we append the STOP sentinel to all of the json files that each
# node writes to export_dir or if we come up with another way to know when the export is finished.
#streamer.queue_stream_chunk(self.c)
# TODO(jpatrin): Don't we need to add the join clause here to make the where_clauses work for inline
# trimming? They won't work with COPY TO, but we may need to figure out a way to support it.
sql = (
"COPY %(schema)s.%(table)s (%(columns)s) "
"WHERE %(wheres)s "
"TO DIRECTORY '%(export_dir)s' "
"WITH (format='json_array') "
) % {
'columns': ', '.join(col.name for col in self.c.export_columns),
'schema': self.c.source_schema,
'table': self.c.table_config.table_name,
'wheres': ' AND '.join(wheres),
'export_dir': self.c.export_dir,
}
#self.log_warning('%s', sql)
#self.log_warning(repr(where_values))
with db.cursor(crate_conn) as cur:
cur.execute(sql % {'?': crate_conn.PARAMETER_PLACEHOLDER}, where_values)
self.c.num_records_exported = cur.rowcount
def _migrate_partition_shard(
redis_conn,
partition_val, namespace, source_shard, destination_shard,
force=False, requeue=False, migration_type=orm.MigrationType.FULL,
parent_migration_id=None, latest_migration=None, wanted_delta_start=None, wanted_delta_end=None,
chunk_migration_type=orm.ChunkMigrationType.INDIRECT
):
if source_shard not in config.SOURCES:
raise UnrecoverableError('There is no configured source shard named %r' % (source_shard,))
if destination_shard not in config.DESTINATIONS:
raise UnrecoverableError('There is no configured destination shard named %r' % (destination_shard,))
if chunk_migration_type not in orm.ChunkMigrationType.__ALL__:
raise UnrecoverableError('chunk_migration_type %r is not valid' % (chunk_migration_type,))
start = datetime.now()
migration_id = str(uuid.uuid4())
log.info('Migrating partition shard type=%s chunk_migration_type=%s partition_val=%s source_shard=%s '
'destination_shard=%s migration_id=%s',
migration_type, chunk_migration_type, partition_val, source_shard, destination_shard, migration_id)
tables = {}
# Allow the last_migration to be passed in so that container migrations (AUTODELTA and COMPLETE) can pass in
# the last migration before them for the initial sub-migration that is started as part of the container
# migration. Otherwise, this code will find the container migration, which has only just been inserted, and
# fail.
if latest_migration is None:
latest_migration = orm.Migration.get_latest(
redis_conn,
source_shard=source_shard,
destination_shard=destination_shard,
partition_val=partition_val
)
# If the last migration is not finished we don't want to allow another one to be queued unless the requestor
# is specifically asking for requeueing (i.e. in the case of a failed migration or one that was stopped
# mid-way).
if latest_migration is not None:
if latest_migration.migration_id == parent_migration_id:
log.debug('Latest migration is our parent, ignoring')
elif not requeue:
if latest_migration.status not in ('finished', 'empty'):
raise UnrecoverableError(
'Latest migration for this partition is not finished '
'partition_val=%s source_shard=%s destination_shard=%s '
'latest_migration_id=%s latest_migration_status=%s' % (
partition_val, source_shard, destination_shard,
latest_migration.migration_id, latest_migration.status))
elif migration_type not in orm.MigrationType.__DELTA_TYPES__:
raise UnrecoverableError(
'An initial migration has already been performed '
'partition_val=%s source_shard=%s destination_shard=%s '
'latest_migration_id=%s latest_migration_status=%s' % (
partition_val, source_shard, destination_shard,
latest_migration.migration_id, latest_migration.status))
if migration_type in orm.MigrationType.__DELTA_TYPES__:
delta_end = wanted_delta_end
if wanted_delta_start is not None:
delta_start = wanted_delta_start
else:
found_migrations = orm.Migration.get_by_index(
redis_conn,
source_shard=source_shard,
destination_shard=destination_shard,
partition_val=partition_val,
)
found_migrations.sort(key=lambda m: m.start_time)
finished_migrations = [m for m in found_migrations if m.status in ('finished', 'empty')]
if finished_migrations:
latest_finished_migration = finished_migrations[-1]
if latest_finished_migration.type in orm.MigrationType.__DELTA_TYPES__:
# Delta on top of delta uses the latest finished delta's source_start_time
base_migration = latest_finished_migration
else:
# Delta on top of full migration uses the earlier migration's source_start_time to avoid missing
# changes to records that may have been imported before the last finished full migration.
base_migration = found_migrations[0]
# If a delta_end was previously specified, use that as our new delta_start
if base_migration.delta_end is not None:
delta_start = base_migration.delta_end
elif base_migration.source_start_time:
delta_start = (
base_migration.source_start_time / 1000 - config.DELTA_START_FUDGE_FACTOR)
elif base_migration.start_time:
log.warning('Base migration did not have a source_start_time, falling back on start_time')
delta_start = (
base_migration.start_time / 1000 - config.DELTA_START_FUDGE_FACTOR_FALLBACK)
else:
raise UnrecoverableError(
'Latest migration does not have a source_start_time, start_time, or delta_end, '
'which should not be possible')
if delta_start is not None and delta_end is not None and delta_end < delta_start:
raise UnrecoverableError(
'delta_start is after delta_end, this delta migration is nonsensical '
'delta_start=%u delta_end=%u' % (delta_start, delta_end))
log.info('delta_start=%r', delta_start)
else:
raise UnrecoverableError(
'No finished migration was found for this delta migration to be based on')
else:
delta_start = None
delta_end = None
migration = orm.Migration(redis_conn)
migration.type = migration_type
migration.chunk_migration_type = chunk_migration_type
migration.migration_id = migration_id
migration.source_shard = source_shard
migration.destination_shard = destination_shard
migration.partition_val = partition_val
migration.namespace = namespace
if migration_type in orm.MigrationType.__CONTAINER_TYPES__:
migration.status = 'in_progress'
else:
migration.status = 'queued'
migration.verification_status = ''
migration.delta_start = delta_start
migration.delta_end = delta_end
migration.parent_migration_id = parent_migration_id
with db.shard_connection(source_shard, read=True) as conn:
migration.source_start_time = conn.get_current_timestamp()
migration.start_time = int(time.time() * 1000)
migration.insert()
if migration_type == orm.MigrationType.AUTODELTA:
# NOTE(jpatrin): We could skip most of the logic above for AUTODELTA since we're going
# to do it again here for its first DELTA but for now I'd rather leave it in so we don't
# miss anything.
_migrate_partition_shard(
redis_conn,
partition_val, namespace, source_shard, destination_shard,
force, requeue, orm.MigrationType.DELTA, migration.migration_id, latest_migration=latest_migration,
wanted_delta_start=wanted_delta_start, wanted_delta_end=wanted_delta_end,
chunk_migration_type=chunk_migration_type)
elif migration_type == orm.MigrationType.COMPLETE:
_migrate_partition_shard(
redis_conn,
partition_val, namespace, source_shard, destination_shard,
force, requeue, orm.MigrationType.FULL, migration.migration_id,
chunk_migration_type=chunk_migration_type)
else:
for table_config in config.SOURCES[source_shard]['config']['tables']:
tables[table_config.table_name] = _migrate_partition_shard_table(
migration, table_config, latest_migration, requeue, force)
log.info('Migration queued '
'partition_val=%s source_shard=%s elapsed=%s',
partition_val, source_shard, datetime.now() - start)
return (migration, tables)
def _run(self):
time.sleep(random.randint(0, 10))
start = datetime.now()
if self.c.source_type != 'mysql':
raise UnrecoverableError('This exporter only supports mysql sources, passed in source_type was %r' % (
self.c.source_type,))
self.chunk.status = 'exporting'
self.chunk.start_time = int(time.time() * 1000)
self.chunk.update()
source_host = config.SOURCES[self.c.source_shard]['config']['read_host']['host']
with db.shard_connection(self.c.source_shard, read=True) as mysql_conn:
self.log('Starting export')
mysql_host_user = '******' % (
config.SSH_USER, source_host)
cmd = (
'sudo mkdir -p %(source_dir)s && sudo chmod 777 %(source_dir)s; '
'[ ! -e %(outfile)s ] || sudo rm %(outfile)s'
) % {
'tmp_dir': config.TMP_DIR,
'source_dir': worker.SOURCE_DIR,
'outfile': self.c.export_filename,
}
sshcmd = (
'ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -o LogLevel=ERROR '
'-o ControlMaster=no -o ControlPath=none '
'-p %r -i %s %s "%s"' % (
config.MYSQL_SSH_PORT, config.SSH_PRIVKEY, mysql_host_user, ssh.escape_double_quotes(cmd)))
rm_existing = subprocess.Popen(
sshcmd,
shell=True,
stdin=subprocess.PIPE)
rm_existing.stdin.close()
rm_existing.wait()
if rm_existing.returncode != 0:
raise CommandException('Checking for and removing export file failed with exit code %r' % (
rm_existing.returncode,))
# Inline trimming may be in self.c.where_clauses
wheres = copy.deepcopy(self.c.where_clauses)
where_values = copy.deepcopy(self.c.where_values)
wheres.extend([
'%s >= %%(?)s' % (self.c.table_config.chunk_col,),
'%s < %%(?)s' % (self.c.table_config.chunk_col,),
])
where_values.extend([
self.c.start_id,
self.c.start_id + self.c.chunk_size,
])
# We want to kick off the streamer here to enable streaming while the export file is still being written
# but if we do and the export below fails the streamer will be stuck in an infinite loop and the next time
# the export task gets retried we'll kick off yet another streamer task, potentially corrupting data.
# TODO(jpatrin): A fix for this would be to use a random token so the streamer knows it belongs to the
# running exporter. Before kicking off the streamer, write a file with a random UUID next to where the
# exported file will be. Put the token in self.c so it gets passed to the streamer. When the streamer
# starts up, read the token file and check it vs. the token in self.c. If it's different, mark the chunk
# as failed, end the streamer without doing anything, and don't retry the task.
#streamer.queue_stream_chunk(self.c)
# TODO(jpatrin): Don't we need to add the join clause here to make the where_clauses work for inline
# trimming?
sql = (
(
"SELECT %(columns)s INTO OUTFILE '%(outfile)s' CHARACTER SET utf8 "
# We use | as an escape character as MySQL's default of \ is problematic and at some times is not
# compatible with the csv module's parser.
"""FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '|' """
"LINES TERMINATED BY '\\n' "
"FROM %(schema)s.%(table)s %(table_alias)s WHERE %(wheres)s"
# Adding sorting here only slows down the query.
# "ORDER BY %(chunk_col)s ASC"
) % {
'columns': ', '.join(
(
# We're using NULL_SENTINEL here because MySQL uses a nonstandard value for null
# in its OUTFILE which is not as easy to detect and convert as I'd like.
"IF(%s IS NULL, '%s', %s)" % (
col.name,
NULL_SENTINEL,
mysql_conn.column_query_sql(col)
)
) for col in self.c.export_columns
),
'outfile': self.c.export_filename,
'schema': self.c.source_schema,
'table': self.c.table_config.table_name,
'table_alias': self.c.table_config.table_alias,
'wheres': ' AND '.join(wheres),
}
)
with db.cursor(mysql_conn) as cur:
cur.execute(sql % {'?': mysql_conn.PARAMETER_PLACEHOLDER}, where_values)
self.c.num_records_exported = cur.rowcount
# kick off the streamer
streamer.queue_stream_chunk(self.c)
self.chunk.num_records_exported = self.c.num_records_exported
self.chunk.export_elapsed_ms = int((datetime.now() - start).total_seconds() * 1000)
self.chunk.update()
# The streaming may or may not have started, so we only update the status if it's still set to exporting
self.chunk.update_status('exported', where_status='exporting')
# signal to the processor that we have reached the end of the data
self.log('Signaling EOF to conversion')
cmd = 'sudo bash -c "echo STOP >> %s"' % (
ssh.escape_double_quotes(self.c.export_filename),
)
signal_stop = subprocess.Popen(
'ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -o LogLevel=ERROR '
'-p %r -i %s %s@%s "%s"' % (
config.MYSQL_SSH_PORT, config.SSH_PRIVKEY, config.SSH_USER,
source_host,
ssh.escape_double_quotes(cmd)),
shell=True,
stdin=subprocess.PIPE)
signal_stop.stdin.close()
signal_stop.wait()
self.log('Finished chunk export num_records_exported=%s elapsed=%s',
self.c.num_records_exported, datetime.now() - start)
