find_nopk.py

'''
Simple frmsearch command to find tables without a primary key

Run this via:

# curl get.dbsake.net > dbsake
# export DATADIR=$(mysql -sse 'select @@datadir') PYTHONPATH=$PWD/dbsake
# python find_nopk.py
!! mysql.slow_log does not appear to have a primary key !!
---
    --
    -- Table structure for table `slow_log`
    -- Created with MySQL Version 10.0.17
    --

    CREATE TABLE `slow_log` (
      `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
      `user_host` mediumtext NOT NULL,
      `query_time` time(6) NOT NULL,
      `lock_time` time(6) NOT NULL,
      `rows_sent` int(11) NOT NULL,
      `rows_examined` int(11) NOT NULL,
      `db` varchar(512) NOT NULL,
      `last_insert_id` int(11) NOT NULL,
      `insert_id` int(11) NOT NULL,
      `server_id` int(10) unsigned NOT NULL,
      `sql_text` mediumtext NOT NULL,
      `thread_id` bigint(21) unsigned NOT NULL
    ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
---
!! mysql.general_log does not appear to have a primary key !!
---
    --
    -- Table structure for table `general_log`
    -- Created with MySQL Version 10.0.17
    --

    CREATE TABLE `general_log` (
      `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
      `user_host` mediumtext NOT NULL,
      `thread_id` bigint(21) unsigned NOT NULL,
      `server_id` int(10) unsigned NOT NULL,
      `command_type` varchar(64) NOT NULL,
      `argument` mediumtext NOT NULL
    ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
---

'''

import glob
import os

from dbsake.core.mysql.frm import *

datadir = os.environ.get('DATADIR', '/var/lib/mysql')

# scan ${datadir}/*/*.frm for files
for path in glob.iglob(os.path.join(datadir, '*', '*.frm')):
    table = parse(path)
    #print("Parsed: %s" % (path,))
    if table.type != 'VIEW':
        database = os.path.basename(os.path.dirname(path))
        if '@' in database:
            # dbsake.core.mysql.frm.tablename.decode(...)
            # to decode MySQL filename encoding -> unicode
            database = tablename.decode(database)
        # ignore performance_schema
        if database in ('performance_schema',):
            continue
        #print("Table: %s" % (table.name,))
        # find UNIQUE indexes
        unique_keys = [key for key in table.keys if key.is_unique]
        # find UNIQUE indexes that only have not null columns
        # Note: frm api doesn't quite expose context here about whether a
        # column is nullable.
        not_null_unique_keys = [key for key in unique_keys
                                if all(u'NOT NULL' in p.column.type_name
                                       for p in key.parts)]
        # if we fail to find any unque indexes where all columns are not
        # null output - missing a primary key
        if not not_null_unique_keys:
            print("!! %s.%s does not appear to have a primary key !!" % (database, table.name,))
            print("---")
            # reindent the CREATE TABLE statement
            print("\t" + "\n\t".join(table.format().splitlines()))
            print("---")