Checks AUTO_INCREMENT fields in MySQL to see if near MAXVALUE

mysql_autoinc_checker.go

/*
   Copyright (c) 2014, Percona LLC and/or its affiliates. All rights reserved.

   This program is free software: you can redistribute it and/or modify
   it under the terms of the GNU Affero General Public License as published by
   the Free Software Foundation, either version 3 of the License, or
   (at your option) any later version.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU Affero General Public License for more details.

   You should have received a copy of the GNU Affero General Public License
   along with this program.  If not, see <http://www.gnu.org/licenses/>
*/

/*
   mysql_int_checker.go

   All integer-based fields in MySQL have a maximum possible value.
   This application will check the MAX() value of all integer-based fields in a MySQL
   database and print a report showing how close the column is to its maximum value.

	Usage of ./mysql_int_checker:
	  -D: Database to check
	  -h: MySQL Host
	  -p: MySQL password
	  -q: Don't print summary or header lines.
	  -r: Print report only if ratio > this value.
	  -t: Table to check. If not specified, checks all tables in database.
	  -u: MySQL user. Must have SELECT priv on database.

	Examples:
	  -- Check all columns on on tables in the 'foo' database, full summary:
	  ./mysql_int_checker -D foo -h testhost1 -u myuser -p mypw

	  -- Check all columns on 'foo' database, only reporting if 90% to max
      ./mysql_int_checker -D foo -h testhost1 -u myuser -p mypw -q -r 90

      -- Check all columns on table 'bar' in database 'foo', reporting > 75%
      ./mysql_int_checker -D foo -t bar -h testhost1 -u myuser -p mypw -q -r 75
*/

package main

import (
	"database/sql"
	"flag"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"os"
)

func main() {

	var hostToCheck string
	var dbToCheck string
	var tableToCheck string
	var summary bool
	var reportPct int

	var mysqlUn string
	var mysqlPw string

	// Set up and parse command-line flags
	flag.StringVar(&hostToCheck, "h", "", "MySQL Host")
	flag.StringVar(&dbToCheck, "D", "", "Database to check")
	flag.StringVar(&tableToCheck, "t", "", "Table to check. If not specified, checks all tables in database.")

	flag.StringVar(&mysqlUn, "u", "mysql_admin", "MySQL user. Must have SELECT priv on database.")
	flag.StringVar(&mysqlPw, "p", "", "MySQL password")

	flag.IntVar(&reportPct, "r", 0, "Print report only if ratio > this value.")
	flag.BoolVar(&summary, "q", true, "Print summary and header lines.")

	flag.Parse()

	// Sanity check
	if hostToCheck == "" || dbToCheck == "" {
		fmt.Println("You must supply at least a MySQL host and a database.")
		flag.PrintDefaults()
		os.Exit(1)
	}

	// Print summary
	if summary {
		fmt.Print("\n-- Summary --\n")
		fmt.Println("Host:     ", hostToCheck)
		fmt.Println("Database: ", dbToCheck)

		if tableToCheck != "" {
			fmt.Println("Table:    ", tableToCheck)
		} else {
			fmt.Println("Table:     -- All Tables --")
		}

		fmt.Println("Username: ", mysqlUn)
		fmt.Println("Password: ", mysqlPw)
		fmt.Println("Report %: ", reportPct)
	}

	// MySQL Connect
	db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:3306)/%s", mysqlUn, mysqlPw, hostToCheck, dbToCheck))
	if err != nil {
		fmt.Printf("Error connecting to MySQL on '%s': \n", hostToCheck, err)
		db.Close()
		os.Exit(1)
	}

	// Make sure we close the connection when we exit for any reason
	defer db.Close()

	// Check connection is alive.
	err = db.Ping()
	if err != nil {
		fmt.Printf("Unable to ping mysql at '%s': %s\n", hostToCheck, err)
		db.Close()
		os.Exit(1)
	}

	// Construct our base i_s query
	var tableExtraSql string
	if tableToCheck != "" {
		tableExtraSql = fmt.Sprintf("AND TABLE_NAME = '%s'", tableToCheck)
	}

	baseSql := fmt.Sprintf(`
		SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, (CASE DATA_TYPE
    	  WHEN 'tinyint' THEN 255
    	  WHEN 'smallint' THEN 65535
    	  WHEN 'mediumint' THEN 16777215
    	  WHEN 'int' THEN 4294967295
    	  WHEN 'bigint' THEN 18446744073709551615
   		END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
    	) AS MAX_VALUE FROM information_schema.columns
		WHERE TABLE_SCHEMA = '%s' %s
		AND DATA_TYPE IN ('tinyint', 'int', 'mediumint', 'bigint')`,
		dbToCheck, tableExtraSql)

	// Get a list of all the integer-type columns in the specified database
	columnsToCheck, err := db.Query(baseSql)
	if err != nil {
		fmt.Println("Couldn't retrieve column information: ", err)
		fmt.Println("SQL: ", baseSql)
		
		// Because os.Exit() doesn't call defers
		columnsToCheck.Close()
		db.Close()
		
		os.Exit(1)
	}

	// Call destructor on rows when we exit; mysql_free_result equiv
	defer columnsToCheck.Close()

	// Variables to store info from the rows
	var tableName string
	var columnName string
	var columnType string
	var maxValue uint64

	// Special SQL types to handle the NULL case
	var ratio sql.NullFloat64
	var currentValue sql.NullInt64

	if summary {
		fmt.Print("\n-- Report --\n")
	}

	// Loop over rows received from i_s query above.
	for columnsToCheck.Next() {
		err := columnsToCheck.Scan(&tableName, &columnName, &columnType, &maxValue)
		if err != nil {
			log.Fatal("Scanning Row Error: ", err)
		}

		// Check this column
		query := fmt.Sprintf("SELECT MAX(%[1]s.%[2]s), ROUND((MAX(%[1]s.%[2]s)/%[3]d)*100, 2) AS ratio FROM %[4]s.%[1]s",
			tableName, columnName, maxValue, dbToCheck)
		err = db.QueryRow(query).Scan(&currentValue, &ratio)
		if err != nil {
			fmt.Printf("Couldn't get MAX(%s.%s): %s\n", tableName, columnName, err)
			fmt.Println("SQL: ", query)
			continue
		}

		// Print report
		if ratio.Valid && ratio.Float64 >= float64(reportPct) {
			fmt.Printf("'%s'.'%s' - Type: '%s' - ", tableName, columnName, columnType)
			fmt.Printf("ColumMax: '%d'", maxValue)
			fmt.Printf(" - CurVal: '%d'", currentValue.Int64)
			fmt.Printf(" - FillRatio: '%.2f'\n", ratio.Float64)
		}
	}

	// Just to be safe and clean
	columnsToCheck.Close()
	db.Close()

	if summary {
		fmt.Print("\nFinished!\n")
	}
}