get_recent_checksums.py

#!/usr/bin/env python
import argparse
import socket
import sys
from lib import host_utils
from lib import mysql_lib

LINE_TEMPLATE = ('{master_instance:<MSPC}'
                 '{instance:<RSPC}'
                 '{reported_at:<22}'
                 '{db:<DBSPC}'
                 '{tbl:<TSPC}'
                 '{row_count:<RCSPC}'
                 '{row_diffs:<DCSPC}'
                 '{checksum_status}')


def main():
    parser = argparse.ArgumentParser(description='MySQL checksum interface')
    parser.add_argument('-i',
                        '--instance',
                        help='Defaults to localhost:3306',
                        default=''.join((socket.getfqdn(), ':3306')),
                        required=True)
    parser.add_argument('-d',
                        '--db',
                        help="Restrict results to a single named db",
                        default=False)
    parser.add_argument('-t',
                        '--table',
                        help='Restrict results to a single table',
                        default=False)
    args = parser.parse_args()
    instance = host_utils.HostAddr(args.instance)

    checksums = get_checksums(instance, args.db)
    if not checksums:
        print "No data found!"
        sys.exit(1)

    format_str, line_length = generate_format_string(checksums)
    header = format_str.format(master_instance='Master',
                               instance='Replica',
                               reported_at='Date',
                               db='Database',
                               tbl='Table',
                               row_count='Row Count',
                               row_diffs='Diff Count',
                               checksum_status='Status')
    print header
    print '=' * line_length

    # now actually print the output.
    for checksum in checksums:
        if args.table is False or args.table == checksum['tbl']:
            checksum['reported_at'] = str(checksum['reported_at'])
            print format_str.format(**checksum)


def generate_format_string(checksums):
    """ Use the base template and proper string lengths to make the output
        look nicer.

        Args:
        checksums - a collection of checksum rows.

        Returns:
        format_str - a format string with spacing offsets filled in.
        line_length - the maximum length of the line + some extra space
    """
    # initial padding values
    padding = {'master_instance': len('Master'),
               'instance': len('Replica'),
               'db': len('Database'),
               'tbl': len('Table'),
               'reported_at': len('Date'),
               'row_count': len('Row Count'),
               'row_diffs': len('Diff Count'),
               'checksum_status': len('Status')}

    line_length = 40 + sum(padding.values())

    for checksum in checksums:
        # Humans don't care about false positives for diffs
        if (checksum['checksum_status'] == 'ROW_DIFFS_FOUND' and
                checksum['rows_checked'] == 'YES' and
                checksum['row_diffs'] == 0):
            checksum['checksum_status'] = 'GOOD'

        for key, value in padding.items():
            if len(str(checksum[key])) > padding[key]:
                line_length += len(str(checksum[key])) - padding[key]
                padding[key] = len(str(checksum[key]))

    # regenerate the output template based on padding.
    format_str = LINE_TEMPLATE.replace(
        'MSPC', str(padding['master_instance'] + 3)).replace(
        'RSPC', str(padding['instance'] + 3)).replace(
        'DBSPC', str(padding['db'] + 3)).replace(
        'TSPC', str(padding['tbl'] + 3)).replace(
        'RCSPC', str(padding['row_count'] + 3)).replace(
        'DCSPC', str(padding['row_diffs'] + 3))

    return format_str, line_length


def get_checksums(instance, db=False):
    """ Get recent mysql replication checksums

    Args:
    instance - a hostaddr object for what server to pull results for
    db - a string of a data to for which to restrict results

    Returns:
    A list of dicts from a select * on the relevant rows
    """

    vars_for_query = dict()
    vars_for_query['instance'] = instance

    zk = host_utils.MysqlZookeeper()
    host_shard_map = zk.get_host_shard_map()

    # extra SQL if this is a sharded data set.
    SHARD_DB_IN_SQL = ' AND db in ({sp}) '

    if db is False:
        cnt = 0
        shard_param_set = set()
        try:
            for entry in host_shard_map[instance.__str__()]:
                key = ''.join(('shard', str(cnt)))
                vars_for_query[key] = entry
                shard_param_set.add(key)
                cnt += 1
            shard_param = ''.join(('%(',
                                   ')s,%('.join(shard_param_set),
                                   ')s'))
        except KeyError:
            # if this is not a sharded data set, don't use this.
            shard_param = None

    else:
        shard_param = '%(shard1)s'
        vars_for_query['shard1'] = db

    # connect to the instance we care about and get some data.
    conn = mysql_lib.connect_mysql(instance, 'scriptrw')

    # We only care about the most recent checksum
    cursor = conn.cursor()

    sql_base = ("SELECT detail.master_instance, "
                "       detail.instance, "
                "       detail.db, "
                "       detail.tbl, "
                "       detail.reported_at, "
                "       detail.checksum_status, "
                "       detail.rows_checked, "
                "       detail.row_count, "
                "       detail.row_diffs "
                "FROM "
                "  (SELECT master_instance,"
                "          instance, "
                "          db, "
                "          tbl, "
                "          MAX(reported_at) AS reported_at "
                "   FROM test.checksum_detail "
                "   WHERE master_instance=%(instance)s "
                "   {in_db}"
                "   GROUP BY 1,2,3,4 "
                "  ) AS most_recent "
                "JOIN test.checksum_detail AS detail "
                "USING(master_instance, instance, db, "
                "tbl, reported_at) ")

    # and then fill in the variables.
    if shard_param:
        sql = sql_base.format(in_db=SHARD_DB_IN_SQL.format(sp=shard_param))
    else:
        sql = sql_base.format(in_db='')

    cursor.execute(sql, vars_for_query)
    checksums = cursor.fetchall()
    return checksums


if __name__ == "__main__":
    main()