Scan .frm files in a MySQL datadir for old DATE/TIME/TIMESTAMP columns

frmsearch.py

'''
Simple frmsearch command to find tables with pre 5.6 date/time types.

Run this via:

# curl get.dbsake.net > dbsake
# DATADIR=$(mysql -sse 'select @@datadir') PYTHONPATH=$PWD/dbsake python frmsearch.py
ALTER TABLE `mysql`.`procs_priv` FORCE;
ALTER TABLE `mysql`.`columns_priv` FORCE;
ALTER TABLE `mysql`.`general_log` FORCE;
ALTER TABLE `mysql`.`event` FORCE;
ALTER TABLE `mysql`.`tables_priv` FORCE;
ALTER TABLE `mysql`.`slow_log` FORCE;
ALTER TABLE `mysql`.`proc` FORCE;
ALTER TABLE `mysql`.`proxies_priv` FORCE;
#


Where each of these tables has at least one column with an old type. For instance procs_privs uses the old TIMESTAMP type:

# ./dbsake frmdump --type-codes /root/mysql-5.5.40/data/mysql/procs_priv.frm
--
-- Table structure for table `procs_priv`
-- Created with MySQL Version 5.5.40
--

CREATE TABLE `procs_priv` (
  ...
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  /* MYSQL_TYPE_TIMESTAMP */,
  ...
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Procedure privileges';
'''

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 - this would be upgraded via mysql_upgrade
        if database in ('performance_schema',):
            continue
        #print("Table: %s" % (table.name,))
        for column in table.columns:
            if column.type_code.name in ('TIMESTAMP', 'TIME', 'DATETIME'):
                print("ALTER TABLE `%s`.`%s` FORCE;" % (database, table.name))
                break